[toc]

存储引擎简介

image-20230411170520815

  • 文件系统
    • 操作系统组织和存取数据的一种机制。
    • 文件系统是一种软件。
  • 文件系统类型
    • 不管使用什么文件系统，数据内容不会变化
    • 不同的是，存储空间、大小、速度。
  • Linux的文件系统
    • CentOS6文件系统：ext4
    • CentOS7文件系统：xfs
  • MySQL引擎
    • 可以理解为，MySQL的“文件系统”，只不过功能更加强大。
  • MySQL的存储引擎
    • MySQL5.5版本之前：MyIsam
    • MySQL5.5版本之后：Innodb

区别：myisam 表级锁，innodb行级锁

MySQL自带的存储引擎类型

MySQL 提供以下存储引擎

  1. InnoDB
  2. MyISAM
  3. MEMORY
  4. ARCHIVE
  5. FEDERATED
  6. EXAMPLE
  7. BLACKHOLE
  8. MERGE
  9. NDBCLUSTER
  10. CSV

还可以使用第三方存储引擎

  1. MySQL当中插件式的存储引擎类型
  2. MySQL的两个分支
  3. perconaDB
  4. mariaDB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
# 查看当前MySQL支持的存储引擎类型
show engines;

InnoDB
MRG_MYISAM
MEMORY
BLACKHOLE
MyISAM
CSV
ARCHIVE
PERFORMANCE_SCHEMA
FEDERATED

# 列出使用'innodb'的库
select table_schema,table_name,engine from information_schema.tables where engine='innodb';

# 列出使用'mysam'的库
select table_schema,table_name,engine from information_schema.tables where engine='myisam';

innodb和myisam物理上的区别

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
# 进入mysql目录
cd /application/mysql/data/mysql

# 查看所有user的文件
ll user.*
mysql mysql 10684 Mar  6  2017 user.frm
mysql mysql   960 Aug 14 01:15 user.MYD
mysql mysql  2048 Aug 14 01:15 user.MYI

# 进入word目录
cd /application/mysql/data/world/

# 查看所有city的文件
ll city.*
mysql mysql   8710 Aug 14 16:23 city.frm
mysql mysql 688128 Aug 14 16:23 city.ibd

innodb存储引擎的简介

在MySQL5.5版本之后，默认的存储引擎，提供高可靠性和高性能。

**优点: **

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

image-20230412154254364

外键

引用另外一个表中的一列或多列数据，被引用的列应该具有主键约束或者唯一性约束（简单来说外键是另一个表的主键或者唯一约束）。

外键可以不唯一, 可以是空值，用来建立和加强两个表数据之间的连接

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
# 有外键的建表语句
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`),
KEY `idx_population` (`Population`),
CONSTRAINT `city_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `country` (`Code`)
) ENGINE=InnoDB AUTO_INCREMENT=4080 DEFAULT CHARSET=latin1

# 无外键的建表语句
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`),
KEY `idx_population` (`Population`)
) ENGINE=InnoDB AUTO_INCREMENT=4080 DEFAULT CHARSET=latin1;

MySQL存储引擎核心特性

  • 表空间
  • 事务
  • 锁
  • CSR（自动故障恢复）
  • MVCC（多版本并发控制）
  • 隔离级别

查看存储引擎

1
2
3
4
5
6
7
8
9
10
11
12
13
14
# 使用 SELECT 确认当前默认使用的存储引擎
SELECT @@default_storage_engine;
+--------------------------+
| @@default_storage_engine |
+--------------------------+
| InnoDB                   |
+--------------------------+

# 使用 SHOW 确认每个表的存储引擎
show create table City\G
show table status like 'CountryLanguage'\G

# 使用 INFORMATION_SCHEMA 确认每个表的存储引擎
SELECT TABLE_NAME, ENGINE FROM 
INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'City'
AND TABLE_SCHEMA = 'world'\G

存储引擎的设置

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
# 建表指定存储引擎
CREATE TABLE t (i INT) ENGINE = <Storage Engine>;
create table test_engine(id int) charset latin1 engine myisam;
create table tb(id int) engine innodb;

# 修改默认存储引擎（永久生效需要重启数据库）
vim /etc/my.cnf
[mysqld]
default-storage-engine=innodb

# 修改默认存储引擎（临时生效不需要重启数据库）
set @@default_storage_engine=innodb;
select @@default_storage_engine;
+--------------------------+
| @@default_storage_engine |
+--------------------------+
| InnoDB                   |
+--------------------------+

# 修改表的存储引擎
alter table test_engine engine innodb;

Innodb存储引擎核心特性—表空间

表空间介绍

img

  • 共享表空间
    • 系统数据
    • undo
    • 临时表
  • 独立表空间
    • 用户真实数据

表空间位置

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
# 数据目录下(共享表空间)
/application/mysql/data/ibdata1

# 数据目录下（独立表空间）
/application/mysql/data/库名/stu_age.frm      # 表结构文件
/application/mysql/data/库名/stu_age.ibd      # 独立表空间 （用户真实数据）

# 查看所有默认配置（库外）
mysqladmin -uroot -p123@qqdianCOM variables

# 查看所有默认配置（库内）
show variables;
show variables like '%path%';
+----------------------------------+------------------------+
| Variable_name                    | Value                  |
+----------------------------------+------------------------+
| innodb_data_file_path            | ibdata1:12M:autoextend |
+----------------------------------+------------------------+

# 修改配置文件做共享表空间切割
innodb_data_file_path=ibdata1:50M;ibdata2:50M:autoextend
InnoDB: The innodb_system data file './ibdata1' is of a different size 13056 pages (rounded down to MB) than the 3200 pages specified in the .cnf file!
## 报错：修改ibdata1的表空间大小
1 pages = 16k
13056 pages = 204MB
3200 pages = 50M
204M ibdata1
innodb_data_file_path=ibdata1:204M;ibdata2:50M:autoextend

# 查看独立表空间
show variables like '%per_table%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | ON    |
+-----------------------+-------+

Innodb存储引擎核心特性-事务

什么是事务

主要针对DML语句（update，delete，insert）

一组数据操作执行步骤，这些步骤被视为一个工作单元:

所有步骤都成功或都失败

  1. 如果所有步骤正常，则执行
  2. 如果步骤出现错误或不完整，则取消

事务的特性

  • A：原子性
    • 所有语句作为一个单元全部成功执行或全部取消。
  • C：一致性
    • 如果数据库在事务开始时处于一致状态，则在执行该。 事务期间将保留一致状态。
  • I：隔离性
    • 事务与事务之间是相互隔离的
  • D：持久性
    • 事务成功完成后，所做的所有更改都会准确地记录在数据库中。所做的更改不会丢失。

事务交易流程

image-20230412170702469

SQL语句类别 DTL

Database Trasation Language(数据事务语言)

语法属性

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
# 开启一个事务
begin;
start transaction;

# 提交一个事务
commit;

# 回滚一个事务
rollback;

# 记录事务的点
savepoint
savepoint test1;

# 将事务回滚到记录的点位
rollback to savepoint
rollback to savepoint test1;

# 删除事务点位
release savepoint
release savepoint test10;

注意：只要执行一条DML开始，就已经开启了一个事务，无需执行begin

DML自动commit

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
# DML语句(默认自动开启commit)
insert
update
delete

# 查看是否开启autocommit
show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+

# 关闭自动提交(永久关闭)
vim /etc/my.cnf
[mysqld]
autocommit=0
## 0=off=关闭；1=on=开启

# 临时关闭
set autocommit=0;

事务的生命周期流程

image-20230412172355632

事务的隐式提交

  • 即不执行’commit’或’rollback’的情况下自动提交事务
    • 在事务执行期间，如果执行了DDL或者DCL语句，则自动提交上一个事务
    • 在事务执行期间，如果执行了begin或者start transaction会自动提交上一个事务
    • 在事务执行期间，如果执行了lock或unlock锁表语句，则会自动提交上一个事务
    • 在事务执行期间，如果执行load data infile导出数据，则会提交上一个事务
    • 在事务执行期间，如果执行select for update语句，则会提交上一个事务
    • 在事务执行期间，如果设置了autocommit=1，则会提交上一个事务

Innodb存储引擎核心特性-自动故障恢复（CSR）

redo(重做日志)

  • redo：
    • 顾名思义“重做日志”，是事务日志的一种。
  • 作用：
    • 在事务ACID过程中，实现的是“D”持久化的作用。

image-20230413161154374

redo log恢复数据过程

image-20230413161810548

当我们执行commit之后，会将数据写入redo log
在一段时间内，redo buffer page中的数据会自动写入redo log

1
2
3

数据执行了commit;后断电，表空间数据还未写入，如何自动恢复？
数据没有执行commit;变化过程到指定时间后，也被写入redo log中，如何恢复数据？

undo(回滚日志)

  • undo：
    • 顾名思义“回滚日志”，是事务日志的一种。
  • 作用：
    • 在事务ACID过程中，实现的是“A”原子性的作用。当然CI的特性也和undo有关

redo+undo(CSR)恢复数据过程

  • undo会记录commit的状态

  • 数据执行了commit，data buffer page会写入到磁盘

  • 数据没有执行commit，将数据将会回滚到begin时的状态

image-20230413161716642

redo和undo的存储位置

1
2
3
4
5
6
7
8
9
# redo位置
ll /application/mysql/data/
ib_logfile0
ib_logfile1

# undo位置
ll /application/mysql/data/
ibdata1
ibdata2

Innodb存储引擎核心特性-锁

  • 锁：
    • 顾名思义就是锁定的意思。
  • 作用：
    • 在事务ACID特性过程中，“锁”和“隔离级别”一起来实现“I”隔离性的作用。

锁的力度

  • Innodb：
    • 行级锁(前提：该表中，必须有主键索引)
  • myisam：
    • 表级锁

锁分类

  • 排它锁：
    • 事务执行期间，阻塞其他事务的修改操作
  • 共享锁：
    • 事务执行期间，不阻塞其他事务的查询操作
  • 乐观锁：
    • 事务执行期间，谁先提交以谁为准
  • 悲观锁：
    • 事务执行期间，其他事务无法查看该数据的变化

Innodb存储引擎核心特性-多版本并发控制（MVCC）

多版本并发控制（MVCC）的构成

多版本并发控制是由三个锁构成：

  • 排它锁
    • 只阻塞修改类操作，不阻塞查询类操作
  • 共享锁
    • 例如所有用户可以查询同一表的数据
  • 乐观锁
    • 乐观锁的机制（谁先提交谁为准）

事务的四种隔离级别

  • RU：Read Uncommit（未提交读）
    • 允许事务查看其他事务所进行的未提交更改
  • RC：Read Commit（提交读）
    • 允许事务查看其他事务所进行的已提交更改
  • RR：Repeatable Read(可重复读——InnoDB 的默认级别)**
    • 确保每个事务的 select 输出一致
  • SERIALIZABLE（串行化）
    • 将一个事务的结果与其他事务完全隔离
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
# 查看隔离级别(默认为RR级别)
show variables like '%iso%';
+-----------------------+-----------------+
| Variable_name         | Value           |
+-----------------------+-----------------+
| transaction_isolation | REPEATABLE-READ |
| tx_isolation          | REPEATABLE-READ |
+-----------------------+-----------------+

# 修改隔离级别为RU
[mysqld]
transaction_isolation=read-uncommit

# 修改隔离级别为RC
[mysqld]
transaction_isolation=read-commit

# SERIALIZABLE 串行化
将一个事务的结果与其他事务完全隔离(包括基础查询语句)

# 脏读
脏读是指事务读取到其他事务未提交的数据

# 幻读
所谓幻读，即一个事务在前后两次查询同一个范围的时候，后一次查询看到了前一次查询没有看到的行。
在可重复读隔离级别下，普通的查询是快照读，当前事务是不会看到别的事务插入的数据的。因此，幻读问题在 “当前读” 下才会出现。

企业案例1

项目背景：

公司原有的架构：一个展示型的网站，LAMT，MySQL5.1.77版本（MYISAM），50M数据量。

公司问题：

  1. 表级锁：对表中任意一行数据修改类操作时，整个表都会锁定，对其他行的操作都不能同时进行。
  2. 不支持故障自动恢复（CSR）：当断电时有可能会出现数据损坏或丢失的问题。

如何解决：

  • 提建议将现有的MYISAM引擎替换为Innodb，将版本替换为5.6.38

    • 如果使用MYISAM会产生”小问题”，性能安全不能得到保证，使用innodb可以解决这个问题。
    • 5.1.77版本对于innodb引擎支持不够完善，5.6.38版本对innodb支持非常完善了。

  • 实施过程和注意要素准备新环境

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    # 安装MySQL5.6或5.7

    # mysqldump备份出旧库的所有表
    mysqldump -uroot -p123@qqdianCOM -B world > /tmp/world.sql

    # 替换
    vim /tmp/world.sql
    :%s#ENGINE=MyISAM#ENGINE=InnoDB#g

    # 数据恢复到新环境
    MySQL < /tmp/world.sql

企业案例2

项目背景：

在没有备份数据的情况下，突然断电导致表损坏，打不开数据库。

如何解决：

  1. 准备新环境

    1
    mysqld_safe --defaults-file=/data/3307/my.cnf &

  2. 拷贝库目录到新库中

    1
    cp -r /application/mysql/data/world/ /data/3307/data/

  3. 恢复表

    • 恢复city表

      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      12
      13
      14
      15
      16
      17
      18
      19
      20
      21
      22
      23
      24
      25
      26
      27
      28
      29
      30
      31
      32
      33
      # 用以前的表结构在新库中建表(找DBA或开发要)
      show create table world.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`),
        KEY `idx_city` (`Population`,`CountryCode`),
        CONSTRAINT `city_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `country` (`Code`)
      ) ENGINE=InnoDB AUTO_INCREMENT=4080 DEFAULT CHARSET=latin1;

      # 删除表空间文件
      alter table city_new discard tablespace;

      # 拷贝旧表空间文件
      cp -a city.ibd city_new.ibd

      # 授权
      chown -R mysql.mysql *

      # 导入表空间
      alter table city_new import tablespace;

      # 物理删除损坏的表文件
      rm -fr city.frm
      rm -fr city.ibd

      # 改表名
      alter table city_new rename city;

    • 恢复country表

      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      12
      13
      14
      15
      16
      17
      18
      19
      20
      21
      22
      23
      24
      25
      26
      27
      28
      29
      30
      31
      32
      33
      34
      35
      36
      # 用以前的表结构在新库中建表(找DBA或开发要)
      show create table world.country;
      CREATE TABLE `country_new` (
        `Code` char(3) NOT NULL DEFAULT '',
        `Name` char(52) NOT NULL DEFAULT '',
        `Continent` enum('Asia','Europe','North America','Africa','Oceania','Antarctica','South America') NOT NULL DEFAULT 'Asia',
        `Region` char(26) NOT NULL DEFAULT '',
        `SurfaceArea` float(10,2) NOT NULL DEFAULT '0.00',
        `IndepYear` smallint(6) DEFAULT NULL,
        `Population` int(11) NOT NULL DEFAULT '0',
        `LifeExpectancy` float(3,1) DEFAULT NULL,
        `GNP` float(10,2) DEFAULT NULL,
        `GNPOld` float(10,2) DEFAULT NULL,
        `LocalName` char(45) NOT NULL DEFAULT '',
        `GovernmentForm` char(45) NOT NULL DEFAULT '',
        `HeadOfState` char(60) DEFAULT NULL,
        `Capital` int(11) DEFAULT NULL,
        `Code2` char(2) NOT NULL DEFAULT '',
        PRIMARY KEY (`Code`)
      ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

      # 删除表空间文件
      alter table country_new discard tablespace;

      # 拷贝旧表空间文件
      cp -a country.ibd country_new.ibd

      # 导入表空间
      alter table country_new import tablespace;

      # 物理删除损坏的表文件
      rm -fr country.frm
      rm -fr country.ibd

      # 改表名
      alter table country_new rename country;

    • 恢复countrylanguage表

      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      12
      13
      14
      15
      16
      17
      18
      19
      20
      21
      22
      23
      24
      25
      26
      # 用以前的表结构在新库中建表(找DBA或开发要)
      show create table world.countrylanguage;
      CREATE TABLE `countrylanguage_new` (
        `CountryCode` char(3) NOT NULL DEFAULT '',
        `Language` char(30) NOT NULL DEFAULT '',
        `IsOfficial` enum('T','F') NOT NULL DEFAULT 'F',
        `Percentage` float(4,1) NOT NULL DEFAULT '0.0',
        PRIMARY KEY (`CountryCode`,`Language`),
        KEY `CountryCode` (`CountryCode`)
      ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

      # 删除表空间文件.
      alter table countrylanguage_new discard tablespace;

      # 拷贝旧表空间文件
      cp -a countrylanguage.ibd countrylanguage_new.ibd

      # 导入表空间
      alter table countrylanguage_new import tablespace;

      # 物理删除损坏的表文件
      rm -fr countrylanguage.frm
      rm -fr countrylanguage.ibd

      # 改表名
      alter table countrylanguage_new rename countrylanguage;