| MySQL最左匹配原则，道儿上兄弟都得知道的原则 | 您所在的位置：网站首页 › 索引字段选择原则 › MySQL最左匹配原则，道儿上兄弟都得知道的原则 |
MySQL最左匹配原则，道儿上兄弟都得知道的原则
|
自MySQL5.5版本起，主流的索引结构转为B+树。B+树的节点存储索引顺序是从左向右存储，在检索匹配的时候也要满足自左向右匹配。 目录 一、最左匹配原则的原理二、违背最左原则导致索引失效的情况三、查询优化器偷偷干了哪些事儿四、需要你mark的知识点1、如何通过有序索引排序，避免冗余执行order by2、like 语句的索引问题3、不要在列上进行运算4、索引不会包含有 NULL 值的列5、尽量选择区分度高的列作为索引6、覆盖索引的好处 通常我们在建立联合索引的时候，相信建立过索引的同学们会发现，无论是Oracle还是 MySQL 都会让我们选择索引的顺序，比如我们想在a,b,c三个字段上建立一个联合索引，我们可以选择自己想要的优先级，(a、b、c)，或是 (b、a、c) 或者是(c、a、b) 等顺序。 为什么数据库会让我们选择字段的顺序呢？不都是三个字段的联合索引么？这里就引出了数据库索引的最重要的原则之一，最左匹配原则。 在我们开发中经常会遇到这种问题，明明这个字段建了联合索引，但是SQL查询该字段时却不会使用这个索引。难道这索引是假的？白嫖老子资源？！ 
比如索引abc_index:(a,b,c)是a,b,c三个字段的联合索引，下列sql执行时都无法命中索引abc_index； select * from table where c = '1'; select * from table where b ='1' and c ='2';以下三种情况却会走索引: select * from table where a = '1'; select * from table where a = '1' and b = '2'; select * from table where a = '1' and b = '2' and c='3';从上面两个例子大家有木有看出点眉目呢？ 是的，索引abc_index:(a,b,c)，只会在where条件中带有(a)、(a,b)、(a,b,c)的三种类型的查询中使用。其实这里说的有一点歧义，其实当where条件只有(a,c)时也会走，但是只走a字段索引，不会走c字段。 那么这都是为什么呢？我们一起来看看其原理吧。 一、最左匹配原则的原理MySQL 建立多列索引（联合索引）有最左匹配的原则，即最左优先： 如果有一个 2 列的索引 (a, b)，则已经对 (a)、(a, b) 上建立了索引； 如果有一个 3 列索引 (a, b, c)，则已经对 (a)、(a, b)、(a, b, c) 上建立了索引； 假设数据 表 LOL (id,sex,price,name) 的物理位置（表中的无序数据）如下： （注：下面数据是测试少量数据选用的，只为了方便大家看清楚。实际操作中，应按照使用频率、数据区分度来综合设定索引顺序喔~） 主键id sex(a) price(b) name(c) (1) 1 1350 AAA安妮 (2) 2 6300 MMM盲僧 (3) 1 3150 NNN奈德丽 (4) 2 6300 CCC锤石 (5) 1 6300 LLL龙女 (6) 2 3150 EEE伊泽瑞尔 (7) 2 6300 III艾克 (8) 1 6300 BBB暴走萝莉 (9) 1 4800 FFF发条魔灵 (10) 2 3150 KKK卡牌大师 (11) 1 450 HHH寒冰射手 (12) 2 450 GGG盖伦 (13) 2 3150 OOO小提莫 (14) 2 3150 DDD刀锋之影 (15) 2 6300 JJJ疾风剑豪 (16) 2 450 JJJ剑圣当你在LOL表创建一个联合索引 abc_index:(sex,price,name)时，生成的索引文件逻辑上等同于下表内容（分级排序）： sex(a) price(b) name(c) 主键id 1 450 HHH寒冰射手 (11) 1 1350 AAA安妮 (1) 1 3150 NNN奈德丽 (3) 1 4800 FFF发条魔灵 (9) 1 6300 BBB暴走萝莉 (8) 1 6300 LLL龙女 (5) 2 450 GGG盖伦 (12) 2 450 JJJ剑圣 (16) 2 3150 DDD刀锋之影 (14) 2 3150 EEE伊泽瑞尔 (6) 2 3150 KKK卡牌大师 (10) 2 3150 OOO小提莫 (13) 2 6300 CCC锤石 (4) 2 6300 III艾克 (7) 2 6300 JJJ疾风剑豪 (15) 2 6300 MMM盲僧 (2)小伙伴儿们有没有发现B+树联合索引的规律？感觉还有点模糊的话，那咱们再来看一张索引存储数据的结构图，或许更明了一些。
由上图可以看出，B+ 树的数据项是复合的数据结构，同样，对于我们这张表的联合索引 (sex,price,name)来说 ，B+ 树也是按照从左到右的顺序来建立搜索树的，当SQL如下时： select sex,price,name from LOL where sex = 2 and price = 6300 and name = 'JJJ疾风剑豪';B+ 树会优先比较 sex 来确定下一步的指针所搜方向，如果 sex 相同再依次比较 price 和 name，最后得到检索的数据； 二、违背最左原则导致索引失效的情况（下面以联合索引 abc_index:(a,b,c) 来进行讲解，便于理解） 1、查询条件中，缺失优先级最高的索引 “a” 当 where b = 6300 and c = 'JJJ疾风剑豪' 这种没有以 a 为条件来检索时；B+树就不知道第一步该查哪个节点，从而需要去全表扫描了（即不走索引）。因为建立搜索树的时候 a 就是第一个比较因子，必须要先根据 a 来搜索，进而才能往后继续查询b 和 c，这点我们通过上面的存储结构图可以看明白。 2、查询条件中，缺失优先级居中的索引 “b” 当 where a =1 and c =“JJJ疾风剑豪” 这样的数据来检索时；B+ 树可以用 a 来指定第一步搜索方向，但由于下一个字段 b 的缺失，所以只能把 a = 1 的数据主键ID都找到，通过查到的主键ID回表查询相关行，再去匹配 c = ‘JJJ疾风剑豪’ 的数据了，当然，这至少把 a = 1 的数据筛选出来了，总比直接全表扫描好多了。 这就是MySQL非常重要的原则，即索引的最左匹配原则。 三、查询优化器偷偷干了哪些事儿当对索引中所有列通过"=" 或 “IN” 进行精确匹配时，索引都可以被用到。 1、如果建的索引顺序是 (a, b)。而查询的语句是 where b = 1 AND a = ‘陈哈哈’; 为什么还能利用到索引？ 理论上索引对顺序是敏感的，但是由于 MySQL 的查询优化器会自动调整 where 子句的条件顺序以使用适合的索引，所以 MySQL 不存在 where 子句的顺序问题而造成索引失效。当然了，SQL书写的好习惯要保持，这也能让其他同事更好地理解你的SQL。 2、还有一个特殊情况说明下，下面这种类型的SQL， a 与 b 会走索引，c不会走。 select * from LOL where a = 2 and b > 1000 and c='JJJ疾风剑豪';对于上面这种类型的sql语句；mysql会一直向右匹配直到遇到范围查询(>、 |
这是一张来自思否上的图片，层次感很清晰，小伙伴可以看到，对于B+树中的联合索引，每级索引都是排好序的。联合索引 bcd_index:(b,c,d) , 在索引树中的样子如图 ， 在比较的过程中 ，先判断 b 再判断 c 然后是 d 。【本文地址】