大家好，我是被白菜拱的猪。

一个热爱学习废寝忘食头悬梁锥刺股，痴迷于girl的潇洒从容淡然coding handsome boy。

文章目录

• • • • SQL调优你了解吗？
      • • 查询缓存
        • Explain
        • 覆盖索引
        • 唯一索引普通索引
        • 前缀索引
        • 条件字段函数操作
        • 总结

SQL调优你了解吗？

查询缓存

假如数据库在8.0一下的话，我会先看看有没有开启缓存，针对非静态表，我会选择把缓存关掉。

因为查询缓存的失效非常频繁，只要有对一个表的更新，这个表上所有的查询缓存都会被清空。因此很可能你费劲地把结果存起来，还没使用呢，就被一个更新全清空了。对于更新压力大的数据库来说，查询缓存的命中率会非常低。除非你的业务就是有一张静态表，很长时间才会更新一次。比如，一个系统配置表，那这张表上的查询才适合使用查询缓存。

MySQL 8.0就把查询缓存这块部分删除了。

Explain

解决了查询缓存问题之后，我会使用 explain 来分析 SQL 语句，分析走了哪些索引，表的读取顺序，以及每张表有多少行被优化器查询。

explain 有哪些字段呢？

1. id： select 查询的序列号，id 值越大优先级越高，越先被执行。
2. select_type:查询的类型，看看查询是什么类型，区别普通查询、联合查询、子查询等。SIMPLE、PRIMARY、SUBQUERY
3. table：显示这一行的数据是关于哪张表的。
4. type：显示查询使用了何种类型
   1. system、const、eq_ref、ref、range、index、all
5. possible_keys:显示可能应用在这张表中的索引，一个或多个。查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用。
6. key：实际使用的索引，如果为 NULL，则没有使用索引，如果使用了覆盖索引，则该索引仅出现在 key 列表中
7. key_len:索引使用的字节数，使用的的索引的长度，在不损失精度性的情况下，长度越短越好。
8. ref：显示索引被哪一个列使用了
9. rows：根据表统计的信息及索引选用情况，大致估算查询所需要读取的行数
10. Extra：一些其他重要信息，using filesort、using temporary、using index、using where

explain分析之后，我们去分析跟我们预期值是否相似，有没有走索引，以及行数准不准确

行数是采用采样统计的方法，默认选择 n 个数据页。即便行数少，也会出现不会走索引的情况，因为有回表的现象，优化器认为回表产生的代价还不如直接全表扫描。

假如我们 explain rows 跟实际情况差距比较大，可以使用 analyze table 重新统计一下索引信息。或者 force index 让他强制走索引。

覆盖索引

上面讲到了回表，于是乎在 SQL 优化这块，我会尽量避免回表，于是会建一些覆盖索引，在 select 的时候尽量使用覆盖索引，覆盖索引可以减少树的搜索次数，显著提升查询性能。

为了能够更好的使用覆盖索引，我会创建一些联合索引，这个跟覆盖索引一样都会减少回表的次数。

唯一索引普通索引

change buffer 减少了随机磁盘访问，change buffer的主要目的就是将记录的变更动作缓存下来，所以在一个数据页做merge之前，change buffer记录的变更越多（也就是这个页面上要更新的次数越多），收益就越大。

因此，对于写多读少的业务来说，页面在写完以后马上被访问到的概率比较小，此时change buffer的使用效果最好，这种业务模型常见的就是账单类、日志类的系统。

使用于写多读少的业务。如果是读多写少的话，先更新在 change buffer 中，但之后由于马上访问这个数据页，立即出发 merge，这个随机访问 IO 的次数不会减少，反而增加了 change buffer 的维护代价。

前缀索引

邮箱，定义字符串的一部分作为索引。使用前缀索引，定义好长度，就可以做到既节省空间，又不用额外增加太多的查询成本。

很长的字段，想做索引我们怎么去优化他呢？

因为存在一个磁盘占用的问题，索引选取的越长，占用的磁盘空间就越大，相同的数据页能放下的索引值就越少，搜索的效率也就会越低。那么我们的做法就是减少索引的长度。

1. 使用hash，把字段hash为另外一个字段存起来，每次校验hash。
2. 比如把区分度不高的字段截取掉，比如邮箱www.或者身份证区域开头的，同区域的人很多，那么就可以reverse()翻转一下。

条件字段函数操作

如果对字段做了函数计算，不会走索引。

1. 隐式类型转换，where id = 1，相当于加了CAST()这样的函数
2. 隐式字符编码转换，两个表字符集不同，在转化的过程中相当于加了 CONVERT()函数，就用不上索引了

总结

先看有没有关缓存，然后预先 explain 一下，看看参数跟预想的一不一样，看看行数对不对，因为是采样统计，假如不对，那就 analyze table t 矫正一下。看走不走索引，索引不一定走索引，因为有回表的消耗，假如想强制走索引，那就 force index。假如有回表的情况，那就看能不能使用覆盖索引，然后联合索引的情况下，看看有没有符合最左前缀原则，不符合的话就改改 SQL 语句的顺序，5.6 之后索引下推，减少回表次数。

假如要给字符串加索引的话，减少字符串的长度就使用前缀索引指定长度，或者使用hash、或者截取，倒叙存储。然后针对索引不建议使用函数，一般使用函数会导致走不上索引，字符集不同也是一样。