您现在的位置是：首页 >技术教程 >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，表示全表扫描，说明索引没有生效，需要优化。

总结

✅ 优化查询的关键点：

1. 避免 SELECT *，只查询需要的字段。
2. 在 WHERE 条件和 JOIN 连接字段上建立索引。
3. 避免 LIKE '%XX%'，可以用全文索引。
4. 避免自动类型转换，确保 WHERE 条件的类型匹配。
5. 使用 EXISTS 代替 IN，避免大数据量子查询的低效问题。
6. 深度分页时，使用 WHERE 过滤来减少 OFFSET 造成的性能损失。
7. 尽量使用 UNION ALL 代替 OR，提高查询效率。
8. 优化数据类型，减少存储空间，提高索引效率。
9. 用 GROUP BY 替代 DISTINCT，避免额外排序。
10. 避免 ORDER BY RAND()，改用随机 ID 选择方法。
11. 使用 EXPLAIN 分析查询计划，确保索引生效。

做好这些优化，可以极大提高 SQL 查询的执行效率，减少数据库负载。🚀