Display the transaction and related SQL

[crazycs520]

Feature Request

Is your feature request related to a problem? Please describe:

Add a page such as below:

It's easy to see the total time of each transaction type and the related SQL in the transaction, and the time gap between each SQLs.

How to classify the transaction?

We can generate a digest for the transaction:

Here is a transaction example:

1. begin
2. select ...
3. update ...
4. update ...
5. commit

if SQL 3 and SQL 4 in the upper has same SQL digest, we the transaction SQL digest can be below:

sql_1 digest 
sql_2 digest 
sql_3 digest  -- suppose SQL 4 has the same digest with SQL 3
sql_5 digest 

Then we can use upper sql digests to generate a transaction digest.

How to collect the information?

Set tidb_slow_log_threshold variable to 0 value, then TiDB will record the every execute SQL. Then use txn_start_ts to get the sqls that execution in the transactions. such as below:

SELECT  MIN(time)
       ,COUNT(*)
       ,MIN(query_time)
       ,AVG(query_time)
       ,MAX(query_time)
       ,SUM(query_time)
       ,MIN(query)
       ,MIN(plan)
FROM `SLOW_QUERY`
WHERE txn_start_ts=417754666182377492 
AND time > "2020-07-01 20:43:50" 
AND time < "2020-07-01 20:45:53" 
GROUP BY  digest,plan_digest
ORDER BY MIN(time)\G

[scsldb]

What kind of scene will be used?

[breezewish]

@scsldb This reveals how TiDB clients (e.g. JDBC / Application) send SQL queries, which can identify the root cause when TiDB resource is not fully utilized.

[breezewish]

Dependent project: #710