「MySQL」存储结构与索引

• MySQL分层

  1. 接入层 -> MySQL服务层 -> 存储引擎层 -> 系统文件层

  2. 接入层: 不同语言客户端通过MySQL协议与MySQL服务器连接通信, 该层负责权限验证, 连接池管理

  3. MySQL服务层: SQL解析器, SQL优化器, 缓存

  4. 存储引擎层, mysql服务器中对数据的读取和写入是交给存储引擎来处理的

  5. 系统文件层: 保存数据, 索引, 日志

• innodb引擎的存储结构

  1. 记录

     1. 行格式: 每条记录存储的格式称为行格式, 一般格式为: "可变字段长度列表,null值列表,记录的头信息,每列的具体数据".

     2. 头信息

        1. delete_mask位用来标记该记录是否被删除

        2. record_type位表示记录类型 (0为普通记录, 1为目录项记录, 2为最小记录, 3为最大记录)

        3. next_record位指向下一条记录

     3. 删除记录: 改变该记录上家的next_record, 并把该记录中头信息的delete_mask标记为1即可

     4. 行溢出: 当记录中数据太多时, 会将数据存到其他页中

  2. 页

     1. 概述: 数据以页为单位, 作为内存和磁盘间数据交换的单位, 页的大小为16KB, 一个页中至少存储两条记录

     2. 分类

        1. 页可以分为数据页和目录项页, 数据页存储数据, 目录项页存储页编号

        2. 目录项页和数据页的结构一致, 但只有两列: "主键值, 页号". 记录的头信息中next_record指向顺序中的下一个页. 数据页中记录的头信息中每行record_type都是0, 目录项页中记录的头信息中每行record_type都是1

     3. 结构

        1. 页中

           1. 记录在页中是按照主键由小到大顺序存为单链表, 被删记录也被连成一条链表

           2. 页会被分为多个组, 每组中拥有1~8条记录, 每组的最后一个记录的地址偏移量被称为槽, 存放在该页的"页目录"中

           3. 在页中查找记录: 先通过二分法搜索"页目录"确定记录附近的槽, 在通过遍历槽所对应的组定位记录

        2. 页之间

           1. 页之间是按主键由小到大顺序组成的双向链表. 为方便查找, 也需为页建立目录, 该目录也会被存入上层页中. 当该上层页存满目录后会发生"页分裂", 因此还需要更高层的页来记录这两个目录页的位置, 以上这些页组成了一棵树, 叫B+树

           2. 页分裂: 插入数据, 如果超出页的大小, 就会发生移动, 大的往后移. 插入时, 主键有序比无序效率高很多, 原因就是可以避免大量页分裂造成的移动

           3. 在页之间查找记录: 目录项页中当然也有页目录, 查找也是二分组号+一组遍历, 最终找到对应的页号, 再在下层新页中不断重复

• MyISAM引擎的存储结构

  1. MyISAM数据存储到数据文件中, 每条记录都对应着一个行号

  2. 建立索引的时候, 叶子节点页中行存的是"主键值+行号", 所以其不像innodb可以马上拿到记录, 其需要去数据文件中读取对应行才能拿到, 这个操作叫"回表"

  3. 因此MyISAM都是二级索引, 没有聚簇索引. 优点是当其使用非主键列查时, 因为存储的是行号, 相比innodb在相同场景下会更快. 所以Innodb的索引即是数据, MyISAM则是索引和数据明确分开

• 索引概述

  1. mysql索引分为聚簇索引, 二级索引和联合索引

  2. 可以看出innodb建立索引就是建立B+树, 树的叶子节点存数据, 非叶子节点记录的都是目录项

  3. 查询时尽量使用索引覆盖, 避免使用*, 因为会增加回表

• 聚簇索引

  1. 树的叶子节点就是全部的数据, 这样的索引就是聚簇索引

  2. innodb优先采用用户定义的主键作为主键, 未设置则使用表中unique键作为主键, 如果也没有, 则用row_id隐藏列作为主键

• 二级索引

  我们也可以自己对目标列建立索引, 生成B+树, 大致结构不变, 但有以下几点不一样:

  1. 目录项页中每行记录的具体列名不再是"主键+页号", 而是变为"目标列+主键+页号"

  2. 页中, 页间也不是用主键值从小到大排序了, 而是对目标列从小到大排序, 当目标列相同时, 自动按主键从小到大排序. 查找时也是和上面一样, 当目标列有多个值相等时, 就会看主键

  3. 叶子节点存储的是"目标列+主键", 故查完只能找到主键值. 要获取那行的真正记录, 还需要拿主键值到聚簇索引所对应的B+树里去查找, 这个操作叫"回表". 为啥不放数据呢? 省内存

• 联合索引

  1. 和二级索引一样的, 只是页中每行的具体列名变成了"列1 + 列2 + .. + 主键 + 页号", 排序是也先按照列1, 列2, 列3...

  2. 使用联合索引的好处: 减少开销(避免多条索引), 索引覆盖

• B+树的索引适用于

  1. 全值匹配

  2. 匹配最左边的列

  3. 匹配范围值

  4. 精确匹配左边列, 并范围匹配索引剩下列. 遇到范围匹配(>, <, between, like)就会停止使用索引

  5. 排序/分组时也遵守上述规则

  6. 例子

     1. a, b, c联合索引, c, a查询时也是可以用到a的索引

     2. a, b, c联合索引, a=10, b>10, c=10, 可以用到ab索引, 为什么不能用到c的索引? b是范围值, 这个范围内c是无序的, 不能使用索引, 只能对c对全量扫描

• 建立索引的原则

  1. 只为用于搜索, 排序, 分组条件的列创建索引

  2. 为"列基数大"的列创建索引, "列基数"指表中该列的不同值数量

  3. 可以只对字符串列的前缀建立索引

  4. 主键尽量用自增的, 避免插入数据时发生页分裂