Open YangKeao opened 4 months ago
mysql> explain format='cost_trace' SELECT `c_balance`, `c_first`, `c_middle`, `c_id` FROM `customer` use index(idx_customer) WHERE `c_w_id` = 712 AND `c_d_id` = 6 AND `c_last` = "CALLYATIONPRES" ORDER BY `c_first`;
+----------------------------------+---------+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+---------------------------------------------------------------------+--------------------------------------------------------------------------------------------+
| id | estRows | estCost | costFormula | task | access object | operator info |
+----------------------------------+---------+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+---------------------------------------------------------------------+--------------------------------------------------------------------------------------------+
| Projection_11 | 242.46 | 480039.18 | ((((net(242.4574839561879*rowsize(75.465)*tidb_kv_net_factor(3.96))) + (scan(242.4574839561879*logrowsize(114.59)*tikv_scan_factor(40.7))))/15.00) + (((((net(242.4574839561879*rowsize(108.88499999999999)*tidb_kv_net_factor(3.96))) + (scan(242.4574839561879*logrowsize(847.9200000000001)*tikv_scan_factor(40.7))))/15.00) + ((double-read-cpu(242.4574839561879*tidb_cpu_factor(49.9))) + (doubleRead(tasks(0.3879319743299006)*tidb_request_factor(6e+06)))))/5.00)) + ((cpu(242.4574839561879*filters(0.04)*tidb_cpu_factor(49.9)))/5.00) | root | | tpcc.customer.c_balance, tpcc.customer.c_first, tpcc.customer.c_middle, tpcc.customer.c_id |
| └─IndexLookUp_14 | 242.46 | 479942.39 | (((net(242.4574839561879*rowsize(75.465)*tidb_kv_net_factor(3.96))) + (scan(242.4574839561879*logrowsize(114.59)*tikv_scan_factor(40.7))))/15.00) + (((((net(242.4574839561879*rowsize(108.88499999999999)*tidb_kv_net_factor(3.96))) + (scan(242.4574839561879*logrowsize(847.9200000000001)*tikv_scan_factor(40.7))))/15.00) + ((double-read-cpu(242.4574839561879*tidb_cpu_factor(49.9))) + (doubleRead(tasks(0.3879319743299006)*tidb_request_factor(6e+06)))))/5.00) | root | | |
| ├─IndexRangeScan_12(Build) | 242.46 | 67500.58 | scan(242.4574839561879*logrowsize(114.59)*tikv_scan_factor(40.7)) | cop[tikv] | table:customer, index:idx_customer(c_w_id, c_d_id, c_last, c_first) | range:[712 6 "CALLYATIONPRES",712 6 "CALLYATIONPRES"], keep order:true |
| └─TableRowIDScan_13(Probe) | 242.46 | 95993.97 | scan(242.4574839561879*logrowsize(847.9200000000001)*tikv_scan_factor(40.7)) | cop[tikv] | table:customer | keep order:false |
+----------------------------------+---------+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+---------------------------------------------------------------------+--------------------------------------------------------------------------------------------+
mysql> explain format='cost_trace' SELECT `c_balance`, `c_first`, `c_middle`, `c_id` FROM `customer` WHERE `c_w_id` = 712 AND `c_d_id` = 6 AND `c_last` = "CALLYATIONPRES" ORDER BY `c_first`;
+------------------------------+---------+------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+----------------+--------------------------------------------------------------------------------------------+
| id | estRows | estCost | costFormula | task | access object | operator info |
+------------------------------+---------+------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+----------------+--------------------------------------------------------------------------------------------+
| Sort_5 | 242.46 | 389417.47 | (((((cpu(9465.141505828295*filters(1)*tikv_cpu_factor(49.9))) + (scan(9465.141505828295*logrowsize(847.94)*tikv_scan_factor(40.7)))) + (net(242.4574839561879*rowsize(140.01)*tidb_kv_net_factor(3.96))))/15.00) + ((cpu(242.4574839561879*filters(0.04)*tidb_cpu_factor(49.9)))/5.00)) + ((exprCPU(242.4574839561879*0*tidb_cpu_factor(49.9))) + (orderCPU(242.4574839561879*log(242.4574839561879)*tidb_cpu_factor(49.9)))) + (sortMem(242.4574839561879*66*tidb_mem_factor(0.2))) | root | | tpcc.customer.c_first |
| └─Projection_7 | 242.46 | 290376.68 | ((((cpu(9465.141505828295*filters(1)*tikv_cpu_factor(49.9))) + (scan(9465.141505828295*logrowsize(847.94)*tikv_scan_factor(40.7)))) + (net(242.4574839561879*rowsize(140.01)*tidb_kv_net_factor(3.96))))/15.00) + ((cpu(242.4574839561879*filters(0.04)*tidb_cpu_factor(49.9)))/5.00) | root | | tpcc.customer.c_balance, tpcc.customer.c_first, tpcc.customer.c_middle, tpcc.customer.c_id |
| └─TableReader_10 | 242.46 | 290279.89 | (((cpu(9465.141505828295*filters(1)*tikv_cpu_factor(49.9))) + (scan(9465.141505828295*logrowsize(847.94)*tikv_scan_factor(40.7)))) + (net(242.4574839561879*rowsize(140.01)*tidb_kv_net_factor(3.96))))/15.00 | root | | data:Selection_9 |
| └─Selection_9 | 242.46 | 4219770.28 | (cpu(9465.141505828295*filters(1)*tikv_cpu_factor(49.9))) + (scan(9465.141505828295*logrowsize(847.94)*tikv_scan_factor(40.7))) | cop[tikv] | | eq(tpcc.customer.c_last, "CALLYATIONPRES") |
| └─TableRangeScan_8 | 9465.14 | 3747459.72 | scan(9465.141505828295*logrowsize(847.94)*tikv_scan_factor(40.7)) | cop[tikv] | table:customer | range:[712 6,712 6], keep order:false |
+------------------------------+---------+------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+----------------+--------------------------------------------------------------------------------------------+
5 rows in set (0.00 sec)
mysql> explain format='cost_trace' SELECT `o_id`, `o_carrier_id`, `o_entry_d` FROM `orders` WHERE `o_w_id` = 507 AND `o_d_id` = 6 AND `o_c_id` = 819 ORDER BY `o_id` DESC LIMIT 1;
+---------------------------------+---------+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+---------------+-------------------------------------------------------------------+
| id | estRows | estCost | costFormula | task | access object | operator info |
+---------------------------------+---------+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+---------------+-------------------------------------------------------------------+
| Projection_7 | 1.00 | 1176.40 | ((((((cpu(37.007478408580226*filters(1)*tikv_cpu_factor(49.9))) + (scan(37.007478408580226*logrowsize(120)*tikv_desc_scan_factor(61.05))))) + (net(1*rowsize(48)*tidb_kv_net_factor(3.96))))/15.00)) + ((cpu(1*filters(0.03)*tidb_cpu_factor(49.9)))/5.00) | root | | tpcc.orders.o_id, tpcc.orders.o_carrier_id, tpcc.orders.o_entry_d |
| └─Limit_11 | 1.00 | 1176.10 | (((((cpu(37.007478408580226*filters(1)*tikv_cpu_factor(49.9))) + (scan(37.007478408580226*logrowsize(120)*tikv_desc_scan_factor(61.05))))) + (net(1*rowsize(48)*tidb_kv_net_factor(3.96))))/15.00) | root | | offset:0, count:1 |
| └─TableReader_24 | 1.00 | 1176.10 | ((((cpu(37.007478408580226*filters(1)*tikv_cpu_factor(49.9))) + (scan(37.007478408580226*logrowsize(120)*tikv_desc_scan_factor(61.05))))) + (net(1*rowsize(48)*tidb_kv_net_factor(3.96))))/15.00 | root | | data:Limit_23 |
| └─Limit_23 | 1.00 | 17451.46 | ((cpu(37.007478408580226*filters(1)*tikv_cpu_factor(49.9))) + (scan(37.007478408580226*logrowsize(120)*tikv_desc_scan_factor(61.05)))) | cop[tikv] | | offset:0, count:1 |
| └─Selection_22 | 1.00 | 17451.46 | (cpu(37.007478408580226*filters(1)*tikv_cpu_factor(49.9))) + (scan(37.007478408580226*logrowsize(120)*tikv_desc_scan_factor(61.05))) | cop[tikv] | | eq(tpcc.orders.o_c_id, 819) |
| └─TableRangeScan_21 | 37.01 | 15604.78 | scan(37.007478408580226*logrowsize(120)*tikv_desc_scan_factor(61.05)) | cop[tikv] | table:orders | range:[507 6,507 6], keep order:true, desc |
+---------------------------------+---------+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+---------------+-------------------------------------------------------------------+
6 rows in set (0.00 sec)
mysql> explain format='cost_trace' SELECT `o_id`, `o_carrier_id`, `o_entry_d` FROM `orders` use index(idx_order) WHERE `o_w_id` = 507 AND `o_d_id` = 6 AND `o_c_id` = 819 ORDER BY `o_id` DESC LIMIT 1;
+----------------------------------+---------+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+-------------------------------------------------------------+-------------------------------------------------------------------+
| id | estRows | estCost | costFormula | task | access object | operator info |
+----------------------------------+---------+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+-------------------------------------------------------------+-------------------------------------------------------------------+
| Projection_7 | 1.00 | 1187.42 | (((((net(0.9999999999999999*rowsize(56.875)*tidb_kv_net_factor(3.96))) + ((scan(0.9999999999999999*logrowsize(112)*tikv_desc_scan_factor(61.05)))))/15.00) + (((((net(0.9999999999999999*rowsize(48.75)*tidb_kv_net_factor(3.96))) + (scan(1*logrowsize(64)*tikv_scan_factor(40.7))))/15.00) + ((double-read-cpu(0.9999999999999999*tidb_cpu_factor(49.9))) + (doubleRead(tasks(0.0015999999999999999)*tidb_request_factor(6e+06)))))/5.00))*0.60) + ((cpu(1*filters(0.03)*tidb_cpu_factor(49.9)))/5.00) | root | | tpcc.orders.o_id, tpcc.orders.o_carrier_id, tpcc.orders.o_entry_d |
| └─IndexLookUp_20 | 1.00 | 1187.12 | ((((net(0.9999999999999999*rowsize(56.875)*tidb_kv_net_factor(3.96))) + ((scan(0.9999999999999999*logrowsize(112)*tikv_desc_scan_factor(61.05)))))/15.00) + (((((net(0.9999999999999999*rowsize(48.75)*tidb_kv_net_factor(3.96))) + (scan(1*logrowsize(64)*tikv_scan_factor(40.7))))/15.00) + ((double-read-cpu(0.9999999999999999*tidb_cpu_factor(49.9))) + (doubleRead(tasks(0.0015999999999999999)*tidb_request_factor(6e+06)))))/5.00))*0.60 | root | | limit embedded(offset:0, count:1) |
| ├─Limit_19(Build) | 1.00 | 415.59 | (scan(0.9999999999999999*logrowsize(112)*tikv_desc_scan_factor(61.05))) | cop[tikv] | | offset:0, count:1 |
| │ └─IndexRangeScan_17 | 1.00 | 415.59 | scan(0.9999999999999999*logrowsize(112)*tikv_desc_scan_factor(61.05)) | cop[tikv] | table:orders, index:idx_order(o_w_id, o_d_id, o_c_id, o_id) | range:[507 6 819,507 6 819], keep order:true, desc |
| └─TableRowIDScan_18(Probe) | 1.00 | 244.20 | scan(1*logrowsize(64)*tikv_scan_factor(40.7)) | cop[tikv] | table:orders | keep order:false |
+----------------------------------+---------+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+-------------------------------------------------------------+-------------------------------------------------------------------+
5 rows in set (0.00 sec)
It may be a regression case of our new cost model. If we set @@tidb_cost_model_version=1
, the plan seems to be stable.
I copied the result from the author.
The influence on the throughput seems not significant. But the latency will.
Now that the latency is not considered as regression. We move down its severity first.
The plan of two queries in TPC-C
ORDER_STATUS
transaction is not stable:First Query
Wrong plan:
Good plan:
For this query, it's more likely to pick slower plan:
Second Query
Wrong plan:
Good plan:
For this query, it's more likely to pick faster plan, but it's also possible to pick slower plan some time:
I tried to use the following bindings as a workaround:
If you need any help to reproduce this issue, feel free to touch me.