MySQL Case--优化OR语句踩坑记录
问题描述
研发同事反馈某应用执行较慢，对应SQL为：
UPDATE bs_serial_trac SET 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: 1 select_type: UPDATE table: bs_serial_trac partitions: NULL type: range possible_keys: idx_serial_goodsNo key: idx_serial_goodsNo key_len: 93 ref: const rows: 404920 filtered: 100.00 Extra: Using where 1 row in set (0.00 sec)
由于GOODS_NO列选择性较差，满足条件的记录较多，导致查询性能较差：
SELECT COUNT(1) FROM bs_serial_trac WHERE GOODS_NO = '4418095740626'; +----------+ | COUNT(1) | +----------+ | 215447 | +----------+
解决步骤
问题很明显，由于OR语句导致索引无法正常使用，将OR调整为UNION ALL，调整后的SQL语句为：
SELECT * FROM bs_serial_trac WHERE GOODS_NO = '4418095740626' AND PARENT_CODE = 'F9G7S19722007485' UNION ALL SELECT * FROM bs_serial_trac WHERE 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') AS C1, NOT(PARENT_CODE = 'F9G7S19722007485') AS C2, PARENT_CODE <> 'F9G7S19722007485' AS C3 FROM bs_serial_trac WHERE GOODS_NO = '4418095740626' AND SERIAL = 'F9G7S19722007485' ; +----+------+------+ | C1 | C2 | C3 | +----+------+------+ | 1 | NULL | NULL | +----+------+------+
因此改写为UNION ALL语句时需要改写为：
SELECT * FROM bs_serial_trac WHERE GOODS_NO = '4418095740626' AND PARENT_CODE = 'F9G7S19722007485' UNION ALL SELECT * FROM bs_serial_trac WHERE GOODS_NO = '4418095740626' AND SERIAL = 'F9G7S19722007485' AND NOT(PARENT_CODE <=> 'F9G7S19722007485')
或改写为：
SELECT * FROM bs_serial_trac WHERE GOODS_NO = '4418095740626' AND PARENT_CODE = 'F9G7S19722007485' UNION ALL SELECT * FROM bs_serial_trac WHERE GOODS_NO = '4418095740626' AND SERIAL = 'F9G7S19722007485' AND (PARENT_CODE <> 'F9G7S19722007485' OR PARENT_CODE IS NULL)
由于在UNION ALL的第二部分查询中，PARENT_CODE不用于索引查找，只用于数据过滤，因此两种方式都不会影响查询性能。
确认使用UNION ALL性能满足需求后，将UPDATE操作改写为：
UPDATE bs_serial_trac SET SERIAL_IS_LOCK = 0, LOCK_VALUE = '', UPDATE_USER = 'transSys' WHERE ID IN( SELECT ID FROM( SELECT ID FROM bs_serial_trac WHERE GOODS_NO = '4418095740626' AND PARENT_CODE = 'F9G7S19722007485' UNION ALL SELECT ID FROM bs_serial_trac WHERE GOODS_NO = '4418095740626' AND SERIAL = 'F9G7S19722007485' AND (PARENT_CODE <> 'F9G7S19722007485' OR PARENT_CODE IS NULL) ) AS T1 )
其对于执行计划为：
*************************** 1. row *************************** id: 1 select_type: UPDATE table: bs_serial_trac partitions: NULL type: index possible_keys: NULL key: PRIMARY key_len: 8 ref: NULL rows: 13270473 filtered: 100.00 Extra: Using where *************************** 2. row *************************** id: 2 select_type: DEPENDENT SUBQUERY table: <derived3> partitions: NULL type: index_subquery possible_keys: <auto_key0> key: <auto_key0> key_len: 8 ref: func rows: 2 filtered: 100.00 Extra: Using index *************************** 3. row *************************** id: 3 select_type: DERIVED table: bs_serial_trac partitions: NULL type: ref possible_keys: idx_serial_goodsNo,idx_parent_code key: idx_parent_code key_len: 153 ref: const rows: 1 filtered: 5.00 Extra: Using where *************************** 4. row *************************** id: 4 select_type: UNION table: bs_serial_trac partitions: NULL type: const possible_keys: idx_complex_serial_goodsNo,idx_serial_goodsNo,idx_parent_code key: idx_complex_serial_goodsNo key_len: 695 ref: const,const rows: 1 filtered: 100.00 Extra: NULL 4 rows in set (0.00 sec)
虽然按照主键ID去更新，但是由于使用IN语句，仍导致查询走全表扫描，性能极差，需要将IN查询转换成INNER JOIN：
UPDATE bs_serial_trac AS T2 INNER JOIN ( SELECT ID FROM bs_serial_trac WHERE GOODS_NO = '4418095740626' AND PARENT_CODE = 'F9G7S19722007485' UNION ALL SELECT ID FROM bs_serial_trac WHERE GOODS_NO = '4418095740626' AND SERIAL = 'F9G7S19722007485' AND (PARENT_CODE <> 'F9G7S19722007485' OR PARENT_CODE IS NULL) ) AS T1 ON T1.ID=T2.ID SET T2.SERIAL_IS_LOCK = 0, T2.LOCK_VALUE = '', T2.UPDATE_USER = 'transSys'
修改后执行计划为：
*************************** 1. row *************************** id: 1 select_type: PRIMARY table: <derived2> partitions: NULL type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 3 filtered: 100.00 Extra: NULL *************************** 2. row *************************** id: 1 select_type: UPDATE table: T2 partitions: NULL type: eq_ref possible_keys: PRIMARY key: PRIMARY key_len: 8 ref: T1.ID rows: 1 filtered: 100.00 Extra: NULL *************************** 3. row *************************** id: 2 select_type: DERIVED table: bs_serial_trac partitions: NULL type: ref possible_keys: idx_serial_goodsNo,idx_parent_code key: idx_parent_code key_len: 153 ref: const rows: 1 filtered: 5.00 Extra: Using where *************************** 4. row *************************** id: 3 select_type: UNION table: bs_serial_trac partitions: NULL type: const possible_keys: idx_complex_serial_goodsNo,idx_serial_goodsNo,idx_parent_code key: idx_complex_serial_goodsNo key_len: 695 ref: const,const rows: 1 filtered: 100.00 Extra: 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
