SQL 优化是从慢查询定位 → 索引优化 → 语句改写 → 配置调优的完整流程，我把最实用、最高频的优化方法整理好了，直接照着做就能大幅提升查询速度。
一、先定位：找到慢SQL（第一步必须做）
不定位问题就优化=瞎改，先开启慢查询日志：
-- 临时开启（重启失效）
set global slow_query_log = 1;
set global slow_query_log_file = '/var/lib/mysql/slow.log';
set global long_query_time = 1; -- 超过1秒就算慢查询
set global log_queries_not_using_indexes = 1; -- 记录未走索引的SQL
工具分析：
mysqldumpslow slow.log直接看最慢SQL- 或用
pt-query-digest（更清晰）
二、核心优化：索引优化（90%慢查询根源）
1. 最关键：建立正确索引
高频索引规则（背下来）
- where 条件列 必须建索引
- join 关联列 必须建索引
- order by / group by 列 建议建索引
- 联合索引遵循最左前缀原则
- 索引不要乱建：单表索引不超过5个
✅ 推荐写法：
-- 联合索引（最常用）
create index idx_user_age_status on user(age, status);
2. 绝对避免：索引失效场景（重点！）
这些写法一定会让索引失效，必须改：
- 列上运算/函数：
where age+1=18❌ - 隐式类型转换：
where id='123'（id是int）❌ - like 以 % 开头：
where name like '%张三'❌ - 使用 != / not in / is not null（有时会失效）
- 联合索引不满足最左前缀
✅ 正确写法：
where age = 17 -- 索引生效
where name like '张三%' -- 索引生效
where id = 123 -- 类型匹配
3. 查看SQL是否走索引
explain select * from user where age=18;
看关键字段：
- type：range / ref / eq_ref 最好
- type：ALL 全表扫描=必须优化
- key：实际使用的索引
- rows：扫描行数越小越好
三、SQL语句本身优化（简单改法，速度飞涨）
1. 禁止使用 select *
select * from user; -- 垃圾
select id,name,age from user; -- 优秀
减少IO、避免回表、无法使用覆盖索引。
2. 小表驱动大表（join优化）
-- 优秀：小表 user 驱动大表 order
select o.* from user u
inner join order o on u.id=o.user_id
where u.age=18;
inner join 自动优化，left join 必须小表在左。
3. 优化 in / exists
- 子查询结果少：用
in - 子查询结果多：用
exists
-- 数据量小时
select * from order where user_id in (select id from user where age=18);
-- 数据量大时
select * from order o
where exists (select 1 from user u where u.id=o.user_id and u.age=18);
4. 避免排序消耗
- 能用
order by 索引列就不要用文件排序 - 尽量避免
distinct、group by无索引
5. 分页优化（limit深分页极慢）
-- 慢！
select * from user limit 100000,20;
-- 快！（延迟关联）
select u.* from user u
inner join (select id from user limit 100000,20) t on u.id=t.id;
四、表结构优化（从根源提速）
- 字段类型尽可能小
- 能用 tinyint 不用 int
- 能用 varchar(20) 不用 varchar(255)
- 允许为 NULL 的列，尽量设默认值 NULL 查询效率低、索引效率差
- 大字段拆分 text/blob 单独建表，不要放主表
- 适度反范式 减少 join，允许少量冗余（提升查询）
五、MySQL 配置优化（服务器级）
修改 my.cnf / my.ini，重启生效：
[mysqld]
# 连接数
max_connections = 1000
# 缓冲池（关键！设为物理内存的 50%~70%）
innodb_buffer_pool_size = 16G
# 日志文件
innodb_log_file_size = 512M
innodb_flush_log_at_trx_commit = 2
# 临时表
tmp_table_size = 256M
max_heap_table_size = 256M
六、最快见效的优化清单（直接照抄）
- 用
explain查看所有查询是否走索引 - 给
where / join / order by字段建索引 - 禁止
select * - 禁止
%xxx模糊查询 - 禁止列上运算/函数
- 深分页用延迟关联
- 小表驱动大表
- 关闭不必要的查询缓存（MySQL 8.0已废弃）
总结
- 90% 的慢查询都是索引问题，先看 explain
- 遵循最左前缀、避免索引失效、小表驱动大表
- 配置重点调
innodb_buffer_pool_size - 先定位慢查询，再针对性优化，不要盲目改
