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》
  • 《数据库索引设计与优化》

更多文章
  • 为什么我要用Linux作为桌面?
  • disqus获取评论时忽略query string
  • MySQL性能优化指南
  • 网络编程所需要熟悉的那些函数
  • DNSCrypt简明教程
  • SQLAlchemy简明教程
  • 这些年,我们错过的n个亿
  • 给Linux用户的FreeBSD快速指南
  • 旧电脑也不能闲着:家用备份方案
  • 将SQLite的数据迁移到MySQL
  • Linux托管Windows虚拟机最佳实践
  • 为什么gRPC难以推广
  • 关于ORM的思考
  • MySQL指定使用索引(使用索引提示)
  • QT5使用GTK主题