索引

简介

索引是数据库中用于加速数据检索的一种数据结构，通过创建指向表中数据的指针，使得数据库可以快速定位和访问特定的行，而无需扫描整个表。索引类似于书籍的目录，帮助用户快速定位所需要的内容，从而显著提高查询性能。虽然索引可以提高数据访问速度，但同时也增加了插入、更新、和删除操作的处理时间。所以是否要为表创建索引、索引建立在哪些字段上，是创建索引前必须要考虑的问题。

索引类型

• Btree：使用一种类似于B+tree的结构来存储数据的键值，叶节点按键值顺序存储数据。适用于等值查询、范围查询、排序和分组的操作。
• UBtree：仅供Ustore表使用的多版本Btree索引，索引页面上包含事务信息，并能自主回收页面。Ubtree索引默认开启insertpt功能。
• GIN：通用倒排索引，可用于处理索引项为组合值的情况，如数组或者JSONB。
• UGIN：通用倒排索引，一种仅供Ustore表使用的GIN索引。
• GIST：适用于几何和地理等多维数据类型和集合数据类型。目前支持的数据类型包括box、point、poly、circle、tsvector、tsquery和range。

示例

• 单列索引
  基于单个列创建的索引，适用于简单的查询条件。

  --创建表。
  gaussdb=# CREATE TABLE tb_t1(c1 int, c2 int);
  
  --创建索引。
  gaussdb=# CREATE INDEX idx_t1_c1 ON tb_t1(c1);
  
  --删除表。
  gaussdb=# DROP TABLE tb_t1;

• 多列索引
  基于多个列的组合索引，适用于多条件查询。

  --创建表。
  gaussdb=# CREATE TABLE tb_t2(c1 int, c2 int, c3 int);
  
  --创建索引。
  gaussdb=# CREATE INDEX idx_t2_c1_c2 ON tb_t2(c1,c2);
  
  --删除表。
  gaussdb=# DROP TABLE tb_t2;

• 唯一索引
  确保索引列唯一，同时允许NULL值。为表添加唯一约束时，数据库自动为表添加唯一索引。

  --创建表。
  gaussdb=# CREATE TABLE tb_userinfo(uid char(5), name varchar(20));
  
  --创建唯一索引。
  gaussdb=# CREATE UNIQUE INDEX idx_userinfo_uid ON tb_userinfo(uid);
  
  --删除表。
  gaussdb=# DROP TABLE tb_userinfo;

• 表达式索引
  表达式索引是一种基于计算表达式或函数结果创建的索引，而非直接基于表中的原始列。它允许对一些计算或转换后的值进行快速检索，适用于查询条件中包含函数或表达式的情况。

  --创建表及插入数据。
  gaussdb=# CREATE TABLE tb_test(c1 varchar,c2 varchar);
  gaussdb=# INSERT INTO tb_test VALUES ('AAa','AAA12');
  gaussdb=# INSERT INTO tb_test VALUES ('ABa','AAA13');
  gaussdb=# INSERT INTO tb_test VALUES ('AAc','AAA14');
  
  --创建索引。
  gaussdb=# CREATE INDEX idx_test_c1_lower ON tb_test(lower(c1));
  
  --查询。
  gaussdb=# SELECT * FROM tb_test WHERE lower(c1) = 'aac';
  
  --删除表。
  gaussdb=# DROP TABLE tb_test;

• 部分索引
  部分索引仅对表中满足特定条件的数据建立索引，从而减少索引体积，提升写入的性能。

  --创建表及插入数据。
  gaussdb=# CREATE TABLE tb_users(uid varchar, name varchar, is_active boolean);
  gaussdb=# INSERT INTO tb_users VALUES('bc'||generate_series(1,100),'uname'||generate_series(1,100),true);
  gaussdb=# INSERT INTO tb_users VALUES('ac'||generate_series(1,50),'uname'||generate_series(1,50),false);
  
  --创建索引。
  gaussdb=# CREATE INDEX idx_users_uid_act ON tb_users(uid) WHERE is_active = true;
  
  --查询时自动利用部分索引。
  gaussdb=# SELECT * FROM tb_users WHERE uid = 'bc001' AND is_active = true;
  
  --删除表。
  gaussdb=# DROP TABLE tb_users;

• 分区索引

  --创建表。
  gaussdb=# CREATE TABLE student(id int, name varchar(20)) PARTITION BY RANGE (id) (
      PARTITION p1 VALUES LESS THAN (200),
      PARTITION pmax VALUES LESS THAN (MAXVALUE)
  );
  
  --创建LOCAL分区索引不指定索引分区的名称。
  gaussdb=# CREATE INDEX idx_student1 ON student(id) LOCAL;
  
  --查看索引分区信息，发现LOCAL索引分区数和表的分区数一致。
  gaussdb=# SELECT relname FROM pg_partition WHERE parentid = 'idx_student1'::regclass;
     relname   
  -------------
   p1_id_idx
   pmax_id_idx
  (2 rows)
  
  --删除LOCAL分区索引。
  gaussdb=# DROP INDEX idx_student1;
  
  --创建GLOBAL索引。
  gaussdb=# CREATE INDEX idx_student2 ON student(id) GLOBAL;
  
  --查看索引分区信息，发现GLOBAL索引分区数和表的分区数不一致。
  gaussdb=# SELECT relname FROM pg_partition WHERE parentid = 'idx_student2'::regclass;
   relname 
  ---------
  (0 rows)
  
  --删除GLOBAL索引。
  gaussdb=# DROP INDEX idx_student2;
  
  --创建LOCAL表达式索引，不指定索引分区的名称。
  gaussdb=# CREATE INDEX idx_student3 ON student(abs(id)) LOCAL;
  
  --查看索引分区信息，发现LOCAL索引分区数和表的分区数一致。
  gaussdb=# SELECT relname FROM pg_partition WHERE parentid = 'idx_student3'::regclass;
     relname   
  -------------
   p1_abs_idx
   pmax_abs_idx
  (2 rows)
  
  --删除LOCAL分区表达式索引。
  gaussdb=# DROP INDEX idx_student3;
  
  --创建GLOBAL表达式索引。
  gaussdb=# CREATE INDEX idx_student4 ON student(abs(id)) GLOBAL;
  
  --查看索引分区信息，GLOBAL表达式索引分区数和表的分区数不一致。
  gaussdb=# SELECT relname FROM pg_partition WHERE parentid = 'idx_student4'::regclass;
   relname 
  ---------
  (0 rows)
  
  --删除GLOBAL表达式索引。
  gaussdb=# DROP INDEX idx_student4;
  
  --删除表。
  gaussdb=# DROP TABLE student;

• UGIN索引

  --创建表。
  gaussdb=# CREATE TABLE test_trgm(t varchar(100)) WITH (STORAGE_TYPE=USTORE);
  
  --插入数据。
  gaussdb=# INSERT INTO test_trgm VALUES('qwertyu0001');
  gaussdb=# INSERT INTO test_trgm VALUES('qwertyu0099');
  gaussdb=# INSERT INTO test_trgm VALUES('2024新年快乐7days');
  
  --创建UGIN索引。
  gaussdb=# create index trgm_idx on test_trgm using ugin (t ugin_trgm_ops);
  
  --LIKE查询语句。
  gaussdb=# select * from test_trgm where t like '%24新%';
  
  --删除。
  DROP TABLE test_trgm;

• GIN索引

  --创建表。
  gaussdb=# CREATE TABLE test_gin (id INT, info INT[]) WITH(STORAGE_TYPE = ASTORE);
  
  --创建索引。
  gaussdb=# CREATE INDEX test_gin_idx ON test_gin USING GIN(info);
  
  --插入数据。
  gaussdb=# INSERT INTO test_gin SELECT g, ARRAY[1, g % 5, g] FROM generate_series(1, 20000) g;
  
  --查询语句。
  gaussdb=# SELECT * FROM test_gin WHERE info @> '{2}' AND info @> '{22}' ORDER BY id, info;
  
  --删除表。
  gaussdb=# DROP TABLE test_gin;

• GiST索引

  --创建表。
  gaussdb=# CREATE TABLE gist_box_tbl(f1 point, f2 int)WITH(storage_type = astore);
  
  --创建索引。
  gaussdb=# CREATE INDEX gist_box_index ON gist_box_tbl USING gist(f1);
  
  --插入数据。
  gaussdb=# INSERT INTO gist_box_tbl VALUES(point(1,2),1);
  gaussdb=# INSERT INTO gist_box_tbl VALUES(point(3,4),3);
  
  --查询语句。
  gaussdb=# SELECT count(*) FROM gist_box_tbl WHERE f1 ~= point(1, 2);
  
  --删除表。
  gaussdb=# DROP TABLE gist_box_tbl;