Mysql查询优化

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

posted @   N暖阳_李维宁  阅读(30)  评论(0编辑  收藏  举报
相关博文:
阅读排行:
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· DeepSeek “源神”启动!「GitHub 热点速览」
· C# 集成 DeepSeek 模型实现 AI 私有化(本地部署与 API 调用教程)
· DeepSeek R1 简明指南:架构、训练、本地部署及硬件要求
· NetPad:一个.NET开源、跨平台的C#编辑器
点击右上角即可分享
微信分享提示