[Bug]: select limit1 query need to be scan 1 millions row and cost more than 1 senconds

[aressu1985]

Is there an existing issue for the same bug?

• I have checked the existing issues.

Branch Name

1.1-dev

Commit ID

55eb0f9

Other Environment Information

- Hardware parameters:
3*CN: 16C 64G
1*DN: 16C 64G
3*LOG: 4C 16G
- OS type:
- Others:

Actual Behavior

select * from statement_info limit 1 , cost more than 1 sencond:

mysql> select * from statement_info limit 1;
+--------------------------------------+--------------------------------------+--------------------------------------+-----------+-------+----------------+----------+-----------+---------------+-----------------------+--------------------------------------+-----------+----------------------------+----------------------------+----------+---------+----------+-------+--------------------------------------------------------------------------------------------------------------------+-----------+------------+---------------------------+----------------+------------+---------+-----------------+------------+--------------+
| statement_id | transaction_id | session_id | account | user | host | database | statement | statement_tag | statement_fingerprint | node_uuid | node_type | request_at | response_at | duration | status | err_code | error | exec_plan | rows_read | bytes_scan | stats | statement_type | query_type | role_id | sql_source_type | aggr_count | result_count |
+--------------------------------------+--------------------------------------+--------------------------------------+-----------+-------+----------------+----------+-----------+---------------+-----------------------+--------------------------------------+-----------+----------------------------+----------------------------+----------+---------+----------+-------+--------------------------------------------------------------------------------------------------------------------+-----------+------------+---------------------------+----------------+------------+---------+-----------------+------------+--------------+
| 018ecab4-2555-7bde-8a0d-00334a53c4a5 | 018ecab4-17b1-7f23-ad35-0a25001f9ed0 | 018ec911-a405-70db-a259-ce4dc831b075 | tpcc_test | admin | 10.10.3.0:8775 | tpcc_10 | commit | | | 62313066-3865-3331-6235-626563653038 | CN | 2024-04-11 01:09:47.477001 | 2024-04-11 01:09:47.571643 | 94642146 | Success | 0 | | {"code":200,"message":"NO ExecPlan Serialize function","steps":null,"uuid":"018ecab4-2555-7bde-8a0d-00334a53c4a5"} | 0 | 0 | [4,0,0,0,0,12,0,2,0.0094] | Commit | TCL | 2 | external_sql | 0 | 0 |
+--------------------------------------+--------------------------------------+--------------------------------------+-----------+-------+----------------+----------+-----------+---------------+-----------------------+--------------------------------------+-----------+----------------------------+----------------------------+----------+---------+----------+-------+--------------------------------------------------------------------------------------------------------------------+-----------+------------+---------------------------+----------------+------------+---------+-----------------+------------+--------------+
1 row in set (1.27 sec)

explain analyze :

mysql> explain analyze select * from statement_info limit 1;
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| QUERY PLAN |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Project |
| Analyze: timeConsumed=0ms waitTime=892ms inputRows=1 outputRows=1 InputSize=362548bytes OutputSize=362548bytes MemorySize=362548bytes |
| -> Table Scan on system.statement_info |
| Analyze: timeConsumed=96900ms scan_time=[total=96291ms,min=64ms,max=830ms,dop=162] filter_time=[total=585ms,min=0ms,max=90ms,dop=162] waitTime=2756ms inputRows=1265819 outputRows=1 InputSize=2208mb OutputSize=362548bytes MemorySize=3894mb |
| Filter Cond: (statement_info.account = 'tpcc_test') |
| Block Filter Cond: (statement_info.account = 'tpcc_test') |
| Limit: 1 |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
7 rows in set (0.91 sec)

and the count of table:

mysql> select count() from statement_info;
+----------+
| count() |
+----------+
| 642617 |
+----------+
1 row in set (0.15 sec)

Expected Behavior

No response

Steps to Reproduce

as described in Actual Behavior

Additional information

No response

[badboynt1]

已经在多cn环境下验证成功修复

这个问题的关键是，多cn，然后select语句带filter。

[badboynt1]

mysql> select count() from t1;
+----------+
| count() |
+----------+
| 10000000 |
+----------+
1 row in set (0.00 sec)

mysql> show backend servers;
+--------------------------------------+-----------------+------------+--------+
| UUID | Address | Work State | Labels |
+--------------------------------------+-----------------+------------+--------+
| 36666164-6161-3535-3832-333565363232 | 10.158.1.3:6001 | Working | |
| 33313162-3365-6537-3461-366665376131 | 10.158.1.2:6001 | Working | |
+--------------------------------------+-----------------+------------+--------+
2 rows in set (0.00 sec)

mysql> explain analyze select * from t1 where c2!=1 limit 1;
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| QUERY PLAN |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Project |
| Analyze: timeConsumed=0ms waitTime=0ms inputRows=1 outputRows=1 InputSize=12bytes OutputSize=12bytes MemorySize=12bytes |
| -> Table Scan on mo.t1 |
| Analyze: timeConsumed=2ms scan_time=[total=1ms,min=0ms,max=0ms,dop=8] filter_time=[total=0ms,min=0ms,max=0ms,dop=8] waitTime=1ms inputRows=65536 outputRows=1 InputSize=786432bytes OutputSize=12bytes MemorySize=1mb |
| Filter Cond: (t1.c2 <> 1) |
| Limit: 1 |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
6 rows in set (0.00 sec)

[aressu1985]

mysql> explain analyze select * from statement_info limit 1;
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| QUERY PLAN |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Project |
| Analyze: timeConsumed=0ms waitTime=255ms inputRows=1 outputRows=1 InputSize=12mb OutputSize=12mb MemorySize=12mb |
| -> Table Scan on system.statement_info |
| Analyze: timeConsumed=490ms scan_time=[total=489ms,min=8ms,max=148ms,dop=16] waitTime=508ms inputRows=54641 outputRows=1 InputSize=176mb OutputSize=12mb MemorySize=465mb |
| Limit: 1 |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
5 rows in set (0.27 sec)

目前已经解决 limit 1不会并行到多CN进行处理，但是还是存在两个问题：

1. 时间依然很慢，每次执行还是需要扫描几万行数据
2. 对于私有化方式部署，每个CN获取的cpu核数是物理机的总核数，会导致实际执行并发数远大于CN能够使用的CPU核数

实际每个CN只有16个core，但是宿主机有128个core，如果此种情况，会导致改SQL执行巨慢无比

继续在主干版本解决

[aressu1985]

testing

[aressu1985]

fixed: 275af9c