从头带你捋一遍 MySQL 索引结构( 五 ) _MySQL

文章插图

B+树：

文章插图

注意：此时由于是非聚簇索引，所以叶子节点不在有数据，而是存了一个主键索引，最终会通过主键索引来回表查询数据。
B+树的结构有了，就可以通过这个来理解最左前缀匹配原则了。
我们先写一个查询语句
SELECT*FROMuserWHEREage=1andheight=2andweight=7毋庸置疑，这条语句一定会走idx_obj这个索引。
那么我们再看一个语句：
SELECT*FROMuserWHEREheight=2andweight=7思考一下，这条SQL会走索引吗？
答案是否定的，那么我们分析的方向就是，为什么这条语句不会走索引。
上文中我们提到了一个多列的排序原则，是从左到右进行比较然后排序的，而我们的idx_obj这个索引从左到右依次是age,height,weight，所以当我们使用height和weight来作为查询条件时，由于age的缺失，那么就无法从age来进行比较了。
看到这里可能有小伙伴会有疑问，那如果直接用height和weight来进行比较不可以吗？
显然是不可以的，可以举个例子，我们把缺失的这一列写作一个问号，那么这条语句的查询条件就变成了?27，那么我们从这课B+树的根节点开始，根节点上有127和365，那么以height和weight来进行比较的话，走的一定是127这一边
但是如果缺失的列数字是大于3的呢？比如427，527，627，那么如果走索引来查询数据，将会丢失数据，错误查询。所以这种情况下是绝对不会走索引进行查询的。这就是最左前缀匹配原则的成因。

最左前缀匹配原则，MySQL会一直向右匹配直到遇到范围查询（>、<、between、like）就停止匹配，比如 a="3" and="" b="4" c="">5 and d=6,如果建立(a,b,c,d)顺序的索引，d是无法使用索引的，如果建立(a,b,d,c)的索引则都可以使用到，a、b、d的顺序可以任意调整。
=和in可以乱序，比如 a=1 and b=2 and c=3 建立(a,b,c)索引可以任意顺序，MySQL的查询优化器会帮你优化成索引可以识别的形式。

根据我们了解的可以得出结论：
只要无法进行排序比较大小的，就无法走联合索引。
可以再看几个语句：
SELECT*FROMuserWHEREage=1andheight=2这条语句是可以走idx_obj索引的，因为它可以通过比较 (12?<365) 。
SELECT*FROMuserWHEREage=1andweight=7这条语句也是可以走ind_obj索引的，因为它也可以通过比较(1?7<365)，走左子树，但是实际上weight并没有用到索引
因为根据最左匹配原则，如果有两页的age都等于1，那么会去比较height，但是height在这里并不作为查询条件，所以MySQL会将这两页全都加载到内存中进行最后的weight字段的比较，进行扫描查询。
SELECT*FROMuserwhereage>1这条语句不会走索引，但是可以走索引。这句话是什么意思呢？
这条SQL很特殊，由于其存在可以比较的索引，所以它走索引也可以查询出结果，但是由于这种情况是范围查询并且是全字段查询，如果走索引，还需要进行回表，MySQL查询优化器就会认为走索引的效率比全表扫描还要低，所以MySQL会去优化它，让他直接进行全表扫描。
SELECT*FROMuserWEHREage=1andheight>2andweight=7这条语句是可以走索引的，因为它可以通过age进行比较，但是weight不会用到索引，因为height是范围查找，与第二条语句类似，如果有两页的height都大于2，那么MySQL会将两页的数据都加载进内存，然后再来通过weight匹配正确的数据。
为什么InnoDB只有一个聚簇索引，而不将所有索引都使用聚簇索引？因为聚簇索引是将索引和数据都存放在叶子节点中，如果所有的索引都用聚簇索引，则每一个索引都将保存一份数据，会造成数据的冗余，在数据量很大的情况下，这种数据冗余是很消耗资源的。
补充两个关于索引的点这两个点也是上次写关于索引的博客时漏下的，这里补上。
1.什么情况下会发生明明创建了索引，但是执行的时候并没有通过索引呢？
科普时间：查询优化器一条SQL语句的查询，可以有不同的执行方案，至于最终选择哪种方案，需要通过优化器进行选择，选择执行成本最低的方案。
在一条单表查询语句真正执行之前，MySQL的查询优化器会找出执行该语句所有可能使用的方案，对比之后找出成本最低的方案。这个成本最低的方案就是所谓的执行计划。
优化过程大致如下：