存储引擎

2020-03-11  本文已影响0人  麟之趾a

简介

相当于linux文件系统，只不过比文件系统更加强大
MySQL支持同一个实例，存在多种存储引擎(于 DB2，sqlserver，oracle都不一样)，是针对表级别设计的

功能了解

存储引擎介绍

MySQL的大致引擎分类

InnoDB（默认）
MyISAM
MEMORY
CSV
第三方存储引擎： ROCKSDB, myROCKS，TokuDB，属于压缩比高，数据插入性能高，其他功能和InnoDB一样。不过MySQL需要插件支持

同源产品存储引擎

perconaDB XtraDB（默认）
MariaDB InnoDB（默认）

InnoDB存储引擎的核心特性说明

存储引擎查看

查看表的存储引擎

show create table 表名

存储引擎修改

alter table 表名 engine='innodb'

存储引擎整理碎片

碎片: 是在做delete时，逐行删除原有数据时，原有数据的空间不会立即释放掉，所以会产生碎片。碎片中的数据越来越多，数据页就会出现间隙，所以索引也会出现间隙。影响查询效率.
alter table city engine='innodb'整理碎片(前提表是innodb引擎)，会有短暂的锁表
另一种方法：先把表数据导出来，truncate原先的表，再表导入进去（最干净，没有碎片）

批量替换zabbix小表innodb为tokudb

select concat('alter table',table_schema,'.',table_name,'engine=tokudb') from information_schema.tables where table_schema='zabbix'

InnoDB 存储的物理结构（最直观的存储方式/data/mysql/data）

表空间（tablespace）

共享表空间（ibdata1~ibdataN）

MySQL5.5: 默认共享表空间模式，所有的数据都存在共享表空间里
MySQL5.6: 转换为独立表空间，共享表空间只用来存储数据字典，undo日志，临时表
MySQL5.7: 临时表被独立出去
MySQL8.0: undo日志也被独立出去
查看共享表空间

mysql> select @@innodb_data_file_path;
+-------------------------+
| @@innodb_data_file_path |
+-------------------------+
| ibdata1:12M:autoextend  |
+-------------------------+
表示初始值一个共享表空间，初始大小12M，自动增长，每次增长64M
共享表空间，可以在MySQL初始化时指定，如指定两个共享表空间文件，先把ibdata1用完，在用ibdata2

独立表空间（ibd）

从5.6开始，默认表空间不再使用共享表空间，替换为独立表空间。
存储特点：一个表一个ibd文件，存储数据行+ 索引信息
基本表结构元数据存储： xx.frm

最终结论

一张innodb表=ibd+ibdata1+frm

MySQL存储日志

独立表空间设置问题

+-------------------------+
| @@innodb_file_per_table |
+-------------------------+
|                       1 |
+-------------------------+
默认为1，表示1个表，代表一个独立的表空间文件

独立表空间迁移

alter table t100 discard tablespace;   //清楚t100表空间数据
alter table t100 import tablespace;    //导入t100 ibd数据

独立表空间迁移步骤

1 .建立和原表结构一致的表
2 .将空表的idb文件删除alter table t100 discard tablespace
3 .将原先表的ibd文件，拷贝，当前的数据目录下，并修改权限
4 .将原表的数据进行导入alter table t100 import tablespace

innodb的核心特性

事务

事务的ACID特性

事务就是保证交易在计算机（程序）中的完整性

事务的生命周期（标准的事务控制语句）

在线修改autocommit参数

set autocommit = 0      //会话级别（只影响当前会话，及时生效）
set global autocommit =0  //全局级别（窗口断开，重连，才会生效）

配置文件修改

vim /etc/my.cnf
autocommit = 0

事务的隐式提交

同一个会话内
begin 开始
DML 
DML
[commit]  触发
[set 命令] 触发
[DDL]   触发
[DCL] 触发
[锁定语句 lock tables和 unlock tables] 触发
[begin]  触发

事务的ACID如何保证

一些概念名词
redo日志

作用： 主要保证D(持久性） 还为 A （原子性）和 C （一致性）也有作用

  1. CSR过程中实现前滚操作（磁盘数据页和redo日志中的LSN号要保持一致）

因为data buffer pool 是将ibd中所要修改行的中所在的整个数据页加载上来，进行修改，然后把整个数据页重新写入磁盘。如果进行大量的DML 语句，会加载大量的数据页，写入磁盘时，会造成磁盘IO瓶颈，所以采用redo日志的方式，记录行的变化，写入到磁盘。等数据页达到内存的一个限定值时，就会写入到磁盘

启动MySQL，会检查redo 日志中的LSN 和 ibd中的LSN是否一致，如果不一致，则重新加载idb文件内容，和redo日志中的内容，进行自动故障恢复（ASCR）

自动故障恢复ASCR

1.先把脏页重新构建出来（重新加载idb文件内容，和redo日志中的内容到内存，构建脏页）
2 .构建出脏页后，立即触发ckpt

redo的刷写策略

commit 将redo buffer刷写到redo日志上（redo buffer中可能存在未提交的事务，也会被刷写到redo日志中）

undo

作用： 在ACID特性中，主要保证A（原子性）的特性，同时对C（一致性）和 I（隔离性）也有一定功效
记录了什么

当redo日志，存在未提交事务时，会给此事务（TXID）打上未提交的标签，然后对比undo日志的TXID，从undo日志加载出来原先数据，到undo buffer中，进行回滚。最终保持了，ibd和redo日志中的LSN号一致

锁

实现了事务之间的隔离功能
innodb中实现了行级锁
row-level lock
gap 锁
next-lock

隔离级别

Innodb核心参数设置

# 存储引擎设置
default_storage_engine=innodb
# 表空间模式设置
innodb_file_per_table=1     //设置为独立表空间
# 共享表空间文件个数和大小
innodb_data_file_path=ibdata1:512M:ibdata2:512M:autoextend
# 双一标准其中一个
innodb_flush_log_at_trx_commit=1
# 控制redo buffer和data buffer pool的刷写策略
innodb_flush_method=O_DIRECT
# redo 日志相关
innodb_log_buffer_size=16777216 (redo buffer 大小)
innodb_log_file=50331648     (innodb文件大小)
innodb_log_files_in_group=3 (redo 日志的个数)
# 脏页刷写策略
innodb_max_dirty_pages_pct=75
最大脏页占用内存比例75%，即data buffer 写入到data的限制
还有哪些机制触发写磁盘？
CSR(自动故障恢复)
redo 满了
image.png

在追求性能的结构中，可以设置为innodb_flush_at_trx_commit=0 ,比如监控系统。

redo buffer 和data buffer 刷写策略

fsync（默认）

fsync.png

fsync是由redo buffer和data buffer pool 刷到os buffer，再由os buffer 刷到 data。因为redo buffer 和data buffer pool 都是 在内存，fsync这种模式可能会给内存造成压力

O_DIRECT（推荐）

image.png

O_DIRECT是redo buffer直接刷到data，data buffer pool先刷到os buffer，再由os buffer 刷到data

O_DSYNC

image.png

O_DSYNC是把redo buffer直接刷到 data，data buffer pool 刷到os buffer，再由os buffer 刷到data

最高安全模式

innodb_flush_log_at_trx_commit=1
innodb_flush_method=O_DIRECT

最高性能模式

innodb_flush_log_at_trx_commit=1
innodb_flush_method=fsync

概述redo日志和undo日志

redo 日志

undo日志

上一篇下一篇

猜你喜欢

热点阅读