tidb 如何减少表扫描？

🪐 TiDB 技术问题
7

举个例子

SELECT * FROM bbc
WHERE bbc.biz_type='ER'
AND bbc.biz_no='930'
;

SELECT * FROM bbc
WHERE bbc.biz_type='ER'
AND bbc.bid BASIC_INFO_NO='930'
;

-- 为什么是表扫描，而不是 index merge
SELECT * FROM bbc
WHERE
bbc.biz_type='ER'
AND
bbc.biz_no='930'
OR bbc.bid BASIC_INFO_NO='930'
)

表：`bbc`，记数率 44.6万
CREATE TABLE `bbc` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `biz_type` varchar(32) COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `biz_no` varchar(36) COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `bid_basic_info_no` varchar(50) COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_bid_change` (`bid_change_no`),
  KEY `IX_biz_type_biz_no` (`biz_type`,`biz_no`),
  KEY `IX_bid_no` (`bid_basic_info_no`),
  KEY `IX_biz_no` (`biz_no`),
  KEY `IX_biz_type_bid_basic_info_no` (`biz_type`, `bid_basic_info_no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci AUTO_INCREMENT=592050 COMMENT='招标变更'

image
image1032×421 107 KB

将 OR 拆成两个查询，都能走索引，而且返回的记录数都很少（实际执行也是如此），但写成OR条件之后，就变成表扫描了，絮棉按成本评估，应该是 index-merge 更优才对