MySQL的存储引擎
MySQL的存储引擎
存储引擎介绍
- 文件系统
- 操作系统组织和存取数据的一种机制
- 文件系统是一种软件
- 文件系统
- 不管使用什么文件系统，数据内容不会变化
- 不同的是，存储空间、大小、速度
- mysql引擎
- 可以理解为MySQL的文件系统，只不过功能更加强大
- MySQL引擎功能
- 处理可以提供基本的存取功能，还有更多功能事物功能，锁定，备份和恢复、优化以及特殊功能
总之存储引擎的各项是特效就是为了保证数据库的安全和性能设计结构。
mysql自带的存储引擎类型
mysql提供一下存储引擎：
1.InonDB
2.MyISAM
3.MEMORY
4.ARCHIVE
5.FEDERATED
6.EXAMPLE
7.BLACKHOLE
8.MERGE
9.NDBCLUSTER
10.CSV
# 还可以使用第三方存储引擎
1.MySQL当中的插件式的存储引擎
2.MySQL的两个分支
perconaDB
mariaDB
查看MySQL的存储引擎
# 查看所有的存储引擎
show engines
# 查看库中哪些表示InnoDB的存储引擎
select TABLE_SCHEMA,TABLE_NAME,ENGINE from information_schema.tables where engine='innodb';
# 查看库中哪些表示是Myisam的存储引擎
select TABLE_SCHEMA,TABLE_NAME,ENGINE from information_schema.tables where engine='myisam';
InnoDB和Mylsam的区别
物理区别
# innodb
-rw-r----- 1 mysql mysql 8710 Aug 15 10:05 city.frm # 表结构文件
-rw-r----- 1 mysql mysql 704512 Aug 15 10:05 city.ibd # 数据文件
# myisam
-rw-r----- 1 mysql mysql 10816 Aug 3 01:48 user.frm # 表结构文件
-rw-r----- 1 mysql mysql 456 Aug 9 10:17 user.MYD # 数据文件
-rw-r----- 1 mysql mysql 4096 Aug 9 11:04 user.MYI # 数据文件
逻辑结构
# 在mysql5.5版本之后，默认的存储引擎，提供高可靠性和高性能
优点
1.事物安全
2.MVCC （Multi-Versioning Concurrency Control，多版本并发控制）
3.InnoDB 行级别锁定
4.Orade 样式一致非锁定读取
5.表数据进行整理来优化基于主键的查询
6.支持外键应用完整性约束
7.大小数据卷上的最大性能
8.将对表的查询与不同存储引擎混合
9.出现故障后快速自动回复
10.用于在内存中缓存数据和所有的缓存区池
InnoDB
# 重点
- MVCC
- 事物
- 行级锁
- 热备份
- Crash Safe Recovery (自动故障恢复)
# 查看存储引擎
SELECT @@default_storage_engine;
# 查看建表语句
show create table student;
select TABLE_SCHEMA,TABLE_NAME,ENGINE from information_schema.tables where engine='innodb' and table_name='stu';
show table status like 'stu'\G
设置存储引擎
# 编辑MySQL配置文件
[mysql]
default-storage-engine=<Storage Engine>
default-storage-engine=innodb
default-storage-engine=myisam
# 库内临时设置
create table 表名(id int)engine='存储引擎名';
企业案例
项目背景：
公司原有的架构：一个展示形的网站，LAMT，MySQL5.1.77版本(MYISAM),50M数据量。
小问题不断：
-
表级锁：对表中任意一行数据修改类操作时，整个表都会锁定，对其他行的操作都不能同时操作
-
不支持故障自动恢复(CSR) ：当断电时有可能会出现数据孙华或丢失的问题。
如何解决
- 提议将现有的MYISAM引擎替换为Innodb，将版本替换为5.6.38
- 如果使用MYISAM会产生小问题，性能安全不能得到保障，使用innodb可以结局这个问题
- 5.1.77版本对于innodb引擎支持不够完善，5.6.38版本对innodb支持非常完善
- 实施过程和注意要素
解决思路和过程
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.将备份的数据发送到新环境中
将myisam存储引擎改成innodb
# 方案一
#!/bin/bash
for table in ` mysql -e 'show tables from zls'|awk 'NR>1'`;do
mysql -e "alter table zls.$table engine='myisam'";
done、
# 方案二
mysqldump -B zls > /tmp/zls.sql
:%s@ENGINE=MyISAM@ENGINE=InnoDB@g
7.将数据库导入到新的数据库中
8.将几台web服务器，连接到新库，做测试
9.应用割接
表空间
- 共享表空间
- 独立表空间
5.5版本以后出现共享表空间概念
表空间的管理模式的出现是为了数据库的存储更容易扩展扩展
5.6版本中默认的是独立表空间
[root@db03 ~]# cd /application/mysql/data/
[root@db03 data]# ll
-rw-r----- 1 mysql mysql 12582912 Aug 16 09:52 ibdata1
mysql> show variables like '%path%';
ibdata1:12M:autoextend
共享表空间，初始大小默认：12M：自动扩容
5.6版本中默认存储：
1.系统数据
2.undo
3.临时表
# 共享表空间
-rw-r----- 1 mysql mysql 12582912 Aug 16 09:52 ibdata1
# redo log
-rw-r----- 1 mysql mysql 50331648 Aug 16 09:52 ib_logfile0
# redo log
-rw-r----- 1 mysql mysql 50331648 Aug 3 01:48 ib_logfile1
5.7版本中默认会将undo和临时表独立出来，5.6版本也可以独立，只不过需要在初始化的时候进行配置
# 修改共享表空间
[root@db03 data]# vim /etc/my.cnf
[mysqld]
innodb_data_file_path=ibdata1:12M;ibdata2:50M:autoextend
[root@db03 data]# systemctl restart mysqld
[root@db03 data]# ll -h
-rw-r----- 1 mysql mysql 12M Aug 16 17:21 ibdata1
-rw-r----- 1 mysql mysql 50M Aug 16 09:52 ibdata2
独立表空间
对于用户自主创建的表，会采用此种模式，每一个表由一个独立的表空间进行管理
[root@db03 world]# ll
-rw-r----- 1 mysql mysql 8710 Aug 15 10:05 city.frm # 表结构
-rw-r----- 1 mysql mysql 704512 Aug 15 10:05 city.ibd # 独立表空间
mysql> show variables like '%per_table%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| innodb_file_per_table | ON |
+-----------------------+-------+
string # 查看*.idb文件
企业案例
在没有备份数据的情况下，突然断电导致表损坏，打不开数据库
# 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.删除新表的表空间
mysql> alter table country_new discard tablespace;
# 5.将旧表的表空间，拷贝成新表的表空间
cp -a ../world/country.ibd country_new.ibd
# 6.将新表的表空间导入进去
alter table country_new import tablespace;
# 7.两种解决方案：
- 跟开发说，去改代码，把所有要增删改成country表的代码改成country_new
- 删除已损坏的表，将新表改名为旧表名
rm -f country.ibd
rm -f country.frm
alter table country_new rename country;
# 8.旧业务先停机
###### binlog日志###
# 9.使用binlog截取新环境，恢复到新的环境中
事务
1.什么是事务
# 主要针对DML语句 (update，delete，insert)
一组数据操作执行步骤，这些步骤被视为一个工作单元：
1.用于对多个语句进行分组
2.可以在多个客户机并发访问同一个表中的数据时使用
2.事务
# 伴随着"交易"出现的数据库概念。
我们理解的"交易"是什么？
1.物与物的交换
2.货币现金与实物的交换
3.虚拟货币与实物的交换
4.虚拟货币与虚拟实物交换
3.如何保证在数据库中，事务的"和谐"
靠事务特性来维持
A: 原子性：所有语句作为一个单元全部成功执行或者全部取消
C：一致性：如果数据库在事务开始是处于一致一致状态，则在执行该事务期间将保留一致性
I：隔离性：事务之间不互相影响
D：持久性：事务成功完成后，所做的所有更爱都会准确地记录在数据库中。所做的更爱不会丢失。
4.事务的流程
5.事务的流程控制语句
情况1：只要执行了DML语句，就会开启一个事务
insert update delete
情况2：begin知悉后，会开启一个事务
begin;
start transaction;
start transaction (或 begin) :显示开始一个新事务
savepoint：分配事务过程中的一个位置，以供将来引用
savepoint abc;
commit: 永久记录当前事务所做的更改
rollback：取消当前事务所做的更改
rollback to savepoint：取消在savepoint 之后知悉的更改
rollback to savepoint abc
release savepoint：删除 savepoint 标识
release savepoint abc;
set autocommit: 为当前连接禁用或启用默认 autocommit 模式
set autocommit=1; 开启自动提交(临时)
set autocommit=0; 关闭自动提交(临时)
# 在MySQL中默认开启自动提交
autocommit=1
# 如何关闭自动提交
#临时关闭
set autocommit=0
#永久关闭
vim /etc/my.cnf
[mysqld]
autocommit=0
# 事务的什么周期
# 一个失败的事务生命周期
begin 开启一个事务
DML
DML
...
rollback;
# 一个成功的事务生命周期
begin 开启一个事务
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原理图
redo log 故障恢复过程
undo log
1.提交了，commit，数据保存到磁盘上redo log中了，断电了数据还没写入独立表空间
2.没提交，数据也保存到磁盘上redo log中了，断电了，数据还没有写入独立表空间
3.没有提交，数据也没有保存到 redo log中，断电了，数据还没写入独立表空间
事务中的锁
1.什么是锁
“锁”顾名思义就是锁定的意思。
2.“锁”的作用是什么
在事务ACID特性过程中，“锁”和“隔离级别”一起来实现“I”隔离性的作用。
MySQL中的锁
- 排它锁：在事务操作期间，实现行级锁，保证数据的一致性
- 共享锁：在事务操作期间，其他事务不可以修改数据，但是可以查询数据
- 乐观锁：谁先提交以谁
- 悲观锁：只要事务执行期间，其他事务均无法查询
MVCC多版本并发
- 只阻塞修改类操作，不阻塞查询类操作
- 乐观锁的机制(谁先提交谁为准)
事务的隔离级别
# 查看当前使用的事务级别类型
show variables like '%iso%'
read uncommitted (RU级别：未提交读)
允许事务查看其他书屋所进行的未提交更改
read committed (RC级别：已提交读)
允许事务查看其他事务所进行的已提交更改
repeatable read (RR级别)
确保每个书屋得 select 输出一致
InnoDB 的默认级别
serializable (串行化级别)
将一个事务的结果与其他事务完全隔离
## 修改隔离级别
vim /etc/my.cnf
[mysqld]
autocommit=0
## 未提交读
transaction_isolation=read-uncommit
# 脏读
事务A读取了事务B中尚未提交的数据。如果事务B回滚，则A读取使用了错误的数据。
比如我给你转了100万，但是我还没有提交，此时你查询自己账户，多了100万，很开心。然后我发现转错人了，回滚了事物。然后你100万就没了。 在过程中你查到了没有提交事物的数据（多出的100万），这就是脏读。
解决：如果一个事物在读的时候，禁止读取未提交的事物。是不是就解决了。
# 不可重复读
不可重复读是指在对于数据库中的某个数据，一个事务范围内多次查询却返回了不同的数据值，这是由于在查询间隔，被另一个事务修改并提交了。
不可重复读和脏读的区别是，脏读是某一事务读取了另一个事务未提交的脏数据，而不可重复读则是读取了前一事务提交的数据。
在某些情况下，不可重复读并不是问题，比如我们多次查询某个数据当然以最后查询得到的结果为主。但在另一些情况下就有可能发生问题，例如同一个事物前后两次查询同一个数据，期望两次读的内容是一样的，但是因为读的过程中，因为令一个数据写了该数据，导致不可重复读。（解决：如果 一个事物在读的时候，禁止任何事物写）
# 幻读
在事务A多次读取构成中，事务B对数据进行了新增操作，导致事务A多次读取的数据不一致。
幻读和不可重复读的区别在于，不可重复是针对记录的update操作，只要在记录上加写锁，就可避免；幻读是对记录的insert操作，要禁止幻读必须加上全局的写锁(比如在表上加写锁)。
【推荐】国内首个AI IDE，深度理解中文开发场景，立即下载体验Trae
【推荐】编程新体验，更懂你的AI，立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包，你的智能百科全书，全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell：AI 加持，快人一步
· TypeScript + Deepseek 打造卜卦网站：技术与玄学的结合
· Manus的开源复刻OpenManus初探
· AI 智能体引爆开源社区「GitHub 热点速览」
· 从HTTP原因短语缺失研究HTTP/2和HTTP/3的设计差异
· 三行代码完成国际化适配，妙~啊~