《MySQL是怎样运行的》笔记（三）

pochilal
2,051 阅读7分钟

对小册《MySQL是怎样运行的》知识点的一些摘录。

页内记录插入

1. 空间分配

  • 分配：每插入一条记录都会从页的Free Space申请一个记录大小的空间划分到User Records，当Free Space用完后新的记录插入就需要去申请新页了

  • 重用：被删除掉的记录只会用记录头中的delete_mask标记一下，然后维护一个垃圾链表，新记录插入到表中可能会重用这些空间

2. 按主键有序插入

  • InnoDB建表时主键的生成策略：优先使用用户自定义主键作为主键，如果没有定义则选取一个Unique键作为主键，如果仍没有定义则为表默认添加一个名为row_id的隐藏列作为主键

  • 记录按照主键从小到大的顺序在页内形成了一个单链表：下一条记录的相对位置存储在记录头的next_record中，Infimum指向主键值最小的用户记录，主键值最大的用户记录指向Supremum

  • 页内通过对Page Directory的二分查找，找到Slot指向分组的最大记录偏移量，再顺着链表向下比较主键确定插入位置

    扩展：innodb页对于记录插入方向的优化如何实现的,有何意义？ - 陈广胜的回答 - 知乎

3. 页分裂

  • 当页满了再插入新的记录的时候，会新分配一个页存储新插入的记录

  • 新个页和下一个页的编号而建立了链表关系，在存储空间里可能并不挨着

  • 如果新纪录的主键值小于上一页的最大主键值，需要进行一次记录移动：

    • 把上一页最大主键值的记录移动到新页中

    • 把新插入的纪录移动到上一页中

聚簇索引

为数据页建立目录项

  • 这些16KB的页在物理存储上可能并不挨着，需要给它们做个目录，每个目录项包括页内最小主键值与页号两部分

  • 目录项其实长得跟用户记录差不多，只不过目录项中的两个列是主键和页号，并通过记录头中的record_type来区分

  • 目录项与用户记录用的是一样的数据页，页的组成结构也是一样的

  • 目录项变多后需要生成一个更高级别的目录，这种组织数据的形式就是B+树

B+树

  • 实际用户记录其实都存放在B+树的最底层的节点上，这些节点也被称为叶节点，最低层叫做第0层

  • 用来存放目录项的节点称为非叶节点或者内节点，其中B+树最上边的那个节点也称为根节点

  • 假设叶节点代表的数据页可以存放 MM 条用户记录，内节点代表的数据页可以存放 NN 条目录项，那么 nn 层的B+树就能存放 M∗Nn−1M*N^{n-1} 条记录

  • 一般情况下B+树都不会超过4层，所以通过主键值去查找某条记录最多只需要做4个页面内的查找

聚簇索引

  • 使用记录主键值的大小进行记录和页的排序：

    • 页内的记录是按照主键的大小顺序排成一个单向链表

    • 各个存放用户记录的页也是根据页中用户记录的主键大小顺序排成一个双向链表

    • 存放目录项记录的页分为不同的层次，在同一层次中的页也是根据页中目录项记录的主键大小顺序排成一个双向链表

  • B+树的叶节点存储的是完整的用户记录

  • 具有上述两种特性的B+树称为聚簇索引

  • 在InnoDB中聚簇索引就是数据的存储方式（所有的用户记录都存储在了叶节点），也就是所谓的索引即数据，数据即索引（MyISAM中却是索引是索引、数据是数据）

二级索引

索引的创建

  • 二级索引会用索引列的大小作为数据页、页中记录的排序规则，再建一棵B+树

  • B+树的叶节点存储的并不是完整的用户记录，而只是索引列+主键这两个列的值

  • 目录项记录中不再是主键+页号的搭配，而变成了索引列+主键+页号的搭配

有索引的记录插入

  • 每创建一个B+树索引，都会为这个索引创建一个根节点页

  • 随后向表中插入用户记录时，先把用户记录存储到这个根节点中

  • 当根节点中的可用空间用完时继续插入记录：

    • 将根节点中的所有记录复制到一个新分配的页，比如页a中

    • 对这个新页进行页分裂的操作，得到另一个新页，比如页b

    • 这时新插入的记录根据键值的大小就会被分配到页a或者页b中

    • 根节点升级为存储目录项记录的页

  • B+树索引的根节存储在固定的地方，每次用到的时候都从这个固定位置访问这个索引

索引的代价

  • 空间上，一个数据页会默认占用16KB的存储空间，每建立一个索引生成的B+树会占用相当大一部分空间

  • 时间上，索引越多意味着增删改记录时需要进行的维护B+树操作（记录移位，页面分裂、页面回收等）就越多，增删改的性能就越差

回表

  • 根据二级索引找到的叶节点只能确定要查找记录的主键值，需要到聚簇索引中再查一遍从而得到完整的用户记录

  • 这种按照非主键列建立的B+树需要一次回表操作才可以定位到完整的用户记录，所以这种B+树被称为二级索引（英文名secondary index），或者辅助索引

  • 在对索引进行范围查询时，如果不需要回表的话只需要使用顺序I/O，如果需要回表的话还需要使用随机I/O访问聚簇索引

  • 有时候二级索引 + 回表的方式还不如全表扫描（直接遍历聚簇索引然后再进行文件排序），查询优化器会事先计算需要回表的记录数，如果需要回表的记录数越多，就越倾向于使用全表扫描

  • 为了避免回表带来的性能损耗，建议在查询列表里只包含索引列

利用索引进行排序

  • 在MySQL中，把在内存中或者磁盘上进行排序的方式统称为文件排序（filesort），文件排序的速度非常慢

  • ORDER BY子句里使用索引列的话会省去文件排序的步骤

  • ORDER BY子句里使用联合索引进行排序要求各个列都是ASC或者都是DESC规则排序，不能ASC与DESC混用

  • ORDER BY子句里使用不在一个索引中的多个列也不能使用索引进行排序

如何挑选索引

  • 只为用于搜索、排序或分组的列创建索引，也就是只为出现在WHERE子句中的列、连接子句中的连接列，或者出现在ORDER BY或GROUP BY子句中的列创建索引

  • 最好为那些列的基数（去重计数）大的列建立索引，为基数太小列的建立索引效果可能无法有效利用

  • 主键与索引列的类型尽量小：

    • 数据类型越小，在查询时进行的比较操作越快

    • 数据类型越小，索引占用的存储空间就越少，可以把更多的数据页缓存在内存中

  • 索引字符串值的前缀，也就是说在二级索引的记录中只保留字符串前几个字符，但是这种方式无法使用索引排序

  • 让存储引擎自己为表生成主键并AUTO_INCREMENT，可以避免插入数据时聚簇索引的页面分裂和记录移位

avatar
文章
阅读
粉丝