创建索引

您可以通过 CREATE INDEX 语句为表创建索引。

背景信息

OceanBase 数据库支持在非分区表和分区表上创建索引，索引可以是局部索引或全局索引，也可以是唯一索引或普通索引。如果是分区表的唯一局部索引，则唯一局部索引必须包含表分区的拆分键。

本文主要介绍如何在非分区表上创建索引，分区表上索引的创建请参见 分区表的索引。

语法

MySQL 模式下，创建索引的 SQL 语法格式如下：

语法 1

通过 CREATE TABLE 创建索引。

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] table_name
      (table_definition_list) [table_option_list] [partition_option] [AS] select;

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] table_name
      LIKE table_name;

table_definition_list:
    table_definition [, table_definition ...]

table_definition:
      column_definition
    | [CONSTRAINT [constraint_name]] PRIMARY KEY index_desc
    | [CONSTRAINT [constraint_name]] UNIQUE {INDEX | KEY} [index_name] index_desc
    | {INDEX | KEY} [index_name] index_desc
    | FULLTEXT [INDEX | KEY] [index_name] fulltext_index_desc

column_definition_list:
    column_definition [, column_definition ...]

column_definition:
    column_name data_type
    [DEFAULT const_value] [AUTO_INCREMENT]
    [NULL | NOT NULL] [[PRIMARY] KEY] [UNIQUE [KEY]] comment

index_desc:
   (column_desc_list) [index_type] [index_option_list]

fulltext_index_desc:
   (column_desc_list) CTXCAT(column_desc_list) [index_option_list]

column_desc_list:
    column_desc [, column_desc ...]

column_desc:
     column_name [(length)] [ASC | DESC]

index_type:
    USING BTREE

index_option_list:
    index_option [ index_option ...]

index_option:
      [GLOBAL | LOCAL]
    | block_size
    | compression
    | STORING(column_name_list)
    | comment

table_option_list:
    table_option [ table_option ...]

table_option:
      [DEFAULT] {CHARSET | CHARACTER SET} [=] charset_name
    | [DEFAULT] COLLATE [=] collation_name
    | primary_zone
    | replica_num
    | table_tablegroup
    | block_size
    | compression
    | AUTO_INCREMENT [=] INT_VALUE
    | comment
    | DUPLICATE_SCOPE [=] "none|zone|region|cluster"
    | LOCALITY [=] "locality description"
    | ROW_FORMAT [=] REDUNDANT|COMPACT|DYNAMIC|COMPRESSED|DEFAULT
    | PCTFREE [=] num

partition_option:
      PARTITION BY HASH(expression)
      [subpartition_option] PARTITIONS partition_count
    | PARTITION BY KEY([column_name_list])
      [subpartition_option] PARTITIONS partition_count
    | PARTITION BY RANGE {(expression) | COLUMNS (column_name_list)}
      [subpartition_option] (range_partition_list)
    | PARTITION BY LIST {(expression) | COLUMNS (column_name_list)}
      [subpartition_option] PARTITIONS partition_count

subpartition_option:
      SUBPARTITION BY HASH(expression)
      SUBPARTITIONS subpartition_count
    | SUBPARTITION BY KEY(column_name_list)
      SUBPARTITIONS subpartition_count
    | SUBPARTITION BY RANGE {(expression) | COLUMNS (column_name_list)}
      (range_subpartition_list)
    | SUBPARTITION BY LIST(expression)

range_partition_list:
    range_partition [, range_partition ...]

range_partition:
    PARTITION partition_name
    VALUES LESS THAN {(expression_list) | MAXVALUE}

range_subpartition_list:
    range_subpartition [, range_subpartition ...]

range_subpartition:
    SUBPARTITION subpartition_name
    VALUES LESS THAN {(expression_list) | MAXVALUE}

expression_list:
    expression [, expression ...]

column_name_list:
    column_name [, column_name ...]

partition_name_list:
    partition_name [, partition_name ...]

partition_count | subpartition_count:
    INT_VALUE

语法 2

通过 CREATE INDEX 创建索引。

CREATE [UNIQUE] INDEX index_name ON table_name ( column_list ) [LOCAL | GLOBAL] [ PARTITION BY column_list PARTITIONS N ];

MySQL 租户里，索引名称在表范围内不能重复，查看索引可以通过命令 SHOW INDEX。

语法 3

在 MySQL 租户里，新增索引还有一种方法，其 SQL 语法格式如下：

ALTER TABLE  table_name  
ADD INDEX|KEY  index_name ( column_list );

该语句可以一次增加多个索引，索引关键字用 INDEX 或 KEY 都可以。

示例

示例 1

1. 创建 t_idx1 表。

   obclient> CREATE TABLE t_idx1(
     id int,
     name varchar(18),
     date date,
     PRIMARY KEY (id),
     index idx (date)
   );
   

2. 查看 t_idx1 表的索引。

   obclient> SHOW INDEX FROM t_idx1;
   +--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+
   | Table  | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment   | Index_comment | Visible |
   +--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+
   | t_idx1 |          0 | PRIMARY  |            1 | id          | A         |        NULL | NULL     | NULL   |      | BTREE      | available |               | YES     |
   | t_idx1 |          1 | idx      |            1 | date        | A         |        NULL | NULL     | NULL   | YES  | BTREE      | available |               | YES     |
   +--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+
   2 rows in set
   

示例 2

1. 创建 t_idx2 表。

   obclient> CREATE TABLE t_idx2 (id int primary key, name varchar(10));
   Query OK, 0 rows affected
   

2. 在 t_idx2 表添加索引 idx。

   obclient> CREATE INDEX idx ON t_idx2 (id, name);
   Query OK, 0 rows affected
   

3. 查看 t_idx2 表的索引。

   obclient> SHOW INDEX FROM t_idx2;
   +--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+
   | Table  | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment   | Index_comment | Visible |
   +--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+
   | t_idx2 |          0 | PRIMARY  |            1 | id          | A         |        NULL | NULL     | NULL   |      | BTREE      | available |               | YES     |
   | t_idx2 |          1 | idx      |            1 | id          | A         |        NULL | NULL     | NULL   |      | BTREE      | available |               | YES     |
   | t_idx2 |          1 | idx      |            2 | name        | A         |        NULL | NULL     | NULL   | YES  | BTREE      | available |               | YES     |
   +--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+
   3 rows in set
   

示例 3

1. 创建 t_idx3 表。

   CREATE TABLE t_idx3(
     id int,
     name varchar(18),
     date date,
     PRIMARY KEY (id)
   );
   

2. 在 t_idx3 表添加索引 idx。

   obclient> ALTER TABLE t_idx3 ADD INDEX idx ( date ) ;
   Query OK, 0 rows affected
   

3. 查看 t_idx3 表的索引。

   obclient> SHOW INDEX FROM t_idx3;
   +--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+
   | Table  | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment   | Index_comment | Visible |
   +--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+
   | t_idx3 |          0 | PRIMARY  |            1 | id          | A         |        NULL | NULL     | NULL   |      | BTREE      | available |               | YES     |
   | t_idx3 |          1 | idx      |            1 | date        | A         |        NULL | NULL     | NULL   | YES  | BTREE      | available |               | YES     |
   +--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+
   2 rows in set