Mysql索引

最新推荐文章于 2024-09-09 14:47:35 发布

一剑逍遥

最新推荐文章于 2024-09-09 14:47:35 发布

阅读量974

点赞数 23

分类专栏： MySQL系列文章标签： mysql 数据结构

本文链接：https://blog.csdn.net/qq_44781222/article/details/128666296

版权

MySQL系列专栏收录该内容

6 篇文章 0 订阅

订阅专栏

1. 索引概念以及优缺点

什么是索引

索引是帮助MySQL高效获取数据的排好序的数据结构
为什么要建立索引
1. 数据在磁盘中的位置肯定是随机的，行数据的查询需要与磁盘IO获取
2. 磁盘IO效率低，减少I/O的次数，加快查询速率
3. 相邻行数据存储的位置也不一定相邻：磁盘写数据时，相邻行数据的写入可能存在时间差，而这个时间差磁道可能已经写入其他数据
索引的优缺点
- 优点：
  - 提高数据检索的效率，降低数据库的IO成本，最主要的原因
  - 创建唯一索引，保证数据库表中每一行数据的唯一性
  - 加速表和表之间的连接
  - 在使用分组和排序子句进行数据查询时，可以显著减少查询中分组和排序的时间，降低了CPU的消耗。
- 缺点：
  - 创建索引和维护索引要耗费时间
  - 索引需要占磁盘空间，大量的索引下，索引文件就可能比数据文件更快达到最大文件尺寸
  - 虽然索引大大提高了查询速度，但是同时也会降低更新表的速度

2. 索引数据结构详解

数据结构网站：https://www.cs.usfca.edu/~galles/visualization/Algorithms.html

二叉树
1. 特点：
  二叉树的右子节点比父节点大，左子节点比父节点小。通过这一特点进行查询时，通过比对可以快速定位节点位置
2. 局限性：
  1. 数据形成的是满二叉树或者完全二叉树的时候去查找才能很好的发挥索引的优势
  2. 对于单列自增长的数据列建立所以，会形成链表形式的二叉树，要找到指定元素，跟全表遍历没有区别，IO次数无变化，效率比较低下
3. 示例图
  
  特殊情况示例
红黑树
1. 特点：
  1. 红黑树又称二叉平衡树，在新增节点时可以自动调整节点的位置
2. 局限性：
  1. 虽然效率比二叉树高，但是在大数据量的情况下，树的高度可能会很大，那么IO交互的次数≥树的高度，查询效率依然不高
  2. 大数据量时，树的高度可能会很高
3. 如果查询的数据恰好在叶子节点，而树的查询都是从父节点开始的，执行的IO次数也会很多。
4. 示例图
Hash表
1. 特点：
  1. 对索引的key进行依次hash计算就可以定位出数据存储的位置
  2. 构建一个HASH桶，将计算好HASH的元素以及地址放到桶的指定位置，再有相同的则追加在元素后面。查询则先计算好在桶的哪个位置，再遍历链表。故很多时候Hash索引要比B+ 树索引更高效
2. 局限性：
  1. 仅能满足 “=”，“IN”，不支持范围查询
  2. 存在hash冲突问题
3. 示例图：
正在上传…重新上传取消正在上传…重新上传取消
B-Tree（B树）
1. 特点
  1. 叶节点具有相同的深度，叶节点的指针为空
  2. 所有索引元素不重复
  3. 节点中的数据索引从左到右递增排列
  4. B树的结构是在一个节点里面放很多的小节点元素，通过节点的横向扩展来解决树的高度问题。小节点元素是从左到右递增排列的。
2. 局限性：（和B+树比较）
  1. B+树非叶子节点不存储data,只存储索引，因此一个叶子节点（数据页）可以存储更多的索引
  2. B+树叶子节点使用双向指针连接，区间访问数据（范围查询）更加高效
3. 示例图
B+Tree(B-Tree 变种)（B+树）
1. 特点：
  1. 非叶子节点不存储data，只存储索引(冗余)，可以放更多的索引
  2. 叶子节点包含所有索引字段
  3. 叶子节点用指针连接，提高区间访问的性能
  4. 从左到右依次递增，自动排序
  5. 层数最多3层
2. 什么是叶子节点？
  叶子节点：最后一层，其他层为非叶子节点
3. 示例图

3. 聚集索引&聚族索引&稀疏索引到底是什么

索引类型

非聚集索引：索引文件和数据文件是分离的

聚集索引（聚族索引）：聚集索引-叶节点包含了完整的数据记录，一般只有一个，可以理解为主键索引

联合索引：多个字段共同组成一个索引，B+树结构，最左前缀优化原则，依次执行，不能跳过
MySQL存储引擎
- MyISAM
  - MyISAM是非聚集索引
  - 索引文件和数据文件是分离的(非聚集索引/稀疏索引)
  - .frm(数据表结构)、.MYD(数据文件)、.MYI(索引文件)
- 示例图
- InnoDB
  - InnoDB是聚集索引（聚族索引）
  - 索引数据文件：.ibd(索引数据文件)；.frm(表结构文件)；
  - 表数据文件（.ibd文件）本身就是按B+Tree组织的一个索引结构文件
  - 聚集索引-叶节点包含了完整的数据记录
- 示例图
最左优化原则
- 按索引键先后顺序建立，自左而右
- 索引按B+树结构按顺序排序

4. 思考

为什么建议InnoDB表必须建主键，并且推荐使用整型的自增主键？
1. 如果设置了主键，那么InnoDB会选择主键作为聚集索引、如果没有显式定义主键，则InnoDB会选择第一个不包含有NULL值的唯一索引作为主键索引、如果也没有这样的唯一索引，则InnoDB会选择内置6字节长的ROWID作为隐含的聚集索引(ROWID随着行记录的写入而主键递增)。
2. 如果设置了主键，那么InnoDB会选择主键作为聚集索引、如果没有显式定义主键，则InnoDB会选择第一个不包含有NULL值的唯一索引作为主键索引、如果也没有这样的唯一索引，则InnoDB会选择内置6字节长的ROWID作为隐含的聚集索引(ROWID随着行记录的写入而主键递增)。
3. 如果使用非自增主键（如果身份证号或学号等）由于每次插入主键的值近似于随机，因此每次新纪录都要被插到现有索引页得中间某个位置，此时MySQL不得不为了将新记录插到合适位置而移动数据，甚至目标页面可能已经被回写到磁盘上而从缓存中清掉，此时又要从磁盘上读回来，这增加了很多开销，同时频繁的移动、分页操作造成了大量的碎片，得到了不够紧凑的索引结构，后续不得不通过OPTIMIZE TABLE来重建表并优化填充页面。
为什么非主键索引结构叶子节点存储的是主键值？(一致性和节省存储空间)
1. 一致性：如果存储的是完整的数据，一旦数据变更，主键索引的叶子节点+非主键索引的叶子节点数据都更新完成后，才算更新操作完成。而存储的是主键的话，主键索引的叶子节点数据更新完成后即可认为是更新操作完成，为了保证一致性，避免同步数据造成的数据问题。
2. 节省磁盘存储空间，相比较完整的数据，主键显然占用空间更小。