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%';
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· DeepSeek “源神”启动!「GitHub 热点速览」
· C# 集成 DeepSeek 模型实现 AI 私有化(本地部署与 API 调用教程)
· DeepSeek R1 简明指南:架构、训练、本地部署及硬件要求
· NetPad:一个.NET开源、跨平台的C#编辑器