一、查询语句本身的优化（最易落地）
这是优化查询速度的第一道关卡，很多慢查询问题根源就在 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);总结
- MySQL 查询优化是多维度的：除了索引，还需优化查询语句、表结构、配置参数，甚至架构 / 硬件；
- 核心思路：减少数据扫描量（如精简字段、用 WHERE 缩小范围）、利用缓存（内存 / Redis）、降低磁盘 IO 和资源竞争；
- 优化前先通过
EXPLAIN分析执行计划，定位瓶颈后再针对性优化（避免盲目加索引 / 改配置）。
