引言
首先思考一个问题，MySQL 执行单表查询 SQL 时可能使用到多个索引吗？
实际上，在 MySQL5.0 之前，一个表一次只能使用一个索引，无法同时使用多个索引分别进行条件扫描。但是从 5.1 开始，引入了 index merge 优化技术，对同一个表可以使用多个索引分别进行条件扫描。
本文介绍一个使用 index merge 优化 OR 连接两个等值查询后 limit 的慢 SQL 案例，并介绍了 index merge 的概念与使用。
现象
时间：20221220 18:25
现象：SQL 全表扫描，执行用时超过 20秒，甚至查看执行计划也超时（超时时间为 10s）。
SQL
SQL如下所示。
SELECT os.waybill_no AS waybillNo,
os.order_no AS orderNo,
os.package_no AS packageNo,
os.weight AS totalWeight,
om.carrier_name AS carrierName,
om.update_time AS updateTime,
om.update_user AS updateUser,
om.order_status AS id,
oc.province AS province,
oc.city AS city,
oc.county AS county,
oc.customer_address1 AS customerAddress1,
oc.phone AS phone,
oc.contact AS contact
FROM order_shipno os
LEFT JOIN order_m om ON os.order_no = om.order_no
AND os.tenant_id = om.tenant_id
AND os.is_delete = om.is_delete
LEFT JOIN order_contact oc ON os.order_no = oc.order_no
AND os.tenant_id = oc.tenant_id
AND os.is_delete = oc.is_delete
WHERE os.tenant_id = 'x'
AND os.order_no =
(SELECT order_no
FROM order_shipno os
WHERE os.tenant_id = 'x'
AND os.warehouse_no = 'x'
AND (os.order_no = 'x'
OR os.waybill_no = 'x')
LIMIT 1)
AND os.is_delete = 0
AND om.update_time >= CONCAT('2022-12-20', ' 00:00:00')
AND om.update_time <= CONCAT('2022-12-20', ' 23:59:59');
其中：
-
SQL 类型为多表关联嵌套 + 子查询； -
子查询中有两组条件的 OR，os.order_no = ‘x’ OR os.waybill_no = ‘x’。
执行计划
查看执行计划超时。
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: os
partitions: NULL
type: ref
possible_keys: uniq_packageNo_waybillNo
key: uniq_packageNo_waybillNo
key_len: 305
ref: const,const
rows: 1
filtered: 10.00
Extra: Using where
*************************** 2. row ***************************
id: 1
select_type: PRIMARY
table: om
partitions: NULL
type: ref
possible_keys: uniq_orderNo_tenantId_warehouseNo,idx_tenantId_warehouseNo_orderStatus
key: uniq_orderNo_tenantId_warehouseNo
key_len: 215
ref: const,const
rows: 1
filtered: 5.00
Extra: Using index condition; Using where
*************************** 3. row ***************************
id: 1
select_type: PRIMARY
table: oc
partitions: NULL
type: ref
possible_keys: idx_orderNo
key: idx_orderNo
key_len: 152
ref: jcloud_pickingplan.os.order_no
rows: 1
filtered: 100.00
Extra: Using where
*************************** 4. row ***************************
id: 2
select_type: SUBQUERY
table: os
partitions: NULL
type: ALL
possible_keys: uniq_packageNo_waybillNo,idx_waybillNo
key: NULL
key_len: NULL
ref: NULL
rows: 42810940
filtered: 0.19
Extra: Using where
4 rows in set, 1 warning (26.34 sec)
其中：
-
多表查询时执行计划中有多条记录，其中按照 id 从大到小的顺序执行，当 id 相同时，执行顺序由上至下。因此 row=4，id=2 的表先执行； -
select_type: SUBQUERY 表明是子查询，type: ALL 表明索引失效全表扫描，并显示 rows: 42810940，因此判断慢的原因是子查询。 -
获取执行计划用时超过 20s。
实际执行用时超过 20 秒。
1 row in set (26.81 sec)
子查询
由于判断性能瓶颈出现在子查询，因此查看子查询的执行计划。
mysql> explain SELECT order_no
-> FROM order_shipno os
-> WHERE os.tenant_id = 'x'
-> AND os.warehouse_no = 'x'
-> AND (os.order_no = 'x'
-> OR os.waybill_no = 'x')
-> LIMIT 1 G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: os
partitions: NULL
type: ALL
possible_keys: uniq_packageNo_waybillNo,idx_waybillNo
key: NULL
key_len: NULL
ref: NULL
rows: 42811520
filtered: 0.19
Extra: Using where
1 row in set, 1 warning (0.01 sec)
与关联查询中相同，执行计划显示全表扫描。
因此优化思路便是优化该子查询。
表结构
查看表结构
mysql> show create table order_shipno G
*************************** 1. row ***************************
Table: order_shipno
Create Table: CREATE TABLE `order_shipno` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键 唯一标识',
`order_no` varchar(50) NOT NULL COMMENT '订单号',
`carrier_no` varchar(50) DEFAULT NULL COMMENT '承运商',
`waybill_no` varchar(50) DEFAULT NULL COMMENT '运单号',
`electronic` tinyint(4) DEFAULT NULL COMMENT '是否电子运单，1：非电子运单；0：电子运单',
`origin` tinyint(4) DEFAULT NULL COMMENT '单号来源，0：上游订单系统；1：wms系统 2：京配 3：京东alpha平台',
`tenant_id` varchar(50) DEFAULT NULL COMMENT '租户ID',
`warehouse_no` varchar(20) DEFAULT NULL COMMENT '仓库编码',
`print_status` int(11) DEFAULT '0' COMMENT '打印状态，0：未打印；1：已打印',
`print_times` int(11) DEFAULT '0' COMMENT '打印次数',
`print_time` datetime DEFAULT NULL COMMENT '打印时间',
`create_time` datetime NOT NULL COMMENT '创建时间',
`update_time` datetime NOT NULL COMMENT '更新时间',
`create_user` varchar(200) NOT NULL COMMENT '创建人',
`update_user` varchar(200) NOT NULL COMMENT '更新人',
`ts` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '默认时间',
`is_delete` tinyint(1) DEFAULT '0' COMMENT '刪除标志',
`cost_paid` decimal(18,4) DEFAULT '0.0000' COMMENT '买家所付费用',
`cost_total` decimal(18,4) DEFAULT '0.0000' COMMENT '实际运费',
`package_center_code` varchar(20) DEFAULT NULL COMMENT '集包地编码',
`pakcage_center_name` varchar(100) DEFAULT NULL COMMENT '集包地名称',
`ship_branch_code` varchar(20) DEFAULT NULL COMMENT '始发网点',
`short_address` varchar(50) DEFAULT NULL COMMENT '大头笔',
`distribute_code` varchar(20) DEFAULT NULL COMMENT '分拣码',
`delivery_type` varchar(20) DEFAULT NULL COMMENT '产品类型',
`send_code` varchar(20) DEFAULT NULL COMMENT '原寄地（顺丰用）',
`arrive_code` varchar(100) DEFAULT NULL COMMENT '目的地（顺丰用）',
`payment_type` varchar(20) DEFAULT NULL COMMENT '付款方式（顺丰用）',
`cost` decimal(18,4) DEFAULT NULL COMMENT '邮资（元）',
`weight` decimal(18,4) DEFAULT NULL COMMENT '重量（kg）',
`weigh_user` varchar(20) DEFAULT NULL COMMENT '称重人',
`weigh_time` datetime DEFAULT NULL COMMENT '称重时间',
`package_no` varchar(50) DEFAULT NULL COMMENT '包裹号',
`out_order_no` varchar(30) DEFAULT NULL COMMENT '对外订单号',
`jd_extension` varchar(1000) DEFAULT NULL COMMENT '扩展信息',
`package_status` int(11) DEFAULT '0' COMMENT '包裹状态',
`four_pl_mail_no` varchar(50) DEFAULT NULL COMMENT '4pl号',
`package_origin` tinyint(4) DEFAULT '0' COMMENT '包裹来源',
`version` int(11) DEFAULT NULL COMMENT '版本号',
`line_id` int(11) DEFAULT NULL COMMENT '行号',
`settlement_code` varchar(50) DEFAULT NULL COMMENT '结算编码',
`ex_waybill_no` varchar(500) DEFAULT NULL COMMENT '扩展运单号/母单号',
`theoretical_weight` decimal(18,4) DEFAULT NULL COMMENT '理论重量(kg)',
`pro_code` varchar(30) DEFAULT NULL COMMENT '时效类型',
`coding_mapping_out` varchar(60) DEFAULT NULL COMMENT '出港映射码',
`coding_mapping` varchar(60) DEFAULT NULL COMMENT '入港映射码',
`pay_type` varchar(20) DEFAULT NULL COMMENT '运单的运费支付方式（却决于上游下发和配置)',
`freight_fee` decimal(18,4) DEFAULT NULL COMMENT '上游下发订单为到付时，运费信息',
`length` decimal(18,4) DEFAULT NULL COMMENT '包裹信息采集：长，单位cm',
`width` decimal(18,4) DEFAULT NULL COMMENT '包裹信息采集：宽，单位cm',
`height` decimal(18,4) DEFAULT NULL COMMENT '包裹信息采集：高，单位cm',
`volume` decimal(18,4) DEFAULT NULL COMMENT '包裹信息采集：体积，单位cm',
PRIMARY KEY (`id`),
UNIQUE KEY `uniq_packageNo_waybillNo` (`order_no`,`tenant_id`,`package_no`,`waybill_no`),
KEY `idx_waybillNo` (`waybill_no`),
KEY `idx_packageNo` (`package_no`)
) ENGINE=InnoDB AUTO_INCREMENT=965715837 DEFAULT CHARSET=utf8 COMMENT='运单信息'
1 row in set (0.00 sec)
相关索引如下所示，表明索引失效。
UNIQUE KEY `uniq_packageNo_waybillNo` (`order_no`,`tenant_id`,`package_no`,`waybill_no`),
KEY `idx_waybillNo` (`waybill_no`)
possible_keys: uniq_packageNo_waybillNo,idx_waybillNo
key: NULL
表大小，3000w 行。
mysql> show table status like 'order_shipno' G
*************************** 1. row ***************************
Name: order_shipno
Engine: InnoDB
Version: 10
Row_format: Dynamic
Rows: 33028336
Avg_row_length: 882
Data_length: 29160898560
Max_data_length: 0
Index_length: 12108595200
Data_free: 423624704
Auto_increment: 965716601
Create_time: 2022-12-09 11:51:41
Update_time: 2022-12-20 19:46:04
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment: 运单信息
1 row in set (0.01 sec)
分析
trace
查看 trace，尝试分析索引生效的原因。
mysql> select * FROM information_schema.optimizer_trace G
*************************** 1. row ***************************
QUERY: SELECT order_no
FROM order_shipno os
WHERE os.tenant_id = 'x'
AND os.warehouse_no = 'x'
AND (os.order_no = 'x'
OR os.waybill_no = 'x')
LIMIT 1
TRACE: {
"steps": [
{
"join_preparation": {
"select#": 1,
"steps": [
{
"expanded_query": "/* select#1 */ select `os`.`order_no` AS `order_no` from `order_shipno` `os` where ((`os`.`tenant_id` = 'x') and (`os`.`warehouse_no` = 'x') and ((`os`.`order_no` = 'x') or (`os`.`waybill_no` = 'x'))) limit 1"
}
]
}
},
{
"join_optimization": {
"select#": 1,
"steps": [
{
"condition_processing": {
"condition": "WHERE",
"original_condition": "((`os`.`tenant_id` = 'x') and (`os`.`warehouse_no` = 'x') and ((`os`.`order_no` = 'x') or (`os`.`waybill_no` = 'x')))",
"steps": [
{
"transformation": "equality_propagation",
"resulting_condition": "((`os`.`tenant_id` = 'x') and (`os`.`warehouse_no` = 'x') and ((`os`.`order_no` = 'x') or (`os`.`waybill_no` = 'x')))"
},
{
"transformation": "constant_propagation",
"resulting_condition": "((`os`.`tenant_id` = 'x') and (`os`.`warehouse_no` = 'x') and ((`os`.`order_no` = 'x') or (`os`.`waybill_no` = 'x')))"
},
{
"transformation": "trivial_condition_removal",
"resulting_condition": "((`os`.`tenant_id` = 'x') and (`os`.`warehouse_no` = 'x') and ((`os`.`order_no` = 'x') or (`os`.`waybill_no` = 'x')))"
}
]
}
},
{
"substitute_generated_columns": {
}
},
{
"table_dependencies": [
{
"table": "`order_shipno` `os`",
"row_may_be_null": false,
"map_bit": 0,
"depends_on_map_bits": [
]
}
]
},
{
"ref_optimizer_key_uses": [
]
},
{
"rows_estimation": [
{
"table": "`order_shipno` `os`",
"range_analysis": {
"table_scan": {
"rows": 33028443,
"cost": 3.96e7
},
"potential_range_indexes": [
{
"index": "PRIMARY",
"usable": false,
"cause": "not_applicable"
},
{
"index": "uniq_packageNo_waybillNo",
"usable": true,
"key_parts": [
"order_no",
"tenant_id",
"package_no",
"waybill_no"
]
},
{
"index": "idx_waybillNo",
"usable": true,
"key_parts": [
"waybill_no",
"id"
]
},
{
"index": "idx_packageNo",
"usable": false,
"cause": "not_applicable"
}
],
"setup_range_conditions": [
],
"group_index_range": {
"chosen": false,
"cause": "not_group_by_or_distinct"
},
"analyzing_range_alternatives": {
"range_scan_alternatives": [
{
"index": "uniq_packageNo_waybillNo",
"chosen": false,
"cause": "unknown"
}
]
}
}
}
]
},
{
"considered_execution_plans": [
{
"plan_prefix": [
],
"table": "`order_shipno` `os`",
"best_access_path": {
"considered_access_paths": [
{
"rows_to_scan": 33028443,
"access_type": "scan",
"resulting_rows": 3.3e7,
"cost": 8.39e6,
"chosen": true
}
]
},
"condition_filtering_pct": 100,
"rows_for_plan": 3.3e7,
"cost_for_plan": 8.39e6,
"chosen": true
}
]
},
{
"attaching_conditions_to_tables": {
"original_condition": "((`os`.`tenant_id` = 'x') and (`os`.`warehouse_no` = 'x') and ((`os`.`order_no` = 'x') or (`os`.`waybill_no` = 'x')))",
"attached_conditions_computation": [
{
"table": "`order_shipno` `os`",
"rechecking_index_usage": {
"recheck_reason": "low_limit",
"limit": 1,
"row_estimate": 3.3e7
}
}
],
"attached_conditions_summary": [
{
"table": "`order_shipno` `os`",
"attached": "((`os`.`tenant_id` = 'x') and (`os`.`warehouse_no` = 'x') and ((`os`.`order_no` = 'x') or (`os`.`waybill_no` = 'x')))"
}
]
}
},
{
"refine_plan": [
{
"table": "`order_shipno` `os`"
}
]
}
]
}
},
{
"join_execution": {
"select#": 1,
"steps": [
]
}
}
]
}
MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0
INSUFFICIENT_PRIVILEGES: 0
1 row in set (0.00 sec)
但是从中并未找到索引失效的原因。
"analyzing_range_alternatives": {
"range_scan_alternatives": [
{
"index": "uniq_packageNo_waybillNo",
"chosen": false,
"cause": "unknown"
}
]
}
index_merge
研发反馈数据库切换后变慢，因此对比源库执行计划，显示源库中执行计划显示使用 index_merge。
查看源和目标 optimizer_switch 参数，显示配置不同，其中源开启 index_merge，目标关闭 index_merge。
详情参数如下所示。
源，5.7.24
index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on
目标，5.7.33
index_merge=off,index_merge_union=off,index_merge_sort_union=off,index_merge_intersection=off,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on,prefer_ordering_index=on
索引合并相关有四个参数，该场景下适用于哪个参数呢？
下面依次开启这四个参数进行测试。
mysql> set session optimizer_switch='index_merge=on';
Query OK, 0 rows affected (0.00 sec)
mysql> explain SELECT order_no FROM order_shipno os WHERE os.tenant_id = 'x' AND os.warehouse_no = 'x' AND (os.order_no = 'x' OR os.waybill_no = 'x') LIMIT 100000G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: os
partitions: NULL
type: ALL
possible_keys: uniq_packageNo_waybillNo,idx_waybillNo
key: NULL
key_len: NULL
ref: NULL
rows: 33035098
filtered: 0.19
Extra: Using where
1 row in set, 1 warning (0.01 sec)
mysql> set session optimizer_switch='index_merge_intersection=on';
Query OK, 0 rows affected (0.01 sec)
mysql> explain SELECT order_no FROM order_shipno os WHERE os.tenant_id = 'x' AND os.warehouse_no = 'x' AND (os.order_no = 'x' OR os.waybill_no = 'x') LIMIT 100000G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: os
partitions: NULL
type: ALL
possible_keys: uniq_packageNo_waybillNo,idx_waybillNo
key: NULL
key_len: NULL
ref: NULL
rows: 33035317
filtered: 0.19
Extra: Using where
1 row in set, 1 warning (0.01 sec)
mysql> set session optimizer_switch='index_merge_union=on';
Query OK, 0 rows affected (0.00 sec)
mysql> explain SELECT order_no FROM order_shipno os WHERE os.tenant_id = 'x' AND os.warehouse_no = 'x' AND (os.order_no = 'x' OR os.waybill_no = 'x') LIMIT 100000G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: os
partitions: NULL
type: ALL
possible_keys: uniq_packageNo_waybillNo,idx_waybillNo
key: NULL
key_len: NULL
ref: NULL
rows: 33035396
filtered: 0.19
Extra: Using where
1 row in set, 1 warning (0.01 sec)
mysql> set session optimizer_switch='index_merge_sort_union=on';
Query OK, 0 rows affected (0.00 sec)
mysql> explain SELECT order_no FROM order_shipno os WHERE os.tenant_id = 'x' AND os.warehouse_no = 'x' AND (os.order_no = 'x' OR os.waybill_no = 'x') LIMIT 100000G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: os
partitions: NULL
type: index_merge
possible_keys: uniq_packageNo_waybillNo,idx_waybillNo
key: uniq_packageNo_waybillNo,idx_waybillNo
key_len: 152,153
ref: NULL
rows: 2
filtered: 2.50
Extra: Using sort_union(uniq_packageNo_waybillNo,idx_waybillNo); Using where
1 row in set, 1 warning (0.00 sec)
经测试，开启 index_merge_sort_union、index_merge 后优化器会选择合理的索引。
mysql> set global optimizer_switch='index_merge_sort_union=on';
Query OK, 0 rows affected (0.00 sec)
mysql> set global optimizer_switch='index_merge=on';
Query OK, 0 rows affected (0.00 sec)
开启 index_merge 时使用两个索引，但是为什么关闭 index_merge 时索引失效？
这与 index_merge 的原理有关，下文中将介绍。
SQL 优化
OR 导致无法通过单个索引 uniq_packageNo_waybillNo 或 idx_waybillNo 来完成数据的检索过滤。
可选的执行方式有四种：
-
第一种就是全表扫描，但是成本也是最高的； -
第二种就是利用索引合并特性，通过 uniq_packageNo_waybillNo 和 idx_waybillNo 进行数据查询，然后将结果集 union 合并； -
第三种方式就是不让 MySQL 帮我们优化这个 SQL，我们自己将这条 SQL 拆分为两条 SQL 来 union； -
第四种方式是创建（tenant_id，warehouse_no，order_no，waybill_no）四列复合索引，利用索引覆盖来减少回表操作。
一般情况下如果利用索引合并的特性，两个索引扫描的结果集再进行 union，结果集的大小直接决定着 sql 性能，但本条 SQL 中的两个索引选择性都特别好，接近唯一索引的选择性。
union
将 OR 改写为 union。
SELECT
order_no
FROM
order_shipno os
WHERE
os.tenant_id = 'x'
AND os.warehouse_no = 'x'
AND os.order_no = 'x'
union
SELECT
order_no
FROM
order_shipno os
WHERE
os.tenant_id = 'x'
AND os.warehouse_no = 'x'
AND
os.waybill_no = 'x'
LIMIT
1 G
查看执行计划
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: os
partitions: NULL
type: ref
possible_keys: uniq_packageNo_waybillNo
key: uniq_packageNo_waybillNo
key_len: 305
ref: const,const
rows: 1
filtered: 10.00
Extra: Using where
*************************** 2. row ***************************
id: 2
select_type: UNION
table: os
partitions: NULL
type: ref
possible_keys: idx_waybillNo
key: idx_waybillNo
key_len: 153
ref: const
rows: 1
filtered: 5.00
Extra: Using where
*************************** 3. row ***************************
id: NULL
select_type: UNION RESULT
table: <union1,2>
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: NULL
filtered: NULL
Extra: Using temporary
3 rows in set, 1 warning (0.02 sec)
其中：
-
id: 1，select_type: PRIMARY，key: uniq_packageNo_waybillNo，表明最外层查询索引生效； -
id: 2，select_type: UNION，key: idx_waybillNo，表明包含在 UNION 中的第二个或之后的 SELECT 语句索引生效； -
id: NULL，select_type: UNION RESULT，key: NULL，表明获取 UNION 操作的结果集操作。
执行用时 0.01s。
1 row in set (0.01 sec)
表明将 OR 拆分为 unin 有效。
下面介绍 index merge 相关原理并测试进行验证。
知识点
index_merge
索引合并（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.
索引合并适用于多个索引的布尔运算，具体分为以下三种场景。
| 场景 | 布尔运算 | 算法 |
|---|---|---|
| AND | 交集 | intersection |
| OR | 并集 | union |
| OR | 排序并集 | sort_union |
备注：索引合并仅适用于单表。
其中 union 与 sor_union 的区别在于 sort-union 算法会在合并结果集时首先将数据按照主键排序并去重，然后回表读取行数据，从而避免重复访问相同的行，而 union 算法在合并结果集时首先分别回表读取行数，然后将结果排序并去重。
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.
索引合并策略相关参数与示例 SQL 如下所示：
-
index_merge，用于控制是否使用索引合并，仅开启算法不开启该参数将不生效； -
index_merge_union，如：c1、c2 是两个单列索引，SELECT * FROM t1 WHERE c1 = 1 AND c2 = 2； -
index_merge_intersection，如：c1、c2、c3 是三个单列索引，SELECT * FROM t1 WHERE c1 = 1 OR c2 = 2 OR c3 = 3； -
index_merge_sort_union，如：c1、c2 是两个单列索引，SELECT * FROM t1 WHERE c1 < 10 OR c2 < 20。
个人判断 sort-union 适用于范围查询或等值查询 + 排序。union 适用于等值查询。
索引合并是使用多个索引，联合索引是使用索引的多个字段，两者的区别是什么？
-
联合索引，第一个字段有序的前提下第二个字段有序，因此适合 AND 等值查询； -
索引合并，每个索引上的字段均有序。
那么，什么场景下是单个联合索引或多个单列索引满足不了的需要引入索引合并呢？
-
OR，针对单列索引，最多使用一个，针对联合索引，最多使用第一个字段； -
AND，针对单列索引，最多使用一个，针对联合索引，范围查询最多使用第一个字段，等值查询可以使用多个字段，原因是联合索引中在第一个字段等值的条件下第二个字段有序。
备注：未考虑索引下推机制。
因此：
-
OR 条件下适合使用索引合并，因为如果使用单个索引，无论是单列索引还是联合索引，都只能用到一个字段； -
AND 条件下更适合使用联合索引。
参考 chatgpt，索引合并的适用场景主要包括：
-
当 WHERE 子句中有多个独立的条件，并且它们需要使用不同的索引。 -
对查询结果的排序和分组需要使用与搜索条件不同的索引。 -
包含在 OR/AND 之间的多个列。 -
在集合或非等式查询中存在独立的列。
索引合并的优点：
-
可以利用多个索引提高查询性能。特别是当单个索引无法满足查询需求时，可以利用索引合并来提高查询性能； -
提高了索引的复用性，降低了索引的维护成本。减少了为满足查询需求而必须创建联合索引的需要； -
可以解决 MySQL 在处理复杂查询时候难以进行索引优化的问题。
索引合并的缺点：
-
使用索引合并会占用更多的内存和 CPU 资源，可能会对系统性能产生影响； -
根据 MySQL 官方文档，索引合并优化并非总是更高效。在某些场景下，扫描全表可能更快； -
根据 MySQL 官方文档，只有在系统配置参数 optimizer_switch 中的 index_merge 参数设置为 on 时，才能使用索引合并优化。使用这个优化可能需要对系统进行配置； -
可能不会像期望的那样触发索引合并，这取决于表结构、索引和查询。因此必须将其视为一种可能的优化，而不是在所有情况下都能提升性能的保证。
OR 导致索引失效
MySQL 慢SQL优化之多表 or 可能导致索引失效文章中分别测试了单表与多表条件下 AND 与 OR 多条件是否会导致索引失效。
本文借用其中的测试表，分别测试 AND 与 OR 多条件下是否会导致索引失效。
准备测试数据
mysql> show create table join_t2 G
*************************** 1. row ***************************
Table: join_t2
Create Table: CREATE TABLE `join_t2` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`a` varchar(11) DEFAULT NULL,
`b` varchar(11) DEFAULT NULL,
`t1_id` int(11) DEFAULT '0',
PRIMARY KEY (`id`),
KEY `idx_varchar_a` (`a`),
KEY `idx_t1_id` (`t1_id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
mysql> select count(*) from join_t2;
+----------+
| count(*) |
+----------+
| 5 |
+----------+
1 row in set (0.00 sec)
其中：
-
测试表中 a 字段有索引，b 字段无索引。
1）AND，一个单列索引，索引生效
mysql> explain select * from join_t2 where a='a' and b='b' G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: join_t2
partitions: NULL
type: ref
possible_keys: idx_varchar_a
key: idx_varchar_a
key_len: 36
ref: const
rows: 1
filtered: 20.00
Extra: Using where
1 row in set, 1 warning (0.00 sec)
2）OR，一个单列索引，索引失效
mysql> explain select * from join_t2 where a='a' or b='b' G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: join_t2
partitions: NULL
type: ALL
possible_keys: idx_varchar_a
key: NULL
key_len: NULL
ref: NULL
rows: 5
filtered: 36.00
Extra: Using where
1 row in set, 1 warning (0.00 sec)
接下来，给 b 字段也创建索引。
mysql> alter table join_t2 add index idx_varchar_b(b);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
3）AND，两个单列索引，一个索引生效
mysql> explain select * from join_t2 where a='a' and b='b' G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: join_t2
partitions: NULL
type: ref
possible_keys: idx_varchar_a,idx_varchar_b
key: idx_varchar_a
key_len: 36
ref: const
rows: 1
filtered: 20.00
Extra: Using where
1 row in set, 1 warning (0.00 sec)
4）OR，两个单列索引，两个索引均失效
mysql> explain select * from join_t2 where a='a' or b='b' G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: join_t2
partitions: NULL
type: ALL
possible_keys: idx_varchar_a,idx_varchar_b
key: NULL
key_len: NULL
ref: NULL
rows: 5
filtered: 36.00
Extra: Using where
1 row in set, 1 warning (0.00 sec)
接下来，给 a、b 字段创建联合索引。
mysql> alter table join_t2 add index idx_a_b(a, b);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
5）AND，两个单列索引 + 联合索引，一个索引生效
mysql> explain select * from join_t2 where a='a' and b='b' G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: join_t2
partitions: NULL
type: ref
possible_keys: idx_varchar_a,idx_varchar_b,idx_a_b
key: idx_varchar_a
key_len: 36
ref: const
rows: 1
filtered: 20.00
Extra: Using where
1 row in set, 1 warning (0.00 sec)
6）OR，两个单列索引 + 联合索引，三个索引均失效
mysql> explain select * from join_t2 where a='a' or b='b' G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: join_t2
partitions: NULL
type: ALL
possible_keys: idx_varchar_a,idx_varchar_b,idx_a_b
key: NULL
key_len: NULL
ref: NULL
rows: 5
filtered: 36.00
Extra: Using where
1 row in set, 1 warning (0.00 sec)
下面测试是否可以通过 index_merge 优化 OR。
mysql> set session optimizer_switch="index_merge=on";
Query OK, 0 rows affected (0.00 sec)
mysql> set session optimizer_switch="index_merge_union=on";
Query OK, 0 rows affected (0.00 sec)
mysql> set session optimizer_switch="index_merge_sort_union=on";
Query OK, 0 rows affected (0.00 sec)
mysql> set session optimizer_switch="index_merge_intersection=on";
Query OK, 0 rows affected (0.00 sec)
7）AND，两个单列索引 + 联合索引 + index_merge，一个索引生效
mysql> explain select * from join_t2 where a='a' and b='b' G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: join_t2
partitions: NULL
type: ref
possible_keys: idx_varchar_a,idx_varchar_b,idx_a_b
key: idx_varchar_a
key_len: 36
ref: const
rows: 1
filtered: 20.00
Extra: Using where
1 row in set, 1 warning (0.00 sec)
8）OR，两个单列索引 + 联合索引 + index_merge，三个索引均失效
mysql> explain select * from join_t2 where a='a' or b='b' G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: join_t2
partitions: NULL
type: ALL
possible_keys: idx_varchar_a,idx_varchar_b,idx_a_b
key: NULL
key_len: NULL
ref: NULL
rows: 5
filtered: 36.00
Extra: Using where
1 row in set, 1 warning (0.00 sec)
下表中对比总结了不同场景下 AND 与 OR 中索引是否生效。
| 场景 | AND | OR |
|---|---|---|
| 一个单列索引 | ✔ | ❌ |
| 两个单列索引 | ✔ | ❌ |
| 两个单列索引 + 联合索引 | ✔ | ❌ |
| 两个单列索引 + 联合索引 + index_merge | ✔ | ❌ |
其中：
-
每种场景下 AND 生效的索引都是单列索引； -
每种场景下 OR 索引都不生效，即使是开启 index_merge。判断与数据量有关系，因此下面通过一个生产案例再次验证索引合并是否可以优化 OR。
生产案例
时间：20230725
现象：CPU 打满，SQL 全表扫描，加索引是否有用？
SQL
select count(1) count
from ao_main
WHERE yn=1
and (target_seller_id = 20000016121 or origin_seller_id = 20000016121 )
and (
target_dept_id in(4418046534214)
or origin_dept_id in (4418046534214)
)
and warehouse_id in (10146);
其中：
-
存在两组 OR，优化过程中暂时仅考虑第一组 OR，即 target_seller_id or origin_seller_id。
执行计划
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: ao_main
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 21321693
filtered: 0.04
Extra: Using where
1 row in set, 1 warning (0.00 sec)
其中：
-
type: ALL，表明是全表扫描 -
possible_keys: NULL，表明没有可用索引
表结构
mysql> show create table ao_main G
*************************** 1. row ***************************
Table: ao_main
Create Table: CREATE TABLE `ao_main` (
`id` bigint(50) NOT NULL COMMENT '主键',
`ao_no` varchar(50) DEFAULT NULL COMMENT '调整单单号',
`origin_seller_id` bigint(50) DEFAULT NULL COMMENT '供方商家ID',
`origin_seller_no` varchar(50) DEFAULT NULL COMMENT '供方商家No',
`origin_seller_name` varchar(100) DEFAULT NULL COMMENT '供方商家名称',
`origin_dept_id` bigint(50) DEFAULT NULL COMMENT '供方事业部',
`origin_dept_no` varchar(50) DEFAULT NULL COMMENT '供方事业部',
`origin_dept_name` varchar(100) DEFAULT NULL COMMENT '供方事业部',
`target_seller_id` bigint(50) DEFAULT NULL COMMENT '使用方商家',
`target_seller_no` varchar(50) DEFAULT NULL COMMENT '使用方商家',
`target_seller_name` varchar(100) DEFAULT NULL COMMENT '使用方商家',
`target_dept_id` bigint(50) DEFAULT NULL COMMENT '使用方事业部',
`target_dept_no` varchar(50) DEFAULT NULL COMMENT '使用方事业部',
`target_dept_name` varchar(100) DEFAULT NULL COMMENT '使用方事业部',
`partner_id` bigint(50) DEFAULT NULL COMMENT '合作伙伴',
`partner_no` varchar(50) DEFAULT NULL COMMENT '合作伙伴',
`partner_name` varchar(100) DEFAULT NULL COMMENT '合作伙伴',
`warehouse_id` bigint(50) DEFAULT NULL COMMENT '库房',
`warehouse_no` varchar(50) DEFAULT NULL COMMENT '库房',
`warehouse_name` varchar(100) DEFAULT NULL COMMENT '库房',
`type` varchar(4) DEFAULT NULL COMMENT '调整单类型',
`biz_type` varchar(4) DEFAULT NULL COMMENT '业务单据类型',
`status` int(11) DEFAULT NULL COMMENT '调整单状态',
`source` tinyint(4) DEFAULT NULL COMMENT '调整单来源',
`biz_no` varchar(50) DEFAULT NULL COMMENT '调整单关联业务单号',
`po_no` varchar(400) DEFAULT NULL COMMENT '关联采购单号',
`finish_time` datetime DEFAULT NULL COMMENT '完成时间',
`create_time` datetime DEFAULT NULL COMMENT '创建时间',
`update_time` datetime DEFAULT NULL COMMENT '更新时间',
`create_user` varchar(50) DEFAULT NULL COMMENT '创建人',
`update_user` varchar(50) DEFAULT NULL COMMENT '更新人',
`yn` tinyint(4) DEFAULT NULL COMMENT '使用生效',
`ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'ts',
`version` tinyint(4) DEFAULT NULL COMMENT '版本',
`seller_ao_no` varchar(50) DEFAULT NULL COMMENT '商家调整编码',
`adjust_type` varchar(10) DEFAULT NULL COMMENT '调整类型',
`related_ao_no` varchar(50) DEFAULT NULL COMMENT '关联货主调整单号',
`parent_ao_no` varchar(50) DEFAULT NULL COMMENT '调整父单号',
`allow_lack_change` varchar(10) DEFAULT NULL COMMENT '是否允许缺量',
`seller_so_no` varchar(50) DEFAULT NULL COMMENT '商家销售单号',
PRIMARY KEY (`id`) USING BTREE,
KEY `idx_biz_no` (`biz_no`),
KEY `idx_ao_no` (`ao_no`),
KEY `idx_rel_no` (`related_ao_no`),
KEY `idx_seller_ao` (`seller_ao_no`),
KEY `idx_po_no` (`po_no`(255)),
KEY `idx_sono` (`seller_so_no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT COMMENT='调整单主档'
1 row in set (0.00 sec)
mysql> show table status like 'ao_main' G
*************************** 1. row ***************************
Name: ao_main
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 21004666
Avg_row_length: 536
Data_length: 11262754816
Max_data_length: 0
Index_length: 6507413504
Data_free: 3145728
Auto_increment: NULL
Create_time: 2023-07-30 10:17:34
Update_time: 2023-07-29 19:27:36
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options: row_format=COMPACT
Comment: 调整单主档
1 row in set (0.00 sec)
其中：
-
查询条件中的四个字段 target_dept_id、origin_dept_id、warehouse_id、yn 均没有索引； -
表大小 2100w。
很明显，该 SQL 需要进行索引优化，可是有一个问题，就是假设给 OR 前后两个字段创建索引，索引会生效吗？
查看索引合并相关参数，显示全部关闭。
mysql> show variables like '%optimizer_switch%';
+------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value |
+------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| optimizer_switch | index_merge=off,index_merge_union=off,index_merge_sort_union=off,index_merge_intersection=off,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on,prefer_ordering_index=on |
+------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
下面验证下给 OR 前后两个字段创建索引，索引会生效吗？
创建索引
mysql> alter table ao_main add index idx_target_seller_id(target_seller_id);
Query OK, 0 rows affected (1 min 10.14 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table ao_main add index idx_origin_seller_id(origin_seller_id);
Query OK, 0 rows affected (1 min 11.31 sec)
Records: 0 Duplicates: 0 Warnings: 0
经测试，无论是创建一个还是单列索引，都会索引失效。
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: ao_main
partitions: NULL
type: ALL
possible_keys: idx_target_seller_id,idx_origin_seller_id
key: NULL
key_len: NULL
ref: NULL
rows: 21004666
filtered: 0.00
Extra: Using where
1 row in set, 1 warning (0.00 sec)
那么，创建联合索引呢？
创建联合索引
mysql> alter table ao_main add index idx_target_seller_id_origin_seller_id(target_seller_id, origin_seller_id);
Query OK, 0 rows affected (1 min 24.10 sec)
Records: 0 Duplicates: 0 Warnings: 0
执行计划显示索引依然失效
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: ao_main
partitions: NULL
type: ALL
possible_keys: idx_target_seller_id,idx_origin_seller_id,idx_target_seller_id_origin_seller_id
key: NULL
key_len: NULL
ref: NULL
rows: 21004666
filtered: 0.00
Extra: Using where
1 row in set, 1 warning (0.00 sec)
开启索引合并
mysql> set session optimizer_switch="index_merge=on";
Query OK, 0 rows affected (0.00 sec)
mysql> set session optimizer_switch="index_merge_union=on";
Query OK, 0 rows affected (0.00 sec)
执行计划显示使用索引合并后索引生效，并且使用的是两个单列索引而非联合索引。
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: ao_main
partitions: NULL
type: index_merge
possible_keys: idx_target_seller_id,idx_origin_seller_id,idx_target_seller_id_origin_seller_id
key: idx_target_seller_id,idx_origin_seller_id
key_len: 9,9
ref: NULL
rows: 19293
filtered: 0.19
Extra: Using union(idx_target_seller_id,idx_origin_seller_id); Using where
1 row in set, 1 warning (0.00 sec)
下表中对比总结了不同场景下 OR 中索引是否生效。
| 场景 | OR |
|---|---|
| 一个单列索引 | ❌ |
| 两个单列索引 | ❌ |
| 两个单列索引 + 联合索引 | ❌ |
| 两个单列索引 + 联合索引 + index_merge | ✔ |
其中：
-
开启索引合并后索引生效，表明索引合并可用于优化该 SQL。
下面分析为什么索引合并可以优化该 SQL。
分别查看 target_seller_id = 20000016121 与 origin_seller_id = 20000016121 符合条件的数据行数。
mysql> select count(1) count from ao_main WHERE yn=1 and target_seller_id = 20000016121 and target_dept_id in(4418046534214) and warehouse_id in (10146);
+-------+
| count |
+-------+
| 371 |
+-------+
1 row in set (0.00 sec)
mysql> select count(1) count from ao_main WHERE yn=1 and origin_seller_id = 20000016121 and origin_dept_id in (4418046534214) and warehouse_id in (10146);
+-------+
| count |
+-------+
| 10167 |
+-------+
1 row in set (0.04 sec)
其中：
-
两个索引字段 target_seller_id 与 origin_seller_id 的区分度都很好，过滤后的数据最多有 1/2000。
但是假如 MySQL 一定要走索引，在仅适用一个索引时执行需要三步：索引扫描 + 全表扫描 + 结果合并。
其中由于全表扫描的步骤无法省略，因此优化器选择直接一遍全表扫描。
而使用索引合并时，仅需要两步：两次索引扫描 + 结果合并。
小技巧
SQL 优化
慢 SQL 优化的方法可以分为三类：
-
查询优化 -
索引优化 -
库表结构优化
其中索引优化是数据库中用来提升性能的最常用工具。
本文中介绍了另一种方法，也就是优化器参数优化。
优化器参数用于调整和控制 MySQL 查询优化器的行为，如执行计划的选择、索引的使用、多表连接策略等。
实际上有很多非常有用的优化器参数，如：
-
index_merge，索引合并 -
index_condition_pushdown，索引下推 -
prefer_ordering_index，适用于 order by limit 或 group by limit
不过非必要不建议调整优化器参数。
结论
索引合并是表的一种访问方法，同时范围扫描多个索引然后将结果合并，注意索引合并仅适用于单表。
同时扫描多个索引是一项很有创意的优化思路，但它也是有适用场景的。
MySQL 5.1 中引入 index merge 优化技术，对同一个表可以使用多个索引分别进行条件扫描。
索引合并支持三种算法，包括 index_merge_union、index_merge_intersection、index_merge_sort_union。
其中个人判断 sort-union 适用于范围查询或等值查询 + 排序。union 适用于等值查询。
索引合并是使用多个索引，联合索引是使用索引的多个字段，两者对比：
-
联合索引，第一个字段有序的前提下第二个字段有序，因此适合 AND 等值查询； -
索引合并，每个索引上的字段均有序，因此适合 OR 等值查询或范围查询。
OR 条件下如果使用单个索引，无论是单列索引还是联合索引，都只能用到一个字段，而且很有可能索引优化无效。
尽管可以通过开启优化器参数 index_merge 进行优化，但是由于存在参数依赖，因此如果环境发生变化，比如数据库迁移后参数不一致将导致 SQL 性能下降。
因此针对 OR 建议通过将 SQL 改写为 UNION 来优化，这样可以保证 SQL 性能稳定。
后续将介绍更多 index merge 相关案例，从中可以看到使用它所带来的风险。
待办
-
handler
参考教程
-
MySQL 8.0 Reference Manual / Index Merge Optimization
-
【叶问15期】MySQL执行sql时一个表只能用到一个索引吗？
-
MySQL 优化之 index merge(索引合并)
-
Multi Column indexes vs Index Merge
原文始发于微信公众号（丹柿小院）：MySQL 关闭 index_merge 导致慢 SQL
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 举报，一经查实，本站将立刻删除。
文章由极客之音整理，本文链接：https://www.bmabk.com/index.php/post/178502.html
