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%';