【MySQL | 第十篇】重新认识MySQL索引匹配过程

文章目录

• 10.重新认识MySQL索引匹配过程
• • 10.1匹配规则
  • 10.2举例：联合索引遇到范围查询（>、
  • 10.2.2例子二：>=
  • 10.2.3例子三：between
  • 10.2.4例子四：like

    10.重新认识MySQL索引匹配过程

    ​ MySQL 的索引匹配过程是数据库查询优化中的关键部分。

    10.1匹配规则

    经过分析总结，MySQL 索引匹配的一般规则一共有：五种

    • 等值匹配、最左匹配原则、列前缀匹配、匹配范围值、索引覆盖

      1. 等值匹配：

         如果查询条件是等值匹配，即 = 操作符，MySQL 可以高效地使用索引来查找匹配的行。这种情况下，MySQL 会直接根据索引中的值找到匹配的记录。

      2. 最左匹配原则：

         MySQL 的组合索引使用最左匹配原则，即只有当查询条件匹配索引的最左侧列时，索引才会被使用。例如，对于 (a, b, c) 的组合索引，查询条件只包含 a 或 a, b 或 a, b, c 才会使用索引。

         • mysql会一直向右匹配直到遇到范围查询(>、=、 3 and d = 4
           • 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，
           • 如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整（建立索引时他们三个的顺序）

           • 列前缀匹配：

             可以匹配某一列值的开头部分：

             使用索引： explain select * from staffs where name like 'j%'  ；
             索引失效： explain select * from staffs where name like '%j%'  ；
             

           • 匹配范围值：

             可以查找某一范围的值：explain select * from staffs where name >‘Mary’ ；

           • 索引覆盖：

             如果查询的列都包含在索引中，并且查询不需要访问表数据，即可通过索引得到所有需要的信息，这种情况称为索引覆盖。索引覆盖可以提高查询性能，因为不需要访问表中的实际数据行。

             例子：

             explain select name，age，pos from staffs where name=‘张三’ and age=23 and pos =‘dev’;

      10.2举例：联合索引遇到范围查询（>、、、=、

      Q1: select * from t_table where a > 1 and b = 2；

      联合索引（a, b）哪一个字段用到了联合索引的 B+Tree？

      1. 由于联合索引（二级索引）是先按照 a 字段的值排序的，所以符合 a > 1 条件的二级索引记录肯定是相邻的，于是在进行索引扫描的时候，可以定位到符合 a > 1 条件的第一条记录
      2. 然后沿着记录所在的链表向后扫描，直到某条记录不符合 a > 1 条件位置。所以 a 字段可以在联合索引的 B+Tree 中进行索引查询。
      3. 但是在符合 a > 1 条件的二级索引记录的范围里，b 字段的值是无序的。
      4. 因此，我们不能根据查询条件 b = 2 来进一步减少需要扫描的记录数量（b 字段无法利用联合索引进行索引查询的意思）。
      5. 因此，Q1 这条查询语句只有 a 字段用到了联合索引进行索引查询，而 b 字段并没有使用到联合索引。

      10.2.2例子二：>=

      Q2: select * from t_table where a >= 2 and b = 7，

      联合索引（a, b）哪一个字段用到了联合索引的 B+Tree？

      1. Q2 和 Q1 的查询语句很像，唯一的区别就是 a 字段的查询条件「大于等于」。

      2. 虽然在符合 a>= 1 条件的二级索引记录的范围里，b 字段的值是「无序」的，但是对于符合 a = 1 的二级索引记录的范围里，b 字段的值是「有序」的（因为对于联合索引，是先按照 a 字段的值排序，然后在 a 字段的值相同的情况下，再按照 b 字段的值进行排序）。

         

      3. 所以，Q2 这条查询语句 a 和 b 字段都用到了联合索引进行索引查询。

      10.2.3例子三：between

      Q3: SELECT * FROM t_table WHERE a BETWEEN 2 AND 8 AND b = 2，

      联合索引（a, b）哪一个字段用到了联合索引的 B+Tree？

      1. Q3 查询条件中 a BETWEEN 2 AND 8 的意思是查询 a 字段的值在 2 和 8 之间的记录。

      2. 不同的数据库对 BETWEEN … AND 处理方式是有差异的。在MySQL 中，BETWEEN 包含了 value1 和 value2 边界值，类似于 >= and =