MySQL Index Merge Optimization
2024-10-11 23:55:51
Index Merge用在通过一些range scans得到检索数据行和合并成一个整体。合并可以通过 unions,intersections,或者unions-intersection运用在底层的扫描上。合并Index scans结果只能在一个表中，不能合并多张表的scans结果；
在explain执行计划输出中，index merge方法出现在 type 列，显示index merge，这种情况下key 列显示一列使用indexes，并且key_len列包含一列这些indexes 的最长部分；
Examples:
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);
Index Merge 方法有许多access算法 ( Extra 列 Explain 输出):
Using intersect(...) Using union(...) Using sort_union(...)
1：如果查询语句在含有复杂的内嵌的深度and / or where语句，Mysql不会选择最佳的执行计划，
(x AND y) OR z = (x OR z) AND (y OR z)
(x OR y) AND z = (x AND z) OR (y AND z)
2：index merge 不适用在full-text index；
The Index Merge Intersection Access Algorithm
该access算法在运用在,当where子句中不同的index range conditions用 and 组合时：
1：index包括 N部分（所有index部分都包含）:
key_part1=const1 AND key_part2=const2 ... AND key_partN=constN
2: 任何innodb主键上范围条件；
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;
Index merge intersection 算法同时在所有使用的indexes上执行scan，并且从合并的Index扫描中产生行序列的交集；
如果查询中的所有列都被使用的indexes所涵盖，完整的数据行不会被回访（只访问索引数据）（此时explain 输出包含显示using index 在extra列）:
SELECT COUNT(*) FROM t1 WHERE key1=1 AND key2=1;
如果使用的indexes并不涵盖查询中的所有列，完整的数据行（基表的数据行）只会在范围条件被所有使用的Indexes都满足的情况下才会被回访；
如果在innodb table中，一个合并条件是在主键上，则他不会被使用在基表数据行的回访，被用在过滤其他条件回访返回的数据行；
index merge union access algorithm
该算法试用在表的where语句在不同的index上的不同范围条件的 Or 组合：
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;
Index Merge sort-union access algorithm
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;
sort-union algorithm 和 the union algorithm 之间的区别在于必须首先获得所有的rows,并且对其排序被返回；
mysql> explain select * from employees where emp_no < 257654 and hire_date = '1990-09-06';
+----+-------------+-----------+-------------+-------------------+-------------------+---------+------+------+-------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+-------------+-------------------+-------------------+---------+------+------+-------------------------------------------------+
| 1 | SIMPLE | employees | index_merge | PRIMARY,inx_hi_dt | inx_hi_dt,PRIMARY | 7,4 | NULL | 16 | Using intersect(inx_hi_dt,PRIMARY); Using where |
+----+-------------+-----------+-------------+-------------------+-------------------+---------+------+------+-------------------------------------------------+
1 row in set (0.09 sec)
mysql> explain select * from employees where emp_no < 257654 or hire_date = '1990-09-06';
+----+-------------+-----------+-------------+-------------------+-------------------+---------+------+--------+---------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+-------------+-------------------+-------------------+---------+------+--------+---------------------------------------------+
| 1 | SIMPLE | employees | index_merge | PRIMARY,inx_hi_dt | PRIMARY,inx_hi_dt | 4,3 | NULL | 149716 | Using union(PRIMARY,inx_hi_dt); Using where |
+----+-------------+-----------+-------------+-------------------+-------------------+---------+------+--------+---------------------------------------------+
1 row in set (0.00 sec)
mysql> desc select birth_date from employees where birth_date ='1961-07-09' and hire_date ='1986-06-29';
+----+-------------+-----------+-------------+---------------------+---------------------+---------+------+------+----------------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+-------------+---------------------+---------------------+---------+------+------+----------------------------------------------------------------+
| 1 | SIMPLE | employees | index_merge | inx_hi_dt,idx_br_dt | idx_br_dt,inx_hi_dt | 3,3 | NULL | 1 | Using intersect(idx_br_dt,inx_hi_dt); Using where; Using index |
+----+-------------+-----------+-------------+---------------------+---------------------+---------+------+------+----------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> desc select birth_date from employees where birth_date ='1961-07-09' or hire_date ='1986-06-29';
+----+-------------+-----------+-------------+---------------------+---------------------+---------+------+------+-----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+-------------+---------------------+---------------------+---------+------+------+-----------------------------------------------+
| 1 | SIMPLE | employees | index_merge | inx_hi_dt,idx_br_dt | idx_br_dt,inx_hi_dt | 3,3 | NULL | 166 | Using union(idx_br_dt,inx_hi_dt); Using where |
+----+-------------+-----------+-------------+---------------------+---------------------+---------+------+------+-----------------------------------------------+
1 row in set (0.02 sec)
mysql> desc select birth_date from employees where birth_date < '1950-07-09' or hire_date < '1970-06-29';
+----+-------------+-----------+-------------+---------------------+---------------------+---------+------+------+----------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+-------------+---------------------+---------------------+---------+------+------+----------------------------------------------------+
| 1 | SIMPLE | employees | index_merge | inx_hi_dt,idx_br_dt | idx_br_dt,inx_hi_dt | 3,3 | NULL | 2 | Using sort_union(idx_br_dt,inx_hi_dt); Using where |
+----+-------------+-----------+-------------+---------------------+---------------------+---------+------+------+----------------------------------------------------+
1 row in set (0.00 sec)
mysql> desc select birth_date from employees where birth_date < '1950-07-09' or hire_date = '1986-06-29';
+----+-------------+-----------+-------------+---------------------+---------------------+---------+------+------+----------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+-------------+---------------------+---------------------+---------+------+------+----------------------------------------------------+
| 1 | SIMPLE | employees | index_merge | inx_hi_dt,idx_br_dt | idx_br_dt,inx_hi_dt | 3,3 | NULL | 111 | Using sort_union(idx_br_dt,inx_hi_dt); Using where |
+----+-------------+-----------+-------------+---------------------+---------------------+---------+------+------+----------------------------------------------------+
1 row in set (0.00 sec)
最新文章
- Linux上的SQL Server的起步
- 【原创】Java和C#下String类型中的==和equals的原理与区别
- grunt 检测js配置
- [内核]Takslet和Workqueue的应用场景
- HBM内存介绍
- Web应用程序系统的多用户权限控制设计及实现-用户模块【7】
- The Skyline Problem
- 关于json的理解
- python数据分析师面试题选
- 【开源项目3】Android快速开源框架--afinal
- JAVA实现word doc docx pdf excel的在线浏览 - 仿百度文库 源码
- 【GDOI 2011 DAY2 T3】零什么的最讨厌了 （快速求阶乘、中国剩余定理）
- 利用TOAD实现把EXCEL数据导入oracle数据库
- 《think in python》学习-2
- AIO5岗位桌面主页【我的收藏夹】只显示8行，怎样增加显示行？
- dicom错误解决
- UI自动化项目搭建（selenium+testng+java+maven）
- Google SwipeRefreshLayout（Goolge官方下拉刷新控件）尝鲜
- 深入理解Java中的反射机制
- faker模块