表格:
CREATE TABLE `test` (
`uid` int(11) unsigned NOT NULL AUTO_INCREMENT,
`rating` smallint(5) unsigned NOT NULL DEFAULT '100',
PRIMARY KEY (`uid`),
KEY `rating` (`rating`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
此查询运行得足够快(0.015s):
SELECT uid FROM test ORDER BY rating DESC LIMIT 0,100
但由于有较大的极限偏移,它运行非常缓慢(2.215s):
SELECT uid FROM test ORDER BY rating DESC LIMIT 10000,100
如何消除巨大的限制偏移?!
最佳答案
提高性能的最简单方法是按主键排序。
因为您不能用rating
列真正做到这一点,所以您可以作弊。
创建此表:
CREATE TABLE `test_ranks` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`uid` int(11) unsigned NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
然后将以下内容放入cron脚本中,该脚本每X时间运行一次(1分钟,5分钟。。。基本上是更新速度和运行时间之间的一个很好的折衷方案):
CREATE TEMPORARY TABLE `_tmp_test_ranks` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`uid` int(11) unsigned NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `_tmp_test_ranks` (`uid`) VALUES (SELECT `uid` FROM `test` ORDER BY `rating` DESC);
TRUNCATE `test_ranks`;
INSERT INTO `test_ranks` SELECT * from `_tmp_test_ranks`;
DROP TABLE `_tmp_test_ranks`;
现在,您可以运行速度更快的选项,而不是慢速运行的选项:
SELECT `uid` FROM `test_ranks` WHERE `id` BETWEEN 10000 AND 10100 ORDER BY `id` ASC
关于mysql - 如何优化MySQL ORDER BY +巨大的LIMIT偏移,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/10459328/