一篇文章教会你sql调优，简单有效可落地

数据库操作很容易变成接口的性能怪物，这篇文章也不讲非常深奥有难度的实操，都是一些非常简单可实现的思路和方法，我也是通过这些思路将2s的慢sql优化到了2ms

先上工具 —— Explain

Explain：打印一个sql语句的执行计划
字段	含义
id	查询序号，表示查询中执行select子句或操作表的顺序 id相同，执行顺序从上往下 id不同，id越大，优先级越高，越先执行
select_type	查询类型 SIMPLE：简单select，不适用union或子查询等 PRIMARY：子查询中最外层查询，查询中若包含任何复杂的字部分，最外层的select被标记为PRIMARY UNION：如果第二个select出现在UNION之后，则被标记为UNION，如果union包含在from子句的子查询中，外层select被标记为derived union result：UNION的结果 subquery：在select或where列表中包含了子查询 derived：在from列表中包含的子查询被标记为derived（衍生的），mysql会递归执行这些子查询，把结果放到临时表中
table	输出的行所引用的表
type	显式查询使用了何种类型的索引 system： 表中仅有一行（=系统表） const： 表示通过一次就找到（通过主键查找） eq_ref： 唯一索引扫描，对于每个索引键，表中只有一条记录与之匹配 ref： 非唯一性索引扫描，返回匹配某个单独值的所有行，本质上也是一种索引访问，它返回所有匹配某个单独值的行 range： 索引范围扫描。 只检索到给定范围的行，使用一个索引来选择行，key列显示使用了哪个索引，一般就是where语句中出现了betwwen，in等范围的查询。这种范围扫描索引扫描比全表扫描要好，因为它开始于索引的某一个点，而结束于另一个点，不用全表扫描 index： 全索引扫描 all： 全表扫描 访问效率：const > eq_ref>ref>range>index>all
possiable_keys	可能用到的索引，指出mysql能使用哪个索引在该表中找到行
key	该次查最终际使用的索引。如果没有选择索引则是NULL。
key_len	索引长度（字节数），长度越短越好，若是NULL，则长度为NULL。该字段显示为索引字段的最大可能长度，并非实际使用长度
ref	显示索引的哪一列被使用了
rows	扫描行数： mysql中数据的单位是页，mysql采用了采样统计的方法，采样统计的时候，InnoDB默认会选择N个数据页，统计这些页面上的不同值，得到一个平均值，然后乘以这个索引的页面数，就得到了这个索引的基数，因为我的数据是一直在变的，所以索引的统计信息也是会变的，会根据一个阈值，重新做统计。
extra	额外信息 Impossible WHERE： where子句的值总是false，不能用来获取任何元组，Mysql优化器通过分析不可能存在结果 Using index ： 使用了覆盖索引， 所需要的数据只需要在Index即可全部获得而不需要再到表中取数据、 Using index for group-by： 当Query中使用了GROUP BY或者DISTINCT子句的时候，如果分组字段也在索引中，Extra中出现该信息 Using filesort： Query中包含ORDER BY，且无法利用索引完成排序操作的时候，MySQL Query Optimizer不得不选择相应的排序算法来实现。（并不一定代表磁盘排序） Using temporary： 使用临时表时保存中间结果，主要常见于GROUP BY 和 ORDER BY等操作中 Using where ：不是读取表的所有数据，或者不是仅仅通过索引就可以获取所有需要的数据，则会出现Using where信息 using join buffer：使用了连接缓存 distinct：优化distinct操作，在找到第一匹配的元组后即停止同样的动作

调优思路

1. 排查缓存的干扰（8.0之前是有缓存的），执行sql的时候加上no sql 得到真实的查询时间
2. explian 熟练使用工具找到慢的原因：

1. 1. 如果发现rows统计信息有差别(原因上文有提到)，使用analyze table tablename 重新统计索引信息
   2. 若发现走错索引，一般是因为优化器在选择的时候发现，走A索引没有额外的代价，可能B索引还要回表什么的
   3. force index 强制走正确的索引
   4. 优化sql
   5. 新建索引，删掉错误的索引

1. 覆盖索引，避免回表
2. 联合索引：现有的索引最大利用化

1. 1. 最左匹配原则：可能索引没建对，但是调整一下顺序，就可以优化到整个sql了
   2. 索引下推：在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数

1. 唯一索引，普通索引 如何选择

可落地的-优化建议

• sql语句不要太复杂，不要嵌套太多层
• 使用【临时表】缓存结果，避免程序中多次扫描主表，减少阻塞，提高并发性能
• 使用like时注意要符合最左匹配
• 避免使用 != 或<>操作符
• 尽量避免使用 or来连接条件，会导致全表扫描
• 在where子句中避免使用in 和not in，会进行全表扫描；使用exsts和not exists代替
• 可以考虑使用强制走索引：force index
• 避免使用表达式，函数等操作作为查询条件
• 避免使用大事务，提高系统并发力
• 不要使用select * ，用具体的字段代替*，不要返回用不到的任何字段
• 索引不是越多越好，可以提高相应select的效率，但同时也降低了insert和update的效率
• 区分度，避免内部优化

如何查看慢sql

1. 查看是否开启慢sql ：show variables like 'log_slow_queries';
2. 开启慢查询命令：set global log_slow_queries = on;
3. 查看慢查询参数，即设置超过多少秒的查询归为了慢查询。参数为：long_query_time，查询命令： show global variables like 'long_query_time'; mysql默认时间为10秒，即10秒及以上的查询被归为了慢查询
4. 查看慢查询存放日志，命令： show variables like 'slow_query_log_file';
5. 去相应的目录下查看日志即可