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