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》
- 《数据库索引设计与优化》
更多文章
- socks5 协议详解
- zerotier简明教程
- 搞定面试中的系统设计题
- 用peewee代替SQLAlchemy
- frp 源码阅读与分析(一):流程和概念
- Golang(Go语言)中实现典型的fork调用
- DNSCrypt简明教程
- 一个Gunicorn worker数量引发的血案
- Golang validator使用教程
- Docker组件介绍(一):runc和containerd
- Docker组件介绍(二):shim, docker-init和docker-proxy
- 使用Go语言实现一个异步任务框架
- 协程(coroutine)简介 - 什么是协程?
- SQLAlchemy简明教程
- Go Module 简明教程