TiDB SQL查询慢问题分析

雪落香杉树 · 2024 年3 月 13 日 03:08

【 TiDB 使用环境】生产环境
【 TiDB 版本】v5.4.3
【复现路径】无
【遇到的问题：问题现象及影响】
TiDB根据索引查询65条数据很慢，SQL，执行计划和表结构如下：

SELECT
	* 
FROM
	sp_amzn_order_item_ods_v2 
WHERE
	amazon_order_id IN (
		'113-7859550-1819431',
		'114-4128634-7767415',
		'111-0906088-7900212',
		'114-0612396-3973015',
		'111-0137857-8760255',
		'113-2580885-2600200',
		'111-1797559-5097806',
		'111-7691865-0851427',
		'112-0601458-9140213',
		'114-7117348-3914644',
		'111-9105406-5359455',
		'112-3369338-7113832',
		'114-6228916-8645832',
		'114-1304613-1266624',
		'114-9101707-6609816',
		'113-5784092-7551447',
		'112-5486596-2654655',
		'113-2879780-9145002',
		'113-5818133-6757032',
		'111-9384693-1232263' 
	) 
ORDER BY
	purchase_date;

执行计划：

	id                   	task     	estRows    	operator info                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        	actRows 	execution info                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            	memory 	disk
	IndexLookUp_18       	root     	42376833.94	                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     	0       	time:5m0s, loops:1, index_task: {total_time: 4m53.8s, fetch_handle: 1.92s, build: 3.78s, wait: 4m48.1s}, table_task: {total_time: 49m28.3s, num: 628, concurrency: 10}                                                                                                                                                                                                                                                                                                                                                                                                                                    	94.2 MB	N/A
	├─IndexFullScan_15   	cop[tikv]	2118841697 	table:sp_amzn_order_item_ods_v2, index:idx-purchase_date(purchase_date), keep order:true, stats:pseudo                                                                                                                                                                                                                                                                                                                                                                                                                               	12924236	time:633.5ms, loops:12532, cop_task: {num: 47, max: 1.25s, min: 6.95ms, avg: 435.9ms, p95: 766.6ms, max_proc_keys: 548149, p95_proc_keys: 495746, tot_proc: 19.1s, tot_wait: 538ms, rpc_num: 47, rpc_time: 20.5s, copr_cache_hit_ratio: 0.00}, tikv_task:{proc max:584ms, min:5ms, p80:443ms, p95:545ms, iters:12832, tasks:47}, scan_detail: {total_process_keys: 12924236, total_process_keys_size: 594514856, total_keys: 18470727, rocksdb: {delete_skipped_count: 970052, key_skipped_count: 19440732, block: {cache_hit_count: 471, read_count: 14989, read_byte: 469.9 MB}}}                       	N/A    	N/A
	└─Selection_17       	cop[tikv]	42376833.94	in(sp_order_db.sp_amzn_order_item_ods_v2.amazon_order_id, "113-7859550-1819431", "114-4128634-7767415", "111-0906088-7900212", "114-0612396-3973015", "111-0137857-8760255", "113-2580885-2600200", "111-1797559-5097806", "111-7691865-0851427", "112-0601458-9140213", "114-7117348-3914644", "111-9105406-5359455", "112-3369338-7113832", "114-6228916-8645832", "114-1304613-1266624", "114-9101707-6609816", "113-5784092-7551447", "112-5486596-2654655", "113-2879780-9145002", "113-5818133-6757032", "111-9384693-1232263")	0       	time:48m24.7s, loops:628, cop_task: {num: 3700329, max: 1.96s, min: 377µs, avg: 11.5ms, p95: 44.4ms, max_proc_keys: 453, p95_proc_keys: 11, tot_proc: 1h40m53.4s, tot_wait: 7h53m20.7s, rpc_num: 3700329, rpc_time: 11h44m33.2s, copr_cache_hit_ratio: 0.00}, tikv_task:{proc max:188ms, min:0s, p80:2ms, p95:5ms, iters:3743402, tasks:3700329}, scan_detail: {total_process_keys: 12703189, total_process_keys_size: 15737276643, total_keys: 12785620, rocksdb: {delete_skipped_count: 97, key_skipped_count: 286521, block: {cache_hit_count: 195004941, read_count: 12531521, read_byte: 197.7 GB}}}	N/A    	N/A
	  └─TableRowIDScan_16	cop[tikv]	2118841697 	table:sp_amzn_order_item_ods_v2, keep order:false, stats:pseudo                                                                                                                                                                                                                                                                                                                                                                                                                                                                      	12703189	tikv_task:{proc max:188ms, min:0s, p80:2ms, p95:5ms, iters:3743402, tasks:3700329}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        	N/A    	N/A

表结构：

CREATE TABLE `sp_amzn_order_item_ods_v2` (
  `id` bigint(20) NOT NULL /*T![auto_rand] AUTO_RANDOM(4) */,
  `asid` bigint(20) unsigned NOT NULL DEFAULT '0',
  `seller_id` varchar(100) NOT NULL DEFAULT '',
  `marketplace_id` varchar(50) NOT NULL DEFAULT '',
  `region` char(2) NOT NULL DEFAULT '' COMMENT '地区编码',
  `amazon_order_id` varchar(50) NOT NULL DEFAULT '' COMMENT '订单号',
  `order_last_update_time` int(10) NOT NULL DEFAULT '0' COMMENT '订单内容里记录的最后修改时间',
  `request_timestamp` int(10) NOT NULL DEFAULT '0' COMMENT '请求接口的时间',
  `res_json` json NOT NULL COMMENT 'order item json',
  `buyer_info` varchar(1000) NOT NULL DEFAULT '',
  `item_md5` varchar(32) NOT NULL DEFAULT '',
  `purchase_date` int(10) NOT NULL DEFAULT '0' COMMENT '购买时间',
  `timezone` varchar(20) NOT NULL,
  `fulfillment_channel` varchar(100) NOT NULL DEFAULT '',
  `sales_channel` varchar(100) NOT NULL DEFAULT '',
  `api_type` varchar(20) NOT NULL DEFAULT 'sp',
  `gmt_modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '数据更新时间',
  `gmt_create` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '数据创建时间',
  PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */,
  KEY `idx-amazon_order_id` (`amazon_order_id`),
  UNIQUE KEY `uk-order-item` (`seller_id`,`marketplace_id`,`amazon_order_id`,`item_md5`),
  KEY `idx-order_last_update_time` (`seller_id`,`order_last_update_time`),
  KEY `idx-purchase_date` (`purchase_date`),
  KEY `idx-gmtmodified` (`gmt_modified`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin /*T![auto_rand_base] AUTO_RANDOM_BASE=14369210099 */

根据order_id查询总行数65条：

SELECT 
count(*)
FROM sp_amzn_order_item_ods_v2 
WHERE
	amazon_order_id IN (
		'113-7859550-1819431',
		'114-4128634-7767415',
		'111-0906088-7900212',
		'114-0612396-3973015',
		'111-0137857-8760255',
		'113-2580885-2600200',
		'111-1797559-5097806',
		'111-7691865-0851427',
		'112-0601458-9140213',
		'114-7117348-3914644',
		'111-9105406-5359455',
		'112-3369338-7113832',
		'114-6228916-8645832',
		'114-1304613-1266624',
		'114-9101707-6609816',
		'113-5784092-7551447',
		'112-5486596-2654655',
		'113-2879780-9145002',
		'113-5818133-6757032',
		'111-9384693-1232263' 
	)

去掉order by之后查询很快0.1s，去掉之后的执行计划：

IndexLookUp_10	42383080.76	root		
├─IndexRangeScan_8(Build)	42383080.76	cop[tikv]	table:sp_amzn_order_item_ods_v2, index:idx-amazon_order_id(amazon_order_id)	range:["111-0137857-8760255","111-0137857-8760255"], ["111-0906088-7900212","111-0906088-7900212"], ["111-1797559-5097806","111-1797559-5097806"], ["111-7691865-0851427","111-7691865-0851427"], ["111-9105406-5359455","111-9105406-5359455"], ["111-9384693-1232263","111-9384693-1232263"], ["112-0601458-9140213","112-0601458-9140213"], ["112-3369338-7113832","112-3369338-7113832"], ["112-5486596-2654655","112-5486596-2654655"], ["113-2580885-2600200","113-2580885-2600200"], ["113-2879780-9145002","113-2879780-9145002"], ["113-5784092-7551447","113-5784092-7551447"], ["113-5818133-6757032","113-5818133-6757032"], ["113-7859550-1819431","113-7859550-1819431"], ["114-0612396-3973015","114-0612396-3973015"], ["114-1304613-1266624","114-1304613-1266624"], ["114-4128634-7767415","114-4128634-7767415"], ["114-6228916-8645832","114-6228916-8645832"], ["114-7117348-3914644","114-7117348-3914644"], ["114-9101707-6609816","114-9101707-6609816"], keep order:false, stats:pseudo
└─TableRowIDScan_9(Probe)	42383080.76	cop[tikv]	table:sp_amzn_order_item_ods_v2	keep order:false, stats:pseudo

zhaokede · 2024 年3 月 13 日 03:17

purchase_date这个字段是索引吗
不是的话肯定慢

Jasper · 2024 年3 月 13 日 03:26

你第一个执行计划要利用 purchase_date这个索引的有序性来排序，去掉排序逻辑之后直接读 amazon_order_id 这个索引就行了，你可以试试建一个 amazon_order_id，purchase_date 的联合索引再跑一下

江湖故人 · 2024 年3 月 13 日 03:31

加Hint吧
/*+ USE_INDEX(sp_amzn_order_item_ods_v2, idx-amazon_order_id) */

caiyfc · 2024 年3 月 13 日 03:33

你看执行计划，显示过滤之后预估还有42376833.94条数据，过滤性这么低，所以先走索引排序了，再来过滤。但是其实执行计划的过滤性很好，actRows显示是0，所以，可以先analyze一下这个表，再来执行sql，或者你就直接hint绑定执行计划走amazon_order_id 的索引

江湖故人 · 2024 年3 月 13 日 03:34

pseudo的表需要做下统计信息收集，不然执行计划会跑偏
ANALYZE TABLE sp_amzn_order_item_ods_v2;

tidb菜鸟一只 · 2024 年3 月 13 日 03:41

pseudo 看到这个关键字直接收集统计信息就行了，不二话

H2O2 · 2024 年3 月 13 日 06:57

检查下purchase_date字段，有没有做索引，加个索引再试试

这里介绍不了我 · 2024 年3 月 13 日 07:04

stats:pseudo 表示 estRows 显示的预估数可能不准确。TiDB 定期在后台更新统计信息。也可以通过执行 ANALYZE TABLE t 来手动更新统计信息。

这里介绍不了我 · 2024 年3 月 13 日 07:05

先更新下统计信息，如果SQL执行频率很高，可以考虑amazon_order_id & purchase_date 建立一个联合索引，再者可以将select * 替换为业务所需的字段

buddyyuan · 2024 年3 月 13 日 09:14

你这走了 order by 的索引啊，你直接把 order by 字段改成 +0就行了，反正是int类型。经典解决手法。

Soysauce520 · 2024 年3 月 13 日 09:26

走错索引了，统计信息是个问题，也可能是估算有问题，稳妥的方式是hint绑定

redgame · 2024 年3 月 13 日 09:51

查下统计信息收集时间

Jiawei · 2024 年3 月 13 日 10:00

我的理解，这搞个id, date日期联合索引可以提升很大吧全索引排序

TiDBer_aaO4sU46 · 2024 年3 月 13 日 10:10

我看像统计信息不对

小龙虾爱大龙虾 · 2024 年3 月 13 日 11:13

这个索引应该不太行，还是加hint比较好

Kongdom · 2024 年3 月 13 日 13:53

这个解决方案有点天秀啊，真的学习了

Jellybean · 2024 年3 月 13 日 15:04

执行计划里已经出现了 stats:pseudo 关键字，表示统计信息已经过期了，先 Analyze 手动更新统计信息吧

Kongdom · 2024 年3 月 14 日 01:14

如果tidb能自动识别这个关键字，然后触发收集一次统计信息就好了。

WalterWj · 2024 年3 月 14 日 06:35

升级我记得对于这种排序干扰执行计划逻辑优化的新版本有优化。