mysql查询优化
Mysql查询优化
🎯 一句话核心
查询优化= 让mysql少读数据+少做计算+走对索引
📊 优化前的准备工作
1.开启慢查询日志
-- 找到执行慢的SQL
SET GLOBAL slow_query_log = ON;
SET GONGLOBAL long_query_time = 1; -- 超过1秒的记录
2.使用explain分析(重中之重)
EXPLAIN SELECT * FROM users WHERE age = 18;
explian结果怎么查看：
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| 1 | SIMPLE | users | ALL | NULL | NULL | NULL | 10000| Using where|
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
- 红色警报：
type = ALL→ 全表扫描 ❌key = NULL→ 没走索引 ❌rows = 10000→ 扫描太多行 ❌Extra = Using filesort→ 文件排序 ❌
🔧 15个最实用的查询优化技巧
技巧1：只查询所需要的字段，不实用*
-- ❌ 差：查询所有字段
SELECT * FROM users WHERE age > 20;
-- ✅ 好：只查需要的字段
SELECT id, name, age FROM users WHERE age > 20;
-- 原因：减少IO、减少网络传输、可能用到覆盖索引
技巧2:避免在select中使用函数
-- ❌ 差：对索引字段使用函数
SELECT * FROM users WHERE YEAR(birthday) = 2024;
-- ✅ 好：改成范围查询
SELECT * FROM users
WHERE birthday >= '2024-01-01'
AND birthday < '2025-01-01';
-- 原因：函数会让索引失效
技巧3:避免隐式类型转换
-- ❌ 差：phone是varchar类型，传入数字
SELECT * FROM users WHERE phone = 13800138000;
-- ✅ 好：传入字符串
SELECT * FROM users WHERE phone = '13800138000';
-- 原因：类型转换会让索引失效
技巧4:like 查询%不要放前面
-- ❌ 差：%开头的LIKE
SELECT * FROM users WHERE name LIKE '%张%';
-- ✅ 好：%放后面
SELECT * FROM users WHERE name LIKE '张%';
-- ✅ 更好：使用全文索引
SELECT * FROM users WHERE MATCH(name) AGAINST('张');
-- 原因：前导%导致索引失效
技巧5:不要使用on，改用union或者in
-- ❌ 差：OR可能导致索引失效
SELECT * FROM users WHERE age = 18 OR age = 20;
-- ✅ 好：用IN
SELECT * FROM users WHERE age IN (18, 20);
-- ✅ 更好：如果OR连接不同字段，用UNION
SELECT * FROM users WHERE age = 18
UNION
SELECT * FROM users WHERE name = '张三';
-- 原因：OR可能让MySQL放弃索引
技巧6: 避免使用not in 、!=、<>
-- ❌ 差：负向查询不走索引
SELECT * FROM users WHERE age != 18;
-- ✅ 好：改成正向查询
SELECT * FROM users WHERE age < 18 OR age > 18;
-- ✅ 更好：分两次查
SELECT * FROM users WHERE age < 18
UNION
SELECT * FROM users WHERE age > 18;
技巧7:合理使用索引覆盖
-- 联合索引：idx_name_age (name, age)
-- ✅ 好：查询字段都在索引里
SELECT name, age FROM users WHERE name = '张三';
-- Extra: Using index（完全覆盖）
-- ❌ 差：需要回表查询
SELECT name, age, address FROM users WHERE name = '张三';
-- Extra: NULL（需要回表）
技巧8:分页优化(延迟关联)
-- ❌ 差：大偏移量分页
SELECT * FROM users ORDER BY id LIMIT 100000, 10;
-- ✅ 好：先查ID，再关联
SELECT * FROM users
INNER JOIN (
SELECT id FROM users
ORDER BY id
LIMIT 100000, 10
) AS tmp ON users.id = tmp.id;
-- ✅ 更好：记住上次位置（推荐）
SELECT * FROM users
WHERE id > 100000
ORDER BY id
LIMIT 10;
技巧9:优化ORDER BY
-- ❌ 差：排序字段没索引
SELECT * FROM users ORDER BY age DESC;
-- ✅ 好：排序字段建索引
ALTER TABLE users ADD INDEX idx_age(age);
-- ✅ 更好：索引字段和排序顺序一致
ALTER TABLE users ADD INDEX idx_age_desc(age DESC);
技巧10：优化GROUP BY
-- ❌ 差：临时表分组
SELECT department, COUNT(*)
FROM users
GROUP BY department;
-- ✅ 好：先过滤再分组
SELECT department, COUNT(*)
FROM users
WHERE status = 1
GROUP BY department;
-- ✅ 更好：分组字段建索引
ALTER TABLE users ADD INDEX idx_dept(department);
技巧11：避免使用DISTINCT
-- ❌ 差：DISTINCT耗时
SELECT DISTINCT department FROM users;
-- ✅ 好：用GROUP BY代替
SELECT department FROM users GROUP BY department;
-- ✅ 更好：如果只是去重，考虑缓存
技巧12：优化子查询
-- ❌ 差：相关子查询（每行执行一次）
SELECT * FROM users
WHERE age > (
SELECT AVG(age) FROM users u2
WHERE u2.department = users.department
);
-- ✅ 好：改成JOIN
SELECT u.* FROM users u
INNER JOIN (
SELECT department, AVG(age) as avg_age
FROM users
GROUP BY department
) tmp ON u.department = tmp.department
WHERE u.age > tmp.avg_age;
技巧13：优化UNION
-- ❌ 差：UNION会去重（耗时）
SELECT name FROM users_2023
UNION
SELECT name FROM users_2024;
-- ✅ 好：不需要去重用UNION ALL
SELECT name FROM users_2023
UNION ALL
SELECT name FROM users_2024;
-- 原因：UNION会做DISTINCT去重，UNION ALL直接合并
技巧14：大批量插入优化
-- ❌ 差：循环单条插入
INSERT INTO users VALUES (1, '张三');
INSERT INTO users VALUES (2, '李四');
-- ... 10000次
-- ✅ 好：批量插入
INSERT INTO users VALUES
(1, '张三'),
(2, '李四'),
-- ... 一次插入1000条
(1000, '王五');
-- ✅ 更好：事务包裹
START TRANSACTION;
INSERT INTO users VALUES ... (1000条);
INSERT INTO users VALUES ... (1000条);
COMMIT;
技巧15：COUNT优化
-- ❌ 差：COUNT(列名)会判空
SELECT COUNT(age) FROM users;
-- ✅ 好：COUNT(*)最快
SELECT COUNT(*) FROM users;
-- ❌ 差：频繁COUNT大表
SELECT COUNT(*) FROM orders;
-- ✅ 好：使用缓存或汇总表
-- Redis缓存总数，或维护统计表
📈 实战案例优化
案例1：查询2024年注册的VIP用户
-- 原始SQL（执行3秒）
SELECT * FROM users
WHERE YEAR(reg_time) = 2024
AND vip_level > 3
ORDER BY last_login DESC;
-- 优化后（0.1秒）
-- 1. 建复合索引
ALTER TABLE users ADD INDEX idx_reg_vip_login (
reg_time, vip_level, last_login
);
-- 2. 改写SQL
SELECT * FROM users
WHERE reg_time >= '2024-01-01'
AND reg_time < '2025-01-01'
AND vip_level > 3
ORDER BY last_login DESC;
案例2：分页查询商品评论
-- 原始SQL（执行5秒）
SELECT * FROM comments
WHERE product_id = 100
ORDER BY create_time DESC
LIMIT 100000, 20;
-- 优化后（0.05秒）
-- 1. 建索引
ALTER TABLE comments ADD INDEX idx_product_time (
product_id, create_time
);
-- 2. 记录上次位置
SELECT * FROM comments
WHERE product_id = 100
AND create_time < '2024-01-01 00:00:00' -- 上次最后时间
ORDER BY create_time DESC
LIMIT 20;
⚠️ 最易犯的5个错误
| 错误写法 | 问题 | 正确写法 |
|---|---|---|
WHERE name = 123 |
类型转换，索引失效 | WHERE name = '123' |
WHERE age + 1 = 20 |
索引列运算 | WHERE age = 19 |
WHERE name LIKE '%张' |
前导% | WHERE name LIKE '张%' |
WHERE age IS NULL |
索引效率低 | WHERE age = 0（设置默认值） |
ORDER BY RAND() |
全表排序 | WHERE id >= (RAND()*max_id) LIMIT 1 |
🎯 优化检查清单
执行任何查询前，问自己：
- 是否用了SELECT *？
- WHERE条件是否让索引失效？
- EXPLAIN的type是不是ALL？
- 有没有用上合适的索引？
- 分页是不是深度分页？
- 排序字段有索引吗？
- 连接查询的ON条件有索引吗？
- 子查询可以改JOIN吗？
- 数据量大是否可以缓存？
📚 一句话总结
查询优化三板斧：
- EXPLAIN看问题 → 找出全表扫描、文件排序
- 建合适的索引 → 复合索引、覆盖索引
- 改写SQL → 避免函数、类型转换、前导%
记住：优化1条烂SQL，胜过加10台服务器！ 🚀
最后，希望我写的这篇博客对大家进一步学习mysql有更深刻的体会
浙公网安备 33010602011771号