扫二维码与项目经理沟通
我们在微信上24小时期待你的声音
解答本文疑问/技术咨询/运营咨询/技术建议/互联网交流
本篇内容介绍了“MySQL千万数据表Limit分页查询如何优化”的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有所成！
成都创新互联坚持“要么做到，要么别承诺”的工作理念，服务领域包括：网站制作、做网站、企业官网、英文网站、手机端网站、网站推广等服务，满足客户于互联网时代的萨嘎网站设计、移动媒体设计的需求，帮助企业找到有效的互联网解决方案。努力成为您成熟可靠的网络建设合作伙伴！
在各类系统的表格类信息展示的功能中，经常会用到“翻页”这个操作，在页面上每次只展示有限的数据，需要看其他数据的时候则像翻书一样翻到后面的“页”。在 MySQL 支持的 SQL 语法中对此有特殊的支持，开发人员在实现这类功能的时候很方便：
select*fromxxx limit M,N
select*fromxxx limit N offset M
这两类语法代表的意思是一样的：返回从第 M 开始（不包括这一行）之后的 N 行数据。虽然使用起来很方便，但是这类语句存在查询性能上的陷阱，需要特别注意一下。
在解释原理之前，先看一下实际的效果，看看这个“性能的陷阱”是什么。
两个语句的内容都非常简单，差别只在 limit 的部分，第一个语句跳过的行数很少，第二个语句跳过的行数很多，结果是两个语句的执行时间差了至少 200 倍。PS：limit 配合 order by 使用是一个好习惯，确保结果数据是稳定的。
可以看到跳过的行数大幅度增长时，SQL 语句的执行时间也会快速增长，原因其实比较简单：在处理 limit M,N 的时候，MySQL 会先拿到 M+N 行结果数据，然后再丢弃 M 行数据，展示之后剩下的 N 行数据。所以上图的第二个语句实际上扫描了 800 多万行数据，然后丢弃了 800 万行数据，只展示之后的 1 行结果。
利用[慢查询分析三部曲][Link 1]的方法尝试排查一下，explain 和 optimizer_trace 都看不出来差别，但是 profile 里面能看出来两者的差距：
虽然都只输出一行结果，但是在 Sending data 阶段花费的时间差别很大，其实就是花在扫描 800 万行数据上去了。
针对这个问题，其实有一个比较通用的优化思路：利用 join，先根据主键搜索到需要的数据，再通过主键关联到原来的表输出结果。SQL 可以改写一下：
SQL 改写的效果
可以看到查询时间降到了 1.5s 左右，提升了约 37%，看起来还可以，那么还有其他的办法么？
显然还是有的，不过这会要求表有自增主键。在分页查询的时候，记录上一次查询结果中的主键，然后在 where 条件中添加主键的范围约束。以上面的查询为例，上次分页查询时的主键是 8000001，那么下次分页的时候，where 条件中添加一个主键约束：id>8000001，再来看看查询效果：
添加条件之后的效果
可以发现利用主键来筛选掉上一次分页前的所有数据后再用 limit，查询基本是马上返回结果的。不过要特别注意，这种方法是根据主键的顺序先做了一次筛选，不一定会适用于所有的业务场景，理论上 UUID 类的主键也可以用，但是改造 SQL 前务必确保查询结果是符合预期的。
“mysql千万数据表Limit分页查询如何优化”的内容就介绍到这里了，感谢大家的阅读。如果想了解更多行业相关的知识可以关注创新互联网站，小编将为大家输出更多高质量的实用文章！
我们在微信上24小时期待你的声音
解答本文疑问/技术咨询/运营咨询/技术建议/互联网交流
微信二维码
移动版官网