标注页面从超时卡顿到毫秒级响应的优化实践

用户101924032654
0 阅读5分钟

标注页面从超时卡顿到毫秒级响应的优化实践

本文记录一次完整的数据库查询性能优化过程，从页面超时到毫秒级响应，涵盖分页、索引、数据传输等多个层面的迭代优化。

📌 背景

标注平台的列表页长期存在性能问题，页面加载经常超时，严重影响标注效率。该页面的特点是：

  • 数据量大，历史积累记录众多
  • 筛选条件复杂，多达十几项，包含时间范围、多字段模糊匹配、多字段取反等
  • 查询条件由用户动态选择，后端需动态拼接

技术栈： Python · FastAPI · SQLAlchemy · MySQL

经过几轮迭代，最终将响应时间控制在毫秒级，以下是完整的优化过程。

优化历程

第一步：引入分页，控制单次数据量

最初页面一次性加载全部数据，数据量一大直接压垮查询。引入 offset 分页后，每次只加载当前页数据，单次查询规模得到控制。

这是后续所有优化的前提，没有分页，再好的索引效果也有限。

第二步：添加索引，消除全表扫描

分页之后响应依然很慢，根本原因是缺少索引，MySQL 每次都在做全表扫描。对筛选字段和排序字段添加索引后，响应时间从超时降到了几十秒级。

第三步：系统性优化索引

加了索引，偶尔仍有慢查询，问题出在索引质量上。

3.1 多维度定位慢查询

排查慢查询不应只依赖单一手段，几种方法结合使用效果更好：

① 慢查询日志

开启 MySQL 慢查询日志，自动记录超过阈值的 SQL，是最直接的问题发现手段：

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1; -- 超过 1 秒的查询记录下来

② performance_schema

从全局视角统计各类 SQL 的执行频率和平均耗时，找出系统中耗时最高的查询：

SELECT digest_text, count_star, avg_timer_wait
FROM performance_schema.events_statements_summary_by_digest
ORDER BY avg_timer_wait DESC
LIMIT 10;

③ EXPLAIN / EXPLAIN ANALYZE

定位到具体慢 SQL 后，用 EXPLAIN 分析执行计划，确认是否走了索引。重点关注 type 字段：

type 值含义
ref / range走了索引，正常
ALL全表扫描，需要优化
3.2 索引健康度检查

不只是看有没有索引，还要定期检查索引质量：

-- 找出从未被使用的索引
SELECT * FROM sys.schema_unused_indexes
WHERE object_schema = 'your_database';

-- 找出重复、冗余的索引
SELECT * FROM sys.schema_redundant_indexes
WHERE table_schema = 'your_database';

-- 查看索引基数，基数过低说明区分度差，索引效果有限
SHOW INDEX FROM annotations;
3.3 修复索引失效场景

场景一：对索引字段使用函数

-- ❌ 索引失效
WHERE DATE(created_at) = '2024-01-01'

-- ✅ 修复：改用范围查询
WHERE created_at >= '2024-01-01 00:00:00'
  AND created_at <  '2024-01-02 00:00:00'

场景二：取反查询

!=、NOT LIKE 在 MySQL 中通常无法走索引，能改成正向查询的尽量改：

-- ❌ 索引失效
WHERE status != 'deleted'

-- ✅ 修复：改为正向 IN 查询
WHERE status IN ('pending', 'approved', 'reviewing')

场景三：前模糊匹配

LIKE '%xxx%' 无法走索引。优化思路：让其他可走索引的条件先过滤掉大部分数据，缩小模糊查询的扫描范围；对查询频率极高的字段，可考虑引入全文索引（FULLTEXT）。

场景四：联合索引未遵循最左前缀

联合索引中，跳过中间字段会导致后续字段索引失效，需要调整查询条件或重建索引顺序。

建联合索引的原则：区分度高的字段放前、范围查询字段放最后

经过以上优化，响应时间降到了秒级，但在数据量大、筛选条件多时仍有波动，瓶颈转移到了数据传输层。

第四步：减少数据传输量，实现毫秒级响应

索引优化到位后，响应时间已经到秒级，但偶尔还有波动。这时候把排查视角从数据库转向数据传输层，通过接口响应体大小、网络耗时等指标量化定位，发现真正的瓶颈在于每次返回的数据量太大。

4.1 大文本字段截断

标注数据中存在大量超长文本字段，列表页无需完整展示，但全量返回会严重拖慢传输速度。对大文本字段在查询时截断，长度控制在 200 字符以内，需要完整内容时再通过详情接口单独获取。

4.2 按需查询，动态返回字段

列表页默认只返回核心字段，用户可自由选择需要展示的列，后端根据前端传参动态构建查询字段，避免每次拉取全量数据。

4.3 详情页 + 悬浮懒加载

完整的大文本内容拆分到独立的详情接口，列表页不再返回。需要快速预览时，通过悬浮交互按需触发详情接口，彻底避免列表页的数据冗余。

总结

优化阶段核心手段响应时间
优化前无超时 ❌
第一步分页加载有所改善，但仍慢
第二步添加基础索引几十秒级
第三步多维度定位、清理无效索引、修复失效场景、优化联合索引秒级
第四步文本截断、按需查询、懒加载毫秒级 ✅

经验沉淀

  • 分页是前提，没有分页谈索引优化意义有限
  • 慢查询排查要多手段结合，慢查询日志找问题 SQL，performance_schema 看全局分布，EXPLAIN 分析执行计划
  • 索引加了不等于生效，取反查询、函数操作、前模糊匹配是高发失效场景，需要逐一排查
  • 瓶颈不一定在数据库，很多慢查询的真正根源在返回的数据量太大，把排查视角从数据库延伸到传输层才能彻底解决
avatar
文章
阅读
粉丝