MySQL分页从按随机数据索引排序的大表中选择

Question

我知道当您可以按某些唯一索引对表进行排序时的解决方案

SELECT user_id, external_id, name, metadata, date_created
FROM users
WHERE user_id > 51234123 
ORDER BY user_id ASC
LIMIT 10000;

但就我而言，我想按一些索引对表进行排序，这些索引具有随机数据

CREATE TABLE `t` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`sorter` bigint(20) NOT NULL,
`data1` varchar(200) NOT NULL,
`data2` varchar(200) NOT NULL,
`data3` varchar(200) NOT NULL,
`data4` int(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `sorter` (`sorter`),
KEY `id` (`id`,`sorter`),
KEY `sorter_2` (`sorter`,`id`)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
for ($i = 0; $i < 2e6; $i++)
$db->query("INSERT INTO `t` (`sorter`, `data1`, `data2`, `data3`, `data4`) VALUES (rand()*3e17, rand(), rand(), rand(), rand())");
for ($i = 0; $i < 1e6; $i++)
$db->query("INSERT INTO `t` (`sorter`, `data1`, `data2`, `data3`, `data4`) VALUES (0, rand(), rand(), rand(), rand())");

解决方案 1：

for ($i = 0; $i < $maxId; $i += $step)
select * from t
where id>=$i
order by sorter
limit $step
select * from t order by sorter limit 512123, 10000;
10000 rows in set (9.22 sec)
select * from t order by sorter limit 512123, 1000;
1000 rows in set (6.25 sec)
+------+-------------+-------+------+---------------+------+---------+------+---------+----------------+
| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows    | Extra          |
+------+-------------+-------+------+---------------+------+---------+------+---------+----------------+
|    1 | SIMPLE      | t     | ALL  | NULL          | NULL | NULL    | NULL | 3000000 | Using filesort |
+------+-------------+-------+------+---------------+------+---------+------+---------+----------------+

解决方案 2：

按分拣机限制1512123 10000 从 T 订单中选择 ID;

+------+-------------+-------+-------+---------------+----------+---------+------+---------+-------------+
| id   | select_type | table | type  | possible_keys | key      | key_len | ref  | rows    | Extra       |
+------+-------------+-------+-------+---------------+----------+---------+------+---------+-------------+
|    1 | SIMPLE      | t     | index | NULL          | sorter_2 | 16      | NULL | 1522123 | Using index |
+------+-------------+-------+-------+---------------+----------+---------+------+---------+-------------+

10000 行(0.74 秒(

0.74听起来不错，但对于所有表，它需要 0.74*3000e3/10e3/60 = 超过 3 分钟，并且仅用于收集 ID

Answer 1

使用OFFSET并不像您想象的那么有效。 使用LIMIT 1512123, 10000时，必须跨1512123行。 该数字越大，查询运行速度越慢。

为了解释EXPLAINs的差异...

"解决方案 1" 使用SELECT *; 您没有覆盖索引。 因此，有两种运行查询的方式：

• (它这样做了(：扫描表的"ALL"，收集所有列(*(;排序;跳过512123行;并提供10000或1000行。

• (一个小OFFSET和LIMIT可能会导致这种情况(：在BTree内部，INDEX(sorter, id)跳过OFFSET行;抓取LIMIT行;对于索引中的每个抓取行，使用字节偏移量(注意：您使用的是MyISAM，而不是InnoDB(进入数据文件以查找行;抓取*并交付它。 无需排序。

遗憾的是，优化器没有足够的统计信息，也没有足够的智能来始终在这两个选项之间进行正确的选择。

"解决方案 2"使用"覆盖"索引INDEX(sorter, id)。 (线索："使用索引"。 它包含查询(select id from t order by sorter limit 1512123, 10000;(中任何地方找到的所有列(只有sorter和id(，因此索引可以(并且通常(优先于扫描表。

另一个解决方案提到了涉及where id>=$i。 这避免了OFFSET。 但是，由于您使用的是MyISAM，因此索引和数据不能"聚类"在一起。 使用InnoDB，数据根据PRIMARY KEY排序。 如果这是id，则查询可以通过直接跳转到数据中间开始(在$i处(。 使用MyISAM，我刚才描述的内容是在BTree中完成的INDEX(id);但它仍然必须在该 Btree 和数据所在的.MYD文件之间来回反弹。 (这是InnoDB的设计本质上比MyISAM更有效的一个例子。

如果你的目标是从表格中获取一堆随机行，请阅读我的论文。 总之，有更快的方法，但没有一个是"完美的"，尽管通常"足够好"。