有单表接近3亿数据,使用mysql查询30秒左右。tidb查询在500秒左右。
请教有何优化建议。谢谢
【TiDB 版本】
Cluster type: tidb
Cluster name: test-cluster
Cluster version: v4.0.9
SSH type: builtin
Dashboard URL: http://192.168.241.26:12379/dashboard
ID Role Host Ports OS/Arch Status Data Dir Deploy Dir
-- ---- ---- ----- ------- ------ -------- ----------
192.168.241.7:9093 alertmanager 192.168.241.7 9093/9094 linux/x86_64 Up /home/tidb/deploy/data.alertmanager /home/tidb/deploy
192.168.241.7:3000 grafana 192.168.241.7 3000 linux/x86_64 Up - /home/tidb/deploy
192.168.241.24:12379 pd 192.168.241.24 12379/12380 linux/x86_64 Up|L /disk1/pd/data.pd /disk1/pd
192.168.241.26:12379 pd 192.168.241.26 12379/12380 linux/x86_64 Up|UI /disk1/pd/data.pd /disk1/pd
192.168.241.49:12379 pd 192.168.241.49 12379/12380 linux/x86_64 Up /disk1/pd/data.pd /disk1/pd
192.168.241.7:9090 prometheus 192.168.241.7 9090 linux/x86_64 Up /home/tidb/deploy/prometheus2.0.0.data.metrics /home/tidb/deploy
192.168.241.26:4000 tidb 192.168.241.26 4000/10080 linux/x86_64 Up - /disk1/pd
192.168.241.7:4000 tidb 192.168.241.7 4000/10080 linux/x86_64 Up - /home/tidb/deploy
192.168.241.11:20160 tikv 192.168.241.11 20160/20180 linux/x86_64 Up /disk1/tikv/data /disk1/tikv
192.168.241.53:20160 tikv 192.168.241.53 20160/20180 linux/x86_64 Up /disk2/tikv/data /disk2/tikv
192.168.241.56:20160 tikv 192.168.241.56 20160/20180 linux/x86_64 Up /disk1/tikv/data /disk1/tikv
192.168.241.58:20160 tikv 192.168.241.58 20160/20180 linux/x86_64 Up /disk1/tikv/data /disk1/tikv
192.168.241.59:20160 tikv 192.168.241.59 20160/20180 linux/x86_64 Up /disk1/tikv/data /disk1/tikv
192.168.241.60:20160 tikv 192.168.241.60 20160/20180 linux/x86_64 Up /disk1/tikv/data /disk1/tikv
192.168.241.61:20160 tikv 192.168.241.61 20160/20180 linux/x86_64 Up /disk1/tikv/data /disk1/tikv
Total nodes: 15
【问题描述】
【SQL】
SELECT xpost
.sourcetype
, COUNT(xpost
.postid
) AS c
FROM xpost
WHERE (xpost
.type
= -1 AND xpost
.hidden
IN (-2, -1, 0, 2, 3, 4) AND xpost
.entryid
IN (2113792, 2110849, 2113160, 2110857, 2110223, 2112530, 2113268, 2111130, 2110368, 2111010, 2112937, 2113067, 2110508, 2110765, 2113201, 2112820, 2114102, 2111799, 2109624, 2109625, 2110010, 2109500, 2109377, 2110786, 2112715, 2109508, 2107847, 2110536, 2107849, 2110026, 2111307, 2112588, 2107854, 2113105, 2111316, 2111573, 2113622, 2111375, 2113628, 2114017, 2114022, 2112116, 2112504, 2107561, 2109688, 2112121, 2111102)) GROUP BY xpost
.sourcetype
ORDER BY c
DESC;
【建表语句】
/*!40101 SET NAMES binary*/;
CREATE TABLE `xpost` (
`postid` bigint(20) NOT NULL AUTO_INCREMENT,
`facetid` int(10) NOT NULL,
`entryid` int(10) NOT NULL,
`title` varchar(255) COLLATE utf8mb4_bin NOT NULL DEFAULT '',
`url` varchar(512) COLLATE utf8mb4_bin NOT NULL,
`abstract` text COLLATE utf8mb4_bin COMMENT '摘要',
`click` int(11) DEFAULT '0' COMMENT '点击/阅读',
`reply` int(11) DEFAULT '0' COMMENT '回复/评论',
`repost` int(11) DEFAULT '0' COMMENT '转发',
`praise` int(11) DEFAULT '0' COMMENT '点赞',
`collect` int(11) DEFAULT '0' COMMENT '收藏',
`watch` int(11) DEFAULT NULL,
`wordscount` int(11) DEFAULT '0' COMMENT '字数',
`keywordcount` int(11) DEFAULT NULL,
`siteid` int(11) DEFAULT '0',
`domain` varchar(60) COLLATE utf8mb4_bin DEFAULT '',
`author` varchar(60) COLLATE utf8mb4_bin DEFAULT '',
`author_id` varchar(60) COLLATE utf8mb4_bin DEFAULT '' ,
`posttime` datetime NOT NULL,
`include_t` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`type` tinyint(4) DEFAULT '0',
`source` int(11) DEFAULT '0',
`hidden` tinyint(4) NOT NULL DEFAULT '0',
`sourcetype` tinyint(4) NOT NULL,
`crisis_post` tinyint(4) NOT NULL DEFAULT '0',
`ontop` tinyint(4) NOT NULL DEFAULT '0',
`type_rank` tinyint(4) NOT NULL DEFAULT '0' ,
`noise_rank` tinyint(4) NOT NULL DEFAULT '0' ,
`device` varchar(60) COLLATE utf8mb4_bin DEFAULT,
`is_origin` tinyint(4) NOT NULL DEFAULT '0' ,
`is_top` tinyint(4) NOT NULL DEFAULT '0' ,
`media_type` tinyint(4) NOT NULL DEFAULT '0',
`author_type` tinyint(4) NOT NULL DEFAULT '0' ,
`content_type` tinyint(4) NOT NULL DEFAULT '0' ,
`client_type` tinyint(4) NOT NULL DEFAULT '0' ,
`industry` varchar(60) COLLATE utf8mb4_bin DEFAULT '',
`tags` text COLLATE utf8mb4_bin,
`post_type` tinyint(4) DEFAULT '0',
`type_reason` varchar(256) COLLATE utf8mb4_bin DEFAULT '',
`update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ,
`origin_source` varchar(64) COLLATE utf8mb4_bin DEFAULT '' ,
`media_id` int(11) DEFAULT '0' ,
`w_level` tinyint(1) NOT NULL DEFAULT '0' ,
PRIMARY KEY (`postid`),
UNIQUE KEY `idx_fid_url` (`facetid`,`url`),
UNIQUE KEY `postid` (`postid`),
KEY `idx_xpost_url` (`url`(255)),
KEY `idx_xpost_entryid` (`entryid`),
KEY `idx_xpost_type` (`type`),
KEY `idx_xpost_sourcetype` (`sourcetype`),
KEY `idx_xpost_pt` (`posttime`),
KEY `idx_xpost_source` (`source`),
KEY `idx_fid` (`facetid`)
) ENGINE=InnoDB AUTO_INCREMENT=35754662501 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=DYNAMIC;
【执行计划】
id task estRows operator info actRows execution info memory disk
Sort_6 root 1 Column#44:desc 10 time:8m12.35556453s, loops:2 2.90625 KB 0 Bytes
└─Projection_8 root 1 bsppr.xpost.sourcetype, Column#44 10 time:8m12.355521054s, loops:5, Concurrency:OFF 744 Bytes N/A
└─HashAgg_24 root 1 group by:bsppr.xpost.sourcetype, funcs:count(Column#47)->Column#44, funcs:firstrow(bsppr.xpost.sourcetype)->bsppr.xpost.sourcetype 10 time:8m12.355510469s, loops:5, PartialConcurrency:4, FinalConcurrency:4 18.8046875 KB N/A
└─IndexLookUp_25 root 1 716 time:8m12.355212168s, loops:2, index_task:565.047179ms, table_task:{num:989, concurrency:4, time:32m49.300313321s} 116.0312728881836 MB N/A
├─IndexScan_21 cop[tikv] 292193.528 table:xpost, index:idx_xpost_type(type), range:[-1,-1], keep order:false, stats:pseudo 20169439 time:8m11.734913647s, loops:989, cop_task: {num: 23, max: 8.141668188s, min: 80.969035ms, avg: 3.097711149s, p95: 7.954322171s, max_proc_keys: 1427031, p95_proc_keys: 1318099, tot_proc: 15.032s, tot_wait: 264ms, rpc_num: 23, rpc_time: 1m11.246906286s, copr_cache_hit_ratio: 0.00}, tikv_task:{proc max:1.6s, min:69ms, p80:817ms, p95:930ms, iters:19802, tasks:23} N/A N/A
└─HashAgg_10 cop[tikv] 1 group by:bsppr.xpost.sourcetype, funcs:count(bsppr.xpost.postid)->Column#47 716 time:32m38.136256077s, loops:1040, cop_task: {num: 4379, max: 15.333293494s, min: 2.061226ms, avg: 1.089125071s, p95: 2.611202658s, max_proc_keys: 14882, p95_proc_keys: 8965, tot_proc: 53m54.918s, tot_wait: 34.944s, rpc_num: 4379, rpc_time: 1h19m29.201809423s, copr_cache_hit_ratio: 0.00}, tikv_task:{proc max:15.096s, min:1ms, p80:954ms, p95:1.908s, iters:21898, tasks:4379} N/A N/A
└─Selection_23 cop[tikv] 82.39857489600008 in(bsppr.xpost.entryid, 2113792, 2110849, 2113160, 2110857, 2110223, 2112530, 2113268, 2111130, 2110368, 2111010, 2112937, 2113067, 2110508, 2110765, 2113201, 2112820, 2114102, 2111799, 2109624, 2109625, 2110010, 2109500, 2109377, 2110786, 2112715, 2109508, 2107847, 2110536, 2107849, 2110026, 2111307, 2112588, 2107854, 2113105, 2111316, 2111573, 2113622, 2111375, 2113628, 2114017, 2114022, 2112116, 2112504, 2107561, 2109688, 2112121, 2111102), in(bsppr.xpost.hidden, -2, -1, 0, 2, 3, 4) 3440 time:0ns, loops:0, tikv_task:{proc max:15.096s, min:1ms, p80:954ms, p95:1.908s, iters:21898, tasks:4379} N/A N/A
└─TableScan_22 cop[tikv] 292193.528 table:xpost, keep order:false, stats:pseudo 20169439 time:0ns, loops:0, tikv_task:{proc max:15.096s, min:1ms, p80:952ms, p95:1.907s, iters:21898, tasks:4379} N/A N/A
【explain】
id estRows task access object operator info
Sort_6 1.00 root Column#44:desc
└─Projection_8 1.00 root bsppr.xpost.sourcetype, Column#44
└─HashAgg_24 1.00 root group by:bsppr.xpost.sourcetype, funcs:count(Column#47)->Column#44, funcs:firstrow(bsppr.xpost.sourcetype)->bsppr.xpost.sourcetype
└─IndexLookUp_25 1.00 root
├─IndexRangeScan_21(Build) 292269.84 cop[tikv] table:xpost, index:idx_xpost_type(type) range:[-1,-1], keep order:false, stats:pseudo
└─HashAgg_10(Probe) 1.00 cop[tikv] group by:bsppr.xpost.sourcetype, funcs:count(bsppr.xpost.postid)->Column#47
└─Selection_23 82.42 cop[tikv] in(bsppr.xpost.entryid, 2113792, 2110849, 2113160, 2110857, 2110223, 2112530, 2113268, 2111130, 2110368, 2111010, 2112937, 2113067, 2110508, 2110765, 2113201, 2112820, 2114102, 2111799, 2109624, 2109625, 2110010, 2109500, 2109377, 2110786, 2112715, 2109508, 2107847, 2110536, 2107849, 2110026, 2111307, 2112588, 2107854, 2113105, 2111316, 2111573, 2113622, 2111375, 2113628, 2114017, 2114022, 2112116, 2112504, 2107561, 2109688, 2112121, 2111102), in(bsppr.xpost.hidden, -2, -1, 0, 2, 3, 4)
└─TableRowIDScan_22 292269.84 cop[tikv] table:xpost keep order:false, stats:pseudo