mysql使用or条件使索引失效

黎小强
2020-12-27 / 0 评论 / 4,061 阅读 / 正在检测是否收录...
原因

很多人都喜欢用 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_monthfreeze_up_monthcheck_money_2_month虽然都加了索引,但是EXPLAIN分析下, type是 all ,还是走全表扫描(ALL),非常慢。

1.png

解决方案

把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'
执行结果

2.png

再看,这个时候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有不同
1

评论 (0)

取消