矫情的女人 發表於 2025-12-25 08:34:52

MySQL分页查询优化的实践指南

<div id="navCategory"><h5 class="catalogue">目录</h5><ul class="first_class_ul"><li><a href="#_label0">引言</a></li><li><a href="#_label1">一、测试环境搭建：模拟万级数据量</a></li><ul class="second_class_ul"><li><a href="#_lab2_1_0">1.1 创建测试表</a></li><li><a href="#_lab2_1_1">1.2 批量插入测试数据</a></li></ul><li><a href="#_label2">二、基础分页查询：问题与执行计划分析</a></li><ul class="second_class_ul"><li><a href="#_lab2_2_2">2.1 普通limit分页SQL</a></li><li><a href="#_lab2_2_3">2.2 执行计划分析</a></li></ul><li><a href="#_label3">三、优化方案一：基于自增连续主键的分页查询</a></li><ul class="second_class_ul"><li><a href="#_lab2_3_4">3.1 优化后的SQL</a></li><li><a href="#_lab2_3_5">3.2 执行计划对比</a></li><li><a href="#_lab2_3_6">3.3 关键注意事项</a></li></ul><li><a href="#_label4">四、优化方案二：基于非主键字段排序的分页查询</a></li><ul class="second_class_ul"><li><a href="#_lab2_4_7">4.1 普通非主键排序分页的问题</a></li><li><a href="#_lab2_4_8">4.2 优化后的SQL</a></li><li><a href="#_lab2_4_9">4.3 执行计划优化点</a></li></ul><li><a href="#_label5">五、总结：不同场景的分页查询选型</a></li><ul class="second_class_ul"></ul></ul></div><p class="maodian"><a name="_label0"></a></p><h2>引言</h2>
<p>在日常业务开发中，分页查询是高频操作，比如列表页数据展示、历史记录查询等。但当数据量达到万级以上时，普通的<code>limit</code>分页往往会出现性能瓶颈。本文基于实际测试场景，详细分析MySQL分页查询的执行原理，并针对不同排序场景提供优化方案，附完整测试代码与执行计划对比。</p>
<p class="maodian"><a name="_label1"></a></p><h2>一、测试环境搭建：模拟万级数据量</h2>
<p>为了更真实地复现分页查询问题，我们先创建测试表并插入10万条测试数据，确保测试环境的一致性。</p>
<p class="maodian"><a name="_lab2_1_0"></a></p><h3>1.1 创建测试表</h3>
<div class="jb51code"><pre class="brush:sql;">use martin;-- 切换到目标数据库
drop table if exists t1;-- 若表已存在则删除

CREATE TABLE `t1` (            
`id` int NOT NULL auto_increment,-- 自增主键
`a` int DEFAULT NULL,            -- 普通字段，用于非主键排序测试
`b` int DEFAULT NULL,            -- 普通字段
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '记录创建时间',
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '记录更新时间',
PRIMARY KEY (`id`),-- 主键索引
KEY `idx_a` (`a`),   -- 为字段a创建普通索引，用于非主键排序优化
KEY `idx_b` (`b`)    -- 为字段b创建普通索引（备用）
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
</pre></div>
<p class="maodian"><a name="_lab2_1_1"></a></p><h3>1.2 批量插入测试数据</h3>
<p>通过存储过程批量插入10万条数据，避免手动插入的繁琐：</p>
<div class="jb51code"><pre class="brush:sql;">drop procedure if exists insert_t1;-- 若存储过程已存在则删除
delimiter ;;-- 修改语句结束符，避免与存储过程内的分号冲突
create procedure insert_t1()      
begin
declare i int;                  
set i=1;                        
while(i&lt;=100000)do-- 插入10万条数据
    insert into t1(a,b) values(i, i);-- a、b字段值与自增id一致
    set i=i+1;                  
end while;
end;;
delimiter ;-- 恢复语句结束符为分号
call insert_t1();-- 调用存储过程插入数据
</pre></div>
<p class="maodian"><a name="_label2"></a></p><h2>二、基础分页查询：问题与执行计划分析</h2>
<p>最常见的分页查询方式是使用<code>limit offset, size</code>，但当<code>offset</code>（偏移量）较大时，性能会显著下降。我们以&ldquo;查询第10001-10010条数据&rdquo;为例，分析其执行逻辑。</p>
<p class="maodian"><a name="_lab2_2_2"></a></p><h3>2.1 普通limit分页SQL</h3>
<div class="jb51code"><pre class="brush:sql;">-- 查询a、b字段，跳过前10000条，取10条
select a,b from t1 limit 10000,10;
</pre></div>
<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202512/2025122508344294.png" /></p>
<p class="maodian"><a name="_lab2_2_3"></a></p><h3>2.2 执行计划分析</h3>
<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202512/2025122508344290.png" /></p>
<p>通过<code>explain</code>查看SQL执行计划，关键信息如下（对应测试截图结果）：</p>
<ul><li><strong>type</strong>：可能为<code>ALL</code>（全表扫描）或<code>range</code>（范围扫描），取决于是否使用索引；</li><li><strong>key</strong>：若未命中索引，<code>key</code>字段为空，意味着需要扫描全表数据；</li><li><strong>rows</strong>：扫描行数接近10010行（需跳过前10000行，再取10行），数据量越大，扫描行数越多，性能越差。</li></ul>
<p><strong>核心问题</strong>：<code>limit 10000,10</code>会先扫描前10010条数据，再丢弃前10000条，仅返回最后10条，大量数据的&ldquo;无效扫描&rdquo;导致性能损耗。</p>
<p class="maodian"><a name="_label3"></a></p><h2>三、优化方案一：基于自增连续主键的分页查询</h2>
<p>若分页查询基于<strong>自增且连续的主键</strong>排序（如按<code>id</code>升序），可通过&ldquo;主键范围查询&rdquo;替代<code>limit offset</code>，彻底避免无效数据扫描。</p>
<p class="maodian"><a name="_lab2_3_4"></a></p><h3>3.1 优化后的SQL</h3>
<div class="jb51code"><pre class="brush:sql;">-- 直接查询id在10001-10010之间的数据，无需跳过前10000条
select a,b from t1 where id &gt; 10000 and id &lt;= 10010;
</pre></div>
<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202512/2025122508344271.png" /></p>
<p class="maodian"><a name="_lab2_3_5"></a></p><h3>3.2 执行计划对比</h3>
<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202512/2025122508344272.png" /></p>
<p>再次使用<code>explain</code>分析优化后的SQL，执行计划发生显著变化：</p>
<ul><li><strong>type</strong>：变为<code>range</code>（范围扫描），仅扫描主键索引中<code>id</code>在10001-10010之间的记录；</li><li><strong>key</strong>：命中主键索引（<code>PRIMARY</code>），无需扫描全表；</li><li><strong>rows</strong>：扫描行数仅为10行，与需要返回的数据量完全一致，性能大幅提升。</li></ul>
<p class="maodian"><a name="_lab2_3_6"></a></p><h3>3.3 关键注意事项</h3>
<p>此方案的前提是<strong>主键必须连续</strong>。若主键不连续（如删除过数据），会导致查询结果与普通<code>limit</code>分页不一致，示例如下：</p>
<p>先删除一条数据，破坏主键连续性：</p>
<div class="jb51code"><pre class="brush:sql;">delete from t1 where id=10;-- 删除id=10的记录
</pre></div>
<p>对比两种查询结果：</p>
<ul><li>普通<code>limit</code>：<code>select a,b from t1 limit 10000,10</code>会跳过前10000条（包含被删除的id=10，实际扫描10001条有效数据），返回第10001-10010条有效数据；</li><li>主键范围查询：<code>select a,b from t1 where id &gt;10000 and id &lt;=10010</code>会跳过id=10的空缺，直接返回id=10001-10010的10条数据，与预期结果不一致。</li></ul>
<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202512/2025122508344246.png" /></p>
<p><strong>适用场景</strong>：主键自增且无删除操作的表（如日志表、流水表）。</p>
<p class="maodian"><a name="_label4"></a></p><h2>四、优化方案二：基于非主键字段排序的分页查询</h2>
<p>若分页查询需要按<strong>非主键字段排序</strong>（如按<code>a</code>字段升序），直接使用<code>order by + limit</code>会触发<code>filesort</code>（文件排序），性能极差。我们通过&ldquo;子查询查主键 + 关联查详情&rdquo;的方式优化。</p>
<p class="maodian"><a name="_lab2_4_7"></a></p><h3>4.1 普通非主键排序分页的问题</h3>
<p>以&ldquo;按<code>a</code>字段排序，查询第99001-99002条数据&rdquo;为例，普通SQL如下：</p>
<div class="jb51code"><pre class="brush:sql;">select * from t1 order by a limit 99000,2;
</pre></div>
<ul><li><strong>执行计划问题</strong>：<code>order by a</code>会触发<code>filesort</code>（即使<code>a</code>字段有索引<code>idx_a</code>，若查询字段包含非索引字段，仍需回表，可能导致<code>filesort</code>）；</li><li><strong>性能损耗</strong>：需扫描大量数据并排序，数据量越大，排序耗时越长。</li></ul>
<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202512/2025122508344233.png" /></p>
<p class="maodian"><a name="_lab2_4_8"></a></p><h3>4.2 优化后的SQL</h3>
<p>核心思路：先通过子查询仅查询&ldquo;排序后的主键id&rdquo;（利用索引避免<code>filesort</code>），再通过主键关联查询完整数据：</p>
<div class="jb51code"><pre class="brush:sql;">-- 子查询：按a排序，取第99001-99002条的id（仅扫描索引，无filesort）
-- 主查询：通过id关联表t1，查询完整数据（主键关联性能极高）
select f.* from t1 f
inner join (select id from t1 order by a limit 99000,2) g
on f.id = g.id;
</pre></div>
<p class="maodian"><a name="_lab2_4_9"></a></p><h3>4.3 执行计划优化点</h3>
<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202512/2025122508344275.png" /></p>
<ul><li><strong>子查询</strong>：<code>select id from t1 order by a limit 99000,2</code>命中索引<code>idx_a</code>，<code>type</code>为<code>index</code>，无<code>filesort</code>，仅扫描99002条索引记录（远少于全表扫描）；</li><li><strong>主查询</strong>：通过主键<code>id</code>关联，<code>type</code>为<code>eq_ref</code>（主键等值匹配，性能最优），<code>rows</code>仅为2行，无额外性能损耗。</li></ul>
<p><strong>适用场景</strong>：所有需要按非主键字段排序的分页查询，尤其适合数据量超过10万级的表。</p>
<p class="maodian"><a name="_label5"></a></p><h2>五、总结：不同场景的分页查询选型</h2>
<table><thead><tr><th>分页场景</th><th>推荐方案</th><th>优点</th><th>注意事项</th></tr></thead><tbody><tr><td>主键自增且连续、按id排序</td><td><code>where id &gt; offset and id &lt;= offset+size</code></td><td>无无效扫描，性能最优</td><td>主键必须连续，无删除操作</td></tr><tr><td>非主键字段排序</td><td>子查询查id + 主键关联查详情</td><td>避免filesort，减少扫描行数</td><td>需为排序字段创建索引</td></tr><tr><td>主键不连续、按id排序</td><td>保留普通limit，或结合覆盖索引</td><td>结果准确，兼容性强</td><td>可通过&ldquo;覆盖索引&rdquo;减少全表扫描范围</td></tr></tbody></table>
<p>通过以上优化方案，可有效解决MySQL分页查询在大数据量下的性能问题，实际项目中需根据业务场景（排序字段、主键连续性）选择合适的方案，并结合索引设计进一步提升性能。</p>
<p>以上就是MySQL分页查询优化的实践指南的详细内容，更多关于MySQL分页查询优化的资料请关注琼殿技术社区其它相关文章！</p>
頁: [1]
查看完整版本: MySQL分页查询优化的实践指南