【MySQL原理系列】- MySQL的存储引擎

于 2025-02-27 23:30:56 发布
阅读量1.1k 收藏 13
点赞数 13
分类专栏： MySQL原理系列 文章标签： mysql 数据库 java 后端
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/m0_68383079/article/details/145916409
版权

【MySQL原理系列】- MySQL的存储引擎

文章目录

在文件系统中，MySQL将每个数据库（也可以称之为schema）保存为数据目录下的一个子目录

创建表时，MySQL会在数据库子目录下创建一个和表同名的 .frm 文件保存表的定义

  • 因为MySQL使用文件系统的目录和文件来保存数据库和表的定义，大小写敏感性和具体的平台密切相关；在Windows中大小写是不敏感的，而在类Unix中则是敏感的

  • 不同存储引擎保存数据和索引的方式不同，但表的定义在MySQL服务层统一处理

可以使用SHOW TABLE STATUS 命令显示表的相关信息

一、页

在MySQL中，页是存储引擎用于管理和组织数据的基本单位，是数据库从磁盘中读取或写入数据的最小块，也是缓存系统操作的基本单位

在InnoDB中，页的默认大小通常是 16KB

页的类型

不同类型的数据被存储在不同类型的页中，InnoDB中的页类型主要有以下几种：

  • 数据页（Data Page）：存储实际的表记录，每个数据页中包含了多条记录
  • 索引页（Index Page）：存储B+树结构的索引信息
  • Undo页：用于存储事务回滚操作所需的数据
  • 系统页：存储与数据字典和表空间相关的信息

页的功能

每次 MySQL 读取或写入磁盘时，操作的都是一页数据，而不是单条记录，好处如下：

  • 提高磁盘IO效率：通过一次性读取多个记录（即一页数据）可以减少多次IO操作的开销
  • 支持缓存：InnoDB使用 **缓冲池（buffer pool）**来缓存页，以减少磁盘访问。每当需要访问一条记录时，首先会检查该记录所在的页是否在缓冲池中，如果在，则直接从内存读取，避免磁盘IO

页与索引的关系

InnoDB 使用B+树索引结构，数据页和索引页共同构成了B+树的节点，叶子节点存储实际的数据，而非叶子节点存储指向其他页的指针

页分裂与合并

  • 在B+树的索引结构中，当一个页的数据超过了页的容量，InnoDB 会进行 页分裂 操作，将数据分裂到两个页中，从而保持B+树的平衡

  • 同样，当删除数据后，页中数据减少，InnoDB 可能会进行 页合并，以释放空间

二、InnoDB存储引擎

InnoDB是MySQL的默认事务型引擎，也是最重要、使用最广泛的存储引擎

它被设计用来处理大量的短期事务，短期事务大部分正常提交，且很少回滚

InnoDB的性能和自动崩溃恢复特性，使得它在非事务型存储的需求中也很流行。除非有特别原因需用其他存储引擎，否则用InnoDB引擎

  • InnoDB的数据存储在表空间tablespace中，表空间是由InnoDB管理的一个黑盒子，由一系列的数据文件组成，InnoDB可以将每个表的数据和索引存放在单独的文件中

  • InnoDB采用MVCC来支持高并发，并且实现了四个标准的隔离级别，默认是可重复读，并且通过间隙锁策略防止幻行的出现

  • InnoDB表是基于聚簇索引建立的，聚簇索引对主键查询有很高的性能，不过它的二级索引中必须包含主键列，所以如果主键列很大，其他的所有索引都会很大，若表上的索引较多的话，主键应尽量小

  • InnoDB的存储格式是平台独立的，与平台无关，可以在平台间迁移

  • InnoDB内部做了很多优化：

    • 从磁盘读取数据时采用预读
    • 自适应哈希索引能自动在内存中创建hash索引以加速读操作
    • 用插入缓冲区加速插入操作的

  • 作为事务型的存储引擎，InnoDB通过一些机制和工具支持真正的热备份

    • 存储引擎要为所有用户维持一致性的视图，是非常复杂的工作
    • MySQL的其他存储引擎不支持热备份，要获取一致性视图需要停止对所有表的写入

三、MyISAM存储引擎

在MySQL 5.1及之前的版本，MyISAM是默认的存储引擎

MyISAM不支持事务和行级锁，崩溃后无法安全恢复

对于只读的数据，或表比较小能忍受修复操作，可以使用MyISAM，因其速度快

MyISAM会将表存储在两个文件中：数据文件和索引文件，分别以 .MYD 和 .MYI 为扩展名

  • MyISAM表可以存储的行记录数，一般受限于可用的磁盘空间，或者操作系统中单个文件的最大尺寸

  • MyISAM表可以包含动态或者静态行

    • 动态静态指行长度是否固定
    • 在MySQL 5.0中，MyISAM表如果是变长行，则默认配置只能处理256TB的数据，因为指向数据记录的指针长度是6个字节

1. MyISAM特性

  • 加锁与并发

    MyISAM对整张表加锁，而不是针对行。读取时对表加共享锁，写入时加排他锁，在表有读取查询的同时，也可以往表中插入新的记录（称为并发插入）

  • 修复

    对于MyISAM表，MySQL可以手工或者自动执行检查和修复操作，修复和事务恢复崩溃恢复不同

  • 索引特性

    对于MyISAM表，即使是BLOB和TEXT等长字段，也可以基于其前500个字符创建索引。MyISAM也支持全文索引，这是一种基于分词创建的索引

  • 延迟更新索引键（Delayed Key Write）

    创建MyISAM表时，如果指定了DELAY_KEY_WRITE 选项，在每次修改执行完成时，不会立刻将修改的索引数据写入磁盘，而是会写到内存中的键缓冲区，只有在清理键缓冲区或者关闭表的时候才会将对应的索引块写入到磁盘

2. MyISAM压缩表

如果表在创建并导入数据以后，不会再进行修改操作，则更适合用MyISAM压缩表

  • 使用myisampack 对MyISAM表进行压缩

  • 压缩表不能进行修改（除非将表解缩，修改数据，再次压缩）

  • 读取压缩表数据时需要解压，每条记录独立压缩，故读取单行时不用解压整个表

  • 压缩表的索引也是只读的

压缩表可以极大地减少磁盘空间占用，因此可以减少磁盘I/O，从而提升查询性能

解压的开销影响并不大，而减少I/O带来的好处要大得多

3. MyISAM性能

MyISAM引擎在某些场景下性能很好，但MyISAM最典型的性能问题是表锁的问题

四、MySQL内建的其他存储引擎

MySQL还有一些有特殊用途的存储引擎

  • Archive引擎

    • Archive引擎不是一个事务型的引擎，而是一个针对高速插入和压缩做了优化的简单引擎
    • 只支持INSERT 和SELECT 操作
    • 支持行级锁和专用的缓冲区
    • 会缓存所有的写并对插入的行进行压缩，比MyISAM表的磁盘I/O更少
    • 可以实现高并发的插入，每次SELECT查询都需要执行全表扫描，适合日志和数据采集类应用

  • CSV引擎

    CSV引擎可以将CSV文件作为MySQL的表来处理，CSV引擎可以在数据库运行时拷入或者拷出文件

    • 可以将数据存储为CSV文件，复制到MySQL数据目录下，在MySQL中打开使用

    • 同样，将数据写入到CSV引擎表，其他外部程序也能立即从表的数据文件中读取CSV格式的数据

    • 因此CSV引擎可以作为一种数据交换的机制，非常有用

  • Memory引擎

    • 如果要快速访问数据，并且数据不会被修改，重启以后丢失也没关系，则可以用Memory表
    • 优点：Memory表至少比MyISAM表要快一个数量级，因为所有的数据都保存在内存中，不需要进行磁盘I/O，且Memory表支持Hash索引，因此查找操作非常快；Memory表的结构在重启以后还会保留，但数据会丢失
    • 缺点：表级锁，并发写入的性能较低；不支持BLOB 或TEXT 类型的列，且每行的长度固定
    • 用于查找或映射表，或保存数据分析中产生的中间数据。如果MySQL在执行查询的过程中需要使用临时表来保存中间结果，其临时表多选用Memory表；如果中间结果太大超出了Memory表的限制，或者含有BLOB 或TEXT 字段，则临时表会转换成MyISAM表

  • NDB集群引擎

    MySQL服务器、NDB集群存储引擎，以及NDB数据库的组合，被称为MySQL集群(MySQL Cluster)

五、选择合适的引擎

除非要用到某些InnoDB不具备的特性，并且没有其他办法可以替代，否则都应该优先选择InnoDB引擎，所以以Oracle在MySQL 5.5版本时将InnoDB作为默认存储引擎

不要混合使用多种存储引擎，否则可能带来一系列复杂的问题

选引擎的考虑因素：

  • 事务
    • 应用需要事务支持，则InnoDB（或者XtraDB）
    • 如果不需要事务，且主要是SELECT和INSERT 操作，那可选MyISAM
  • 备份
    • 如果需要在线热备份，则InnoDB
  • 崩溃恢复
    • MyISAM崩溃后发生损坏的概率比InnoDB高很多，而且恢复速度也要慢
  • 特有的特性
    • 很多应用依赖InnoDB聚簇索引的优化
    • MySQL中只有MyISAM支持地理空间搜索

常见应用场景：

  • 日志型应用

    • 假设需要实时地记录日志到MySQL中，或者通过Apache的mod_log_sql 模块将网站的所有访问信息直接记录到表中，这一类应用的插入速度有很高的要求，数据库不能成为瓶颈
    • MyISAM或者Archive存储引擎比较合适，因为它们开销低，而且插入速度非常快

  • 读多写少的表

    • 如果不介意MyISAM的崩溃恢复问题，选用MyISAM引擎是合适的
    • MyISAM引擎在一开始可能没有任何问题，但随着应用压力的上升，则可能迅速恶化，各种锁争用、崩溃后的数据丢失等问题都会随之而来

  • 订单处理

    • 支持事务就是必要选项，还需考虑存储引擎对外键的支持情况，需用InnoDB

  • 主题讨论区

    • 多数应用只设计了几张表来保存所有的数据，所以核心表的读写压力非常大
    • 中低负载时可以工作得很好，高负载时需要更换支持更高读写的存储引擎，如MyISAM

  • CD-ROM应用

    • CD-ROM或者DVD-ROM是两种只读的光盘存储介质

    • 可以考虑使用MyISAM表或者MyISAM压缩表

  • 大数据量

    • 很多InnoDB数据库的数据量在3～5TB之间
    • 大数据量下如果采用MyISAM，崩溃后的恢复就是噩梦
    • 如果数据量继续增长到10TB以上的级别，就需要建立数据仓库Infobright

六、转换表的引擎

将表的存储引擎转换成另外一种引擎的方法：

  • ALTER TABLE：高效

    • 将mytable 的引擎修改为InnoDB，mysql> ALTER TABLE mytable ENGINE=InnoDB;
    • 该方法执行时间很长，在复制期间可能消耗系统所有I/O能力，同时原表会加上读锁
    • 如果转换表的存储引擎，将会失去和原引擎相关的所有特性；如将一张InnoDB表转换为MyISAM，然后再转换回InnoDB，原InnoDB表上所有的外键将丢失

  • 导出与导入：安全

    • 使用mysqldump工具将数据导出到文件，然后修改文件中CREATE TABLE语句的存储引擎选项，注意修改表名，因为同一个数据库中不能存在相同的表名，即使它们使用的是不同的存储引擎

  • 创建与查询（CREATE和SELECT）

    • 综合了第一种方法的高效和第二种方法的安全

    • 是先创建一个新的存储引擎的表，然后利用INSERT…SELECT 语法来导数据

      mysql> CREATE TABLE innodb_table LIKE myisam_table;

      mysql> ALTER TABLE innodb_table ENGINE=InnoDB;

      mysql> INSERT INTO innodb_table SELECT \* FROM myisam_table;

    • 如果有必要，可以在执行的过程中对原表加锁，以确保新表和原表的数据一致

MySQL 存储引擎的详细讲解
zjw541806的博客
05-04 561
FOREIGN KEY (user_id) REFERENCES users(id) -- 外键约束（仅 InnoDB 支持）-- SELECT * FROM test_myisam;-- InnoDB 行级锁示例（同上操作，会话2可立即更新 id=2 的行）-- 强制终止连接后，数据不会插入（事务回滚）-- 会话2：尝试写入会阻塞，直到会话1提交。-- 使用 InnoDB 引擎（默认）复制->-- 使用 MyISAM 引擎。复制->-- MyISAM 表级锁示例。-- 会话1：执行写操作（锁定全表）
MySQL--- InnoDB 存储引擎
青竹暖回阳的博客
10-09 947
InnoDB 存储引擎，Mysql
MySQL的存储引擎
2402_83805984的博客
06-18 1740
常用存储引擎：InnoDB、MyISAM不支持事务和外键约束，占用资源较小，访问速度快，表级锁定，支持全文索引，适用于不需要事务处理，单独写入或查询的应用场景。支持事务处理、外键约束，缓存能力较好，支持行级锁定，读写并发能力较好，5.5版本后支持全文索引，适用于一致性要求高、数据更新频繁的应用场景。2.MylSAM不支持事务，也不支持外键约束，只支持全文索引，数据文件和索引文件是分开保存的访问速度快，对事务完整性没有要求MylSAM适合查询、插入为主的应用。
MySQL存储引擎
ylspirit
02-04 771
MySQL5.5以后默认使用InnoDB存储引擎，其中InnoDB和BDB提供事务安全表，其它存储引擎都是非事务安全表。 若要修改默认引擎，可以修改配置文件中的default-storage-engine。可以通过：show variables like 'default_storage_engine';查看当前数据库到默认引擎。命令：show engines和show variables
MySQL 存储引擎
siriusol的博客
05-31 239
InnoDB 与 MyISAM 的区别 MyISAM是MySQL的默认数据库引擎（5.5版之前）。虽然性能极佳，而且提供了大量的特性，包括全文索引、压缩、空间函数等，但MyISAM不支持事务和行级锁，而且最大的缺陷就是崩溃后无法安全恢复。不过，5.5版本之后，MySQL 引入了 InnoDB（事务性数据库引擎），MySQL 5.5 版本后默认的存储引擎为 InnoDB。 是否支持行级锁 : MyISAM 只有表级锁(table-level locking)，而 InnoDB 支持行级锁 (row-leve
MySQL—存储引擎
灵魂在求知中净化。。。
06-29 261
MySQL—存储引擎 MySQL 存储引擎 一、存储引擎概念介绍 MySQL中的数据用各种不同的技术存储在文件中，每一种技术都使用不同的存储机制、索引技巧、锁定水平并最终提供不同的功能和能力，这些不同的技术以及配套的功能在MySQL中称为存储引擎 存储引擎是MySQL将数据存储在文件系统中的存储方式或者存储格式 1、MySQL常用的存储引擎： 1、MyISAM 2、InnoDB MySQL数据库中的组件，负责执行实际的数据I/O操作 MySQL系统中，存储引擎处于文件系统之上，在数
Linux运维数据库篇 MySQL的引擎概念和作用
weixin_49172531的博客
04-13 469
文章目录前言一、引擎的概念二、MySQL的引擎的分类1.mylsam事务引擎文件存储格式适用的场景2.innoDB事务读写硬件要求索引三 查看当前数据库的引擎四 修改库和表的引擎总结 前言 引擎是mysql以及其他数据库中的一个重要组件，它可以对数据进行搜索 可以和索引结合来进行快速搜索 一、引擎的概念 MySQL中的数据用各种不同的技术存储在文件中，每一种技术都使用不同的存储机制、索引技巧、锁定水平并最终提供不同的功能和能力，这些不同的技术以及配套的功能在MySQL中称为存储引擎 存储引擎是MySQL将数
mysql学习之旅（十三）——Mysql的存储引擎
weixin_38377637的博客
09-02 407
文章目录Mysql的存储引擎存储引擎的介绍如何设置存储引擎 Mysql的存储引擎 存储引擎的介绍 什么是存储引擎 数据库存储引擎是数据库底层软件组件。数据库管理系统使用数据引擎进行创建、查询、更新和删除数据的操作。 Mysql的核心就是存储引擎。 Mysql提供了多个不同的存储引擎，在Mysql中，不需要在整个服务器中使用同一种存储引擎，我们可以根据对数据处理的需求，选择不同的存储引擎。 MYSQ...
mysql-8.0.16-winx64-debug-test.zip
01-16
4. **InnoDB引擎改进**：InnoDB存储引擎在8.0.16版本中进行了大量优化，包括更快的索引插入、更高的并发性能以及更好的内存管理，提升了整体数据库性能。 5. **安全增强**：MySQL 8.0增强了安全性，如默认使用更...
mysql数据库原理及引擎-MySQL数据库原理及应用PDF
11-17
《MySQL数据库原理及应用》从教学实际出发，系统地介绍了MySQL数据库的有关原理和基本操作，主要内容包括数据库技术概述、MySQL概述、数据库基本操作、数据表、索引、结构化查询语言SQL、视图、触发器、存储过程和...
mysql-8.0.20-winx64-debug-test.zip
05-17
2. 数据库引擎：如InnoDB，这是MySQL的核心组件，负责存储和管理数据。 3. 客户端工具：包括命令行客户端、配置工具、备份工具（如mysqldump）等，使得用户能够连接、管理、查询数据库。 4. 配置文件：如my.ini或my....
mysql源码（mysql-8.2.0.zip）
12-15
4. **事务处理**：InnoDB存储引擎支持ACID（原子性、一致性、隔离性、持久性）特性，源码中涉及事务处理的部分可以让我们理解这些特性的实现原理。 5. **复制与集群**：MySQL的复制功能使得数据可以从主服务器同步...
Mysql的存储引擎
cqwoniu的博客
04-21 1044
一、什么是存储引擎 MySQL中的数据用各种不同的技术存储在文件中，每一种技术都使用不同的存储机制、索引技巧、锁定水平并最终提供不同的功能和能力，这些不同的技术以及配套的功能在MySQL中称为存储引擎 存储引擎是MySQL将数据存储在文件系统中的存储方式或者存储格式 MySQL数据库中的组件，负责执行实际的数据I/O操作 MySQL系统中，存储引擎处于文件系统之上，在数据保存到数据文件之前会传输到存储引擎，之后按照各个存储引擎的存储格式进行存储 二、有哪些存储引擎 1、InnoDB 它是mysql的默认
mysql存储引擎
热门推荐
yjclsx的博客
07-31 6万+
数据库存储引擎 数据库存储引擎是数据库底层软件组织，数据库管理系统（DBMS）使用数据引擎进行创建、查询、更新和删除数据。不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能，使用不同的存储引擎，还可以获得特定的功能。现在许多不同的数据库管理系统都支持多种不同的数据引擎。 因为在关系数据库中数据的存储是以表的形式存储的，所以存储引擎也可以称为表类型(Table Type，即存储和操作此表...
mysql的存储引擎
左直拳的马桶_日用桶
04-10 1425
一、什么是存储引擎 数据库存储引擎是数据库底层软件组织，数据库管理系统（DBMS）使用数据引擎进行创建、查询、更新和删除数据。不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能，使用不同的存储引擎，还可以 获得特定的功能。 存储引擎这个概念似乎只有mysql有，存储引擎不同，mysql呈现出来的特性也不同，适用场景就不同。其他数据库，没有听说过有存储引擎这个概念。但应该也有类似的机制。比如...
MySQL架构与历史5 MySQL的存储引擎5.1 InnoDB
Rush6666的博客
10-27 131
MySQL 的存储引擎有哪些?它们之间的区别是什么?
最新发布
Zero_pl的博客
02-23 1283
MySQL 作为一个关系型数据库管理系统，支持多种。，不同的存储引擎适用于不同的业务场景。在 MySQL 8.0 及以上版本中，
MySQL（五）、存储引擎
王义凯 的博客
08-17 619
MySQL的核心是存储引擎，在前面的文章中我们介绍了聚簇索引，非聚簇索引，表级锁行级锁等内容，其实它们在不同的存储引擎上的表现是不一样的，本篇就来介绍MySQL中常用的存储引擎以及适用的场景对比。
mysql5.1引擎_mysql【5.1x】存储引擎简要说明(1)
weixin_35890184的博客
01-19 226
MySQL支持数个存储引擎作为对不同表的类型的处理器。MySQL存储引擎包括处理事务安全表的引擎和处理非事务安全表的引擎：·MyISAM管理非事务表。它提供高速存储和检索，以及全文搜索能力。MyISAM在所有MySQL配置里被支持，它是默认的存储引擎，除非你配置MySQL默认使用另外一个引擎。·MEMORY存储引擎提供“内存中”表。MERGE存储引擎允许集合将被处理同样的MyISAM表作为一个单独...
shell获取上个月第一天
10-22
在Shell脚本中，获取上个月的第一天可以使用一些日期算术和条件判断来实现。这里假设您使用的是Bash环境，并且想获取当前时间所在月份的上一个月的第一天。以下是一个简单的示例： ```bash #!/bin/bash # 获取当前日期 current_date=$(date +%Y-%m-%d) # 分离出年月日 year_month=$(echo $current_date | cut -d '-' -f1,2) # 减去一天得到上个月最后一天 last_day_of_previous_month=$(date -d "$year_month-01" --date="last day of month" +%Y-%m-%d) # 再减去一天就是上个月的第一天 first_day_of_previous_month=$(date -d "$last_day_of_previous_month - 1 day" +%Y-%m-%d) echo "上个月的第一天是: $first_day_of_previous_month" ``` 运行这个脚本，它会打印出上个月第一天的日期。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值