上文提到可以通过去除跳页和限制翻页来减少整个系统的压力。然而PD往往会考虑到用户体验，不愿意去推动这种功能上的删减。
家家有本难念的经，可以理解PD的苦衷。那么作为一个DBA，就只能从SQL上去优化分页的性能了。
假设我们有一个需要分页的表，如下：
CREATE TABLE `test` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`b` int(11) NOT NULL,
`c` datetime NOT NULL,
`d` varchar(1000) NOT NULL,
PRIMARY KEY (`id`),
KEY `idx_b_c` (`b`,`c`),
KEY `idx_b_id` (`b`,`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
ID为自增，b为随机数，c为递增的时间，d为长度1000的字符串。往里面插入200w+数据。
典型的分页语句如下：
SQL1：select SQL_NO_CACHE * from test force index(idx_b_c) where b=1 order by c desc limit 2000,10
在六块磁盘做RAID10的情况下，SQL1需要耗费6s以上。explain SQL1可以得到以下结果：
+—-+————-+——-+——+—————+———+———+——-+——+————-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+——-+——+—————+———+———+——-+——+————-+
| 1 | SIMPLE | test | ref | idx_b_c | idx_b_c | 4 | const | 2574 | Using where |
+—-+————-+——-+——+—————+———+———+——-+——+————-+
using where表示MySQL从InnoDB获取了数据之后还要根据条件进行筛选，也就是limit 2000,10那部分。
从原理上说，MySQL是根据二级索引来获取符合条件（b=1）的记录主键（id），再根据记录的主键（id）去查询相应的记录。
然而这里有两种做法：
一种是，先查询出2010条记录的id，回表查询数据，再将2012条完整记录发给MySQL以便筛选最后10条；
另外一种是，先查询出2010条记录的id，筛选出最后10条记录的id再回表查询，最后返回10条完整记录给MySQL。
在回表次数很多（limit决定）的情况下，显然第二种方法是比较快的，而MySQL默认采用了第一种。
于是我们有了改进版本的分页语句：
SQL2：select SQL_NO_CACHE * from test, (select SQL_NO_CACHE id from test force index(idx_b_c_id) where b=1 order by c desc limit 2000,10) temp where test.id=temp.id
SQL2的耗时在0.05s左右（我在做这个实验时开启了O_DIRECT，并且每次查询前都要重启数据库，所以不用担心buffer的影响）。
explain SQL2可以得到：
explain select SQL_NO_CACHE * from test, (select SQL_NO_CACHE id from test force index(idx_b_c) where b=1 order by c desc limit 2000,10) temp where test.id=temp.id;
+—-+————-+————+——–+—————+———+———+———+——+————————–+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+————+——–+—————+———+———+———+——+————————–+
| 1 | PRIMARY | | ALL | NULL | NULL | NULL | NULL | 10 | |
| 1 | PRIMARY | test | eq_ref | PRIMARY | PRIMARY | 8 | temp.id | 1 | |
| 2 | DERIVED | test | ref | idx_b_c | idx_b_c | 4 | | 2574 | Using where; Using index |
+—-+————-+————+——–+—————+———+———+———+——+————————–+
3 rows in set (0.00 sec)
虽然还是难以避免地扫过了2574个id（该数字不准确），但是回表次数大大降低了。SQL 2只需要回表10次（0.05s），而SQL1需要2010次（6s+）。回表带来的大量随机IO就这样被SQL2避免了。
仔细思考分页需求，是否还可以进一步优化SQL2？
答案是肯定的。虽然回表次数没有办法减少了，但是我们可以想办法减少二级索引的扫描次数。请看第三个分页语句：
SQL 3：select SQL_NO_CACHE * from test force index(idx_b_id) where b=1 and id 这样的优化是带有一定条件的。其中最重要的一条就是，id字段和c字段在功能上可以互换。在很多系统（包括淘宝的评价系统）中，自增ID和记录创建时间一般满足这个条件；其次，SQL 3只能提供翻页功能，而且必须在客户端缓存当前页的最大id和最小id，以便翻页SQL的生成。若要优化跳页，还必须加上SQL 2。在一定程度上，这种优化方式使得代码更加复杂了；再其次，索引结构需要调整，大家可以看到SQL 2和SQL 3用的索引是不一样的。对于繁忙的系统，很多大表根本就没法调整索引结构，这也限制了SQL 3的使用场景。
如果大家懒得做大改变，那么请注意一下翻页SQL的desc和asc写法：
select SQL_NO_CACHE * from test force index(idx_b_c) where b=1 order by c desc n,10
select SQL_NO_CACHE * from test force index(idx_b_c) where b=1 order by c asc n,10
针对同一个请求，这两个SQL的效率是不一样的。假设评价总共有1000页，每页有10条记录。用户想要查看第88页的内容。
select SQL_NO_CACHE * from test force index(idx_b_c) where b=1 order by c desc 870,10
select SQL_NO_CACHE * from test force index(idx_b_c) where b=1 order by c asc 9120,10
一般而言，n越大SQL的效率会越差。而我们在得到了具体的页数之后，往往可以计算一下desc和asc各自的n是多少，从而选择更有效率的查询SQL。
分页优化很值得研究，我在这先抛砖引玉了，希望大家也谈谈自己的优化经验。
--------------------------------------------------------------------------------------------------------
下面看下各大网站的情况
当数据量大到一定程度的时候，用户往往已经无法一次性查看所有数据，于是就产生了分页需求。
纵观百度、腾讯、网易、淘宝等大型互联网公司的产品，分页之处比比皆是。下面是淘宝的评价页面截图：
说实话，淘宝的分页太过于强大，想怎么查看就怎么查看。不加限制的分页功能，会给系统带来极大的压力和隐患。要优化分页，就必须要舍弃一些华而不实的功能。下面就是造成巨大压力的几个祸首：
1、跳页。用户实际上并不知道在哪一页会出现什么样的内容，也就是说用户无法预期自己跳页后将会获得的数据。从这个角度想，跳页是用户的一种不精确行为。假设某用户查询了2009-01-01到2010-01-01的数据，他在翻了几页之后突然想起需要查看2009-11-11那一天的评价，于是他就使用了跳页功能。跳到差不多的页数后，他再慢慢向前或者向后翻页。
2、无限制翻页。翻页功能是必要的，但是无限制的翻页往往会消耗过多的性能。典型代表就是网络爬虫。
以下截图是更合理的分页方式：
百度贴吧取消了大幅度的跳页功能，用户只能在10页范围内进行跳跃。
而twitter更狠，用户只能获取最新的消息，想要查看更久远一些的内容，必须点more按钮。
作为淘宝的用户，我觉得跳页功能多少有点鸡肋。PD应该改进产品，培养用户使用搜索条件的习惯。为了查找2009-11-11的数据而在2009-01-01到2010-01-01的数据里面跳来跳去，还不如直接在2009-11-10到2010-11-12的数据里面精确定位自己想要的内容。
扫一扫
4638
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
