MySQL 隐形索引

---

简介：在本教程中，您将了解MySQL隐形索引和管理索引可见性的语句。

---

MySQL隐形索引简介

不可见索引允许您将索引标记为查询优化器不可用。MySQL维护不可见索引，并在与索引关联的列中的数据发生更改时使其保持最新。 默认情况下，索引是可见的。要使它们不可见，您必须在创建时或使用ALTER TABLE命令显式声明其可见性。MySQL为我们提供了维护索引可见性的关键字VISIBLE和INVISIBLE关键字。 要创建不可见索引，请使用以下语句：

CREATE INDEX index_name
ON table_name( c1, c2, ...) INVISIBLE;

在这个语法中：

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

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

CREATE INDEX extension 
ON employees(extension) INVISIBLE;

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

ALTER TABLE table_name
ALTER INDEX index_name [VISIBLE | INVISIBLE];

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

ALTER TABLE employees
ALTER INDEX extension VISIBLE;

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

SELECT 
    index_name, 
    is_visible
FROM
    information_schema.statistics
WHERE
    table_schema = 'mysqldemo'
        AND table_name = 'employees';

这是输出：

+------------+------------+
| INDEX_NAME | IS_VISIBLE |
+------------+------------+
| extension  | YES        |
| officeCode | YES        |
| PRIMARY    | YES        |
| reportsTo  | YES        |
+------------+------------+
4 rows in set (0.11 sec)

此外，您可以使用SHOW INDEXES命令显示表的所有索引：

SHOW INDEXES FROM employees;

+-----------+------------+------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table     | Non_unique | Key_name   | Seq_in_index | Column_name    | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-----------+------------+------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| employees |          0 | PRIMARY    |            1 | employeeNumber | A         |          23 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| employees |          1 | reportsTo  |            1 | reportsTo      | A         |           7 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| employees |          1 | officeCode |            1 | officeCode     | A         |           7 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| employees |          1 | extension  |            1 | extension      | A         |          20 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+-----------+------------+------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
4 rows in set (0.07 sec)

如前所述，查询优化器不使用不可见索引，那么为什么首先使用不可见索引？实际上，隐形索引有许多应用程序。例如，您可以使索引不可见，以查看它是否对性能产生影响，并将索引再次标记为可见。

---

MySQL隐形索引和主键

主键列上的索引不能是不可见的。如果您尝试这样做，MySQL将发出错误。 另外，隐式主键索引也不能是不可见的。当您UNIQUE在NOT NULL没有主键的表的列上定义索引时，MySQL会隐式地理解列是主键列，并且不允许您使索引不可见。 请考虑以下示例。 首先，在列上创建一个表带有UNIQUE索引NOT NULL：

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)
);

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

ALTER TABLE discounts
ALTER INDEX discount_id INVISIBLE;

MySQL发出以下错误消息：

ERROR 3522 (HY000): A primary key index cannot be invisible

---

MySQL隐形索引系统变量

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

SELECT @@optimizer_switch;

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

---