mysql索引片段创建（5.7）

萧小木
275 阅读10分钟

前言

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}

通常情况，在你使用create table创建表时候就会创建所有的索引，这条准则对于INNODB表尤其重要，主键决定了数据文件的物理布局， create index 能够为现有表添加索引。

create index被映射为 alter table片段来创建索引，create index 不能用来创建primary key，而应该使用alter table。

INNODB支持虚拟列上的二级索引

当INNODB_state_persistent被设置成开启时，再INNODB表创建索引之后，需要运行analyze_table语句。

形式为（key_part1，key_part2）的索引说明创建了一个多个key的索引，索引的键值由给定兼职部分的值连接而成，例如（col1,col2,col3）指定了一个多列索引，索引键由col1,col2,col3的值组成。

key_part可以以ASC或者DESC结尾,这些关键字允许用于未来的扩展,用以指定以升序或降序存储索引值.目前这些关键字不会被解析,索引总是以升序存储

对于创建索引的片段我们将从以下几个选项描述

一. 列前缀关键部分

对于字符串类型的列，创建可以用使用列的前缀部分，使用col_name（length）语法来说明列的长度

  • 前缀仅仅能够被指定在char，varvahr，binary，varbingary的类型列

  • 必须为blob和text关键部分指定前缀，此外，blob和text作为索引列，只能使用在INNODB,MYISAM,blackhole表

  • 前缀的测量单位是字节，然而对于非二进制字符串（char，varchar，text）类型 在create table,alter table 和create index语句中被解释成字符数量，在二进制字符串（binary，varbinary，blob）中被解释成字节的数量。为多字节字符集的非二进制字符串指定前缀长度的时候要注意这一点。

    前缀支持和前缀长度取决于存储引擎，例如INNODB表的前缀上限威767字节，如果开启了INNODB_large_prefix 则最大前缀长度变为3072。对于MYISAM表，前缀的长度上线为1000字节。对于ndb引擎不支持。

    此处需要注意单位是字节，如果你的INNODB建表时候设置的字符集是utf8mb4，则非二进制字符类型最大可以设置的为3072/4 =768

对于mysql5.7.17，如果你指定的索引前缀超过数据类型的大小，create index处理索引将使用以下方式

  • 对于非唯一索引，如果开启严格模式将会报错，如果开启非严格模式，索引前缀长度将会被减少到列数据裂隙的最大值，并且产生报警。
  • 对于唯一索引，无论sql模式如何，都会报错。因为减少索引长度可能会插入不符合唯一性要求的非唯一性数据

这个片段展示了使用name列的前10个字符创建了一个索引（假设该name列是非二进制字符串类型）

create index part_of_name on customer(name(10))

如果name列的前10个字符大不相同，使用索引查找的速度不会比使用整个name列做索引慢很多，此外为索引使用前缀能够使前缀更小，也可以节省大量的磁盘空间并且提高插入的速度。

二. 唯一索引

唯一索引会创建一个约束，所有的索引值必须是不同的，如果你尝试添加的键值已经存在，则会报出错误。如果你在unique索引中指定了前缀值，则在前缀长度内值必须是唯一的。对于包含多列null唯一索引允许多个null值。

如果一个表的primary key或者unique not null由单列的Integer类型组成，你能够使用_rowid来引用索引列在select片段中

  • 如果primary key（主键列）由单个integer类型组成，rowid指向primary key列，如果有primary key但是不是单个列， rowid不能不被使用
  • 如果第一个unique not null 索引包含一个整数列，_rowid能够指向该列，如果不是一个整数类型不能使用

三.全文索引

全文索引仅仅被支持使用在INNODB和MYISAM中的char，varchar，text列中。索引总是覆盖总列，列前缀不被支持如果指定将被忽略，更多全文索引的信息可以看这 Section 12.9, “Full-Text Search Functions”,

四.空间索引

MYISAM，INNODB,nod和archive存储引擎支持如point,geometry的空间列。Section 11.4, “Spatial Data Types”，前边这个链接详细解释了空间类型。然而不同存储引擎堆空间索引支持不同。空间列的空间和非空间索引可以根据以下的规则使用。

空间列上使用空间索引（created using spatial index）有以下一些特征

  • 仅仅对于myisma和onnob表可用，在一些其他存储引擎上指定 SPATIAL INDEX会报错。
  • 索引列必须不为空
  • 列前缀长度被禁止，每个列的整个长度都被索引。

空间列的非空间索引（created with index，unique，or primary key）有以下一些特征

  • 除了archieve存储引擎外允许用于任意支持空间列的存储引擎
  • 除非是主键，否则列可以为空
  • 对于每个空间列中的非空间索引（point外）列前缀必须被指定。这与索引blob的要求一致，前缀长度以字节为长度。
  • 非空间索引的索引类型依赖于存储引擎，目前使用的是B-tree
  • 仅仅使用于INNODB,MYISAM和MEMORY表中可以有null值的列

五.索引选项

在关键部分列表hi后列表之后，可以给出索引选项，index_options的值可以是以下任何一种

  • KEY_BLOCK_SIZE [=] value

对于MYISAM表，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选项

Index Types Per Storage Engine

Storage EnginePermissible Index Types
INNODBBTREE
MYISAMBTREE
MEMORY/HEAPHASH, BTREE
NDBHASH, BTREE (see note in text)

index_type子句不能用于全文索引和空间索引，全文索引的实现依赖于存储引擎，空间索引的实现依赖于R-Tree树。

在ndb引擎中使用Btree本质上使用的是Tree索引

对于 NDB 表列上的索引，只能为唯一索引或主键指定 USING 选项。 使用 HASH 可以防止创建有序索引；否则，在 NDB 表上创建唯一索引或主键会自动导致创建有序索引和散列索引，而这两种索引都会索引同一组列。
​
对于包含 NDB 表中一个或多个 NULL 列的唯一索引，散列索引只能用于查找字面值，这意味着 IS [NOT] NULL 条件需要对表进行全扫描。 一种解决方法是确保在此类表上创建使用一个或多个 NULL 列的唯一索引时，始终包含有序索引；也就是说，在创建索引时避免使用 USING HASH。

如果你指定的索引类型对存储引擎无效，但是使用另一种索引类型不会影响查询结果，则引擎会使用可用的类型，解析器可将RTree识别未类型名称，但是目前无法为任何存储引擎指定这种类型

在 ON tbl_name 子句之前使用 index_type 选项已被弃用；预计在未来的 MySQL 版本中将不再支持在该位置使用该选项。 如果在前面和后面的位置都给出了 index_type 选项，那么最后一个选项将适用。

type type_name被视为using type_name的同义词，不过using这种写法还是首选推荐

下表展示了支持index_type选项的存储引擎和索引特性

INNODB Storage Engine Index Characteristics

Index ClassIndex TypeStores NULL VALUESPermits Multiple NULL ValuesIS NULL Scan TypeIS NOT NULL Scan Type
Primary keyBTREENoNoN/AN/A
UniqueBTREEYesYesIndexIndex
KeyBTREEYesYesIndexIndex
FULLTEXTN/AYesYesTableTable
SPATIALN/ANoNoN/AN/A

MYISAM Storage Engine Index Characteristics

Index ClassIndex TypeStores NULL VALUESPermits Multiple NULL ValuesIS NULL Scan TypeIS NOT NULL Scan Type
Primary keyBTREENoNoN/AN/A
UniqueBTREEYesYesIndexIndex
KeyBTREEYesYesIndexIndex
FULLTEXTN/AYesYesTableTable
SPATIALN/ANoNoN/AN/A

MEMORY Storage Engine Index Characteristics

Index ClassIndex TypeStores NULL VALUESPermits Multiple NULL ValuesIS NULL Scan TypeIS NOT NULL Scan Type
Primary keyBTREENoNoN/AN/A
UniqueBTREEYesYesIndexIndex
KeyBTREEYesYesIndexIndex
Primary keyHASHNoNoN/AN/A
UniqueHASHYesYesIndexIndex
KeyHASHYesYesIndexIndex

NDB Storage Engine Index Characteristics

Index ClassIndex TypeStores NULL VALUESPermits Multiple NULL ValuesIS NULL Scan TypeIS NOT NULL Scan Type
Primary keyBTREENoNoIndexIndex
UniqueBTREEYesYesIndexIndex
KeyBTREEYesYesIndexIndex
Primary keyHASHNoNoTable (see note 1)Table (see note 1)
UniqueHASHYesYesTable (see note 1)Table (see note 1)
KeyHASHYesYesTable (see note 1)Table (see note 1)

如果使用了hash索引，则无法创建隐式的有序索引

  • WITH PARSER parser_name

该选项只能用于 FULLTEXT 索引。 如果全文索引和搜索操作需要特殊处理，该选项会为索引关联一个解析器插件。 INNODB 和 MYISAM 支持全文分析器插件。 如果 MYISAM 表带有相关的全文分析器插件，则可以使用 ALTER TABLE 将表转换为 INNODB。 如需了解更多信息，请参阅全文分析器插件和编写全文分析器插件。

  • COMMENT 'string'

索引定义的时候可以包含最多1024字符

可以使用 CREATE INDEX 语句中的 index_option COMMENT 子句为单个索引配置索引页的 MERGE_THRESHOLD。 例如

CREATE TABLE t1 (id INT);
CREATE INDEX id_index ON t1 (id) COMMENT 'MERGE_THRESHOLD=40';

如果在删除记录或更新操作缩短记录时，索引页的页满百分比低于 MERGE_THRESHOLD 值，INNODB 会尝试将索引页与相邻的索引页合并。 默认的 MERGE_THRESHOLD 值是 50，也就是之前的硬编码值。

六.表格复制和锁选项

可以使用 ALGORITHM 和 LOCK 子句来影响表的复制方法，以及在修改表的索引时读写表的并发程度。 它们的含义与 ALTER TABLE 语句相同。 Section 13.1.8, “ALTER TABLE Statement”

NDB Cluster 以前使用另一种语法支持在线 CREATE INDEX 操作，现在已不再支持该语法。 现在，NDB Cluster 支持使用与标准 MySQL Server 相同的 ALGORITHM=INPLACE 语法进行联机操作。

七.总结

  • mysql的索引类型根据索引创建语法划分有普通索引，唯一索引，文本索引，空间索引四种
  • 索引前缀可以被设置在字符类型的列中，不同引擎的支持有差异
  • 全文索引被MYISAM和INNODB引擎部分数据类型支持，不支持设置索引前缀
  • 空间索引可以设置在MYISAM和INNODB引擎的空间列（geometry）
  • 不同引擎对于索引类型，空值的支持等都有异同具体可以查看上述表格
avatar
文章
阅读
粉丝