CREATE INDEX语句
3.1.15 CREATE INDEX语句
CREATE [UNIQUE |FULLTEXT |SPATIAL ]INDEX index_name [index_type]ON tbl_name (key_part,...) [index_option] [algorithm_option | lock_option] ... key_part: {col_name [(length)] | (expr)} [ASC |DESC ] index_option:KEY_BLOCK_SIZE [=] value | index_type |WITH PARSER parser_name |COMMENT 'string' | {VISIBLE |INVISIBLE } index_type:USING {BTREE |HASH } algorithm_option:ALGORITHM [=] {DEFAULT | INPLACE | COPY} lock_option:LOCK [=] {DEFAULT |NONE | SHARED | EXCLUSIVE}
通常,在使用创建表本身时,会在表上创建所有索引CREATE TABLE
。请参见“ CREATE TABLE语句”。该指南对于InnoDB
表尤为重要,在表中,主键确定数据文件中行的物理布局。CREATE INDEX
使您可以向现有表添加索引。
CREATE INDEX
映射到ALTER TABLE
语句以创建索引。请参见“ ALTER TABLE语句”。CREATE INDEX
不能用于创建一个PRIMARY KEY
;使用ALTER TABLE
代替。有关索引的更多信息,请参见“ MySQL如何使用索引”。
InnoDB
支持虚拟列上的二级索引。有关更多信息,请参见“二级索引和生成的列”。
innodb_stats_persistent
启用该设置后,在该表上创建索引后运行该表的ANALYZE TABLE
语句InnoDB
。
与MySQL 8.0.17开始,expr
用于key_part
规范可以采取的形式来创建上的一个多值索引列。请参阅多值索引。(CAST json_expression AS type ARRAY)
JSON
表单的索引规范会创建包含多个关键部分的索引。索引键值是通过合并给定键部分的值而形成的。例如指定与由索引关键字值从多列索引,和。(key_part1,key_part2,...)
(col1, col2, col3)
col1
col2
col3
甲key_part
规格可以与结束ASC
或DESC
以指定的索引值是否被存储在升序或降序排序。如果未指定订单说明符,则默认值为升序。ASC
并且DESC
不允许用于HASH
索引。ASC
并且DESC
多值索引也不支持。在MySQL 8.0.12的,ASC
而DESC
不是允许SPATIAL
索引。
以下各节描述了该CREATE INDEX
语句的不同方面:
- 列前缀关键部分
- 功能关键部件
- 唯一索引
- 全文索引
- 多值索引
- 空间指数
- 指数期权
- 表复制和锁定选项
列前缀关键部分
对于字符串列,可以使用语法指定索引前缀长度来创建仅使用列值开头的索引:col_name(length)
- 前缀可以指定
CHAR
,VARCHAR
,BINARY
,和VARBINARY
关键零部件。 - 前缀必须用于指定
BLOB
和TEXT
关键零部件。此外,BLOB
和TEXT
列可以只对索引InnoDB
,MyISAM
和BLACKHOLE
表。 前缀限制以字节为单位。然而,前缀长度为索引规范
CREATE TABLE
,ALTER TABLE
和CREATE INDEX
语句解释为非二进制串类型的字符数(CHAR
,VARCHAR
,TEXT
对于二进制串类型),并且字节数(BINARY
,VARBINARY
,BLOB
)。为使用多字节字符集的非二进制字符串列指定前缀长度时,请考虑到这一点。前缀支持和前缀长度(如果支持)取决于存储引擎。例如,对于
InnoDB
使用REDUNDANT
或COMPACT
行格式的表,前缀的最大长度为767个字节。对于InnoDB
使用DYNAMIC
或COMPRESSED
行格式的表,前缀长度限制为3072字节。对于MyISAM
表,前缀长度限制为1000个字节。该NDB
存储引擎不支持前缀(见第22.1.7.6,“不支持或缺少的功能在NDB集群”)。
如果指定的索引前缀超过最大列数据类型的大小,CREATE INDEX
则按以下方式处理索引:
- 对于非唯一索引,可能会发生错误(如果启用了严格的SQL模式),或者索引长度被减小到最大列数据类型大小之内,并且会产生警告(如果未启用严格的SQL模式)。
- 对于唯一索引,无论采用哪种SQL模式,都会发生错误,因为减小索引长度可能会导致插入不符合指定唯一性要求的非唯一条目。
此处显示的语句使用列的前10个字符创建索引name
(假定name
具有非二进制字符串类型):
CREATE INDEX part_of_nameON customer (name (10));
如果该列中的名称通常前10个字符不同,则使用此索引执行的查找不应比使用从整个name
列创建的索引慢得多。另外,对索引使用列前缀可以使索引文件更小,这可以节省大量磁盘空间,并且还可以加快INSERT
操作速度。
功能关键部件
阿“正常”索引索引列值或者列值的前缀。例如,在下表中,给定t1
行的索引条目包括完整col1
值和该值的前缀,该前缀col2
由其前10个字符组成:
CREATE TABLE t1 ( col1 VARCHAR(10), col2 VARCHAR(20),INDEX (col1, col2(10)) );
MySQL 8.0.13和更高版本支持功能性关键部分,这些关键部分索引表达式值而不是列或列前缀值。使用功能性关键部件可以索引未直接存储在表中的值。例子:
CREATE TABLE t1 (col1 INT, col2 INT,INDEX func_index ((ABS(col1))));CREATE INDEX idx1ON t1 ((col1 + col2));CREATE INDEX idx2ON t1 ((col1 + col2), (col1 - col2), col1);ALTER TABLE t1ADD INDEX ((col1 * 40)DESC );
具有多个关键部分的索引可以将非功能和功能关键部分混合在一起。
ASC
并DESC
支持功能关键部件。
功能关键部件必须遵守以下规则。如果关键零件定义包含不允许的构造,则会发生错误。
在索引定义中,将表达式括在括号内以将它们与列或列前缀区分开。例如,这是允许的;表达式用括号括起来:
INDEX ((col1 + col2), (col3 - col4))这会产生错误;表达式不包含在括号内:
INDEX (col1 + col2, col3 - col4)功能键部分不能仅由列名组成。例如,这是不允许的:
INDEX ((col1), (col2))
而是将关键部分写为无功能的关键部分,不带括号:
INDEX (col1, col2)
- 功能性关键部分表达式不能引用列前缀。有关解决方法,请参阅的讨论
SUBSTRING()
,并CAST()
在本节后面。 - 外键规格中不允许使用功能键部件。
对于CREATE TABLE ... LIKE
,目标表保留原始表中的功能关键部分。
功能索引被实现为隐藏的虚拟生成的列,这具有以下含义:
- 每个功能键部分都计入表列总数的限制;请参见“数据表的限制”。
功能性关键部件继承了适用于生成的列的所有限制。例子:
- 功能键部件仅允许生成列使用的功能。
- 不允许使用子查询,参数,变量,存储的函数和用户定义的函数。
有关适用限制的更多信息,请参见“创建表和生成的列”和“ ALTER TABLE和生成的列”。
- 虚拟生成的列本身不需要存储。索引本身像其他任何索引一样占用存储空间。
UNIQUE
包含功能关键部分的索引受支持。但是,主键不能包含功能键部分。主键要求存储生成的列,但是功能键部分被实现为虚拟生成的列,而不是存储的生成的列。
SPATIAL
并且FULLTEXT
索引不能包含功能性关键部分。
如果表不包含主键,则InnoDB
自动将第一个UNIQUE NOT NULL
索引提升为主键。UNIQUE NOT NULL
具有功能关键部分的索引不支持此功能。
如果索引重复,则非功能性索引会发出警告。包含功能性关键部分的索引不具有此功能。
要删除功能键部件引用的列,必须先删除索引。否则,将发生错误。
尽管非功能键部件支持前缀长度规范,但对于功能键部件而言这是不可能的。解决方案是使用SUBSTRING()
(或CAST()
如本节稍后所述)。对于包含SUBSTRING()
要在查询中使用的功能的功能键部件,WHERE
子句必须包含SUBSTRING()
具有相同参数的参数。在以下示例中,只有第二个SELECT
能够使用索引,因为这是唯一一个参数SUBSTRING()
与索引规范匹配的查询:
CREATE TABLE tbl ( col1 LONGTEXT,INDEX idx1 ((SUBSTRING(col1, 1, 10))) );SELECT *FROM tblWHERE SUBSTRING(col1, 1, 9) = '123456789';SELECT *FROM tblWHERE SUBSTRING(col1, 1, 10) = '1234567890';
功能性关键部分支持对无法通过其他方式索引的值(例如JSON
值)进行索引。但是,必须正确完成此操作才能获得所需的效果。例如,此语法不起作用:
CREATE TABLE employees (data JSON,INDEX ((data ->>'$.name')) );
语法失败是因为:
- 该
->>
运营商转变成JSON_UNQUOTE(JSON_EXTRACT(...))
。 JSON_UNQUOTE()
返回数据类型为的值,LONGTEXT
从而为隐藏的生成列分配了相同的数据类型。- MySQL无法索引
LONGTEXT
在键部分没有前缀长度的指定列,并且功能键部分不允许前缀长度。
要索引该JSON
列,您可以尝试使用CAST()
如下功能:
CREATE TABLE employees (data JSON,INDEX ((CAST(data ->>'$.name'AS CHAR(30)))) );
为隐藏的生成的列分配了VARCHAR(30)
数据类型,该数据类型可以建立索引。但是,当尝试使用索引时,此方法会产生一个新问题:
CAST()
返回具有排序规则的字符串utf8mb4_0900_ai_ci
(服务器默认排序规则)。JSON_UNQUOTE()
返回带有排序规则的字符串utf8mb4_bin
(硬编码)。
结果,在先前表定义中的索引表达式WHERE
与以下查询中的子句表达式之间存在排序规则不匹配:
SELECT *FROM employeesWHERE data ->>'$.name' = 'James';
未使用索引,因为查询和索引中的表达式不同。为了支持这种方案的功能键部分,优化自动去除CAST()
寻找一个索引使用时,但仅如果查询表达式的索引表达式匹配的排序规则。对于要使用具有功能性关键部分的索引,可以使用以下两种解决方案之一(尽管它们在效果上有所不同):
解决方案1.为索引表达式分配与以下排序规则相同的排序规则
JSON_UNQUOTE()
:CREATE TABLE employees (data JSON,INDEX idx ((CAST(data ->>"$.name"AS CHAR(30))COLLATE utf8mb4_bin)) );INSERT INTO employeesVALUES ('{ "name": "james", "salary": 9000 }'), ('{ "name": "James", "salary": 10000 }'), ('{ "name": "Mary", "salary": 12000 }'), ('{ "name": "Peter", "salary": 8000 }');SELECT *FROM employeesWHERE data ->>'$.name' = 'James';该
->>
操作是一样的JSON_UNQUOTE(JSON_EXTRACT(...))
,并JSON_UNQUOTE()
返回与归类的字符串utf8mb4_bin
。因此,该比较区分大小写,并且只有一行匹配:+------------------------------------ + | data | +------------------------------------ + | {"name": "James", "salary": 10000} | +------------------------------------ +
解决方案2.在查询中指定完整表达式:
CREATE TABLE employees (data JSON,INDEX idx ((CAST(data ->>"$.name"AS CHAR(30)))) );INSERT INTO employeesVALUES ('{ "name": "james", "salary": 9000 }'), ('{ "name": "James", "salary": 10000 }'), ('{ "name": "Mary", "salary": 12000 }'), ('{ "name": "Peter", "salary": 8000 }');SELECT *FROM employeesWHERE CAST(data ->>'$.name'AS CHAR(30)) = 'James';CAST()
返回一个带有collation的字符串utf8mb4_0900_ai_ci
,因此比较不区分大小写,并且两行匹配:+------------------------------------ + | data | +------------------------------------ + | {"name": "james", "salary": 9000} | | {"name": "James", "salary": 10000} | +------------------------------------ +
请注意,尽管优化器支持CAST()
使用索引生成的列自动剥离,但是以下方法不起作用,因为它在使用索引和不使用索引的情况下都会产生不同的结果(错误#27337092):
mysql>CREATE TABLE employees (data JSON, generated_col VARCHAR(30)AS (CAST(data ->>'$.name'AS CHAR(30))) ); Query OK, 0 rows affected, 1 warning (0.03 sec) mysql>INSERT INTO employees (data )VALUES ('{"name": "james"}'), ('{"name": "James"}'); Query OK, 2 rows affected, 1 warning (0.01 sec) Records: 2 Duplicates: 0 Warnings: 1 mysql>SELECT *FROM employeesWHERE data ->>'$.name' = 'James'; +------------------- +--------------- + | data | generated_col | +------------------- +--------------- + | {"name": "James"} | James | +------------------- +--------------- + 1 row in set (0.00 sec) mysql>ALTER TABLE employeesADD INDEX idx (generated_col); Query OK, 0 rows affected, 1 warning (0.03 sec) Records: 0 Duplicates: 0 Warnings: 1 mysql>SELECT *FROM employeesWHERE data ->>'$.name' = 'James'; +------------------- +--------------- + | data | generated_col | +------------------- +--------------- + | {"name": "james"} | james | | {"name": "James"} | James | +------------------- +--------------- + 2 rows in set (0.01 sec)
唯一索引
一个UNIQUE
索引创建的约束,使得该指数的所有值必须是不同的。如果您尝试添加键值与现有行匹配的新行,则会发生错误。如果为UNIQUE
索引中的列指定前缀值,则列值在前缀长度内必须唯一。一个UNIQUE
索引,可以多次NULL
进行,可以包含列的值NULL
。
如果表的PRIMARY KEY
或UNIQUE NOT NULL
索引由具有整数类型的单个列组成,则可以使用_rowid
来引用SELECT
语句中的索引列,如下所示:
_rowid
PRIMARY KEY
如果有一个PRIMARY KEY
由单个整数组成的列,则引用该列。如果有一个PRIMARY KEY
但不由单个整数组成的列,_rowid
则不能使用。- 否则,如果
_rowid
第一个UNIQUE NOT NULL
索引由单个整数列组成,则引用该列。如果第一个UNIQUE NOT NULL
索引不由单个整数列组成,_rowid
则不能使用。
全文索引
FULLTEXT
索引仅支持InnoDB
和MyISAM
表格,并且可以只包括CHAR
,VARCHAR
和TEXT
列。索引总是在整个列上进行;不支持列前缀索引,并且如果指定,则将忽略任何前缀长度。有关操作的详细信息,请参见“全文搜索功能”。
多值索引
从MySQL 8.0.17开始,InnoDB
支持多值索引。多值索引是在存储值数组的列上定义的二级索引。一个“普通”索引对每个数据记录(1:1)都有一个索引记录。多值索引可以为单个数据记录(N:1)具有多个索引记录。多值索引旨在为JSON
数组建立索引。例如,在以下JSON文档中的邮政编码数组上定义的多值索引会为每个邮政编码创建一个索引记录,每个索引记录都引用同一数据记录。
{ "user":"Bob", "user_id":31, "zipcode":[94477,94536] }
创建多值索引
您可以在创建一个多值指数CREATE TABLE
,ALTER TABLE
或CREATE INDEX
说明。这要求使用CAST(... AS ... ARRAY)
索引定义,该定义将JSON
数组中相同类型的标量值转换为SQL数据类型数组。然后,使用SQL数据类型数组中的值透明地生成一个虚拟列。最后,在虚拟列上创建一个功能索引(也称为虚拟索引)。是在SQL数据类型数组的值的虚拟列上定义的功能索引,该功能索引形成了多值索引。
下表中的示例显示了zips
可以在名为的表中$.zipcode
的JSON
列上的数组上创建多值索引的三种不同方式。在每种情况下,JSON数组都将转换为整数值的SQL数据类型数组。custinfo
customers
UNSIGNED
CREATE TABLE
只要:CREATE TABLE customers ( id BIGINT NOT NULLAUTO_INCREMENT PRIMARY KEY , modified DATETIMEDEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP , custinfo JSON,INDEX zips( (CAST(custinfo->'$.zip'AS UNSIGNED ARRAY )) ) );CREATE TABLE
加ALTER TABLE
:CREATE TABLE customers ( id BIGINT NOT NULLAUTO_INCREMENT PRIMARY KEY , modified DATETIMEDEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP , custinfo JSON );ALTER TABLE customersADD INDEX zips( (CAST(custinfo->'$.zip'AS UNSIGNED ARRAY )) );CREATE TABLE
加CREATE INDEX
:CREATE TABLE customers ( id BIGINT NOT NULLAUTO_INCREMENT PRIMARY KEY , modified DATETIMEDEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP , custinfo JSON );CREATE INDEX zipsON customers ( (CAST(custinfo->'$.zip'AS UNSIGNED ARRAY )) );
也可以将多值索引定义为复合索引的一部分。本示例显示了一个复合索引,其中包括两个单值部分(用于id
和modified
列)和一个多值部分(用于custinfo
列):
CREATE TABLE customers ( id BIGINT NOT NULLAUTO_INCREMENT PRIMARY KEY , modified DATETIMEDEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP , custinfo JSON );ALTER TABLE customersADD INDEX comp(id, modified, (CAST(custinfo->'$.zipcode'AS UNSIGNED ARRAY )) );
复合索引中只能使用一个多值键部分。多值键部分可以相对于键的其他部分以任何顺序使用。换句话说,ALTER TABLE
刚刚显示的语句可能已经使用comp(id,(CAST(custinfo->'$.zipcode' AS UNSIGNED ARRAY), modified))
(或任何其他排序)并且仍然有效。
使用多值索引
在WHERE
子句中指定以下功能时,优化器将使用多值索引来获取记录:
MEMBER OF()
JSON_CONTAINS()
JSON_OVERLAPS()
我们可以通过创建和填充证明这一点customers
使用下表CREATE TABLE
和INSERT
语句:
mysql>CREATE TABLE customers ( -> id BIGINT NOT NULLAUTO_INCREMENT PRIMARY KEY , -> modified DATETIMEDEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP , -> custinfo JSON -> ); Query OK, 0 rows affected (0.51 sec) mysql>INSERT INTO customersVALUES -> (NULL, NOW(), '{"user":"Jack","user_id":37,"zipcode":[94582,94536]}'), -> (NULL, NOW(), '{"user":"Jill","user_id":22,"zipcode":[94568,94507,94582]}'), -> (NULL, NOW(), '{"user":"Bob","user_id":31,"zipcode":[94477,94507]}'), -> (NULL, NOW(), '{"user":"Mary","user_id":72,"zipcode":[94536]}'), -> (NULL, NOW(), '{"user":"Ted","user_id":56,"zipcode":[94507,94582]}'); Query OK, 5 rows affected (0.07 sec) Records: 5 Duplicates: 0 Warnings: 0
首先,我们在执行三个查询customers
表,利用每一个MEMBER OF()
,JSON_CONTAINS()
和JSON_OVERLAPS()
,从这里显示每个查询的结果:
mysql>SELECT *FROM customers ->WHERE 94507 MEMBER OF(custinfo->'$.zipcode'); +---- +--------------------- +------------------------------------------------------------------- + | id | modified | custinfo | +---- +--------------------- +------------------------------------------------------------------- + | 2 | 2019 -06 -29 22:23:12 | {"user": "Jill", "user_id": 22, "zipcode": [94568, 94507, 94582]} | | 3 | 2019 -06 -29 22:23:12 | {"user": "Bob", "user_id": 31, "zipcode": [94477, 94507]} | | 5 | 2019 -06 -29 22:23:12 | {"user": "Ted", "user_id": 56, "zipcode": [94507, 94582]} | +---- +--------------------- +------------------------------------------------------------------- + 3 rows in set (0.00 sec) mysql>SELECT *FROM customers ->WHERE JSON_CONTAINS(custinfo->'$.zipcode', CAST('[94507,94582]'AS JSON)); +---- +--------------------- +------------------------------------------------------------------- + | id | modified | custinfo | +---- +--------------------- +------------------------------------------------------------------- + | 2 | 2019 -06 -29 22:23:12 | {"user": "Jill", "user_id": 22, "zipcode": [94568, 94507, 94582]} | | 5 | 2019 -06 -29 22:23:12 | {"user": "Ted", "user_id": 56, "zipcode": [94507, 94582]} | +---- +--------------------- +------------------------------------------------------------------- + 2 rows in set (0.00 sec) mysql>SELECT *FROM customers ->WHERE JSON_OVERLAPS(custinfo->'$.zipcode', CAST('[94507,94582]'AS JSON)); +---- +--------------------- +------------------------------------------------------------------- + | id | modified | custinfo | +---- +--------------------- +------------------------------------------------------------------- + | 1 | 2019 -06 -29 22:23:12 | {"user": "Jack", "user_id": 37, "zipcode": [94582, 94536]} | | 2 | 2019 -06 -29 22:23:12 | {"user": "Jill", "user_id": 22, "zipcode": [94568, 94507, 94582]} | | 3 | 2019 -06 -29 22:23:12 | {"user": "Bob", "user_id": 31, "zipcode": [94477, 94507]} | | 5 | 2019 -06 -29 22:23:12 | {"user": "Ted", "user_id": 56, "zipcode": [94507, 94582]} | +---- +--------------------- +------------------------------------------------------------------- + 4 rows in set (0.00 sec)
接下来,我们EXPLAIN
对前三个查询中的每一个运行:
mysql>EXPLAIN SELECT *FROM customers ->WHERE 94507 MEMBER OF(custinfo->'$.zipcode'); +---- +------------- +----------- +------------ +------ +--------------- +------ +--------- +------ +------ +---------- +------------- + | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +---- +------------- +----------- +------------ +------ +--------------- +------ +--------- +------ +------ +---------- +------------- + | 1 | SIMPLE | customers | NULL | ALL | NULL | NULL | NULL | NULL | 5 | 100.00 | Using where | +---- +------------- +----------- +------------ +------ +--------------- +------ +--------- +------ +------ +---------- +------------- + 1 row in set, 1 warning (0.00 sec) mysql>EXPLAIN SELECT *FROM customers ->WHERE JSON_CONTAINS(custinfo->'$.zipcode', CAST('[94507,94582]'AS JSON)); +---- +------------- +----------- +------------ +------ +--------------- +------ +--------- +------ +------ +---------- +------------- + | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +---- +------------- +----------- +------------ +------ +--------------- +------ +--------- +------ +------ +---------- +------------- + | 1 | SIMPLE | customers | NULL | ALL | NULL | NULL | NULL | NULL | 5 | 100.00 | Using where | +---- +------------- +----------- +------------ +------ +--------------- +------ +--------- +------ +------ +---------- +------------- + 1 row in set, 1 warning (0.00 sec) mysql>EXPLAIN SELECT *FROM customers ->WHERE JSON_OVERLAPS(custinfo->'$.zipcode', CAST('[94507,94582]'AS JSON)); +---- +------------- +----------- +------------ +------ +--------------- +------ +--------- +------ +------ +---------- +------------- + | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +---- +------------- +----------- +------------ +------ +--------------- +------ +--------- +------ +------ +---------- +------------- + | 1 | SIMPLE | customers | NULL | ALL | NULL | NULL | NULL | NULL | 5 | 100.00 | Using where | +---- +------------- +----------- +------------ +------ +--------------- +------ +--------- +------ +------ +---------- +------------- + 1 row in set, 1 warning (0.01 sec)
刚刚显示的三个查询都不能使用任何键。要解决此问题,我们可以zipcode
在JSON
(custinfo
)列中的数组上添加多值索引,如下所示:
mysql>ALTER TABLE customers ->ADD INDEX zips( (CAST(custinfo->'$.zipcode'AS UNSIGNED ARRAY )) ); Query OK, 0 rows affected (0.47 sec) Records: 0 Duplicates: 0 Warnings: 0
当我们EXPLAIN
再次运行前面的语句时,我们现在可以看到查询可以(并且确实)使用zips
刚刚创建的索引:
mysql>EXPLAIN SELECT *FROM customers ->WHERE 94507 MEMBER OF(custinfo->'$.zipcode'); +---- +------------- +----------- +------------ +------ +--------------- +------ +--------- +------- +------ +---------- +------------- + | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +---- +------------- +----------- +------------ +------ +--------------- +------ +--------- +------- +------ +---------- +------------- + | 1 | SIMPLE | customers | NULL | ref | zips | zips | 9 | const | 1 | 100.00 | Using where | +---- +------------- +----------- +------------ +------ +--------------- +------ +--------- +------- +------ +---------- +------------- + 1 row in set, 1 warning (0.00 sec) mysql>EXPLAIN SELECT *FROM customers ->WHERE JSON_CONTAINS(custinfo->'$.zipcode', CAST('[94507,94582]'AS JSON)); +---- +------------- +----------- +------------ +------- +--------------- +------ +--------- +------ +------ +---------- +------------- + | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +---- +------------- +----------- +------------ +------- +--------------- +------ +--------- +------ +------ +---------- +------------- + | 1 | SIMPLE | customers | NULL | range | zips | zips | 9 | NULL | 6 | 100.00 | Using where | +---- +------------- +----------- +------------ +------- +--------------- +------ +--------- +------ +------ +---------- +------------- + 1 row in set, 1 warning (0.00 sec) mysql>EXPLAIN SELECT *FROM customers ->WHERE JSON_OVERLAPS(custinfo->'$.zipcode', CAST('[94507,94582]'AS JSON)); +---- +------------- +----------- +------------ +------- +--------------- +------ +--------- +------ +------ +---------- +------------- + | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +---- +------------- +----------- +------------ +------- +--------------- +------ +--------- +------ +------ +---------- +------------- + | 1 | SIMPLE | customers | NULL | range | zips | zips | 9 | NULL | 6 | 100.00 | Using where | +---- +------------- +----------- +------------ +------- +--------------- +------ +--------- +------ +------ +---------- +------------- + 1 row in set, 1 warning (0.01 sec)
可以将多值索引定义为唯一键。如果定义为唯一键,则尝试插入多值索引中已经存在的值会返回重复的键错误。如果已经存在重复值,则尝试添加唯一的多值索引失败,如下所示:
mysql>ALTER TABLE customersDROP INDEX zips; Query OK, 0 rows affected (0.55 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql>ALTER TABLE customers ->ADD UNIQUE INDEX zips((CAST(custinfo->'$.zipcode'AS UNSIGNED ARRAY ))); ERROR 1062 (23000): Duplicate entry '[94507, ' for key 'customers.zips' mysql>ALTER TABLE customers ->ADD INDEX zips((CAST(custinfo->'$.zipcode'AS UNSIGNED ARRAY ))); Query OK, 0 rows affected (0.36 sec) Records: 0 Duplicates: 0 Warnings: 0
多值索引的特征
多值索引具有此处列出的其他特征:
- 影响多值索引的DML操作与影响普通索引的DML操作的处理方式相同,唯一的区别是单个聚簇索引记录可能有多个插入或更新。
可空性和多值索引:
- 如果多值键部分的数组为空,则索引中不会添加任何条目,并且索引扫描无法访问数据记录。
- 如果多值关键部分生成返回一个
NULL
值,则包含一个条目NULL
将添加到多值索引中。如果关键部分定义为NOT NULL
,则报告错误。 - 如果类型数组列设置为
NULL
,则存储引擎将存储包含NULL
指向数据记录的单个记录。 JSON
在索引数组中不允许使用null值。如果返回的任何值为NULL
,则将其视为JSON空,并报告无效的JSON值错误。
- 由于多值索引是虚拟列上的虚拟索引,因此它们必须遵循与虚拟生成列上的二级索引相同的规则。
- 没有为空数组添加索引记录。
多值索引的限制和限制
多值索引受此处列出的限制和限制:
每个多值索引仅允许一个多值键部分。但是,该
CAST(... AS ... ARRAY)
表达式可以引用JSON
文档中的多个数组,如下所示:CAST(
data ->'$.arr[*][*]'AS UNSIGNED ARRAY )在这种情况下,所有与JSON表达式匹配的值都作为单个平面数组存储在索引中。
- 具有多值键部分的索引不支持排序,因此不能用作主键。出于相同的原因,不能使用
ASC
orDESC
关键字定义多值索引。 - 多值索引不能是覆盖索引。
- 多值索引的每条记录的最大值数由可以在单个撤消日志页上存储的数据量决定,该数据量为65221字节(64K减去315字节的开销),这意味着最大总数键值的长度也是65221字节。键的最大数量取决于各种因素,这会阻止定义特定的限制。测试显示了一个多值索引,例如,每个记录允许多达1604个整数键。当达到限制时,将报告类似于以下错误:错误3905(HY000):多记录索引'idx'的每条记录的最大数值超出1个值。
- 多值键部分允许的唯一表达式类型是
JSON
表达式。该表达式无需引用插入到索引列中的JSON文档中的现有元素,而本身在语法上必须有效。 - 因为同一聚集索引记录的索引记录分散在整个多值索引中,所以多值索引不支持范围扫描或仅索引扫描。
- 外键规范中不允许使用多值索引。
- 不能为多值索引定义索引前缀。
- 多值索引不能在强制转换为的数据上定义
BINARY
(请参见CAST()
函数说明)。 - 不支持在线创建多值索引,这意味着该操作使用
ALGORITHM=COPY
。请参阅性能和空间要求。 多值索引不支持以下字符集和排序规则的以下两种组合以外的字符集和排序规则:
binary
具有默认binary
排序规则的字符集utf8mb4
具有默认utf8mb4_0900_as_cs
排序规则的字符集。
- 与
InnoDB
表列上的其他索引一样,不能使用USING HASH
;创建多值索引。尝试执行此操作将导致警告:该存储引擎不支持HASH索引算法,而是使用默认存储引擎。(USING BTREE
照常支持。)
空间指数
在MyISAM
,InnoDB
,NDB
,和ARCHIVE
存储引擎支持空间列,比如POINT
和GEOMETRY
。(“空间数据类型”描述了空间数据类型。)但是,各引擎对空间列索引的支持有所不同。根据以下规则,可以使用空间列上的空间索引和非空间索引。
空间列上的空间索引具有以下特征:
- 仅适用于
InnoDB
和MyISAM
表。指定SPATIAL INDEX
其他存储引擎会导致错误。 - 从MySQL 8.0.12开始,空间列上的索引必须是
SPATIAL
索引。因此,SPATIAL
关键字是可选的,但对于在空间列上创建索引是隐式的。 - 仅适用于单个空间列。不能在多个空间列上创建空间索引。
- 索引列必须为
NOT NULL
。 - 禁止使用列前缀长度。索引每列的全宽。
- 不允许用于主键或唯一索引。
在空间列非空间索引(与创建INDEX
,UNIQUE
或PRIMARY KEY
)具有以下特征:
- 允许用于任何支持空间列的存储引擎,但除外
ARCHIVE
。 NULL
除非索引是主键,否则列可以是。- 非
SPATIAL
索引的索引类型取决于存储引擎。当前,使用B树。 - 允许,可以有一个列
NULL
仅值InnoDB
,MyISAM
和MEMORY
表。
指数期权
在关键零件列表之后,可以给出索引选项。的index_option
值可以是以下任意的:
KEY_BLOCK_SIZE[=]value
对于
MyISAM
表,KEY_BLOCK_SIZE
可以选择指定用于索引键块的字节大小。该值被视为提示;如有必要,可以使用其他大小。KEY_BLOCK_SIZE
为单个索引定义指定的值将覆盖表级KEY_BLOCK_SIZE
值。KEY_BLOCK_SIZE
InnoDB
表的索引级别不支持。请参见“ CREATE TABLE语句”。index_type
一些存储引擎允许您在创建索引时指定索引类型。例如:
CREATE TABLE lookup (id INT)ENGINE =MEMORY ;CREATE INDEX id_indexON lookup (id)USING BTREE ;“每个存储引擎的索引类型”显示了不同存储引擎支持的允许索引类型值。如果列出了多个索引类型,则在没有给出索引类型说明符的情况下,第一个是默认值。表中未列出的存储引擎不支持
index_type
索引定义中的子句。每个存储引擎的索引类型
储存引擎 允许的索引类型 InnoDB
BTREE
MyISAM
BTREE
MEMORY
/HEAP
HASH
,BTREE
NDB
HASH
,BTREE
(请参见文字注释)该
index_type
子句不能用于FULLTEXT INDEX
或(在MySQL 8.0.12之前)SPATIAL INDEX
规范。全文索引实现取决于存储引擎。空间索引被实现为R树索引。如果指定的索引类型对于给定的存储引擎无效,但是该引擎可以使用另一种索引类型而不影响查询结果,则引擎将使用可用的类型。解析器将识别
RTREE
为类型名称。从MySQL 8.0.12开始,仅允许用于SPATIAL
索引。8.0.12之前的版本,RTREE
不能为任何存储引擎指定。BTREE
索引由NDB
存储引擎实现为T树索引。注意
对于
NDB
表列上的索引,USING
只能为唯一索引或主键指定该选项。USING HASH
防止创建有序索引;否则,在NDB
表上创建唯一索引或主键会自动导致同时创建有序索引和哈希索引,每个索引都索引同一组列。对于包含
NULL
表的一个或多个列的唯一索引,NDB
哈希索引只能用于查找文字值,这意味着IS[NOT] NULL
条件需要对表进行全面扫描。一种解决方法是,确保NULL
始终使用包含有序索引的方式创建使用此类表上一个或多个列的唯一索引。也就是说,USING HASH
在创建索引时避免使用。如果指定的索引类型对于给定的存储引擎无效,但是该引擎可以使用另一种索引类型而不影响查询结果,则引擎将使用可用的类型。解析器识别
RTREE
为类型名称,但是当前无法为任何存储引擎指定该名称。注意
不赞成使用
index_type
该子句之前的选项;在将来的MySQL版本中,将不再支持在此位置使用该选项。如果在较早和较晚的位置都给出了一个选项,则以最后一个选项为准。ON tbl_name
index_type
TYPE type_name
被公认是的同义词。但是,是首选形式。USING type_name
USING
下表显示了支持该
index_type
选件的存储引擎的索引特征。InnoDB存储引擎索引特征
索引类别 索引类型 存储空值 允许多个NULL值 IS NULL扫描类型 IS NOT NULL扫描类型 首要的关键 BTREE
没有 没有 不适用 不适用 独特 BTREE
是 是 指数 指数 键 BTREE
是 是 指数 指数 FULLTEXT
不适用 是 是 表 表 SPATIAL
不适用 没有 没有 不适用 不适用 MyISAM存储引擎索引特征
索引类别 索引类型 存储空值 允许多个NULL值 IS NULL扫描类型 IS NOT NULL扫描类型 首要的关键 BTREE
没有 没有 不适用 不适用 独特 BTREE
是 是 指数 指数 键 BTREE
是 是 指数 指数 FULLTEXT
不适用 是 是 表 表 SPATIAL
不适用 没有 没有 不适用 不适用 MEMORY存储引擎索引特征
索引类别 索引类型 存储空值 允许多个NULL值 IS NULL扫描类型 IS NOT NULL扫描类型 首要的关键 BTREE
没有 没有 不适用 不适用 独特 BTREE
是 是 指数 指数 键 BTREE
是 是 指数 指数 首要的关键 HASH
没有 没有 不适用 不适用 独特 HASH
是 是 指数 指数 键 HASH
是 是 指数 指数 NDB存储引擎索引特征
索引类别 索引类型 存储空值 允许多个NULL值 IS NULL扫描类型 IS NOT NULL扫描类型 首要的关键 BTREE
没有 没有 指数 指数 独特 BTREE
是 是 指数 指数 键 BTREE
是 是 指数 指数 首要的关键 HASH
没有 没有 表(见注1) 表(见注1) 独特 HASH
是 是 表(见注1) 表(见注1) 键 HASH
是 是 表(见注1) 表(见注1) 表注:
1.
USING HASH
防止创建隐式有序索引。WITH PARSER parser_name
此选项只能与
FULLTEXT
索引一起使用。如果全文索引和搜索操作需要特殊处理,它将解析器插件与索引关联。InnoDB
并MyISAM
支持全文分析器插件。有关更多信息,请参见全文分析器插件和“编写全文分析器插件”。COMMENT 'string'
索引定义可以包含最多1024个字符的可选注释。
该
MERGE_THRESHOLD
索引页面可以被配置为使用单独的索引中的条款声明。例如:index_option
COMMENT
CREATE INDEX
CREATE TABLE t1 (id INT);CREATE INDEX id_indexON t1 (id)COMMENT 'MERGE_THRESHOLD=40';如果
MERGE_THRESHOLD
删除某行或通过更新操作缩短某行时索引页的页面已满百分比低于该值,请InnoDB
尝试将索引页与相邻的索引页合并。默认MERGE_THRESHOLD
值为50,这是以前的硬编码值。MERGE_THRESHOLD
也可以使用CREATE TABLE
andALTER TABLE
语句在索引级别和表级别定义。有关更多信息,请参见“为索引页配置合并阈值”。VISIBLE
,INVISIBLE
指定索引可见性。默认情况下,索引可见。优化器不使用不可见索引。索引可见性的规范适用于除主键(显式或隐式)以外的索引。有关更多信息,请参见“不可见索引”。
表复制和锁定选项
ALGORITHM
和LOCK
可给予子句来影响并发的表格中复制的方法和水平为读出和写入的表,而其索引被修改。它们具有与ALTER TABLE
声明相同的含义。有关更多信息,请参见“ ALTER TABLE语句”
NDB Cluster支持ALGORITHM=INPLACE
使用与标准MySQL Server 相同的语法进行联机操作。有关更多信息,请参见“在NDB群集中使用ALTER TABLE进行在线操作”。