v7.5.x版本SQL走错索引

Hacker_Yv76YjBL · 2025 年9 月 15 日 03:25

【TiDB 使用环境】生产环境
【TiDB 版本】v7.5.6
【遇到的问题：问题现象及影响】
SQL执行耗时较高，执行计划选择索引偏差极其不准。有没有参数可以让执行计划选择最优的索引。
应该选择索引： KEY idx_userid_feetype_time (seller_user_id,fee_type,gmt_created),
错误选择索引： KEY idx_gmt_created (gmt_created),
表结构

CREATE TABLE `fin_pay_handle_row` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'pk',
  `seller_user_id` bigint(20) NOT NULL ,
  `head_id` bigint(20) NOT NULL,
  `bill_head_id` bigint(20) NOT NULL ,
  `amount` bigint(20) NOT NULL ,
  `pay_log_no` varchar(64) NOT NULL DEFAULT '' ,
  `biz_no` varchar(64) NOT NULL DEFAULT '',
  `ori_biz_no` varchar(32) NOT NULL DEFAULT '',
  `trans_pay_no` varchar(32) NOT NULL DEFAULT '' ,
  `ext_sn` varchar(64) DEFAULT NULL ,
  `pay_way` varchar(20) NOT NULL DEFAULT '' ,
  `pay_channel` varchar(20) NOT NULL DEFAULT '' ,
  `payer_role_id` varchar(32) NOT NULL ,
  `payer_type` varchar(32) NOT NULL ,
  `payer_account_type` varchar(20) NOT NULL ,
  `payee_role_id` varchar(32) NOT NULL ,
  `payee_type` varchar(32) NOT NULL ,
  `payee_account_type` varchar(20) NOT NULL ,
  `settle_period` tinyint(4) NOT NULL DEFAULT '0' ,
  `fee_type` varchar(20) NOT NULL DEFAULT '0' ,
  `actual_pay_way` varchar(20) DEFAULT NULL ,
  `handle_status` tinyint(4) NOT NULL ,
  `req_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '请求时间',
  `complete_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '完成时间',
  `gmt_created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `modifier` varchar(50) NOT NULL COMMENT '修改人',
  `gmt_modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
  UNIQUE KEY `v_pk` (`id`,`seller_user_id`),
  KEY `idx_bill_head_id` (`bill_head_id`),
  KEY `idx_head_id` (`head_id`),
  KEY `idx_gmt_created` (`gmt_created`),
  KEY `idx_biz_no` (`biz_no`),
  KEY `idx_handle_status_gmt_created` (`handle_status`,`gmt_created`),
  KEY `idx_ext_sn` (`ext_sn`),
  KEY `idx_userid_feetype_time` (`seller_user_id`,`fee_type`,`gmt_created`),
  KEY `idx_user_fee_payway_status` (`seller_user_id`,`fee_type`,`actual_pay_way`,`handle_status`),
  KEY `idx_ori_biz_no` (`ori_biz_no`),
  KEY `idx_status_strategy` (`strategy_type`,`handle_status`),
  KEY `idx_status_type_gmt_created` (`handle_status`,`fee_type`,`gmt_created`),
  KEY `idx_trans_pay_no` (`trans_pay_no`),
  KEY `idx_seller_handle_status` (`seller_user_id`,`handle_status`)
)

表健康度

show stats_healthy where table_name='fin_pay_handle_row';
+-----------------+--------------------+----------------+---------+
| Db_name         | Table_name         | Partition_name | Healthy |
+-----------------+--------------------+----------------+---------+
| dw_finance_stmt | fin_pay_handle_row |                |      85 |
+-----------------+--------------------+----------------+---------+
1 row in set (0.00 sec)

表容量

执行SQL

SELECT
  id,
  seller_user_id,
  head_id,
  bill_head_id,
  amount,
  pay_log_no,
  biz_no,
  ori_biz_no,
  trans_pay_no,
  ext_sn,
  pay_way,
  pay_channel,
  payer_role_id,
  payer_type,
  payer_account_type,
  payee_role_id,
  payee_type,
  payee_account_type,
  settle_period,
  fee_type,
  handle_status,
  retry_status,
  exec_seq_no,
  req_time,
  complete_time,
  extra_info,
  remark,
  res_code,
  res_msg,
  actual_pay_way,
  creator,
  gmt_created,
  modifier,
  gmt_modified,
  is_del,
  app_code,
  currency,
  product_code,
  strategy_type,
  sub_pay_log_no,
  biz_area,
  merchant_id,
  payer_acc_id,
  payee_acc_id
FROM
  fin_pay_handle_row
WHERE
  seller_user_id IN (1565305001)
  AND gmt_created >= '2024-09-15 00:00:00.0'
  AND gmt_created <= '2025-09-15 23:59:59.0'
  AND fee_type IN (
    'G0002',
    'A0011',
    'GF002',
    'GD002',
    'GR002',
    'GN002'
  )
  AND pay_way NOT IN ('WZP0010')
  AND is_del IN ('N', 'Y')
  AND settle_period = 2
ORDER BY
  gmt_created DESC
LIMIT
  0, 20

执行计划

explain SELECT
    ->   id,
    ->   seller_user_id,
    ->   head_id,
    ->   bill_head_id,
    ->   amount,
    ->   pay_log_no,
    ->   biz_no,
    ->   ori_biz_no,
    ->   trans_pay_no,
    ->   ext_sn,
    ->   pay_way,
    ->   pay_channel,
    ->   payer_role_id,
    ->   payer_type,
    ->   payer_account_type,
    ->   payee_role_id,
    ->   payee_type,
    ->   payee_account_type,
    ->   settle_period,
    ->   fee_type,
    ->   handle_status,
    ->   retry_status,
    ->   exec_seq_no,
    ->   req_time,
    ->   complete_time,
    ->   extra_info,
    ->   remark,
    ->   res_code,
    ->   res_msg,
    ->   actual_pay_way,
    ->   creator,
    ->   gmt_created,
    ->   modifier,
    ->   gmt_modified,
    ->   is_del,
    ->   app_code,
    ->   currency,
    ->   product_code,
    ->   strategy_type,
    ->   sub_pay_log_no,
    ->   biz_area,
    ->   merchant_id,
    ->   payer_acc_id,
    ->   payee_acc_id
    -> FROM
    ->   fin_pay_handle_row
    -> WHERE
    ->   seller_user_id IN (1565305001)
    ->   AND gmt_created >= '2024-09-15 00:00:00.0'
    ->   AND gmt_created <= '2025-09-15 23:59:59.0'
    ->   AND fee_type IN (
    ->     'G0002',
    ->     'A0011',
    ->     'GF002',
    ->     'GD002',
    ->     'GR002',
    ->     'GN002'
    ->   )
    ->   AND pay_way NOT IN ('WZP0010')
    ->   AND is_del IN ('N', 'Y')
    ->   AND settle_period = 2
    -> ORDER BY
    ->   gmt_created DESC
    -> LIMIT
    ->   0, 20;
+--------------------------------------+-----------+-----------+--------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id                                   | estRows   | task      | access object                                                | operator info                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    |
+--------------------------------------+-----------+-----------+--------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Projection_7                         | 20.00     | root      |                                                              | dw_finance_stmt.fin_pay_handle_row.id, dw_finance_stmt.fin_pay_handle_row.seller_user_id, dw_finance_stmt.fin_pay_handle_row.head_id, dw_finance_stmt.fin_pay_handle_row.bill_head_id, dw_finance_stmt.fin_pay_handle_row.amount, dw_finance_stmt.fin_pay_handle_row.pay_log_no, dw_finance_stmt.fin_pay_handle_row.biz_no, dw_finance_stmt.fin_pay_handle_row.ori_biz_no, dw_finance_stmt.fin_pay_handle_row.trans_pay_no, dw_finance_stmt.fin_pay_handle_row.ext_sn, dw_finance_stmt.fin_pay_handle_row.pay_way, dw_finance_stmt.fin_pay_handle_row.pay_channel, dw_finance_stmt.fin_pay_handle_row.payer_role_id, dw_finance_stmt.fin_pay_handle_row.payer_type, dw_finance_stmt.fin_pay_handle_row.payer_account_type, dw_finance_stmt.fin_pay_handle_row.payee_role_id, dw_finance_stmt.fin_pay_handle_row.payee_type, dw_finance_stmt.fin_pay_handle_row.payee_account_type, dw_finance_stmt.fin_pay_handle_row.settle_period, dw_finance_stmt.fin_pay_handle_row.fee_type, dw_finance_stmt.fin_pay_handle_row.handle_status, dw_finance_stmt.fin_pay_handle_row.retry_status, dw_finance_stmt.fin_pay_handle_row.exec_seq_no, dw_finance_stmt.fin_pay_handle_row.req_time, dw_finance_stmt.fin_pay_handle_row.complete_time, dw_finance_stmt.fin_pay_handle_row.extra_info, dw_finance_stmt.fin_pay_handle_row.remark, dw_finance_stmt.fin_pay_handle_row.res_code, dw_finance_stmt.fin_pay_handle_row.res_msg, dw_finance_stmt.fin_pay_handle_row.actual_pay_way, dw_finance_stmt.fin_pay_handle_row.creator, dw_finance_stmt.fin_pay_handle_row.gmt_created, dw_finance_stmt.fin_pay_handle_row.modifier, dw_finance_stmt.fin_pay_handle_row.gmt_modified, dw_finance_stmt.fin_pay_handle_row.is_del, dw_finance_stmt.fin_pay_handle_row.app_code, dw_finance_stmt.fin_pay_handle_row.currency, dw_finance_stmt.fin_pay_handle_row.product_code, dw_finance_stmt.fin_pay_handle_row.strategy_type, dw_finance_stmt.fin_pay_handle_row.sub_pay_log_no, dw_finance_stmt.fin_pay_handle_row.biz_area, dw_finance_stmt.fin_pay_handle_row.merchant_id, dw_finance_stmt.fin_pay_handle_row.payer_acc_id, dw_finance_stmt.fin_pay_handle_row.payee_acc_id |
| └─Limit_12                           | 20.00     | root      |                                                              | offset:0, count:20                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               |
|   └─Projection_22                    | 20.00     | root      |                                                              | dw_finance_stmt.fin_pay_handle_row.id, dw_finance_stmt.fin_pay_handle_row.seller_user_id, dw_finance_stmt.fin_pay_handle_row.head_id, dw_finance_stmt.fin_pay_handle_row.bill_head_id, dw_finance_stmt.fin_pay_handle_row.amount, dw_finance_stmt.fin_pay_handle_row.pay_log_no, dw_finance_stmt.fin_pay_handle_row.biz_no, dw_finance_stmt.fin_pay_handle_row.ori_biz_no, dw_finance_stmt.fin_pay_handle_row.trans_pay_no, dw_finance_stmt.fin_pay_handle_row.ext_sn, dw_finance_stmt.fin_pay_handle_row.pay_way, dw_finance_stmt.fin_pay_handle_row.pay_channel, dw_finance_stmt.fin_pay_handle_row.payer_role_id, dw_finance_stmt.fin_pay_handle_row.payer_type, dw_finance_stmt.fin_pay_handle_row.payer_account_type, dw_finance_stmt.fin_pay_handle_row.payee_role_id, dw_finance_stmt.fin_pay_handle_row.payee_type, dw_finance_stmt.fin_pay_handle_row.payee_account_type, dw_finance_stmt.fin_pay_handle_row.settle_period, dw_finance_stmt.fin_pay_handle_row.fee_type, dw_finance_stmt.fin_pay_handle_row.handle_status, dw_finance_stmt.fin_pay_handle_row.exec_seq_no, dw_finance_stmt.fin_pay_handle_row.req_time, dw_finance_stmt.fin_pay_handle_row.complete_time, dw_finance_stmt.fin_pay_handle_row.extra_info, dw_finance_stmt.fin_pay_handle_row.remark, dw_finance_stmt.fin_pay_handle_row.res_msg, dw_finance_stmt.fin_pay_handle_row.actual_pay_way, dw_finance_stmt.fin_pay_handle_row.retry_status, dw_finance_stmt.fin_pay_handle_row.res_code, dw_finance_stmt.fin_pay_handle_row.creator, dw_finance_stmt.fin_pay_handle_row.gmt_created, dw_finance_stmt.fin_pay_handle_row.modifier, dw_finance_stmt.fin_pay_handle_row.gmt_modified, dw_finance_stmt.fin_pay_handle_row.is_del, dw_finance_stmt.fin_pay_handle_row.currency, dw_finance_stmt.fin_pay_handle_row.app_code, dw_finance_stmt.fin_pay_handle_row.product_code, dw_finance_stmt.fin_pay_handle_row.strategy_type, dw_finance_stmt.fin_pay_handle_row.sub_pay_log_no, dw_finance_stmt.fin_pay_handle_row.merchant_id, dw_finance_stmt.fin_pay_handle_row.payer_acc_id, dw_finance_stmt.fin_pay_handle_row.payee_acc_id, dw_finance_stmt.fin_pay_handle_row.biz_area |
|     └─IndexLookUp_21                 | 20.00     | root      |                                                              |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  |
|       ├─IndexRangeScan_18(Build)     | 152445.04 | cop[tikv] | table:fin_pay_handle_row, index:idx_gmt_created(gmt_created) | range:[2024-09-15 00:00:00,2025-09-15 23:59:59], keep order:true, desc                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           |
|       └─Selection_20(Probe)          | 20.00     | cop[tikv] |                                                              | eq(dw_finance_stmt.fin_pay_handle_row.seller_user_id, 1565305001), eq(dw_finance_stmt.fin_pay_handle_row.settle_period, 2), in(dw_finance_stmt.fin_pay_handle_row.fee_type, "G0002", "A0011", "GF002", "GD002", "GR002", "GN002"), in(dw_finance_stmt.fin_pay_handle_row.is_del, "N", "Y"), ne(dw_finance_stmt.fin_pay_handle_row.pay_way, "WZP0010")                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            |
|         └─TableRowIDScan_19          | 152445.04 | cop[tikv] | table:fin_pay_handle_row                                     | keep order:false                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 |
+--------------------------------------+-----------+-----------+--------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
7 rows in set (0.00 sec)

WalterWj · 2025 年9 月 15 日 03:26

https://docs.pingcap.com/zh/tidb/v7.5/system-variables/#tidb_opt_ordering_index_selectivity_threshold-从-v700-版本开始引入

试试这个参数能解决问题不。

Hacker_Yv76YjBL · 2025 年9 月 15 日 03:32

我们SQL的where条件，都可以命中索引，是否可以直接将tidb_opt_ordering_index_selectivity_threshold调整为1？

CHENGX · 2025 年9 月 15 日 04:09

重新analyze表试试

llsong · 2025 年9 月 15 日 06:12

我也发现有时候明明有索引却不使用，可以使用using语句来指定索引进行优化

老鹰506 · 2025 年9 月 15 日 11:33

TIDB遵循索引最左原则，但是最左索引原则会被范围查询中断。所以不应该是sql where条件中顺序的问题？

调整成如下试试呢？

... ...
seller_user_id = 1565305001
  AND fee_type IN (
    'G0002',
    'A0011',
    'GF002',
    'GD002',
    'GR002',
    'GN002'
  )
AND gmt_created >= '2024-09-15 00:00:00.0'
AND gmt_created <= '2025-09-15 23:59:59.0'
... ...

我是火炎焱燚 · 2025 年9 月 15 日 16:48

优先通过统计信息修复和SQL Hint 强制索引快速止血；中长期需结合代价模型切换、索引匹配验证和自动 ANALYZE 调优从根本上解决问题。

system · 2025 年9 月 22 日 16:49

此话题已在最后回复的 7 天后被自动关闭。不再允许新回复。