【Mysql-SQL优化方法】

一、Explain执行计划

1. ID 列： 描述select查询的序列号，表示查询中执行select子句或操作表的顺序

   1. id相同：执行顺序由上至下

   2. id不同：如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行

2. select_type列 ：查询类型， 要是用于区别:普通查询、联合查询、子查询等的复杂查询

   1. Primary：主查询，复杂查询的最外层 

   2. SUBQUERY子查询 ： select、where子句中包含子查询 ；

      ex： select t1.*,(select t2.id from t2 where t2.id = 1 ) from t1

   3. DERIVED衍生查询：from 列表中包含的子查询为衍生查询，结果放在临时表

      ex： select t1.* from t1 ,(select t2.* from t2 where t2.id = 1 ) s2  where t1.id = s2.id

3. table 列：数据是哪张表的

4. Type 列： 重要指标; 结果值从最好到最坏依次是( 至少达到range级别，平均在ref )： 

   1. system >  只有一行记录

   2. const >    一次索引就能找到 （ 只匹配一行数据，所以很快）

   3. eq_ref >   主键或唯一索引，只有一条记录与之匹配   

      疑问：eq_ref 与 const的区别 （eq_ref是结果只有一条，const 一般是查询结果集只有一条，被优化为常量查询返回）

   4. ref >         非唯一索引，等值匹配（多条记录），也可能是前缀匹配，ex：  where col = 'abc' ;

   5. fulltext >   ref_or_null > index_merge > unique_subquery > index_subquery >

   6. range >     检索索引指定范围行， 一般是where语句中出现了between、<、>、in等的查询

   7. index >     全部扫描，只扫描索引列，优于全表扫描

   8. ALL            全表扫描

5. possible_keys 与Key：  可能使用的key，与  实际使用的索引

   查询中若使用了覆盖索引，则该索引和查询的select字段重叠 （覆盖索引：查询字段即为索引字段，主键索引与二级索引同时满足时，覆盖索引优先选择二级索引，因为相对主键索引比较小）

6. key_len  索引中使用的字节数，最大可能长度 （不同编码长度不同）；长度也可用于判断用到联合索引中的哪些字段

7. Ref     ： 索引的哪一列被使用了

8. Rows： 根据索引选用和统计信息，估算查询所需记录需要扫描的行数

9. Extra列： 

   1. 值为 using index condition 时，指使用了覆盖索引

   2. using temporary  ：用到临时表 （一般是没有命中索引时，又需要加入临时表再次匹配过滤）

   3. using file sort   :   结果集太大时，文件系统辅助排序（sortBuffer辅助优化）  参考本文：Using filesort文件排序原理

二、避免索引失效常见方法

1. 不要在索引字段上使用函数

2. 不要手动转换索引字段类型

3. != 或 <> 一般不会用到索引，可能导致全表扫描，因为结果集会很大

4. like 使用前缀匹配

5. 联合索引中间一个字段为范围查询时，第三个字段用不到索引

   EX：where a=3 and b >4 and c=5  ，索引只能用到a、b两字段，b条件无法终止右区间，所以中断了后面的条件

explain extended   select XXX from tablename where id=1;

show warning ;    -- 显示优化后语句

三、SQL索引优化

• 索引下推

1. 概念：5.6 及之后的版本，联合索引第一个字段匹配后，其他索引字段在二级索引中匹配过滤掉不符合条件的记录，比较 5.6之前的版本回表后再过滤效率要高。

   where like '前缀%'  可能会索引下推； 索引下推可根据 （key_len长度判断）

2. 有些情况  where  a > 5  and  b = 3  and c = 2; 第一字段通过范围查询可能不会走索引下推（Mysql会分析预估，也许结果集过大不会走索引下推） 

• Mysql如何选择合适的合适的索引

1. where a > 'a'  很大可能不走索引，比如 B+树 Root节点的关键点是这样的 [ a,  h , i, ...,y ] ，那么通过root节点就能够大致评估大部分的节点都是符合条件的，类似于全表扫描，所以不走索引

2. where a > 'z'  大可能会走索引， 比如  B+树 Root节点 = [ a,  h , i, ...,y ] ，那么下一层节点 只有最右边的一页数据是符合条件的，根据root节点就能过滤掉大部分数据，所以很容易判断走索引效率高

• trace 工具，打印sql执行过程分析

       打开方法：

       SET SESSION optimizer_trace="enabled=on", end_markers_in_json=on;

       select * from information_schema.OPTIMIZER_TRACE;

1. 第一步：sql 准备，格式化

2. 第二步：sql 优化，去除无意义的条件或语句，索引字段顺序优化

3. 扫描行预估：全表扫描、可能的索引（二级索引可能有回表的成本所以扫描行数不能确定成本）查询 各自的成本对比

• order by 与 group by优化

1. group by  如何用于去重场景，可以用distinct替代，group by 底层会order by效率较低

2. key idx(a, b, c )   where a=1 and b=2 order by c ;    # c 字段在索引中有序，会走索引

3. key idx(a, b, c )   where a=1 and c=2 order by b ;    # b 字段在a和c确定时，无序，所以排序无法走索引 extra：using filesort

• Using filesort文件排序原理：

1. 扫描聚簇索引，相比 using index 只扫描二级索引效率低些

2. 单路排序：查询出的整个结果集的所有字段，放入内存排序集合参与排序

   特点：占用内存大，排序完后不需要再回表查询

3. 双路排序：查询结果集中ID(确认排序后记录的唯一性) + 排序字段，放入内存排序；

   特点：占用内存相对小，排序完后需要回表查询数据

4. 注：文件排序方法可以设置，但一般mysql自己会做优化，所以一般不修改

   一般参与字段之和> 1024字节 (max_length_for_sort_data)时，使用双路，否则使用单路

5. trace 打印的结果中，sort_buffer（默认1M空间） = 0 表示内存排序 ，否则大数据量会用到磁盘参与排序（磁盘参与临时表的缓存）

• 分页查询优化

1. lastid过滤后 取top n ；   滚动查询法  EX ： where id > 1001 limit 10; 

   注：前提是自增连续有序字段；或者是下拉加载更多的业务场景，其实不是分页，只是按顺序分段加载

2. 先用覆盖索引做排序优化，再join 排序后的主键回表查询所需字段 

• 关联查询优化

1. inner join 返回两表完全匹配的行，查询时会根据小数据量表优先查询（默认小表为驱动表），然后一条一条等值查询另一张表

   1. 嵌套循环连接算法 （NLJ），inner join on 索引字段

   2. 基于块的嵌套循环连接（BNL） inner join on 非索引字段         （mysql优化：把小表加载到join_buffer（join_buffer_size默认256k））

      注：不走索引的嵌套查询只合适用 BNL算法，如果用 NLJ算法 ，每一条数据的匹配都要走磁盘查询

2. straight_join 指定哪张表作为驱动表（一般不使用）  EX： select * from t2 straight_join t1 on t1.a = t2.a;    或者使用 left join 、right join  指定执行顺序

3. 总结：

   1. 关联字段，尤其大表字段，要加索引

   2. 小表是指过滤后结果集小的表，而不是数据量小的表

• IN和EXISTS优化

1. select * from A where id in ( select id from B)   或   select * from A where EXISTS(select 1 from B where B.id = A.id)

   1. 对于 B 结果集小于A的情况 ，in 优于 EXISTS，等价于 for (B) {  query A with id }

   2. 对于 A 结果集小于B的情况，EXISTS优于in   ,   等价于 for (A) { query B with id }

2. 能用join替代就用join替代

• count(*) 查询优化：

1. 所有的相差不大，count(*)最优  ≈ count(1)最优 > count(二级索引字段) > count(主键) 在有二级索引时，count(主键)会被优化为count(二级索引)

2. count(*)  mysql 做了优化，不取值 ，按行累加效率很高

3. myisam存储引擎会维护表的总行数，用 select count(*) from table ; 效率最高

4. innodb 由于mvcc机制，count(*) 可能是不同的结果，所以不容易优化

5. 其他方案：总条数维护在 redis（不保证一致性） 或数据库的 单独表里（保证一致性）

四、索引的设计原则

1. 代码先行，索引根据具体业务场景和查询字段使用率创建 

2. 联合索引尽量覆盖条件：索引包含 where 、order by、group by的字段；联合索引首字段尽量选择最大区分度字段

3. 不在区分度不高的字段上建索引，EX：性别字段

4. 长字符串，可以使用前缀索引，EX：key idx(name(20), age),  注：前缀索引 ，就不能使用order by 了

5. where 与order by冲突时，优先where

6. 基于慢sql查询做优化

扩展： 字符存储用utf-8 ,存储表情的要用 utf8mb4

参考文章：

EXPLAIN 执行计划详解 - 程序员自我修养张振力 - 博客园

MySQL :: MySQL 5.7 Reference Manual :: 13.8.2 EXPLAIN Statement  Mysql官方手册