sql查询优化2.0------周东海

CREATE TABLE IF NOT EXISTS t(id INT,num INT DEFAULT 0,NAME VARCHAR(20));

CREATE INDEX ix_num ON t(num);

DELIMITER $
CREATE PROCEDURE t()
BEGIN
#定义一个循环变量
DECLARE i INT DEFAULT 0;
DECLARE d INT DEFAULT 0;
WHILE(i < 1000) DO
	BEGIN
		SELECT i;
		SET i = i + 1;
		SET d = i + 1;
		INSERT INTO t(id,num,NAME)VALUES(d,i,'lisi');
	END;
END WHILE;
END $
DELIMITER ;

CALL t()

DROP TABLE t

#未使用索引
SELECT id FROM t WHERE num IS NULL;
#使用索引
SELECT id FROM t WHERE num = 0;
#未使用索引不能使用<> , !=
SELECT * FROM t WHERE num != 5;
SELECT * FROM t WHERE num <> 5;
#避免使用or
SELECT id FROM t WHERE num = 10 OR num = 20;
SELECT id FROM t WHERE num = 10
UNION
SELECT id FROM t WHERE num = 20;
#尽量少用in,notin,改用between
SELECT id FROM t WHERE num IN (1,2,3);
SELECT id FROM t WHERE num BETWEEN 1 AND 3;
#模糊查询
SELECT * FROM t WHERE NAME LIKE 'li%'
#避免索引字段在where后面运算
SELECT id FROM t WHERE num/2=100;
SELECT id FROM t WHERE num = 200;
#避免索引字段进行函数操作
SELECT id FROM t WHERE SUBSTRING(NAME,1,3)='abc';
SELECT id FROM t WHERE NAME LIKE 'abc%';
#复合索引，必须使用第一个字段
CREATE TABLE IF NOT EXISTS student(id INT,NAME VARCHAR(20),
age INT,tel VARCHAR(15));
CREATE INDEX ix_id_name ON student(id,NAME);
SELECT * FROM student WHERE id = 10;
SELECT * FROM student WHERE id = 10 AND NAME ='li';