常见的慢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的具体优化示例。