栏目分类:
子分类:
返回
名师互学网用户登录
快速导航关闭
当前搜索
当前分类
子分类
实用工具
热门搜索
名师互学网 > IT > 软件开发 > 后端开发 > Java

索引合并优化

Java 更新时间: 发布时间: IT归档 最新发布 模块sitemap 名妆网 法律咨询 聚返吧 英语巴士网 伯小乐 网商动力

索引合并优化

当你兴高采烈地在各个字段建立索引,并在这些索引上使用WHERe+OR的SELECt操作,以为MySQL不会聪明到使用各个列的索引时,神奇的事情发生了:你看到了从没见过的type和Extra:

这是因为MySQL使用了名为index merge的优化操作。

这种优化会在你用多种不同的type为range的select检索行,并且最后将它们合并成一个时出现。这个优化仅仅能够合并单个表里的索引扫描,并不能跨表。这些索引合并可以产生底层扫描出的结果后的并集、交集、交集的并集。

以下是index merge的可能应用场景:

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);

注意:索引合并算法拥有以下已知的限制:

  • 如果你的查询拥有一个复杂的WHERe子句,里面有很深的AND/OR嵌套逻辑,则MySQL大概率不会选择优化。试着用下面的分配律转化你的where子句,看看会不会变好:
(x AND y) OR z => (x OR z) AND (y OR z)
(x OR y) AND z => (x AND z) OR (y AND z)
  • 全文索引无法使用索引合并

使用EXPLAIN之后,若发生了索引合并,则type中会显示index_merge,key会包含使用到的所有索引,key_len为使用到的所有索引的最长key长度的列表,而EXPLAIN中会有三种不同的选择,代表着三种不同的索引合并的算法,MySQL优化器会在预估查询成本之后做出它自己的选择,下面我们依次来讲一下:

索引交集算法

显示:Using intersect(...)

这个算法用于where子句(或转化后)是AND连接的几个针对不同属性的范围查询,每个属性符合以下至少一个条件:

  • 符合以下表达式,且每个key_part都对应一个索引
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;

 

索引并集算法

显示:Using union(...)

这个算法用于where子句(或转化后)是OR连接的几个针对不同属性的范围查询,每个属性符合以下至少一个条件:

  • 符合以下表达式,且每个key_part都对应一个索引
key_part1 = const1 AND key_part2 = const2 ... AND key_partN = constN
  • 若为InnoDB存储引擎,则任意一个范围条件覆盖了主键即可。(感觉应该是因为聚簇索引)
  • 可被索引交集算法优化的条件 

总而言之,索引交集算法对所有使用的索引执行同时扫描,并生成从合并索引扫描接收的行序列的并集。如果查询中使用的所有列都被使用的索引覆盖,则不会检索完整的表行。

举例子:

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;

 

 索引并集排序算法

显示:Using sort_union(...)

这个算法用于where子句(或转化后)是OR连接的几个针对不同属性的范围查询,但索引交集算法都无法在这些条件上应用。

举例子:

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;

转载请注明:文章转载自 www.mshxw.com
本文地址:https://www.mshxw.com/it/937415.html
我们一直用心在做
关于我们 文章归档 网站地图 联系我们

版权所有 (c)2021-2022 MSHXW.COM

ICP备案号:晋ICP备2021003244-6号