ADD INDEX
ALTER TABLE.. ADD INDEX 语句用于为已有表添加索引。该操作在 TiDB 中是在线进行的，这意味着在添加索引的过程中，表的读写操作都不会被阻塞。
语法
- AlterTableStmt
- AddIndexSpec
- IndexPartSpecification
- IndexOption
- IndexType
AlterTableStmt
::= 'ALTER' 'IGNORE'? 'TABLE' TableName AddIndexSpec ( ',' AddIndexSpec )*
AddIndexSpec
::= 'ADD' ( ( 'PRIMARY' 'KEY' | ( 'KEY' | 'INDEX' ) 'IF NOT EXISTS'? | 'UNIQUE' ( 'KEY' | 'INDEX' )? ) ( ( Identifier? 'USING' | Identifier 'TYPE' ) IndexType )? | 'FULLTEXT' ( 'KEY' | 'INDEX' )? IndexName ) '(' IndexPartSpecification ( ',' IndexPartSpecification )* ')' IndexOption*
IndexPartSpecification
::= ( ColumnName ( '(' LengthNum ')' )? | '(' Expression ')' ) ( 'ASC' | 'DESC' )
IndexOption
::= 'KEY_BLOCK_SIZE' '='? LengthNum
| 'USING' IndexType
| 'WITH' 'PARSER' Identifier
| 'COMMENT' stringLit
| 'VISIBLE'
| 'INVISIBLE'
| 'GLOBAL'
| 'LOCAL'
IndexType
::= 'BTREE'
| 'HASH'
| 'RTREE'
示例
mysql> CREATE TABLE t1 (id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, c1 INT NOT NULL);
Query OK, 0 rows affected (0.11 sec)
mysql> INSERT INTO t1 (c1) VALUES (1),(2),(3),(4),(5);
Query OK, 5 rows affected (0.03 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> EXPLAIN SELECT * FROM t1 WHERE c1 = 3;
+-------------------------+----------+-----------+---------------+--------------------------------+
| id | estRows | task | access object | operator info |
+-------------------------+----------+-----------+---------------+--------------------------------+
| TableReader_7 | 10.00 | root | | data:Selection_6 |
| └─Selection_6 | 10.00 | cop[tikv] | | eq(test.t1.c1, 3) |
| └─TableFullScan_5 | 10000.00 | cop[tikv] | table:t1 | keep order:false, stats:pseudo |
+-------------------------+----------+-----------+---------------+--------------------------------+
3 rows in set (0.00 sec)
mysql> ALTER TABLE t1 ADD INDEX (c1);
Query OK, 0 rows affected (0.30 sec)
mysql> EXPLAIN SELECT * FROM t1 WHERE c1 = 3;
+------------------------+---------+-----------+------------------------+---------------------------------------------+
| id | estRows | task | access object | operator info |
+------------------------+---------+-----------+------------------------+---------------------------------------------+
| IndexReader_6 | 0.01 | root | | index:IndexRangeScan_5 |
| └─IndexRangeScan_5 | 0.01 | cop[tikv] | table:t1, index:c1(c1) | range:[3,3], keep order:false, stats:pseudo |
+------------------------+---------+-----------+------------------------+---------------------------------------------+
2 rows in set (0.00 sec)
MySQL 兼容性
TiDB 在语法上兼容 MySQL，支持
HASH、BTREE和RTREE等索引类型，但实际会忽略这些类型。不支持
SPATIAL索引。TiDB 自建版和 TiDB Cloud Dedicated 支持解析
FULLTEXT语法，但不支持使用FULLTEXT索引。不支持降序索引（与 MySQL 5.7 类似）。
不支持为表添加
CLUSTERED类型的主键。关于CLUSTERED类型主键的更多信息，参见 聚簇索引。将
PRIMARY KEY或UNIQUE INDEX作为 全局索引 并通过GLOBAL索引选项设置，是 TiDB 针对 分区表 的扩展功能，不兼容 MySQL。