MySQL性能优化[实践篇]-索引合并与复合索引

上一篇创建索引的实践中,我们看到了索引给我们带来的性能提升是非常可观的。

我们上次创建的表结构非常简单,只有两三个字段,where子句查询条件只有一个字段。

实际应用场景中我们的表结构会更复杂,查询条件也会非常多。在多条件查询的情况下又如何才能用到索引呢,我们可以测试一下。

准备测试数据

创建表结构

1
2
3
4
5
create table tb_test(id int primary key auto_increment,
c1 char(1),
c2 char(1),
c3 char(1),
c4 char(1));

生成随机字符的函数

1
2
3
4
5
6
7
8
delimiter $$
# 生成随机字符串
create function rand_char() returns char(1)
begin
declare CHARS char(52) default 'abcdefghijklmnopqrstuvwxyz';
return substr(CHARS,floor(1+RAND()*52), 1);
end
$$

生成测试数据的存储过程

1
2
3
4
5
6
7
8
9
10
11
12
13
create procedure insert_tb_test(c int)
begin
declare i int default 0;
set autocommit = 0;
repeat
set i = i + 1;
insert into tb_test(c1,c2,c3,c4) values(rand_char(),rand_char(),rand_char(),rand_char());
until i = c end repeat;
set autocommit = 1;
end
$$
delimiter ;

调用存储过程生成一百万条测试数据:

1
call insert_tb_test(1000000);

现在的查询场景是select * from tb_test where c1=? and c2=? and c3=? and c4=?

没有索引的情况下我们试着查询一次:

1
2
mysql> select * from tb_test where c1='A' and c2='B' and c3='C' and c4='D';
Empty set (0.61 sec) # 0.61秒,很慢

我们可以为c1、c2、c3、c4分别创建一个索引或者为他们创建一个复合索引。

我们先来试试第一种方案。

多个单列索引

创建多个索引

1
2
3
4
alter table tb_test add index idx_tb_test_c1(c1);
alter table tb_test add index idx_tb_test_c2(c2);
alter table tb_test add index idx_tb_test_c3(c3);
alter table tb_test add index idx_tb_test_c4(c4);

创建索引后查询

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
mysql> select * from tb_test where c1='A' and c2='B' and c3='C' and c4='D';
Empty set (0.05 sec) # 花了 0.05秒
mysql> explain select * from tb_test where c1='A' and c2='B' and c3='C' and c4='D'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tb_test
partitions: NULL
type: index_merge
possible_keys: idx_tb_test_c1,idx_tb_test_c2,idx_tb_test_c3,idx_tb_test_c4
key: idx_tb_test_c2,idx_tb_test_c4,idx_tb_test_c1,idx_tb_test_c3 # 用上了四个索引
key_len: 4,4,4,4
ref: NULL
rows: 1
filtered: 100.00
Extra: Using intersect(idx_tb_test_c2,idx_tb_test_c4,idx_tb_test_c1,idx_tb_test_c3); Using where; Using index
1 row in set, 1 warning (0.00 sec)

我们在explain输出的内容上可以看到type为index_merge,并且附加信息上提示Using intersect(idx_tb_test_c2,idx_tb_test_c4,idx_tb_test_c1,idx_tb_test_c3);

这是MySQL5.0版后引入的的“索引合并”策略:它将每个索引查询的结果进行合并

合并算法有三种intersect(交集)、union(并集)、sort_union(不常见)。

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
mysql> explain select * from tb_test where c1='A' and c2='B' or c3='C' and c4='D'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tb_test
partitions: NULL
type: index_merge
possible_keys: idx_tb_test_c1,idx_tb_test_c2,idx_tb_test_c3,idx_tb_test_c4
key: idx_tb_test_c2,idx_tb_test_c1,idx_tb_test_c4,idx_tb_test_c3
key_len: 4,4,4,4
ref: NULL
rows: 2629
filtered: 100.00
Extra: Using union(intersect(idx_tb_test_c2,idx_tb_test_c1),intersect(idx_tb_test_c4,idx_tb_test_c3)); Using where
1 row in set, 1 warning (0.00 sec)
mysql> explain select * from tb_test where c1='A' and c2='B' and c3>'C' and c4<'D'\G # 范围查询
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tb_test
partitions: NULL
type: index_merge
possible_keys: idx_tb_test_c1,idx_tb_test_c2,idx_tb_test_c3,idx_tb_test_c4
key: idx_tb_test_c2,idx_tb_test_c1
key_len: 4,4
ref: NULL
rows: 1300
filtered: 25.00
Extra: Using intersect(idx_tb_test_c2,idx_tb_test_c1); Using where # 范围查询的列不会进行索引合并
1 row in set, 1 warning (0.00 sec)

在某些时候索引合并是一种优化策略,但实际上更多时候它也说明表上的索引建的很糟糕:

  • 当出现using intersect(多个AND条件),更应该创建相关列的复合索引,而不是多个独立的单列索引。
  • 当出现using union时,通常会耗费大量的CPU和内存资源用在缓存、排序、合并等操作上。特别是有的单列索引的选择性不高,需要合并扫描返回大量的数据。
  • 更重要的是,MySQL优化器不会将这些操作算入“查询成本”,优化器只关心随机页面的读取。从而低估查询成本,可能导致执行计划还不如直接进行全表扫描。

如果在explain中看到index_merge,应该好好检查表结构和查询语句是否是最有的。

也可以通过optimizer_switch变量来关闭索引合并功能(具体参考Switchable Optimizations)。

或者使用force indexignore index提示优化器强制使用或忽略某些索引(具体参考Index Hints)。

复合索引

删除之前的单列索引:

1
2
3
4
drop index idx_tb_test_c1 on tb_test;
drop index idx_tb_test_c2 on tb_test;
drop index idx_tb_test_c3 on tb_test;
drop index idx_tb_test_c4 on tb_test;

创建复合索引:

1
alter table tb_test add index idx_tb_test_c1_c2_c3_c4(c1,c2,c3,c4);

创建索引后查询:

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
mysql> select * from tb_test where c1='A' and c2='B' and c3='C' and c4='D'\G
Empty set (0.00 sec) # show profiles 显示只有0.00042275秒
mysql> explain select * from tb_test where c1='A' and c2='B' and c3='C' and c4='D'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tb_test
partitions: NULL
type: ref
possible_keys: idx_tb_test_c1_c2_c3_c4
key: idx_tb_test_c1_c2_c3_c4 # 用到的是复合索引
key_len: 16 # 索引长度为16,用到了复合索引的所有列
ref: const,const,const,const
rows: 1
filtered: 100.00
Extra: Using index
1 row in set, 1 warning (0.00 sec)
# 索引的使用与where条件的顺序没有任何关系,解析器将SQL解析成树后,这几个条件的层级是一样的
mysql> explain select * from tb_test where c3='A' and c1='B' and c4='C' and c2='D'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tb_test
partitions: NULL
type: ref
possible_keys: idx_tb_test_c1_c2_c3_c4
key: idx_tb_test_c1_c2_c3_c4
key_len: 16
ref: const,const,const,const
rows: 1
filtered: 100.00
Extra: Using index
1 row in set, 1 warning (0.00 sec)

很明显复合索引的速度和之前不是一个量级的。

选择合适的索引顺序

正确的索引顺序对查询性能也很重要。但是选择合适的索引顺序要从多方面考虑。

1. 范围查询放在索引最后面

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
mysql> select * from tb_test where c1='A' and c2<'B' and c3='C' and c4='D'\G
Empty set (0.01 sec) # show profiles显示消耗了0.01148575秒
mysql> explain select * from tb_test where c1='A' and c2<'B' and c3='C' and c4='D'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tb_test
partitions: NULL
type: range # 范围查询
possible_keys: idx_tb_test_c1_c2_c3_c4
key: idx_tb_test_c1_c2_c3_c4 # 用到了复合索引
key_len: 8 # 但是只用到了索引的一半
ref: NULL
rows: 18572
filtered: 1.00
Extra: Using where; Using index
1 row in set, 1 warning (0.00 sec)

上面这个查询只用到了索引的一半,其中c1列精确匹配,c2列范围查询。

只针对这个SQL语句,我们需要把索引的顺序调整为c1_c3_c4_c2

2. 索引列顺序尽量匹配order by子句的排序列顺序

之前说过B+树本身是有序的,使用B+树排序可以避免filesort。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> explain select * from tb_test where c1='A' and c2='B' order by c4,c3\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tb_test
partitions: NULL
type: ref # 精确匹配
possible_keys: idx_tb_test_c1_c2_c3_c4
key: idx_tb_test_c1_c2_c3_c4 # 使用的复合索引
key_len: 8 # 索引前两列精确匹配
ref: const,const
rows: 382
filtered: 100.00
Extra: Using where; Using index; Using filesort # 外排序
1 row in set, 1 warning (0.00 sec)

针对这个查询,索引的顺序应该设计为c1_c2_c4_c3

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
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
mysql> drop index idx_tb_test_c1_c2_c3_c4 on tb_test;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table tb_test add index idx_c1_c2_c4_c3(c1,c2,c4,c3);
Query OK, 0 rows affected (7.80 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> explain select * from tb_test where c1='A' and c2='B' order by c4,c3\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tb_test
partitions: NULL
type: ref # 精确匹配
possible_keys: idx_c1_c2_c4_c3
key: idx_c1_c2_c4_c3
key_len: 8 # 索引前两列精确匹配
ref: const,const
rows: 382
filtered: 100.00
Extra: Using where; Using index # 没有filesort
1 row in set, 1 warning (0.00 sec)
# 范围查询会导致后面的排序列用不上索引
mysql> explain select * from tb_test where c1='A' and c2<'B' order by c4,c3\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tb_test
partitions: NULL
type: range
possible_keys: idx_c1_c2_c4_c3
key: idx_c1_c2_c4_c3
key_len: 8
ref: NULL
rows: 18572
filtered: 100.00
Extra: Using where; Using index; Using filesort # 又变回filesort了
1 row in set, 1 warning (0.00 sec)
mysql> explain select * from tb_test where c1='A' and c2<'B' order by c2,c4,c3\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tb_test
partitions: NULL
type: range
possible_keys: idx_c1_c2_c4_c3
key: idx_c1_c2_c4_c3
key_len: 8
ref: NULL
rows: 18572
filtered: 100.00
Extra: Using where; Using index
1 row in set, 1 warning (0.00 sec)

3. 索引列顺序尽量匹配group by子句顺序

没有索引的情况下,group by子句执行filesort对数据进行排序,然后在进行分组。这个过程中可能会导致全表扫描并且创建中间临时表

Group By原理

order by子句相比,group by多了创建中间表和分组的操作,group by子句也是需要排序的。

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
mysql> drop index idx_c1_c2_c4_c3 on tb_test; # 删除索引
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show index from tb_test\G
*************************** 1. row ***************************
Table: tb_test
Non_unique: 0
Key_name: PRIMARY # 只剩下主键索引了
Seq_in_index: 1
Column_name: id
Collation: A
Cardinality: 900858
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
1 row in set (0.00 sec)
mysql> explain select c1,c2 from tb_test group by c1,c2\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tb_test
partitions: NULL
type: ALL # 全表扫描
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 998722 # 预估扫描99万多行数据
filtered: 100.00
Extra: Using temporary; Using filesort # 临时表;文件排序
1 row in set, 1 warning (0.00 sec)

创建索引后查询:

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
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
mysql> alter table tb_test add index idx_tb_test_c1_c2_c3_c4(c1,c2,c3,c4);
Query OK, 0 rows affected (7.55 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> explain select c1,c2 from tb_test group by c1,c2\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tb_test
partitions: NULL
type: range
possible_keys: idx_tb_test_c1_c2_c3_c4
key: idx_tb_test_c1_c2_c3_c4
key_len: 8 # 使用到索引的前两列
ref: NULL
rows: 727
filtered: 100.00
Extra: Using index for group-by # 使用索引执行group by子句
1 row in set, 1 warning (0.00 sec)
# 下面这个语句会用到c1,c2列进行精确匹配,c3,c4列用来分组
mysql> explain select count(c3) from tb_test where c1='A' and c2='B' group by c3,c4\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tb_test
partitions: NULL
type: ref
possible_keys: idx_tb_test_c1_c2_c3_c4
key: idx_tb_test_c1_c2_c3_c4
key_len: 8
ref: const,const
rows: 382
filtered: 100.00
Extra: Using where; Using index
1 row in set, 1 warning (0.00 sec)
# 如果group by的顺序与索引顺序不一致,group by子句仍会filesort并创建中间表
mysql> explain select count(c3) from tb_test where c1='A' and c2='B' group by c4,c3\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tb_test
partitions: NULL
type: ref
possible_keys: idx_tb_test_c1_c2_c3_c4
key: idx_tb_test_c1_c2_c3_c4
key_len: 8
ref: const,const
rows: 382
filtered: 100.00
Extra: Using where; Using index; Using temporary; Using filesort # 中间表;文件排序
1 row in set, 1 warning (0.00 sec)
# 范围查询也会导致索引后面的列用不上索引进行group by
mysql> explain select count(c3) from tb_test where c1='A' and c2<'B' group by c3,c4\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tb_test
partitions: NULL
type: range
possible_keys: idx_tb_test_c1_c2_c3_c4
key: idx_tb_test_c1_c2_c3_c4
key_len: 8
ref: NULL
rows: 18572
filtered: 100.00
Extra: Using where; Using index; Using temporary; Using filesort # 中间表;文件排序
1 row in set, 1 warning (0.00 sec)

4. 选择性最高的列放在索引最前面

关于选择索引顺序有个经验:将选择性最高的列作为索引的最前列。在某些场景下确实很有帮助,但通常不如避免随机IO和排序那么重要。(很多时候需要全方面的考虑索引列的顺序)。

当不考虑排序和分组的时候,将选择性最高的列放在索引最前列通常是非常好的。索引的顺序将会优化where条件的查找:这样设计的索引能最快的过滤出需要的行

1
create table tb_user(id int primary key auto_increment, age tinyint, gender tinyint, name varchar(16));

比如用户表中有性别、年龄、姓名这几列。很明显将性别作为索引最前列是不可取的,因为性别总共就两种。

下节待续:

假设某个表有一个联合索引(c1,c2,c3,c4),问以下查询中只能使用该联合索引的c1,c2,c3部分的有那些

  1. where c1=x and c2=x and c4>x and c3=x
  2. where c1=x and c2=x and c4=x order by c3
  3. where c1=x and c4=x group by c3,c2
  4. where c1=? and c5=? order by c2,c3
  5. where c1=? and c2=? and c5=? order by c2,c3

参考链接:

《高性能MySQL》

https://dev.mysql.com/doc/refman/5.7/en/index-merge-optimization.html

https://dev.mysql.com/doc/refman/5.7/en/multiple-column-indexes.html