MySQL的SQL语句 - 数据定义语句（9）- CREATE INDEX 语句（1）

林员外聊编程 2020-08-01

239

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}
  | ENGINE_ATTRIBUTE [=] 'string'
  | SECONDARY_ENGINE_ATTRIBUTE [=] 'string'
}


index_type:
    USING {BTREE | HASH}


algorithm_option:
    ALGORITHM [=] {DEFAULT | INPLACE | COPY}


lock_option:
    LOCK [=] {DEFAULT | NONE | SHARED | EXCLUSIVE}

一般来说，在使用CREATE TABLE创建表时，可以同时创建所有索引。这个原则对于InnoDB表尤其重要，因为主键决定数据文件中行的物理布局。CREATE INDEX能够向现有表添加索引。

CREATE INDEX也可用到ALTER TABLE语句来创建索引。CREATE INDEX不能用于创建主键；请改用ALTER TABLE。

InnoDB支持虚拟列上的二级索引。

当innodb_stats_persistent设置启用时，在为InnoDB表创建索引之后，运行ANALYZE TABLE语句。

从MySQL 8.0.17开始，key_part声明中的expr可以采用以下形式（CAST json_expression AS type ARRAY）在JSON列上创建多值索引。

形如(key_part1, key_part2, …)的索引声明创建一个包含多个成分的索引。索引键值是通过连接给定键部分的值而形成的。例如（col1, col2, col3）指定一个多列索引，索引由col1、col2和col3中的值组成。

key_part 声明可以以ASC或DESC结尾，以指定索引值是按升序还是降序存储。如果没有指定顺序说明符，则默认值为升序。哈希索引不允许使用ASC和DESC。多值索引也不支持ASC和DESC。从MySQL 8.0.12开始，SPATIAL索引不允许使用ASC和DESC。

以下各节介绍CREATE INDEX语句的各方面内容：

列前缀索引

对于字符串列，可以创建只使用列值前导部分的索引，使用col_name(length)语法指定索引前缀长度：

● 可以为CHAR、VARCHAR、BINARY和VARBINARY索引指定前缀。

● 必须为BLOB和TEXT索引指定前缀。此外，只能为InnoDB、MyISAM和BLACKHOLE表的BLOB和TEXT列编制索引。

● 前缀限制以字节为单位。但是，CREATE TABLE、ALTER TABLE和CREATE INDEX语句中索引声明中的前缀长度解释为非二进制字符串类型（CHAR、VARCHAR、TEXT）的字符数和二进制字符串类型（BINARY、VARBINARY、BLOB）的字节数。在为使用多字节字符集的非二进制字符串列指定前缀长度时，请注意这一点。

是否支持前缀和前缀长度（如果支持）取决于存储引擎。例如，对于使用REDUNDANT或COMPACT行格式的InnoDB表，前缀最长可达767字节。对于使用DYNAMIC或COMPRESSED行格式的InnoDB表，前缀长度限制为3072字节。对于MyISAM表，前缀长度限制为1000字节。NDB存储引擎不支持前缀。

如果指定的索引前缀超过最大列数据类型大小，CREATE INDEX将按如下方式处理索引：

● 对于非唯一索引，要么发生错误（如果启用了严格SQL模式），要么索引长度减小到列数据类型大小的最大值之内，并生成警告（如果未启用严格SQL模式）。

● 对于唯一索引，无论SQL模式如何，都会发生错误，因为减少索引长度可能会导致插入不满足指定唯一性要求的非唯一项。

此处显示的语句使用name列的前10个字符创建索引（假设name具有非二进制字符串类型）：

CREATE INDEX part_of_name ON customer (name(10));

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

函数索引

“普通”的索引索引列值或列值的前缀。例如，在下表中，给定t1行的索引项包括完整的col1值和由其前10个字符组成的col2值前缀：

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

多列索引可以同时包含非函数列和函数列。

函数索引支持ASC和DESC。

函数索引必须遵循以下规则。如果索引键中包含了不允许的结构，则会产生错误。

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

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

下面语句产生一个错误；表达式没有括在括号中：

INDEX (col1 + col2, col3 - col4)

● 函数索引不能仅由列名组成。例如，这是不允许的：

INDEX ((col1), (col2))

但是，可以写成非函数索引形式，不带括号：

INDEX (col1, col2)

● 函数索引表达式不能引用列前缀。有关解决方法，可以参考后文的SUBSTRING()和CAST()的讨论。

● 外键不支持使用函数索引。

对于CREATE TABLE ... LIKE语句，目标表保留了原始表中的函数索引。

函数索引被实现为隐藏的虚拟生成列，隐含有以下限制：

● 每个函数索引都会当成一列，会受限于表的总数列限制。

● 函数索引继承应用于生成列的所有限制。例如：

■ 只有能用于生成列的函数才能用于函数索引。

■ 不允许使用子查询、参数、变量、存储函数和用户定义函数。

虚拟生成列本身不需要存储。索引本身与任何其他索引一样占用存储空间。

函数索引支持UNIQUE选项。但是，主键不能包含函数列。主键要求存储生成的列，但是函数索引由虚拟生成列实现，不是存储的生成列。

空间索引和全文索引不支持函数索引。

如果一个表不包含主键，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() 返回排序规则为utf8mb4_0900_ai_ci的字符串，因此比较不区分大小写，有两行记录匹配：

+------------------------------------+
| data                               |
+------------------------------------+
| {"name": "james", "salary": 9000}  |
| {"name": "James", "salary": 10000} |
+------------------------------------+

请注意，尽管优化器支持使用索引生成列自动剥离CAST()，但以下方法不起作用，因为带有索引和不带索引它会生成不同结果：

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)

官方网址：

https://dev.mysql.com/doc/refman/8.0/en/create-index.html

mysql

文章转载自林员外聊编程，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

MySQL的SQL语句 - 数据定义语句（9）- CREATE INDEX 语句 （1）

评论

MySQL的SQL语句 - 数据定义语句（9）- CREATE INDEX 语句（1）