MySQL性能优化指南

数据库优化，是一个存在了几十年的问题，更是每一个后端开发者精进路上必须掌握的技能。

而性能优化的核心，就是抠门。在完成功能的前提下：对于CPU，执行越少的代码，那么所需要的CPU时间就越少，因此程序就会越快； 对于IO，执行越少次数的I/O，阻塞于I/O的时间就会越少；对于网络，传输越少的数据，那么所需要的时间就会越少。

对于MySQL的优化，也是一样的。接下来我们来看看MySQL优化的几个步骤：

首先要找到问题所在

性能测试，重现问题，这是进行性能优化的前提。因此我们需要靠一些工具，例如压测工具、慢查询日志等，定位到问题，知道我们 遇到的问题，然后才能解决问题。如果定位到多个性能问题，那么需要进行一个优先级（性能影响程度）的排序，从影响最大的开始 逐次解决。

常见的性能指标包括：

• 吞吐量：指的是单位时间内执行的事务的数量。
• 响应时间/延迟：是指一次访问，从开始到收到响应结果所需要的总的时长。
• 并发性：指的是在任意时间有多少同时发生的并发请求。
• 可扩展性：指的是能否让数据库的性能随着并发量的增加而线性增长。

而常见的测试方式就是，一方面逐步加大查询量（或写入量），另一方面定时收集性能数据，从而进行观察。理想情况下可以通过图表 的方式进行展示，这样可以快速获得一个直观的数据表示。

此处推荐Prometheus + MySQL Exporter。

数据库缓存

MySQL有对查询进行缓存，但是这要取决于配置是否开启，可以参见 此处

The query cache is deprecated as of MySQL 5.7.20, and is removed in MySQL 8.0. 注意，MySQL 8.0 移除了此功能。

开启了query cache之后，MySQL会对查询进行缓存，缓存的规则是对输入的SQL进行hash计算，如果下一次的SQL算出来的hash得到 相同结果，那么直接返回结果，而不会再次去查询。

但是此处注意，由于是对SQL进行hash计算，所以只要SQL有一丁点的变动，比如多了一个空格，那么就会计算出一个不同的hash值， 从而不会从缓存中读取结果。此外只要查询语句中有任何不确定的参数，例如函数 NOW() 或者类似的自定义函数、存储函数、用户变量、 临时表，mysql库中的系统表，或者任何包含对列级别权限的表，都不会被缓存。

而另一方便，由于所有的输入都会进行一遍缓存查询，此处也有可能会成为性能瓶颈。

数据库schema的设计

schema设计的好，那么就能省事很多。数据库设计中，有数据库范式和反数据库范式两种极端。通常来说，我们会糅合两种方向，取 一个适中的度。遵循数据库范式的好处有数据库操作会更快、没有重复数据等，而反数据库范式的好处有避免随机IO而加快查询速度等。

一般来说，数据库schema的设计遵循这么几点：

• 选取范式和反范式中合适的度，但是这个度和具体业务有关
• schema不应该有太多的列，遵循最小原则，即无用的列，就不放此表中
• ALTER TABLE 时加上 ONLINE DDL 提示，让MySQL进行在线改表，而不会进行锁表
• 数据类型同样遵守最小原则，即选取合适，但又不浪费的数据类型。例如用bool来存储true和false，用int来存储id等等。

在《数据库索引设计与优化》一书中提出了一个非常好的概念：三星索引。我们将会看到如何设计一个三星索引。不过在此之前，我们会 先看看常见的索引类型以及他们的特性。

• B-Tree索引。B-Tree索引适用于这么几种查询：

  • 精准匹配，例如 WHERE a = 'a'；
  • 最左前缀匹配，即只使用索引的第一列，例如有一个联合索引是 (user_id, user_name)，当查询条件是 WHERE user_id=1 时也可以用上这个索引；
  • 匹配列前缀，例如有一个索引是 user_name，那么查询 WHERE user_name LIKE "James%" 可以用上该索引；
  • 范围匹配，由于B-Tree是有顺序的，因此可以进行范围查询，例如有一个索引是 user_id，那么查询 WHERE user_id BETWEEN 1 AND 10 可以用上该索引；
  • 精确匹配一列并且范围匹配另一列，这就是把第一条和上一条结合起来；
  • 只访问索引的查询，这种一般叫做“覆盖索引”，意思就是要查询的数据，都在索引里，这样就不用去查询数据行，而可以直接返回结果。

但同时注意，B-Tree索引也有缺点，那就是遵循最左原则：必须从左往右，而且必须是连续的，不能跳过任何一列，否则就用不上索引；此外， 范围查询之后的查询，也无法用上索引，也就是说，如果有一个索引 (a, b, c)，我们的查询是 WHERE a = 'a' AND b LIKE 'j%' AND c = c， 索引无法用上 c，只能用上 (a, b) 这一部分。

• Hash索引。Hash索引基于哈希表来实现，因此只有精确匹配索引所有列的查询才能有效。由于是使用哈希表，因此哈希索引的结构十分紧凑， 查找速度非常快，但是也有缺点：

  • 哈希索引不包含数据行中的数据，因此访问到索引之后，必须进行数据行的查询；
  • 哈希表没有顺序，因此哈希索引无法用于排序，同时也不支持范围查找。

而索引设计则与业务非常相关，但是必须纠正一个错误，那就是我们要先思考一下，一个查询能用上几个索引？答案是一个，当然，MySQL有 引入一个叫做 index merge 的操作，也就是说使用多个索引进行查询，然后对结果进行集合操作得到结果，但是这通常意味着索引没有 设计好。回到上一句开头，我们记住，在好的索引设计的情况下，数据库一次查询是只使用一个索引的。设计好一个索引通常遵循以下原则：

• MySQL中所有的二级索引都会把主键包含进去，因此不能让主键太长，否则索引会变得很大。例如主键可以设置为自增id。
• 所选择的索引列的数据，区分度(区分度的概念，例如100行数据里，50个是true，50个是false，那么区分度就不够高，而如果数据 是1-100，那么区分度就很高，也就是说，通过一个where条件筛选出来的数据如果越多，那么区分度越低)应该足够高，如果是一个 联合索引，那么从左到右的顺序应当是区分度逐渐降低。

回到开头的问题，什么样的索引是三星索引呢？这三颗星分别是什么呢？

• 第一颗星是如果用上了索引，就给一颗星。也就是说，如果有多个查询条件，把有索引的条件放前面。
• 第二颗星是如果索引中的数据顺序和查询中的排列顺序一致，那么就给第二颗星，也就是说，如果ORDER BY中的条件不在索引里，那么就不符合条件。
• 第三颗星是使用“覆盖索引”，也就是说，如果要查询的数据，都在索引里，那么就给第三颗星。因为这样不需要去访问数据行， 从而可以加快访问速度。

通常来说，第三颗星比较难获得，因为一般业务需要的数据各式各样，不可能把所有数据都加到索引里。但是第一和第二颗星星还是可以争取 一下的。

结合上面的索引设计，数据库查询也有一些常见的优化规则：

• 查询条件中不使用表达式，而应该使用常量。例如，应该使用 WHERE a = 1 而不是 WHERE a +1 = 5。
• 不查询不必要的数据，如有可能，刚好查询索引中的数据。那么就不用多传输不必要的数据。使用ORM的同学请注意，ORM通常会把所有 数据都查询出来，这样才实例化对象。
• 避免多个范围条件，这样产生的数据量可能会很大，因此也就需要更多的时间来处理。使用尽可能精确的条件来进行查询。

除了软件上的优化，如果性能仍然不够，那么就需要提升硬件性能了，这个就不在我们这一篇中来讲述了。

这一篇中我们总结了数据库（MySQL）中进行优化的步骤和注意点，首先我们介绍了步骤，然后介绍了索引的类型，如何设计索引，如何 使用索引，设计出高效高性能的查询。

---

参考资料：

• 《高性能MySQL》
• 《数据库索引设计与优化》

---

密码技术简明教程(二)：散列、消息认证码和数字签名 密码技术简明教程(一)：对称加密和非对称加密 Kubernetes 笔记 go mod 和 logrus 路径大小写的问题 Flask自动加载Blueprint 在KVM里安装Minikube 搞定面试中的系统设计题 Crontab + Sendmail实现定时任务并且通知 Nginx设置Referer来防止盗图 Graphviz dot简明教程 jQuery简明教程 Python RQ(Redis Queue)添加gevent支持 读《超级运营术》- 如何做社区？ 技术人，光有技术是不行的 搭建aria2服务器

---