MySQL --- 读书笔记 --- 索引（1）

索引的分类

1. 普通索引
2. 主键索引
3. 唯一索引
4. 联合索引

创建索引

• 创建表时创建索引

CREATE TABLE table_name [col_name data_type]
[UNIQUE] INDEX index_name (col_name) [ASC | DESC]

• 已创建表时创建索引

ALTER TABLE table_name ADD [INDEX | UNIQUE] index_name(col_name)

删除索引

ALTER TABLE table_name DROP INDEX index_name

DROP INDEX index_name ON table_name

MySQL8.0索引

• 支持降序索引
• 隐藏索引，设置索引为隐藏后，SQL的执行将不会使用这个索引，在确定系统不受影响之后，就可以彻底删除，设置隐藏是一种软删除

索引的设计原则

一、哪些情况适合创建索引

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

• 当表中某一个数据在业务上具有唯一性的特征时，应该为其创建唯一性索引或者主键索引
• 即使是组合字段，只要是业务上具有唯一性，也应该为其创建唯一索引
• 添加唯一索引会影响插入的速度，但是这个损耗相对查询速度的提升，是可以忽略的

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

• 当一个字段经常在查询条件中出现，那么就应该考虑为其添加索引

3. 经常出现在GROUP BY和ORDER BY中的字段

• 索引就是让数据以某种顺序有序地存储并检索，那么使用GROUP BY或者ORDER BY检索时，就需要对这个字段进行索引，如果有多个字段，那么可以考虑联合索引

• GROUP BY是对相同的值进行分组，那么索引的字段本身就是相同的值会在一起，那么字段建立索引后，分组检索速度会提升

• ORDER BY是对某字段排序，字段索引后，本身就是有序的，那么会减少排序的时间，排序检索速度会提升

4. UPDATE和DELETE的WHERE条件字段

• 如果进行更新的时候，更新字段不是索引字段，那么效率会更高，因为不需要对索引字段进行维护

5. DISTINCT字段需要创建索引

• 索引后的字段，相同的值都会放在一起，去重就更高效率

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

1. 连接表的数量尽量不超过3，因为每增加一张表，相当于增加一次嵌套循环，时间呈指数级别增加
2. 对WHERE条件字段索引，因为WHERE是对数据的过滤，如果数据量很大的时候，没有过滤的数据是非常大的
3. 对用于连接的字段创建索引，并且该字段在两张表中的类型必须一致，因为当类型不一致的时候，系统会隐式调用函数对字段进行转换，一旦使用函数，索引就会失效

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

• 类型小指的是类型表示的数据范围小
• 对于数据类型范围更小的列，它所占的空间越小，那么页中存储的数据也就更多，那么磁盘I/O次数也就更少
• 数据类型越小，查询时的比较操作越快
• 对于主键来说更是如此，因为主键不仅仅在聚簇索引中，二级索引中也有存有主键，主键更小，也就能节省更多的存储空间以及更高效的I/O

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

当字符串很长时，它需要占用比较大的存储空间，当为其创建索引的时候，需要记录完整的字符串，更费时，索引占用的空间越大，而且在做比较时耗时更多

可以通过截取字符串一部分内容建立索引，叫做前缀索引。这样在检索时，虽然不能精确定位到记录，但是可以定位到记录的前缀位置，然后再回表查询。这样既节约了空间，又减少了字符串比较的时间，还大体能解决排序的问题

如何选择截取的长度呢，长了达不到节约空间的目的，短了数据重复性太高，数据的选择性会降低

• 可以计算一下字段在表中的选择度是多少

select count(distinct left(address_name, num)) / count(*) from table_name;

通过截取不同的长度，比较它们之间的选择度，越大，代表选择度越高

9. 区分度（散列性）高的列适合做索引

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

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

• 这样可以较少建立索引，同时，由于“最左前缀原则”，提高联合索引的使用率

二、限制索引的数目

在实际中，索引的数量不是越多越好，建议每张表的索引不超过6个

• 因为每个索引都需要占用空间
• 索引会影响增删改的性能
• 优化器在优化查询时，会对每一个可以用到的索引进行评估，如果有多个索引都可以选择，那么在生成执行计划时消耗时间也就多

三、哪些情况下不能创建索引

1. 在WHERE中使用不到的字段，不要设置索引

• 包括在GROUP BY和ORDER BY中用不到的

2. 数据量少的表最好不要设置索引

• 表的数据量太少，比如少于1000条记录，那么是不需要创建索引的；记录太少，是否创建索引对查询效率的影响并不大，甚至说，查询花费的时间可能比遍历索引的时间还要短

3. 有大量重复数据的列不要创建索引

• 区分度太低，不但不会提高查询效率，反而会严重降低数据更新的速度

4. 避免对经常更新的表创建过多的索引

5. 不建议用无序的值作为索引

6. 删除不再使用或者很少使用的索引

7. 不要定义重复或者冗余的索引