【GreatSQL优化器-15】index merge

于 2025-02-21 10:37:18 发布
阅读量923 收藏 8
点赞数 29
文章标签： oracle 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/GreatSQL2021/article/details/145771445
版权

【GreatSQL优化器-15】index merge

一、index merge介绍

GreatSQL的优化器的Index Merge Optimization是查询优化器在处理复杂查询时使用的一种高级技术。当查询的 WHERE 子句中有多个独立的条件，且每个条件都可以使用不同的索引时，优化器会尝试将这些索引合并起来，以提高查询效率。这种优化策略允许数据库在一个查询中同时使用多个索引，从而避免全表扫描或减少需要扫描的数据量。

在某些情况下，单独使用任何一个索引都无法高效地获取到完整的结果集。而通过合并多个索引的扫描结果，我们可以更精确地定位到满足所有条件的记录，从而提高查询效率。当优化器生成mm tree的时候会保存不同索引的tree信息，生成mm tree之后会基于OR或者AND条件进行索引并集合并或者交集合并，从而实现index merge。

下面用一个简单的例子来说明索引合并是什么。

greatsql> CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 INT,date1 DATETIME);
greatsql> INSERT INTO t1 VALUES (1,10,'2021-03-25 16:44:00.123456'),(2,1,'2022-03-26 16:44:00.123456'),(3,4,'2023-03-27 16:44:00.123456'),(5,5,'2024-03-25 16:44:00.123456'),(7,null,'2020-03-25 16:44:00.123456'),(8,10,'2020-10-25 16:44:00.123456'),(11,16,'2023-03-25 16:44:00.123456');
greatsql> CREATE TABLE t2 (cc1 INT, cc2 INT,cc3 int);
greatsql> INSERT INTO t2 VALUES (1,3,1),(2,1,4),(3,2,10),(4,3,4),(5,15,10),(1,10,3),(4,4,1),(6,4,9),(11,110,1);
greatsql> CREATE INDEX idx1 ON t1(c2);
greatsql> CREATE INDEX idx2 ON t1(c2,date1);
greatsql> CREATE INDEX idx2_1 ON t2(cc1);
greatsql> CREATE INDEX idx2_2 ON t2(cc2);
greatsql> CREATE INDEX idx2_3 ON t2(cc3);

greatsql>  explain SELECT * FROM t2 where cc2=3 and cc1=1 and cc3=1;
+----+-------------+-------+------------+-------------+----------------------+---------------+---------+------+------+----------+---------------------------------------------+
| id | select_type | table | partitions | type        | possible_keys        | key           | key_len | ref  | rows | filtered | Extra                                       |
+----+-------------+-------+------------+-------------+----------------------+---------------+---------+------+------+----------+---------------------------------------------+
|  1 | SIMPLE      | t2    | NULL       | index_merge | idx2_1,idx2_2,idx2_3 | idx2_1,idx2_2 | 5,5     | NULL |    1 |    33.33 | Using intersect(idx2_1,idx2_2); Using where |
这里用到了索引交集合并
+----+-------------+-------+------------+-------------+----------------------+---------------+---------+------+------+----------+---------------------------------------------+
二、test_quick_select代码解释

首先了解一下ROR的定义，ROR 的含义是 Rowid-Ordered Retrieval，表示单个索引返回的结果集是按照主键有序排列的。索引交集和并集是对ROR的索引进行操作，而如果有非ROR索引的话就要执行排序并集操作。

不是所有涉及不同mm tree的查询最后都会走索引合并，还是要取决于cost大小，有时候全表扫描反而cost更小。例子见下面第三节。

int test_quick_select() {
  // 先判断skip_scan是否满足条件，不满足的话执行索引合并。skip_scan下一期介绍
  AccessPath *skip_scan_path = get_best_skip_scan();
  // 不满足skip_scan执行索引合并
  if (tree && (best_path == nullptr || !get_forced_by_hint(best_path))) {
    // 获取is_ror_scan值与table->quick_rows[keynr]区间范围行数，is_ror_scan为true才能执行索引合并，取值见表二
    get_key_scans_params();
    // 执行索引交集合并，计算cost并跟全表扫描对比，选取cost低的path
    AccessPath *rori_path = get_best_ror_intersect(
            thd, &param, table, index_merge_intersect_allowed, tree,
            &needed_fields, best_cost,
            /*force_index_merge_result=*/true, /*reuse_handler=*/true);
    // tree->merges在tree_or()的时候赋值，赋值条件见表二
    if (!tree->merges.is_empty()) {
      // 按照不同索引组执行索引并集合并，计算cost并跟全表扫描对比，选取cost低的path
      for (SEL_IMERGE &imerge : tree->merges) {
          new_conj_path = get_best_disjunct_quick(
              thd, &param, table, index_merge_union_allowed,
              index_merge_sort_union_allowed, index_merge_intersect_allowed,
              skip_records_in_range, &needed_fields, &imerge, best_cost,
              needed_reg);
      }
    }
  }
}

// 执行索引交集合并
AccessPath *get_best_ror_intersect() {
  // 遍历mm tree的索引数组，给tree->ror_scans数组和cpk_scan赋值，cpk_scan专门存放主键索引信息
  for (idx = 0, cur_ror_scan = tree->ror_scans; idx < param->keys; idx++) {
    tree->ror_scans = make_ror_scan();
  }
  // 对tree->ror_scans数组根据覆盖列个数和范围包含的行数进行排序，范围包含的行数越少排序越前。这个步骤排除了主键索引，因为主键在函数最后单独处理
  // 排序函数见下面is_better_intersect_match函数
  find_intersect_order(tree->ror_scans, tree->ror_scans_end, param,
                       needed_fields);
  // 按照上面的索引排序顺序进行交集操作，可以减少cost的索引进行交集操作，不能减少的排除
  while (cur_ror_scan != tree->ror_scans_end && !intersect->is_covering) {
    // 计算除了主键以外的所有索引的过滤系数、行数、cost并且累加到intersect变量
    ror_intersect_add(intersect);
  }
  // 计算主键列的过滤系数、行数、cost并与之前别的索引结果累加到intersect变量
  if (cpk_scan) ror_intersect_add(intersect);
  // 最后生成AccessPath
  AccessPath *path = new (param->return_mem_root) AccessPath;
}

// 对索引进行排序，可以看到覆盖的列越少，包含的行数越少，排序越靠前
static bool is_better_intersect_match(const ROR_SCAN_INFO *scan1,
                                      const ROR_SCAN_INFO *scan2) {
  if (scan1 == scan2) return false;

  if (scan1->num_covered_fields_remaining > scan2->num_covered_fields_remaining)
    return false;

  if (scan1->num_covered_fields_remaining < scan2->num_covered_fields_remaining)
    return true;

  return (scan1->records > scan2->records);
}

// 执行索引并集合并
static AccessPath *get_best_disjunct_quick() {
  // 按照索引遍历所有tree
  for (auto tree_it = imerge->trees.begin(); tree_it != imerge->trees.end();
         ++tree_it, cur_child++) {
    // 获取is_ror_scan值与table->quick_rows[keynr]区间范围行数，is_ror_scan为true才能执行索引合并，取值见表二
    get_key_scans_params();
  }
  // 如果所有索引都是ROR的，那么直接返回结果
  if (all_scans_rors && (index_merge_union_allowed || force_index_merge))
    return get_ror_union_path();
  // 如果不是所有索引都是ROR的，那么需要执行Sort-Union
  // 首先计算磁盘扫描的cost
  get_sweep_read_cost();
  // 如果扫描磁盘cost太大，那么继续执行Sort-Union
  // 索引去重cost估计
  dup_removal_cost = Unique::get_use_cost(
        (uint)non_cpk_scan_records, table->file->ref_length,
        // 这个系统变量见表七
        thd->variables.sortbuff_size, cost_model);
  // 执行索引Sort-Union
  get_ror_union_path();
}

static AccessPath *get_ror_union_path() {
  // 遍历所有tree元素，对每个元素执行Intersection Merge
  for (auto tree_it = imerge->trees.begin(); tree_it != imerge->trees.end();
         tree_it++, cur_child++, cur_roru_plan++) {
    get_best_ror_intersect();
  }
  // 计算磁盘扫描cost
  get_sweep_read_cost();
  // 生成AccessPath，这个AccessPath带有child即Intersection Merge的索引子集
  AccessPath *path = new (param->return_mem_root) AccessPath;
  path->rowid_union().children = children;
}

表一：索引合并类型

索引合并类型 说明 对应代码 举例
交集合并（Intersection Merge） 当查询需要满足多个条件（使用 AND 连接），并且每个条件都可以使用不同的索引时，分别扫描这些索引，然后取结果的交集。原则是通过mm tree能找到唯一的那条数据的时候才执行交集 get_best_ror_intersect() ROR条件 and ROR条件
并集合并（Union Merge） 查询可能只需要满足多个条件中的任意一个（使用 OR 连接）。分别扫描这些索引，然后取结果的并集。被合并的子集也可以是索引交集比如union(intersect(),intersect())，例子见下面 get_best_disjunct_quick() ROR条件 or ROR条件
排序并集合并（Sort-Union Merge） 需要对结果进行排序，并且排序的字段也有索引时。分别扫描索引，然后合并并排序结果。这个cost在三者中最大因为要排序 get_best_disjunct_quick() 排序执行过程：通过IndexMergeIterator::Init()的unique->unique_add进行排序 非ROR条件 OR 非ROR条件，比如有非唯一索引列范围条件，where key1 < 3 or key2 > 1020

表二：is_ror_scan取值情况

条件 is_ror_scan值 赋值的函数 说明
赋初始值 !(file->index_flags(keynr, 0, true) & HA_KEY_SCAN_NOT_ROR) check_quick_select() 初值
索引是倒序的 false check_quick_select()
索引类型不等于SE_SPECIFIC和BTREE或者索引包含虚拟列 false check_quick_select()
索引是主键列 true check_quick_select()
列的第二个以上条件有右节点 false sel_arg_range_seq_next() 见下面例子
tree叶节点范围最大值和最小值不相等 false sel_arg_range_seq_next() 见下面例子
不是等号条件或者不为is_key_scan_ror() false sel_arg_range_seq_next() 等于条件的is_ror_scan=true is_key_scan_ror()比较索引列长度和插入数据的长度，不一致的话就返回false。比如创建索引字符集和插入数据的字符集不一致就会导致该列无法用index merge。

注：is_ror_scan原则就是通过条件可以确定唯一的位置，这就是ROR有序的含义

表三：tree->merges数组赋值条件

条件(以下必须全部满足) 举例
不同索引的OR条件 where d2<5 or d1=10，这两个条件涉及2个不同索引
两个SEL_TREE不能被合并 where (d2=5 and d1=5) or (d2=4)，这两个OR条件里面带有相同的索引d2，因此不能加入merges数组

表四：不同索引合并方法行数计算方法

索引合并类型 公式
交集合并（Intersection Merge） 总行数 * (第1个索引范围对应的行数 / 总行数) * (第2个索引范围对应的行数 / 总行数) …
并集合并（Union Merge） 第1个索引范围对应的行数+第2个索引范围对应的行数 …
排序并集合并（Sort-Union Merge） 第1个索引范围对应的行数+第2个索引范围对应的行数 …

表五：跟索引合并相关的OPTIMIZER_SWITCH

OPTIMIZER_SWITCH 默认 说明
OPTIMIZER_SWITCH_INDEX_MERGE ON 可以在多个索引上进行查询，并将结果合并返回。
OPTIMIZER_SWITCH_INDEX_MERGE_UNION OFF 会在使用到的多个索引上同时进行扫描，并取这些扫描结果的并集作为最终结果集。
OPTIMIZER_SWITCH_INDEX_MERGE_SORT_UNION ON 比单纯的Union索引合并多了一步对二级索引记录的主键id排序的过程。
OPTIMIZER_SWITCH_INDEX_MERGE_INTERSECT ON 会在使用到的多个索引上同时进行扫描，并取这些扫描结果的交集作为最终结果集。

表六：索引扫描类型

类型 说明 trace显示
INDEX_RANGE_SCAN 范围扫描 range_scan
INDEX_MERGE 排序并集合并模式 index_merge
ROWID_INTERSECTION 交集合并模式 index_roworder_intersect
ROWID_UNION 并集合并模式 index_roworder_union
INDEX_SKIP_SCAN 使用skip scan方式进行范围扫描，当要查询的列都在索引中时，即使where中的条件不是索引的第一部分，也可以使用索引。 skip_scan
GROUP_INDEX_SKIP_SCAN 用于group by的索引跳跃 index_group

表七：涉及的系统变量

系统变量 说明
thd->variables.sortbuff_size 用在索引Sort-Union操作的时候对索引进行排序去重估计cost
OPTIMIZER_SWITCH_FAVOR_RANGE_SCAN 索引合并执行在get_key_scans_params()的时候，如果这个设置为true，那么会对rows对应算出来的cost乘以系数0.1，让cost结果小十倍，这样结果就不会走索引合并而走RANGE_SCAN，见下面例子
三、实际例子说明

接下来看几个例子来说明上面的代码。

交集合并

greatsql> EXPLAIN SELECT * FROM t1 where c2=10 and c1<10;
+----+-------------+-------+------------+-------------+-------------------+--------------+---------+------+------+----------+--------------------------------------------+
| id | select_type | table | partitions | type        | possible_keys     | key          | key_len | ref  | rows | filtered | Extra                                      |
+----+-------------+-------+------------+-------------+-------------------+--------------+---------+------+------+----------+--------------------------------------------+
|  1 | SIMPLE      | t1    | NULL       | index_merge | PRIMARY,idx1,idx2 | idx1,PRIMARY | 9,4     | NULL |    1 |    58.33 | Using intersect(idx1,PRIMARY); Using where |
+----+-------------+-------+------------+-------------+-------------------+--------------+---------+------+------+----------+--------------------------------------------+
                  "analyzing_range_alternatives": {
                    "range_scan_alternatives": [
                      {
                        "index": "PRIMARY",
                        "ranges": [
                          "c1 < 10"
                        ],
                        "index_dives_for_eq_ranges": true,
                        "rowid_ordered": true, 这里为true说明这个索引是ROR的
                        "using_mrr": false
最低0.47元/天 解锁文章
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值