17370845950

日志表关键字段（如level、service_name、trace_id）须用VARCHAR而非TEXT以支持索引与高效查询；批量插入优于单条插入；WHERE条件中level必须前置以命中联合索引；归档应通过表重命名而非DELETE。

日志表设计要避开 TEXT 字段存关键字段

直接用 TEXT 存 level、service_name 或 trace_id 会导致查询慢、无法索引、排序失效。这些字段必须用定长或变长字符串类型，比如 VARCHAR(32) 或 VARCHAR(64)。

典型错误设计：

CREATE TABLE logs (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  content TEXT,          -- ✅ 日志正文可
用 TEXT
  level TEXT,            -- ❌ 错误：level 应为 VARCHAR(16)
  service_name TEXT,     -- ❌ 错误：应为 VARCHAR(64)
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

推荐结构要点：

• level 用 VARCHAR(16)（值如 'INFO'、'ERROR'）并加索引
• service_name 和 trace_id 同样用 VARCHAR，长度按实际最长值 +20% 预留
• created_at 必须建索引，复合查询常搭配 level，建议建联合索引：INDEX idx_level_time (level, created_at)
• 避免在日志表里存二进制或 Base64 内容；真有需要，单独拆到 log_attachments 表

批量写入要用 INSERT ... VALUES (...), (...), (...)

单条 INSERT 插一条日志，在高并发下会迅速成为瓶颈，QPS 上不去，连接还容易被占满。MySQL 原生支持一次插入多行，性能提升明显（实测 5~10 倍），且事务开销更小。

示例（一次写 3 条）：

INSERT INTO logs (level, service_name, trace_id, content, created_at) 
VALUES 
('ERROR', 'user-service', 'trc-9a8b7c', 'DB connection timeout', NOW()),
('WARN',  'order-service', 'trc-9a8b7c', 'retry limit reached', NOW()),
('INFO',  'gateway',       'trc-9a8b7c', 'request forwarded', NOW());

注意事项：

• 单次最多插多少行？取决于 max_allowed_packet（默认 4MB），建议单批 ≤ 500 行，每行内容平均 ≤ 2KB
• 应用层做批量缓冲时，别等太久——超 500ms 或积满 100 条就发一次，避免日志延迟过高
• 别用 REPLACE INTO 或 INSERT IGNORE 写日志：它们会触发唯一键检查，纯属浪费

查最近 1 小时 ERROR 日志，WHERE 条件顺序影响执行计划

哪怕加了索引，WHERE level = 'ERROR' AND created_at > DATE_SUB(NOW(), INTERVAL 1 HOUR) 和反过来写，执行效率可能差一个数量级。MySQL 优化器倾向先过滤高区分度、范围小的条件。

因为 level 只有少数几个值（'INFO'/'WARN'/'ERROR'），而时间范围是连续区间，所以 level 必须放前面：

SELECT * FROM logs 
WHERE level = 'ERROR' 
  AND created_at > DATE_SUB(NOW(), INTERVAL 1 HOUR)
ORDER BY created_at DESC 
LIMIT 100;

如果没走索引，用 EXPLAIN 看 key 列是否显示你建的联合索引名。常见掉坑点：

• 写了 WHERE created_at > ... AND level = 'ERROR' → 优化器可能弃用联合索引
• 用了函数包裹字段，如 WHERE DATE(created_at) = '2025-06-01' → 索引完全失效
• 查询带 LIKE '%xxx' 在 content 字段上 → 没法走索引，只能全表扫；真要模糊查，考虑导出到 Elasticsearch

归档旧日志别用 DELETE 大表，用 RENAME + DROP

线上日志表跑一个月后动辄千万行，直接 DELETE FROM logs WHERE created_at 会锁表、打满 I/O、拖慢写入，甚至触发主从延迟爆炸。

安全做法是按月分表 + 交换归档：

• 每月初新建表 logs_202505，原表改名为 logs_202504
• 应用配置指向新表，旧表留着只读或导出后 DROP
• 建表语句保持一致，但可对旧表删掉不必要的索引（比如只留 created_at）来减小体积

脚本化示例（MySQL 8.0+ 支持原子重命名）：

-- 假设当前是 2025-06-01，把老表归档
RENAME TABLE logs TO logs_202505;
CREATE TABLE logs LIKE logs_202505;
-- （可选）给新表加写入优化：关闭 autocommit 批量插入时更稳

注意：归档不是一劳永逸。如果业务要求保留 90 天，就得写定时任务自动清理 logs_202503 及更早的表，而不是留一堆“已归档但没删”的表占空间。