本文介绍如何通过 SQL 命令来创建索引。
TDSQL PG 数据库采用的是聚集索引表模型，对于用户指定的主键，系统会自动生成主键索引。可以根据自身业务需要来决定在哪些字段上创建索引，以便加快在这些字段上的查询速度。
前置条件
- 拥有
CREATE权限。
索引的限制
- 在 TDSQL PG 数据库中，索引名称必须在表对应的 SCHEMA 内保证唯一。
- 索引名称的长度不能超过 63 字节 （超过63个字符表名称会被截断，保留前63个字符）。
- 在一个表中可以创建多个唯一索引，但是每个唯一索引所对应的列值都必须保持唯一。
- 如果一个表带shard key，那么唯一索引必须包含shard key。
索引使用的建议
- 索引名称应该包含对应表名与列名：
- 主键索引：pk_table_column
- 唯一索引：uk_table_column
- 普通索引：idx_table_column
- 单个表索引数量不宜太多，索引数量多会占用大量磁盘空间，同时索引过多，也会增加计算成本。
- 频繁更新的表列不宜建索引。表列的频繁更新会导致索引的频繁更新，增加维护索引成本。
- 选择经常用于查询且区分度高的表列进行创建索引。
创建索引
创建索引语法如下
CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ IF NOT EXISTS ] name ] ON table_name [ USING method ]
( { column_name | ( expression ) } [ COLLATE collation ] [ opclass ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] )
[ WITH ( storage_parameter = value [, ... ] ) ]
[ TABLESPACE tablespace_name ]
[ WHERE predicate ]
参数说明:
UNIQUE
系统会在索引被创建时（如果数据已经存在）或者插入数据时检查重复值。CONCURRENTLY
一般情况下，索引创建时会把表锁住以阻止对表的写（但不阻塞读），这种锁定会持续到索引创建完毕。当使用了这个选项时，TDSQL PG在构建索引时不会取得任何会阻止该表上并发插入、更新或者删除的锁。IF NOT EXISTS
当索引不存在时才创建，若存在则会给出提示跳过。若使用 IF NOT EXISTS，需要指定索引名。name
要创建的索引名称。这里不能包括模式名，因为索引总是被创建在其基表所在的模式中。若不指定索引名，TDSQL PG将根据表名以及被索引的列名自动分配一个索引名称。table_name
拥有该索引的表 （可以被模式限定）。method
索引类型。可以选择 btree、hash、gist、spgist、gin以及brin。 默认为btree。column_name
一个表列的名称。expression
一个基于一个或者更多个表列的表达式。collation
要用于该索引的排序规则。默认情况下，该索引使用被索引列的排序规则或者被索引表达式的结果排序规则。当查询涉及到使用非默认排序规则的表达式时，使用非默认排序规则的索引就能排上用场。opclass
一个操作符类的名称。ASC
指定上升排序（默认）。DESC
指定下降排序。NULLS FIRST
指定把空值排序在非空值前面。在指定DESC时， 这是默认行为。NULLS LAST
指定把空值排序在非空值后面。在没有指定DESC时， 这是默认行为。storage_parameter
索引方法相关的存储参数的名称。详见下方索引存储参数。tablespace_name
在其中创建索引的表空间。如果没有指定，将会使用 default_tablespace。或者对临时表上的索引使用 temp_tablespaces。predicate
部分索引的约束表达式。说明:可以使用
SELECT * FROM pg_indexes WHERE tablename = 'table_name';语句查看表中索引的信息。
示例
创建唯一索引
如果列上不存在重复的值，可以对该列创建唯一索引。使用以下 SQL 语句创建一个名为 t_test 的表，并为表 t_test 创建一个基于 col2 列的唯一索引。
创建表
t_test。tdsql=# CREATE TABLE t_test(col1 INT, col2 INT, col3 VARCHAR(50), PRIMARY KEY (col1, col2)) DISTRIBUTE BY SHARD(col2); CREATE TABLE在表
t_test上基于col2列创建一个名为idx_tbl1_col2的唯一索引。tdsql=# CREATE UNIQUE INDEX idx_tbl1_col2 ON t_test(col2); CREATE INDEX查看表
t_test索引信息。tdsql=# SELECT * FROM pg_indexes WHERE tablename = 't_test';返回结果如下：
schemaname | tablename | indexname | tablespace | indexdef ------------+-----------+---------------+------------+--------------------------------------------------------------------------- public | t_test | idx_tbl1_col2 | | CREATE UNIQUE INDEX idx_tbl1_col2 ON public.t_test USING btree (col2) public | t_test | t_test_pkey | | CREATE UNIQUE INDEX t_test_pkey ON public.t_test USING btree (col1, col2) (2 rows)使用索引扫描
tdsql=# insert into t_test select i, i from generate_series(1, 100000) i;
INSERT 0 100000
tdsql=# analyze t_test;
ANALYZE
tdsql=# explain select * from t_test where col2 = 1;
QUERY PLAN
------------------------------------------------------------------------------------
Remote Fast Query Execution
Node/s: dn001
-> Index Scan using idx_tbl1_col2 on t_test (cost=0.29..8.31 rows=1 width=126)
Index Cond: (col2 = 1)
(4 rows)
创建非唯一索引
使用以下 SQL 语句创建一个名为 t_test 的表，并为表 t_test 创建一个基于 col2 列的索引。
创建表
t_test。tdsql=# CREATE TABLE t_test(col1 INT, col2 INT, col3 VARCHAR(50), PRIMARY KEY (col1, col2)); CREATE TABLE在表
t_test上基于col2列创建一个名为idx_tbl2_col2的索引。tdsql=# CREATE INDEX idx_tbl2_col2 ON t_test(col2); CREATE INDEX查看表
t_test索引信息。tdsql=# SELECT * FROM pg_indexes WHERE tablename = 't_test';返回结果如下：
schemaname | tablename | indexname | tablespace | indexdef ------------+-----------+---------------+------------+--------------------------------------------------------------------------- public | t_test | idx_tbl2_col2 | | CREATE INDEX idx_tbl2_col2 ON public.t_test USING btree (col2) public | t_test | t_test_pkey | | CREATE UNIQUE INDEX t_test_pkey ON public.t_test USING btree (col1, col2) (2 rows)使用索引扫描
tdsql=# insert into t_test select i, i from generate_series(1,100000) i;
INSERT 0 100000
tdsql=# analyze t_test;
ANALYZE
tdsql=# explain select * from t_test where col2 = 1;
QUERY PLAN
------------------------------------------------------------------------------------
Remote Fast Query Execution
Node/s: dn001, dn002
-> Index Scan using idx_tbl2_col2 on t_test (cost=0.29..8.31 rows=1 width=126)
Index Cond: (col2 = 1)
(4 rows)
创建空间索引
空间索引是一种用于处理和优化空间数据的数据库索引。它被广泛应用于地理信息系统（GIS）和位置数据的存储和查询, TDSQL PG 支持的空间索引类型有：gist, spgist, brin。
使用以下 SQL 语句创建一个名为 t_test 的表，并为表 t_test 创建一个基于 c3 列的gist空间索引。
创建表
t_test。tdsql=# CREATE TABLE t_test(c1 int,c2 int, c3 box, c4 box); CREATE TABLE在表
t_test上基于c3列创建一个名为idx_test_c3的空间索引。tdsql=# CREATE INDEX idx_test_c3 on t_test USING gist(c3); CREATE INDEX查看表
t_test索引信息。tdsql=# SELECT * FROM pg_indexes WHERE tablename = 't_test';返回结果如下：
tdsql=# SELECT * FROM pg_indexes WHERE tablename = 't_test'; schemaname | tablename | indexname | tablespace | indexdef ------------+-----------+-------------+------------+----------------------------------------------------------- public | t_test | idx_test_c3 | | CREATE INDEX idx_test_c3 ON public.t_test USING gist (c3) (1 row)使用索引扫描
tdsql=# insert into t_test select i, i, box(point(0.05*i, 0.05*i), point(0.05*i, 0.05*i)) from generate_series(1,100000) i;
INSERT 0 100000
tdsql=# analyze t_test;
ANALYZE
tdsql=# explain select * from t_test where c3 <@ box(point(0,0), point(0.5, 0.5));
QUERY PLAN
---------------------------------------------------------------------------------
Remote Fast Query Execution
Node/s: dn001, dn002
-> Bitmap Heap Scan on t_test (cost=4.67..155.43 rows=50 width=72)
Recheck Cond: (c3 <@ '(0.5,0.5),(0,0)'::box)
-> Bitmap Index Scan on idx_test_c3 (cost=0.00..4.65 rows=50 width=0)
Index Cond: (c3 <@ '(0.5,0.5),(0,0)'::box)
(6 rows)
创建函数索引
基于表中一列或多列的值进行计算后的结果建立的索引称为函数索引。函数索引是一种优化技术，使用函数索引可以在查询时快速定位匹配的函数值，从而避免重复计算，提高查询效率。
使用以下 SQL 语句创建一个名为 t_test 的表，并为表 t_test 创建一个基于 c_time 列的函数索引。
创建表
t_test。tdsql=# CREATE TABLE t_test(id INT, name VARCHAR(18), c_time DATE); CREATE TABLE在表
t_test上创建一个名为idx_t_test_c_time的索引，该索引是基于c_time列的年份部分进行创建的。tdsql=# CREATE INDEX idx_t_test_c_time ON t_test(EXTRACT(YEAR FROM c_time)); CREATE INDEX使用下面 SQL 语句可以查看创建的函数索引。
tdsql=# SELECT * FROM pg_indexes WHERE tablename = 't_test';返回结果如下：
schemaname | tablename | indexname | tablespace | indexdef ------------+-----------+-------------------+------------+----------------------------------------------------------------------------------------------- public | t_test | idx_t_test_c_time | | CREATE INDEX idx_t_test_c_time ON public.t_test USING btree (date_part('year'::text, c_time)) (1 row)使用索引扫描
tdsql=# insert into t_test select i, 'name', '2021-1-1'::date + interval '1 day' from generate_series(1, 100000) i;
INSERT 0 100000
tdsql=# analyze t_test;
ANALYZE
tdsql=# explain select * from t_test where EXTRACT(YEAR FROM c_time) = 123;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------
Remote Fast Query Execution
Node/s: dn001, dn002
-> Index Scan using idx_t_test_c_time on t_test (cost=0.29..4.31 rows=1 width=13)
Index Cond: (date_part('year'::text, (c_time)::timestamp without time zone) = '123'::double precision)
(4 rows)