美文网首页关系型数据库
MySQL 索引合并(Index Merge)优化

MySQL 索引合并(Index Merge)优化

作者: 月饮沙 | 来源:发表于2020-05-27 16:01 被阅读0次

    本文问题

    1. 什么是Index MergeIndex Merge的限制有哪些?
    2. 如何查看语句是否使用了Index Merger
    3. Index Merge有哪几种?分别适用于那些情景?
    4. 如何控制优化器是否使用Index Merge

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

    Index Merge的已知缺陷

    • 如果在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)
    
    • Index Merger不能应用于全文索引(fulltext index

    Index Merge的EXPLAIN输出

    • type列的值显示为index_merge
    • key列显示使用的索引列表
    • key_len列显示这些索引的最大长度(列表)。
    • Extra列显示Index Merge的算法:
      • Using intersect(...)
      • Using union(...)
      • Using sort_union(...)
    mysql> explain select * from test_merge where (col1<10 and col2>50) or col3=50;
    +----+-------------+------------+------------+-------------+---------------+---------------+---------+------+------+----------+----------------------------------------------+
    | id | select_type | table      | partitions | type        | possible_keys | key           | key_len | ref  | rows | filtered | Extra                                        |
    +----+-------------+------------+------------+-------------+---------------+---------------+---------+------+------+----------+----------------------------------------------+
    |  1 | SIMPLE      | test_merge | NULL       | index_merge | idx_1_2,idx_3 | idx_1_2,idx_3 | 5,5     | NULL |  214 |   100.00 | Using sort_union(idx_1_2,idx_3); Using where |
    +----+-------------+------------+------------+-------------+---------------+---------------+---------+------+------+----------+----------------------------------------------+
    1 row in set, 1 warning (0.00 sec)
    
    mysql> explain select * from test_merge where (col1=10 and col2=50) or col3=50;
    +----+-------------+------------+------------+-------------+---------------+---------------+---------+------+------+----------+-----------------------------------------+
    | id | select_type | table      | partitions | type        | possible_keys | key           | key_len | ref  | rows | filtered | Extra                                   |
    +----+-------------+------------+------------+-------------+---------------+---------------+---------+------+------+----------+-----------------------------------------+
    |  1 | SIMPLE      | test_merge | NULL       | index_merge | idx_1_2,idx_3 | idx_1_2,idx_3 | 10,5    | NULL |   22 |   100.00 | Using union(idx_1_2,idx_3); Using where |
    +----+-------------+------------+------------+-------------+---------------+---------------+---------+------+------+----------+-----------------------------------------+
    1 row in set, 1 warning (0.00 sec)
    

    Index Merge 算法

    Index Merge Intersection 索引合并交集

    这种方法适用于WHERE子句中的条件是通过AND结合的不同索引的范围条件时,其中的每个条件都需要满足下列条件之一:

    • 如果其中的索引是多列索引,条件中需要包括索引的所有列
      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;
    

    索引合并交集算法在所有使用的索引上同时进行扫描,并从扫描结果中生成行的交集
    如果查询中的所有列都被使用的索引覆盖,不需要检索所有表行(EXPLAIN输出中的Extra列中包括Using index)。例如这个语句:
    SELECT COUNT(*) FROM t1 WHERE key1 = 1 AND key2 = 1;

    如果使用的索引没有覆盖查询中所有的行,只有当所有使用的索引的范围条件满足时才检索整个行。
    如果合并条件中包括Innodb表主键索引条件,主键并不用来检索数据,而是用来筛选使用其他条件检索出的行。 # 就是先通过其他的范围条件筛选出一部分数据,在从这部分数据中,通过主键来筛选出最终的结果

    Index Merge Union 索引合并并集

    这种方法适用于WHERE子句中的条件是通过OR结合的不同索引的范围条件时,其中的每个条件都需要满足下列条件之一:

    • 如果其中的索引是多列索引,条件中需要包括索引的所有列
      key_part1 = const1 AND key_part2 = const2 ... AND key_partN = constN
    • Innodb表的主键上的范围条件
    • 适用于Index Merger 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结合的不同索引的范围条件,但是不能使用Index Merge Union算法的情景
    示例:

    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;
    

    sort_unionunion算法的区别是,sort_union必须在返回行数据前先获取行ID并对行ID进行排序。

    禁用Index Merge

    optimizer_swith中有4个关于Index Merge的变量:
    index_merge,index_merge_intersection,index_merge_union,index_merge_sort_union
    默认情况下都是启用的。要单独启用某个算法,设置index_merge=off,并将相应的标志设置为on

    问题答案

    1. 什么是Index MergeIndex Merge的限制有哪些?
      如果查询中使用到了不同的索引,可以对不同索引的条件分别进行范围扫描,然后将扫描结果合并得到最终的结果,这就是Index Merge
      限制:只能合并同一个表的索引扫描结果,不能跨表合并。此外,无法对fulltext索引进行合并
    2. 如何查看语句是否使用了Index Merge
      EXPLAINtype列的值为index_merge表示使用了索引合并。根据索引合并算法的不同,会在Extra列中显示Using intersect/union/sort_union
    3. Index Merge有哪几种?分别适用于那些情景?
      3种:Intersection,Union,Sort_union
      Intersection:使用AND结合的关于不同索引的条件(普通索引的等值表达式或者主键索引的范围表达式)
      UnionSort Union:使用OR结合的关于不同索引的范围条件
      区别:当条件为普通索引的等值表达式或者主键索引的范围表达式时,可以使用Union。其他不符合条件的只能使用Sort Union
      如果包括多列索引,在范围条件中需要包括索引中的所有列。
    4. 如何控制优化器是否使用Index Merge
      optimizer_swith中有4个关于Index Merge的变量:
      index_merge,index_merge_intersection,index_merge_union,index_merge_sort_union
      默认情况下都是启用的。要单独启用某个算法,设置index_merge=off,并将相应的标志设置为on

    相关文章

      网友评论

        本文标题:MySQL 索引合并(Index Merge)优化

        本文链接:https://www.haomeiwen.com/subject/gnniahtx.html