对线面试官：MySQL 调优
The following article is from 对线面试官 Author Java3y
连载「讲人话」Java面试系列
面试官：要不你来讲讲你们对 MySQL 是怎么调优的？
候选者：哇，这命题很大阿……我认为，对于开发者而言，对 MySQL 的调优重点一般是在「开发规范」、「数据库索引」又或者说解决线上慢查询上。而对于 MySQL 内部的参数调优，由专业的 DBA 来搞。
面试官：扯了这么多，你就是想表达你不会 MySQL 参数调优，对吧？
候选者：额，被发现了。
面试官：那你来聊聊你们平时开发的规范和索引这块，平时是怎么样的吧？
候选者：嗯。首先，我们在生产环境下，创建数据库表，都是在工单系统下完成的（那就自然需要 DBA 审批）。如果在创建表时检测到没有创建索引，那就会直接提示warning（：
候选者：理论上来说，如果表有一定的数据量，那就应该要创建对应的索引。从数据库查询数据需要注意的地方还是蛮多的，其中很多都是平时积累来的。比如说：
是否能使用「覆盖索引」，减少「回表」所消耗的时间。意味着，我们在 select 的时候，一定要指明对应的列，而不是 select *；
考虑是否组建「联合索引」。如果组建「联合索引」，尽量将区分度最高的放在最左边，并且需要考虑「最左匹配原则」；
对索引进行函数操作或者表达式计算会导致索引失效；
利用子查询优化超多分页场景。比如 limit offset , n 在 MySQL 是获取 offset + n 的记录，再返回 n 条。而利用子查询则是查出 n 条，通过 ID 检索对应的记录出来，提高查询效率；
通过 explain 命令来查看 SQL 的执行计划，看看自己写的 SQL 是否走了索引，走了什么索引。通过 show profile 来查看 SQL 对系统资源的损耗情况（不过一般还是比较少用到的）；
在开启事务后，在事务内尽可能只操作数据库，并有意识地减少锁的持有时间。比如在事务内需要插入并修改数据，那可以先插入后修改。因为修改是更新操作，会加行锁。如果先更新，那并发下可能会导致多个事务的请求等待行锁释放。
面试官：嗯，你提到了事务。之前也讲过了事务的隔离级别嘛，那你线上用的是什么隔离级别？
候选者：嗯，我们这边用的是 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。
面试官：嗯，那我顺便想问下，你们遇到过类似的问题吗：即便走对了索引，线上查询还是慢。
候选者：嗯嗯，当然遇到过了。
面试官：那你们是怎么做的？
候选者：如果走对了索引，但查询还是慢，那一般来说就是表的数据量实在是太大了。
首先，考虑能不能把「旧的数据」给”删掉”。对于我们公司而言，我们都会把数据同步到 Hive，说明已经离线存储了一份了。
那如果「旧的数据」已经没有查询的业务了，那最简单的办法肯定是”删掉”部分数据咯。数据量降低了，那自然，检索速度就快了……
面试官：嗯，但一般不会删的。
没错，只有极少部分业务可以删掉数据（：
随后，就考虑另一种情况，能不能在查询之前，直接走一层缓存（Redis）。
而走缓存的话，又要看业务能不能忍受读取的「非真正实时」的数据（毕竟 Redis 和 MySQL 的数据一致性需要保证）。如果查询条件相对复杂且多变的话（涉及各种group by 和 sum），那走缓存也不是一种好的办法，维护起来就不方便了……
再看看是不是有「字符串」检索的场景导致查询低效，如果是的话，可以考虑把表的数据导入至 Elasticsearch 类的搜索引擎，后续的线上查询就直接走 Elasticsearch 了。
MySQL->Elasticsearch 需要有对应的同步程序（一般就是监听 MySQL 的 binlog，解析 binlog 后导入到 Elasticsearch)。
如果还不是的话，那考虑要不要根据查询条件的维度，做相对应的聚合表。线上的请求就查询聚合表的数据，不走原表。
比如，用户下单后有一份订单明细，而订单明细表的量级太大。但在产品侧（前台）透出的查询功能是以「天」维度来展示的，那就可以将每个用户的每天数据聚合起来，在聚合表就是一个用户一天只有一条汇总后的数据。
查询走聚合后的表，那速度肯定杠杠的（聚合后的表数据量肯定比原始表要少很多）。
思路大致的就是「以空间换时间」，相同的数据换别的地方也存储一份，提高查询效率。
面试官：那我还想问下，除了读之外，写性能同样有瓶颈，怎么办？
候选者：你说到这个，我就不困了。
如果在 MySQL 读写都有瓶颈，那首先看下目前 MySQL 的架构是怎么样的。
如果是单库的，那是不是可以考虑升级至主从架构，实现读写分离。
简单理解就是主库接收写请求，从库接收读请求。从库的数据由主库发送的 binlog 进而更新，实现主从数据一致（在一般场景下，主从的数据是通过异步来保证最终一致性的）。
面试官：嗯……
候选者：如果在主从架构下，读写仍存在瓶颈，那就要考虑是否要分库分表了。
至少在我前公司的架构下，业务是区分的。流量有流量数据库，广告有广告的数据库，商品有商品的数据库。所以，我这里讲的分库分表的含义是：在原来的某个库的某个表进而拆分。
比如，现在我有一张业务订单表，这张订单表在广告库中，假定这张业务订单表已经有1亿数据量了，现在我要分库分表。
那就会将这张表的数据分至多个广告库以及多张表中（：
分库分表的最明显的好处就是把请求进行均摊（本来单个库单个表有一亿的数据，那假设我分开 8 个库，那每个库 1200 多万的数据量，每个库下分 8 张表，那每张表就 150 万的数据量）。
面试官：你们是以什么来作为分库键的？
候选者：按照我们这边的经验，一般来说是按照 userId 的（因为按照用户的维度查询比较多）。如果要按照其他的维度进行查询，那还是参照上面的的思路（以空间换时间）。
面试官：那分库分表后的 ID 是怎么生成的？
候选者：这就涉及到分布式 ID 生成的方式了，思路有很多。有借助 MySQL 自增的，有借助 Redis 自增的，有基于「雪花算法」自增的。具体使用哪种方式，那就看公司的技术栈了，一般使用 Redis 和基于「雪花算法」实现用得比较多。至于为什么强调自增（还是跟索引是有序有关，前面已经讲过了，你应该还记得）。
面试官：嗯，那如果我要分库分表了，迁移的过程是怎么样的呢？
候选者：我们一般采取「双写」的方式来进行迁移，大致步骤就是：
增量的消息各自往新表和旧表写一份；
将旧表的数据迁移至新库；
迟早新表的数据都会追得上旧表（在某个节点上数据是同步的）；
校验新表和老表的数据是否正常（主要看能不能对得上）；
开启双读（一部分流量走新表，一部分流量走老表），相当于灰度上线的过程；
读流量全部切新表，停止老表的写入；
提前准备回滚机制，临时切换失败能恢复正常业务以及有修数据的相关程序。
面试官：嗯……今天就到这吧。
总结
数据库表存在一定数据量，就需要有对应的索引； 发现慢查询时，检查是否走对索引，是否能用更好的索引进行优化查询速度，查看使用索引的姿势有没有问题； 当索引解决不了慢查询时，一般由于业务表的数据量太大导致，利用空间换时间的思想； 当读写性能均遇到瓶颈时，先考虑能否升级数据库架构即可解决问题，若不能则需要考虑分库分表； 分库分表虽然能解决掉读写瓶颈，但同时会带来各种问题，需要提前调研解决方案和踩坑。
- EOF -
看完本文有收获？请转发分享给更多人
关注「ImportNew」，提升Java技能
点赞和在看就是最大的支持❤️