改造成非分区表后,SQL 查询时间变长。

线上遇过场景,一个按天递增的表A,每天是11:30插入数据,11:35左右插入结束,每天也就30W 数据量,12:00开始有另一个 job 使用唯一键顺序范围查询这个表。

  • 简化的 SQL 为select * from B left join A on B.user_id = A.user_id where A.insert_date = "2024-10-31"
  • 表 A 在分区表时索引为
  PRIMARY KEY (`id`)/*T![clustered_index] CLUSTERED */,
  UNIQUE KEY `idx_user_id`  (`user_id`,`insert_date`),
  KEY `idx_date_userid` (`insert_date`,`user_id`),
  • 为了做读写分离,对表做了分区化改造,索引变成:
  PRIMARY KEY (`user_id`,`insert_date`) /*T![clustered_index] NONCLUSTERED */,
  KEY `idx_id` (`id`),
  KEY `idx_date_userid` (`insert_date`,`user_id`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci 
PARTITION BY RANGE COLUMNS(`insert_date`)
(PARTITION `p202409` VALUES LESS THAN ('2024-10-01'),
 PARTITION `p202410` VALUES LESS THAN ('2024-11-01'),
 PARTITION `p202411` VALUES LESS THAN ('2024-12-01'),
 PARTITION `p202412` VALUES LESS THAN ('2025-01-01'))

在非分区表情况下,这个表在12:00查询时候时间是稳定的,都是 0.5s 左右。理论上改成分区表,查询里包含分区键insert_date每次能定位到所需分区,查询时间会更快才对。
但实际上,改造后查询时间变得很慢,变成了1-3秒, 必须重新执行下ANALYZE TABLE A PARTITION p202410;,速度才能达到预期的时间,执行时间会变成 0.3 s。另外,在ANALYZE TABLE 后执行计划并没有发生改变,走的索引都是一样的

请问这个是什么问题导致的?现在解决办法是加了个定时 job,每天 11:50 执行下 analyze table,帮忙看下有没有更好的解决办法。。

当表中的数据发生大量变化(如大量插入、更新或删除操作)时,原有的统计信息可能会变得过时或不准确。如果不及时更新这些统计信息,优化器在生成查询计划时可能会基于错误的信息,导致查询性能下降。
建议在写完数据后,再定时执行ANALYZE TABLE。然后再查询数据

生成的执行计划影响最大的应该是走哪个索引吧? 我观察了下 ANALYZE TABLE 前后的执行计划,最终走的都是 KEY idx_date_userid (insert_date,user_id) 这个索引,并没有走错索引的。

这个表是个通知消息表,只会基于 insert_date 每天追加数据,并不会修改历史数据。

insert_date 这个字段单独创建索引试试效果呢

分区剪裁后insert_date字段的过滤效果已经没有了,加的意义不大

嗯,分区后不会再全扫描。确实增加这个索引效果不好。

发执行计划,估计执行计划不一样

这个是A、B表都做了相同的分区,还是只有A表做了分区的

不添加索引试一下,因为id是主键,而且insert_date是分区键。

当天数据插入后,未 ANALYZE TABLE 时的执行计划

+----------------------------------------------------+-----------+---------+-----------+--------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+---------+
| id                                                 | estRows   | actRows | task      | access object                                                | execution info                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          | operator info                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            | memory   | disk    |
+----------------------------------------------------+-----------+---------+-----------+--------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+---------+
| Sort_22                                            | 100.00    | 100     | root      |                                                              | time:1.94s, loops:2, RU:42976.701654                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    | ying99_pomodel.user_plan.id                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              | 415.0 KB | 0 Bytes |
| └─Projection_24                                    | 100.00    | 100     | root      |                                                              | time:1.94s, loops:2, Concurrency:OFF                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    | ying99_pomodel.user_plan.id, ying99_pomodel.portfolio_manager_info.po_manager_id, ying99_pomodel.portfolio_manager_info.account3_id, ying99_pomodel.user_plan.type, ying99_pomodel.user_plan.invest_type, ying99_pomodel.user_plan.status, ying99_pomodel.portfolio_manager_info.phone, ying99_pomodel.portfolio_manager_info.gender, ying99_pomodel.portfolio_manager_info.payment_method_id, ying99_pomodel.user_plan.ext_unit_info, ying99_qieman.user_property.uma_id, ying99_qieman.user_manage_account.name, ying99_qieman.user_property.id, ying99_pomodel.portfolio_manager_info.real_name, ying99_dp.longwin_user_cover.advice_date, ying99_dp.longwin_user_cover.cover_advice, ying99_dp.longwin_user_cover.capital_account_id | 414.2 KB | N/A     |
|   └─HashJoin_32                                    | 100.00    | 100     | root      |                                                              | time:1.94s, loops:2, build_hash_table:{total:1.94s, fetch:1.88s, build:57.7ms}, probe:{concurrency:20, total:38.8s, max:1.94s, probe:367.6µs, fetch:38.8s}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              | left outer join, equal:[eq(ying99_qieman.user_property.id, ying99_dp.longwin_user_cover.user_property_id)]                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               | 396.0 MB | 0 Bytes |
|     ├─IndexLookUp_241(Build)                       | 0.00      | 175770  | root      | partition:p202411                                            | time:1.94s, loops:173, index_task: {total_time: 239.4ms, fetch_handle: 239.4ms, build: 14.3µs, wait: 42.9µs}, table_task: {total_time: 9.25s, num: 12, concurrency: 20}, next: {wait_index: 229.9ms, wait_table_lookup_build: 1.23ms, wait_table_lookup_resp: 1.44s}                                                                                                                                                                                                                                                                                                                                                                                                                                                                    |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          | 299.9 MB | N/A     |
|     │ ├─IndexRangeScan_238(Build)                  | 0.00      | 175770  | cop[tikv] | table:c, index:idx_propertyid(advice_date, user_property_id) | time:229.5ms, loops:173, cop_task: {num: 1, max: 229ms, proc_keys: 175770, tot_proc: 202.7ms, tot_wait: 2.27ms, copr_cache_hit_ratio: 0.00, build_task_duration: 316.9µs, max_distsql_concurrency: 1}, rpc_info:{Cop:{num_rpc:1, total_time:229ms}}, tikv_task:{time:200ms, loops:176}, scan_detail: {total_process_keys: 175770, total_process_keys_size: 9667350, total_keys: 175771, get_snapshot_time: 2.24ms, rocksdb: {delete_skipped_count: 11673, key_skipped_count: 175770, block: {cache_hit_count: 12, read_count: 23, read_byte: 338.3 KB, read_time: 252.1µs}}}                                                                                                                                                            | range:[2024-11-05,2024-11-05], keep order:false                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          | N/A      | N/A     |
|     │ └─Selection_240(Probe)                       | 0.00      | 175770  | cop[tikv] |                                                              | time:9.18s, loops:199, cop_task: {num: 43, max: 1.48s, min: 4.93ms, avg: 380.9ms, p95: 1.15s, max_proc_keys: 16168, p95_proc_keys: 13809, tot_proc: 11.9s, tot_wait: 899.3ms, copr_cache_hit_ratio: 0.00, build_task_duration: 6.74ms, max_distsql_concurrency: 5, max_extra_concurrency: 1}, rpc_info:{Cop:{num_rpc:43, total_time:16.4s}}, tikv_task:{proc max:1.17s, min:0s, avg: 283.8ms, p80:448ms, p95:768ms, iters:344, tasks:43}, scan_detail: {total_process_keys: 175770, total_process_keys_size: 372203378, total_keys: 204435, get_snapshot_time: 851.6ms, rocksdb: {delete_skipped_count: 10789, key_skipped_count: 161859, block: {cache_hit_count: 1393826, read_count: 23574, read_byte: 596.4 MB, read_time: 4.11s}}} | or(eq(cast(ying99_dp.longwin_user_cover.status, double BINARY), 1), eq(cast(ying99_dp.longwin_user_cover.status, double BINARY), 2))                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     | N/A      | N/A     |
|     │   └─TableRowIDScan_239                       | 0.00      | 175770  | cop[tikv] | table:c                                                      | tikv_task:{proc max:1.16s, min:0s, avg: 282.3ms, p80:448ms, p95:764ms, iters:344, tasks:43}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             | keep order:false                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         | N/A      | N/A     |
|     └─Limit_40(Probe)                              | 100.00    | 100     | root      |                                                              | time:440ms, loops:2                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     | offset:0, count:100                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      | N/A      | N/A     |
|       └─Selection_197                              | 100.00    | 100     | root      |                                                              | time:440ms, loops:1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     | or(eq(ying99_qieman.user_manage_account.status, "N"), isnull(ying99_qieman.user_manage_account.status))                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  | 1.40 MB  | N/A     |
|         └─IndexJoin_203                            | 100.00    | 1024    | root      |                                                              | time:439.6ms, loops:1, inner:{total:1.19s, concurrency:20, task:23, construct:11.5ms, fetch:1.18s, build:4.45ms}, probe:1.54ms                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          | left outer join, inner:TableReader_200, outer key:ying99_qieman.user_property.uma_id, inner key:ying99_qieman.user_manage_account.id, equal cond:eq(ying99_qieman.user_property.uma_id, ying99_qieman.user_manage_account.id)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            | 30.4 MB  | N/A     |
|           ├─Projection_207(Build)                  | 100.00    | 22592   | root      |                                                              | time:1.76s, loops:23, Concurrency:OFF                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   | ying99_pomodel.user_plan.id, ying99_pomodel.user_plan.type, ying99_pomodel.user_plan.invest_type, ying99_pomodel.user_plan.status, ying99_pomodel.user_plan.ext_unit_info, ying99_pomodel.portfolio_manager_info.po_manager_id, ying99_pomodel.portfolio_manager_info.account3_id, ying99_pomodel.portfolio_manager_info.payment_method_id, ying99_pomodel.portfolio_manager_info.phone, ying99_pomodel.portfolio_manager_info.gender, ying99_pomodel.portfolio_manager_info.real_name, ying99_qieman.user_property.id, ying99_qieman.user_property.uma_id                                                                                                                                                                               | 25.0 MB  | N/A     |
|           │ └─IndexJoin_217                        | 100.00    | 22592   | root      |                                                              | time:1.76s, loops:23, inner:{total:3.15s, concurrency:20, task:15, construct:118.8ms, fetch:3s, build:26.1ms}, probe:34.7ms                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             | inner join, inner:Projection_212, outer key:ying99_pomodel.user_plan.user_id, inner key:ying99_pomodel.portfolio_manager_info.po_manager_id, equal cond:eq(ying99_pomodel.user_plan.user_id, ying99_pomodel.portfolio_manager_info.po_manager_id)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        | 198.5 MB | N/A     |
|           │   ├─IndexJoin_228(Build)               | 97.85     | 170066  | root      |                                                              | time:1.47s, loops:172, inner:{total:4.34s, concurrency:20, task:15, construct:68.2ms, fetch:4.24s, build:29.1ms}, probe:188.4ms                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         | inner join, inner:TableReader_224, outer key:ying99_pomodel.user_plan.user_property_id, inner key:ying99_qieman.user_property.id, equal cond:eq(ying99_pomodel.user_plan.user_property_id, ying99_qieman.user_property.id)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               | 215.2 MB | N/A     |
|           │   │ ├─TableReader_234(Build)           | 97.66     | 175160  | root      |                                                              | time:573.1ms, loops:178, cop_task: {num: 34, max: 501.8ms, min: 7.99ms, avg: 196.6ms, p95: 404.8ms, max_proc_keys: 48774, p95_proc_keys: 41786, tot_proc: 5.57s, tot_wait: 178.7ms, copr_cache_hit_ratio: 0.00, build_task_duration: 6.6ms, max_distsql_concurrency: 34}, rpc_info:{Cop:{num_rpc:34, total_time:6.68s}}                                                                                                                                                                                                                                                                                                                                                                                                                 | data:Selection_233                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       | 174.8 MB | N/A     |
|           │   │ │ └─Selection_233                  | 97.66     | 175160  | cop[tikv] |                                                              | tikv_task:{proc max:352ms, min:4ms, avg: 148ms, p80:236ms, p95:344ms, iters:909, tasks:34}, scan_detail: {total_process_keys: 775303, total_process_keys_size: 1709295318, total_keys: 940534, get_snapshot_time: 177.8ms, rocksdb: {delete_skipped_count: 109601, key_skipped_count: 1560533, block: {cache_hit_count: 54130, read_count: 41528, read_byte: 232.2 MB, read_time: 267.6ms}}}                                                                                                                                                                                                                                                                                                                                            | eq(ying99_pomodel.user_plan.cover_advice_push_enabled, 1), eq(ying99_pomodel.user_plan.invest_type, "E"), eq(ying99_pomodel.user_plan.type, "LONG_WIN"), not(isnull(ying99_pomodel.user_plan.user_property_id))                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          | N/A      | N/A     |
|           │   │ │   └─TableRangeScan_232           | 779105.66 | 775303  | cop[tikv] | table:p                                                      | tikv_task:{proc max:352ms, min:4ms, avg: 142.5ms, p80:220ms, p95:344ms, iters:909, tasks:34}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            | range:(10000,+inf], keep order:true                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      | N/A      | N/A     |
|           │   │ └─TableReader_224(Probe)           | 89.58     | 170066  | root      |                                                              | time:4.19s, loops:195, cop_task: {num: 28, max: 768.9ms, min: 4.93ms, avg: 177.6ms, p95: 754.9ms, max_proc_keys: 25665, p95_proc_keys: 25518, tot_proc: 3.22s, tot_wait: 277.3ms, copr_cache_hit_ratio: 0.00, build_task_duration: 6.51ms, max_distsql_concurrency: 2, max_extra_concurrency: 1}, rpc_info:{Cop:{num_rpc:28, total_time:4.97s}}                                                                                                                                                                                                                                                                                                                                                                                         | data:Selection_223                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       | N/A      | N/A     |
|           │   │   └─Selection_223                  | 89.58     | 170066  | cop[tikv] |                                                              | tikv_task:{proc max:712ms, min:0s, avg: 157.9ms, p80:328ms, p95:664ms, iters:275, tasks:28}, scan_detail: {total_process_keys: 175160, total_process_keys_size: 27121004, total_keys: 193523, get_snapshot_time: 227.8ms, rocksdb: {key_skipped_count: 42968, block: {cache_hit_count: 1525074, read_count: 4064, read_byte: 40.3 MB, read_time: 37.6ms}}}                                                                                                                                                                                                                                                                                                                                                                              | eq(ying99_qieman.user_property.status, "N"), not(isnull(ying99_qieman.user_property.capital_account_id))                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 | N/A      | N/A     |
|           │   │     └─TableRangeScan_222           | 97.66     | 175160  | cop[tikv] | table:pt                                                     | tikv_task:{proc max:708ms, min:0s, avg: 157.1ms, p80:328ms, p95:664ms, iters:275, tasks:28}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             | range: decided by [ying99_pomodel.user_plan.user_property_id], keep order:false                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          | N/A      | N/A     |
|           │   └─Projection_212(Probe)              | 97.85     | 158546  | root      |                                                              | time:2.95s, loops:188, Concurrency:OFF                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  | ying99_pomodel.portfolio_manager_info.po_manager_id, ying99_pomodel.portfolio_manager_info.account3_id, ying99_pomodel.portfolio_manager_info.payment_method_id, ying99_pomodel.portfolio_manager_info.phone, ying99_pomodel.portfolio_manager_info.po_manager_status, ying99_pomodel.portfolio_manager_info.gender, ying99_pomodel.portfolio_manager_info.real_name                                                                                                                                                                                                                                                                                                                                                                     | 9.70 KB  | N/A     |
|           │     └─TableReader_211                  | 91.76     | 158546  | root      |                                                              | time:2.95s, loops:188, cop_task: {num: 57, max: 429.9ms, min: 746.4µs, avg: 112.5ms, p95: 407.7ms, max_proc_keys: 11112, p95_proc_keys: 10328, tot_proc: 4.74s, tot_wait: 307ms, copr_cache_hit_ratio: 0.00, build_task_duration: 5.04ms, max_distsql_concurrency: 11, max_extra_concurrency: 1}, rpc_info:{Cop:{num_rpc:57, total_time:6.41s}}                                                                                                                                                                                                                                                                                                                                                                                         | data:Selection_210                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       | N/A      | N/A     |
|           │       └─Selection_210                  | 91.76     | 158546  | cop[tikv] |                                                              | tikv_task:{proc max:388ms, min:0s, avg: 95.6ms, p80:160ms, p95:360ms, iters:385, tasks:57}, scan_detail: {total_process_keys: 159610, total_process_keys_size: 86541111, total_keys: 169144, get_snapshot_time: 258.6ms, rocksdb: {delete_skipped_count: 132, key_skipped_count: 28623, block: {cache_hit_count: 2237848, read_count: 79, read_byte: 1.00 MB, read_time: 2.42ms}}}                                                                                                                                                                                                                                                                                                                                                      | eq(ying99_pomodel.portfolio_manager_info.po_manager_status, "ACTIVE")                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    | N/A      | N/A     |
|           │         └─TableRangeScan_209           | 97.85     | 159610  | cop[tikv] | table:m                                                      | tikv_task:{proc max:388ms, min:0s, avg: 95.2ms, p80:160ms, p95:360ms, iters:385, tasks:57}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              | range: decided by [ying99_pomodel.user_plan.user_id], keep order:false                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   | N/A      | N/A     |
|           └─TableReader_200(Probe)                 | 100.00    | 22592   | root      |                                                              | time:1.17s, loops:46, cop_task: {num: 93, max: 125.3ms, min: 2.36ms, avg: 25.5ms, p95: 63.8ms, max_proc_keys: 569, p95_proc_keys: 521, tot_proc: 1.77s, tot_wait: 247.6ms, copr_cache_hit_ratio: 0.00, build_task_duration: 1.98ms, max_distsql_concurrency: 3, max_extra_concurrency: 1}, rpc_info:{Cop:{num_rpc:93, total_time:2.37s}}                                                                                                                                                                                                                                                                                                                                                                                                | data:TableRangeScan_199                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  | N/A      | N/A     |
|             └─TableRangeScan_199                   | 100.00    | 22592   | cop[tikv] | table:uma                                                    | tikv_task:{proc max:120ms, min:0s, avg: 19.2ms, p80:24ms, p95:60ms, iters:265, tasks:93}, scan_detail: {total_process_keys: 22592, total_process_keys_size: 5631490, total_keys: 23840, get_snapshot_time: 204.2ms, rocksdb: {key_skipped_count: 4433, block: {cache_hit_count: 263347, read_count: 6699, read_byte: 31.0 MB, read_time: 635.8ms}}}                                                                                                                                                                                                                                                                                                                                                                                     | range: decided by [ying99_qieman.user_property.uma_id], keep order:false                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 | N/A      | N/A     |
+----------------------------------------------------+-----------+---------+-----------+--------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+---------+
25 rows in set (1.95 sec)

当天数据插入后,ANALYZE TABLE 后的执行计划

+----------------------------------------------------+-----------+---------+-----------+-------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+------+
| id                                                 | estRows   | actRows | task      | access object                                         | execution info                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      | operator info                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            | memory   | disk |
+----------------------------------------------------+-----------+---------+-----------+-------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+------+
| Projection_242                                     | 3981.92   | 100     | root      |                                                       | time:356.3ms, loops:2, RU:22845.771256, Concurrency:20                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              | ying99_pomodel.user_plan.id, ying99_pomodel.portfolio_manager_info.po_manager_id, ying99_pomodel.portfolio_manager_info.account3_id, ying99_pomodel.user_plan.type, ying99_pomodel.user_plan.invest_type, ying99_pomodel.user_plan.status, ying99_pomodel.portfolio_manager_info.phone, ying99_pomodel.portfolio_manager_info.gender, ying99_pomodel.portfolio_manager_info.payment_method_id, ying99_pomodel.user_plan.ext_unit_info, ying99_qieman.user_property.uma_id, ying99_qieman.user_manage_account.name, ying99_qieman.user_property.id, ying99_pomodel.portfolio_manager_info.real_name, ying99_dp.longwin_user_cover.advice_date, ying99_dp.longwin_user_cover.cover_advice, ying99_dp.longwin_user_cover.capital_account_id | 767.5 KB | N/A  |
| └─IndexJoin_247                                    | 3981.92   | 100     | root      |                                                       | time:355.9ms, loops:2, inner:{total:48.7ms, concurrency:20, task:2, construct:66.7µs, fetch:48.6ms, build:25.5µs}, probe:285.9µs                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    | left outer join, inner:IndexLookUp_246, outer key:ying99_qieman.user_property.id, inner key:ying99_dp.longwin_user_cover.user_property_id, equal cond:eq(ying99_qieman.user_property.id, ying99_dp.longwin_user_cover.user_property_id)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  | 677.6 KB | N/A  |
|   ├─Limit_256(Build)                               | 100.00    | 100     | root      |                                                       | time:328.7ms, loops:4                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               | offset:0, count:100                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      | N/A      | N/A  |
|   │ └─Selection_197                                | 100.00    | 100     | root      |                                                       | time:328.7ms, loops:2                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               | or(eq(ying99_qieman.user_manage_account.status, "N"), isnull(ying99_qieman.user_manage_account.status))                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  | 1.40 MB  | N/A  |
|   │   └─IndexJoin_203                              | 100.00    | 1024    | root      |                                                       | time:328.3ms, loops:1, inner:{total:46.6ms, concurrency:20, task:4, construct:1.84ms, fetch:44.5ms, build:282.9µs}, probe:1.66ms                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    | left outer join, inner:TableReader_200, outer key:ying99_qieman.user_property.uma_id, inner key:ying99_qieman.user_manage_account.id, equal cond:eq(ying99_qieman.user_property.uma_id, ying99_qieman.user_manage_account.id)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            | 4.21 MB  | N/A  |
|   │     ├─Projection_207(Build)                    | 100.00    | 3136    | root      |                                                       | time:355.6ms, loops:5, Concurrency:OFF                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              | ying99_pomodel.user_plan.id, ying99_pomodel.user_plan.type, ying99_pomodel.user_plan.invest_type, ying99_pomodel.user_plan.status, ying99_pomodel.user_plan.ext_unit_info, ying99_pomodel.portfolio_manager_info.po_manager_id, ying99_pomodel.portfolio_manager_info.account3_id, ying99_pomodel.portfolio_manager_info.payment_method_id, ying99_pomodel.portfolio_manager_info.phone, ying99_pomodel.portfolio_manager_info.gender, ying99_pomodel.portfolio_manager_info.real_name, ying99_qieman.user_property.id, ying99_qieman.user_property.uma_id                                                                                                                                                                               | 4.45 MB  | N/A  |
|   │     │ └─IndexJoin_217                          | 100.00    | 4013    | root      |                                                       | time:355.6ms, loops:5, inner:{total:148.6ms, concurrency:20, task:6, construct:2.23ms, fetch:145.5ms, build:813.2µs}, probe:5.08ms                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  | inner join, inner:Projection_212, outer key:ying99_pomodel.user_plan.user_id, inner key:ying99_pomodel.portfolio_manager_info.po_manager_id, equal cond:eq(ying99_pomodel.user_plan.user_id, ying99_pomodel.portfolio_manager_info.po_manager_id)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        | 6.34 MB  | N/A  |
|   │     │   ├─IndexJoin_228(Build)                 | 97.85     | 8049    | root      |                                                       | time:355.4ms, loops:11, inner:{total:247.9ms, concurrency:20, task:7, construct:6.58ms, fetch:238.7ms, build:2.65ms}, probe:10.3ms                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  | inner join, inner:TableReader_224, outer key:ying99_pomodel.user_plan.user_property_id, inner key:ying99_qieman.user_property.id, equal cond:eq(ying99_pomodel.user_plan.user_property_id, ying99_qieman.user_property.id)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               | 16.4 MB  | N/A  |
|   │     │   │ ├─TableReader_234(Build)             | 97.66     | 13810   | root      |                                                       | time:355.2ms, loops:19, cop_task: {num: 3, max: 237.7ms, min: 7.34ms, avg: 124.8ms, p95: 237.7ms, max_proc_keys: 26880, p95_proc_keys: 26880, tot_proc: 297ms, tot_wait: 9.67ms, copr_cache_hit_ratio: 0.00, build_task_duration: 85.6µs, max_distsql_concurrency: 34}, rpc_info:{Cop:{num_rpc:3, total_time:374.3ms}}                                                                                                                                                                                                                                                                                                                                              | data:Selection_233                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       | 149.5 MB | N/A  |
|   │     │   │ │ └─Selection_233                    | 97.66     | 13810   | cop[tikv] |                                                       | tikv_task:{proc max:176ms, min:4ms, avg: 84ms, p80:176ms, p95:176ms, iters:61, tasks:3}, scan_detail: {total_process_keys: 47793, total_process_keys_size: 110149601, total_keys: 51413, get_snapshot_time: 9.56ms, rocksdb: {delete_skipped_count: 6557, key_skipped_count: 77400, block: {cache_hit_count: 4741, read_count: 1653, read_byte: 12.3 MB, read_time: 13ms}}}                                                                                                                                                                                                                                                                                         | eq(ying99_pomodel.user_plan.cover_advice_push_enabled, 1), eq(ying99_pomodel.user_plan.invest_type, "E"), eq(ying99_pomodel.user_plan.type, "LONG_WIN"), not(isnull(ying99_pomodel.user_plan.user_property_id))                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          | N/A      | N/A  |
|   │     │   │ │   └─TableRangeScan_232             | 779105.66 | 47793   | cop[tikv] | table:p                                               | tikv_task:{proc max:168ms, min:4ms, avg: 81.3ms, p80:168ms, p95:168ms, iters:61, tasks:3}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           | range:(10000,+inf], keep order:true                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      | N/A      | N/A  |
|   │     │   │ └─TableReader_224(Probe)             | 89.58     | 8049    | root      |                                                       | time:235.6ms, loops:20, cop_task: {num: 14, max: 81.3ms, min: 4.73ms, avg: 24.1ms, p95: 81.3ms, max_proc_keys: 3519, p95_proc_keys: 3519, tot_proc: 143.3ms, tot_wait: 101.8ms, copr_cache_hit_ratio: 0.07, build_task_duration: 404.8µs, max_distsql_concurrency: 2, max_extra_concurrency: 1}, rpc_info:{Cop:{num_rpc:14, total_time:337.3ms}}                                                                                                                                                                                                                                                                                                                    | data:Selection_223                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       | N/A      | N/A  |
|   │     │   │   └─Selection_223                    | 89.58     | 8049    | cop[tikv] |                                                       | tikv_task:{proc max:68ms, min:0s, avg: 12ms, p80:20ms, p95:68ms, iters:51, tasks:14}, scan_detail: {total_process_keys: 8185, total_process_keys_size: 1261779, total_keys: 9391, get_snapshot_time: 97.7ms, rocksdb: {key_skipped_count: 3244, block: {cache_hit_count: 64767, read_count: 175, read_byte: 1.54 MB, read_time: 1.77ms}}}                                                                                                                                                                                                                                                                                                                           | eq(ying99_qieman.user_property.status, "N"), not(isnull(ying99_qieman.user_property.capital_account_id))                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 | N/A      | N/A  |
|   │     │   │     └─TableRangeScan_222             | 97.66     | 8773    | cop[tikv] | table:pt                                              | tikv_task:{proc max:68ms, min:0s, avg: 11.4ms, p80:20ms, p95:68ms, iters:51, tasks:14}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              | range: decided by [ying99_pomodel.user_plan.user_property_id], keep order:false                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          | N/A      | N/A  |
|   │     │   └─Projection_212(Probe)                | 97.85     | 4013    | root      |                                                       | time:143.7ms, loops:14, Concurrency:OFF                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             | ying99_pomodel.portfolio_manager_info.po_manager_id, ying99_pomodel.portfolio_manager_info.account3_id, ying99_pomodel.portfolio_manager_info.payment_method_id, ying99_pomodel.portfolio_manager_info.phone, ying99_pomodel.portfolio_manager_info.po_manager_status, ying99_pomodel.portfolio_manager_info.gender, ying99_pomodel.portfolio_manager_info.real_name                                                                                                                                                                                                                                                                                                                                                                     | 9.70 KB  | N/A  |
|   │     │     └─TableReader_211                    | 91.76     | 4013    | root      |                                                       | time:143.6ms, loops:14, cop_task: {num: 6, max: 62.7ms, min: 5ms, avg: 23.1ms, p95: 62.7ms, max_proc_keys: 2048, p95_proc_keys: 2048, tot_proc: 95.4ms, tot_wait: 22ms, copr_cache_hit_ratio: 0.00, build_task_duration: 267.1µs, max_distsql_concurrency: 1}, rpc_info:{Cop:{num_rpc:6, total_time:138.5ms}}                                                                                                                                                                                                                                                                                                                                                       | data:Selection_210                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       | N/A      | N/A  |
|   │     │       └─Selection_210                    | 91.76     | 4013    | cop[tikv] |                                                       | tikv_task:{proc max:56ms, min:4ms, avg: 17.3ms, p80:24ms, p95:56ms, iters:27, tasks:6}, scan_detail: {total_process_keys: 4032, total_process_keys_size: 3512915, total_keys: 4726, get_snapshot_time: 20.2ms, rocksdb: {key_skipped_count: 2612, block: {cache_hit_count: 49478, read_count: 6, read_byte: 7.69 KB, read_time: 16.1µs}}}                                                                                                                                                                                                                                                                                                                           | eq(ying99_pomodel.portfolio_manager_info.po_manager_status, "ACTIVE")                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    | N/A      | N/A  |
|   │     │         └─TableRangeScan_209             | 97.85     | 4032    | cop[tikv] | table:m                                               | tikv_task:{proc max:56ms, min:4ms, avg: 17.3ms, p80:24ms, p95:56ms, iters:27, tasks:6}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              | range: decided by [ying99_pomodel.user_plan.user_id], keep order:false                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   | N/A      | N/A  |
|   │     └─TableReader_200(Probe)                   | 100.00    | 1166    | root      |                                                       | time:42.9ms, loops:6, cop_task: {num: 11, max: 7.78ms, min: 2.93ms, avg: 5.65ms, p95: 7.78ms, max_proc_keys: 32, p95_proc_keys: 32, tot_proc: 7.18ms, tot_wait: 43.6ms, copr_cache_hit_ratio: 0.36, build_task_duration: 250.1µs, max_distsql_concurrency: 3, max_extra_concurrency: 1}, rpc_info:{Cop:{num_rpc:11, total_time:61.9ms}}                                                                                                                                                                                                                                                                                                                             | data:TableRangeScan_199                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  | N/A      | N/A  |
|   │       └─TableRangeScan_199                     | 100.00    | 1166    | cop[tikv] | table:uma                                             | tikv_task:{proc max:68ms, min:0s, avg: 11.3ms, p80:20ms, p95:68ms, iters:21, tasks:11}, scan_detail: {total_process_keys: 72, total_process_keys_size: 18383, total_keys: 75, get_snapshot_time: 42.6ms, rocksdb: {key_skipped_count: 13, block: {cache_hit_count: 863, read_count: 37, read_byte: 193.1 KB, read_time: 2.34ms}}}                                                                                                                                                                                                                                                                                                                                   | range: decided by [ying99_qieman.user_property.uma_id], keep order:false                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 | N/A      | N/A  |
|   └─IndexLookUp_246(Probe)                         | 100.00    | 97      | root      | partition:p202411                                     | time:48.3ms, loops:4, index_task: {total_time: 26.1ms, fetch_handle: 26.1ms, build: 1.48µs, wait: 6.67µs}, table_task: {total_time: 21ms, num: 2, concurrency: 20}, next: {wait_index: 27.1ms, wait_table_lookup_build: 184.1µs, wait_table_lookup_resp: 20.7ms}                                                                                                                                                                                                                                                                                                                                                                                                    |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          | 181.4 KB | N/A  |
|     ├─IndexRangeScan_243(Build)                    | 100.00    | 97      | cop[tikv] | table:c, index:PRIMARY(user_property_id, advice_date) | time:26.1ms, loops:6, cop_task: {num: 2, max: 19.2ms, min: 6.7ms, avg: 13ms, p95: 19.2ms, max_proc_keys: 62, p95_proc_keys: 62, tot_proc: 17.3ms, tot_wait: 5.96ms, copr_cache_hit_ratio: 0.00, build_task_duration: 857.5µs, max_distsql_concurrency: 1}, rpc_info:{Cop:{num_rpc:2, total_time:25.9ms}}, tikv_task:{proc max:16ms, min:0s, avg: 8ms, p80:16ms, p95:16ms, iters:4, tasks:2}, scan_detail: {total_process_keys: 97, total_process_keys_size: 5141, total_keys: 197, get_snapshot_time: 5.82ms, rocksdb: {key_skipped_count: 97, block: {cache_hit_count: 984, read_count: 56, read_byte: 2.13 MB, read_time: 11.2ms}}}                               | range: decided by [eq(ying99_dp.longwin_user_cover.user_property_id, ying99_qieman.user_property.id) eq(ying99_dp.longwin_user_cover.advice_date, 2024-11-05 00:00:00.000000)], keep order:false                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         | N/A      | N/A  |
|     └─Selection_245(Probe)                         | 100.00    | 97      | cop[tikv] |                                                       | time:20.7ms, loops:4, cop_task: {num: 4, max: 8.11ms, min: 4.94ms, avg: 6.35ms, p95: 8.11ms, max_proc_keys: 50, p95_proc_keys: 50, tot_proc: 7.42ms, tot_wait: 10.1ms, copr_cache_hit_ratio: 0.00, build_task_duration: 63.7µs, max_distsql_concurrency: 1, max_extra_concurrency: 1}, rpc_info:{Cop:{num_rpc:4, total_time:25.3ms}}, tikv_task:{proc max:4ms, min:4ms, avg: 4ms, p80:4ms, p95:4ms, iters:5, tasks:4}, scan_detail: {total_process_keys: 97, total_process_keys_size: 235014, total_keys: 113, get_snapshot_time: 9.94ms, rocksdb: {key_skipped_count: 48, block: {cache_hit_count: 962, read_count: 33, read_byte: 194.5 KB, read_time: 1.53ms}}}  | or(eq(cast(ying99_dp.longwin_user_cover.status, double BINARY), 1), eq(cast(ying99_dp.longwin_user_cover.status, double BINARY), 2))                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     | N/A      | N/A  |
|       └─TableRowIDScan_244                         | 100.00    | 97      | cop[tikv] | table:c                                               | tikv_task:{proc max:4ms, min:4ms, avg: 4ms, p80:4ms, p95:4ms, iters:5, tasks:4}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     | keep order:false                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         | N/A      | N/A  |
+----------------------------------------------------+-----------+---------+-----------+-------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+------+
24 rows in set (0.36 sec)

今天又试了下,确实执行计划不一样,未 analyze 之前使用的是 HashJoin, analyze之后使用的是 indexJoin。

PS: 原始的 SQL 里 B 其实是个带 limit 的子查询

还是统计信息不准导致的吧,未收集之前,预计有175770 行,实际收集之后,预计只有97 行了,导致关联方式都改变了,如果不想每次都收集统计信息,就通过hint指定关联方式吧

是统计信息不准导致,不过这里讲的有些不准,其实是:

  • 未收集之前,因为没识别到当天插入的新数据,所以预估为0,优化器用了 HashJoin,但是实际扫描了175770 行
  • 收集之后,预估为 100行,实际是 97行,所以使用了 IndexJoin 。


比较坑的是,因为子查询有四个表的 join,还没办法绑定执行计划。。

可以锁定统计信息,7.4 GA
https://docs.pingcap.com/zh/tidb/v7.5/statistics#锁定统计信息

哦,对,后面是实际行数,前面因为数据是新插入的,老的统计信息没有,所以预估为了0行。

这种分布式数据库,不分区是不是反而更好呢?

要做冷热分离,现在 TiDB 只能通过 Placement Policy + 分区表来实现。