Mysql 5.7 创建索引官方解读

一、环境

Mysql 5.7

二、Mysql索引创建解读

1.概述

通常我们在使用CREATE TABLE时会创建所有的索引。索引的创建对于 InnoDB 表尤其重要，其中主键决定了数据文件中行的物理布局。

CREATE INDEX是另一种添加索引的方式，针对已经创建的表添加索引。注意，CREATE INDEX这种方式是被映射到 ALTER TABLE来创建索引。而且对于PRIMARY KEY不能使用CREATE INDEX，需要使用 ALTER TABLE。

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

当启用 innodb_stats_persistent设置后，每次在 InnoDB 表上创建索引后会运行 ANALYZE TABLE 语句。

CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name
    [index_type]
    ON tbl_name (key_part,...)
    [index_option]
    [algorithm_option | lock_option] ...

2.key part

key_part:
    col_name [(length)] [ASC | DESC]

(key_part1, key_part2, ...) 形式的索引规范创建具有多个key part的索引。索引键值是通过连接给定key part的值形成的。key part可以[ASC | DESC]结尾，可能在未来扩展用于指定索引值存储的升序或降序。但目前，在代码层面[ASC | DESC]参与解析，而索引值始终按升序ASC存储。

对于String列，可以创建仅使用列的值（关键的一部分值）的索引，使用 col_name(length) 语法来指定索引前缀长度。

key part需要注意一下几点：

1）可以对 CHAR、VARCHAR、BINARY 和 VARBINARY 的key part指定前缀。

2）必须对 BLOB 和 TEXT 的key part指定前缀。此外，BLOB 和 TEXT 列只能对 InnoDB、MyISAM 和 BLACKHOLE 表建立索引。

3）前缀的长度限制以byte来衡量。但是，CREATE TABLE、ALTER TABLE和CREATE INDEX句中索引规范的前缀长度被解释为：

非二进制字符串类型(CHAR、VARCHAR、TEXT)的char的个数；

和二进制字符串类型(BINARY、VARBINARY、BLOB)的byte的个数。

因此，在对一个multibyte的char set（MBCS）的非二进制字符串的列指定前缀长度时，要考虑到用byte衡量。

前缀支持和前缀长度（如果支持）取决于存储引擎。例如，对于InnoDB表，前缀最长可达 767 字节，如果启用了innodb_large_prefix选项，则最长可达 3072 字节。对于MyISAM表，前缀长度限制为 1000 字节。NDB存储引擎不支持前缀。

3.Mysql创建索引的SQL

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)] [ASC | DESC]

index_option: {
    KEY_BLOCK_SIZE [=] value
  | index_type
  | WITH PARSER parser_name
  | COMMENT 'string'
}

index_type:
    USING {BTREE | HASH}

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

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

4.InnoDB存储引擎支持的索引类型

InnoDB存储引擎支持的索引类型仅为BTREE。

5.InnoDB存储引擎的索引的特性

InnoDB存储引擎支持主键索引、唯一索引、普通索引使用BTREE的类型。但注意，对于主键索引而言，如果索引命中NULL值时，InnoDB存储引擎是不对NULL进行存储的，更不允许多个NULL值的。InnoDB存储引擎是不具备NULL和NOT NULL的索引检索。

而对于唯一索引、普通索引，如果索引命中NULL值是可行的，而且允许出现多个NULL值的情况。InnoDB存储引擎对这两种索引，是支持用索引进行NULL和NOT NULL的检索。