Mysql优化的查询语句(1)
SQL 查询优化是一个系统性工程，除了你提到的几点，我再补充一些优化建议，帮助提升数据库查询效率：
1. 避免 SELECT *，只查询需要的字段
问题：
SELECT * FROM users WHERE id = 100;
SELECT *会获取表中所有字段，可能会增加 I/O 负担，尤其是在大表查询时。
✅ 优化：
SELECT name, email FROM users WHERE id = 100;
只查询必要的字段，减少数据传输和解析时间。
2. 使用合适的索引
问题：
SELECT * FROM orders WHERE YEAR(created_at) = 2024;
YEAR(created_at)使用了函数，导致索引失效，数据库无法直接利用created_at索引。
✅ 优化：
SELECT * FROM orders WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01';
- 这样可以让数据库利用索引，避免对每一行进行函数运算。
3. 尽量使用 EXISTS 代替 IN
问题：
SELECT * FROM users WHERE id IN (SELECT user_id FROM orders WHERE amount > 1000);
- 如果
orders结果集很大，IN会导致全表扫描，影响查询效率。
✅ 优化：
SELECT * FROM users u WHERE EXISTS (
SELECT 1 FROM orders o WHERE o.user_id = u.id AND o.amount > 1000
);
EXISTS在匹配到第一条数据时就会终止搜索，性能更优。
4. JOIN 连接表时，确保连接字段有索引
问题：
SELECT users.name, orders.amount
FROM users
JOIN orders ON users.id = orders.user_id
WHERE orders.amount > 100;
- 如果
orders.user_id没有索引，会导致全表扫描。
✅ 优化：
CREATE INDEX idx_orders_user_id ON orders(user_id);
- 确保连接字段有索引，加速
JOIN查询。
5. 使用 LIMIT 限制返回行数
问题：
SELECT * FROM logs ORDER BY created_at DESC;
- 如果
logs表有大量数据，会扫描整个表并排序。
✅ 优化：
SELECT * FROM logs ORDER BY created_at DESC LIMIT 100;
LIMIT限制返回行数，减少数据读取量。
6. 避免 OR，改用 UNION ALL
问题：
SELECT * FROM users WHERE name = 'Alice' OR email = 'alice@example.com';
OR可能导致索引失效。
✅ 优化：
SELECT * FROM users WHERE name = 'Alice'
UNION ALL
SELECT * FROM users WHERE email = 'alice@example.com';
UNION ALL在某些情况下可以提高性能，因为它避免了OR可能带来的全表扫描。
7. 避免使用 OFFSET 进行深度分页
问题：
SELECT * FROM orders ORDER BY created_at DESC LIMIT 10 OFFSET 100000;
- 数据量大时，
OFFSET会导致数据库扫描大量行，影响性能。
✅ 优化：
SELECT * FROM orders
WHERE created_at < (SELECT created_at FROM orders ORDER BY created_at DESC LIMIT 1 OFFSET 100000)
ORDER BY created_at DESC
LIMIT 10;
- 这样能减少扫描的行数，提高查询效率。
8. 使用适当的数据类型
问题：
CREATE TABLE users (
id BIGINT NOT NULL,
name VARCHAR(255),
age INT
);
VARCHAR(255)太长，如果name的数据通常在 50 个字符以内，可以使用VARCHAR(50)，减少存储开销。BIGINT可能比INT多占用 4 个字节，如果id不超过 21 亿（2^31-1），可以使用INT。
✅ 优化：
CREATE TABLE users (
id INT NOT NULL,
name VARCHAR(50),
age TINYINT UNSIGNED
);
- 使用合适的数据类型，减少存储空间，提高查询效率。
9. 避免 DISTINCT，可以使用 GROUP BY
问题：
SELECT DISTINCT category FROM products;
DISTINCT需要额外排序，可能影响性能。
✅ 优化：
SELECT category FROM products GROUP BY category;
- 在某些数据库中，
GROUP BY可能比DISTINCT更快。
10. 避免 ORDER BY RAND() 进行随机排序
问题：
SELECT * FROM users ORDER BY RAND() LIMIT 10;
RAND()需要为每一行生成随机数并排序，效率很低。
✅ 优化：
SELECT * FROM users WHERE id >= (SELECT FLOOR(RAND() * (SELECT MAX(id) FROM users))) LIMIT 10;
- 这样能快速获取随机记录，而不影响索引。
11. 使用 EXPLAIN 检查查询计划
在优化 SQL 语句时，可以使用 EXPLAIN 来分析查询是否使用了索引：
EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';
如果 type 列显示 ALL，表示全表扫描，说明索引没有生效，需要优化。
总结
✅ 优化查询的关键点：
- 避免
SELECT *，只查询需要的字段。 - 在
WHERE条件和JOIN连接字段上建立索引。 - 避免
LIKE '%XX%'，可以用全文索引。 - 避免自动类型转换，确保
WHERE条件的类型匹配。 - 使用
EXISTS代替IN，避免大数据量子查询的低效问题。 - 深度分页时，使用
WHERE过滤来减少OFFSET造成的性能损失。 - 尽量使用
UNION ALL代替OR，提高查询效率。 - 优化数据类型，减少存储空间，提高索引效率。
- 用
GROUP BY替代DISTINCT，避免额外排序。 - 避免
ORDER BY RAND()，改用随机 ID 选择方法。 - 使用
EXPLAIN分析查询计划，确保索引生效。
做好这些优化，可以极大提高 SQL 查询的执行效率，减少数据库负载。🚀