那些拖垮数据库的慢SQL，该如何优化？

写在前面

在日常开发中，我们有时候会遇到因为一两条执行很慢的SQL将数据库拖垮。

结合实际场景，整理了一些常见的慢SQL示例及其优化后的SQL脚本。

1. 未使用索引的查询

场景：查询某个用户的所有订单
原始SQL：

SELECT * FROM orders WHERE user_id = 123;

问题：user_id列没有索引，导致全表扫描。

优化后SQL：

-- 创建索引
CREATE INDEX idx_user_id ON orders(user_id);

-- 查询语句
SELECT * FROM orders WHERE user_id = 123;

优化点：在user_id上创建索引，提高查询效率。

2. SELECT *

场景：查询用户基本信息
原始SQL：

SELECT * FROM users WHERE id = 1;

问题：查询了所有字段，但可能只需要部分字段。

优化后SQL：

SELECT id, name, email FROM users WHERE id = 1;

优化点：只选择需要的字段，减少数据传输量。

3. WHERE子句中使用函数

场景：按日期筛选订单
原始SQL：

SELECT * FROM orders WHERE DATE(order_time) = '2023-10-01';

问题：对order_time使用了函数，导致无法使用索引。

优化后SQL：

-- 创建索引
CREATE INDEX idx_order_time ON orders(order_time);

-- 查询语句
SELECT * FROM orders WHERE order_time >= '2023-10-01 00:00:00' 
  AND order_time < '2023-10-02 00:00:00';

优化点：避免在WHERE子句中对字段进行函数操作。

4. 大量JOIN操作

场景：多表关联查询用户信息
原始SQL：

SELECT * FROM users u
JOIN orders o ON u.id = o.user_id
JOIN products p ON o.product_id = p.id
WHERE u.id = 1;

问题：JOIN操作过多，导致性能下降。

优化后SQL：

-- 只查询需要的字段
SELECT u.id, u.name, o.order_id, p.product_name 
FROM users u
JOIN orders o ON u.id = o.user_id
JOIN products p ON o.product_id = p.id
WHERE u.id = 1;

优化点：减少JOIN操作或仅选择必要字段。

5. 未限制结果集大小

场景：查询最新订单
原始SQL：

SELECT * FROM orders ORDER BY order_time DESC;

问题：未限制返回行数，可能导致大量数据返回。

优化后SQL：

SELECT * FROM orders ORDER BY order_time DESC LIMIT 10;

优化点：使用LIMIT限制结果集大小。

6. SQL中使用IN

场景：查找有订单的用户
原始SQL：

SELECT * FROM users WHERE id IN (SELECT user_id FROM orders);

问题：IN操作效率较低。

优化后SQL：

SELECT * FROM users u WHERE EXISTS (
    SELECT 1 FROM orders o WHERE o.user_id = u.id
);

优化点：使用EXISTS代替IN，提高查询效率。

7. 使用OR条件过多

场景：查询用户订单
原始SQL：

SELECT * FROM orders WHERE user_id = 1 OR user_id = 2;

问题：OR条件过多，导致索引失效。

优化后SQL：

SELECT * FROM orders WHERE user_id IN (1, 2);

优化点：使用IN代替多个OR条件。

8. 没有使用分页

场景：查询所有用户
原始SQL：

SELECT * FROM users;

问题：大数据量时返回所有结果，性能差。

优化后SQL：

SELECT * FROM users LIMIT 0, 100;

优化点：使用分页查询，减少一次性返回的数据量。

9. 使用LIKE通配符开头

场景：模糊查询用户名称
原始SQL：

SELECT * FROM users WHERE name LIKE '%Tom%';

问题：使用%开头，导致无法使用索引。

优化后SQL：

-- 如果必须使用前缀模糊查询，可以考虑使用全文索引
SELECT * FROM users WHERE name LIKE 'Tom%';

优化点：避免使用%开头，或使用全文索引。

10. 未使用覆盖索引

场景：查询订单状态
原始SQL：

SELECT status FROM orders WHERE user_id = 123;

问题：user_id上有索引，但查询字段不在索引中。

优化后SQL：

-- 创建覆盖索引
CREATE INDEX idx_user_id_status ON orders(user_id, status);

-- 查询语句
SELECT status FROM orders WHERE user_id = 123;

优化点：使用覆盖索引，避免回表查询。

以下是继续补充的 11~50条慢SQL示例，以及对应的 优化后的SQL脚本和建议，帮助你更好地理解并优化数据库性能瓶颈。

11. 未使用批量操作（插入）

原始SQL：

INSERT INTO users (name, email) VALUES ('Tom', 'tom@example.com');
INSERT INTO users (name, email) VALUES ('Jerry', 'jerry@example.com');

优化后SQL：

INSERT INTO users (name, email) VALUES
('Tom', 'tom@example.com'),
('Jerry', 'jerry@example.com');

优化点：使用批量插入减少数据库交互次数。

12. 在WHERE中使用NOT

原始SQL：

SELECT * FROM orders WHERE NOT status = 'completed';

优化后SQL：

SELECT * FROM orders WHERE status != 'completed';

优化点：避免使用NOT，改用更明确的比较操作符。

13. 没有使用连接池

原始SQL（伪代码）：

-- 每次请求都新建连接
connect();
query("SELECT * FROM users WHERE id = 1");
disconnect();

优化后SQL（伪代码）：

-- 使用连接池
pool = create_connection_pool();
conn = pool.get_connection();
query("SELECT * FROM users WHERE id = 1");

优化点：使用连接池管理数据库连接，提升性能。

14. 使用了子查询

原始SQL：

SELECT * FROM users WHERE id IN (SELECT user_id FROM orders WHERE amount > 100);

优化后SQL：

SELECT u.* 
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE o.amount > 100;

优化点：使用JOIN代替子查询，提高执行效率。

15. 全表扫描

原始SQL：

SELECT * FROM logs WHERE message LIKE '%error%';

优化后SQL：

-- 创建全文索引
CREATE FULLTEXT INDEX idx_message ON logs(message);

-- 查询
SELECT * FROM logs WHERE MATCH(message) AGAINST('error');

优化点：使用全文索引替代LIKE模糊查询。

16.ORDER BY未使用索引

原始SQL：

SELECT * FROM orders ORDER BY create_time DESC;

优化后SQL：

-- 创建索引
CREATE INDEX idx_create_time ON orders(create_time);

-- 查询
SELECT * FROM orders ORDER BY create_time DESC;

优化点：为排序字段添加索引。

17. GROUP BY未使用索引

原始SQL：

SELECT user_id, COUNT(*) FROM orders GROUP BY user_id;

优化后SQL：

-- 创建索引
CREATE INDEX idx_user_id ON orders(user_id);

-- 查询
SELECT user_id, COUNT(*) FROM orders GROUP BY user_id;

优化点：为GROUP BY字段添加索引。

18. 未使用分区表

原始SQL：

SELECT * FROM logs WHERE log_date BETWEEN '2023-01-01' AND '2023-01-31';

优化后SQL：

-- 分区表按log_date分区
CREATETABLElogs (
    idINT,
    log_date DATE
) PARTITIONBYRANGE (YEAR(log_date)) (
    PARTITION p2022 VALUESLESSTHAN (2023),
    PARTITION p2023 VALUESLESSTHAN (2024)
);

-- 查询
SELECT * FROMlogsWHERE log_date BETWEEN'2023-01-01'AND'2023-01-31';

优化点：对大表进行分区，提高查询效率。

19. 慢SQL：未使用缓存

原始SQL：

SELECT * FROM config WHERE module = 'system';

优化后SQL（伪代码）：

-- 使用Redis缓存
config = redis.get("config:system");
if not config:
    config = db.query("SELECT * FROM config WHERE module = 'system'");
    redis.set("config:system", config, ex=3600);

优化点：将高频查询结果缓存，减少数据库访问。

20. 未使用覆盖索引

原始SQL：

SELECT name FROM users WHERE age > 30;

优化后SQL：

-- 创建覆盖索引
CREATE INDEX idx_age_name ON users(age, name);

-- 查询
SELECT name FROM users WHERE age > 30;

优化点：使用覆盖索引避免回表查询。

21. 使用了不合适的JOIN类型

原始SQL：

SELECT * FROM users u LEFT JOIN orders o ON u.id = o.user_id;

优化后SQL：

-- 如果只需要有订单的用户，改用INNER JOIN
SELECT * FROM users u INNER JOIN orders o ON u.id = o.user_id;

优化点：根据业务需求选择合适的JOIN类型。

22. 未使用合适的索引类型

原始SQL：

SELECT * FROM products WHERE category_id = 10;

优化后SQL：

-- 创建B-tree索引
CREATE INDEX idx_category_id ON products(category_id);

优化点：根据查询字段选择合适的索引类型（如B-tree、哈希、全文索引等）。

23. 未使用合适的查询计划

原始SQL：

EXPLAIN SELECT * FROM orders WHERE user_id = 123;

优化后SQL：

-- 分析执行计划
EXPLAIN SELECT * FROM orders WHERE user_id = 123;

-- 根据执行计划优化索引或查询语句

优化点：使用EXPLAIN分析SQL执行计划，找出性能瓶颈。

24. 未使用合适的数据库配置

原始SQL（伪代码）：

-- 默认配置
query_cache_size = 0;

优化后SQL（MySQL配置）：

# 启用查询缓存（MySQL 5.7及以下）
query_cache_type = 1
query_cache_size = 64M

优化点：根据业务需求调整数据库配置参数。

25. 未使用合适的数据库引擎

原始SQL：

CREATE TABLE logs (id INT, content TEXT);

优化后SQL：

-- 使用InnoDB支持事务和行锁
CREATE TABLE logs (
    id INT,
    content TEXT
) ENGINE=InnoDB;

优化点：根据业务选择合适的存储引擎（如InnoDB、MyISAM等）。

26. 未使用合适的数据库版本

原始SQL：

-- MySQL 5.6
SELECT * FROM orders WHERE user_id = 123;

优化后SQL：

-- 升级到MySQL 8.0，支持更多索引优化和窗口函数
SELECT * FROM orders WHERE user_id = 123;

优化点：升级数据库版本以获得更好的性能和功能支持。

27. 未定期维护索引

原始SQL：

-- 长时间未维护
SELECT * FROM orders WHERE user_id = 123;

优化后SQL：

-- 重建索引
ALTER INDEX idx_user_id ON orders REBUILD;

-- 更新统计信息
ANALYZE TABLE orders;

优化点：定期维护索引和统计信息，保持查询效率。

28. 未使用合适的锁机制

原始SQL：

-- 未加锁导致并发问题
UPDATE orders SET status = 'paid' WHERE order_id = 1001;

优化后SQL：

-- 显式加锁
START TRANSACTION;
SELECT * FROM orders WHERE order_id = 1001 FOR UPDATE;
UPDATE orders SET status = 'paid' WHERE order_id = 1001;
COMMIT;

优化点：合理使用锁机制避免并发冲突。

29. 未使用合适的事务

原始SQL：

-- 多条语句未使用事务
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

优化后SQL：

-- 使用事务保证一致性
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

优化点：使用事务保证数据一致性。

30. 未使用合适的备份策略

原始SQL：

-- 无备份

优化后SQL（伪代码）：

# 定期备份
mysqldump -u root -p dbname > backup.sql

优化点：制定定期备份策略，保障数据安全。

以上是部分慢SQL的具体优化示例。

如果你有特定的SQL脚本需要优化，或优化建议，欢迎评论区留言。

本期内容就到这儿

希望对您有所帮助