深入理解MySQL底层数据结构和算法

枫吹过的柚
166 阅读5分钟

深入理解MySQL底层数据结构和算法

数据结构: www.cs.usfca.edu/~galles/vis…

索引

  • 如果没有任何索引，MySQL会从数据表的第一行开始查找记录，数据表都是存储在磁盘上面的，插入的记录可能是不规整的，我们从磁盘上拿取记录，需要做IO操作，但是IO操作的性能并不是很高的，我们需要将IO交互频率减少来提升效率。

  • 问题: 什么是索引

    • 索引是存储引擎用于提高数据库表访问速度的一种数据结构

  • 问题: 索引的优缺点

    • 优点

      • 加快数据查找的速度
      • 为排好序或者分组的字段添加索引，可以加快分组和排序的速度
      • 加快表与表之间的连接

    • 缺点

      • 建立索引需要占用物理空间
      • 会降低表的增删改的效率，因为每次对表记录进行增删改的时候，都需要对索引进行动态维护，这样消耗的时间也会相对较长

  • 问题: 索引的作用

    • 数据是存储在磁盘上的，查询数据时，如果没有索引，会加载所有的数据到内存，依次进行检索，读取磁盘次数较多
    • 有了索引就不需要加载所有数据，因为B+Tree的深度一般在2至4层，最多读取2至4次磁盘，查询效率就大大提升了

  • 问题: 什么情况下需要建索引

    • 经常用于查询的字段
    • 经常用于连接的字段(比如外键)建立索引，可以加快连接的速度
    • 经常需要排序的字段建立索引，因为索引已经排行序，可以加快查询速度

  • 问题: 什么情况下不需要建立索引

    • where条件中用不到的字段不适合建立索引
    • 表记录较少
    • 需要经常增删改
    • 参与列计算不适合建索引
    • 区分度不高的字段不适合建立索引，比如性别，年龄等

常见的索引数据结构

二叉树

  • 每个节点最多拥有两个子节点
  • 左子树的键值小于根的键值，右子树的键值大于根的键值

image.png

红黑树

  • 节点是红色或黑色
  • 根节点是黑节点
  • 叶子节点都是黑节点
  • 红节点的子节点一定是黑节点
  • 节点到达叶子节点相同路径包含相同数量的黑节点

image.png

Hash表

  • 对索引的key进行一次hash计算就可以定位出数据存储的位置
  • 很多时候hash索引要比B+树索引要更高效
  • 只能满足=、in等值查询，不支持范围查询
  • 会出现hash冲突问题
  • 问题：字符串要按照ascll码排序，且占用空间比整型大

image.png

B-Tree

  • 叶子节点具有相同的深度，叶子节点的指针为空
  • 所有索引元素不重复
  • 节点中的数据索引从左到右递增排列

image.png

B+Tree

  • 叶子节点之间具有指针连接，提升区间访问效率

  • 非叶子节点不存储数据只存储索引并且会冗余部分叶子节点来构建整个数据结构，可以存放更多索引

    • 注: 如果按照B-Tree的方式，倘若要放满2000w行记录，16^n = 2000w，而n远远大于B+Tree的高度，将data移动到叶子节点，主要是想让横向存储更多的索引，这样可以降低树的高度，提升查询性能

  • 叶子节点包含所有索引字段

  • 节点是从左到右递增排列

image.png

  • 索引数据计算公式

    • 聚集索引

      • 16kb/页

        • 非叶子节点

          • 按照常规的bigint占用8b、 InnoDB的索引指针占用6b，一页占用16kb，每一页拥有 16kb / 14b = 1170个索引指针

            • # 查看MySQL每页大小
SHOW GLOBAL STATUS LIKE 'Innodb_page_size';

        • 叶子节点

          • 除了存储索引，还存储数据
          • 按照常规索引加数据占用1kb，也就是叶子节点可以存放16条记录

        • 整个B+Tree深度为3，只要经历过3次磁盘IO可以找到需要的元素，而索引也会有 1170 * 1170 * 16 = 21902400个

        • 问题: B+树一个非叶子节点能放多少指针，一个B+树可能放多少数据

          • 一个非叶子节点存放 16kb / 14b = 1170个索引指针
          • 一个B+可能放1170 * 1170 * 16 = 21902400条数据

    • 非聚集索引

      • 叶子节点存储的是索引+主键ID
      • 除了叶子节点可以相对聚集索引能存储更多的索引，其他一样计算

  • 问题: 超大字段(如long)建什么索引

    • FULLTEXT

聚簇索引/非聚簇索引

  • 注: 存储引擎是用来形容数据库表的

InnoDB索引实现(聚簇索引)

  • 叶子节点包含完整的数据记录(索引和数据一起存储)

MylSAM索引实现(非聚簇索引)

叶子节点包含磁盘地址(索引跟数据分开存储)

  • 索引文件中对于叶子节点存放的是索引和磁盘地址，会先从索引文件中找到索引以及磁盘地址，然后再从数据文件中通过磁盘地址找到对应的数据

    • 注: .frm主要是存放表结构 .MYD主要是存放表记录 .MYI主要是存放表索引 .ibd主要是存放以B+Tree方式存储索引+磁盘地址

非聚簇索引.png

二级索引(非聚簇索引)

二级索引也是非聚簇索引(稀疏索引)，查数据的时候也是要做回表操作

二级索引.png

联合索引

联合索引根据最左前缀原则，会根据索引的先后顺序去比较

联合索引.png

avatar
文章
阅读
粉丝