如何优化MySQL的查询性能？

一、基础优化（低成本、立竿见影）

这是优化的第一步，无需复杂操作，优先排查这些点：

1. 优化 SQL 语句本身

• 避免全表扫描：杜绝SELECT *，只查询需要的字段（减少数据传输和内存占用）；示例：

  sql

  -- 差：全表扫描+查询所有字段 SELECT * FROM user WHERE name LIKE '%张三%'; -- 优：只查需要的字段+尽量避免左模糊（若必须模糊查询，可考虑全文索引） SELECT id, name, phone FROM user WHERE name LIKE '张三%';

• 优化 WHERE 条件：避免在字段上做函数 / 运算（会导致索引失效）；示例：

  sql

  -- 差：id字段做运算，索引失效 SELECT * FROM order WHERE id + 1 = 100; -- 优：调整条件写法，利用索引 SELECT * FROM order WHERE id = 99;

• 减少子查询，改用 JOIN：子查询会创建临时表，效率低，JOIN 更高效；
• LIMIT 限制结果集：查询大量数据时（如分页），必须加LIMIT，避免一次性加载过多数据。

2. 检查并修复慢查询

• 开启 MySQL 慢查询日志：记录执行时间超过阈值（如 1 秒）的 SQL，定位性能瓶颈；开启命令（临时生效，重启失效）：

  sql

  SET GLOBAL slow_query_log = ON; -- 开启慢查询日志 SET GLOBAL long_query_time = 1; -- 执行时间超过1秒的SQL记录

• 用EXPLAIN分析 SQL 执行计划：查看是否使用索引、是否全表扫描、关联方式等；示例：

  sql

  -- 分析这条SQL的执行计划 EXPLAIN SELECT id, name FROM user WHERE age > 20;

  重点看type字段（越接近const/range越好，ALL代表全表扫描）、key字段（不为 NULL 代表使用了索引）。

二、核心优化（索引优化，性能提升关键）

索引是提升查询速度的核心，但不是越多越好，需精准设计：

1. 创建合适的索引

• 优先给 WHERE/ORDER BY/GROUP BY 字段建索引：比如查询条件中的id、age，排序的create_time；
• 选择合适的索引类型：
  • 主键索引（PRIMARY KEY）：必须建，InnoDB 默认按主键聚簇存储；
  • 普通索引（INDEX）：单字段索引，适用于简单查询；
  • 联合索引（复合索引）：多字段组合索引，需遵循 “最左前缀原则”；示例：创建idx_age_create_time (age, create_time)联合索引，能匹配age、age+create_time查询，但不匹配create_time单独查询；
  • 唯一索引（UNIQUE）：适用于唯一字段（如手机号、邮箱），既能加速查询，又能保证数据唯一性。

2. 避免索引失效的常见场景

• 字段为 NULL：索引不存储 NULL 值，尽量给字段设默认值（如空字符串）；
• 联合索引不满足最左前缀；
• 使用OR连接条件（除非所有字段都有索引）；
• 模糊查询用%xxx（左模糊），索引失效（可改用全文索引）。

3. 定期维护索引

• 删无用索引：重复索引、未使用的索引会增加插入 / 更新的开销；
• 重建碎片索引：频繁删除 / 更新数据会导致索引碎片，可通过OPTIMIZE TABLE重建；示例：

  sql

  OPTIMIZE TABLE user; -- 优化user表的索引碎片

三、进阶优化（配置 / 架构层面，适配高并发）

若基础和索引优化后仍不够，需从配置或架构层面优化：

1. MySQL 配置优化（修改 my.cnf/my.ini）

• 调整缓存参数：
  • innodb_buffer_pool_size：InnoDB 缓存池大小，建议设为物理内存的 50%-70%（核心参数，缓存表数据和索引，减少磁盘 IO）；
  • query_cache_size：查询缓存（8.0 已移除，低版本可适当设置）；
• 调整连接参数：
  • max_connections：最大连接数，避免因连接数不足导致请求排队；
  • wait_timeout：空闲连接超时时间，释放无用连接。

2. 架构层面优化

• 读写分离：主库写、从库读，分散数据库压力（适用于读多写少场景）；
• 分库分表：数据量过大（如单表千万级）时，按业务拆分（如按用户 ID 分表、按时间分表）；
• 缓存优化：用 Redis 缓存高频查询结果（如热门商品、用户信息），减少数据库查询次数；
• 使用 SSD 硬盘：大幅降低磁盘 IO 延迟，提升数据读取速度。

四、避坑提醒

1. 不要盲目加索引：索引会降低插入 / 更新 / 删除的速度，一张表索引建议不超过 5 个；
2. 小表无需优化：数据量小于 1 万行的表，全表扫描可能比索引查询更快；
3. 避免大事务：大事务会占用锁资源，导致其他查询阻塞，拆分小事务。

总结

1. 基础优化：先优化 SQL 语句（避免全表扫描、用 EXPLAIN 分析），开启慢查询日志定位瓶颈；
2. 核心优化：给查询 / 排序字段建精准索引，遵循最左前缀原则，定期维护索引；
3. 进阶优化：调整 MySQL 缓存 / 连接配置，高并发场景可做读写分离、分库分表或加缓存。