JavaGuide知识点整理——MySQL索引

何为索引？有什么作用？

索引是一种用于快速查询和检索数据的数据结构，常见的索引结构有：B树，B+树和hash
索引的作用就相当于书的目录。

索引的优缺点

优点：

• 使用索引可以大大加快数据的检索速度，这也是创建索引的最主要的原因。
• 通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性

缺点：

• 创建索引和维护索引需要耗费许多时间，当对表中的数据进行增删改的时候，如果数据有索引，那么索引也需要动态的修改，会降低sql执行效率
• 索引需要使用物理文件存储，也会耗费一定空间。

索引不一定能提高查询性能，大多数情况下索引查询都比全表扫描要快的，但是如果数据库的数据量不大，那么使用索引也不一定能带来很大的提升。

索引的底层数据结构

Hash表

哈希表是键值对的集合，通过键即可快速取出对应的值。因此哈希表可以快速检索数据。
但是哈希算法有个hash冲突。也就是说多个key得到同一个value。通常情况下我们常用的解决方法就是链地址法。就是将同一hash的数据存放在链表中。
既然哈希表这么快，为什么MySQL没有使用其作为索引的数据结构呢？

1. hash冲突问题
2. Hash索引不支持顺序和范围查询。

B树和B+树

B树也叫B-树，全程是多路平衡查找树。B+树是B树的一种变体。B是balanced(平衡)的意思。

B树和B+树异同：

• B树的节点存放key也存放数据。B+树只有叶子节点存放key和数据，其余只存放key。
• B树的叶子节点是独立的.B+树的叶子节点有引用链指向相邻的叶子节点
• B树的检索过程相当于每个节点二分，效率不稳定。而B+树检索效率很稳定，任何查找都是从根节点到叶子节点的过程。叶子节点的顺序检索很明显。

MyISAM引擎中，B+树的叶子节点数据存放的是数据记录的地址。然后根据地址读取对应的数据记录，这叫做非聚簇索引。
InnoDB中，叶子节点key是数据表的主键，data中保存了完整的数据记录。这种叫聚簇索引。而其余的索引都叫做辅助索引。辅助索引的data存放的是记录主键的值而不是地址。在使用时，如果查询的是主索引，则直接获取key对应的数据。如果是辅助索引查找，则先获取主键的值，再走一遍主索引。

索引类型

主键索引

数据表的主键列使用的就是主键索引。
一张表只能有一个主键(但可以是多个列组成的联合主键)，且主键不能为null，不能重复。在MySQL的InnoDB表中，如果没有显示指定表的主键，InnoDB会自动先检查表中是否有唯一索引且不允许存在null值的字段。如果有则默认该字段为主键。否则InnoDB会自动创建一个6字节的自增主键。

二级索引(辅助索引)

二级索引又称为辅助索引，是因为二级索引的叶子节点存储的数据是主键。也就是说通过二级索引，可以定位主键的位置。
唯一索引，普通索引，前缀索引等都属于二级索引。

• 唯一索引(Unique Key) ：唯一索引也是一种约束。唯一索引的属性列不能出现重复的数据，但是允许数据为 NULL，一张表允许创建多个唯一索引。 建立唯一索引的目的大部分时候都是为了该属性列的数据的唯一性，而不是为了查询效率。
• 普通索引(Index) ：普通索引的唯一作用就是为了快速查询数据，一张表允许创建多个普通索引，并允许数据重复和 NULL。
• 前缀索引(Prefix) ：前缀索引只适用于字符串类型的数据。前缀索引是对文本的前几个字符创建索引，相比普通索引建立的数据更小， 因为只取前几个字符。
• 全文索引(Full Text) ：全文索引主要是为了检索大文本数据中的关键字的信息，是目前搜索引擎数据库使用的一种技术。Mysql5.6 之前只有 MYISAM 引擎支持全文索引，5.6 之后 InnoDB 也支持了全文索引。

聚集索引和非聚集索引

聚集索引

聚集索引即索引结构和数据一起存放的索引，主键索引属于聚集索引
聚集索引的优点：
聚集索引的查询速度非常快，因为整个B+树本身就是一颗多叉平衡树，叶子节点也都是有序的，定位到索引的节点相当于定位到了数据。
聚集索引的缺点：

• 依赖于有序的数据：因为B+树是多路平衡树，如果索引的数据不是有序的，那么就需要在插入时排序，如果数据是字符串或者uuid这种又长又难比较的数据，插入或者查找的速度会比较慢。
• 更新代价大：如果对索引列的数据被修改时，那么对应的索引也会被 修改，而且聚集索引的叶子节点还存放着数据，修改代价是很大的。所以对于主键索引来讲，主键一般都是不可修改的。

非聚集索引

非聚集索引即索引结构和数据分开存放的索引。
二级索引属于非聚集索引。非聚集索引的叶子节点并不一定存放数据的指针，因为二级索引的叶子节点就存放的是主键，根据主键再回表查数据。
非聚集索引的优点：
更新代价比聚集索引小。因为非聚集索引的叶子节点不存放数据。
非聚集索引的缺点：

• 跟聚集索引一样，非聚集索引也依赖于有序的数据
• 可能会徽标查询：因为查到索引对应的指针或者主键后，还需要再次到数据文件中查询。

非聚集索引一定要回表查询么？

非聚集索引不一样回表查询。因为可能查询的字段正好是索引，那么直接取key就行了。这种情景叫做覆盖索引。

覆盖索引

如果一个索引包含(或者说覆盖)所有需要查询的字段的值，我们就称之为覆盖索引。覆盖索引即需要查询的字段正好是索引的字段，那么直接根据该索引，就可以查到数据了，无需回表查询。

联合索引

使用表中的多个字段创建索引，就是联合索引，也叫做组合索引或者复合索引。

最左前缀匹配原则

在使用联合索引的时候，MySQL会根据联合索引中字段顺序，从左到右依次到查询条件中匹配。直到联合索引中全部字段匹配完成或者遇到范围查询才会停止。
所以我们使用联合索引时，可以将区分度最高的字段放在最左边。

索引下推

MySQL5.6中提供的索引优化功能，可以在非聚簇索引遍历过程中，对索引中包含的字段先做判断，过滤掉不符合条件的记录，减少回表次数。

创建索引的注意事项

1. 选择合适的字段创建索引

   • 不为null的字段：因为数据为null 的字段数据库比较难优化
   • 被频繁查询的字段
   • 被作为条件查询的字段
   • 频繁需要排序的字段
   • 被经常用于连接的字段

2. 被频繁更新的字段应该慎重建立索引
   因为索引的维护成本不小。如果一个字段经常被修改且不经常被查询，不应该建立索引。

3. 尽可能的考虑建立联合索引而不是单列索引
   因为索引需要占用磁盘空间，可以简单理解为每一个索引都对应一棵B+树。如果一个表索引过多，那么索引占用空间也是很多的，且修改的时候耗费的时间也多。如果是联合索引，多个字段在一个索引上，会节约很大磁盘空间。

4. 注意避免冗余索引
   如果同时建立a,b,c。 a,b。 a。这三个索引，就算是冗余索引。

5. 考虑再字符串类型的字段上使用前缀索引代替普通索引
   前缀索引仅限于字符串类型，较普通索引会占用更小的空间。

使用索引的一些建议

• 中到大型表索引都是非常有效的。但是特大型表的话维护开销会很大，不适合建立索引。
• 避免在where子句中对字段施加函数，会造成无法命中索引
• 在InnoDB时使用和业务无端的自增主键作为主键。而不要使用业务主键。
• 删除长期未使用的索引，不用的索引会造成不必要的性能损耗。
• 在使用limit offset查询缓慢时，可以借助索引来提高性能。

本篇笔记就记到这里，如果稍微帮到你了记得点个喜欢点个关注。也祝大家工作顺顺利利！生活愉快~