postgresql 表达式索引

本文介绍了如何通过创建特定类型的索引来优化SQL查询性能,包括表达式索引、前缀匹配索引、后缀匹配索引和大小写不敏感索引。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

表达式索引:根据业务特点及场景去建立

例1:

比如说,密码存储的是md5(id || username)的值,就可以这么建立索引:

    select id from user where passwd = md5(id || username);

这个时候我们就可以建立索引去加快查询:

    create index on user(md5(id || username));

例2:

我们前缀匹配字符串 123456;

    select name from user where name like '123456%';

则可以这建立索引:

create index on user( name varchar_pattern_ops);

例3:

我们后缀匹配字符串 123456;

    select name from user where name like '%123456';

则可以建立索引:

    create index on user( reverse(name) varchar_pattern_ops);
   

例4:

忽略大小写匹配一个指定的值:

    select * from user WHERE lower(name) = 'value';

则可以建立索引:

    create index on user(lower(name));

创建完成后执行一下原sql, 比较一下前后执行时间;
或者利用explain analyze 查看一下执行计划又没有使用到新建的索引。

<think>我们正在回答用户关于PostgreSQL创建索引语法的问题。用户已经提供了三个引用,其中引用[1]包含了基本的创建索引语法,引用[2]详细描述了创建索引的流程和锁分析,引用[3]给出了唯一索引的示例。 根据引用[1],创建索引的基本语法如下: 1. 普通索引:`CREATE INDEX index_name ON table_name;` 2. 组合索引:`CREATE INDEX index_name ON table_name (column1_name, column2_name);` 3. 唯一索引:`CREATE UNIQUE INDEX index_name on table_name (column_name);` 4. 删除索引:`DROP INDEX index_name;` 引用[3]补充了唯一索引的示例:`CREATE UNIQUE INDEX idx_unique_email ON employees (email);` 此外,我们还需要注意,索引可以指定类型(如B-tree, Hash, GIN等),默认是B-tree。语法为: `CREATE [UNIQUE] INDEX index_name ON table_name [USING method] (column_name [ASC | DESC] [NULLS {FIRST | LAST}], ...);` 其中: - `method`:索引方法,如btree, hash, gist, spgist, gin, brin等。 - `ASC | DESC`:指定排序顺序(默认ASC)。 - `NULLS FIRST | NULLS LAST`:指定空值的排序位置。 另外,还可以创建部分索引(带条件的索引)和表达式索引: - 部分索引:`CREATE INDEX index_name ON table_name (column_name) WHERE condition;` - 表达式索引:`CREATE INDEX index_name ON table_name (expression);` 因此,我们将整理一个完整的创建索引语法说明。 注意:用户的问题中并没有要求详细说明创建索引的流程和锁,所以我们的回答集中在语法上。但我们可以简要提及创建索引的锁问题(引用[2]提到创建索引会加锁),因为这对生产环境很重要,尤其是在大表上创建索引可能会长时间锁表,可以使用`CONCURRENTLY`选项避免锁表。 并发创建索引的语法: `CREATE INDEX CONCURRENTLY index_name ON table_name (column_name);` 最后,我们按照要求生成相关问题。</think>### PostgreSQL 创建索引语法详解 #### 一、基础语法 ```sql -- 基本索引 CREATE INDEX index_name ON table_name (column_name); -- 组合索引(多列) CREATE INDEX index_name ON table_name (column1, column2, ...); -- 唯一索引 CREATE UNIQUE INDEX index_name ON table_name (column_name); ``` #### 二、高级语法选项 1. **指定索引类型** ```sql CREATE INDEX index_name ON table_name USING btree (column); -- B-tree(默认) CREATE INDEX index_name ON table_name USING gin (jsonb_column); -- GIN CREATE INDEX index_name ON table_name USING brin (timestamp_column); -- BRIN ``` 2. **部分索引(条件索引)** ```sql CREATE INDEX idx_active_users ON users (id) WHERE is_active = true; ``` 3. **表达式索引** ```sql CREATE INDEX idx_lower_email ON users (LOWER(email)); ``` 4. **并发创建(避免锁表)** ```sql CREATE INDEX CONCURRENTLY index_name ON large_table (column); ``` 5. **排序控制** ```sql CREATE INDEX idx_desc ON orders (order_date DESC); ``` #### 三、索引管理 1. **删除索引** ```sql DROP INDEX IF EXISTS index_name; ``` 2. **重命名索引** ```sql ALTER INDEX old_name RENAME TO new_name; ``` 3. **重建索引** ```sql REINDEX INDEX index_name; -- 单索引 REINDEX TABLE table_name; -- 整表索引 ``` #### 四、创建流程与锁机制 1. **创建流程** - 语法解析 → 元数据校验 → 文件创建 → 元数据写入 → B-tree构建[^2] 2. **锁级别** | 操作 | 锁类型 | 影响 | |------|--------|------| | 普通创建 | `SHARE`锁 | 阻塞写操作 | | 并发创建 | `SHARE UPDATE EXCLUSIVE` | 允许读写[^2] | #### 五、最佳实践示例 ```sql -- 创建组合唯一索引 CREATE UNIQUE INDEX idx_user_email ON users (last_name, first_name, email); -- 时间序列表的BRIN索引 CREATE INDEX idx_log_time ON server_logs USING brin (log_time) WITH (pages_per_range = 128); -- JSONB字段的GIN索引 CREATE INDEX idx_metadata ON orders USING gin (metadata); ``` #### 六、注意事项 1. **命名规范**:`idx_<表名>_<列名>`(如`idx_orders_date`) 2. **大表操作**:使用`CONCURRENTLY`避免业务中断 3. **索引膨胀**:定期`REINDEX`或使用`pg_repack` 4. **性能监控**: ```sql SELECT * FROM pg_stat_all_indexes WHERE relname = 'your_table'; ``` [^1]: 基础索引操作语法规范 [^2]: 索引创建流程与锁机制解析 [^3]: 唯一索引应用场景示例 --- ### 相关问题 1. 如何查看 PostgreSQL 索引的使用效率统计? 2. 并发创建索引(`CONCURRENTLY`)有哪些潜在风险? 3. BRIN 索引的`pages_per_range`参数如何影响查询性能? 4. 表达式索引在哪些场景下比普通索引更高效? 5. 如何解决 GIN 索引更新导致的写放大问题?
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值