mysql复合索引最左原则原理

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

mysql的最左原则，就是从左至右匹配，直到遇到(>， explain ｓｅｌｅｃｔ * 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个字节，ｃｈａｒ(n)占用n个字符。2.变长字段varchar(n)，占用n个字符加两个字节。3.需要注意的是，不同的字符集，一个字符占用的字节数是不同的。例如：Latin1编码的，一个字符占用一个字节，gdk编码的，一个字符占用两个字节，utf-8编码的，一个字符占用三个字节，而utf8mb4，一个字符则占用四个字节。4.对于所有的索引字段，如果设置为NULL，则额外需要占用一个字节。

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

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

mysql> explain ｓｅｌｅｃｔ * 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 ｓｅｌｅｃｔ * 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 ｓｅｌｅｃｔ * 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 ｓｅｌｅｃｔ * 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)

好了，今天就写到这里，如果觉得对你有帮助，请加关注，点赞，分享或者留言。