mysql 索引合并的使用

作者:camellia 时间:2024-01-14 05:53:39 

索引合并是mysql底层为我们提供的智能算法。了解索引合并的算法,有助于我们更好的创建索引。

索引合并是通过多个range类型的扫描并且合并它们的结果集来检索行的。仅合并来自单个表的索引扫描,而不是跨多个表的索引扫描。合并会产生底层扫描的三种形式:unions(合并)、intersections(交集)、unions-of-intersections(先取交集再合并)。

以下四个例子会产生索引合并:


SELECT * FROM tbl_name WHERE key1 = 10 OR key2 = 20;
SELECT * FROM tbl_name WHERE (key1 = 10 OR key2 = 20) AND non_key = 30;
SELECT * FROM t1, t2 WHERE (t1.key1 IN (1,2) OR t1.key2 LIKE 'value%') AND t2.key1 = t1.some_col;
SELECT * FROM t1, t2 WHERE t1.key1 = 1 AND (t2.key1 = t1.some_col OR t2.key2 = t1.some_col2);

索引合并有以下已知的局限性:

1、如果查询语句包含一个带有严重AND/OR嵌套的复杂的WHERE子句而MySQL没有选择最佳计划,那么可以尝试使用以下的标志符转换:

(x AND y) OR z => (x OR z) AND (y OR z)
(x OR y) AND z => (x AND z) OR (y AND z)

2、索引合并不适用于全文索引。

在 EXPLAIN 语句输出的信息中,索引合并在type列中表现为“index_merge”,在这种情况下,key列包含使用的索引列表。
索引合并访问方法有几种算法,表现在 EXPLAIN 语句输出的Extra字段中:


Using intersect(...)
Using union(...)
Using sort_union(...)

下面将更详细地描述这些算法。优化器根据各种可用选项的成本估计,在不同的索引合并算法和其他访问方法之间进行选择。

Index Merge Intersection算法

Index Merge Intersection算法对所有使用的索引执行同步扫描,并生成从合并的索引扫描接收到的行序列的交集。
这种算法适用于当WHERE子句被转换成多个使用AND连接的不同索引key上的范围条件,且条件是以下两种之一:

一、这种形式的N部分表达式,索引正好包括N个字段(所有索引字段都被覆盖),N>=1,N如果大于1就是复合索引:


key_part1 = const1 AND key_part2 = const2 ... AND key_partN = constN。

二、InnoDB表主键上的任何范围条件。

例子:


SELECT * FROM innodb_table
WHERE primary_key < 10 AND key_col1 = 20;
SELECT * FROM tbl_name
WHERE key1_part1 = 1 AND key1_part2 = 2 AND key2 = 2;

Index Merge Union算法

该算法类似于Index Merge Intersection算法,适用于当WHERE子句被转换成多个使用OR连接的不同索引key上的范围条件,且条件是以下三种之一:

一、这种形式的N部分表达式,索引正好包括N个字段(所有索引字段都被覆盖),N>=1,N如果大于1就是复合索引:


key_part1 = const1 AND key_part2 = const2 ... AND key_partN = constN。

二、InnoDB表主键上的任何范围条件。
三、符合Index Merge Intersection算法的条件。

例子:


SELECT * FROM t1
WHERE key1 = 1 OR key2 = 2 OR key3 = 3;
SELECT * FROM innodb_table
WHERE (key1 = 1 AND key2 = 2)
OR (key3 = 'foo' AND key4 = 'bar') AND key5 = 5;

Index Merge Sort-Union算法

该算法适用于当WHERE子句被转换成多个使用OR连接的不同索引key上的范围条件,但是不符合 Index Merge Union算法的。Index Merge Sort-Union和Index Merge Union算法的区别在于,Index Merge Sort-Union必须首先获取所有行的行id并在返回任何行之前对它们进行排序。

例子:


SELECT * FROM tbl_name
WHERE key_col1 < 10 OR key_col2 < 20;
SELECT * FROM tbl_name
WHERE (key_col1 > 10 OR key_col2 = 20) AND nonkey_col = 30;

来源:https://juejin.cn/post/7000542421125693453

标签:mysql,索引合并
0
投稿

猜你喜欢

  • 通过实例简单了解Python sys.argv[]使用方法

    2022-09-12 14:14:35
  • Python中字符串的基础介绍及常用操作总结

    2022-04-12 18:01:03
  • mysql中使用replace替换某字段的部分内容

    2024-01-20 14:43:39
  • 浅谈使用Python变量时要避免的3个错误

    2022-06-20 21:07:07
  • MySQL下海量数据的迁移步骤分享

    2024-01-16 13:13:10
  • 解决bootstrap中使用modal加载kindeditor时弹出层文本框不能输入的问题

    2024-04-16 09:26:21
  • 分享:在存储过程中使用另一个存储过程返回的查询结果集的方法

    2024-01-16 13:03:57
  • PHP实现表单处理方法详解

    2023-05-25 07:39:18
  • 增删改查sql语法基础教程

    2024-01-16 23:42:18
  • SQL语句中LEFT JOIN的ON和WHERE有什么区别

    2024-01-21 04:33:00
  • replace MYSQL字符替换函数sql语句分享(正则判断)

    2024-01-20 15:07:43
  • 数据库基本操作语法归纳总结

    2024-01-19 04:15:40
  • Vue之使用mockjs生成模拟数据案例详解

    2024-05-29 22:24:40
  • python调用API接口实现登陆短信验证

    2021-03-13 00:04:03
  • SQL Server2016正式版安装配置方法图文教程

    2024-01-18 10:21:52
  • 再说淘宝的评价和信用机制

    2008-07-10 12:43:00
  • 关于Python中浮点数精度处理的技巧总结

    2021-12-28 15:12:53
  • 代码讲解Python对Windows服务进行监控

    2023-10-07 10:59:09
  • vue中element-ui表格缩略图悬浮放大功能的实例代码

    2024-05-29 22:44:31
  • PHP获取网页标题的3种实现方法代码实例

    2024-05-03 15:51:06
  • asp之家 网络编程 m.aspxhome.com