热门标签
热门文章
- 1自然语言处理 Transformer 模型_transformer的mlp在哪
- 2ChatGPT当中的“GPT”是什么意思？
- 3matlab指纹图像分割介绍,基于MATLAB指纹图像预处理.doc
- 4HDD杭州站·HarmonyOS技术专家分享HUAWEI DevEco Studio特色功能_huawei deveco的优势
- 5华为Atlas200DK硬件--矿大人工智能实验（环境搭建+人像语义分割实现）_安装acllite库
- 6鸿蒙万物互联和谷歌的,谷歌也开始效仿华为鸿蒙，准备抛弃安卓，重新开发万物互联操作系统...
- 7总结-前端开发常用简单的js方法_前段开发常用js语句
- 8AI免费一键生成Word、PPT文档，让工作和学习更轻松、更高效、AI办公工具_自动生成word的ai
- 9百万级访问量—高并发问题的解决历程_前端十万用户访问量处理
- 105. ＜tag-数组模拟矩阵＞-lt.48- 旋转图像(全部解法) + lt.59-螺旋矩阵 II 1.5_tag矩阵
当前位置: article > 正文
MySQL性能优化[实践篇]-索引合并与复合索引_14.索引合并和复合索引
作者：不正经 | 2024-03-25 05:35:48
赞
踩
14.索引合并和复合索引
系列文章：
- MySQL性能优化[理论篇]-B树索引与hash索引
- MySQL性能优化[理论篇]-聚簇索引和非聚簇索引,InnoDB和MyISAM
- MySQL性能优化[准备篇]-慢查询日志
- MySQL性能优化[准备篇]-单条SQL性能剖析
- MySQL性能优化[实践篇]-索引合并与复合索引
- MySQL性能优化[实践篇]-复合索引实例
- MySQL性能优化[实践篇]-使用B树索引
- 分库分表的一些思考
从上一篇创建索引的实践中，我们看到了索引给我们带来的性能提升是非常可观的。
我们上次创建的表结构非常简单，只有两三个字段，where子句查询条件只有一个字段。
实际应用场景中我们的表结构会更复杂，查询条件也会非常多。在多条件查询的情况下又如何才能用到索引呢，我们可以测试一下。
准备测试数据
创建表结构
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
生成随机字符的函数
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
生成测试数据的存储过程
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
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
调用存储过程生成一百万条测试数据：
call insert_tb_test(1000000);
- 1
现在的查询场景是select * from tb_test where c1=? and c2=? and c3=? and c4=?
没有索引的情况下我们试着查询一次：
mysql> select * from tb_test where c1='A' and c2='B' and c3='C' and c4='D';
Empty set (0.61 sec) # 0.61秒，很慢
- 1
- 2
我们可以为c1、c2、c3、c4分别创建一个索引或者为他们创建一个复合索引。
我们先来试试第一种方案。
多个单列索引
创建多个索引
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
创建索引后查询
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)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
我们在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(不常见)。
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)
- 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
在某些时候索引合并是一种优化策略，但实际上更多时候它也说明表上的索引建的很糟糕：
- 当出现
using intersect(多个AND条件)，更应该创建相关列的复合索引，而不是多个独立的单列索引。 - 当出现
using union时，通常会耗费大量的CPU和内存资源用在缓存、排序、合并等操作上。特别是有的单列索引的选择性不高，需要合并扫描返回大量的数据。 - 更重要的是，MySQL优化器不会将这些操作算入“查询成本”，优化器只关心随机页面的读取。从而低估查询成本，可能导致执行计划还不如直接进行全表扫描。
如果在explain中看到index_merge，应该好好检查表结构和查询语句是否是最有的。
也可以通过optimizer_switch变量来关闭索引合并功能(具体参考Switchable Optimizations)。
或者使用force index、ignore index提示优化器强制使用或忽略某些索引(具体参考Index Hints)。
复合索引
删除之前的单列索引：
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
- 2
- 3
- 4
创建复合索引：
alter table tb_test add index idx_tb_test_c1_c2_c3_c4(c1,c2,c3,c4);
- 1
创建索引后查询：
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
- 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
很明显复合索引的速度和之前不是一个量级的。
选择合适的索引顺序
正确的索引顺序对查询性能也很重要。但是选择合适的索引顺序要从多方面考虑。
1. 范围查询放在索引最后面
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)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
上面这个查询只用到了索引的一半，其中c1列精确匹配，c2列范围查询。
只针对这个SQL语句，我们需要把索引的顺序调整为c1_c3_c4_c2。
2. 索引列顺序尽量匹配order by子句的排序列顺序
之前说过B+树本身是有序的，使用B+树排序可以避免filesort。
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)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
针对这个查询，索引的顺序应该设计为c1_c2_c4_c3。
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)
- 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
3. 索引列顺序尽量匹配group by子句顺序
没有索引的情况下，group by子句执行filesort对数据进行排序，然后在进行分组。这个过程中可能会导致全表扫描并且创建中间临时表。
和order by子句相比，group by多了创建中间表和分组的操作，group by子句也是需要排序的。
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
创建索引后查询：
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)
- 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
4. 选择性最高的列放在索引最前面
关于选择索引顺序有个经验：将选择性最高的列作为索引的最前列。在某些场景下确实很有帮助，但通常不如避免随机IO和排序那么重要。(很多时候需要全方面的考虑索引列的顺序)。
当不考虑排序和分组的时候，将选择性最高的列放在索引最前列通常是非常好的。索引的顺序将会优化where条件的查找：这样设计的索引能最快的过滤出需要的行。
create table tb_user(id int primary key auto_increment, age tinyint, gender tinyint, name varchar(16));
- 1
比如用户表中有性别、年龄、姓名这几列。很明显将性别作为索引最前列是不可取的，因为性别总共就两种。
下节待续：
假设某个表有一个联合索引(c1,c2,c3,c4)，问以下查询中只能使用该联合索引的c1,c2,c3部分的有那些
- where c1=x and c2=x and c4>x and c3=x
- where c1=x and c2=x and c4=x order by c3
- where c1=x and c4=x group by c3,c2
- where c1=? and c5=? order by c2,c3
- 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
声明：本文内容由网友自发贡献，转载请注明出处：【wpsshop】
推荐阅读
相关标签
