MySQL 常见SQL优化案例

全表扫描优化

问题描述

当查询条件没有使用索引时，MySQL会进行全表扫描，导致查询性能低下。

优化方案

添加合适的索引
优化查询条件
避免使用不必要的列

案例分析

原始查询：

sql

SELECT * FROM users WHERE age > 30;

优化前：全表扫描，执行时间长。

优化后：

sql

-- 添加索引
CREATE INDEX idx_users_age ON users(age);

-- 执行查询
SELECT * FROM users WHERE age > 30;

优化效果：使用索引扫描，执行时间显著缩短。

索引失效优化

问题描述

虽然添加了索引，但由于查询条件不当，导致索引失效。

常见索引失效场景

使用函数或表达式
类型转换
不等于条件
LIKE以%开头
OR条件没有全部使用索引
范围查询后的字段无法使用索引

案例分析

场景1：使用函数

原始查询：

sql

SELECT * FROM users WHERE DATE(create_time) = '2023-01-01';

优化后：

sql

SELECT * FROM users WHERE create_time >= '2023-01-01' AND create_time < '2023-01-02';

场景2：LIKE以%开头

原始查询：

sql

SELECT * FROM users WHERE username LIKE '%john%';

优化后：

sql

-- 使用全文索引（适用于MyISAM或InnoDB 5.6+）
ALTER TABLE users ADD FULLTEXT INDEX ft_users_username(username);
SELECT * FROM users WHERE MATCH(username) AGAINST('john');

场景3：OR条件

原始查询：

sql

SELECT * FROM users WHERE username = 'john' OR email = 'john@example.com';

优化后：

sql

-- 为两个字段分别添加索引
CREATE INDEX idx_users_username ON users(username);
CREATE INDEX idx_users_email ON users(email);

-- 或使用UNION
SELECT * FROM users WHERE username = 'john'
UNION ALL
SELECT * FROM users WHERE email = 'john@example.com';

JOIN查询优化

问题描述

JOIN查询涉及多个表，不当的JOIN顺序和缺少索引会导致性能问题。

优化方案

确保关联字段有索引
小表驱动大表
减少JOIN的表数量
使用INNER JOIN替代OUTER JOIN

案例分析

原始查询：

sql

SELECT u.*, o.* FROM users u LEFT JOIN orders o ON u.id = o.user_id WHERE u.status = 'active';

优化后：

sql

-- 确保关联字段有索引
CREATE INDEX idx_orders_user_id ON orders(user_id);

-- 优化查询，先过滤小表
SELECT u.*, o.* 
FROM (SELECT * FROM users WHERE status = 'active') u 
LEFT JOIN orders o ON u.id = o.user_id;

子查询优化

问题描述

子查询的执行效率通常较低，尤其是在大表上使用。

优化方案

使用JOIN替代子查询
使用EXISTS替代IN
将子查询结果缓存

案例分析

场景1：IN子查询

原始查询：

sql

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

优化后：

sql

SELECT u.* 
FROM users u 
INNER JOIN (SELECT DISTINCT user_id FROM orders WHERE amount > 1000) o 
ON u.id = o.user_id;

场景2：EXISTS替代IN

原始查询：

sql

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

优化后：

sql

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

ORDER BY优化

问题描述

ORDER BY操作如果无法使用索引，会导致文件排序，性能低下。

优化方案

创建合适的索引
避免在ORDER BY中使用函数
使用相同的排序方向

案例分析

原始查询：

sql

SELECT * FROM users ORDER BY create_time DESC, username ASC;

优化后：

sql

-- 创建复合索引
CREATE INDEX idx_users_create_time_username ON users(create_time DESC, username ASC);

-- 执行查询
SELECT * FROM users ORDER BY create_time DESC, username ASC;

GROUP BY优化

问题描述

GROUP BY操作需要进行排序和分组，消耗较多资源。

优化方案

创建合适的索引
使用WHERE替代HAVING
避免使用ROLLUP

案例分析

原始查询：

sql

SELECT status, COUNT(*) as count FROM users GROUP BY status HAVING count > 10;

优化后：

sql

-- 创建索引
CREATE INDEX idx_users_status ON users(status);

-- 优化查询
SELECT status, COUNT(*) as count FROM users GROUP BY status HAVING count > 10;

LIMIT查询优化

问题描述

当使用LIMIT offset, rows时，如果offset较大，会导致MySQL扫描大量数据后只返回少量结果。

优化方案

使用索引覆盖查询
使用主键排序
使用延迟关联

案例分析

原始查询：

sql

SELECT * FROM users ORDER BY create_time DESC LIMIT 100000, 10;

优化后：

sql

-- 使用延迟关联
SELECT u.* 
FROM users u 
INNER JOIN (
    SELECT id FROM users ORDER BY create_time DESC LIMIT 100000, 10
) t ON u.id = t.id;

-- 或使用主键过滤
SELECT * FROM users WHERE id > (SELECT id FROM users ORDER BY id LIMIT 100000, 1) ORDER BY id LIMIT 10;

COUNT查询优化

问题描述

COUNT(*)查询在大表上执行效率低下。

优化方案

使用COUNT(id)或COUNT(1)替代COUNT(*)
使用缓存
使用汇总表
利用索引覆盖

案例分析

场景1：统计总数

原始查询：

sql

SELECT COUNT(*) FROM users;

优化后：

sql

-- 使用主键索引
SELECT COUNT(id) FROM users;

-- 或使用汇总表
CREATE TABLE users_count (count INT);
UPDATE users_count SET count = (SELECT COUNT(*) FROM users);
SELECT count FROM users_count;

场景2：条件统计

原始查询：

sql

SELECT COUNT(*) FROM users WHERE status = 'active';

优化后：

sql

-- 添加索引
CREATE INDEX idx_users_status ON users(status);

-- 执行查询
SELECT COUNT(*) FROM users WHERE status = 'active';

NULL值处理优化

问题描述

NULL值在索引中会占用空间，且查询时需要特殊处理。

优化方案

使用默认值替代NULL
使用NOT NULL约束
优化NULL值查询

案例分析

原始查询：

sql

SELECT * FROM users WHERE email IS NULL;

优化后：

sql

-- 设置默认值
ALTER TABLE users MODIFY email VARCHAR(100) DEFAULT '' NOT NULL;

-- 查询优化
SELECT * FROM users WHERE email = '';

批量操作优化

问题描述

频繁的单条插入或更新操作会导致性能问题。

优化方案

使用批量插入
使用LOAD DATA INFILE
减少事务提交次数
使用ON DUPLICATE KEY UPDATE

案例分析

场景1：批量插入

原始操作：

sql

INSERT INTO users (username, email) VALUES ('user1', 'user1@example.com');
INSERT INTO users (username, email) VALUES ('user2', 'user2@example.com');
INSERT INTO users (username, email) VALUES ('user3', 'user3@example.com');

优化后：

sql

INSERT INTO users (username, email) VALUES 
('user1', 'user1@example.com'),
('user2', 'user2@example.com'),
('user3', 'user3@example.com');

场景2：批量更新

原始操作：

sql

UPDATE users SET status = 'inactive' WHERE id = 1;
UPDATE users SET status = 'inactive' WHERE id = 2;
UPDATE users SET status = 'inactive' WHERE id = 3;

优化后：

sql

UPDATE users SET status = 'inactive' WHERE id IN (1, 2, 3);

版本差异

MySQL 5.7 vs MySQL 8.0

优化器增强：
- MySQL 8.0引入了更多的优化策略
- 支持直方图统计信息，提高查询计划准确性
- 支持不可见索引，便于测试索引效果
索引增强：
- MySQL 8.0支持降序索引
- 支持函数索引
- 支持隐藏索引
执行计划增强：
- MySQL 8.0提供了更详细的执行计划信息
- 支持EXPLAIN ANALYZE，提供实际执行统计

生产环境最佳实践

定期分析慢查询日志：
- 开启慢查询日志
- 使用pt-query-digest分析慢查询
- 针对高频慢查询进行优化
定期优化表结构：
- 优化数据类型
- 清理冗余数据
- 重建索引
使用查询缓存：
- 对于读多写少的场景，开启查询缓存
- 注意缓存失效的影响
监控查询性能：
- 使用Performance Schema监控查询性能
- 监控索引使用情况
- 监控锁等待情况

常见问题（FAQ）

Q1: 如何确定是否需要添加索引？

A1: 可以通过以下方法确定：

分析慢查询日志，查看高频全表扫描的查询
使用EXPLAIN分析查询计划
监控索引使用情况：`SHOW INDEX FROM table_name;
考虑查询频率和数据量

Q2: 如何优化大表查询？

A2: 可以考虑以下方法：

分区表：根据时间或其他维度分区
分表：垂直分表或水平分表
数据归档：将历史数据迁移到归档表
优化查询：只查询必要的列，使用索引

Q3: 为什么有时候添加索引后性能反而下降？

A3: 可能的原因：

索引过多，导致写操作性能下降
索引选择性差，无法有效过滤数据
索引设计不合理，无法被查询使用
统计信息不准确，导致优化器选择错误的执行计划

Q4: 如何优化ORDER BY和LIMIT组合查询？

A4: 可以考虑以下方法：

创建覆盖索引，包含ORDER BY的字段
使用延迟关联，先获取主键，再关联查询
避免使用OFFSET，尽量使用主键过滤

Q5: 如何优化DELETE和UPDATE操作？

A5: 可以考虑以下方法：

批量操作，减少提交次数
使用索引，避免全表扫描
限制每次操作的数量，避免长时间锁表
在业务低峰期执行

Q6: 如何优化JOIN查询的顺序？

A6: MySQL会自动优化JOIN顺序，但可以通过以下方法手动优化：

小表驱动大表
将过滤条件提前
确保关联字段有索引
减少JOIN的表数量

Q7: 如何处理死锁问题？

A7: 可以考虑以下方法：

保持一致的锁顺序
减少事务持有锁的时间
使用较小的事务
避免长事务
监控死锁日志：`SHOW ENGINE INNODB STATUS;

Q8: 如何优化GROUP BY查询？

A8: 可以考虑以下方法：

创建合适的索引
使用WHERE替代HAVING
避免在GROUP BY中使用函数
考虑使用汇总表

Q9: 如何优化LIKE查询？

A9: 可以考虑以下方法：

避免以%开头的LIKE查询
使用全文索引
使用搜索引擎，如Elasticsearch
考虑使用前缀索引

Q10: 如何监控SQL性能？

A10: 可以使用以下工具：

MySQL Performance Schema
MySQL Sys Schema
pt-query-digest
MySQL Enterprise Monitor
Prometheus + Grafana
Zabbix

MySQL 常见SQL优化案例 ​

全表扫描优化 ​

问题描述 ​

优化方案 ​

案例分析 ​

索引失效优化 ​

问题描述 ​

常见索引失效场景 ​

案例分析 ​

JOIN查询优化 ​

问题描述 ​

优化方案 ​

案例分析 ​

子查询优化 ​

问题描述 ​

优化方案 ​

案例分析 ​

ORDER BY优化 ​

问题描述 ​

优化方案 ​

案例分析 ​

GROUP BY优化 ​

问题描述 ​

优化方案 ​

案例分析 ​

LIMIT查询优化 ​

问题描述 ​

优化方案 ​

案例分析 ​

COUNT查询优化 ​

问题描述 ​

优化方案 ​

案例分析 ​

NULL值处理优化 ​

问题描述 ​

优化方案 ​

案例分析 ​

批量操作优化 ​

问题描述 ​

优化方案 ​

案例分析 ​

版本差异 ​

MySQL 5.7 vs MySQL 8.0 ​

生产环境最佳实践 ​

常见问题（FAQ） ​

Q1: 如何确定是否需要添加索引？ ​

Q2: 如何优化大表查询？ ​

Q3: 为什么有时候添加索引后性能反而下降？ ​

Q4: 如何优化ORDER BY和LIMIT组合查询？ ​

Q5: 如何优化DELETE和UPDATE操作？ ​

Q6: 如何优化JOIN查询的顺序？ ​

Q7: 如何处理死锁问题？ ​

Q8: 如何优化GROUP BY查询？ ​

MySQL 常见SQL优化案例

全表扫描优化

问题描述

优化方案

案例分析

索引失效优化

问题描述

常见索引失效场景

案例分析

JOIN查询优化

问题描述

优化方案

案例分析

子查询优化

问题描述

优化方案

案例分析

ORDER BY优化

问题描述

优化方案

案例分析

GROUP BY优化

问题描述

优化方案

案例分析

LIMIT查询优化

问题描述

优化方案

案例分析

COUNT查询优化

问题描述

优化方案

案例分析

NULL值处理优化

问题描述

优化方案

案例分析

批量操作优化

问题描述

优化方案

案例分析

版本差异

MySQL 5.7 vs MySQL 8.0

生产环境最佳实践

常见问题（FAQ）

Q1: 如何确定是否需要添加索引？

Q2: 如何优化大表查询？

Q3: 为什么有时候添加索引后性能反而下降？

Q4: 如何优化ORDER BY和LIMIT组合查询？

Q5: 如何优化DELETE和UPDATE操作？

Q6: 如何优化JOIN查询的顺序？

Q7: 如何处理死锁问题？

Q8: 如何优化GROUP BY查询？