YugabyteDB YSQL中的表达式索引深度解析-优快云博客

本文链接：https://blog.youkuaiyun.com/gitblog_01108/article/details/148440719

YugabyteDB YSQL中的表达式索引深度解析

yugabyte-db yugabyte/yugabyte-db: 是 YugaByte DB 的官方仓库，一个高性能、高可扩展、分布式的 SQL 数据库，支持 PostgreSQL 兼容性。适合对分布式数据库、SQL 数据库和云原生应用的开发者。项目地址: https://gitcode.com/gh_mirrors/yu/yugabyte-db

什么是表达式索引

表达式索引（也称为函数索引）是一种特殊类型的数据库索引，它不是在表的列上直接创建索引，而是在对列值进行某种计算或转换后的表达式结果上建立索引。与传统索引相比，表达式索引为复杂查询条件提供了更高效的访问路径。

在YugabyteDB的YSQL兼容层中，表达式索引的实现遵循PostgreSQL的标准，为开发者提供了强大的查询优化能力。

表达式索引的核心价值

大小写不敏感查询优化：如用户邮箱的查询场景，原始数据保留大小写格式，但查询时需要忽略大小写
数学计算优化：对数值列进行数学运算后的结果建立索引
日期处理优化：如提取日期中的年份或月份进行索引
JSON/JSONB字段访问：对JSON文档中特定路径的值建立索引
字符串处理：如对字符串进行截取、连接等操作后的结果建立索引

创建表达式索引的语法详解

基本语法格式如下：

CREATE INDEX index_name ON table_name( (expression) );

其中：

index_name 是自定义的索引名称
table_name 是目标表名
expression 是包含表列的任意有效表达式

当表达式是简单函数调用时，可以省略外层括号：

CREATE INDEX idx_lower_name ON users(LOWER(name));

实战案例：员工部门查询优化

假设我们有一个员工表employees，其中包含department字段存储部门名称。业务需求要求支持不区分大小写的部门查询。

初始查询分析

首先我们分析没有表达式索引时的查询计划：

EXPLAIN SELECT * FROM employees
  WHERE LOWER(department) = 'operations';

输出结果显示数据库执行了全表扫描（Seq Scan），这对于大表来说性能会很差：

                      QUERY PLAN
-------------------------------------------------------
 Seq Scan on employees  (cost=0.00..105.00 rows=1000 width=68)
  Filter: (lower(department) = 'operations'::text)
(2 rows)

创建表达式索引

我们为小写的部门名称创建表达式索引：

CREATE INDEX index_employees_department_lc
  ON employees(LOWER(department));

索引效果验证

再次执行相同的EXPLAIN命令：

EXPLAIN SELECT * FROM employees
  WHERE LOWER(department) = 'operations';

现在查询计划显示使用了我们创建的表达式索引，性能显著提升：

                                        QUERY PLAN
-----------------------------------------------------------------------------------------
 Index Scan using index_employees_department_lc on employees  (cost=0.00..5.25 rows=10 width=68)
  Index Cond: (lower(department) = 'operations'::text)