常用sql优化方法，索引失效场景，explain执行计划

常用sql优化方法

1. 禁止使用select *(全表扫描效率非常慢且使用不到联合索引)，只查询出需要用到的字段
2. 尽量减少表关联
3. 合理使用索引
4. 联合索引遵循最左前缀法
5. 使用执行计划explain查看sql执行信息是否走索引
6. 避免回表（覆盖索引），select后面指定的字段尽量覆盖掉索引
7. 分页查询优化

   # id自增且和rowNum必须一一对应，如果id有间隔则查出来数据不准确（不推荐）
   SELECT t.id, t.name FROM book t WHERE id> 190000 ORDER BY id LIMIT 10;
   # id为自增时，使用子查询
   SELECT t.id, t.name FROM book t WHERE id >= (SELECT id FROM book ORDER BY id LIMIT 190000,1) ORDER BY id LIMIT 10;
   # id为uuid时，使用内连接
   SELECT t.id, t.name FROM book t,(SELECT id FROM book LIMIT 190000,10) b WHERE t.id = b.id 

8. MySQL支持两种方式的排序 filesort 和 index ，Using index是指MySQL 扫描索引本身完成排序 。index 效率高，filesort效率低
9. 尽量在 索引列 上完成排序，遵循 索引建立（索引创建的顺序） 时的最左前缀法则group by与order by很类似，其实质是先 排序后分组，遵照 索引创建顺序的最左前缀法则。对于group
10. by的优化如果不需要排序的可以加上 order by null禁止排序 。注意，where高于having，能写在where中 的限定条件就不要去having限定了。
11. 能用覆盖索引尽量用覆盖索引
12. 如果order by的条件不在索引列上，就会产生Using filesort
13. order by满足两种情况会使用Using index。

    1) order by语句使用 索引最左前列 。

    2) 使用where子句与order by子句 条件列组合满足索引最左前 列

explain执行计划

explain只是用来分析语句，返回信息而不是执行这条sql

explain extend:

        显示 part，filer列

show warnings:

       显示sql语句优化后的sql语句（有可能是伪sql语句）

执行计划字段含义：

id:

sql语句一共拆分几条查询，每一条查询一个id，id越高执行sql时越优先，id不一定唯一，相等时（关联语句）谁在前谁先执行

select_type:

• simple简单查询
• primary:复杂查询：即最外层的查询
• subquery:子查询：非from子句里的查询（select后面）
• derived:衍生查询： from子句里的查询（from后面）

 注：id为1的外层查询，查询的table表名id为3的衍生查询即derived3

type（访问类型）: 

1.  即mysql决定如何查找表中的行，查找数据行记录的大概范围
2. 速度从优到差：system > const > eq_ref > ref > range > index > all
3. 一般来说查询起码达到range，最好达到ref

• const：只有一条和查询常量一样
• system：const外层的查询即system，结果集本身就一条的场景，整张表就一条记录，这种查询即system
• eq_ref：主键关联或唯一键关联
• ref：查询条件用的不是唯一索引，用的是普通索引，可能查询出了多条数据，即联合索引使用最左前缀匹配到了部分条件，where匹配了组合索引中的最左前缀
• range：范围查找，< ，>等；（结果集太大的话效率也不高）
• index：扫描某个二级索引拿到结果。如果查询出的数据在主键索引和二级索引都有时，mysql会选择二级索引，因为二级索引范围小。（一般情况主键索引比二级索引快，因为二级索引找到地址后需要回表，但index这种场景主键索引和二级索引都有时，二级索引小，即选择二级索引）
• all：即全表扫描，扫描你的聚簇索引的所有叶子节点。通常情况下这需要增加索引来进行优化了。index 与ALL 其实都是读全表，区别在于index是遍历索引树读取，而ALL是从硬盘中读取。

possible_keys：

        mysql分析可能会走的索引

key_len:

        通过key_len的长度可以知道具体使用了联合索引的哪个字段

ref:

        表关联的字段也可能是常量

rows:

        扫描多少行数（只是预估行数）

extra：

• using index：可能使用了覆盖索引。覆盖索引：通过索引树就能查到数据，不需要回表（回表：通过索引拿到主键值，再去主键索引里找数据）
• using where：没有用到索引，这种情况需要优化。查询时未找到可用的索引，进而通过where条件过滤获取所需数据，但要注意的是并不是所有带where语句的查询都会显示Using where。
• using index condition：查询的列不完全被索引覆盖，where条件中是一个前导列的范围；如：explain select * from actor where name = 'a' ：  比如联合索引index(a，b，c)只走了a字段
• Using temporary：mysql需要创建一张临时表来处理查询。出现这种情况一般是要进行优化的，首先是想到用索引来优化。如： explain select distinct name from actor，name字段没有建索引时，就是Using temporary的情况
• Using filesort：将用外部排序而不是索引排序，数据较小时从内存排序，否则需要在磁盘完成排序。这种情况下一般也是要考虑使用索引来优化的
• Select tables optimized away：使用某些聚合函数（比如 max、min）来访问存在索引的某个字段 explain select distinct name from actor
  eplain select distinct name from actor;

EXPLAIN SELECT MIN(id) FROM book：

        mysql能够在 优化阶段分解查询语句 ，在执行阶段用不着再访问表或索引。例如：在索引列中选取最小值， 可以单独查找索引来完成，不需要在执行时访问表

   id  select_type  table   partitions  type    possible_keys  key     key_len  ref       rows  filtered  Extra                         
------  -----------  ------  ----------  ------  -------------  ------  -------  ------  ------  --------  ------------------------------
     1  SIMPLE       (NULL)  (NULL)      (NULL)  (NULL)         (NULL)  (NULL)   (NULL)  (NULL)    (NULL)  Select tables optimized away  

索引失效场景

1. 不对索引字段进行函数运算，如字符串函数，日期函数，or，类型转换等都会导致索引失效；（这些函数运算的结果在索引树里没有，所以不会走索引）select date1 from day where date(date1)='20220202' 这种语句怎么优化改进，可转换为范围查找有可能会使用索引：select date1 from day where date1>='20220202' and date1 <='20220202'
2. 这些字段会导致索引失效：+， -， *  ，/  ，！= ，<，>，is null ，is not null ，or，in等；in和or在表数据量比较大的情况会走索引，在表记录不多的情况下会选择全表扫描；id>1  and <2000看起来会走索引不一定会走索引，mysql内部机制可能认为范围太大没有走索引，解决方法：拆分范围，如：id>1000 and <2000
3. 索引字段放在查找范围的右边会导致范围字段右边的索引字段失效，如 select * from t_user where phone='123' and age >20 and province ='sh' （第二个字段是范围查找，第三个字段不一定是有序的，必须在左边相等的情况下，后面的字段才能比较，所以此种情况只会前两个字段走索引）；联合索引a，b，c，只有where a=1 and b=1 and c>1的这种情况三个字段都会走索引；以下这些情况不会走索引：where a>1 and b=1 and c=1（三个都不会走索引，因为第一个字段就是范围查找，mysql会认为这样还不如直接全表扫描），where a=1 and b>1 and c=1（前两个会走）
4. 模糊查询通配符写在左边会导致失效，如like “%123”；模糊查询通配符写在左边怎么优化，可以使用覆盖索引，查询字段必须是建立覆盖索引字段，EXPLAIN SELECT name,age,position FROM employees WHERE name like '%Lei%' ；'123%' 通配符在后面相当于等值查询，通配符在左边相当于范围查询；
5. oder by优化，使用索引字段来排序：select create_time form t_user order by create_time
6. 字符串不加单引号索引失效
7. force  index（字段）， 强制走索引扫描行数少了速度却不一定快，mysql内部的优化非常智能大多数时不需要动
8.  联合索引(a，b，c)，where a like '1%' and b=1 and c=1，a段都会走索引，like '1%'此时相当于查找常量；5.6之前的版本会先在二级索引里找到主键后，然后再回表去主键索引树里查找到数据，查找完了再过滤后面两个字段条件；5.6及之后的版本会在二级索引里每次找到一条数据，就去主键索引树里匹配后面两个字段条件，如果符合则找到id，这种方式即 索引下推；索引下推：mysql内部适用于数据比较小的情况，索引查找范围的情况不会走索引下推 
9. trace 成本分析

Using filesort文件排序

filesort文件排序方式

单路排序：是一次性取出满足条件行的所有字段，然后在sort buffer中进行排序；用trace工具可

以看到sort_mode信息里显示< sort_key, additional_fields >或者< sort_key,

packed_additional_fields >

双路排序（又叫 回表 排序模式）：是首先根据相应的条件取出相应的 排序字段 和 可以直接定位行

数据的行 ID ，然后在 sort buffer 中进行排序，排序完后需要再次取回其它需要的字段；用trace工具

可以看到sort_mode信息里显示< sort_key, rowid >