MySQL（二）索引

索引

1. 概念

   • 索引是存储引擎用于提高数据库表的访问速度的一种数据结构。可以比作一本字典的目录，可以帮你快速找到对应的记录。
   • 一般存储在磁盘的文件中，占用物理空间

2. 优缺点：

   1. 优点：
      • 加快数据查找的速度
      • 为用来排序或者分组的字段添加索引，可以加快分组和排序的速度
      • 加快表与表之间的连接
   2. 缺点
      • 建立索引需要占用物理空间
      • 会降低表的增删改的效率，因为每次对表记录进行增删改，需要进行动态维护索引，导致增删该时间变长

3. 作用：数据存储在磁盘上，查询数据时，如果没有索引，会加载所有的数据到内存，然后依次进行检索，这样读取磁盘次数较多。有了索引，就不需要加载所有数据，因为B+树的高度一般在2-4层，最多只需要读取2-4次磁盘，查询速度大大提升

4. 什么情况下需要建索引？

   • 查询，连接，排序

5. 什么情况下不建索引？

   • where条件中用不到的字段不适合建立索引
   • 表记录少的。比如只有几百条，就没必要
   • 需要经常增删改。需要评估之后再用，索引会降低表的增删改的效率，需要进行动态维护索引，增删改时间会变长
   • 参与列计算的列不适合建索引
   • 区分度不高的字段不适合建立索引，比如性别，只有男/女/未知三个值。加了索引，查询效率也不会提高

6. 索引的使用：

   • 查看索引：show index from 表名

   • 手动创建索引：create index 索引名 on 表名，正确的索引创建是在建表的时候

   • 删除索引：drop index 索引名 on 表名

7. 索引的数据结构

   ​ 索引的数据结构主要有B+树和哈希表，对应的索引分别为B+树索引和哈希索引。InnoDB引擎的索引类型有B+树索引和哈希索引，默认的索引类型为B＋树索引

   1. B＋树索引

      B+树是B树的变种，所有的值都在叶子节点上，并且叶子节点是通过指针连接的，这样就提供了对数据的顺序访问。内部节点（非叶子节点）只存储键值，并作为索引使用。

      1. B＋树是基于B树和叶子节点顺序访问指针进行实现，具有B树的平衡性
      2. 通过顺序访问指针来提高区间查询的功能
      3. 叶子节点有指针
      4. 非叶子节点都冗余了一份在叶子节点上
      5. 一个节点有多个元素

      B+树与B树类似，但不同的是：一是非叶子节点不存储数据，仅用于索引；二是所有叶子节点之间都是相互链接的，支持快速的顺序遍历

      B+树优缺点：

      • 优点：所有的查询都要查找到叶子节点，查询性能稳定，叶子节点形成了一个有序链表，便于全范围扫描
      • 缺点：由于数据只存在于叶子节点，所以可能需要更多的I/O操作来达到叶子节点

   2. 哈希索引和B＋树索引的区别

      • 哈希索引不支持排序，哈希表是无序的
      • 哈希索引不支持范围查找
      • 哈希索引不支持模糊查询及多列索引的最左前缀匹配
      • 因为存在哈希冲突，所以性能不稳定。B＋树相对稳定。

8. 索引的分类

   1. 主键索引：名为primary的唯一非空索引，不允许有空值
   2. 唯一索引：索引列中的值必须唯一，允许为空值。
   3. 组合索引：在多个字段组合上创建的索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用，使用组合索引时需遵循最左前缀原则。
   4. 全文索引：只能在char、varchar、TEXT类型字段上使用
   5. 普通索引：最基本的索引，没有任何限制，可以为空值