如何优化 MySQL 数据库的查询性能？

一、SQL 语句层面优化

1. 杜绝 SELECT *
   只查需要字段，减少IO与网络传输

-- 差
SELECT * FROM user;
-- 优
SELECT id,name FROM user;

2. 合理使用索引字段做条件
   where、order by、group by、join 字段优先建索引

3. 避免索引失效

• 不要对索引列做函数运算：where DATE(create_time)='2026'
• 不要隐式类型转换：字符串字段传数字
• 禁止 !=、<>、not in、is not null 全表扫描
• 左模糊 %xx 不走索引，改用全文索引

4. 分页优化
   深分页 limit 100000,10 极慢

-- 慢
select * from table limit 100000,10
-- 快（主键定位）
select * from table where id>100000 limit 10

5. 大表禁用 distinct 多字段
   改用分组去重，效率更高

6. 联表查询优化
   小表驱动大表，left join 左表小、右表大
   优先内连接 inner join 比左右连接快

二、索引优化（核心）

1. 主键优先自增INT，避免UUID做主键
2. 联合索引遵循最左前缀原则
   idx(a,b,c) 支持 a / a,b / a,b,c 查询
3. 区分度低字段不建索引（性别、状态）
4. 冗余索引及时删除
5. 高频查询、排序、分组字段必建索引
6. 超长字符串用前缀索引

三、表结构设计优化

1. 字段类型越小越好

• 手机号用 varchar 不用bigint
• 状态用 tinyint 代替int

2. 时间字段用 datetime / timestamp，少用字符串存时间
3. 大字段（文本、图片）拆表，垂直分表
4. 避免大字段常驻常用查询表
5. 合理设置 not null，默认值代替null

四、数据库配置优化（my.ini/my.cnf）

# 缓冲池（最重要，设物理内存50%-70%）
innodb_buffer_pool_size=4G
# 单次查询最大内存
sort_buffer_size=2M
join_buffer_size=2M
# 最大连接数
max_connections=500
# 慢查询日志开启，抓慢SQL
slow_query_log=1
long_query_time=1

五、架构层面优化

1. 读写分离：主库写，从库读
2. 分库分表
   • 水平分表：数据量大拆分
   • 垂直分库：业务拆分
3. 缓存拦截
   Redis 缓存热点数据，避开DB查询
4. 定时清理脏数据、归档历史数据

六、执行计划调优（必用）

explain 分析SQL执行情况

EXPLAIN SELECT * FROM user WHERE name='test';

重点看：

• type：优先 ref、range，拒绝 all 全表扫描
• key：是否用到索引
• rows：扫描行数越少越好
• Extra：避免 Using filesort 文件排序、Using temporary临时表

七、InnoDB 专属优化

1. 关闭不必要事务自动提交
2. 大事务拆分，减少锁等待
3. 批量插入用 insert values(),(),() 批量语法
4. 定期执行 optimize table 整理碎片