存储引擎

查看建表语句：show create table 表名;

查看当前数据库支持的存储引擎 show engines;

— 1、在创建表时，指定存储引擎

— 查看当前数据库支持的存储引擎
show engines;

— 查看建表语句
show create table account;

— 创建表 my_syisam， 并指定myisam存储引擎

create table my_myisam(
id int
) engine= MyISAM;
show create table my_myisam;

— 创建表my_memory,指定memory存储引擎
create table my_memory(
id int
) engine=Memory;
show create table my_memory;
show ENGINES;

 

存储引擎特点

InnoDB是一种兼顾高可靠性和高性能的通用存储引擎，在MySQL5.5之后，InnoDB是默认的MySQL存储引擎

特点

DML操作遵循ACID模型，支持事务；行级锁；外键

MyISAM早期的默认存储引擎

特点

不支持事务，外键

支持表锁，不支持行锁

访问速度快

 

Memory表数据存储在内存中，受硬件、断电等影响，只能用来做临时表或缓存表

特点

内存存放

hash索引（默认）

 

索引

index是帮助mysql高效获取数据的数据结构（有序）

索引结构

mysql的索引是在存储引擎层实现的，不同的存储引擎有不同的结构，

B+Tree索引：最常见的索引类型，大部分引擎都支持B+树索引

Hash索引：底层是用哈希表实现的，只有精确匹配索引列的查询才有效，不支持范围查询

R-Tree（空间索引）：是MyISAM引擎的一个特殊索引类型，主要用于地理空间数据类型，通常使用较少

Full-text（全文索引）：是一种通过建立倒排索引，快速匹配文档的方式，类似于Lucene, Solr, Es

 

索引分类

主键索引 primary 表中主键创建的索引

唯一索引 unique 避免表中数据重复

常规索引 快速定位特定数据

全文索引 fulltext 全文索引查询的是文文本中的关键词，而不是比较索引中的值

在InnoDB存储引擎中，根据索引的存储形式，又可以分为以下两种：

聚集索引（Clustered Index）将数据存储与索引放到了一块，索引结构的叶子节点保存了行数据。特点：必须有且唯一

二级索引（Secondary Index）将数据与索引分开存储，索引结构的叶子节点关联的是对应的主键。特点：可以存在多个

 

use study;
— 创建索引
create [unique|fulltext] index index_name on table_name (index_col_name,…);
— 查看索引
show index from table_name;
— 删除索引
drop index index_name on table_name;

— name字段为姓名字段，该字段的值可能会重复，为该字段创建索引
create index idx_user_name on tb_user(name);
show index from tb_user;

— 3/为profession、age、status创建联合索引
create index idx_user_pro_age_sta on tb_user(profession, age, status);

create index idx_user_name on tb_user(name);
create unique index idx_user_phone on tb_user(phone);
create index idx_user_pro_age_sta on tb_user(profession, age, status);
create index idx_email on tb_user(email);

— SQL性能分析
— SQL执行频率
— MySQL客户端连接成功后，通过show[session|global]status 命令可以提供服务器状态信息。
— 查询insert/update/delete/select/的访问频次：
show global status like ‘Com_’;
show global status like ‘Com_select’;
show global status like ‘Com_insert’;

— 慢查询日志
— 慢查询日志记录了所有执行时间超过指定参数(long_query_time，单位：秒，默认10秒）的所有SQL语句的日志。
— mysql的慢查询日fatkun志默认没有开启，需要在MySQL的配置文件(/etc/my.cnf)中配置如下信息：
— 开启MySQL慢日志查询开关
slow_query_log=1
— 设置慢日志的时间为2秒，SQL语句执行时间超过2秒，就会视为慢查询，记录慢查询日志
long_query_time=2

— 查看每一条SQL的耗时基本情况
show profiles;

— 查看指定query_id的sql语句各个阶段的耗时情况
show profile for query query_id;

— 查看指定query_id的SQL语句cpu的使用情况
show profile cpu for query query_id;

— explain执行计划
— explain 或者desc命令获取mysql如何执行select语句的信息，包括在select 语句执行过程中表现如何连接和连接的顺序。
— 语法
— explain/desc select 字段列表 from 表名 where 条件;

— Id
— select 查询的序列号，表示查询中执行select自居或者是操作表的顺序（id相同，执行顺序从上到下；id不同，值越大，越优先执行）
— select_type
— 表示select的类型，常见的取值有simple（简单表，即不使用连接或者子查询）、primary（主查询，即外层的查询）、union（union中的第二个或者后面的查询语句）、subquery（select/where 之后包含了子查询）等

— type
— 表示连接类型，性能由好到差的连接类型为null、system、const、eq_ref、range、index、all

— possible_key
— 显示可能应用在这张表上的索引，一个或多个

— key
— 实际使用的索引，如果为null，则没有使用索引。

— key_len
— 表示索引中使用的字节数，该值为索引字段最大可能长度，并非实际使用长度，在不损失精确性的前提下，长短越短越好
— rows
— mysql认为必须要执行查询的行数，在innodb引擎的表中，是一个估计值，可能并不总是精确的

— filtered
— 表示返回结果的行数占续读取行数的百分比，filtered的值越大越好

— 索引-使用规则-验证索引效率
— 语法： \G
select * from tb_user where id = ‘2’ \G;

— 针对字段创建索引
create index idx_sku_sn on tb_sku(sn);

— 然后再次执行相同的SQL语句，再次查看SQL的耗时
select * from tb_sku where sn = ‘10000000123123’\G;

— 最左前缀法则
— 如果索引了多列（联合索引），要遵守最左前缀法则。最左前缀法则指的是查询从索引的最左列开始，并且不跳过索引中的列。

 

第三章-SQL优化

插入数据

大批量插入数据

 

use study;
— 视图
— 视图（View）是一种虚拟存在的表。视图中的数据并不在数据库中实际存在，行和列数据来自定义视图的查询中使用的表，并且是在使用视图时动态生成的。
— 通俗的将，视图只保存了查询的SQL逻辑，不在村查询结果。所以我们在创建视图的时候，主要的工作就落在创建这条SQL查询语句上。
— 创建
— create [or replace] view 视图名称[(列名列表)] AS select语句 [with[cascaded|local] check OPTION]

— 创建视图
create or replace view stu_v_1 as select id, name from student where id <=10;

— 查询视图
— 查看创建视图语句： show create view 视图名称;
— 查看视图数据：select * from 视图名称…;
show create view stu_v_1;
select * from stu_v_1;

— 修改视图
— 方式一：create [or replace] view 视图名称[(列名列表)] as select 语句[with [cascaded|local] check option]
— 方式二：alter view 视图名称[(列名列表)] as select语句 [with[cascaded|local] check OPTION]
create or replace view stu_v_1 as select id, name, no from student where id <=10;
alter view stu_v_1 as select id, name from student where id <=10;
— 删除
— drop view [if exists] 视图名称[,视图名称]….
drop view if exists stu_v_1;
— ———————————————————
— 视图-检查选项cascaded

create or replace view stu_v_1 as select id, name from student where id <=20;
select * from stu_v_1;

insert into stu_v_1 VALUES(31, ‘测试’);
— ———————————————————

— 视图的检查选项
— 当使用with check option字句创建视图时， MySQL会通过视图检查正在更改的每个行，例如插入，更新，删除，以使其符合视图的定义。MySQL允许基于另一个视图创建视图，它还会检查依赖视图中的规则以保持一致性。为了确定检查的范围，mysql提供了两个选项：cascaded和local， 默认值为cascaded

— CASCADED
create or replace view stu_v_1 as select id, name from student where id <=20;

insert into stu_v_1 VALUES(32, ‘测试’);

create or replace view stu_v_2 as select id, name from stu_v_1 where id >=10 with cascaded check option;

insert into stu_v_2 VALUES(36, ‘测试’); — 报错：1369 – CHECK OPTION failed ‘study.stu_v_2’

— Local
….with local check option;

— 要使视图可更新，视图中的行与基础表中的行之间必须存在一对一的关系。如果视图包含以下任何一项，则该视图不可更新：
1、聚合函数或窗口函数（sum(),min(),max(),count()等)
2、DISTINCT
3、group by
4、having
5、union 或者 union all

— 视图作用
— 简单：简化用户对数据的理解、操作。常用的查询可以被定义为视图
— 安全：通过视图用户只能查询和修改他们所能见到的数据
— 数据独立：屏蔽真实表结构变化带来的影响

— 存储过程

— 特点：封装、复用
— 基本语法
— 创建
create procedure 存储过程名称([参数列表])
begin
— SQL语句
end;

create procedure p1()
BEGIN
select count(*) from student;

END;

调用
call p1();
— 查看
select * from information_schema.ROUTINES where ROUTINE_SCHEMA = ‘itcast’;

show create procedure p1;

— 删除
drop procedure if exists p1;

— 注意：在命令行中， 执行创建存储过程的SQL时，需要通过delimiter指定SQL语句的结束符。
create procedure p1()
BEGIN
select count(*) from student;

END$$

— 命令行中的结束符：$$