一、分页查询优化

1. 自增连续主键排序的分页优化

示例：基础分页写法：select * from employees limit 90000,5;该 SQL 表示从第 90001 行开始取 5 行数据，默认按主键排序。

优化：当主键自增且连续时，可通过主键范围查询替代limit offset, rows，避免数据库扫描前 90000 行数据。优化后 SQL：select * from employees where id > 90000 limit 5;

注意事项：

• 主键自增但不连续（如批量插入回滚、手动指定主键） 也会导致结果不一致；
• 若分页 SQL 包含order by 非主键字段，该优化方式不适用，会导致排序结果错乱；

总结：适用条件：① 主键自增且连续 ② 结果按主键排序。

2. 非主键字段排序的分页优化

示例：基础写法：select * from employees ORDER BY name limit 90000,5;该 SQL 因name索引扫描 + 回表成本高于全表扫描，优化器放弃使用索引，触发filesort。

优化：拆分排序分页和数据查询：先通过索引获取分页后的主键，再通过主键回表查全量数据，减少排序阶段的数据加载量。优化后 SQL：

mysql

select * from employees e 
inner join (select id from employees order by name limit 90000,5) ed 
on e.id = ed.id;

补充：

• 确保name字段有索引（如idx_employees_name），否则子查询仍会全表扫描；
• 若分页偏移量极大（如limit 1000000, 10），可结合业务做 "游标分页"（记录上一页最后一条的name和id，如where name > 'xxx' and id > xxx limit 10），彻底避免大偏移量扫描。

二、表关联查询优化

MySQL 表关联核心遵循小表驱动大表原则，底层依赖两种核心算法：

1. 嵌套循环连接（Nested-Loop Join, NLJ）

原理：一次一行循环从驱动表取数据，通过关联字段（带索引）查询被驱动表，合并结果。

执行流程：

1. 驱动表（小表）全扫描，取一行数据；
2. 用关联字段查被驱动表（大表）的索引；
3. 合并结果，重复上述步骤。

执行特征

• EXPLAIN 结果中Extra无Using join buffer；
• 驱动表选择：INNER JOIN 时优化器选小表，LEFT JOIN 左表为驱动表，RIGHT JOIN 右表为驱动表。

2. 基于块的嵌套循环连接（Block Nested-Loop Join, BNL）

原理：驱动表数据载入join_buffer，被驱动表全扫描，逐行与join_buffer数据对比。

执行流程：

1. 驱动表数据批量载入join_buffer（默认 256k，可通过join_buffer_size调整）；
2. 被驱动表全扫描，每行与join_buffer中数据匹配；
3. 若join_buffer不足，分段载入驱动表数据，重复扫描被驱动表。

执行特征

• EXPLAIN 结果中Extra显示Using join buffer (Block Nested Loop)；
• 适用于被驱动表关联字段无索引的场景。

关联查询优化准则：

1. 核心优化：被驱动表关联字段加索引，优先触发 NLJ 算法（磁盘 IO 远低于 BNL）；
2. 驱动表选择：过滤后数据量小的表作为驱动表（"小表" 指查询后的数据量，非原表行数）；
3. 强制驱动表：使用straight_join固定驱动表（仅适用于 INNER JOIN），如select * from t2 straight_join t1 on t2.a = t1.a;，但需谨慎（优化器多数情况下更智能）；
4. join_buffer 调优：若必须用 BNL，可适当调大join_buffer_size（如 1M），减少分段次数，但避免全局调大（每个连接独立分配）。

三、IN 与 EXISTS 优化

核心原则：小表驱动大表

语法	适用场景	执行逻辑
IN	B 表数据量 < A 表	select * from A where id in (select id from B) 等价于：遍历 B 表，用 B.id 查 A 表
EXISTS	A 表数据量 < B 表	select * from A where exists (select 1 from B where B.id = A.id) 等价于：遍历 A 表，用 A.id 查 B 表

补充：

• EXISTS 子查询中SELECT *等价于SELECT 1（优化器忽略 SELECT 清单）；
• 复杂场景下，IN/EXISTS 可替换为 JOIN，需实测对比（如SELECT DISTINCT A.* FROM A JOIN B ON A.id = B.id）；
• MySQL8.0 对 IN 子查询做了优化，部分场景下 IN 和 EXISTS 性能差异缩小。

四、COUNT (*) 查询优化

1. COUNT 语法差异

语法	统计规则	性能（字段有索引时）	性能（字段无索引时）
COUNT(*)	统计所有行（含 NULL）	最优（MySQL5.6 + 优化，仅计数不取值）	最优
COUNT(1)	统计所有行（含 NULL）	略逊于 COUNT (*)	略逊于 COUNT (*)
COUNT (主键 id)	统计主键非 NULL 行	差于 COUNT (字段)	优于 COUNT (字段)
COUNT (字段)	统计字段非 NULL 行	优于 COUNT (主键 id)	最差（全表扫描）

补充：COUNT (*) 是 MySQL 官方推荐写法，一般不用替换为 COUNT (1) 或 COUNT (主键)。

2. COUNT 优化方案

方案	适用引擎	优点	缺点
依赖 MyISAM 总行数	MyISAM	无需计算，性能极高	仅无 WHERE 时可用；INNODB 不支持
SHOW TABLE STATUS	INNODB	性能高，毫秒级返回	结果为估计值（误差 10% 左右）
Redis 维护计数	所有引擎	查询极快	难保证事务一致性（表操作与 Redis 操作原子性）
数据库计数表	所有引擎	数据准确	维护成本高（需在事务中同步更新）

五、Order by 与 Group by 优化

1. Order by 核心问题：using Filesort

触发原因

• 排序字段无索引；
• 索引排序方向与 ORDER BY 方向不一致（MySQL8.0 前无降序索引）；
• 索引包含的字段不足，需回表补充数据后排序。

优化示例（MySQL8.0 降序索引）

-- 创建降序索引
CREATE INDEX idx_position_desc ON employees(position DESC);
-- 优化后SQL（避免Filesort）
SELECT * FROM employees ORDER BY position DESC LIMIT 10;

2. Group by 优化

• 与 Order by 原理一致，优先使用索引减少排序；
• 避免SELECT * FROM table GROUP BY non_index_field，会触发临时表 + Filesort；
• 优化方式：先通过索引获取分组字段 + 主键，再回表查询（同分页优化思路）。

3. Filesort 排序方式

MySQL8.0 中max_length_for_sort_data默认 4096 字节：

• 若排序行大小 ≤ 4096 字节：使用 "快速排序"（内存排序）；
• 若排序行大小 > 4096 字节：使用 "归并排序"（磁盘临时文件排序）；
• 优化：减少排序字段数量，降低单行大小。

六、MySQL 数据类型选择规范（阿里规范）

1. 数值类型

类型	取值范围	优化建议
TINYINT	-128~127（UNSIGNED：0~255）	替代 ENUM/BIT，节省空间
INT	-2^31~2^31-1（UNSIGNED：0~2^32-1）	无负数时用 UNSIGNED，不指定显示宽度（如 INT (10) 无意义）
DECIMAL	高精度小数	存储金额等高精度数据；优先用整数存储（如金额 ×100 存 INT）

补充：显示宽度仅在添加UNSIGNED ZEROFILL时生效（如INT(2) ZEROFILL，值 5 显示为 05），不会影响存储范围，若不添加UNSIGNED ZEROFILL则什么都不影响。

2. 日期时间类型

类型	存储空间	适用场景
DATE	3 字节	仅存储日期（yyyy-mm-dd）
TIME	3 字节	仅存储时间（hh:mm:ss）
TIMESTAMP	4 字节	跨时区场景，自动更新（默认 CURRENT_TIMESTAMP）
DATETIME	8 字节	无需时区转换，无时间上限（推荐）

3. 字符串类型

类型	特性	优化建议
CHAR(n)	定长，补空格	短字符串（如手机号、邮编），长度固定场景
VARCHAR(n)	变长，存长度标识	长度差异大的字符串（如用户名、描述）
BLOB/TEXT	大字段	单独分表存储，避免影响主表查询性能

补充规范

• 表情符号需用utf8mb4编码（utf8 仅支持 3 字节，表情占 4 字节）；
• 避免用 VARCHAR 存储数值（如手机号），无法利用数值索引特性；
• VARCHAR (n) 的 n 为最大长度，按实际业务上限设置（如用户名设 VARCHAR (32)，而非 VARCHAR (255)）。

七、总结

MySQL 性能优化核心围绕减少磁盘 IO、利用索引、降低内存计算量三大原则：

1. 分页查询：优先主键范围查询，非主键排序拆分 "排序取主键 + 回表"；
2. 关联查询：小表驱动大表，被驱动表关联字段加索引；
3. 计数查询：根据精度要求选择 MyISAM/Redis/ 计数表方案；
4. 排序分组：利用索引避免 Filesort，MySQL8.0 可使用降序索引；
5. 数据类型：最小化类型长度，优先非 NULL，避免不合理类型选择。

最后，所有优化需结合实际业务场景，通过EXPLAIN分析执行计划，避免过度优化（如为低频查询创建大量索引）。