Posted Updated 数据库 / MySQL21 minutes read (About 3193 words)

如何进行数据库性能优化？

慢 SQL 原因及方案

SQL 执行慢的原因有很多，比如下面这些：

  • 索引问题

    • 索引失效
    • 索引设计问题
    • MySQL 选错了索引

  • 复杂查询

  • 数据量过大

  • 锁竞争

  • 硬件资源不足

  • 数据库负载过高

  • 数据库配置问题

针对不同的原因，需要采用不同的方案：

索引问题

像索引失效和索引设计问题，是 SQL 设计之初就可以发现和避免的，一方面对索引原理要有基本的掌握，另一方面在 SQL 上线前可以通过 expalin 查看执行计划进行规避。

MySQL 选错索引可能是由于不同的原因导致的，比如数据库统计信息不准确，可以通过 analyze table t 命令来重新统计索引信息。

另外更常见的原因是，MySQL 查询优化器分析得出的理论上的最佳索引，因为实际数据分布的原因，查询速度并不是最快的。这里描述比较抽象，文章后面 第一个 SQL 优化案例就是一个 MySQL 选错索引的例子，可以结合起来理解。

这种情况下就需要我们采取一定的措施去对索引进行修正。常用的方案有以下几种：

  • 通过 force index 强制指定索引

  • 修改 SQL 语句，引导 MySQL 使用我们期望的索引

  • 新建一个更合适的索引来给优化器选择，或者删掉误用的索引

其中，很多人不太推荐使用 force index ，一是将数据库的问题放在了业务中来解决，设计上不够优美，二是把索引名耦合进了 SQL 语句中，索引名改了后，SQL 也得修改，三是迁移到别的数据库的话，这个语法未必兼容，四是修改 SQL 语句加上 force index 之后还要测试和发布，整个过程不够敏捷。

第二种通过修改引导数据库的方式可能导致 SQL 的可读性变得很差，让人不理解设计者的意图，往往不被推荐使用。

第三种，增加和删除索引是比较好的方案，但应用场景非常有限，其它时候就只能使用前面两种方案了。

复杂查询

复杂查询是指 SQL 查询语句本身过于复杂，可能包含多个连接、嵌套子查询、聚合函数等，导致数据库需要花费更多的时间来解析和执行查询。

这种场景下如果对数据库查询效率还有比较高的要求，则可以考虑其它对复杂查询支持更加好的数据库，比如 ES。

如果不要求数据库查询效率，比如离线统计需求、运营后台查询需求，为了避免慢查询 SQL 占用大量连接影响线上业务，可以划分出单独的从库实例提供给这类 SQL 使用。

数据量过大

单表数据量过大，则需要考虑分表：《分库分表——原理篇》

锁竞争

锁竞争可能是存在需要频繁更新的热点数据，也可能是存在长事务导致的。热点数据的话可以考虑通过将一行改成逻辑上的多行来减少锁冲突，或者通过写缓存，异步更新数据库的方式避免锁冲突。

如果是长事务，则需要进一步分析导致长事务的原因，采取相应的解决措施，比如在开发过程中，尽可能的减小事务范围，少用长事务。另外，如果事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放。

硬件资源不足

数据库服务器的硬件资源不足，例如 CPU、内存、磁盘等，无法满足大量查询请求或者写请求的处理需求，导致负载增加。这种情况下可以采取的方案，包括升级数据库硬件配置，或者增加从节点承接更多的读流量，或者通过分库分担写流量。

数据库负载过高

上面也提到了，数据库负载过高可能是由于硬件资源不足导致的。但有时可能是一些特殊场景，比如双十一、促销活动等导致的请求突增，这时候如果采取升级配置、增加节点等方案成本上可能不是那么划算，则可以考虑增加缓存来缓解数据库压力。

数据库服务器配置问题

数据库服务器配置，比如缓冲池大小等，这些配置往往是由 DBA 来掌控的，业务研发不需要也无权进行调整。而且这类配置在经历大量生产环境的考验后通常已经形成了最佳实践，出问题的概率很小。所以生产实践中很少遇到需要调优服务器的参数的情况，但我们分析问题时还是需要考虑到这种可能性。

上面这几种慢 SQL 原因和方案有一个前提，那就是数据库服务器执行 SQl 慢，而另一种也比较常见的情况是，服务器执行 SQL 正常，而客户端出现慢 SQL，这种情况则需要考虑客户端配置，如连接池大小，以及网络延迟等因素的影响。

小结

面对不同的问题和原因，我们可以采取不同的措施。另外，从提前规避的角度来思考，不同的开发阶段，需要考虑的问题也不一样。往往越早需要思考，越早能够应用的方案，也是优先级更高的方案。举个例子，针对具体的业务场景选择合适数据库，比选定一个不合适的数据后，再去进行各种优化措施，显然要更好。

这里把所有可以采取的措施，按照开发周期中从早到晚进行一个总结。

  • 选择合适的 DBMS

  • 表设计优化

  • 逻辑查询优化

  • 物理查询优化

  • 加缓存

  • 库级优化

概括下，前两个方案在于选择，中间两个方案是 SQL 的查询优化，最后两个方案在于引入外援，不管是其他类型的数据库，还是更多的同类型的数据库。

数据库调优步骤

上面一小节介绍了常见的慢 SQL 原因和解决方案，但当我们真的面对慢 SQL 时，该如何确定是哪种原因呢？下面这张图很好的说明了一般的思考过程和调优步骤。

整个流程分为观察（S）和行动（A）两类操作，通过观察了解数据库整体的运行状态，通过性能分析工具了解执行慢的 SQL 都有哪些，查看具体的 SQL 执行计划，甚至是 SQL 执行中的每一步的成本代价，最终定位问题所在，找到了问题，再采取相应的行动。

关于这张图的具体细节这里就不进行讲解了，可以查看图片来源文章，也就是本文参考文章 2 进行进一步的学习。这里需要指出的一点是，虽然是流程图，但真正进行数据库调优时，并不一定是自上而下顺序进行的，完全可以并行执行多种观察动作，综合得出最可能的一个原因，然后首先针对这个最可能的原因采取措施，进行验证。

数据库调优案例

本小结中介绍一些实际业务中遇到比较典型的 SQL 慢查询，分析其原因，介绍当时采取的方案以及效果。相关案例会持续补充。

案例1：数据库选错索引

系统中有这样一张表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
CREATE TABLE puri_video_user (
 id bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
 user_id varchar(100) NOT NULL DEFAULT '' COMMENT '用户id',
 doc_id varchar(100) NOT NULL DEFAULT '' COMMENT '内容池统一ID',
 audit_status tinyint(4) NOT NULL DEFAULT '0' COMMENT '视频审核状态1 待审核 2审核通过 3审核拒绝 4管理员cms设置仅自己可见',
 visible_status tinyint(4) NOT NULL DEFAULT '0' COMMENT '可见状态, 1:用户设置所有用户可见 2:用户设置仅自己可见',
 delete_status tinyint(4) NOT NULL DEFAULT '0' COMMENT '用户删除状态 0未删除 1用户自己删除且内容池没有删除 2用户自己删除且内容池删除成功',
 create_time timestamp NOT NULL DEFAULT '2018-05-29 00:00:00' COMMENT '创建时间',
 update_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
 PRIMARY KEY (id),
 UNIQUE KEY uk_idx_docid (doc_id),
 KEY idx_create_time (create_time),
 KEY idx_uid_status (user_id,audit_status,delete_status,visible_status)
) ENGINE=InnoDB AUTO_INCREMENT=193268096 DEFAULT CHARSET=utf8mb4 COMMENT='视频用户信息表'

发生慢查询的 SQL 如下，

1
select * from puri_video_user where user_id = '724ce5f73221b9ce' and delete_status = 0 and id < 2147483647 order by id desc limit 12\G

explain 查看执行计划如下：

发现 MySQL 同时使用了 PRIMARY 和 idx_uid_status 这两个索引，最后取了交集。推测 SQL 执行慢的原因可能是这两种：

  • 走主键索引扫描数据过多

  • 走 idx_uid_status 索引排序比较耗时

然后对比了强制走主键索引和强制走 idx_uid_status 索引耗时，发现走主键索引的话和优化器给出的索引方案耗时接近，走 idx_uid_status 索引耗时有明显下降。

所以最终确定了通过 force index 强制走 idx_uid_status 索引的方案。上线后慢查询频率确实明显降低，但并未完全消失，关于依然存在的慢 SQL，在案例二中进一步介绍。

案例2：排序数据过多

经过案例一种的优化，SQL 如下所示：

1
select * from puri_video_user force index(idx_uid_status) where user_id = '724ce5f73221b9ce' and delete_status = 0 and id < 2147483647 order by id desc limit 12\G

慢查询频率虽然明显降低，但依然存在，查看了几个例子，发现慢 SQL 有这样一个一个特点，就是 user_id 对应的用户，对应的视频总数非常多。因为这条 SQL 需要对满足条件的数据进行排序，如果需要排序的数据越多，则排序越慢，而且可能会使用临时文件进行外部排序，导致查询性能严重下降。

这里也说明方案一中的方案没有完全奏效，对于视频数少的用户效果不错，但是如果视频数很多，依然会因为排序导致慢查询。

这里考虑为什么需要排序，因为查询条件中包含了 user_id 和 delete_status 这两个字段，但是索引 idx_uid_status 则是 user_id, audit_status, delete_status, visible_status 四个字段，delete_status 位于第三个位置，无法利用耳机索引主键的有序性。

所以如果要避免排序，则需要新建 user_id 和 delete_status 的联合索引。

案例3：分库分表

《分库分表——实战篇》

总结

本文首先介绍了慢 SQL 可能的原因以及对应的方案，还介绍了数据性能优化可以从那几个维度去思考，在实际中按照什么样的步骤去排查问题，选择合适的方案。最后则是在实际业务中的几个数据库性能优化案例，以后遇到一些比较典型的问题，也会持续补充在这里。

参考

  1. 当我们思考数据库调优的时候，都有哪些维度可以选择？
  2. 如何使用性能分析工具定位SQL执行慢的原因？
  3. MySQL为什么有时候会选错索引？
#
×