文档中心

创建索引 ​

索引是数据库中重要的对象，利用索引对数据进行各种操作可以极大地提高系统性能，在数据定位方面表现非常突出。

索引可以为数据定位带来高性能、高效率，但并非表中创建的索引越多越好，因为利用索引提高查询效率是以额外占用存储空间为代价的，而且为了维护索引的有效性，当对表中数据进行操作时，数据库需要对索引维护。

索引设计可参考以下原则：

  • 在常用的数据定位列上创建索引（即WHERE子句中出现的列）。
  • 在表的主键、外键上创建索引（默认创建的有索引）。
  • 在经常用于表与表之间连接的字段上创建索引。
  • 查询几乎没有涉及到的列不纳入索引列。
  • 针对重复度过高的列不纳入索引列或不创建索引。
  • 当该表写的性能比查询的性能要求高时，应少建或者不建索引。

主要语法结构 ​

语法格式 ​

sql
IndexStmt::=
    CREATE [UNIQUE] [IF NOT EXISTS] INDEX index_name ON name_space ( index_elem [,index_elem]... )
    [index_type_opt]  [ftidx_opt]  [opt_idx_parti]  [opt_online] [parallel_opt2] [opt_wait] [opt_sort]

参数说明 ​

  • CREATE：关键字，用于创建索引。
  • UNIQUE：当指定参数UNIQUE时，系统将自动为表增加一个对应的唯一值约束，后续若要删除该索引，需通过删除对应约束的方式进行。
  • IF NOT EXISTS：当创建索引时存在同名索引则忽略此错误，该关键字无法验证已有同名索引与当前创建索引结构是否一致。
  • index_name：指定要创建的索引名称。
  • ON name_space ( index_elem [,index_elem]... )：指定索引所在的表或视图（name_space），以及索引的列（index_elem）。
  • [index_type_opt]：索引类型，通常用于指定自定义索引类型。
  • [ftidx_opt]：全文索引选项，用于创建全文索引。
  • [opt_idx_parti]：分区选项，用于创建分区索引。
  • [opt_online]：在线/离线模式，指定索引创建是否可以在数据库在线状态下进行。
  • [parallel_opt2]：并行处理选项，指定索引创建是否并行执行。
  • [opt_wait]：等待选项，指定索引创建是否等待其他操作完成。
  • [opt_sort]：排序选项，指定索引列的排序顺序（升序或降序）。

索引类型index_type_opt ​

语法格式 ​

sql
index_type_opt::=
    /*EMPTY*/
|   INDEXTYPE IS ColId

参数说明 ​

  • /*EMPTY*/：没有指定索引类型时，默认空。
  • INDEXTYPE IS ColId：指定索引类型。

全文索引ftidx_opt ​

语法格式 ​

sql
ftidx_opt::=
    /*EMPTY*/
|   full_index_opts

full_index_opts::=
    full_index_opt_item
|   full_index_opts full_index_opt_item

full_index_opt_item::=
    USING VOCABLE TABLE Sconst
|   FOR EVERY ICONST VOCABLE
|   USING FILTER Sconst
|   USING LEXER Sconst

参数说明 ​

  • /*EMPTY*/：没有指定全文索引选项时，默认空。
  • full_index_opt_item：指定全文索引的选项。
  • FILTER：文档过滤器。
  • VOCABLE：词表。
  • LEXER：语法分析器。

分区索引opt_idx_parti ​

语法格式 ​

sql
opt_idx_parti::=
    LOCAL
|   (GLOBAL [opt_partitioning_clause] [opt_subpartitioning_clause])

opt_partitioning_clause::=
    PARTITION BY RANGE '(' name_list ')' opt_parti_interval PARTITIONS '(' range_parti_items ')'
|   PARTITION BY LIST '(' name_list ')' PARTITIONS '('list_parti_items ')'
|   PARTITION BY HASH '(' name_list ')' PARTITIONS iconst
|   PARTITION BY HASH '(' name_list ')' PARTITIONS '(' name_list ')'

opt_subpartitioning_clause ::=
    SUBPARTITION BY HASH '(' name_list ')' SUBPARTITIONS iconst
|   SUBPARTITION BY HASH '(' name_list ')' SUBPARTITIONS '(' name_list')'
|   SUBPARTITION BY LIST '(' name_list ')' SUBPARTITIONS '('list_parti_items ')'
|   SUBPARTITION BY RANGE '('name_list')' SUBPARTITIONS '('range_parti_items ')'

参数说明 ​

  • LOCAL：用于指定索引的分区模式为局部分区，即针对各个表分区各建一个子索引，局部索引仅限于分区表。
  • GLOBAL：用于指定索引的分区模式为全局索引，即无论表是否分区，索引都不分区。
  • opt_partitioning_clause为一级分区子句。
  • opt_subpartitioning_clause为二级分区子句。

其他选项 ​

语法格式 ​

sql
opt_online::=
    ONLINE
|   OFFLINE

parallel_opt2::=
    NOPARALLEL
|   (PARALLEL [ICONST ["," ICONST]])
 
opt_wait::= 
    empty
|   NOWAIT 
|   WAIT 
|   WAIT ICONST

opt_sort::=
    ASC
|   DESC

参数说明 ​

  • opt_online：索引创建是否在线进行。
  • parallel_opt2：索引创建是否使用并行处理。
  • opt_wait：索引创建时的等待行为。
  • opt_sort：索引的排序顺序。

示例 ​

  • 示例1
    创建一个堆表index_list，在其上创建一个全局列表分区索引，分区键city_name，索引键值根据city_name进行分区。

    sql
    CREATE TABLE index_list (city_id INT ,city_name VARCHAR,city_population INT);

CREATE INDEX index_l ON index_list(city_name) GLOBAL PARTITION BY LIST (city_name) PARTITIONS
(
    partition1 VALUES('成都'),
    partition2 VALUES('重庆'),
    partition3 VALUES('上海'),
    partition4 VALUES('北京'),
    partition5 VALUES('广州'),
    partition6 VALUES('台湾'),
    partition7 VALUES('香港')
);

  • 示例2
    为表index_list创建一个全局分区索引，该索引以city_name作为一级列表分区的分区键，以city_population作为二级范围分区的分区键。

    sql
    CREATE INDEX index_list_range ON index_list(city_name) GLOBAL PARTITION BY LIST (city_name) PARTITIONS
(
    partition_chengdu VALUES('成都'),
    partition_chongqing VALUES('重庆'),
    partition_shanghai VALUES('上海'),
    partition_beijing VALUES('北京'),
    partition_guangzhou VALUES('广州'),
    partition_taiwan VALUES('台湾'),
    partition_hongkong VALUES('香港')
)
SUBPARTITION BY RANGE(city_population) SUBPARTITIONS
(
    partition_3000w VALUES LESS THAN (3000),
    partition_2500w VALUES LESS THAN (2500),
    partition_2000w VALUES LESS THAN (2000),
    partition_1500w VALUES LESS THAN (1500),
    partition_1000w VALUES LESS THAN (1000),
    partition_500w VALUES LESS THAN (500)
);

  • 示例3
    创建索引时，支持排序关键字（ASC/DESC）的使用。为表tab创建一个升序索引。

    sql
    CREATE TABLE tab(id INT);

CREATE INDEX idx1 ON tab(id ASC); #执行成功

    说明：

    默认采用升序索引。

  • 示例4
    创建索引时，支持关键字（IF NOT EXISTS）的使用。创建索引已存在同名索引，但要创建的索引类型与原索引不同，此处创建不会抛出错误，也不会改变原索引。

    sql
    SQL> CREATE TABLE index_tab(id int,name varchar(20));
SQL> CREATE INDEX test_idx on index_tab(id);
SQL> SELECT di.index_name,di.index_type FROM dba_indexes di WHERE di.index_name='TEST_IDX';
INDEX_NAME	|INDEX_TYPE
----------------------
TEST_IDX	|0
SQL> CREATE UNIQUE INDEX IF NOT EXISTS test_idx on index_tab(id); 
SQL> SELECT di.index_name,di.index_type FROM dba_indexes di WHERE di.index_name='TEST_IDX';
INDEX_NAME	|INDEX_TYPE
----------------------
TEST_IDX	|0