表格:

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/

10-13 04:10