AI工具为啥可以分析Perfetto性能？手把手教你常用SQL分析性能问题

learnframework

448人浏览 · 2026-04-28 11:52:17

learnframework · 2026-04-28 11:52:17 发布

背景

近来AI在平时工作中频繁使用，经常有人说是否可以考虑使用AI来分析Perfetto等场景。其实大家可能会感叹惊讶为啥AI还可以分析Perfetto呢？其实大家去体验一下AI分析Perfetto相关文件就会知道，其实后面的本质原理就是使用Perfetto本身支持的SQL语句对文件的相关表进行查询和统计等操作。

在这里插入图片描述所以说理解AI背后的原理就会觉得其实AI也没有那么神秘，下面列出与AI可以分析Perfetto的原理：

AI理解用户的自然语言
—》 AI会去查找生成达到用户要求的 SQL语句
—》使用生成的SQL语句对Perfetto文件相关表进行检索查询情况
—》AI对查询到的结果进行整理

其实Perfetto的sql使用对于我们黑盒情况下分析统计性能问题也是作用很大的，所以学习Perfetto SQL的使用是非常有必要的。

在这里插入图片描述

一、为什么 Perfetto 使用 SQL？

1.1 传统性能分析的痛点

痛点	传统工具	Perfetto SQL 解决方案
数据孤岛	Systrace、Logcat、内存快照各自独立	统一 Trace 文件，所有数据一张表查询
分析困难	需要肉眼在可视化界面中寻找问题	SQL 精确查询，秒级定位
无法量化	“看起来卡顿” → 无法衡量	SQL 统计精确到毫秒
难以对比	不同 Trace 无法批量分析	SQL 导出数据，批量对比
学习成本高	每个工具一套使用方法	标准 SQL，一次学会到处用

1.2 SQL 查询的优势

精确量化示例：

-- 精确找出所有超过 100ms 的卡顿
SELECT name, dur/1e6 as ms FROM slice WHERE dur > 100000000;

多表关联示例：

-- 关联 slice -> thread_track -> thread -> process 四张表
SELECT process.name, thread.name, slice.name, slice.dur
FROM slice 
JOIN thread_track ON slice.track_id = thread_track.id
JOIN thread ON thread_track.utid = thread.utid
JOIN process ON thread.upid = process.upid;

二、环境准备

2.1 下载工具

curl -LO https://github.com/google/perfetto/releases/latest/download/trace_processor_linux_amd64
mv trace_processor_linux_amd64 trace_processor_shell
chmod +x trace_processor_shell

2.2 验证安装

$ ./trace_processor_shell --version
Perfetto Trace Processor v47.0

三、表关系说明（重要！）

关联路径：slice → thread_track → thread → process

slice.track_id = thread_track.id
thread_track.utid = thread.utid
thread.upid = process.upid

⚠️ 常见错误：直接 JOIN process 而不经过 thread_track 和 thread，会导致笛卡尔积，结果完全错误！

四、基础 SQL 查询（全部实测验证）

4.1 Trace 基本信息

SELECT 
    'Trace时长' as metric, 
    ROUND((MAX(ts) - MIN(ts))/1e9, 2) as value, 
    '秒' as unit 
FROM slice
UNION ALL
SELECT 'Slice总数', COUNT(*), '个' FROM slice
UNION ALL
SELECT '进程数量', COUNT(DISTINCT upid), '个' FROM thread WHERE upid IS NOT NULL;

执行：

./trace_processor_shell trace.pftrace -q /dev/stdin << 'EOF'
SELECT 
    'Trace时长' as metric, 
    ROUND((MAX(ts) - MIN(ts))/1e9, 2) as value, 
    '秒' as unit 
FROM slice
UNION ALL
SELECT 'Slice总数', COUNT(*), '个' FROM slice
UNION ALL
SELECT '进程数量', COUNT(DISTINCT upid), '个' FROM thread WHERE upid IS NOT NULL;
EOF

输出：

"metric","value","unit"
"Trace时长",5.37,"秒"
"Slice总数",10090,"个"
"进程数量",105,"个"

4.2 进程耗时排名（✅ 完整 JOIN 条件）

SELECT 
    p.name, 
    COUNT(*) as cnt, 
    ROUND(SUM(s.dur)/1e9, 2) as total_s
FROM slice s
JOIN thread_track tt ON s.track_id = tt.id      -- 关键：关联 track
JOIN thread t ON tt.utid = t.id                 -- 关键：关联 thread
JOIN process p ON t.upid = p.id                 -- 关键：关联 process
GROUP BY p.id 
ORDER BY total_s DESC 
LIMIT 5;

执行：

./trace_processor_shell trace.pftrace -q /dev/stdin << 'EOF'
SELECT 
    p.name, 
    COUNT(*) as cnt, 
    ROUND(SUM(s.dur)/1e9, 2) as total_s
FROM slice s
JOIN thread_track tt ON s.track_id = tt.id
JOIN thread t ON tt.utid = t.id
JOIN process p ON t.upid = p.id
GROUP BY p.id 
ORDER BY total_s DESC 
LIMIT 5;
EOF

输出：

"name","cnt","total_s"
"/system/bin/surfaceflinger",8111,14.42
"system_server",273,9.47
"/vendor/bin/hw/android.hardware.sensors@1.0-service",247,9.18
"com.android.launcher3",640,0.73
"mdss_fb0",153,0.22

❌ 错误写法（缺少 JOIN 条件）：

-- 错误！会产生笛卡尔积，所有结果都一样
SELECT p.name, SUM(s.dur)/1e6 as time 
FROM slice s 
JOIN process p        -- 缺少 ON 条件！
GROUP BY p.id;

4.3 最耗时的操作

SELECT 
    name, 
    COUNT(*) as cnt, 
    ROUND(MAX(dur)/1e6, 2) as max_ms
FROM slice 
GROUP BY name 
ORDER BY max_ms DESC 
LIMIT 5;

输出：

"name","cnt","max_ms"
"threadMain",132,175.71
"vsyncRequested = 0 ",24,173.54
"mCondition.wait",24,173.09
"HIDL::ISensors::poll::client",122,47.33
"binder transaction",172,46.92

4.4 长耗时操作（>10ms）

SELECT 
    name, 
    ROUND(ts/1e9, 2) as time_s, 
    ROUND(dur/1e6, 2) as ms
FROM slice 
WHERE dur > 10000000 
ORDER BY dur DESC 
LIMIT 5;

输出：

"name","time_s","ms"
"threadMain",41825.02,175.71
"threadMain",41824.82,174.60
"threadMain",41823.02,173.92
"vsyncRequested = 0 ",41825.02,173.54
"vsyncRequested = 0 ",41824.82,173.19

4.5 线程耗时统计（✅ 完整 JOIN 条件）

SELECT 
    t.name as thread, 
    p.name as proc, 
    ROUND(SUM(s.dur)/1e9, 2) as total_s
FROM slice s
JOIN thread_track tt ON s.track_id = tt.id
JOIN thread t ON tt.utid = t.id
JOIN process p ON t.upid = p.id
GROUP BY t.id 
ORDER BY total_s DESC 
LIMIT 5;

输出：

"thread","proc","total_s"
"app","/system/bin/surfaceflinger",13.61
"SensorService","system_server",9.41
"HwBinder:991_1","/vendor/bin/hw/android.hardware.sensors@1.0-service",6.99
"sensors@1.0-ser","/vendor/bin/hw/android.hardware.sensors@1.0-service",2.19
"droid.launcher3","com.android.launcher3",0.41

4.6 Binder 通信分析

SELECT 
    name, 
    COUNT(*) as cnt, 
    ROUND(AVG(dur)/1e6, 2) as avg_ms, 
    ROUND(MAX(dur)/1e6, 2) as max_ms
FROM slice 
WHERE category = 'binder' 
GROUP BY name;

输出：

"name","cnt","avg_ms","max_ms"
"binder async rcv",150,0.00,0.00
"binder reply",172,27.29,46.44
"binder transaction",172,28.00,46.92
"binder transaction async",150,0.00,0.00

4.7 调度延迟分析（✅ 完整 JOIN 条件）

SELECT 
    thread.name, 
    sched.ts/1e9 as time_s, 
    sched.dur/1e6 as ms, 
    sched.cpu
FROM sched
JOIN thread USING (utid)      -- 使用 USING 简化写法
WHERE sched.dur > 10000000
ORDER BY sched.dur DESC 
LIMIT 5;

输出：

"name","time_s","ms","cpu"
"swapper",41822.44,2986.34,6
"swapper",41822.44,1556.84,7
"swapper",41824.00,913.56,7
"swapper",41822.55,749.87,4
"swapper",41825.02,409.86,7

4.8 帧渲染分析

SELECT 
    name, 
    COUNT(*) as cnt, 
    ROUND(AVG(dur)/1e6, 2) as avg_ms, 
    ROUND(MAX(dur)/1e6, 2) as max_ms
FROM slice 
WHERE name LIKE '%vsync%' 
GROUP BY name 
ORDER BY cnt DESC 
LIMIT 5;

输出：

"name","cnt","avg_ms","max_ms"
" connection->vsyncRequest = None",2400,0.10,0.51
"vsyncRequested = 1 ",108,5.34,18.96
"VSyncDispatchTimerQueue 0x74f0397af0 cancelTimer...",80,0.03,0.11
"0x74f0397af0 VSyncDispatchTimerQueue::schedule...",78,0.85,1.43
"VSyncDispatchTimerQueue 0x74f0397af0 timerCallback...",58,0.29,0.71

五、主线程与渲染分析（全部实测验证）

5.1 RenderThread 最耗时方法（✅ 完整 JOIN 条件）

SELECT 
    s.name as method_name,
    p.name as process_name,
    ROUND(s.dur/1e6, 2) as duration_ms,
    ROUND(s.ts/1e9, 2) as timestamp_s
FROM slice s
JOIN thread_track tt ON s.track_id = tt.id
JOIN thread t ON tt.utid = t.id
JOIN process p ON t.upid = p.id
WHERE t.name = 'RenderThread'
ORDER BY s.dur DESC
LIMIT 10;

执行：

./trace_processor_shell trace.pftrace -q /dev/stdin << 'EOF'
SELECT 
    s.name as method_name,
    p.name as process_name,
    ROUND(s.dur/1e6, 2) as duration_ms,
    ROUND(s.ts/1e9, 2) as timestamp_s
FROM slice s
JOIN thread_track tt ON s.track_id = tt.id
JOIN thread t ON tt.utid = t.id
JOIN process p ON t.upid = p.id
WHERE t.name = 'RenderThread'
ORDER BY s.dur DESC
LIMIT 10;
EOF

输出：

"method_name","process_name","duration_ms","timestamp_s"
"DrawFrames 277526","com.android.launcher3",17.88,41824.63
"DrawFrames 277483","com.android.launcher3",16.79,41823.64
"Drawing 437.00 1186.00 644.00 1457.00","com.android.launcher3",15.73,41824.63
"Drawing 437.00 1186.00 644.00 1457.00","com.android.launcher3",13.92,41823.64
"DrawFrames 277569","com.android.launcher3",13.36,41825.63

5.2 掉帧统计（帧耗时 > 16.67ms）

SELECT 
    '掉帧统计' as metric,
    COUNT(*) as frame_count,
    ROUND(AVG(dur)/1e6, 2) as avg_frame_ms,
    ROUND(MAX(dur)/1e6, 2) as max_frame_ms
FROM slice
WHERE name LIKE '%DrawFrame%' AND dur > 16667000
UNION ALL
SELECT 
    '总帧数',
    COUNT(*),
    ROUND(AVG(dur)/1e6, 2),
    ROUND(MAX(dur)/1e6, 2)
FROM slice
WHERE name LIKE '%DrawFrame%';

输出：

"metric","frame_count","avg_frame_ms","max_frame_ms"
"掉帧统计",2,17.33,17.88
"总帧数",5,13.99,17.88

分析：5 帧中有 2 帧掉帧（40% 掉帧率）

5.3 Handler 消息处理耗时（✅ 完整 JOIN 条件）

SELECT 
    s.name as handler_msg,
    p.name as process,
    COUNT(*) as count,
    ROUND(AVG(s.dur)/1e6, 2) as avg_ms,
    ROUND(MAX(s.dur)/1e6, 2) as max_ms
FROM slice s
JOIN thread_track tt ON s.track_id = tt.id
JOIN thread t ON tt.utid = t.id
JOIN process p ON t.upid = p.id
WHERE s.name LIKE '%handler%' OR s.name LIKE '%Message%' OR s.name LIKE '%Looper%'
GROUP BY s.name, p.name
HAVING count > 0
ORDER BY max_ms DESC
LIMIT 10;

输出：

"handler_msg","process","count","avg_ms","max_ms"
"handleMessage","/system/bin/surfaceflinger",5,8.76,13.02
"JIT compiling android.os.Looper...","com.android.launcher3",1,4.07,4.07
"monitor contention with owner temporary-7...","com.tencent.android.qqdownloader:daemon",1,2.00,2.00

5.4 IO 操作耗时（✅ 完整 JOIN 条件）

SELECT 
    s.name as operation,
    p.name as process,
    COUNT(*) as count,
    ROUND(AVG(s.dur)/1e6, 2) as avg_ms,
    ROUND(MAX(s.dur)/1e6, 2) as max_ms
FROM slice s
JOIN thread_track tt ON s.track_id = tt.id
JOIN thread t ON tt.utid = t.id
JOIN process p ON t.upid = p.id
WHERE s.name LIKE '%read%' OR s.name LIKE '%write%' OR s.name LIKE '%fsync%' OR s.name LIKE '%open%'
GROUP BY s.name, p.name
HAVING count > 0
ORDER BY max_ms DESC
LIMIT 10;

输出：

"operation","process","count","avg_ms","max_ms"
"threadMain","/system/bin/surfaceflinger",132,35.06,175.71
"Lock contention on thread list lock...","com.tencent.android.qqdownloader:daemon",1,2.58,2.58
"mdss_mdp_cmd_readptr_done","[NULL]",15,0.84,1.85

六、ANR 与卡顿排查（全部实测验证）

6.1 ANR WatchDog 相关（✅ 完整 JOIN 条件）

SELECT 
    s.name as operation,
    p.name as process,
    t.name as thread,
    ROUND(s.dur/1e6, 2) as duration_ms,
    ROUND(s.ts/1e9, 2) as timestamp_s
FROM slice s
JOIN thread_track tt ON s.track_id = tt.id
JOIN thread t ON tt.utid = t.id
JOIN process p ON t.upid = p.id
WHERE s.name LIKE '%ANR%' OR s.name LIKE '%anr%' OR s.name LIKE '%WatchDog%' OR s.name LIKE '%freeze%'
ORDER BY s.dur DESC
LIMIT 10;

输出：

"operation","process","thread","duration_ms","timestamp_s"
"monitor contention with owner |ANR-WatchDog| (106)...","com.zhihu.android","m.zhihu.android",0.33,41823.53

6.2 锁竞争分析（✅ 完整 JOIN 条件）

SELECT 
    s.name as lock_contention,
    p.name as process,
    ROUND(s.dur/1e6, 2) as duration_ms,
    ROUND(s.ts/1e9, 2) as timestamp_s
FROM slice s
JOIN thread_track tt ON s.track_id = tt.id
JOIN thread t ON tt.utid = t.id
JOIN process p ON t.upid = p.id
WHERE s.name LIKE '%Lock contention%' OR s.name LIKE '%monitor contention%'
ORDER BY s.dur DESC
LIMIT 10;

6.3 卡顿时间分布

SELECT 
    CASE 
        WHEN dur < 1000000 THEN '<1ms'
        WHEN dur < 5000000 THEN '1-5ms'
        WHEN dur < 10000000 THEN '5-10ms'
        WHEN dur < 16667000 THEN '10-16.67ms'
        WHEN dur < 33333000 THEN '16.67-33.33ms (1-2帧)'
        ELSE '>33.33ms (严重卡顿)'
    END as duration_range,
    COUNT(*) as count,
    ROUND(100.0 * COUNT(*) / (SELECT COUNT(*) FROM slice), 2) as percentage
FROM slice
GROUP BY duration_range
ORDER BY MIN(dur);

七、常用 SQL 速查表

7.1 基础查询

#	查询目的	核心 SQL
1	Trace 基本信息	`SELECT COUNT(*), MAX(ts)-MIN(ts) FROM slice`
2	进程耗时排名	`SELECT p.name, SUM(s.dur) FROM slice s JOIN thread_track tt ON s.track_id=tt.id JOIN thread t ON tt.utid=t.id JOIN process p ON t.upid=p.id GROUP BY p.id`
3	最耗时操作	`SELECT name, MAX(dur) FROM slice GROUP BY name`
4	长耗时操作	`SELECT * FROM slice WHERE dur > 10000000`
5	线程耗时	`SELECT t.name, SUM(s.dur) FROM slice s JOIN thread_track tt ON s.track_id=tt.id JOIN thread t ON tt.utid=t.id GROUP BY t.id`
6	Binder 分析	`SELECT * FROM slice WHERE category='binder'`
7	调度延迟	`SELECT * FROM sched WHERE dur > 10000000`
8	帧渲染	`SELECT * FROM slice WHERE name LIKE '%vsync%'`

7.2 主线程与渲染

#	查询目的	核心 SQL
9	RenderThread 耗时	`SELECT s.name, p.name, s.dur FROM slice s JOIN thread_track tt ON s.track_id=tt.id JOIN thread t ON tt.utid=t.id JOIN process p ON t.upid=p.id WHERE t.name='RenderThread'`
10	掉帧统计	`SELECT COUNT(*) FROM slice WHERE name LIKE '%DrawFrame%' AND dur > 16667000`
11	Handler 耗时	`SELECT * FROM slice WHERE name LIKE '%handler%'`
12	IO 耗时	`SELECT * FROM slice WHERE name LIKE '%read%' OR name LIKE '%write%'`

7.3 ANR 与卡顿

#	查询目的	核心 SQL
13	ANR WatchDog	`SELECT * FROM slice WHERE name LIKE '%ANR%'`
14	锁竞争	`SELECT * FROM slice WHERE name LIKE '%contention%'`
15	卡顿分布	`SELECT CASE WHEN dur > 16667000 THEN '卡顿' END, COUNT(*) FROM slice`

八、重要限制说明

8.1 SQL 文件限制

❌ 错误做法（多个 SELECT）：

-- analysis.sql - 会报错！
SELECT '查询1';
SELECT '查询2';  -- 多个 SELECT 报错

错误信息：

Result rows were returned for multiples queries. 
Ensure that only the final statement is a SELECT statement...

✅ 正确做法（单个 SELECT 或 UNION）：

-- 只保留一个 SELECT
SELECT '查询1';

-- 或使用 UNION 合并
SELECT '查询1' as q UNION ALL SELECT '查询2';

8.2 JOIN 条件必须完整

❌ 错误（笛卡尔积）：

-- 错误！所有结果都一样
SELECT p.name, SUM(s.dur) 
FROM slice s 
JOIN process p        -- 缺少 ON 条件
GROUP BY p.id;

✅ 正确（完整 JOIN 链）：

-- 正确！结果准确
SELECT p.name, SUM(s.dur) 
FROM slice s
JOIN thread_track tt ON s.track_id = tt.id
JOIN thread t ON tt.utid = t.id
JOIN process p ON t.upid = p.id
GROUP BY p.id;

8.3 保存结果到文件

./trace_processor_shell trace.pftrace -q query.sql > result.csv

九、性能优化建议总结

问题	数据	严重程度	建议
threadMain 卡顿	最大 175.71ms	🔴 严重	需深入分析代码
RenderThread 掉帧	40% 掉帧率	🔴 严重	优化绘制逻辑
Binder 延迟	平均 28ms	🟡 中等	改为异步调用
Handler 消息	最大 13ms	🟢 轻微	持续监控

参考：

Perfetto 文档：https://perfetto.dev/docs/
SQL 表参考：https://perfetto.dev/docs/analysis/sql-tables

原文地址：
https://mp.weixin.qq.com/s/_bMe-BjwaiLzceI4_DhnPw

更多framework实战开发干货，定期输出相关干货，请关注下面“千里马学框架”

AtomGit开源社区

AtomGit 是由开放原子开源基金会联合 CSDN 等生态伙伴共同推出的新一代开源与人工智能协作平台。平台坚持“开放、中立、公益”的理念，把代码托管、模型共享、数据集托管、智能体开发体验和算力服务整合在一起，为开发者提供从开发、训练到部署的一站式体验。

更多推荐

Nodejs也能写Agent - 1.基础知识

AtomGit开源社区

Flyway 分工方案（DBA 执行 + 应用只读校验）

执行命令：flyway migrate -locations=files:///脚本存放路径（如本地路径、代码仓库路径），执行完成后，检查 flyway_schema_history 表，确认版本已记录、执行状态为 SUCCESS。- 校验失败：应用启动报错，日志显示“Validation failed”，提示版本不匹配/脚本缺失，部署团队立即反馈给 DBA 和开发，排查问题（如 DBA 未执行对