sql 优化的问题
澳门银河官网
澳门银河官网
当前位置 : 澳门银河官网 > 澳门银河官网网址

sql 优化的问题

这个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来替代

广告位

澳门银河官网