索引是创建在表上的，对数据库表中一列或多列的值进行排序的一种结构。其作用主要在于提高查询的速度，降低数据库系统的性能开销。

    说明

    注意： 1. OceanBase中，新创建的索引需要等待一次每日合并后才生效（唯一索引需要两次每日合并后才生效）。比如新建的唯一索引，在未合并生效前依然可以插入违反唯一性的数据，合并后系统如发现无法使索引生效（如唯一索引因重复值不能生效），系统会把索引状态变为 index_error。 2. 索引状态可能通过show index from <table>命令显示。

    格式

    1. CREATE [UNIQUE] INDEX indexname 
    2.     ON tblname (index_col_name,...) 
    3.       [index_type] [index_options] 
    4. | CREATE FULLTEXT INDEX indexname ON tblname (index_col_name,...) 
    5. CTXCAT(index_col_name,...) [index_options]
    6. index_type:
    7.        USING BTREE
    9. index_options:
    10.        index_option [index_option…]      
    12. index_option:
    13.      GLOBAL [LOCAL]
    14.      | COMMENT 'string'
    15.      | COMPRESSION [=] {NONE | LZ4_1.0 | LZO_1.0 | SNAPPY_1.0 | ZLIB_1.0}
    16.      | BLOCK_SIZE [=] size
    17.      | STORING(columname_list) 
    19. index_col_name:
    20.      colname [(length)] [ASC]
    22. columname_list:
    23.      colname [, colname…]
    • index_col_name中，每个列名后都支持指定为ASC（升序），默认就为升序。

    • 本语句建立索引的排列方式为：首先以index_col_name中第一个列的值排序；该列值相同的记录，按下一列名的值排序；以此类推。

    • 执行“SHOW INDEX FROM tblname”可以查看创建的索引。

    • “index_option”中，可以指定GLOBAL，LOCAL关键字，表述全局或局部索引。默认是GLOBAL　index. VISIBLE和INVISIBLE关键字，表述可见索引或不可见索引，可见及不可见是对优化器而言的。创建带有Parition的表时index一定要加LOCAL关键字。如果没有加，系统将报错。多个index option以空格分隔。

    • 使用可选字段 STORING，表示索引表中冗余存储某些列，应用在唯一索引上，可以避免唯一所以回表带来的性能损失，提高系统查询系统（为OceanBase数据库特有）。

    说明

    说明：OceanBase内部数据以b树为索引。

    示例

    1. 执行以下命令，创建表test。
    1. CREATE TABLE test (c1 int primary key, c2 VARCHAR(10));
    1. 执行以下命令，创建表test的索引。
    1. CREATE INDEX test_index ON test (c1, c2 DESC);
    1. 执行以下命令，查看表test的索引。
    1. SHOW INDEX FROM test;

    报错

    • 语法错误时报 ERROR 1064 (42000): You have an error in your SQL syntax;

    • 输入表名错误时，报ERROR 1146 (42S02): Table 'XXX' doesn't exist

    • 输入表名过长，报ERROR 1059 (42000): Identifier name 'XXX' is too long

    • 输入列名错误，报ERROR 1072 (42000): Key column 'XXX' doesn't exist in table

    表达式索引

    前面提到我们可以使用生成列的创建语法创建一个VIRTUAL或者STORED形式的生成列，和普通列的一样，生成列上也可以创建索引。如果生成列是VIRTUAL方式的，那么在INSERT、REPLACE或者UPDATE的时候，VIRTUAL COLUMN的值将会被物化到索引表中，如果生成列是STORED形式，那么除了生成列的值来源于表达式计算外，并没有什么区别。

    前缀索引

    对于字符串类型的列，索引能够被建立在列值的前缀部分上，我们可以去使用col_name(length)语法去指定前缀的长度。

    • CHAR、VARCHAR、BINARY和VARBINARY类型的列上能够创建前缀索引

    • BLOB和TEXT类型的列上如果要创建索引必须指定前缀

    FULLETXTCTXCAT索引

    OceanBase支持一种全文索引：FULLTEXT CTXCAT索引，这种全文索引跟普通的全文索引不同之处在于这种索引允许索引中同时存在参与分词的列和不参与分词的普通列，其创建语法为：

    1. CREATE FULLTEXT INDEX indexname ON tblname (index_col_name,...) 
    2. CTXCAT(index_col_name,...) [index_options]

    其中，tblname (index_col_name,…)用来指定数据表中哪些列上被创建了索引以及列在索引中的组织顺序，CTXCAT(index_col_name,…)用来指定全文列，表示这些列将参与全文分词，OB支持多个全文列参与分词，如果是多个列参与全文分词，那么分词的集合是所有列文本集合之和，他们之间是OR的关系，参与全文分词的列必须是字符串类型。被CTXCAT修饰的全文列必须是在索引列表中声明的，并且在索引列表中的位置必须是连续的，不能间隔，例如：

    1. create table t1(a int primary key, b varchar(100), c varchar(100), d int); 
    2. create fulltext index i1 on t1(b, d) ctxcat(c);
    3. ERROR 1072 (42000): Key column 'c' doesn't exist in table

    指定的全文列没有在索引列表中：

    1. create table t1(a int primary key, b varchar(100), c varchar(100), d int);
    2. create fulltext index i1 on t1(b, d, c) ctxcat(b, c);
    3. ERROR 5291 (HY000): The CTXCAT column must be contiguous in the index column list

    指定的全文列在索引列表中不是连续存在的。

    不可见索引

    在某些场景下，我们希望优化器忽略某个索引，或者该索引一直idle状态，并没有任何查询使用到该索引，但是同时不想立即删掉这个索引，因为这个索引可能在未来会发挥作用，或者无法评估删除索引的风险。此时我们可以使用invisible index，通过将索引的状态改为invisible，让该索引对优化器不可见。

    我们可以在创建索引的时候，指定索引的可见状态，如果未指定，则默认是visible的状态。

    在建表建索引及单独建索引的ddl语句中均可以指定索引的可见状态，具体参看下面的例子：

    1. CREATE TABLE t1(c1 int primary key, c2 int, c3 int, c4 varchar(16), key idx1(c1) visible, index idx2(c2) invisible, unique key idx3(c3) visible, unique index idx4(c1, c2) invisible, unique idx5(c2,c3));
    3. CREATE UNIQUE INDEX idx6 ON t1(c2) visible;
    5. CREATE INDEX idx7 ON t1(c3) invisible;
    6. CREATE INDEX idx8 ON t1(c4) ;

    其中， idx1、idx3、idx6 显式指定为可见的， idx2、idx4、idx7 显式指定为不可见，idx5、idx8 未指定，默认为可见的。

    同时我们也可以通过alter table alter index的语法来改变已创建索引的可见状态。

    1. ALTER TABLE t1 ALTER INDEX idx8 invisible;
    2. //该语句将索引idx8由可见状态更改为不可见状态，优化器在选择索引的时候无法使用该索引