1. 存储引擎简介

相当于Linux文件系统(组织存储数据)，只不过比文件系统强大

功能
数据读写
数据安全和一致性
提高性能
热备份
自动故障恢复
高可用方面支持
等.

2. 存储引擎种类（Oracle MySQL）面试题

2.1 介绍

InnoDB
MyISAM
MEMORY
ARCHIVE
FEDERATED
EXAMPLE
BLACKHOLE
MERGE
NDBCLUSTER
CSV

2.2 存储引擎种类查看

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

MariaDB : InnoDB,TokuDB,Myrocks 
percona : xtraDB

PerconaDB:默认是XtraDB
MariaDB:默认是InnoDB
其他的存储引擎支持:
TokuDB    
RocksDB
MyRocks
以上三种存储引擎的共同点:压缩比较高,数据插入性能极高
现在很多的NewSQL,使用比较多的功能特性.

2.3 简历案例---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,另外经过测试环境,5.7要比5.5 版本性能 高  2-3倍
2. TokuDB:insert数据比Innodb快的多，数据压缩比要Innodb高
3.监控数据按月份进行切割,为了能够truncate每个分区表,立即释放空间
4.关闭binlog ----->减少无关日志的记录.
5.参数调整...----->安全性参数关闭,提高性能.

3 InnoDB存储引擎核心特性

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

**请你列举MySQL InnoDB存储引擎优点？ （如上）
**请你列举 InooDB和MyIsam的区别？（如上）

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

3.1 优点

1、事务（Transaction）
2、MVCC（Multi-Version Concurrency Control多版本并发控制）
3、行级锁(Row-level Lock)
4、ACSR（Auto Crash Safey Recovery）自动的故障安全恢复
5、支持热备份(Hot Backup)
6、Replication: Group Commit , GTID (Global Transaction ID) ,多线程(Multi-Threads-SQL ) 

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

mysql> show engines;
mysql> show create table city;
mysql> select @@default_storage_engine;  **默认的存储引擎**
mysql> show variables like '%engine%';

mysql> alter table t_engine engine=innodb;
功能说明:
    1. 修改存储引擎类型
    2. InnoDB碎片整理.

5. 存储引擎替换案例

环境: centos 5.8 ,MySQL 5.0版本,MyISAM存储引擎,网站业务(LNMP),数据量50G左右
现象问题: 业务压力大的时候,非常卡;经历过宕机,会有部分数据丢失.
问题分析:
1.MyISAM存储引擎表级锁,在高并发时,会有很高锁等待
2.MyISAM存储引擎不支持事务,在断电时,会有可能丢失数据
职责
1.监控锁的情况:有很多的表锁等待
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
ibtmp1          :  临时表空间文件
t.frm           
t.ibd

6.2 表空间介绍（tablespace）

6.2.1共享表空间

需要将所有数据存储到同一个表空间中 ，管理比较混乱
5.5版本出现的管理模式，也是默认的管理模式。
5.6版本以，共享表空间保留，只用来存储:数据字典信息,undo,临时表。
5.7 版本,临时表被独立出来了
8.0版本,undo也被独立出去了

具体变化参考官方文档:
https://dev.mysql.com/doc/refman/5.6/en/innodb-architecture.html
https://dev.mysql.com/doc/refman/5.7/en/innodb-architecture.html
https://dev.mysql.com/doc/refman/8.0/en/innodb-architecture.html

6.2.2 共享表空间设置

共享表空间设置(在搭建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

6.2.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.2.4 独立表空间设置

db01 [(none)]>select @@innodb_file_per_table;
+-------------------------+
| @@innodb_file_per_table |
+-------------------------+
|                      1 |
+-------------------------+

6.2.5表空间迁移

alter table city discard tablespace;   **删除表空间
alter table city import tablespace;   **导入表空间

使用方法:  
    1. 新环境安装MySQL,启动
    2. 新环境创建一模一样结构的表
    3. alter table t discard tablespace;
    4. 原环境的ibd文件拷贝过来
    5. alter table t import tablespace;

6.1.6 真实生产事故案例

案例背景：

硬件及软件环境:
联想服务器（IBM） 
磁盘500G 没有raid
centos 6.8
mysql 5.6.33  innodb引擎  独立表空间
备份没有，日志也没开

开发用户专用库:
jira(bug追踪) 、 confluence(内部知识库)    ------>LNMT

故障描述：

断电了，启动完成后“/” 只读
fsck  重启,系统成功启动,mysql启动不了。
结果：confulence库在  ， jira库不见了

求助郭导：

这种情况怎么恢复？
郭导问：
有备份没
求助：
连二进制日志都没有，没有备份，没有主从
郭导说：
没招了，jira需要硬盘恢复了。
求助：
1、jira问题拉倒中关村了
2、能不能暂时把confulence库先打开用着
将生产库confulence，拷贝到1:1虚拟机上/var/lib/mysql,直接访问时访问不了的

学员问：有没有工具能直接读取ibd
郭导说：我查查，最后发现没有

郭导想出解决办法：

表空间迁移:
create table xxx
alter table  confulence.t1 discard tablespace;
alter table confulence.t1 import tablespace;
虚拟机测试可行。

解决问题思路：

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）

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

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

7.1 事务的ACID特性

Atomic（原子性）

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

Consistent（一致性）

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

Isolated（隔离性）

事务之间不相互影响。

Durable（持久性）

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

7.2 事务的生命周期

7.2.1 标准事务控制语句

begin;  / start transaction;   开始事务
DML 
DML 
commit;                          提交
rollback;                        回滚

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

标准事务语句 : DML语句

begin;
update 
update
commit;

7.2.2 特殊的控制语句（自动提交策略）

begin; 
a 
b 
commit;

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     

7.2.3 隐式提交语句

用于隐式提交的 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

7.2.4 隐式回滚情况

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

8 InnoDB 事务的ACID如何保证?

名词概念介绍

redo log ---> 重做日志 ib_logfile0~1   50M   , 轮询使用
redo log buffer ---> redo内存区域
ibd     ----> 存储 数据行和索引 
buffer pool --->缓冲区池,数据和索引的缓冲
LSN : 日志序列号 
磁盘数据页,redo文件,buffer pool,redo buffer
MySQL 每次数据库启动,都会比较磁盘数据页和redolog的LSN,必须要求两者LSN一致数据库才能正常启动
WAL : write ahead log 日志优先写的方式实现持久化
脏页: 内存脏页,内存中发生了修改,没写入到磁盘之前,我们把内存页称之为脏页.
CKPT:Checkpoint,检查点,就是将脏页刷写到磁盘的动作
TXID: 事务号,InnoDB会为每一个事务生成一个事务号,伴随着整个事务.

image.png

7.1 relog

7.1.1 relog是什么

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

7.1.2 作用

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

7.1.3 文件位置

数据路径下,ib_logfile0~N

damao[(none)]>show variables like '%innodb_log_file%';
+---------------------------+----------+
| Variable_name             | Value    |
+---------------------------+----------+
| innodb_log_file_size      | 50331648 |
| innodb_log_files_in_group | 2        |
+---------------------------+----------+
2 rows in set (0.00 sec)
 

7.1.4 redo有关的内存

redo buffer
innodb_log_buffer_size   大小
innodb_flush_log_at_trx_commit=1   控制redo什么时候往磁盘上写.“双1之1”

damao[(none)]>show variables like 'innodb_log_buffer_size';
+------------------------+----------+
| Variable_name          | Value    |
+------------------------+----------+
| innodb_log_buffer_size | 16777216 |
+------------------------+----------+
1 row in set (0.00 sec)

damao[(none)]>show variables like 'innodb_flush_log_at_trx_commit';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| innodb_flush_log_at_trx_commit | 1     |
+--------------------------------+-------+
1 row in set (0.00 sec)

7.1.5 redo的刷新策略

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

7.1.6 redo功能描述（MySQL CSR——前滚）

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触发CSR.在内存追平LSN号,触发ckpt,将内存数据页更新到磁盘,从而保证磁盘数据页和redolog LSN一值.这时MySQL正长启动
以上的工作过程,我们把它称之为基于REDO的"前滚操作"

说明

MySQL redo具备预写入功能,刷写redo buffer 到磁盘时, 满足已经提交的时候(innodb_flush_log_at_trx_commit=1  ),
    会立即触发写入磁盘,一些未提交的的redo buffer日志,也会被连带着被刷写磁盘.
    此时,redo log就会存在多种状态的事务日志.
    怎么区分?
        通过标签不同来区分此日志对应的事务是否提交

7.2 undo回滚日志

7.2.1 undo是什么？

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

7.2.2 回滚日志的作用

在事务ACID过程中，实现的是“A” 原子性的作用
另外CI也依赖于Undo
在rolback时,将数据恢复到修改之前的状态
在CSR实现的是,将redo当中记录的未提交的时候进行回滚.
undo提供快照技术,保存事务修改之前的数据状态.保证了MVCC,隔离性,mysqldump的热备

概念理解

redo怎么应用的
undo怎么应用的
CSR(自动故障恢复)过程
LSN :日志序列号   log seq  number
TXID:事务ID
CKPT(Checkpoint) ：CSR过程中将脏页强制刷写到磁盘中

8. 锁

8.1 锁的概念

“锁”顾名思义就是锁定的意思。
“锁”的作用是什么？
在事务ACID过程中，“锁”和“隔离级别”一起来实现“I”隔离性和"C" 一致性 (redo也有参与).
悲观锁:行级锁定(行锁)   IX, X ,IS,S 
InnoDB支持行锁,保证事务更新某行数据时,不会受到其他事务影响.
谁先操作某个数据行,就会持有<这行>的(X)锁.
GAP 锁  ,  Next-lock锁 : 为了解决在RR (MySQL的隔离级别),防止幻读.
乐观锁: 没有锁

8.2 隔离级别

8.2.1 隔离级别的作用

影响的是读数据的隔离.增删改查都要涉及到读数据.

8.2.2 隔离级别种类

innodb默认为RR隔离级别

damao[test]>select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| REPEATABLE-READ         |
+-------------------------+
1 row in set (0.02 sec)

transaction_isolation   隔离级别(参数)
负责的是,MVCC,读一致性问题
RU （READ-UNCOMMIT） : 读未提交,可脏读,一般部议叙出现
RC （READ-COMMIT）: 读已提交,可能出现幻读,可以防止脏读.
RR  (REPEATABLE-READ ): 可重复读,功能是防止"幻读"现象 ,利用的是undo的快照技术+GAP(间隙锁)+NextLock(下键锁)
SR   : 可串行化,可以防止死锁,但是并发事务性能较差
补充: 在RC级别下,可以减轻GAP+NextLock锁的问题,但是会出现幻读现象,一般在为了读一致性会在正常select后添加for update语句.但是,请记住执行完一定要commit 否则容易出现所等待比较严重.

8.2.3 各种隔离级别功能演示

vim /etc/my.cnf
 
autocommit=0   不自动提交
transaction_isolation=read-uncommitted

脏读：读取的是内存中的内容，当MySQL打开两个会话窗口。会话a  begin开始一 
          个事务，但是没有提交，这是更改的数据页在内存中并 没有刷写到磁盘中， 
           但是会 话b可以看到在内存中更改后但没有提交的数据 页，这就是脏读。
不可重复读：还是a  b两个会话，当会话a  begin开始一个事务并进行提交，这时会 
                      话b也可以看到提交后的内容，是的会话b前后看到的数据不一致，这 
                      中现象在一些金融行业中是被允许出现的，这就是不可重复读。在 
                      RR 隔离级别中会利用undo的快照功能进行多版本并发控制(MVCC), 
                      使得 一个窗口的数据多次查询不会看到改变，如果进行一些事务操 
                      作就会看到新数据。
幻读：当会话a进行一些update操作时，会话b同时在进行insert的操作，在会话a的 
           事务提交之前，会话b先行提交。此时a提交后再次查询会看到会话b的更改 
           数据，使得会话a的更改并不能完全有效，RR隔离级别结合GAP（间隙锁） 
           和next lock（下键锁）来进行控制，作用是：在会话a进行一定范围的数据 
           页更改时，会话b不能对这些范围的数据页进行操作。

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

9.1 存储引擎相关

9.1.1 查看MySQL支持的存储引擎及默认的存储引擎

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

9.1.2 如何指定和修改存储引擎

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

9.2 表空间

9.2.1 共享表空间（系统库 MyISAM）

innodb_data_file_path
一般是在初始化数据之前就设置好
例子:
innodb_data_file_path=ibdata1:512M:ibdata2:512M:autoextend
**说明：共享表空间ibdata1大小为512M,用完之后使用ibdata2，ibdata2为512M，用完之后自己扩展空间。

9.2.2 独立表空间

damao[test]>show variables like 'innodb_file_per_table';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | ON    |
+-----------------------+-------+
1 row in set (0.01 sec)

9.3 缓冲区池

9.3.1 查询设置

select @@innodb_buffer_pool_size;
show engine innodb status\G

innodb_buffer_pool_size   写在配置文件中
一般建议最多是物理内存的 75-80%

生产故障:
内存OOM,问题: buffer设置过大

9.4 innodb_flush_log_at_trx_commit (双一标准之一)

9.4.1 作用

主要控制了innodb将log buffer中的数据写入日志文件并flush磁盘的时间点，取值分别为0、1、2三个。

9.4.2 查询

select @@innodb_flush_log_at_trx_commit;
show variables like 'innodb_flush_log_at_trx_commit';

9.4.3 参数说明0.1.2

1，每次事物的提交都会引起日志文件写入、flush磁盘的操作，确保了事务的ACID；flush  到操作系统的文件系统缓存  fsync到物理磁盘.
0，表示当事务提交时，不做日志写入操作，而是每秒钟将log buffer中的数据写入文件系统缓存并且秒fsync磁盘一次；
2，每次事务提交引起写入文件系统缓存,但每秒钟完成一次fsync磁盘操作。

1:   每次事务commit; 都会立即将redo刷写os cache,并立即刷写到磁盘
0 :  每秒钟完成,redo刷写os cache,并立即刷写到磁盘
2:   每次事务commit; 都会立即将redo刷写os cache,并每秒写入磁盘一次.
--------
The default setting of 1 is required for full ACID compliance. Logs are written and flushed to disk at each transaction commit.
With a setting of 0, logs are written and flushed to disk once per second. Transactions for which logs have not been flushed can be lost in a crash.
With a setting of 2, logs are written after each transaction commit and flushed to disk once per second. Transactions for which logs have not been flushed can be lost in a crash.
-------

9.5 Innodb_flush_method=(O_DIRECT, fdatasync)

image.png

9.5.1 作用

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

9.5.2 查看

show variables like '%innodb_flush%';

9.5.3 参数值说明

O_DIRECT  :   buffer pool 刷盘是跨过文件系统缓存,直接写磁盘,redo buffer 先写 os cache在写磁盘
FSYNC     :   buffer pool ,redo buffer 先写 os cache在写磁盘
O_DSYNC  :日志缓冲区写磁盘,不走 OS buffer

9.5.4 使用建议

最高安全模式
innodb_flush_log_at_trx_commit=1
Innodb_flush_method=O_DIRECT
最高性能:
innodb_flush_log_at_trx_commit=0
Innodb_flush_method=fsync

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

9.6 redo日志有关参数

innodb_log_buffer_size=16777216 
innodb_log_file_size=50331648
innodb_log_files_in_group = 3

说明：1写完写2，2写完写3，3写完（覆盖）写1

10 扩展

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是什么?