MySQL存储引擎(七)

第1章 MySQL存储引擎介绍

1.1 MySQL存储引擎介绍

文件系统是一种软件,它是操作系统组织和存取数据的一种机制。文件系统类型分为:ext2、ext3、ext4,xfs和数据等,不管使用什么文件系统,数据内容都不会变化,不同的是文件系统的存储空间、大小、速度。

MySQL引擎可以理解为,MySQL的“文件系统”,只不过功能更加强大,除了可以提供基本的存取功能,还有更多功能事务功能、锁定、备份和恢复、优化以及特殊功能。

1.2 MySQL存储引擎种类

MySQL 提供以下存储引擎:InnoDB、MyISAM(以上两个最常用),MEMORY、ARCHIVE、FEDERATED、EXAMPLE、BLACKHOLE、MERGE、NDBCLUSTER、CSV等,还可以使用第三方存储引擎。

第2章 MySQL Innodb存储引擎简介

Innodb存储引擎是MySQL5.5版本之后默认的存储引擎,它提供了高可靠性和高性能。

2.1 Innodb存储引擎优点

  • 事务安全(遵从 ACID)
  • MVCC(Multi-Versioning Concurrency Control,多版本并发控制)(InnoDB 行级别锁定、Oracle 样式一致非锁定读取)
  • 表数据进行整理来优化基于主键的查询
  • 支持外键引用完整性约束
  • 大型数据卷上的最大性能
  • 将对表的查询与不同存储引擎混合
  • 出现故障后快速自动恢复
  • 用于在内存中缓存数据和索引的缓冲区池

2.2 Innodb功能总览

图片[1]|MySQL存储引擎(七)|leon的博客

2.3 查看数据库的存储引擎设置

2.3.1 使用 SELECT 确认会话存储引擎

mysql> select @@default_storage_engine;
+--------------------------+
| @@default_storage_engine |
+--------------------------+
| InnoDB                   |
+--------------------------+
1 row in set (0.01 sec)

2.3.2 使用 SHOW 确认每个表的存储引擎

mysql> use world;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

mysql> show create table city;
+-------+-------------------------------------------------------------------------------+
| Table | Create Table                                                                  |
+-------+-------------------------------------------------------------------------------+
| city  | CREATE TABLE `city` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Name` char(35) NOT NULL DEFAULT '',
  `CountryCode` char(3) NOT NULL DEFAULT '',
  `District` char(20) NOT NULL DEFAULT '',
  `Population` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`ID`),
  KEY `CountryCode` (`CountryCode`),
  CONSTRAINT `city_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `country` (`Code`)
) ENGINE=InnoDB AUTO_INCREMENT=4080 DEFAULT CHARSET=latin1 |
+-------+-------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> show table status like 'countrylanguage'\G;
*************************** 1. row ***************************
           Name: countrylanguage
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 984
 Avg_row_length: 99
    Data_length: 98304
Max_data_length: 0
   Index_length: 65536
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2017-11-15 11:05:34
    Update_time: NULL
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

2.3.3 使用 INFORMATION_SCHEMA 确认每个表的存储引擎

mysql> SELECT TABLE_NAME, ENGINE FROM
    -> INFORMATION_SCHEMA.TABLES
    -> WHERE TABLE_NAME = 'city'
    -> AND TABLE_SCHEMA = 'world';
+------------+--------+
| TABLE_NAME | ENGINE |
+------------+--------+
| city       | InnoDB |
+------------+--------+
1 row in set (0.00 sec)

2.4 设置存储引擎

2.4.1 在启动配置文件中设置服务器存储引擎

[mysqld]
default-storage-engine=<Storage Engine>

2.4.2 使用 SET 命令为当前客户机会话设置

SET @@storage_engine=<Storage Engine>;

2.4.3 在 CREATE TABLE 语句指定

CREATE TABLE 表名(字段 类型) ENGINE = <Storage Engine>;

第3章 Innodb体系结构

共享表空间(ibdata1:默认情况下,InnoDB 数据字典(元数据)、撤销日志(UNDO,是事务日志的一种)和缓冲区存储在系统“表空间”中。

独立表空间:是5.6以后默认的存储引擎,影响的是在新创建的表时自动会使用独立表空间存储,每个表一个frm(表结构定义文件)和数据文件(.ibd)

3.1 物理存储结构(表空间)

图片[2]|MySQL存储引擎(七)|leon的博客

3.1.1 段、区、页

  • 页是数据文件中,最小分配单元,默认是16KB
  • 区是连续的页
  • 段,多个区构成,一个表是一个段

图片[3]|MySQL存储引擎(七)|leon的博客

3.2 系统共享表空间

这是单个逻辑存储区域,可以包含一个或多个文件,每个文件可以是常规文件或原始分区,最后的文件可以自动扩展。

图片[4]|MySQL存储引擎(七)|leon的博客

3.2.1 通过添加数据文件增加表空间大小

在 my.cnf 文件中使用 innodb_data_file_path 选项:

[mysqld]
innodb_data_file_path=datafile_spec1[;datafile_spec2]...
提示:ibdata1 这个文件必须要和当前文件大小一致,不能多不能少

3.2.2 配置示例

创建一个表空间,其中包含一个名为 ibdata1 且大小为 50 MB (固定)的数据文件和一个名为 ibdata2 且大小为 50 MB(自动扩展)的数据文件:

[mysqld]
innodb_data_file_path=ibdata1:50M;ibdata2:50M:autoextend
提示:默认情况下将文件放置在 data 目录中,如果需要,可以显式指定文件位置。

3.3 数据独立表空间

除了系统表空间之外,InnoDB还在数据库目录中创建另外的表空间,用于每个InnoDB表的.ibd文件,InnoDB 创建的每个新表在数据库目录中设置一个.ibd文件来搭配表的.frm文件,可以使用innodb_file_per_table选项控制此设置,更改该设置仅会更改已创建的新表的默认值。

提示:在mysql5.6开始,默认的配置为:|innodb_file_per_table | ON|

第4章 Innodb引擎事务

4.1 Innodb引擎事务简介

一组数据操作执行步骤,这些步骤被视为一个工作单元,可以用于对多个语句进行分组,或在多个客户机并发访问同一个表中的数据时使用。所有步骤都成功或都失败,如果所有步骤正常,则执行;如果步骤出现错误或不完整,则取消。

事务是伴随着“交易”出现的数据库概念,数据库中的“交易”是指ACID。

4.1.1 事务ACID介绍

  • Atomic(原子性):所有语句作为一个单元全部成功执行或全部取消
  • Consistent(一致性):如果数据库在事务开始时处于一致状态,则在执行该事务期间将保留一致状态
  • Isolated(隔离性):事务之间不相互影响
  • Durable(持久性):事务成功完成后,所做的所有更改都会准确地记录在数据库中。所做的更改不会丢失。

4.1.2 事务处理流程举例

图片[5]|MySQL存储引擎(七)|leon的博客

4.1.3 事务SQL控制语句

START TRANSACTION(或 BEGIN):显式开始一个新事务,事务开始的标记(二进制日志中有很明显的标记)
SAVEPOINT:分配事务过程中的一个位置,以供将来引用
COMMIT:事务的完成标记,永久记录当前事务所做的更改
ROLLBACK:事务撤销标记,取消当前事务所做的更改
ROLLBACK TO SAVEPOINT:取消在 savepoint 之后执行的更改
RELEASE SAVEPOINT:删除 savepoint 标识符
SET AUTOCOMMIT:为当前连接禁用或启用默认 autocommit模式

4.2 Autocommit模式设置

从MySQL5.5开始,开启事务时不再需要begin或者start transaction语句,并且默认是开启了Autocommit模式,作为一个事务隐式提交每个语句,在有些业务繁忙企业场景下,这种配置可能会对性能产生很大影响,但对于安全性上有很大提高,将来我们需要去权衡我们的业务需求再去调整是否自动提交。

可以通过以下命令进行修改关闭(0是关闭,1是开启):

SET GLOBAL AUTOCOMMIT=0;  # 所有新建会话
SET SESSION AUTOCOMMIT=0; # 当前会话
SELECT @@AUTOCOMMIT;      # 看设置结果

也可以修改配置文件让其永久生效:

vim /etc/my.cnf
[mysqld]
AUTOCOMMIT=0

4.3 其他触发隐式commit的情况

4.3.1 用于隐式提交的 SQL 语句

START TRANSACTION
SET AUTOCOMMIT = 1

4.3.2 导致提交的非事务语句

DDL语句: (ALTER、CREATE 和 DROP)
DCL语句: (GRANT、REVOKE 和 SET PASSWORD)
锁定语句:(LOCK TABLES 和 UNLOCK TABLES)

4.3.3 导致隐式提交的语句

TRUNCATE TABLE
LOAD DATA INFILE
SELECT FOR UPDATE

4.4 事务日志redo

4.4.1 Redo简介

redo,顾名思义“重做日志”,记录的是内存页的变化,是事务日志的一种,也为了提高数据持久化速度的一种特性,可以理解为只要redo落地,就认为数据持久化了,因此可以不需要将数据页落地到磁盘才算数据安全。

4.4.2 Redo的作用

在事务ACID过程中,实现的是“D”持久化的作用。

图片[6]|MySQL存储引擎(七)|leon的博客

4.5 事务日志undo

4.5.1 undo简介

undo,顾名思义“回滚日志”,修改之前的内存数据页,是事务日志的一种。当需要回滚的时候,将undo页覆盖到数据内存页。

4.5.2 undo的作用

在事务ACID过程中,实现的是“A”一致性的作用。

图片[7]|MySQL存储引擎(七)|leon的博客

4.6 LSN日志版本号

LSN是日志版本号,或者叫日志序列号,存在于数据页,内存页,redo,undo中。

4.6.1 数据库崩溃恢复的过程原理

  • 假设执行update命令将A=10改成A=20时突然断电,此时数据库恢复的过程为:
  1. 将A=10数据页加载到内存页中,假如LSN号原始是LSN=1,此时当前数据页LSN=1,当前的内存页LSN=1
  2. 发起update操作时,数据页LSN=1,A=10;undo的LSN=1,A=10;内存页LSN=2;redo内存页LSN=2,如果已经存储在磁盘上时,ib_logfile0的LSN=2
  3. 如果已经commit成功,在redo文件会有commit标记,如果没有提交,就会有rollback标记
  4. 如果有commit标记时,会读取redo文件,查看LSN=2,发现比数据页中的LSN高,此时数据库就会重演redo的变化操作,并且会直接将变化后的数据刷写到磁盘
  5. 如果没有commit标记,会读取redo文件,查看LSN=2,发现比数据页中的LSN高,数据库就会重演redo的变化操作,并且会利用undo回滚所有操作
总结:数据库崩溃时,永远是按照先前滚(redo),后回滚顺序(undo)。

4.7 事务中的锁

4.7.1 “锁”的作用

“锁”就是锁定的意思,在事务ACID过程中,“锁”和“隔离级别”一起来实现“I”隔离性的作用。

图片[8]|MySQL存储引擎(七)|leon的博客

4.7.2 锁的粒度

  • MyIasm:低并发锁—表级锁
  • Innodb:高并发锁—行级锁

4.8 事务的隔离级别简介

  • READ UNCOMMITTED:允许事务查看其他事务所进行的未提交更改
  • READ COMMITTED:允许事务查看其他事务所进行的已提交更改
  • REPEATABLE READ:(很重要)是InnoDB 的默认级别,确保每个事务的 SELECT 输出一致
  • SERIALIZABLE:将一个事务的结果与其他事务完全隔离

第5章 扩展内容

5.1 线程+内存+存储结构整体图

图片[9]|MySQL存储引擎(七)|leon的博客

温馨提示:本文最后更新于2022-12-20 20:57:52,已超过431天没有更新。某些文章具有时效性,若文章内容或图片资源有错误或已失效,请联系站长。谢谢!
转载请注明本文链接:https://blog.leonshadow.cn/763482/710.html
© 版权声明
THE END
喜欢就支持一下吧
点赞0 分享