- Author(s): @92hackers, @chrysan, @Tammyxia, @yiwen92 (in alphabetical order)
- Last updated: 2022-01-05
- Discussion at: online meeting
- Project at: https://github.com/Hackathon-2022-TiVP
TiVP is a Visual Plan for TiDB SQL explaination integreted with Dashboard. 该项目旨在可视化 TiDB 生成的执行计划。
随着 TiDB 被运用到更加复杂的分析场景，SQL 语句会变得异常复杂，由此 explain 出来的执行计划（https://docs.pingcap.com/zh/tidb/stable/explain-walkthrough/#使用-explain-解读执行计划） 就会令人费解，影响性能调优效率。
当开发者对着 explain 生成的复杂执行计划抓耳挠腮的时候，我们更希望化繁为简，用可视化的方式将一条复杂 SQL 语句的执行流程清楚简明地展示出来，在帮助开发者快速了解 SQL Plan 执行计划的同时，给予优化的提示和帮助。
收集 TiDB 数据库侧 SQL 的执行计划和运行时信息，结构化定义执行计划信息，开发一个显示界面，用于图像化呈现 SQL 的执行计划，帮助技术人员更便捷地了解、分析 SQL 语句及其执行逻辑，从而快速定位以及解决执行计划相关的各类问题，如慢 SQL 优化、执行计划选错等。
主要功能：
- 收集和整理数据库侧必要信息，如执行计划（包括逻辑计划和物理计划）、各步骤运行耗时、访问信息（estRows 和 actRows）等
- 将执行计划的算子和代价通过树状结构进行可视化展现，明确显示耗时最长或代价最大的执行路径
- 集成于 TiDB 现有的管理工具 Dashboard，或以独立的可交互的展示界面进行演示
未来扩展：
- Optimize Trace？呈现优化器生成计划的逻辑
- SQL 助手：智能 Hint 和改写？基于规则给予 SQL Hint 提示
- 统计信息可视化？帮助定位统计信息不准确问题
SELECT rel_users_exams.user_username AS rel_users_exams_user_username,
rel_users_exams.exam_id AS rel_users_exams_exam_id,
rel_users_exams.started_at AS rel_users_exams_started_at,
rel_users_exams.finished_at AS rel_users_exams_finished_at,
answer_1.id AS answer_1_id,
answer_1.text AS answer_1_text,
answer_1.correct AS answer_1_correct,
answer_1.fraction AS answer_1_fraction,
answer_1.question_id AS answer_1_question_id,
question_1.id AS question_1_id,
question_1.title AS question_1_title,
question_1.text AS question_1_text,
question_1.file AS question_1_file,
question_1.type AS question_1_type,
question_1.source AS question_1_source,
question_1.exam_id AS question_1_exam_id,
exam_1.id AS exam_1_id,
exam_1.title AS exam_1_title,
exam_1.date_from AS exam_1_date_from,
exam_1.date_to AS exam_1_date_to,
exam_1.created AS exam_1_created,
exam_1.created_by_ AS exam_1_created_by_,
exam_1.duration AS exam_1_duration,
exam_1.success_threshold AS exam_1_success_threshold,
exam_1.published AS exam_1_published
FROM rel_users_exams LEFT OUTER
JOIN exam AS exam_1
ON exam_1.id = rel_users_exams.exam_id LEFT OUTER
JOIN question AS question_1
ON exam_1.id = question_1.exam_id LEFT OUTER
JOIN answer AS answer_1
ON question_1.id = answer_1.question_id
WHERE rel_users_exams.user_username = %(param_1)s
AND rel_users_exams.exam_id = %(param_2)s
ORDER BY question_1.id;[
{
"Plan": {
"Node ID": "Sort_6",
"Estimated Rows": 2.94,
"Actual Rows": 4,
"task": "root",
"access object": "",
"operator info": "tpch50.lineitem.l_returnflag, tpch50.lineitem.l_linestatus",
"execution info": "time:13.4s, loops:2",
"disk": "0 Bytes",
"memory": "45.4 KB",
"Plans": [
{
"Node ID": "Projection_8",
"Plan Rows": 2.94,
"Actual Rows": 4,
"task": "root",
"access object": "",
"operator info": "tpch50.lineitem.l_returnflag, tpch50.lineitem.l_linestatus, Column#18, Column#19, Column#20, Column#21, Column#22, Column#23, Column#24, Column#25",
"execution info": "time:13.4s, loops:5, Concurrency:OFF",
"disk": "N/A",
"memory": "11.4 KB",
"Plans": [
{
"Node ID": "TableReader_15",
"Plan Rows": 293818698.60,
"Actual Rows": 293936693,
"task": "root",
"access object": "",
"operator info": "",
"execution info": "time:13.4s, loops:4, cop_task: {num: 655, max: 2.83s, min: 574.8µs, avg: 595ms, p95: 1.65s, max_proc_keys: 465701, p95_proc_keys: 458343, tot_proc: 5m50.1s, tot_wait: 24.2s, rpc_num: 655, rpc_time: 6m29.7s, copr_cache_hit_ratio: 0.55} ",
"disk": "N/A",
"memory": "6.34 KB",
"Plans": [
{
"Node ID": "Selection_13",
"Plan Rows": 293818698.60,
"Actual Rows": 293936693,
"task": "cop[tikv]",
"access object": "",
"operator info": "le(tpch50.lineitem.l_shipdate, 1998-08-15)",
"execution info": "tikv_task:{proc max:893ms, min:270ms, p80:576ms, p95:676ms, iters:293267, tasks:655}",
"disk": "N/A",
"memory": "N/A",
"Plans": [
{
"Node ID": "TableFullScan_12",
"Plan Rows": 2.94,
"Actual Rows": 2620,
"task": "cop[tikv]",
"access object": "table:lineitem",
"operator info": "keep order:false",
"execution info": "tikv_task:{proc max:871ms, min:252ms, p80:555ms, p95:652ms, iters:293267, tasks:655}",
"disk": "N/A",
"memory": "N/A"
}
]
}
]
}
]
}
]
}
}
]
这是第一套针对 TiDB 执行计划进行图形展示的项目，我们会根据现有 TiDB 执行计划内容做相应适配和整理工作； 独立的 UI 会更加灵活地实现我们想展示的风格和交互形式；与 TiDB Dashboard 做集成则有方便易用，自动部署，官方维护，风格统一的好处。
无。
使用 EXPLAIN SQL 语句可查看 TiDB 执行某条语句时选用的执行计划，后端服务会将生成的执行计划结果格式化为 JSON 格式的数据, 通过接口返回给 Dashboard 前端。
Dashboard 前端解析返回的 JSON 数据，根据预设的字段类型和和图表的映射关系，渲染为图表。
@92hackers focus on frontend
@chrysan focus on backend
@Tammyxia focus on testing
@yiwen92 focus on project
1st meeting note: Setup team, Sync-up idea
Task: Preparation & investigation RFC draft Repo setup
2nd meeting note：framework discussion, responsibility division
Task: Frontend work & Backend work RFC commit & Org setup Presenting initial
演示。 Demo https://tidb-hackathon-pev2-d8qnp31ff-92hackers.vercel.app/ Vedio https://mp.weixin.qq.com/s/reCnLs0e4IX9e8CgHBd0dA
无。