国内餐饮类网站欣赏,网站建设贰金手指下拉贰拾,做网站注意哪些方面,seo整站优化更能准确获得客户MySQL 8.0 虽然发布很久了#xff0c;但可能大家都停留在 5.7.x#xff0c;甚至更老#xff0c;其实 MySQL 8.0 新增了许多重磅新特性#xff0c;比如栈长今天要介绍的 隐藏索引 或者 不可见索引。隐藏索引是什么鬼#xff1f; 隐藏索引 字面意思… MySQL 8.0 虽然发布很久了但可能大家都停留在 5.7.x甚至更老其实 MySQL 8.0 新增了许多重磅新特性比如栈长今天要介绍的 隐藏索引 或者 不可见索引。隐藏索引是什么鬼 隐藏索引 字面意思就是把索引进行隐藏即不可见它不是用来查询优化的所以它不会被优化器使用到。隐藏索引适用于除主键索引显示或者隐式设置之外的索引意味着主键索引是不能通过任何方式隐藏的。MySQL 数据库默认创建的索引都是可见的要显式控制一个索引的可见性可以在 CREATE TABLECREATE INDEX 或 ALTER TABLE 的索引定义命令中使用 VISIBLE 或 INVISIBLE 关键字。如下面示例所示CREATE TABLE javastack (age INT,weight INT,tall INT,INDEX age_idx (age) INVISIBLE
) ENGINE InnoDB;
CREATE INDEX weight_idx ON javastack (weight) INVISIBLE;
ALTER TABLE javastack ADD INDEX tall_idx (tall) INVISIBLE;
要变更现有索引的可见性可以在 ALTER TABLE ... ALTER INDEX 命令中使用 VISIBLE 或 INVISIBLE 关键字。年龄索引变更为不可见隐藏ALTER TABLE javastack ALTER INDEX age_idx INVISIBLE;
年龄索引变更为可见ALTER TABLE javastack ALTER INDEX age_idx VISIBLE;
怎么知道一个表中的索引是可见还是不可见可以从 INFORMATION_SCHEMA.STATISTICS 表或者 SHOW INDEX 命令输出中获得。例如mysql SELECT INDEX_NAME, IS_VISIBLEFROM INFORMATION_SCHEMA.STATISTICSWHERE TABLE_SCHEMA db1 AND TABLE_NAME javastack;
------------------------
| INDEX_NAME | IS_VISIBLE |
------------------------
| age_idx | YES |
| weight_idx | NO |
| tall_idx | NO |
------------------------
隐藏索引有什么用 从上面隐藏索引介绍我们知道隐藏索引可以不被优化器所使用那么我们可以把某个表的某个索引设置隐藏然后再测试 SQL 语句的查询性能。这样就可以利用隐藏索引快速测试删除索引后对 SQL 查询性能的影响而无需进行索引删除、重建操作如果需要该索引再设置可见就好了这在大表测试中无疑非常有用因为对于大表索引的删除和重新添加很耗性能甚至影响表的正常工作。隐藏索引设置 如果一个索引被设置成隐藏了但实际上又需要被优化器所使用有几种表索引情况缺失对查询造成的影响1SQL 查询语句中包含了索引提示指向不可见索引会发生错误2性能模式数据中显示了受影响 SQL 查询语句的负载增高3SQL 查询语句进行 EXPLIAN 时出现了不同的执行计划4SQL 查询语句出现在了慢查询日志中之前没有出现系统变量 optimizer_switch 的 use_invisible_indexes 标志的值控制了优化器执行计划构建时是否使用隐藏索引。如果 use_invisible_indexes 值设置为 off 关闭状态默认值优化器默认会忽略隐藏索引即和加入该参数之前的效果一样。如果 use_invisible_indexes 值设置为 on 打开状态隐藏索引仍然保持不可见但优化器会把隐藏索引加入到执行计划的构建中。如果想要在某条单个 SQL 查询语句上启用隐藏索引可以使用 SET_VAR 优化器提示来临时更新 optimizer_switch 的值如下所示mysql EXPLAIN SELECT /* SET_VAR(optimizer_switch use_invisible_indexeson) */ age, weight FROM javastack WHERE weight 150\G
*************************** 1. row ***************************id: 1select_type: SIMPLEtable: javastackpartitions: NULLtype: range
possible_keys: weight_idxkey: weight_idxkey_len: 5ref: NULLrows: 2filtered: 100.00Extra: Using index conditionmysql EXPLAIN SELECT age, weight FROM javastack WHERE weight 150\G
*************************** 1. row ***************************id: 1select_type: SIMPLEtable: javastackpartitions: NULLtype: ALL
possible_keys: NULLkey: NULLkey_len: NULLref: NULLrows: 5filtered: 33.33Extra: Using where
索引的可见性不会影响索引的自身维护例如不管索引是可见还是不可见每次表数据行的更改索引都会更新并且唯一索引也可防止插入重复数据。没有显式主键的表如果在 NOT NULL 列上有任何一个唯一索引则仍可能成为有效的隐式主键。在这种情况下第一个这样的索引会对表数据行施加与显式主键相同的约束并且该索引不能设置为不可见。如以下表的定义CREATE TABLE javastack (age INT NOT NULL,weight INT NOT NULL,UNIQUE weight_idx (weight)
) ENGINE InnoDB;
该表定义不包含任何显式主键但是 weight 列为 NOT NULL在该列上创建的唯一索引在数据行上与主键具有相同的约束并且不能使其不可见mysql ALTER TABLE javastack ALTER INDEX weight_idx INVISIBLE;
ERROR 3522 (HY000): A primary key index cannot be invisible.
假设现在我们将一个显式主键添加到表中ALTER TABLE javastack ADD PRIMARY KEY (age);
显式主键不能设置为不可见此时weight 列上的唯一索引不再充当隐式主键因此可以使其设置不可见。mysql ALTER TABLE javastack ALTER INDEX weight_idx INVISIBLE;
Query OK, 0 rows affected (0.03 sec)
总结 本文介绍了 MySQL 8.0 中的新特性隐藏不可见索引这个索引并不是新加的索引类型而是可以控制索引是否加入到执行计划的构建之中。在实际生产中也可以利用隐藏索引进行 SQL 语句的性能测试或者对索引进行逻辑删除以及索引的灰度发布测试等用处还是蛮大的。参考文档https://dev.mysql.com/doc/refman/8.0/en/invisible-indexes.html话说你们用的 MySQL 哪个版本呢
往期推荐
忘记MySQL密码怎么办一招教你搞定2020-10-20 MySQL为Null会导致5个问题个个致命2020-12-31 很实用的21个SQL小技巧2020-11-03
