【面试系列】当面试官问你 MySQL 优化时该怎么应对？

小茵：要不你来讲讲你们对MySQL是怎么调优的？

小奥：哇，这命题很大阿…我认为，对于开发者而言，对MySQL的调优重点一般是在「开发规范」、[数据库索引]又或者说解决线上慢查询上。

小奥：而对于MySQL内部的[参数调优]，由专业的DBA来搞。

小茵：扯了这么多，你就是想表达你不会MySQL参数调优，对吧

小奥：草，被发现了。

小茵：那你来聊聊你们平时开发的规范和索引这块，平时是怎么样的吧。

小奥：嗯，首先，我们在生产环境下，创建[数据库表]，都是在工单系统下完成的（那就自然需要DBA审批）。如果在创建表时检测到没有创建索引，那就会直接提示warning（：

image.png

小奥：理论上来说，如果表有一定的数据量，那就应该要创建对应的索引。从数据库查询数据需要注意的地方还是蛮多的，其中很多都是平时积累来的。比如说：

小奥：1. 是否能使用[覆盖索引]，减少「回表」所消耗的时间。意味着，我们在select 的时候，一定要指明对应的列，而不是select *

小奥：2. 考虑是否组建[联合索引]，如果组建「联合索引」，尽量将区分度最高的放在最左边，并且需要考虑[最左匹配原则]

小奥：3.对索引进行函数操作或者表达式计算会导致索引失效

小奥：4.利用[子查询]优化超多分页场景。比如 limit offset , n 在MySQL是获取 offset + n的记录，再返回n条。而利用子查询则是查出n条，通过ID检索对应的记录出来，提高查询效率。

小茵：嗯…

小奥：5.通过[explain命令]来查看SQL的执行计划，看看自己写的SQL是否走了索引，走了什么索引。通过[show profile]来查看SQL对系统资源的损耗情况（不过一般还是比较少用到的）

小奥：6.在开启事务后，在事务内尽可能只操作数据库，并有意识地减少锁的持有时间（比如在事务内需要插入&&修改数据，那可以先插入后修改。因为修改是更新操作，会加行锁。如果先更新，那并发下可能会导致多个事务的请求等待[行锁]释放）

image.png

小茵：嗯，你提到了事务，之前也讲过了事务的隔离级别嘛，那你线上用的是什么隔离级别？

小奥：嗯，我们这边用的是Read Commit（读已提交），MySQL默认用的是Repeatable read（可重复读）。选用什么[隔离]级别，主要看应用场景嘛，因为隔离级别越低，事务并发性能越高。

小奥：（一般互联网公司都选择Read Commit作为主要的隔离级别）

小奥：像Repeatable read（可重复读）隔离级别，就有可能因为[间隙锁]导致的死锁问题。

小奥：但可能你已经知道，MySQL默认的隔离级别为Repeatable read。很大一部分原因是在最开始的时候，MySQL的binlog没有[row模式]，在read commit隔离级别下会存在「主从数据不一致」的问题

小奥：[binlog]记录了数据库表结构和表数据「变更」，比如update/delete/insert/truncate/create。在MySQL中，主从同步实际上就是应用了binlog来实现的（：

小奥：有了该历史原因，所以MySQL就将默认的隔离级别设置为Repeatable read

image.png

小茵：嗯，那我顺便想问下，你们遇到过类似的问题吗：即便走对了索引，[线上查询]还是慢。

小奥：嗯嗯，当然遇到过了

小茵：那你们是怎么做的？

小奥：如果走对了索引，但查询还是慢，那一般来说就是表的数据量实在是太大了。

小奥：首先，考虑能不能把「旧的数据」给”删掉”，对于我们公司而言，我们都会把数据同步到Hive，说明已经[离线存储]了一份了。

小奥：那如果「旧的数据」已经没有查询的业务了，那最简单的办法肯定是”删掉”部分数据咯。数据量降低了，那自然，检索速度就快了…

小茵：嗯，但一般不会删的

小奥：没错，只有极少部分业务可以删掉数据（：

小奥：随后，就考虑另一种情况，能不能在查询之前，直接走一层[缓存]（Redis）。

小奥：而走缓存的话，又要看业务能不能忍受读取的「非真正实时」的数据（毕竟Redis和MySQL的数据一致性需要保证），如果查询条件相对复杂且多变的话（涉及各种group by 和sum），那走缓存也不是一种好的办法，维护起来就不方便了…

小奥：再看看是不是有[字符串]检索的场景导致查询低效，如果是的话，可以考虑把表的数据导入至Elasticsearch类的搜索引擎，后续的线上查询就直接走Elasticsearch了。

小奥：MySQL->Elasticsearch需要有对应的[同步程序] (一般就是监听MySQL的binlog，解析binlog后导入到Elasticsearch)

小奥：如果还不是的话，那考虑要不要根据查询条件的维度，做相对应的[聚合表]，线上的请求就查询聚合表的数据，不走原表。

小奥：比如，用户下单后，有一份订单明细，而订单明细表的量级太大。但在产品侧(前台)透出的查询功能是以「天」[维度]来展示的，那就可以将每个用户的每天数据聚合起来，在聚合表就是一个用户一天只有一条汇总后的数据。

小奥：查询走聚合后的表，那速度肯定杠杠的（聚合后的表数据量肯定比原始表要少很多）

小奥：思路大致的就是「以空间换时间」，相同的数据换别的地方也存储一份，提高查询效率

image.png

小茵：那我还想问下，除了读之外，写性能同样有瓶颈，怎么办？

小奥：你说到这个，我就不困了。

小奥：如果在MySQL读写都有瓶颈，那首先看下目前MySQL的架构是怎么样的。

小奥：如果是单库的，那是不是可以考虑升级至主从架构，实现[读写分离] 。

小奥：简单理解就是：主库接收写请求，从库接收读请求。从库的数据由主库发送的binlog进而更新，实现主从数据一致（在一般场景下，主从的数据是通过异步来保证最终一致性的）

image.png

小茵：嗯…

小奥：如果在主从架构下，读写仍存在瓶颈，那就要考虑是否要[分库分表]了

小奥：至少在我前公司的架构下，业务是区分的。流量有流量数据库，广告有广告的数据库，商品有商品的数据库。所以，我这里讲的分库分表的含义是：在原来的某个库的某个表进而拆分。

小奥：比如，现在我有一张业务订单表，这张订单表在广告库中，假定这张业务订单表已经有1亿数据量了，现在我要分库分表

小奥：那就会将这张表的数据分至多个广告库以及多张表中（：

小奥：分库分表的最明显的好处就是把请求进行均摊（本来单个库单个表有一亿的数据，那假设我分开8个库，那每个库1200+W的数据量，每个库下分8张表，那每张表就150W的数据量）。

image.png

小茵：你们是以什么来作为[分库键]的？

小奥：按照我们这边的经验，一般来说是按照userId的（因为按照用户的维度查询比较多），如果要按照其他的维度进行查询，那还是参照上面的的思路（以空间换时间）。

小茵：那分库分表后的ID是怎么生成的？

小奥：这就涉及到[分布式ID]生成的方式了，思路有很多。有借助MySQL自增的，有借助Redis自增的，有基于[雪花算法]自增的。具体使用哪种方式，那就看公司的技术栈了，一般使用Redis和基于「雪花算法」实现用得比较多。

小奥：至于为什么强调[自增]（还是跟索引是有序有关，前面已经讲过了，你应该还记得）

image.png

小茵：嗯，那如果我要分库分表了，迁移的过程是怎么样的呢

小奥：我们一般采取[双写]的方式来进行迁移，大致步骤就是：

小奥：一、增量的消息各自往新表和旧表写一份

小奥：二、将旧表的数据迁移至新库

小奥：三、迟早新表的数据都会追得上旧表（在某个[节点]上数据是同步的）

小奥：四、校验新表和老表的数据是否正常（主要看能不能对得上）

小奥：五、开启[双读]（一部分流量走新表，一部分流量走老表），相当于灰度上线的过程

小奥：六、读流量全部切新表，停止老表的写入

小奥：七、提前准备回滚机制，临时切换失败能恢复正常业务以及有修数据的相关程序。

image.png

小茵：嗯…今天就到这吧

本文总结：

• 数据库表存在一定数据量，就需要有对应的索引
• 发现慢查询时，检查是否走对索引，是否能用更好的索引进行[优化]查询速度，查看使用索引的姿势有没有问题
• 当索引解决不了慢查询时，一般由于业务表的数据量太大导致，利用空间换时间的思想
• 当读写性能均遇到瓶颈时，先考虑能否升级数据库架构即可解决问题，若不能则需要考虑分库分表
• 分库分表虽然能解决掉读写瓶颈，但同时会带来各种问题，需要提前调研解决方案和踩坑

最后编辑于 ： 2022.07.05 09:51:50 © 著作权归作者所有,转载或内容合作请联系作者