自MySQL5.5版本起，主流的索引结构转为B+树。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字段。
那么这都是为什么呢？我们一起来看看其原理吧。
一、最左匹配原则的原理
主键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+树中的联合索引，每级索引都是排好序的。联合索引 bcd_index:(b,c,d) , 在索引树中的样子如图 ， 在比较的过程中 ，先判断 b 再判断 c 然后是 d 。
由上图可以看出，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会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配（包括like ‘陈%’这种）。在a、b走完索引后，c已经是无序了，所以c就没法走索引，优化器会认为还不如全表扫描c字段来的快。所以只使用了(a,b)两个索引，影响了执行效率。
其实，这种场景可以通过修改索引顺序为 abc_index:(a,c,b)，就可以使三个索引字段都用到索引，建议小伙伴们不要有问题就想着新增索引哦，浪费资源还增加服务器压力。
综上，如果通过调整顺序，就可以解决问题或少维护一个索引，那么这个顺序往往就是我们DBA人员需要优先考虑采用的。
四、需要你mark的知识点
1、如何通过有序索引排序，避免冗余执行order by
order by用在select语句中，具备排序功能。如：
SELECT sex, price, name FROM LOL ORDER BY sex; 是将表 LOL 中的数据按 “sex” 一列排序。
而只有当order by 与where 语句同时出现，order by的排序功能无效。换句话说，order by 中的字段在执行计划中利用了索引时，不用排序操作。如下SQL时，不会按 sex 一列排序，因为 sex 本身已经是有序的了。
SELECT sex, price, name FROM LOL where sex = 1 ORDER BY sex ; 所以，只有order by 字段出现在where条件中时,才会利用该字段的索引而避免排序。
对于上面的语句，数据库的处理顺序是：
- 第一步：根据where条件和统计信息生成执行计划，得到数据。
- 第二步：将得到的数据排序。当执行处理数据（order by）时，
数据库会先查看第一步的执行计划，看order by 的字段是否在执行计划中利用了索引。如果是，则可以利用索引顺序而直接取得已经排好序的数据。如果不是，则排序操作。 - 第三步：返回排序后的数据。
2、like 语句的索引问题
如果通配符 % 不出现在开头，则可以用到索引，但根据具体情况不同可能只会用其中一个前缀，在 like “value%” 可以使用索引，但是 like “%value%” 违背了最左匹配原则，不会使用索引，走的是全表扫描。
3、不要在列上进行运算
select * from user where YEAR(birthday) < 1990 可以改造成：
select * from users where birthday <’1990-01-01′ 4、索引不会包含有 NULL 值的列
只要列中包含有 NULL 值都将不会被包含在索引中，复合索引中只要有一列含有 NULL 值，那么这一列对于此复合索引就是无效的。所以在数据库设计时不要让字段的默认值为 NULL
5、尽量选择区分度高的列作为索引
区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是 1，而一些状态、性别字段可能在大数据面前区分度就是 0。一般需要 join 的字段都要求区分度 0.1 以上，即平均 1 条扫描 10 条记录
6、覆盖索引的好处
如果一个索引包含所有需要的查询的字段的值，我们称之为覆盖索引。覆盖索引是非常有用的工具，能够极大的提高性能。因为，只需要读取索引，而无需读表，极大减少数据访问量，这也是不建议使用Select * 的原因。
发布者：全栈程序员-站长，转载请注明出处：https://javaforall.net/208384.html原文链接：https://javaforall.net
