1. pt-query-digest 版本
3.0.9
2. pt-query-digest 输出结果分析
##第一部分总体信息
# 25.3s user time, 1.7s system time, 127.93M rss, 330.44M vsz
#本次日志分析消耗的系统资源情况，包括 消耗的cpu用户时间及cpu系统时间，占用的系统物理内存大小及系统虚拟内存大小。
# Current date: Tue Aug 27 09:20:51 2019
#本次分析开始时间
# Hostname: vm_192_168_20_111_centos
#本次分析主机名(运行pt-query-digest脚本的服务器主机名)
# Files: vm_192_168_20_111_centos-slow.log
#本次pt-query-digest分析的文件名
# Overall: 41.82k total, 312 unique, 0.06 QPS, 0.02x concurrency _________
#本次分析的语句的总数量; 唯一语句数量(对语句进行格式化然后去重后的语句数量); QPS(用上面的Overall除以下面Time range); 并发数
# Time range: 2019-08-18T00:00:00 to 2019-08-25T23:55:00
#本次分析的语句的实际跨度(可以通过--since 和 --until 参数指定分析某个时间段内的语句,当然不是你使用了--since和--until后这里的Time range就是你参数指定的值，Time range值由你指定范围内第一条及最后一条语句的执行时间决定)
#下面开始是本次分析语句执行情况的总的摘要
# 属性名称 属性总计值 属性最小值 属性最大值 平均值
# Attribute total min max avg 95% stddev median
# ============ ======= ======= ======= ======= ======= ======= =======
# Exec time 12858s 100ms 9s 307ms 992ms 406ms 128ms
# Lock time 24s 0 6s 568us 152us 30ms 66us
# Rows sent 26.14M 0 30.07k 655.33 4.27k 2.23k 0.99
# Rows examine 21.52G 0 15.89M 539.52k 1.61M 548.26k 462.39k
# Query size 367.15M 6 970.25k 8.99k 1012.63 88.68k 143.84
#下面是摘要部分详细解释
---------------------------------------------------------------------------Exec time->total 表示本次分析的所有语句总的执行时间为 12858sExec time->min 表示本次分析的所有语句中最小执行时间为 100msExec time->max 表示本次分析的所有语句中最大执行时间为 9sExec time->avg 表示本次分析的所有语句的平均执行时间为 307msExec time->95% 表示本次分析的所有语句有百分之95的执行时间小于 992sExec time->stddev 标准偏差，表示所有值偏离平均值的波动程度 （计算这个是从百分之95来算出的，排除了特别大的数）Exec time->median 本次分析的所有语句执行时间的中位数 （计算这个是从百分之95来算出的，排除了特别大的数）其他属性不一一列举，同上面的分析类似
---------------------------------------------------------------------------## 第二部分，语句分组统计(格式化后的语句)
# Profile
# Rank Query ID Response time Calls R/Call V/M Item
# ==== ================== =============== ===== ====== ===== =============
# 1 0x045645AD93CE7CCF 2824.8649 22.0% 2281 1.2384 0.26 UPDATE test_vehicle_his_insurance
# 2 0x1D24EE2380F3EBE8 1115.0075 8.7% 8591 0.1298 0.00 SELECT test_geo_fence_point
# 3 0x0206DF96E75631AC 1084.0781 8.4% 1152 0.9410 0.01 UPDATE SELECT test_driver_mileage_month test_driver_overkm
# 4 0xA133D65F1CBD1FB0 717.0132 5.6% 6556 0.1094 0.00 SELECT test_repair_job_material
# 5 0xE1A76A51FD24CB09 612.3070 4.8% 1278 0.4791 0.00 SELECT test_statementest_order_track
# 6 0xEED367F9ED0291A9 543.1165 4.2% 1278 0.4250 0.00 SELECT test_statementest_order_track
# 7 0xC484F9262CFD4F9E 494.2454 3.8% 2304 0.2145 0.03 SELECT test_fin_cost
# 8 0xEA49393BAD5DE9BD 454.3905 3.5% 896 0.5071 0.00 SELECT test_statementest_order_track
# 9 0x5C7D03C68F531CA1 407.6589 3.2% 896 0.4550 0.00 SELECT test_statementest_order_track
# 10 0x7036718B32B40AC9 400.9848 3.1% 192 2.0885 0.01 UPDATE test_statementest_order_track
# 11 0xAC978D1E2F1D1303 381.9412 3.0% 94 4.0632 0.78 SELECT test_statementest_order_track
# 12 0x8CF9C99E322B9A72 277.1513 2.2% 2423 0.1144 0.00 UPDATE test_insurance_job_dtl
# 13 0x92642515C84B0824 156.6433 1.2% 542 0.2890 0.00 SELECT test_statementest_customer_group_track
# 14 0xFA583B1B9AE159C4 143.4585 1.1% 286 0.5016 0.00 SELECT test_statementest_order_track
# 15 0x196DCBF490C12E29 138.6476 1.1% 258 0.5374 0.00 SELECT test_statementest_order_track
# 16 0xD43A7A94940255A7 128.6958 1.0% 286 0.4500 0.00 SELECT test_statementest_order_track
# 17 0x68781873C2D7B4F0 125.1531 1.0% 258 0.4851 0.00 SELECT test_statementest_order_track
# 18 0x1E6A990854E81B49 123.1654 1.0% 192 0.6415 0.00 UPDATE test_statementest_customer_group_track
# 19 0x84C0D30BBD146E67 111.0259 0.9% 1015 0.1094 0.00 SELECT test_repair_job_material
# 20 0x7CFB8B37AE3A1204 106.6400 0.8% 41 2.6010 0.01 SELECT test_statementest_order_track
# 43 0x65447DD1C4529805 38.1800 0.3% 15 2.5453 0.00 SELECT test_statementest_order_track
# 46 0x4A1C3AEBDF0F48A7 33.1424 0.3% 12 2.7619 0.01 SELECT test_statementest_order_track
# 52 0xF6E2D7980398900C 25.9197 0.2% 10 2.5920 0.01 SELECT test_statementest_order_track
# MISC 0xMISC 2414.1565 18.8% 10964 0.2202 0.0 <289 ITEMS>
-------------------------------------------------------------------------------------------
第二部分的解释如下：
Rank： 该分组在本次分析的所有分组的排名，默认按查询时间降序排列，通过--order-by指定排序规则
Query ID：语句的ID，（去掉多余空格和文本字符，计算hash值）
Response：该分组中所有语句总的响应时间
time： 该分组所有语句响应时间占本次分析的所有语句总的响应时间的百分比
calls： 执行次数，即本次分析的语句中总共有多少条这种类型的语句被执行
R/Call： 平均每次执行的响应时间
V/M： 响应时间的方差均值比，方差均值比也就是常说的离差指数
Item： 具体的查询语句(语句进行了截断)
最后一行 MISC 表示所有其他的语句的相关属性值
-------------------------------------------------------------------------------------------## 第三部分，针对每个语句分组的分析(并不是所有分组都会在此列出，如下两个条件满足其一即被列出，1.满足 --limit 条件; 2.满足 --outliers条件),此次分析--limit及--outliers都使用的默认值
# Query 1: 0.01 QPS, 0.01x concurrency, ID 0x045645AD93CE7CCF at byte 728432441
(分组语句序号)，(分组语句qps)，(并发数)，(去掉多余空格和文本字符计算hash值)，(语句在文件中的位置)
# This item is included in the report because it matches --limit.
这里给出该分组被列出的理由(被列出有两种可能，1.满足 --limit 条件; 2.满足 --outliers条件)。这里因为满足--limit 条件所以该分组被列出
# Scores: V/M = 0.26
响应时间方差均值比
# Time range: 2019-08-19T15:42:47 to 2019-08-23T15:46:06
该分组语句出现的时间跨度(最早及最晚一次语句出现的时间)
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 5 2281
# Exec time 21 2825s 100ms 2s 1s 2s 569ms 1sCount->pct 表示分组中语句数量占本次分析总语句数量的百分比Count->total 表示分组中总的语句数量Exec time->pct 表示本组语句的执行时间占本次分析所有语句执行时间的百分比Exec time->total 表示本组所有语句总的执行时间为 2825sExec time->min 表示本组所有语句中最小执行时间为 100msExec time->max 表示本组所有语句中最大执行时间为 2sExec time->avg 表示本组所有语句的平均执行时间为 1sExec time->95% 表示本组所有语句中有百分之95的执行时间小于 2sExec time->stddev 标准偏差，表示所有值偏离平均值的波动程度 （计算这个是从百分之95来算出的，排除了特别大的数）Exec time->median 本组语句执行时间的中位数 （计算这个是从百分之95来算出的，排除了特别大的数）其他属性不一一列举，同上面的分析类似
# Lock time 1 246ms 41us 50ms 107us 93us 1ms 60us
# Rows sent 0 0 0 0 0 0 0 0
# Rows examine 0 190.09M 6.20k 86.82k 85.34k 83.83k 3.22k 83.83k
# Query size 0 337.95k 137 161 151.72 158.58 6.25 143.84
# String:
# Databases xxxx_vdm2_vehicle
语句中表属于哪个库
# Hosts 10.191.16.124 (1146/50%)... 1 more
查询请求来自哪个服务器，如果查询请求来自多个ip，会列出部分ip请求的次数及占比(猜测会列出占比比较高的ip，如果占比比较低不会列出)
# Users vdm2_vehicle_user
执行该查询的用户名(是否会出现多个用户名？)
# Query_time distribution
查询时间分布
# 1us
# 10us
# 100us
# 1ms
# 10ms
# 100ms ###########################
# 1s ################################################################
# 10s+
如果你想优化该分组语句，可以复制下面被注释的语句 show table status,show create table,以及explain 语句查看表相信以及执行计划.这里考虑到一些语句可能不支持 explain，所以explain 处对可能不支持的语句进行了改写(这里把update改写成了select,其实update语句早已支持直接explain了)
# Tables
# SHOW TABLE STATUS FROM `xxxx_vdm2_vehicle` LIKE 'test_vehicle_his_insurance'\G
# SHOW CREATE TABLE `xxxx_vdm2_vehicle`.`test_vehicle_his_insurance`\G
update test_vehicle_his_insurance set job_status=6, update_time=NOW() where vehicle_id=24197 and insurance_code='THIRD_PARTY' and insured_amount=5000000\G
# Converted for EXPLAIN
因为最开始mysql并不支持直接查询update语句的执行计划，所以这里把update语句改写成select 语句
# EXPLAIN /*!50100 PARTITIONS*/
select job_status=6, update_time=NOW() from test_vehicle_his_insurance where vehicle_id=24197 and insurance_code='THIRD_PARTY' and insured_amount=5000000\G# Query 2: 0.03 QPS, 0.00x concurrency, ID 0x1D24EE2380F3EBE8 at byte 577601815
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2019-08-19T09:32:36 to 2019-08-22T17:17:08
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 20 8591
# Exec time 8 1115s 125ms 306ms 130ms 134ms 7ms 122ms
# Lock time 2 560ms 33us 220us 65us 93us 18us 60us
# Rows sent 72 18.89M 0 25.19k 2.25k 9.33k 3.91k 27.38
# Rows examine 18 3.96G 483.60k 483.60k 483.60k 483.60k 0 483.60k
# Query size 0 1.23M 150 151 150.24 143.84 0 143.84
# String:
# Databases xxxx_sr_cfg
# Hosts 10.191.11.16
# 100us
# 1ms
# Tables
# SHOW TABLE STATUS FROM `xxxx_sr_cfg` LIKE 'test_geo_fence_point'\G
# SHOW CREATE TABLE `xxxx_sr_cfg`.`test_geo_fence_point`\G
# EXPLAIN /*!50100 PARTITIONS*/
select geo_fence_id as geoFenceId, longitude as lon, latitude as lat, area_group as `group` from test_geo_fence_point where 1 = 1 and geo_fence_id = 909\G# Query 3: 0.00 QPS, 0.00x concurrency, ID 0x0206DF96E75631AC at byte 673957352
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.01
# Time range: 2019-08-18T00:00:01 to 2019-08-25T23:50:01
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 2 1152
# Exec time 8 1084s 769ms 1s 941ms 992ms 98ms 992ms
# Lock time 0 170ms 124us 372us 147us 167us 15us 144us
# Rows sent 0 0 0 0 0 0 0 0
# Rows examine 4 1002.60M 739.83k 959.95k 891.20k 915.49k 76.97k 915.49k
# Query size 0 1.97M 1.50k 1.92k 1.75k 1.86k 168.46 1.78k
# String:
# Databases xxxx_vdm2_driverjob
# Hosts 10.191.16.12 (578/50%), 10.191.16.118 (574/49%)
# Users vdm2_driverjob_user
# Query_time distribution
# 1us
# 10us
# 100us
# 1ms
# 10ms
# 100ms #################################################
# 1s ################################################################
# 10s+
# Tables
# SHOW TABLE STATUS FROM `xxxx_vdm2_driverjob` LIKE 'test_driver_mileage_month'\G
# SHOW CREATE TABLE `xxxx_vdm2_driverjob`.`test_driver_mileage_month`\G
# SHOW TABLE STATUS FROM `xxxx_vdm2_driverjob` LIKE 'test_driver_overkm'\G
# SHOW CREATE TABLE `xxxx_vdm2_driverjob`.`test_driver_overkm`\G
# Converted for EXPLAIN
# EXPLAIN /*!50100 PARTITIONS*/# Query 4: 0.01 QPS, 0.00x concurrency, ID 0xA133D65F1CBD1FB0 at byte 780415452
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2019-08-18T08:07:24 to 2019-08-25T17:07:41
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 15 6556
# Exec time 5 717s 103ms 213ms 109ms 110ms 5ms 105ms
# Lock time 1 299ms 29us 112us 45us 69us 10us 40us
# Rows sent 0 19.23k 0 32 3.00 5.75 1.74 2.90
# Rows examine 6 1.40G 213.96k 232.77k 223.51k 222.42k 5.25k 222.42k
# Query size 0 492.98k 76 77 77.00 76.28 0.09 76.28
# String:
# Databases xxxx_vdm2_vehiclejob
# 1us
# 10us
# 100us
# 1ms
# 10ms
# 100ms ################################################################
# 1s
# 10s+
# Tables
# SHOW TABLE STATUS FROM `xxxx_vdm2_vehiclejob` LIKE 'test_repair_job_material'\G
# SHOW CREATE TABLE `xxxx_vdm2_vehiclejob`.`test_repair_job_material`\G
# EXPLAIN /*!50100 PARTITIONS*/
select * from test_repair_job_material where company_id = 1 and job_id = 2009904\G# Query 5: 0.00 QPS, 0.00x concurrency, ID 0xE1A76A51FD24CB09 at byte 715708316
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2019-08-19T08:51:39 to 2019-08-25T15:12:53
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 3 1278
# Exec time 4 612s 438ms 851ms 479ms 501ms 26ms 455ms
# Lock time 0 119ms 56us 157us 93us 119us 17us 89us
# Rows sent 0 12.39k 1 10 9.93 9.83 0.55 9.83
# Rows examine 9 2.01G 1.49M 1.81M 1.61M 1.69M 82.95k 1.61M
# Query size 0 599.50k 453 592 480.35 487.09 18.60 463.90
# String:
# Databases xxxx_lr_settlement
# Hosts 10.191.17.1 (651/50%), 10.191.17.112 (627/49%)
# Users xxxx_lr_settlementest_user
# Query_time distribution
# 1us
# 10us
# 100us
# 1ms
# 10ms
# 100ms ################################################################
# 1s
# 10s+
# Tables
# SHOW TABLE STATUS FROM `xxxx_lr_settlement` LIKE 'test_statementest_order_track'\G
# SHOW CREATE TABLE `xxxx_lr_settlement`.`test_statementest_order_track`\G
# EXPLAIN /*!50100 PARTITIONS*/# Query 6: 0.00 QPS, 0.00x concurrency, ID 0xEED367F9ED0291A9 at byte 715707932
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2019-08-19T08:51:39 to 2019-08-25T15:12:53
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 3 1278
# Exec time 4 543s 388ms 565ms 425ms 455ms 22ms 412ms
# Lock time 0 80ms 42us 133us 62us 76us 10us 60us
# Rows sent 0 1.25k 1 1 1 1 0 1
# Rows examine 9 2.01G 1.49M 1.81M 1.61M 1.69M 82.95k 1.61M
# Query size 0 212.57k 143 282 170.32 183.58 14.96 166.51
# String:
# Databases xxxx_lr_settlement
# Hosts 10.191.17.1 (651/50%), 10.191.17.112 (627/49%)
# Users xxxx_lr_settlementest_user
# Query_time distribution
# 1us
# 10us
# 100us
# 1ms
# 10ms
# 100ms ################################################################
# 1s
# 10s+
# Tables
# SHOW TABLE STATUS FROM `xxxx_lr_settlement` LIKE 'test_statementest_order_track'\G
# SHOW CREATE TABLE `xxxx_lr_settlement`.`test_statementest_order_track`\G
# EXPLAIN /*!50100 PARTITIONS*/
select count(1) from test_statementest_order_track where is_lasted = 1 and settlementest_company in (40) order by create_time desc\G# Query 7: 0.00 QPS, 0.00x concurrency, ID 0xC484F9262CFD4F9E at byte 822464768
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.03
# Time range: 2019-08-18T00:00:00 to 2019-08-25T23:55:00
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 5 2304
# Exec time 3 494s 149ms 409ms 215ms 356ms 85ms 163ms
# Lock time 0 163ms 50us 159us 70us 89us 12us 66us
# Rows sent 14 3.81M 1.58k 1.94k 1.69k 1.86k 125.53 1.61k
# Rows examine 0 202.94M 63.95k 152.38k 90.20k 150.54k 38.57k 65.68k
# Query size 0 193.50k 86 86 86 86 0 86
# String:
# Databases xxxx_vdm2_declare
# 10us
# 100us
# 1ms
# 10ms
# 100ms ################################################################
# 1s
# 10s+
# Tables
# SHOW TABLE STATUS FROM `xxxx_vdm2_declare` LIKE 'test_fin_cost'\G
# SHOW CREATE TABLE `xxxx_vdm2_declare`.`test_fin_cost`\G
# EXPLAIN /*!50100 PARTITIONS*/
select * from test_fin_cost where need_settle = 1 and sttl_status = 1 and send_status = 2\G# Query 8: 0.00 QPS, 0.00x concurrency, ID 0xEA49393BAD5DE9BD at byte 780138505
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2019-08-19T08:59:58 to 2019-08-25T12:30:27
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 2 896
# Exec time 3 454s 468ms 590ms 507ms 526ms 26ms 501ms
# Lock time 0 101ms 79us 180us 112us 138us 18us 108us
# Rows sent 0 832 0 10 0.93 1.96 0.73 0.99
# Rows examine 6 1.40G 1.49M 1.80M 1.60M 1.69M 80.57k 1.53M
# Query size 0 471.24k 502 553 538.56 537.02 13.92 537.02
# String:
# Databases xxxx_lr_settlement
# Hosts 10.191.17.112 (451/50%), 10.191.17.1 (445/49%)
# Users xxxx_lr_settlementest_user
# Query_time distribution
# 100us
# 1ms
# 10ms
# 100ms ################################################################
# 1s
# 10s+
# Tables
# SHOW TABLE STATUS FROM `xxxx_lr_settlement` LIKE 'test_statementest_order_track'\G
# SHOW CREATE TABLE `xxxx_lr_settlement`.`test_statementest_order_track`\G
# EXPLAIN /*!50100 PARTITIONS*/# Query 9: 0.00 QPS, 0.00x concurrency, ID 0x5C7D03C68F531CA1 at byte 780208355
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2019-08-19T08:59:57 to 2019-08-25T12:30:26
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 2 896
# Exec time 3 408s 418ms 536ms 455ms 477ms 23ms 455ms
# Lock time 0 76ms 63us 347us 84us 98us 14us 80us
# Rows sent 0 896 1 1 1 1 0 1
# Rows examine 6 1.40G 1.49M 1.80M 1.60M 1.69M 80.57k 1.53M
# Query size 0 199.99k 192 243 228.56 234.30 7.74 223.14
# String:
# Databases xxxx_lr_settlement
# Hosts 10.191.17.112 (451/50%), 10.191.17.1 (445/49%)
# Users xxxx_lr_settlementest_user
# Query_time distribution
# 1us
# 10us
# 100us
# 1ms
# 10ms
# 100ms ################################################################
# 1s
# 10s+
# Tables
# SHOW TABLE STATUS FROM `xxxx_lr_settlement` LIKE 'test_statementest_order_track'\G
# SHOW CREATE TABLE `xxxx_lr_settlement`.`test_statementest_order_track`\G
# EXPLAIN /*!50100 PARTITIONS*/# Query 10: 0.00 QPS, 0.00x concurrency, ID 0x7036718B32B40AC9 at byte 881135888
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.01
# Time range: 2019-08-18T00:05:01 to 2019-08-25T23:05:02
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 0 192
# Exec time 3 401s 2s 2s 2s 2s 156ms 2s
# Lock time 0 29ms 41us 4ms 152us 108us 546us 52us
# Rows sent 0 0 0 0 0 0 0 0
# Rows examine 1 312.25M 1.42M 1.83M 1.63M 1.77M 122.40k 1.61M
# Query size 0 21.56k 115 115 115 115 0 115
# String:
# Databases xxxx_lr_settlement
# Hosts 10.191.17.169 (96/50%), 10.191.17.19 (96/50%)
# Users xxxx_lr_settlementest_user
# 1ms
# 10ms
# 100ms
# 1s ################################################################
# 10s+
# Tables
# SHOW TABLE STATUS FROM `xxxx_lr_settlement` LIKE 'test_statementest_order_track'\G
# SHOW CREATE TABLE `xxxx_lr_settlement`.`test_statementest_order_track`\G
update test_statementest_order_track set is_lasted = 2, update_by = 'timer', update_time = NOW() where is_lasted = 1\G
# Converted for EXPLAIN
# EXPLAIN /*!50100 PARTITIONS*/
select is_lasted = 2, update_by = 'timer', update_time = NOW() from test_statementest_order_track where is_lasted = 1\G# Query 11: 0.01 QPS, 0.03x concurrency, ID 0xAC978D1E2F1D1303 at byte 728259422
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.78
# Time range: 2019-08-22T13:16:45 to 2019-08-22T16:20:35
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 0 94
# Exec time 2 382s 3s 9s 4s 8s 2s 3s
# Lock time 0 10ms 76us 159us 106us 131us 17us 98us
# Rows sent 0 94 1 1 1 1 0 1
# Rows examine 0 156.81M 1.66M 1.67M 1.67M 1.61M 0 1.61M
# Query size 0 51.18k 556 601 557.51 537.02 8.76 537.02
# String:
# Databases xxxx_lr_settlement
# Hosts 10.191.17.1 (47/50%), 10.191.17.112 (47/50%)
# Users xxxx_lr_settlementest_user
# 10us
# 100us
# 100ms
# 1s ################################################################
# 10s+
# Tables
# SHOW TABLE STATUS FROM `xxxx_lr_settlement` LIKE 'test_statementest_order_track'\G
# SHOW CREATE TABLE `xxxx_lr_settlement`.`test_statementest_order_track`\G
# EXPLAIN /*!50100 PARTITIONS*/# Query 12: 0.01 QPS, 0.00x concurrency, ID 0x8CF9C99E322B9A72 at byte 673956891
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2019-08-19T10:31:47 to 2019-08-23T17:11:11
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 5 2423
# Exec time 2 277s 106ms 158ms 114ms 116ms 4ms 110ms
# Lock time 0 184ms 42us 4ms 75us 98us 74us 69us
# Rows sent 0 0 0 0 0 0 0 0
# Rows examine 1 254.71M 103.66k 109.21k 107.64k 106.99k 1.47k 106.99k
# Query size 0 560.58k 217 255 236.91 246.02 10.97 234.30
# String:
# Databases xxxx_vdm2_vehiclejob
# Hosts 10.191.16.157 (1516/62%)... 1 more
# Users vdm2_vehiclejob_user
# Query_time distribution
# 1us
# 10ms
# 100ms ################################################################
# 1s
# 10s+
# Tables
# SHOW TABLE STATUS FROM `xxxx_vdm2_vehiclejob` LIKE 'test_insurance_job_dtl'\G
# SHOW CREATE TABLE `xxxx_vdm2_vehiclejob`.`test_insurance_job_dtl`\G
# Converted for EXPLAIN
# EXPLAIN /*!50100 PARTITIONS*/# Query 13: 0.00 QPS, 0.00x concurrency, ID 0x92642515C84B0824 at byte 783919813
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2019-08-19T08:53:28 to 2019-08-25T15:00:54
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 1 542
# Exec time 1 157s 261ms 373ms 289ms 293ms 14ms 279ms
# Lock time 0 47ms 55us 147us 87us 113us 16us 80us
# Rows sent 0 4.59k 0 10 8.67 9.83 3.27 9.83
# Rows examine 1 261.94M 447.76k 556.07k 494.88k 509.78k 22.00k 485.50k
# Query size 0 244.85k 434 573 462.60 463.90 19.38 463.90
# String:
# Databases xxxx_lr_settlement
# Hosts 10.191.17.112 (273/50%), 10.191.17.1 (269/49%)
# Users xxxx_lr_settlementest_user
# 1ms
# 10ms
# 100ms ################################################################
# 1s
# 10s+
# Tables
# SHOW TABLE STATUS FROM `xxxx_lr_settlement` LIKE 'test_statementest_customer_group_track'\G
# SHOW CREATE TABLE `xxxx_lr_settlement`.`test_statementest_customer_group_track`\G
# EXPLAIN /*!50100 PARTITIONS*/# Query 14: 0.00 QPS, 0.00x concurrency, ID 0xFA583B1B9AE159C4 at byte 773131426
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2019-08-19T10:39:54 to 2019-08-23T16:46:29
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 0 286
# Exec time 1 143s 474ms 568ms 502ms 526ms 24ms 477ms
# Lock time 0 32ms 81us 147us 112us 138us 18us 103us
# Rows sent 0 198 0 3 0.69 1.96 0.74 0.99
# Rows examine 2 450.67M 1.50M 1.72M 1.58M 1.69M 80.08k 1.53M
# Query size 0 149.58k 500 644 535.55 537.02 15.71 511.45
# String:
# Databases xxxx_lr_settlement
# Hosts 10.191.17.112 (147/51%), 10.191.17.1 (139/48%)
# Users xxxx_lr_settlementest_user
# Query_time distribution
# 10ms
# 100ms ################################################################
# 1s
# 10s+
# Tables
# SHOW TABLE STATUS FROM `xxxx_lr_settlement` LIKE 'test_statementest_order_track'\G
# SHOW CREATE TABLE `xxxx_lr_settlement`.`test_statementest_order_track`\G
# EXPLAIN /*!50100 PARTITIONS*/# Query 15: 0.00 QPS, 0.00x concurrency, ID 0x196DCBF490C12E29 at byte 877097315
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2019-08-19T10:13:27 to 2019-08-25T15:00:32
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 0 258
# Exec time 1 139s 493ms 632ms 537ms 552ms 26ms 526ms
# Lock time 0 29ms 82us 175us 112us 144us 18us 108us
# Rows sent 0 622 0 20 2.41 9.83 3.23 0.99
# Rows examine 1 414.92M 1.50M 1.81M 1.61M 1.69M 74.67k 1.53M
# Query size 0 138.85k 516 603 551.10 563.87 18.06 537.02
# String:
# Databases xxxx_lr_settlement
# Hosts 10.191.17.1 (132/51%), 10.191.17.112 (126/48%)
# Users xxxx_lr_settlementest_user
# Query_time distribution
# 100us
# 1ms
# 10ms
# 100ms ################################################################
# 1s
# 10s+
# Tables
# SHOW TABLE STATUS FROM `xxxx_lr_settlement` LIKE 'test_statementest_order_track'\G
# SHOW CREATE TABLE `xxxx_lr_settlement`.`test_statementest_order_track`\G
# EXPLAIN /*!50100 PARTITIONS*/# Query 16: 0.00 QPS, 0.00x concurrency, ID 0xD43A7A94940255A7 at byte 722958436
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2019-08-19T10:39:54 to 2019-08-23T16:46:28
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 0 286
# Exec time 1 129s 423ms 547ms 450ms 477ms 23ms 433ms
# Lock time 0 25ms 62us 148us 86us 108us 13us 80us
# Rows sent 0 286 1 1 1 1 0 1
# Rows examine 2 450.67M 1.50M 1.72M 1.58M 1.69M 80.08k 1.53M
# Query size 0 62.99k 190 334 225.55 234.30 13.88 223.14
# String:
# Databases xxxx_lr_settlement
# Hosts 10.191.17.112 (147/51%), 10.191.17.1 (139/48%)
# 1us
# 10us
# 100us
# 1ms
# 10ms
# 100ms ################################################################
# 1s
# 10s+
# Tables
# SHOW TABLE STATUS FROM `xxxx_lr_settlement` LIKE 'test_statementest_order_track'\G
# SHOW CREATE TABLE `xxxx_lr_settlement`.`test_statementest_order_track`\G
# EXPLAIN /*!50100 PARTITIONS*/# Query 17: 0.00 QPS, 0.00x concurrency, ID 0x68781873C2D7B4F0 at byte 877096423
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2019-08-19T10:13:26 to 2019-08-25T15:00:31
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 0 258
# Exec time 0 125s 442ms 570ms 485ms 526ms 24ms 477ms
# Lock time 0 22ms 61us 132us 85us 103us 10us 80us
# Rows sent 0 258 1 1 1 1 0 1
# Rows examine 1 414.92M 1.50M 1.81M 1.61M 1.69M 74.67k 1.53M
# Query size 0 60.75k 206 293 241.10 271.23 19.97 234.30
# String:
# Databases xxxx_lr_settlement
# Hosts 10.191.17.1 (132/51%), 10.191.17.112 (126/48%)
# Users xxxx_lr_settlementest_user
# Query_time distribution
# 1us
# 10us
# 100us
# 1ms
# 10ms
# 100ms ################################################################
# 1s
# 10s+
# Tables
# SHOW TABLE STATUS FROM `xxxx_lr_settlement` LIKE 'test_statementest_order_track'\G
# SHOW CREATE TABLE `xxxx_lr_settlement`.`test_statementest_order_track`\G
# EXPLAIN /*!50100 PARTITIONS*/# Query 18: 0.00 QPS, 0.00x concurrency, ID 0x1E6A990854E81B49 at byte 887153274
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2019-08-18T00:05:02 to 2019-08-25T23:05:03
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 0 192
# Exec time 0 123s 546ms 741ms 641ms 705ms 51ms 640ms
# Lock time 0 14ms 48us 148us 74us 113us 23us 60us
# Rows sent 0 0 0 0 0 0 0 0
# Rows examine 0 92.48M 422.66k 561.82k 493.23k 535.27k 39.01k 485.50k
# Query size 0 23.25k 124 124 124 124 0 124
# String:
# Databases xxxx_lr_settlement
# Hosts 10.191.17.169 (96/50%), 10.191.17.19 (96/50%)
# Users xxxx_lr_settlementest_user
# Query_time distribution
# 1us
# 10us
# 100us
# 1ms
# 10ms
# 100ms ################################################################
# 1s
# 10s+
# Tables
# SHOW TABLE STATUS FROM `xxxx_lr_settlement` LIKE 'test_statementest_customer_group_track'\G
# SHOW CREATE TABLE `xxxx_lr_settlement`.`test_statementest_customer_group_track`\G
update test_statementest_customer_group_track set is_lasted = 2, update_by = 'timer', update_time = NOW() where is_lasted = 1\G
# Converted for EXPLAIN
# EXPLAIN /*!50100 PARTITIONS*/
select is_lasted = 2, update_by = 'timer', update_time = NOW() from test_statementest_customer_group_track where is_lasted = 1\G# Query 19: 0.00 QPS, 0.00x concurrency, ID 0x84C0D30BBD146E67 at byte 728253391
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2019-08-19T08:04:44 to 2019-08-24T16:12:50
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 2 1015
# Exec time 0 111s 107ms 199ms 109ms 116ms 5ms 105ms
# Lock time 0 60ms 37us 126us 58us 84us 14us 52us
# Rows sent 0 3.02k 0 12 3.05 5.75 2.12 2.90
# Rows examine 0 214.36M 213.97k 232.73k 216.26k 222.42k 3.00k 211.82k
# Query size 0 76.02k 76 77 76.69 76.28 0.50 76.28
# String:
# Databases xxxx_vdm2_vehiclejob
# 1us
# 10us
# 100us
# 1ms
# 10ms
# 100ms ################################################################
# 1s
# 10s+
# Tables
# SHOW TABLE STATUS FROM `xxxx_vdm2_vehiclejob` LIKE 'test_repair_job_material'\G
# SHOW CREATE TABLE `xxxx_vdm2_vehiclejob`.`test_repair_job_material`\G
# EXPLAIN /*!50100 PARTITIONS*/
select * from test_repair_job_material where job_id = 2001625 and company_id = 1\G# Query 20: 0.00 QPS, 0.00x concurrency, ID 0x7CFB8B37AE3A1204 at byte 575309473
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.01
# Time range: 2019-08-19T13:49:08 to 2019-08-20T14:45:30
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 0 41
# Exec time 0 107s 2s 3s 3s 3s 149ms 3s
# Lock time 0 4ms 78us 139us 106us 125us 18us 108us
# Rows sent 0 32 0 4 0.78 2.90 1.09 0
# Rows examine 0 61.93M 1.50M 1.56M 1.51M 1.53M 22.12k 1.46M
# Query size 0 19.84k 494 503 495.61 487.09 0 487.09
# String:
# Databases xxxx_lr_settlement
# Hosts 10.191.17.1 (24/58%), 10.191.17.112 (17/41%)
# Users xxxx_lr_settlementest_user
# 100us
# 1ms
# 10ms
# 100ms
# 1s ################################################################
# 10s+
# Tables
# SHOW TABLE STATUS FROM `xxxx_lr_settlement` LIKE 'test_statementest_order_track'\G
# SHOW CREATE TABLE `xxxx_lr_settlement`.`test_statementest_order_track`\G
# EXPLAIN /*!50100 PARTITIONS*/# Query 43: 0.00 QPS, 0.00x concurrency, ID 0x65447DD1C4529805 at byte 674149700
# This item is included in the report because it matches --outliers.
# Scores: V/M = 0.00
# Time range: 2019-08-19T11:10:59 to 2019-08-21T15:37:11
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 0 15
# Exec time 0 38s 2s 3s 3s 3s 94ms 3s
# Lock time 0 2ms 83us 134us 106us 131us 17us 98us
# Rows sent 0 673 21 81 44.87 76.28 21.06 40.45
# Rows examine 0 23.15M 1.50M 1.61M 1.54M 1.61M 59.79k 1.46M
# Query size 0 7.27k 496 496 496 496 0 496
# String:
# Databases xxxx_lr_settlement
# Hosts 10.191.17.112 (8/53%), 10.191.17.1 (7/46%)
# Users xxxx_lr_settlementest_user
# 100us
# 1ms
# 10ms
# 100ms
# 1s ################################################################
# 10s+
# Tables
# SHOW TABLE STATUS FROM `xxxx_lr_settlement` LIKE 'test_statementest_order_track'\G
# SHOW CREATE TABLE `xxxx_lr_settlement`.`test_statementest_order_track`\G
# EXPLAIN /*!50100 PARTITIONS*/# Query 46: 0.02 QPS, 0.05x concurrency, ID 0x4A1C3AEBDF0F48A7 at byte 570791439
# This item is included in the report because it matches --outliers.
# Scores: V/M = 0.01
# Time range: 2019-08-19T14:10:40 to 2019-08-19T14:22:51
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 0 12
# Exec time 0 33s 3s 3s 3s 3s 139ms 3s
# Lock time 0 1ms 72us 135us 102us 125us 21us 93us
# Rows sent 0 0 0 0 0 0 0 0
# Rows examine 0 18.06M 1.50M 1.50M 1.50M 1.50M 0 1.50M
# Query size 0 5.61k 479 479 479 479 0 479
# String:
# Databases xxxx_lr_settlement
# Hosts 10.191.17.1 (6/50%), 10.191.17.112 (6/50%)
# Users xxxx_lr_settlementest_user
# 100us
# 1ms
# 10ms
# 100ms
# 1s ################################################################
# 10s+
# Tables
# SHOW TABLE STATUS FROM `xxxx_lr_settlement` LIKE 'test_statementest_order_track'\G
# SHOW CREATE TABLE `xxxx_lr_settlement`.`test_statementest_order_track`\G
# EXPLAIN /*!50100 PARTITIONS*/
select id as order_statementest_track_id, order_no, vehicle_plate, statementest_customer, customer_id, invoice_city, tax_rate, tax_type, biz_type, settlementest_company, settlementest_company_name, statementest_manager, recentest_reconciliation_deadline, reconciliation_day, notest_reconciled_amount, preinvoice_amount from test_statementest_order_track where is_lasted = 1 and order_no like concat('%', '180700019', '%') order by statementest_customer asc limit 0, 200\G# Query 52: 0.00 QPS, 0.00x concurrency, ID 0xF6E2D7980398900C at byte 620902146
# This item is included in the report because it matches --outliers.
# Scores: V/M = 0.01
# Time range: 2019-08-19T13:56:07 to 2019-08-20T14:47:27
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 0 10
# Exec time 0 26s 2s 3s 3s 3s 152ms 3s
# Lock time 0 1ms 78us 132us 100us 119us 19us 98us
# Rows sent 0 6 0 1 0.60 0.99 0.49 0.99
# Rows examine 0 15.14M 1.50M 1.56M 1.51M 1.53M 28.85k 1.46M
# Query size 0 4.70k 481 481 481 481 0 481
# String:
# Databases xxxx_lr_settlement
# Hosts 10.191.17.112 (6/60%), 10.191.17.1 (4/40%)
# Users xxxx_lr_settlementest_user
# Query_time distribution
# 1us
# 10us
# 100us
# 1ms
# 10ms
# 100ms
# 1s ################################################################
# 10s+
# Tables
# SHOW TABLE STATUS FROM `xxxx_lr_settlement` LIKE 'test_statementest_order_track'\G
# SHOW CREATE TABLE `xxxx_lr_settlement`.`test_statementest_order_track`\G
# EXPLAIN /*!50100 PARTITIONS*/
select id as order_statementest_track_id, order_no, vehicle_plate, statementest_customer, customer_id, invoice_city, tax_rate, tax_type, biz_type, settlementest_company, settlementest_company_name, statementest_manager, recentest_reconciliation_deadline, reconciliation_day, notest_reconciled_amount, preinvoice_amount from test_statementest_order_track where is_lasted = 1 and vehicle_plate like concat('%', 'JY1838', '%') order by statementest_customer asc limit 0, 200\G