MySQL索引之创建索引

参考文档：https://dev.mysql.com/doc/refman/5.7/en/create-index.html

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

note：[]里面的内容是可选的，（）里面的内容是必选的

逐个解析：
1、[UNIQUE|FULLTEXT|SPATIAL]：索引类型

Unique  唯一索引
Normal 普通索引
Full Text 全文索引

2、[index_type]：索引的方法

index_type:
    USING {BTREE | HASH}
        B-Tree   Hash

3、(index_col_name,…)：欲建立索引的字段

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

对于String类型的数据类型，可以使用length来定义欲建立的索引是表示在该字符串的前几位：
    char、varchar、binary、varbinary是可以选择使用length的；
    blob、text是一定要使用length的。
    length的长度是以字节来算的
        其中，非二进制string类型的char、varchar、text是字符类型来算的，
        而二进制string类型的binary、varbinary、blob是字节类型的

    For string columns, indexes can be created that use only the leading part of column values, 
using col_name(length) syntax to specify an index prefix length:
    Prefixes can be specified for CHAR, VARCHAR, BINARY, and VARBINARY column indexes.
    Prefixes must be specified for BLOB and TEXT column indexes. 
    Prefix limits are measured in bytes, whereas the prefix length in CREATE TABLE, ALTER TABLE, 
and CREATE INDEX statements is interpreted as number of characters for nonbinary string types 
(CHAR, VARCHAR, TEXT) and number of bytes for binary string types (BINARY, VARBINARY, BLOB). 

4、[index_option]：

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

1.KEY_BLOCK_SIZE:
    注意：对于MyISAM表，KEY_BLOCK_SIZE 可选地指定用于索引键块的字节大小；InnoDB表的索引级别不支持键块大小。

2.index_type：可以定义创建表时使用的存储引擎
    InnoDB          BTREE
    MyISAM          BTREE
    MEMORY/HEAP     HASH, BTREE
    NDB             HASH, BTREE

    而且index_type不能用于全文索引和空间索引；其中全文索引依赖于存储引擎，空间索引使用的是R-Tree

3.WITH PARSER parser_name:
    只能用于全文索引里面，如果全文索引和搜索操作需要特殊处理，它可以将解析器插件与索引关联起来

4.COMMENT 'string'
    可以在创建索引的时候，为索引定义一个最多1024字符的评论
    eg：CREATE INDEX id_index ON t1 (id) COMMENT 'MERGE_THRESHOLD=40';

5、algorithm_option：

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

ALGORITHM = INPLACE 可以避免冗余；
ALGORITHM = copy 在插入操作可以防止并发DML,但是仍然允许读，除非进一步限制。

6、lock_option：控制在并发写入和读取时的级别

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

1.LOCK = DEFAULT
如果支持的话，允许并发读和写。如果不支持，则允许并发读取。如果没有，则强制执行独占访问。

2.LOCK = NONE
如果支持，允许并发读和写。否则,发生错误。

3.LOCK = SHARED 共享锁
如果支持，允许并发读，但是不支持并发写。即使存储引擎支持并发写，也会被阻塞。如果不支持并发读取，则会发生错误。

4.LOCK = EXCLUSIVE 排它锁
执行独占访问。即使存储引擎支持并发读/写，也保持独占访问。