SQL查询优化-------周东海

SQL查询的时候尽量避免查询是不进行计算

2.避免丶情况 !=,<>, 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时不能使用索引

3.合理使用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);

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

SELECT * FROM cnt WHERE id <> 10

#between是连续的范围可以用索引
SELECT * FROM emp WHERE age BETWEEN 100 AND 200;

#in不连续的范围，不能使用索引
SELECT * FROM emp WHERE age IN (100,150,200);

#distinct去重：从重复的数据取一个
SELECT DISTINCT NAME FROM emp ;
#group by分组
SELECT NAME FROM emp  GROUP BY NAME;