mysql使用or条件使索引失效

原因

很多人都喜欢用 OR 去解决，虽然语句简单，但是就会发生下面的场景，容易走不到索引

失效指的是，条件中如果有or，只要其中一个条件没有索引，其他字段有索引也不会使用。

原sql语句

SELECT
    `user`,
    `check_money`,
    `is_freeze_up`,
    `freeze_up_month`,
    `freeze_money`,
    `freeze_up_time`,
    `check_money_2_month`,
    `check_money_2`,
    `check_money_2_time`,
    `tc_qr`,
    `log_id`,
    1 AS type,
    `branch` 
FROM
    `pxs_custom_log_bv_sale` `s`
    LEFT JOIN `pxs_custom_money_log` `l` ON `s`.`log_id` = `l`.`id` 
WHERE
    ( l.lock_month = '2020-11' OR s.freeze_up_month = '2020-11' OR s.check_money_2_month = '2020-11' )

因为表设计这种结构并且要求这种业务场景。
这里的lock_month 和freeze_up_month、check_money_2_month虽然都加了索引，但是EXPLAIN分析下， type是 all ，还是走全表扫描（ALL），非常慢。

解决方案

把3个where的字段分开几个sql段。

使用union或者union all去连接多个 SELECT 语句的结果组合到一个结果集合中。


EXPLAIN
SELECT
    `user`,
    `check_money`,
    `is_freeze_up`,
    `freeze_up_month`,
    `freeze_money`,
    `freeze_up_time`,
    `check_money_2_month`,
    `check_money_2`,
    `check_money_2_time`,
    `tc_qr`,
    `log_id`,
    1 AS type,
    `branch` 
FROM
    `pxs_custom_log_bv_sale` `s`
    LEFT JOIN `pxs_custom_money_log` `l` ON `s`.`log_id` = `l`.`id` 
WHERE
    ( l.lock_month = '2020-11') 
    
UNION all

SELECT
    `user`,
    `check_money`,
    `is_freeze_up`,
    `freeze_up_month`,
    `freeze_money`,
    `freeze_up_time`,
    `check_money_2_month`,
    `check_money_2`,
    `check_money_2_time`,
    `tc_qr`,
    `log_id`,
    1 AS type,
    `branch` 
FROM
    `pxs_custom_log_bv_sale` `s`
    LEFT JOIN `pxs_custom_money_log` `l` ON `s`.`log_id` = `l`.`id` 
    WHERE s.freeze_up_month = '2020-11'
    
union all 
    
SELECT
    `user`,
    `check_money`,
    `is_freeze_up`,
    `freeze_up_month`,
    `freeze_money`,
    `freeze_up_time`,
    `check_money_2_month`,
    `check_money_2`,
    `check_money_2_time`,
    `tc_qr`,
    `log_id`,
    1 AS type,
    `branch` 
FROM
    `pxs_custom_log_bv_sale` `s`
    LEFT JOIN `pxs_custom_money_log` `l` ON `s`.`log_id` = `l`.`id` 
    
    WHERE s.check_money_2_month = '2020-11'

执行结果

再看，这个时候type 是range级别,
这样在生产环境上测试，会比原先快很多。

解决思路

优化的方法是改成union分成多个 sql，走各自的索引。如果可能有重复就用union，不用union all。
or 条件在数据库上很难优化，能在代码里优化逻辑，不至于拖垮数据库。只有在 or 条件下无需索引时（且需要比较的数据量小），才考虑。
相同字段or可改成in，如 f_id=1 or f_id=100 -> f_id in (1,100)。上述优化情景都是存储引擎在 InnoDB 情况下，在MyISAM有不同