一、查询语句本身的优化（最易落地）

这是优化查询速度的第一道关卡，很多慢查询问题根源就在 SQL 写法上：

1. 精简查询字段，避免SELECT *

• 做法：只查询业务需要的字段，而非全字段。
• 原因：减少网络传输数据量、避免回表（若索引能覆盖所需字段，可直接走索引查询）、降低内存消耗。

  sql

  -- 差的写法
  SELECT * FROM user WHERE id > 100;
  
  -- 优的写法
  SELECT id, name, phone FROM user WHERE id > 100;
  

2. 避免在 WHERE 子句中操作字段（导致索引失效）

• 做法：不要对 WHERE 后的字段做函数 / 运算处理，提前计算好条件值。
• 原因：MySQL 无法使用索引，会触发全表扫描。

  sql

  -- 差的写法（函数操作字段）
  SELECT * FROM order WHERE DATE(create_time) = '2025-12-24';
  
  -- 优的写法（条件值预处理）
  SELECT * FROM order WHERE create_time BETWEEN '2025-12-24 00:00:00' AND '2025-12-24 23:59:59';
  

3. 优化子查询，优先用 JOIN 替代

• 做法：相关子查询（依赖外部查询的子查询）效率极低，改用 JOIN 关联。
• 原因：子查询会多次执行（外部查询每一行都执行一次子查询），JOIN 只需一次关联计算。

  sql

  -- 差的写法（子查询）
  SELECT * FROM user WHERE id IN (SELECT user_id FROM order WHERE amount > 1000);
  
  -- 优的写法（JOIN）
  SELECT DISTINCT u.* FROM user u 
  JOIN `order` o ON u.id = o.user_id 
  WHERE o.amount > 1000;
  

4. 避免ORDER BY RAND()和无限制的排序

• 做法：ORDER BY RAND()会全表扫描 + 随机排序，效率极差；排序时确保排序字段有索引，且只排序必要数据。
• 替代方案：若需随机取数，可先查主键再随机筛选，减少排序数据量。

  sql

  -- 差的写法（全表随机）
  SELECT * FROM product ORDER BY RAND() LIMIT 10;
  
  -- 优的写法（先查主键范围，再随机）
  SELECT * FROM product 
  WHERE id BETWEEN 1 AND 10000 
  ORDER BY RAND() LIMIT 10;
  

5. 合理使用 LIMIT，避免全量返回

• 做法：分页查询或只取部分数据时，必须加LIMIT，且结合WHERE缩小范围。
• 原因：避免一次性返回几十万 / 百万条数据，占用内存和网络带宽。

二、表结构设计优化

从源头减少查询开销，适合表设计阶段或重构阶段：

1. 选择最优数据类型

• 核心原则：够用即可，越小的类型查询越快（占用磁盘 / 内存更少，索引更高效）。
  • 用INT/BIGINT存 ID，而非VARCHAR；
  • 用DATETIME/TIMESTAMP存时间，而非VARCHAR；
  • 枚举值用ENUM，而非VARCHAR（如性别：ENUM('男','女')）。

2. 分表优化（应对大表）

当单表数据量超过 1000 万行时，即使加索引也会变慢，需分表：

• 水平分表：按行拆分（如订单表按创建时间拆分为order_202501、order_202502）；
• 垂直分表：按列拆分（如用户表拆分为user_base（基础信息）和user_extra（扩展信息），减少单表字段数）。

3. 避免 NULL 值（尽量）

• 做法：给字段设置默认值（如字符串默认 ''，数字默认 0），而非允许NULL。
• 原因：NULL会增加索引和查询的开销（MySQL 需特殊处理 NULL 值），且聚合函数（如COUNT()）会忽略 NULL 值，易出错。

三、数据库配置优化

调整 MySQL 的核心配置参数，充分利用服务器资源：

1. 优化 InnoDB 缓存（核心）

innodb_buffer_pool_size：InnoDB 的缓存池，用于缓存数据和索引，建议设置为物理内存的 50-70%（如服务器 16G 内存，设为 10G）。

• 作用：让常用数据 / 索引常驻内存，避免频繁读磁盘（磁盘 IO 是查询慢的主要瓶颈）。

2. 调整排序 / 连接缓存

• sort_buffer_size：排序缓存，用于ORDER BY/GROUP BY，按需调整（不要过大，避免内存竞争）；
• join_buffer_size：JOIN 关联缓存，优化多表连接的效率。

3. 关闭无用的查询缓存（MySQL 8.0 已移除）

MySQL 5.7 及以下的query_cache_size：查询缓存会缓存 SQL 语句和结果，但更新频繁的表会导致缓存频繁失效，反而拖慢速度，建议关闭（设为 0）。

四、架构 / 硬件层面优化

适合中大型系统的进阶优化：

1. 读写分离

• 部署主从架构：主库负责写操作（INSERT/UPDATE/DELETE），从库负责读操作（SELECT），分摊查询压力。
• 原理：从库通过主库的 binlog 同步数据，所有查询请求路由到从库，降低主库负载。

2. 使用缓存（Redis/Memcached）

• 缓存热点查询结果（如首页推荐数据、用户基础信息），避免频繁查询数据库。
• 示例：查询用户信息时，先查 Redis，若没有再查 MySQL，查到后同步到 Redis。

3. 升级硬件

• 磁盘：用 SSD 替代机械硬盘（IO 速度提升 10 倍以上）；
• 内存：增加服务器内存，让更多数据缓存到内存；
• CPU：升级多核 CPU，提升并发查询处理能力。

五、辅助优化手段

1. 用 EXPLAIN 分析执行计划

优化前先定位瓶颈：在查询语句前加EXPLAIN，查看 MySQL 的执行计划（是否走索引、扫描行数、连接方式等）。

sql

EXPLAIN SELECT id, name FROM user WHERE phone = '13800138000';

• 重点看type列（ALL= 全表扫描，ref/range/eq_ref= 走索引，越优）；
• 看rows列：预估扫描行数，行数越少越好。

2. 定期分析表（更新统计信息）

sql

ANALYZE TABLE user;

作用：更新表的统计信息（如数据分布、行数），让 MySQL 优化器生成更优的执行计划。

3. 优化覆盖索引

创建包含查询所需所有字段的索引（无需回表查询），速度比普通索引更快：

sql

-- 查询字段：id（主键）、name、phone
-- 创建覆盖索引
CREATE INDEX idx_name_phone ON user(name, phone);

---

总结

1. MySQL 查询优化是多维度的：除了索引，还需优化查询语句、表结构、配置参数，甚至架构 / 硬件；
2. 核心思路：减少数据扫描量（如精简字段、用 WHERE 缩小范围）、利用缓存（内存 / Redis）、降低磁盘 IO 和资源竞争；
3. 优化前先通过EXPLAIN分析执行计划，定位瓶颈后再针对性优化（避免盲目加索引 / 改配置）。