慢SQL优化提升咨询

有单表接近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

1、看着没有索引,可以创建个索引
2、这种 SQL 多的话,也可以考虑使用 tiflash ,不过 创建个索引肯定就OK了

从语句来看,只有hidden没有索引,我加一下试试,只不过加索引需要不少时间

加索引时,可以调整一下 ddl 相关的参数:show variables like ‘%recorg%; 有2个参数:size /work 可以适当调大一些来加快速度

通过增加hidden字段索引,速度确实提升很大。
但是这个语句没有变化,仍需要40多分钟。
请指点。谢谢

SELECT `xpost`.`postid`, `xpost`.`facetid`, `xpost`.`entryid`, `xpost`.`title`, `xpost`.`url`, `xpost`.`abstract`, `xpost`.`click`, `xpost`.`reply`, `xpost`.`repost`, `xpost`.`praise`, `xpost`.`collect`, `xpost`.`wordscount`, `xpost`.`siteid`, `xpost`.`domain`, `xpost`.`author`, `xpost`.`author_id`, `xpost`.`posttime`, `xpost`.`include_t`, `xpost`.`type`, `xpost`.`source`, `xpost`.`hidden`, `xpost`.`sourcetype`, `xpost`.`crisis_post`, `xpost`.`ontop`, `xpost`.`type_rank`, `xpost`.`noise_rank`, `xpost`.`device`, `xpost`.`is_origin`, `xpost`.`is_top`, `xpost`.`media_type`, `xpost`.`author_type`, `xpost`.`content_type`, `xpost`.`client_type`, `xpost`.`industry`, `xpost`.`tags`, `xpost`.`post_type`, `xpost`.`type_reason`, `xpost`.`update_time`, `xpost`.`origin_source`, `xpost`.`media_id`, `xpost`.`w_level`, `xpost`.`watch`, `xpost`.`keywordcount` FROM `xpost` WHERE (`xpost`.`entryid` = 2067481 AND `xpost`.`hidden` in (-2,-1,0,2,3,4)) ORDER BY `xpost`.`posttime` ASC LIMIT 1;

麻烦提供下具体的执行计划看下

感谢回复,执行计划如下:

	id                  	task     	estRows           	operator info                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        	actRows  	execution info                                                                                                                                                                                                                                                                                                                                                                                                                                                              	memory               	disk
	Projection_7        	root     	1                 	bsppr.xpost.postid, bsppr.xpost.facetid, bsppr.xpost.entryid, bsppr.xpost.title, bsppr.xpost.url, bsppr.xpost.abstract, bsppr.xpost.click, bsppr.xpost.reply, bsppr.xpost.repost, bsppr.xpost.praise, bsppr.xpost.collect, bsppr.xpost.wordscount, bsppr.xpost.siteid, bsppr.xpost.domain, bsppr.xpost.author, bsppr.xpost.author_id, bsppr.xpost.posttime, bsppr.xpost.include_t, bsppr.xpost.type, bsppr.xpost.source, bsppr.xpost.hidden, bsppr.xpost.sourcetype, bsppr.xpost.crisis_post, bsppr.xpost.ontop, bsppr.xpost.type_rank, bsppr.xpost.noise_rank, bsppr.xpost.device, bsppr.xpost.is_origin, bsppr.xpost.is_top, bsppr.xpost.media_type, bsppr.xpost.author_type, bsppr.xpost.content_type, bsppr.xpost.client_type, bsppr.xpost.industry, bsppr.xpost.tags, bsppr.xpost.post_type, bsppr.xpost.type_reason, bsppr.xpost.update_time, bsppr.xpost.origin_source, bsppr.xpost.media_id, bsppr.xpost.w_level, bsppr.xpost.watch, bsppr.xpost.keywordcount	1        	time:47m28.879521686s, loops:2, Concurrency:OFF                                                                                                                                                                                                                                                                                                                                                                                                                             	5.5263671875 KB      	N/A
	└─Limit_12          	root     	1                 	offset:0, count:1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    	1        	time:47m28.879444478s, loops:2                                                                                                                                                                                                                                                                                                                                                                                                                                              	N/A                  	N/A
	  └─IndexLookUp_40  	root     	0.9999999999999999	                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     	1        	time:47m28.879439899s, loops:1, index_task:16.064645384s, table_task:{num:6674, concurrency:4, time:3h10m0.553811573s}                                                                                                                                                                                                                                                                                                                                                      	233.16255950927734 MB	N/A
	    ├─IndexScan_37  	cop[tikv]	10156.000000000002	table:xpost, index:idx_xpost_pt(posttime), keep order:true                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           	136683570	time:47m28.750623511s, loops:6674, cop_task: {num: 151, max: 29.215824827s, min: 34.786174ms, avg: 5.259103346s, p95: 17.498300392s, max_proc_keys: 1447226, p95_proc_keys: 1370023, tot_proc: 1m55.24s, tot_wait: 9.678s, rpc_num: 158, rpc_time: 13m14.117553845s, copr_cache_hit_ratio: 0.00}, backoff{regionMiss: 176ms}, tikv_task:{proc max:2.947s, min:31ms, p80:855ms, p95:1.507s, iters:134162, tasks:151}                                                         	N/A                  	N/A
	    └─Selection_39  	cop[tikv]	0.9999999999999999	eq(bsppr.xpost.entryid, 2067481), in(bsppr.xpost.hidden, -2, -1, 0, 2, 3, 4)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         	110      	time:3h8m13.081425593s, loops:6680, cop_task: {num: 1455461, max: 28.162318346s, min: 525.212µs, avg: 85.06388ms, p95: 331.470885ms, max_proc_keys: 16941, p95_proc_keys: 366, tot_proc: 3h45m24.464s, tot_wait: 5h20m20.974s, rpc_num: 1460149, rpc_time: 34h26m14.550078995s, copr_cache_hit_ratio: 0.00}, backoff{regionMiss: 3m49.55s, staleCommand: 9.026s, tikvRPC: 23m15.47s}, tikv_task:{proc max:14.871s, min:0s, p80:6ms, p95:35ms, iters:2505534, tasks:1455461}	N/A                  	N/A
	      └─TableScan_38	cop[tikv]	10156.000000000002	table:xpost, keep order:false                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        	136423322	time:0ns, loops:0, tikv_task:{proc max:14.87s, min:0s, p80:6ms, p95:35ms, iters:2505534, tasks:1455461}                                                                                                                                                                                                                                                                                                                                                                     	N/A                  	N/A

从执行计划看,应该是回表查询时间比较长,可以参考一下楼上建议使用 tiflash 列存 cop 下推计算。或者建一个多列复合索引,减少回表次数。

去掉最后的LIMIT 1,变成秒级查询。
所以加上FORCE INDEX (idx_xpost_entryid) 就好了

:+1::+1:

此话题已在最后回复的 1 分钟后被自动关闭。不再允许新回复。