Analysis of Slow SQL Query Issues in TiDB

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: TiDB SQL查询慢问题分析

| username: 雪落香杉树

[TiDB Usage Environment] Production Environment
[TiDB Version] v5.4.3
[Reproduction Path] None
[Encountered Problem: Problem Phenomenon and Impact]
TiDB is very slow when querying 65 rows of data based on the index. The SQL, execution plan, and table structure are as follows:

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;

Execution Plan:

	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

Table Structure:

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 'Region Code',
  `amazon_order_id` varchar(50) NOT NULL DEFAULT '' COMMENT 'Order Number',
  `order_last_update_time` int(10) NOT NULL DEFAULT '0' COMMENT 'Last Update Time Recorded in Order Content',
  `request_timestamp` int(10) NOT NULL DEFAULT '0' COMMENT 'Request Interface Time',
  `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 'Purchase Time',
  `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 'Data Update Time',
  `gmt_create` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Data Creation Time',
  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 */

Querying the total number of rows based on order_id: 65 rows:

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' 
	) 

Query is fast (0.1s) after removing the order by clause. Execution plan after removal:

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
| username: zhaokede | Original post link

Is the purchase_date field indexed? If not, it will definitely be slow.

| username: Jasper | Original post link

Your first execution plan needs to use the orderliness of the purchase_date index to sort. After removing the sorting logic, you can directly read the amazon_order_id index. You can try creating a composite index of amazon_order_id and purchase_date and then run it again.

| username: 江湖故人 | Original post link

Add a hint
/*+ USE_INDEX(sp_amzn_order_item_ods_v2, idx-amazon_order_id) */

| username: caiyfc | Original post link

Looking at the execution plan, it shows that after filtering, an estimated 42,376,833.94 rows of data remain. The filter selectivity is so low that it first sorts by index and then filters. However, the actual execution plan shows good filter selectivity, with actRows showing 0. So, you can first analyze this table and then execute the SQL, or you can directly use a hint to bind the execution plan to use the amazon_order_id index.

| username: 江湖故人 | Original post link

Tables with pseudo statistics need to have statistics collected, otherwise the execution plan will be skewed.
ANALYZE TABLE sp_amzn_order_item_ods_v2;

| username: tidb菜鸟一只 | Original post link

When you see the keyword “pseudo,” just collect statistical information directly, no questions asked.

| username: H2O2 | Original post link

Check the purchase_date field to see if an index has been created. Try adding an index and see if it helps.

| username: 这里介绍不了我 | Original post link

  • stats:pseudo indicates that the estRows displayed might be inaccurate. TiDB periodically updates statistics in the background. You can also manually update statistics by executing ANALYZE TABLE t.
| username: 这里介绍不了我 | Original post link

First, update the statistics. If the SQL execution frequency is very high, you can consider creating a composite index on amazon_order_id & purchase_date. Additionally, you can replace “select *” with the specific fields required by the business.

| username: buddyyuan | Original post link

You used the index for the order by. You can directly change the order by field to +0, since it’s an int type. Classic solution.

| username: Soysauce520 | Original post link

The wrong index was used, the statistics might be an issue, and there could also be an estimation problem. The safest approach is to use hint binding.

| username: redgame | Original post link

Check the statistics collection time.

| username: Jiawei | Original post link

My understanding is that creating a combined index with id and date can significantly improve the performance of full index sorting.

| username: TiDBer_aaO4sU46 | Original post link

It looks like the statistics are incorrect.

| username: 小龙虾爱大龙虾 | Original post link

This index might not work very well :neutral_face:, it’s better to add a hint.

| username: Kongdom | Original post link

This solution is quite brilliant, really learned something.

| username: Jellybean | Original post link

The keyword “stats:pseudo” has already appeared in the execution plan, indicating that the statistics are outdated. First, manually update the statistics with Analyze.

| username: Kongdom | Original post link

:yum: It would be great if TiDB could automatically recognize this keyword and then trigger a statistics collection.

| username: WalterWj | Original post link

Upgrade: I remember that the new version has optimizations for this kind of sorting that interferes with the execution plan logic optimization.