浅谈MySQL索引

InnoDB 页

InnoDB 是一个将表中的数据存储到磁盘上的存储引擎，即使我们关闭并重启数据库，数据还是存在。而真正处理数据的过程发生在内存中，所以需要把磁盘中的数据加载到内存中。如果是处理写入或修改请求，还需要把内存中的内容刷新到磁盘上，而我们知道读写磁盘的速度非常慢，与读写内存差了几个数量级，当我们想从表中获取默写记录时，InnoDB 存储引擎需要一条一条地把记录从磁盘上读出来么？不，那样会慢死，InnoDB 采取的方式是，将数据划分为若干个页，以页作为磁盘和内存之间交互的基本单位。InnoDB 中页的大小一般为16KB。也就是在一般情况下，一次最少从磁盘中读取16KB的内容到内存中，一次最少把内存中的16K内容刷新到磁盘中。
系统变量 innodb_page_size 表明了InnoDB存储引擎中的页大小，默认值是16384K，该变量只能是在第一次初始化MySql数据目录时指定，之后再也不能更改了。

InnoDB 行格式

我们平时都是以记录为单位向表中插入数据的，这些记录在磁盘上的存放形式称为行格式或者记录格式。目前有4种行记录：Compact、Redundant、Dynamic、Compressed。

Compact存储格式：

Redundant存储格式

Dynamic、Compressed存储格式：

这两种行格式类似于COMPACT行格式，只不过在处理溢出列数据时有点分歧：它们不会再记录的真实数据处存储列真实数据的钱768字节，而是把所有的数据都存储到所谓的溢出页中，只在记录的真实数据处存储指向这些溢出页的地址。另外Compressed行格式会采用压缩算法对页面进行压缩。

  • 真实数据还包括一些隐藏列：
    • DB_ROW_ID：行ID，唯一标识一条记录；
    • DB_TRX_ID：事务ID；
    • DB_ROLL_PTR：回滚指针；
  • InnoDB的主键生成策略：优先使用用户自定义的主键作为主键，如果没有自定义主键，则选取一个不允许存储NULL值得UNIQUE做为主键；如果表中连不为空的UNIQUE的字段都没有定义，则InnoDB默认添加一个名为 DB_ROW_ID 的隐藏列作为主键；

数据页格式


  • 存储的记录会按照指定的行格式（如COMPACT 存储到User Record部分 一开始生成页 User Record部分的空间是通过从Free Space部分生申请一个记录大小的空间生成的 当FreeSpace用完了后 如果还有新的记录插入 需要申请新的页

  • record_type:
    • 0是普通记录（数据项记录）
    • 1是B+树非叶结点的目录项记录
    • 2是Infimum记录
    • 3是Supremum记录
  • next_record:
    • 表示从当前记录的真实数据到下一条记录的真实数据之间的距离；

Page Directory 页目录

  • 将所有正常的目录（包括Infimum和Supremum记录 不包括垃圾链表中已被删除的记录 划分为几个组
  • 每个组的最后一条记录（组内最大的那条记录）的头信息中的n_owned属性表示该组内共有几条记录
  • 将每个组中最后一条记录在页面中的地址偏移量（即该记录的真实数据与页面中第0个字节之间的距离）单独提取出来 按顺序存储到靠近页面尾部的地方 这个地方就称为页目录 页目录存储的这些地址偏移量称为槽 每个槽占用2字节 1个组对应1个槽
  • 初始情况下 一个数据页中只有Infimum和Supremum记录 Infimum组只能存储这一条记录 而 Supremum组能存储1-8条记录 剩下分组记录的条数范围在4-8之间
  • 插入记录是按照主键从小到大的顺序依次插入的
  • 在一个数据页中查找指定主键值的记录
    • 通过二分法确定该记录所在分组对应的槽 找到该槽所在分组中主键最小的那条记录
    • 通过记录的next_record属性遍历该槽所在的组中的各个记录

B+树索引

mysql中如果没有索引如何查找数据

1、方式一：在一个页中查找
比如数据较少，都存储在一个页中。在查找记录时，是要根据搜索条件的不同分为两种情况的。
第一种情况：以主键列作为搜索条件
（1）、在页目录（Page Directory）中使用二分法快速定位到对应的槽。
（2）、然后再遍历该槽对应分组中的记录。
第二种情况：以其他列作为搜索条件
（1）、因为在数据页中，没有为非主键建立Page Directory。只能从Infimum记录开始依次遍历单向链表中的每条记录，然后对比每条记录是否符合搜索条件。
2、方式二：在多个页中查找
大多情况，数据量都是很多的，那么就会涉及到多个页中的查找
由于没有索引，我们无法定位到记录所在的页，所以只能从第1页开始，利用【在一个页中查找】的方式，遍历所有的数据页来查找，非常耗时。

索引是什么

索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内容。
索引分类：
聚簇索引：根据主键构建的索引叫做聚簇索引。
非聚簇索引：不是根据主键构建的索引叫做非聚集索引或者二级索引或者辅助索引。

聚簇索引

使用记录主键值的大小进行记录和页的排序，这包括3方面的含义：

  • 页（包含叶子节点和内节点）内的记录按照主键的大小排成一个单向链表，页内的记录被划分成若干个组，每个组中的主键值最大的记录在页内的偏移量会被当做槽依次存放在页目录中，可以在页目录中通过二分查找快速定位到主键列等于莫个值得记录；
  • 各个存放用户记录的页也是根据页中用户记录得主键大小顺序排成一个双向链表；
  • 存放目录记录得页分为不同得层级，在同一层级中得页也是根据页中目录项记录的主键大小排序成一个双向链表；
  • B+树的叶子节点存储的是完整的用户记录，包含了所有列+隐藏列；
  • 聚簇索引并不需要我们在MySql语句中显示地使用INDEX语句去创建，InnoDB引擎会自动为我们创建聚簇索引，在InnoDB存储引擎中，聚簇索引就是数据地存储方式（所有的用户记录都存储在叶子节点），也就是所谓的”索引即是数据，数据即使索引“；
数据量计算：
假设一张数据页可以存储100条用户记录或者1000条目录记录：
1 层B+树：100
2 层B+树：1000 * 100
3 层B+树：1000 * 1000 * 100
4 层B+树：1000 * 1000 * 1000* 100

二级索引（辅助索引）

  • B+树的叶子节点存储的不是完整的用户记录，而是索引列+主键数据；
  • 目录项记录中不再是主键+页号的搭配，而是使用索引列+页号+（主键）；
    • 即使申明了不为空的唯一二级索引，目录项记录也还是会保存主键值，MVCC服务会使用到该主键信息；
  • 通过二级索引的叶子节点的主键信息到聚簇索引中查找完整的用户记录时，我们称这个动作为回表；

联合索引

联合索引其实也是一个二级索引，只不过索引列有多个列组成，例如：以c1、c2列建立索引，则排序方式为：

  • 先把各个记录和页按照c1进行排序；
  • 在记录的c1列相同的情况下，再采用c2列进行排序；

posted on 2023-04-24 10:41  bigstrong_code  阅读(17)  评论(0)  编辑  收藏  举报

导航