Mysql数据库索引优化

    在讲Mysql数据库优化前先讲讲Mysql是怎样执行我们写的sql。

一、Mysql分层

连接层：提供与客户端连接的服务(只是和客户端建立连接)

服务层：第一是提供各种用户使用的接口(select,insert...)。第二是提供SQI优化器(Mysql会自动等价优化我们写的sql)

引擎层：提供了各种存储数据的方式(InnoDB MyISAM等)

InnoDB(默认):事务优先 (适合高并发操作；行锁)每行数据都加个锁。支持外键

MyISAM:性能优先（表锁）

存储层：存储数据

二、SQL解析过程

SQL编写过程：select ....from ...join ...on ...where ...group by ....having ....order by ...limit....

SQL解析过程：from ....on ...join ...where ... group by ...having ...select ...order by ...limit....

三、SQL性能问题(优化索引)

分析SQL执行计划：explain+SQL。可以模拟SQL优化器执行sql，从而知道自己写的sql性能高低。

Mysql查询优化会干扰我们的优化

explain+SQL 参数讲解：

1，id：编号

这是一个三表查询例子：

结论：当id值相同时，由上到下顺序执行，先查询t、tc、c(笛卡尔积，表数据小的先执行，前提编号id相同)

这是一个子查询例子：

结论：id值不同，id值越大越优先执行(本质：在嵌套子查询时，先查内层，再查外层)

这是多表+子查询的例子：

结论：id有相同又有不同时，大的先执行，相同的由上往下顺序执行

2，select_type:查询类型

PRIMARY：包含子查询SQL中的主查询(最外层)

SUBQUERY：包含子查询SQL中的子查询(非最外层)

SIMPLE：简单查询(不包含子查询、union)

DERIVED:衍生查询(使用到了临时表)

UNION：在from子查询中有table1 union table2  table1就是DERIVED，table2  就是UNION

UNION RESULT：告知开发人员哪些表时UNION

3，table：所查询的表名

4，type：类型(该条SQL的查询性能)，常见的有system>const>eq_ref>ref>range>index>all 

4.1，system：只有一条数据的衍生表(基本达不到，忽略)

4.2，const：仅仅能查到一条数据的sql，用于Primary key 或 unique索引(基本达不到)，与索引类型有关

4.3，eq_ref：唯一性索引(基本达不到)：对于每个索引健的查询，返回匹配唯一行数据(有且只有一个，不能多，不能为0)

例如：select ... from student  where name='JAY'；查询出来的数据可以有很多条，但是名字叫JAY的只能有一条

4.4，ref：非唯一性索引：对于每个索引健的查询，返回所有行(0，多)

4.5，range：检索指定范围的行，where后面是一个范围chaxun(between  in  <>)

4.6，index：查询全部索引中数据

4.7，all：查询全部表数据

5，possible_keys：可能用到的索引

6，key：真正用到的索引

7，key_len：实际所用索引长度(作用：用于判断复合索引是否被完全使用)

例如：创建了一个3个字段的复合索引(a,b,c)，三个字段都是char(20)类型(一个字符等于3个字节)，就可以根据key_len来判断用了几个字段的索引了。(字段允许为空的话 会额外用一个字节标识)

a char(20) ：是固定长度类型。一个字符等于3个字节，如果a可以为null，额外用一个字节标识

a varchar(20)：可变长度类型。一个字符等于3个字节，如果a可以为null，额外用一个字节标识，额外用两个字节标识可变类型

8，ref：表之间的引用。作用：指明当前表 所参照的字段

例如：select ..... from ....where a.c = b.x (b.x可以是常量，如果是常量则值为const)

9，rows：通过索引查到的行数

10，Extra：额外字段

10.1, using filesort：性能消耗大，需要一次额外的排序(查询)，需要优化    常见于order by中 

排序前提：先查询

先讲一个单值索引例子：我们创建一个表，有3个单值索引：

create table test02
(
a1 char(3),
a2 char(3),
a3 char(3),
index  idx_a1(a1),
index  idx_a2(a2),
index  idx_a3(a3),
)

explain select  * from test02 where a1 ='' order by a1; 

explain select  * from test02 where a1 ='' order by a2;  --------using filesort

小结：对于单个索引，查询和排序不是同一个字段(要额外再拍一次序)，则会出现using filesort

             避免：where哪些字段就order by哪些字段

复合索引例子：
drop index idx_a1(a1) on test02;
drop index idx_a1(a2) on test02;
drop index idx_a1(a3) on test02;
alter table test02 add index idx_a1_a2_a3(a1,a2,a3);

explain select  * from test02 where a1 =' ' order by a2; 

explain select  * from test02 where a1 =' ' order by a3;  --------using filesort

explain select  * from test02 where a2 =' ' order by a3;  --------using filesort

（1）explain select a1 from test02 where a2 =' ' order by a3; --------using filesort 

（2）explain select  a2 from test02 where a1 =' ' order by a3;

（1）为什么会出现using filesort 呢？前面我们讲到SQL解析过程为：

   from ....on ...join ...where ... group by ...having ...select ...order by ...limit....，所以上述SQL应改为（2）

小结：复合索引：不能跨列(最佳左前缀原则)

            避免：where 和 order by 按照复合索引顺序使用，不要跨列或无序使用。也要注意SQL解析顺序

           将含in的范围查询，放到where条件的最后(因为范围查询常常导致索引失效，以免影响后面的索引)

10.2，using temporary:性能消耗大，用到了临时表。常见于 group by

explain select  * from test02 where a1 in ('1' , '2', '3')  order by a1;

explain select  * from test02 where a1 in ('1' , '2', '3')  order by a2; --------using temporary

避免：查询哪些列，就用哪些列group by

10.3，using index：性能提升，覆盖索引。原因：只需要查询索引不需要回表查询

例：age、name是复合索引: (age,name)

select  age，name from test where age='' and name='';

覆盖索引：一个索引覆盖了(包含了)满足查询(select)语句中字段与查询条件(where)中所涉及的字段，可以只查索引，不回表查询

10.4，using where：需要回原表查询

10.5，impossible where：where字句永远为false

10.6，using join buffer:Mysql引擎使用了连接缓存(编写的sql太差，启动缓存)

四、索引优化

4.1 单表优化

主要就是上面讲到的：

复合索引：不能跨列(最佳左前缀原则)

将含in的范围查询，放到where条件的最后(因为范围查询常常导致索引失效，以免影响后面的索引)，假如复合索引(a,b,c),最佳左前缀原则，如果a索引失效了，则b、c都失效

4.2 多表优化

select  *  from teacher2 t left join course2 c on t.cid=c.cid where c.name = 'java';

小表驱动大表： t.cid=c.cid   t.表有10条数据，放左边、c表有300条数据，放右边

索引：如果是左连接，索引一般加到右边。右连接索引一般加到左边

索引建立在经常查询的字段

这里顺便附上join常用写法图：

很多索引优化的理解都可以参考上篇文章讲的Mysql索引底层的数据结构来理解。

五、索引使用场景

5.1，适合建立索引

主键自动建立唯一索引
频繁作为查询条件的字段应该创建索引
查询中与其它表关联的字段，外键关系建立索引
频繁更新的字段不适合建立索引(因为每次更新不单单是更新了记录还会更新索引)
where条件用不到的字段不创建索引
单键/组合索引 倾向选择组合索引
查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度(order by name,age,height时，建立索引(name,age,height))
查询中统计或者分组字段(group by,分组前提必排序)

5.2，不适合建立索引

表记录太少(300万条数据后，效率下降明显)
经常增删改的表
如果某个数据列包含许多重复且平均分配的内容，为它建立索引就没有太大实际效果(选择性:如果一个表有2000条记录，表索引列有1980个不同的值，那么这个索引的选择性为1980/2000=0.99，越接近1，索引效率越高)

六、常见索引失效场景

6.1，复合索引
不要跨列或无序使用(最佳左前缀)
尽量使用权所有匹配
不能使用(!= <>)或is null is not null,否则自身和右侧索引全部失效
6.2，不要在索引上进行任何操作
SQL优化是概率层面的优化(服务层有SQL优化器，会影响我们的优化)，需要用explain去验证Mysql底层是否使用了我们的优化
尽量使用覆盖索引(100%生效)
6.3，like尽量以"常量"开头，不要以%开头，否则索引失效
select * from teacher where name like %aa%  name索引失效
select name from teacher where name like %aa%  如果非要用%开头，使用覆盖索引挽救
6.4，尽量不要使用类型转换，否则索引失效
tname 为字符串类型
select * from teacher where tname = 123//底层将123 ->'123'
6.5，尽量不要使用or，否则索引失效

七，常见优化方法

(1)exist和in
如果主查询的数据集大，则使用in。如果子查询数据大，则使用exist。
exist语法：将主查询的结果，放到子查询结果进行校验，如果符合校验，则保留数据
select tname from teacher where exist (select * from teacher)
(2)order by 优化
using filesort有两种算法：双路排序和单路排序(根据IO的次数)
Mysql4.1之前默认使用双路排序(扫描两次磁盘)
Mysql4.1之前默认使用单路排序(只读取一次全部字段，在buffer中排序，如果buffer容量太小会自动切换到双路排序)
优化：
选择使用单路、双路；调整buffer的容量大小
避免使用select *....
复合索引 不要跨列使用，避免using filesort
保证全部的排序字段，排序的一次性(都是升序或降序)

八，SQL排查

慢查询日志：Mysql提供的一种日志记录，用于记录Mysql中响应时间超过阈值(默认10S)的SQL语句(long_query_time)。慢查询日志默认是关闭的。建议：开发调优时开启，上现网环境关闭

8.1,检查是否开启了慢查询日志：show variables like '%slow_query_log'; 默认关闭

8.2,开启慢查询日志

临时开启慢查询日志：set global slow_query_log =1;

永久开启慢查询日志：etc/my.cnf 中追加配置:

8.3，慢查询阈值

查看慢查询阈值：show variables like '%long_query_time';

设置慢查询阈值：set global long_query_time=3;

执行语句：select sleep(4);该SQL超过我们设置的慢查询阈值3S。

查询超过阈值的SQL：show global status like '%slow_queries%';

具体可以到日志文件中查看。或者可以通过mysqldumpslow(mysql自带)工具查看，具体参数命令查看mysqldumpslow --help