4.MySQL存储引擎

小新1号
102 阅读11分钟

一、存储引擎

概念：存储引擎是MySQL组件，用于处理不同表类型的SQL操作，不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能，使用不同的存储引擎还可以获得特定的功能。

使用哪种引擎可以灵活选择，一个数据库中多个表可以使用不同引擎以满足各种性能，为不同的表选择合适的存储引擎，将会提高整个数据库的性能。

1.1 查看存储引擎

-- 查看支持的存储引擎
SHOW ENGINES

-- 查看默认存储引擎
SHOW VARIABLES LIKE 'storage_engine'

--查看具体某一个表所使用的存储引擎，这个默认存储引擎被修改了！
show create table tablename

--准确查看某个数据库中的某一表所使用的存储引擎
show table status like 'tablename'
show table status from database where name="tablename"

1.2 设置存储引擎

-- 建表时指定存储引擎。默认的就是INNODB，不需要设置
CREATE TABLE t1 (i INT) ENGINE = INNODB;
CREATE TABLE t2 (i INT) ENGINE = CSV;
CREATE TABLE t3 (i INT) ENGINE = MEMORY;

-- 修改存储引擎
ALTER TABLE t ENGINE = InnoDB;

-- 修改默认存储引擎，也可以在配置文件my.cnf中修改默认引擎
SET default_storage_engine=NDBCLUSTER;

二、存储引擎种类介绍

image.png

  • MyISAM: 拥有较高的插入、查询速度，不支持事务、不支持外键，支持表锁查询速度快
  • InnoDB：支持ACID事务可靠性与性能较高，支持行级锁定适合高并发场景。读写效率稍差，空间占用较大。
  • Memory：所有数据置于内存的存储引擎，拥有极高的插入、更新、查询效率。由于受到硬件和断电影响一般作为临时表使用

2.1 InnoDB引擎

2.1.1 文件存储结构

MySQL中建立任何一张数据表，其数据目录对应的数据库目录下都有对应表的.frm文件，.frm文件用来保存每个数据表的元数据信息，包括表结构定义等。与数据库存储引擎无关，也就是任何存储引擎数据表都必有.frm文件，命名方式为数据表名.frm

查看MySQL数据保存在哪的命令：show variables like 'data%'

物理文件结构

  • .frm文件：与表相关的元数据信息都存放在frm文件，包括表结构定义信息等
  • .ibd文件或.ibdata文件：这两种文件都存放InnoDB数据的文件，之所以有两种文件形式存放数据，是因为InnoDB数据存储方式能够通过配置决定使用共享表空间还是独享表空间存储数据。

独享表空间存储方式使用.ibd文件，并且每个表一个.ibd文件 共享表空间存储方式使用.ibdata文件，所有表共同使用一个.ibdata文件

2.1.2 特点

  • 支持事务，支持4个事务隔离级别
  • 支持行级锁（更新时锁定当前行）
  • 读写阻塞与事务隔离级别相关
  • 既能缓存索引又能缓存数据
  • 支持外键

2.1.3 业务场景

  • 需要支持事务的场景（银行转账）
  • 适合高并发，行级锁对高并发有很好地适应
  • 数据修改较频繁的业务

2.1.4 引擎调优

  • 主键尽可能小，否则会给Secondary index带来负担
  • 避免全表扫描，会造成锁表
  • 尽可能缓存所有索引和数据，减少IO操作
  • 尽量避免主键更新，会造成大量数据移动

2.2 MyISM引擎

2.2.1 文件存储结构

物理文件结构

  • .frm文件：与表相关的元数据信息都存放在frm文件，包括表结构定义信息等。
  • .MYD(MYData)文件：MyISAM存储引擎专用，用于存储MyISAM表数据
  • .MYI(MYIndex)文件：MyISAM存储引擎专用，用于存储MyISAM表的索引相关信息

2.2.2 特点

  • 不支持事务
  • 表级锁定（更新时锁定整个表）
  • 读写互相阻塞（写入时读入阻塞、读时写入阻塞）
  • 只会缓存索引（通过key_buffer_size缓存索引，但是不会缓存数据）
  • 不支持外键
  • 读取速度快

2.2.3 业务场景

  • 不需要支持事务的场景
  • 一般读数据较多的业务，数据修改相对少的业务
  • 数据一致性要求不高的业务

2.2.4 MyISAM引擎调优

  • 设置合适索引
  • 启用延迟写入、尽量一次大批量写入、避免频繁写入
  • 尽量顺序insert数据，让数据写入到尾部，减少阻塞
  • 降低并发数、高并发使用排队机制
  • MyISAM的count只有全表扫描比较高效

三、常用存储引擎对比

对比项MyISAMInnoDB
主外键不支持支持
事务不支持支持
行表锁表锁，即使操作一条记录也会锁住整个表，不适合高并发的操作行锁,操作时只锁某一行，不对其它行有影响，适合高并发的操作
缓存只缓存索引，不缓存真实数据不仅缓存索引还要缓存真实数据，对内存要求较高，而且内存大小对性能有决定性的影响
表空间小大
关注点性能事务
默认安装是是

四、数据存储拓展

4.1 磁盘基本知识

数据库中的数据存储在文件系统中。文件系统是操作系统用来明确存储设备（如磁盘、固态硬盘）或分区上的文件的方法和数据结构。磁盘上数据必须用一个三维地质唯一标识：柱面号、盘面号、块号（磁道上的盘块）

硬盘整体模型图：

image.png

image.png

  • 盘片（platter）：硬盘中承载数据存储的介质。硬盘一般由多个盘片组成，每个盘片包含两个面，每个盘面都对应的有一个读/写磁头。受硬盘体积和生产成本限制，盘片数量都受到限制，一般在5片以内。盘片编号自下向上从0开始，如最下边的盘片有0面和1面，再上一个盘片就为2面和3面。
  • 磁头（head）：通过磁性原理读取磁性介质上数据的部件
  • 磁道（track）：磁盘旋转时，磁头若保持在一个位置上，则每个磁头都会在磁盘表面划出一个圆型轨迹，这些轨迹叫做磁道
  • 扇区（sector）：磁盘上的每个磁道被等分为若干个弧段，这些弧段便是硬盘的扇区，同一块硬盘上扇区大小是一致的。
  • 柱面（cylinder）：在有多个盘片构成的盘组中，由不同盘片的面，但处于同一半径圆的多个磁道组成一个圆柱面。

4.2 磁盘基本单位

  • 物理扇区：磁盘上的每个磁道被等分为若干个弧段，这些弧段便是硬盘的扇区，同一块硬盘上扇区大小是一致的。
  • 逻辑扇区：为了追求更大的硬盘容量，便出现了扇区存储容量为2048、4096等字节的硬盘。这种大扇区会导致许多兼容问题。为了解决该问题，硬盘内部将物理扇区在逻辑上划分为多个扇区片段并将其作为普通扇区报告给操作系统。实际读写时由硬盘内程序负责逻辑扇区和物理扇区间转换。
  • 块（Block）\簇（Cluster）：块\簇是操作系统中最小的逻辑存储单位，操作系统与磁盘打交道（IO）的最小单位是块\簇。

磁盘最小单位是扇区，操作系统使用的是块\簇作为IO的基本单位。使用块读取方便，扇区容量下，数据多会加大寻址难度。分离对底层依赖，操作系统忽略对底层物理存储结构设计。扇区是对硬盘而言，块是对文件系统而言，出于不同的需要

  • 页（Page）：操作系统经常与内存和硬盘两种存储设备通信，类似于块的概念。与内存操作时，是虚拟一个页的概念作为最小单位。

扇区：硬盘的最小读写单元

块/簇：操作系统对硬盘读写的最小单元

页：操作系统对内存交互的最小单元

4.3 MySQL InnoDB数据存储及结构

MySQL InnoDB数据存储结构可划分为逻辑存储结构和物理存储结构

  1. 前置：数据库磁盘读取与系统磁盘读取

系统从磁盘中读取数据到内存时是以磁盘块（block）为基本单位，位于同一个磁盘块中的数据会被一次性读取。

InnoDB存储引擎中有页（Page）的概念，页是数据库管理磁盘的最小单位

InnoDB存储引擎中默认每页大小为16kb，因此InnoDB每次IO操作时都会将若干个地址连续的磁盘块的数据读入内存，从而实现整页读入内存

2. 物理存储结构

物理意义上来看，InnoDB表由共享表空间、日志文件组（准确的说是Redo文件组）、表结构定义文件组成。若将innodb_file_pre_table设置为on，则每个表将独立的产生一个表空间文件，以ibd结尾，数据、索引、表的内部数据字典信息都将保存在这个单独的表空间文件中。表结构定义文件以frm结尾，这个文件与存储引擎无关。

3.逻辑存储结构

InnoDB存储引擎的逻辑存储结构和Oracle大致相同，所有数据都被逻辑的存放在一个空间中我们称之为表空间，这个空间称为表空间。表空间又由段、区、页组成。1 extent = 64 pages，InnoDB存储引擎的逻辑存储结构如图所示

image.png

表空间（tablespace）

表空间可以看做InnoDB存储引擎逻辑结构的最高层，所有数据都存放在表空间中。默认情况下InnoDB存储引擎有个共享表空间ibdata1，所有数据都放在这个表空间内。如果我们启用了参数innodb_file_per_table，则每张表内的数据可单独放到一个表空间内。

需要注意，每张表的表空间内存放的只是数据、索引、插入缓冲、其它类的数据，如撤销（Undo）信息，系统事务信息、二次写缓冲等还是存放在原来的共享表空间内。也就是说：即使启用了参数innodb_file_per_table后，共享表空间大小还是会不断地增加

段（segment）

表空间由各个段组成的，常见的段有数据段、索引段、回滚段等。

InnoDB存储引擎表是由索引组织的，因此数据即索引，索引即数据。InnoDB采取B+树作为存储数据的结构，数据段即为B+树的叶节点，索引段即为B+树的非叶子节点

区（extent）

一个区是由64个连续的页组成，每个页大小为16kb，即每个区大小为1MB。对于大的数据段，InnoDB存储引擎最多每次可以申请4个区来保证数据的顺序性能。

在我们启用了参数innodb_file_per_talbe后，创建的表默认大小是96KB，新建的InnoDB表就是一个区。区是64个连续的页，那创建的表的大小至少是1MB才对啊？其实这是因为在每个段开始时，先有32个页大小的碎片页（fragment page）来存放数据，当这些页使用完之后才是64个连续页的申请

页（Page）

每个页大小为16KB，页是InnoDB磁盘管理的最小单位，整页整页的读取。

InnoDB主要页类型： 1.数据页（BTreeNode） 2.Undo页（undo Log page） 3.系统页（System Page） 4.事务数据页（Transaction SystemPage）

image.png

  • 0-38：页头占据38位字节，页面id（32位的整数），页面类型以及两个分别指向前一个page和后一个page的指针（page是一个双向链表）等信息
  • 38-16376：不同类型页所含的数据不同，这部分空间包含系统记录和用户记录，我们表中的一条条记录就放在UserRecord部分。
  • 16376-16384：页面结束标识

由页组成的链表，页之间是双向列表，页里面的数据是单向链表，这种结构组成了主键索引B+树组成了叶子节点数据。

image.png

定位一条记录的过程

select * from user where id = 29

此处id为主键，通过这棵B+树来寻找。

1.首先找到user表根页面（一个表通常由多个页组成，根页就是起始页。每张表根页位置在表空间文件中是固定的）

2.层级遍历非叶子节点页（索引）读取到key值为29的指针（遍历非叶子节点的过程随着节点的遍历会将一个或多个页加载到内存），最后到指针指向的叶子节点所在的页中，遍历找出该条记录。

avatar
文章
阅读
粉丝