• 首页
  • vue
  • TypeScript
  • JavaScript
  • scss
  • css3
  • html5
  • php
  • MySQL
  • redis
  • jQuery
  • 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)col1col2col3

    key_part规格可以与结束ASCDESC以指定的索引值是否被存储在升序或降序排序。如果未指定订单说明符,则默认值为升序。ASC并且DESC不允许用于HASH索引。ASC并且DESC多值索引也不支持。在MySQL 8.0.12的,ASCDESC不是允许SPATIAL索引。

    以下各节描述了该CREATE INDEX语句的不同方面:

    • 列前缀关键部分
    • 功能关键部件
    • 唯一索引
    • 全文索引
    • 多值索引
    • 空间指数
    • 指数期权
    • 表复制和锁定选项

    列前缀关键部分

    对于字符串列,可以使用语法指定索引前缀长度来创建仅使用列值开头的索引:col_name(length)

    • 前缀可以指定CHARVARCHARBINARY,和VARBINARY关键零部件。
    • 前缀必须用于指定BLOBTEXT关键零部件。此外,BLOBTEXT列可以只对索引InnoDBMyISAMBLACKHOLE表。
    • 前缀限制以字节为单位。然而,前缀长度为索引规范CREATE TABLEALTER TABLECREATE INDEX语句解释为非二进制串类型的字符数(CHARVARCHARTEXT对于二进制串类型),并且字节数(BINARYVARBINARYBLOB)。为使用多字节字符集的非二进制字符串列指定前缀长度时,请考虑到这一点。

      前缀支持和前缀长度(如果支持)取决于存储引擎。例如,对于InnoDB使用REDUNDANTCOMPACT行格式的表,前缀的最大长度为767个字节。对于InnoDB使用DYNAMICCOMPRESSED行格式的表,前缀长度限制为3072字节。对于MyISAM表,前缀长度限制为1000个字节。该NDB存储引擎不支持前缀(见第22.1.7.6,“不支持或缺少的功能在NDB集群”)。

    如果指定的索引前缀超过最大列数据类型的大小,CREATE INDEX则按以下方式处理索引:

    • 对于非唯一索引,可能会发生错误(如果启用了严格的SQL模式),或者索引长度被减小到最大列数据类型大小之内,并且会产生警告(如果未启用严格的SQL模式)。
    • 对于唯一索引,无论采用哪种SQL模式,都会发生错误,因为减小索引长度可能会导致插入不符合指定唯一性要求的非唯一条目。

    此处显示的语句使用列的前10个字符创建索引name(假定name具有非二进制字符串类型):

    CREATE INDEX part_of_name ON 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 idx1 ON t1 ((col1 + col2));
    CREATE INDEX idx2 ON t1 ((col1 + col2), (col1 - col2), col1);
    ALTER TABLE t1 ADD INDEX ((col1 * 40) DESC);
    

    具有多个关键部分的索引可以将非功能和功能关键部分混合在一起。

    ASCDESC支持功能关键部件。

    功能关键部件必须遵守以下规则。如果关键零件定义包含不允许的构造,则会发生错误。

    • 在索引定义中,将表达式括在括号内以将它们与列或列前缀区分开。例如,这是允许的;表达式用括号括起来:

      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 tbl WHERE SUBSTRING(col1, 1, 9) = '123456789';
    SELECT * FROM tbl WHERE 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 employees WHERE 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 employees VALUES
        ('{ "name": "james", "salary": 9000 }'),
        ('{ "name": "James", "salary": 10000 }'),
        ('{ "name": "Mary", "salary": 12000 }'),
        ('{ "name": "Peter", "salary": 8000 }');
      SELECT * FROM employees WHERE 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 employees VALUES
        ('{ "name": "james", "salary": 9000 }'),
        ('{ "name": "James", "salary": 10000 }'),
        ('{ "name": "Mary", "salary": 12000 }'),
        ('{ "name": "Peter", "salary": 8000 }');
      SELECT * FROM employees WHERE 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 employees WHERE data->>'$.name' = 'James';
    +-------------------	+---------------	+
    | data              	| generated_col 	|
    +-------------------	+---------------	+
    | {"name": "James"} 	| James         	|
    +-------------------	+---------------	+
    1 row in set (0.00 sec)
    
    mysql> ALTER TABLE employees ADD INDEX idx (generated_col);
    Query OK, 0 rows affected, 1 warning (0.03 sec)
    Records: 0  Duplicates: 0  Warnings: 1
    
    mysql> SELECT * FROM employees WHERE 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 KEYUNIQUE NOT NULL索引由具有整数类型的单个列组成,则可以使用_rowid来引用SELECT语句中的索引列,如下所示:

    • _rowidPRIMARY KEY如果有一个PRIMARY KEY由单个整数组成的列,则引用该列。如果有一个PRIMARY KEY但不由单个整数组成的列,_rowid则不能使用。
    • 否则,如果_rowid第一个UNIQUE NOT NULL索引由单个整数列组成,则引用该列。如果第一个UNIQUE NOT NULL索引不由单个整数列组成,_rowid则不能使用。

    全文索引

    FULLTEXT索引仅支持InnoDBMyISAM表格,并且可以只包括CHARVARCHARTEXT列。索引总是在整个列上进行;不支持列前缀索引,并且如果指定,则将忽略任何前缀长度。有关操作的详细信息,请参见“全文搜索功能”。

    多值索引

    从MySQL 8.0.17开始,InnoDB支持多值索引。多值索引是在存储值数组的列上定义的二级索引。一个“普通”索引对每个数据记录(1:1)都有一个索引记录。多值索引可以为单个数据记录(N:1)具有多个索引记录。多值索引旨在为JSON数组建立索引。例如,在以下JSON文档中的邮政编码数组上定义的多值索引会为每个邮政编码创建一个索引记录,每个索引记录都引用同一数据记录。

    {
        "user":"Bob",
        "user_id":31,
        "zipcode":[94477,94536]
    }
    

    创建多值索引

    您可以在创建一个多值指数CREATE TABLEALTER TABLECREATE INDEX说明。这要求使用CAST(... AS ... ARRAY)索引定义,该定义将JSON数组中相同类型的标量值转换为SQL数据类型数组。然后,使用SQL数据类型数组中的值透明地生成一个虚拟列。最后,在虚拟列上创建一个功能索引(也称为虚拟索引)。是在SQL数据类型数组的值的虚拟列上定义的功能索引,该功能索引形成了多值索引。

    下表中的示例显示了zips可以在名为的表中$.zipcodeJSON列上的数组上创建多值索引的三种不同方式。在每种情况下,JSON数组都将转换为整数值的SQL数据类型数组。custinfocustomersUNSIGNED

    • CREATE TABLE只要:

      CREATE TABLE customers (
          id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
          modified DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
          custinfo JSON,
          INDEX zips( (CAST(custinfo->'$.zip' AS UNSIGNED ARRAY)) )
          );
      
    • CREATE TABLEALTER TABLE

      CREATE TABLE customers (
          id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
          modified DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
          custinfo JSON
          );
      
      ALTER TABLE customers ADD INDEX zips( (CAST(custinfo->'$.zip' AS UNSIGNED ARRAY)) );
      
    • CREATE TABLECREATE INDEX

      CREATE TABLE customers (
          id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
          modified DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
          custinfo JSON
          );
      
      CREATE INDEX zips ON customers ( (CAST(custinfo->'$.zip' AS UNSIGNED ARRAY)) );
      

    也可以将多值索引定义为复合索引的一部分。本示例显示了一个复合索引,其中包括两个单值部分(用于idmodified列)和一个多值部分(用于custinfo列):

    CREATE TABLE customers (
        id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
        modified DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        custinfo JSON
        );
    
    ALTER TABLE customers ADD 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 TABLEINSERT语句:

    mysql> CREATE TABLE customers (
    ->     id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ->     modified DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    ->     custinfo JSON
    ->     );
    Query OK, 0 rows affected (0.51 sec)
    
    mysql> INSERT INTO customers VALUES
    ->     (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)
    

    刚刚显示的三个查询都不能使用任何键。要解决此问题,我们可以zipcodeJSONcustinfo)列中的数组上添加多值索引,如下所示:

    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 customers DROP 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表达式匹配的值都作为单个平面数组存储在索引中。

    • 具有多值键部分的索引不支持排序,因此不能用作主键。出于相同的原因,不能使用ASCor DESC关键字定义多值索引。
    • 多值索引不能是覆盖索引。
    • 多值索引的每条记录的最大值数由可以在单个撤消日志页上存储的数据量决定,该数据量为65221字节(64K减去315字节的开销),这意味着最大总数键值的长度也是65221字节。键的最大数量取决于各种因素,这会阻止定义特定的限制。测试显示了一个多值索引,例如,每个记录允许多达1604个整数键。当达到限制时,将报告类似于以下错误:错误3905(HY000):多记录索引'idx'的每条记录的最大数值超出1个值。
    • 多值键部分允许的唯一表达式类型是JSON表达式。该表达式无需引用插入到索引列中的JSON文档中的现有元素,而本身在语法上必须有效。
    • 因为同一聚集索引记录的索引记录分散在整个多值索引中,所以多值索引不支持范围扫描或仅索引扫描。
    • 外键规范中不允许使用多值索引。
    • 不能为多值索引定义索引前缀。
    • 多值索引不能在强制转换为的数据上定义BINARY(请参见CAST()函数说明)。
    • 不支持在线创建多值索引,这意味着该操作使用ALGORITHM=COPY。请参阅性能和空间要求。
    • 多值索引不支持以下字符集和排序规则的以下两种组合以外的字符集和排序规则:

      1. binary具有默认binary排序规则的字符集
      2. utf8mb4具有默认utf8mb4_0900_as_cs排序规则的字符集。
    • InnoDB表列上的其他索引一样,不能使用USING HASH;创建多值索引。尝试执行此操作将导致警告:该存储引擎不支持HASH索引算法,而是使用默认存储引擎。(USING BTREE照常支持。)

    空间指数

    MyISAMInnoDBNDB,和ARCHIVE存储引擎支持空间列,比如POINTGEOMETRY。(“空间数据类型”描述了空间数据类型。)但是,各引擎对空间列索引的支持有所不同。根据以下规则,可以使用空间列上的空间索引和非空间索引。

    空间列上的空间索引具有以下特征:

    • 仅适用于InnoDBMyISAM表。指定SPATIAL INDEX其他存储引擎会导致错误。
    • 从MySQL 8.0.12开始,空间列上的索引必须SPATIAL索引。因此,SPATIAL关键字是可选的,但对于在空间列上创建索引是隐式的。
    • 仅适用于单个空间列。不能在多个空间列上创建空间索引。
    • 索引列必须为NOT NULL
    • 禁止使用列前缀长度。索引每列的全宽。
    • 不允许用于主键或唯一索引。

    在空间列非空间索引(与创建INDEXUNIQUEPRIMARY KEY)具有以下特征:

    • 允许用于任何支持空间列的存储引擎,但除外ARCHIVE
    • NULL除非索引是主键,否则列可以是。
    • SPATIAL索引的索引类型取决于存储引擎。当前,使用B树。
    • 允许,可以有一个列NULL仅值InnoDBMyISAMMEMORY表。

    指数期权

    在关键零件列表之后,可以给出索引选项。的index_option值可以是以下任意的:

    • KEY_BLOCK_SIZE[=]value

      对于MyISAM表,KEY_BLOCK_SIZE可以选择指定用于索引键块的字节大小。该值被视为提示;如有必要,可以使用其他大小。KEY_BLOCK_SIZE为单个索引定义指定的值将覆盖表级KEY_BLOCK_SIZE值。

      KEY_BLOCK_SIZEInnoDB表的索引级别不支持。请参见“ CREATE TABLE语句”。

    • index_type

      一些存储引擎允许您在创建索引时指定索引类型。例如:

      CREATE TABLE lookup (id INT) ENGINE = MEMORY;
      CREATE INDEX id_index ON lookup (id) USING BTREE;
      

      “每个存储引擎的索引类型”显示了不同存储引擎支持的允许索引类型值。如果列出了多个索引类型,则在没有给出索引类型说明符的情况下,第一个是默认值。表中未列出的存储引擎不支持index_type索引定义中的子句。

      每个存储引擎的索引类型

      储存引擎允许的索引类型
      InnoDBBTREE
      MyISAMBTREE
      MEMORY/HEAPHASHBTREE
      NDBHASHBTREE(请参见文字注释)

      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_nameindex_type

      TYPE type_name被公认是的同义词。但是,是首选形式。USING type_nameUSING

      下表显示了支持该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索引一起使用。如果全文索引和搜索操作需要特殊处理,它将解析器插件与索引关联。InnoDBMyISAM支持全文分析器插件。有关更多信息,请参见全文分析器插件和“编写全文分析器插件”。

    • COMMENT 'string'

      索引定义可以包含最多1024个字符的可选注释。

      MERGE_THRESHOLD索引页面可以被配置为使用单独的索引中的条款声明。例如:index_optionCOMMENTCREATE INDEX

      CREATE TABLE t1 (id INT);
      CREATE INDEX id_index ON t1 (id) COMMENT 'MERGE_THRESHOLD=40';
      

      如果MERGE_THRESHOLD删除某行或通过更新操作缩短某行时索引页的页面已满百分比低于该值,请InnoDB尝试将索引页与相邻的索引页合并。默认MERGE_THRESHOLD值为50,这是以前的硬编码值。

      MERGE_THRESHOLD也可以使用CREATE TABLEand ALTER TABLE语句在索引级别和表级别定义。有关更多信息,请参见“为索引页配置合并阈值”。

    • VISIBLEINVISIBLE

      指定索引可见性。默认情况下,索引可见。优化器不使用不可见索引。索引可见性的规范适用于除主键(显式或隐式)以外的索引。有关更多信息,请参见“不可见索引”。

    表复制和锁定选项

    ALGORITHMLOCK可给予子句来影响并发的表格中复制的方法和水平为读出和写入的表,而其索引被修改。它们具有与ALTER TABLE声明相同的含义。有关更多信息,请参见“ ALTER TABLE语句”

    NDB Cluster支持ALGORITHM=INPLACE使用与标准MySQL Server 相同的语法进行联机操作。有关更多信息,请参见“在NDB群集中使用ALTER TABLE进行在线操作”。