通过 orm 的 query 查出来的 sql 语句大概是这个样子的:
SELECT * FROM `table` WHERE (`table`.`column1` IN ('condition1', 'condition1', 'condition1', 'condition1', 'condition1', 'condition1') AND `table`.`column1` = '2021-01-03' AND NOT (`table`.`column2` IN ('condition2', 'condition2') AND `table`.`column2` IS NOT NULL) AND ((`table`.`column3` = 'column3`' AND NOT (`table`.`column3` = 'condition3' AND `table`.`column3` IS NOT NULL)) OR `table`.`column4` IN ('condition4', 'condition4', 'condition4')) AND (`table`.`column5` LIKE BINARY 'condition5%' OR `table`.`column6` = 'condition6'))
数据量大概 600w,查出来的结果有 100 条 在 mysql 里试了下,大概要花 7min,explain 得到的结果是,只有 column1, column2 用到了索引,我对每个 column 都建了索引,请问下还有什么办法优化阿。。。谢谢~
1
neoblackcap 2021-02-07 12:47:06 +08:00
干掉 like 查询
|
2
wuwukai007 2021-02-07 18:57:21 +08:00 via Android
建议先写 sql 在转成 orm,orm 是个黑箱子
|