MySQL的存储引擎

存储引擎简介

image.png

  • 1、文件系统
    • 操作系统组织和存取数据的一种机制
    • 文件系统是一种软件
  • 2、文件系统类型:ext2 3 4 ,xfs 数据
    • 不管使用什么文件系统,数据内容不会变化
    • 不同的是,存储空间、大小、速度
  • 3、MySQL引擎:
    • 可以理解为,MySQL的“文件系统”,只不过功能更加强大
  • 4、MySQL引擎功能:
    • 除了可以提供基本的存取功能,还有更多功能事务功能、锁定、备份和恢复、优化以及特殊功能

总之,存储引擎的各项特性就是为了保障数据库的安全和性能设计结构。

MySQL自带的存储引擎类型

MySQL 提供以下存储引擎:

  • InnoDB
  • MyISAM
  • MEMORY
  • ARCHIVE
  • FEDERATED
  • EXAMPLE
  • BLACKHOLE
  • MERGE
  • NDBCLUSTER
  • CSV

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

  • MySQL当中插件式的存储引擎类型
  • MySQL的两个分支
  • perconaDB
  • mariaDB

查看MySQL的存储引擎

## 查看所有的存储引擎
mysql[world]> show engines;
+--------------------+---------+
| Engine             | Support |
+--------------------+---------+
| MRG_MYISAM         | YES     |
| CSV                | YES     |
| InnoDB             | DEFAULT |
| BLACKHOLE          | YES     |
| MyISAM             | YES     |
| PERFORMANCE_SCHEMA | YES     |
| ARCHIVE            | YES     |
| MEMORY             | YES     |
| FEDERATED          | NO      |
+--------------------+---------+

## 查看库中哪些表是InnoDB的存储引擎
mysql> select TABLE_SCHEMA,TABLE_NAME,ENGINE from information_schema.tables where engine='innodb';
+--------------+----------------------+--------+
| TABLE_SCHEMA | TABLE_NAME           | ENGINE |
+--------------+----------------------+--------+
| linux50      | course               | InnoDB |
| linux50      | score                | InnoDB |
| linux50      | student              | InnoDB |
| linux50      | teacher              | InnoDB |
| linux50      | test                 | InnoDB |
| mysql        | innodb_index_stats   | InnoDB |
| mysql        | innodb_table_stats   | InnoDB |
| mysql        | slave_master_info    | InnoDB |
| mysql        | slave_relay_log_info | InnoDB |
| mysql        | slave_worker_info    | InnoDB |
+--------------+----------------------+--------+

## 查看库中哪些表是MyIsam的存储引擎
mysql> select TABLE_SCHEMA,TABLE_NAME,ENGINE from information_schema.tables where engine='myisam';

InnoDB和MyIsam的区别

物理区别

## innodb
[root@db02 data]# cd /application/mysql/data/linux50/
[root@db02 linux50]# ll
total 576
-rw-rw---- 1 mysql mysql   8659 Aug 14 15:36 course.frm	# 表结构文件
-rw-rw---- 1 mysql mysql  98304 Aug 14 16:32 course.ibd	# 数据文件

## myisam
[root@db02 data]# cd /application/mysql/data/mysql/
-rw-rw---- 1 mysql mysql  10684 Aug  2 17:47 user.frm	# 表结构文件
-rw-rw---- 1 mysql mysql    436 Aug  2 17:47 user.MYD	# 数据文件
-rw-rw---- 1 mysql mysql   2048 Aug  2 17:47 user.MYI	# 数据文件

逻辑区别

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

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

image.png

InnoDB核心特性

重点:

  • MVCC
  • 事务
  • 行级锁
  • 热备份
  • Crash Safe Recovery(自动故障恢复)

查看存储引擎

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

## 查看建表语句
mysql> show create table linux50.student;

mysql> select TABLE_SCHEMA,TABLE_NAME,ENGINE from information_schema.tables where engine='innodb' and table_name='student';
+--------------+------------+--------+
| TABLE_SCHEMA | TABLE_NAME | ENGINE |
+--------------+------------+--------+
| linux50      | student    | InnoDB |
+--------------+------------+--------+
1 row in set (0.00 sec)

mysql> show table status like 'stu%'\G
*************************** 1. row ***************************
           Name: student
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 3
 Avg_row_length: 5461
    Data_length: 16384
Max_data_length: 0
   Index_length: 16384
      Data_free: 0
 Auto_increment: 4
    Create_time: 2022-08-14 21:18:56
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.00 sec)

设置存储引擎

## 编辑MySQL配置文件
[root@db02 mysql]# vim /etc/my.cnf
[mysqld]
default-storage-engine=<Storage Engine>
default-storage-engine=innodb
default-storage-engine=myisam

## 建表时指定存储引擎
create table 表名(id int)engine='存储引擎名';

企业案例

项目背景:

公司原有的架构:一个展示型的网站,LAMT,MySQL5.1.77版本(MYISAM),50M数据量。

小问题不断:

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

如何解决:

  • 1、提建议将现有的MYISAM引擎替换为Innodb,将版本替换为5.6.38
    • 1)如果使用MYISAM会产生”小问题”,性能安全不能得到保证,使用innodb可以解决这个问题。
    • 2)5.1.77版本对于innodb引擎支持不够完善,5.6.38版本对innodb支持非常完善了。
  • 2、实施过程和注意要素

解决思路和过程

1.开会讨论,停机维护
2.将操作步骤,提前写在文档中


2.1准备新环境
新的CentOS系统
新的MySQL版本
配置文件优化好
服务启动好

3.关闭所有连接数据库的服务
systemctl stop php-fpm
systemctl stop tomcat

4.停数据库
systemctl stop mysqld

5.备份数据库中的全部数据
mysqldump -A -R --trigger --single-transaction --master-data=1

6.将备份的数据发送到新环境中
scp rsync


将表的myisam存储引擎改成innodb
mysql[zls]> alter table student engine='myisam';
mysql[zls]> alter table student charset='latin1';

## 方案一:
#!/bin/bash

for table in ` mysql -e 'show tables from zls'|awk 'NR>1'`;do
  mysql -e "alter table zls.$table engine='myisam'";
done

## 方案二:
[root@db04 zls]# mysqldump -B zls > /tmp/zls.sql
:%s@ENGINE=MyISAM@ENGINE=InnoDB@g

7.将数据导入到新的数据库中
mysql -uroot -p123 < /tmp/zls.sql

8.将几台web服务器,连接到新库,做测试

9.应用割接

表空间

  • 共享表空间
  • 独立表空间

5.5版本以后出现共享表空间概念

表空间的管理模式的出现是为了数据库的存储更容易扩展

5.6版本中默认的是独立表空间

共享表空间

[root@db01 linux50]# cd /application/mysql/data/
[root@db01 data]# ll ibdata1 
-rw-rw---- 1 mysql mysql 79691776 Aug 16 10:04 ibdata1

mysql> show variables like '%path%';
+----------------------------------+------------------------+
| Variable_name                    | Value                  |
+----------------------------------+------------------------+
| innodb_data_file_path            | ibdata1:12M:autoextend |
| sha256_password_private_key_path | private_key.pem        |
| sha256_password_public_key_path  | public_key.pem         |
| ssl_capath                       |                        |
| ssl_crlpath                      |                        |
+----------------------------------+------------------------+
5 rows in set (0.00 sec)

## 共享表空间,初始大小默认:12M:自动扩容

5.6版本中默认存储:
	1.系统数据
	2.undo
	3.临时表
	
-rw-rw---- 1 mysql mysql 12582912 Aug  9 10:24 ibdata1			# 共享表空间
-rw-rw---- 1 mysql mysql 50331648 Aug  9 10:24 ib_logfile0       # redo log
-rw-rw---- 1 mysql mysql 50331648 Aug  2 14:45 ib_logfile1       # redo log

5.7版本中默认存储:
	1.系统数据	
-rw-r----- 1 mysql mysql 79691776 Aug 15 12:02 ibdata1			# 共享表空间
-rw-r----- 1 mysql mysql 50331648 Aug 15 12:02 ib_logfile0       # redo log
-rw-r----- 1 mysql mysql 50331648 Aug  3 17:10 ib_logfile1       # redo log
-rw-r----- 1 mysql mysql 12582912 Aug 15 12:01 ibtmp1            # 临时表


5.7版本中默认会将undo和临时表独立出来,5.6版本也可以独立,只不过需要在初始化的时候进行配置

## 修改共享表空间
[root@db03 ~]# vim /etc/my.cnf
[mysqld]
innodb_data_file_path=ibdata1:12M;ibdata2:50M:autoextend

[root@db03 ~]# du -sh /application/mysql/data/ibdata1
12M	/application/mysql/data/ibdata1
[root@db03 ~]# du -sh /application/mysql/data/ibdata2
50M	/application/mysql/data/ibdata2

独立表空间

对于用户自主创建的表,会采用此种模式,每个表由一个独立的表空间进行管理

[root@db01 data]# ll world
total 1492
-rw-rw---- 1 mysql mysql   8710 Aug 15 10:07 city.frm	# 表结构
-rw-rw---- 1 mysql mysql 966656 Aug 15 10:07 city.ibd	# 独立表空间
-rw-rw---- 1 mysql mysql   9172 Aug 11 15:20 country.frm
-rw-rw---- 1 mysql mysql 163840 Aug 11 15:20 country.ibd
-rw-rw---- 1 mysql mysql   8702 Aug 11 15:20 countrylanguage.frm
-rw-rw---- 1 mysql mysql 229376 Aug 11 15:20 countrylanguage.ibd
-rw-rw---- 1 mysql mysql     61 Aug 11 15:20 db.opt
-rw-rw---- 1 mysql mysql   8618 Aug 15 15:55 test1.frm
-rw-rw---- 1 mysql mysql 114688 Aug 15 15:55 test1.ibd

## 查看独立表空间是否开启
mysql> show variables like '%per_table%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | ON    |
+-----------------------+-------+
1 row in set (0.00 sec)

## 查看表的二进制文件(只能粗略,不能详细显示)
[root@db01 data]# strings world/city.ibd

企业案例

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

解决思路

# 1.准备新环境,安装完数据库启动

# 2.将data目录备份出来导入新环境

# 3.要知道建表语句,管开发要
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

# 4.删除新表的表空间
alter table world.country_new discard tablespace;

# 5.将旧表的表空间,拷贝成新表的表空间
[root@db02 world]# cp -a country.ibd country_new.ibd


### 光物理拷贝表空间,会报错,country_new表的表空间已经被删除
mysql> select * from country_new;
ERROR 1814 (HY000): Tablespace has been discarded for table 'country_new'

# 6.将新表的表空间,导入进去
mysql> alter table world.country_new import tablespace;

# 7.两种解决方案:
	- 跟开发说,去改代码,把所有要增删改成country表的代码改成country_new
	- 删除已损坏的表,将新表改名为旧表名
	[root@db02 world]# rm -f country.ibd
	[root@db02 world]# rm -f country.frm
	mysql> alter table country_new rename country;

# 8.旧业务先停机
#####################################  binlog日志   ####################################
# 9.使用binlog截取新数据,恢复到新环境中
# 10.业务应用割接到新环境

CREATE TABLE `city_new` (
  `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`)
) ENGINE=InnoDB AUTO_INCREMENT=4080 DEFAULT CHARSET=latin1;

模拟故障

## 将一台MySQL5.6的数据拷贝到另一台5.6中(两个5.7)
[root@m01 data]# scp -r world 172.16.1.52:/application/mysql/data

## 重启数据库
[root@db02 world]# /etc/init.d/mysqld restart

## 连接数据库查询数据
[root@db02 world]# mysql
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
| world              |
| zls                |
+--------------------+
6 rows in set (0.00 sec)

mysql> use world
mysql> show tables;
+-----------------+
| Tables_in_world |
+-----------------+
| city            |
| city_new        |
| country         |
| countrylanguage |
+-----------------+
4 rows in set (0.00 sec)

## 表损坏(库内没有该表的元数据)
mysql> select * from city;
ERROR 1146 (42S02): Table 'world.city' doesn't exist

故障恢复

# 1.准备新环境
CentOS7系统
MySQL5.7

# 2.将data目录导入到新环境

# 3.找开发要建表语句
CREATE TABLE `city_2` (
  `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`)
) ENGINE=InnoDB AUTO_INCREMENT=4080 DEFAULT CHARSET=latin1;

# 3.删除新表的表空间
mysql> alter table city_2 discard tablespace;


# 4.将旧表的表空间拷贝成新表的表空间
[root@db02 world]# cp -a city.ibd city_2.ibd

# 5.进库,导入新表的表空间
mysql> alter table world.city_2 import tablespace;

# 6.删除旧表,将新表改名为旧表
[root@db02 world]# rm -fr city.*
mysql> alter table world.city_2 rename world.city;

# 7.将binlog日志新数据,恢复到新环境
# 8.测试,应用割接

事务

1.什么是事务

主要针对DML语句(update,delete,insert)

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

  • 用于对多个语句进行分组
  • 可以在多个客户机并发访问同一个表中的数据时使用

2.事务的通俗理解

伴随着“交易”出现的数据库概念。

我们理解的“交易”是什么?

  • 物与物的交换(古代)
  • 货币现金与实物的交换(现代1)
  • 虚拟货币与实物的交换(现代2)
  • 虚拟货币与虚拟实物交换(现代3)

3.如何保证在数据库中,事务的''和谐''

靠事务特性,来维持

A:原子性:所有语句作为一个单元全部成功执行或全部取消。

C:一致性:如果数据库在事务开始时处于一致状态,则在执行该。 事务期间将保留一致状态。

I:隔离性:事务之间不相互影响。

D:持久性:事务成功完成后,所做的所有更改都会准确地记录在 数据库中。所做的更改不会丢失。

4.事务的流程

image.png

5.事务的流程控制语句

情况一:只要执行了DML语句,就会开启一个事务
insert update delete

情况二:begin执行后,会开启一个事务

## 开启事务语句
begin;
start transaction;

mysql> create table payment(id int,name varchar(10),money bigint);
mysql> insert into payment value(1,'wzj',1000),(2,'hl',1000);
mysql> select * from payment;
+------+------+-------+
| id   | name | money |
+------+------+-------+
|    1 | wzj  |  1000 |
|    2 | hl   |  1000 |
+------+------+-------+


mysql> update payment set money=500 where name='wzj';

start transaction(或 begin):显式开始一个新事务
savepoint:分配事务过程中的一个位置,以供将来引用
savepoint abc;

commit:永久记录当前事务所做的更改
rollback:取消当前事务所做的更改
rollback to savepoint:取消在 savepoint 之后执行的更改
rollback to savepoint abc;

release savepoint:删除 savepoint 标识符
release savepoint sb_hl;


set autocommit:为当前连接禁用或启用默认 autocommit 模式
set autocommit=1; 开启自动提交(临时)
set autocommit=0; 关闭自动提交(临时)

### 在MySQL中,默认开启自动提交
autocommit=1

### 如何关闭自动提交
## 临时关闭
mysql> set autocommit=0;
## 永久关闭
vim /etc/my.cnf
[mysqld]
autocommit=0


### 事务的生命周期
## 一个失败的事务生命周期
begin 开启一个事务
DML
DML
DML
DML
...
rollback;

## 一个成功的事务生命周期
begin 开启一个事务
DML
DML
DML
DML
...
commit;

事务的隐式提交

1.在上一个事务没有执行完,就执行begin或者start transaction,就会将上一个事务提交
2.在上一个事务没有执行完,就执行DDL和DCL就会隐式提交上一个事务
3.在事务运行期间,执行锁定语句(lock tables、unlock tables)
4.load data infile
5.autocommit=1

事务的日志(CSR自动故障恢复)

  • redo log

redo,顾名思义“重做日志”,是事务日志的一种。

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

MySQL中:WAL Write Ahead Log 日志优先写

redo log原理图

image.png

redo log 故障恢复过程

image.png

image.png

  • undo log image.png

image.png

1.提交了,commit,数据保存到磁盘上redo log中了,断电了数据还没有写入独立表空间

2.没提交,数据也保存到磁盘上redo log中了,断电了,数据还没有写入独立表空间

3.没有提交,数据也没有保存到 redo log中,断电了,数据还没写入独立表空间

事务中的锁

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

image.png

MySQL中的锁:

  • 排它锁:在事务操作期间,实现行级锁,保证数据的一致性
  • 共享锁:在事务操作期间,其它事务不可以修改数据,但是可以查询数据
  • 乐观锁:谁先提交以谁
  • 悲观锁:只要事务执行期间,其它事务均无法查询

MVCC多版本并发控制

  • 只阻塞修改类操作,不阻塞查询类操作
  • 乐观锁的机制(谁先提交谁为准)

事务的隔离级别

## 查看当前使用的事务隔离级别
mysql> show variables like '%iso%';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| tx_isolation  | REPEATABLE-READ |
+---------------+-----------------+


read uncommitted (RU级别:未提交读)
允许事务查看其他事务所进行的未提交更改


read committed (RC级别,已提交读)
允许事务查看其他事务所进行的已提交更改


repeatable read      ****** (RR级别)
确保每个事务的 SELECT 输出一致
InnoDB 的默认级别


serializable  (串行化级别)
将一个事务的结果与其他事务完全隔离


## 修改隔离级别
[root@db02 world]# vim /etc/my.cnf
[mysqld]
autocommit=0
## 未提交读
transaction_isolation=read-uncommit


脏读:事务未提交时,修改的数据会被其它事务查询到,该事务查询的数据是脏数据且不可用的数据
幻读:插入,删除
不可重复读:修改