表分区 同时存在联合主键与联合索引，优化器是如何工作的？

🪐 TiDB 技术问题 性能调优
1

【TiDB 版本】：

3.0.11

【问题描述】：

mysql root@192.168.180.58:prd2_pfizer> EXPLAIN SELECT
                                           COUNT( * )
                                       FROM
                                           test sale
                                       WHERE
                                           sale.is_del = '0'
                                           AND sale.normal_seller_code != '99999'
                                           AND sale.normal_organ_code != '99999'
                                           AND sale.normal_product_code != '99999'
                                           AND sale.br_status = '3';
Reconnecting...
+----------------------+----------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id                   | count    | task | operator info                                                                                                                                                                                                               |
+----------------------+----------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| StreamAgg_19         | 1.00     | root | funcs:count(1)                                                                                                                                                                                                              |
| └─Union_20           | 0.02     | root |                                                                                                                                                                                                                             |
|   ├─IndexReader_23   | 0.00     | root | index:Selection_22                                                                                                                                                                                                          |
|   │ └─Selection_22   | 0.00     | cop  | eq(prd2_pfizer.sale.br_status, "3"), eq(prd2_pfizer.sale.is_del, "0"), ne(prd2_pfizer.sale.normal_organ_code, "99999"), ne(prd2_pfizer.sale.normal_product_code, "99999"), ne(prd2_pfizer.sale.normal_seller_code, "99999") |
|   │   └─IndexScan_21 | 10000.00 | cop  | table:sale, partition:p200001, index:seller_date, distributor_code, normal_product_code, normal_seller_code, normal_organ_code, br_status, is_del, range:[NULL,+inf], keep order:false, stats:pseudo                        |
|   ├─IndexReader_26   | 0.00     | root | index:Selection_25                                                                                                                                                                                                          |
|   │ └─Selection_25   | 0.00     | cop  | eq(prd2_pfizer.sale.br_status, "3"), eq(prd2_pfizer.sale.is_del, "0"), ne(prd2_pfizer.sale.normal_organ_code, "99999"), ne(prd2_pfizer.sale.normal_product_code, "99999"), ne(prd2_pfizer.sale.normal_seller_code, "99999") |
|   │   └─IndexScan_24 | 10000.00 | cop  | table:sale, partition:p201701, index:seller_date, distributor_code, normal_product_code, normal_seller_code, normal_organ_code, br_status, is_del, range:[NULL,+inf], keep order:false, stats:pseudo                        |
|   ├─IndexReader_29   | 0.00     | root | index:Selection_28                                                                                                                                                                                                          |
|   │ └─Selection_28   | 0.00     | cop  | eq(prd2_pfizer.sale.br_status, "3"), eq(prd2_pfizer.sale.is_del, "0"), ne(prd2_pfizer.sale.normal_organ_code, "99999"), ne(prd2_pfizer.sale.normal_product_code, "99999"), ne(prd2_pfizer.sale.normal_seller_code, "99999") |
|   │   └─IndexScan_27 | 10000.00 | cop  | table:sale, partition:p201712, index:seller_date, distributor_code, normal_product_code, normal_seller_code, normal_organ_code, br_status, is_del, range:[NULL,+inf], keep order:false, stats:pseudo                        |
|   ├─IndexReader_32   | 0.00     | root | index:Selection_31                                                                                                                                                                                                          |
|   │ └─Selection_31   | 0.00     | cop  | eq(prd2_pfizer.sale.br_status, "3"), eq(prd2_pfizer.sale.is_del, "0"), ne(prd2_pfizer.sale.normal_organ_code, "99999"), ne(prd2_pfizer.sale.normal_product_code, "99999"), ne(prd2_pfizer.sale.normal_seller_code, "99999") |
|   │   └─IndexScan_30 | 10000.00 | cop  | table:sale, partition:p201812, index:seller_date, distributor_code, normal_product_code, normal_seller_code, normal_organ_code, br_status, is_del, range:[NULL,+inf], keep order:false, stats:pseudo                        |
|   ├─IndexReader_35   | 0.00     | root | index:Selection_34                                                                                                                                                                                                          |
|   │ └─Selection_34   | 0.00     | cop  | eq(prd2_pfizer.sale.br_status, "3"), eq(prd2_pfizer.sale.is_del, "0"), ne(prd2_pfizer.sale.normal_organ_code, "99999"), ne(prd2_pfizer.sale.normal_product_code, "99999"), ne(prd2_pfizer.sale.normal_seller_code, "99999") |
|   │   └─IndexScan_33 | 10000.00 | cop  | table:sale, partition:p201912, index:seller_date, distributor_code, normal_product_code, normal_seller_code, normal_organ_code, br_status, is_del, range:[NULL,+inf], keep order:false, stats:pseudo                        |
|   ├─IndexReader_38   | 0.00     | root | index:Selection_37                                                                                                                                                                                                          |
|   │ └─Selection_37   | 0.00     | cop  | eq(prd2_pfizer.sale.br_status, "3"), eq(prd2_pfizer.sale.is_del, "0"), ne(prd2_pfizer.sale.normal_organ_code, "99999"), ne(prd2_pfizer.sale.normal_product_code, "99999"), ne(prd2_pfizer.sale.normal_seller_code, "99999") |
|   │   └─IndexScan_36 | 10000.00 | cop  | table:sale, partition:p202012, index:seller_date, distributor_code, normal_product_code, normal_seller_code, normal_organ_code, br_status, is_del, range:[NULL,+inf], keep order:false, stats:pseudo                        |
|   ├─IndexReader_41   | 0.00     | root | index:Selection_40                                                                                                                                                                                                          |
|   │ └─Selection_40   | 0.00     | cop  | eq(prd2_pfizer.sale.br_status, "3"), eq(prd2_pfizer.sale.is_del, "0"), ne(prd2_pfizer.sale.normal_organ_code, "99999"), ne(prd2_pfizer.sale.normal_product_code, "99999"), ne(prd2_pfizer.sale.normal_seller_code, "99999") |
|   │   └─IndexScan_39 | 10000.00 | cop  | table:sale, partition:p202112, index:seller_date, distributor_code, normal_product_code, normal_seller_code, normal_organ_code, br_status, is_del, range:[NULL,+inf], keep order:false, stats:pseudo                        |
|   └─IndexReader_44   | 0.00     | root | index:Selection_43                                                                                                                                                                                                          |
|     └─Selection_43   | 0.00     | cop  | eq(prd2_pfizer.sale.br_status, "3"), eq(prd2_pfizer.sale.is_del, "0"), ne(prd2_pfizer.sale.normal_organ_code, "99999"), ne(prd2_pfizer.sale.normal_product_code, "99999"), ne(prd2_pfizer.sale.normal_seller_code, "99999") |
|       └─IndexScan_42 | 10000.00 | cop  | table:sale, partition:p202211, index:seller_date, distributor_code, normal_product_code, normal_seller_code, normal_organ_code, br_status, is_del, range:[NULL,+inf], keep order:false, stats:pseudo                        |
+----------------------+----------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

表分区结构

CREATE TABLE `test` (
  `seller_date` date NOT NULL COMMENT '销售日期',
  `distributor_code` varchar(200) COLLATE utf8_bin DEFAULT NULL COMMENT '客户编码',
  `normal_product_code` varchar(32) COLLATE utf8_bin NOT NULL COMMENT '产品编码',
  `normal_seller_code` varchar(32) COLLATE utf8_bin DEFAULT NULL COMMENT '卖方编码',
  `normal_organ_code` varchar(32) COLLATE utf8_bin DEFAULT NULL COMMENT '买方编码',
  `br_status` varchar(32) COLLATE utf8_bin DEFAULT NULL COMMENT 'BR核查状态',
  `is_del` varchar(32) COLLATE utf8_bin DEFAULT '0' COMMENT '系统是否删除',
  `id` varchar(32) COLLATE utf8_bin NOT NULL,
  `source_file_id` varchar(500) COLLATE utf8_bin DEFAULT NULL COMMENT '原始文件ID',
  PRIMARY KEY (`seller_date`,`id`),
  KEY `index1` (`seller_date`,`distributor_code`,`normal_product_code`,`normal_seller_code`,`normal_organ_code`,`br_status`,`is_del`),
  KEY `index_source_file_id` (`source_file_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci COMMENT='销售数据'
PARTITION BY RANGE ( to_days(`seller_date`) ) (
  PARTITION p200001 VALUES LESS THAN (730485),
  PARTITION p201701 VALUES LESS THAN (736695),
  PARTITION p201712 VALUES LESS THAN (737060),
  PARTITION p201812 VALUES LESS THAN (737425),
  PARTITION p201912 VALUES LESS THAN (737790),
  PARTITION p202012 VALUES LESS THAN (738156),
  PARTITION p202112 VALUES LESS THAN (738521),
  PARTITION p202211 VALUES LESS THAN (738886)
);

问题

1 我的问题是不明白，我并没有满足 联合索引的查询条件，它是如何触发了联合索引呢？

2 仔细看一下，└─Selection_43 | 0.00 | cop | eq(prd2_pfizer.sale.br_status, "3"), eq 这种应该是没有走索引的吧，不理解

3 我猜是因为表分区 列 加入到了主键中，走了索引， 但是为什么不是走的主键索引，而是联合索引呢？

4 之所以问这样的问题，是想更进一步的了解，在实际业务中，如何正确的做表分区 与 添加 联合索引

2

看下执行计划部分内容，对 explain 结果有详细的介绍

https://pingcap.com/docs-cn/stable/reference/performance/understanding-the-query-execution-plan/#理解-tidb-执行计划

3

1、我的问题是不明白，我并没有满足 联合索引的查询条件，它是如何触发了联合索引呢？

上面的执行计划使用到了索引，索引为

image
image1340×50 8.62 KB

选择这个索引的原因是，在进行在进行 count 查询的时候：
image
image746×388 28.6 KB

这个索引对于上面的查询来说是覆盖查询，在索引选择时，选择了这个索引

2、仔细看一下， └─Selection_43 | 0.00 | cop | eq(prd2_pfizer.sale.br_status, "3"), eq 这种应该是没有走索引的吧，不理解

我们来澄清下这个执行计划的顺序，比如下面这一小段：

image
image1338×118 22.4 KB

这个的执行顺序是：21、22、23

21：tikv 层是使用这个联合索引进行扫描

22：tikv 层在扫描时筛选出来满足查询条件的数据

23：index reader 是 tidb 层的算子

3、我猜是因为表分区 列 加入到了主键中，走了索引， 但是为什么不是走的主键索引，而是联合索引呢？

整个执行计划的选择是优化器选择的结果，TiDB 的优化器是 CBO，与上面的例子中是否包括主键没有直接关系。具体执行计划的选择可以参考问题 1 的回复，以及官网关于优化器相关的文档：

4

优化器相关文档