快速成长期应用架构实践 （11）：数据库调优

欢迎访问网易云社区，了解更多网易技术产品运营经验。

4.3.2 数据库调优

随着运营推广的开始，业务进入快速增长期，数据库作为后端系统唯一或者主要持久 化组件，无论是存储的数据量还是事务请求次数都呈现大幅增长，数据库的事务处理能力 逐渐成为整个系统性能瓶颈。增加物理资源虽然可以起到一定程度的缓解作用，但是毕竟 是一种治标不治本的方法。分布式数据库虽然听起来高端，但是其系统改造成本及学习运维成本又让一般的中小型团队望而却步。SQL 优化，根据用户访问的 SQL 语句，对数据库 的表结构，尤其是索引进行优化，能够有效加速 SQL 的执行效率，对于开发者来说是最简 单有效的解决方案。接下来，我们就来聊聊 SQL 优化的一般步骤。

1. 收集 SQL 语句

SQL 优化的起点是用户访问数据库的 SQL 语句，尤其是问题 SQL 语句，在数据库中， 主要指访问时间比较长的 SQL 语句。MySQL 数据库提供了慢 SQL 日志功能，帮助开发者 获取执行时间超过一定阈值的 SQL 语句列表，通过参数 slow_query_log 开启。MySQL 支 持两种慢 SQL 日志保存格式：Table 和 File，通过参数 log_output 进行配置，使用 table 的 优势在于可以直接使用 SQL 进行查询分析。开启慢 SQL 日志后，系统会将执行时间超过 long_query_time 的 SQL 语句记入慢 SQL 日志中，默认时间为 10s。

除了超时 SQL 语句，开发者还可以将没有使用索引的 SQL 语句列为问题 SQL，记入 慢 SQL 日志，通过 long_queries_not_using_indexes 开启。为了控制慢 SQL 日志写入频率， log_throttle_queries_not_using_indexes 规定了每分钟因为没有走索引而记入慢 SQL 日志的 查询数。MySQL 还提供了参数 log_slow_admin_statements 控制管理类型的 SQL 语句是否 记入慢 SQL 日志，例如 ALTER TABLE、ANALYZE TABLE 等。min_examined_row_limit 规定了所有记入慢 SQL 日志的 SQL 语句最小的行扫描记录数，默认值为 0，只有扫描记录 数大于该值的 SQL 语句才会被记入慢 SQL 日志，推荐开发者将其设置为 100，如果 SQL 语句只扫描一行记录，说明该 SQL 语句的执行效率非常高。

图 4-16 展示了使用 SQL 查看慢日志的结果，sql_text 字段展示了具体的慢 SQL 语句。

图 4-16 使用 SQL 查看慢日志

慢SQL作为衡量数据库访问速度的一个重要指标，可以通过执行show global status like 'Slow_queries'来监控慢 SQL 数量的变化。如果在某个时间段范围内，数据库的慢 SQL 数量 出现急剧增长，开发者就需要关注该时间段内的慢 SQL 语句，进行 SQL 语句排查。
除了慢 SQL 语句，系统中执行次数最多，扫描记录数最多、执行时间最长的 SQL 语 句，都是 SQL 优化的对象。在 MySQL 5.7 版本的 sys 库的 statement_analysis 视图中，收集 了所有来自用户的 SQL 语句，如图 4-17 所示。 

图 4-17 MySQL 5.7 版本的 sys 库的 statement_analysis 视图

query 字段中的 SQL 语句已经进行了格式化处理，将格式相同仅参数不同的 SQL 语句 归为一类。开发者按照 exec_count、latency、rows_examined 3 个字段分别排序，即可获取 执行次数最多、执行时间最长、扫描记录数最多的 SQL 语句列表。

2. 跟踪执行过程

明确了待优化的 SQL 语句之后，接下来，为了跟踪这些 SQL 语句执行过程，我们需 要采集一些关键指标。

索引：如图 4-18 所示，通过执行 explain SQL 语句，获取 SQL 的执行计划，key 字段标识了该 SQL 语句是否通过索引扫描记录，如果该字段为 NULL，且扫描记 录数较多，则可以通过创建索引来优化执行效率。 

图 4-18 通过执行 explain SQL 语句获取 SQL 的执行计划

扫描记录数：慢日志的 rows_examined 字段，标识了该 SQL 语句扫描的记录数， 扫描记录数越多，表示该 SQL 语句执行花销越大，执行效率越低。

持锁时间：慢日志的 lock_time 字段标识了该 SQL 语句因为锁等待浪费的时间， 如果该值较大，说明 SQL 语句存在较多的锁冲突和等待。

返回记录数：慢日志的 rows_sent 字段标识该条 SQL 语句返回的记录条数，返回 记录越多，对系统资源的消耗也越多。

除了上述几个关键指标外，在网易维护的 MySQL 分支版本 InnoSQL 中，还增加了针 对某条 SQL 语句的 I/O 开销统计，在 slow log 中增加了 logical reads 和 physical reads 两个 列，分别表示该 SQL 语句读数据库缓存和产生实际硬盘 I/O 的次数。

3. 分析优化方案

掌握了这些关键运行指标，接下来就开始分析优化。

没有覆盖索引：对于没有被索引覆盖的 SQL 语句的过滤条件涉及的字段，在区分 度较大的字段上创建索引，如果涉及多个字段，尽量创建联合索引。需要注意的 是，SQL 语句在一些情况下是无法使用索引的，例如使用！、 <、>判断等，此时 应修改 SQL 语句逻辑。

SQL 语句被索引覆盖，但是扫描记录数非常多，返回记录数不多：此时要考虑索 引是否高效，衡量索引效率的一个判断标准是索引的区分度，通过 MySQL 库下 的 innodb_index_stats 表的 stat_value 字段，我们可以知道该表在该索引涉及列上 取值不同的记录数，然后与 n_rows 表中记录的该表总记录数相除，即可得到该 表的区分度，结果越接近 1，表示区分度越高，结果低于 0.1，则说明区分度较 差，开发者应该重新评估 SQL 语句涉及的字段，选择区分度高的多个字段创建 索引。

SQL 语句被索引覆盖，扫描记录数非常多，返回记录数也非常多：此时除了索引 效率的问题，也有可能是因为 SQL 语句本身过滤条件不强，导致返回的记录数过 多引起的，此时，开发者应该从业务层修改 SQL 语句，增加 SQL 过滤条件。
除了上述指标的特征外，由于索引的创建在插入、更新、删除数据时会带来索引维护 的开销，所以我们必须尽可能精简索引的数量，可以通过如下方式实现。

去除冗余索引：通过 MySQL sys 库下的 schema_redundant_indexes 视图，查看当 前实例有哪些冗余索引。

合并多个索引：对于已经创建的索引，如果多个索引涉及字段顺序一致，且索引 的第一个字段相同，则可以组成一个联合索引；例如索引（a，c）和索引（a，d）， 可以组成联合索引（a，c，d）。值得注意的是，合并的两个索引的第一个字段必 须相同，因为联合索引的第一个字段必须要出现在查询条件中，否则查询不能使 用该索引。

去除无效索引：通过 MySQL sys 库下的 schema_unused_indexes 视图，可以查看 当前实例哪些索引从未被使用。

网易云 SQL 优化专家系统 

为了方便开发者简单有效地完成 SQL 优化过程，网易云基础服务推出了 SQL 优化专 家系统，集成网易 10 年的数据库优化经验进行标准化输出，推出了慢 SQL、SQL 排行榜 和热表 3 个功能，如图 4-19 所示。

点击“慢 SQL”，开发者可以查看数据库实例每秒慢 SQL 数量变化，平台自动标识超 过警戒线的时间范围，同时还提供了对应时间范围内 CPU 和 I/O 负载变化，如图 4-20 所示。

图 4-19 网易云 SQL 优化专家系统 

图 4-20 慢 SQL 监控信息

点击某个时间范围，就获取了对应时间范围内的慢 SQL 列表，平台对所有慢 SQL 语 句进行了格式化处理，如图 4-21 所示。 

图 4-21 慢 SQL 列表

点击某类 SQL 语句，平台会为开发者呈现该 SQL 语句执行过程中涉及的所有关键指 标，同时综合这些关键指标和系统提前预置的优化经验判断逻辑，给出开发者实用的 SQL 优化建议，如图 4-22 所示。 

图 4-22 慢 SQL 详细信息

“SQL 排行榜”功能从执行次数、执行时间、扫描记录数 3 个维度对 SQL 语句进行排 序，开发者可以轻松获取执行次数最多、执行时间最长、扫描记录数最多的 SQL 语句，如 图 4-23 所示。 

图 4-23 SQL 排行榜

“热表”功能是网易云基础服务推出的一个通过分析表的访问特点，针对表进行优化的 功能。例如，在电商的业务架构中，数据库有多种用途，对于存储商品信息的数据库表， 一个明显的特点是读请求远远多于写请求，对于读多写少的业务，专家系统会推荐开发者 在数据库前端增加缓存，或者创建只读从节点，通过读写分离技术来扩展数据库的读能力。 对于存储交易流水、用户足迹的业务，插入数据较为集中，专家系统会通过建议用户使用 批量插入 insert into table values()()()的方式提高插入的效率，避免每次插入都需要数据库 反复解析 SQL 语句生成执行计划的过程开销。对于一些要求实时插入的场景，专家系统 会推荐开发者使用插入效率更高的 TokuDB 存储引擎作为 MySQL 的数据存储解决方案， 如图 4-24 所示。 

图 4-24 热表分析

热表功能的另外一个作用是预热，新建只读从节点，为了防止业务切入新建节点后， 由于节点没有缓存热点数据，请求直接访问硬盘，导致访问延迟增加，开发者可以通过热 表功能锁定热点数据，预先将这些数据扫描到内存中。

文章节选自《云原生应用架构实践》 网易云基础服务架构团队 著 

网易云计算基础服务深度整合了 IaaS、PaaS 及容器技术，提供弹性计算、DevOps 工具链及微服务基础设施等服务，帮助企业解决 IT、架构及运维等问题，使企业更聚焦于业务，是新一代的云计算平台。点击可免费试用。