sql查询优化__邓治安

#避免的情况 != ,  < >  ,is null , in not null, in  , not in
SELECT COUNT(*) FROM emp WHERE age IN(SELECT age FROM emp WHERE id >100);
SELECT COUNT(*) FROM emp WHERE EXISTS(SELECT age FROM emp WHERE id > 100);

CREATE TABLE t1(c1 INT,c2 INT)
CREATE TABLE t2(c1 INT,c2 INT)

SELECT SUM(t1,c1) FROM t1 WHERE (SELECT COUNT(*) FROM t2 WHERE t2.c2=t1.c2 >0)

SELECT SUM(t1,c1) FROM t1 WHERE EXISTS (SELECT COUNT(*) FROM t2 WHERE t2.c2=t1.c2)

#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

#创建索引
CREATE INDEX IX ON T1(processid)

#a语句不使用索引
SELECT * FROM T1 WHERE nextprocess = 1 AND processid IN (8,32,45);
-- b语句强制使用索引
SELECT * FROM T1 FORCE INDEX(IX) WHERE nextprocess = 1 AND processid IN (8,32,45);


CREATE TABLE orders(cus INT,order_num INT)
#消除顺序读取，使用索引
#a语句不使用索引
SELECT * FROM orders WHERE(cus=104 AND order_num>1001) OR order_num=1008;
#b语句拆分，使用索引
SELECT * FROM orders WHERE cus =1004 AND order_num>1001 
UNION
SELECT * FROM  orders WHERE order_num=1008

#模糊查询where like，字母打头‘l%’会使用索引，非字母打头‘%l‘不会使用索引
CREATE TABLE t2(NAME VARCHAR(20))
CREATE INDEX ix_name ON t2(NAME)

#a语句
SELECT * FROM t2 WHERE NAME LIKE '%L%'；
SELECT * FROM t2 WHERE SUBSTRING(NAME,2,1)='L';
#b语句
SELECT * FROM t2 WHERE NAME LIKE 'L%';