tidb 单表过大无法进行analyze

🪐 TiDB 技术问题
1

【TiDB 使用环境】生产环境

情况描述：当前集群存在较多大单表，目前已经无法进行正常analyze 寻求解决方法
TABLE_ROWS
3749987385

2

啊, 有分区吗?

3

是不是先拆一下表?

4

https://docs.pingcap.com/zh/tidb/stable/system-variables/#tidb_analyze_skip_column_types-从-v720-版本开始引入

1 个赞
5

可以修改 ANALYZE 的采样率、并行度、超时时间等参数，降低执行压力

1 个赞
6

单表的

7

类似 这种情况咋样处理好些呢

TABLE_NAME	PARTITION_NAME	JOB_INFO	PROCESSED_ROWS	START_TIME	END_TIME	STATE	FAIL_REASON	INSTANCE	PROCESS_ID	REMAINING_SECONDS	PROGRESS	ESTIMATED_TOTAL_ROWS
xxxx		auto analyze table columns id, region, seller_id, country_code, financial_event_group_id, account_type, shipment_id, shipment_item_id, amazon_order_id, order_item_id, row_index, sales_channel, fulfillment, is_reship, purchase_date_locale, estimated_arrival_date_locale, shipment_date_locale, payments_date_locale, reporting_date_locale, finance_posted_date_locale, seller_sku, quantity, currency_code, report_currency_amount, finance_currency_amount, fba_per_unit_fulfillment_fee, md5, gmt_modified, gmt_create with 256 buckets, 500 topn, 8.18555580286261e-06 samplerate	468309314	2025/12/2 10:00	2025/12/2 14:03	failed	[tikv:1317]Query execution was interrupted	172.10.243:4000	(null)	(null)	(null)	(null)
xxxx		auto analyze table columns id, region, seller_id, country_code, financial_event_group_id, account_type, shipment_id, shipment_item_id, amazon_order_id, order_item_id, row_index, sales_channel, fulfillment, is_reship, purchase_date_locale, estimated_arrival_date_locale, shipment_date_locale, payments_date_locale, reporting_date_locale, finance_posted_date_locale, seller_sku, quantity, currency_code, report_currency_amount, finance_currency_amount, fba_per_unit_fulfillment_fee, md5, gmt_modified, gmt_create with 256 buckets, 500 topn, 8.197638031590141e-06 samplerate	462825052	2025/12/1 10:00	2025/12/1 14:03	failed	[tikv:1317]Query execution was interrupted	172.10.243:4000	(null)	(null)	(null)	(null)
xxxx		auto analyze table columns id, region, seller_id, country_code, financial_event_group_id, account_type, shipment_id, shipment_item_id, amazon_order_id, order_item_id, row_index, sales_channel, fulfillment, is_reship, purchase_date_locale, estimated_arrival_date_locale, shipment_date_locale, payments_date_locale, reporting_date_locale, finance_posted_date_locale, seller_sku, quantity, currency_code, report_currency_amount, finance_currency_amount, fba_per_unit_fulfillment_fee, md5, gmt_modified, gmt_create with 256 buckets, 500 topn, 8.210384346663156e-06 samplerate	477139562	2025/11/30 10:00	2025/11/30 14:03	failed	[tikv:1317]Query execution was interrupted	172.10.243:4000	(null)	(null)	(null)	(null)
xxxx		auto analyze table columns id, region, seller_id, country_code, financial_event_group_id, account_type, shipment_id, shipment_item_id, amazon_order_id, order_item_id, row_index, sales_channel, fulfillment, is_reship, purchase_date_locale, estimated_arrival_date_locale, shipment_date_locale, payments_date_locale, reporting_date_locale, finance_posted_date_locale, seller_sku, quantity, currency_code, report_currency_amount, finance_currency_amount, fba_per_unit_fulfillment_fee, md5, gmt_modified, gmt_create with 256 buckets, 500 topn, 8.225267375259613e-06 samplerate	466809083	2025/11/29 10:00	2025/11/29 14:03	failed	[tikv:1317]Query execution was interrupted	172.10.243:4000	(null)	(null)	(null)	(null)
xxxx		auto analyze table columns id, region, seller_id, country_code, financial_event_group_id, account_type, shipment_id, shipment_item_id, amazon_order_id, order_item_id, row_index, sales_channel, fulfillment, is_reship, purchase_date_locale, estimated_arrival_date_locale, shipment_date_locale, payments_date_locale, reporting_date_locale, finance_posted_date_locale, seller_sku, quantity, currency_code, report_currency_amount, finance_currency_amount, fba_per_unit_fulfillment_fee, md5, gmt_modified, gmt_create with 256 buckets, 500 topn, 8.238469679224119e-06 samplerate	532782240	2025/11/28 10:00	2025/11/28 14:03	failed	[tikv:1317]Query execution was interrupted	172.10.243:4000	(null)	(null)	(null)	(null)
xxxx		auto analyze table columns id, region, seller_id, country_code, financial_event_group_id, account_type, shipment_id, shipment_item_id, amazon_order_id, order_item_id, row_index, sales_channel, fulfillment, is_reship, purchase_date_locale, estimated_arrival_date_locale, shipment_date_locale, payments_date_locale, reporting_date_locale, finance_posted_date_locale, seller_sku, quantity, currency_code, report_currency_amount, finance_currency_amount, fba_per_unit_fulfillment_fee, md5, gmt_modified, gmt_create with 256 buckets, 500 topn, 8.250006159248349e-06 samplerate	379219918	2025/11/27 10:36	2025/11/27 14:03	failed	[tikv:1317]Query execution was interrupted	172.10.243:4000	(null)	(null)	(null)	(null)
8

我2000亿行记录的表做analyze都可以的
手动analyze一张大表超出内存限制 - #29，来自 像风一样的男子.

9

1, tidb_enable_historical_stats 关闭
2,手动analyze table
是这样吗风哥

10

image
image1171×298 25.9 KB

风哥 这个参数关闭后 现有的查询还有执行计划吗

11

就是这样。前面的已经说过没得问题

12

有最新的结果嘛

13

还没尝试

14

30多亿记录手动做一次analyze一个小时不到吧，可以试下

15

你这个自动analyze的开始时间和结束时间有限制吗？

16

没有的风哥 并且目前手动是失败的 关闭那个参数 之前的执行计划还在吗

17

关闭那个参数不影响你的sql执行计划的，这个只影响统计信息收集

18

执行完之后需要开回去吗

19

不需要吧。新版本默认值off

20

通过「采样分析」快速生成统计信息