MySQL延迟关联性能优化方法

【背景】

 某业务数据库load 报警异常，出现较多的线上慢sql查询。定位问题之后，发现原因是：查询sql语句同时使用了ORDE BY 和 LIMIT，即使使用了索引，进行分页处理。因为随着偏移量的增加，MySQL需要花费大量的时间来扫描需要丢弃的数据。

下面这个查询就通过ORDE BY 和 LIMIT偏移量的组合翻页到很后面的时候：

mysql>SELECT <cols> FROM profiles WHERE sex='M' ORDER BY rating LIMIT 100000,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> SELECT <cols> FROM profiles INNER JOIN(
      ->           SELECT id FROM profiles
      ->           WHERE x.sex = 'M' ORDER BY rating LIMIT 100000,10
      ->  ) AS x WHERE x.id = profiles.id;