创建索引

# 方式一：使用 CREATE INDEX 语句
CREATE [UNIQUE] INDEX 
     index_name ON tbl_name (column_names) 
     [index_option] 
     [algorithm_option];

# 方式二：使用 ALTER TABLE 语句
ALTER TABLE tbl_name ADD
     { [UNIQUE] {INDEX | KEY} 
      | PRIMARY KEY
      }
     index_name (column_names)
     [index_option]
     [algorithm_option];
     

index_option: {
  | COMMENT 'string'
  | {VISIBLE | INVISIBLE}
}

algorithm_option:
    ALGORITHM [=] {DEFAULT | INPLACE | COPY}

# 创建测试表
CREATE TABLE sbtest1 (id int, v1 int, v2 int, v3 int, v4 int);

# 截止迭代21.2.3，暂不支持online add pk
ALTER TABLE sbtest1 ADD PRIMARY KEY(id), ALGORITHM = COPY;
ERROR 8528 (HY000): Online alter table tdsql.sbtest1 failed with 'Not support table without primary key', please set variable 'tdsql_use_online_copy_ddl' to 'false' if no write during alter is acceptable.

# 显示设置索引 COMMENT,可见性和算法
CREATE UNIQUE INDEX idx_v1 ON sbtest1 (v1) COMMENT 'v1_index' INVISIBLE ALGORITHM = INPLACE;
ALTER TABLE sbtest1 ADD INDEX idx_v2 (v2) COMMENT 'v2_index' VISIBLE, ALGORITHM = INPLACE;

# 默认使用 INPLACE 算法
CREATE UNIQUE INDEX idx_v4 ON sbtest1 (v4);
ALTER TABLE sbtest1 ADD INDEX idx_v3 (v3);