「数据库调优」

正文数据库调优其实一般情况都是我们的SQL调优SQL的调优就可以解决大部分问题了当然也不排除SQL执行环节的调优。我之前在索引和数据库基础环节有介绍过相关的基础知识这里就不过多的赘述了但是数据库的组成可能很多小伙伴都忘记了那我们再看一遍结构图吧。我们所谓的调优也就是在执行器执行之前的分析器优化器阶段完成的那我们开发工作中怎么去调优的呢帅丙一般在开发涉及SQL的业务都会去本地环境跑一遍SQL用explain去看一下执行计划看看分析的结果是否符合自己的预期用没用到相关的索引然后再去线上环境跑一下看看执行时间这里只有查询语句修改语句也无法在线上执行。遇SQL不决explain但是这里就要说到第一个坑了。排除缓存干扰因为在MySQL8.0之前我们的数据库是存在缓存这样的情况的我之前就被坑过因为存在缓存我发现我sql怎么执行都是很快当然第一次其实不快但是我没注意到以至于上线后因为缓存经常失效导致rtResponse time时高时低。后面就发现了是缓存的问题我们在执行SQL的时候记得加上SQL NoCache去跑SQL这样跑出来的时间就是真实的查询时间了。我说一下为什么缓存会失效而且是经常失效。如果我们当前的MySQL版本支持缓存而且我们又开启了缓存那每次请求的查询语句和结果都会以key-value的形式缓存在内存中的大家也看到我们的结构图了一个请求会先去看缓存是否存在不存在才会走解析器。缓存失效比较频繁的原因就是只要我们一对表进行更新那这个表所有的缓存都会被清空其实我们很少存在不更新的表特别是我之前的电商场景可能静态表可以用到缓存但是我们都走大数据离线分析缓存也就没用了。大家如果是8.0以上的版本就不用担心这个问题如果是8.0之下的版本记得排除缓存的干扰。Explain最开始提到了用执行计划去分析我想explain是大家SQL调优都会回答到的吧。因为这基本上是写SQL的必备操作那我现在问大家一个我去阿里面试被问过的一个问题explain你记得哪些字段分别有什么含义当时我就回答上来三个我默认大家都是有数据库基础的所以每个我这里不具体讨论每个字段怕大家忘记我贴一遍图大家自己回忆一下。那我再问大家一下你们认为统计这个统计的行数就是完全对的么索引一定会走到最优索引么当然我都这么问了你们肯定也知道结果了行数只是一个接近的数字不是完全正确的索引也不一定就是走最优的是可能走错的。我的总行数大概有10W行但是我去用explain去分析sql的时候就会发现只得到了9.4W为啥行数只是个近视值呢看过基础章节的小伙伴都知道MySQL中数据的单位都是页MySQL又采用了采样统计的方法采样统计的时候InnoDB默认会选择N个数据页统计这些页面上的不同值得到一个平均值然后乘以这个索引的页面数就得到了这个索引的基数。我们数据是一直在变的所以索引的统计信息也是会变的会根据一个阈值重新做统计。至于MySQL索引可能走错也很好理解如果走A索引要扫描100行B所有只要20行但是他可能选择走A索引你可能会想MySQL是不是有病啊其实不是的。一般走错都是因为优化器在选择的时候发现走A索引没有额外的代价比如走B索引并不能直接拿到我们的值还需要回到主键索引才可以拿到多了一次回表的过程这个也是会被优化器考虑进去的。他发现走A索引不需要回表没有额外的开销所有他选错了。如果是上面的统计信息错了那简单我们用analyze table tablename 就可以重新统计索引信息了所以在实践中如果你发现explain的结果预估的rows值跟实际情况差距比较大可以采用这个方法来处理。还有一个方法就是force index强制走正确的索引或者优化SQL最后实在不行可以新建索引或者删掉错误的索引。覆盖索引上面我提到了可能需要回表这样的操作那我们怎么能做到不回表呢在自己的索引上就查到自己想要的不要去主键索引查了。覆盖索引如果在我们建立的索引上就已经有我们需要的字段就不需要回表了在电商里面也是很常见的我们需要去商品表通过各种信息查询到商品idid一般都是主键可能sql类似这样体验AI代码助手代码解读复制代码select itemId from itemCenter where size between 1 and 6因为商品id itemId一般都是主键在size索引上肯定会有我们这个值这个时候就不需要回主键表去查询id信息了。由于覆盖索引可以减少树的搜索次数显著提升查询性能所以使用覆盖索引是一个常用的性能优化手段。联合索引还是商品表举例我们需要根据他的名称去查他的库存假设这是一个很高频的查询请求你会怎么建立索引呢大家可以思考上面的回表的消耗对SQL进行优化。是的建立一个名称和库存的联合索引这样名称查出来就可以看到库存了不需要查出id之后去回表再查询库存了联合索引在我们开发过程中也是常见的但是并不是可以一直建立的大家要思考索引占据的空间。刚才我举的例子其实有点生硬正常通过商品名称去查询库存的请求是不多的但是也不代表没有哈真来了难道我们去全表扫描最左匹配原则大家在写sql的时候最好能利用到现有的SQL最大化利用像上面的场景如果利用一个模糊查询 itemname like ’敖丙%‘这样还是能利用到这个索引的而且如果有这样的联合索引大家也没必要去新建一个商品名称单独的索引了。很多时候我们索引可能没建对那你调整一下顺序可能就可以优化到整个SQL了。索引下推你已经知道了前缀索引规则那我就说一个官方帮我们优化的东西索引下推。体验AI代码助手代码解读复制代码select * from itemcenter where name like 敖% and size22 and age 20;所以这个语句在搜索索引树的时候只能用 “敖”找到第一个满足条件的记录ID1当然这还不错总比全表扫描要好。然后呢当然是判断其他条件是否满足比如size。在MySQL 5.6之前只能从ID1开始一个个回表到主键索引上找出数据行再对比字段值。而MySQL 5.6 引入的索引下推优化index condition pushdown) 可以在索引遍历过程中对索引中包含的字段先做判断直接过滤掉不满足条件的记录减少回表次数。唯一索引普通索引选择难题这个在我的面试视频里面其实问了好几次了核心是需要回答到change buffer那change buffer又是个什么东西呢当需要更新一个数据页时如果数据页在内存中就直接更新而如果这个数据页还没有在内存中的话在不影响数据一致性的前提下InooDB会将这些更新操作缓存在change buffer中这样就不需要从磁盘中读入这个数据页了。在下次查询需要访问这个数据页的时候将数据页读入内存然后执行change buffer中与这个页有关的操作通过这种方式就能保证这个数据逻辑的正确性。需要说明的是虽然名字叫作change buffer实际上它是可以持久化的数据。也就是说change buffer在内存中有拷贝也会被写入到磁盘上。将change buffer中的操作应用到原数据页得到最新结果的过程称为merge。除了访问这个数据页会触发merge外系统有后台线程会定期merge。在数据库正常关闭shutdown的过程中也会执行merge操作。显然如果能够将更新操作先记录在change buffer减少读磁盘语句的执行速度会得到明显的提升。而且数据读入内存是需要占用buffer pool的所以这种方式还能够避免占用内存提高内存利用率那么什么条件下可以使用change buffer呢对于唯一索引来说所有的更新操作都要先判断这个操作是否违反唯一性约束。要判断表中是否存在这个数据而这必须要将数据页读入内存才能判断如果都已经读入到内存了那直接更新内存会更快就没必要使用change buffer了。因此唯一索引的更新就不能使用change buffer实际上也只有普通索引可以使用。change buffer用的是buffer pool里的内存因此不能无限增大change buffer的大小可以通过参数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反而起到了副作用。前缀索引我们存在邮箱作为用户名的情况每个人的邮箱都是不一样的那我们是不是可以在邮箱上建立索引但是邮箱这么长我们怎么去建立索引呢MySQL是支持前缀索引的也就是说你可以定义字符串的一部分作为索引。默认地如果你创建索引的语句不指定前缀长度那么索引就会包含整个字符串。我们是否可以建立一个区分度很高的前缀索引达到优化和节约空间的目的呢使用前缀索引定义好长度就可以做到既节省空间又不用额外增加太多的查询成本。上面说过覆盖索引了覆盖索引是不需要回表的但是前缀索引即使你的联合索引已经包涵了相关信息他还是会回表因为他不确定你到底是不是一个完整的信息就算你是www.aobingmogu.com一个完整的邮箱去查询他还是不知道你是否是完整的所以他需要回表去判断一下。下面这个也是我在阿里面试面试官问过我的很长的字段想做索引我们怎么去优化他呢因为存在一个磁盘占用的问题索引选取的越长占用的磁盘空间就越大相同的数据页能放下的索引值就越少搜索的效率也就会越低。我当时就回答了一个hash把字段hash为另外一个字段存起来每次校验hash就好了hash的索引也不大。我们都知道只要区分度过高都可以那我们可以采用倒序或者删减字符串这样的情况去建立我们自己的区分度不过大家需要注意的是调用函数也是一次开销哟这点当时没注意。就比如本来是www.aobingqq,com 其实前面的www.基本上是没任何区分度的所有人的邮箱都是这么开头的你一搜一大堆出来放在索引还浪费内存你可以substring()函数截取掉前面的然后建立索引。我们所有人的身份证都是区域开头的同区域的人很多那怎么做良好的区分呢REVERSE函数翻转一下区分度可能就高了。这些操作都用到了函数我就说一下函数的坑。条件字段函数操作日常开发过程中大家经常对很多字段进行函数操作如果对日期字段操作浮点字符操作等等大家需要注意的是如果对字段做了函数计算就用不上索引了这是MySQL的规定。对索引字段做函数操作可能会破坏索引值的有序性因此优化器就决定放弃走树搜索功能。需要注意的是优化器并不是要放弃使用这个索引。这个时候大家可以用一些取巧的方法比如 select * from tradelog where id 1 10000 就走不上索引select * from tradelog where id 9999就可以。隐式类型转换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了。flushredo log大家都知道也就是我们对数据库操作的日志他是在内存中的每次操作一旦写了redo log就会立马返回结果但是这个redo log总会找个时间去更新到磁盘这个操作就是flush。在更新之前当内存数据页跟磁盘数据页内容不一致的时候我们称这个内存页为“脏页”。内存数据写入到磁盘后内存和磁盘上的数据页的内容就一致了称为“干净页“。那什么时候会flush呢InnoDB的redo log写满了这时候系统会停止所有更新操作把checkpoint往前推进redo log留出空间可以继续写。系统内存不足当需要新的内存页而内存不够用的时候就要淘汰一些数据页空出内存给别的数据页使用。如果淘汰的是“脏页”就要先将脏页写到磁盘。你一定会说这时候难道不能直接把内存淘汰掉下次需要请求的时候从磁盘读入数据页然后拿redo log出来应用不就行了这里其实是从性能考虑的如果刷脏页一定会写盘就保证了每个数据页有两种状态一种是内存里存在内存里就肯定是正确的结果直接返回另一种是内存里没有数据就可以肯定数据文件上是正确的结果读入内存后返回。 这样的效率最高。MySQL认为系统“空闲”的时候只要有机会就刷一点“脏页”。MySQL正常关闭这时候MySQL会把内存的脏页都flush到磁盘上这样下次MySQL启动的时候就可以直接从磁盘上读数据启动速度会很快。那我们怎么做才能把握flush的时机呢Innodb刷脏页控制策略我们每个电脑主机的io能力是不一样的你要正确地告诉InnoDB所在主机的IO能力这样InnoDB才能知道需要全力刷脏页的时候可以刷多快。这就要用到innodb_io_capacity这个参数了它会告诉InnoDB你的磁盘能力这个值建议设置成磁盘的IOPS磁盘的IOPS可以通过fio这个工具来测试。正确地设置innodb_io_capacity参数可以有效的解决这个问题。这中间有个有意思的点刷脏页的时候旁边如果也是脏页会一起刷掉的并且如果周围还有脏页这个连带责任制会一直蔓延这种情况其实在机械硬盘时代比较好一次IO就解决了所有问题但是现在都是固态硬盘了innodb_flush_neighbors0这个参数可以不产生连带制在MySQL 8.0中innodb_flush_neighbors参数的默认值已经是0了。资料参考《MySQL实战》、《高性能MySQL》、《丁奇MySQL47讲》总结在本文中我提到了以下知识点应该还不算全行锁、表锁、间隙锁、同步场景等等都没怎么提到因为他们的场景比较复杂每种都可以单独开一篇了丁奇的MySQL里面算是很全了还有就是高性能MySQL大家可以展开看看要是懒也可以等我总结。每个点我也没多仔细的讲解主要是篇幅原因其实每个点在MySQL相关书籍都是很多篇幅才介绍完的我就做个总结对具体的概念不了解可以用搜索引擎查询相关概念不过我想我说得还算通俗易懂。