MySQL-存储引擎

1、简介

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

2、功能了解

数据读写

数据安全

提高性能

热备份

自动故障恢复

高可用方面支持

3、存储引擎种类（笔试）

3.1 介绍

InnoDB

MyISAM

MEMORY

ARCHIVE

FEDERATED

EXAMPLE

BLACKHOLE

MERGE

NDBCLUSTER

CSV

MySQL支持的存储引擎种类查看：

show engines;

存储引擎是作用在表上的，也就意味着，不同的表可以有不同的存储引擎类型。

PerconaDB:XtraDB

MariaDB:

TokuDB ------> zabbix

扩展:

TokuDB:insert数据比Innodb快的多，数据压缩比要Innodb高

Myrocks

3.2 简历案例（存储引擎替换）

环境: zabbix 3.2 mariaDB 5.5 centos 7.3

现象 : zabbix卡的要死 , 每隔3-4个月,都要重新搭建一遍zabbix

问题 :

1. zabbix 版本

2. 数据库版本

3. zabbix数据库500G,存在一个文件里

优化建议:

1.版本升级到5.7版本

2.存储引擎改为tokudb

3.监控数据按月份进行切割

4.关闭binlog

5.参数调整…

优化结果:

监控状态良好

为什么?

1. 原生态支持tokudb,另外经过测试环境,5.7要比5.5 版本性能 高 2-3倍

2. TokuDB:insert数据比Innodb快的多，数据压缩比要Innodb高

3.监控数据按月份进行切割,为了能够truncate每个分区表,立即释放空间

4.关闭binlog ----->减少无关日志的记录

5.参数调整…----->安全性参数关闭,提高性能

---

4、InnoDB存储引擎介绍

在MySQL5.5版本之后，默认的存储引擎，提供高可靠性和高性能。

4.1 优点

事务安全（遵从 ACID）

MVCC（Multi-Versioning Concurrency Control，多版本并发控制）

InnoDB 行级别锁定

Oracle 样式一致非锁定读取

表数据进行整理来优化基于主键的查询

支持外键引用完整性约束

大型数据卷上的最大性能

将对表的查询与不同存储引擎混合

出现故障后快速自动恢复

用于在内存中缓存数据和索引的缓冲区池

---

4.2 笔试题

请你列举MySQL InnoDB存储优点？

请你列举 InooDB和MyIsam的区别？

InnoDB：

1、事务（Transaction）

2、MVCC（Multi-Version Concurrency Control多版本并发控制）

3、行级锁(Row-level Lock)

4、ACSR（Auto Crash Safe Recovery）自动的故障安全恢复

5、支持热备份(Hot backup)

6、Replication: Group Commit , GTID (Global Transaction ID) ,多线程(SQL Multi-Threads)

5、存储引擎查看

5.1 使用 SELECT 确认会话存储引擎

SELECT @@default_storage_engine;

5.2 存储引擎(不代表生产操作)

会话级别:

set default_storage_engine=myisam;

全局级别(仅影响新会话):

set global default_storage_engine=myisam;

重启之后,所有参数均失效.

如果要永久生效:

写入配置文件

vim /etc/my.cnf

[mysqld]

default_storage_engine=myisam

存储引擎是表级别的,每个表创建时可以指定不同的存储引擎,但是我们建议统一为innodb.

5.3 SHOW 确认每个表的存储引擎：

SHOW CREATE TABLE City\G;

SHOW TABLE STATUS LIKE ‘CountryLanguage’\G

5.4 INFORMATION_SCHEMA 确认每个表的存储引擎

Master [world]>select table_schema,table_name ,engine from information_schema.tables where engine=‘innodb’;

Master [world]>show table status;

Master [world]>show create table city;

5.5 修改一个表的存储引擎

db01 [oldboy]>alter table t1 engine innodb;

注意：此命令我们经常使用他，进行innodb表的碎片整理

5.6 扩展:如何批量修改

需求:将zabbix库中的所有表,innodb替换为tokudb

select concat(“alter table zabbix.”,table_name," engine tokudb;") from

information_schema.tables where table_schema=‘zabbix’ into outfile ‘/tmp/tokudb.sql’;

注:

此命令既可以修改存储引擎类型,也可以自动分析整理innodb表的随便,避开业务繁忙期.

alter table t1 engine innodb;

怎么判断有大量的碎片?

提示:表真实的空间占用和磁盘的ibd文件比较

(1) 真正占用空间大小

SELECT table_schema,SUM(AVG_ROW_LENGTH * TABLE_ROWS+ INDEX_LENGTH)/1024 AS Total_KB

FROM information_schema.tables

GROUP BY table_schema;

(2) 磁盘方面查看IBD文件占用的磁盘空间

du -sh

ll -h

5.7 InnoDB与MyISAM存储引擎区别（面试题）

InnoDB支持事务，支持行级别锁，支持热备，支持自动故障恢复，MVCC

MyISAM 不支持事务，支持表级锁，支持温备份，不支持自动故障恢复和MVCC

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

6.0 最直观的存储方式(/data/mysql/data)

ibdata1：系统元数据表，undo表空间等数据

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

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

frm：存储表的列信息

ibd：表的数据行和索引

6.1 表空间(Tablespace)

6.1.1、共享表空间

需要将所有数据存储到同一个表空间中 ，管理比较混乱

5.5版本出现的管理模式，也是默认的管理模式。

5.6版本以后，共享表空间保留，只用来存储，系统表相关数据,undo,临时表。

6.1.2 共享表空间设置

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

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

6.1.3 独立表空间

从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操作产生临时数据，用完就自动

6.1.4 独立表空间设置问题

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

±------------------------+

| @@innodb_file_per_table |

±------------------------+

| 1 |

±------------------------+

alter table city dicard tablespace;

alter table city import tablespace;

---

6.1.5 真实的学生案例

开发用户

jira(bug追踪) 、 confluence(内部知识库) ------>LNMT

联想服务器（IBM）

磁盘500G 没有raid

centos 6.8

mysql 5.6.33 innodb引擎 独立表空间

备份没有，日志也没开

---

编译→制作rpm

/usr/bin/mysql

/var/lib/mysql

confulence jira

所有软件和数据都在"/"

---

断电了，启动完成后“/” 只读

fsck 重启

启动mysql启动不了。

结果：confulence库在 ， jira库不见了

---

求助：

这种情况怎么恢复？

我问：

有备份没

求助：

连二进制日志都没有，没有备份，没有主从

我说：

没招了，jira需要硬盘恢复了。

求助：

1、jira问题拉倒中关村了

2、能不能暂时把confulence库先打开用着

将生产库confulence，拷贝到1:1虚拟机上/var/lib/mysql,直接访问时访问不了的

问：有没有工具能直接读取ibd

我说：我查查，最后发现没有

CREATE TABLE t1 (

stuid int(11) NOT NULL,

stuname varchar(20) NOT NULL,

stusex char(1) NOT NULL,

cardid varchar(20) NOT NULL,

birthday datetime DEFAULT NULL,

entertime datetime DEFAULT NULL,

address varchar(100) DEFAULT NULL,

PRIMARY KEY (stuid),

KEY idx_name (stuname)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;

我想出一个办法来：

create table xxx

alter table confulence.t1 discard tablespace;

alter table confulence.t1 import tablespace;

虚拟机测试可行。

CREATE TABLE city_new (

ID int(11) NOT NULL AUTO_INCREMENT,

Name char(35) NOT NULL DEFAULT ‘’,

CountryCode char(3) NOT NULL DEFAULT ‘’,

District char(20) NOT NULL DEFAULT ‘’,

Population int(11) NOT NULL DEFAULT ‘0’,

PRIMARY KEY (ID),

KEY CountryCode (CountryCode),

KEY idx_popu (Population)

) ENGINE=InnoDB AUTO_INCREMENT=4080 DEFAULT CHARSET=latin1;

面临的问题，confulence库中一共有107张表。

1、创建107和和原来一模一样的表。

他有2016年的历史库，我让他去他同时电脑上 mysqldump备份confulence库

mysqldump -uroot -ppassw0rd -B confulence --no-data >test.sql

拿到你的测试库，进行恢复

到这步为止，表结构有了。

2、表空间删除。

select concat(‘alter table ‘,table_schema,’.‘table_name,’ discard tablespace;’) from information_schema.tables where table_schema=‘confluence’ into outfile ‘/tmp/discad.sql’;

source /tmp/discard.sql

执行过程中发现，有20-30个表无法成功。主外键关系

很绝望，一个表一个表分析表结构，很痛苦。

set foreign_key_checks=0 跳过外键检查。

把有问题的表表空间也删掉了。

3、拷贝生产中confulence库下的所有表的ibd文件拷贝到准备好的环境中

select concat(‘alter table ‘,table_schema,’.‘table_name,’ import tablespace;’) from information_schema.tables where table_schema=‘confluence’ into outfile ‘/tmp/discad.sql’;

4、验证数据

表都可以访问了，数据挽回到了出现问题时刻的状态（2-8）

---

故障案例模拟

（1）获得原数据库表的结构（创建语句）

<1>通过历史备份

<2>和开发一起来完成

模拟——我们直接 show create table t1;

CREATE TABLE t1 (

stuid int(11) NOT NULL,

stuname varchar(20) NOT NULL,

stusex char(1) NOT NULL,

cardid varchar(20) NOT NULL,

birthday datetime DEFAULT NULL,

entertime datetime DEFAULT NULL,

address varchar(100) DEFAULT NULL,

PRIMARY KEY (stuid),

KEY idx_stuname (stuname)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

(2)搭建一个1:1的测试数据库

mysql -S /data/3307/mysql.sock

(3)在测试库中，恢复表结构

mysql -S /data/3307/mysql.sock

create database oldboy;

CREATE TABLE t1 (

stuid int(11) NOT NULL,

stuname varchar(20) NOT NULL,

stusex char(1) NOT NULL,

cardid varchar(20) NOT NULL,

birthday datetime DEFAULT NULL,

entertime datetime DEFAULT NULL,

address varchar(100) DEFAULT NULL,

PRIMARY KEY (stuid),

KEY idx_stuname (stuname)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

（4）删除空的ibd文件

alter table t1 discard tablespace;

（5）使用源库的ibd导入

cp /usr/local/mysql/mydata/oldboy/t1.ibd /data/3307/data/oldboy/

chown -R mysql.mysql /data/*

alter table t1 import tablespace;

文件句柄

rm -rf

inode 位图索引 标记改为可覆盖状态

7、事务简介 （OLTP，在线事务处理系统）*****

事务：主要是针对DML（insert、update、delete）来进行控制的。

DML1

DML2

DML3

以上三条语句,必须同时成功,或同时失败

8、事务的ACID特性

Atomic（原子性）

所有语句作为一个单元全部成功执行或全部取消。不能出现中间状态。

Consistent（一致性）

如果数据库在事务开始时处于一致状态，则在执行该事务期间将保留一致状态。

Isolated（隔离性）

事务之间不相互影响。

Durable（持久性）

事务成功完成后，所做的所有更改都会准确地记录在数据库中。所做的更改不会丢失。

9、事务的生命周期（事务控制语句）

9.1 事务的开始

begin

说明:在5.5 以上的版本，不需要手工begin，只要你执行的是一个DML，会自动在前面加一个begin命令。

9.2 事务的结束

commit：提交事务

完成一个事务，一旦事务提交成功 ，就说明具备ACID特性了。

rollback ：回滚事务

将内存中，已执行过的操作，回滚回去

9.3 自动提交策略（autocommit）

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

db01 [(none)]>set autocommit=0;

db01 [(none)]>set global autocommit=0;

注：

自动提交是否打开，一般在有事务需求的MySQL中，将其关闭

不管有没有事务需求，我们一般也都建议设置为0，可以很大程度上提高数据库性能

(1)

set autocommit=0;

set global autocommit=0;

(2)

vim /etc/my.cnf

autocommit=0

9.4 隐式提交语句

用于隐式提交的 SQL 语句：

begin

a

b

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

========================

开始事务流程：

1、检查autocommit是否为关闭状态

select @@autocommit;

或者：

show variables like ‘autocommit’;

2、开启事务,并结束事务

begin

delete from student where name=‘alexsb’;

update student set name=‘alexsb’ where name=‘alex’;

rollback;

---

begin

delete from student where name=‘alexsb’;

update student set name=‘alexsb’ where name=‘alex’;

commit;

10. InnoDB 事务的ACID如何保证?

10.1 redo log

10.1.1 Redo是什么？

redo,顾名思义“重做日志”，是事务日志的一种。

10.1.2 作用是什么？

在事务ACID过程中，实现的是“D”持久化的作用。对于AC也有相应的作用

10.1.3 redo日志位置

redo的日志文件：iblogfile0 iblogfile1

10.1.4 redo buffer

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

LSN：日志序列号 磁盘数据页、内存数据页、redo buffer、redolog

10.1.5 redo的刷新策略

commit;

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

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

10.1.6 MySQL CSR——前滚

MySQL : 在启动时,必须保证redo日志文件和数据文件LSN必须一致,

如果不一致就会触发CSR,最终保证一致

redo 日志文件是轮询使用

11.2 undo 回滚日志

11.2.1 undo是什么？

undo,顾名思义“回滚日志”

11.2.2 作用是什么？

在事务ACID过程中，实现的是“A” 原子性的作用

另外CI也依赖于Undo

11.3 概念性的东西:

redo怎么应用的

undo怎么应用的

CSR(自动故障恢复)过程

LSN :日志序列号

TXID:事务ID

CKPT(Checkpoint)

11.4 锁

“锁”顾名思义就是锁定的意思。

“锁”的作用是什么？

在事务ACID过程中，“锁”和“隔离级别”一起来实现“I”隔离性和"C" 一致性 (redo也有参与).

悲观锁:行级锁定(行锁)

谁先操作某个数据行,就会持有<这行>的(X)锁.

乐观锁: 没有锁

11.5 隔离级别

影响到数据的读取,默认的级别是 RR模式.

RU(read uncommited) : 读未提交(脏读)

transaction_isolation=read-uncommit

事务没提交,其他事务也可以看到修改后的数据(脏页读)

此种隔离级别，会造成脏读和幻读。

RC : 读已提交

事务已提交,其他事务才可以可以看到修改后的数据(会出现幻读)

防止了脏读，会有幻读，非金融性的业务此种级别足够

RR : 可重复读

不管是事务执行前,事务执行中,事务结束后,同一个会话一定读到的是同一个数据,MySQL默认的隔离级别.必须支持事务(undo快照),必须有索引,可以防止幻读,读一致性的目的.

防止脏读和幻读，通过一致性快照实现（undo）。

S : 可串行化

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

12.1 存储引擎相关

12.1.1 查看

show engines;

show variables like ‘default_storage_engine’;

select @@default_storage_engine;

12.1.2 如何指定和修改存储引擎
(1) 通过参数设置默认引擎

(2) 建表的时候进行设置

(3) alter table t1 engine=innodb;

12.2. 表空间
12.2.1 共享表空间
innodb_data_file_path

一般是在初始化数据之前就设置好

例子:

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

12.2.2 独立表空间

show variables like ‘innodb_file_per_table’;

12.3. 缓冲区池

12.3.1 查询

select @@innodb_buffer_pool_size;

show engine innodb status\G

innodb_buffer_pool_size

一般建议最多是物理内存的 75-80%

12.4. innodb_flush_log_at_trx_commit (双一标准之一)
12.4.1 作用
主要控制了innodb将log buffer中的数据写入日志文件并flush磁盘的时间点，取值分别为0、1、2三个。

12.4.2 查询

select @@innodb_flush_log_at_trx_commit;

12.4.3 参数说明:

1，每次事物的提交都会引起日志文件写入、flush磁盘的操作，确保了事务的ACID；

flush 到操作系统的文件系统缓存 fsync到物理磁盘

0，表示当事务提交时，不做日志写入操作，而是每秒钟将log buffer中的数据写入日志文件并flush磁盘一次；

2，每次事务提交引起写入日志文件的动作,但每秒钟完成一次flush磁盘操作。

12.5. Innodb_flush_method=(O_DIRECT, fdatasync)

12.5.1 作用

控制的是,log buffer 和data buffer,刷写磁盘的时候是否经过文件系统缓存

12.5.2 查看

show variables like ‘%innodb_flush%’;

12.5.3 参数值说明

O_DIRECT :数据缓冲区写磁盘,不走OS buffer

fdatasync :日志和数据缓冲区写磁盘,都走OS buffer

O_DSYNC :日志缓冲区写磁盘,不走 OS buffer

12.5.4 使用建议

最高安全模式

innodb_flush_log_at_trx_commit=1

Innodb_flush_method=O_DIRECT

最高性能:

innodb_flush_log_at_trx_commit=0

Innodb_flush_method=fdatasync

12.6. redo日志有关的参数

innodb_log_buffer_size=16777216

innodb_log_file_size=50331648

innodb_log_files_in_group = 3

13.扩展(自己扩展，建议是官方文档。)

GAP

next-lock

RR模式(对索引进行删除时):

GAP: 间隙锁

next-lock: 下一件锁定

id（有索引）

1 2 3 4 5 6

GAP：

在对3这个值做变更时，会产生两种锁，一种是本行的行级锁，另一种会在2和4索引键上进行枷锁

next-lock：

对第六行变更时，一种是本行的行级锁，在索引末尾键进行加锁，6以后的值在这时是不能被插入的。

总之：

GAP、next lock都是为了保证RR模式下，不会出现幻读，降低隔离级别或取消索引，这两种锁都不会产生。

IX IS X S