OceanBase CREATE INDEX

描述

該語句用來創(chuàng)建索引。索引是創(chuàng)建在表上的，對(duì)數(shù)據(jù)庫(kù)表中一列或多列的值進(jìn)行排序的一種結(jié)構(gòu)。其作用主要在于提高查詢的速度，降低數(shù)據(jù)庫(kù)系統(tǒng)的性能開銷。

格式

CREATE [UNIQUE] INDEX indexname 
?????ON tblname (index_col_name,...) 
      [index_type] [index_options] 
index_type: 
      USING BTREE

index_options: 
      index_option [index_option…]
      
index_option: 
    GLOBAL | LOCAL
    | COMMENT 'string'
    | COMPRESSION [=] {NONE | LZ4_1.0 | LZO_1.0 | SNAPPY_1.0 | ZLIB_1.0}
    | BLOCK_SIZE [=] size
    | STORING(columname_list) 
    | VISIBLE | INVISIBLE

index_col_name: 
    colname [(length)] [ASC | DESC]

columname_list: 
    colname [, colname…]

參數(shù)解釋

示例

1. 執(zhí)行以下命令，創(chuàng)建表 test。

obclient> CREATE TABLE test (c1 int primary key, c2 VARCHAR(10));

1. 執(zhí)行以下命令，創(chuàng)建表 test 的索引。

obclient> CREATE INDEX test_index ON test (c1, c2 ASC);

1. 執(zhí)行以下命令，查看表 test 的索引。

obclient> SHOW INDEX FROM test;
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+
| Table | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment   | Index_comment | Visible |
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+
| test  |          0 | PRIMARY    |            1 | c1          | A         |        NULL | NULL     | NULL   |      | BTREE      | available |               | YES     |
| test  |          1 | test_index |            1 | c1          | A         |        NULL | NULL     | NULL   |      | BTREE      | available |               | YES     |
| test  |          1 | test_index |            2 | c2          | A         |        NULL | NULL     | NULL   | YES  | BTREE      | available |               | YES     |
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+
3 rows in set (0.05 sec)