MySQL高级（六）

六、索引的创建与设计原则

1. 索引的声明与使用

1.1 索引的分类

MySQL的索引包括普通索引、唯一性索引、全文索引、单列索引、多列索引和空间索引等。

从 功能逻辑 上说，索引主要有 4 种，分别是普通索引、唯一索引、主键索引、全文索引。
按照 物理实现方式 ，索引可以分为 2 种：聚簇索引和非聚簇索引。
按照 作用字段个数 进行划分，分成单列索引和联合索引。

不同的存储引擎支持的索引类型也不一样：

InnoDB ：支持 B-tree、Full-text 等索引，不支持 Hash 索引；

MyISAM ：支持 B-tree、Full-text 等索引，不支持 Hash 索引；

Memory ：支持 B-tree、Hash 等索引，不支持 Full-text 索引；

NDB ：支持 Hash 索引，不支持 B-tree、Full-text 等索引；

Archive ：不支持 B-tree、Hash、Full-text 等索引；

1.2 创建索引

1.2.1 创建表的时候创建

CREATE TABLE table_name [col_name data_type]
[UNIQUE | FULLTEXT | SPATIAL] [INDEX | KEY] [index_name] (col_name [length]) [ASC | DESC]

UNIQUE 、 FULLTEXT 和 SPATIAL为可选参数，分别表示唯一索引、全文索引和空间索引；
INDEX 与 KEY 为同义词，两者的作用相同，用来指定创建索引；
index_name 指定索引的名称，为可选参数，如果不指定，那么MySQL默认col_name为索引名；
col_name 为需要创建索引的字段列，该列必须从数据表中定义的多个列中选择；
length 为可选参数，表示索引的长度，只有字符串类型的字段才能指定索引长度；
ASC 或 DESC 指定升序或者降序的索引值存储。

1.2.2 在已经存在的表上创建索引

# 1. 使用ALTER TABLE语句创建索引：
ALTER TABLE table_name ADD [UNIQUE | FULLTEXT | SPATIAL] [INDEX | KEY]
[index_name] (col_name[length],...) [ASC | DESC]
# 2. 使用CREATE INDEX创建索引
CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name
ON table_name (col_name[length],...) [ASC | DESC]

1.3 删除索引

# 1. 使用ALTER TABLE语句删除索引：
ALTER TABLE table_name DROP INDEX index_name;
# 2. 使用DROP INDEX删除索引
DROP INDEX index_name ON table_name;

提示：删除表中的列时，如果要删除的列为索引的组成部分，则该列也会从索引中删除。如果组成索引的所有列都被删除，则整个索引将被删除。

2. MySQL8.0索引新特性

2.1 支持降序索引

仅限InnoDB

2.2 隐藏索引

从MySQL 8.x开始支持 隐藏索引（invisible indexes） ，只需要将待删除的索引设置为隐藏索引，使查询优化器不再使用这个索引（即使使用force index（强制使用索引），优化器也不会使用该索引），确认将索引设置为隐藏索引后系统不受任何响应，就可以彻底删除索引。 这种通过先将索引设置为隐藏索引，再删除索引的方式就是软删除 。

注意：当索引被隐藏时，它的内容仍然是和正常索引一样实时更新的。如果一个索引需要长期被隐藏，那么可以将其删除，因为索引的存在会影响插入、更新和删除的性能。

CREATE TABLE tablename(
propname1 type1[CONSTRAINT1],
propname2 type2[CONSTRAINT2],
……
propnamen typen,
INDEX [indexname](propname1 [(length)]) INVISIBLE
);

CREATE INDEX indexname
ON tablename(propname[(length)]) INVISIBLE;

CREATE INDEX indexname
ON tablename(propname[(length)]) INVISIBLE;

ALTER TABLE tablename ALTER INDEX index_name INVISIBLE; #切换成隐藏索引
ALTER TABLE tablename ALTER INDEX index_name VISIBLE; #切换成非隐藏索引

# 使隐藏索引对查询优化器可见
mysql> select @@optimizer_switch \G
mysql> set session optimizer_switch="use_invisible_indexes=on";

3. 索引的设计原则

3.1 适合创建索引的情况

字段的数值有唯一性的限制

业务上具有唯一特性的字段，即使是组合字段，也必须建成唯一索引。（来源：Alibaba）说明：不要以为唯一索引影响了 insert 速度，这个速度损耗可以忽略，但提高查找速度是明显的。
频繁作为 WHERE 查询条件的字段

某个字段在SELECT语句的 WHERE 条件中经常被使用到，那么就需要给这个字段创建索引了。尤其是在数据量大的情况下，创建普通索引就可以大幅提升数据查询的效率。

经常 GROUP BY 和 ORDER BY 的列

索引就是让数据按照某种顺序进行存储或检索，因此当我们使用 GROUP BY 对数据进行分组查询，或者使用 ORDER BY 对数据进行排序的时候，就需要 对分组或者排序的字段进行索引 。如果待排序的列有多个，那么可以在这些列上建立 组合索引 。
UPDATE、DELETE 的 WHERE 条件列

对数据按照某个条件进行查询后再进行 UPDATE 或 DELETE 的操作，如果对 WHERE 字段创建了索引，就能大幅提升效率。原理是因为我们需要先根据 WHERE 条件列检索出来这条记录，然后再对它进行更新或删除。如果进行更新的时候，更新的字段是非索引字段，提升的效率会更明显，这是因为非索引字段更新不需要对索引进行维护。
DISTINCT 字段需要创建索引
多表 JOIN 连接操作时，创建索引注意事项

首先， 连接表的数量尽量不要超过 3 张 ;其次，对 WHERE 条件创建索引 ; 最后， 对用于连接的字段创建索引 。
使用列的类型小的创建索引

类型越小能表示的数据范围越小，这样创建索引，更新索引时的花销更小。
使用字符串前缀创建索引
拓展：Alibaba《Java开发手册》

【强制】在 varchar 字段上建立索引时，必须指定索引长度，没必要对全字段建立索引，根据实际文本区分度决定索引长度。 (字符串太长时间和空间都很浪费)

说明：索引的长度与区分度是一对矛盾体，一般对字符串类型数据，长度为 20 的索引，区分度会 高达 90% 以上 ，可以使用 count(distinct left(列名, 索引长度))/count(*)的区分度来确定。

使用索引列前缀就无法支持索引列排序。
```
count(distinct left(列名, 索引长度))/count(*)
```
区分度高(散列性高)的列适合作为索引

可使用公式计算区分度，并且在联合索引时把区分度高的列写在前面。
使用最频繁的列放到联合索引的左侧
在多个字段都要创建索引的情况下，联合索引优于单值索引

3.2 限制索引数量

单张表的索引数量不超过6个

每个索引都会占用 磁盘空间。
索引会影响INSERT、DELETE和UPDATE的性能，因为在更新表的数据时，索引也会调整造成负担。
优化器在选择如何优化查询时，会对每一个可以用到的索引进行评估，以生成一个最好的执行计划，如果同时有很多索引都可以用于查询，会增加MySQL优化器生成执行计划的时间。

3.3 不适合创建索引的情况

在where中使用不到的字段，不要设置索引
数据量小的表最好不要使用索引

结论：在数据表中的数据行数比较少的情况下，比如不到 1000 行，是不需要创建索引的
有大量重复数据的列上不要建立索引

结论：当数据重复度大，比如 高于 10% 的时候，也不需要对这个字段使用索引
避免对经常更新的表创建过多的索引
不建议用无序的值作为索引

例如身份证、UUID(在索引比较时需要转为ASCII，并且插入时可能造成页分裂)、MD5、HASH、无序长字符串等。
删除不再使用或者很少使用的索引
不要定义冗余或重复的索引