4.2 慢查询、索引与数据库优化

4.2 慢查询、索引与数据库优化

把「站点一卡」翻译成「哪条SQL 慢」，再翻译成「缺哪个索引」。

4.2.1 先装上放大镜：慢查询日志

慢查询日志（MySQL 把执行时间超过阈值的 SQL 记下来的日志文件）是最硬的证据。临时开启（重启失效）：

SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;
SET GLOBAL slow_query_log_file = '/www/server/data/mysql-slow.log';

长期生效写进 my.cnf 的 [mysqld] 段：

[mysqld]
slow_query_log = ON
long_query_time = 1
log_queries_not_using_indexes = ON
max_connections = 150

读日志用 mysqldumpslow，盯 Query_time、Lock_time 三列：

mysqldumpslow -s t -t 20 /www/server/data/mysql-slow.log

-s t 按总耗时排序，-t 20 取前 20 条。

4.2.2 EXPLAIN 逐列读懂

把慢日志里最慢的那条前面加 EXPLAIN，再看结果：

列	含义	该看什么
`type`	访问类型，从优到劣：const、eq_ref、ref、range、index、ALL	出现 `ALL` 就是全表扫描，必须优化
`possible_keys`	优化器认为可能可用的索引	有候选但 `key` 为空，说明统计信息过期
`key`	实际选中的索引	为 `NULL` 时要重点排查
`key_len`	使用的索引长度（字节）	偏小可能只用了索引左端一部分
`rows`	预估要扫描的行数	与实际差一个数量级说明统计信息过期
`Extra`	额外信息	`Using filesort`、`Using temporary` 都要处理

ALL 的处理方向是按 WHERE、JOIN、ORDER BY 涉及的列补索引。Using filesort 不等于「磁盘文件排序」，也可能只是内存里排了一下，判断标准看 rows：几千行基本无感，几十万行才慢。

4.2.3 Discuz! 核心表的索引要点

Discuz! X5.0 共 258 张表（表前缀安装时可改，官方文档写作 cdb_，本文统一用 pre_），日常最常被拖慢的是这四类表：

表	作用	索引要点
`pre_forum_post`	帖子正文表，全站最大	主键 `pid`；`tid`+`invisible` 组合索引必须有
`pre_forum_thread`	主题表，承载列表页	主键 `tid`；`fid`+`displayorder`+`lastpost` 组合索引决定排序性能
`pre_member`	会员主表	主键 `uid`；`username` 上的唯一索引用于登录
`pre_common_member_count`	会员发帖、金币等计数	联合唯一索引（`uid`+`day`）撑起积分统计

「该加没加的索引」常见清单，多出现在自定义插件或自己写的查询里：

联合索引只加了第一列，缺 ORDER BY 的后续列。
字符串列参与 LIKE '%关键词%'，左模糊用不上索引。
排序字段与过滤字段在不同列上，Using filesort 常客。
OR 连接两个条件，其中一边没索引。
COUNT(*)、DISTINCT 上没索引，退化成全表。
时间字段没索引（dateline、lastpost 这类）。

4.2.4 完整的索引优化示例

「查询慢 → 加索引 → 验证」全过程如下。执行前先备份，字段名以官方数据字典为准。

-- 1. 看执行计划
EXPLAIN SELECT p.pid, p.subject, p.dateline, m.username
FROM pre_forum_post p
INNER JOIN pre_member m ON m.uid = p.authorid
WHERE p.tid = 128391 AND p.invisible = '0'
ORDER BY p.pid ASC LIMIT 20;

-- 2. 现状：主键在，但按 tid 过滤走不到合适索引（key 为 NULL 或 type 为 ALL）

-- 3. 补联合索引：过滤列在前，排序列紧跟其后
ALTER TABLE pre_forum_post ADD INDEX idx_tid_invisible_pid (tid, invisible, pid);

-- 4. 再 EXPLAIN 一次，type 应从 ALL 变成 ref 或 range，Using filesort 消失

-- 5. 验证耗时（SQL_NO_CACHE 避开缓存，跑两次取第二次更准）
SELECT SQL_NO_CACHE p.pid FROM pre_forum_post p
WHERE p.tid = 128391 AND p.invisible = '0'
ORDER BY p.pid LIMIT 20;

主题列表页和会员计数同理：ALTER TABLE pre_forum_thread ADD INDEX idx_fid_displayorder_lastpost (fid, displayorder, lastpost);、ALTER TABLE pre_common_member_count ADD INDEX idx_uid_day (uid, day);

加索引会让写入变慢、锁表，有活跃发帖时安排在低谷。索引也不是越多越好，每个都占磁盘、让写入多维护一份。

4.2.5 深翻页：大 OFFSET 的坑

LIMIT 900000, 20 意味着数据库要先扫过 90 万行再丢掉。帖子表到百万级后，用户点「下一页」点到 500 页，慢到怀疑人生。两条解法：

游标分页——记住上一页最后一条记录的排序值，下一页用它做起点：

-- 上一页最后一条 pid = 902345
SELECT p.pid, p.subject FROM pre_forum_post p
WHERE p.fid = 12 AND p.tid &lt; 902345
ORDER BY p.tid DESC LIMIT 20;

延迟关联——先用覆盖索引查主键，再回表取详情，把回表代价推迟到只剩 20 行：

SELECT t.* FROM pre_forum_thread t
INNER JOIN (
    SELECT tid FROM pre_forum_thread
    WHERE fid = 12 AND displayorder &gt;= 0
    ORDER BY displayorder DESC, lastpost DESC
    LIMIT 20 OFFSET 900000
) AS x ON t.tid = x.tid
ORDER BY t.displayorder DESC, t.lastpost DESC;

前端要配套改造：页码按钮换成「上一页 / 下一页」。「只看最新」和「只看未读」本来就不需要深翻页。

4.2.6 连接数与 2C4G 的现实

max_connections 不是越大越好，每个连接都吃内存和调度开销，2C4G 设成 500 反而不如 150 稳。更常见的事故是连接数被慢查询占满，新请求拿不到连接，页面直接报 Too many connections。设 200 左右比较稳，同时盯：

SHOW STATUS LIKE 'Threads_connected';   -- 当前连接数
SHOW VARIABLES LIKE 'max_connections';  -- 上限
SHOW PROCESSLIST;                       -- 谁在占着

2C4G 上 innodb_buffer_pool_size 给到内存的 50%~60% 比较合理（1.5G 左右）。tmp_table_size 和 max_heap_table_size 小于结果集时，MySQL 会把临时表写到磁盘，这就是 Using temporary 慢得离谱的原因。

4.2.7 一周数据库优化动作清单

按这个顺序走一遍，多数站点的慢查询能被解决大半：

天	动作	验证方式
周一	开慢查询日志，`mysqldumpslow -s t` 排出前 20 条	有报表可查
周二	对前 2 名慢查询逐条 `EXPLAIN`，标出 `type=ALL` 和 `Using filesort`	记进运维笔记
周三	给 `pre_forum_post`、`pre_forum_thread` 补联合索引	`key` 不为 NULL
周四	偏差大时跑 `ANALYZE TABLE` 更新统计信息	`rows` 与实际接近
周五	调 `innodb_buffer_pool_size`、`tmp_table_size`	观察 24 小时
周六	深翻页改为游标或延迟关联	第 50 页仍秒开
周日	备份数据库，比对索引改动效果	出前后耗时对比