我知道一个解决方案，当你可以排序表的一些唯一的索引
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
1条答案
按热度按时间hjqgdpho1#
使用
OFFSET没有你想象的那么高效。与LIMIT 1512123, 10000，1512123行必须跨过。这个数字越大，查询运行越慢。来解释
EXPLAINs...“解决方案1”使用
SELECT *; 你没有它的覆盖索引。因此，有两种方式可以运行查询：（它这样做了）：扫描'所有'表，收集所有列(
*); 排序；跳过512123行；并交付10000或1000行。（一小部分）
OFFSET以及LIMIT可能会导致这种情况）：在btree中INDEX(sorter, id)跳过OFFSET排；抓住那个LIMIT排；对于索引中的每个抓取行，使用字节偏移量（注意：您使用的是myisam，而不是innodb）进入数据文件以查找该行；抓住*然后送过去。不需要排序。不幸的是，优化器没有足够的统计信息，也没有足够的智能来在这两个选择中进行正确的选择。
“解决方案2”使用“覆盖”索引
INDEX(sorter, id). (线索：“使用索引”。）它包含所有列（仅sorter以及id)在查询中的任意位置找到(select id from t order by sorter limit 1512123, 10000;)，因此索引可以（通常会）优先于扫描表。另一个解决方案暗指
where id>=$i. 这避免了OFFSET. 但是，由于您使用的是myisam，因此索引和数据不能“聚集”在一起。对于innodb，数据是根据PRIMARY KEY. 如果是的话id，则查询可以直接跳转到数据的中间（在$i). 对于myisam，我刚才描述的是在btree中为INDEX(id); 但它仍然需要在树和树之间来回反弹.MYD数据所在的文件(这是innodb的设计比myisam的效率更高的一个例子。）如果你的目标是从一个表中得到一堆随机的行，请阅读我的论文。总之，有更快的方法，但没有一种是“完美的”，尽管通常“足够好”。