非聚簇表count(1)和count(id)执行计划不一致

【TiDB 使用环境】生产环境
【TiDB 版本】7.5.1
【操作系统】centos7.9
【部署方式】云上部署（什么云）/机器部署（什么机器配置、什么硬盘）

建表
CREATE TABLE test_nocluster_info (
id bigint(20) NOT NULL AUTO_INCREMENT COMMENT ‘label:主键ID’,
info_id bigint(20) NOT NULL COMMENT ‘aaa’,
KEY test (info_id),
PRIMARY KEY (id) /*T![clustered_index] NONCLUSTERED */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT=‘test’;

写入
insert into test_nocluster_info values(1,1),(2,2),(3,3);

MySQL [test]> explain analyze select count(1) from test_nocluster_info where info_id=3;
+---------------------------+---------+---------+-----------+------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------+-----------+------+
| id                        | estRows | actRows | task      | access object                                  | execution info                                                                                                                                                                                                                      | operator info                               | memory    | disk |
+---------------------------+---------+---------+-----------+------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------+-----------+------+
| StreamAgg_10              | 1.00    | 1       | root      |                                                | time:497.4µs, loops:2, RU:0.504593                                                                                                                                                                                                  | funcs:count(1)->Column#4                    | 388 Bytes | N/A  |
| └─IndexReader_15          | 0.00    | 1       | root      |                                                | time:490.5µs, loops:2, cop_task: {num: 1, max: 439.4µs, proc_keys: 1, tot_proc: 86.7µs, tot_wait: 38.5µs, rpc_num: 1, rpc_time: 424.3µs, copr_cache_hit_ratio: 0.00, build_task_duration: 15.7µs, max_distsql_concurrency: 1}       | index:IndexRangeScan_14                     | 258 Bytes | N/A  |
|   └─IndexRangeScan_14     | 0.00    | 1       | cop[tikv] | table:test_nocluster_info, index:test(info_id) | tikv_task:{time:0s, loops:1}, scan_detail: {total_process_keys: 1, total_process_keys_size: 46, total_keys: 2, get_snapshot_time: 16.3µs, rocksdb: {key_skipped_count: 1, block: {cache_hit_count: 7}}}                             | range:[3,3], keep order:false, stats:pseudo | N/A       | N/A  |
+---------------------------+---------+---------+-----------+------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------+-----------+------+
3 rows in set (0.00 sec)


MySQL [test]> explain analyze select count(id) from test_nocluster_info where info_id=3;
+----------------------------------+---------+---------+-----------+------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------+-----------+------+
| id                               | estRows | actRows | task      | access object                                  | execution info                                                                                                                                                                                                                                                                                                                                                                                                                                    | operator info                                                       | memory    | disk |
+----------------------------------+---------+---------+-----------+------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------+-----------+------+
| StreamAgg_10                     | 1.00    | 1       | root      |                                                | time:1.14ms, loops:2, RU:1.010787                                                                                                                                                                                                                                                                                                                                                                                                                 | funcs:count(test.test_nocluster_info.id)->Column#4                  | 768 Bytes | N/A  |
| └─IndexLookUp_24                 | 0.00    | 1       | root      |                                                | time:1.14ms, loops:2, index_task: {total_time: 489.1µs, fetch_handle: 486.6µs, build: 866ns, wait: 1.56µs}, table_task: {total_time: 542.5µs, num: 1, concurrency: 5}, next: {wait_index: 581.5µs, wait_table_lookup_build: 50.8µs, wait_table_lookup_resp: 487.5µs}                                                                                                                                                                              |                                                                     | 8.76 KB   | N/A  |
|   ├─IndexRangeScan_22(Build)     | 0.00    | 1       | cop[tikv] | table:test_nocluster_info, index:test(info_id) | time:474.5µs, loops:3, cop_task: {num: 1, max: 423.2µs, proc_keys: 1, tot_proc: 94.5µs, tot_wait: 49.7µs, rpc_num: 1, rpc_time: 404.1µs, copr_cache_hit_ratio: 0.00, build_task_duration: 18µs, max_distsql_concurrency: 1}, tikv_task:{time:0s, loops:1}, scan_detail: {total_process_keys: 1, total_process_keys_size: 46, total_keys: 2, get_snapshot_time: 26.2µs, rocksdb: {key_skipped_count: 1, block: {cache_hit_count: 7}}}              | range:[3,3], keep order:false, stats:partial[info_id:unInitialized] | N/A       | N/A  |
|   └─TableRowIDScan_23(Probe)     | 0.00    | 1       | cop[tikv] | table:test_nocluster_info                      | time:466.8µs, loops:2, cop_task: {num: 1, max: 423.5µs, proc_keys: 1, tot_proc: 83.9µs, tot_wait: 47.1µs, rpc_num: 1, rpc_time: 407.4µs, copr_cache_hit_ratio: 0.00, build_task_duration: 24.1µs, max_distsql_concurrency: 1, max_extra_concurrency: 1}, tikv_task:{time:0s, loops:1}, scan_detail: {total_process_keys: 1, total_process_keys_size: 41, total_keys: 1, get_snapshot_time: 25.5µs, rocksdb: {block: {cache_hit_count: 7}}}        | keep order:false, stats:partial[info_id:unInitialized]              | N/A       | N/A  |
+----------------------------------+---------+---------+-----------+------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------+-----------+------+

可以看到select count(1)的执行计划要明显优于select count(id),不太清楚是否算bug

多少数据测试的，插入几万条看看
先分析下analyze
数据量如果太少，比如几十条几百条，执行计划怎么走就毫无意义了

count(info_id)试试呢，id既不是索引也不是主键。 正常应该count(1)和count(*)没啥区别。

原表是几亿行的，我只是做了最小复现步骤而已

建表那里粘贴错了，我重新编辑下，这回正常了

感觉是统计信息的问题。

看起来是非聚簇表。id 相当于索引列,执行计划多了一次回表。感觉这个回表没必要啊

确实感觉这个回表没有必要，但是7.5.1和7.5.4测试都是一个结果，就感觉非常诡异，正常应该是走count(1)的这种执行计划的

应该不是统计信息的问题，真正的原始表analyze很多遍了

优化器的事情怎么能叫 bug 呢 ，只能说不是最优执行计划
首先：count(1) 和 count(id) 含义不一样，count(id) 会忽略 id 为 null 的列
当 id 列定义中带 not null 时，count(1) 和 count(id)返回结果是一样的，但是 tidb 逻辑优化时好像没有意识到这点
所以 count(id) 的时候不用回表，像第一个执行计划就好了

哈哈 我感觉也没有必要。

不过可能和语义有关系，所以这个回表不能消掉。感觉可以让优化组老师看看。

我测试了下，确实回表了，这回表对于非空字段来说应该没有必要

嗯呢，有可能，我暂时先强制绑定下吧，等优化组老师看完看是否有更优的解决办法

是啊，不回表就要节约好多时间

请教下，怎么绑定？

是因为回表了的原因吧？注意字段空值问题

主键不可能有空啊，明显是执行计划的问题

https://docs.pingcap.com/zh/tidb/v7.5/sql-statement-create-binding/#语法图

不是binding语法，是这个hint怎么写呢？