【数据库】优化排序 && 高效分页
例子:
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》