TiDB慢查询日志分析：从日志到性能优化的完整指南

一、慢查询日志的核心价值与工作原理

TiDB作为分布式HTAP数据库，其慢查询日志是性能优化的重要数据源。不同于传统关系型数据库，TiDB的慢查询日志不仅记录执行时间超过阈值的SQL，还包含分布式执行的关键信息。

日志生成机制：
当SQL执行时间超过tidb_slow_log_threshold（默认300ms）时，系统会将执行信息写入日志文件。日志采用JSON格式，包含查询语句、执行时间、等待锁时间、扫描行数等40+个字段。

关键字段解析：

• Query_time：总执行时间（毫秒）
• Process_time：TiDB处理时间（不含网络传输）
• Wait_time：等待TiKV响应的时间
• Backoff_time：重试或等待资源的时间
• Request_count：向TiKV发起的RPC次数
• Cop_process_time：Coprocessor处理时间（扫描/聚合等）

二、慢查询日志分析四步法

1. 日志收集与结构化解析

基础收集：

# 查看慢查询日志路径
grep 'slow-query-file' /etc/tidb/tidb.toml
# 默认路径：/var/lib/tidb/tidb-slow.log

结构化处理：
使用jq工具解析JSON日志：

jq '. | {Query: .Query, Time: .Query_time, ScanRows: .Scan_rows}' tidb-slow.log | head

可视化方案：
推荐使用ELK Stack或Grafana+Loki构建监控看板，关键指标包括：

• 慢查询数量趋势
• 平均执行时间分布
• 扫描行数与返回行数比例
• 热点表访问频次

2. 性能瓶颈定位模型

执行计划分析：
通过EXPLAIN ANALYZE获取实际执行计划：

EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 1001;

重点关注：

• 全表扫描（type=ALL）
• 错误的索引选择
• 分布式扫描的cop_task数量

等待事件分析：
日志中的Wait_time可细分为：

• pd.client.ttl.refresh：PD TTL刷新等待
• tikv.grpc.poll：gRPC请求积压
• lock.wait：事务锁等待

3. 典型问题诊断与优化

案例1：索引失效问题
日志现象：

{
  "Query": "SELECT * FROM users WHERE name LIKE '%john%'",
  "Query_time": 1250,
  "Scan_rows": 5000000,
  "Plan_digest": "..."
}

优化方案：

1. 添加函数索引：ALTER TABLE users ADD INDEX idx_name((name));
2. 改用全文索引方案
3. 考虑使用TiDB的倒排索引插件

案例2：分布式执行效率低
日志特征：

• Request_count异常高（>1000）
• Cop_process_time占比超过70%
  优化措施：

1. 调整tidb_distsql_scan_concurrency参数
2. 对大表进行分区（PARTITION BY RANGE）
3. 检查网络拓扑，减少跨机房数据传输

案例3：锁等待严重
日志表现：

{
  "Backoff_time": 850,
  "Lock_keys": ["t_1001_r_12345"],
  "Detail": "lock wait timeout"
}

解决方案：

1. 缩短事务：拆分大事务为多个小事务
2. 调整innodb_lock_wait_timeout（TiDB兼容参数）
3. 检查是否有死锁循环，使用SHOW ENGINE INNODB STATUS

三、高级优化技术

1. 执行计划绑定

对频繁出现的慢查询，可固定优化器选择：

-- 创建绑定
CREATE GLOBAL BINDING FOR 
SELECT * FROM orders USING SELECT * FROM orders USE INDEX(idx_customer);
-- 查看绑定
SELECT * FROM mysql.bind_info;

2. 统计信息更新

确保统计信息准确：

-- 手动收集统计信息
ANALYZE TABLE orders;
-- 调整自动收集阈值
SET GLOBAL tidb_auto_analyze_ratio = 0.5;

3. 参数调优矩阵

参数	适用场景	推荐值
tidb_hashagg_partial_concurrency	聚合操作并发	CPU核心数
tidb_hashjoin_concurrency	Join操作并发	CPU核心数/2
raftstore.apply-pool-size	存储层应用线程	2-4
storage.block-cache.capacity	块缓存大小	总内存的40%

四、持续优化体系构建

1. 慢查询基线管理

建立慢查询基线表：

CREATE TABLE slow_query_baseline (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    sql_digest VARCHAR(64),
    avg_query_time FLOAT,
    max_query_time FLOAT,
    scan_rows_avg BIGINT,
    first_seen DATETIME,
    last_seen DATETIME
);

2. 自动化告警规则

设置Prometheus告警：

- alert: TiDBSlowQueryIncrease
  expr: increase(tidb_slow_query_total{instance=~".*"}[5m]) > 10
  for: 2m
  labels:
    severity: warning
  annotations:
    summary: "慢查询数量激增 ({{ $value }}/5m)"

3. 性能测试方法论

基准测试工具：

• Sysbench定制脚本
• Go-YCSB压力测试
• 真实业务数据回放

测试要点：

1. 混合读写比例（建议7:3）
2. 逐步增加并发（从50到2000）
3. 监控QPS、延迟、错误率的拐点

五、最佳实践总结

1. 日志轮转策略：配置slow_query_file和log_file_max_days避免日志膨胀
2. 定期归档分析：建立每周的慢查询评审会议
3. 版本升级检查：每次TiDB升级后验证执行计划变化
4. 容量规划：根据慢查询趋势预测存储和计算资源需求

通过系统化的慢查询日志分析，某金融客户将平均查询响应时间从2.3s降至380ms，同时将资源利用率提升了40%。关键在于建立”监控-分析-优化-验证”的闭环体系，持续迭代数据库性能。