MySQL 查询优化

查询优化通过改进SQL语句结构，充分利用索引，减少资源消耗，提升查询效率。

避免 SELECT *

-- ✗ 查询所有列，浪费资源
SELECT * FROM users;

-- ✓ 只查询需要的列
SELECT id, name FROM users;

-- ✓ 使用覆盖索引
CREATE INDEX idx_name ON users(name);
SELECT name FROM users WHERE name = '张三';  -- 覆盖索引

分页优化

-- ✗ 大偏移量分页效率低
SELECT * FROM users LIMIT 10000, 10;
-- 需扫描前10010行再返回10行

-- ✓ 使用WHERE条件优化
SELECT * FROM users WHERE id > 10000 LIMIT 10;

-- ✓ 使用子查询
SELECT * FROM users
WHERE id >= (SELECT id FROM users ORDER BY id LIMIT 10000, 1)
LIMIT 10;

-- ✓ 使用JOIN
SELECT u.* FROM users u
INNER JOIN (SELECT id FROM users ORDER BY id LIMIT 10000, 10) t
ON u.id = t.id;

避免子查询

-- ✗ 子查询效率低（临时表）
SELECT * FROM users
WHERE id IN (SELECT user_id FROM orders WHERE amount > 1000);

-- ✓ 使用JOIN替代
SELECT u.* FROM users u
INNER JOIN orders o ON u.id = o.user_id
WHERE o.amount > 1000;

-- ✓ 使用EXISTS替代IN
SELECT * FROM users u
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id AND o.amount > 1000);

JOIN优化

-- ✓ 小表驱动大表
-- LEFT JOIN时左表尽量小
SELECT * FROM small_table s
LEFT JOIN large_table l ON s.id = l.id;

-- ✓ 使用索引关联字段
CREATE INDEX idx_user_id ON orders(user_id);
SELECT * FROM users u
INNER JOIN orders o ON u.id = o.user_id;

-- ✓ 减少JOIN表数量
-- 超过3个表的JOIN需拆分

-- ✓ 避免复杂JOIN条件
-- JOIN条件尽量用索引字段

ORDER BY优化

-- ✓ 利用索引排序
CREATE INDEX idx_create_time ON users(create_time);
SELECT * FROM users ORDER BY create_time;  -- Using index

-- ✗ 无索引排序效率低
SELECT * FROM users ORDER BY RAND();  -- 临时表+文件排序

-- ✓ 组合索引排序
CREATE INDEX idx_age_name ON users(age, name);
SELECT * FROM users ORDER BY age, name;  -- 使用索引

-- ✗ 顺序不一致
SELECT * FROM users ORDER BY name, age;  -- 索引失效

-- ✓ 相同排序方向
SELECT * FROM users ORDER BY age ASC, name ASC;  -- 使用索引
-- ✗ 不同排序方向
SELECT * FROM users ORDER BY age ASC, name DESC;  -- 索引失效

GROUP BY优化

-- ✓ 利用索引分组
CREATE INDEX idx_dept ON users(dept);
SELECT dept, COUNT(*) FROM users GROUP BY dept;  -- Using index

-- ✗ 无索引分组效率低
SELECT name, COUNT(*) FROM users GROUP BY name;

-- ✓ 避免临时表
-- 分组字段尽量有索引
-- 结果尽量有序

-- ✓ 使用WHERE减少分组数据量
SELECT dept, COUNT(*) FROM users
WHERE status = 'active'
GROUP BY dept;

DISTINCT优化

-- ✗ DISTINCT效率低
SELECT DISTINCT name FROM users;

-- ✓ 使用GROUP BY替代
SELECT name FROM users GROUP BY name;

-- ✓ DISTINCT配合索引
CREATE INDEX idx_name ON users(name);
SELECT DISTINCT name FROM users;  -- 使用索引

批量操作优化

-- ✗ 单条INSERT效率低
INSERT INTO users VALUES (1, '张三');
INSERT INTO users VALUES (2, '李四');
INSERT INTO users VALUES (3, '王五');

-- ✓ 批量INSERT
INSERT INTO users VALUES (1, '张三'), (2, '李四'), (3, '王五');

-- ✓ 使用LOAD DATA批量导入
LOAD DATA INFILE 'users.csv' INTO TABLE users;

-- ✓ 批量UPDATE减少事务
UPDATE users SET status = 'active' WHERE id IN (1, 2, 3);

使用LIMIT

-- ✓ LIMIT限制结果集
SELECT * FROM users WHERE name LIKE '张%' LIMIT 100;

-- ✓ 查询是否存在用LIMIT 1
SELECT 1 FROM users WHERE email = 'test@test.com' LIMIT 1;

-- ✓ 防止全表扫描
SELECT * FROM orders WHERE user_id = 100 LIMIT 10;

要点总结

• 避免 SELECT *，只查需要的列
• 大分页用 WHERE 条件优化
• 子查询用 JOIN 或 EXISTS 替代
• ORDER BY/GROUP BY 利用索引
• 批量操作合并执行
• LIMIT 限制结果集，减少资源消耗