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