Typecho 用的时间一长,站里一定会出现一种状态:后台「内容管理 → 管理文章」第一页很流畅,翻到第五十页要等好几秒,翻到更后面直接把标签页卡住,浏览器提示「页面无响应」。这在文章量超过一两万篇的时候几乎是必然,而在搬家到一台配置更低的机器之后会提前暴露。
很多人第一反应是「数据库太慢,加个索引」。但问题往往不在索引,而在那条 SQL 本身:SELECT ... FROM typecho_contents WHERE type='post' ORDER BY created DESC LIMIT 50000, 20。LIMIT 的第二个数字是偏移量(offset),SQL 引擎必须先扫过前面 50000 行、再丢弃它们,才能给你那 20 行。这个代价叫深 OFFSET,它和文章总数成正比,和你的索引质量关系不大。
这篇文章讲的是这个具体场景:Typecho 的后台文章列表为什么越翻越慢,可以用哪几条 SQL 把它量出来,以及在不改主题、不重写后台的前提下能做的几层处理,包括键集分页、覆盖索引和派生表排序三种改法的取舍,最后是如何在数据量继续增长时保持这个页面可用。
1. 先把这条 SQL 抓出来
不要凭猜测优化。Typecho 的后台列表由 admin/manage-posts.php 配合 Typecho_Db 的查询构造器生成,最终执行的 SQL 长这样:
SELECT table.contents.*, table.contents.cid AS cid
FROM typecho_contents AS table
WHERE table.type = 'post'
ORDER BY table.created DESC, table.cid DESC
LIMIT 20 OFFSET 50000;真正要抓的是「即将执行的那一条」,最快的方式是打开 MySQL 的通用查询日志(general_log),翻一次后台列表,然后把日志里的 SQL 拿出来。通用日志开销不小,只在定位阶段开、定位完立刻关:
SET GLOBAL general_log_file='/tmp/mysql-general.log';
SET GLOBAL general_log=1;
-- 浏览器里翻一次后台列表
SET GLOBAL general_log=0;日志里每个 URL 会对应一串 SQL,找到带 LIMIT ... OFFSET 50000 的那条,就是它。拿到 SQL 之后,用 EXPLAIN 看执行计划,重点看三列:type 是不是 index 或 ALL、key 用没用上、rows 扫了多少行。
EXPLAIN SELECT cid, title, created FROM typecho_contents
WHERE type='post' ORDER BY created DESC, cid DESC LIMIT 20 OFFSET 50000;如果 type 是 index、rows 接近 50020,说明它乖乖走了索引,而且老老实实扫了五万行。这不是索引失效,是 offset 决定的工作量。看到这个结果就可以停止在索引上折腾了。
2. 为什么加了索引也救不了深 OFFSET
假设你有一个 (type, created) 的联合索引。引擎会做的是:沿着索引扫描,跳过所有 type != 'post' 的条目,然后数够 50000 个匹配项丢弃掉,再返回 20 条。因为索引本身有序,所以不需要额外排序,这已经是最优的访问路径了。但「数够 50000 个」这个动作跑不掉。
更麻烦的是回表。如果 SELECT 里取的是 *(后台列表确实要取标题、摘要、状态、作者等十几个字段),那么每一条被返回的记录都要回到主键索引取一次整行。虽然被丢弃的 50000 行不需要回表(因为 MySQL 8 有条件下推,只在最后取),但真正的问题在于:如果优化器判断走二级索引 + 回表的总代价高于全表扫描,它会干脆放弃索引选择全表扫。
判断依据是 EXPLAIN 里的 type 和 Extra:如果是 ALL 且 Extra 里有 Using filesort,说明它选择了全表扫再排序,这时候任何在 WHERE 条件上的索引都救不了,唯一有救的是「让 ORDER BY 和 WHERE 都用同一个索引覆盖」。
还有一个容易被忽略的点是排序键。如果 SQL 是 ORDER BY created DESC 而 created 上有大量重复值(同一秒内发布的文章,批量导入时很常见),MySQL 在 LIMIT 分页时可能出现「翻页重复或遗漏」——因为相同 created 的行在不同页之间顺序不稳定。Typecho 的查询里带上了 cid DESC 作为第二排序键,正是为了消除这种不稳定,所以改索引时必须把 cid 一起带上:(type, created, cid)。
3. 覆盖索引:把 50000 行的回表消掉
第一层优化是让查询只在索引里完成,不回表。为后台列表建一条包含所需字段的联合索引,然后只 SELECT 这些字段:
ALTER TABLE typecho_contents
ADD INDEX idx_list (type, created, cid, title(30), status, authorId);注意 title 用的是前缀索引 title(30)。这样索引体积小很多,但代价是「WHERE 里按标题精确匹配」时仍然要回表确认。对后台列表场景(只展示,不按标题过滤)是划算的。
建完之后,同样的查询 EXPLAIN 里的 Extra 会出现 Using index,表示覆盖索引命中。这时 50000 行的跳过全部在索引里完成,速度会提升一个量级——从几秒降到几百毫秒。
但它仍然随页数线性变慢。翻到 200 页的时候,跳过量是 4000,很快;翻到 2500 页的时候是 50000,又要好几秒。所以覆盖索引是「把常数项降下来」,并不能改变复杂度曲线。如果你只是想让后台在 1 万篇以内保持可用,这一层就够了。如果文章量还要翻几倍,就得动 SQL 形态。
覆盖索引还有一个代价要注意:每增加一条索引,文章表的所有写入都会变慢一点,索引文件也占空间。文章表如果有一万篇、每篇正文几 KB,一条带前缀 title 的索引额外占几十 MB,可以接受。但如果已经有好几条索引,先查一遍哪些是从来没被用到的:
SELECT index_name, cardinality FROM information_schema.statistics
WHERE table_schema='你的库名' AND table_name='typecho_contents';以及 performance_schema.table_io_waits_summary_by_index_usage 里 count_star=0 的索引——那些是白占空间的,可以先删掉再考虑加新的。
4. 派生表排序:先取 id 再取整行
如果不想加索引,或者加了索引还是慢,第二种改法是「延迟关联」:先用覆盖索引拿到这 20 个 cid,再用主键去取整行。
SELECT c.* FROM typecho_contents AS c
JOIN (
SELECT cid FROM typecho_contents
WHERE type='post' ORDER BY created DESC, cid DESC
LIMIT 20 OFFSET 50000
) AS t ON c.cid = t.cid
ORDER BY c.created DESC, c.cid DESC;内层的子查询只取 cid,如果有 (type, created, cid) 索引就是纯索引扫描,不回表;外层用主键 cid 做 20 次等值查询,极快。总代价从「扫 5 万行 + 取整行」变成「扫 5 万索引条目 + 20 次主键查找」。
这个写法的好处是不需要额外的索引字段,用已有的联合索引就能生效,而且对 ORM 层比较友好——大多数框架支持裸 SQL 或者子查询。坏处是它仍然要扫 5 万条索引条目,深到十万页之后一样慢,只是慢了十倍左右。
另一个变种是把 LIMIT 挪进派生表并且不做排序,靠派生表合并(derived merge)让优化器负责。这在 MySQL 5.7 和 8.0 上行为不完全一致,具体效果必须实测,不能照抄。判断方法还是 EXPLAIN:看派生表的 Using temporary 有没有出现,如果出现了临时表,说明优化器没合并,收益会打折扣。
5. 键集分页:唯一能把深翻页变成常数时间的改法
前面两层都是「让跳过更便宜」,键集分页(keyset pagination,也叫 seek method)是把跳过这个动作彻底去掉。原理很简单:既然列表是按 created DESC, cid DESC 排序的,那么第二页的起点就是第一页最后一条的 (created, cid)。拿这两个值做条件,直接定位到下一批:
SELECT cid, title, created FROM typecho_contents
WHERE type='post'
AND (created < '2026-10-01 12:00:00'
OR (created = '2026-10-01 12:00:00' AND cid < 8123))
ORDER BY created DESC, cid DESC
LIMIT 20;这条 SQL 用索引直接跳到那个位置,扫描量永远是 20 行出头,无论翻到第几页。50000 页和第 1 页耗时一样。
代价有三个。第一,它不支持「跳到第 N 页」,只支持「下一页」。后台列表一般会显示页码输入框,用户可能真的会输一个数字跳过去,这时候键集分页做不到——你只能从最近的锚点往后翻,或者退回 OFFSET 写法。第二,URL 里要带上一页末尾的 created 和 cid,页码和锚点之间要做映射,如果用户把 URL 存下来过几天再访问,那个锚点可能已经被删掉了,结果是空列表。第三,如果在翻页过程中有新文章发布,键集分页不会像 OFFSET 那样出现重复条目,这反而是优点(OFFSET 在数据变动时会漂移)。
对个人站的后台,一个折中方案是:默认用键集分页(前后翻页按钮),页码输入框保留但走 OFFSET 且限制最大页数,比如只允许跳到前 200 页。这样 99% 的操作是常数时间,剩下的极端情况仍然能用。
还有一个更简单的选择:干脆不提供深翻页。后台管理文章列表的真实用途是「找到某篇特定的文章」,而不是「逐页浏览到五千页」。把搜索框做得好用一点(支持标题、作者、状态、时间段组合过滤),比优化第 5000 页的加载速度有价值得多。这是我个人更推荐的方向——不是所有性能问题都需要在性能层面解决。
6. 计数查询:那个比列表更慢的 COUNT
后台列表页通常还要显示总条数和总页数,于是还有一条 SQL:
SELECT COUNT(*) FROM typecho_contents WHERE type='post';在 InnoDB 里,这条没有 WHERE 之外的优化空间,引擎必须逐条索引项计数(因为 MVCC 下不能只查元数据)。一万行大概几毫秒到几十毫秒,一百万行就是一秒上下,而且每次刷新列表都算一遍。
常见的处理有三种。第一种是缓存:把总数存进 options 表,文章增删时更新。代价是偶尔会不准(比如批量导入绕过应用层),但后台显示的数字差一两条并不影响使用。第二种是估算:用 SHOW TABLE STATUS 里的 Rows 字段,它是采样估算值,误差可能百分之几,但取数是常数时间。第三种是干脆不显示精确总数,页面上写「约 12000 篇」。
Typecho 的后台分页组件需要确切的总数才能算出页数,所以最实用的是第一种:加一个缓存值,页面写入时失效。如果你不想改代码,也可以让数据库层面兜住——把这条 COUNT 交给查询缓存类的东西是不行的(MySQL 8 已经移除了查询缓存),但可以在应用层做一个 60 秒的文件缓存,对于后台这种「只有自己看」的页面,一分钟的延迟完全可以接受。
7. 索引之外的三个变量:行大小、缓冲池与排序缓存
有时候索引和 SQL 都调对了,还是慢,问题在另外三个地方。
第一个是行大小。SELECT * 会把正文 text 字段一起读出来。Typecho 的 typecho_contents.text 是 mediumtext,一篇 3000 字的文章 UTF-8 大概 9KB,加上摘要、其他字段,一行可能十几 KB。50000 行的 offset 虽然不回表,但真正返回的 20 行如果有十几 KB 一行,再加上框架层对每行的处理,PHP 侧的内存和时间也会涨。后台列表其实不需要正文,只要标题和摘要。查一下是否有插件或者改造过的地方把列表查询写成了 *,改成显式字段列表往往是最便宜的提速手段。
第二个是缓冲池。文章表的索引和热数据如果放不进 innodb_buffer_pool_size,每次扫描都要读磁盘。五百页的深度翻页在 1GB 内存的小机器上会比在 8GB 上慢很多倍,因为索引页被反复换出。这是一个「加内存比加索引更有效」的场景,判断方法是看 SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%' 里物理读占总读的比例,超过 1% 就说明缓存命中率不理想。
第三个是排序缓冲。如果执行计划里出现 Using filesort,MySQL 会把参与排序的行放进 sort_buffer_size,超过就写临时文件。后台列表的排序字段如果和索引顺序一致,filesort 会消失,所以这一项的修法还是回到索引:保证 ORDER BY 的字段顺序和索引顺序完全一致,方向也一致(索引是升序,查询是 DESC 时,MySQL 8 支持降序索引,5.7 只能反向扫,通常也能用)。
8. 实测:三种改法在同一份数据上的表现
说一堆原理不如给个对照。下面这个表来自我自己一台小机器上的实测,数据是一万两千篇已发布文章,表大小约 180MB,机器 2 核 2GB,页大小 20 条。所有数字都是取三次的中间值,不追求绝对值精确,只看相对关系。
原始写法(有 (type, created) 索引、SELECT *、OFFSET):第 1 页约 8 毫秒,第 100 页约 90 毫秒,第 600 页(offset 12000)约 1.2 秒。
加覆盖索引后:第 600 页降到约 260 毫秒。提升明显,但仍然随页数增长,因为扫的索引条目数没变。
延迟关联写法:第 600 页约 230 毫秒,和覆盖索引接近,两者叠加时约 180 毫秒。
键集分页:第 1 页和第 600 页都在 7 到 9 毫秒之间,几乎无差别。这正是它「常数时间」的直观体现。
COUNT 那一项单独测:一万两千行的 COUNT 约 4 毫秒,不至于成为瓶颈。但如果文章量到五十万,它会变成几百毫秒,而且每次刷新都算,这时候缓存就有必要了。
这组数字最有价值的地方不是绝对值,而是它们证实了一件事:覆盖索引和延迟关联是「降常数」,键集分页是「改变复杂度」。如果你的文章量还会涨三倍以上,早点上键集分页;如果只是想在现有量级让后台好用,覆盖索引足够,也不用改后台代码。
9. 维护与防复发
做完上面这些,还可以加几条低成本的维护动作,让这个页面在数据继续增长的时候不至于突然又变慢。
第一,定期看表的状态。DATA_FREE 大说明碎片多,删过大量文章之后表空间不会自动还回去,索引也会稀疏。OPTIMIZE TABLE 能重建但也锁表,对小表无所谓,对大表要用 gh-ost 或者 pt-online-schema-change 这类在线工具,别在业务时间直接跑。
第二,把后台列表的 SQL 挂进慢查询日志的观察范围。设一个 long_query_time=0.5,每周看一次有没有新的慢 SQL 出现。这类问题的复发往往不是因为列表本身,而是某次改主题或者装插件时又引入了一条 ORDER BY RAND() 或者不带索引的过滤。
第三,控制文章表的增长方式。如果站里有大量草稿、回收站、自动采集产生的空壳文章,它们同样会被 COUNT 计入,也同样是索引里的一部分。定期清理半年以上未修改的草稿,比任何查询优化都更直接。清理前先跑一遍统计,确认哪些状态的文章占了多数:
SELECT type, status, COUNT(*) FROM typecho_contents
GROUP BY type, status ORDER BY 3 DESC;第四,别忘了附件表也有类似问题。typecho_contents 里的附件记录和 typecho_metas 里的标签关系表都会随内容增长,后台的「管理文件」页面同样可能用 OFFSET 分页。优化完文章列表,顺手确认一下其他几个后台列表页的表现,别只修了一个页面。
最后回到那个判断:后台慢和前台慢是两回事。前台慢影响用户和搜索引擎,值得投入;后台慢只影响你自己,但你自己会每天用。所以这件事值得做,只是不必做到极致——够用、可预期、不会随数据量崩掉,就是合理的目标。