MySQL Index Merge
date
Apr 11, 2024
slug
mysql-index-merge
status
Published
tags
Tech
MySQL
summary
多列索引与索引合并
type
Post
今天朋友在群里突然问了个问题，已知
t1 表有2000w条数据，col1有索引，下面的sql怎么优化， 群友们积极讨论，有人说不用加索引，有人说可以给time列加一个索引，有人说应该建立联合索引。从大家的讨论中我们可以引申出来这几个问题：
- 如果表里有多个单独列的索引，MySQL如何让它们同时工作？
- 联合索引和多个单独索引哪个性能好？
- 这个案例的最佳实践是什么？
那么我们就来从索引原理结合实践分析一下各种创建索引方式之间的区别。
索引
我们先回顾一下MySQL如何使用索引这个问题，MySQL索引是使用了一种叫做B+ TREE的数据结构，这种结构能够快速查找任何前缀的数据，并按排序顺序遍历值之间的范围。例如，当您使用单列 索引查询
col1 between 100 and 200 时，MySQL 将在索引树中查找第一个匹配100这个值的row，然后继续按顺序扫描索引，直到遇到 col1 的值超过 200。索引的筛选结果通常（除非它覆盖索引）是MySQL的一个“行指针”，它可以是主键值（对于Innodb表），物理文件偏移量（对于MyISAM表）或其他东西，使得内部存储引擎通过该值来查找与给定索引条目对应的完整行数据。
创建表
可以看到在这个表中，我们有列 i1 和 i2，这张表大约有10,000,00 行数据。
首先是第一个例子
我们可以看到结果，MySQL会默认使用联合主键查询，实际耗时大概在
10ms左右。接下来是第二个例子，我们假设只有单列索引（通过提示优化器忽略组合索引）效果如何呢？
这个时候MySQL会同时使用这两个索引，MySQL对两个索引查询出来的结果集取交集，查询时间大约在
70ms内完成，这是一个很大的差异。我们再假设我们只有i1单个索引。我们从执行计划中可以看到，查询扫描了很多行，查询时间大约在
290ms 左右因此我们可以得出结论，与仅使用单个索引相比，索引合并确实提高了查询性能，但使用多列索引才是最佳实践。
Index Merge 失效
我们再来看下Index Merge 在一些特殊的场景下会出现什么问题。
从这个例子可以看出，一旦我们的某个条件是枚举而不是相等，MySQL将不再选择索引合并。
接下来我修改了数据，使列 i1 和 i2 完全相同，让我们看看在这种情况下会发生什么。
嗯......在这种情况下，优化器决定使用index merge，但是这可能是一个糟糕的决定。事实上，查询需要
360 ms ，请注意，“rows”的估计值在这里也是错误的。发生这种情况是因为 Optimizer 假定列 i1 和 i2 是独立的，用于估计交集的选择性，但是没有可用的相关性统计信息。
接下来如果我们让MySQL优化器忽略第二个索引index(i2)，因此索引合并，它会变成什么？它并不会直接选择组合索引，而是用了另一列上的单个索引。这是因为MySQL能够估计使用两个索引找到的行数，并且由于它们大致相同，因此会选择较小的索引。查询需要
290 ms。那么如果我们让MySQL别无选择，只能使用组合索引怎么办：
我们可以在这里看到 MySQL 估计要遍历的行数增加了 20%，这当然是错误的——它不会比仅使用索引前缀时需要scan的行数更多。但是MySQL不知道，所以它分别查看来自不同索引的统计数据，而不是试图以某种方式来协调它们。
由于索引比较长，索引查询的执行时间稍长 –
300ms因此，在这种情况下我们可以看到选择了索引合并，即使它被证明是最糟糕的计划。尽管从技术上讲，考虑到MySQL可用的统计数据，这是正确的计划。
Index merge
我们最后再来总结一下什么是 Index Merge， 技术如果简单的说，其实就是：对多个索引分别进行条件扫描，然后将它们各自的结果进行合并(intersect/union)。
- MySQL5.0之前，一个表一次只能使用一个索引，无法同时使用多个索引分别进行条件扫描
- 我们的 where 中可能有多个条件(或者join)涉及到多个字段，它们之间进行 AND 或者 OR，那么此时就有可能会使用到 index merge 技术。index merge 技术如果简单的说，其实就是：对多个索引分别进行条件扫描，然后将它们各自的结果进行合并(intersect/union)
总结
- 现在我们再回到文章开头的问题，看完整篇文章之后，答案就呼之欲出了，最好的方案是创建一个
col1和time的联合索引，这样通过这个索引就可以筛选出我们需要查询的数据。
- 如果对
col1和time分别创建索引也可以，但是性能上没有联合索引号，MySQL通过Index Merge优化对两个索引筛选出来的结果取交集，但是在实际应用中我们往往不会只有这两个单纯的条件，其他条件有可能会导致index merge优化失效，我们在使用的时候一定要仔细。
