MySQL优化器:index merge介绍
在MySQL官方手册上,关于index merge的介绍非常非常少。甚至还有不少误导的地方,这次把5.1版本关于此类优化处理的代码细看了一遍,以案例的方式介绍了各种实用index merge访问类型的SQL。后续的还会继续介绍index merge实现的主要数据结构,以及成本评估。
在MySQL官方手册上,关于index merge的介绍非常非常少。甚至还有不少误导的地方,这次把5.1版本关于此类优化处理的代码细看了一遍,以案例的方式介绍了各种实用index merge访问类型的SQL。后续的还会继续介绍index merge实现的主要数据结构,以及成本评估。
目录
- 1. 什么是index merge
- 1.1 index merge的限制:range优先
- 2. 关于index merge的一些案例
- 2.1 k1_p1 = 2 or k2_p1 = 4
- 2.2 (k1_p1=2 and k1_p2=7) or k2_p1=4\G
- 2.3 (k1_p1=2 or k1_p1=7) or k2_p1=4\G
- 2.4 (k1_p1=2 or k1_p2=7) or k2_p1=4\G
- 2.5 k1_p1=1 or (k1_p1=2 and k1_p2=4 and k2_p1=3)
- 2.6 嵌套的案例1
- 2.7 嵌套的案例2
- 3. 更多关于range优先原则
- 可以使用range的情况
- 4. 其他
- 4.1 type in MySQL Explain
- 4.2 示例中的表结构和数据
1. 什么是index merge
MySQL优化器如果发现可以使用多个索引查找后的交集/并集定位数据,那么MySQL优化器就会尝试index merge这类访问方式。index merge主要分为两大类,多个索引交集访问(intersections),多个索引并集访问,当然这两类还可以组合出更为复杂的方式,例如多个交集后做并集。
1.1 index merge的限制:range优先
MySQL在5.6.7之前,使用index merge有一个重要的前提条件:没有range可以使用。这个限制降低了MySQL index merge可以使用的场景。理想状态是同时评估成本后然后做出选择。因为这个限制,就有了下面这个已知的bad case(参考):
SELECT * FROM t1 WHERE (goodkey1 <p>优化器可以选择使用goodkey1和goodkey2做index merge,也可以使用badkey做range。因为上面的原则,无论goodkey1和goodkey2的选择度如何,MySQL都只会考虑range,而不会使用index merge的访问方式。这是一个悲剧...(5.6.7版本针对此有修复)</p> <h3 id="span-id-index-merge-关于index-merge的一些案例-span"><span id="2_index_merge">2. 关于index merge的一些案例</span></h3> <p>关于什么是交集/并集在手册中有详细介绍,这里不赘述。这里通过几个案例来看看,哪些情况使用交集,哪些情况使用并集,哪些情况使用更复杂的组合。</p> <p>示例中使用的表结构和数据参考本文4.2节。</p> <h4 id="span-id-k-p-or-k-p-k-p-or-k-p-span"><span id="21_k1_p1_2_or_k2_p1_4">2.1 k1_p1 = 2 or k2_p1 = 4</span></h4> <p>这是最典型,也是最简单的场景了:</p> <p>SELECT * FROM tmp_index_merge where key1_part1 = 2 or key2_part1 = 4</p> <pre class="brush:php;toolbar:false">explain SELECT * FROM tmp_index_merge where key1_part1 = 2 or key2_part1 = 4\G ...... table: tmp_index_merge type: index_merge key: ind1,ind2 key_len: 4,4 Extra: Using sort_union(ind1,ind2); Using where
2.2 (k1_p1=2 and k1_p2=7) or k2_p1=4\G
这个案例稍微复杂一丁点,第一个索引使用了两个字段:
explain SELECT * FROM tmp_index_merge where (key1_part1 = 2 and key1_part2 = 7) or key2_part1 = 4\G ...... table: tmp_index_merge type: index_merge key: ind1,ind2 key_len: 8,4 Extra: Using sort_union(ind1,ind2); Using where
2.3 (k1_p1=2 or k1_p1=7) or k2_p1=4\G
这个案例也能够使用index merge。内部的实现比它表面上看起来要复杂,这里简单解释一下:MySQL在递归处理这个WHERE条件时,先处理前一部分(key1_part1 = 2 or key1_part1 = 7)。对于同一个索引的同一个字段进行or操作,MySQL会将其合并成一颗SEL_ARG树(具体参考),两个条件通过SEL_ARG的Next/prev指针连接。MySQL的range访问方式可以通过遍历这棵树(也可以参考前面这篇文章)。接着优化器再处理or的另一个分支(key2_part1 = 4)发现可以使用第二个索引,于是将index merge加入可能的执行计划列表(后续评估成本,再决定是否实用该访问方式)。
explain SELECT * FROM tmp_index_merge where (key1_part1 = 2 or key1_part1 = 7) or key2_part1 = 4\G ...... table: tmp_index_merge type: index_merge key: ind1,ind2 key_len: 4,4 Extra: Using sort_union(ind1,ind2); Using where
2.4 (k1_p1=2 or k1_p2=7) or k2_p1=4\G
这种情况是无法直接使用任何索引的。不解释。
explain SELECT * FROM tmp_index_merge where (key1_part1 = 2 or key1_part2 = 7) or key2_part1 = 4\G ...... table: tmp_index_merge type: ALL possible_keys: ind1,ind2 key: NULL Extra: Using where
2.5 k1_p1=1 or (k1_p1=2 and k1_p2=4 and k2_p1=3)
对于这样的条件,MySQL会发现可以使用range访问方式。而根据前面的"range优先"原则,MySQL不再考虑index merge(这里k1_p1=1和k2_p1=3是可以通过index merge访问方式实现的)。MySQL在考虑使用key1访问的时候,看到的条件是:k1_p1=1 or (k1_p1=2 and k1_p2=4)。这里OR两边的条件可以构造成一颗独立的SEL_ARG。(本文后面小结“更多关于range优先原则”有更多详细介绍)
所以,MySQL会直接使用range,而不再考虑index merge。(怎样的条件无法够着成一颗SEL_ARG树,参考,对于两颗SEL_ARG通过or合并的时候,还有一些更复杂的事情,这里暂时不做介绍)
explain SELECT * FROM tmp_sel_tree where key1_part1=1 or (key1_part1=2 and key1_part2=4 and key2_part1=3)\G table: tmp_sel_tree type: range key: ind1 key_len: 8 Extra: Using where
如果前面这几个案例看明白了,那可以继续了,下面会有一些更复杂的案例:
2.6 嵌套的案例1
这个案例看起来很复杂,但其本质跟最前面提到的"已知的bad case"相同,是一个可以使用index merge,但是被range优先掉的案例。
SELECT * FROM tmp_sel_tree where ( key1_part1 = 1 or (key1_part2 = 2 and key2_part1 = 3) ) and ( key3_part1 = 5 )
2.7 嵌套的案例2
这个案例跟上面稍有不同,是一个三个索引的index merge,这里MySQL将考虑使用index merge。但是一般来说,这类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成本小于全表,则会使用:
table: tmp_sel_tree type: index_merge key: ind1,ind2,ind3 Extra: Using sort_union(ind1,ind2,ind3); Using where
3. 更多关于range优先原则
可以使用range的情况
在5.6.7之前的MySQL版本,只要可以使用Range访问方式,那就不会再使用index merge。因为可以使用range访问的WHERE条件是非常多的,除了我们常见的(k1_p1=const and k2_p2>const),如果参考Range优化相关的数据结构,还会看到更多的WHERE条件可以使用range。
这里拿出其中一个较为复杂的可以使用range访问的WHERE条件,做一个简单分析。
WHERE ( key1_part1 = 3 and key1_part2 > 5 and key2_part1 = 7 ) or ( key1_part1 > 2 )
对于索引key2来说,这个条件可以简化为如下,可以使用index merge的访问方式:
(TRUE AND TRUE AND key2_part1 = 7) OR ( key1_part1 <p>对于索引key1来说,条件简化为:</p> <pre class="brush:php;toolbar:false">(key1_part1 = 3 and key1_part2 > 5 and TRUE) or (key1_part1 > 2)
对于索引key1,这是一个可以使用range访问方式的条件。根据前文Range优化相关的数据结构可以构造成一颗SEL_ARG结构,如下:
$ $ SEL_ARG[2,∞) $ $ |^ $ $ next|| $ $ ||prev $ $ v| $ $ SEL_ARG[3,3] ==$====> SEL_ARG[5,∞] $ $ $
range访问会依次SEL_ARG,遍历访问。因为有range访问方式,所以这类条件不会再考虑index merge。
但如果WHERE是如下样子(OR后面条件是key1_part2而不是key1_part1):
WHERE ( key1_part1 = 3 and key1_part2 > 5 and key2_part1 = 7 ) or ( key1_part2 > 2 )
OR后面的key1_part2是无法与前面的key1条件合并成一颗SEL_ARG树,所以也就无法使用range访问。因为or后面条件无法独立使用索引访问,所以也同样无法做index merge访问。
4. 其他
4.1 type in MySQL Explain
在MySQL手册中把Explain中type列称为:"EXPLAIN Join Types"。这给很多人产生了误解,这里的Type实际是指在整个JOIN中这个单表的访问方式。例如:
id: 1 select_type: SIMPLE table: tmp_sel_tree type: index_merge possible_keys: ind1,ind2,ind3 key: ind1,ind2,ind3 key_len: 4,4,4
常见的单表访问方式有:const/ref/range/index/all
MySQL的优化器主要有两个自由度,一个是确定每个单表的访问方式。另一个就是访问顺序。博客中常说的使用"range优化" "index merge优化"也是指MySQL单表访问方式选择了"range"或者"index merge"。
4.2 示例中的表结构和数据
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', KEY `ind1` (`key1_part1`,`key1_part2`), KEY `ind2` (`key2_part1`,`key2_part2`,`key2_part3`), KEY `ind3` (`key3_part1`) ) ENGINE=InnoDB DEFAULT CHARSET=gbk 1 row in set (0.01 sec) root@test 11:33:22>select * from tmp_index_merge; +----+------------+------------+------------+------------+------------+------------+ | id | key1_part1 | key1_part2 | key2_part1 | key2_part2 | key2_part3 | key3_part1 | +----+------------+------------+------------+------------+------------+------------+ | 6 | 6 | 1 | 9 | 2 | 1 | 8 | | 8 | 9 | 9 | 1 | 6 | 6 | 6 | | 4 | 1 | 3 | 4 | 9 | 3 | 6 | | 10 | 9 | 7 | 5 | 7 | 1 | 2 | | 1 | 4 | 7 | 2 | 1 | 8 | 3 | | 6 | 6 | 3 | 9 | 3 | 9 | 7 | | 8 | 10 | 6 | 2 | 1 | 1 | 7 | | 0 | 9 | 4 | 4 | 8 | 7 | 6 | | 2 | 9 | 1 | 5 | 4 | 5 | 7 | | 2 | 7 | 10 | 6 | 1 | 8 | 6 | | 7 | 10 | 8 | 2 | 3 | 1 | 9 | | 7 | 3 | 3 | 7 | 7 | 2 | 10 | | 6 | 6 | 1 | 9 | 2 | 1 | 8 | | 8 | 9 | 9 | 1 | 6 | 6 | 6 | | 4 | 1 | 3 | 4 | 9 | 3 | 6 | | 10 | 9 | 7 | 5 | 7 | 1 | 2 | | 1 | 4 | 7 | 2 | 1 | 8 | 3 | | 6 | 6 | 3 | 9 | 3 | 9 | 7 | | 8 | 10 | 6 | 2 | 1 | 1 | 7 | | 0 | 9 | 4 | 4 | 8 | 7 | 6 | | 2 | 9 | 1 | 5 | 4 | 5 | 7 | | 2 | 7 | 10 | 6 | 1 | 8 | 6 | | 7 | 10 | 8 | 2 | 3 | 1 | 9 | | 7 | 3 | 3 | 7 | 7 | 2 | 10 | +----+------------+------------+------------+------------+------------+------------+

热AI工具

Undresser.AI Undress
人工智能驱动的应用程序,用于创建逼真的裸体照片

AI Clothes Remover
用于从照片中去除衣服的在线人工智能工具。

Undress AI Tool
免费脱衣服图片

Clothoff.io
AI脱衣机

AI Hentai Generator
免费生成ai无尽的。

热门文章

热工具

记事本++7.3.1
好用且免费的代码编辑器

SublimeText3汉化版
中文版,非常好用

禅工作室 13.0.1
功能强大的PHP集成开发环境

Dreamweaver CS6
视觉化网页开发工具

SublimeText3 Mac版
神级代码编辑软件(SublimeText3)

热门话题

MySQL是一个开源的关系型数据库管理系统。1)创建数据库和表:使用CREATEDATABASE和CREATETABLE命令。2)基本操作:INSERT、UPDATE、DELETE和SELECT。3)高级操作:JOIN、子查询和事务处理。4)调试技巧:检查语法、数据类型和权限。5)优化建议:使用索引、避免SELECT*和使用事务。

可以通过以下步骤打开 phpMyAdmin:1. 登录网站控制面板;2. 找到并点击 phpMyAdmin 图标;3. 输入 MySQL 凭据;4. 点击 "登录"。

MySQL是一种开源的关系型数据库管理系统,主要用于快速、可靠地存储和检索数据。其工作原理包括客户端请求、查询解析、执行查询和返回结果。使用示例包括创建表、插入和查询数据,以及高级功能如JOIN操作。常见错误涉及SQL语法、数据类型和权限问题,优化建议包括使用索引、优化查询和分表分区。

选择MySQL的原因是其性能、可靠性、易用性和社区支持。1.MySQL提供高效的数据存储和检索功能,支持多种数据类型和高级查询操作。2.采用客户端-服务器架构和多种存储引擎,支持事务和查询优化。3.易于使用,支持多种操作系统和编程语言。4.拥有强大的社区支持,提供丰富的资源和解决方案。

Redis 使用单线程架构,以提供高性能、简单性和一致性。它利用 I/O 多路复用、事件循环、非阻塞 I/O 和共享内存来提高并发性,但同时存在并发性受限、单点故障和不适合写密集型工作负载的局限性。

MySQL和SQL是开发者必备技能。1.MySQL是开源的关系型数据库管理系统,SQL是用于管理和操作数据库的标准语言。2.MySQL通过高效的数据存储和检索功能支持多种存储引擎,SQL通过简单语句完成复杂数据操作。3.使用示例包括基本查询和高级查询,如按条件过滤和排序。4.常见错误包括语法错误和性能问题,可通过检查SQL语句和使用EXPLAIN命令优化。5.性能优化技巧包括使用索引、避免全表扫描、优化JOIN操作和提升代码可读性。

MySQL在数据库和编程中的地位非常重要,它是一个开源的关系型数据库管理系统,广泛应用于各种应用场景。1)MySQL提供高效的数据存储、组织和检索功能,支持Web、移动和企业级系统。2)它使用客户端-服务器架构,支持多种存储引擎和索引优化。3)基本用法包括创建表和插入数据,高级用法涉及多表JOIN和复杂查询。4)常见问题如SQL语法错误和性能问题可以通过EXPLAIN命令和慢查询日志调试。5)性能优化方法包括合理使用索引、优化查询和使用缓存,最佳实践包括使用事务和PreparedStatemen

构建 SQL 数据库涉及 10 个步骤:选择 DBMS;安装 DBMS;创建数据库;创建表;插入数据;检索数据;更新数据;删除数据;管理用户;备份数据库。
