【面试题】MySQL 中如何进行 SQL 调优？
MySQL SQL 调优：从"龟速"到"光速"的秘籍 🚀
一、调优心法：先诊断，再开药 💊
调优金字塔（从最有效到最复杂）：
┌─────────┐
│ 业务层 │ ← 80%的问题在这层解决
│ SQL层 │ ← 15%的问题在这层解决
│ 架构层 │ ← 4%的问题在这层解决
│ 服务器层 │ ← 1%的问题在这层解决
└─────────┘
二、诊断工具：MySQL的"听诊器" 🩺
1. 慢查询日志 - 抓"罪犯"SQL
-- 查看慢查询配置
SHOW VARIABLES LIKE '%slow%';
SHOW VARIABLES LIKE 'long_query_time';
-- 开启慢查询日志（临时）
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1; -- 超过1秒的查询
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';
-- 分析慢查询日志
mysqldumpslow -s t /var/log/mysql/slow.log -- 按时间排序
pt-query-digest /var/log/mysql/slow.log -- Percona工具，更详细
2. EXPLAIN - SQL的"X光"
-- 基本用法
EXPLAIN SELECT * FROM users WHERE age > 25;
-- 详细格式（MySQL 8.0+）
EXPLAIN FORMAT=JSON SELECT * FROM users WHERE age > 25;
EXPLAIN ANALYZE SELECT * FROM users WHERE age > 25; -- 实际执行并分析
-- 查看执行计划中的警告
EXPLAIN EXTENDED SELECT * FROM users WHERE age > 25;
SHOW WARNINGS;
3. 性能监控 - 实时"心电图"
-- 查看当前进程
SHOW PROCESSLIST;
SHOW FULL PROCESSLIST; -- 显示完整SQL
-- 查看锁信息
SELECT * FROM information_schema.INNODB_LOCKS;
SELECT * FROM information_schema.INNODB_LOCK_WAITS;
-- 查看性能指标
SHOW STATUS LIKE '%Handler%';
SHOW STATUS LIKE '%Innodb%';
-- 查看表状态
SHOW TABLE STATUS LIKE 'users';
三、索引优化：给查询装"火箭推进器" 🚀
1. 索引创建黄金法则
-- ✅ 正确创建索引
-- 1. 为WHERE条件创建索引
CREATE INDEX idx_age ON users(age);
CREATE INDEX idx_status ON orders(status);
-- 2. 为JOIN条件创建索引
CREATE INDEX idx_user_id ON orders(user_id);
-- 3. 为ORDER BY创建索引
CREATE INDEX idx_created_at ON logs(created_at);
-- 4. 复合索引（注意顺序！）
-- 原则：最左前缀 + 区分度高在前 + 等值在前，范围在后
CREATE INDEX idx_status_created ON orders(status, created_at);
2. 索引失效的"七宗罪" ❌
-- 1. 索引列参与计算
SELECT * FROM users WHERE YEAR(created_at) = 2023; -- ❌
SELECT * FROM users WHERE created_at >= '2023-01-01'; -- ✅
-- 2. 使用函数
SELECT * FROM users WHERE UPPER(name) = 'JOHN'; -- ❌
SELECT * FROM users WHERE name = 'JOHN'; -- ✅
-- 3. 类型转换
SELECT * FROM users WHERE phone = 13800138000; -- ❌ phone是varchar
SELECT * FROM users WHERE phone = '13800138000'; -- ✅
-- 4. LIKE以%开头
SELECT * FROM users WHERE name LIKE '%张%'; -- ❌
SELECT * FROM users WHERE name LIKE '张%'; -- ✅
-- 5. OR条件（除非每个条件都有索引）
SELECT * FROM users WHERE age = 20 OR name = '张三'; -- ❌
-- 6. 使用NOT、!=、<>
SELECT * FROM users WHERE age != 20; -- ❌
-- 7. 复合索引跳过最左列
-- 有索引 idx_age_name(age, name)
SELECT * FROM users WHERE name = '张三'; -- ❌ 跳过了age
SELECT * FROM users WHERE age = 20 AND name = '张三'; -- ✅
3. 覆盖索引：查询加速的"魔法" ✨
-- ❌ 需要回表
SELECT * FROM users WHERE age > 20; -- 即使age有索引，也要读整行
-- ✅ 覆盖索引：索引包含所有查询字段
CREATE INDEX idx_age_name_email ON users(age, name, email);
SELECT name, email FROM users WHERE age > 20; -- 直接从索引取数据
四、SQL写法优化：从"拖拉机"到"跑车" 🏎️
1. SELECT优化
-- ❌ 糟糕的写法
SELECT * FROM users;
-- ✅ 优化写法
SELECT id, name, email FROM users; -- 只取需要的列
-- ❌ 使用子查询（可能效率低）
SELECT * FROM users
WHERE id IN (SELECT user_id FROM orders WHERE amount > 1000);
-- ✅ 使用JOIN
SELECT u.* FROM users u
JOIN orders o ON u.id = o.user_id
WHERE o.amount > 1000;
-- ✅ 使用EXISTS（适合"存在性"检查）
SELECT * FROM users u
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);
2. JOIN优化
-- ❌ 笛卡尔积
SELECT * FROM users, orders; -- 产生大量中间结果
-- ✅ 明确JOIN条件
SELECT * FROM users JOIN orders ON users.id = orders.user_id;
-- ❌ 大表JOIN大表
SELECT * FROM huge_table1 JOIN huge_table2 ON ...;
-- ✅ 小表驱动大表（一般让结果集小的表做驱动表）
SELECT * FROM small_table s
JOIN large_table l ON s.id = l.small_id;
-- 使用STRAIGHT_JOIN强制连接顺序（慎用！）
SELECT STRAIGHT_JOIN * FROM users u JOIN orders o ON u.id = o.user_id;
3. 分页优化
-- ❌ 传统分页（偏移量大时极慢）
SELECT * FROM users ORDER BY id LIMIT 1000000, 20;
-- ✅ 优化方案1：使用主键
SELECT * FROM users
WHERE id > 上次查询的最大ID
ORDER BY id LIMIT 20;
-- ✅ 优化方案2：子查询（覆盖索引）
SELECT * FROM users
WHERE id >= (SELECT id FROM users ORDER BY id LIMIT 1000000, 1)
LIMIT 20;
-- ✅ 优化方案3：JOIN分页
SELECT u.* FROM users u
JOIN (SELECT id FROM users ORDER BY id LIMIT 1000000, 20) AS tmp
ON u.id = tmp.id;
4. GROUP BY优化
-- ❌ 没有索引的GROUP BY可能产生临时表
SELECT age, COUNT(*) FROM users GROUP BY age;
-- ✅ 为GROUP BY字段创建索引
CREATE INDEX idx_age ON users(age);
SELECT age, COUNT(*) FROM users GROUP BY age;
-- ✅ 使用SQL_BIG_RESULT提示
SELECT SQL_BIG_RESULT age, COUNT(*) FROM users GROUP BY age;
5. UNION优化
-- ❌ UNION会去重（有开销）
SELECT id FROM table1
UNION
SELECT id FROM table2;
-- ✅ UNION ALL不去重（更快）
SELECT id FROM table1
UNION ALL
SELECT id FROM table2;
-- 确实需要去重时，考虑在应用层做
五、数据库设计优化：地基要打牢 🏗️
1. 数据类型选择
-- ❌ 浪费空间
CREATE TABLE users (
id BIGINT, -- 其实INT足够
age TINYINT UNSIGNED -- 0-255足够了
ip VARCHAR(15) -- 用INT UNSIGNED存储更快
);
-- ✅ 合适的数据类型
CREATE TABLE users (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
age TINYINT UNSIGNED,
ip INT UNSIGNED, -- INET_ATON()转换
status ENUM('active', 'inactive') -- 枚举比VARCHAR快
);
2. 范式与反范式
-- 第三范式（减少冗余）
CREATE TABLE users (
id INT PRIMARY KEY,
name VARCHAR(50),
dept_id INT
);
CREATE TABLE departments (
id INT PRIMARY KEY,
name VARCHAR(50)
);
-- 适当反范式（提高查询性能）
CREATE TABLE orders (
id INT PRIMARY KEY,
user_id INT,
user_name VARCHAR(50), -- 冗余字段，避免JOIN
amount DECIMAL(10,2)
);
3. 分区表
-- 按时间分区（适合日志表）
CREATE TABLE logs (
id INT NOT NULL,
log_date DATE NOT NULL
) PARTITION BY RANGE (YEAR(log_date)) (
PARTITION p0 VALUES LESS THAN (2020),
PARTITION p1 VALUES LESS THAN (2021),
PARTITION p2 VALUES LESS THAN (2022),
PARTITION p3 VALUES LESS THAN (2023)
);
-- 查询时指定分区（提高性能）
SELECT * FROM logs PARTITION (p2);
六、服务器参数调优：给引擎"加氮气" 🔧
关键参数配置（my.cnf）
[mysqld]
# InnoDB缓冲池（重要！设置为物理内存的70-80%）
innodb_buffer_pool_size = 8G
# 日志文件大小
innodb_log_file_size = 256M
innodb_log_buffer_size = 16M
# 连接相关
max_connections = 500
thread_cache_size = 50
table_open_cache = 2000
# 查询缓存（MySQL 8.0已移除）
# query_cache_type = 0
# 临时表设置
tmp_table_size = 64M
max_heap_table_size = 64M
# 排序和连接设置
sort_buffer_size = 4M
join_buffer_size = 4M
read_buffer_size = 2M
read_rnd_buffer_size = 4M
# InnoDB刷新策略
innodb_flush_log_at_trx_commit = 2 # 性能优先（可能丢1秒数据）
# innodb_flush_log_at_trx_commit = 1 # 安全优先（默认）
七、实战案例：完整调优流程 📋
案例：电商订单查询慢
-- 原始慢SQL
SELECT * FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.status = 'PAID'
AND o.created_at BETWEEN '2023-01-01' AND '2023-12-31'
AND u.city = '北京'
ORDER BY o.total_amount DESC
LIMIT 0, 20;
-- 步骤1：EXPLAIN分析
EXPLAIN SELECT ...;
-- 步骤2：发现缺少索引
-- 创建索引
CREATE INDEX idx_status_created ON orders(status, created_at);
CREATE INDEX idx_city ON users(city);
CREATE INDEX idx_user_id ON orders(user_id);
-- 步骤3：改写SQL
SELECT o.* FROM orders o
WHERE o.status = 'PAID'
AND o.created_at BETWEEN '2023-01-01' AND '2023-12-31'
AND EXISTS (
SELECT 1 FROM users u
WHERE u.id = o.user_id AND u.city = '北京'
)
ORDER BY o.total_amount DESC
LIMIT 0, 20;
-- 步骤4：考虑物化视图（汇总表）
CREATE TABLE order_summary (
date DATE,
city VARCHAR(50),
status VARCHAR(20),
total_amount DECIMAL(10,2),
order_count INT,
PRIMARY KEY (date, city, status)
);
八、调优工具箱 🧰
1. Percona Toolkit
# 分析查询
pt-query-digest slow.log
# 在线修改表结构
pt-online-schema-change --alter "ADD INDEX idx_name (name)" D=database,t=table
# 查看索引使用情况
pt-index-usage slow.log
2. sys库（MySQL 5.7+）
-- 查看未使用索引
SELECT * FROM sys.schema_unused_indexes;
-- 查看冗余索引
SELECT * FROM sys.schema_redundant_indexes;
-- 查看等待事件
SELECT * FROM sys.innodb_lock_waits;
-- 查看内存使用
SELECT * FROM sys.memory_global_by_current_bytes;
3. 监控脚本
-- 查看当前问题SQL
SELECT * FROM information_schema.processlist
WHERE COMMAND != 'Sleep'
AND TIME > 10
ORDER BY TIME DESC;
-- 查看索引统计
SELECT TABLE_NAME, INDEX_NAME, ROWS_READ
FROM sys.schema_index_statistics
WHERE TABLE_SCHEMA = 'your_db';
九、调优检查清单 ✅
快速自检：
十、一句话调优口诀 📝
"先看计划再优化，索引要用覆盖法，查询避免全表扫，分批处理大分页，连接小表驱动好，数据类型要最小，参数调优看瓶颈，监控报警不能少"
记住：调优是迭代过程，没有一劳永逸的方案。随着数据增长和业务变化，需要持续监控和优化！🔄
❤️ 如果你喜欢这篇文章，请点赞支持！ 👍 同时欢迎关注我的博客，获取更多精彩内容！
本文来自博客园，作者：佛祖让我来巡山，转载请注明原文链接：https://www.cnblogs.com/sun-10387834/p/19453020
浙公网安备 33010602011771号