【GreatSQL优化器-15】index merge

【GreatSQL优化器-15】index merge

一、index merge介绍

GreatSQL的优化器的Index Merge Optimization是查询优化器在处理复杂查询时使用的一种高级技术。当查询的 WHERE 子句中有多个独立的条件，且每个条件都可以使用不同的索引时，优化器会尝试将这些索引合并起来，以提高查询效率。这种优化计谋允许数据库在一个查询中同时使用多个索引，从而制止全表扫描或减少需要扫描的数据量。
在某些环境下，单独使用任何一个索引都无法高效地获取到完备的效果集。而通过合并多个索引的扫描效果，我们可以更精确地定位到满足所有条件的记录，从而提高查询效率。当优化器天生mm tree的时候会保存不同索引的tree信息，天生mm tree之后会基于OR大概AND条件举行索引并集合并大概交集合并，从而实现index merge。
下面用一个简朴的例子来说明索引合并是什么。

1. greatsql> CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 INT,date1 DATETIME);
2. 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');
3. greatsql> CREATE TABLE t2 (cc1 INT, cc2 INT,cc3 int);
4. 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);
5. greatsql> CREATE INDEX idx1 ON t1(c2);
6. greatsql> CREATE INDEX idx2 ON t1(c2,date1);
7. greatsql> CREATE INDEX idx2_1 ON t2(cc1);
8. greatsql> CREATE INDEX idx2_2 ON t2(cc2);
9. greatsql> CREATE INDEX idx2_3 ON t2(cc3);
11. greatsql>  explain SELECT * FROM t2 where cc2=3 and cc1=1 and cc3=1;
12. +----+-------------+-------+------------+-------------+----------------------+---------------+---------+------+------+----------+---------------------------------------------+
13. | id | select_type | table | partitions | type        | possible_keys        | key           | key_len | ref  | rows | filtered | Extra                                       |
14. +----+-------------+-------+------------+-------------+----------------------+---------------+---------+------+------+----------+---------------------------------------------+
15. |  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 |
16. 这里用到了索引交集合并
17. +----+-------------+-------+------------+-------------+----------------------+---------------+---------+------+------+----------+---------------------------------------------+

复制代码

二、test_quick_select代码解释

首先了解一下ROR的定义，ROR 的含义是 Rowid-Ordered Retrieval，表示单个索引返回的效果集是按照主键有序排列的。索引交集和并集是对ROR的索引举行操作，而如果有非ROR索引的话就要执行排序并集操作。
不是所有涉及不同mm tree的查询最后都会走索引合并，还是要取决于cost大小，有时候全表扫描反而cost更小。例子见下面第三节。

1. int test_quick_select() {
2.   // 先判断skip_scan是否满足条件，不满足的话执行索引合并。skip_scan下一期介绍
3.   AccessPath *skip_scan_path = get_best_skip_scan();
4.   // 不满足skip_scan执行索引合并
5.   if (tree && (best_path == nullptr || !get_forced_by_hint(best_path))) {
6.     // 获取is_ror_scan值与table->quick_rows[keynr]区间范围行数，is_ror_scan为true才能执行索引合并，取值见表二
7.     get_key_scans_params();
8.     // 执行索引交集合并，计算cost并跟全表扫描对比，选取cost低的path
9.     AccessPath *rori_path = get_best_ror_intersect(
10.             thd, &param, table, index_merge_intersect_allowed, tree,
11.             &needed_fields, best_cost,
12.             /*force_index_merge_result=*/true, /*reuse_handler=*/true);
13.     // tree->merges在tree_or()的时候赋值，赋值条件见表二
14.     if (!tree->merges.is_empty()) {
15.       // 按照不同索引组执行索引并集合并，计算cost并跟全表扫描对比，选取cost低的path
16.       for (SEL_IMERGE &imerge : tree->merges) {
17.           new_conj_path = get_best_disjunct_quick(
18.               thd, &param, table, index_merge_union_allowed,
19.               index_merge_sort_union_allowed, index_merge_intersect_allowed,
20.               skip_records_in_range, &needed_fields, &imerge, best_cost,
21.               needed_reg);
22.       }
23.     }
24.   }
25. }
27. // 执行索引交集合并
28. AccessPath *get_best_ror_intersect() {
29.   // 遍历mm tree的索引数组，给tree->ror_scans数组和cpk_scan赋值，cpk_scan专门存放主键索引信息
30.   for (idx = 0, cur_ror_scan = tree->ror_scans; idx < param->keys; idx++) {
31.     tree->ror_scans = make_ror_scan();
32.   }
33.   // 对tree->ror_scans数组根据覆盖列个数和范围包含的行数进行排序，范围包含的行数越少排序越前。这个步骤排除了主键索引，因为主键在函数最后单独处理
34.   // 排序函数见下面is_better_intersect_match函数
35.   find_intersect_order(tree->ror_scans, tree->ror_scans_end, param,
36.                        needed_fields);
37.   // 按照上面的索引排序顺序进行交集操作，可以减少cost的索引进行交集操作，不能减少的排除
38.   while (cur_ror_scan != tree->ror_scans_end && !intersect->is_covering) {
39.     // 计算除了主键以外的所有索引的过滤系数、行数、cost并且累加到intersect变量
40.     ror_intersect_add(intersect);
41.   }
42.   // 计算主键列的过滤系数、行数、cost并与之前别的索引结果累加到intersect变量
43.   if (cpk_scan) ror_intersect_add(intersect);
44.   // 最后生成AccessPath
45.   AccessPath *path = new (param->return_mem_root) AccessPath;
46. }
48. // 对索引进行排序，可以看到覆盖的列越少，包含的行数越少，排序越靠前
49. static bool is_better_intersect_match(const ROR_SCAN_INFO *scan1,
50.                                       const ROR_SCAN_INFO *scan2) {
51.   if (scan1 == scan2) return false;
53.   if (scan1->num_covered_fields_remaining > scan2->num_covered_fields_remaining)
54.     return false;
56.   if (scan1->num_covered_fields_remaining < scan2->num_covered_fields_remaining)
57.     return true;
59.   return (scan1->records > scan2->records);
60. }
62. // 执行索引并集合并
63. static AccessPath *get_best_disjunct_quick() {
64.   // 按照索引遍历所有tree
65.   for (auto tree_it = imerge->trees.begin(); tree_it != imerge->trees.end();
66.          ++tree_it, cur_child++) {
67.     // 获取is_ror_scan值与table->quick_rows[keynr]区间范围行数，is_ror_scan为true才能执行索引合并，取值见表二
68.     get_key_scans_params();
69.   }
70.   // 如果所有索引都是ROR的，那么直接返回结果
71.   if (all_scans_rors && (index_merge_union_allowed || force_index_merge))
72.     return get_ror_union_path();
73.   // 如果不是所有索引都是ROR的，那么需要执行Sort-Union
74.   // 首先计算磁盘扫描的cost
75.   get_sweep_read_cost();
76.   // 如果扫描磁盘cost太大，那么继续执行Sort-Union
77.   // 索引去重cost估计
78.   dup_removal_cost = Unique::get_use_cost(
79.         (uint)non_cpk_scan_records, table->file->ref_length,
80.         // 这个系统变量见表七
81.         thd->variables.sortbuff_size, cost_model);
82.   // 执行索引Sort-Union
83.   get_ror_union_path();
84. }
86. static AccessPath *get_ror_union_path() {
87.   // 遍历所有tree元素，对每个元素执行Intersection Merge
88.   for (auto tree_it = imerge->trees.begin(); tree_it != imerge->trees.end();
89.          tree_it++, cur_child++, cur_roru_plan++) {
90.     get_best_ror_intersect();
91.   }
92.   // 计算磁盘扫描cost
93.   get_sweep_read_cost();
94.   // 生成AccessPath，这个AccessPath带有child即Intersection Merge的索引子集
95.   AccessPath *path = new (param->return_mem_root) AccessPath;
96.   path->rowid_union().children = children;
97. }

复制代码

表一：索引合并范例
索引合并范例说明对应代码举例交集合并（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()初值索引是倒序的falsecheck_quick_select()索引范例不即是SE_SPECIFIC和BTREE大概索引包罗虚拟列falsecheck_quick_select()索引是主键列truecheck_quick_select()列的第二个以上条件有右节点falsesel_arg_range_seq_next()见下面例子tree叶节点范围最大值和最小值不相当falsesel_arg_range_seq_next()见下面例子不是等号条件大概不为is_key_scan_ror()falsesel_arg_range_seq_next()即是条件的is_ror_scan=true is_key_scan_ror()比力索引列长度和插入数据的长度，不一致的话就返回false。比如创建索引字符集和插入数据的字符集不一致就会导致该列无法用index merge。

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

表三：tree->merges数组赋值条件
条件(以下必须全部满足)举例不同索引的OR条件where d2variables.sortbuff_size用在索引Sort-Union操作的时候对索引举行排序去重估计costOPTIMIZER_SWITCH_FAVOR_RANGE_SCAN索引合并执行在get_key_scans_params()的时候，如果这个设置为true，那么会对rows对应算出来的cost乘以系数0.1，让cost效果小十倍，这样效果就不会走索引合并而走RANGE_SCAN，见下面例子三、实际例子说明

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

1. greatsql> EXPLAIN SELECT * FROM t1 where c2=10 and c1<10;
2. +----+-------------+-------+------------+-------------+-------------------+--------------+---------+------+------+----------+--------------------------------------------+
3. | id | select_type | table | partitions | type        | possible_keys     | key          | key_len | ref  | rows | filtered | Extra                                      |
4. +----+-------------+-------+------------+-------------+-------------------+--------------+---------+------+------+----------+--------------------------------------------+
5. |  1 | SIMPLE      | t1    | NULL       | index_merge | PRIMARY,idx1,idx2 | idx1,PRIMARY | 9,4     | NULL |    1 |    58.33 | Using intersect(idx1,PRIMARY); Using where |
6. +----+-------------+-------+------------+-------------+-------------------+--------------+---------+------+------+----------+--------------------------------------------+
7.                   "analyzing_range_alternatives": {
8.                     "range_scan_alternatives": [
9.                       {
10.                         "index": "PRIMARY",
11.                         "ranges": [
12.                           "c1 < 10"
13.                         ],
14.                         "index_dives_for_eq_ranges": true,
15.                         "rowid_ordered": true, 这里为true说明这个索引是ROR的
16.                         "using_mrr": false,
17.                         "index_only": false,
18.                         "in_memory": 1,
19.                         "rows": 6,
20.                         "cost": 0.861465,
21.                         "chosen": true
22.                       },
23.                       {
24.                         "index": "idx1",
25.                         "ranges": [
26.                           "c2 = 10 AND c1 < 10"
27.                         ],
28.                         "index_dives_for_eq_ranges": true,
29.                         "rowid_ordered": true, 这里为true说明这个索引是ROR的
30.                         "using_mrr": false,
31.                         "index_only": false,
32.                         "in_memory": 1,
33.                         "rows": 2,
34.                         "cost": 0.96,
35.                         "chosen": false,
36.                         "cause": "cost"
37.                       },
38.                       {
39.                         "index": "idx2",
40.                         "ranges": [
41.                           "c2 = 10"
42.                         ],
43.                         "index_dives_for_eq_ranges": true,
44.                         "rowid_ordered": false, 这里为false说明这个索引不可以被合并
45.                         "using_mrr": false,
46.                         "index_only": true,
47.                         "in_memory": 0,
48.                         "rows": 2,
49.                         "cost": 1.21183,
50.                         "chosen": false,
51.                         "cause": "cost"
52.                       }
53.                     ],
54.                     "analyzing_roworder_intersect": {
55.                       "intersecting_indexes": [ 上面"rowid_ordered"数量加起来为2，因此可以执行索引交集
56.                         {
57.                           "index": "idx1",
58.                           "index_scan_cost": 0.250274, 满足c2 = 10行数2条，这个是对应2条的cost
59.                           "cumulated_index_scan_cost": 0.250274,
60.                           "disk_sweep_cost": 0.4375, 从硬盘读取数据的cost
61.                           "cumulated_total_cost": 0.687774, 这个值=index_scan_cost+disk_sweep_cost
62.                           "usable": true,
63.                           "matching_rows_now": 2,
64.                           "isect_covering_with_this_index": false,
65.                           "chosen": true
66.                         }
67.                       ],
68.                       "clustered_pk": {
69.                         "index_scan_cost": 0.1,
70.                         "cumulated_index_scan_cost": 0.350274, 这个值=index_scan_cost+上一个cumulated_index_scan_cost
71.                         "disk_sweep_cost": 0.25,
72.                         "clustered_pk_scan_added_to_intersect": true,
73.                         "cumulated_cost": 0.600274 这个值=disk_sweep_cost+cumulated_index_scan_cost，这里取两个索引交集以后的cost小于单独用idx1索引的cost 0.687774，说明索引交集确实提高了效率
74.                       },
75.                       "rows": 1.71429, 这个值=索引行数 * 过滤系数 =7 * 2/7 * 6/7，其中2是满足idx1条件c2=10的行数，6是满足主键列c1<10的行数
76.                       "cost": 0.600274,
77.                       "covering": false,
78.                       "chosen": true
79.                     }
80.                   },
81.                   "chosen_range_access_summary": {
82.                     "range_access_plan": {
83.                       "type": "index_roworder_intersect",
84.                       "rows": 1.71429,
85.                       "cost": 0.600274,
86.                       "covering": false,
87.                       "clustered_pk_scan": true,
88.                       "intersect_of": [
89.                         {
90.                           "type": "range_scan",
91.                           "index": "idx1",
92.                           "rows": 2,
93.                           "ranges": [
94.                             "c2 = 10 AND c1 < 10"
95.                           ]
96.                         }
97.                       ]
98.                     },
99.                     "rows_for_plan": 1.71429,
100.                     "cost_for_plan": 0.600274,
101.                     "chosen": true
102.                   }
103.                 }
104.               }
105.           {
106.             "considered_execution_plans": [
107.               {
108.                 "plan_prefix": [
109.                 ],
110.                 "table": "`t1`",
111.                 "best_access_path": {
112.                   "considered_access_paths": [
113.                     {
114.                       "access_type": "ref",
115.                       "index": "idx1",
116.                       "chosen": false,
117.                       "cause": "range_uses_more_keyparts"
118.                     },
119.                     {
120.                       "access_type": "ref", 这个是因为keyuse_array数组有值，所以根据索引单独计算
121.                       "index": "idx2",
122.                       "rows": 2,
123.                       "cost": 1.20183,
124.                       "chosen": true
125.                     },
126.                     {
127.                       "rows_to_scan": 1, 这个值等于上面索引交集算出来的"rows_for_plan": 1.71429
128.                       "filtering_effect": [
129.                       ],
130.                       "final_filtering_effect": 1,
131.                       "access_type": "range",
132.                       "range_details": {
133.                         "used_index": "intersect(idx1,PRIMARY)"
134.                       },
135.                       "resulting_rows": 1, 这个值等于"rows_to_scan"
136.                       "cost": 0.700274,
137.                       "chosen": true
138.                     }
139.                   ]
140.                 },
141.                 "condition_filtering_pct": 100,
142.                 "rows_for_plan": 1,
143.                 "cost_for_plan": 0.700274, 最后结果：在上面ref和range两种方法中选择了cost低的range方法即索引交集方法
144.                 "chosen": true
145.               }
146.             ]
147.           },

复制代码

排序合并

1. greatsql> CREATE TABLE t4 (d1 INT, d2 int, d3 varchar(100));
2. greatsql> INSERT INTO t4 VALUES (1,2,'aa1'),(2,1,'bb1'),(2,3,'cc1'),(3,3,'cc1'),(4,2,'ff1'),(4,4,'ert'),(4,2,'f5fg'),(null,2,'ee'),(5,30,'cc1'),(5,4,'fcc1'),(4,10,'cc1'),(6,4,'ccd1'),(null,1,'fee'),(1,2,'aa1'),(2,1,'bb1'),(2,3,'cc1'),(3,3,'cc1'),(4,2,'ff1'),(4,4,'ert'),(4,2,'f5fg'),(null,2,'ee'),(5,30,'cc1'),(5,4,'fcc1'),(4,10,'cc1'),(6,4,'ccd1'),(null,1,'fee'),(1,2,'aa1'),(2,1,'bb1'),(2,3,'cc1'),(3,3,'cc1'),(4,2,'ff1'),(4,4,'ert'),(4,2,'f5fg'),(null,2,'ee'),(5,30,'cc1'),(5,4,'fcc1'),(4,10,'cc1'),(6,4,'ccd1'),(null,1,'fee');
3. greatsql> CREATE INDEX idx4_1 ON t4(d1);
4. greatsql> CREATE INDEX idx4_2 ON t4(d2);
6. greatsql> EXPLAIN SELECT * FROM t4 where d2=4 or d1=10;
7. +----+-------------+-------+------------+-------------+---------------+---------------+---------+------+------+----------+-----------------------------------------+
8. | id | select_type | table | partitions | type        | possible_keys | key           | key_len | ref  | rows | filtered | Extra                                   |
9. +----+-------------+-------+------------+-------------+---------------+---------------+---------+------+------+----------+-----------------------------------------+
10. |  1 | SIMPLE      | t4    | NULL       | index_merge | idx4_2,idx4_1 | idx4_2,idx4_1 | 5,5     | NULL |   10 |   100.00 | Using union(idx4_2,idx4_1); Using where |
11. +----+-------------+-------+------------+-------------+---------------+---------------+---------+------+------+----------+-----------------------------------------+
12.                   "analyzing_range_alternatives": {
13.                     "range_scan_alternatives": [
14.                     ],
15.                     "analyzing_roworder_intersect": {
16.                       "usable": false,
17.                       "cause": "too_few_roworder_scans"
18.                     }
19.                   },
20.                   "analyzing_index_merge_union": [
21.                     {
22.                       "indexes_to_merge": [
23.                         {
24.                           "range_scan_alternatives": [
25.                             {
26.                               "index": "idx4_2",
27.                               "ranges": [
28.                                 "d2 = 4"
29.                               ],
30.                               "index_dives_for_eq_ranges": true,
31.                               "rowid_ordered": true, 这里为true说明这个索引是ROR的
32.                               "using_mrr": false,
33.                               "index_only": true,
34.                               "in_memory": 0,
35.                               "rows": 9,
36.                               "cost": 1.92074,
37.                               "chosen": true
38.                             }
39.                           ],
40.                           "index_to_merge": "idx4_2",
41.                           "cumulated_cost": 1.92074
42.                         },
43.                         {
44.                           "range_scan_alternatives": [
45.                             {
46.                               "index": "idx4_1",
47.                               "ranges": [
48.                                 "d1 = 10"
49.                               ],
50.                               "index_dives_for_eq_ranges": true,
51.                               "rowid_ordered": true, 这里为true说明这个索引是ROR的
52.                               "using_mrr": false,
53.                               "index_only": true,
54.                               "in_memory": 0,
55.                               "rows": 1,
56.                               "cost": 1.11,
57.                               "chosen": true
58.                             }
59.                           ],
60.                           "index_to_merge": "idx4_1",
61.                           "cumulated_cost": 3.03074 这个值=idx4_1索引对应的cost+idx4_2索引对应的cost=1.11+1.92074
62.                         }
63.                       ],
64.                       "cost_of_reading_ranges": 3.03074,
65.                       "use_roworder_union": true,
66.                       "cause": "always_cheaper_than_not_roworder_retrieval",
67.                       "analyzing_roworder_scans": [
68.                         {
69.                           "type": "range_scan",
70.                           "index": "idx4_2",
71.                           "rows": 9,
72.                           "ranges": [
73.                             "d2 = 4"
74.                           ],
75.                           "analyzing_roworder_intersect": {
76.                             "usable": false,
77.                             "cause": "too_few_roworder_scans"
78.                           }
79.                         },
80.                         {
81.                           "type": "range_scan",
82.                           "index": "idx4_1",
83.                           "rows": 1,
84.                           "ranges": [
85.                             "d1 = 10"
86.                           ],
87.                           "analyzing_roworder_intersect": {
88.                             "usable": false,
89.                             "cause": "too_few_roworder_scans"
90.                           }
91.                         }
92.                       ],
93.                       "index_roworder_union_cost": 4.47442,
94.                       "members": 2, 涉及2个索引
95.                       "chosen": true
96.                     }
97.                   ],
98.                   "chosen_range_access_summary": {
99.                     "range_access_plan": {
100.                       "type": "index_roworder_union",
101.                       "union_of": [
102.                         {
103.                           "type": "range_scan",
104.                           "index": "idx4_2",
105.                           "rows": 9,
106.                           "ranges": [
107.                             "d2 = 4"
108.                           ]
109.                         },
110.                         {
111.                           "type": "range_scan",
112.                           "index": "idx4_1",
113.                           "rows": 1,
114.                           "ranges": [
115.                             "d1 = 10"
116.                           ]
117.                         }
118.                       ]
119.                     },
120.                     "rows_for_plan": 10,
121.                     "cost_for_plan": 4.47442,
122.                     "chosen": true
123.                   }
124.                 }
125.               }
126.             ]
127.           },

复制代码

上面的例子改一个条件范围，效果变成不选择排序合并而选择全表扫描的环境。下面trace对比上面发现，少了"chosen_range_access_summary"，对比源码发现是imerge_cost > read_cost，因此被放弃。这里主要缘故原由是索引排序cost太高以是放弃了。

1. greatsql> EXPLAIN SELECT * FROM t4 WHERE d1 < 2 OR d2 > 20;
2. +----+-------------+-------+------------+-------------+---------------+---------------+---------+------+------+----------+----------------------------------------------+
3. | id | select_type | table | partitions | type        | possible_keys | key           | key_len | ref  | rows | filtered | Extra                                        |
4. +----+-------------+-------+------------+-------------+---------------+---------------+---------+------+------+----------+----------------------------------------------+
5. |  1 | SIMPLE      | t4    | NULL       | index_merge | idx4_2,idx4_1 | idx4_1,idx4_2 | 5,5     | NULL |    6 |   100.00 | Using sort_union(idx4_1,idx4_2); Using where |
6. +----+-------------+-------+------------+-------------+---------------+---------------+---------+------+------+----------+----------------------------------------------+
7.                   "analyzing_range_alternatives": {
8.                     "range_scan_alternatives": [
9.                     ],
10.                     "analyzing_roworder_intersect": {
11.                       "usable": false,
12.                       "cause": "too_few_roworder_scans"
13.                     }
14.                   },
15.                   "analyzing_index_merge_union": [
16.                     {
17.                       "indexes_to_merge": [
18.                         {
19.                           "range_scan_alternatives": [
20.                             {
21.                               "index": "idx4_1",
22.                               "ranges": [
23.                                 "NULL < d1 < 2"
24.                               ],
25.                               "index_dives_for_eq_ranges": true,
26.                               "rowid_ordered": false, 这里说明这个不是ROR索引，因为条件是一个范围不是等于
27.                               "using_mrr": false,
28.                               "index_only": true,
29.                               "in_memory": 1,
30.                               "rows": 3,
31.                               "cost": 0.560671,
32.                               "chosen": true
33.                             }
34.                           ],
35.                           "index_to_merge": "idx4_1",
36.                           "cumulated_cost": 0.560671
37.                         },
38.                         {
39.                           "range_scan_alternatives": [
40.                             {
41.                               "index": "idx4_2",
42.                               "ranges": [
43.                                 "20 < d2"
44.                               ],
45.                               "index_dives_for_eq_ranges": true,
46.                               "rowid_ordered": false, 这里说明这个不是ROR索引，因为条件是一个范围不是等于
47.                               "using_mrr": false,
48.                               "index_only": true,
49.                               "in_memory": 1,
50.                               "rows": 3,
51.                               "cost": 0.560671,
52.                               "chosen": true
53.                             }
54.                           ],
55.                           "index_to_merge": "idx4_2",
56.                           "cumulated_cost": 1.12134 这个值=两个索引累加=0.560671+0.560671
57.                         }
58.                       ],
59.                       "cost_of_reading_ranges": 1.12134,
60.                       "cost_sort_rowid_and_read_disk": 0.822021, 磁盘扫描的cost
61.                       "cost_duplicate_removal": 1.2282, 索引排序去重cost
62.                       "total_cost": 3.17157
63.                     }
64.                   ],
65.                   "chosen_range_access_summary": {
66.                     "range_access_plan": {
67.                       "type": "index_merge",
68.                       "index_merge_of": [
69.                         {
70.                           "type": "range_scan",
71.                           "index": "idx4_1",
72.                           "rows": 3,
73.                           "ranges": [
74.                             "NULL < d1 < 2"
75.                           ]
76.                         },
77.                         {
78.                           "type": "range_scan",
79.                           "index": "idx4_2",
80.                           "rows": 3,
81.                           "ranges": [
82.                             "20 < d2"
83.                           ]
84.                         }
85.                       ]
86.                     },
87.                     "rows_for_plan": 6,
88.                     "cost_for_plan": 3.17157,
89.                     "chosen": true
90.                   }
91.                 }
92.               }
93.             ]
94.           },

复制代码

无法使用index merge交集合并的环境

1. greatsql> EXPLAIN SELECT * FROM t4 WHERE d1 < 4 OR d2 > 20;
2. +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
3. | id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
4. +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
5. |  1 | SIMPLE      | t4    | NULL       | ALL  | idx4_2,idx4_1 | NULL | NULL    | NULL |   39 |    53.84 | Using where |
6. +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
7.           {
8.             "rows_estimation": [
9.               {
10.                 "table": "`t4`",
11.                 "range_analysis": {
12.                   "table_scan": {
13.                     "rows": 39,
14.                     "cost": 6.25 这个是全表扫描的cost
15.                   },
17.                   "analyzing_index_merge_union": [
18.                     {
19.                       "indexes_to_merge": [
20.                         {
21.                           "range_scan_alternatives": [
22.                             {
23.                               "index": "idx4_1",
24.                               "ranges": [
25.                                 "NULL < d1 < 4"
26.                               ],
27.                               "index_dives_for_eq_ranges": true,
28.                               "rowid_ordered": false,
29.                               "using_mrr": false,
30.                               "index_only": true,
31.                               "in_memory": 1,
32.                               "rows": 12,
33.                               "cost": 1.46369,
34.                               "chosen": true
35.                             }
36.                           ],
37.                           "index_to_merge": "idx4_1",
38.                           "cumulated_cost": 1.46369
39.                         },
40.                         {
41.                           "range_scan_alternatives": [
42.                             {
43.                               "index": "idx4_2",
44.                               "ranges": [
45.                                 "20 < d2"
46.                               ],
47.                               "index_dives_for_eq_ranges": true,
48.                               "rowid_ordered": false,
49.                               "using_mrr": false,
50.                               "index_only": true,
51.                               "in_memory": 1,
52.                               "rows": 3,
53.                               "cost": 0.560671,
54.                               "chosen": true
55.                             }
56.                           ],
57.                           "index_to_merge": "idx4_2",
58.                           "cumulated_cost": 2.02436
59.                         }
60.                       ],
61.                       "cost_of_reading_ranges": 2.02436,
62.                       "cost_sort_rowid_and_read_disk": 0.986637,
63.                       "cost_duplicate_removal": 4.42426,
64.                       "total_cost": 7.43526 索引合并cost大于全表扫描cost的6.25，因此不选择用索引合并
65.                     }
66.                   ]
67.                 }
68.               }
69.             ]
70.           },

复制代码

索引并集合并的子集是索引交集的环境

1. 列的第二个以上条件有右节点
2. greatsql> EXPLAIN SELECT * FROM t4 WHERE d2>=2 AND (d2>=6 or d2=4);
3. +----+-------------+-------+------------+-------+---------------+--------+---------+------+------+----------+-----------------------+
4. | id | select_type | table | partitions | type  | possible_keys | key    | key_len | ref  | rows | filtered | Extra                 |
5. +----+-------------+-------+------------+-------+---------------+--------+---------+------+------+----------+-----------------------+
6. |  1 | SIMPLE      | t4    | NULL       | range | idx4_2        | idx4_2 | 5       | NULL |   15 |   100.00 | Using index condition |
7. +----+-------------+-------+------------+-------+---------------+--------+---------+------+------+----------+-----------------------+
9. tree叶节点范围最大值和最小值不相等
10. greatsql> EXPLAIN SELECT * FROM t1 WHERE c1>=2 AND c2>=2 AND c2<=11;
11. +----+-------------+-------+------------+-------+-------------------+------+---------+------+------+----------+--------------------------+
12. | id | select_type | table | partitions | type  | possible_keys     | key  | key_len | ref  | rows | filtered | Extra                    |
13. +----+-------------+-------+------------+-------+-------------------+------+---------+------+------+----------+--------------------------+
14. |  1 | SIMPLE      | t1    | NULL       | range | PRIMARY,idx1,idx2 | idx2 | 5       | NULL |    4 |    85.71 | Using where; Using index |
15. +----+-------------+-------+------------+-------+-------------------+------+---------+------+------+----------+--------------------------+

复制代码

OPTIMIZER_SWITCH_FAVOR_RANGE_SCAN使用举例

1. greatsql> CREATE TABLE t5 (d1 INT, d2 int, d3 int, d4 int, d5 int);
2. greatsql> INSERT INTO t5 VALUES (1,2,1,2,4),(2,1,1,2,1),(2,3,6,9,10),(3,3,2,1,5),(4,2,9,22,4),(4,4,2,1,3),(4,2,7,3,5);
3. greatsql> create index idx4_1 on t5(d1);
4. greatsql> CREATE INDEX idx4_2 ON t5(d2);
5. greatsql> CREATE INDEX idx4_3 ON t5(d3);
6. greatsql> CREATE INDEX idx4_4 ON t5(d4);
7. greatsql> CREATE INDEX idx4_5 ON t5(d5);
9. greatsql> EXPLAIN SELECT * FROM t5 where d2=2 and d4=2 and d3=1 ;
10. +----+-------------+-------+------------+-------------+----------------------+---------------+---------+------+------+----------+---------------------------------------------+
11. | id | select_type | table | partitions | type        | possible_keys        | key           | key_len | ref  | rows | filtered | Extra                                       |
12. +----+-------------+-------+------------+-------------+----------------------+---------------+---------+------+------+----------+---------------------------------------------+
13. |  1 | SIMPLE      | t5    | NULL       | index_merge | idx4_2,idx4_3,idx4_4 | idx4_3,idx4_4 | 5,5     | NULL |    1 |    42.86 | Using intersect(idx4_3,idx4_4); Using where |
14. +----+-------------+-------+------------+-------------+----------------------+---------------+---------+------+------+----------+---------------------------------------------+
15. 这个sql通过find_intersect_order函数排序结果为idx4_3、idx4_4、idx4_2，因为idx4_3和idx4_4的rows都是2，但是因为idx4_3在sql位置靠前因此排序靠前。而idx4_2包含的rows是3，因此位置在最后。
16.                   "analyzing_range_alternatives": {
17.                     "range_scan_alternatives": [
18.                       {
19.                         "index": "idx4_2",
20.                         "ranges": [
21.                           "d2 = 2"
22.                         ],
23.                         "index_dives_for_eq_ranges": true,
24.                         "rowid_ordered": true,
25.                         "using_mrr": false,
26.                         "index_only": false,
27.                         "in_memory": 1,
28.                         "rows": 3,
29.                         "cost": 1.31,
30.                         "chosen": true
31.                       },
32.                       {
33.                         "index": "idx4_3",
34.                         "ranges": [
35.                           "d3 = 1"
36.                         ],
37.                         "index_dives_for_eq_ranges": true,
38.                         "rowid_ordered": true,
39.                         "using_mrr": false,
40.                         "index_only": false,
41.                         "in_memory": 1,
42.                         "rows": 2,
43.                         "cost": 0.96,
44.                         "chosen": true
45.                       },
46.                       {
47.                         "index": "idx4_4",
48.                         "ranges": [
49.                           "d4 = 2"
50.                         ],
51.                         "index_dives_for_eq_ranges": true,
52.                         "rowid_ordered": true,
53.                         "using_mrr": false,
54.                         "index_only": false,
55.                         "in_memory": 1,
56.                         "rows": 2,
57.                         "cost": 0.96,
58.                         "chosen": false,
59.                         "cause": "cost"
60.                       }
61.                     ],
62.                    "analyzing_roworder_intersect": {
63.                       "intersecting_indexes": [
64.                         {
65.                           "index": "idx4_3",
66.                           "index_scan_cost": 0.250336,
67.                           "cumulated_index_scan_cost": 0.250336,
68.                           "disk_sweep_cost": 0.4375,
69.                           "cumulated_total_cost": 0.687836,
70.                           "usable": true,
71.                           "matching_rows_now": 2,
72.                           "isect_covering_with_this_index": false,
73.                           "chosen": true
74.                         },
75.                         {
76.                           "index": "idx4_4",
77.                           "index_scan_cost": 0.250336,
78.                           "cumulated_index_scan_cost": 0.500671,
79.                           "disk_sweep_cost": 0,
80.                           "cumulated_total_cost": 0.500671,
81.                           "usable": true,
82.                           "matching_rows_now": 0.571429,
83.                           "isect_covering_with_this_index": false,
84.                           "chosen": true 合并完idx4_3和idx4_4以后发现cost变小，因此可以选择
85.                         },
86.                         {
87.                           "index": "idx4_2",
88.                           "index_scan_cost": 0.250671,
89.                           "cumulated_index_scan_cost": 0.751342,
90.                           "disk_sweep_cost": 0,
91.                           "cumulated_total_cost": 0.751342,
92.                           "usable": true,
93.                           "matching_rows_now": 0.244898,
94.                           "isect_covering_with_this_index": false,
95.                           "chosen": false,
96.                           "cause": "does_not_reduce_cost" 这里合并到idx4_2就发现cost大于单独用前两个索引，因此不继续进行交集操作了
97.                         }
98.                       ],
99.                       "clustered_pk": {
100.                         "clustered_pk_added_to_intersect": false,
101.                         "cause": "no_clustered_pk_index"
102.                       },
103.                       "rows": 1,
104.                       "cost": 0.500671,
105.                       "covering": false,
106.                       "chosen": true
107.                     }

复制代码

四、总结

从上面索引合并的应用例子我们认识了索引合并的使用场合和好处，也知道了索引合并的三种范例以及分别使用的条件，认识了ROR条件的判断和使用，还有相干系统变量，以上选择都是系统自动选择的，如果要改变效果只能添加OPTIMIZER_SWITCH逼迫改变索引的使用。

Enjoy GreatSQL
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。