DROP INDEX

DROP INDEX 语句用于从指定的表中删除索引,并在 TiKV 中将空间标记为释放。

语法图

AlterTableDropIndexStmt
ALTERIgnoreOptionalTABLEAlterTableDropIndexSpec
IgnoreOptional
IGNORE
TableName
Identifier.Identifier
AlterTableDropIndexSpec
DROPKeyOrIndexFOREIGNKEYIfExistsIdentifier
KeyOrIndex
KEYINDEX
IfExists
IFEXISTS

示例

CREATE TABLE t1 (id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, c1 INT NOT NULL);
Query OK, 0 rows affected (0.10 sec)
INSERT INTO t1 (c1) VALUES (1),(2),(3),(4),(5);
Query OK, 5 rows affected (0.02 sec) Records: 5 Duplicates: 0 Warnings: 0
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)
CREATE INDEX c1 ON t1 (c1);
Query OK, 0 rows affected (0.30 sec)
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)
ALTER TABLE t1 DROP INDEX c1;
Query OK, 0 rows affected (0.30 sec)

MySQL 兼容性

  • 不支持删除 CLUSTERED 类型的 PRIMARY KEY。要了解关于 CLUSTERED 主键的详细信息,请参考聚簇索引

另请参阅