企业官网建设流程全解析

排序和分页大概是MySQL里最容易被低估的两个操作。很多开发写SQL的时候，ORDER BY和LIMIT用得顺手，觉得不就是排序加取前几条嘛，能有什么技术含量。但真到了生产环境，数据量一上来，这两个“小操作”会联手制造出让人挠头的大麻烦：查询慢、CPU飙高、内存吃紧，甚至直接把数据库拖垮。我自己就踩过不少次坑，有一次凌晨三点被线上告警叫醒，排查到最后发现就是一条ORDER BY + LIMIT的慢查询在作祟。从那以后我花了很长时间把排序和分页从头到尾梳理了一遍，今天就把这些经验和教训完整地分享出来。

这篇内容适合所有用MySQL做开发的读者，不管你是刚入门的新手，还是工作了几年的老手，排序和分页背后的执行细节都值得你重新过一遍。新手可以把它当操作手册，老手可以重点看深分页优化和性能调优那几节，里面很多坑都是常规文档不会写清楚的。

1. 排序需求拆解：ORDER BY看起来简单，其实到处都是细节

1.1 单列排序与多列排序的执行差异

先从一个最基础的场景说起。假设有张用户表：

CREATE TABLE user ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(50), age INT, city VARCHAR(50), created_at DATETIME );

一条常规的排序查询长这样：

SELECT * FROM user ORDER BY created_at DESC;

这条SQL在数据量小的时候毫无压力，但你知道吗，MySQL处理它的方式和你想象的很可能不一样。当数据量小到能装进内存的sort_buffer_size时，MySQL会把所有数据加载到内存里排序，整个过程快如闪电。可一旦数据量超过排序缓冲区的上限，MySQL就不得不把数据拆成多份，每一份在内存排序后再写入磁盘上的临时文件，最后对这些临时文件做归并排序。这个时候的磁盘I/O开销非常可观，查询慢就不奇怪了。

多列排序就更有意思了。我见过不少人以为ORDER BY a, b的意思是“先按a排，再按b排”，这个理解没错，但忽略了一个关键点：排序的优先级和列本身的顺序深度绑定。比如：

SELECT * FROM user ORDER BY age DESC, created_at DESC;

这条SQL会先按age降序排列，age相同的记录再按created_at降序排列。但如果你写成：

SELECT * FROM user ORDER BY created_at DESC, age DESC;

结果就完全不一样了，现在是created_at优先。这个顺序问题在写动态排序时特别容易出bug，尤其是当排序字段来自前端传入的参数时，一定要在服务端严格校验白名单，否则用户传一个你没预料到的字段组合，轻则排序结果不符合预期，重则可能引发慢查询。

1.2 NULL值的排序位置容易踩坑

NULL值的排序位置是个经典陷阱。在MySQL中，默认排序规则是NULL值在升序时排最前面，降序时排最后面。这个行为和很多其他数据库不一样，比如Oracle默认NULL在升序时排在最后。

我举个具体例子。你有一条查询：

SELECT * FROM user WHERE city IS NOT NULL ORDER BY city ASC;

假如你想把city为空的记录排到最后，直接写ORDER BY city ASC是做不到的，因为NULL会排在开头。正确的写法是：

SELECT * FROM user ORDER BY (city IS NULL), city ASC;

这里用了一个小技巧，把“是否为NULL”作为一个布尔排序键，false（0）排在true（1）前面，这样非空的记录就排在了前面，空值的记录全部沉底。同理，如果要在降序时把NULL放到最后，可以这样：

SELECT * FROM user ORDER BY (city IS NULL), city DESC;

这个技巧很实用，但它会破坏索引排序优化，因为排序表达式变成了复合表达式，索引帮不上忙。所以在数据量大的表上使用时要注意评估性能。

1.3 别让函数悄悄摧毁你的排序索引

还有一个非常隐蔽的问题，就是在ORDER BY中套函数。比如：

SELECT * FROM user ORDER BY DATE_FORMAT(created_at, '%Y-%m-%d') DESC;

这条SQL在逻辑上没有任何问题，但它注定无法使用索引进行排序优化。原因很简单，MySQL的索引是基于原始列值构建的，当你在排序字段上应用了函数，索引里的值和函数计算后的值之间没有直接的顺序对应关系，查询优化器只能放弃索引排序，转而执行文件排序（filesort）。

这个坑我见得太多了。很多人在做“按日期格式化后的字符串排序”这种需求时，下意识就直接套函数，完全没意识到性能代价。更合理的做法是直接在原始列上排序，如果需要格式化展示，放到SELECT层做，或者干脆在应用层格式化：

SELECT id, name, DATE_FORMAT(created_at, '%Y-%m-%d') AS date_str FROM user ORDER BY created_at DESC;

注意这里的DATE_FORMAT只出现在SELECT列表里，ORDER BY用的是原始列created_at，索引排序优化可以正常工作。

2. 分页机制的深层逻辑：LIMIT的代价往往被人为低估

2.1 LIMIT offset的底层扫描原理

分页的写法大家再熟悉不过了：

SELECT * FROM user ORDER BY id LIMIT 10, 20;

这条SQL的意思是跳过前10条记录，然后返回接下来的20条。表面上看，MySQL只需要找到第11条到第30条数据就行了，但实际执行过程完全不是这样。MySQL的InnoDB存储引擎在执行这条SQL时，会从第一条记录开始扫描，把前10条数据依次读取、判断、然后丢弃，一直数到第11条才开始真正返回数据。更麻烦的是，它不会在第30条就停下来，而是会一直扫描到第30条为止。

所以LIMIT offset, count的代价是随着offset的增长而线性增长的。当offset是10000时，MySQL要白白扫描和丢弃10000条记录；当offset是1000万时，它就要扫描和丢弃1000万条记录。这个过程中所有被丢弃的记录都要经历一次完整的数据读取，I/O开销非常大。

我用一个真实的压测数据说明问题。在单表500万行数据的情况下，id是主键，执行：

SELECT * FROM user ORDER BY id LIMIT 99990, 10;

这条SQL的耗时大约在150到250毫秒之间，看着好像还行对吧。但当你把offset拉到500万：

SELECT * FROM user ORDER BY id LIMIT 4999990, 10;

同样的表结构和数据量，耗时暴涨到5秒以上。这就是深分页问题的直观感受，第500万页的10条数据，和第一页的10条数据，返回结果数量完全一样，但执行代价天差地别。

2.2 排序加LIMIT的组合效应

当分页遇到排序，情况更复杂。MySQL在处理ORDER BY + LIMIT的组合时，有两种不同的执行策略。

一种情况是，如果ORDER BY的字段能被索引覆盖，MySQL会按照索引顺序逐条读取数据，读到第offset + count条就停下来，这个过程中读取的数据量是可控的。

另一种情况是，如果ORDER BY字段无法使用索引，MySQL就必须先把所有满足WHERE条件的记录全部找出来，完整地做一次排序，然后才从排序结果中取出第offset到第offset+count条。这意味着排序的代价和LIMIT无关，即使你只需要10条数据，MySQL也会把所有数据都排序一遍。

这个差异在数据量大的时候非常致命。还是以那张500万行的user表为例，如果对created_at做ORDER BY，而created_at上没有索引：

SELECT * FROM user ORDER BY created_at LIMIT 100, 10;

这条SQL的实际执行过程是：先全表扫描找出500万条记录，对500万条记录做文件排序，然后丢弃前100条，返回后面的10条。你只想要10个结果，数据库却干了500万条排序的活。这就是排序和分页组合后最容易被低估的代价。

2.3 ORDER BY主键和OFFSET的微妙关系

还有一种常见写法是ORDER BY id LIMIT offset, count。因为主键通常自带聚簇索引，所以这个排序本身是有序的，不需要额外的排序操作。但这不代表深分页问题就消失了，OFFSET带来的扫描和丢弃依然存在，只是排序的开销省掉了。

我之前做过一个优化案例，线上有个报表查询接口，每次请求都翻到很后面的页，接口响应时间稳定在4到5秒。加了一个二级索引之后，响应时间降到30毫秒左右。那为什么一个二级索引就能带来这么夸张的优化？

因为原来的查询走的是全表扫描，然后对结果集做文件排序，再取数据。加了二级索引之后，MySQL可以直接按索引的顺序去扫描，不需要额外的排序步骤。这个案例让我深刻体会到，很多分页慢的根因不是LIMIT本身，而是排序方式太昂贵。

3. 文件排序的两条路线：双路排序和单路排序的取舍

3.1 为什么MySQL不能直接用索引时要用filesort

当ORDER BY的字段无法使用索引时，MySQL会启用文件排序（filesort）。这个名字容易让人误解，以为一定涉及磁盘文件。实际上，filesort的意思是“在排序缓冲区之外用额外的排序算法来完成排序”，它可能在内存里完成，也可能真的需要写磁盘，完全取决于数据量是否超出sort_buffer_size的容量。

InnoDB和MyISAM在执行filesort时的策略不太一样，但总体流程是相同的：MySQL从表中读取满足条件的记录，提取排序字段和必要的行指针，放入排序缓冲区，在缓冲区里做排序，如果缓冲区不够就分批排序并把中间结果写入磁盘临时文件，最后合并所有临时文件得到最终的有序结果。

3.2 sort_buffer_size与max_length_for_sort_data参数调优

filesort内部有两种算法：双路排序（old）和单路排序（new）。

双路排序的做法是：第一次读取记录时，只把排序字段和行ID放入排序缓冲区，排序完成后，再根据行ID回表读取完整记录。这种方式在排序时占用的内存少，但需要两次读取数据，回表次数多，I/O开销大。

单路排序的做法是：第一次读取记录时，直接把排序字段和查询要返回的所有字段全部放入排序缓冲区，排序完成后直接返回结果，不需要回表。这种方式只读一次数据，但缓冲区占用空间大，容易触发缓冲区溢出。

MySQL原本的逻辑是，当查询的字段总长度不超过max_length_for_sort_data时，使用单路排序；超过时退化为双路排序。这个参数在MySQL 8.0.x版本中已经被标记为废弃，但在一些老版本和分支版本里依然有效。如果你在维护老系统，遇到排序慢的问题，可以看看这个参数是否设置得过小，导致大量排序都走了双路排序。

sort_buffer_size这个参数也需要谨慎对待。它设置的是每个会话的排序缓冲区大小，不是全局共享的。如果你的数据库有100个并发连接，每个连接的sort_buffer_size设置为256MB，那么理论上最多会占用25.6GB内存。这个参数过大会导致内存耗尽，过小会导致频繁写临时文件，需要在两者之间找到平衡。根据我的实践经验，一般设置在2MB到8MB之间比较合理，具体的值需要结合服务器的物理内存和并发量通过压测来确定。

3.3 通过EXPLAIN识别排序优化状态

分析一条排序查询的优化空间，最直接的方法是看EXPLAIN的输出。在Extra列里，如果出现Using filesort，说明排序没有用到索引；如果出现Using index，说明查询走了覆盖索引；如果出现Using temporary，说明涉及临时表，一般和GROUP BY或DISTINCT联合排序时会出现。

举几个典型场景。第一条：

EXPLAIN SELECT * FROM user WHERE age > 18 ORDER BY created_at;

如果Extra列显示Using filesort，说明创建时间字段上没有合适的索引，排序无法优化。

第二条：

EXPLAIN SELECT id, name FROM user ORDER BY id;

如果Extra列显示Using index，说明查询利用主键的聚簇索引完成了排序，同时只返回索引中包含的列，不需要回表。

第三条：

EXPLAIN SELECT id, name FROM user ORDER BY name;

如果name上有二级索引，Extra列可能显示Using index，这时排序是零成本的，因为二级索引本身就是有序的。

我建议每个开发人员在写排序和分页SQL之后都养成跑一下EXPLAIN的习惯。这个习惯养成之后，你会发现很多性能问题在开发阶段就能发现，根本不需要等上线后在压测阶段才暴露。

4. 深分页实战优化：几套方案用下来，我推荐这样组合

4.1 延迟关联方案：先定位主键，再回表取数据

延迟关联是处理深分页最经典的方案之一。核心思想是：先用覆盖索引快速拿到目标记录的主键ID，再用这些主键ID去关联原表取完整数据。

SELECT u.* FROM user u INNER JOIN ( SELECT id FROM user ORDER BY created_at DESC LIMIT 99990, 10 ) tmp ON u.id = tmp.id;

这条SQL的奥妙在于，内层子查询只返回主键id和排序字段，完全不涉及回表操作，可以在索引上直接完成排序和分页。因为二级索引的叶子节点包含主键值，加上created_at上建了索引的话，这个子查询可以用到索引排序，而且只需要扫描一个索引结构，代价远小于全表扫描。拿到这10个主键ID之后，再用JOIN回原表取完整记录，总共只需要回表10次。

我实测过，在500万行数据的表上，用这个方案做第50万页的分页，响应时间从5秒以上降到了200毫秒以内，性能提升非常明显。

4.2 游标分页方案：用WHERE范围替代OFFSET

游标分页是另一种非常实用的方案，它的核心逻辑是抛弃OFFSET，改用WHERE条件来定位起始位置。比如按id排序的分页，第一页取id小于100的记录，第二页就取id小于上一页最小id的记录。

-- 第一页 SELECT * FROM user WHERE id > 0 ORDER BY id LIMIT 10; -- 第二页，上一页最后一条记录的id是10 SELECT * FROM user WHERE id > 10 ORDER BY id LIMIT 10; -- 第三页 SELECT * FROM user WHERE id > 20 ORDER BY id LIMIT 10;

这种方案的性能优势非常明显，因为它可以利用主键索引直接定位到起始位置，然后向后扫描count条记录即可，完全没有扫描和丢弃的过程。不管翻到第几页，查询代价都是固定的。

但游标分页有一个天然限制：它只支持按顺序翻页，不支持跳页。如果你的产品需求里有“跳到第100页”这种功能，游标分页就无能为力了，只能回到OFFSET方案，或者结合覆盖索引来优化。

4.3 覆盖索引方案：让排序字段和查询字段都集中在索引内

覆盖索引方案的核心是让查询要返回的所有字段都包含在同一个索引中，这样MySQL就不需要回表取数据，直接在索引上完成排序和分页，然后返回结果。

SELECT id, name, created_at FROM user ORDER BY created_at DESC LIMIT 1000, 10;

如果为created_at创建一个联合索引（created_at, name），并且查询只返回id、name、created_at这三个字段，那这个查询可以完全通过索引完成，Extra列显示Using index，不需要回表，排序也走索引顺序。

覆盖索引方案的局限性在于：你能返回的字段被限制在索引列范围内。如果你需要SELECT *或者需要返回大量不在索引中的字段，覆盖索引就爱莫能助了。这种情况下，可以和延迟关联方案配合使用，先用覆盖索引拿到主键ID，再回表取完整数据。

4.4 各方案对比与选型建议

我把这几种方案放在一起做了个对比：

我的实践经验是：对于列表页这种可以接受顺序翻页的场景，优先用游标分页，性能最稳定；对于管理后台这种必须支持跳页的场景，用延迟关联加OFFSET；对于数据量本身就很小（比如一万行以下）的场景，直接OFFSET就行，别过度设计。

5. 高频场景下的稳定性优化：排序不只是SQL层面的问题

5.1 排序查询在连接池和并发下的表现

排序和分页查询在高并发场景下还有一个容易被忽视的问题，就是对数据库连接池的占用时间。

一个慢排序查询执行5秒，如果应用层的连接池有50个连接，理论上10个这样的慢查询就能把连接池占满，其他所有请求全部排队等待。这时候你会发现数据库CPU使用率其实不高，但接口全部超时，问题就出在连接被长时间占用上。

我遇到过这样一个线上事故。某个报表接口的SQL里有ORDER BY和LIMIT，数据量增长到一定规模后，单次查询耗时从100毫秒飙升到7秒。接口的QPS不算高，只有20左右，但每个请求占用连接7秒，导致连接池很快耗尽，所有依赖这个数据库的业务全部受到影响。最后通过给排序字段加上索引优化了排序，再配合延迟关联改写了分页逻辑，单次查询耗时降到了80毫秒，故障瞬间解除。

5.2 临时表与排序缓冲区的内存压力

排序操作对内存的消耗同样需要关注。前面提到过，每个连接都有独立的sort_buffer_size，当大量并发排序发生时，内存压力会急剧上升。

如果MySQL在排序过程中发现数据量超出sort_buffer_size，就会使用磁盘临时文件。磁盘临时文件中转的数据越多，排序就越慢，同时会带来大量的磁盘I/O。

在出现这类问题时，可以从两个方向入手。第一，从应用层减少不必要的排序，比如能通过索引排序的就别让MySQL自己排序。第二，从参数层适度调大sort_buffer_size，但要结合并发数计算内存峰值，避免设置过大导致OOM。

5.3 弹性伸缩与读写分离下的排序设计

最后聊一下架构层面的考虑。在读写分离的架构里，排序和分页查询一般应该路由到从库执行，减轻主库压力。但要注意从库的数据延迟问题，一个顺序翻页的场景如果数据延迟了，用户可能看到重复或缺失的记录。

另外，分页查询的深度在架构层面也要做限制。很多业务场景中，用户实际上根本不会翻到第10万页，让产品层面限制最大翻页深度，比在数据库层面无限优化更实在。比如配合搜索引擎做聚合搜索时，限制最大返回页数就是常见的做法。

6. 常见问题与排查技巧实录

6.1 ORDER BY字段顺序导致结果不稳定

先说一个很常见的现象：同样一条SQL，执行两次，返回的记录顺序不一致。尤其是在没有明确ORDER BY的情况下，MySQL并不保证返回结果的顺序。很多开发以为SELECT * FROM user不加ORDER BY就会按主键排序，这个理解在小数据量下碰巧成立，但一旦涉及并行查询、临时表或者分表场景，顺序就完全不可控了。

所以第一个实践建议是：任何需要稳定顺序的查询，都必须显式写ORDER BY。别指望数据库给你默认的排序。

6.2 中文排序结果不符合预期

中文排序乱序的问题也经常出现。MySQL默认的字符集和排序规则是基于拼音还是基于Unicode，取决于你建表时选的collation设置。

如果你的表使用utf8mb4_unicode_ci，中文排序会按Unicode编码顺序排列。如果需要按拼音排序，一个办法是使用CONVERT函数：

SELECT * FROM user ORDER BY CONVERT(name USING gbk);

把姓名转成GBK编码后再排序，中文会按拼音顺序排列。这个办法有性能代价，没法用索引，适合数据量不大的场景。数据量大时，更合理的做法是在应用层排序，或者在写入时就存储一个拼音索引字段。

6.3 分页记录重复或缺失

分页时出现重复或缺失记录，绝大多数情况是因为ORDER BY的字段不是唯一的，也就是存在相同排序值的记录。比如ORDER BY created_at，而同一秒内有多条记录，MySQL在排序后如果还涉及并发写入或从库延迟，翻页时看到的记录就可能出现偏差。

解决办法很简单：在ORDER BY的字段后面追加一个唯一字段，比如主键ID：

SELECT * FROM user ORDER BY created_at DESC, id DESC;

加上主键作为第二排序键之后，整个结果集的顺序就完全确定了，分页不会出现重复或缺失。

6.4 WHERE条件里的隐式类型转换

还有一个容易忽略的坑：WHERE条件和ORDER BY字段混用时的索引失效。最经典的场景是字段是varchar类型，但查询时传入了整数类型的值，MySQL会做隐式类型转换，导致索引失效，排序自然也就走了filesort。

-- phone字段是varchar类型 SELECT * FROM user WHERE phone = 13800138000 ORDER BY created_at LIMIT 10;

正确的写法是：

SELECT * FROM user WHERE phone = '13800138000' ORDER BY created_at LIMIT 10;

多写一对引号，就能避免隐式转换，让索引正常工作。

6.5 快速定位慢排序SQL的方法

最后分享一个排查慢排序的实操方法。开启慢查询日志，设置一个合理的阈值，比如500毫秒：

SET GLOBAL slow_query_log = ON; SET GLOBAL long_query_time = 0.5;

然后定期分析慢查询日志。看到以下特征的SQL，优先检查排序和分页相关的问题：语句里带ORDER BY和LIMIT，扫描行数远大于返回行数，出现的频率较高，消耗的时间较长。

用EXPLAIN确认执行计划后，如果发现Using filesort，就看看能不能通过加索引来优化排序；如果发现offset巨大，就考虑换游标分页或者延迟关联。

我自己在实际排查中养成了一个习惯，排序和分页的SQL写完之后先看三件事：ORDER BY的字段有没有索引支持，OFFSET有没有可能到很大的值，LIMIT后面的返回字段能不能被索引覆盖。这三件事都确认过了，这条SQL基本就能在绝大多数场景下保持稳定。

排序和分页看似基础，但它把索引、内存、磁盘I/O、执行计划这几个核心要素全部串联在一起。把这块吃透了，你再去看MySQL其他的性能问题，会有一种豁然开朗的感觉。