sql 优化的问题
程序员文章站
2022-05-19 09:30:54
...
这个sql 还要怎么优化?
SELECT COUNT(*) AS tp_count FROM `course`
WHERE `project_id` = 1 AND `project_id` NOT IN ('15','20','21','22','23','24','25','26','27','28','30','31','32','36','38','39','40')
AND `isissue` = 1 AND `isapp` = 0 AND `isdel` = 0
AND `subject_id` NOT IN ('10','19','20','21','22','23','24','25','28','29','30','31','32','33','34','35','41','42','47','48','49','50','51','52','53','54','55','56','57','58','60','62','63','69','71','72','73','74','75','76','77','78','79','82','85','86','87','88','89','90','93','95','96','97','98','99','100','101','107','108','109','110','111','113','114','115','116','117','118','119','120','121','122','123','124','125','126','130','131','132','133','134','145','146','151','159','161','167','168','170','172','173','174','175','176','177','182','192','193','194','199','208','209','210','211','213','214','215','216','217','218','219','220')
AND `zone` NOT IN ('3','4','6') AND `kind` NOT IN ('1','2') LIMIT 1
回复内容:
这个sql 还要怎么优化?
SELECT COUNT(*) AS tp_count FROM `course`
WHERE `project_id` = 1 AND `project_id` NOT IN ('15','20','21','22','23','24','25','26','27','28','30','31','32','36','38','39','40')
AND `isissue` = 1 AND `isapp` = 0 AND `isdel` = 0
AND `subject_id` NOT IN ('10','19','20','21','22','23','24','25','28','29','30','31','32','33','34','35','41','42','47','48','49','50','51','52','53','54','55','56','57','58','60','62','63','69','71','72','73','74','75','76','77','78','79','82','85','86','87','88','89','90','93','95','96','97','98','99','100','101','107','108','109','110','111','113','114','115','116','117','118','119','120','121','122','123','124','125','126','130','131','132','133','134','145','146','151','159','161','167','168','170','172','173','174','175','176','177','182','192','193','194','199','208','209','210','211','213','214','215','216','217','218','219','220')
AND `zone` NOT IN ('3','4','6') AND `kind` NOT IN ('1','2') LIMIT 1
一个是sql本身,另一个你还需要创建适当的索引才能更好的优化。比如project_id, isissue,isapp,isdel,subject_id, zone, kind都应该是被索引的。
对于sql本身来说,建议
1-将简单条件前置,如
先判断isissue = 1 and isapp = 0 and isdel = 0 and project_id = 1
2- 去掉无效的条件,如
project_id = 1 了 那还 project_id not in ... 干啥?如果两者不冲突后者就是废话,如果两者冲突count(*)必然是0,组合sql之前完全能判断出来。
in和not in请使用exists和not exists来替代