MySQL学习笔记(七)--Index Merge
什么是Index Merge
1.Index Merge
The Index Merge access method retrieves rows with multiple range scans and merges their results into one. This access method merges index scans from a single table only, not scans across multiple tables. The merge can produce unions, intersections, or unions-of-intersections of its underlying scans.
举例说明
SELECT * FROM tbl_name WHERE key1 = 10 OR key2 = 20; SELECT * FROM tbl_name WHERE (key1 = 10 OR key2 = 20) AND non_key = 30; SELECT * FROM t1, t2 WHERE (t1.key1 IN (1,2) OR t1.key2 LIKE 'value%') AND t2.key1 = t1.some_col; SELECT * FROM t1, t2 WHERE t1.key1 = 1 AND (t2.key1 = t1.some_col OR t2.key2 = t1.some_col2);
2.使用限制
• If your query has a complex WHERE clause with deep AND/OR nesting and MySQL does not choose the optimal plan, try distributing terms using the following identity transformations:
(x AND y) OR z => (x OR z) AND (y OR z) (x OR y) AND z => (x AND z) OR (y AND z)
• Index Merge is not applicable to full-text indexes.
3.Index Merge与EXPLAIN output
In EXPLAIN output, the Index Merge method appears as index_merge in the type column. In this case, the key column contains a list of indexes used, and key_len contains a list of the longest key parts for those indexes. The Index Merge access method has several algorithms, which are displayed in the Extra field of EXPLAIN output:Using intersect(...) 或者Using union(...)或者Using sort_union(...)
The Index Merge access method has three algorithms
4.Index Merge Intersection Access Algorithm
This access algorithm is applicable when a WHERE clause is converted to several range conditions on different keys combined with AND, and each condition is one of the following:
• An N-part expression of this form, where the index has exactly N parts (that is, all index parts are covered):
key_part1 = const1 AND key_part2 = const2 ... AND key_partN = constN
• Any range condition over the primary key of an InnoDB table.
例如:
SELECT * FROM innodb_table WHERE primary_key < 10 AND key_col1 = 20; SELECT * FROM tbl_name WHERE key1_part1 = 1 AND key1_part2 = 2 AND key2 = 2;
The Index Merge intersection algorithm performs simultaneous(同时的、同步的) scans on all used indexes and produces the intersection of row sequences that it receives from the merged index scans.
If all columns used in the query are covered by the used indexes, full table rows are not retrieved (EXPLAIN output contains Using index in Extra field in this case).
If the used indexes do not cover all columns used in the query, full rows are retrieved only when the range conditions for all used keys are satisfied.
If one of the merged conditions is a condition over the primary key of an InnoDB table, it is not used for row retrieval, but is used to filter out rows retrieved using other conditions.
5.Index Merge Union Access Algorithm
The criteria(标准、原则) for this algorithm are similar to those for the Index Merge intersection algorithm. The algorithm is applicable when the table's WHERE clause is converted to several range conditions on different keys combined with OR, and each condition is one of the following:
• An N-part expression of this form, where the index has exactly N parts (that is, all index parts are covered):
key_part1 = const1 AND key_part2 = const2 ... AND key_partN = constN
• Any range condition over a primary key of an InnoDB table.
• A condition for which the Index Merge intersection algorithm is applicable
例如:
SELECT * FROM t1 WHERE key1 = 1 OR key2 = 2 OR key3 = 3; SELECT * FROM innodb_table WHERE (key1 = 1 AND key2 = 2) OR (key3 = 'foo' AND key4 = 'bar') AND key5 = 5;
6.Index Merge Sort-Union Access Algorithm
This access algorithm is applicable when the WHERE clause is converted to several range conditions combined by OR, but the Index Merge union algorithm is not applicable.
例如:
SELECT * FROM tbl_name WHERE key_col1 < 10 OR key_col2 < 20; SELECT * FROM tbl_name WHERE (key_col1 > 10 OR key_col2 = 20) AND nonkey_col = 30;
The difference between the sort-union algorithm and the union algorithm is that the sort-union algorithm must first fetch row IDs for all rows and sort them before returning any rows.
---转载MySQL官网文档
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 开发中对象命名的一点思考
· .NET Core内存结构体系(Windows环境)底层原理浅谈
· C# 深度学习:对抗生成网络(GAN)训练头像生成模型
· .NET 适配 HarmonyOS 进展
· .NET 进程 stackoverflow异常后,还可以接收 TCP 连接请求吗?
· 本地部署 DeepSeek:小白也能轻松搞定!
· 如何给本地部署的DeepSeek投喂数据,让他更懂你
· 在缓慢中沉淀,在挑战中重生!2024个人总结!
· 大人,时代变了! 赶快把自有业务的本地AI“模型”训练起来!
· 从 Windows Forms 到微服务的经验教训
2018-11-11 Elasticsearch 安装操作手册
2018-11-11 SQL Server 数据库调整表中列的顺序操作