数据库索引以及索引结构

索引：

• 是用来帮助MySQL高效的获取数据的数据结构。就相当于一本字典的目录，加速数据库的查找速度；
• 索引本身也不小，一般存在磁盘上的文件中；
• 一般所说的索引都是B+树结构。

索引的优点：

1. 高效率进行数据的检索，降低数据库查询数据的IO成本；
2. 降低数据排序成本，降低CPU的消耗，通过索引类进行数据排序。

索引的缺点：

1. 创建索引会占据磁盘空间；
2. 索引提高查询效率，但是会降低更新表的效率。

都有哪些索引类型？

• 主键索引（聚簇索引）：用于标识索引列的值唯一，不允许有空值，一个表只能有一个聚餐索引；

  • InnoDB创建聚簇索引的规则：
    • 在表上定义primary Key则表示有主键索引作为聚簇索引；
    • 没有定义主键，则是第一个不为null的列作为聚簇索引；
    • 如果两个都没有，则创建一个6字节的ROWID进行作为聚簇索引，会在插入数据的时候进行自增。

• 辅助索引：除聚簇索引之外的所有索引都称为辅助索引。

  • 主键索引的叶子节点会存储数据行，辅助索引存放的是主键值。
  • 使用辅助索引进行检索查询数据需要检索两边索引：首先检索辅助索引回去主键值，然后使用主键值进行再次查询索引进行获取记录。

• 唯一索引：用于标识索引列的值唯一，但是允许有空值；

• 辅助索引（二级索引）：除聚簇索引之外的所有索引都称为辅助索引

  • InnoDB的辅助索引只会存储主键值而非磁盘地址；

  • 在MyISAM 中,辅助索引和主键索引的结构是一样的，没有任何区别，叶子节点的数据存储的都是行记录的磁盘地址。只是主键索引的键值是唯一的，而辅助索引的键值可以重复；

    • 查询数据时，由于辅助索引的键值不唯一，可能存在多个拥有相同的记录，所以即使是等值查询，也需要按照范围查询的方式在辅助索引树中检索数据。

• 全文索引：效率慢，可以在文本类型char、varchar、text上创建；

  • 通过数值⽐较、范围过滤等就可以完成绝⼤多数需要的查询，但如果希望通过关键字匹配进⾏查询，就需要基于相似度的查询，⽽不是精确的数值⽐较，全⽂索引就是为这种场景设计的；

  • 用于查找文本上的关键词，而不是直接比较是否相等。

• 普通索引：基本类型索引。

• 前缀索引：对于BLOB、TEXT 和 VARCHAR 类型的列使用前缀索引，只索引开始的部分字符，长度具体确定。

• 组合索引（联合索引）：多个字段创建的索引，遵循最左前缀匹配原则

  • 比如索引（x，y，z） select * from table where x=1and y=2 and z=3；

    B+树会先比较x列来确定下一步应该搜索的方向，往左还是往右。如果x列相同再比较y列；

    但是如果查询条件没有x列，B+树就不知道第一步应该从哪个节点查起；

    相当于创建了（x）、（x，y）、（x，y，z）三个索引。

  • 组合索引的最左前缀匹配原则：使用组合索引查询时，mysql会一直向右匹配直至遇到范围查询(>、<、between、like)就停止匹配。

  索引使用原则：

  1. 让选择性最强的索引列放在前面。索引的选择性是指：不重复的索引值和记录总数的比值；
  2. 尽量选择区分度高的列作为索引；
  3. 索引列不能参与计算，尽量保持列独立；
  4. 尽可能的扩展索引，不要新建立索引。

• 覆盖索引：索引包含所有需要查询的字段的值

  • 不是一种数据结构，而是一种优化索引的手段；
  • 一般在使用辅助索引的时候，我们拿到主键，回去数据还需要进行再次主键索引相当于回表，如果需要的数据少的话，意味着我们查到组合索引的叶子结点就可以直接返回，不需要回表操作；
  • 覆盖索引可以减少树的搜索次数，提升性能，他也是我们在实际开发过程中经常用来优化查询效率的手段；
  • 避免回表的操作。

  举一个使用覆盖索引的例子：有一个Stu表

  [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-woBugoGg-1627291992031)(C:\Users\肖森波\AppData\Roaming\Typora\typora-user-images\image-20210726155108812.png)]

  /*
  如果在一个场景下：
  select stu_id,Stu_class,stu_username from Stu where stu_username ='zhangsan';
  
  这个语句在查询业务上频繁使用到，而Stu表的其他字段使用频率很低，在这种情况下，如果我们在建立 stu_username 字段的索引的时候，不是使用单一索引，而是使用联合索引（stu_username，stu_sex）这样的话再执行这个查询语句是不是根据辅助索引查询到的结果就可以获取当前语句的完整数据。
  这样就可以有效地避免了回表再获取sex的数据。
  */
  

  这里就是一个典型的使用覆盖索引的优化策略减少回表的情况。

• 回表;根据在辅助索引中获取的主键id，到主键索引树检索数据的过程称为回表查询。

  等等：

可以索引的数据结构都有哪些？

Hash：

• 跟集合的Hash差不多，是根据Hash算法计算的下标位置，可能出现哈希冲突；
• 查询精准快速，但不支持范围查询，范围查询就成了全文检索；
• 显然不适合数据库索引使用

适合场景：

等只查询的场景，就只有KV形式的情况，在Redis、Memcached一些NOSql的中间件；

有序数组：

​ 有序数组在范围查询和等值查询上很好；有序的适合静态数组，

​ 可以做来静态存储引擎，保存一些静态数据，不会变动的静态数据

​ 有序数组的缺点就是变换数据时会移动数据，改变数据结构；

​ 静态数组存放一些一般不会改变的数据也是不错的。

二叉树：

• 是有序的，可以支持范围查询；
• 时间复杂度是log（N），为了维持时间复杂度更新的复杂度也要一样，就成了完全平衡二叉树了；
• 但随着数据的增加，对于二叉树就会变的很高，查询消耗的时间就会很多。

B树：

数据结构是一个结点可以存储多个数据，相比二叉树就很矮，就会提高磁盘的IO效率，

1. B树不支持范围查询的快速查找，如果数据不在同一个磁盘上就需要从根节点进行多次遍历，查询效率有待提高。
2. 如果data存储的是行记录，行的大小随着列数的增多，所占空间会变大。这时，一个页中可存储的数据量就会变少，树相应就会变高，磁盘IO次数就会变大。

B+树：

• 是B树的升级版，只在叶子结点存放数据，其他节点存放索引值，然后叶子结点再加上一个双向链表连接，方便了范围查询的效率。
• B+树中的非叶子节点会冗余一份在叶子节点中，并且叶子节点之间用指针相连。
• B+树一个结点为一页或者一页的倍数最好；

提高查询索引时的磁盘I/O效率，并可以提高范围查询的效率，并且B+数里面的元素是有序的