MySQL隐形索引简介
时间:2022-03-16 11:35
不可见索引允许您将索引标记为查询优化器不可用。MySQL维护不可见索引,并在与索引关联的列中的数据发生更改时使其保持最新。
默认情况下,索引是可见的。要使它们不可见,您必须在创建时或使用ALTER TABLE
命令显式声明其可见性。MySQL为我们提供了维护索引可见性的关键字VISIBLE
和INVISIBLE
关键字。
要创建不可见索引,请使用以下语句:
1 2 | CREATE INDEX index_name ON table_name( c1, c2, ...) INVISIBLE; |
在这个语法中:
- 首先,在
CREATE INDEX
子句后指定索引的名称。 - 其次,列出要添加到索引的表名和列列表。该
INVISIBLE
关键字表明您正在创建的索引是不可见的。
例如,以下语句在示例数据库extension
中的employees
表的列上创建索引,并将其标记为不可见索引:
1 2 | CREATE INDEX extension ON employees(extension) INVISIBLE; |
要更改现有索引的可见性,请使用以下语句:
1 2 | ALTER TABLE table_name ALTER INDEX index_name [VISIBLE | INVISIBLE]; |
例如,要使extension
索引可见,请使用以下语句:
1 2 | ALTER TABLE employees ALTER INDEX extension VISIBLE; |
您可以通过查询数据库中的statistics
表来查找索引及其可见性information_schema
:
1 2 3 4 5 6 7 8 | SELECT index_name, is_visible FROM information_schema.statistics WHERE table_schema = ‘classicmodels‘ AND table_name = ‘employees‘; |
这是输出:
此外,您可以使用该命令显示表的所有索引:
1 | SHOW INDEXES FROM employees; |
如前所述,查询优化器不使用不可见索引,那么为什么首先使用不可见索引?实际上,隐形索引有许多应用程序。例如,您可以使索引不可见,以查看它是否对性能产生影响,并将索引再次标记为可见。
MySQL隐形索引和主键
列上的索引不能是不可见的。如果您尝试这样做,MySQL将发出错误。
此外,隐式主键索引也不可见。当您在
NOT NULL
没有主键的表的列上定义索引时,MySQL会隐式地理解该列是主键列,并且不允许您使索引不可见。
请考虑以下示例。
首先,与UNIQUE
上一个索引NOT NULL
列:
1 2 3 4 5 6 7 8 | 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
索引不可见:
1 2 | ALTER TABLE discounts ALTER INDEX discount_id INVISIBLE; |
MySQL发出以下错误消息:
1 | Error Code: 3522. A primary key index cannot be invisible |
MySQL隐形索引系统变量
为了控制查询优化器使用的可见索引,MySQL使用系统变量的use_invisible_indexes
标志optimizer_switch
。默认情况下,use_invisible_indexes
关闭:
1 | SELECT @@optimizer_switch; |
在本教程中,您了解了MySQL隐形索引,如何创建