MySQL查询优化

USE test;
 
CREATE TABLE IF NOT EXISTS emp
(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20),
age INT,
salary FLOAT
);
创建存储过程
DELIMITER $
CREATE PROCEDURE emp()
 
BEGIN
定义一个循环变量
DECLARE i INT DEFAULT 0;
WHILE(i<1000) DO
BEGIN
SELECT i;
SET i=i+1;
INSERT INTO emp(age) VALUES (i);
END;
END WHILE;
END $;
DELIMITER;
调用存储过程
CALL emp();
SELECT * FROM emp;
避免的情况 !=,<>,is null,is 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);
合理使用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);
between,in
SELECT * FROM emp WHERE age BETWEEN 100 AND 200;
 
SELECT * FROM emp WHERE age IN(10,66,88);
distinct,group by
SELECT DISTINCT NAME FROM emp;
SELECT NAME FROM emp GROUP BY NAME;
 
USE test;
创建表
CREATE TABLE IF NOT EXISTS T1(processid INT,nextprocess INT);
创建索引
CREATE INDEX IX_ProcessID ON T1(processid);
a语句不使用索引
SELECT * FROM T1 WHERE nextprocess = 1 AND processid IN (8,32,45);
b语句强制使用索引
SELECT * FROM T1 FORCE INDEX(IX_ProcessID) WHERE nextprocess = 1 AND processid IN (8,32,45);
 
CREATE TABLE IF NOT EXISTS orders(customer_num INT,order_num INT);
CREATE INDEX i_order_num ON orders(order_num);
消除顺序读取,使用索引
a语句不使用索引
SELECT * FROM orders WHERE (customer =104 AND order_num > 1001) OR order_num = 1008;
b语句拆分,使用索引
SELECT * FROM orders WHERE (customer_num = 104) AND order_num > 1001;
UNION
SELECT * FROM orders WHERE order_num = 1008;
模糊查询where like,字母大头'1%'会使用索引,非字母大头'%1%'不会使用索引
CREATE TABLE IF NOT EXISTS 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%';