Kyle's Notebook

MySQL 性能优化基础

Word count: 10.7kReading time: 40 min
2021/09/12

MySQL 性能优化基础

在软件开发领域,一项重要的原则是要避免过早优化。相比起需求分析、架构设计、标准化等一系列的工作,性能优化的优先级确实比较低。而且在预算宽裕的情况下,更建议优先考虑投入更多资源,而非在技术门槛较高的性能优化中迷失。

如果必要地性能优化,应遵循帕累托法则,只有对少部分性能瓶颈深度优化,才能以最小成本获得最大收益。

实现性能优化需要使用各种工具,而且要结合具体场景。常见问题有查询阻塞、慢查询、短连接风暴,QPS 激增等。由于生产问题排查与性能优化分属不同的议题,限于篇幅不在此赘述。

SQL 优化

减少操作数据量

由于 MySQL Server 一边读取、一边发送,而非在服务端暂存所有查询结果。

当客户端接收慢,服务端发不出去、数据挤压在 net_buffer,会导致事务执行时间变长。

使用 show processlist 可查看事务 State,如一直处于 Sending to client 状态:

  • 当客户端使用 -quick 参数,即用 mysql_use_result 读一行处理一行。如客户端业务逻辑复杂,每读一行数据后数据处理太慢。建议使用 mysql_store_result 接口,把查询结果保存到本地。

  • 当返回数据太多,大量线程处于该状态,需要优化查询结果,或将 net_buffer_length 设置成更大的值。

在设置服务端参数前,建议先考虑业务层面优化。

只返回必要的列

避免使用 SELECT *,由于无法利用覆盖索引,必然导致回表。

只返回必要的行

存储引擎只会返回满足条件的数据,由 Server 层执行器决定取多少条。

LIMIT(offset, rows) 将查询 offset 规模的数据,再只保留 rows 条,因此建议自定义分页逻辑:

  • 取上一页结果排序最值,作为下一页的条件(比如最新记录)。

  • 先用覆盖索引获取主键,再回表关联查询(思路是延迟关联):

1
2
3
4
5
6
7
SELECT xxx
FROM A
JOIN (
SELECT id
FROM B
LIMIT 10000000, 10
) AS tp

缓存数据

当数据经常被重复查询时,缓存可避免每次都请求数据库,带来的性能提升非常明显。

切分大查询

当执行涉及大量数据的查询:

  • 一次锁住很多数据。

  • 占满整个事务日志。

  • 耗尽系统资源。

  • 阻塞其它小而重要的查询。

比如:

1
2
DELETE FROM messages 
WHERE create < DATE_SUB(NOW(), INTERVAL 3 MONTH);

可在应用层优化:

1
2
3
4
rows_affected = 0
do {
rows_affected = do_query("DELETE FROM messages WHERE create < DATE_SUB(NOW(), INTERVAL 3 MONTH) LIMIT 10000")
} while rows_affected > 0

优化聚合

分解关联

将大连接查询分解成对多个单表查询,在应用层关联

  • 减少锁竞争。

  • 使缓存更高效。

    • 对于关联查询,当其中某表发生变化,整个查询缓存都将更新;而分解后的多个查询,即使其中某些表有变化,其它表的缓存依然可用;

    • 单表查询的缓存结果更可能被其它查询复用。

  • 可更容易实现分库,实现高性能和可伸缩(视乎具体场景)。

  • 查询本身的效率也可能会有所提升。

如果关联查询可改写为固定、少量的 IN 范围,可让 MySQL 按照 ID 顺序查询,将比随机连接更高效。

1
2
3
4
5
SELECT * 
FROM tab
JOIN tag_post ON tag_post.tag_id=tag.id
JOIN post ON tag_post.post_id=post.id
WHERE tag.tag='mysql';
1
2
3
4
5
6
7
8
9
10
11
SELECT * 
FROM tag
WHERE tag='mysql';

SELECT *
FROM tag_post
WHERE tag_id=1234;

SELECT *
FROM post
WHERE post.id IN (123,456,567,9098,8904);

如无法避免关联,应考虑在被驱动表(右)的关联字段加上索引。

优化行数统计

在不同的存储引擎中,COUNT(*) 有不同实现:

  • MyISAM 引擎把表总行数存在磁盘上,执行时直接返回,但前提是没有 WHERE 条件。

  • InnoDB 选择最小的索引树遍历、计数返回(由于 MVCC,不确定在并行会话中应返回多少可见的行,只能遍历整表),数据最准确但有性能问题。

存储引擎只会返回 Server 层需要的、必要的值,以下几种语句效率从高到低:

  • COUNT(*):基于专门的优化(不会取每行的值),全表遍历时选取最小的索引树,效率最高。

  • COUNT(1):InnoDB 引擎遍历整表而不取值。Server 层在返回的每行放入数字“1”,按行累加。执行效率比 COUNT(pk) 快。

  • COUNT(pk):InnoDB 引擎会遍历全表,把每行 id 取出返回给 Server 层。Server 层判断非空、按行累加(从引擎返回 ID 会涉及数据行解析,以及字段值拷贝)。

  • COUNT(field):如果 field 被定义为非空,则要从逐行记录中读取该字段按行累加;否则判断到有可能为空,还要把值取出再判断非空才累加。

也可以单独建立计数表以代替在原表上计数,需考虑维护方式和成本。

分组查询

当根据多个字段分组查询,相当于进行多轮分组操作,再对最小粒度的组进行统计。

应优先根据索引来分组查询,即尽量使分组顺序和索引列顺序一致。

如果是先 JOIN 再 GROUP BY,可考虑对第⼆个表先执行(⽤上索引)GROUP BY 再 JOIN。

优化关联

关联索引

即驱动表全表扫描、被驱动表索引查询,要求关联的字段在被驱动表上有索引。

无论能否利用被驱动表的索引,都应优先选用小表作为被驱动表。

在选用驱动表时,两个表按照各自的条件过滤,根据参与关联的各字段总数据量,数据量小的即为小表,应作为驱动表。

通常把查询驱动表后得到的记录数称为驱动表的 扇出

连接查询总成本 = 单次访问驱动表的成本 + 驱动表扇出数 * 单次访问被驱动表的成本

扇出值越小,对被驱动表查询次数越少,连接查询总成本越低。显然应选择小表作为驱动表。

使用临时表

为被驱动表创建索引可以把 BNL(Block Nested-Loop Join)转换成 INL(Index Nested-Loop Join),要权衡查询与更新、时间和空间的效率。

如果没有条件创建索引,可使用临时表:

  • 把被驱动表中满足条件的数据放在临时表中。

  • 要利用上 BKA 算法,为临时表的关联字段加上索引。

  • 让驱动表和临时表做关联操作。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
CREATE temporary TABLE temp_t(
int primary key,
a int,
b int, index(b)
) engine=innodb;

INSERT INTO temp_t
SELECT *
FROM t2
WHERE b>=1 AND b<=2000;

SELECT *
FROM t1
JOIN temp_t ON (t1.b=temp_t.b);

哈希关联

BNL 的 join_buffer 是无序数组,如果使用哈希表,就能以 N 次 hash 查找代替 M*N 次判断。由于 MySQL 不支持 Hash Join,可在应用层实现:

  • 取得驱动表的全部数据,在业务端存入哈希表。

  • 获取被驱动表中满足条件的数据,取到业务端。

  • 在哈希表表中寻找匹配的数据,满足匹配的条件则加入结果集。

EXPLAIN 分析

使用 EXPLAIN 分析一个查询语句可预估出需要扫描的行数,并可根据当前查询方式分析出访问类型(type)。

比如在 EXPLAIN 输出的 type 字段中描述表是如何连接的(从最好到最差)。

const

唯⼀键或主键。该表最多有一个匹配行,在查询开始时读取。其值可被优化器的其余部分视为常量。

由于只被读取一次,速度非常快。

1
2
3
4
5
6
7
SELECT * 
FROM tbl_name
WHERE primary_key=1;

SELECT *
FROM tbl_name
WHERE primary_key_part1=1 AND primary_key_part2=2;

一种特殊情况是 system,其查询系统表,只有一行。

eq_ref

除了 constsystem 以外最佳的连接方式,其中⼀个表⽤到了唯⼀键或主键。

可用于使用 = 运算符比较的索引列。值可以是常量或表达式,该表达式使用在此表之前读取的表中的列。比如下列语句中的 ref_table:

1
2
3
4
5
6
7
8
SELECT * 
FROM ref_table,other_table
WHERE ref_table.key_column=other_table.column;

SELECT *
FROM ref_table,other_table
WHERE ref_table.key_column_part1=other_table.column
AND ref_table.key_column_part2=1;

ref

如果连接仅使用键最左前缀,或该字段非唯一索引(连接无法根据键值选择单行)则使用 ref。当只需匹配几行,这仍然是很好的连接类型。

ref 可用于使用运算符比较的索引列,比如下面语句的 ref_table:

1
2
3
4
5
6
7
8
9
10
11
12
SELECT * 
FROM ref_table
WHERE key_column=expr;

SELECT *
FROM ref_table,other_table
WHERE ref_table.key_column=other_table.column;

SELECT *
FROM ref_table,other_table
WHERE ref_table.key_column_part1=other_table.column
AND ref_table.key_column_part2=1;

fulltext

使用 FULLTEXT 索引时采用。

ref_or_null

ref 的基础上,还会额外搜索包含 NULL 值的行,最常用于 优化解析子查询。比如下面语句的 ref_table:

1
2
SELECT * 
FROM ref_tableWHERE key_column=expr OR key_column IS NULL;

index_merge

索引合并:输出行中的列包含使用索引的列,key_len 包含所使用索引的最长键部分的列表。

索引合并即使用到多个二级索引完成一次查询。比如查询条件涉及两个字段等值匹配(或在主键上进行范围匹配),可分别在两个二级索引上筛选出符合条件的记录(顺序 I/O),取其交集(并集等同理)后再回表取完整记录。

相比之下,如果只在其中一个索引上筛选,则需要在回表阶段过滤其它字段的筛选条件(随机 I/O),效率可能比前者更差。

一般情况下,更建议使用联合索引代替索引合并。

unique_subquery

一种索引查找函数,完全替换子查询以提高效率。

对于以下形式的 IN 子查询,此类型替换 eq_ref

1
2
3
4
5
value IN (
SELECT primary_key
FROM single_table
WHERE some_expr
)

index_subquery

类似于 unique_subquery,其取代了 IN 子查询,但只适用于以下形式的子查询中的非唯一索引:

1
2
3
4
5
value IN (
SELECT key_column
FROM single_table
WHERE some_expr
)

range

在给定范围内检索、使用索引选择行,在输出行中的键列指示使用的索引,key_len 包含使用过的最长的键部分。 此类型的 ref 列为 NULL。

当使用比较运算符、IS NULL、BETWEEN、LIKE 或 IN 等运算符将键列与常量比较时适用:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SELECT * 
FROM tbl_name
WHERE key_column = 10;

SELECT *
FROM tbl_name
WHERE key_column BETWEEN 10 and 20;

SELECT *
FROM tbl_name
WHERE key_column IN (10,20,30);

SELECT *
FROM tbl_name
WHERE key_part1 = 10 AND key_part2 IN (10,20,30);

index

当查询仅使用属于单个索引的列时,扫描索引树有两种方式:

  • 如果覆盖索引,则只扫描索引树。此时 Extra 列显示使用索引。由于索引的数据比表的数据少,这种情况 比 all 快。

  • 按索引顺序查找数据行,并全表扫描。使用的索引不在 Extra 列中出现。

all

性能最差的全表扫描,可通过添加索引来避免,索引支持基于常量值或早期表中的列值从表中检索行。


如果打开 optimizer_trace,再执行 SQL 就可以查询 information_schema.OPTIMIZER_TRACE 表查看执行计划,最后可以关闭 optimizer_trace 功能:

1
2
3
4
5
6
7
8
9
10
SET optimizer_trace="enabled=on";

SELECT *
FROM person
WHERE NAME >'name84059' AND create_time>'2020-01-24 05:00:00';

SELECT *
FROM information_schema.OPTIMIZER_TRACE;

SET optimizer_trace="enabled=off";

可见不同语句的执行成本:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
EXPLAIN 
SELECT *
FROM person
WHERE NAME='name1';
# "range_scan_alternatives": [
# {
# "index": "name_score",
# "ranges": [
# "name1 <= name <= name1"
# ] /* ranges */,
# "index_dives_for_eq_ranges": true,
# "rowid_ordered": false,
# "using_mrr": false,
# "index_only": false,
# "rows": 1,
# "cost": 2.21,
# "chosen": true
# }
# ]

EXPLAIN
SELECT NAME,SCORE
FROM person
WHERE NAME='name1';
# analyzing_range_alternatives": {
# "range_scan_alternatives": [
# {
# "index": "name_score",
# "ranges": [
# "name1 <= name <= name1"
# ] /* ranges */,
# "index_dives_for_eq_ranges": true,
# "rowid_ordered": false,
# "using_mrr": false,
# "index_only": true,
# "rows": 1,
# "cost": 1.21,
# "chosen": true
# }
# ]

关于 MySQL 优化器的工作,可以参考:MySQL 基于规则的优化

索引优化

对于一个查询语句,选择索引的过程发生在优化器。强制指定索引分析查询结果:

1
2
3
4
EXPLAIN
SELECT *
FROM T FORCE INDEX(a)
WHERE a BETWEEN 10000 AND 20000;

根据词法分析结果,得出候选的索引列表,依次判断每个索引需要扫描的行数。

如使用 FORCE INDEX 指定的索引在候选列表中,则直接选择该索引,不评估执行代价。

生产环境代码中不建议使用 FORCE INDEX,迁移到别的数据库可能不兼容、且不好维护。

索引选取规则

在执行语句前不能准确知道查询包含记录数,只能根据统计信息估算。

参考依据是区分度,即索引上不同的值的数量。可查看索引的统计基数(Cardinality):

1
SHOW INDEX;

基数(Cardinality):InnoDB 执行采样统计。

  • 默认选择 N 个数据页,统计页面上不同的值,求出平均值后乘上页面数。

  • 每当变更的数据行数超过 1/M 时,会自动触发重新统计。

抽样模式可以通过 innodb_stats_persistent 指定:

  • on:统计信息持久化到磁盘,默认 N = 20,M = 10。

  • off:统计信息只存储在内存,默认 N = 8,M = 16。

实际查询中,统计扫描行数还要加上 回表 的代价。当统计信息不准确可修正:

1
ANALYZE TABLE t;

其它选取规则:是否使用临时表、排序方式等。

一般情况下不需要自己计算索引的基数等统计信息,可以使用 SHOW INDEX FROM t 查看。

同理,要查看访问某个表的统计信息可以使用 SHOW TABLE STATUS LIKE 't'\G

关于查询成本分析,可参考:MySQL 基于成本的优化

查询条件优化

避免函数或表达式

比如对于交易记录表:

1
2
3
4
5
6
7
8
9
CREATE TABLE `trade_log` (
`id` int(11) NOT NULL,
`trade_id` varchar(32) DEFAULT NULL,
`operator` int(11) DEFAULT NULL,
`t_modified` datetime DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `trade_id` (`trade_id`),
KEY `t_modified` (`t_modified`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

查询 7 月份的交易记录总数:

1
2
3
SELECT COUNT(*) 
FROM trade_log
WHERE MONTH(t_modified) = 7;

由于条件字段为函数调用,则无法使用索引。

优化器可选择遍历主键索引或 t_modified 索引。经过对比后发现索引 t_modified 更小,比遍历主键索引更快,因此选择 t_modified。

由于 MONTH() 函数操作可能导致了全索引扫描(区别于精确查找某值,在遍历树时调用函数可能会破坏索引值的有序性,优化器会放弃优化)。

要用上索引的快速定位能力,应把 SQL 语句改成基于字段本身的范围查询。

1
2
3
4
5
6
SELECT COUNT(*) 
FROM trade_log
WHERE
(t_modified >= '2016-7-1' AND t_modified<'2016-8-1') OR
(t_modified >= '2017-7-1' AND t_modified<'2017-8-1') OR
(t_modified >= '2018-7-1' AND t_modified<'2018-8-1');

即使是不改变值的有序性的函数或表达式,优化器也会放弃使用索引。比如 SELECT * FROM trade_log WHERE id + 1 = 10000; 不会改变有序性,但是优化器还是不能用 id 索引快速定位到 9999。

因此在写 SQL 语句时要改写成 WHERE id = 10000 - 1(前者类型为 all,后者为 const)。

最好让索引列单独出现在表达式中。

避免类型转换和编码转换

对于上面的交易记录表,要取某条记录。

1
2
3
SELECT * 
FROM trade_log
WHERE trade_id = 110717;

交易编号 trade_id 上有索引,而 trade_id 的类型是 varchar(32),而参数是整型、需要做类型转换。该语句等价于:

1
2
3
SELECT * 
FROM trade_log
WHERE CAST(trade_id AS signed int) = 110717;

规则是当字符串与数字比较时会将字符串转换成数字,应该先自行转换好类型再传参。

又比如引入交易记录细节表:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
CREATE TABLE `trade_detail` (
`id` int(11) NOT NULL,
`trade_id` varchar(32) DEFAULT NULL,
`trade_step` int(11) DEFAULT NULL, /*操作步骤*/
`step_info` varchar(32) DEFAULT NULL, /*步骤信息*/
PRIMARY KEY (`id`),
KEY `trade_id` (`trade_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO trade_log VALUES(1, 'aaaaaaaa', 1000, now());
INSERT INTO trade_log VALUES(2, 'aaaaaaab', 1000, now());
INSERT INTO trade_log VALUES(3, 'aaaaaaac', 1000, now());
INSERT INTO trade_detail VALUES(1, 'aaaaaaaa', 1, 'add');
INSERT INTO trade_detail VALUES(2, 'aaaaaaaa', 2, 'update');
INSERT INTO trade_detail VALUES(3, 'aaaaaaaa', 3, 'commit');
INSERT INTO trade_detail VALUES(4, 'aaaaaaab', 1, 'add');
INSERT INTO trade_detail VALUES(5, 'aaaaaaab', 2, 'update');
INSERT INTO trade_detail VALUES(6, 'aaaaaaab', 3, 'update again');

要查询 id=2 的交易的所有操作步骤信息:

1
2
3
SELECT d.* 
FROM trade_log l, trade_detail d
WHERE d.trade_id=l.trade_id AND l.id=2;
  • 优化器先在 trade_log 上使用主键索引查到 id=2 的行,rows=1 表示只扫描一行。

  • 由于没用上 trade_detail 上的 trade_id 索引(key=NULL),了全表扫描。

在执行计划中,从 trade_log 表(驱动表)中取 trade_id 字段再去 trade_detail 表(被驱动表)查询匹配字段。

由于两表字符编码不同,关联字段在做比较时,会先把 utf8 字符串转成 utf8mb4(utf8 的超集)再做比较。上述语句等价于:

1
2
3
SELECT * 
FROM trade_detail
WHERE CONVERT(trade_id USING utf8mb4)=$L2.trade_id.value;

可以考虑把字段的字符集统一:

1
2
ALTER TABLE trade_detail 
MODIFY trade_id varchar(32) CHARACTER SET utf8mb4 DEFAULT NULL;

如果数据量大不便于改表,可修改查询语句:

1
2
3
SELECT d.* 
FROM trade_log l, trade_detail d
WHERE d.trade_id=CONVERT(l.trade_id USING utf8) AND l.id=2;

合理安排索引顺序

根据 选择性:即不重复的索引值和记录总数的比值。

  • 最大值为 1,即每条记录都有唯一的索引与其对应,相应存储占用空间最大。

  • 选择性越高查询效率也越高,应让选择性最强的索引列放在前面。

比如:

1
2
3
4
5
SELECT     
COUNT(DISTINCT staff_id)/COUNT(*) AS staff_id_selectivity,
COUNT(DISTINCT customer_id)/COUNT(*) AS customer_id_selectivity,
COUNT(*)
FROM payment;
1
2
3
staff_id_selectivity: 0.0001
customer_id_selectivity: 0.0373
COUNT(*): 16049

customer_id 选择性比 staff_id 更高,应把 customer_id 列放在多列索引前面。

当多列索引的区分度都很低时,会触发 索引合并

范围条件后置

尽可能将范围查询的 字段放在最后

  • 最左索引遇到第⼀个范围查询后就不再继续。

  • 在范围查询中,后面的条件无法使用索引。

不要滥用 IN 语句,MySQL 会将多个 IN 转化成 inA * inB * inC 种组合,执⾏计划需要检查所有组合,可能会给系统造成巨大压力。

排序优化

排序使用到索引,在执行计划中的体现是 EXPLAIN 语句的 key 列。

如果没有用到索引,在 Extra 可见 Using filesort,表示使用内存或磁盘排序(由 sort_buffer_size 和排序数据大小决定)。

排序无法使用到索引的情况:

  • 对于使用联合索引排序的场景,多个字段排序 ASC 和 DESC 混用。

  • a+b 作为联合索引,按照 a 范围查询后按照 b 排序。

  • 排序列涉及到的多个字段不属于同一个联合索引。

  • 排序列使用表达式。

一般情况下,能利用上二级索引匹配记录都是有序的,即顺序 I/O;而以取得的主键在聚簇索引上匹配则为随机 I/O。如果在二级索引上取得大量主键再到聚簇索引上大量进行随机 I/O,其效率很可能比直接在聚簇索引上全表扫描更低。

需要回表的记录数越多,优化器越倾向于选择全表扫描(遍历聚簇索引 + 文件排序),否则倾向于二级索引 + 回表(比如使用 LIMIT 限制记录数)。


因此选择索引时主要考虑两方面:

  • 针对用于搜索、排序或分组的列(结果集里的列则没有必要,除非能利用覆盖索引)。

  • 考虑列的基数(选择性),列的基数越小,该列中的值越集中,排序、快速查找效果差(见“合理安排索引顺序”)。

设计维护

主键索引

使用自增 ID:插入新记录时不指定 ID 值,系统会获取当前 ID 最大值加 1 作为下一条记录的 ID 值。

  • 高效插入:每次插入新记录都是顺序追加,不影响其他记录,不触发叶节点分裂。如果用业务字段做主键,则不容易保证有序插入,写成本相对较高。

  • 节省空间:非主键索引的叶节点上是主键的值。如果用业务字段做主键,每个二级索引叶节点占用更多字节,而采用整型做主键则只要 4bytes(长整型 bigint 8bytes)。

使用业务字段:当表只有一个唯一索引,即 K-V 场景(无需考虑二级索引叶节点大小),将业务字段设置为主键可避免每次都查询两棵树。

联合索引

联合索引 (a, b, c) 依次按三个字段排序,即对记录按 a 排序,只有当 a 的值相同才比较 b,当 b 的值相同才比较 c。

尤其当使用多个列作为查询条件,使用多列索引比使用多个单列索引性能更好(毕竟只访问一棵树)。

  • 安排索引字段顺序时考虑索引复用能力,如 通过优化查询语句减少索引,则优先考虑采用。

  • 从左到右使用,同时要避免冗余(即无需创建 (a) 和 (a, b))。由于不能越过 a 只使用 (b, c),所以不能被 (b, c) 复用,无法利用联合索引,需要单独创建。

  • 节省空间,类型较小的字段优先。

  • 只允许顺序访问,对上述字段排倒序则无法利用索引。

比如把 actor_id 和 film_id 设置为多列索引:

1
2
3
SELECT film_id, actor_ id 
FROM sakila.film_actor
WHERE actor_id = 1 AND film_id = 1;

覆盖索引

索引中包含所有待查询字段的值,查询时只需要过滤二级索引,不需要回表、向引擎请求主键索引。

  • 相比起整行读取,只读取索引能大大减少数据访问量。

  • 一些存储引擎(MyISAM)在内存中只缓存索引,数据依赖于操作系统缓存。只访问索引可不经过系统调用(通常比较费时)。

  • 对于 InnoDB 引擎,若二级索引能覆盖查询,则无需访问主键索引。

比如:

1
2
3
SELECT ID
FROM T
WHERE k BETWEEN 3 AND 5;

由于待取出 ID 的值已经在 k 树的叶节点,在访问 k 树时无需把 ID 带到 ID 树中去查找。

前缀索引

对于 BLOB、TEXT 和 VARCHAR 等字符串类型的列,必须使用前缀索引,即只索引开始的部分字符。

需要根据索引选择性来确定前缀长度:

1
ALTER TABLE user ADD INDEX index1(email(6));

权衡记录扫描次数(区分度)和占用空间:数值越大表示区分度越高、损失比例越小,但占用空间越多,建议不小于 L * 95% 的值。

1
2
3
4
5
6
7
SELECT      
COUNT(DISTINCT email)AS L,
COUNT(DISTINCT LEFT(email, 4))AS L4,
COUNT(DISTINCT LEFT(email, 5))AS L5,
COUNT(DISTINCT LEFT(email, 6))AS L6,
COUNT(DISTINCT LEFT(email, 7))AS L7
FROM user;

使用合适长度的前缀索引,可节省空间又无需额外增加太多查询成本。

  • 不能与覆盖索引同用:使用前缀索引(即使长度 100% 覆盖,系统不确定前缀索引的定义是否截断了完整信息)可能由于截取了列,不能避免回表。

  • 对于 区分度不高 的字符串(比如身份证号前 n 位都相同),如果不需要范围查询,可以考虑两种方法:

    • 倒序存储:存储时可以 REVERSE 把数据倒过来写入,在查询时再反过来(优点是不需要额外空间、且函数计算开销较小)。

    • 增加 hash 字段:在表上再建一个字段、存放字符串的校验码,同时在这个字段上创建索引、使用 CRC32 函数计算代替前缀索引。优点是冲突概率小,每次查询平均扫描行数接近 1。

  • 对于前缀索引(比如前 10 个字符),由于无法对前一部分字符相同、后半字符不同的记录排序,即无法支持使用索引对完整字段排序。

锁优化

Innodb 会锁住所有返回给 MySQL 的数据,即使 MySQL 后⾯会⽤ WHERE 过滤。

应在二级索引上使用共享锁,在主键索引上使用排他锁。

空间维护

首先索引列的类型尽量小:在查询时比较效率较高;而且因为占用空间较小,在一个页内可放下更多记录,减少磁盘 I/O,即在内存缓存更多的页,加快读写效率。

及时清理 无必要的索引、节省空间,且 尽可能优化复用 现有索引以代替新建。

1
2
3
4
5
6
7
# 一般索引
ALTER TABLE T DROP INDEX k;
ALTER TABLE T ADD INDEX(k);

# 主键索引
ALTER TABLE T DROP PRIMARY KEY;
ALTER TABLE T ADD PRIMARY KEY(id);

不论是删除还是创建主键,都会将整个表重建,所以重建主键是不必要的:

1
ALTER TABLE T engine=InnoDB

InnoDB 索引容量容易膨胀,即使删除表部分记录,索引仍然存在、不会主动释放,只有重建表才能重建索引。

事务与锁优化

以上 SQL 优化和索引优化都是针对查询,对于增删改操作则涉及到事务与锁。

控制锁粒度

封锁粒度.jpg

全局锁

Flush tables with read lock(FTWRL) 可让整个库处于只读状态,阻塞其他线程,使数据更新(数据的增删改)、数据定义(包括建表、修改表结构等)语句和更新类事务的提交语句都无法执行。

  • 使用 mysqldump 备份时参数 -single-transaction 会启动事务,得到一致性视图。由于 MVCC 的支持该过程可以正常更新。但由于 MyISAM 等存储引擎不支持事务,只能以 FTWRL 的方式确保备份的一致性。

  • 不要使用 set global readonly=true 代替 FTWRL:一些系统使用 readonly 值判断库是主库/备库等功能,修改 global 变量的方式影响面更大。执行 FTWRL 后如果客户端发生异常断开,全局锁将自动释放;如果是 readonly 的库,则会一直保持状态,导致长时间不可写。

表级锁

lock tables ... read/write。可用 unlock tables 主动释放,或在客户端断开时自动释放。除了限制其它线程的读写外,也限定本线程接下来操作的对象。

另一类表级锁是元数据锁(MDL,MySQL 5.5+ 引入),在访问表时被自动加上:

  • 保证读写正确性。如某线程正在遍历表数据,另一线程对该表结构做变更,会导致查询结果对不上。

  • 读锁之间不互斥,可有多个线程同时对同一表增删查改。

  • 读写锁之间、写锁之间互斥,以保证变更表结构操作的安全性。当两个线程同时修改表结构,则其一要等待对方完成。

因此在修改表(尤其大表)结构时要避免对其它 CRUD 操作造成阻塞。当事务提交前会占用 MDL 锁,还要尽量避免长事务(在 information_schema 库的 innodb_trx 表可查)。

然而操作热点表时不能直接杀死事务,应考虑在 ALTER TABLE 语句中设定等待时间,获取 MDL 锁超时则失败、放弃,之后再通过重试命令重复该过程。

1
2
ALTER TABLE tbl_name NOWAIT ADD column ...
ALTER TABLE tbl_name WAIT N ADD column ...

行级锁

行锁在引擎层由各个引擎自行实现的。比如 MyISAM 引擎就不支持行锁,至少要对整表加锁。

在选择封锁粒度时,需要在锁开销和并发程度之间做权衡:

  • 尽量只锁定目标数据而非所有资源。锁定数据量越少,发生锁争用可能性越小,系统并发程度越高。

  • 加锁需消耗资源,各种锁操作(包括获取、释放、检查锁状态)会增加系统开销。因此封锁粒度越小,系统开销就越大。

间隙锁

间隙锁在 可重复读 下才生效,同时要解决可能造成数据和日志不一致问题,需要设置 binlog_format=row

1
2
3
SELECT c 
FROM t
WHERE c BETWEEN 10 AND 20 FOR UPDATE;

Record Locks 锁定一条记录上的索引而非记录本身,如果没有其它索引,则锁定聚簇索引。

而 Next-Key Locks 是 Record Locks 和 Gap Locks 的结合,除了锁定一条记录上的索引之外,也锁定索引之间的间隙。例如索引包含值 10, 11, 13, 20,需锁定以下区间:

1
2
3
4
5
(negative infinity, 10]
(10, 11]
(11, 13]
(13, 20]
(20, positive infinity)

间隙锁的引入可能导致更大的范围锁定,从而影响了并发度,因此只在必要时使用。

避免长事务

由于 MySQL 采用多版本并发控制(MVCC),当系统中保存大量事务视图,表示随时可能访问的事务期间的任何数据,在提交前要保留大量回滚记录,导致大量的空间占用;而且由于长时间占用锁资源,可能拖垮整个库。

可以在 information_schema 库的 innodb_trx 表中查询长事务,比如查询持续时间超过 60s 的事务:

1
2
3
SELECT * 
FROM information_schema.innodb_trx
WHERE TIME_TO_SEC(timediff(now(),trx_started))>60

手动提交事务

注意事务启动的方式:

  • 显式启动事务语句, begin 或 start transaction。配套的提交语句是 commit,回滚语句是 rollback。

  • set autocommit=0 事务手动提交。如果只执行一个查询语句、事务也会启动,而且不自动提交、持续存在直到主动执行提交或回滚语句,或者断开连接。

一些框架默认连接成功后先执行 set autocommit=0,查询操作在事务中,长连接会导致长事务。

建议指定 set autocommit=1 显式启动事务。此时用 begin 启动事务,用 commit 提交事务。

为避免大量重复的启动、提交逻辑,可使用 commit work and chain,即提交事务并自动启动下一个事务,可省去再次执行 begin 语句的开销;同时对于程序而言也能明确知道每个语句是否处于事务中。

避免死锁

等待超时

可通过参数 innodb_lock_wait_timeout 设置超时时间,InnoDB 默认 50s。随即其他线程才有可能继续执行。

但要避免设置成较小的值容易误伤其他普通锁。

死锁检测

将参数 innodb_deadlock_detect 设置为 on,发现死锁后主动回滚死锁中的某个事务,使其他事务继续执行。

  • 耗费大量 CPU 资源:每个被阻塞的线程都参与判断,即使最终检测的结果是安全,期间仍要消耗大量的 CPU 资源(可能表现为 CPU 占用很高,但 TPS 很低)。

  • 如确保业务不会出现死锁,可临时把死锁检测关闭,出现死锁时就直接回滚业务重试。

  • 并发控制:如果并发能控制住,比如同一行同时最多只有 10 个线程更新,死锁检测成本很低,就不会出现以上问题(可在中间件实现)。

逻辑拆分

可考虑将逻辑上的一行改成多行,减少锁冲突。

比如银行账户操作放在多条记录上,账户总额等于多条记录值的总和。每次为账户加金额时,随机选其中一条记录执行。每次冲突概率变成原来的 1/10,可减少锁等待个数,也减少了死锁检测的 CPU 消耗。

库表优化

水平分表

即 Sharding,将同一个表中的记录根据 ID (哈希、范围、映射表等策略)拆分到多个结构相同的表中。

当单表数据不断增多,可将数据分布到集群的不同节点上,从而缓存单个数据库的压力。

核心问题是唯一 ID 设计,常用以下方法:

  • 使用全局唯一 ID(GUID)。

  • 为每个分片指定一个 ID 范围。

  • 分布式 ID 生成器 (如 Twitter 的 Snowflake 算法)。

无论使用何种分表,都难免会遇到 数据热点数据倾斜 的问题,请结合业务场景选择合适的方式。

水平切分.jpg

垂直分表

将单表按列切分成多表,通常按照列的关系密切程度切分,也可利用垂直切分将经常被使用的列和不经常被使用的列切分到不同的表中。

在数据库层面使用垂直切分,将表部署到不同的库中。例如将原来的电商数据库垂直切分成商品数据库、用户数据库等,需要解决 分布式事务应用层连接 的问题。

垂直切分.jpg

读写分离

读写分离基于主从复制实现,主要涉及三个线程:binlog 线程、I/O 线程和 SQL 线程。

  • binlog 线程 :将主服务器上的数据更改写入二进制日志中。

  • I/O 线程 :从主服务器上读取二进制日志,写入从服务器的中继日志。

  • SQL 线程 :读取中继日志,解析出主服务器已执行的数据操作,在从服务器中执行。

master-slave.png

主服务器处理写操作以及实时性要求较高的读操作,而从服务器处理读操作。

  • 主从服务器负责各自的读和写,极大程度缓解了锁的争用;

  • 从服务器可使用 MyISAM,提升查询性能,节约系统开销;

  • 增加冗余,提高可用性。

通常以代理方式实现,代理服务器接收应用层传来的读写请求,再转发到具体服务器。

master-slave-proxy.png

分库中间件

  • Cobar:阿里 B2B 团队开发和开源,属于 Proxy 层方案,介于应用服务器和数据库服务器之间。应用程序通过 JDBC 驱动访问 cobar 集群,cobar 根据 SQL 和分库规则对 SQL 做分解,分发到 MySQL 集群不同的数据库实例上执行。不支持读写分离、存储过程、跨库 JOIN 和分页等操作。

  • TDDL:淘宝团队开发,属于 Client 层方案。支持基本的 CRUD 语法和读写分离,不支持 JOIN、多表查询等语法。目前使用不多,依赖于淘宝的 Diamond 配置管理系统。

  • Atlas:360 开源的 Proxy 层方案,基本上很少使用。

  • Sharding-JDBC:当当开源的 Client 层方案。SQL 语法支持也比较多,没有太多限制,支持分库分表、读写分离、分布式 id 生成、柔性事务(最大努力送达型事务、TCC 事务)。

  • Mycat:基于 Cobar 改造的 Proxy 层方案,支持的功能非常完善。

分区表

对业务透明,相对于用户分表来说使用分区表的业务代码更简洁。

使用分区表可方便地清理历史数据,如按照时间分区,可使用 ALTER TABLE t DROP PARTITION ... 删除历史数据分区(速度快,对系统影响小)。

1
2
3
4
5
6
7
8
9
10
11
CREATE TABLE `t` (
`ftime` datetime NOT NULL,
`c` int(11) DEFAULT NULL,
KEY (`ftime`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
PARTITION BY RANGE (YEAR(ftime))(
PARTITION p_2017 VALUES LESS THAN (2017) ENGINE = InnoDB,
PARTITION p_2018 VALUES LESS THAN (2018) ENGINE = InnoDB,
PARTITION p_2019 VALUES LESS THAN (2019) ENGINE = InnoDB,
PARTITION p_others VALUES LESS THAN MAXVALUE ENGINE = InnoDB
);

会在磁盘上生成文件:

1
2
3
4
5
t.frm
t#P#p_2017.ibd
t#P#p_2018.ibd
t#P#p_2019.ibd
t#P#p_other.ibd

对于存储引擎层是 4 个表,对于 Server 层是 1 个表。

间隙表

对于 InnoDB,使用间隙锁时在存储引擎层面只会针对区间所在的分区,而不会影响相邻的分区。

比如在表中插入两条数据:

1
INSERT INTO t VALUES('2017-4-1',1), ('2018-4-1',1);

在查询 SELECT... WHERE ftime='2017-5-1' FOR UPDATE 时会对这两条记录之间的间隙加锁。如果是普通表,加锁范围是 2017-4-1 ~ 2018-4-1;如果是分区表,则加锁范围是分区 p_2018 的 2017-4-1 ~ end,而不会影响分区 p_2019。

MyISAM 的表锁在引擎层实现,事务加表锁锁在分区上。因此值会阻塞该分区上的查询,而落在其他分区上的查询不会受影响。

分区策略

每当第一次访问某分区表时,MySQL 需要把所有分区都访问一遍(即使操作时只需要访问某一分区):如果一个表分区很多,MySQL 启动时 open_files_limit 参数使用的是默认值 1024,访问该表打开所有的文件,可能导致开启文件个数超过了上限而报错。

  • MyISAM 采用通用分区策略,每次访问分区都由 Server 层控制。该策略在 MySQL 一开始支持分区表时就存在,在文件管理、表管理的实现上很粗糙,有比较严重的性能问题(在 5.7.17+ 时,标记为即将弃用,8+ 完全弃用)。

  • InnoDB 采用本地分区策略(MySQL 5.8.9+),在 InnoDB 内部自行管理打开分区的行为。

Server 层行为:

  • MySQL 在第一次打开分区表时需要访问所有的分区。

  • Server 层认为是同一张表,所有分区共用同一个 MDL 锁。

  • 引擎层认为是不同的表,MDL 锁之后的操作会根据分区表规则,只访问必要的分区(如果条件中没有分区的 key,则访问所有分区)。

应用场景

使用细则:

  • 不是分得越细越好。单表或单分区数据千万级(只要没有特别大的索引)也只属于小表,要考虑跨分区查询对性能的负面影响(分表同理)。相比起分区更应考虑优化访问方式或减少数据量。

  • 不要提前预留太多,可在使用前预先创建。比如按月分区,在年底创建明年的 12 个分区,没有数据的历史分区要及时删除。

数据类型

选择的基本原则:

  • 越小越好:选择范围尽可能⼩的类型,⼀般情况下默认⽤ INT 即可,特意选用⽤ TINYINT 效果也不明显;

  • 越简单越好:整型比字符串好(后者需要校对、复杂的排序),内建的时间类型比字符串好(时间戳可以⾃动更新、⽀持时区区,但相比器 DATETIME 支持的时间较短);

  • 尽量避免允许 NULL(会让索引创建、统计和比较更复杂)。

整型
  • TINYINT(8 bit),SMALLINT(16 bit),MEDIUMINT(24 bit),INT(32 bit),BIGINT(64 bit),一般情况下越小的列越好。

  • INT(11) 数字只规定交互工具显示字符个数,对于存储和计算而言没有意义。

浮点型

  • FLOAT(32 bit) 和 DOUBLE(64 bit) 为浮点类型,DECIMAL 为高精度小数类型。

  • CPU 原生支持浮点运算,不支持 DECIMAl 类型计算,因此前者效率更高。

  • FLOAT、DOUBLE 和 DECIMAL 都可以指定列宽,例如 DECIMAL(18, 9) 表示总共 18 位,取 9 位存储小数部分,剩下 9 位存储整数部分。

  • 在数据量很⼤时可以考虑放大(比如单位从元变为分,*100)转为整型存储(BIGINT)。

字符串

主要有 CHAR(定长)和 VARCHAR(变长),以及 BLOB 和 TEXT。

  • VARCHAR 只需存储必要内容,可节省空间,在执行 UPDATE 时可能会使行变得更长。超出单页容纳量时,要执行额外操作:

    • MyISAM 将行拆成不同片段存储。

    • InnoDB 分裂页来使行放进页内。

  • 在存储和检索时 VARCHAR 会保留末尾空格,而 CHAR 会删除末尾的空格。

  • 取值范围较小且固定时,可使用枚举代替字符串。MySQL ⽤整数表示枚举,映射关系保存在 frm ⽂件中,和 CREATE 在⼀块。

MySQL 还支持位类型,但将其看作字符串存储,应尽量避免使用。

时间日期

DATETIME TIMESTAMP
表示范围 1001 年到 9999 年 1970 年到 2038 年
占用空间 64 bit 32 bit
默认值 NULL 当前时间
时区相关 无关 有关

TIMESTAMP 空间效率更高,更建议使用。DATETIME 的优势是以可排序、无歧义的格式展示时间,比如 2008-01-16 223708,是 ANSI 标准定义的日期和时间表示方法。

反模式设计

树形表

请参考:树形表结构设计总结 · Kylo’s Notebook

比如论坛网站留言、权限资源管理等功能,需要解决以下问题:

  • 检索较长的分支,尤其当递归深度是无限的。

  • 数据量庞大,难以一次性加载到内存中用代码处理。

设计 查询子 查询树 插入 删除 引用完整性
邻接表 1 简单 困难 简单 简单
递归查询 1 简单 简单 简单 简单
枚举路径 1 简单 简单 简单 简单
嵌套集 1 困难 简单 困难 困难
闭包表 2 简单 简单 简单 简单
  • 邻接表最易于实现,但 MySQL 不支持 WITH 或 CONNECT BY PRIOR 递归查询,相对低效。

  • 枚举路径可直观展示祖先到后代的路径,但由于不能确保引用完整性,设计上非常脆弱。枚举路径也使数据存储冗余。

  • 嵌套集相对巧妙,但也不能确保引用完整性。在查询性能要求很高而对其他要求一般的场合使用。

  • 闭包表最通用,且允许一个节点属于多棵树。其要求额外的表存储关系,使用空间换时间的方案,减少操作过程中由冗余计算造成的消耗。

反范式

在范式化的数据库中为严格规范,相同字段只出现在⼀处。在复杂查询中需要多表关联,开销很大。

  • 冗余存储:如相同的字段存储在不同表中,目的是提高查询效率。但在更新时需要多重关联、多处更新。

  • 用时更新:对大型表的更新通常很慢,需要重写大量的行。可把要修改的字段设置默认为 NULL,在读取时填充(类似文档数据库)。

反缓存

主要的数据存储在内存,只有当内存放不下或需要持久化时才落盘。

参考

CATALOG
  1. 1. MySQL 性能优化基础
    1. 1.1. SQL 优化
      1. 1.1.1. 减少操作数据量
        1. 1.1.1.1. 只返回必要的列
        2. 1.1.1.2. 只返回必要的行
        3. 1.1.1.3. 缓存数据
        4. 1.1.1.4. 切分大查询
      2. 1.1.2. 优化聚合
        1. 1.1.2.1. 分解关联
        2. 1.1.2.2. 优化行数统计
        3. 1.1.2.3. 分组查询
      3. 1.1.3. 优化关联
        1. 1.1.3.1. 关联索引
        2. 1.1.3.2. 使用临时表
        3. 1.1.3.3. 哈希关联
      4. 1.1.4. EXPLAIN 分析
        1. 1.1.4.1. const
        2. 1.1.4.2. eq_ref
        3. 1.1.4.3. ref
        4. 1.1.4.4. fulltext
        5. 1.1.4.5. ref_or_null
        6. 1.1.4.6. index_merge
        7. 1.1.4.7. unique_subquery
        8. 1.1.4.8. index_subquery
        9. 1.1.4.9. range
        10. 1.1.4.10. index
        11. 1.1.4.11. all
    2. 1.2. 索引优化
      1. 1.2.1. 索引选取规则
      2. 1.2.2. 查询条件优化
        1. 1.2.2.1. 避免函数或表达式
        2. 1.2.2.2. 避免类型转换和编码转换
        3. 1.2.2.3. 合理安排索引顺序
        4. 1.2.2.4. 范围条件后置
        5. 1.2.2.5. 排序优化
      3. 1.2.3. 设计维护
        1. 1.2.3.1. 主键索引
        2. 1.2.3.2. 联合索引
        3. 1.2.3.3. 覆盖索引
        4. 1.2.3.4. 前缀索引
        5. 1.2.3.5. 锁优化
        6. 1.2.3.6. 空间维护
    3. 1.3. 事务与锁优化
      1. 1.3.1. 控制锁粒度
        1. 1.3.1.1. 全局锁
        2. 1.3.1.2. 表级锁
        3. 1.3.1.3. 行级锁
        4. 1.3.1.4. 间隙锁
      2. 1.3.2. 避免长事务
      3. 1.3.3. 手动提交事务
      4. 1.3.4. 避免死锁
        1. 1.3.4.1. 等待超时
        2. 1.3.4.2. 死锁检测
        3. 1.3.4.3. 逻辑拆分
    4. 1.4. 库表优化
      1. 1.4.1. 水平分表
      2. 1.4.2. 垂直分表
      3. 1.4.3. 读写分离
      4. 1.4.4. 分库中间件
      5. 1.4.5. 分区表
        1. 1.4.5.1. 间隙表
        2. 1.4.5.2. 分区策略
        3. 1.4.5.3. 应用场景
      6. 1.4.6. 数据类型
        1. 1.4.6.0.1. 整型
      7. 1.4.6.1. 浮点型
      8. 1.4.6.2. 字符串
      9. 1.4.6.3. 时间日期
    5. 1.4.7. 反模式设计
      1. 1.4.7.1. 树形表
      2. 1.4.7.2. 反范式
      3. 1.4.7.3. 反缓存
  2. 1.5. 参考