介绍
慢SQL
版本：5.6.39
业务反馈慢SQL。
SELECT
sodi.id AS id
FROM
stock_out_doc_info sodi
LEFT JOIN stock_out_handover soh ON sodi.id = soh.stock_out_id
AND soh.is_delete = 0
LEFT JOIN stock_out_doc_refund sodr ON sodi.id = sodr.stock_out_id
AND sodr.is_delete = 0
AND sodr.out_refund_type = 1100
LEFT JOIN stock_out_doc_waybill sodw ON sodi.id = sodw.stock_out_id
AND sodw.is_delete = 0
WHERE
sodi.is_delete = 0
AND sodi.ware_unique_code IN (
...
)
AND sodi.stock_out_type IN (
...
)
AND (
sodi.stock_out_no = ''
OR sodr.out_refund_no = ''
)
;
其中：
1.属于四张表的关联查询；
2.查询条件中包括 1 张表的多个 IN；
3.查询条件中包括 2 张表的 OR。
执行用时超过 6 秒。
+---------+
| id |
+---------+
| ... |
+---------+
1 row in set (6.51 sec)
下面开始在定位性能瓶颈后进行优化。
执行计划
查看执行计划。
*************************** 堆代码 duidaima.com ***************************
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: sodi
type: ALL
possible_keys: idx_stock_out_no,idx_stock_out_type,idx_ware_unique_code
key: NULL
key_len: NULL
ref: NULL
rows: 1486841
Extra: Using where
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: soh
type: ref
possible_keys: idx_stock_out_id
key: idx_stock_out_id
key_len: 8
ref: las_spare_stockout.sodi.id
rows: 1
Extra: Using where
*************************** 3. row ***************************
id: 1
select_type: SIMPLE
table: sodr
type: ref
possible_keys: idx_stock_out_id
key: idx_stock_out_id
key_len: 8
ref: las_spare_stockout.sodi.id
rows: 1
Extra: Using where
*************************** 4. row ***************************
id: 1
select_type: SIMPLE
table: sodw
type: ref
possible_keys: idx_stock_out_id
key: idx_stock_out_id
key_len: 8
ref: las_spare_stockout.sodi.id
rows: 1
Extra: Using where
4 rows in set (0.00 sec)
其中：
执行计划返回四条记录，分别对应四张表，但是 id 都等于 1，因此执行顺序从上到下；
第一条记录 type: ALL，表明全表扫描，rows: 1486841；
其他三条记录分别对应另外三张表，type: ref，表明属于非唯一索引访问，key: idx_stock_out_id，rows: 1；
其他三条记录均显示 ref: las_spare_stockout.sodi.id，表明与被驱动表的关联字段进行等值匹配的对象是驱动表的 id 列；
四条记录均显示 Extra: Using where，表明需要回表然后在 server 层判断搜索条件，原因是每个被驱动表的查询条件中包括 is_delete=0 的条件查询，而索引中不包括该字段。
从执行计划中可以发现，慢 SQL 的瓶颈出现在 stock_out_doc_info sodi 表的全表扫描，因此查看该表的表结构。
表结构
与查询条件相关的简化后的表结构如下所示。
mysql> show create table stock_out_doc_info \G
*************************** 1. row ***************************
Table: stock_out_doc_info
Create Table: CREATE TABLE `stock_out_doc_info` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
`ware_unique_code` varchar(100) NOT NULL DEFAULT '' COMMENT '库房唯一编码',
`stock_out_no` varchar(255) NOT NULL COMMENT '出库单号',
`stock_out_type` smallint(6) NOT NULL COMMENT '出库类型',
`is_delete` tinyint(1) NOT NULL COMMENT '是否删除（0:否;1:是）',
...
PRIMARY KEY (`id`),
KEY `idx_stock_out_no` (`stock_out_no`),
KEY `idx_stock_out_type` (`stock_out_type`),
KEY `idx_ware_unique_code` (`ware_unique_code`) USING BTREE,
) ENGINE=InnoDB AUTO_INCREMENT=1649456 DEFAULT CHARSET=utf8 COMMENT='出库单表'
1 row in set (0.01 sec)
表大小 100w 行数据。
mysql> show table status like 'stock_out_doc_info' \G
*************************** 1. row ***************************
Name: stock_out_doc_info
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 1401412
Avg_row_length: 429
Data_length: 601882624
Max_data_length: 0
Index_length: 313458688
Data_free: 4194304
Auto_increment: 1649456
Create_time: 2023-03-29 09:58:32
Update_time: NULL
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment: 出库单表
1 row in set (0.00 sec)
is_delete=0 表示未删除，可以发现表中大部分数据逻辑未删除。
mysql> select is_delete,count(*) from stock_out_doc_info group by is_delete;
+-----------+----------+
| is_delete | count(*) |
+-----------+----------+
| 0 | 1471743 |
| 1 | 74 |
+-----------+----------+
2 rows in set (0.66 sec)
分析
慢日志
即使是全表扫描，100 多万条数据执行用时超过 6 秒也比较反常，因此查看慢日志进行确认。
# Time: 230327 10:18:37
# User@Host: admin[admin] @ [172.18.145.199] Id: 54041679
# Query_time: 6.529481 Lock_time: 0.000218 Rows_sent: 1 Rows_examined: 3161657
use las_spare_stockout;
SET timestamp=1679883517;
SELECT
sodi.id AS id
FROM
stock_out_doc_info sodi
LEFT JOIN stock_out_handover soh ON sodi.id = soh.stock_out_id
AND soh.is_delete = 0
LEFT JOIN stock_out_doc_refund sodr ON sodi.id = sodr.stock_out_id
AND sodr.is_delete = 0
AND sodr.out_refund_type = 1100
LEFT JOIN stock_out_doc_waybill sodw ON sodi.id = sodw.stock_out_id
AND sodw.is_delete = 0
WHERE
sodi.is_delete = 0
AND sodi.ware_unique_code IN (
...
)
AND sodi.stock_out_type IN (
...
)
AND (
sodi.stock_out_no = ''
OR sodr.out_refund_no = ''
)
;
其中：
Lock_time: 0.000218，表明没有发生锁等待；
Query_time: 6.529481，表明实际执行用时超过 6s；
Rows_sent: 1，表明
Rows_examined: 3161657，表明扫描行数大约是执行计划中显示的全表扫描行数的两倍。
那么为什么 stock_out_doc_info 表全表扫描，没有使用索引呢？
下面结合 trace 分析完整的执行计划生成过程。
trace
查看 trace，简化后的结果如下所示。
{
"rows_estimation": [
{
"table": "`stock_out_doc_info` `sodi`",
"range_analysis": {
"table_scan": {
"rows": 1479545,
"cost": 341095
},
"potential_range_indices": [
{
"index": "PRIMARY",
"usable": false,
"cause": "not_applicable"
},
{
"index": "idx_warehouse_code",
"usable": false,
"cause": "not_applicable"
},
{
"index": "idx_stock_out_no",
"usable": true, # possible_keys
"key_parts": [
"stock_out_no",
"id"
]
},
{
"index": "idx_stock_out_type",
"usable": true, # possible_keys
"key_parts": [
"stock_out_type",
"id"
]
},
{
"index": "idx_create_time",
"usable": false,
"cause": "not_applicable"
},
{
"index": "idx_refer",
"usable": false,
"cause": "not_applicable"
},
{
"index": "idx_process",
"usable": false,
"cause": "not_applicable"
},
{
"index": "idx_dest_ware_unique_code",
"usable": false,
"cause": "not_applicable"
},
{
"index": "idx_ware_unique_code",
"usable": true, # possible_keys
"key_parts": [
"ware_unique_code",
"id"
]
},
{
"index": "idx_stock_out_status",
"usable": false,
"cause": "not_applicable"
},
{
"index": "idx_predict_finish_time",
"usable": false,
"cause": "not_applicable"
}
],
"setup_range_conditions": [
],
"group_index_range": {
"chosen": false,
"cause": "not_single_table"
},
"analyzing_range_alternatives": {
"range_scan_alternatives": [
{
"index": "idx_stock_out_type",
"ranges": [
"100 <= stock_out_type <= 100",
"200 <= stock_out_type <= 200",
"250 <= stock_out_type <= 250",
...
],
"index_dives_for_eq_ranges": false,
"rowid_ordered": false,
"using_mrr": false,
"index_only": false,
"rows": 634086,
"cost": 760921,
"chosen": false,
"cause": "cost"
},
{
"index": "idx_ware_unique_code",
"ranges": [
"1001 <= ware_unique_code <= 1001",
"1002 <= ware_unique_code <= 1002",
"1003 <= ware_unique_code <= 1003"
...
],
"index_dives_for_eq_ranges": false,
"rowid_ordered": false,
"using_mrr": false,
"index_only": false,
"rows": 565218,
"cost": 678343,
"chosen": false,
"cause": "cost"
}
],
"analyzing_roworder_intersect": {
"usable": false,
"cause": "too_few_roworder_scans"
}
}
}
},
{
"table": "`stock_out_handover` `soh`",
"table_scan": {
"rows": 1391368,
"cost": 38016
}
},
{
"table": "`stock_out_doc_refund` `sodr`",
"table_scan": {
"rows": 898290,
"cost": 25021
}
},
{
"table": "`stock_out_doc_waybill` `sodw`",
"table_scan": {
"rows": 249196,
"cost": 4713
}
}
]
}
其中：
全表扫描的成本等于 341095，小于索引 idx_stock_out_type 的成本 760921，也小于索引 idx_ware_unique_code 的成本 678343。可见，全表扫描的性能优于这两个二级索引；
没有计算 idx_stock_out_no 的成本，原因是什么呢？
为验证当前索引的有效性，首先查看多个字段的区分度。
字段区分度
分别查询 stock_out_doc_info 表中三个查询字段的区分度
其中：
. ware_unique_code 与 stock_out_type 区分度低；
. 多字段查询区分度时显示 count(distinct stock_out_no) = 0，但实际上不等于0，原因待分析；
. stock_out_no 字段区分度高，实际上相当于唯一索引。但是执行计划中并不包括该索引。
尝试强制指定 idx_stock_out_no 索引，依然显示索引失效。
mysql> explain
SELECT sodi.id AS id
FROM stock_out_doc_info sodi
force index(idx_stock_out_no) ...\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: sodi
type: ALL
possible_keys: idx_stock_out_no
key: NULL
key_len: NULL
ref: NULL
rows: 1403150
Extra: Using where
...
stock_out_no 字段是 varchar 数据类型，数据示例如下所示。
mysql> select stock_out_no from stock_out_doc_info limit 3;
+------------------+
| stock_out_no |
+------------------+
| 1-100-xxxxxxxxx1 |
| 1-200-xxxxxxxxx2 |
| 1-300-xxxxxxxxx3 |
+------------------+
3 rows in set (0.00 sec)
explain format=json 可以打印详细的执行计划成本，因此进行分析。
explain format=json
mysql> explain format=json SELECT ... \G
*************************** 1. row ***************************
EXPLAIN: {
"query_block": {
"select_id": 1,
"nested_loop": [
{
"table": {
"table_name": "sodi",
"access_type": "ALL", # 全表扫描
"possible_keys": [ # 没有使用下列索引
"idx_stock_out_no",
"idx_stock_out_type",
"idx_ware_unique_code"
],
"rows": 1480082,
"filtered": 38.205,
"attached_condition": "((`las_spare_stockout`.`sodi`.`is_delete` = 0) and (`las_spare_stockout`.`sodi`.`ware_unique_code` in (...)) and (`las_spare_stockout`.`sodi`.`stock_out_type` in (...)))"
}
},
{
"table": {
"table_name": "soh",
"access_type": "ref",
"possible_keys": [
"idx_stock_out_id"
],
"key": "idx_stock_out_id",
"used_key_parts": [
"stock_out_id"
],
"key_length": "8",
"ref": [
"las_spare_stockout.sodi.id"
],
"rows": 1,
"filtered": 100,
"attached_condition": "<if>(is_not_null_compl(soh), ((`las_spare_stockout`.`soh`.`is_delete` = 0) and (`las_spare_stockout`.`sodi`.`id` = `las_spare_stockout`.`soh`.`stock_out_id`)), true)"
}
},
{
"table": {
"table_name": "sodr",
"access_type": "ref",
"possible_keys": [
"idx_stock_out_id"
],
"key": "idx_stock_out_id",
"used_key_parts": [
"stock_out_id"
],
"key_length": "8",
"ref": [
"las_spare_stockout.sodi.id"
],
"rows": 1,
"filtered": 100,
"attached_condition": "(<if>(found_match(sodr), ((`las_spare_stockout`.`sodi`.`stock_out_no` = 'xx') or (`las_spare_stockout`.`sodr`.`out_refund_no` = 'xx')), true) and <if>(is_not_null_compl(sodr), ((`las_spare_stockout`.`sodr`.`out_refund_type` = 1100) and (`las_spare_stockout`.`sodr`.`is_delete` = 0) and (`las_spare_stockout`.`sodi`.`id` = `las_spare_stockout`.`sodr`.`stock_out_id`)), true))"
}
},
{
"table": {
"table_name": "sodw",
"access_type": "ref",
"possible_keys": [
"idx_stock_out_id"
],
"key": "idx_stock_out_id",
"used_key_parts": [
"stock_out_id"
],
"key_length": "8",
"ref": [
"las_spare_stockout.sodi.id"
],
"rows": 1,
"filtered": 100,
"attached_condition": "<if>(is_not_null_compl(sodw), ((`las_spare_stockout`.`sodw`.`is_delete` = 0) and (`las_spare_stockout`.`sodi`.`id` = `las_spare_stockout`.`sodw`.`stock_out_id`)), true)"
}
}
]
}
}
1 row in set, 1 warning (0.00 sec)
到目前为止，仅发现 sodi 表全表扫描，但是索引失效的原因不确定。
可以再看下查询条件。
WHERE
sodi.ware_unique_code IN (
...
)
AND sodi.stock_out_type IN (
...
)
AND (
sodi.stock_out_no = ''
OR sodr.out_refund_no = ''
)
其中 sodi 表索引失效可能有两个原因：
1. IN 导致索引失效
2. OR 导致索引失效
如果通过删除查询条件验证导致索引失效的原因，可能导致查询结果发生变化，因此不足以证明。由于强制指定 idx_stock_out_no 索引依然失效，因此怀疑是 OR 导致索引失效。将 OR 连接的多个查询条件拆分为单个查询。
mysql> explain select id from stock_out_doc_info where stock_out_no = 'xx' \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: stock_out_doc_info
type: ref
possible_keys: idx_stock_out_no
key: idx_stock_out_no
key_len: 767
ref: const
rows: 1
Extra: Using where; Using index
1 row in set (0.00 sec)
mysql> explain select id from stock_out_doc_refund where out_refund_no = 'xx' \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: stock_out_doc_refund
type: ref
possible_keys: idx_out_refund_no
key: idx_out_refund_no
key_len: 767
ref: const
rows: 1
Extra: Using where; Using index
1 row in set (0.00 sec)
显示索引均生效，两个表对应的查询字段都有索引。但是通过 OR 连接后的多个查询条件索引失效。
OR 改写为 Union
改写后的 SQL 如下所示。
SELECT
sodi.id AS id
FROM
stock_out_doc_info sodi
LEFT JOIN stock_out_handover soh ON sodi.id = soh.stock_out_id
AND soh.is_delete = 0
LEFT JOIN stock_out_doc_refund sodr ON sodi.id = sodr.stock_out_id
AND sodr.is_delete = 0
AND sodr.out_refund_type = 1100
LEFT JOIN stock_out_doc_waybill sodw ON sodi.id = sodw.stock_out_id
AND sodw.is_delete = 0
WHERE
sodi.is_delete = 0
AND sodi.ware_unique_code IN (
...
)
AND sodi.stock_out_type IN (
...
)
AND
sodi.stock_out_no = ''
union
SELECT
sodi.id AS id
FROM
stock_out_doc_info sodi
LEFT JOIN stock_out_handover soh ON sodi.id = soh.stock_out_id
AND soh.is_delete = 0
LEFT JOIN stock_out_doc_refund sodr ON sodi.id = sodr.stock_out_id
AND sodr.is_delete = 0
AND sodr.out_refund_type = 1100
LEFT JOIN stock_out_doc_waybill sodw ON sodi.id = sodw.stock_out_id
AND sodw.is_delete = 0
WHERE
sodi.is_delete = 0
AND sodi.ware_unique_code IN (
...
)
AND sodi.stock_out_type IN (
...
)
and
sodr.out_refund_no = ''
;
执行计划显示 idx_stock_out_no 索引生效。
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: sodi
type: ref
possible_keys: idx_stock_out_no,idx_stock_out_type,idx_ware_unique_code
key: idx_stock_out_no
key_len: 767
ref: const
rows: 1
Extra: Using index condition; Using where
*************************** 2. row ***************************
id: 1
select_type: PRIMARY
table: soh
type: ref
possible_keys: idx_stock_out_id
key: idx_stock_out_id
key_len: 8
ref: las_spare_stockout.sodi.id
rows: 1
Extra: Using where
*************************** 3. row ***************************
id: 1
select_type: PRIMARY
table: sodr
type: ref
possible_keys: idx_stock_out_id
key: idx_stock_out_id
key_len: 8
ref: las_spare_stockout.sodi.id
rows: 1
Extra: Using where
*************************** 4. row ***************************
id: 1
select_type: PRIMARY
table: sodw
type: ref
possible_keys: idx_stock_out_id
key: idx_stock_out_id
key_len: 8
ref: las_spare_stockout.sodi.id
rows: 1
Extra: Using where
*************************** 5. row ***************************
id: 2
select_type: UNION
table: sodr
type: ref
possible_keys: idx_stock_out_id,idx_out_refund_no
key: idx_out_refund_no
key_len: 767
ref: const
rows: 1
Extra: Using index condition; Using where
*************************** 6. row ***************************
id: 2
select_type: UNION
table: sodi
type: eq_ref
possible_keys: PRIMARY,idx_stock_out_type,idx_ware_unique_code
key: PRIMARY
key_len: 8
ref: las_spare_stockout.sodr.stock_out_id
rows: 1
Extra: Using where
*************************** 7. row ***************************
id: 2
select_type: UNION
table: sodw
type: ref
possible_keys: idx_stock_out_id
key: idx_stock_out_id
key_len: 8
ref: las_spare_stockout.sodi.id
rows: 1
Extra: Using where
*************************** 8. row ***************************
id: 2
select_type: UNION
table: soh
type: ref
possible_keys: idx_stock_out_id
key: idx_stock_out_id
key_len: 8
ref: las_spare_stockout.sodi.id
rows: 1
Extra: Using where
*************************** 9. row ***************************
id: NULL
select_type: UNION RESULT
table: <union1,2>
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: NULL
Extra: Using temporary
9 rows in set (0.00 sec)
其中：
执行计划返回 9 条记录，其中前 8 条记录的 id 分别等于 1 和 2，其中 id 不同时，
第 9 条记录的 id: NULL，表明是一个结果集，不用于查询，常出现在包含union等查询语句中。
查询用时等于 0.00 s，慢 SQL 优化完成。
+---------+
| id |
+---------+
| ... |
+---------+
1 row in set (0.00 sec)
下面测试验证，通过 OR 连接后的多个查询条件索引失效。
测试
准备数据
创建两张表用于测试。
mysql> show create table join_t1 \G
*************************** 1. row ***************************
Table: join_t1
Create Table: CREATE TABLE `join_t1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`a` int(11) DEFAULT NULL,
`b` varchar(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_int_a` (`a`),
KEY `idx_b` (`b`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
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,
PRIMARY KEY (`id`),
KEY `idx_varchar_a` (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
其中：
两张表中 a 字段的数据类型不同，一个是 int，一个是 varchar；
join_t2 表中 a 字段有索引，b 字段无索引。
单表无索引
AND 连接多个查询条件，其中 a 有索引，b 没有索引，索引生效。
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)
OR 连接多个查询条件，其中 a 有索引，b 没有索引，索引失效。
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)
因此，单表查询中 OR 连接多个查询条件时，如果一个字段没有创建索引，将导致整个查询语句索引失效。
下面分析原因。
首先，a='a' or b='b' 的查询结果可能包括以下三类。
由于 a 列有索引，而 b 列没有索引，因此通过索引 a 无法查到 a!='a' and b='b' 的数据，因此仅扫描索引 a 再回表可能导致查询结果数据丢失，因此从理论上就不可行，所以必然存在全表扫描的环节。
假设 MySQL 一定要走索引，可能需要三步：索引扫描 + 全表扫描 + 结果合并。
由于其中全表扫描的步骤无法省略，因此优化器选择直接一遍全表扫描。
实际上，即使指定强制索引 a，依然是全表扫描。
但是，本文中与这条 SQL 有两点不同：
1.属于多表关联查询，而不是单表查询；
2.OR 前后是两个表的条件，并且两个查询字段都有索引。
因此有没有可能是当 OR 前后是两个表的条件时，索引将失效？
下面进行验证。
关联条件
关联条件与查询条件分别如下所示。
select t1.id, t2.id from join_t1 as t1
left join join_t2 as t2 on t1.b=t2.a
where (t1.b='b' or t2.a='a') \G
执行计划如下所示。
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t1
partitions: NULL
type: index
possible_keys: idx_b
key: idx_b
key_len: 36
ref: NULL
rows: 3
filtered: 100.00
Extra: Using index
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: t2
partitions: NULL
type: ref
possible_keys: idx_varchar_a
key: idx_varchar_a
key_len: 36
ref: test_zk.t1.b
rows: 1
filtered: 100.00
Extra: Using where; Using index
2 rows in set, 1 warning (0.00 sec)
其中：
1.驱动表 t1 全索引扫描，原因是没有过滤条件；
2.被驱动表 t2 显示 type: ref，表明属于非唯一索引访问。
可见当 OR 前后是两个表的条件时，索引不一定失效。
当前查询条件是关联条件，如果查询条件不是关联条件，结果会怎么样呢？
非关联条件
关联条件与查询条件分别如下所示。
select t1.id, t2.id
from join_t1 as t1 left join join_t2 as t2 on t1.b=t2.a
where (t1.a=1 or t2.a='a') \G
执行计划如下所示。
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t1
partitions: NULL
type: ALL
possible_keys: idx_int_a
key: NULL
key_len: NULL
ref: NULL
rows: 3
filtered: 100.00
Extra: NULL
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: t2
partitions: NULL
type: ref
possible_keys: idx_varchar_a
key: idx_varchar_a
key_len: 36
ref: test_zk.t1.b
rows: 1
filtered: 100.00
Extra: Using where; Using index
2 rows in set, 1 warning (0.00 sec)
其中：
驱动表 t1 全表扫描，复现生产案例。
因此，当 OR 前后是两个表的条件，并且查询条件不是关联条件时，索引将失效。
那么，为什么当查询条件是关联条件时，索引生效呢？原因是两个表连接的过程中首先从驱动表中执行单表查询语句并找到满足条件的记录，然后针对从驱动表产生的结果集中的每一条记录，分别在被驱动表中查找符合过滤条件的记录。当查询条件是关联条件时，可以直接过滤不符合条件的记录，不会导致查询结果数据丢失。
当查询条件不是关联条件时，如果直接过滤不符合条件的记录，可能导致查询结果数据丢失。可见，其中的关键在于两个表连接时，被驱动表中被扫描的数据依赖驱动表的查询结果。
结论
首先上结论，OR 连接多个查询条件有可能导致索引失效，无论是单表还是多表，其中：
单表， OR 连接多个查询条件时，如果一个查询字段没有创建索引，将导致整个查询语句索引失效。
多表， OR 连接多个查询条件时，如果查询条件不是关联条件，即使每个查询字段都有索引，也将导致索引失效。
因此不建议用 OR 连接多个查询条件，建议用 Union 改写 SQL。
下面分析原因:
对于单表查询，where a='a' and b='b'，其中 a 有索引，b 没有索引。仅扫描索引 a 再回表可能导致查询结果数据丢失，因此从理论上就不可行，所以必然存在全表扫描的环节。假设 MySQL 一定要走索引，可能需要三步：索引扫描 + 全表扫描 + 结果合并。由于其中全表扫描的步骤无法省略，因此优化器选择直接一遍全表扫描。实际上，即使指定强制索引，依然是全表扫描。
对于多表查询，t1 left join t2 on t1.b=t2.a where (t1.a=1 or t2.a='a')，其中 t1.a、t2.a 都有索引。由于两个表连接时，被驱动表中被扫描的数据依赖驱动表的查询结果。
因此：
当查询条件是关联条件时，可以直接过滤不符合条件的记录，不会导致查询结果数据丢失；
当查询条件不是关联条件时，如果直接过滤不符合条件的记录，可能导致查询结果数据丢失。
闽公网安备 35020302035485号
