MySQL索引优化实战指南：分页、Join、Count与阿里规范解析

在数据库应用开发中，高效的查询是保障系统性能的关键。索引作为 MySQL 性能优化的核心手段，其使用策略直接影响查询效率。本文将深入探讨几种常见场景下的索引优化方案，并结合阿里开发规范，为你提供一套可落地的实战指南。

一、分页查询优化

业务系统中实现分页功能常使用如下 SQL：

select * from employees limit 10000,10;

这条语句表示从表 employees 中取出从第 10001 行开始的 10 行记录。虽然最终只返回 10 条数据，但数据库需要先读取 10010 条记录，然后抛弃前 10000 条。当查询大表中靠后的数据时，这种“深度分页”效率非常低。

分页场景优化技巧

1、根据自增且连续的主键排序的分页查询

先看一个例子：

select * from employees limit 90000,5;

该 SQL 未指定 ORDER BY，默认通过主键排序。假设主键 id 是自增且连续的，我们可以将其改写为：

select * from employees where id > 90000 limit 5;

对比两者的执行计划：

EXPLAIN select * from employees limit 90000,5;

EXPLAIN select * from employees where id > 90000 limit 5;

显然，改写后的 SQL 利用了主键索引，扫描行数大大减少。但这种优化方法有严格的前提条件：

• 主键必须自增且连续。
• 查询结果必须是按照主键排序的。

如果表中有数据被删除导致主键不连续，或者原 SQL 是按非主键字段排序，则不能使用此方法。

2、根据非主键字段排序的分页查询

对于按非主键字段排序的分页，例如：

select * from employees ORDER BY name limit 90000,5;

其执行计划如下：

EXPLAIN select * from employees ORDER BY name limit 90000,5;

优化器没有使用 name 字段的索引，因为扫描整个索引并回表查找的成本可能高于直接全表扫描。

如何优化？
关键在于利用覆盖索引，先通过索引完成排序和分页，拿到主键ID，再通过主键关联获取全部数据。改写 SQL 如下：

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

优化后的执行计划显示，子查询(DERIVED)部分使用了覆盖索引(Using index)，避免了昂贵的文件排序(file sort)。

二、Join关联查询优化

为了说明关联查询优化，先创建示例表：

-- 示例表：
CREATE TABLE `t1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`a` int(11) DEFAULT NULL,
`b` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_a` (`a`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

create table t2 like t1;
-- ... 插入示例数据（t1万行，t2百行）

MySQL 的表关联主要有两种算法：Nested-Loop Join (NLJ) 和 Block Nested-Loop Join (BNL)。

1、 嵌套循环连接 Nested-Loop Join (NLJ) 算法

当关联字段有索引时，通常使用 NLJ 算法。

EXPLAIN select * from t1 inner join t2 on t1.a= t2.a;  -- // a字段有索引

从执行计划可以看出：

• 驱动表是数据量较小的 t2，被驱动表是 t1。优化器会优先选择小表作为驱动表。
• Extra 字段未出现 Using join buffer，说明使用的是 NLJ 算法。

执行流程大致为：遍历驱动表 t2 的每一行，用其关联字段 a 的值去被驱动表 t1 的索引树中查找，然后合并结果。整个过程扫描了约 200 行（t2的100行 + t1的100次索引扫描）。

2、 基于块的嵌套循环连接 Block Nested-Loop Join (BNL) 算法

当关联字段没有索引时，MySQL 会使用 BNL 算法。

EXPLAIN select * from t1 inner join t2 on t1.b= t2.b; -- // b字段没有索引

Extra 中的 Using join buffer (Block Nested Loop) 表明使用了 BNL 算法。

执行流程：

1. 将整个驱动表 t2 的数据读入 join_buffer。
2. 遍历被驱动表 t1 的每一行，与 join_buffer 中的所有数据进行比对。
3. 返回满足条件的行。

这个过程对两表都进行了全表扫描（10100行），并在内存中进行了约100万次判断。如果 join_buffer 放不下驱动表，则会分段处理，导致被驱动表被多次扫描。

为什么被驱动表无索引时选择 BNL？
如果此时使用 NLJ，需要对被驱动表进行 100万次（100 * 10000）无索引的磁盘扫描，效率极低。BNL 将磁盘扫描转化为内存计算，虽然计算量大，但速度远快于磁盘 IO。

关联查询优化总结

1. 关联字段加索引：这是最根本的优化，让 MySQL 优先使用高效的 NLJ 算法。
2. 小表驱动大表：优化器通常会自动选择。在明确知晓小表的情况下，可使用 straight_join 强制指定驱动顺序，但需谨慎使用。

   select * from t2 straight_join t1 on t2.a = t1.a; -- 指定t2为驱动表

   straight_join 只适用于 inner join。

3. “小表”的定义：是指在应用了各自的 WHERE 条件过滤后，参与 join 的数据总量较小的那个表。

在构建高并发系统时，关联查询的优化是数据库/中间件/技术栈性能调优的重要一环。

三、in和exists优化

核心原则：小数据集驱动大数据集。

• in 查询：适用于 in 后的子查询结果集（B表）较小的情况。它先执行子查询，然后将结果用于主查询。

  select * from A where id in ( select id from B)
  -- 等价逻辑：
  for(select id from B){
    select * from A where A.id = B.id
  }

• exists 查询：适用于主查询结果集（A表）较小的情况。它将主查询的数据放到子查询中做验证。

  select * from A where exists (select 1 from B where B.id = A.id)
  -- 等价逻辑：
  for(select * from A){
    select * from B where B.id = A.id
  }

总结：

• in 适合于B表数据量小的情况。
• exists 适合于A表数据量小的情况。
• 无论是 in 还是 exists，都应确保关联字段（如 id）上建有索引。

四、count(*)查询优化

不同 count 写法的效率对比一直是个热门话题。我们关闭查询缓存来测试：

set global query_cache_size=0;
set global query_cache_type=0;

EXPLAIN select count(1) from employees;
EXPLAIN select count(id) from employees;
EXPLAIN select count(name) from employees;
EXPLAIN select count(*) from employees;

四个 SQL 的执行计划相同，但内部处理效率有差异：

• 字段有索引时：count(*) ≈ count(1) > count(索引字段) > count(主键 id)。因为 count(字段) 走二级索引，数据量通常比主键索引小。
• 字段无索引时：count(*) ≈ count(1) > count(主键 id) > count(普通字段)。

关键结论：

• count(*) 是 SQL92 标准语法，MySQL 对其做了专门优化（例如 5.7 版本后，InnoDB 引擎不会取出所有字段，而是按行累加），效率很高。
• 无需用 count(列名) 或 count(1) 来替代 count(*)。

大数据量表 count 优化方法

1. 利用存储引擎特性：MyISAM 表会存储总行数，count(*) 极快。但 InnoDB 因 MVCC 机制需实时计算。
   
2. 使用 show table status：如果只需估算行数，此命令性能极高。
   
3. 将计数维护到 Redis：在数据增删时同步更新 Redis 中的计数。缺点是无法保证严格的数据库与缓存事务一致性。
4. 增加计数表：在同一个数据库事务中，同时操作业务表和计数表，保证一致性。

五、阿里MySQL开发规范精要

遵循良好的开发规范能从根本上避免许多性能问题。以下节选部分阿里Java开发手册中与数据库相关的核心规约。

(一) 建表规约

• 【强制】表名、字段名必须使用小写字母或数字，禁止出现数字开头。
• 【强制】表达是与否的字段，使用 is_xxx 命名，类型为 unsigned tinyint（1是/0否）。
• 【强制】小数类型用 decimal，禁用 float 和 double。
• 【强制】varchar 长度不超过 5000，超长字段使用 text 并拆分到单独表。
• 【强制】表必备三字段：id (bigint unsigned 主键)、gmt_create、gmt_modified（均为 datetime）。
• 【推荐】单表行数超500万或容量超2GB时，才考虑分库分表。

(二) 索引规约

• 【强制】业务上具有唯一特性的字段，必须建成唯一索引。
• 【强制】超过三个表禁止 join。关联字段必须要有索引。
• 【强制】varchar 字段建索引必须指定长度。
• 【强制】页面搜索严禁左模糊（like ‘%...’）或全模糊。
• 【推荐】利用覆盖索引进行查询，避免回表。
• 【推荐】利用延迟关联或子查询优化超多分页场景。

(三) SQL语句

• 【强制】使用 count(*)，而不是 count(列名) 或 count(常量)。
• 【强制】禁止使用存储过程和外键。
• 【强制】数据订正时，必须先 select 确认，再执行更新。
• 【推荐】in 操作集合元素数量应控制在1000个以内。

(四) ORM映射

• 【强制】禁止使用 * 作为查询字段列表。
• 【强制】更新记录时，必须同时更新 gmt_modified 字段为当前时间。
• 【推荐】不要写大而全的数据更新接口，只更新有变动的字段。

六、MySQL数据类型选择

选择正确的数据类型对性能至关重要。基本原则是：选用更小的类型、字段定义为 NOT NULL。

1、数值类型

优化建议：

• 无负数的整数（如ID）指定为 UNSIGNED。
• 避免使用整数的显示宽度（如 INT(10)），直接用 INT。
• DECIMAL 用于需要精确计算（如价格），但注意长度设置。
• 整数通常是运算和存储实数（如金额以分为单位）的最佳选择。

2、日期和时间类型

优化建议：

• 使用 DATE、TIME、DATETIME 存储时间，而非字符串。
• TIMESTAMP 占用4字节，带时区信息，存在2038年上限问题。
• DATETIME 占用8字节，存储绝对时间，无时区问题，范围更大。阿里等大厂常使用 DATETIME。

3、字符串类型

优化建议：

• 长度变化大用 VARCHAR；长度固定或很短用 CHAR。
• VARCHAR 长度建议不超过5000字符。
• 尽量少用 BLOB 和 TEXT，如需使用可考虑单独存表。
• BLOB 系列存储二进制数据，TEXT 系列存储字符数据，与字符集相关。

关于整型显示宽度的提示：
在创建表时指定的 INT(11) 或 TINYINT(2)，其中的数字是显示宽度，不影响存储范围。它仅在使用 ZEROFILL 时，在输出数值前填充零。实际存储和计算完全不受这个宽度影响。因此，在定义字段时直接使用 INT、TINYINT UNSIGNED 等即可，无需指定显示宽度。

希望通过以上从具体场景优化到开发规范，再到数据类型选择的系统讲解，能帮助你构建出性能更卓越的数据访问层。如果你有更多关于分布式系统或数据库的优化心得，欢迎在云栈社区交流探讨。