forked from pingcap/tidb
-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathexplain_complex_stats.result
230 lines (230 loc) · 25.3 KB
/
explain_complex_stats.result
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
set tidb_cost_model_version=1;
set @@sql_mode='STRICT_TRANS_TABLES';
drop table if exists dt;
CREATE TABLE dt (
id int(11) unsigned NOT NULL,
aid varchar(32) NOT NULL comment '[[set=cn.sbkcq,us.sbkcq]]',
cm int(10) unsigned NOT NULL comment '[[range=1000,5000]]',
pt varchar(10) NOT NULL comment '[[set=android,ios]]',
dic varchar(64) DEFAULT NULL,
ip varchar(15) DEFAULT NULL,
ds date DEFAULT NULL comment '[[range=2016-01-01,2016-12-31]]',
ds2 varchar(13) DEFAULT NULL ,
t int(13) DEFAULT NULL comment '[[range=1477971470,1480000000]]',
ext varchar(550) DEFAULT NULL,
p1 varchar(64) DEFAULT NULL ,
p2 varchar(64) DEFAULT NULL,
p3 varchar(64) DEFAULT NULL,
p4 varchar(64) DEFAULT NULL,
p5 varchar(64) DEFAULT NULL,
p6_md5 varchar(32) DEFAULT NULL,
p7_md5 varchar(32) DEFAULT NULL,
bm tinyint(1) DEFAULT '0' comment '[[set=0,1]]',
bgm tinyint(1) DEFAULT '0' comment '[[set=0,1]]',
insert_date timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id),
UNIQUE KEY aid (aid,dic),
KEY ip (ip),
KEY cm (cm)
);
load stats 's/explain_complex_stats_dt.json';
drop table if exists st;
CREATE TABLE st (
id int(11) UNSIGNED NOT NULL,
pt varchar(10) NOT NULL comment '[[set=android,ios]]',
aid varchar(35) NOT NULL comment '[[set=cn.sbkcq,us.sbkcq]]',
cm int(10) NOT NULL comment '[[range=1000,5000]]',
ip varchar(15) DEFAULT NULL ,
dic varchar(64) DEFAULT NULL ,
dit varchar(5) DEFAULT NULL comment '[[set=win,mac,linux]]',
p1 varchar(64) DEFAULT NULL ,
p2 varchar(64) DEFAULT NULL,
p3 varchar(64) DEFAULT NULL,
p4 varchar(64) DEFAULT NULL,
p5 varchar(64) DEFAULT NULL,
p6_md5 varchar(32) DEFAULT NULL,
p7_md5 varchar(32) DEFAULT NULL,
ext varchar(550) DEFAULT NULL,
bm tinyint(1) DEFAULT '0' comment '[[set=0,1]]',
ds date NOT NULL ,
ds2 varchar(13) DEFAULT NULL ,
t int(13) NOT NULL comment '[[range=1477971470,1479144000]]',
PRIMARY KEY (id),
KEY t (t),
KEY icd (cm,ds)
);
load stats 's/explain_complex_stats_st.json';
drop table if exists dd;
CREATE TABLE dd (
id int(11) UNSIGNED NOT NULL,
aid varchar(35) NOT NULL comment '[[set=cn.sbkcq,us.sbkcq]]',
pt varchar(10) NOT NULL comment '[[set=android,ios]]',
dic varchar(64) NOT NULL,
dim varchar(32) NOT NULL ,
mac varchar(32) DEFAULT NULL ,
ip varchar(15) DEFAULT NULL ,
t int(13) DEFAULT NULL comment '[[range=1478143900,1478144000]]',
bm tinyint(1) DEFAULT '0' comment '[[set=0,1]]',
PRIMARY KEY (id),
UNIQUE KEY aid (aid,dic),
KEY ip (ip),
KEY pi (aid,dim),
KEY t (t)
);
load stats 's/explain_complex_stats_dd.json';
drop table if exists pp;
CREATE TABLE pp (
oid varchar(20) NOT NULL,
uid bigint(20) unsigned NOT NULL comment '[[set=18089709,18089710,18090780,18090781]]',
cid int(11) unsigned NOT NULL,
ppt int(11) NOT NULL DEFAULT '0' comment '[[range=1478188700,1478275300]]',
pdt int(11) DEFAULT '0',
am decimal(10,2) unsigned NOT NULL DEFAULT '0.00',
cc decimal(10,2) NOT NULL DEFAULT '0.00',
ps tinyint(1) NOT NULL comment '[[set=0,1,2]]',
tid varchar(200) DEFAULT NULL,
ppf varchar(50) NOT NULL comment '[[set=android,ios]]',
bs tinyint(1) NOT NULL DEFAULT '0' comment '[[set=0,1]]',
bex tinyint(1) NOT NULL DEFAULT '0' comment '[[set=0,1]]',
bu int(11) NOT NULL DEFAULT '0' comment '[[set=0,1]]',
pc char(10) NOT NULL DEFAULT 'CNY',
ui int(16) NOT NULL DEFAULT '1',
cr decimal(10,4) unsigned NOT NULL DEFAULT '1.0000',
pi int(11) unsigned NOT NULL comment '[[set=510017,520017,530017]]',
si int(11) unsigned NOT NULL,
bcc int(11) NOT NULL DEFAULT '0',
acc int(11) NOT NULL DEFAULT '0',
KEY oid (oid),
KEY uid (uid),
KEY ppt (ppt),
KEY pdt (pdt),
KEY cid (cid),
KEY ps (ps),
KEY sp (uid,pi)
);
load stats 's/explain_complex_stats_pp.json';
drop table if exists rr;
CREATE TABLE rr (
aid varchar(35) NOT NULL comment '[[set=cn.sbkcq,us.sbkcq]]',
pt varchar(10) NOT NULL comment '[[set=android,ios]]',
dic varchar(64) NOT NULL ,
gid varchar(42) NOT NULL ,
acd varchar(32) NOT NULL ,
t int(13) DEFAULT NULL comment '[[range=1478180000,1480275300]]',
bm tinyint(1) DEFAULT '0' comment '[[set=0,1]]',
PRIMARY KEY (aid,dic)
);
load stats 's/explain_complex_stats_rr.json';
explain format = 'brief' SELECT ds, p1, p2, p3, p4, p5, p6_md5, p7_md5, count(dic) as install_device FROM dt use index (cm) WHERE (ds >= '2016-09-01') AND (ds <= '2016-11-03') AND (cm IN ('1062', '1086', '1423', '1424', '1425', '1426', '1427', '1428', '1429', '1430', '1431', '1432', '1433', '1434', '1435', '1436', '1437', '1438', '1439', '1440', '1441', '1442', '1443', '1444', '1445', '1446', '1447', '1448', '1449', '1450', '1451', '1452', '1488', '1489', '1490', '1491', '1492', '1493', '1494', '1495', '1496', '1497', '1550', '1551', '1552', '1553', '1554', '1555', '1556', '1557', '1558', '1559', '1597', '1598', '1599', '1600', '1601', '1602', '1603', '1604', '1605', '1606', '1607', '1608', '1609', '1610', '1611', '1612', '1613', '1614', '1615', '1616', '1623', '1624', '1625', '1626', '1627', '1628', '1629', '1630', '1631', '1632', '1709', '1719', '1720', '1843', '2813', '2814', '2815', '2816', '2817', '2818', '2819', '2820', '2821', '2822', '2823', '2824', '2825', '2826', '2827', '2828', '2829', '2830', '2831', '2832', '2833', '2834', '2835', '2836', '2837', '2838', '2839', '2840', '2841', '2842', '2843', '2844', '2845', '2846', '2847', '2848', '2849', '2850', '2851', '2852', '2853', '2854', '2855', '2856', '2857', '2858', '2859', '2860', '2861', '2862', '2863', '2864', '2865', '2866', '2867', '2868', '2869', '2870', '2871', '2872', '3139', '3140', '3141', '3142', '3143', '3144', '3145', '3146', '3147', '3148', '3149', '3150', '3151', '3152', '3153', '3154', '3155', '3156', '3157', '3158', '3386', '3387', '3388', '3389', '3390', '3391', '3392', '3393', '3394', '3395', '3664', '3665', '3666', '3667', '3668', '3670', '3671', '3672', '3673', '3674', '3676', '3677', '3678', '3679', '3680', '3681', '3682', '3683', '3684', '3685', '3686', '3687', '3688', '3689', '3690', '3691', '3692', '3693', '3694', '3695', '3696', '3697', '3698', '3699', '3700', '3701', '3702', '3703', '3704', '3705', '3706', '3707', '3708', '3709', '3710', '3711', '3712', '3713', '3714', '3715', '3960', '3961', '3962', '3963', '3964', '3965', '3966', '3967', '3968', '3978', '3979', '3980', '3981', '3982', '3983', '3984', '3985', '3986', '3987', '4208', '4209', '4210', '4211', '4212', '4304', '4305', '4306', '4307', '4308', '4866', '4867', '4868', '4869', '4870', '4871', '4872', '4873', '4874', '4875')) GROUP BY ds, p1, p2, p3, p4, p5, p6_md5, p7_md5 ORDER BY ds2 DESC;
id estRows task access object operator info
Projection 21.47 root explain_complex_stats.dt.ds, explain_complex_stats.dt.p1, explain_complex_stats.dt.p2, explain_complex_stats.dt.p3, explain_complex_stats.dt.p4, explain_complex_stats.dt.p5, explain_complex_stats.dt.p6_md5, explain_complex_stats.dt.p7_md5, Column#21->Column#30
└─Sort 21.47 root explain_complex_stats.dt.ds2:desc
└─HashAgg 21.47 root group by:explain_complex_stats.dt.ds, explain_complex_stats.dt.p1, explain_complex_stats.dt.p2, explain_complex_stats.dt.p3, explain_complex_stats.dt.p4, explain_complex_stats.dt.p5, explain_complex_stats.dt.p6_md5, explain_complex_stats.dt.p7_md5, funcs:count(Column#32)->Column#21, funcs:firstrow(explain_complex_stats.dt.ds)->explain_complex_stats.dt.ds, funcs:firstrow(Column#34)->explain_complex_stats.dt.ds2, funcs:firstrow(explain_complex_stats.dt.p1)->explain_complex_stats.dt.p1, funcs:firstrow(explain_complex_stats.dt.p2)->explain_complex_stats.dt.p2, funcs:firstrow(explain_complex_stats.dt.p3)->explain_complex_stats.dt.p3, funcs:firstrow(explain_complex_stats.dt.p4)->explain_complex_stats.dt.p4, funcs:firstrow(explain_complex_stats.dt.p5)->explain_complex_stats.dt.p5, funcs:firstrow(explain_complex_stats.dt.p6_md5)->explain_complex_stats.dt.p6_md5, funcs:firstrow(explain_complex_stats.dt.p7_md5)->explain_complex_stats.dt.p7_md5
└─IndexLookUp 21.47 root
├─IndexRangeScan(Build) 128.00 cop[tikv] table:dt, index:cm(cm) range:[1062,1062], [1086,1086], [1423,1423], [1424,1424], [1425,1425], [1426,1426], [1427,1427], [1428,1428], [1429,1429], [1430,1430], [1431,1431], [1432,1432], [1433,1433], [1434,1434], [1435,1435], [1436,1436], [1437,1437], [1438,1438], [1439,1439], [1440,1440], [1441,1441], [1442,1442], [1443,1443], [1444,1444], [1445,1445], [1446,1446], [1447,1447], [1448,1448], [1449,1449], [1450,1450], [1451,1451], [1452,1452], [1488,1488], [1489,1489], [1490,1490], [1491,1491], [1492,1492], [1493,1493], [1494,1494], [1495,1495], [1496,1496], [1497,1497], [1550,1550], [1551,1551], [1552,1552], [1553,1553], [1554,1554], [1555,1555], [1556,1556], [1557,1557], [1558,1558], [1559,1559], [1597,1597], [1598,1598], [1599,1599], [1600,1600], [1601,1601], [1602,1602], [1603,1603], [1604,1604], [1605,1605], [1606,1606], [1607,1607], [1608,1608], [1609,1609], [1610,1610], [1611,1611], [1612,1612], [1613,1613], [1614,1614], [1615,1615], [1616,1616], [1623,1623], [1624,1624], [1625,1625], [1626,1626], [1627,1627], [1628,1628], [1629,1629], [1630,1630], [1631,1631], [1632,1632], [1709,1709], [1719,1719], [1720,1720], [1843,1843], [2813,2813], [2814,2814], [2815,2815], [2816,2816], [2817,2817], [2818,2818], [2819,2819], [2820,2820], [2821,2821], [2822,2822], [2823,2823], [2824,2824], [2825,2825], [2826,2826], [2827,2827], [2828,2828], [2829,2829], [2830,2830], [2831,2831], [2832,2832], [2833,2833], [2834,2834], [2835,2835], [2836,2836], [2837,2837], [2838,2838], [2839,2839], [2840,2840], [2841,2841], [2842,2842], [2843,2843], [2844,2844], [2845,2845], [2846,2846], [2847,2847], [2848,2848], [2849,2849], [2850,2850], [2851,2851], [2852,2852], [2853,2853], [2854,2854], [2855,2855], [2856,2856], [2857,2857], [2858,2858], [2859,2859], [2860,2860], [2861,2861], [2862,2862], [2863,2863], [2864,2864], [2865,2865], [2866,2866], [2867,2867], [2868,2868], [2869,2869], [2870,2870], [2871,2871], [2872,2872], [3139,3139], [3140,3140], [3141,3141], [3142,3142], [3143,3143], [3144,3144], [3145,3145], [3146,3146], [3147,3147], [3148,3148], [3149,3149], [3150,3150], [3151,3151], [3152,3152], [3153,3153], [3154,3154], [3155,3155], [3156,3156], [3157,3157], [3158,3158], [3386,3386], [3387,3387], [3388,3388], [3389,3389], [3390,3390], [3391,3391], [3392,3392], [3393,3393], [3394,3394], [3395,3395], [3664,3664], [3665,3665], [3666,3666], [3667,3667], [3668,3668], [3670,3670], [3671,3671], [3672,3672], [3673,3673], [3674,3674], [3676,3676], [3677,3677], [3678,3678], [3679,3679], [3680,3680], [3681,3681], [3682,3682], [3683,3683], [3684,3684], [3685,3685], [3686,3686], [3687,3687], [3688,3688], [3689,3689], [3690,3690], [3691,3691], [3692,3692], [3693,3693], [3694,3694], [3695,3695], [3696,3696], [3697,3697], [3698,3698], [3699,3699], [3700,3700], [3701,3701], [3702,3702], [3703,3703], [3704,3704], [3705,3705], [3706,3706], [3707,3707], [3708,3708], [3709,3709], [3710,3710], [3711,3711], [3712,3712], [3713,3713], [3714,3714], [3715,3715], [3960,3960], [3961,3961], [3962,3962], [3963,3963], [3964,3964], [3965,3965], [3966,3966], [3967,3967], [3968,3968], [3978,3978], [3979,3979], [3980,3980], [3981,3981], [3982,3982], [3983,3983], [3984,3984], [3985,3985], [3986,3986], [3987,3987], [4208,4208], [4209,4209], [4210,4210], [4211,4211], [4212,4212], [4304,4304], [4305,4305], [4306,4306], [4307,4307], [4308,4308], [4866,4866], [4867,4867], [4868,4868], [4869,4869], [4870,4870], [4871,4871], [4872,4872], [4873,4873], [4874,4874], [4875,4875], keep order:false
└─HashAgg(Probe) 21.47 cop[tikv] group by:explain_complex_stats.dt.ds, explain_complex_stats.dt.p1, explain_complex_stats.dt.p2, explain_complex_stats.dt.p3, explain_complex_stats.dt.p4, explain_complex_stats.dt.p5, explain_complex_stats.dt.p6_md5, explain_complex_stats.dt.p7_md5, funcs:count(explain_complex_stats.dt.dic)->Column#32, funcs:firstrow(explain_complex_stats.dt.ds2)->Column#34
└─Selection 21.50 cop[tikv] ge(explain_complex_stats.dt.ds, 2016-09-01 00:00:00.000000), le(explain_complex_stats.dt.ds, 2016-11-03 00:00:00.000000)
└─TableRowIDScan 128.00 cop[tikv] table:dt keep order:false
explain format = 'brief' select gad.id as gid,sdk.id as sid,gad.aid as aid,gad.cm as cm,sdk.dic as dic,sdk.ip as ip, sdk.t as t, gad.p1 as p1, gad.p2 as p2, gad.p3 as p3, gad.p4 as p4, gad.p5 as p5, gad.p6_md5 as p6, gad.p7_md5 as p7, gad.ext as ext, gad.t as gtime from st gad join (select id, aid, pt, dic, ip, t from dd where pt = 'android' and bm = 0 and t > 1478143908) sdk on gad.aid = sdk.aid and gad.ip = sdk.ip and sdk.t > gad.t where gad.t > 1478143908 and gad.bm = 0 and gad.pt = 'android' group by gad.aid, sdk.dic limit 2500;
id estRows task access object operator info
Projection 424.00 root explain_complex_stats.st.id, explain_complex_stats.dd.id, explain_complex_stats.st.aid, explain_complex_stats.st.cm, explain_complex_stats.dd.dic, explain_complex_stats.dd.ip, explain_complex_stats.dd.t, explain_complex_stats.st.p1, explain_complex_stats.st.p2, explain_complex_stats.st.p3, explain_complex_stats.st.p4, explain_complex_stats.st.p5, explain_complex_stats.st.p6_md5, explain_complex_stats.st.p7_md5, explain_complex_stats.st.ext, explain_complex_stats.st.t
└─Limit 424.00 root offset:0, count:2500
└─HashAgg 424.00 root group by:explain_complex_stats.dd.dic, explain_complex_stats.st.aid, funcs:firstrow(explain_complex_stats.st.id)->explain_complex_stats.st.id, funcs:firstrow(explain_complex_stats.st.aid)->explain_complex_stats.st.aid, funcs:firstrow(explain_complex_stats.st.cm)->explain_complex_stats.st.cm, funcs:firstrow(explain_complex_stats.st.p1)->explain_complex_stats.st.p1, funcs:firstrow(explain_complex_stats.st.p2)->explain_complex_stats.st.p2, funcs:firstrow(explain_complex_stats.st.p3)->explain_complex_stats.st.p3, funcs:firstrow(explain_complex_stats.st.p4)->explain_complex_stats.st.p4, funcs:firstrow(explain_complex_stats.st.p5)->explain_complex_stats.st.p5, funcs:firstrow(explain_complex_stats.st.p6_md5)->explain_complex_stats.st.p6_md5, funcs:firstrow(explain_complex_stats.st.p7_md5)->explain_complex_stats.st.p7_md5, funcs:firstrow(explain_complex_stats.st.ext)->explain_complex_stats.st.ext, funcs:firstrow(explain_complex_stats.st.t)->explain_complex_stats.st.t, funcs:firstrow(explain_complex_stats.dd.id)->explain_complex_stats.dd.id, funcs:firstrow(explain_complex_stats.dd.dic)->explain_complex_stats.dd.dic, funcs:firstrow(explain_complex_stats.dd.ip)->explain_complex_stats.dd.ip, funcs:firstrow(explain_complex_stats.dd.t)->explain_complex_stats.dd.t
└─HashJoin 424.00 root inner join, equal:[eq(explain_complex_stats.st.aid, explain_complex_stats.dd.aid) eq(explain_complex_stats.st.ip, explain_complex_stats.dd.ip)], other cond:gt(explain_complex_stats.dd.t, explain_complex_stats.st.t)
├─TableReader(Build) 424.00 root data:Selection
│ └─Selection 424.00 cop[tikv] eq(explain_complex_stats.st.bm, 0), eq(explain_complex_stats.st.pt, "android"), gt(explain_complex_stats.st.t, 1478143908), not(isnull(explain_complex_stats.st.ip))
│ └─TableFullScan 1999.00 cop[tikv] table:gad keep order:false
└─TableReader(Probe) 450.56 root data:Selection
└─Selection 450.56 cop[tikv] eq(explain_complex_stats.dd.bm, 0), eq(explain_complex_stats.dd.pt, "android"), gt(explain_complex_stats.dd.t, 1478143908), not(isnull(explain_complex_stats.dd.ip)), not(isnull(explain_complex_stats.dd.t))
└─TableFullScan 2000.00 cop[tikv] table:dd keep order:false
explain format = 'brief' select gad.id as gid,sdk.id as sid,gad.aid as aid,gad.cm as cm,sdk.dic as dic,sdk.ip as ip, sdk.t as t, gad.p1 as p1, gad.p2 as p2, gad.p3 as p3, gad.p4 as p4, gad.p5 as p5, gad.p6_md5 as p6, gad.p7_md5 as p7, gad.ext as ext from st gad join dd sdk on gad.aid = sdk.aid and gad.dic = sdk.mac and gad.t < sdk.t where gad.t > 1477971479 and gad.bm = 0 and gad.pt = 'ios' and gad.dit = 'mac' and sdk.t > 1477971479 and sdk.bm = 0 and sdk.pt = 'ios' limit 3000;
id estRows task access object operator info
Projection 170.34 root explain_complex_stats.st.id, explain_complex_stats.dd.id, explain_complex_stats.st.aid, explain_complex_stats.st.cm, explain_complex_stats.dd.dic, explain_complex_stats.dd.ip, explain_complex_stats.dd.t, explain_complex_stats.st.p1, explain_complex_stats.st.p2, explain_complex_stats.st.p3, explain_complex_stats.st.p4, explain_complex_stats.st.p5, explain_complex_stats.st.p6_md5, explain_complex_stats.st.p7_md5, explain_complex_stats.st.ext
└─Limit 170.34 root offset:0, count:3000
└─IndexJoin 170.34 root inner join, inner:IndexLookUp, outer key:explain_complex_stats.st.aid, inner key:explain_complex_stats.dd.aid, equal cond:eq(explain_complex_stats.st.aid, explain_complex_stats.dd.aid), eq(explain_complex_stats.st.dic, explain_complex_stats.dd.mac), other cond:lt(explain_complex_stats.st.t, explain_complex_stats.dd.t)
├─TableReader(Build) 170.34 root data:Selection
│ └─Selection 170.34 cop[tikv] eq(explain_complex_stats.st.bm, 0), eq(explain_complex_stats.st.dit, "mac"), eq(explain_complex_stats.st.pt, "ios"), gt(explain_complex_stats.st.t, 1477971479), not(isnull(explain_complex_stats.st.dic))
│ └─TableFullScan 1999.00 cop[tikv] table:gad keep order:false
└─IndexLookUp(Probe) 170.34 root
├─IndexRangeScan(Build) 669.25 cop[tikv] table:sdk, index:aid(aid, dic) range: decided by [eq(explain_complex_stats.dd.aid, explain_complex_stats.st.aid)], keep order:false
└─Selection(Probe) 170.34 cop[tikv] eq(explain_complex_stats.dd.bm, 0), eq(explain_complex_stats.dd.pt, "ios"), gt(explain_complex_stats.dd.t, 1477971479), not(isnull(explain_complex_stats.dd.mac)), not(isnull(explain_complex_stats.dd.t))
└─TableRowIDScan 669.25 cop[tikv] table:sdk keep order:false
explain format = 'brief' SELECT cm, p1, p2, p3, p4, p5, p6_md5, p7_md5, count(1) as click_pv, count(DISTINCT ip) as click_ip FROM st WHERE (t between 1478188800 and 1478275200) and aid='cn.sbkcq' and pt='android' GROUP BY cm, p1, p2, p3, p4, p5, p6_md5, p7_md5;
id estRows task access object operator info
Projection 39.28 root explain_complex_stats.st.cm, explain_complex_stats.st.p1, explain_complex_stats.st.p2, explain_complex_stats.st.p3, explain_complex_stats.st.p4, explain_complex_stats.st.p5, explain_complex_stats.st.p6_md5, explain_complex_stats.st.p7_md5, Column#20, Column#21
└─HashAgg 39.28 root group by:explain_complex_stats.st.cm, explain_complex_stats.st.p1, explain_complex_stats.st.p2, explain_complex_stats.st.p3, explain_complex_stats.st.p4, explain_complex_stats.st.p5, explain_complex_stats.st.p6_md5, explain_complex_stats.st.p7_md5, funcs:count(1)->Column#20, funcs:count(distinct explain_complex_stats.st.ip)->Column#21, funcs:firstrow(explain_complex_stats.st.cm)->explain_complex_stats.st.cm, funcs:firstrow(explain_complex_stats.st.p1)->explain_complex_stats.st.p1, funcs:firstrow(explain_complex_stats.st.p2)->explain_complex_stats.st.p2, funcs:firstrow(explain_complex_stats.st.p3)->explain_complex_stats.st.p3, funcs:firstrow(explain_complex_stats.st.p4)->explain_complex_stats.st.p4, funcs:firstrow(explain_complex_stats.st.p5)->explain_complex_stats.st.p5, funcs:firstrow(explain_complex_stats.st.p6_md5)->explain_complex_stats.st.p6_md5, funcs:firstrow(explain_complex_stats.st.p7_md5)->explain_complex_stats.st.p7_md5
└─IndexLookUp 39.38 root
├─IndexRangeScan(Build) 160.23 cop[tikv] table:st, index:t(t) range:[1478188800,1478275200], keep order:false
└─Selection(Probe) 39.38 cop[tikv] eq(explain_complex_stats.st.aid, "cn.sbkcq"), eq(explain_complex_stats.st.pt, "android")
└─TableRowIDScan 160.23 cop[tikv] table:st keep order:false
explain format = 'brief' select dt.id as id, dt.aid as aid, dt.pt as pt, dt.dic as dic, dt.cm as cm, rr.gid as gid, rr.acd as acd, rr.t as t,dt.p1 as p1, dt.p2 as p2, dt.p3 as p3, dt.p4 as p4, dt.p5 as p5, dt.p6_md5 as p6, dt.p7_md5 as p7 from dt dt join rr rr on (rr.pt = 'ios' and rr.t > 1478185592 and dt.aid = rr.aid and dt.dic = rr.dic) where dt.pt = 'ios' and dt.t > 1478185592 and dt.bm = 0 limit 2000;
id estRows task access object operator info
Projection 428.32 root explain_complex_stats.dt.id, explain_complex_stats.dt.aid, explain_complex_stats.dt.pt, explain_complex_stats.dt.dic, explain_complex_stats.dt.cm, explain_complex_stats.rr.gid, explain_complex_stats.rr.acd, explain_complex_stats.rr.t, explain_complex_stats.dt.p1, explain_complex_stats.dt.p2, explain_complex_stats.dt.p3, explain_complex_stats.dt.p4, explain_complex_stats.dt.p5, explain_complex_stats.dt.p6_md5, explain_complex_stats.dt.p7_md5
└─Limit 428.32 root offset:0, count:2000
└─IndexJoin 428.32 root inner join, inner:IndexLookUp, outer key:explain_complex_stats.dt.aid, explain_complex_stats.dt.dic, inner key:explain_complex_stats.rr.aid, explain_complex_stats.rr.dic, equal cond:eq(explain_complex_stats.dt.aid, explain_complex_stats.rr.aid), eq(explain_complex_stats.dt.dic, explain_complex_stats.rr.dic)
├─TableReader(Build) 428.32 root data:Selection
│ └─Selection 428.32 cop[tikv] eq(explain_complex_stats.dt.bm, 0), eq(explain_complex_stats.dt.pt, "ios"), gt(explain_complex_stats.dt.t, 1478185592), not(isnull(explain_complex_stats.dt.dic))
│ └─TableFullScan 2000.00 cop[tikv] table:dt keep order:false
└─IndexLookUp(Probe) 428.32 root
├─IndexRangeScan(Build) 428.32 cop[tikv] table:rr, index:PRIMARY(aid, dic) range: decided by [eq(explain_complex_stats.rr.aid, explain_complex_stats.dt.aid) eq(explain_complex_stats.rr.dic, explain_complex_stats.dt.dic)], keep order:false
└─Selection(Probe) 428.32 cop[tikv] eq(explain_complex_stats.rr.pt, "ios"), gt(explain_complex_stats.rr.t, 1478185592)
└─TableRowIDScan 428.32 cop[tikv] table:rr keep order:false
explain format = 'brief' select pc,cr,count(DISTINCT uid) as pay_users,count(oid) as pay_times,sum(am) as am from pp where ps=2 and ppt>=1478188800 and ppt<1478275200 and pi in ('510017','520017') and uid in ('18089709','18090780') group by pc,cr;
id estRows task access object operator info
Projection 207.02 root explain_complex_stats.pp.pc, explain_complex_stats.pp.cr, Column#22, Column#23, Column#24
└─HashAgg 207.02 root group by:explain_complex_stats.pp.cr, explain_complex_stats.pp.pc, funcs:count(distinct explain_complex_stats.pp.uid)->Column#22, funcs:count(explain_complex_stats.pp.oid)->Column#23, funcs:sum(explain_complex_stats.pp.am)->Column#24, funcs:firstrow(explain_complex_stats.pp.pc)->explain_complex_stats.pp.pc, funcs:firstrow(explain_complex_stats.pp.cr)->explain_complex_stats.pp.cr
└─IndexLookUp 207.02 root
├─IndexRangeScan(Build) 627.00 cop[tikv] table:pp, index:ps(ps) range:[2,2], keep order:false
└─Selection(Probe) 207.02 cop[tikv] ge(explain_complex_stats.pp.ppt, 1478188800), in(explain_complex_stats.pp.pi, 510017, 520017), in(explain_complex_stats.pp.uid, 18089709, 18090780), lt(explain_complex_stats.pp.ppt, 1478275200)
└─TableRowIDScan 627.00 cop[tikv] table:pp keep order:false
drop table if exists tbl_001;
CREATE TABLE tbl_001 (a int, b int);
load stats 's/explain_complex_stats_tbl_001.json';
drop table if exists tbl_002;
CREATE TABLE tbl_002 (a int, b int);
load stats 's/explain_complex_stats_tbl_002.json';
drop table if exists tbl_003;
CREATE TABLE tbl_003 (a int, b int);
load stats 's/explain_complex_stats_tbl_003.json';
drop table if exists tbl_004;
CREATE TABLE tbl_004 (a int, b int);
load stats 's/explain_complex_stats_tbl_004.json';
drop table if exists tbl_005;
CREATE TABLE tbl_005 (a int, b int);
load stats 's/explain_complex_stats_tbl_005.json';
drop table if exists tbl_006;
CREATE TABLE tbl_006 (a int, b int);
load stats 's/explain_complex_stats_tbl_006.json';
drop table if exists tbl_007;
CREATE TABLE tbl_007 (a int, b int);
load stats 's/explain_complex_stats_tbl_007.json';
drop table if exists tbl_008;
CREATE TABLE tbl_008 (a int, b int);
load stats 's/explain_complex_stats_tbl_008.json';
drop table if exists tbl_009;
CREATE TABLE tbl_009 (a int, b int);
load stats 's/explain_complex_stats_tbl_009.json';
explain format = 'brief' select sum(a) from (select * from tbl_001 union all select * from tbl_002 union all select * from tbl_003 union all select * from tbl_004 union all select * from tbl_005 union all select * from tbl_006 union all select * from tbl_007 union all select * from tbl_008 union all select * from tbl_009) x group by b;
id estRows task access object operator info
HashAgg 18000.00 root group by:Column#32, funcs:sum(Column#31)->Column#30
└─Projection 18000.00 root cast(Column#28, decimal(10,0) BINARY)->Column#31, Column#29->Column#32
└─Union 18000.00 root
├─TableReader 2000.00 root data:TableFullScan
│ └─TableFullScan 2000.00 cop[tikv] table:tbl_001 keep order:false
├─TableReader 2000.00 root data:TableFullScan
│ └─TableFullScan 2000.00 cop[tikv] table:tbl_002 keep order:false
├─TableReader 2000.00 root data:TableFullScan
│ └─TableFullScan 2000.00 cop[tikv] table:tbl_003 keep order:false
├─TableReader 2000.00 root data:TableFullScan
│ └─TableFullScan 2000.00 cop[tikv] table:tbl_004 keep order:false
├─TableReader 2000.00 root data:TableFullScan
│ └─TableFullScan 2000.00 cop[tikv] table:tbl_005 keep order:false
├─TableReader 2000.00 root data:TableFullScan
│ └─TableFullScan 2000.00 cop[tikv] table:tbl_006 keep order:false
├─TableReader 2000.00 root data:TableFullScan
│ └─TableFullScan 2000.00 cop[tikv] table:tbl_007 keep order:false
├─TableReader 2000.00 root data:TableFullScan
│ └─TableFullScan 2000.00 cop[tikv] table:tbl_008 keep order:false
└─TableReader 2000.00 root data:TableFullScan
└─TableFullScan 2000.00 cop[tikv] table:tbl_009 keep order:false
set @@sql_mode=default