- 联系我们
- duidaima.com 版权声明
- 闽ICP备2020021581号
-
闽公网安备 35020302035485号
闽公网安备 35020302035485号
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');
其中：2.子查询中有两组条件的 OR，os.order_no = 'x' OR os.waybill_no = 'x'。
*************************** 堆代码 duidaima.com ***************************
*************************** 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)
其中：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)
分析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=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 100000\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: 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 100000\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: 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 100000\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: 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 100000\G
*************************** 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 时索引失效？
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)
其中：1 row in set (0.01 sec)表明将 OR 拆分为 unin 有效。
| 场景 | 布尔运算 | 算法 |
|---|---|---|
| AND | 交集 | intersection |
| OR | 并集 | union |
| OR | 排序并集 | sort_union |
4.在集合或非等式查询中存在独立的列。
3.可以解决 MySQL 在处理复杂查询时候难以进行索引优化的问题。
4.可能不会像期望的那样触发索引合并，这取决于表结构、索引和查询。因此必须将其视为一种可能的优化，而不是在所有情况下都能提升性能的保证。
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)
其中：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: 03）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: 05）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 |
|---|---|---|
| 一个单列索引 | ✔ | ❌ |
| 两个单列索引 | ✔ | ❌ |
| 两个单列索引 + 联合索引 | ✔ | ❌ |
| 两个单列索引 + 联合索引 + index_merge | ✔ | ❌ |
2.每种场景下 OR 索引都不生效，即使是开启 index_merge。判断与数据量有关系，因此下面通过一个生产案例再次验证索引合并是否可以优化 OR。
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);
其中：*************************** 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)
其中：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)
其中：2.表大小 2100w。
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 | ✔ |
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。
3.库表结构优化
