【TiDB 使用环境】生产环境
【TiDB 版本】v7.1.5
【操作系统】centos7.9
【部署方式】物理机部署，nvme单盘
【集群数据量】100亿+
【集群节点数】
【问题复现路径】做过哪些操作出现的问题
【遇到的问题：问题现象及影响】
MySQL [bsppr]> explain ANALYZE SELECT xpost.postid, xpost.facetid, xpost.entryid, xpost.title, xpost.url, xpost.abstract, xpost.click, xpost.reply, xpost.repost, xpost.praise, xpost.collect, xpost.watch, xpost.wordscount, xpost.keywordcount, 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.sid, xpost.location, xpost.is_comment, xpost.pos_type_rank, xpost.text, xpost.spider_time, xpost.process_time, xpost.tidb_in_time, xpost.is_yqt, xpost.fans_num, xpost.gender, xpost.author_location, xpost.verify_info, xpost.author_tags, xpost.is_ocr, xpost.signature, xpost.api_call_num, xpost.api_call_time, xpost.extra_str_1, xpost.extra_str_2, xpost.extra_str_3, xpost.extra_str_4, xpost.extra_str_5, xpost.extra_str_6, xpost.extra_str_7, xpost.extra_str_8, xpost.extra_text_1, xpost.extra_int_1, xpost.extra_int_2, xpost.extra_int_3, xpost.extra_float_1, xpost.extra_float_2, xpost.extra_float_3, xpost.media_industry, xpost.cluster_id, xpost.parent_url, xpost.personal_sourcetype, xpost.personal_domain, xpost.server_info
-> FROM xpost
-> WHERE xpost.tidb_in_time >= '2025-04-25 18:00:01' AND xpost.tidb_in_time <= '2025-04-25 19:00:00' AND true AND true AND xpost.tidb_in_time >= '2025-04-25 17:57:57' ORDER BY xpost.tidb_in_time;
+----------------------------------+-----------+---------+-----------+----------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+------+
| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |
+----------------------------------+-----------+---------+-----------+----------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+------+
| Projection_14 | 220440.85 | 1666220 | root | | time:42.2s, loops:1629, RU:85362.928183, Concurrency:5 | 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.watch, bsppr.xpost.wordscount, bsppr.xpost.keywordcount, 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.sid, bsppr.xpost.location, bsppr.xpost.is_comment, bsppr.xpost.pos_type_rank, bsppr.xpost.text, bsppr.xpost.spider_time, bsppr.xpost.process_time, bsppr.xpost.tidb_in_time, bsppr.xpost.is_yqt, bsppr.xpost.fans_num, bsppr.xpost.gender, bsppr.xpost.author_location, bsppr.xpost.verify_info, bsppr.xpost.author_tags, bsppr.xpost.is_ocr, bsppr.xpost.signature, bsppr.xpost.api_call_num, bsppr.xpost.api_call_time, bsppr.xpost.extra_str_1, bsppr.xpost.extra_str_2, bsppr.xpost.extra_str_3, bsppr.xpost.extra_str_4, bsppr.xpost.extra_str_5, bsppr.xpost.extra_str_6, bsppr.xpost.extra_str_7, bsppr.xpost.extra_str_8, bsppr.xpost.extra_text_1, bsppr.xpost.extra_int_1, bsppr.xpost.extra_int_2, bsppr.xpost.extra_int_3, bsppr.xpost.extra_float_1, bsppr.xpost.extra_float_2, bsppr.xpost.extra_float_3, bsppr.xpost.media_industry, bsppr.xpost.cluster_id, bsppr.xpost.parent_url, bsppr.xpost.personal_sourcetype, bsppr.xpost.personal_domain, bsppr.xpost.server_info | 108.0 MB | N/A |
| └─IndexLookUp_17 | 275551.06 | 1666220 | root | | time:42.2s, loops:1629, index_task: {total_time: 40.6s, fetch_handle: 262.4ms, build: 385.7ms, wait: 40s}, table_task: {total_time: 3m28s, num: 85, concurrency: 5}, next: {wait_index: 7.38ms, wait_table_lookup_build: 405.8µs, wait_table_lookup_resp: 37.7s} | | 565.7 MB | N/A |
| ├─IndexRangeScan_15(Build) | 275551.06 | 1666220 | cop[tikv] | table:xpost, index:idx_tidb_in_time_posttime(tidb_in_time, posttime) | time:129.2ms, loops:1634, cop_task: {num: 58, max: 358.9ms, min: 844.4µs, avg: 36ms, p95: 151.9ms, max_proc_keys: 50144, p95_proc_keys: 50144, tot_proc: 971.2ms, tot_wait: 7.95ms, rpc_num: 58, rpc_time: 2.09s, copr_cache_hit_ratio: 0.00, build_task_duration: 1.34ms, max_distsql_concurrency: 2}, tikv_task:{proc max:48ms, min:0s, avg: 16.2ms, p80:25ms, p95:41ms, iters:1857, tasks:58}, scan_detail: {total_process_keys: 1666220, total_process_keys_size: 91642100, total_keys: 1666460, get_snapshot_time: 6.05ms, rocksdb: {key_skipped_count: 1666402, block: {cache_hit_count: 894, read_count: 2136, read_byte: 14.4 MB, read_time: 10.7ms}}} | range:[2025-04-25 18:00:01,2025-04-25 19:00:00], keep order:true | N/A | N/A |
| └─TableRowIDScan_16(Probe) | 275551.06 | 1666220 | cop[tikv] | table:xpost | time:3m26.6s, loops:1827, cop_task: {num: 234, max: 5.97s, min: 7.08ms, avg: 1.57s, p95: 3.87s, max_proc_keys: 18817, p95_proc_keys: 14475, tot_proc: 1m5.6s, tot_wait: 218.2ms, rpc_num: 234, rpc_time: 6m7s, copr_cache_hit_ratio: 0.00, build_task_duration: 47.1ms, max_distsql_concurrency: 5, max_extra_concurrency: 1}, tikv_task:{proc max:1s, min:1ms, avg: 269.7ms, p80:403ms, p95:690ms, iters:2663, tasks:234}, scan_detail: {total_process_keys: 1666220, total_process_keys_size: 4044373946, total_keys: 3451882, get_snapshot_time: 210.2ms, rocksdb: {delete_skipped_count: 758415, key_skipped_count: 5874121, block: {cache_hit_count: 11084336, read_count: 321959, read_byte: 1.85 GB, read_time: 5.25s}}} | keep order:false | N/A | N/A |
+----------------------------------+-----------+---------+-----------+----------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+------+
4 rows in set (42.21 sec)
以上我的SQL，不定期的会出现超限制 tidb_mem_quota_query，但我手动执行 explain analyze 才占用内存不到1G，我限制是3G， 这是什么原因？
pymysql.err.OperationalError: (1105, 'Your query has been cancelled due to exceeding the allowed memory limit for a single SQL query. Please try narrowing your query scope or increase the tidb_mem_quota_query limit and try again.[conn=4981223080481497723]')
【资源配置】进入到 TiDB Dashboard -集群信息 (Cluster Info) -主机(Hosts) 截图此页面
【复制黏贴 ERROR 报错的日志】
【其他附件：截图/日志/监控】
执行计划没有走索引应该也会出现。 要不考虑先把限制调大，然后在dashboard里看看用内存多的时候，执行计划是怎样的。
你的意思是，不管什么时间执行，都是查询2025-04-25 17:57:57到2025-04-25 18:00:01的？如果只是时间间隔固定，也可能出现某一时间段内数据特别多吧。或者刚好赶上自动analyze，导致执行计划不准 