MySQL 存储引擎介绍
文件系统
操作系统组织和存取数据的一种机制
文件系统是一种软件
文件系统类型
ext2 ext3 ext4 xfs 数据
不管使用什么文件系统, 数据内容不会变化
不同的是, 存储空间大小速度
MySQL 引擎
可以将 MySQL 引擎理解为: MySQL 的文件系统, 只不过功能更加强大
MySQL 引擎的功能
除了可以提供基本的存取功能, 还有更多功能事务功能锁定备份和恢复优化以及特殊功能
MySQL 提供以下存储引擎:
- InnoDB
- MyISAM
- MEMORY
- ARCHIVE
- FEDERATED
- EXAMPLE
- BLACKHOLE
- MERGE
- NDBCLUSTER
- CSV
注: 只有 innodb 与 myisam 最常用
Innodb 存储引擎简介
在 MySQL5.5 版本之后, 默认的存储引擎, 提供高可靠性和高性能
Innodb 引擎的优点
事务安全(遵从 ACID)
MVCC(Multi-Versioning Concurrency Control, 多版本并发控制):InnoDB 行级锁 Oracle 样式一致非锁定读取(共享锁)
表数据进行整理来优化基于主键的查询(聚集索引)
支持外键引用完整性约束
大型数据卷上的最大性能
将对表的查询与不同存储引擎混合
出现故障后快速自动恢复(crash safe recovery)
用于在内存中缓存数据和索引的缓冲区池(buffer pool(data buffer page log buffer page) undo buffer page)
查看数据库的存储引擎设置
使用 SELECT 确认会话存储引擎:
- SELECT @@default_storage_engine;
- show variables like '%engine%';
使用 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
设置存储引擎
基本不需要修改设置存储引擎
在启动配置文件中设置服务器存储引擎:
- [mysqld]
- default-storage-engine=<Storage Engine>
使用 SET 命令为当前客户机会话设置:
SET @@storage_engine=<Storage Engine>;
在 CREATE TABLE 语句指定:
CREATE TABLE t (i INT) ENGINE = <Storage Engine>;
从 5.1 版本迁移到 5.5 以上版本的 mysql 注意修改存储引擎
假如 5.1 所有生产表都是 myisam 的
使用 mysqldump 备份后, 一定要替换备份的文件中的 engine 字段从 myisam 到 innodb
否则迁移就没有意义
Innodb 体系结构
物理存储结构(表空间):
InnoDB 系统表空间
默认情况下, InnoDB 元数据撤消日志和缓冲区存储在系统表空间中
表空间: MySQL 数据库存储的方式
表空间中包含数据文件
MySQL 表空间和数据文件是 1:1 的关系
共享表空间除外, 是可以 1:N 关系
表空间类型
1 共享表空间: ibdata1~ibdataN, 一般是 2-3 个
2 独立表空间: 存放在指定库目录下
例如 data/world / 目录下的 city.ibd 表空间位置(datadir):data / 目录下
系统表空间的存储内容
共享表空间的物理存储结构(ibdata1~N), 也通常被叫做系统表空间, 是数据库初始化生成的
1 系统元数据, 基表数据, 除了表内容数据之外的数据
2undo 日志 (回滚日志) 数据
3tmp 表空间(一般很少关注)
ib_logfile0~N(redo 日志)
存放的是 innodb 表的重做日志
注释:
undo 日志默认是在 ibdata 中的, 在 5.6 以后是可以单独定义的
tmp 表空间在 5.7 版本以后也被移出了 ibdata1,ibtmp1
在 5.5 版本以前, 所有的应用数据也都默认存放到了 ibdata 中
数据独立表空间
除了系统表空间之外, InnoDB 还在数据库目录中创建另外的表空间, 用于每个 InnoDB 表的 .ibd 文件
InnoDB 创建的每个新表在数据库目录中设置一个 .ibd 文件来搭配表的 .frm 文件
在 5.6 以后, 默认的情况下, 会单表单独存储到独立表空间文件中
独立表空间设置:
show variables like '%per_table%';
在参数文件 / etc/my.cnf 可以控制独立表空间功能是否开启, 5.6 默认开启的
innodb_file_per_table=1 ---->开启独立表空间, 单表单存储
innodb_file_per_table=0 ---->关闭独立表空间, 所有数据存放到 ibdata 中
设置共享表空间
通过添加数据文件增加表空间大小
在 my.cnf 文件中使用 innodb_data_file_path 选项
- [mysqld]
- innodb_data_file_path=datafile_spec1[;datafile_spec2]
配置共享表空间实例
配置示例: 创建一个表空间, 其中包含一个名为 ibdata1 且大小为 12 MB (固定)的数据文件和一个名为 ibdata2 且大小为 100 MB(自动扩展)的数据文件:
一般是在初始搭建环境的时候就定义好, 一般 2-3 个共享表空间文件
预设值 1G, 最后一个文件自动扩展
- vi /etc/my.cnf
- innodb_data_file_path=ibdata1:12M;ibdata2:100M:autoextend
默认情况下将文件放置在 data 目录中
如果需要, 显式指定文件位置
Innodb 引擎事务
事务生命周期图
事务 ACID
A --- Atomic(原子性)
所有语句作为一个单元全部成功执行或全部取消
例如:
update t1 set money=10000-17 where id = 个人微信号
update t1 set money=1000000+17 where id = 二连长的微信号
以上语句都成功了, 才能把产品给你
C --- Consistent(一致性)
如果数据库在事务开始时处于一致状态, 则在执行该事务期间将保留一致状态
例如:
update t1 set money=10000-17 where id = 个人微信号
update t1 set money=1000000+17 where id = 二连长的微信号
在以上操作过程没有完全成功情况下, 你去查自己的账户, 应该还是 10000 块
I --- Isolated(隔离性)
事务之间不相互影响
例如:
update t1 set money=10000-17 where id = 个人微信号
update t1 set money=1000000+17 where id = 二连长的微信号
1 在做以上操作的时候, 其他人是不能对这两个账户做任何的取款, 存款操作
2 在不同的隔离条件下, 可能一致性保证又不一样
隔离级别会影响到一致性
read-uncommit 做了操作就显示结果了
read-commit 可能会用的一种级别,
repeatable-read 默认级别, 和 oracle 一样,
serializable 严格模式, 一般不用
D --- Durable(持久性)
事务成功完成后, 所做的所有更改都会准确地记录在
数据库中所做的更改不会丢失
事务的控制语句
START TRANSACTION(或 BEGIN): 显式开始一个新事务
SAVEPOINT: 分配事务过程中的一个位置, 以供将来引用
COMMIT: 永久记录当前事务所做的更改
ROLLBACK: 取消当前事务所做的更改
ROLLBACK TO SAVEPOINT: 取消在 savepoint 之后执行的更改
RELEASE SAVEPOINT: 删除 savepoint 标识符
SET AUTOCOMMIT: 为当前连接禁用或启用默认 autocommit 模式
主要:
begin 事务开始的标记
commit 事务成功提交的标记
rollback 事务回滚的标记
autocommit 设置, 控制是否每条 DML 语句自动提交, 生产中要关掉 autocommit=0; 也就是设置为手工提交事务的模式;
设置为 1, 开启自动提交的优点: 数据安全性好, 每次修改都会落地
缺点:
1 不能进行银行类的交易事务
2 产生大量的小 IO
我们可以通过以下命令进行修改关闭(0 是关闭, 1 是开启)
SET GLOBAL AUTOCOMMIT=0; --- 所有新建会话
SET SESSION AUTOCOMMIT=0; --- 当前会话
SELECT @@AUTOCOMMIT; --- 查看设置结果
我们也可以修改配置文件让其永久生效
- vi /etc/my.cnf
- [mysqld]
- autocommit=0
- redo
redo, 顾名思义重做日志, 是事务日志的一种
作用: 在事务 ACID 过程中, 实现的是 D 持久化的作用
先将数据提取到内存中进行操作, 在修改完成后, redo 记录数据变化的过程, 就算事务的完成, 然后就算是断电事务没来的及写入磁盘, 下次开启数据库 redo 也会先执行修改数据
innodb_flush_log_at_trx_commit ---- 此参数控制着事务提交的时候刷新 redo 日志
- mysql> show variables like '%commit%';
- +--------------------------------+-------+
- | Variable_name | Value |
- +--------------------------------+-------+
- | autocommit | ON |
- | binlog_order_commits | ON |
- | innodb_api_bk_commit_interval | 5 |
- | innodb_commit_concurrency | 0 |
- | innodb_flush_log_at_trx_commit | 1 |
- +--------------------------------+-------+
- 5 rows in set (0.00 sec)
undo
undo, 顾名思义回滚日志, 是事务日志的一种
作用: 在事务 ACID 过程中, 实现的是 AC 原子性和一致性的作用
在将数据提取到内存打算修改数据的时候, undo 会将数据没改的时候做一个快照, 然后如果有一方失败, 事务就会返回致原样
事务中的锁
在事务 ACID 过程中, 锁和隔离级别一起来实现 I 隔离性的作用
四种隔离级别: 建议使用 REPEATABLE READ
READ UNCOMMITTED 允许事务查看其他事务所进行的未提交更改
READ COMMITTED 允许事务查看其他事务所进行的已提交更改
REPEATABLE READ 确保每个事务的 SELECT 输出一致(InnoDB 的默认级别)
SERIALIZABLE 将一个事务的结果与其他事务完全隔离
来源: https://www.cnblogs.com/lyq863987322/p/8490034.html