mysql 引擎是表级别_MySQL的存储引擎

MySQL的存储引擎

相当于Linux文件系统，只不过比文件系统强大

数据读写

数据安全和一致性

提高性能

热备份

自动故障恢复

高可用方面支持

存储引擎种类介绍

#查看支持的搜索引擎类型

mysql> show engines;

CSV

MRG_MYISAM

MyISAM

BLACKHOLE

PERFORMANCE_SCHEMA

MEMORY

ARCHIVE

InnoDB

FEDERATED

#第三方引擎类型

RocksDB

MyRocks

TokuDB

压缩比较高,数据的插入性能高.其他功能和InnoDB没差.

存储引擎查看

#查看支持的搜索引擎类型

mysql> show engines;

#查看mysql默认的存储引擎

mysql> select @@default_storage_engine;

#设置存储引擎

[root@mysql ~]# vim /etc/my.cnf

[mysqld]

default_storage_engine=InnoDB

#查看表存储引擎状态

mysql> show create table stu;

mysql> show table status like 'stu'\G

mysql> select table_schema,table_name ,engine from information_schema.tables where table_schema not in ('sys','mysql','information_schema','performance_schema');

存储引擎的修改

#修改存储引擎

mysql> alter table stu engine=InnoDB;

#整理碎片

mysql> alter table stu engine=InnoDB;

InnoDB存储引擎物理存储结构

ibdata1：系统数据字典信息(统计信息)，UNDO表空间等数据

ib_logfile0 ~ ib_logfile1: REDO日志文件，事务日志文件。

ibtmp1： 临时表空间磁盘位置，存储临时表

frm：存储表的列信息

ibd：表的数据行和索引

表空间(Tablespace)

#表空间数据问题

ibdata1 : 整个库的统计信息+Undo

ibd : 数据行和索引

共享表空间(ibdata1~N)

5.5版本: 用户表数据和索引,系统数据字典信息,undo信息(回滚信息),临时表信息等.

5.6版本: 将用户数据和索引分离.剩下:系统数据字典信息,undo信息(回滚信息),临时表信息等.

5.7版本: 将临时表独立,剩下:系统数据字典信息,undo信息(回滚信息).人为可以undo信息独立

8.0版本: undo也独立了,数据字典只存一份.共享表空间会慢慢解耦了

共享表空间设置

共享表空间设置(在搭建MySQL时，初始化数据之前设置到参数文件中)

[(none)]>select @@innodb_data_file_path;

[(none)]>show variables like '%extend%';

innodb_data_file_path=ibdata1:512M:ibdata2:512M:autoextend

innodb_autoextend_increment=64

独立表空间

从5.6，默认表空间不再使用共享表空间，替换为独立表空间。

主要存储的是用户数据

存储特点为：一个表一个ibd文件，存储数据行和索引信息

基本表结构元数据存储：

xxx.frm

最终结论：

元数据 数据行+索引

mysql表数据 =(ibdataX+frm)+ibd(段、区、页)

DDL DML+DQL

MySQL的存储引擎日志：

Redo Log: ib_logfile0 ib_logfile1，重做日志

Undo Log: ibdata1 ibdata2(存储在共享表空间中)，回滚日志

临时表:ibtmp1，在做join union操作产生临时数据，用完就自动

独立表空间设置问题

db01 [(none)]>select @@innodb_file_per_table;

+-------------------------+

| @@innodb_file_per_table |

+-------------------------+

| 1 |

+-------------------------+

alter table city dicard tablespace;

alter table city import tablespace;

InnoDB核心特性

事物的ACID特性

Atomic(原子性)

不可再分的特性.一个事务就是一个执行单元,不可再分.必须同时成功或失败.

Consistent(一致性)

在一个事务工作单元中,不管是开始时,进行中,结束后,必须数据保证最终一致.

Isolated(隔离性)

多个事务之间工作互不影响.

Durable(持久性)

所有事务结束,必须保证"数据"落盘.

事务的生命周期(事务控制语句)

#如何开启事务

mysql> begin;

#标准的事务语句

DML : insert update delete

mysql> use world;

mysql> update city set countrycode='CHN' where id=1;

mysql> update city set countrycode='CHN' where id=2;

mysql> update city set countrycode='CHN' where id=3;

#事务的结束

提交 : commit;

mysql> commit;

回滚 : rollback;

mysql> rollback;

#自动提交机制(autocommit)

在线修改参数:

(1) 会话级别:

mysql> set autocommit=0;

及时生效,只影响当前登录会话

(2)全局级别:

mysql> set global autocommit=0;

断开窗口重连后生效,影响到所有新开的会话

(3)永久修改(重启生效)

vim /etc/my.cnf

autocommit=0

#隐式提交的情况

导致提交的非事务语句：

DDL语句： (ALTER、CREATE 和 DROP)

DCL语句： (GRANT、REVOKE 和 SET PASSWORD)

锁定语句：(LOCK TABLES 和 UNLOCK TABLES)

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

TRUNCATE TABLE

LOAD DATA INFILE

SELECT FOR UPDATE

SET

隐式回滚:

死锁

会话关闭

概念名词

#redo log:重做日志

ib_logfile0~1默认50M轮询使用

#redo log buffer:redo内存区域

#data pages 数据页(16K)

#data buffer pool:缓冲区池,数据和索引的缓冲

#undo log 回滚日志

#LSN:日志序列号

磁盘数据页,redo文件,buffer pool,redo buffer

MySQL每次数据库启动,都会比较磁盘数据页和redolog的LSN,必须要求两者LSN一致数据库才能正常启动

#WAL(持久化):

write ahead log 日志优先写的方式实现持久化

日志是优先与数据写入磁盘的

#dirty page 脏页

内存脏页,内存种发生了修改,没写入到磁盘之前,我们把内存页称之为脏页

#CKPT

Checkpoint,检查点,就是将脏页刷写到磁盘的动作

#TXID

事务号,InnoDB会为每一个事务生成一个事务号,伴随着整个事务

事务日志--redo

作用:在ACID特性中保证，实现的是“D”持久化的作用

(1)记录了内存数据页的变化.

(2)提供快速的持久化功能(WAL)

(3)ACSR过程中实现前滚的操作(磁盘数据页和redo日志LSN一致)

#redo日志位置

redo的日志文件：iblogfile0 iblogfile1

#redo buffer

redo的buffer:数据页的变化信息+数据页当时的LSN号

#redo的刷写策略

commit;

刷新当前事务的redo buffer到磁盘

还会顺便将一部分redo buffer中没有提交的事务日志也刷新到磁盘

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

#断电导致事务未提交情况

我们做了一个事务,begin;update;commit.

1.在begin ,会立即分配一个TXID=tx_01.

2.update时,会将需要修改的数据页(dp_01,LSN=101),加载到data buffer中

3.DBWR线程,会进行dp_01数据页修改更新,并更新LSN=102

4.LOGBWR日志写线程,会将dp_01数据页的变化+LSN+TXID存储到redobuffer

5. 执行commit时,LGWR日志写线程会将redobuffer信息写入redolog日志文件中,基于WAL原则,

在日志完全写入磁盘后,commit命令才执行成功,(会将此日志打上commit标记)

6.假如此时宕机,内存脏页没有来得及写入磁盘,内存数据全部丢失

7.MySQL再次重启时,必须要redolog和磁盘数据页的LSN是一致的.但是,此时dp_01,TXID=tx_01磁盘是LSN=101,dp_01,TXID=tx_01,redolog中LSN=102

MySQL此时无法正常启动,MySQL触发ACSR.在内存追平LSN号,触发ckpt,将内存数据页更新到磁盘,从而保证磁盘数据页和redolog LSN一值.这时MySQL正长启动

以上的工作过程,我们把它称之为基于REDO的"前滚操作"

回滚日志-undo

作用:在ACID特性中保证,主要保证A的特性,同时对CI也有一定功效

(1)记录了数据修改之前的状态

(2)rollback 将内存的数据修改恢复到修改之前

(3)在ACSR中实现未提交数据的回滚操作

(4)实现一致性快照,配合隔离级别保证MVCC,读和写的操作不会互相阻塞

MVCC ---> undo 快照

锁

实现了事务之间的隔离功能,InnoDB中实现的是行级锁

row-level lock:行级锁定

gap:间隙锁

next lock:下键锁

隔离级别

RU : 读未提交

RC : 读已提交

RR : 可重复读 (默认)

SR : 可串行化

(3) RU级别

读未提交的数据,也叫脏读.

transaction_isolation=READ-UNCOMMITTED

会出现的问题:脏读 , 不可重复读 , 幻读

(5) RC级别

读已提交.保证读到的数据起码是commit过的数据.

transaction_isolation=READ-COMMITTED

会出现的问题: 不可重复读 和 幻读现象.

(6) RR级别

可重复读 ,在同一个会话中,查询同一条数据,不会出现多种值.

transaction_isolation=REPEATABLE-READ

有可能会出现:幻读现象.

利用Undo的快照技术(MVCC),防止不可重复读现象

GAP锁(间隙)+next lock ,RR级别下防止幻读的现象

MVCC: 每次开启一个事务恢复,都会利用UNDO技术,生成一个一致性快照,直到事务结束.

通过MVCC,可以实现一致性快照读,在RR级别实现可重复读功能

在RC级别下,应用的是当前快照读技术,读取的都是当前时间点的最新快照.

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

0 : 每秒刷os cache,同时flushed到磁盘

1 : 每次事务commit,刷os cache,同时flushed到磁盘

2 : 每次事务commit,刷os cache,1秒flushed到磁盘

Innodb_flush_method=(O_DIRECT, fsync)

作用: 控制的是 Redo buffer 和 buffer pool

O_Direct :

数据页刷新磁盘的时候,穿过OS Cache,直接刷磁盘,建议云盘或SSD,或者高速存储

redo buffer , 先刷 os cache,再刷磁盘

fsync :

数据页和redo buffer 在刷新时都OS buffer ,再到磁盘

#最高安全模式

innodb_flush_log_at_trx_commit=1

Innodb_flush_method=O_DIRECT

#最高性能:

innodb_flush_log_at_trx_commit=0

Innodb_flush_method=fsync

#redo日志设置有关的

innodb_log_buffer_size=16777216

innodb_log_file_size=50331648

innodb_log_files_in_group = 3

#脏页刷写策略

innodb_max_dirty_pages_pct=75

标签：存储,redo,MySQL,引擎,mysql,磁盘,日志,数据

来源： https://www.cnblogs.com/opesn/p/12994025.html