mysql 删除or语句_MySQL Case--优化OR语句踩坑记录

问题描述

研发同事反馈某应用执行较慢，对应SQL为：

UPDATEbs_serial_tracSET SERIAL_IS_LOCK = 0,

LOCK_VALUE= '',

UPDATE_USER= 'transSys'

WHERE GOODS_NO = '4418095740626'

AND(

PARENT_CODE= 'F9G7S19722001835'

OR SERIAL = 'F9G7S19722001835');

表bs_serial_trac上索引情况为：

PRIMARY KEY(`ID`),UNIQUE KEY`idx_complex_serial_goodsNo` (`SERIAL`,`GOODS_NO`),KEY`idx_update_time` (`UPDATE_TIME`),KEY`idx_serial_goodsNo` (`GOODS_NO`),KEY`idx_parent_code` (`PARENT_CODE`),KEY `idx_lock_value` (`LOCK_VALUE`)

由于使用OR条件，查询只能基于条件GOODS_NO = '4418095740626' 进行数据查找，其执行计划为：

*************************** 1. row ***************************id:1select_type:UPDATE

table: bs_serial_trac

partitions:NULLtype: range

possible_keys: idx_serial_goodsNokey: idx_serial_goodsNo

key_len:93ref: const

rows:404920filtered:100.00Extra: Usingwhere

1 row in set (0.00 sec)

由于GOODS_NO列选择性较差，满足条件的记录较多，导致查询性能较差：

SELECT COUNT(1)FROMbs_serial_tracWHERE GOODS_NO = '4418095740626';+----------+

| COUNT(1) |

+----------+

| 215447 |

+----------+

解决步骤

问题很明显，由于OR语句导致索引无法正常使用，将OR调整为UNION ALL，调整后的SQL语句为：

SELECT *

FROMbs_serial_tracWHERE GOODS_NO = '4418095740626'

AND PARENT_CODE = 'F9G7S19722007485'

UNION ALL

SELECT *

FROMbs_serial_tracWHERE GOODS_NO = '4418095740626'

AND SERIAL = 'F9G7S19722007485'

AND PARENT_CODE <> 'F9G7S19722007485'

查询速度很快，由之前的800ms优化到10ms以下，看起来很完美，但是查询结果没数据。。。

简单定位下，发现PARENT_CODE列类型为varchar(50) DEFAULT ''，PARENT_CODE列值为NULL，做不等于判断时存在问题：

SELECT

NOT(PARENT_CODE <=> 'F9G7S19722007485') ASC1,NOT(PARENT_CODE = 'F9G7S19722007485') ASC2,

PARENT_CODE<> 'F9G7S19722007485' ASC3FROMbs_serial_tracWHERE GOODS_NO = '4418095740626'

AND SERIAL = 'F9G7S19722007485';+----+------+------+

| C1 | C2 | C3 |

+----+------+------+

| 1 | NULL | NULL |

+----+------+------+

因此改写为UNION ALL语句时需要改写为：

SELECT *

FROMbs_serial_tracWHERE GOODS_NO = '4418095740626'

AND PARENT_CODE = 'F9G7S19722007485'

UNION ALL

SELECT *

FROMbs_serial_tracWHERE GOODS_NO = '4418095740626'

AND SERIAL = 'F9G7S19722007485'

AND NOT(PARENT_CODE <=> 'F9G7S19722007485')

或改写为：

SELECT *

FROMbs_serial_tracWHERE GOODS_NO = '4418095740626'

AND PARENT_CODE = 'F9G7S19722007485'

UNION ALL

SELECT *

FROMbs_serial_tracWHERE GOODS_NO = '4418095740626'

AND SERIAL = 'F9G7S19722007485'

AND (PARENT_CODE <> 'F9G7S19722007485' OR PARENT_CODE IS NULL)

由于在UNION ALL的第二部分查询中，PARENT_CODE不用于索引查找，只用于数据过滤，因此两种方式都不会影响查询性能。

确认使用UNION ALL性能满足需求后，将UPDATE操作改写为：

UPDATEbs_serial_tracSET SERIAL_IS_LOCK = 0,

LOCK_VALUE= '',

UPDATE_USER= 'transSys'

WHERE ID IN(SELECT ID FROM(SELECTIDFROMbs_serial_tracWHERE GOODS_NO = '4418095740626'

AND PARENT_CODE = 'F9G7S19722007485'

UNION ALL

SELECTIDFROMbs_serial_tracWHERE GOODS_NO = '4418095740626'

AND SERIAL = 'F9G7S19722007485'

AND (PARENT_CODE <> 'F9G7S19722007485' OR PARENT_CODE IS NULL)

)AST1

)

其对于执行计划为：

*************************** 1. row ***************************id:1select_type:UPDATE

table: bs_serial_trac

partitions:NULLtype:indexpossible_keys:NULL

key: PRIMARYkey_len:8ref:NULLrows:13270473filtered:100.00Extra: Usingwhere

*************************** 2. row ***************************id:2select_type: DEPENDENT SUBQUERYtable: partitions:NULLtype: index_subquery

possible_keys:

key: key_len:8ref: func

rows:2filtered:100.00Extra: Usingindex

*************************** 3. row ***************************id:3select_type: DERIVEDtable: bs_serial_trac

partitions:NULLtype: ref

possible_keys: idx_serial_goodsNo,idx_parent_codekey: idx_parent_code

key_len:153ref: const

rows:1filtered:5.00Extra: Usingwhere

*************************** 4. row ***************************id:4select_type:UNION

table: bs_serial_trac

partitions:NULLtype: const

possible_keys: idx_complex_serial_goodsNo,idx_serial_goodsNo,idx_parent_codekey: idx_complex_serial_goodsNo

key_len:695ref: const,const

rows:1filtered:100.00Extra:NULL

4 rows in set (0.00 sec)

虽然按照主键ID去更新，但是由于使用IN语句，仍导致查询走全表扫描，性能极差，需要将IN查询转换成INNER JOIN：

UPDATEbs_serial_tracAST2INNER JOIN(SELECTIDFROMbs_serial_tracWHERE GOODS_NO = '4418095740626'

AND PARENT_CODE = 'F9G7S19722007485'

UNION ALL

SELECTIDFROMbs_serial_tracWHERE GOODS_NO = '4418095740626'

AND SERIAL = 'F9G7S19722007485'

AND (PARENT_CODE <> 'F9G7S19722007485' OR PARENT_CODE IS NULL)

)AST1ON T1.ID=T2.IDSETT2.SERIAL_IS_LOCK= 0,

T2.LOCK_VALUE= '',

T2.UPDATE_USER= 'transSys'

修改后执行计划为：

*************************** 1. row ***************************id:1select_type:PRIMARY

table: partitions:NULLtype:ALLpossible_keys:NULL

key: NULLkey_len:NULLref:NULLrows:3filtered:100.00Extra:NULL

*************************** 2. row ***************************id:1select_type:UPDATE

table: T2

partitions:NULLtype: eq_ref

possible_keys:PRIMARY

key: PRIMARYkey_len:8ref: T1.ID

rows:1filtered:100.00Extra:NULL

*************************** 3. row ***************************id:2select_type: DERIVEDtable: bs_serial_trac

partitions:NULLtype: ref

possible_keys: idx_serial_goodsNo,idx_parent_codekey: idx_parent_code

key_len:153ref: const

rows:1filtered:5.00Extra: Usingwhere

*************************** 4. row ***************************id:3select_type:UNION

table: bs_serial_trac

partitions:NULLtype: const

possible_keys: idx_complex_serial_goodsNo,idx_serial_goodsNo,idx_parent_codekey: idx_complex_serial_goodsNo

key_len:695ref: const,const

rows:1filtered:100.00Extra:NULL

4 rows in set (0.00 sec)

调整能正常按照主键去操作，性能有保障。

<=>操作符

<=> : NULL-safe equal. This operator performs an equality comparison like the = operator, but returns 1 rather than NULL if both operands are NULL, and 0 rather than NULL if one operand is NULL.

参考： https://dev.mysql.com/doc/refman/8.0/en/comparison-operators.html#operator_equal-to