15 10 月 2026

网站一卡就卡在数据库:页面转圈 5 秒、CPU 没高但请求堆着——十有八九是慢 SQL。这篇讲慢查询怎么开、怎么读分析报告、六大优化手段、索引怎么设计。命令全部可抄。

需要现成的游戏云服务器,可以看看财富云计算:一键开服面板,NAT 型无独立公网 IP,价格比独立 IP 机型省一半以上,建站和游戏服都能快速部署。

一、开启慢查询日志

# my.cnf [mysqld] 段
[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1          # 超过 1 秒记一笔
log_queries_not_using_indexes = 1   # 没走索引的也记(排查利器)

# 不重启动态生效(需要权限)
SET GLOBAL slow_query_log = 1;
SET GLOBAL long_query_time = 1;
SET GLOBAL log_queries_not_using_indexes = 1;

log_queries_not_using_indexes 必开:很多单表 0.5 秒的查询轮不到慢日志,但全表扫描的它们在数据量上来后会要命。

分析工具:

# 安装 pt-query-digest(Percona Toolkit,慢日志分析神器)
apt-get install percona-toolkit

# 生成报告:按总耗时排序,一眼看出该优化谁
pt-query-digest /var/log/mysql/slow.log > /tmp/slow-report.txt

报告头部就是答案:

# 总查询 5000 条,其中:
# Query 1: 0.15s 平均,总耗时 45分钟,QPS 2  ← 头号优化目标
#   SELECT * FROM wp_posts WHERE post_status='publish' ORDER BY date DESC LIMIT 10

二、用 EXPLAIN 看执行计划

拿到慢 SQL,先看它怎么执行的:

EXPLAIN SELECT * FROM wp_posts WHERE post_status='publish' ORDER BY post_date DESC LIMIT 10\G

重点列:

列糟糕值含义
typeALL全表扫描,必须优化
typeindex扫全索引,也不好
typeref/range用了索引,可接受
typeconst/eq_ref主键/唯一键,最优
keyNULL没用索引
rows很大扫了多少行
ExtraUsing filesort额外排序,大数据量危险
ExtraUsing temporary用了临时表,GROUP BY 常见
-- 看索引实际使用情况(MySQL 8.0)
EXPLAIN ANALYZE SELECT ...;

-- 强制走某索引(测试用)
SELECT * FROM t FORCE INDEX (idx_name) WHERE ...;

三、六大优化手段(按收益排序)

3.1 加对索引(90% 的慢查询靠它解决)

-- 看表的现有索引
SHOW INDEX FROM wp_posts;

-- 组合索引:等值查询的列在前,范围/排序的列在后
-- 查询: WHERE status='publish' AND type='post' ORDER BY date DESC
ALTER TABLE wp_posts ADD INDEX idx_status_type_date (status, type, post_date);

-- 覆盖索引:查询列全在索引里,不用回表
ALTER TABLE orders ADD INDEX idx_user_amount (user_id, amount, status);
SELECT user_id, amount, status FROM orders WHERE user_id = 100;

索引设计三原则:

  1. 组合索引遵循「最左前缀」:(a,b,c) 能用在 a、a,b、a,b,c,单独用 b 用不上
  2. 等值条件放前面,范围条件(>、BETWEEN、LIKE 'x%')放后面
  3. 索引不是越多越好:写性能下降 + 空间占用,单表 5 个以内为宜

3.2 干掉 SELECT *

-- 差:取出所有列,大字段全拉走,走不了覆盖索引
SELECT * FROM articles WHERE category = 5;

-- 好:只取需要的列
SELECT id, title, created_at FROM articles WHERE category = 5;

3.3 深分页优化

-- 差:OFFSET 100000 要扫 10 万行再扔
SELECT * FROM articles ORDER BY id LIMIT 100000, 10;

-- 好:记住上次的 ID,从这继续(游标分页)
SELECT * FROM articles WHERE id > 100000 ORDER BY id LIMIT 10;

-- 好:延迟关联(先查 ID 再回表)
SELECT a.* FROM articles a
JOIN (SELECT id FROM articles ORDER BY id LIMIT 100000, 10) b ON a.id = b.id;

3.4 大查询拆小

-- 差:一次 JOIN 四张百万级表
-- 好:应用层分步查,每次查一张表,结果在代码里组装
-- 或者用 IN + 分批:
SELECT * FROM t WHERE id IN (1..1000);  -- 一批一千

3.5 统计信息与缓冲

-- 更新表统计信息(选错索引时)
ANALYZE TABLE wp_posts;

-- InnoDB 缓冲池给足(专库机器物理内存的 50~70%)
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
SET GLOBAL innodb_buffer_pool_size = 2147483648;

缓冲命中率:

SHOW STATUS LIKE 'Innodb_buffer_pool_read%';
-- read_requests 远大于 read_physical_disk = 命中好

3.6 归档历史数据

-- 日志表/订单表按时间归档,热表保持小体积
CREATE TABLE orders_2025 LIKE orders;
INSERT INTO orders_2025 SELECT * FROM orders WHERE created_at < '2026-01-01';
DELETE FROM orders WHERE created_at < '2026-01-01';

表小了,什么都快。归档数据见MySQL 备份与恢复实战。

四、锁与等待

查询不慢但事务卡住:

-- 看当前在跑的查询
SHOW FULL PROCESSLIST;

-- 看谁锁了什么(MySQL 8.0)
SELECT * FROM performance_schema.data_locks;
SELECT * FROM information_schema.innodb_trx;

-- 等锁超时设置
SET GLOBAL innodb_lock_wait_timeout = 10;

常见坑:长事务 + 高频更新同一行(如计数器表)→ 全站等锁。计数器用 Redis 攒批再写库,或拆行(counter_0~counter_9 随机加)。

五、优化前后对比

# 优化前基线
pt-query-digest slow.log | head -30

# 上线索引后:同样的负载,QPS 提升多少?
# 关注指标:
#   - 慢查询数量(应下降 80%+)
#   - 平均 QPS(页面承载能力)
#   - buffer pool 命中率(应 >99%)

一次只改一个变量,改完观察一天。批量改索引,出了问题不知道回滚哪个。

六、速查:EXPLAIN 结果对照表

看到动作
type=ALL 且 rows 很大加索引
Extra=Using filesort索引带上排序字段
Extra=Using temporary优化 GROUP BY,索引配合
key=NULLWHERE 条件列没索引
rows 远超实际返回索引选择性差,重建
延迟关联 & 覆盖索引查询只取必要列

慢查询优化是个闭环:开日志 → digest 找 Top → EXPLAIN 看计划 → 加索引/改 SQL → 对比验证。90% 的数据库性能问题在这个循环里转两圈就解决了。

需要现成的游戏云服务器,可以看看财富云计算:一键开服面板,NAT 型无独立公网 IP,价格比独立 IP 机型省一半以上,建站快速部署。延伸阅读:MySQL 安装与优化入门、MySQL 常见故障排查、云服务器磁盘类型与 IO 性能。

延伸阅读
  1. 高防误区与避坑清单:25 个坑+下单前必问 14 问+到货验货流程
  2. DNS 攻击防护专题:劫持、隧道、投毒、放大四类攻击与 DNSSEC
  3. 电商大促防攻击预案:T-14 到当天的分级响应与降级开关
  4. 源站真实 IP 隐藏完全指南:六大暴露面排查+NAT 型终极方案
  5. 日志分析找攻击源:Nginx 日志三维度分析法与证据固定
  6. 攻击应急响应手册:六阶段响应流程,从确认到复盘的照做清单
  7. iptables 抗 DDoS 配置模板:状态过滤+SYN 防护+防扫描+游戏服版
  8. OpenResty Lua 自研 WAF:动态限流+攻击特征检测+Redis 黑名单
更多阅读
  1. 上一篇:文件描述符与 inode 耗尽处理:Too many open files 根治
  2. 下一篇:Redis 大 Key 与热 Key 处理:延迟毛刺和主从 lag 的元凶
发表评论 6

    1. Redis 大 Key 与热 Key 处理:延迟毛刺和主从 lag 的元凶 - 			财富云计算 - 维基百科的头像
      Redis 大 Key 与热 Key 处理:延迟毛刺和主从 lag 的元凶 - 财富云计算 - 维基百科  

      [...]redis-cli INFO stats | grep evicted大 Key 和热 Key 是 Redis 从「能跑」到「跑得好」的分水岭。记住三个动作:--bigkeys 每周跑一次、UNLINK 代替 DEL、热数据加本地缓存。Redis 就能又稳又快。需要现成的游戏云服务器,可以看看财富云计算:一键开服面板,NAT 型无独立公网 IP,价格比独立 IP 机型省一半以上,建站快速部署。延伸[...]

    2. Steam游戏开服端口速查表:AppID、端口协议、配置需求一表查完(含Windows/Linux双系统索引) - 			财富云计算 - 维基百科的头像
      Steam游戏开服端口速查表:AppID、端口协议、配置需求一表查完(含Windows/Linux双系统索引) - 财富云计算 - 维基百科  

      [...]netsh advfirewall firewall add rule name="GameRange" dir=in action=allow protocol=UDP localport=26901-26903完整的 Windows 环境准备(远程桌面连接、运行库安装、SteamCMD 用法、开机自启、8 个常见坑)见:《Windows 云服务器开服通用前置教程》→ htt[...]

    3. 小程序数据库对接实战:MySQL 选型、连接池与注入防护 - 			财富云计算 - 维基百科的头像
      小程序数据库对接实战:MySQL 选型、连接池与注入防护 - 财富云计算 - 维基百科  

      [...]# 3. binlog 开启,支持时间点恢复binlog 时间点恢复的完整操作见MySQL 备份与恢复实战。每周做一次恢复演练——没验证过的备份等于没有备份。七、性能 checklist[ ] 连接池已启用,池大小合理[ ] 查询字段具体化(别 SELECT *,大文本列拖慢 IO)[ ] 高频查询走索引,EXPLAIN 验证,慢查询治理见MySQL 慢查询优化实战[ ] 热点数据进 Redis,[...]

    4. 小程序性能优化:首屏、setData 与体验分完全指南 - 			财富云计算 - 维基百科的头像
      小程序性能优化:首屏、setData 与体验分完全指南 - 财富云计算 - 维基百科  

      [...]gzip_min_length 1k;5. 失败重试与超时公网请求必须设超时(建议 10s)和有限重试(1 次),别让用户对着转圈的圈圈怀疑人生。五、服务端:接口快才是真的快前端优化到极限,接口 800ms 全白搭。服务端 checklist:[ ] 接口响应 < 200ms(P95),慢查询治理见MySQL 慢查询优化实战[ ] 热点数据 Redis 缓存,命中率 > 80%[ ] [...]

    5. WooCommerce 电商网站搭建:从开店到接支付的全流程 - 			财富云计算 - 维基百科的头像
      WooCommerce 电商网站搭建:从开店到接支付的全流程 - 财富云计算 - 维基百科  

      [...]个人中心(订单 / 地址 / 优惠券)四、接支付:最需要谨慎的一步国内自建电商的支付选项:方式资质要求费率说明微信支付企业主体 + 商户号约 0.6% 起需对接口,或用插件支付宝企业/个体户约 0.6% 起同上个人码收款无0仅限小额/熟人场景,风控严PayPal企业主体约 3.9%+做海外用硬性要求:企业主体才能申请微信/支付宝商户号,个人主体基本没戏——注册主体时想清楚全站 HTTPS 是强制的[...]

    6. 压测与容量规划:上线前就知道你的服务器能扛多少人 - 			财富云计算 - 维基百科的头像
      压测与容量规划:上线前就知道你的服务器能扛多少人 - 财富云计算 - 维基百科  

      [...]Requests/sec: 2804.26Latency Avg 145ms:平均响应,还行Max 1.99s:长尾已经出现,P99 会更差Req/Sec 2804:这台配置的实测 QPS 上限结合系统监控:QPS 上不去时,看是 CPU 100%(应用瓶颈)、IO 100%(数据库/磁盘)、还是带宽打满先崩的组件决定优化方向:瓶颈现象根因优化CPU 100%,QPS 到顶应用计算密集加核 [...]