第五章 MySQL 存储引擎

最新推荐文章于 2024-05-04 17:01:12 发布

飞鸡起飞

最新推荐文章于 2024-05-04 17:01:12 发布

阅读量248

点赞数

本文链接：https://blog.csdn.net/weixin_45367149/article/details/103120956

版权

1. 介绍

相当于Linux中的文件系统.组织存储数据.

2. 存储引擎种类

mysql> show engines;
InnoDB 
MyISAM 
CSV
Memory
....

MariaDB : InnoDB,TokuDB,Myrocks
percona : xtraDB
TokuDB : 插入性能高,压缩比高
Myrocks : 插入性能高,压缩比高

3. InnoDB存储引擎核心特性

MVCC: 多版本并发控制***
聚簇索引
事务 *****
行级锁
Insert buffer
自适应HASH索引
热备
ACSR:自动故障恢复 *****
主从: 支持一些新特性,GTID等.

4. 存储引擎的查看和配置

#### 查看支持的存储引擎
mysql> show engines;  
mysql> show create table city;
#### 查看系统默认的存储引擎
mysql> select @@default_storage_engine;   
mysql> show variables like '%engine%';
#### 修改存储引擎及inonDB碎片整理  *******
mysql> alter table t_engine engine=innodb;

降低水位线:
https://www.fuwuqizhijia.com/mysql/201704/37836.html

5. 存储引擎替换案例

环境: centos 5.8 ,MySQL 5.0版本,MyISAM存储引擎,网站业务(LNMP),数据量50G左右
现象问题: 业务压力大的时候,非常卡;经历过宕机,会有部分数据丢失.
- 问题分析:
  1.MyISAM存储引擎表级锁,在高并发时,会有很高锁等待
  2.MyISAM存储引擎不支持事务,在断电时,会有可能丢失数据
解决方案:
1.升级MySQL 5.6.10版本
2.迁移所有表到新环境
3.开启双1安全参数

6. InnoDB存储引擎物理存储结构

6.1 宏观
ib_buffer_pool : 关闭数据库时,自动保存bufferpool缓冲热数据文件.
ibdata1 : 系统表空间数据文件. undo日志数据,数据字典
ib_logfile0 : 重做日志文件(redo log)
ib_logfile1 :重做日志文件(redo log)
ibtmp1 : 临时表空间文件
t.frm : 表结构
t.ibd : 数据和索引
6.2 表空间介绍
- 6.2.1 共享表空间
  MySQL 5.5版本加入的功能(ibdata1 自动扩容 ), 作为InnoDB默认数据存储方式.
- 6.2.2 独立表空间
  MySQL 5.6 版本中,作为默认数据存储方式.
  官网: https://dev.mysql.com/doc/refman/8.0/en/innodb-architecture.html
- 6.2.3 共享表空间设置
```
innodb_data_file_path=ibdata1:512M;ibdata2:512M:autoextend 
```
- 6.2.5 独立表空间设置
```
mysql> select @@innodb_file_per_table;
```
- 6.2.6 表空间迁移
  1.新环境安装MySQL,启动
  2.新环境创建一模一样结构的表
  3.alter table t discard tablespace;
  4.原环境的ibd文件拷贝过来
  5.alter table t import tablespace;

7. InnoDB 的核心特性详解–事务

伴随着交易类的业务,出现的概念.

7.1 什么是事务的ACID
- Atomic（原子性）同一个事务中的所有语句,必须同时成功或同时失败(commit,rollback).
- Consistent（一致性）一个事务过程中的数据,在事务开始前,中,后都将保证一致性.
- Isolated（隔离性）事务发生过程中操作的数据,和其他事务进行隔离
- Durable（持久性）事务完成后，数据从内存写入到磁盘中
7.2 事务生命周期控制

7.2.1 标准事务控制语句

#### 格式
begin;  / start transaction;   开始事务
DML语句
DML语句 
commit;     提交
rollback;   回滚

7.2.2 特殊的控制语句

db01 [(none)]>select @@autocommit;
db01 [(none)]>show variables like 'autocommit';
#### 设置自动是否提交事务   
set global autocommit=0;
vim /etc/my.cnf
autocommit=0

7.3 隐式提交语句

用于隐式提交的 SQL 语句：

begin 
DML
DML
begin
SET AUTOCOMMIT = 1

导致提交的非事务语句：

DDL语句： （ALTER、CREATE 和 DROP）
DCL语句： （GRANT、REVOKE 和 SET PASSWORD）
锁定语句：（LOCK TABLES 和 UNLOCK TABLES）

导致隐式提交的语句示例：

TRUNCATE TABLE
LOAD DATA INFILE
SELECT FOR UPDATE

7.5 隐式回滚语句

会话断开 (exit , kill,连接窗口关闭)
死锁 
关机
异常宕机

8. 事务的ACID如何保证的?

8.1 名词介绍
- redo : 重做日志 , 保存数据页的变化 (ib_logfile0~1)
- redo buffer : 日志缓冲区
- undo : 回滚日志 : ibdata1
- datafile : city.ibd
- data buffer pool: 数据缓冲区,数据页(数据行和索引)
- LSN : Log seq no. 日志序列号.
- 脏页: 未写入磁盘的被修改的内存数据页
- CKPT: checkpoint ,刷写内存脏页到磁盘
- TXID: 每个事务开始时,会分配一个唯一事务编号.
- WAL: write ahead log,日志优先写.

8.2 redo

作用: 记录数据页变化日志. 主要用来保证ACID 中的D的特性 ,AC特性也有保证.

8.2.1 文件位置

数据路径下,ib_logfile0~N
innodb_log_file_size=128M   # 重做日志文件大写.建议256M
innodb_log_files_in_group=5   # 重做日志文件个数,建议2~5个

8.2.2 redo有关的内存

mysql> show variables like '%log%';
innodb_log_buffer_size   # 重做日志缓冲区大小
innodb_flush_log_at_trx_commit=1   #控制redo什么时候往磁盘上写.

8.2.3 redo 功能文字描述

MySQL : 在启动时,必须保证redo日志文件和数据文件LSN必须一致, 如果不一致就会触发CSR,最终保证一致
执行事务的流程:

我们做了一个事务,begin;update;commit.
1.在begin ,会立即分配一个TXID=tx_01.
2.update时,会将需要修改的数据页(dp_01,LSN=101),加载到data buffer中
3.IO线程,会进行dp_01数据页修改更新,并更新LSN=102
4.日志写线程,会将dp_01数据页的变化+LSN+TXID存储到redo buffer
5. 执行commit时,日志写线程会将redo buffer信息写入redo log日志文件中,基于WAL原则,在日志完全写入磁盘后,commit命令才执行成功,(会将此日志打上commit标记)
6.假如此时宕机,内存脏页没有来得及写入磁盘,内存数据全部丢失
7.MySQL再次重启时,必须要redo log和磁盘数据页的LSN是一致的.
但是,此时dp_01,TXID=tx_01磁盘是LSN=101,dp_01,TXID=tx_01,redo log中LSN=102
MySQL此时无法正常启动,MySQL触发A.CSR.在内存追平LSN号,触发CKPT,将内存数据页更新到磁盘,从而保证磁盘数据页和redo log LSN一致.这时MySQL正常启动
以上的工作过程,我们把它称之为基于 REDO 的"前滚操作"

补充:

MySQL redo具备预写入功能,刷写redo buffer 到磁盘时, 满足已经提交的时候(innodb_flush_log_at_trx_commit=1 ),
会立即触发写入磁盘,一些未提交的的redo buffer日志,也会被连带着被刷写磁盘.
此时,redo log就会存在多种状态的事务日志.
怎么区分?
通过标签不同来区分此日志对应的事务是否提交.
8.3 undo日志
作用: 记录的是事务的反操作 ,主要用来保证ACID 中的A的特性,CI也有作用.
在CSR实现的是,将redo当中记录的未提交的时候进行回滚
- 8.3.1 快照功能
  undo可以实现针对不同时间点版本快照.对MVCC提供功能保证.
8.5 锁
作用: InnoDB支持行锁,保证事务更新某行数据时,不会受到其他事务影响.保证了ACID中I的特性.

IX, X ,IS,S
GAP 锁 , Next-lock锁 : 为了解决在RR (MySQL的隔离级别),防止幻读.

8.6 隔离级别
作用: 影响的是读数据的隔离.增删改查都要涉及到读数据.
```
#### 查看隔离级别
mysql> select @@tx_isolation;
```
- 8.6.1 种类
  - RU（read-uncommitted） : 读未提交,可脏读,一般不允许出现.
  - RC（read-committed） : 读已提交,可能出现幻读,可以防止脏读.有可能出现"不可重复读"和"幻读".
  - RR（repeatable-read）(默认) : 可重复读 ,利用的是undo的快照技术防止"不可重复读"现象,行锁+GAP(间隙锁)=NextLock(下键锁)一同防止"幻读".
  - SR （serializable） : 可串行化,可以防止死锁,但是并发事务性能较差

9. InnoDB存储引擎核心特性-参数补充

9.1 默认存储引擎查看

show engines;
show variables like 'default_storage_engine';
select @@default_storage_engine;

9.2 修改表的存储引擎

(1) 通过参数设置默认引擎
(2) 建表的时候进行设置
(3) alter table t1 engine=innodb;

9.3 共享表空间设置

innodb_data_file_path
一般是在初始化数据之前就设置好
例子:
innodb_data_file_path=ibdata1:512M:ibdata2:512M:autoextend

9.5 独立表空间

show variables like 'innodb_file_per_table';

9.6 缓冲区池

mysql> select @@innodb_buffer_pool_size;
生产一般不要超过75%
生产故障:
内存OOM,问题: buffer设置过大.

9.7 “双1标准” 之一

innodb_flush_log_at_trx_commit=1   ******
作用: 控制redo log 什么时候刷写磁盘.
1:   每次事务commit; 都会立即将redo刷写os cache,并立即刷写到磁盘
0 :  每秒钟完成,redo刷写os cache,并立即刷写到磁盘
2:   每次事务commit; 都会立即将redo刷写os cache,并每秒写入磁盘一次.

9.8 控制数据

innodb_flush_method  ******
作用: 
1. 控制数据刷盘
2. 控制redo刷盘
O_DIRECT  :   buffer pool 刷盘是跨过文件系统缓存,直接写磁盘,redo buffer 先写 os cache在写磁盘
FSYNC     :   buffer pool ,redo buffer 先写 os cache在写磁盘

O_DIRECT模式一般配合SSD盘或flash更佳.

9.9 redo相关

innodb_log_file_size=256M
innodb_log_files_in_group =4 
innodb_log_buffer_size=2G

10. 表空间迁移案例

案例—zabbix监控系统架构整改
环境: zabbix 3.2 mariaDB 5.5 centos 7.3
现象 : zabbix卡的要死 , 每隔3-4个月,都要重新搭建一遍zabbix,存储空间经常爆满.
问题 :
1.zabbix 版本
2 数据库版本
3.zabbix数据库500G,存在一个文件里

优化建议:
1.数据库版本升级到5.7版本,zabbix升级更高版本
2.存储引擎改为tokudb
3.监控数据按月份进行切割(二次开发:zabbix 数据保留机制功能重写,数据库分表)
4.关闭binlog和双1
5.参数调整....
优化结果:
监控状态良好

为什么?
1. 原生态支持TokuDB,另外经过测试环境,10.x要比5.5 版本性能 高  2-3倍
2. TokuDB:insert数据比Innodb快的多，数据压缩比要Innodb高
3.监控数据按月份进行切割,为了能够truncate每个分区表,立即释放空间
4.关闭binlog ----->减少无关日志的记录.
5.参数调整...----->安全性参数关闭,提高性能.

案例:
背景:
centos 6.8 系统, MySQL:5.5.40版本,默认共享表空间.数据量:600G左右
所有数据都存储在了ibdata1文件中 ,查询管理效率低(IO,CPU负载高).
解决思路:
1. 迁移升级 MySQL 5.5.40 到 5.7.20
2. 所有业务表调整为InnoDB
3. 确认所有表都是独立表空间
  结果:
  IO , CPU负载受到控制.性能提升.

(1) 生产事故:表空间迁移实现故障恢复
背景:
硬件及软件环境:联想服务器（IBM）磁盘500G 没有raid
centos 6.8 + mysql 5.6.33 : innodb引擎独立表空间
备份没有，日志也没开,开发用户专用库:
jira(bug追踪) 、 confluence(内部知识库) ------>LNMT
故障描述:
1. 经历断电.
2. 第二天,发现jira和confluence无法访问.
3. 检查系统,"/" 只读,提示fsck进行修复

解决流程:
1. 开发自己按照屏幕提示执行fsck. 系统正常启动,MySQL无法启动.
2. 找运维解决.通过检查：confulence库在 ，jira库不见了
3. confulence库能否临时用着.
4. 尝试 将confulence库 拷贝到 测试环境,发现用不了.
5. 有没有一款工具可以直接读取ibd文件?  ----> 没有 
6. 尝试用表空间迁移方法.   ----> 测试成功.
7.  confluence库下有107张表.

information_schema拼接语句
8.  alter table t discard tablespace; 107次 
9.  alter table t import tablespace;  107次 

10. 执行过程中发现，有20-30个表无法成功。
主外键关系,很绝望，一个表一个表分析表结构，很痛苦。
set foreign_key_checks=0 跳过外键检查。
把有问题的表表空间也删掉了。

飞鸡起飞

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
第五章 MySQL 存储引擎

1. 介绍相当于Linux中的文件系统.组织存储数据.2. 存储引擎种类mysql> show engines;InnoDB MyISAM CSVMemory....MariaDB : InnoDB,TokuDB,Myrockspercona : xtraDBTokuDB : 插入性能高,压缩比高Myrocks : 插入性能高,压缩比高3. Inno...
复制链接

扫一扫