问题描述
研发同事反馈某应用执行较慢，对应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: rangepossible_keys: idx_serial_goodsNo key: idx_serial_goodsNo key_len: 93 ref: const rows: 404920 filtered: 100.00 Extra: Using where1 row in set (0.00 sec)
由于GOODS_NO列选择性较差，满足条件的记录较多，导致查询性能较差：
SELECT COUNT(1)FROM bs_serial_tracWHERE 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 ALLSELECT *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 C3FROM 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 ALLSELECT *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 ALLSELECT *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: indexpossible_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:partitions: NULL type: index_subquerypossible_keys: key: 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: refpossible_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: constpossible_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: NULL4 rows in set (0.00 sec)
虽然按照主键ID去更新，但是由于使用IN语句，仍导致查询走全表扫描，性能极差，需要将IN查询转换成INNER JOIN：
UPDATEbs_serial_trac AS T2INNER 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.IDSETT2.SERIAL_IS_LOCK = 0,T2.LOCK_VALUE = '',T2.UPDATE_USER = 'transSys'
修改后执行计划为：
*************************** 1. row *************************** id: 1 select_type: PRIMARY table:partitions: NULL type: ALLpossible_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_refpossible_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: refpossible_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: constpossible_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: NULL4 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