ALTER INDEX
ALTER INDEX 语句用于修改索引的可见性为 Visible 或 Invisible。不可见索引由 DML 语句维护，但不会被查询优化器使用。在你想在永久删除索引之前进行双重确认的场景中，这非常有用。从 TiDB v8.0.0 开始，你可以通过修改系统变量 tidb_opt_use_invisible_indexes 来让优化器选择不可见索引。
概要
- AlterTableStmt
- AlterIndexSpec
AlterTableStmt
::= 'ALTER' 'IGNORE'? 'TABLE' TableName AlterIndexSpec ( ',' AlterIndexSpec )*
AlterIndexSpec
::= 'ALTER' 'INDEX' Identifier ( 'VISIBLE' | 'INVISIBLE' )
示例
你可以使用 ALTER TABLE ... ALTER INDEX ... 语句来修改索引的可见性。
CREATE TABLE t1 (c1 INT, UNIQUE(c1));
ALTER TABLE t1 ALTER INDEX c1 INVISIBLE;
Query OK, 0 rows affected (0.02 sec)
SHOW CREATE TABLE t1;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table
|
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t1 | CREATE TABLE `t1` (
`c1` int DEFAULT NULL,
UNIQUE KEY `c1` (`c1`) /*!80000 INVISIBLE */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
优化器无法使用 c1 的 invisible index。
EXPLAIN SELECT c1 FROM t1 ORDER BY c1;
+-------------------------+----------+-----------+---------------+--------------------------------+
| id | estRows | task | access object | operator info |
+-------------------------+----------+-----------+---------------+--------------------------------+
| Sort_4 | 10000.00 | root | | test.t1.c1:asc |
| └─TableReader_8 | 10000.00 | root | | data:TableFullScan_7 |
| └─TableFullScan_7 | 10000.00 | cop[tikv] | table:t1 | keep order:false, stats:pseudo |
+-------------------------+----------+-----------+---------------+--------------------------------+
3 rows in set (0.00 sec)
相比之下，c2 是一个 visible index，可以被优化器使用。
EXPLAIN SELECT c2 FROM t1 ORDER BY c2;
+------------------------+----------+-----------+------------------------+-------------------------------+
| id | estRows | task | access object | operator info |
+------------------------+----------+-----------+------------------------+-------------------------------+
| IndexReader_13 | 10000.00 | root | | index:IndexFullScan_12 |
| └─IndexFullScan_12 | 10000.00 | cop[tikv] | table:t1, index:c2(c2) | keep order:true, stats:pseudo |
+------------------------+----------+-----------+------------------------+-------------------------------+
2 rows in set (0.00 sec)
即使你使用 USE INDEX SQL 提示强制使用索引，优化器仍然不能使用不可见索引；否则会返回错误。
SELECT * FROM t1 USE INDEX(c1);
ERROR 1176 (42000): Key 'c1' doesn't exist in table 't1'
ALTER TABLE t1 DROP INDEX c1;
Query OK, 0 rows affected (0.02 sec)
MySQL 兼容性
- TiDB 中的不可见索引是基于 MySQL 8.0 中的等效功能建模的。
- 与 MySQL 类似，TiDB 不允许将
PRIMARY KEY索引设为不可见。