【数据库】优化排序 && 高效分页

例子：

select <cols> from profiles where sex = 'M' order by rating  limit 10;

同时使用了order by，limit，如果没有索引会很慢。而sey的选择又很低，可以增加一些特殊的索引来做排序。例如，创建(sex，rating）索引。

即使有索引，如果用户需要翻页，并且翻页到比较靠后时查询也可能非常慢。

下面这个查询就通过order by 和limit偏移量的组合翻页到很靠后面的时候：

mysql>select <cols> from profiles where sex = 'M‘order by rating limit 1000000,10 ; 

无论如何创建索引，这种查询都是个严重的问题。因为随着偏移量增多，MySQL需要花费大量的时间来扫描需要丢弃的数据。

反序列化、预先计算和缓存可能是解决这类问题的仅有策略。一个更好的办法是限制用户的翻页数量，实际对用户体验也不会有太大影响，因为用户很少会正在在乎搜索结果的第10000页数据。

优化这类索引的另一个比较好的策略就是使用延迟关联，通过使用覆盖索引查询返回需要的主键，再根据这些主键关联原表获得需要的行。这可以减少MySQL扫描那些丢弃的行数。下面这个查询显示了如何高效地使用（sex,rating)索引进行排序和分页。

mysql>select  <cols> from profiles inner join (

-> select <primary key cols >from profiles 

->where x.sex = 'M' order by rating limit 100000,10

->as x using (<primary key cols>);

 

参考：《高性能MySQL》