跳至主要內容

13.数据库存储引擎

12a2024/10/3大约 21 分钟数据库后端sql

13.数据库存储引擎

  • 存储引擎代表的是表的类型，最早时被叫做表处理器，它的功能就是接收上层传来的指令，然后对表进行数据提取或写入操作

13.1 InnoDB

  • InnoDB引擎：是自MySQL5.6开始引入的引擎，是之后MySQL的默认引擎

  • MySQL 5.7版本存储的文件包括：

    • 数据库相关信息文件db.opt：包含字符集等库全局信息
    • 对应表的数据文件ibd：表中数据默认存放在这里(独立表空间)，可以通过innodb_file_per_table=0切换为系统表空间

    ibdata1是系统表空间，默认为12M，会自动扩展，在MySQL 5.5.7 ~ 5.6.6，数据默认统一存放在这里

    • 对应表的结构文件frm

  • MySQL 8.0版本存储的文件包括对应表的数据和结构文件.ibd，.frm的内容合并到此文件中。.opt文件也已经移除，相关信息存储在mysql数据库中

    ibd文件信息可以通过idb2sdi --dump-file=输出结果文件 idb_file解析，此工具默认集成到MySQL中

    视图在MySQL的存储

    在MySQL5.7中，视图的结构文件都存储在.frm文件中，在MySQL8.0中，视图没有单独的文件存储，记录在对应的表中

  • 引擎特点：

    • 具备外键功能的事务存储引擎
    • 是MySQL的默认事务型引擎，被设计用来处理大量的短期事务，可以保证数据的提交和回滚
    • 如果需要更新或删除数据，应该优先考虑InnoDB引擎，效率更高
    • 支持行级锁，适合高并发场景
    • 除非有什么特别的原因，应该优先考虑使用InnoDB引擎
    • InnoDB相比MyISAM写效率低一些，并且需要更多的磁盘空间存储数据和索引
    • InnoDB不仅需要缓存索引，还需要缓存真实数据，对内存要求较高
    对比InnoDBMyISAM
    外键支持不支持
    事务支持不支持
    锁行级锁表级锁
    缓存缓存索引和真实数据缓存索引
    关注点并发写，事务，需要更多资源节约资源，消耗少，适合简单业务

13.1.1 索引方式

  • InnoDB使用B+树进行索引，通过对数据进行二次索引，提高查询效率

    • 数据页：为了能够分段查找，每个数据页中的数据行都是有序的，数据页存储固定的某个范围的数据，每次查找只需要查找对应的数据页，不需要完全查找
      • 数据页中的数据以单向链表的形式连接，数据页之间以双向链表的形式连接
    • 目录页：为了能够快速索引数据页，使用目录页存储数据页的信息，快速获得对应的数据页
    • 目录页的目录页：用于目录页可能不止一个，因此为目录页创建目录的目录页，目录的目录页存储多个目录页的信息，快速获得对应的目录页

    同级页之间使用双向链表连接，InnoDB默认会对主键创建聚簇索引

    为什么只设置最多3层B+树索引

    树的高度越低，每次查询的IO次数越少，性能越好；而且三层的B+树已经能够满足需求，假设一个数据页中最多存储100条数据，目录页最多存储1000条数据，那么三层B+树可以索引1亿条数据

  • InnoDB中B+树索引特点：

    • 根页面位置不动：索引刚开始时只有一层，数据行都存储在根页；之后数据增加，根页面作为目录页，存储数据页信息；数据再增加，根页面作为目录页的目录页
    • 目录项中的数据是唯一的：
      • 聚簇索引使用唯一的主键，可以保证是唯一的
      • 对于二级索引，可能有重复值，因此需要将主键添加到二级索引的目录项中
      • 一个页面最少可以存储两条数据

    自适应的HASH索引

    在InnoDB中，虽然本身不支持HASH索引，但提供自适应的HASH索引，如果某个数据经常被访问，当满足一定条件时，会将这个数据页地址存放到HASH表中，下次查找时，可以直接找到这个数据页的位置

    -- 查看是否开启自适应的HASH索引
SHOW variables LIKE '%_adaptive_hash_index';

  • B+树索引过程：如果通过B+树的索引查询行记录，首先是从B+树的根开始，逐层检索，直到找到叶子节点，也就是找到对应的数据页为止，将数据页加载到内存中，页目录中的槽(slot)采用二分查找的方式先找到一个粗略的记录分组，然后再在分组中通过链表遍历的方式查找记录

    页目录槽见数据存储结构部分

  • R树索引：InnDB支持R树索引。这种索引用于存储高维数据，比如经纬度，解决高维空间的搜索问题
    • 如果使用B+树解决这类问题需要遍历所有的数据并对每个数据单独判断
    • R树将B+树的分区思想扩展到高维空间，在添加和删除数据时合并分解结点，适合用于范围查找

13.1.2 数据行格式

  • 我们平时的数据以行为单位来向表中插入数据，这些记录在磁盘上的存放方式也被称为行格式或者记录格式。InnoDB存储引擎设计了4种不同类型的行格式，分别是Compact、Redundant、Dynamic和Compressed行格式

    -- 查看默认行格式，默认是 dynamic
SELECT @@innodb_default_row_format;

-- 查看表的行格式，创建时可以通过ROW_FORMAT=指定
SHOW TABLE STATUS like 'tb_name'\G

  • COMPACT行格式：紧凑的行格式，尽可能紧凑存储行数据，不需要的数据将不显示，是MySQL 5.1开始的默认行格式

    • 变长字段列表：存储变长字段(VARCHAR，TEXT等)的实际长度，快速确定每个变长字段的起始位置和长度
      • 变长字段列表第一个值对应最后一个变长字段的长度，也就是顺序正好相反
      • 如果值为NULL将省去对应的变长字段长度
      • 每个字段需要2个字节记录这个长度
    • NULL值列表：存储NULL值字段的标记，确定哪些字段是NULL
      • 标识和变长字段列表一样是反序的，其中的1表示字段为NULL
      • 已经明确限制是非空的列将被跳过标识，节省对应的1个bit位；如果没有允许存储为NULL的列，不存在这个列表
    • 隐藏列：
      • row_id：隐藏行ID，也称为DB_ROW_ID，该列占6字节，仅当没有唯一的列作为聚簇索引的键，系统才会创建DB_ROW_ID，作为聚簇索引的键
      • 事务ID列(transaction_id)：也称为DB_TRX_ID，该列占6字节，用于存储最后对该行进行插入或修改的事务ID。它在事务处理中起到关键作用，帮助实现事务的隔离性和一致性，具体见事务部分
      • 回滚标识列(roll_pointer)：也称为DB_ROLL_PTR，该列占7字节，用于存储最后对该行进行插入或修改的事务的undo信息。它与事务ID列一起，用于实现事务的回滚和恢复
    • 数据列：包括变长列和非变长列的实际数据
    用户记录行格式
    用户记录行格式

  • REDUNDANT行格式：是早期MySQL的默认行格式，它与COMPACT行格式的区别在于对每个字段起始位置的描述，REDUNDANT行格式使用字段偏移列表，不使用变长字段列表和NULL值列表

    • 字段偏移列表：数据和变长字段列表相同也是逆序放置的，会将数据行的所有列的偏移量信息都以逆序的形式存储，包括隐藏列，这意味着它还记录了非变长字段的偏移，同时偏移不如单字段长度直观
    • 页头的不同：Redundant行格式多了n_field和1byte_offs_flag这两个属性，少了record_type属性
      • n_fields代表一行中列的数量，占用10位，这也很好地解释了为什么MySQL一个行支持最多的列为1023
      • 1byte_offs_flags定义了偏移列表占用1个字节还是2个字节。当它的值为1时，表明使用1个字节存储。当它的值为0时，表明使用2个字节存储。当最大偏移量少于128时，使用1个字节存储，否则使用2个字节存储(2个字节存储不下的数据实际上已经发生了行溢出)
    • 对NULL的存储处理也与COMPACT行格式有所不同

    一般已经不使用REDUNDANT行格式，保留这个行格式是为了兼容早期的数据库

  • DYNAMIC行格式：是MySQL 5.7和8的默认行格式，和Compact行格式的区别在于对行溢出的处理

  • COMPRESSED行格式：和DYNAMIC行格式一样，但是数据的处理方式不同，COMPRESSED行格式会压缩存储的数据，从而减少存储空间

    行溢出

    存储的数据长度可能过大，一列的数据长度超过一页，这时一页就存放不了一条记录，这种现象就叫行溢出

    • 行溢出的处理方式：
      • COMPACT和REDUNDANT行格式：对于这种占用空间很大的列，真实数据页只存放一部分数据(前缀768字节)，把剩余的数据保存在其他几个拓展数据页中进行分页存储，在这里还存储指向这些页的地址(20字节)
      • COMPRESSED和DYNAMIC行格式：对于这种占用空间很大的列，真实数据页只存放溢出页地址(20字节)，把所有的数据保存在其他几个拓展数据页中进行分页存储

13.1.3 数据页存储结构

  • 如索引方式中描述的一样，InnoDB将数据划分为若干个页

    • 页是磁盘和内存交互的基本单位，一次查询和修改最少会读取一个页
    • 每个页的默认大小为16KB，可以通过show variables like 'innodb_page_size';查看
    • 页可以不在物理结构上关联，通过双向链表连接，每个数据页都会对应一个页目录信息，可以通过二分法快速查找数据页

    数据库中页(块)的概念在许多数据库中都有，但默认大小可能不同

  • 数据页的内部结构在磁盘中依次为：

    名称占用大小说明
    File Header38字节文件头，描述页的信息
    Page Header56字节页头，页的状态信息
    Infimum+Supremum26字节最大和最小记录，这是两个虚拟的行记录
    User Records不确定用户记录，存储行记录内容
    Free Space不确定空闲部分，页中还没有被使用的空间
    Page Directory不确定页目录，存储用户记录的相对位置
    File Trailer8字节文件尾，校验页是否完整

  • 可以根据用途将存储的数据分为四个部分：

    • 文件通用部分：文件头和文件尾
    • 记录部分：最大和最小记录、用户记录
    • 页部分：页头、页目录
文件通用部分

  • 文件通用部分：包括文件头和文件尾，所有的页都以文件头开头，描述了针对各种页通用的信息，如页类型、页状态、前后页位置等

  • 文件头：

    名称占用空间大小描述
    FIL_PAGE_SPACE_OR_CHKSUM4字节页的校验和，应该与页尾校验和一致，用于检测页数据的完整性
    FIL_PAGE_OFFSET4字节页号，标识该页在表空间中的位置
    FIL_PAGE_PREV4字节上一个页的页号，用于链接前一页，形成链表结构
    FIL_PAGE_NEXT4字节下一个页的页号，用于链接后一页，形成链表结构
    FIL_PAGE_LSN8字节页面被最后修改时对应的日志序列位置(LogSequenceNumber)，应该和文件尾相同，用于恢复机制
    FIL_PAGE_TYPE2字节该页的类型，例如数据页、索引页等
    FIL_PAGE_FILE_FLUSH_LSN8字节仅在系统表空间的一个页中定义，代表文件至少被刷新到了对应的LSN值
    FIL_PAGE_ARCH_LOG_NO_OR_SPACE_ID4字节页所属的表空间ID，标识该页属于哪个表空间

  • 文件尾：前4个字节为校验和，后4个字节为页面被最后修改时对应的日志序列位置，如果和文件头的信息不同，说明页未同步

13.2 MyISAM

  • MyISAM引擎：是MySQL主要的非事务存储引擎，数据和索引分开存储
    • 索引文件.MYI
    • 数据文件.MYD
    • 结构文件.sdi：在MySQL5.7中是.FRM文件
  • 引擎特点：
    • 支持全文索引，压缩等功能，但不支持事务、行级锁、外键，不支持事务会导致崩溃后无法安全恢复
    • 在以写和读为主的场景访问速度快
    • 针对数据有额外的统计，count(*)查询效率高

    适合只读或以读为主的应用，系统自带表都使用MyISAM引擎

13.2.1 索引方式

  • 在MyISAM中，索引数据结构与InnoDB一样，默认使用B+树进行索引，也支持R树索引，但MyISAM中索引和数据分离，索引文件中保存数据记录的地址，也就是没有聚簇索引，全是二级索引
  • MyISAM和InnoDB的索引方式对比：
    • MyISAM中建立的索引都是二级索引，都需要回表操作；InnoDB中根据主键建立的聚簇索引，不需要回表操作
    • MyISAM中索引和数据文件是分离的；InnoDB数据文件本身即索引文件
    • MyISAM中二级索引记录的是数据记录的地址，回表更快；InnoDB中，二级索引记录的是数据相应主键
    • InnoDB的表存储方式要求表必须有主键，进行聚簇索引；MyISAM可以没有，但为了查询方便一般会根据某些列构建索引

13.3 Achive

  • Archive引擎：Archive即归档，此仅仅支持插入和查询两种功能，并在MySQL5.5后支持索引
    • 有很好的压缩功能，使用zlib压缩库，在记录请求的时候实时的进行压缩，经常被用来作为仓库使用

      根据对英文的测试结论来看，同样数据量下，Archive表比MyISAM表要小大约75%，比InnoDB表小大约83%

    • 创建表时，存储引擎会创建名称以表名开头的.ARZ文件
    • Archive引擎采用了行级锁。支持AUTO_INCREMENT列属性。AUTO_INCREMENT列可以具有唯一索引或非唯一索引。尝试在任何其他列上创建索引会导致错误。
    • Archive引擎适合日志和数据采集（档案）类应用；适合存储大量的独立的作为历史记录的数据。拥有很高的插入速度，但是对查询的支持较差

13.4 CSV

  • CSV引擎存储时，会以逗号分隔各个数据项，也就是.csv格式的数据表，同时会创建一个元文件.csm存储该表的元信息
    • CSV引擎可以将普通的CSV文件作为MySQL数据表来处理，但不支持索引，也不支持数据行含有空列
    • CSV引擎可以作为一种数据交换的机制，对于数据的快速导入、导出是有明显优势的
    • CSV存储的数据直接可以在操作系统里使用Excel或文本编辑器进行操作

13.5 Memory

  • Memory引擎把数据存储在内存中(表结构还在磁盘.frm文件中)，在mysql崩溃后数据会丢失
    • 要求数据存储的个数是长度不可变的格式
    • Memory同时支持哈希索引和B+树索引。
      • 哈希索引相等的比较快，但是对于范围的比较慢很多
      • 默认使用哈希(HASH)索引，其速度要比使用B型树(BTREE)索引快
      • 如果希望使用B树索引，可以在创建索引时选择使用
    • Memory表至少比MyISAM表要快一个数量级
    • Memory表的大小是受到限制的。表的大小主要取决于两个参数，分别是max_rows和max_heap_table_size。其中，max_rows可以在创建表时指定；max_heap_table_size的大小默认为16MB，可以按需要进行扩大
  • 使用Memory存储引擎的场景：
    • 目标数据比较小，而且非常频繁的进行访问，在内存中存放数据，如果太大的数据会造成内存溢出

      可以通过参数max_heap_table_size控制Memory表的大小，限制Memory表的最大的大小

    • 如果数据是临时的，而且必须立即可用得到，那么就可以放在内存中
    • 存储在Memory表中的数据如果突然间丢失的话也没有太大的关系

13.5.1 索引方式

  • 默认使用Hash索引，也就是通过HASH结构存储，支持B+树索引，Hash索引相比B+树索引：
    • 如果是仅进行=, <>, IN查询，HASH结构效率更高；如果进行范围查询，HASH索引的查询效率会退化为O(n)
    • HASH索引存储是没有顺序，ORDER BY操作需要对数据进行重新排序
    • 对于联合索引，HASH索引将多个键合并计算，无法单独索引其中一个键
    • 如果索引值有大量重复，HASH索引效率会降低，不能用在重复值多的列上，如性别、年龄等

13.6 其他引擎

  • Merge引擎：管理多个MyISAM表的表集合
  • NDB引擎：NDB引擎是MySQL的分布式存储引擎，主要用于MySQL集群环境
  • Federated引擎：访问其他MySQL数据库服务器的一个代理，尽管非常灵活，但也有很多问题，默认禁用
  • Blackhole引擎：Blackhole引擎不存储数据，只用于测试，向它存储的数据不会被保存
最近更新