sql调优

数据库调优

数据库的组成

我们所谓的调优也就是在，执行器执行之前的分析器，优化器阶段完成的。

一般在开发涉及SQL的业务都会去本地环境跑一遍SQL，用explain去看一下执行计划，看看分析的结果是否符合自己的预期，用没用到相关的索引，然后再去线上环境跑一下看看执行时间（这里只有查询语句，修改语句也无法在线上执行）

排除缓存干扰

因为在MySQL8.0之前我们的数据库是存在缓存这样的情况的

我们在执行SQL的时候，记得加上SQL_No_Cache去跑SQL，这样跑出来的时间就是真实的查询时间了。

SELECT SQL_NO_CACHE …

问： 为什么缓存会失效，且是经常失效？

如果我们当前的MySQL版本支持缓存而且我们又开启了缓存，那每次请求的查询语句和结果都会以key-value的形式缓存在内存中的，一个请求会先去看缓存是否存在，不存在才会走解析器。

缓存失效比较频繁的原因就是，只要我们一对表进行更新，那这个表所有的缓存都会被清空，其实我们很少存在不更新的表，

大家如果是8.0以上的版本就不用担心这个问题，如果是8.0之下的版本，记得排除缓存的干扰。

Explain

用explain 去分析执行计划

**问：**explain你记得哪些字段，分别有什么含义？

Column	含义
id	查询序号
select_type	查询类型
table	表名
partitions	匹配的分区
type	join类型
possible_keys	可能会选择的索引
key	实际选择的索引
key_len	索引长度
ref	与索引作比较的列
rows	要检索的行数（估算值）
filtered	查询条件过滤的行数的百分比
extra	额外信息

**问：**统计这个统计的行数就是完全对的么？索引一定会走到最优索引么？

行数只是一个接近的数字，不是完全正确的，索引也不一定就是走最优的，是可能走错的。

那为什么会走错索引呢？

先索引扫描,再通过rowid去取索引中未能提供的数据,即为回表。

如果A索引要扫描100行，B索引只要20行，但是他可能选择A索引

一般走错是因为，优化器在选择的时候发现，走A索引没有额外的代价，比如走B索引并不能直接拿到我们的值，还需要回到主键索引才可以拿到，多了一次回表的过程，这个也是会被优化器考虑进去的。

他发现走A索引不需要回表，没有额外的开销，所有他选错了。

**问：**我的总行数大概有10W行，但是我去用explain去分析sql的时候，就会发现只得到了9.4W，为啥行数只是个近似值呢？

MySQL中数据的单位都是页，MySQL又采用采样统计的方法，采样统计的时候，innoDB默认会选择N个数据页，统计这些页面上的不同值，得到一个平均值，然后乘以这个索引的页面数，就得到了这个索引的基数。

如果上面的信息错了，可以用analyze table tablename 可以重新统计索引信息

还有一种办法是force index强制走正确的索引，或者优化SQL，最后实在不行就可以新建索引，或者删除错误的索引

覆盖索引

那怎么才能做到查询的时候不会回表呢？

覆盖索引

覆盖索引是select的数据列只用从索引中就能够取得，不必读取数据行，换句话说查询列要被所建的索引锁覆盖

覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段

联合索引

还是以商品表为例，我们需要根据他的名称，去查他的库存，假设这是一个很高频的查询请求，那怎么建立索引？

可以思考上面的回表的消耗对SQL进行优化。

建立一个名称和库存的联合索引，这样名称查出来的时候，就可以看到库存了，不需要查出id之后回表再去查询库存。

联合索引很常见，但是并不是可以一直建立的，还需要思考索引占据的空间。

最左侧匹配原则

在写SQL的时候，最好能利用到现有的SQL最大化利用，像上面的场景，如果我们要利用一个模糊查询 name like ”晨晨%“，这样还是能够利用到这个索引的，而且如果有这样的联合索引，也就没有必要去新建一个商品名称单独的索引了

很多时候，我们的索引可能没建对，那调整一下顺序，可能就可以优化到整个SQL

索引下推

select * from itemcenter where name like '陈%' and size=22 and age = 20;

所以这个语句在搜索索引树的时候，只能用“陈”，找到第一个满足条件的记录ID1，当然，这个总比全表扫描要好

然后

判断其他条件是否满足，比如age

MySQL 5.6之前，只能从ID1开始一个个回表，到主键索引上找出数据行，在对比字段值。

而MySQL 5.6 之后引入索引下推优化，可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数

唯一索引普通索引选择难题

当需要更新一个数据也时，如果数据也在内存中就直接更新，而如果这个数据页还没有在内存中的话，在不影响数据一致性的前提下，innoDB会将这些更新操作缓存在change buffer中，这样就不需要从磁盘中如入这个数据页了。

在下次查询需要访问这个数据页的时候，将数据页读入内存中，然后执行change buffer中这个页的有关的操作，通过这种方式就能保证这个数据逻辑的正确性。

注意：虽然名字叫做change buffer，实际上他是可以持久化的数据，也就是说，change buffer 在内存中有拷贝，也会被写入磁盘中。

将change buffer中操作应用到原数据页，得到最新结果的过程称为merge

除了访问这个数据页会触发merge以外，系统有后台线程会定期merge。在数据库正常关闭（shutdown）的过程中，也会执行merge操作

显然，如果能够将更新操作先记录在change buffer，减少读磁盘，语句的执行速度会明显的提升。而且，数据读入内存是需要占用 buffer pool（数据缓冲池）的，所以这种方式还能避免占用内存，提高内存的利用率

那么，什么条件下可以使用change buffer呢？

对于唯一索引来说，所有的更新操作都要先判断这个操作是否违反唯一性 约束。

要判断表中，是否存在这个数据，而这必须要将数据读入内存中才能够判断，如果都已经读入到内存中了，那直接更新内存会更快，就没必要使用chagne buffer了

因此唯一索引的更新就不能使用change buffer，实际上也只有普通索引可以使用

change buffer用的是buffer pool里的内存，一次不能无限增大，changebuffer的大小可以通过参数innodb_change_buffer_max_size来动态设置，这个参数设置为50的时候，表示change buffer 的大小最多只能占用buffer pool的50%

将数据从磁盘读入内存涉及随机IO的访问，是数据库里面成本最高的操作之一，change buffer因为减少了随机磁盘访问，所以对更新性能的提升是会很明显的。

change buffer的使用场景

因为merge的时候是真正的进行数据更新的时刻，而change buffer的主要目的就是将记录的变更动作缓存下来，所以一个数据页做merge之前，change buffer记录的变更越多（也就是这个页面上要更新额次数越多），收益就越大

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

反过来，假设一个业务的更新模型是写入之后马上回做查询，那么即使满足了条件，将更新先记录在change buffer，但之后由于马上要访问这个数据页，会立即触发merge过程。这样随机访问IO的次数不会减少，反而增加了change buffer 的维护代价，所以，对于这种业务模式来说，change buffer 反而起到了副作用

**总结：**不经常查询，但是经常修改的话，用普通索引，因为有change buffer 的存在

但是经常查询或者修改后立即查询的话，用唯一索引，这样省去了change buffer 的维护代价

前缀索引

我们存在邮箱作为用户名的情况，每个人的邮箱都是不一样的，那我们是不是可以在邮箱上建立索引，但是邮箱这么长，我们怎么去建立索引呢？

MySQL是支持前缀索引的，也就是说，你可以定义字符串的一部分作为索引。默认地，如果你创建索引的语句不指定前缀长度，那么索引就会包含整个字符串。

我们是否可以建立一个区分度很高的前缀索引，达到优化和节约空间的目的呢？

使用前缀索引，定义好长度，就可以做到既节省空间，又不用额外增加太多的查询成本。

上面说过覆盖索引了，覆盖索引是不需要回表的，但是前缀索引，即使你的联合索引已经包涵了相关信息，他还是会回表，因为他不确定你到底是不是一个完整的信息，就算你是czzz6161@163.com一个完整的邮箱去查询，他还是不知道你是否是完整的，所以他需要回表去判断一下。

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

比如邮箱的话，因为很多邮箱都是以www开头的基本没有任何区分度的，放在索引还浪费内存，可以用subString()函数截取掉前面的，然后简历索引。

身份证的haul都是以区域开头的，同区域的人很多，所以选择reverse()函数翻转一下，区分度就高了

条件字段函数操作

日常开发过程中，经常对很多字段进行函数操作，如果对日期字段操作，浮点字符操作等等，需要注意的是，如果对字段做了函数计算，就用不上索引了，这是MySQL的规定。

对索引字段做函数操作，可能会破坏索引值的有序性，因此优化器就决定放弃走树搜索功能。

需要注意的是，优化器并不是要放弃使用这个索引。

这个时候可以用一些取巧的方法，比如 select * from tradelog where id + 1 = 10000 就走不上索引，select * from tradelog where id = 9999就可以。

也就是说计算的过程不要放在在SQL语句中

隐式类型转换

select * from t where id = 1

如果id是字符类型的，1是数字类型的，你用explain会发现走了全表扫描，根本用不上索引，为啥呢？

因为MySQL底层会对你的比较进行转换，相当于加了 CAST( id AS signed int) 这样的一个函数，上面说过函数会导致走不上索引。

相当于进行了函数运算

隐式字符编码转换

还是一样的问题，如果两个表的字符集不一样，一个是utf8mb4，一个是utf8，因为utf8mb4是utf8的超集，所以一旦两个字符比较，就会转换为utf8mb4再比较。

转换的过程相当于加了CONVERT(id USING utf8mb4)函数，那又回到上面的问题了，用到函数就用不上索引了。

还有一会可能会遇到mysql突然卡顿的情况，那可能是MySQLflush了。

flush

redo log，也就是我们对数据库操作的日志，他是在内存中的，每次操作一旦写了redo log就会立马返回结果，但是这个redo log总会找个时间去更新到磁盘，这个操作就是flush。

在更新之前，当内存数据页跟磁盘数据页内容不一致的时候，我们称这个内存页为“脏页”。

内存数据写入到磁盘后，内存和磁盘上的数据页的内容就一致了，称为“干净页“。

那什么时候会flush呢？

1、innoDB的redo log 写满了，这个时候系统会停止所有更新操作，把checkpoint往前推进，redo log留出空间可以继续写

2、系统内存不足，当需要新的内存页，而内存不够用的时候，就要淘汰一些数据页，空出内存给别的数据页使用。如果淘汰的是“脏页”，就要现将脏页写到磁盘。

**问：**那难道不能够直接把内存淘汰掉，下次需要请求的时候，从磁盘读入数据页，然后拿redo log 出来应用不就行了？

这里其实是从性能考虑的，如果刷脏页就一定会写盘，就保证了每个数据页有两种状态：

• 一种是内存里存在，内存就肯定是正确的结果，直接返回；
• 另一种是内存里没有数据，就可以肯定数据文件上是正确的的结果，读入内存后返回。这样的效率更高。

3、MySQL认为系统“空闲”的时候，只要有机会就刷一点“脏页”。

4、MySQL正常关闭，这时候，MySQL会把内存的脏页都flush到磁盘上，这样下次MySQL启动的时候，就可以直接从磁盘上读数据，启动速度会很快。

问：那我们怎么做才能把握flush的时机呢？

Innodb刷脏页控制策略，我们每个电脑主机的io能力是不一样的，你要正确地告诉InnoDB所在主机的IO能力，这样InnoDB才能知道需要全力刷脏页的时候，可以刷多快。

这就要用到innodb_io_capacity这个参数了，它会告诉InnoDB你的磁盘能力，这个值建议设置成磁盘的IOPS，磁盘的IOPS可以通过fio这个工具来测试。

正确地设置innodb_io_capacity参数，可以有效的解决这个问题。

这中间有个有意思的点，刷脏页的时候，旁边如果也是脏页，会一起刷掉的，并且如果周围还有脏页，这个连带责任制会一直蔓延，这种情况其实在机械硬盘时代比较好，一次IO就解决了所有问题，

但是现在都是固态硬盘了，innodb_flush_neighbors=0这个参数可以不产生连带制，在MySQL 8.0中，innodb_flush_neighbors参数的默认值已经是0了。

参考：三太子敖丙https://mp.weixin.qq.com/s/e0CqJG2-PCDgKLjQfh02tw

解决这个问题。

这中间有个有意思的点，刷脏页的时候，旁边如果也是脏页，会一起刷掉的，并且如果周围还有脏页，这个连带责任制会一直蔓延，这种情况其实在机械硬盘时代比较好，一次IO就解决了所有问题，

[外链图片转存中…(img-vXoMnGdE-1598079280209)]

但是现在都是固态硬盘了，innodb_flush_neighbors=0这个参数可以不产生连带制，在MySQL 8.0中，innodb_flush_neighbors参数的默认值已经是0了。

参考：三太子敖丙https://mp.weixin.qq.com/s/e0CqJG2-PCDgKLjQfh02tw