您现在的位置是：网站首页> 编程资料编程资料

MySQL复合索引的深入探究_Mysql_

2023-05-27 382人已围观

简介 MySQL复合索引的深入探究_Mysql_

复合索引(又称为联合索引)，是在多个列上创建的索引。创建复合索引最重要的是列顺序的选择，这关系到索引能否使用上，或者影响多少个谓词条件能使用上索引。复合索引的使用遵循最左匹配原则，只有索引左边的列匹配到，后面的列才能继续匹配。本文主要探究复合索引的创建顺序与使用情况。 

（一）复合索引的概念

在单个列上创建的索引我们称为单列索引，在2个以上的列上创建的索引称为复合索引。在单个列上创建索引相对简单，通常只需要考虑列的选择率即可，选择性越好，代表数据越分散，创建出来的索引性能也就更好。通常，某列选择率的计算公式为：
selectivity = 施加谓词条件后返回的记录数 / 未施加谓词条件后返回的记录数
可选择率的取值范围是(0,1]，值越小，代表选择性越好。
对于复合索引(又称为联合索引)，是在多个列上创建的索引。创建复合索引最重要的是列顺序的选择，这关系到索引能否使用上，或者影响多少个谓词条件能使用上索引。复合索引的使用遵循最左匹配原则，只有索引左边的列匹配到，后面的列才能继续匹配。

（二）什么情况下会使用复合索引的列

复合索引遵循最左匹配原则，只有索引中最左列匹配到，下一列才有可能被匹配。如果左边列使用的是非等值查询，则索引右边的列将不会被查询使用，也不会被排序使用。

实验：哪些情况下会使用到复合索引

 复合索引中的哪些字段被使用到了，是我们非常关心的问题。网络上一个经典的例子：

 -- 创建测试表 CREATE TABLE t1( c1 CHAR(1) not null, c2 CHAR(1) not null, c3 CHAR(1) not null, c4 CHAR(1) not null, c5 CHAR(1) not null )ENGINE innodb CHARSET UTF8; -- 添加索引 alter table t1 add index idx_c1234(c1,c2,c3,c4); --插入测试数据 insert into t1 values('1','1','1','1','1'),('2','2','2','2','2'), ('3','3','3','3','3'),('4','4','4','4','4'),('5','5','5','5','5');

 需要探索下面哪些查询语句使用到了索引idx_c1234，以及使用到了索引的哪些字段？

(A) where c1=? and c2=? and c4>? and c3=?

(B) where c1=? and c2=? and c4=? order by c3

(C) where c1=? and c4=? group by c3,c2

(D) where c1=? and c5=? order by c2,c3

(E) where c1=? and c2=? and c5=? order by c2,c3

(F) where c1>? and c2=? and c4>? and c3=?

A选项：

 mysql> explain select c1,c2,c3,c4,c5 from t1 where c1='2' and c2='2' and c4>'1' and c3='2'; +----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+ | 1 | SIMPLE | t1 | NULL | range | idx_c1234 | idx_c1234 | 12 | NULL | 1 | 100.00 | Using index condition | +----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+

使用的索引长度为12，代表4个字段都使用了索引。由于c1、c2、c3都是等值查询，所以后面的c4列也可以用上。

注：utf8编码，一个索引长度为3，这里12代表4个字段都用到该索引。

B选项：

 mysql> explain select c1,c2,c3,c4,c5 from t1 where c1='2' and c2='2' and c4='2' order by c3; +----+-------------+-------+------------+------+---------------+-----------+---------+-------------+------+----------+-----------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+-----------+---------+-------------+------+----------+-----------------------+ | 1 | SIMPLE | t1 | NULL | ref | idx_c1234 | idx_c1234 | 6 | const,const | 1 | 20.00 | Using index condition | +----+-------------+-------+------------+------+---------------+-----------+---------+-------------+------+----------+-----------------------+

 使用的索引长度为6，代表2个字段使用了索引。根据最左使用原则，c1、c2使用了索引。因为查询中没有c3谓词条件，所以索引值使用到c2后就发生了中断，导致只使用了c1、c2列。这里SQL使用了order by排序，但是在执行计划Extra部分未有filesort关键字，说明在索引中按照c3字段顺序读取数据即可。

这里特别留意，虽然索引中的c3字段没有放在索引的最后，但是确实使用到了索引中c2字段的有序特性，因为执行计划的Extra部分未出现"fileasort"关键字。这是为什么呢？这里用到了MySQL5.6版本引入的Index Condition Pushdown (ICP) 优化。其核心思想是使用索引中的字段做数据过滤。我们来整理一下不使用ICP和使用ICP的区别：

如果没有使用ICP优化，其SQL执行步骤为：

1.使用索引列c1,c2获取满足条件的行数据。where c1='2' and c2='2'

2.回表查询数据，使用where c4='2'来过滤数据

3.对数据排序输出

如果使用了ICP优化，其SQL执行步骤为：

1.使用索引列c1,c2获取满足条件的行数据。where c1='2' and c2='2'

2.在索引中使用where c4='2'来过滤数据

3.因为数据有序，直接按顺序取出满足条件的数据

C选项：

 mysql> explain select c2,c3 from t1 where c1='2' and c4='2' group by c3,c2; +----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-----------------------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-----------------------------------------------------------+ | 1 | SIMPLE | t1 | NULL | ref | idx_c1234 | idx_c1234 | 3 | const | 2 | 14.29 | Using where; Using index; Using temporary; Using filesort | +----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-----------------------------------------------------------+

 使用的索引长度为3，代表1个字段使用了索引。根据最左使用原则，c1使用了索引。因为查询中没有c2谓词条件，所以索引值使用到c1后就发生了中断，导致只使用了c1列。该SQL执行过程为：

1.在c1列使用索引找到c1='2'的所有行，然后回表使用c4='2'过滤掉不匹配的数据
2.根据上一步的结果，对结果中的c3,c2联合排序，以便于得到连续变化的数据，同时在数据库内部创建临时表，用于存储group by的结果。

C选项扩展：

 mysql> explain select c2,c3 from t1 where c1='2' and c4='2' group by c2,c3; +----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+--------------------------+ | 1 | SIMPLE | t1 | NULL | ref | idx_c1234 | idx_c1234 | 3 | const | 2 | 14.29 | Using where; Using index | +----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+--------------------------+

 使用的索引长度为3，代表1个字段使用了索引。根据最左使用原则，c1使用了索引。

D选项：

 mysql> explain select c2,c3 from t1 where c1='2' and c5='2' order by c2,c3; +----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+------------------------------------+ | 1 | SIMPLE | t1 | NULL | ref | idx_c1234 | idx_c1234 | 3 | const | 2 | 14.29 | Using index condition; Using where | +----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+------------------------------------+

 使用的索引长度为3，代表1个字段都使用了索引。根据最左使用原则，c1使用了索引。因为查询中没有c2谓词条件，所以索引值使用到c1后就发生了中断，导致只使用了c1列。

D选项扩展：

 mysql> explain select c2,c3 from t1 where c1='2' and c5='2' order by c3,c2; +----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+----------------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+----------------------------------------------------+ | 1 | SIMPLE | t1 | NULL | ref | idx_c1234 | idx_c1234 | 3 | const | 2 | 14.29 | Using index condition; Using where; Using filesort | +----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+----------------------------------------------------+

 使用的索引长度为3，代表1个字段都使用了索引。根据最左使用原则，c1使用了索引。因为查询中没有c2谓词条件，所以索引值使用到c1后就发生了中断，导致只使用了c1列。

E选项：

 mysql> explain select c1,c2,c3,c4,c5 from t1 where c1='2' and c2='2' and c5='2' order by c2,c3; +----+-------------+-------+------------+------+---------------+-----------+---------+-------------+------+----------+------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+-----------+---------+-------------+------+----------+------------------------------------+ | 1 | SIMPLE | t1 | NULL | ref | idx_c1234 | idx_c1234 | 6 | const,const | 2 | 14.29 | Using index condition; Using where | +----+-------------+-------+------------+------+---------------+-----------+---------+-------------+------+----------+------------------------------------+

 使用的索引长度为6，代表2个字段都使用了索引。根据最左使用原则，c1、c2使用了索引。这里SQL使用了order by排序，但是在执行计划Extra部分未有filesort关键字，说明在索引中按照c3字段顺序读取数据即可(c2是常量)。

F选项：

 mysql> explain select c1,c2,c3,c4,c5 from t1 where c1>'4' and c2='2' and c3='2' and c4='1'; +----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+ | 1 | SIMPLE | t1 | NULL | range | idx_c1234 | idx_c1234 | 3 | NULL | 1 | 20.00 | Using index condition | +----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+

 使用的索引长度为3，代表1个字段都使用了索引。根据最左使用原则，c1使用了索引。这里c1使用了不等值查询，导致后面的c2查询无法使用索引。该案例非常值得警惕，谓词条件中含有等值查询和范围查询时，如果范围查询在索引前面，则等值查询将无法使用索引；如果等值查询在前面，范围查询在后面，则都可以使用到索引。

（三）如何创建复合索引

复合索引创建的难点在于字段顺序选择，我的观点如下：

  • 如果存在等值查询和排序，则在创建复合索引时，将等值查询字段放在前面，排序放在最后面；
  • 如果存在多个等值查询，则选择性好的放在前面，选择性差的放在后面；
  • 如果存在等值查询、范围查询、排序。等值查询放在最前面，范围查询和排序需根据实际情况决定索引顺序；

此外，《阿里巴巴Java开发手册-2020最新嵩山版》中有几个关于复合索引的规约，我们可以看一下：

1.如果有order by的场景，请注意利用索引的有序性。order by后的字段是组合索引的一部

上一篇：mysql update case 更新字段值不固定的操作_Mysql_

下一篇：MySQL创建横向直方图的解决方案_Mysql_

相关内容

点击排行

本栏推荐

猜你喜欢

-六神源码网