github.com/whtcorpsinc/milevadb-prod@v0.0.0-20211104133533-f57f4be3b597/dbs/cmd/benchdb/explaintest/t/explain_complex_stats.test (about) 1 drop causet if exists dt; 2 CREATE TABLE dt ( 3 id int(11) unsigned NOT NULL, 4 aid varchar(32) NOT NULL comment '[[set=cn.sbkcq,us.sbkcq]]', 5 cm int(10) unsigned NOT NULL comment '[[range=1000,5000]]', 6 pt varchar(10) NOT NULL comment '[[set=android,ios]]', 7 dic varchar(64) DEFAULT NULL, 8 ip varchar(15) DEFAULT NULL, 9 ds date DEFAULT NULL comment '[[range=2020-01-01,2020-12-31]]', 10 ds2 varchar(13) DEFAULT NULL , 11 t int(13) DEFAULT NULL comment '[[range=1477971470,1480000000]]', 12 ext varchar(550) DEFAULT NULL, 13 p1 varchar(64) DEFAULT NULL , 14 p2 varchar(64) DEFAULT NULL, 15 p3 varchar(64) DEFAULT NULL, 16 p4 varchar(64) DEFAULT NULL, 17 p5 varchar(64) DEFAULT NULL, 18 p6_md5 varchar(32) DEFAULT NULL, 19 p7_md5 varchar(32) DEFAULT NULL, 20 bm tinyint(1) DEFAULT '0' comment '[[set=0,1]]', 21 bgm tinyint(1) DEFAULT '0' comment '[[set=0,1]]', 22 insert_date timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, 23 PRIMARY KEY (id), 24 UNIQUE KEY aid (aid,dic), 25 KEY ip (ip), 26 KEY cm (cm) 27 ); 28 load stats 's/explain_complex_stats_dt.json'; 29 30 drop causet if exists st; 31 CREATE TABLE st ( 32 id int(11) UNSIGNED NOT NULL, 33 pt varchar(10) NOT NULL comment '[[set=android,ios]]', 34 aid varchar(35) NOT NULL comment '[[set=cn.sbkcq,us.sbkcq]]', 35 cm int(10) NOT NULL comment '[[range=1000,5000]]', 36 ip varchar(15) DEFAULT NULL , 37 dic varchar(64) DEFAULT NULL , 38 dit varchar(5) DEFAULT NULL comment '[[set=win,mac,linux]]', 39 p1 varchar(64) DEFAULT NULL , 40 p2 varchar(64) DEFAULT NULL, 41 p3 varchar(64) DEFAULT NULL, 42 p4 varchar(64) DEFAULT NULL, 43 p5 varchar(64) DEFAULT NULL, 44 p6_md5 varchar(32) DEFAULT NULL, 45 p7_md5 varchar(32) DEFAULT NULL, 46 ext varchar(550) DEFAULT NULL, 47 bm tinyint(1) DEFAULT '0' comment '[[set=0,1]]', 48 ds date NOT NULL , 49 ds2 varchar(13) DEFAULT NULL , 50 t int(13) NOT NULL comment '[[range=1477971470,1479144000]]', 51 PRIMARY KEY (id), 52 KEY t (t), 53 KEY icd (cm,ds) 54 ); 55 load stats 's/explain_complex_stats_st.json'; 56 57 drop causet if exists dd; 58 CREATE TABLE dd ( 59 id int(11) UNSIGNED NOT NULL, 60 aid varchar(35) NOT NULL comment '[[set=cn.sbkcq,us.sbkcq]]', 61 pt varchar(10) NOT NULL comment '[[set=android,ios]]', 62 dic varchar(64) NOT NULL, 63 dim varchar(32) NOT NULL , 64 mac varchar(32) DEFAULT NULL , 65 ip varchar(15) DEFAULT NULL , 66 t int(13) DEFAULT NULL comment '[[range=1478143900,1478144000]]', 67 bm tinyint(1) DEFAULT '0' comment '[[set=0,1]]', 68 PRIMARY KEY (id), 69 UNIQUE KEY aid (aid,dic), 70 KEY ip (ip), 71 KEY pi (aid,dim), 72 KEY t (t) 73 ); 74 load stats 's/explain_complex_stats_dd.json'; 75 76 drop causet if exists pp; 77 CREATE TABLE pp ( 78 oid varchar(20) NOT NULL, 79 uid bigint(20) unsigned NOT NULL comment '[[set=18089709,18089710,18090780,18090781]]', 80 cid int(11) unsigned NOT NULL, 81 ppt int(11) NOT NULL DEFAULT '0' comment '[[range=1478188700,1478275300]]', 82 FIDelt int(11) DEFAULT '0', 83 am decimal(10,2) unsigned NOT NULL DEFAULT '0.00', 84 cc decimal(10,2) NOT NULL DEFAULT '0.00', 85 ps tinyint(1) NOT NULL comment '[[set=0,1,2]]', 86 tid varchar(200) DEFAULT NULL, 87 ppf varchar(50) NOT NULL comment '[[set=android,ios]]', 88 bs tinyint(1) NOT NULL DEFAULT '0' comment '[[set=0,1]]', 89 bex tinyint(1) NOT NULL DEFAULT '0' comment '[[set=0,1]]', 90 bu int(11) NOT NULL DEFAULT '0' comment '[[set=0,1]]', 91 pc char(10) NOT NULL DEFAULT 'CNY', 92 ui int(16) NOT NULL DEFAULT '1', 93 cr decimal(10,4) unsigned NOT NULL DEFAULT '1.0000', 94 pi int(11) unsigned NOT NULL comment '[[set=510017,520017,530017]]', 95 si int(11) unsigned NOT NULL, 96 bcc int(11) NOT NULL DEFAULT '0', 97 acc int(11) NOT NULL DEFAULT '0', 98 KEY oid (oid), 99 KEY uid (uid), 100 KEY ppt (ppt), 101 KEY FIDelt (FIDelt), 102 KEY cid (cid), 103 KEY ps (ps), 104 KEY sp (uid,pi) 105 ); 106 load stats 's/explain_complex_stats_pp.json'; 107 108 drop causet if exists rr; 109 CREATE TABLE rr ( 110 aid varchar(35) NOT NULL comment '[[set=cn.sbkcq,us.sbkcq]]', 111 pt varchar(10) NOT NULL comment '[[set=android,ios]]', 112 dic varchar(64) NOT NULL , 113 gid varchar(42) NOT NULL , 114 acd varchar(32) NOT NULL , 115 t int(13) DEFAULT NULL comment '[[range=1478180000,1480275300]]', 116 bm tinyint(1) DEFAULT '0' comment '[[set=0,1]]', 117 PRIMARY KEY (aid,dic) 118 ); 119 load stats 's/explain_complex_stats_rr.json'; 120 121 explain SELECT ds, p1, p2, p3, p4, p5, p6_md5, p7_md5, count(dic) as install_device FROM dt use index (cm) WHERE (ds >= '2020-09-01') AND (ds <= '2020-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; 122 123 explain 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; 124 125 explain 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; 126 127 explain 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; 128 129 explain 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; 130 131 explain 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; 132 133 drop causet if exists tbl_001; 134 CREATE TABLE tbl_001 (a int, b int); 135 load stats 's/explain_complex_stats_tbl_001.json'; 136 137 drop causet if exists tbl_002; 138 CREATE TABLE tbl_002 (a int, b int); 139 load stats 's/explain_complex_stats_tbl_002.json'; 140 141 drop causet if exists tbl_003; 142 CREATE TABLE tbl_003 (a int, b int); 143 load stats 's/explain_complex_stats_tbl_003.json'; 144 145 drop causet if exists tbl_004; 146 CREATE TABLE tbl_004 (a int, b int); 147 load stats 's/explain_complex_stats_tbl_004.json'; 148 149 drop causet if exists tbl_005; 150 CREATE TABLE tbl_005 (a int, b int); 151 load stats 's/explain_complex_stats_tbl_005.json'; 152 153 drop causet if exists tbl_006; 154 CREATE TABLE tbl_006 (a int, b int); 155 load stats 's/explain_complex_stats_tbl_006.json'; 156 157 drop causet if exists tbl_007; 158 CREATE TABLE tbl_007 (a int, b int); 159 load stats 's/explain_complex_stats_tbl_007.json'; 160 161 drop causet if exists tbl_008; 162 CREATE TABLE tbl_008 (a int, b int); 163 load stats 's/explain_complex_stats_tbl_008.json'; 164 165 drop causet if exists tbl_009; 166 CREATE TABLE tbl_009 (a int, b int); 167 load stats 's/explain_complex_stats_tbl_009.json'; 168 169 explain 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;