10bet网址
MySQL 8.0参考手册
相关的文档10bet官方网站 下载本手册
PDF(美版)- 41.5 mb
PDF (A4)- 41.6 mb
HTML下载(TGZ)- 9.3 mb
HTML下载(Zip)- 9.3 mb
手册页(TGZ)- 262.1 kb
手册页(Zip)- 372.2 kb
信息(Gzip)- 4.0 mb
信息(邮政编码)- 4.0 mb
本手册节选

13.1.15创建INDEX语句

创建[unique |全文|空间]索引index_nameindex_type)tbl_namekey_part,……)index_option] [algorithm_option|lock_option)……key_part: {col_name((长度[b] b]expr)} [asc | desc]index_option: {key_block_size [=]价值|index_type|与解析器parser_name|评论”字符串' |{可见|不可见}| engine_attribute [=] '字符串' | secondary_engine_attribute [=] '字符串'}index_type:使用{btree | hash}algorithm_option: algorithm[=]{默认的|代替|拷贝}lock_option:锁[=]{默认|无|共享|独占}

通常,在创建表本身时创建表上的所有索引创建表.看到第13.1.20节," CREATE TABLE语句".这条指导原则对以下人员尤其重要InnoDB表,其中主键决定数据文件中行的物理布局。创建索引使您能够向现有表添加索引。

创建索引映射到ALTER TABLE语句创建索引。看到第13.1.9节“ALTER TABLE语句”创建索引不能用于创建主键;使用ALTER TABLE代替。有关索引的更多信息,请参见第8.3.1节,MySQL如何使用索引

InnoDB支持虚拟列上的二级索引。有关更多信息,请参见第13.1.20.9节,“二级索引和生成的列”

innodb_stats_persistent设置已启用,运行分析表声明InnoDB在该表上创建索引后。

从MySQL 8.0.17开始expr对于一个key_part规范可以采用以下形式(投json_expression作为类型数组)创建一个多值索引JSON列。看到多值的索引

表单的索引规范key_part1key_part2,……)创建具有多个关键部分的索引。索引键值是通过连接给定键部分的值来形成的。例如(col1, col2, col3)指定一个多列索引,索引键由的值组成col1col2,col3

一个key_part规范可以以ASCDESC指定索引值是按升序还是降序存储。如果没有给出顺序说明符,默认为升序。ASCDESC是不允许的哈希索引。ASCDESC也不支持多值索引。从MySQL 8.0.12开始,ASCDESC是不允许的空间索引。

的不同方面创建索引声明:

列前缀关键部件

对于字符串列,可以创建仅使用列值前导部分的索引,使用col_name长度指定索引前缀长度的语法:

如果指定的索引前缀超过最大列数据类型大小,创建索引对索引的处理如下:

  • 对于非唯一索引,要么发生错误(如果启用了严格SQL模式),要么将索引长度减少到最大列数据类型大小之内,并产生警告(如果未启用严格SQL模式)。

  • 对于唯一索引,无论采用何种SQL模式都会发生错误,因为减少索引长度可能会插入不符合指定唯一性要求的非唯一项。

的前10个字符创建一个索引名字列(假设名字具有非二进制字符串类型):

在客户(name(10))上创建索引

如果列中的名称通常在前10个字符中不同,那么使用此索引执行的查找应该不会比使用从整个列创建的索引慢得多名字列。此外,对索引使用列前缀可以使索引文件更小,这可以节省大量磁盘空间,还可以提高速度插入操作。

功能关键部件

一个正常的索引索引列值或列值的前缀。例如,在下面的表中,给定的索引项t1行包含完整的col1的前缀col2由前10个字符组成的值:

CREATE TABLE t1 (col1 VARCHAR(10), col2 VARCHAR(20), INDEX (col1, col2(10));

MySQL 8.0.13及更高版本支持索引表达式值而不是列或列前缀值的功能键部分。使用功能键部件可以对不直接存储在表中的值建立索引。例子:

创建表t1 (col1 INT, col2 INT, INDEX func_index) ((ABS(col1))));创建索引idx1 ((col1 + col2));CREATE INDEX idx2 ON t1 ((col1 + col2), (col1 - col2), col1);添加索引((col1 * 40));

具有多个键部分的索引可以混合非功能键部分和功能键部分。

ASCDESC支持功能关键部件。

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

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

    INDEX ((col1 + col2), (col3 - col4))

    这会产生一个错误;表达式没有被括在圆括号内:

    索引(col1 + col2, col3 - col4)
  • 功能键部分不能仅由列名组成。例如,这是不允许的:

    INDEX ((col1), (col2))

    相反,将关键部分写成非功能关键部分,不带括号:

    索引(col1, col2)
  • 功能键部分表达式不能引用列前缀。有关解决方法的讨论SUBSTRING ()把()在本节后面。

  • 外键规格中不允许使用功能键部件。

创建表…就像,目标表保留原始表的功能关键部分。

函数索引是作为隐藏的虚拟生成列实现的,这意味着:

独特的支持包含功能关键部分的索引。但是,主键不能包含功能键部分。主键要求存储生成的列,但功能键部分实现为虚拟生成列,而不是存储生成列。

空间全文索引不能有功能关键部分。

如果一个表不包含主键,InnoDB自动提升第一个唯一而非空主键的索引。不支持唯一而非空具有功能关键部分的索引。

如果存在重复索引,非功能索引将发出警告。包含功能关键部分的索引没有此特性。

要删除被功能键部分引用的列,必须首先删除索引。否则,将出现错误。

尽管非功能键部件支持前缀长度规范,但这对于功能键部件是不可能的。解决办法是使用SUBSTRING ()(或把(),如本节后面所述)。的功能关键部件SUBSTRING ()要在查询中使用的在哪里条款必须包含SUBSTRING ()用同样的论点。在下面的示例中,只有第二种选择是否能够使用索引,因为这是唯一的查询,其中的参数为SUBSTRING ()匹配索引规格:

创建表idx1 ((SUBSTRING(col1, 1,10)))));SELECT * FROM表WHERE SUBSTRING(col1, 1,9) = '123456789';SELECT * FROM表WHERE SUBSTRING(col1, 1,10) = '1234567890';

功能关键部件支持对其他方式无法索引的值进行索引,例如JSON值。然而,这必须正确地完成,以达到预期的效果。例如,这个语法不起作用:

CREATE TABLE employees (data JSON, INDEX ((data->>'$.name')));

语法失败的原因是:

要索引JSON列,你可以试着用把()功能如下:

创建表employees (data JSON, INDEX (CAST(data->>'$.name') AS CHAR(30)))));

将隐藏生成的列分配给VARCHAR (30)数据类型,它可以被索引。但是这种方法在尝试使用索引时产生了一个新问题:

  • 把()返回带有排序规则的字符串utf8mb4_0900_ai_ci(服务器默认排序)。

  • JSON_UNQUOTE ()返回带有排序规则的字符串utf8mb4_bin(硬编码)。

结果,在前面的表定义中的索引表达式与在哪里子句表达式:

SELECT * FROM employees WHERE data- b> '$.name' = 'James';

由于查询中的表达式与索引中的表达式不同,因此不使用索引。为了支持这种功能关键部件的场景,优化器会自动剥离把()在寻找要使用的索引时,但是只有如果索引表达式的排序规则与查询表达式的排序规则匹配。对于使用函数键部分的索引,以下两种解决方案中的任何一种都可以工作(尽管它们在效果上有所不同):

  • 解决方案1。将索引表达式赋与相同的排序规则JSON_UNQUOTE ()

    CREATE TABLE employees (data JSON, INDEX idx (CAST(data->>"$.name" AS CHAR(30)) COLLATE utf8mb4_bin));员工插入值(“{“名称”:“詹姆斯”,“工资”:9000  }'), ('{ " 名称”:“詹姆斯”、“工资”:10000  }'), ('{ " 名称”:“玛丽”、“工资”:12000  }'), ('{ " 名称”:“彼得”、“工资”:8000});SELECT * FROM employees WHERE data- b> '$.name' = 'James';

    ->>运算符与JSON_UNQUOTE (JSON_EXTRACT(…)),JSON_UNQUOTE ()返回一个带排序规则的字符串utf8mb4_bin.因此比较是区分大小写的,并且只有一行匹配:

    +------------------------------------+ | 数据  | +------------------------------------+ | {" 名称”:“詹姆斯”、“工资”:10000 } | +------------------------------------+
  • 解决方案2。在查询中指定完整表达式:

    创建表employees (data JSON, INDEX idx) (CAST(data->>"$.name")作为CHAR(30)))));员工插入值(“{“名称”:“詹姆斯”,“工资”:9000  }'), ('{ " 名称”:“詹姆斯”、“工资”:10000  }'), ('{ " 名称”:“玛丽”、“工资”:12000  }'), ('{ " 名称”:“彼得”、“工资”:8000});SELECT * FROM employees WHERE CAST(data->>'$.name') = 'James';

    把()返回一个带排序规则的字符串utf8mb4_0900_ai_ci,因此比较不区分大小写和两行匹配:

    +------------------------------------+ | 数据  | +------------------------------------+ | {" 名称”:“詹姆斯”、“工资”:9000}| |{“名称”:“詹姆斯”,“工资”:10000 } | +------------------------------------+

请注意,尽管优化器支持自动剥离把()对于索引生成的列,下面的方法不起作用,因为它在有索引和没有索引的情况下产生不同的结果(Bug#27337092):

mysql>创建表employees (data JSON, generated_col VARCHAR(30)) AS (CAST(data->>'$.name' AS CHAR(30))));mysql> INSERT INTO employees (data) VALUES ('{"name": "james"}'), ('{"name": "james"}');查询OK, 2行受影响,1警告(0.01秒)记录:2个重复:0警告:1 mysql> SELECT * FROM employees WHERE data->>'$.name' = 'James';+-------------------+---------------+ | 数据| generated_col  | +-------------------+---------------+ | {" 名称”:“詹姆斯”}|詹姆斯  | +-------------------+---------------+ 1行集(0.00秒)mysql > ALTER TABLE员工添加索引idx (generated_col);查询OK, 0行受影响,1个警告(0.03秒)记录:0重复:0警告:1 mysql> SELECT * FROM employees WHERE data->>'$.name' = 'James';+-------------------+---------------+ | 数据| generated_col  | +-------------------+---------------+ | {" 名称”:“詹姆斯”詹姆斯}| | |{“名称”:“詹姆斯”}|詹姆斯  | +-------------------+---------------+ 2行集(0.01秒)

唯一索引

一个独特的Index创建一个约束,使得索引中的所有值必须是不同的。如果尝试添加具有与现有行匹配的键值的新行,则会发生错误。中的列指定前缀值独特的索引时,列值在前缀长度内必须是唯一的。一个独特的索引允许多个列的值,可以包含

如果一个表有主键唯一而非空由具有整数类型的单列组成的索引,您可以使用_rowid中的索引列选择声明如下:

  • _rowid主键主键由单个整数列组成的。如果有主键但它不是由单个整数列组成的,_rowid不能使用。

  • 否则,_rowid引用第一个中的列唯一而非空如果该索引由单个整数列组成。如果是第一个唯一而非空索引不包含单个整数列,_rowid不能使用。

全文索引

全文只支持索引InnoDBMyISAM表和只能包含字符VARCHAR,文本列。索引总是在整个列上进行;不支持列前缀索引,如果指定,则忽略任何前缀长度。看到第12.10节“全文检索功能”,查阅操作详情。

多值的索引

从MySQL 8.0.17开始,InnoDB支持多值索引。多值索引是在存储值数组的列上定义的二级索引。一个正常的索引对每个数据记录有一个索引记录(1:1)。一个多值索引对于一个数据记录(N:1)可以有多个索引记录。多值索引用于索引JSON数组。例如,在下面的JSON文档中,在邮政编码数组上定义的多值索引为每个邮政编码创建一个索引记录,每个索引记录引用相同的数据记录。

{“用户”:“Bob”、“user_id”:31日“zipcode”:(94477、94536)}
创建多值索引

可以在a中创建多值索引创建表ALTER TABLE,或创建索引声明。这需要使用铸造(…为…数组)类型中强制转换相同类型标量值的索引定义JSON数组转换为SQL数据类型数组。然后用SQL数据类型数组中的值透明地生成一个虚拟列;最后,在虚拟列上创建一个函数索引(也称为虚拟索引)。在SQL数据类型数组的值的虚拟列上定义的函数索引构成了多值索引。

下表中的示例显示了使用多值索引的三种不同方式拉链是否可以在阵列上创建.zipcode美元在一个JSONcustinfo在一个名为客户.在每种情况下,JSON数组都被强制转换为的SQL数据类型数组无符号整数值。

  • 创建表只有:

    创建表客户(id BIGINT NOT NULL AUTO_INCREMENT主键,修改DATETIME默认CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, custinfo JSON,索引压缩((CAST(custinfo->'$))。zipcode' AS UNSIGNED ARRAY))));
  • 创建表+ALTER TABLE

    创建表customers (id BIGINT NOT NULL AUTO_INCREMENT主键,修改DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, custinfo JSON);为客户表添加索引zip (CAST(custinfo->'$)。zipcode' AS UNSIGNED ARRAY));

  • 创建表+创建索引

    创建表customers (id BIGINT NOT NULL AUTO_INCREMENT主键,修改DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, custinfo JSON);创建客户的索引zip (CAST(custinfo->'$)。zipcode' AS UNSIGNED ARRAY));

多值指数也可以定义为复合指数的一部分。这个例子展示了一个复合索引,它包含两个单值部分(用于id修改列)和一个多值部分(用于custinfo列):

创建表customers (id BIGINT NOT NULL AUTO_INCREMENT主键,修改DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, custinfo JSON);添加索引comp(id,修改,CAST(custinfo->'$)。zipcode' AS UNSIGNED ARRAY));

一个复合索引中只能使用一个多值关键部分。多值键部分可以以相对于键的其他部分的任何顺序使用。换句话说,ALTER TABLE刚才的表述可以用comp (id (CAST (custinfo - >的美元。zipcode'作为无符号数组),修改))(或任何其他订购),并且仍然有效。

使用多值索引

类中指定以下函数时,优化器使用多值索引来获取记录在哪里条款:

我们可以通过创建和填充客户使用以下内容创建表创建表插入声明:

->修改DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, -> custinfo JSON ->);查询好,0行影响(0.51秒)mysql >插入客户价值观——> (NULL,现在(){“用户”:“杰克”、“user_id”:37岁“zipcode”:94582、94536 ]}'), -> ( 空,现在(){“用户”:“吉尔”、“user_id”:22日" zipcode ":[94568、94507、94582 ]}'), -> ( 空,现在(){“用户”:“Bob”、“user_id”:31日" zipcode ":[94477、94507 ]}'), -> ( 空,现在(){“用户”:“玛丽”,“user_id”:72年,“zipcode”:[94536 ]}'), -> ( 空,现在(){“用户”:“泰德”、“user_id”:56岁“zipcode”:(94507、94582)}”);查询OK, 5行受影响(0.07秒)记录:5条重复:0条警告:0条

对象上执行三个查询客户表,每个使用一个成员()JSON_CONTAINS (),JSON_OVERLAPS (),查询结果如下所示:

mysql SELECT * FROM customers -> WHERE 94507 MEMBER OF(custinfo->'$.zipcode');+----+---------------------+-------------------------------------------------------------------+ | id | | custinfo修改  | +----+---------------------+-------------------------------------------------------------------+ | 2 | 2019-06-29 22:23:12 |{“用户”:“吉尔”、“user_id”:22日" zipcode ":[94568、94507、94582]}| | 3 | 2019-06-29 22:23:12 |{“用户”:“Bob”、“user_id”:31日“zipcode”:(94477、94507)}| | 5 | 2019-06-29 22:23:12 |{“用户”:“泰德”、“user_id”:56岁“zipcode”:(94507年,94582年 ]} | +----+---------------------+-------------------------------------------------------------------+ 3行集(0.00秒)mysql > SELECT *从客户- > JSON_CONTAINS (custinfo - >的美元。zipcode', CAST('[94507,94582]' AS JSON));+----+---------------------+-------------------------------------------------------------------+ | id | | custinfo修改  | +----+---------------------+-------------------------------------------------------------------+ | 2 | 2019-06-29 22:23:12 |{“用户”:“吉尔”、“user_id”:22日" zipcode ":[94568、94507、94582]}| | 5 | 2019-06-29 22:23:12 |{“用户”:“泰德”、“user_id”:56岁“zipcode”:(94507年,94582年 ]} | +----+---------------------+-------------------------------------------------------------------+ 2行集(0.00秒)mysql > SELECT *从客户- > JSON_OVERLAPS (custinfo - >的美元。zipcode', CAST('[94507,94582]' AS JSON));+----+---------------------+-------------------------------------------------------------------+ | id | | custinfo修改  | +----+---------------------+-------------------------------------------------------------------+ | 1 | 2019-06-29 22:23:12 |{“用户”:“杰克”、“user_id”:37岁“zipcode”:(94582、94536)}| | 2 | 2019-06-29 22:23:12 |{“用户”:“吉尔”、“user_id”:22日" zipcode ":[94568、94507、94582]}| | 3 | 2019-06-29 22:23:12 |{“用户”:“Bob”、“user_id”:31日“zipcode”:(94477、94507)}| | 5 | 2019-06-29 22:23:12 |{“用户”:“泰德”、“user_id”:56岁“zipcode”:94507、94582 ]} | +----+---------------------+-------------------------------------------------------------------+ 4行集(0.00秒)

接下来,我们运行解释对于前三个查询中的每一个:

mysql数据库数据库数据库数据库解释SELECT * FROM customers -> WHERE 94507 MEMBER OF(custinfo->'$.zipcode');+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 分区表id | select_type | | | |型possible_keys关键| | key_len | ref | | |过滤多余的行  | +----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 |简单零| | |客户所有零零零零| | | | | | 5 | 100.00 |使用+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+ 1行集,警告(0.00秒)mysql >解释从客户- >选择* JSON_CONTAINS (custinfo - >的美元。zipcode', CAST('[94507,94582]' AS JSON));+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 分区表id | select_type | | | |型possible_keys关键| | key_len | ref | | |过滤多余的行  | +----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 |简单零| | |客户所有零零零零| | | | | | 5 | 100.00 |使用+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+ 1行集,警告(0.00秒)mysql >解释从客户- >选择* JSON_OVERLAPS (custinfo - >的美元。zipcode', CAST('[94507,94582]' AS JSON));+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 分区表id | select_type | | | |型possible_keys关键| | key_len | ref | | |过滤多余的行  | +----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 |简单零| | |客户所有零零零零| | | | | | 5 | 100.00 |使用+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+ 1行集,警告(0.01秒)

刚才显示的三个查询都不能使用任何键。为了解决这个问题,我们可以添加一个多值索引zipcode数组中的JSON列(custinfo),像这样:

添加索引zip (CAST(custinfo->) '$。zipcode' AS UNSIGNED ARRAY));查询OK, 0行受影响(0.47秒)记录:0重复:0警告:0

当我们运行前面的解释语句,我们现在可以观察到查询可以(并且确实)使用索引拉链这是刚刚创建的:

mysql数据库数据库数据库数据库解释SELECT * FROM customers -> WHERE 94507 MEMBER OF(custinfo->'$.zipcode');+----+-------------+-----------+------------+------+---------------+------+---------+-------+------+----------+-------------+ | 分区表id | select_type | | | |型possible_keys关键| | key_len | ref | | |过滤多余的行  | +----+-------------+-----------+------------+------+---------------+------+---------+-------+------+----------+-------------+ | 1 |简单零| | |客户ref |拉链|拉链| 9 | const | 1 | 100.00 | |使用+----+-------------+-----------+------------+------+---------------+------+---------+-------+------+----------+-------------+ 1行集,警告(0.00秒)mysql >解释从客户- >选择* JSON_CONTAINS (custinfo - >的美元。zipcode', CAST('[94507,94582]' AS JSON));+----+-------------+-----------+------------+-------+---------------+------+---------+------+------+----------+-------------+ | 分区表id | select_type | | | |型possible_keys关键| | key_len | ref | | |过滤多余的行  | +----+-------------+-----------+------------+-------+---------------+------+---------+------+------+----------+-------------+ | 1 |简单|客户零范围| | |拉链|拉链| 9零| | 6 | 100.00 | |使用+----+-------------+-----------+------------+-------+---------------+------+---------+------+------+----------+-------------+ 1行集,警告(0.00秒)mysql >解释从客户- >选择* JSON_OVERLAPS (custinfo - >的美元。zipcode', CAST('[94507,94582]' AS JSON));+----+-------------+-----------+------------+-------+---------------+------+---------+------+------+----------+-------------+ | 分区表id | select_type | | | |型possible_keys关键| | key_len | ref | | |过滤多余的行  | +----+-------------+-----------+------------+-------+---------------+------+---------+------+------+----------+-------------+ | 1 |简单|客户零范围| | |拉链|拉链| 9零| | 6 | 100.00 | |使用+----+-------------+-----------+------------+-------+---------------+------+---------+------+------+----------+-------------+ 1行集,警告(0.01秒)

多值索引可以定义为唯一键。如果定义为唯一键,则尝试插入已经存在于多值索引中的值将返回重复键错误。如果重复的值已经存在,尝试添加一个唯一的多值索引失败,如下所示:

删除客户表的索引查询OK, 0行受影响(0.55秒)记录:0重复:0警告:0 mysql> ALTER TABLE customers -> ADD UNIQUE INDEX zip ((CAST(custinfo->'))zipcode' AS UNSIGNED ARRAY));错误1062(23000):关键客户的重复条目'[94507,'。拉链'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操作的处理方式相同,唯一的区别是对单个聚集索引记录可能有不止一次的插入或更新。

  • 可空性和多值索引:

    • 如果多值键部分为空数组,则不会向索引中添加任何条目,并且索引扫描无法访问该数据记录。

    • 如果多值关键部分生成返回a值,单个条目包含添加到多值索引中。如果关键部分定义为非空,则报错。

    • 如果类型化数组列设置为,存储引擎存储单个记录,包含这就指向了数据记录。

    • JSON索引数组中不允许出现空值。如果返回值是,它被视为JSON null和无效的JSON值报错。

  • 因为多值索引是虚拟列上的虚拟索引,所以它们必须遵循与虚拟生成列上的二级索引相同的规则。

  • 不为空数组添加索引记录。

多值索引的局限性和限制

多值指标受到以下所列的限制和限制:

  • 每个多值索引只允许有一个多值键部分。然而,铸造(…为…数组)表达式中可以引用多个数组JSON文件,如下所示:

    (数据- >的美元。arr[*][*]' AS UNSIGNED ARRAY

    在这种情况下,所有匹配JSON表达式的值都作为单个平面数组存储在索引中。

  • 具有多值键部分的索引不支持排序,因此不能用作主键。方法定义多值索引也是出于同样的原因ASCDESC关键字。

  • 多值索引不能是覆盖索引。

  • 多值索引的每条记录的最大值数取决于可以存储在单个撤销日志页上的数据量,即65221字节(64K减去315字节的开销),这意味着键值的最大总长度也是65221字节。键的最大数量取决于各种因素,因此无法定义特定的限制。例如,测试表明,一个多值索引允许每条记录多达1604个整数键。当达到限制时,报告类似以下的错误:错误3905 (HY000):多值索引“idx”的每条记录的最大值数超出1个值。

  • 在多值键部分中允许的唯一表达式类型是aJSON表达式。表达式不需要引用插入索引列的JSON文档中的现有元素,但表达式本身必须在语法上有效。

  • 由于同一聚集索引记录的索引记录分散在多值索引中,因此多值索引不支持范围扫描或仅支持索引扫描。

  • 外键规范中不允许使用多值索引。

  • 不能为多值索引定义索引前缀。

  • 不能在数据类型转换上定义多值索引二进制(参见把()功能)。

  • 不支持在线创建多值索引,这意味着该操作使用算法=复制.看到性能和空间要求

  • 多值索引不支持以下两种字符集和排序法组合以外的字符集和排序法:

    1. 二进制使用默认字符集二进制排序

    2. utf8mb4使用默认字符集utf8mb4_0900_as_cs排序。

  • 的列上的其他索引也是如此InnoDB不能使用表创建多值索引使用哈希;尝试这样做会导致一个警告:此存储引擎不支持HASH索引算法,使用存储引擎默认值代替.(使用BTREE与往常一样支持。)

空间索引

MyISAMInnoDBNDB,存档存储引擎支持空间列,例如几何.(第11.4节“空间数据类型”,描述了空间数据类型。)但是,对空间列索引的支持因引擎而异。根据以下规则,可以使用空间列上的空间和非空间索引。

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

  • 只适用于InnoDBMyISAM表。指定空间索引对于其他存储引擎将导致错误。

  • 从MySQL 8.0.12开始,空间列上的索引必须是一个空间索引。的空间关键字因此是可选的,但对于在空间列上创建索引是隐式的。

  • 仅可用于单个空间列。不能在多个空间列上创建空间索引。

  • 索引列必须为非空

  • 禁止使用列前缀长度。索引每个列的全宽度。

  • 不允许用于主键或唯一索引。

空间列上的非空间索引(使用指数独特的,或主键)有以下特点:

  • 允许用于任何支持空间列的存储引擎,但存档

  • 列可以是除非索引是主键。

  • 非的索引类型空间索引依赖于存储引擎。目前使用的是B-tree。

  • 允许的列可以有值仅用于InnoDBMyISAM,内存表。

指数期权

根据关键部分列表,可以给出索引选项。一个index_optionValue可以是以下任意值:

  • KEY_BLOCK_SIZE (=)价值

    MyISAM表,KEY_BLOCK_SIZE可选地指定索引键块的字节大小。该值被视为提示;如果有必要,可以使用不同的尺寸。一个KEY_BLOCK_SIZE为单个索引定义指定的值将覆盖表级KEY_BLOCK_SIZE价值。

    KEY_BLOCK_SIZE的索引级别不支持InnoDB表。看到第13.1.20节," CREATE TABLE语句"

  • index_type

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

    创建表查找(id INT)使用b树创建索引id_index

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

    表13.1各存储引擎的索引类型

    存储引擎 允许的索引类型
    InnoDB BTREE
    MyISAM BTREE
    内存/ 哈希BTREE
    NDB 哈希BTREE(见正文注释)

    index_type从句不能用于全文索引或(MySQL 8.0.12之前)空间索引规范。全文索引实现依赖于存储引擎。空间索引被实现为r树索引。

    如果指定的索引类型对给定的存储引擎无效,但引擎可以使用另一种索引类型而不会影响查询结果,则引擎使用可用的索引类型。解析器识别RTREE作为类型名。从MySQL 8.0.12开始,这只允许空间索引。在8.0.12之前,RTREE不能为任何存储引擎指定。

    BTREE索引由NDB存储引擎作为t树索引。

    请注意

    上的索引NDB表列、使用选项只能为唯一索引或主键指定。使用哈希防止创建有序索引;对象上创建唯一索引或主键NDB表自动导致创建有序索引和散列索引,每个索引都索引同一组列。

    用于包含一个或多个的唯一索引的列NDB表中,哈希索引只能用于查找文字值,这意味着[not]为空吗条件要求对表进行完整扫描。一种解决方法是确保使用一个或多个惟一索引这样的表上的列总是以这样一种方式创建,它包含有序索引;也就是说,避免雇佣使用哈希创建索引时。

    如果指定的索引类型对给定的存储引擎无效,但引擎可以使用另一种索引类型而不会影响查询结果,则引擎使用可用的索引类型。解析器识别RTREE作为类型名称,但目前不能为任何存储引擎指定。

    请注意

    的使用index_type选项前的tbl_name子句已弃用;期望在未来的MySQL版本中删除对在此位置使用该选项的支持。如果一个index_type在前面和后面的位置都给出了选项,最后的选项适用。

    类型type_name被认为是?的同义词使用type_name.然而,使用是首选形式。

    的存储引擎的索引特征index_type选择。

    表13.2 InnoDB存储引擎索引特征

    指数类 索引类型 存储空值 允许多个NULL值 IS NULL扫描类型 不是空扫描类型
    主键 BTREE 没有 没有 N/A N/A
    独特的 BTREE 是的 是的 指数 指数
    关键 BTREE 是的 是的 指数 指数
    全文 N/A 是的 是的 表格 表格
    空间 N/A 没有 没有 N/A N/A

    表13.3 MyISAM存储引擎索引特征

    指数类 索引类型 存储空值 允许多个NULL值 IS NULL扫描类型 不是空扫描类型
    主键 BTREE 没有 没有 N/A N/A
    独特的 BTREE 是的 是的 指数 指数
    关键 BTREE 是的 是的 指数 指数
    全文 N/A 是的 是的 表格 表格
    空间 N/A 没有 没有 N/A N/A

    表13.4内存存储引擎索引特征

    指数类 索引类型 存储空值 允许多个NULL值 IS NULL扫描类型 不是空扫描类型
    主键 BTREE 没有 没有 N/A N/A
    独特的 BTREE 是的 是的 指数 指数
    关键 BTREE 是的 是的 指数 指数
    主键 哈希 没有 没有 N/A N/A
    独特的 哈希 是的 是的 指数 指数
    关键 哈希 是的 是的 指数 指数

    表13.5 NDB存储引擎索引特征

    指数类 索引类型 存储空值 允许多个NULL值 IS NULL扫描类型 不是空扫描类型
    主键 BTREE 没有 没有 指数 指数
    独特的 BTREE 是的 是的 指数 指数
    关键 BTREE 是的 是的 指数 指数
    主键 哈希 没有 没有 表(见注1) 表(见注1)
    独特的 哈希 是的 是的 表(见注1) 表(见注1)
    关键 哈希 是的 是的 表(见注1) 表(见注1)

    表说明:

    1.使用哈希防止创建隐式有序索引。

  • 与解析器parser_name

    此选项只能与全文索引。如果全文索引和搜索操作需要特殊处理,它将解析器插件与索引关联起来。InnoDBMyISAM支持全文解析器插件。如果你有MyISAM表与关联的全文解析器插件,您可以将表转换为InnoDB使用ALTER TABLE.看到全文解析插件编写全文解析器插件了解更多信息。

  • 评论的字符串

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

    MERGE_THRESHOLD为索引页配置单独的索引index_option评论条款创建索引声明。例如:

    创建表(id INT)CREATE INDEX ON t1 (id) COMMENT 'MERGE_THRESHOLD=40';

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

    MERGE_THRESHOLD也可以在索引级和表级使用创建表ALTER TABLE语句。有关更多信息,请参见第15.8.11节,“配置索引页的合并阈值”

  • 可见看不见的

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

  • ENGINE_ATTRIBUTESECONDARY_ENGINE_ATTRIBUTE选项(从MySQL 8.0.21开始可用)用于指定主存储引擎和辅助存储引擎的索引属性。这些选项被保留以供将来使用。

    允许的值是包含有效的JSON文档或空字符串(")。无效的JSON将被拒绝。

    创建索引i1在t1 (c1)引擎属性='{"关键”:“价值“}”;

    ENGINE_ATTRIBUTESECONDARY_ENGINE_ATTRIBUTE值可以重复而不会出错。在本例中,使用最后指定的值。

    ENGINE_ATTRIBUTESECONDARY_ENGINE_ATTRIBUTE服务器不会检查值,当表的存储引擎发生更改时,也不会清除这些值。

表复制和锁定选项

算法当表的索引被修改时,可以使用子句来影响表的复制方法和读写并发级别。它们的意思和ALTER TABLE声明。有关更多信息,请参见第13.1.9节“ALTER TABLE语句”

NDB Cluster支持在线操作算法=原地使用标准MySQL服务器的语法。看到第23.5.11节“在NDB集群中对ALTER TABLE进行在线操作”,以获取更多资讯。