MySQL SELECT ORDER BY RAND()解析：为何会使用无关索引？

# 查看MySQL版本
mysql --version
# 输出结果
mysql  Ver 14.14 Distrib 5.7.34, for Linux (x86_64) using  EditLine wrapper

案例1 #

表结构与数据插入 #

CREATE TABLE `Foo2` (
  `id` bigint(20) unsigned NOT NULL,
  `column1` int(10) unsigned NOT NULL,
  `column2` int(10) unsigned NOT NULL,
  `column3` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `composite` (`column1`, `column2`, `column3`)
) ENGINE=InnoDB;

# 批量插入10000条数据
for i in `seq 1 10000`; do mysql -uroot -p"password" -e "replace into foo_db.Foo2 (\`id\`,\`column1\`,\`column2\`,\`column3\`) values ($((i * 2)), ${i}, $((i + 1)), NOW());" >/dev/null 2>&1; done

执行查询与结果 #

执行EXPLAIN分析语句：

explain select * from foo_db.Foo2 order by RAND() limit 5;

（执行计划显示：possible_keys为NULL，实际使用composite索引，rows字段数值与实际行数不符）

统计实际数据行数：

select count(*) from foo_db.Foo2;
# 结果: 10000

案例2 #

表结构与数据插入 #

CREATE TABLE `Foo3` (
  `id` bigint(20) unsigned NOT NULL,
  `column1` int(10) unsigned NOT NULL,
  `column2` int(10) unsigned NOT NULL,
  `column3` datetime NOT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `composite` (`column1`, `column2`, `column3`)
) ENGINE=InnoDB;

# 批量插入10000条数据
for i in `seq 1 10000`; do mysql -uroot -p"password" -e "replace into foo_db.Foo3 (\`id\`,\`column1\`,\`column2\`,\`column3\`) values ($((i * 2)), ${i}, $((i + 1)), NOW());" >/dev/null 2>&1; done

执行查询与结果 #

查询1：SELECT *

explain select * from foo_db.Foo3 order by RAND() limit 5;

（执行计划显示：使用全表扫描，rows字段数值与实际行数不符）

查询2：SELECT id

explain select `id` from foo_db.Foo3 order by RAND() limit 5;

（执行计划显示：使用composite索引，rows字段数值与实际行数不符）

统计实际数据行数：

select count(*) from foo_db.Foo3;
# 结果: 10000

疑问点 #

1. 为何EXPLAIN显示的行数与实际SELECT COUNT(*)的结果不一致？
2. 案例1中，possible_keys为NULL时，为何ORDER BY RAND()会使用无关的composite索引？
3. 案例2中，SELECT *时行为与案例1不同，但SELECT id时行为却与案例1一致，原因是什么？
4. 案例2中，执行SELECT id时为何未使用主键id？

问题解答 #

1. EXPLAIN行数与COUNT(*)结果不一致 #

EXPLAIN里的rows是优化器基于采样统计信息估算的扫描行数，不是精确值。InnoDB默认采样8个数据页生成统计信息，当数据分布或总量变化时，估算值会和实际行数产生偏差。此外，针对ORDER BY RAND() LIMIT 5这类带limit的查询，优化器会优先考虑limit带来的行数缩减，进一步导致估算值远低于实际行数。

2. 案例1中使用composite索引的原因 #

MySQL 5.7在处理ORDER BY RAND() LIMIT N时，会优先选择占用空间最小的索引来降低I/O开销。案例1中，主键是聚簇索引，叶子节点存储整行数据，体积远大于composite二级索引（仅存索引字段+主键id）。即使possible_keys为NULL（无可用过滤/排序索引），优化器仍会选择扫描更小的二级索引，再通过主键回表获取全量字段，这种方式比直接扫描聚簇索引的开销更低。

3. 案例2中SELECT *与SELECT id的行为差异 #

案例2的表比案例1多了两个datetime字段，导致聚簇索引的单条数据体积大幅增加：

• 执行SELECT *时，用composite索引需要回表获取所有字段（包括新增的两个datetime），回表的I/O开销超过了扫描聚簇索引的开销，因此优化器选择直接扫描聚簇索引（全表）。
• 执行SELECT id时，composite二级索引的叶子节点已经包含主键id，无需回表，扫描更小的二级索引开销更低，因此和案例1行为一致。

4. 案例2中SELECT id未使用主键的原因 #

主键索引是聚簇索引，叶子节点存储整行数据，体积远大于composite二级索引。优化器会优先选择扫描体积更小的索引来减少I/O开销，因此即使主键索引也能直接获取id，优化器仍会选择composite索引而非主键索引。

内容的提问来源于stack exchange，提问作者cr001