当您分页时，按字母顺序的分页变得越来越慢(MySQL) [英] Alphabetical pagination gets progressively slower as you page (MySQL)
问题描述
我有一个超过10万行的数据集，所以它不是很小，但也不是很大.分页浏览结果时，转到较高的页面时，它会逐渐变慢.换句话说，此查询:
I've got a dataset with over 100k rows, so it's not tiny, but not huge either. When paging through the results, it gets progressively slower as you go to higher pages. In other words, this query:
SELECT * FROM items WHERE public = 1 ORDER BY name LIMIT 0,10
执行速度比
SELECT * FROM items WHERE public = 1 ORDER BY name LIMIT 10000,10
我在 name 上有一个索引，而我以前在 public 上有一个索引，但是我删除了它，因为它似乎会进一步降低性能.
I have an index on name, and I used to have an index on public, but I removed it since it seemed to degrade performance even more.
这里有什么想法吗?有没有简单的方法可以加快速度?我正在考虑取消查看较高页面的功能，因为除了机器人之外，没有人真正浏览过第2或3页，并且他们可以找到更轻松的方法来找到该内容.
Any ideas here? Is there an easy way to speed this up? I'm considering removing the ability to view the higher pages since nobody really browses past page 2 or 3, except robots, and there are easier ways for them to find that content.
推荐答案
大型LIMIT问题:
提防大量LIMIT如果需要前几行，即使使用了一些额外的过滤功能，使用索引进行排序也是有效的，因此您需要按索引扫描更多行，然后再按LIMIT的要求进行扫描.但是，如果您要处理LIMIT查询，那么偏移效率会很高. LIMIT 1000,10可能比LIMIT 0,10慢.的确，大多数用户的搜索结果不会超过10页，但是搜索引擎机器人可能会这样做.我已经看到机器人在我的项目中查看200多个页面.同样，对于许多无法处理此问题的网站而言，它提供了发起DOS攻击的非常简单的任务-从很少的连接中获取大量请求页面，这就足够了.如果您什么都不做，请确保您阻止了页码太大的请求.
Beware of large LIMIT Using index to sort is efficient if you need first few rows, even if some extra filtering takes place so you need to scan more rows by index then requested by LIMIT. However if you're dealing with LIMIT query with large offset efficiency will suffer. LIMIT 1000,10 is likely to be way slower than LIMIT 0,10. It is true most users will not go further than 10 page in results, however Search Engine Bots may very well do so. I've seen bots looking at 200+ page in my projects. Also for many web sites failing to take care of this provides very easy task to launch a DOS attack - request page with some large number from few connections and it is enough. If you do not do anything else make sure you block requests with too large page numbers.
在某些情况下，例如，如果结果为静态，则可以预先计算结果，以便可以查询位置. 因此，您将获得WHERE位置，而不是使用LIMIT 1000,10进行查询，只要位置被索引，它在1000到1009之间的效率就相同.
For some cases, for example if results are static it may make sense to precompute results so you can query them for positions. So instead of query with LIMIT 1000,10 you will have WHERE position between 1000 and 1009 which has same efficiency for any position (as long as it is indexed)
资源:
这篇关于当您分页时，按字母顺序的分页变得越来越慢(MySQL)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
