公司网站营销,中国市场网,画册排版设计网站,久久建筑有限公司文章目录 10.重新认识MySQL索引匹配过程10.1匹配规则10.2举例#xff1a;联合索引遇到范围查询#xff08;、、between、like#xff09;10.2.1例子一#xff1a;10.2.2例子二#xff1a;10.2.3例子三#xff1a;between10.2.4例子四#xff1a;like 10… 文章目录 10.重新认识MySQL索引匹配过程10.1匹配规则10.2举例联合索引遇到范围查询、、between、like10.2.1例子一10.2.2例子二10.2.3例子三between10.2.4例子四like 10.重新认识MySQL索引匹配过程
MySQL 的索引匹配过程是数据库查询优化中的关键部分。
10.1匹配规则
经过分析总结MySQL 索引匹配的一般规则一共有五种
等值匹配、最左匹配原则、列前缀匹配、匹配范围值、索引覆盖 等值匹配 如果查询条件是等值匹配即 操作符MySQL 可以高效地使用索引来查找匹配的行。这种情况下MySQL 会直接根据索引中的值找到匹配的记录。 最左匹配原则 MySQL 的组合索引使用最左匹配原则即只有当查询条件匹配索引的最左侧列时索引才会被使用。例如对于 (a, b, c) 的组合索引查询条件只包含 a 或 a, b 或 a, b, c 才会使用索引。 mysql会一直向右匹配直到遇到范围查询(、)就停止匹配注意对于 、、BETWEEN、like 前缀匹配这四种范围查询并不会停止匹配比如a 1 and b 2 and c 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 nameagepos from staffs where name‘张三’ and age23 and pos ‘dev’;
10.2举例联合索引遇到范围查询、、between、like 参考链接联合索引遇到范围查询(、、between、like)的情况 错误结论联合索引的最左匹配原则会一直向右匹配直到遇到范围查询(、、between、like) 就会停止匹配。正确结论联合索引的最左匹配原则在遇到范围查询如 、的时候就会停止匹配也就是范围查询的字段可以用到联合索引但是在范围查询字段后面的字段无法用到联合索引。但是对于 、、BETWEEN、like 前缀匹配这四种范围查询并不会停止匹配。 10.2.1例子一 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 2 来进一步减少需要扫描的记录数量b 字段无法利用联合索引进行索引查询的意思。因此Q1 这条查询语句只有 a 字段用到了联合索引进行索引查询而 b 字段并没有使用到联合索引。
10.2.2例子二 Q2: select * from t_table where a 2 and b 7 联合索引a, b哪一个字段用到了联合索引的 BTree Q2 和 Q1 的查询语句很像唯一的区别就是 a 字段的查询条件「大于等于」。 虽然在符合 a 1 条件的二级索引记录的范围里b 字段的值是「无序」的但是对于符合 a 1 的二级索引记录的范围里b 字段的值是「有序」的因为对于联合索引是先按照 a 字段的值排序然后在 a 字段的值相同的情况下再按照 b 字段的值进行排序。 所以Q2 这条查询语句 a 和 b 字段都用到了联合索引进行索引查询。
10.2.3例子三between 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 。 虽然 a 字段使用了 BETWEEN 进行范围查询但是联合索引的最左匹配原则并没有在遇到 a 字段的范围查询 BETWEEN后就停止匹配了b 字段还是可以用到了联合索引的。
10.2.4例子四like 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 字段都用到了联合索引进行索引查询。
