mysql 索引合并的使用

索引合并是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;

到此这篇关于mysql 索引合并的使用的文章就介绍到这了,更多相关mysql 索引合并内容请搜索www.887551.com以前的文章或继续浏览下面的相关文章希望大家以后多多支持www.887551.com！

mysql 索引合并的使用

相关推荐