索引 - 创建索引 - 《MySQL》

CREATE INDEX语句详解
删除索引

所有MySQL类型的都可以被索引，对相关列使用索引是提高select操作性能的最佳途径。
以下为创建索引的格式：

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]     #Colunm Prefix Key Parts
index_option:                        #Index Options
    KEY_BLOCK_SIZE [=] value
  | index_type
  | WITH PARSER parser_name
  | COMMENT 'string'
index_type:
    USING {BTREE | HASH}
algorithm_option:      #Table copying and locking option
    ALGORITHM [=] {DEFAULT | INPLACE | COPY}
lock_option:
    LOCK [=] {DEFAULT | NONE | SHARED | EXCLUSIVE}

一个索引按照格式（key_part1, key_part2,…）创建是一索引有多个key parts。那么索引值为这些列的拼接形式。比如（col1, col2,col3）指定了一个多列索引（multiple-column index），那么索引值为col1,col2和col3组成。
一个key_part的指定可以以ASC和DESC结尾。这些关键字允许未来对指定升序和降序索引值存储进行扩展。现在，只是解析但是被忽略，索引值总是按升序存储。

CREATE INDEX语句详解

主要包含以下部分：

Colunm Prefix Key Parts
Unique Indexes
Full-Text Indexes
Spatial Indexes
Index Options
Table Copying and Locking Options

前缀索引部分 Colunm Prefix Key Parts

对于字符串列，可以创建仅使用列值的开头部分的索引，使用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表的前缀长度最多可以达到767字节，如果启用了innodb_large_prefix选项，则可以达到3072字节。对于MyISAM表，前缀长度限制为1000字节。NDB存储引擎不支持前缀。

从MySQL 5.7.17开始，如果指定的索引前缀超过了最大的列数据类型大小，CREATE INDEX将按如下方式处理索引:

对于非惟一索引，要么出现错误(如果启用了strict的SQL mode)，要么将索引长度减少到列数据类型的最大大小以内，并产生一个警告(如果没有启用strict的SQL mode)。
对于惟一索引，无论SQL模式如何，都会发生错误，因为减少索引长度可能会允许插入不满足指定惟一性要求的非惟一条目。

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

create index part_of_name on customer(name(10));

如果列中的名称通常前10个字符不同，那么使用这个索引执行的查找应该不会比使用从整个name列创建的索引慢很多。此外，为索引使用列前缀可以使索引文件更小，这可以节省大量磁盘空间，并可能加快插入操作。

唯一索引 Unique Indexes

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

如果表有一个主键或惟一的NOT NULL索引，该索引由一个具有整数类型的列组成，则可以使用_rowid在SELECT语句中引用索引列，如下所示:

_rowid指的是主键列，如果主键是由单个整数列组成的。如果存在主键，但它不包含单个整数列，则不能使用_rowid。
否则，_rowid将引用第一个惟一NOT NULL索引中的列(如果该索引包含单个整数列)。如果第一个惟一的NOT NULL索引不包含单个整数列，则不能使用_rowid。

全文索引 FullText Indexes
全文索引只支持InnoDB和MyISAM表，并且只能包含CHAR、VARCHAR和TEXT列。索引总是在整个列上进行;不支持列前缀索引，如果指定，则忽略任何前缀长度。

空间索引 Spatial Indexes
MyISAM、InnoDB、NDB和ARCHIVE存储引擎都支持空间列，比如POINT和GEOMETRY。(“空间数据类型”，描述了空间数据类型。)但是，不同的引擎对空间列索引的支持是不同的。根据以下规则，可以使用空间列上的空间和非空间索引。
空间列上的空间索引(使用空间索引创建)具有以下特征:
仅适用于MyISAM和InnoDB表。为其他存储引擎指定空间索引将导致错误。
索引列必须不为空。
禁止列前缀长度。每个列的全宽度被索引。

空间列上的非空间索引(使用INDEX、UNIQUE或PRIMARY KEY创建)具有以下特征:

除了ARCHIVE之外，任何支持空间列的存储引擎都可以使用。
除非索引是主键，否则列可以为空。
对于非空间索引(点列除外)中的每个空间列，必须指定列前缀长度。(这与对索引的BLOB列的要求相同。)前缀长度以字节为单位。
非空间索引的索引类型取决于存储引擎。目前使用B-tree。
允许只有InnoDB、MyISAM和内存表可以有空值的列。

索引选项 Index Options

KEY_BLOCK_SIZE [=] value
- 对于MyISAM表，KEY_BLOCK_SIZE可选地指定索引键块的大小(以字节为单位)。该值被视为提示；如果需要，可以使用不同的大小。为单个索引定义指定的KEY_BLOCK_SIZE值覆盖表级KEY_BLOCK_SIZE值。
- InnoDB表的索引级别不支持KEY_BLOCK_SIZE。
Index_type
一些存储引擎允许您在创建索引时指定索引类型。例如:
```
CREATE TABLE lookup (id INT) ENGINE = MEMORY;
CREATE INDEX id_index ON lookup (id) USING BTREE;
```
下表显示了不同存储引擎支持的允许索引类型值。当列出多个索引类型时，第一个是默认的。表中未列出的存储引擎不支持索引定义中的index_type子句。

Storage Engine	Permissible Index Types
`InnoDB`	`BTREE`
`MyISAM`	`BTREE`
`MEMORY`/`HEAP`	`HASH`, `BTREE`
`NDB`	`HASH`, `BTREE` (see note in text)

index_type子句不能用于FULLTEXT INDEX或SPATIAL INDEX指定。FULLTEXT INDEX实现依赖于存储引擎。SPATIAL INDEX被实现为R-tree索引。
BTREE索引由NDB存储引擎实现为T-tree索引。
如果指定的索引类型对给定的存储引擎无效，但是引擎可以使用另一种索引类型，而不影响查询结果，则引擎使用可用的类型。解析器将RTREE识别为类型名称，但目前无法为任何存储引擎指定此名称。
下表显示了支持index_type选项的存储引擎的索引特征。
InnoDB Storage Engine Index Characteristics

Index Class	Index Type	Stores NULL VALUES	Permits Multiple NULL Values	IS NULL Scan Type	IS NOT NULL Scan Type
Primary key	`BTREE`	No	No	N/A	N/A
Unique	`BTREE`	Yes	Yes	Index	Index
Key	`BTREE`	Yes	Yes	Index	Index
`FULLTEXT`	N/A	Yes	Yes	Table	Table
`SPATIAL`	N/A	No	No	N/A	N/A

MyISAM Storage Engine Index Characteristics

Index Class	Index Type	Stores NULL VALUES	Permits Multiple NULL Values	IS NULL Scan Type	IS NOT NULL Scan Type
Primary key	`BTREE`	No	No	N/A	N/A
Unique	`BTREE`	Yes	Yes	Index	Index
Key	`BTREE`	Yes	Yes	Index	Index
`FULLTEXT`	N/A	Yes	Yes	Table	Table
`SPATIAL`	N/A	No	No	N/A	N/A

MEMORY Storage Engine Index Characteristics

Index Class	Index Type	Stores NULL VALUES	Permits Multiple NULL Values	IS NULL Scan Type	IS NOT NULL Scan Type
Primary key	`BTREE`	No	No	N/A	N/A
Unique	`BTREE`	Yes	Yes	Index	Index
Key	`BTREE`	Yes	Yes	Index	Index
Primary key	`HASH`	No	No	N/A	N/A
Unique	`HASH`	Yes	Yes	Index	Index
Key	`HASH`	Yes	Yes	Index	Index

Table NDB Storage Engine Index Characteristics

Index Class	Index Type	Stores NULL VALUES	Permits Multiple NULL Values	IS NULL Scan Type	IS NOT NULL Scan Type
Primary key	`BTREE`	No	No	Index	Index
Unique	`BTREE`	Yes	Yes	Index	Index
Key	`BTREE`	Yes	Yes	Index	Index
Primary key	`HASH`	No	No	Table (see note 1)	Table (see note 1)
Unique	`HASH`	Yes	Yes	Table (see note 1)	Table (see note 1)
Key	`HASH`	Yes	Yes	Table (see note 1)	Table (see note 1)

如果指定使用HASH，则阻止创建隐式有序索引。

WITH PASER paser_name
此选项只能用于全文索引。如果全文索引和搜索操作需要特殊处理，它将解析器插件与索引相关联。InnoDB和MyISAM支持全文解析插件。
COMMENT ’string’
索引定义可以包含最多1024个字符的可选注释。
表复制和锁选项 Table Copying and Locking Options
在修改表的索引时，可能会给出ALGORITHM和LOCK子句来影响表的复制方法和读写表的并发级别。它们的含义与ALTER TABLE语句相同。
NDB集群以前支持在线创建索引操作，使用的是不再支持的替代语法。NDB集群现在支持使用与标准MySQL服务器相同的ALGORITHM=INPLACE语法进行在线操作。
删除索引
```sql DROP INDEX index_name ON tbl_name [algorithm_option | lock_option] …

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

lockoption: LOCK [=] {DEFAULT|NONE|SHARED|EXCLUSIVE} ``DROP INDEX`将索引名**_index_name从表tbl_name**中删除。这个语句被映射到一个ALTER TABLE语句来删除索引。
删除主键索引需要用反引号，因为PRIMARY是一个保留字。
在修改表的索引时，可能会给出算法和锁子句来影响表的复制方法和读写表的并发级别。它们的含义与ALTER TABLE语句相同。

参考：https://dev.mysql.com/doc/refman/5.7/en/create-index.html#create-index-column-prefixes

创建索引

CREATE INDEX语句详解

前缀索引部分 Colunm Prefix Key Parts

唯一索引 Unique Indexes

全文索引 FullText Indexes

空间索引 Spatial Indexes

索引选项 Index Options

表复制和锁选项 Table Copying and Locking Options

删除索引