多列索引优化最佳实践

在当今数据驱动的世界中，高效处理大数据集上的复杂查询对于保持应用响应和性能至关重要。对于 TiDB 这样专为高规模、高需求环境设计的分布式 SQL 数据库来说，优化数据访问路径是实现高效查询的关键。

索引是提升查询性能的重要工具，可以避免全表扫描。TiDB 的查询优化器能够利用多列索引 (Multi-Column Indexes) 智能过滤数据，处理复杂的查询条件，这在传统数据库（如 MySQL）中往往难以实现。

本文将介绍多列索引的工作原理、重要性，以及 TiDB 如何将复杂的查询条件优化为高效的数据访问路径。通过这些优化，即使在大规模场景下，你也能获得更快的响应速度、最小化的表扫描，以及更流畅的性能。

如果没有这些优化，大型 TiDB 数据库中的查询性能可能会迅速下降。全表扫描和低效过滤会让毫秒级的查询变成分钟级，内存消耗过大还可能导致内存溢出 (Out of Memory, OOM) 错误，尤其是在资源受限的环境下。TiDB 的针对性优化方式确保只访问相关数据，从而保持低延迟和高效的内存使用，即使面对最复杂的查询也能应对自如。

前提条件

多列索引功能在 TiDB v8.3.0 及以上版本可用。
使用该功能前，需将优化器 Fix Control 54337 设置为 ON。

背景：多列索引

本文以一个租房信息表为例，每条记录包含唯一 ID、城市、卧室数、租金和可入住日期：

CREATE TABLE listings (
    listing_id INT PRIMARY KEY AUTO_INCREMENT,
    city VARCHAR(100) NOT NULL,
    bedrooms INT NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    availability_date DATE NOT NULL
);

假设该表在全中国有 2000 万条房源。如果你想查找租金低于 2000 元的房源，可以在 price 列上建索引。这样优化器只需扫描 [-inf, 2000.00) 范围的数据，假设 70% 房源高于 2000 元，实际扫描量约为 1400 万行。执行计划如下：

-- 查询 1：查找租金低于 2000 的房源
EXPLAIN FORMAT = "brief" SELECT * FROM listings WHERE price < 2000;

+-----------------------------+---------+----------------------------------------------+---------------------------+
| id                          | task    | access object                                | operator info             |
+-----------------------------+---------+----------------------------------------------+---------------------------+
| IndexLookUp                 | root    |                                              |                           |
| ├─IndexRangeScan(Build)     | root    | table: listings, index: price_idx(price)     | range: [-inf, 2000.00)    |
| └─TableRowIDScan(Probe)     | root    | table: listings                              |                           |
+-----------------------------+---------+----------------------------------------------+---------------------------+

虽然这样能提升性能，但返回的结果仍然数量庞大。若你需要更精确的房源，可以增加过滤条件，如指定城市、卧室数和最高租金。例如，查找北京两居室且租金低于 2000 元的房源，结果会大大缩小，可能只剩几十条。

为优化此查询，可以在 city、bedrooms 和 price 上创建一个多列索引：

CREATE INDEX idx_city_bedrooms_price ON listings (city, bedrooms, price);

SQL 中的多列索引按字典序排序。以 (city, bedrooms, price) 为例，数据先按 city 排序，再在每个 city 内按 bedrooms 排序，最后在每个 (city, bedrooms) 内按 price 排序。这样 TiDB 能高效利用每个条件：

先按 city 过滤；
再按 bedrooms 过滤；
最后按 price 过滤。

示例数据

下表展示了多列索引如何细化搜索结果：

City	Bedrooms	Price
Beijing	1	1000
Beijing	1	1500
Beijing	2	1000
Beijing	2	1500
Beijing	3	2500
Beijing	3	3000
Shanghai	1	1000
Shanghai	1	1500
Shanghai	2	1000
Shanghai	2	2500
Shanghai	3	1000
Shanghai	3	2500

优化查询与结果

利用多列索引，TiDB 能高效定位北京两居室且租金低于 2000 元的房源：

-- 查询 2：查找北京两居室且租金低于 2000 的房源
EXPLAIN FORMAT = "brief"
    SELECT * FROM listings
    WHERE city = 'Beijing' AND bedrooms = 2 AND price < 2000;

+------------------------+------+---------------------------------------------------------------------------------------------+---------------------------------+
| id                     | task | access object                                                                               | operator info                   |
+------------------------+------+---------------------------------------------------------------------------------------------+---------------------------------+
| IndexLookUp            | root |                                                                                             |                                 |
| ├─IndexRangeScan(Build)| root |table:listings,index:idx_city_bedrooms_price ["Beijing" 2 -inf,(city, bedrooms, price)]|range:["Beijing" 2 2000.00)|
| └─TableRowIDScan(Probe)| root |table:listings                                                                               |                                 |
+------------------------+------+---------------------------------------------------------------------------------------------+---------------------------------+

该查询在示例数据中返回：

City	Bedrooms	Price
Beijing	2	1000
Beijing	2	1500

通过多列索引，TiDB 避免了不必要的行扫描，大幅提升查询性能。

索引范围推导 (Index Range Derivation)

TiDB 优化器内置了强大的范围推导组件。该组件会根据查询条件和相关索引列，生成高效的索引范围，并传递给表访问组件，来决定最优的数据访问方式。

对于查询中的每个表，表访问组件会评估所有可用索引，以确定最佳的访问方式，无论是全表扫描还是索引扫描。它会计算每个相关索引的范围，评估访问代价，选择代价最低的路径。这一过程结合了范围推导和代价评估，确保数据检索既高效又节省资源。

下图展示了 TiDB 如何通过范围推导和代价评估协同工作，以选择最优的表访问路径：

表访问路径选择

多列过滤条件往往比上述示例更复杂，可能包含 AND、OR 或两者组合。TiDB 的范围推导子系统能高效处理这些情况，生成最具选择性或最有效的索引范围。

一般来说，该子系统会对 OR 条件生成的范围执行 UNION 操作，对 AND 条件生成的范围执行 INTERSECT 操作。通过这种方式，TiDB 即使在面对复杂的过滤逻辑时，也能尽可能精确地筛选数据。

多列索引中的析取条件（`OR` 条件）

当查询中包含 OR 条件（Disjunctive Predicates，析取谓词）时，优化器会分别处理每个条件，为每部分生成范围。如果范围有重叠，则合并为一个连续范围；否则保留为多个独立范围，均可用于索引扫描。

示例 1：重叠范围

假设要查找杭州两居室，租金在以下两个重叠区间的房源：

租金在 1000~2000 元之间
租金在 1500~2500 元之间

优化器会将两个区间合并为 1000 ~ 2500。查询及执行计划如下：

-- 查询 3：重叠租金区间
EXPLAIN FORMAT = "brief"
    SELECT * FROM listings
    WHERE (city = 'Hangzhou' AND bedrooms = 2 AND price >= 1000 AND price < 2000)
       OR (city = 'Hangzhou' AND bedrooms = 2 AND price >= 1500 AND price < 2500);

+-------------------------+------+----------------------------------------------------------------------+--------------------------------------------------+
| id                      | task | access object                                                        | operator info                                    |
+-------------------------+------+----------------------------------------------------------------------+--------------------------------------------------+
| IndexLookUp             | root |                                                                      |                                                  |
| ├─IndexRangeScan(Build) | root | table:listings,index:idx_city_bedrooms_price(city, bedrooms, price)  | range:["Hangzhou" 2 1000.00,"Hangzhou" 2 2500.00)|
| └─TableRowIDScan(Probe) | root | table:listings                                                       |                                                  |
+-------------------------+------+----------------------------------------------------------------------+--------------------------------------------------+

示例 2：不重叠范围

再比如查找北京或上海的一居室，分别在不同租金区间：

北京一居室，租金 1500 ~ 2500
上海一居室，租金 1000 ~ 1500

由于区间不重叠，执行计划中会保留两个独立范围：

-- 查询 4：不同城市的不重叠区间

EXPLAIN FORMAT = "brief"
    SELECT * FROM listings
    WHERE
        (city = 'Beijing' AND bedrooms = 1 AND price >= 1500 AND price < 2500)
     OR (city = 'Shanghai' AND bedrooms = 1 AND price >= 1000 AND price < 1500);

+-------------------------+------+--------------------------------------------------------------------+------------------------------------------------------------+
| id                      | task | access object                                                      | operator info                                              |
+-------------------------+------+--------------------------------------------------------------------+------------------------------------------------------------+
| IndexLookUp             | root |                                                                    |                                                            |
| ├─IndexRangeScan(Build) | root | table:listings,index:idx_city_bedrooms_price(city, bedrooms, price)| range:["Beijing" 1 1500.00,"Beijing" 1 2500.00) ["Shanghai" 1 1000, "Shanghai" 1 1500) |
| └─TableRowIDScan(Probe) | root | table:listings                                                     |           |
+-------------------------+------+--------------------------------------------------------------------+------------------------------------------------------------+

通过合并或保留独立范围，优化器能高效利用索引处理 OR 条件，避免无谓扫描，提升性能。

多列索引中的合取条件（`AND` 条件）

对于 AND 条件（合取条件），TiDB 优化器会为每个条件生成范围，并取其交集 (INTERSECT)，得到最精确的索引访问范围。如果某条件包含多个范围，TiDB 会组合这些范围，确保结果最优。

示例 1：表结构

假设有如下表 t1：

CREATE TABLE t1 (
    a1 INT,
    b1 INT,
    c1 INT,
    KEY iab (a1,b1)
);

有如下查询条件：

(a1, b1) > (1, 10) AND (a1, b1) < (10, 20)

该查询涉及多列比较，TiDB 优化器的处理步骤如下：

拆解表达式。
- (a1, b1) > (1, 10) 转换为 (a1 > 1) OR (a1 = 1 AND b1 > 10)，表示包括所有 a1 大于 1 的情况，或 a1 等于 1 且 b1 大于 10 的情况。
- (a1, b1) < (10, 20) 转换为 (a1 < 10) OR (a1 = 10 AND b1 < 20)，表示包括所有 a1 小于 10 的情况，或 a1 等于 10 且 b1 小于 20 的情况。
合并后为：
```
((a1 > 1) OR (a1 = 1 AND b1 > 10)) AND ((a1 < 10) OR (a1 = 10 AND b1 < 20))
```
推导并组合范围。
- (a1, b1) > (1, 10)：推导出的范围包括 (1, +inf]（a1 > 1 的情况）和 (1, 10, 1, +inf]（a1 = 1 且 b1 > 10 的情况）。
- (a1, b1) < (10, 20)：推导出的范围包括 [-inf, 10)（a1 < 10 的情况）和 [10, -inf, 10, 20)（a1 = 10 且 b1 < 20 的情况）。
组合范围后，最终的索引范围为 (1, 10, 1, +inf] UNION (1, 10) UNION [10, -inf, 10, 20)。

示例 2：查询计划

查询计划如下：

-- 查询 5：多列合取条件
EXPLAIN FORMAT = "brief"
    SELECT * FROM t1
    WHERE (a1, b1) > (1, 10) AND (a1, b1) < (10, 20);

+-------------------------+------+----------------------------+-------------------------------------------+
| id                      | task | access object              | operator info                             |
+-------------------------+------+----------------------------+-------------------------------------------+
| IndexLookUp             | root |                            |                                           |
| ├─IndexRangeScan(Build) | root | table:t1,index:iab(a1, b1) | range:(1 10,1 +inf],(1,10)[10 -inf,10 20) |
| └─TableRowIDScan(Probe) | root | table:t1                   |                                           |
+-------------------------+------+----------------------------+-------------------------------------------+

假设表有 5 亿行，通过优化后只需访问约 4000 行，仅占总数据的 0.0008%。查询延迟从两分钟降至几毫秒。

与 MySQL 需全表扫描不同，TiDB 优化器可高效处理复杂行表达式，充分利用推导范围。

总结

TiDB 优化器通过多列索引和高级范围推导，可大幅降低复杂 SQL 查询的数据访问代价。无论是合取 (AND) 还是析取 (OR) 条件，TiDB 都能将行表达式转化为最优访问路径，缩短查询时间，提升性能。与 MySQL 不同，TiDB 支持多列索引上的并集与交集操作，能高效处理复杂过滤条件。在实际应用中，优化后查询可在几毫秒内完成，而未优化时可能需两分钟以上，极大降低了延迟。

更多 TiDB 与 MySQL 架构差异及其对可扩展性、可靠性和 HTAP 工作负载的影响，详见 MySQL vs. TiDB: A Guide to Open Source Database Selection。