mysql 数据库-sql调优
一、基础入门篇：掌握调优核心原理
1.1 索引优化基础
黄金法则：80%的性能问题可通过索引优化解决
-- 创建复合索引的正确姿势
CREATE INDEX idx_user_profile
ON users(last_name, age, status)
COMMENT '覆盖最常用查询条件';
-- 查看索引使用情况
SHOW INDEX FROM users;三大索引原则：
- 最左前缀原则（电梯理论：找3楼必须先经过1-2楼）
- 避免冗余索引（每个索引都是存储成本）
- 选择性原则（区分度高的字段在前）
1.2 基础查询优化技巧
-- 反面教材
SELECT * FROM orders
WHERE YEAR(create_time) = 2023;
-- 优化方案
SELECT order_id, customer_name
FROM orders
WHERE create_time BETWEEN '2023-01-01' AND '2023-12-31';常见优化场景：
- 避免在WHERE子句使用函数操作（导致索引失效）
- 用BETWEEN替代多个OR条件
- LIMIT分页优化（避免OFFSET过大）
二、执行计划深度解析
2.1 EXPLAIN命令详解
EXPLAIN FORMAT=JSON
SELECT u.name, o.total
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.city = '上海'
AND o.status = 'COMPLETED';关键指标解读：
字段 | 优化指标 | 理想值 |
type | 访问类型 | const/ref/range |
rows | 预估扫描行数 | 越小越好 |
Extra | 附加信息 | Using index |
filtered | 条件过滤百分比 | 接近100% |
2.2 JOIN优化策略
三种高性能JOIN方式：
- 索引嵌套循环：确保关联字段有索引
- 批量Key访问：调整join_buffer_size
- 哈希连接：适用于无索引大表关联
-- 强制使用索引关联
SELECT /*+ INDEX(o idx_user_id) */
u.name, o.total
FROM users u
FORCE INDEX(PRIMARY)
JOIN orders o
ON u.id = o.user_id;
三、系统级优化方案
3.1 配置参数调优
# my.cnf 核心参数配置
[mysqld]
innodb_buffer_pool_size = 80%物理内存 # 数据缓存池
innodb_log_file_size = 4G # 事务日志大小
max_connections = 1000 # 最大连接数
thread_cache_size = 100 # 线程缓存 参数调整验证方法：
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';
-- 计算命中率应>99%
命中率 = (1 - Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests) * 100
3.2 慢查询日志分析
全流程诊断方法：
- 开启慢日志记录
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1; # 超过1秒记录 - 使用pt-query-digest分析
pt-query-digest /var/lib/mysql/slow.log - 解读Top SQL报告
四、专家级技巧：全链路优化实践
4.1 分布式架构下的优化
读写分离架构：
graph TD
A[应用层] --> B[读写分离中间件]
B --> C[Master写节点]
B --> D[Slave读节点1]
B --> E[Slave读节点2]分库分表策略：
- 垂直拆分：按业务模块拆分（用户库、订单库）
- 水平拆分：按哈希或范围分片
4.2 高级索引策略
索引合并优化：
-- 启用索引合并
SET optimizer_switch = 'index_merge=on';
-- 查看合并情况
EXPLAIN
SELECT * FROM users
WHERE phone = '13800138000'
OR email = 'admin@example.com'; 虚拟列索引（MySQL 8.0+）：
ALTER TABLE products
ADD COLUMN name_price VARCHAR(100)
GENERATED ALWAYS AS (CONCAT(name, '-', price));
CREATE INDEX idx_comp ON products(name_price);五、调优工具箱推荐
工具名称 | 用途 | 使用场景 |
Percona Toolkit | 专业诊断工具集 | 死锁分析/表结构优化 |
mysqldumpslow | 慢查询日志分析 | 快速定位问题SQL |
Performance Schema | 实时性能监控 | 深度分析服务器状态 |
pt-visual-explain | 可视化执行计划 | 团队演示与方案评审 |
调优检查清单：
- 所有查询都使用索引覆盖
- 没有出现全表扫描（type=ALL）
- JOIN操作使用最佳关联方式
- 事务隔离级别设置合理（通常RR/RC）
- 定期进行索引碎片整理
通过这三个层次的优化，可使MySQL性能提升5-10倍。记住：调优是持续过程，需结合监控系统定期review，随着数据增长动态调整方案。