品牌网站建设哪家好,昆明网红,wordpress博客无法重定向,注册域名的注意事项MySQL的联合索引 联合索引的最左匹配原则会一直向右匹配直到遇到范围查询(、、between、like) 就会停止匹配。 这个结论并不全对#xff01;去掉 「between 和 like 」这个结论就没问题了
经过实验的证明#xff0c;我得出的结论是这样的#xff1a;
联合索引的最…MySQL的联合索引 联合索引的最左匹配原则会一直向右匹配直到遇到范围查询(、、between、like) 就会停止匹配。 这个结论并不全对去掉 「between 和 like 」这个结论就没问题了
经过实验的证明我得出的结论是这样的
联合索引的最左匹配原则在遇到范围查询如 、的时候就会停止匹配也就是范围查询的字段可以用到联合索引但是在范围查询字段后面的字段无法用到联合索引。但是对于 、、BETWEEN、like 前缀匹配这四种范围查询并不会停止匹配。 用几个实验例子来说明这个结论
BTree 索引
首先先来认识下 BTree 索引。
MySQL 的 InnoDB 存储引擎会为每一张数据库表创建一个「聚簇索引」来保存表的数据聚簇索引默认使用的是BTree 索引。
为了让大家理解 BTree 索引的存储和查询的过程接下来我通过一个简单例子说明一下 BTree 索引在存储数据中的具体实现。
假设有一张商品表表里有这些数据 这些数据存储在 BTree 索引时是长什么样子的
BTree 是一种多叉树叶子节点才存放数据非叶子节点只存放索引而且每个节点里的数据是按主键值id顺序存放的每一层父节点的索引值都会出现在下层子节点的索引值中因此在叶子节点中包括了所有的索引值信息并且每一个叶子节点都指向下一个叶子节点形成一个链表便于范围查询。
聚簇索引的 BTree 如图所示 假设执行了 select * from t_product where id 5查询语句该查询语句的条件是找到 id主键为 5 的这条记录。因为 BTree 是一个有序的数据结构所以可以通过二分查找算法快速定位到这条记录这也就是我们常说的索引查询具体过程如下
从根节点开始将 5 与根节点的索引数据 (11020) 比较5 在 1 和 10 之间根据二分查找算法找到第二层的索引数据 (147)在第二层的索引数据 (147)中进行查找因为 5 在 4 和 7 之间根据二分查找算法找到第三层的索引数据456在叶子节点的索引数据456中进行查找然后我们找到了索引值为 5 的这条记录。
聚簇索引只能用于主键字段的快速查询如果想实现「非主键字段」的快速查询我们就要针对「非主键字段」创建索引这种索引称作为「二级索引」。二级索引同样基于 BTree 实现的不过二级索引的叶子节点存放的是主键值不是实际数据。
我这里将前面的商品表中的 product_no 商品编码字段设置为二级索引那么二级索引的 BTree 如下图其中非叶子的索引值是 product_no图中橙色部分叶子节点存储的数据是主键值图中绿色部分。 如果我用 product_no 二级索引查询商品如下查询语句
select * from product where product_no 0002;
会先在二级索引的 BTree 中快速查找到 product_no 为 0002 的二级索引记录然后获取主键值然后利用主键值在主键索引的 BTree 中快速查询到对应的叶子节点然后获取完整的记录。这个过程叫「回表」也就是说要查两个 BTree 才能查到数据。如下图 不过当查询的数据是能在二级索引的 BTree 的叶子节点里查询到这时就不用再查主键索引查比如下面这条查询语句
select id from product where product_no 0002;
这种在二级索引的 BTree 就能查询到结果的过程就叫作「覆盖索引」也就是只需要查一个 BTree 就能找到数据。
什么是联合索引
前文我将 product_no 字段设置为了索引这种二级索引只有一个字段。如果将多个字段组合成一个索引那么这种二级索引就被称为联合索引。
比如将商品表中的 product_no 和 name 字段组合成联合索引(product_no, name)创建联合索引的方式如下
CREATE INDEX index_product_no_name ON product(product_no, name);
联合索引 (product_no, name) 的 BTree 示意图如下 可以看到联合索引的非叶子节点用两个字段的值作为 BTree 的索引值。
联合索引的 BTree 是先按 product_no 进行排序然后再 product_no 相同的情况再按 name 字段排序。记住这句话很重要
最左匹配原则
使用联合索引时存在最左匹配原则也就是按照最左优先的方式进行索引的匹配。
在使用联合索引进行查询的时候如果不遵循「最左匹配原则」联合索引会失效这样就无法利用到索引快速查询的特性了。
比如如果创建了一个 (a, b, c) 联合索引如果查询条件是以下这几种就可以利用联合索引
where a1where a1 and b2 and c3where a1 and b2
需要注意的是因为有查询优化器所以 a 字段在 where 子句的顺序并不重要。但是如果查询条件是以下这几种因为不符合最左匹配原则所以就无法匹配上联合索引联合索引就会失效:
where b2where c3where b2 and c3
上面这些查询条件之所以会失效是因为(a, b, c) 联合索引是先按 a 排序在 a 相同的情况再按 b 排序在 b 相同的情况再按 c 排序。所以b 和 c 是全局无序局部相对有序的这样在没有遵循最左匹配原则的情况下是无法利用到索引的。
我这里举联合索引ab的例子该联合索引的 B Tree 如下
可以看到a 是全局有序的1, 2, 2, 3, 4, 5, 6, 7 ,8而 b 是全局是无序的12782381052。因此直接执行 where b 2 这种查询条件没有办法利用联合索引的利用索引的前提是索引里的 key 是有序的。
只有在 a 相同的情况才b 才是有序的比如 a 等于 2 的时候b 的值为78这时就是有序的这个有序状态是局部的因此执行 where a 2 and b 7 这种查询条件时 a 和 b 字段能用到联合索引的也就是联合索引生效了。
联合索引范围查询 联合索引有一些特殊情况并不是查询过程使用了联合索引查询就代表联合索引中的所有字段都用到了联合索引进行索引查询也就是可能存在部分字段用到联合索引的 BTree部分字段没有用到联合索引的 BTree 的情况。
这种特殊情况就发生在范围查询。也就是文章开头的那句话联合索引的最左匹配原则会一直向右匹配直到遇到「范围查询」就会停止匹配。也就是范围查询的字段可以用到联合索引但是范围查询字段的后面的字段无法用到联合索引。
范围查询有很多种那到底是哪些范围查询会导致联合索引的最左匹配原则会停止匹配呢
接下来举例几个范围查询的例子下面的实验案例是基于 MySQL 8.0 做的。
例子一 Q1: select * from t_table where a 1 and b 2联合索引a, b哪一个字段用到了联合索引的 BTree 由于联合索引二级索引是先按照 a 字段的值排序的所以符合 a 1 条件的二级索引记录肯定是相邻的于是在进行索引扫描的时候可以定位到符合 a 1 条件的第一条记录然后沿着记录所在的链表向后扫描直到某条记录不符合 a 1 条件位置。所以 a 字段可以在联合索引的 BTree 中进行索引查询。
但是在符合 a 1 条件的二级索引记录的范围里b 字段的值是无序的。
比如下图的联合索引的 B Tree 里
下面这三条记录的 a 字段的值都符合 a 1 查询条件而 b 字段的值是无序的
a 字段值为 5 的记录该记录的 b 字段值为 8a 字段值为 6 的记录该记录的 b 字段值为 10a 字段值为 7 的记录该记录的 b 字段值为 5
因此我们不能根据查询条件 b 2 来进一步减少需要扫描的记录数量b 字段无法利用联合索引进行索引查询的意思。
所以在执行 Q1 这条查询语句的时候对应的扫描区间是 (2, ∞)形成该扫描区间的边界条件是 a 1与 b 2 无关。
因此Q1 这条查询语句只有 a 字段用到了联合索引进行索引查询而 b 字段并没有使用到联合索引。
我们也可以在执行计划中的 key_len 知道这一点在使用联合索引进行查询的时候通过 key_len 我们可以知道优化器具体使用了多少个字段的查询条件来形成扫描区间的边界条件。
举例个例子 a 和 b 都是 int 类型且不为 NULL 的字段那么 Q1 这条查询语句执行计划如下
可以看到 key_len 为 4 字节如果字段允许为 NULL就在字段类型占用的字节数上加 1也就是 5 字节说明只有 a 字段用到了联合索引进行索引查询而且可以看到即使 b 字段没用到联合索引key 为 idx_a_b说明 Q1 查询语句使用了 idx_a_b 联合索引。
通过 Q1 查询语句我们可以知道a 字段使用了 进行范围查询联合索引的最左匹配原则在遇到 a 字段的范围查询 后就停止匹配了因此 b 字段并没有使用到联合索引。
例子二 Q2: select * from t_table where a 1 and b 2联合索引a, b哪一个字段用到了联合索引的 BTree Q2 和 Q1 的查询语句很像唯一的区别就是 a 字段的查询条件「大于等于」。
由于联合索引二级索引是先按照 a 字段的值排序的所以符合 1 条件的二级索引记录肯定是相邻于是在进行索引扫描的时候可以定位到符合 1 条件的第一条记录然后沿着记录所在的链表向后扫描直到某条记录不符合 a 1 条件位置。所以 a 字段可以在联合索引的 BTree 中进行索引查询。
虽然在符合 a 1 条件的二级索引记录的范围里b 字段的值是「无序」的但是对于符合 a 1 的二级索引记录的范围里b 字段的值是「有序」的因为对于联合索引是先按照 a 字段的值排序然后在 a 字段的值相同的情况下再按照 b 字段的值进行排序。
于是在确定需要扫描的二级索引的范围时当二级索引记录的 a 字段值为 1 时可以通过 b 2 条件减少需要扫描的二级索引记录范围b 字段可以利用联合索引进行索引查询的意思。也就是说从符合 a 1 and b 2 条件的第一条记录开始扫描而不需要从第一个 a 字段值为 1 的记录开始扫描。
所以Q2 这条查询语句 a 和 b 字段都用到了联合索引进行索引查询。
我们也可以在执行计划中的 key_len 知道这一点。执行计划如下
可以看到 key_len 为 8 字节说明优化器使用了 2 个字段的查询条件来形成扫描区间的边界条件也就是 a 和 b 字段都用到了联合索引进行索引查询。
通过 Q2 查询语句我们可以知道虽然 a 字段使用了 进行范围查询但是联合索引的最左匹配原则并没有在遇到 a 字段的范围查询 后就停止匹配了b 字段还是可以用到了联合索引的。
例子三 Q3: SELECT * FROM t_table WHERE a BETWEEN 2 AND 8 AND b 2联合索引a, b哪一个字段用到了联合索引的 BTree Q3 查询条件中 a BETWEEN 2 AND 8 的意思是查询 a 字段的值在 2 和 8 之间的记录。
不同的数据库对 BETWEEN ... AND 处理方式是有差异的。在 MySQL 中BETWEEN 包含了 value1 和 value2 边界值类似于 and 。而有的数据库则不包含 value1 和 value2 边界值类似于 and 。
这里我们只讨论 MySQL。由于 MySQL 的 BETWEEN 包含 value1 和 value2 边界值所以类似于 Q2 查询语句因此Q3 这条查询语句 a 和 b 字段都用到了联合索引进行索引查询。
我们也可以在执行计划中的 key_len 知道这一点。执行计划如下
可以看到 key_len 为 8 字节说明优化器使用了 2 个字段的查询条件来形成扫描区间的边界条件也就是 a 和 b 字段都用到了联合索引进行索引查询。
通过 Q3 查询语句我们可以知道虽然 a 字段使用了 BETWEEN 进行范围查询但是联合索引的最左匹配原则并没有在遇到 a 字段的范围查询 BETWEEN后就停止匹配了b 字段还是可以用到了联合索引的。
例子四 Q4: SELECT * FROM t_user WHERE name like j% and age 22联合索引name, age哪一个字段用到了联合索引的 BTree 由于联合索引二级索引是先按照 name 字段的值排序的所以前缀为 ‘j’ 的 name 字段的二级索引记录都是相邻的 于是在进行索引扫描的时候可以定位到符合前缀为 ‘j’ 的 name 字段的第一条记录然后沿着记录所在的链表向后扫描直到某条记录的 name 前缀不为 ‘j’ 为止。
所以 a 字段可以在联合索引的 BTree 中进行索引查询形成的扫描区间是[j,k)。注意 j 是闭区间。如下图
虽然在符合前缀为 ‘j’ 的 name 字段的二级索引记录的范围里age 字段的值是「无序」的但是对于符合 name j 的二级索引记录的范围里age字段的值是「有序」的因为对于联合索引是先按照 name 字段的值排序然后在 name 字段的值相同的情况下再按照 age 字段的值进行排序。
于是在确定需要扫描的二级索引的范围时当二级索引记录的 name 字段值为 ‘j’ 时可以通过 age 22 条件减少需要扫描的二级索引记录范围age 字段可以利用联合索引进行索引查询的意思。也就是说从符合 name j and age 22 条件的第一条记录时开始扫描而不需要从第一个 name 为 j 的记录开始扫描 。如下图的右边
所以Q4 这条查询语句 a 和 b 字段都用到了联合索引进行索引查询。
我们也可以在执行计划中的 key_len 知道这一点。本次例子中
name 字段的类型是 varchar(30) 且不为 NULL数据库表使用了 utf8mb4 字符集一个字符集为 utf8mb4 的字符是 4 个字节因此 name 字段的实际数据最多占用的存储空间长度是 120 字节30 x 4然后因为 name 是变长类型的字段需要再加 2也就是 name 的 key_len 为 122。
age 字段的类型是 int 且不为 NULLkey_len 为 4。
Q4 查询语句的执行计划如下
可以看到 key_len 为 126 字节name 的 key_len 为 122age 的 key_len 为 4说明优化器使用了 2 个字段的查询条件来形成扫描区间的边界条件也就是 name 和 age 字段都用到了联合索引进行索引查询。
通过 Q4 查询语句我们可以知道虽然 name 字段使用了 like 前缀匹配进行范围查询但是联合索引的最左匹配原则并没有在遇到 name 字段的范围查询 like j%后就停止匹配了age 字段还是可以用到了联合索引的。
小结
网上传来穿去这句话「联合索引的最左匹配原则会一直向右匹配直到遇到范围查询(、、between、like) 就会停止匹配」并不是对的。
经过实验的证明我得出的结论是这样的
联合索引的最左匹配原则在遇到范围查询如 、的时候就会停止匹配也就是范围查询的字段可以用到联合索引但是在范围查询字段后面的字段无法用到联合索引。注意对于 、、BETWEEN、like 前缀匹配的范围查询并不会停止匹配。
