mysql - 为什么这个覆盖索引查询在远离开头的分页时执行得更好？

Question

在阅读“高性能 MySQL 第二版”的“优化排序”部分时，我发现以下内容很难理解:

mysql> SELECT FROM profiles WHERE sex='M' ORDER BY rating LIMIT 100000, 10;

Such queries can be a serious problem no matter how they’re indexed, because the high offset requires them to spend most of their time scanning a lot of data that they will then throw away.
...
Another good strategy for optimizing such queries is to use a covering index to retrieve just the primary key columns of the rows you’ll eventually retrieve. ... Here’s an example that requires an index on(sex, rating)to work efficiently:

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);

我的问题是，如果第一个查询不能利用(性别评级)索引来查找行 100000-100010，第二个查询将如何做？

Answer 1

来自《高性能MySQL第2版》

Such queries can be a serious problem no matter how they’re indexed, because the high offset requires them to spend most of their time scanning a lot of data that they will then throw away. Denormalizing, precomputing, and caching are likely to be the only strategies that work for queries like this one. An even better strategy is to limit the number of pages you let the user view. This is unlikely to impact the user’s experience, because no one really cares about the 10,000th page of search results.

Another good strategy for optimizing such queries is to use a covering index to retrieve just the primary key columns of the rows you’ll eventually retrieve. You can then join this back to the table to retrieve all desired columns. This helps minimize the amount of work MySQL must do gathering data that it will only throw away.

已经说明了这个查询没有更好的改进。它只是建议将选择的列数最小化到 index 列(当然为了节省存储 10,0000 行所有列的所有数据的内存，以便在期间仅存储 10,000 行的索引列扫描过程)。然后使用检索到的 10 个索引来构建完整的列。