indexmerge的数据结构和成本评估
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了indexmerge的数据结构和成本评估，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含10342字，纯文字阅读大概需要15分钟。
内容图文
前面以案例的形式介绍了什么是index merge，以及它的使用场景。本文将介绍index merge实现的主要数据结构以及MySQL如何评估index merge的成本。在开始本文之前，需要先理解Range访问相关的数据结构介绍：SEL_ARG结构，SEL_TREE结构。 1. 概述:index merge的
前面以案例的形式介绍了什么是index merge，以及它的使用场景。本文将介绍index merge实现的主要数据结构以及MySQL如何评估index merge的成本。在开始本文之前，需要先理解Range访问相关的数据结构介绍：SEL_ARG结构，SEL_TREE结构。
1. 概述:index merge的数据结构
index merge的主要数据结构仍然是存放在SEL_TREE中：
class SEL_TREE :public Sql_alloc
{
...
List merges;
...
};
在merges这个list中存放了所有可能的index merge。本文将从几个案例，来看看SEL_TREE/SEL_IMERGE如何代表一个index merge访问方式。本文将不再重复介绍SEL_ARG/SEL_TREE的Range相关结构。
SEL_IMERGE的主要成员是一个SEL_TREE的链表，每一个SEL_TREE代表了一个独立的索引条件，这个链表中多个条件共同构成这个index merge。我们通过两个案例看看一个SEL_TREE如何表示一个index merge。(这里需要注意，SEL_TREE既可以代表一个RANGE条件，也可以代表一个index merge；代表Range时，其merges成员为空)。
2. 案例1:简单的index merge
SELECT * FROM tmp_sel_tree where ( key1_part1 = 1 or (key1_part2 = 2 and key2_part1 = 3) ) or ( key3_part1 = 5 )
这是一个多个索引的index merge，且没有任何的range可以使用。or条件的三个分支，分表可以使用一个独立的索引，其构成的SEL_TREE结构如下：
SEL_TREE | |-->Listmerges; | | / SEL_TREE-> SEL_ARG(key1_part1 = 1) \ SEL_IMERGE1 | SEL_TREE-> SEL_ARG(key2_part1 = 3) \ SEL_TREE-> SEL_ARG(key3_part1 = 5)
3. 案例2:单个查询多个index merge
SELECT * FROM tmp_sel_tree where ( key1_part1 = 1 or (key1_part2 = 2 and key2_part1 = 3) ) and ( key3_part1 = 5 or key2_part1 = 5)
这个案例中，And条件两边都可以各自使用index merge，MySQL可以选择其中任何一个执行。对应的SEL_TREE中，将会有多个SEL_IMERGE对象，每个SEL_IMERGE对象里面存储了多个独立的可以使用索引的条件(有独立的SEL_TREE表示)：
SEL_TREE | \-->Listmerges; | | / SEL_TREE-> SEL_ARG(key1_part1 = 1) | SEL_IMERGE1 | SEL_TREE-> SEL_ARG(key2_part1 = 3) | \ SEL_TREE-> SEL_ARG(key3_part1 = 5) | | / SEL_TREE-> SEL_ARG(key2_part1 = 5) \ SEL_IMERGE2 | \ SEL_TREE-> SEL_ARG(key3_part1 = 5)
MySQL会在选择执行计划时，逐一评估每个SEL_IMERGE的成本，然后选择最优的执行计划。
4. 成本计算
MySQL在计算index merge的成本时，分开考虑了ROR和non-ROR的场景。所以这里先单独介绍一下什么是ROR，后面再介绍MySQL如何区别对待ROR的成本计算。
4.1 什么是Rowid-Ordered Retrieval
Rowid-Ordered Retrieval简称ROR。看下面的说明。有基于索引的查询：
"key_1=c_1 AND ... AND key_n=c_n"
该索引定义为：(key_1, ..., key_N [,a_1, ..., a_m])，且主键列为(a_1, ..., a_m, b1, ..., b_k)，并且n >= N。
那么这个查询就是一个ROR查询。简单说明：对于该索引左前缀(key_1,...key_n)都是定值，对应该值的子树顺序是按照剩余索引列来排序的，而剩余的索引列又都是主键最左前缀，所以子树的顺序恰好同主键顺序相同。
(这一段可以参考函数is_key_scan_ror的注释和实现部分)
示例：
CREATE TABLE `tmp_index_merge` ( `id` int(11) NOT NULL, `key1_part1` int(11) NOT NULL, `key1_part2` int(11) NOT NULL, `key2_part1` int(11) NOT NULL, `key2_part2` int(11) NOT NULL, `key2_part3` int(11) NOT NULL, `key3_part1` int(11) NOT NULL DEFAULT '4', PRIMARY KEY (`id`), KEY `ind2` (`key2_part1`,`key2_part2`,`key2_part3`), KEY `ind1` (`key1_part1`,`key1_part2`,`id`), KEY `ind3` (`key3_part1`,`id`) ) ENGINE=InnoDB; explain select * from tmp_index_merge where (key1_part1 = 4333 and key1_part2 = 1657) or (key3_part1 = 2877); j+----+-------------+-----------------+-------------+---------------+-----------+---------+------+------+-------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-----------------+-------------+---------------+-----------+---------+------+------+-------------------------------------+ | 1 | SIMPLE | tmp_index_merge | index_merge | ind1,ind3 | ind1,ind3 | 8,4 | NULL | 2 | Using union(ind1,ind3); Using where | +----+-------------+-----------------+-------------+---------------+-----------+---------+------+------+-------------------------------------+ 这就是一个ROR的index查询。ROR在Explain的执行计划中并没有任何体现，通过在代码中设置断点可以观察到。在函数get_best_disjunct_quick中，代码会跳到标签skip_to_ror_scan处执行。
在对index merge的成本评估时，只有所有的SEL_TREE子树都是ROR的，对应的SEL_IMERGE才是ROR的。后面我们将看看ROR和non-ROR在成本评估上的不同。
4.2 成本概述
一个index merge是由多个SEL_TREE子树组成，每个SEL_TREE对应一个range操作(参考)，所以每个SEL_TREE成本仍然会按照range操作类似各自计算成本，并累加。
各个SEL_TREE子树各自获取ROWIDs后，MySQL需要对这些ROWID进行去重，最后根据ROWID获取所有数据。去重操作其实是一个对多组ROWID归并排序的问题。对于ROR和non-ROR场景归并排序复杂度略有不同。对于non-ROR的场景，需要先进行分组排序，然后合并。而对于ROR，因为ROWID是顺序的，所以前面的分组排序就省略了，直接做合并操作，这让non-ROR和ROR在成本计算上有较大的不同。
在完成去重之后，最后是根据ROWID取出主键的成本(对应的二级索引里面取出的ROWID)。
一个细节：如果某个SEL_TREE对应的索引恰好是主键索引时，那么MySQL会在其他SEL_TREE子树扫描时，直接判断扫描出来的ROWID是否在主键对应的SEL_TREE的range内，如果这个ROWID已经存在，则不在记录。这样可以尽可能的减少归并排序的元素个数。我们称这部分成本味"二级ROWID过滤成本"。
4.3 SEL_TREE子树的成本
这部分成本计算与range成本计算相同(参考)，这里会将多个子树成本单独计算并累加。
for (every SEL_TREE IN SEL_IMERGE){
cur_child= get_key_scans_params(param, *ptree, TRUE, FALSE, read_time);
imerge_cost += (*cur_child)->read_cost;
......
}
4.4 non-ROR场景的成本计算
这里通过排序进行去重，是典型的归并排序，如果超过MySQL排序内存的限制，则是典型的外排序。先分组做红黑树排序，然后进行合并。成本分为几部分：创建红黑树、外排时磁盘读写、最后顺序读取排序结果。
4.4.1 去重复成本计算概述
这部分的成本可以完整的参考函数Unique::get_use_cost，这里做一个较为详细的补充说明。
对这个问题做一个简单的抽象：有两部分数据，第一部分有cpk_scan_records条，已排序。第二部分有non_cpk_scan_records未排序，现在需要返回去重后所有数据。单条数据大小为key_size，可用内存为max_in_memory_size。因为前面对第二部数据做了"二级ROWID过滤"，所以这部分ROWID跟第一部分没有重复。因此，仅这里的第二部分数据需要进行去重。去重通过一个排序实现。
简单的说，需要对non_cpk_scan_records条记录进行外排序，最大可用内存是max_in_memory_size，单条记录大小是key_size。排序分成两部分，对部分数据做排序，然后合并。
4.4.2 二级ROWID过滤成本
如果有子树SEL_TREE是对应主键聚簇索引，另一部分子树SEL_TREE对应二级索引，那么在遍历二级索引时将取出对应的ROWID，看看是否再聚簇索引的SEL_TREE子树中，如果是，那么可以忽略这个ROWID，以免重复计算(减少后面Unique操作)。这部分的成本计算为：
imerge_cost += non_cpk_scan_records / TIME_FOR_COMPARE_ROWID;
另外，这里记cpk_scan_records为主键聚簇索引对应的SEL_TREE返回的ROWID数量，non_cpk_scan_records为二级索引对应的所有SEL_TREE返回的ROWID数量。
4.4.3 排序比较成本
需要进行N=non_cpk_scan_records*key_size/max_in_memory_size次排序。在每次排序过程中，如果已经排序好的记录树m个，那么新增一条记录平均需要做log2(m+1)次比较操作，m取值是从1,2...N。比较操作的成本为log2((m+1)!)，MySQL使用了如下公式计算log2((m+1)!)：
\[n! \approx \sqrt{2{\pi}n}(\frac{n}{e})^n\]
\[\log{n!} \approx \log{\sqrt{2{\pi}n}} + n*\log{\frac{n}{e}} \]
这里log是2为底数，再使用\[log_{n}{m} = \frac{\lg{n}}{\lg{m}}\] 通过此公式底数都可以转换为10进行运算(这一部并不是必须的，不过MySQL是这样计算的)。
阶乘转换参考：斯特靈公式(口味略重，慎入)。
对应的代码段：
result+= n_full_trees * log2_n_fact(max_elements_in_tree + 1.0); result /= TIME_FOR_COMPARE_ROWID;
4.4.4 外排序时候的磁盘IO成本
在外排的时候，需要对所有的数据进行一次IO操作，成本计算如下：
293 result += DISK_SEEK_BASE_COST * n_full_trees * max_elements_in_tree / IO_SIZE; 295 result += DISK_SEEK_BASE_COST * key_size * last_tree_elems / IO_SIZE;
第一行是完整树的IO成本，第二部分是最后一个可能不完整树的IO成本。
4.4.5 合并成本
最后是合并成本，这是一个典型的归并排序，是对K个有序列表进行归并，时间复杂度为：
\[O(N*\lg{K})\]
归并过程中有一次读写操作，IO和比较成本加起来就是合并的成本：
\[\frac{total\_buf\_elems*\log(n\_buffers)}{TIME\_FOR\_COMPARE\_ROWID*\log2} + 2*\frac{total\_buf\_elems*elem\_size}{IO\_SIZE} \]
total_buf_elems是总元素个数；n_buffers子树数量；elem_size为单个元素大小。
未尽的细节：MySQL一次最多对15(MERGEBUFF2)颗子树做归并。
4.4.6 最后的读取
这时，完成了所有的排序操作，最后是读取结果到内存的成本：
result += ceil((double)key_size*nkeys/IO_SIZE);
4.4.7 根据ROWID取出记录的成本
所有非聚簇索引扫描获得ROWID后，最后仍然需要根据这些ROWID获取记录。
对于索引组织表(聚簇索引，InnoDB)，这部分的成本计算较为简单，假设聚簇索引的总page为total_pages，这里二级索引取出的rowid数量为rows，该表的总记录树为total_rows，那么成本为：
(rows / total_rows) *total_pages
代码参考：
imerge_cost += get_sweep_read_cost(param, non_cpk_scan_records);
4.5 ROR场景的成本计算
ROR的时候，去重时则少了对子队列的排序，直接是对多个已经排列好的队列做合并排序。所以这里的成本计算相对简单：索引读取，合并排序，最后是根据ROWID取出所有记录的成本。
4.5.1 索引读取成本
这部分计算与索引覆盖扫描计算相同。假设单个索引块大小为BS，索引字段长度味KL，ROWID长度为RL，总是假设索引块有50%为空，如果需要扫描的记录数为RS，那么这部分成本计算为：
\[\frac{RS}{\frac{1}{2}\frac{BS}{(KL+RL)}}\]
参考函数get_index_only_read_time的实现。
4.5.2 合并排序
这次合并排序，是对多个有序列表的合并。若有K个有序列表，总记录数味N，那么其成本为：
\[O(N*\lg{K})\]
这里N为各个SEL_TREE子树对应found_records之和(MySQL这里的计算略微不同)。
4.5.3 根据ROWID取出记录的成本
这部分成本于NON-ROR场景相同，对于索引组织表(聚簇索引，InnoDB)，这部分的成本计算较为简单，假设聚簇索引的总page为total_pages，这里二级索引取出的rowid数量为rows，该表的总记录树为total_rows，那么成本为：
(rows / total_rows) *total_pages
在MySQL中，对于上面表达式的rows计算做了一些不一样的处理。这里说一下主要思想，MySQL假设每个SEL_TREE完全独立，总记录数味R，如果有三个SEL_TREE子树，记对应的记录数为R(1),R(2),R(3)。如果数据都均匀分布，那么去重后总记录数为：
(R(1)+R(2)+R(3)) - R(a)*(R(1)*R(2)+R(2)+R(3)+R(1)*R(3))/R(a)^2 + R(a)*((R(1)*R(2)*R3)/R(a)^3)
MySQL这里做了一个近似：
(R(1)+R(2)+R(3)) - R(a)*((R(1)*R(2)*R3)/R(a)^3)
MySQL利用这个近似值作为上面公式的rows。到这里ROR部分成本就完成了。
5 最后
最后，如果index merge的成本比其他执行计划的成本要更小的话，那么MySQL就会选择改执行计划。案例可以参考index merge介绍。
原文地址：index merge的数据结构和成本评估, 感谢原作者分享。
内容总结
以上是互联网集市为您收集整理的indexmerge的数据结构和成本评估全部内容，希望文章能够帮你解决indexmerge的数据结构和成本评估所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。