跳到主要内容
版本: 最新版本-3.5

前缀索引

在创建表时，指定一列或多列作为排序键。表中的数据行将基于排序键进行排序，然后存储在磁盘上。

在数据写入期间，会自动生成前缀索引。数据按照指定的排序键排序后，每 1024 行数据包含在一个逻辑数据块中。一个索引条目被添加到前缀索引表中，该索引条目由该逻辑数据块中第一行数据排序键列的值组成。

通过这两层排序结构，查询可以使用二分搜索快速跳过不符合查询条件的数据，也可以避免查询期间额外的排序操作。

提示

前缀索引是一个稀疏索引，其大小至少比数据量小 1024 倍。因此，它通常可以完全缓存在内存中，以加速查询性能。

使用说明​

自 v3.0 起，Primary Key 表支持使用 ORDER BY 定义排序键。 自 v3.3 起，Duplicate Key 表、Aggregate 表和 Unique Key 表支持使用 ORDER BY 定义排序键。

  • Duplicate Key 表中的数据按照排序键 ORDER BY 进行排序。排序键可以是任意列的组合。

    信息

    当同时指定 ORDER BY 和 DUPLICATE KEY 时，DUPLICATE KEY 不生效。

  • Aggregate 表中的数据首先根据聚合键 AGGREGATE KEY 进行聚合，然后根据排序键 ORDER BY 进行排序。ORDER BY 和 AGGREGATE KEY 中的列需要相同，但列的顺序不需要相同。

  • Unique Key 表中的数据首先根据唯一键 UNIQUE KEY 进行替换，然后根据排序键 ORDER BY 进行排序。ORDER BY 和 UNIQUE KEY 中的列需要相同，但列的顺序不需要相同。

  • Primary Key 表中的数据首先根据主键 PRIMARY KEY 进行替换，然后根据排序键 ORDER BY 进行排序。

以 Duplicate Key 表为例。排序键使用 ORDER BY 定义为 uid 和 name。

CREATE TABLE user_access (
    uid int,
    name varchar(64),
    age int, 
    phone varchar(16),
    last_access datetime,
    credits double
)
ORDER BY (uid, name);
提示

创建表后，您可以使用 SHOW CREATE TABLE <table_name>; 从返回结果中查看指定的排序列以及这些列在 ORDER BY 子句中的顺序。

由于前缀索引条目的最大长度为 36 字节，因此超出部分将被截断。因此，该表中前缀索引的每个条目为 uid (4 字节) + name (仅取前 32 字节)，前缀字段为 uid 和 name。

注意

  • 前缀字段的数量不能超过 3 个，前缀索引条目的最大长度为 36 字节。

  • 在前缀字段中，CHAR、VARCHAR 或 STRING 类型的列只能出现一次，并且必须在末尾。

    以下表为例，前三列是排序键列。该表的前缀字段为 name (20 字节)。这是因为此前缀索引以 VARCHAR 类型的列 (name) 开头，并且即使前缀索引条目的长度未达到 36 字节，也会直接截断而不包括更多列。因此，此前缀索引仅包含 name 字段。

    MySQL [example_db]> describe user_access2;
    +-------------+-------------+------+-------+---------+-------+
    | Field       | Type        | Null | Key   | Default | Extra |
    +-------------+-------------+------+-------+---------+-------+
    | name        | varchar(20) | YES  | true  | NULL    |       |
    | uid         | int         | YES  | true  | NULL    |       |
    | last_access | datetime    | YES  | true  | NULL    |       |
    | age         | int         | YES  | false | NULL    |       |
    | phone       | varchar(16) | YES  | false | NULL    |       |
    | credits     | double      | YES  | false | NULL    |       |
    +-------------+-------------+------+-------+---------+-------+
    6 rows in set (0.00 sec)

  • 如果在表中通过 ORDER BY 指定了排序键，则前缀索引将基于排序键形成。如果没有使用 ORDER BY 指定排序键，则前缀索引将基于 Key 列形成。

如何合理设计排序键以形成可以加速查询的前缀索引​

对业务场景中的查询和数据进行分析，有助于选择合适的排序键列，并以正确的顺序排列它们以形成前缀索引，从而显着提高查询性能。

  • 排序键列的数量通常为 3 个，不建议超过 4 个。具有过多列的排序键不能提高查询性能，反而会增加数据加载期间的排序开销。
  • 建议按以下顺序优先选择列以形成排序键
    1. 选择在查询过滤条件中经常使用的列作为排序键列。 如果排序键列的数量不止一个，则按它们在查询过滤条件中出现的频率降序排列。这样，如果查询过滤条件包含前缀索引的前缀，则可以显着提高查询性能。如果过滤条件包含前缀索引的整个前缀，则查询可以充分利用前缀索引。当然，只要过滤条件包含前缀，即使不是整个前缀，前缀索引仍然可以优化查询。但是，如果过滤条件中包含的前缀长度太短，则前缀索引的效果会减弱。仍然以 Unique Key 表 为例，其排序键为 (uid,name)。如果查询过滤条件包含整个前缀，例如 select sum(credits) from user_access where uid = 123 and name = 'Jane Smith';，则查询可以充分利用前缀索引来提高性能。如果查询条件仅包含前缀的一部分，例如 select sum(credits) from user_access where uid = 123;，则查询也可以从前缀索引中受益以提高性能。但是，如果查询条件不包含前缀，例如 select sum(credits) from user_access where name = 'Jane Smith';，则查询无法使用前缀索引来加速。

    • 如果多个排序键列作为查询过滤条件的频率相似，则可以衡量这些列的基数。

    • 如果列的基数较高，则可以在查询期间过滤更多数据。如果基数太低，例如对于布尔类型列，其过滤效果不理想。

      提示

      但是，考虑到实际业务场景中的查询特征，通常，基数略低的列比高基数列更常被用作查询条件。这是因为频繁基于高基数列进行过滤的查询，甚至在某些极端情况下，基于具有 UNIQUE 约束的列进行过滤的查询，更像是 OLTP 数据库中的点查询，而不是 OLAP 数据库中的复杂分析查询。

    • 另外，还要考虑存储压缩因素。如果低基数列和高基数列的顺序之间的查询性能差异不明显，则将低基数列放在高基数列之前将导致排序后的低基数列具有更高的存储压缩率。因此，建议将低基数列放在前面。

在创建表时定义排序键列的注意事项​

  • 排序列的数据类型

    • Primary Key 表中的排序列支持数值类型（包括整数、布尔值）、字符串和日期/日期时间类型。
    • Duplicate Key 表、Aggregate 表和 Unique Key 表中的排序列支持数值类型（包括整数、布尔值、小数）、字符串和日期/日期时间类型。

  • 在 Aggregate 表和 Unique Key 表中，必须在其他列之前定义排序列。

前缀索引可以修改吗？​

如果业务场景中的查询特征发生变化，并且查询过滤条件中经常使用前缀字段以外的列，则现有的前缀索引无法过滤数据，并且查询性能可能不理想。

自 v3.0 起，可以修改 Primary Key 表的排序键。自 v3.3 起，可以修改 Duplicate Key 表、Aggregate 表和 Unique Key 表的排序键。Duplicate Key 表和 Primary Key 表中的排序键可以是任何排序列的组合。Aggregate 表和 Unique Key 表中的排序键必须包含所有 Key 列，但这些列的顺序不需要与 Key 列一致。

或者，您还可以基于此表创建同步物化视图，并选择其他常用作条件列的列以形成前缀索引，这可以提高这些查询的性能。但请注意，这会增加存储空间。

如何验证前缀索引是否加速查询​

执行查询后，您可以从 Query Profile 中扫描节点的详细指标（例如 ShortKeyFilterRows）检查前缀索引是否生效并查看其过滤效果。