Mysql复合索引最左匹配原则详解

之前开发发起建表申请时，有时会创建复合索引，可是应用上线之后，数据库监控到很多慢sql，和开发一沟通，开发觉得写的sql应该走创建的复合索引呀，可是为什么不走呢？原来是开发人员没有理解Mysql复合索引最左匹配原则，在这里就详细解释一下什么是最左匹配原则。

mysql的最左原则，就是从左至右匹配，直到遇到(>，<，not in，<> ，like)就停止

首先创建一张测试表和一个复合索引，还有5条测试数据

CREATE TABLE `t_test2` (
  `id` int(11) NOT NULL,
  `depno` int(10) not null DEFAULT 0,
  `name` char(10) NOT NULL DEFAULT '',
  `dep_name` varchar(20) NOT NULL DEFAULT '',
  `c1` char(10) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `idx_t_test2_name_depno_dep_name` (`name`,`depno`,`dep_name`)
)ENGINE=InnoDB DEFAULT CHARSET=latin1;

insert into t_test2 values(1,101,'test1','101sfl','107sfl');
insert into t_test2 values(2,102,'eest1','102sfl','107sfl');
insert into t_test2 values(3,103,'test1','103sfl','107sfl');
insert into t_test2 values(4,104,'dest1','104sfl','104sfl');
insert into t_test2 values(5,105,'jest1','105sfl','105sfl');

在这里我的复合索引字段为（name,depno,dep_name），先测试三个字段都是等于的条件

mysql> explain select * from t_test2 where name= 'jest1' and depno =101 and dep_name='jf112l';
+----+-------------+---------+------------+------+---------------------------------+---------------------------------+---------+-------------------+------+----------+-------+
| id | select_type | table   | partitions | type | possible_keys                   | key                             | key_len | ref               | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------------------------+---------------------------------+---------+-------------------+------+----------+-------+
|  1 | SIMPLE      | t_test2 | NULL       | ref  | idx_t_test2_name_depno_dep_name | idx_t_test2_name_depno_dep_name | 36      | const,const,const |    1 |   100.00 | NULL  |
+----+-------------+---------+------------+------+---------------------------------+---------------------------------+---------+-------------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

写到这里，不知道你心里有没有一个疑问，执行计划中的索引dx_t_test2_name_depno_dep_name到底用到了几个字段，怎么能判断呢

我们可以通过执行计划里的key_len的值，来了解mysql到底匹配哪几个字段，在这里讲解一下key_len的含义

key_len：显示MySQL实际使用的索引的长度。如果索引为NULL，则长度也为NULL。如果不是NULL，则为使用的索引的长度。所以可以通过此字段推断出使用了哪个索引字段。
key_len的计算规则如下所示：
1.定长字段，int占用4个字节，date占用3个字节，char(n)占用n个字符。
2.变长字段varchar(n)，占用n个字符加两个字节。
3.需要注意的是，不同的字符集，一个字符占用的字节数是不同的。例如：Latin1编码的，一个字符占用一个字节，gdk编码的，一个字符占用两个字节，utf-8编码的，一个字符占用三个字节，而utf8mb4，一个字符则占用四个字节。
4.对于所有的索引字段，如果设置为NULL，则额外需要占用一个字节。

知道了key_len的计算规则，那来看看上面的执行计划，key_len的值为36是怎么计算出来。
首先字符集为latin1，name为char(10)，则key_len为10，dep_no为int(10)，则key_len为4，dep_name为varchar(20)，则key_len为22，三个字段的key_len总和为36，是不是就对应上了。

知道原理之后，下面看测试用例就非常简单了

mysql> explain select * from t_test2 where name= 'jest1' and depno >101 and dep_name='jf112l';
+----+-------------+---------+------------+-------+---------------------------------+---------------------------------+---------+------+------+----------+-----------------------+
| id | select_type | table   | partitions | type  | possible_keys                   | key                             | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+---------+------------+-------+---------------------------------+---------------------------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | t_test2 | NULL       | range | idx_t_test2_name_depno_dep_name | idx_t_test2_name_depno_dep_name | 14      | NULL |    2 |    10.00 | Using index condition |
+----+-------------+---------+------------+-------+---------------------------------+---------------------------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

key_len值为14，索引使用的字段为name和depno，因为遇到>，就停止了。
有同学会说，那>=呢，实践出真理，测试一下就知道结果

mysql> explain select * from t_test2 where name= 'jest1' and depno >=101 and dep_name='jf112l';
+----+-------------+---------+------------+-------+---------------------------------+---------------------------------+---------+------+------+----------+-----------------------+
| id | select_type | table   | partitions | type  | possible_keys                   | key                             | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+---------+------------+-------+---------------------------------+---------------------------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | t_test2 | NULL       | range | idx_t_test2_name_depno_dep_name | idx_t_test2_name_depno_dep_name | 36      | NULL |    2 |    10.00 | Using index condition |
+----+-------------+---------+------------+-------+---------------------------------+---------------------------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.01 sec)

看到没有key_len的值为36，就是说>=，复合索引是可以匹配的。

当查询条件中包含like时，得看like后面首个字符是否是通配符，如果是，则终止，如果不是，则复合索引是可以匹配的。

mysql> explain select * from t_test2 where name= 'jest1' and depno =101 and dep_name like 'jf112l%';
+----+-------------+---------+------------+-------+---------------------------------+---------------------------------+---------+------+------+----------+-----------------------+
| id | select_type | table   | partitions | type  | possible_keys                   | key                             | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+---------+------------+-------+---------------------------------+---------------------------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | t_test2 | NULL       | range | idx_t_test2_name_depno_dep_name | idx_t_test2_name_depno_dep_name | 36      | NULL |    1 |   100.00 | Using index condition |
+----+-------------+---------+------------+-------+---------------------------------+---------------------------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

mysql> 
mysql> explain select * from t_test2 where name= 'jest1' and depno =101 and dep_name like '%jf112l%';
+----+-------------+---------+------------+------+---------------------------------+---------------------------------+---------+-------------+------+----------+-----------------------+
| id | select_type | table   | partitions | type | possible_keys                   | key                             | key_len | ref         | rows | filtered | Extra                 |
+----+-------------+---------+------------+------+---------------------------------+---------------------------------+---------+-------------+------+----------+-----------------------+
|  1 | SIMPLE      | t_test2 | NULL       | ref  | idx_t_test2_name_depno_dep_name | idx_t_test2_name_depno_dep_name | 14      | const,const |    1 |    11.11 | Using index condition |
+----+-------------+---------+------------+------+---------------------------------+---------------------------------+---------+-------------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)