openGauss每日一练第 17 天-索引管理

1. 索引

1.1 索引的概念

索引是一个指向表中数据的指针。一个数据库中的索引与一本书的索引目录是非常相似的。
索引可以用来提高数据库查询性能，但是不恰当的使用将导致数据库性能下降。建议仅在匹配如下某条原则时创建索引：

• 经常执行查询的字段。
• 在连接条件上创建索引，对于存在多字段连接的查询，建议在这些字段上建立组合索引
  例如 select * from t1 join t2 on t1.a=t2.a and t1.b=t2.b可以在t1表上的a，b字段上建立组合索引。
• WHERE子句的过滤条件字段上（尤其是范围条件）, 在经常出现在ORDER BY、GROUP BY和DISTINCT后的字段。

1.2 索引的分类

• 单列索引 基于一个表的一列来创建

  creat index index_name on table_name (column_name);
  复制

• 组合索引 基于一个表的多列来创建

  creat index index_name on table_name (column_name1, column_name2...)
  复制

• 唯一索引 指定唯一索引的字段不允许重复值插入，就像加了一个唯一约束

  creat unique index index_name on table_name (column_name1);
  复制

• 局部索引 在表的子集上创建索引，子集由一个条件表达式确定是一列还是多列

  creat index index_name on table_name (expression);
  复制

• 部分索引 只包含表中一部分记录的索引， 通常是比其他部分更有用的部分

  creat index index_name on table_name (column_name) where字句
  复制

2. Explain语句

显示SQL语句的执行计划。执行计划将显示SQL语句所引用的表会采用什么样的扫描方式如：简单的顺序扫描、索引扫描等。如果引用了多个表，执行计划还会显示用到的JOIN算法
执行计划的最关键的部分是语句的预计执行开销，这是计划生成器估算执行该语句将花费多长的时间。
若指定了ANALYZE选项，则该语句会被执行，然后根据实际的运行结果显示统计数据，包括每个计划节点内时间总开销（毫秒为单位）和实际返回的总行数。这对于判断计划生成器的估计是否接近现实非常有用。

explain analyze select * from test;
Seq Scan on test  (cost=0.00..31.49 rows=2149 width=8)(actual time=0.002..0.002 rows=0 loops=1)
复制

• analyze选项表明要执行该语句
• Seq Scan 表明按照连续的方法扫描

3. Plan Hint调优

Plan Hint为用户提供了直接影响执行计划生成的手段，用户可以通过指定join顺序，join、scan方法，指定结果行数等多个手段来进行执行计划的调优，以提升查询的性能。

scan 方式的调优，可以是tablescan、indexscan和indexonlyscan。

语法格式
[no] tablescan|indexscan|indexonlyscan(table [index])
复制

• no 表示hint指定的scan方法不可用
• table 表示hint指定的表
• index 表示索引名称

-- hint语句
select /*+ <plan hint> */ * from test;
复制

4. 每日sql语句

-- explain 语句
explain select * from test;
explain select /*+ indexscan(test id) */ * from test;
复制

-- hint 使用索引
select /*+ indexscan(test id) */ * from test;
复制

-- 重命名索引
alter index idx_test rename to idx_test_new;
复制

-- 重建索引
alter index index_test rebuild;
reindex index index_test;
-- 重建所有索引
reindex table test;
复制

-- 移动索引到其他表空间
alter index idx_test set tablespace test_tb;
复制