TiDB 如何用 INVISIBLE 索引在不删除索引的前提下评估其影响

TiDB 如何用 INVISIBLE 索引在不删除索引的前提下评估其影响【免费下载链接】tidbTiDB is built for agentic workloads that grow unpredictably, with ACID guarantees and native support for transactions, analytics, and vector search. No data silos. No noisy neighbors. No infrastructure ceiling.项目地址: https://gitcode.com/GitHub_Trending/ti/tidb当你要下线一个索引之前通常想知道删掉它之后哪些查询会退化成全表扫描、整体读写性能会受多大影响。直接DROP INDEX再发现问题就加回来在大表上代价很高。TiDB 提供了 INVISIBLE不可见索引把索引标记为INVISIBLE后优化器不再选择它但索引本身仍然保留并随 DML 维护——效果等同于对每条 SQL 使用 Index Hint 忽略该索引只是不用去改任何一条 SQL。而INVISIBLE/VISIBLE都是快速的原地操作不需要重建索引。适用前提该索引不是主键主键不能被设为不可见。整个评估流程是确认可见性 → 设为 INVISIBLE → 观察查询计划与性能 → 恢复 VISIBLE → 确认状态。下面按这个顺序操作。1. 确认索引当前的可见性两种方式都可以。查询INFORMATION_SCHEMA.STATISTICS的IS_VISIBLE列SELECT DISTINCT index_name, is_visible FROM information_schema.statistics WHERE table_schema db_name AND table_name tbl_name ORDER BY index_name;或使用SHOW INDEX其输出中包含VISIBLE列SHOW INDEX FROM tbl_name;其中db_name、tbl_name替换为你实际的库名和表名。IS_VISIBLE取值为YES/NONO表示该索引当前对优化器不可见。2. 把索引设为 INVISIBLE对已存在的索引使用 DDL 语句切换ALTER TABLE tbl_name ALTER INDEX idx_name INVISIBLE;也可以在创建索引或建表时直接声明为不可见CREATE INDEX idx_name ON tbl_name (col) INVISIBLE; CREATE TABLE t (a INT NOT NULL, b INT, KEY(a), UNIQUE(b) INVISIBLE);注意 INVISIBLE 声明的位置属于索引选项放在列约束/索引定义之后如上例。3. 验证索引状态并观察查询行为执行后再次查询information_schema.statistics确认状态变化。以下是 TiDB 集成测试中的真实示例输出表t建表时key(a)可见、unique(b) invisible不可见index_name is_visible a YES b NO执行ALTER TABLE t ALTER INDEX a INVISIBLE;后再次查询结果变为index_name is_visible a NO b NO以上输出引自 集成测试用例 及其 结果文件是文档示例你的表对应的索引名和数量会不同。按 设计文档 的说明invisible 索引无法被优化器使用在use_invisible_indexes开关关闭时对查询语句的效果等同于用 Index Hint 忽略该索引。因此对比设为 INVISIBLE 前后相同 SQL 的执行计划EXPLAIN与性能表现就是在模拟删除该索引后的效果。确认影响可接受后再执行真正的DROP INDEX如果影响不可接受把索引恢复为可见即可无需重建。4. 恢复为 VISIBLEALTER TABLE tbl_name ALTER INDEX idx_name VISIBLE;再用第 1 步的查询确认IS_VISIBLE回到YES。测试用例中alter table t alter index b visible;后输出由NO变为YES见 db_integration.result。SHOW CREATE TABLE也会展示索引的 INVISIBLE 信息可作为补充确认手段。5. 边界情况与报错以下现象来自 TiDB 集成测试的实际用例遇到时可对照判断主键索引不能设为不可见。对充当主键的索引执行ALTER TABLE t1 ALTER INDEX a INVISIBLE;会报Error 3522 (HY000): A primary key index cannot be invisible注意一种容易忽略的情况没有显式主键、但存在NOT NULL列上的 UNIQUE 索引的表该索引实际上充当隐式主键同样不能设为不可见。不能使用PRIMARY关键字指代主键索引。ALTER TABLE t2 ALTER INDEX PRIMARY INVISIBLE;会报Error 1064语法错误需要用实际的索引名。索引名不存在时报Error 1176 (42000): Key non_exists_idx doesnt exist in table t先核对第 1 步查到的index_name。功能索引generated expression index同样支持切换例如ALTER TABLE t3 ALTER INDEX idx INVISIBLE;idx是KEY((ab))这类索引在测试中可正常执行。6. 与 MySQL 的行为差异及优化器开关与 MySQL 的一个关键差异见 设计文档 的兼容性说明当开关关闭时MySQL 允许通过 SQL Hint 强制使用 invisible 索引TiDB 不允许会抛出Unresolved name错误。也就是说在 TiDB 中不可见索引对 Hint 也是真正不可用的评估时不要指望用 Hint 绕过。此外可以通过会话变量tidb_opt_use_invisible_indexes控制优化器是否使用不可见索引。默认值为0不使用。TiDB 提供set_varHint 在查询内设置该变量测试用例中的用法如下引自 setvar 测试SELECT /* set_var(tidb_opt_use_invisible_indexeson) */ tidb_opt_use_invisible_indexes; -- 返回 1 SELECT tidb_opt_use_invisible_indexes; -- 新会话中返回 0该开关的含义与设计文档中optimizer_switch的use_invisible_indexes一致开启后优化器仍可以使用 invisible 索引。需要留意两处文档对变量名的表述略有出入设计文档写作use_invisible_indexes而集成测试实际验证的变量名为tidb_opt_use_invisible_indexes以测试中的实际变量名为准。7. 流程小结一次完整的索引影响评估就是IS_VISIBLE查询确认基线 →ALTER TABLE ... ALTER INDEX ... INVISIBLE→ 观察计划与性能 →... VISIBLE恢复 → 再次查询确认YES。整个过程不删除索引随时可回退唯一不能这样做的是主键索引含充当隐式主键的NOT NULLUNIQUE 索引它只能走真正的DROP/重建流程。【免费下载链接】tidbTiDB is built for agentic workloads that grow unpredictably, with ACID guarantees and native support for transactions, analytics, and vector search. No data silos. No noisy neighbors. No infrastructure ceiling.项目地址: https://gitcode.com/GitHub_Trending/ti/tidb创作声明：本文部分内容由AI辅助生成（AIGC），仅供参考