SQL 分析和调优技巧

Question

我认为，对于开发者⽽⾔，对 MySQL 的调优重点⼀般是在「开发规范」、「数据库索引」⼜或者说解决线上慢查询上。对于参数调优我不怎么会，但是我 可以聊聊平时的开发规范和索引这块。

⾸先，我们在⽣产环境下，创建数据库表，都是在⼯单系统下完成的（那就⾃然需要 DBA 审批）。如在创建表时检测到没 有创建索引，那就会直接提示 warning。理论上来说，如果表有⼀定的数据量，那就应该要创建对应的索引。从数据库查询数据需要注意的地⽅还是蛮多的， 其中很多都是平时积累来的。⽐如说：

1. 是否能使⽤「覆盖索引」，减少「回表」所消耗的时间。意味着，我们在 select 的时候，⼀定要指明对应的列，⽽不是 select *
2. 考虑是否组建「联合索引」，如果组建「联合索引」，尽量将区分度最⾼的放在最左边，并且需要考虑「最左匹配原则」
3. 对索引进⾏函数操作或者表达式计算会导致索引失效
4. 利⽤⼦查询优化超多分⻚场景。⽐如 limit offset , n 在 MySQL 是获取 offset + n 的记录，再返回 n 条。⽽利⽤⼦查询则是查出 n 条，通过 ID 检索对应的记录出来，提⾼查询效率。
5. 通过 explain 命令来查看 SQL 的执⾏计划，看看⾃⼰写的 SQL 是否⾛了索引，⾛了什么索引。通过 show profile 来查看 SQL 对系统资源的损耗情况（不过⼀般还是⽐较少⽤到的）
6. 在开启事务后，在事务内尽可能只操作数据库，并有意识地减少锁的持有时间（⽐如在事务内需要插⼊&&修改数据，那可以先插⼊后修改。因为修改是更新操作，会加⾏锁。如果先更新，那并发下可能会导致多个事务的请求等待⾏锁释放）

线上我们主要用的是是 Read Commit（读已提交），MySQL 默认⽤的是 Repeatable read（可重复读）。选⽤什么隔离级别，主要看应⽤场景嘛，因为隔离级别越低，事务并发性能越⾼。像 Repeatable read（可重复读）隔离级别，就有可能因为「间隙锁」导致的死锁问题。但可能你已经知道，MySQL 默认的隔离级别为 Repeatable read。

很⼤⼀部分原因是在最开始的时候，MySQL 的 binlog 没有 row 模式，在 read commit 隔离级别下会存在「主从数据不⼀致」的问题，binlog 记录了数据库表结构和表数据「变更」，⽐如 update/delete/ insert/truncate/create。在 MySQL 中，主从同步实际上就是应⽤了 binlog 来实现的。有了该历史原因，所以 MySQL 就将默 认的隔离级别设置为 Repeatable read。

即便⾛对了索引，线上查询还是慢。如果⾛对了索引，但查询还是慢，那⼀般来说就是表的数据量实在是太⼤了。

⾸先，考虑能不能把「旧的数据」 给"删掉"，对于我们公司⽽⾔，我们都会把数据同步到另一库，说明已经离线存储了⼀份了。那如果「旧的数据」已经没有查询的业务了，那最简单的办法肯定 是删掉部分数据咯。数据量降低了，那⾃然，检索速度就快了...另一种情况，能不能在查询之前，直接⾛⼀层缓存（Redis）。⽽⾛缓存的话，⼜要看 业务能不能忍受读取的「⾮真正实时」的数据（毕竟 Redis 和 MySQL 的数据⼀致性需要保证），如果查询条件相对复杂且多变的话（涉及各种 group by 和 sum），那⾛缓存也不是⼀种好的办法，维护起来就不⽅便了...。

再看看是不是有「字符串」检索的场景导致查询低效，如果是的话，可以考虑把表的数据导 ⼊⾄Elasticsearch 类的搜索引擎，后续的线上查询就直接⾛Elasticsearch 了。MySQL->Elasticsearch 需 要有对应的同步程序(⼀般就是监听 MySQL 的 binlog，解析 binlog 后导⼊到 Elasticsearch)。：如果还不是的话，那考虑要不要根 据查询条件的维度，做相对应的聚合表，线上的请求就查询聚合表的数据，不⾛原表。

⽐如，⽤户下单后，有⼀份订单明细，⽽订单明细表的量级太⼤。但在产品侧 (前台) 透出的查询功能是「天」维度来展示的，那就可以将每个⽤户的每天数据聚合起来，在聚合表就是⼀个⽤户⼀天只有⼀条汇总后的数据。查询⾛聚合后的 表，那速度肯定杠杠的（聚合后的表数据量肯定⽐原始表要少很多），思路⼤致的就是「以空间换时间」，相同的数据换别的地⽅也存储⼀份，提⾼查询效率。