MySQL不可见索引

时间:2019-11-20 08:52:15  来源:igfitidea点击:

简介:在本教程中,您将学习MySQL不可见索引和管理索引可见性的语句。

MySQL不可见索引(Invisible Index)简介

不可见索引使您可以将索引标记为查询优化器不可用。
当与索引关联的列中的数据发生更改时,MySQL会维护不可见索引并使其保持最新状态。

默认情况下,索引可见。
要使它们不可见,必须在创建时或使用ALTER TABLE命令明确声明其可见性。
MySQL为我们提供了VISIBLE和INVISIBLE关键字,以保持索引的可见性。

要创建不可见索引,请执行以下语句:

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

使用以下语法:

  • 首先,在CREATE INDEX子句之后指定索引的名称。

  • 其次,列出要添加到索引的表名和列列表。
    INVISIBLE关键字指示您正在创建的索引不可见。

例如,以下语句在示例数据库的employees表的扩展列上创建索引,并将其标记为不可见索引:

CREATE INDEX extension 
ON employees(extension) INVISIBLE;

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

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

例如,要使扩展索引可见,请使用以下语句:

ALTER TABLE employees
ALTER INDEX extension VISIBLE;

您可以通过查询information_schema数据库中的统计信息表来找到索引及其可见性:

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

这是输出:

另外,您可以使用SHOW INDEXES命令显示表的所有索引:

SHOW INDEXES FROM employees;

如前所述,查询优化器不使用不可见索引,那么为什么首先要使用不可见索引?实际上,不可见索引具有许多应用。
例如,您可以使索引不可见,以查看它是否对性能有影响,并在索引再次标记为可见。

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

其次,尝试使Discount_id索引不可见:

ALTER TABLE discounts
ALTER INDEX discount_id INVISIBLE;

MySQL发出以下错误消息:

Error Code: 3522. A primary key index cannot be invisible

MySQL不可见索引系统变量

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

SELECT @@optimizer_switch;

在本教程中,您学习了MySQL不可见索引,如何创建不可见索引以及如何更改现有索引的可见性。