索引的创建与设计原则

1.索引的声明与使用

1.1索引的分类

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

1.2创建索引(表已创建)

索引可以在创建表的时候一起创建索引，这种方式这里就不记录，如果感兴趣，可自行百度。

①方式一

ALTER TABLE 表名 ADD INDEX 索引名(字段);
1

②方式二

CREATE INDEX 索引名 ON 表名(字段)
1

1.3查看索引

SHOW INDEX FROM 表名;
1

1.4删除索引

①方式一

ALTER TABLE 表名 DROP INDEX 索引名
1

②方式二

DROP INDEX 索引名 ON 表名;
1

2.MySQL8.0新特性

1.1降序索引

MySQL5.7及以前添加的降序索引是通过反向扫描的方式，并没有真正的创建，MySQL8.0真正创建了降序索引。

1.2隐藏索引

MySQL8.0可以让索引隐藏起来失去作用，如果删除索引后发现问题，还可以马上回复，节约了重新创建浪费的大量时间。要注意的是隐藏索引同样会消耗插入、更新、删除的性能，所以如果长期不使用就将隐藏索引删除掉。

3.索引的设计原则

3.1哪些情况适合创建索引

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

2. 频繁的作为WHERE查询条件的字段

3. 经常GROUP BY 和 ORDER BY 的列

4. UPDATE、DELETE的WHERE条件列

5. DISTINCT字段需要创建索引

6. 多表JOIN连接操作时，创建索引注意事项

首先，连接表的数量尽量不要超过3张，每增加一张表就相当于增加了一次嵌套的循环，数量级增长会非常快，严重映像查询的效率。
其次，对于WHERE 条件创建索引，因为WHERE才是对数据条件的过滤，如果在数据量非常大的情况下，没有WHERE条件过滤是非常可怕的。
最后，对于连接的字段创建索引，并且该字段在多张表中的类型必须一致，不一致会自动使用类型转换函数，无法使用索引。

7. 使用列的类型小的创建索引

数据大小指的是该类型表示的数据范围的大小，TINYINT、MEDIUMINT、INT、BIGINT占用内存空间依次增大。

• 数据类型越小，查询进行比较操作越快。
• 数据类型越小，索引占用的存储空间越少，在一个数据页就可以放下更多的记录，从而减少磁盘的I/O带来的性能损耗，也就意味着把更多的数据页存到内存中，从而加快读写效率。

8. 使用字符串前缀创建索引

ALibaba 强制 在varchar字段建立索引时，必须指定索引长度，没必要对全字段建立索引，根据文本区分决定索引长度。

• B+树索引把整个的字符串保存起来，更费时，而且字符串越长占用空间越大。
• 如果B+树索引中，索引列存储字符串越长，那么在做字符串比较时会占用更多时间。

可以通过截取字段一部分内容建立索引，这个叫做前缀索引，这样也可以找到对应的字符串。
对于截取多少的问题，有一个公式count(distinct left(列名, 索引长度)) / count(*),这个值越接近1越好，当然如果几个索引长度所对应的值都差不多大，取索引长度比较小的。
因为索引前缀是对截取的部分字段作为索引，所以无法对整个字段使用索引排序。

9. 区分度高(散列度高)的列适合作为索引

散列度公式count(distinct 字段名)/count(*)

10. 使用频繁的列放到联合索引的左侧

由于最左前缀原则，，可以增加联合索引的使用率

11. 在多个字段都要创建索引的情况下，联合索引优于单值索引

3.2限制索引的数目

索引不是越多越好，建议一张表最多不超过6个，原因：
①索引占用磁盘空间，索引越多磁盘空间越大。
②索引影响INSERT、DELETE、UPDATE等语句性能，表中数据更改的同时，索引也要更改，会造成负担。
③优化器在选择如何优化查询时，会根据统一信息，对每个可以用到的索引进行评估，如果有多个索引可以用于查询，会增加SQL优化器生成执行计划的时间，降低查询性能。