联合索引最左原则原理_Mysql复合索引最左匹配原则详解

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

338da397be2ba0d4366fd62f9ca9980b.png

mysql的最左原则,就是从左至右匹配,直到遇到(>, ,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到底用到了几个字段,怎么能判断呢

5cb8fdb68a3bd78d7d9ae1d2a2e7aac1.png

我们可以通过执行计划里的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,就是说>=,复合索引是可以匹配的。

85de734d368b1ea7b0be76b4b9e9f2bc.png

当查询条件中包含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)

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

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值