数据库性能不够？先进行SQL优化！

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

收集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日志中，默认为10秒。

除了超时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语句的执行效率已经非常高。

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

慢SQL 作为衡量数据库访问速度的一个重要指标，对慢SQL的监控可以通过执行show global status like 'Slow_queries'来监控慢SQL数量的变化。如果在某个时间段范围内，数据库的慢SQL数量出现急剧增长，开发者就需要关注该时间段内的慢SQL语句，进行SQL语句排查。

除了慢SQL语句，系统中执行次数最多，扫描记录数最多、执行时间最长的SQL语句，都是SQL优化的对象。在MySQL 5.7的 sys库的statement_analysis视图中，收集了所有来自用户的SQL语句。

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

跟踪执行过程

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

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

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

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

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

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

分析优化方案

掌握了这些关键运行指标，我们接下来就要开始进行分析优化的过程。

• 没有覆盖索引：对于没有被索引覆盖的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排行榜和热表三个功能。

点击慢SQL，开发者可以查看数据库实例每秒慢SQL数量变化，平台自动标识超过警戒线的时间范围，同时还提供了对应时间范围内CPU和IO负载变化。

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

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

SQL 排行榜功能从执行次数、执行时间、扫描记录数三个维度对SQL语句进行了排序，开发者可以轻松获取执行次数最多、执行时间最长、扫描记录数最多的SQL语句。

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

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

网易云新用户大礼包：https://www.163yun.com/gift

本文来自网易实践者社区，经作者郭忆授权发布。