MySQL Invisible Index

摘要：在本教程中，您将了解 MySQL 隐形索引以及管理索引可见性的语句。

MySQL隐形索引简介

不可见索引允许您将索引标记为对于查询优化器不可用。 MySQL 维护不可见索引，并在与索引关联的列中的数据发生更改时使它们保持最新。

默认情况下，索引是可见的。要使它们不可见，您必须在创建时显式声明其可见性，或者使用ALTER TABLE命令。 MySQL为我们提供了VISIBLE和INVISIBLE关键字来维护索引的可见性。

要创建不可见索引，请执行以下语句：

CREATE INDEX index_name
ON table_name( c1, c2, ...) INVISIBLE;
Code language: SQL (Structured Query Language) (sql)

在这个语法中：

1. 首先，在CREATE INDEX子句之后指定索引的名称。
2. 其次，列出要添加到索引的表名称和列列表。 INVISIBLE关键字表示您正在创建的索引是不可见的。

例如，以下语句在示例数据库中的employees表的extension列上创建索引，并将其标记为不可见索引：

CREATE INDEX extension 
ON employees(extension) INVISIBLE;
Code language: SQL (Structured Query Language) (sql)

要更改现有索引的可见性，请使用以下语句：

ALTER TABLE table_name
ALTER INDEX index_name [VISIBLE | INVISIBLE];
Code language: SQL (Structured Query Language) (sql)

例如，要使extension索引可见，请使用以下语句：

ALTER TABLE employees
ALTER INDEX extension VISIBLE; 
Code language: SQL (Structured Query Language) (sql)

您可以通过查询information_schema数据库中的statistics来找到索引及其可见性：

SELECT 
    index_name, 
    is_visible
FROM
    information_schema.statistics
WHERE
    table_schema = 'classicmodels'
        AND table_name = 'employees';
Code language: SQL (Structured Query Language) (sql)

这是输出：

另外，还可以使用SHOW INDEXES命令来显示表的所有索引：

SHOW INDEXES FROM employees;
Code language: SQL (Structured Query Language) (sql)

正如前面提到的，查询优化器不使用不可见索引，那么为什么要使用不可见索引呢？实际上，隐形索引有很多应用。例如，您可以使索引不可见，以查看它是否对性能有影响，如果有影响，则将索引再次标记为可见。

MySQL 隐形索引和主键

主键列上的索引不能是不可见的。如果您尝试这样做，MySQL 将发出错误。

另外，隐式主键索引也不能是不可见的。当您在没有主键的表的NOT NULL列上定义UNIQUE索引时，MySQL 隐式地认为该列是主键列，并且不允许您使索引不可见。

考虑以下示例。

首先，创建一个新表，在NOT NULL列上使用UNIQUE索引：

CREATE TABLE discounts (
    discount_id INT NOT NULL,
    name VARCHAR(50) NOT NULL,
    valid_from DATE NOT NULL,
    valid_to DATE NOT NULL,
    amount DEC(5 , 2 ) NOT NULL DEFAULT 0,
    UNIQUE discount_id(discount_id)
);
Code language: SQL (Structured Query Language) (sql)

其次，尝试使discount_id索引不可见：

ALTER TABLE discounts
ALTER INDEX discount_id INVISIBLE;
Code language: SQL (Structured Query Language) (sql)

MySQL 发出以下错误消息：

Error Code: 3522. A primary key index cannot be invisible   
Code language: SQL (Structured Query Language) (sql)

MySQL 隐形索引系统变量

为了控制查询优化器使用的可见索引，MySQL使用optimizer_switch系统变量的use_invisible_indexes标志。默认情况下， use_invisible_indexes处于关闭状态：

SELECT @@optimizer_switch;
Code language: SQL (Structured Query Language) (sql)

在本教程中，您了解了 MySQL 隐形索引、如何创建隐形索引以及如何更改现有索引的可见性。