版本：v6.1.0
表结构如下：
健康度如下：
诡异的执行计划：
加 analyze 也一样，补在贴图。
create table COPY表 like 当前表;
insert into COPY 表 select * from 当前表。是可以正常走预期的索引的。
想知道为什么会不选择走where 条件都包含的联合索引。
为什么不能强制走这个索引。 mysql 客户端已经加-c.不存在忽略注释问题。
ref_id 的类型是bigint 可以尝试把传参 改成 数据类型
不是这个原因，我考虑过。类型不匹配的时候，字符会转换为数字。这里不会导致索引失效。
explain analyze 真实执行一下，看下真实的执行计划。
一样的
我主要是想看一下 actrows 。
mysql> explain ANALYZE SELECT COUNT(*) FROM pack_install WHERE `ref_type` = 'scene' AND `ref_id` = '214' AND `deleted_on` = '0000-00-00 00:00:00';
+----------------------------------+-----------+---------+-----------+---------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------+---------+------+
| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |
+----------------------------------+-----------+---------+-----------+---------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------+---------+------+
| HashAgg_14 | 1.00 | 1 | root | | time:297.3ms, loops:2, partial_worker:{wall_time:297.311558ms, concurrency:5, task_num:1, tot_wait:1.486359099s, tot_exec:15.74µs, tot_time:1.486385559s, max:297.289698ms, p95:297.289698ms}, final_worker:{wall_time:297.330685ms, concurrency:5, task_num:1, tot_wait:1.486469758s, tot_exec:26.15µs, tot_time:1.486502449s, max:297.309835ms, p95:297.309835ms} | funcs:count(Column#15)->Column#12 | 8.07 KB | N/A |
| └─IndexLookUp_15 | 1.00 | 35 | root | | time:297.3ms, loops:2, index_task: {total_time: 223.3ms, fetch_handle: 44.6ms, build: 42.7µs, wait: 178.6ms}, table_task: {total_time: 1.29s, num: 31, concurrency: 5} | | 8.13 MB | N/A |
| ├─IndexRangeScan_11(Build) | 541617.72 | 555638 | cop[tikv] | table:pack_install, index:product_id_user_id(ref_id, user_id) | time:4.21ms, loops:545, cop_task: {num: 1, max: 2.17ms, proc_keys: 0, rpc_num: 1, rpc_time: 2.16ms, copr_cache_hit_ratio: 1.00}, tikv_task:{time:276ms, loops:547} | range:[214,214], keep order:false | N/A | N/A |
| └─HashAgg_7(Probe) | 1.00 | 35 | cop[tikv] | | time:1.12s, loops:62, cop_task: {num: 35, max: 69.7ms, min: 475µs, avg: 32.2ms, p95: 64.4ms, max_proc_keys: 20480, p95_proc_keys: 20480, tot_proc: 1.07s, rpc_num: 35, rpc_time: 1.13s, copr_cache_hit_ratio: 0.26}, tikv_task:{proc max:65ms, min:1ms, p80:51ms, p95:60ms, iters:575, tasks:35}, scan_detail: {total_process_keys: 423411, total_process_keys_size: 45307132, total_keys: 865198, rocksdb: {delete_skipped_count: 0, key_skipped_count: 842077, block: {cache_hit_count: 193335, read_count: 0, read_byte: 0 Bytes}}} | funcs:count(1)->Column#15 | N/A | N/A |
| └─Selection_13 | 541347.49 | 555340 | cop[tikv] | | tikv_task:{proc max:64ms, min:1ms, p80:51ms, p95:60ms, iters:575, tasks:35} | eq(huoban_v4.pack_install.deleted_on, 0000-00-00 00:00:00.000000), eq(huoban_v4.pack_install.ref_type, "scene") | N/A | N/A |
| └─TableRowIDScan_12 | 541617.72 | 555638 | cop[tikv] | table:pack_install | tikv_task:{proc max:61ms, min:1ms, p80:46ms, p95:58ms, iters:575, tasks:35} | keep order:false | N/A | N/A |
+----------------------------------+-----------+---------+-----------+---------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------+---------+------+
6 rows in set (0.30 sec)
mysql> explain ANALYZE SELECT COUNT(*) FROM DBA_recover.pack_install_whb WHERE `ref_type` = 'scene' AND `ref_id` = '214' AND `deleted_on` = '0000-00-00 00:00:00';
+-----------------------------+-----------+---------+-----------+--------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------+-----------+------+
| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |
+-----------------------------+-----------+---------+-----------+--------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------+-----------+------+
| StreamAgg_17 | 1.00 | 1 | root | | time:2.03ms, loops:2 | funcs:count(Column#14)->Column#12 | 388 Bytes | N/A |
| └─IndexReader_18 | 1.00 | 3 | root | | time:2.02ms, loops:2, cop_task: {num: 3, max: 1.93ms, min: 1.25ms, avg: 1.66ms, p95: 1.93ms, rpc_num: 3, rpc_time: 4.93ms, copr_cache_hit_ratio: 1.00} | index:StreamAgg_9 | 321 Bytes | N/A |
| └─StreamAgg_9 | 1.00 | 3 | cop[tikv] | | tikv_task:{proc max:290ms, min:12ms, p80:290ms, p95:290ms, iters:544, tasks:3} | funcs:count(1)->Column#14 | N/A | N/A |
| └─IndexRangeScan_16 | 542650.00 | 555340 | cop[tikv] | table:pack_install_whb, index:ref_id_ref_type_deleted_on_idx(ref_id, ref_type, deleted_on) | tikv_task:{proc max:290ms, min:12ms, p80:290ms, p95:290ms, iters:544, tasks:3} | range:[214 "scene" 0000-00-00 00:00:00.000000,214 "scene" 0000-00-00 00:00:00.000000], keep order:false | N/A | N/A |
+-----------------------------+-----------+---------+-----------+--------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------+-----------+------+
pack_install_whb是我用 create table like 原表；insert into 新表 select * from 原表。得来的。
统计信息都参数有改动过吗 ？ 比如 tidb_analyze_version
全是默认值。
可以看下两张表的 SHOW STATS_HEALTHY对比吗？
对了 顺便看一下你有没有创建SPM，导致执行计划被绑定了 不生效
1 个赞
gc设置的多长时间呢，像是原表的老版本的数据太多了，索引关联度比较低，就走关联度高的索引了，可以清理下gc再试一下
确实是绑定了执行计划，因为是我接手维护的，没有想到这一点。
绑定了执行计划吧
1 个赞
是的。
哈哈哈解决就好
此话题已在最后回复的 60 天后被自动关闭。不再允许新回复。