【TiDB 使用环境】生产环境
【TiDB 版本】v7.5.6
【遇到的问题：问题现象及影响】
表有215亿，该SQL存在合理的索引，但是SQL更倾向于走全表扫描。
走全表扫描执行60秒被kill，强制绑定索引仅需0.4秒。
表信息
索引信息
KEY `idx_seller_user_id_billing_req_no` (`seller_user_id`,`billing_req_no`),
KEY `idx_biz_no` (`biz_no`),
KEY `idx_ori_biz_no` (`ori_biz_no`),
KEY `idx_pay_log_no` (`pay_log_no`),
KEY `idx_pay_detail_id` (`pay_detail_id`),
KEY `idx_bill_head_id_fee_type_pay_status` (`bill_head_id`,`fee_type`,`pay_status`),
KEY `idx_pay_status` (`pay_status`,`seller_user_id`),
KEY `idx_bill_head_seller_user_id` (`bill_head_id`,`seller_user_id`)
SQL语句
SELECT
id,
seller_user_id,
buss_partner_id,
company_code,
billing_sn_no,
billing_req_no,
pay_log_no,
pay_detail_id,
bill_head_id,
biz_no,
biz_type,
biz_sub_type,
ori_biz_no,
biz_identity,
src_app_code,
calc_bill_type,
scene_type,
scene_code,
fee_type,
calc_direction,
fee_amount,
amount,
pay_status,
bill_time,
pay_time,
target_pay_way,
src_pay_way,
pay_type,
pay_channel,
merchant_type,
settle_period,
payer_type,
payer_id,
payer_acc_type,
payee_type,
payee_acc_type,
payee_id,
time_node,
aggr_dimension,
sum_dimension,
extra_info,
creator,
gmt_created,
modifier,
gmt_modified,
is_del,
app_code,
currency,
transaction_amount,
transaction_currency,
stmt_exchange_rate,
sub_pay_log_no,
biz_area,
merchant_id,
migrate_status,
ori_billing_sn_no,
fund_flow_template_code
FROM
fin_pay_apportion
WHERE
bill_head_id = 1207025387984171
AND fee_type = 'G0002'
AND seller_user_id = 113828458
AND target_pay_way = 'YPA0650'
AND is_del IN ('N', 'Y')
LIMIT
1;
错误的执行计划
+--------------------------------+-------------+-----------+-------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id | estRows | task | access object | operator info |
+--------------------------------+-------------+-----------+-------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Projection_7 | 1.00 | root | | dw_finance_stmt.fin_pay_apportion.id, dw_finance_stmt.fin_pay_apportion.seller_user_id, dw_finance_stmt.fin_pay_apportion.buss_partner_id, dw_finance_stmt.fin_pay_apportion.company_code, dw_finance_stmt.fin_pay_apportion.billing_sn_no, dw_finance_stmt.fin_pay_apportion.billing_req_no, dw_finance_stmt.fin_pay_apportion.pay_log_no, dw_finance_stmt.fin_pay_apportion.pay_detail_id, dw_finance_stmt.fin_pay_apportion.bill_head_id, dw_finance_stmt.fin_pay_apportion.biz_no, dw_finance_stmt.fin_pay_apportion.biz_type, dw_finance_stmt.fin_pay_apportion.biz_sub_type, dw_finance_stmt.fin_pay_apportion.ori_biz_no, dw_finance_stmt.fin_pay_apportion.biz_identity, dw_finance_stmt.fin_pay_apportion.src_app_code, dw_finance_stmt.fin_pay_apportion.calc_bill_type, dw_finance_stmt.fin_pay_apportion.scene_type, dw_finance_stmt.fin_pay_apportion.scene_code, dw_finance_stmt.fin_pay_apportion.fee_type, dw_finance_stmt.fin_pay_apportion.calc_direction, dw_finance_stmt.fin_pay_apportion.fee_amount, dw_finance_stmt.fin_pay_apportion.amount, dw_finance_stmt.fin_pay_apportion.pay_status, dw_finance_stmt.fin_pay_apportion.bill_time, dw_finance_stmt.fin_pay_apportion.pay_time, dw_finance_stmt.fin_pay_apportion.target_pay_way, dw_finance_stmt.fin_pay_apportion.src_pay_way, dw_finance_stmt.fin_pay_apportion.pay_type, dw_finance_stmt.fin_pay_apportion.pay_channel, dw_finance_stmt.fin_pay_apportion.merchant_type, dw_finance_stmt.fin_pay_apportion.settle_period, dw_finance_stmt.fin_pay_apportion.payer_type, dw_finance_stmt.fin_pay_apportion.payer_id, dw_finance_stmt.fin_pay_apportion.payer_acc_type, dw_finance_stmt.fin_pay_apportion.payee_type, dw_finance_stmt.fin_pay_apportion.payee_acc_type, dw_finance_stmt.fin_pay_apportion.payee_id, dw_finance_stmt.fin_pay_apportion.time_node, dw_finance_stmt.fin_pay_apportion.aggr_dimension, dw_finance_stmt.fin_pay_apportion.sum_dimension, dw_finance_stmt.fin_pay_apportion.extra_info, dw_finance_stmt.fin_pay_apportion.creator, dw_finance_stmt.fin_pay_apportion.gmt_created, dw_finance_stmt.fin_pay_apportion.modifier, dw_finance_stmt.fin_pay_apportion.gmt_modified, dw_finance_stmt.fin_pay_apportion.is_del, dw_finance_stmt.fin_pay_apportion.app_code, dw_finance_stmt.fin_pay_apportion.currency |
| └─Limit_8 | 1.00 | root | | offset:0, count:1 |
| └─TableReader_13 | 1.00 | root | | data:Limit_12 |
| └─Limit_12 | 1.00 | cop[tikv] | | offset:0, count:1 |
| └─Selection_11 | 1.00 | cop[tikv] | | eq(dw_finance_stmt.fin_pay_apportion.bill_head_id, 1207025387984171), eq(dw_finance_stmt.fin_pay_apportion.fee_type, "G0002"), eq(dw_finance_stmt.fin_pay_apportion.seller_user_id, 113828458), eq(dw_finance_stmt.fin_pay_apportion.target_pay_way, "YPA0650"), in(dw_finance_stmt.fin_pay_apportion.is_del, "N", "Y") |
| └─TableFullScan_10 | 46887974.19 | cop[tikv] | table:fin_pay_apportion | keep order:false |
+--------------------------------+-------------+-----------+-------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
cost信息
+--------------------------------+-------------+----------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+-------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id | estRows | estCost | costFormula | task | access object | operator info |
+--------------------------------+-------------+----------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+-------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Projection_7 | 1.00 | 2121233908.09 | ((((((cpu(4.6887974190885626e+07*filters(5)*tikv_cpu_factor(49.9))) + (scan(4.6887974190885626e+07*logrowsize(1492.1399999999996)*tikv_scan_factor(40.7))))) + (net(1*rowsize(1395)*tidb_kv_net_factor(3.96))))/15.00)) + ((cpu(1*filters(0.48000000000000026)*tidb_cpu_factor(49.9)))/5.00) | root | | dw_finance_stmt.fin_pay_apportion.id, dw_finance_stmt.fin_pay_apportion.seller_user_id, dw_finance_stmt.fin_pay_apportion.buss_partner_id, dw_finance_stmt.fin_pay_apportion.company_code, dw_finance_stmt.fin_pay_apportion.billing_sn_no, dw_finance_stmt.fin_pay_apportion.billing_req_no, dw_finance_stmt.fin_pay_apportion.pay_log_no, dw_finance_stmt.fin_pay_apportion.pay_detail_id, dw_finance_stmt.fin_pay_apportion.bill_head_id, dw_finance_stmt.fin_pay_apportion.biz_no, dw_finance_stmt.fin_pay_apportion.biz_type, dw_finance_stmt.fin_pay_apportion.biz_sub_type, dw_finance_stmt.fin_pay_apportion.ori_biz_no, dw_finance_stmt.fin_pay_apportion.biz_identity, dw_finance_stmt.fin_pay_apportion.src_app_code, dw_finance_stmt.fin_pay_apportion.calc_bill_type, dw_finance_stmt.fin_pay_apportion.scene_type, dw_finance_stmt.fin_pay_apportion.scene_code, dw_finance_stmt.fin_pay_apportion.fee_type, dw_finance_stmt.fin_pay_apportion.calc_direction, dw_finance_stmt.fin_pay_apportion.fee_amount, dw_finance_stmt.fin_pay_apportion.amount, dw_finance_stmt.fin_pay_apportion.pay_status, dw_finance_stmt.fin_pay_apportion.bill_time, dw_finance_stmt.fin_pay_apportion.pay_time, dw_finance_stmt.fin_pay_apportion.target_pay_way, dw_finance_stmt.fin_pay_apportion.src_pay_way, dw_finance_stmt.fin_pay_apportion.pay_type, dw_finance_stmt.fin_pay_apportion.pay_channel, dw_finance_stmt.fin_pay_apportion.merchant_type, dw_finance_stmt.fin_pay_apportion.settle_period, dw_finance_stmt.fin_pay_apportion.payer_type, dw_finance_stmt.fin_pay_apportion.payer_id, dw_finance_stmt.fin_pay_apportion.payer_acc_type, dw_finance_stmt.fin_pay_apportion.payee_type, dw_finance_stmt.fin_pay_apportion.payee_acc_type, dw_finance_stmt.fin_pay_apportion.payee_id, dw_finance_stmt.fin_pay_apportion.time_node, dw_finance_stmt.fin_pay_apportion.aggr_dimension, dw_finance_stmt.fin_pay_apportion.sum_dimension, dw_finance_stmt.fin_pay_apportion.extra_info, dw_finance_stmt.fin_pay_apportion.creator, dw_finance_stmt.fin_pay_apportion.gmt_created, dw_finance_stmt.fin_pay_apportion.modifier, dw_finance_stmt.fin_pay_apportion.gmt_modified, dw_finance_stmt.fin_pay_apportion.is_del, dw_finance_stmt.fin_pay_apportion.app_code, dw_finance_stmt.fin_pay_apportion.currency |
| └─Limit_8 | 1.00 | 2121233903.30 | (((((cpu(4.6887974190885626e+07*filters(5)*tikv_cpu_factor(49.9))) + (scan(4.6887974190885626e+07*logrowsize(1492.1399999999996)*tikv_scan_factor(40.7))))) + (net(1*rowsize(1395)*tidb_kv_net_factor(3.96))))/15.00) | root | | offset:0, count:1 |
| └─TableReader_13 | 1.00 | 2121233903.30 | ((((cpu(4.6887974190885626e+07*filters(5)*tikv_cpu_factor(49.9))) + (scan(4.6887974190885626e+07*logrowsize(1492.1399999999996)*tikv_scan_factor(40.7))))) + (net(1*rowsize(1395)*tidb_kv_net_factor(3.96))))/15.00 | root | | data:Limit_12 |
| └─Limit_12 | 1.00 | 31818503025.29 | ((cpu(4.6887974190885626e+07*filters(5)*tikv_cpu_factor(49.9))) + (scan(4.6887974190885626e+07*logrowsize(1492.1399999999996)*tikv_scan_factor(40.7)))) | cop[tikv] | | offset:0, count:1 |
| └─Selection_11 | 1.00 | 31818503025.29 | (cpu(4.6887974190885626e+07*filters(5)*tikv_cpu_factor(49.9))) + (scan(4.6887974190885626e+07*logrowsize(1492.1399999999996)*tikv_scan_factor(40.7))) | cop[tikv] | | eq(dw_finance_stmt.fin_pay_apportion.bill_head_id, 1207025387984171), eq(dw_finance_stmt.fin_pay_apportion.fee_type, "G0002"), eq(dw_finance_stmt.fin_pay_apportion.seller_user_id, 113828458), eq(dw_finance_stmt.fin_pay_apportion.target_pay_way, "YPA0650"), in(dw_finance_stmt.fin_pay_apportion.is_del, "N", "Y") |
| └─TableFullScan_10 | 46887974.19 | 20119953464.67 | scan(4.6887974190885626e+07*logrowsize(1492.1399999999996)*tikv_scan_factor(40.7)) | cop[tikv] | table:fin_pay_apportion | keep order:false |
+--------------------------------+-------------+----------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+-------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
6 rows in set (0.00 sec)
强制绑定索引执行计划
+------------------------------------+------------+-----------+---------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id | estRows | task | access object | operator info |
+------------------------------------+------------+-----------+---------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Projection_7 | 1.00 | root | | dw_finance_stmt.fin_pay_apportion.id, dw_finance_stmt.fin_pay_apportion.seller_user_id, dw_finance_stmt.fin_pay_apportion.buss_partner_id, dw_finance_stmt.fin_pay_apportion.company_code, dw_finance_stmt.fin_pay_apportion.billing_sn_no, dw_finance_stmt.fin_pay_apportion.billing_req_no, dw_finance_stmt.fin_pay_apportion.pay_log_no, dw_finance_stmt.fin_pay_apportion.pay_detail_id, dw_finance_stmt.fin_pay_apportion.bill_head_id, dw_finance_stmt.fin_pay_apportion.biz_no, dw_finance_stmt.fin_pay_apportion.biz_type, dw_finance_stmt.fin_pay_apportion.biz_sub_type, dw_finance_stmt.fin_pay_apportion.ori_biz_no, dw_finance_stmt.fin_pay_apportion.biz_identity, dw_finance_stmt.fin_pay_apportion.src_app_code, dw_finance_stmt.fin_pay_apportion.calc_bill_type, dw_finance_stmt.fin_pay_apportion.scene_type, dw_finance_stmt.fin_pay_apportion.scene_code, dw_finance_stmt.fin_pay_apportion.fee_type, dw_finance_stmt.fin_pay_apportion.calc_direction, dw_finance_stmt.fin_pay_apportion.fee_amount, dw_finance_stmt.fin_pay_apportion.amount, dw_finance_stmt.fin_pay_apportion.pay_status, dw_finance_stmt.fin_pay_apportion.bill_time, dw_finance_stmt.fin_pay_apportion.pay_time, dw_finance_stmt.fin_pay_apportion.target_pay_way, dw_finance_stmt.fin_pay_apportion.src_pay_way, dw_finance_stmt.fin_pay_apportion.pay_type, dw_finance_stmt.fin_pay_apportion.pay_channel, dw_finance_stmt.fin_pay_apportion.merchant_type, dw_finance_stmt.fin_pay_apportion.settle_period, dw_finance_stmt.fin_pay_apportion.payer_type, dw_finance_stmt.fin_pay_apportion.payer_id, dw_finance_stmt.fin_pay_apportion.payer_acc_type, dw_finance_stmt.fin_pay_apportion.payee_type, dw_finance_stmt.fin_pay_apportion.payee_acc_type, dw_finance_stmt.fin_pay_apportion.payee_id, dw_finance_stmt.fin_pay_apportion.time_node, dw_finance_stmt.fin_pay_apportion.aggr_dimension, dw_finance_stmt.fin_pay_apportion.sum_dimension, dw_finance_stmt.fin_pay_apportion.extra_info, dw_finance_stmt.fin_pay_apportion.creator, dw_finance_stmt.fin_pay_apportion.gmt_created, dw_finance_stmt.fin_pay_apportion.modifier, dw_finance_stmt.fin_pay_apportion.gmt_modified, dw_finance_stmt.fin_pay_apportion.is_del, dw_finance_stmt.fin_pay_apportion.app_code, dw_finance_stmt.fin_pay_apportion.currency, dw_finance_stmt.fin_pay_apportion.transaction_amount, dw_finance_stmt.fin_pay_apportion.transaction_currency, dw_finance_stmt.fin_pay_apportion.stmt_exchange_rate, dw_finance_stmt.fin_pay_apportion.sub_pay_log_no, dw_finance_stmt.fin_pay_apportion.biz_area, dw_finance_stmt.fin_pay_apportion.merchant_id, dw_finance_stmt.fin_pay_apportion.migrate_status, dw_finance_stmt.fin_pay_apportion.ori_billing_sn_no, dw_finance_stmt.fin_pay_apportion.fund_flow_template_code |
| └─Limit_9 | 1.00 | root | | offset:0, count:1 |
| └─IndexLookUp_14 | 1.00 | root | | |
| ├─IndexRangeScan_10(Build) | 1637373.80 | cop[tikv] | table:fin_pay_apportion, index:idx_bill_head_id_fee_type_pay_status(bill_head_id, fee_type, pay_status) | range:[1207025387984171 "G0002",1207025387984171 "G0002"], keep order:false |
| └─Limit_13(Probe) | 1.00 | cop[tikv] | | offset:0, count:1 |
| └─Selection_12 | 1.00 | cop[tikv] | | eq(dw_finance_stmt.fin_pay_apportion.seller_user_id, 113828458), eq(dw_finance_stmt.fin_pay_apportion.target_pay_way, "YPA0650"), in(dw_finance_stmt.fin_pay_apportion.is_del, "N", "Y") |
| └─TableRowIDScan_11 | 1637373.80 | cop[tikv] | table:fin_pay_apportion | keep order:false |
+------------------------------------+------------+-----------+---------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
7 rows in set (0.00 sec)
强制索引cost
+------------------------------------+------------+---------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+---------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id | estRows | estCost | costFormula | task | access object | operator info |
+------------------------------------+------------+---------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+---------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Projection_7 | 1.00 | 3312238535.51 | (((((net(1.6373737951911117e+06*rowsize(35.980000000000004)*tidb_kv_net_factor(3.96))) + (scan(1.6373737951911117e+06*logrowsize(59.480000000000004)*tikv_scan_factor(40.7))))/15.00) + (((((net(1.6373737951911117e+06*rowsize(1130.8249999999998)*tidb_kv_net_factor(3.96))) + (((cpu(1.6373737951911117e+06*filters(3)*tikv_cpu_factor(49.9))) + (scan(1.6373737951911117e+06*logrowsize(1492.1399999999996)*tikv_scan_factor(40.7))))))/15.00) + ((double-read-cpu(1.6373737951911117e+06*tidb_cpu_factor(49.9))) + (doubleRead(tasks(2619.798072305779)*tidb_request_factor(6e+06)))))/5.00))) + ((cpu(1*filters(0.5700000000000003)*tidb_cpu_factor(49.9)))/5.00) | root | | dw_finance_stmt.fin_pay_apportion.id, dw_finance_stmt.fin_pay_apportion.seller_user_id, dw_finance_stmt.fin_pay_apportion.buss_partner_id, dw_finance_stmt.fin_pay_apportion.company_code, dw_finance_stmt.fin_pay_apportion.billing_sn_no, dw_finance_stmt.fin_pay_apportion.billing_req_no, dw_finance_stmt.fin_pay_apportion.pay_log_no, dw_finance_stmt.fin_pay_apportion.pay_detail_id, dw_finance_stmt.fin_pay_apportion.bill_head_id, dw_finance_stmt.fin_pay_apportion.biz_no, dw_finance_stmt.fin_pay_apportion.biz_type, dw_finance_stmt.fin_pay_apportion.biz_sub_type, dw_finance_stmt.fin_pay_apportion.ori_biz_no, dw_finance_stmt.fin_pay_apportion.biz_identity, dw_finance_stmt.fin_pay_apportion.src_app_code, dw_finance_stmt.fin_pay_apportion.calc_bill_type, dw_finance_stmt.fin_pay_apportion.scene_type, dw_finance_stmt.fin_pay_apportion.scene_code, dw_finance_stmt.fin_pay_apportion.fee_type, dw_finance_stmt.fin_pay_apportion.calc_direction, dw_finance_stmt.fin_pay_apportion.fee_amount, dw_finance_stmt.fin_pay_apportion.amount, dw_finance_stmt.fin_pay_apportion.pay_status, dw_finance_stmt.fin_pay_apportion.bill_time, dw_finance_stmt.fin_pay_apportion.pay_time, dw_finance_stmt.fin_pay_apportion.target_pay_way, dw_finance_stmt.fin_pay_apportion.src_pay_way, dw_finance_stmt.fin_pay_apportion.pay_type, dw_finance_stmt.fin_pay_apportion.pay_channel, dw_finance_stmt.fin_pay_apportion.merchant_type, dw_finance_stmt.fin_pay_apportion.settle_period, dw_finance_stmt.fin_pay_apportion.payer_type, dw_finance_stmt.fin_pay_apportion.payer_id, dw_finance_stmt.fin_pay_apportion.payer_acc_type, dw_finance_stmt.fin_pay_apportion.payee_type, dw_finance_stmt.fin_pay_apportion.payee_acc_type, dw_finance_stmt.fin_pay_apportion.payee_id, dw_finance_stmt.fin_pay_apportion.time_node, dw_finance_stmt.fin_pay_apportion.aggr_dimension, dw_finance_stmt.fin_pay_apportion.sum_dimension, dw_finance_stmt.fin_pay_apportion.extra_info, dw_finance_stmt.fin_pay_apportion.creator, dw_finance_stmt.fin_pay_apportion.gmt_created, dw_finance_stmt.fin_pay_apportion.modifier, dw_finance_stmt.fin_pay_apportion.gmt_modified, dw_finance_stmt.fin_pay_apportion.is_del, dw_finance_stmt.fin_pay_apportion.app_code, dw_finance_stmt.fin_pay_apportion.currency, dw_finance_stmt.fin_pay_apportion.transaction_amount, dw_finance_stmt.fin_pay_apportion.transaction_currency, dw_finance_stmt.fin_pay_apportion.stmt_exchange_rate, dw_finance_stmt.fin_pay_apportion.sub_pay_log_no, dw_finance_stmt.fin_pay_apportion.biz_area, dw_finance_stmt.fin_pay_apportion.merchant_id, dw_finance_stmt.fin_pay_apportion.migrate_status, dw_finance_stmt.fin_pay_apportion.ori_billing_sn_no, dw_finance_stmt.fin_pay_apportion.fund_flow_template_code |
| └─Limit_9 | 1.00 | 3312238529.82 | ((((net(1.6373737951911117e+06*rowsize(35.980000000000004)*tidb_kv_net_factor(3.96))) + (scan(1.6373737951911117e+06*logrowsize(59.480000000000004)*tikv_scan_factor(40.7))))/15.00) + (((((net(1.6373737951911117e+06*rowsize(1130.8249999999998)*tidb_kv_net_factor(3.96))) + (((cpu(1.6373737951911117e+06*filters(3)*tikv_cpu_factor(49.9))) + (scan(1.6373737951911117e+06*logrowsize(1492.1399999999996)*tikv_scan_factor(40.7))))))/15.00) + ((double-read-cpu(1.6373737951911117e+06*tidb_cpu_factor(49.9))) + (doubleRead(tasks(2619.798072305779)*tidb_request_factor(6e+06)))))/5.00)) | root | | offset:0, count:1 |
| └─IndexLookUp_14 | 1.00 | 3312238529.82 | (((net(1.6373737951911117e+06*rowsize(35.980000000000004)*tidb_kv_net_factor(3.96))) + (scan(1.6373737951911117e+06*logrowsize(59.480000000000004)*tikv_scan_factor(40.7))))/15.00) + (((((net(1.6373737951911117e+06*rowsize(1130.8249999999998)*tidb_kv_net_factor(3.96))) + (((cpu(1.6373737951911117e+06*filters(3)*tikv_cpu_factor(49.9))) + (scan(1.6373737951911117e+06*logrowsize(1492.1399999999996)*tikv_scan_factor(40.7))))))/15.00) + ((double-read-cpu(1.6373737951911117e+06*tidb_cpu_factor(49.9))) + (doubleRead(tasks(2619.798072305779)*tidb_request_factor(6e+06)))))/5.00) | root | | |
| ├─IndexRangeScan_10(Build) | 1637373.80 | 392804897.07 | scan(1.6373737951911117e+06*logrowsize(59.480000000000004)*tikv_scan_factor(40.7)) | cop[tikv] | table:fin_pay_apportion, index:idx_bill_head_id_fee_type_pay_status(bill_head_id, fee_type, pay_status) | range:[1207025387984171 "G0002",1207025387984171 "G0002"], keep order:false |
| └─Limit_13(Probe) | 1.00 | 947723257.95 | ((cpu(1.6373737951911117e+06*filters(3)*tikv_cpu_factor(49.9))) + (scan(1.6373737951911117e+06*logrowsize(1492.1399999999996)*tikv_scan_factor(40.7)))) | cop[tikv] | | offset:0, count:1 |
| └─Selection_12 | 1.00 | 947723257.95 | (cpu(1.6373737951911117e+06*filters(3)*tikv_cpu_factor(49.9))) + (scan(1.6373737951911117e+06*logrowsize(1492.1399999999996)*tikv_scan_factor(40.7))) | cop[tikv] | | eq(dw_finance_stmt.fin_pay_apportion.seller_user_id, 113828458), eq(dw_finance_stmt.fin_pay_apportion.target_pay_way, "YPA0650"), in(dw_finance_stmt.fin_pay_apportion.is_del, "N", "Y") |
| └─TableRowIDScan_11 | 1637373.80 | 702608400.81 | scan(1.6373737951911117e+06*logrowsize(1492.1399999999996)*tikv_scan_factor(40.7)) | cop[tikv] | table:fin_pay_apportion | keep order:false |
+------------------------------------+------------+---------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+---------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
7 rows in set (0.00 sec)