原因
很多人都喜欢用 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有不同
评论 (0)