sql优化查询

1.避免丶情况 !=,<>, is null,is not null,in,not in;

例如
a语句
SELECT COUNT(*) FROM emp WHERE age IN(SELECT age FROM emp WHERE id > 100);
b语句
SELECT COUNT(*) FROM emp WHERE EXISTS(SELECT age FROM emp WHERE id > 100);
使用in时不能使用索引

2.合理使用exists ， not exists

#合理使用exists，not exists
#建表
CREATE TABLE IF NOT EXISTS t1(c1 INT,c2 INT);
CREATE TABLE IF NOT EXISTS t2(c1 INT,c2 INT);
 
#使用where > 0
SELECT SUM(t1.c1) FROM T1 WHERE (SELECT COUNT(*) FROM t2 WHERE t2.c2=t1.c2 > 0);
#使用exists
SELECT SUM(t1.c1) FROM T1 WHERE EXISTS (SELECT COUNT(*) FROM t2 WHERE t2.c2=t1.c2);

3.避免<> 和!=符合（均为不等于符号）

SELECT * FROM cnt WHERE id <> 10