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;
#模糊查询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%';