1 简介

简介：相当于linux文件系统，只不过比文件系统强大
功能：数据读写，数据安全和一致性，提高性能，热备份，自动故障恢复，高可用方面支持
查看数据库支持的数据库引擎：show engines;
CSV
MRG_MYISAM
MyISAM
BLACKHOLE
PERFORMANCE_SCHEMA
MEMORY
ARCHIVE
InnoDB
FEDERATED
默认存储引擎：InnoDB
PerconaDB：默认是XtraDB
MariaDB：默认是InnoDB
第三方的存储引擎：
RocksDB，MyRocks，TokuDB，压缩比较高，数据的插入性能高，其他功能和InnoDB没差，是下一代数据库产品的雏形；

2 InnoDB 存储引擎核心特性

第8章-存储引擎 - 图1
事务、行锁、MVCC、外键、ACSR自动故障恢复、热备、复制(多线程,GTID，MTS)
1) 引擎查看
>show engines;
>select @@default_storage_engine;
vim /etc/my.cnf
[mysqld]
default_storage_engine=InnoDB
查看表存储引擎状态
>show create table test;
2) 存储引擎修改
>alter table test engine=innodb;
3) 整理碎片
数据行被删除时，磁盘空间不会立即被释放，长时间导致占用大量的磁盘空间，以下命令可解决此问题，当运行此命令后mysql会自动扫描全表，进行碎片整理；另外一种整理碎片的方式就是将表导出，删除原表，再进行导入，此种方式较为彻底，但是会中断业务，存在较大风险
>alter table test engine=innodb;
4) 批量替换innodb为tokudb
alter table zabbix.a engine=tokudb;
>select concat(“alter table”,table_scema,”.”,table_name,”engine=tokudb;”) from information_schema.tables where table_schema=’zabbix’;

3 InnoDB存储引擎物理存储结构

ibdata1：系统数据字典信息(统计信息)，UNDO表空间等数据
ib_logfile0 ~ ib_logfile1: REDO日志文件，事务日志文件
ibtmp1：临时表空间磁盘位置，存储l临时表
frm：存储表的列信息
ibd：表的数据行和索引

3.1 表空间(Tablespace)

表空间数据问题：
ibdata1：整个库的统计信息+Undo（元数据）
idb：数据行和索引
1）共享表空间
5.5版本的默认模式，5.6中转换了独立的表空间
需要将所有数据存储到同一个表空间中 ，管理比较混乱。
2）独立表空间
从5.6版本以后，默认表空间不再使用共享空间，替换为独立表空间，主要存储的是用户数据，共享表空间保留，只用来存储数据字典信息、undo以及临时表，存储特点为：一个表一个idb文件，存储数据行和索引信息
5.7版本，临时表被独立出来了
8.0版本，undo也被独立出去了
最终结论：
一张innodb表=frm+ibd+ibdata1

3.2 MySQL的存储引擎日志

Redo Log：ib_logfile0 ib_logfile1, 重做日志
Undo Log：ibdata1 ibdata2 (存储在共享表空间中)，回滚日志
ibtmp1：临时表，在做join union操作时产生的临时表数据，用完就自动清理
独立表空间设置
>select @@innodb_file_per_table;
>set global innodb_file_per_table=1; 设置为0即为共享表空间模式

3.3 独立表空间迁移

1）创建和原表结构一致的空表
2）将空表的idb文件删除
alter table city discard tablespace;
3）将原表的ibd拷贝过来，并且修改权限
4）将原表ibd进行导入
alter table city import tablespace;
示例将一个数据库实例下的t100w的表迁移至另外一个数据库实例

  1. 在新的数据库实例下创建库并创建空表，以便在新数据库的ibdata1文件下产生t100w表的统计数据
  2. mysql> create database test charset utf8mb4 collate utf8mb4_bin;
  3. Query OK, 1 row affected (0.00 sec)
  4. mysql> use test;
  5. Database changed
  6. 此建表命令可以在原库通过show create table t100w获得
  7. mysql> CREATE TABLE `t100w` (   `id` int(11) DEFAULT NULL,   `num` int(11) DEFAULT NULL,   `k1` char(2) DEFAULT NULL,   `k2` char(4) DEFAULT NULL,   `dt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
  8. Query OK, 0 rows affected (0.01 sec)
  9. 移除ibd文件
  10. mysql> alter table t100w discard tablespace;
  11. Query OK, 0 rows affected (0.02 sec)
  12. 将t100w.ibd文件cp至新数据库文件下并修改权限
  13. [root@db01 ~]# mv /data/mysql/instance01/test/t100w.ibd  /data/mysql/instance-3307/data/test/
  14. [root@db01 test]# chown mysql.mysql t100w.ibd
  15. 导入ibd文件
  16. mysql> alter table t100w import tablespace;     
  17. Query OK, 0 rows affected, 1 warning (0.13 sec)
  18. 查询
  19. mysql> select * from t100w limit 2;
  20. +------+--------+------+------+---------------------+
  21. | id   | num    | k1   | k2   | dt                  |
  22. +------+--------+------+------+---------------------+
  23. |    1 |  25503 | 0M   | IJ56 | 2019-08-12 11:41:16 |
  24. |    2 | 756660 | rx   | bc67 | 2019-08-12 11:41:16 |
  25. +------+--------+------+------+---------------------+
  26. 2 rows in set (0.00 sec)

3.4 InnoDB核心特性

3.4.1 事务的ACID特性

  • Atomic(原子性)：所有语句作为一个单元全部成功执行或全部取消。不能出现中间状态。
  • Consistent (一致性)：如果数据库在事务开始时处于一致状态，则在执行该事务期间将保留一致状态。
  • Isolated (隔离性)：事务之间不相互影响。

  • Durable (持久性)：事务成功完成后，所做的所有更改都会准确地记录在数据库中。所做的更改不会丢失。

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

    1）如何开启事务
    begin;
    2）标准的事务语句
    DML：
    insert
    update
    delete
    >use world;
    >update city set countrycode=’CHN’ where id=1;
    >update city set countrycode=’CHN’ where id=2;
    >update city set countrycode=’CHN’ where id=3;
    3）事务的结束
    提交：
    >commit;
    回滚：只能回滚未提交的
    >rollback;
    自动提交机制(autocommit)
    >select @@autocommit;
    mysql默认开启了自动提交机制，当执行DML语句时，会自动指定begin和commit语句的，且是逐句提交的，即没办法把多行组成一个事务，如需要将多行语句组成一个事务，需要将关闭此参数。

    3.4.3 在线修改参数

    1）会话级别：
    >set autocommit=0; 及时生效，只影响当前登录会话
    2）全局级别：
    >set global autocommit=0; 断开窗口重连后生效，影响到所有新开的会话
    3）永久修改（重启服务生效）
    vim /etc/my.cnf
    autocommit=0

    3.4.4 隐式提交的情况

    触发隐式提交的语句
    begin
    a
    b
    begin/create database;
    导致提交的非事务语句：
    DDL语句：alter,create和drop
    DCL语句：grant,revoke和set password
    锁定语句：lock tables和unlock tables
    导致隐式提交的语句示例
    truncate table
    load data infile
    select for update

    3.5 事务的ACID如何保证

    3.5.1 常用概念名词

    LSN：日志序列号 log sequence number，在ibd，redolog，data buffer pool，redo buffer四个都存在LSN

  • redo log：重做日志，及数据目录下的ib_logfile0和ib_logfile1文件，默认50M，轮询使用

  • redo log buffer：redo内存区域
  • ibd：存储数据行和索引
  • data buffer pool：缓冲区池，数据和索引的缓冲

MySQL每次数据库启动，都会比较磁盘数据页和redolog的LSN，必须要求两者的LSN一致数据库才能正常启动，如果不一致就会触发CSR，最终保证一致
WAL(持久化机制)：
Write Ahead Log日志优先写的方式实现持久化，日志是优先于数据写入磁盘的
脏页：
内存脏页，内存中发生了修改，没写入到磁盘之前，我们把内存页称之为脏页
CKPT：
CheckPoint，检查点，就是将脏页刷写到磁盘的动作
TXID：
事务号，InnoDB会为每一个事务生成一个事务号，伴随着整个事务

3.5.2 事务日志—redo重做日志

主要功能：保证”D”及数据的持久性， A C也有一定的作用
image.png
功能：
1）记录了内存数据页的变化（记录图中data buffer pool中数据的变化）存储在内存区域redo buffer中
2）提供快速的持久化功能（WAL）
3）CSR过程中实现前滚的操作（磁盘数据页和redo日志LSN一致）
redo日志位置：redo日志文件，iblogfile0 iblogfile1
redo buffer：redo log在内存所在内存区域buffer，数据页的变化信息+数据页当时的LSN号
redo的刷写策略：commit，刷新当前事务的redo buffer到磁盘，还会顺便将一部分redo buffer中没有提交的事务日志也刷新到磁盘
示例：我们做了一个事务，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的”前滚操作”

    3.5.3 undo

    回滚日志
    作用：在ACID特性中，主要保证A 的特性，同时对 CI 也有一定的功效
    1）记录了数据修改之前的状态
    2）rollback，将内存的数据修改恢复到修改之前
    3）在CSR中实现未提交数据的回滚操作
    4）实现一致性快照，配合隔离级别保证MVCC，实现读和写的操作，不会互相阻塞

    3.5.4 锁

    实现了各事务之间的隔离功能，InnoDB中实现的是行级锁定
    关闭自动提交autocommit；当多个窗口同时对一行数据做修改时，只要先执行修改命令的会话没有执行commit，后执行修改的会被处于锁等待状态，如下图右侧，默认锁等待时间为50s，超过此时间就会退出此等待状态
    image.png
    row-level lock 行级锁定
    gap 间隙锁
    next-lock 下一行锁

    3.5.5 隔离级别

    image.png
    RU：读未提交，可脏读，可以读取未提交的数据，及一个会话修改了数据，但是还没有commit提交，在新的会话中查询这行数据时已经是新的数据了，一般不允许这种情况出现。
    RC：读己提交，不可重复读，可能出现幻读，可以防止脏读，及每次会话查询总是查询到最新的数据，但是金融行业不允许此问题出现，比如在做年终结算时，银行通常只希望查询XXXX年12月31日23时59分次前的数据，但是这个查询持续了几个小时，在这段查询的过程中数据还在发生更新，在RC模式下会出现查询的结果会是最新更新的数据的情况。
    RR：默认模式，可重复读，通过MVCC(Multi Version Concurrency Control)技术解决了不可重复读，功能是防止”幻读”现象，利用的是undo的快照技术+GAP (间隙锁)+NextLock (下键锁)
    SR：可串行化，可以防止死锁，但是并发事务性能较差
    补充：在RC级别下，可以减轻GAP+nextLock锁的问题，但是会出现幻读现象，一般在为了读一致性会在正常select后添加for update语句。但是请记住执行完一定要commit否则容易出现锁等待比较严重。
    transaction isolation=read-uncommitted
    transaction isolation=read-committed
    transaction isolation=repeatable-read
    MVCC—-> undo快照

    3.6 InnoDB核心参数

    1）存储引擎设置
    defaultstorage_engine=innodb
    2）表空间模式
    innodb_file_per_table=1
    1：独立表空间模式
    0：共享表空间模式
    3）共享表空间文件个数和大小
    innodb_data_file_path=ibdata1:512M:ibdata2:512M:autoextend
    4）”双一”标准的其中一个
    innodb_flush_log_at_trx_commit=1
    共包含3个参数0，1，2，日志刷写过程分为两个阶段，从mysql进程的redo buffer内存区域刷写到os buffer操作系统的文件系统的buffer内存区域，然后从os buffer刷写到磁盘。
    0： 代表每秒定期将redo日志刷写到os buffer 然后os buffer定期刷写到磁盘，存在丢失1s内redo日志的风险
    1：默认设置，redo buffer—>os buffer—>磁盘这个过程完成，才算commit提交成功
    2：保证了redo buffer—>os buffer这一阶段，os buffer到磁盘还是没秒定期同步，断电同0类似，存在丢失1s内redo日志的风险
    Innodb_flush_method=(O_DIRECT,FSYNC)
    作用：控制的是redo buffer 和data buffer pool
    FSYNC模式：默认模式，性能较好，数据和redo日志都经过os buffer然后再写到磁盘
    image.png
    O_DIRECT：建议模式，数据直接写到磁盘，不经过os buffer，较为安全
    image.png
    O_DSYNC：同O_DIRECT相反
    image.png
    以上两个参数配合使用
    最高安全模式
    innodb_flush_log_at_trx_commit=1
    Innodb_flush method=O_DIRECT
    最高性能
    innodb_flush_log_at_trx_commit=0
    Innodb_flush_method=fsync
    5）redo日志设置有关的
    innodb_log_buffer_size =16777216
    innodb_log_file_size =50331648
    nnodb_log_files_in_group = 3
    6）脏页刷写策略
    innodb_max_dirty     pages_ pct= 75
    当脏页达到内存多大比例时，触发刷写到磁盘
    其他触发写到磁盘的情况
    1、CSR自动故障恢复
    2、redo空间满了

    面试题

    1 有关存储引擎导致的性能问题

    环境: 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.参数调整…——->安全性参数关闭,提高性能.

    2 有关行锁表导致的性能问题

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

    3 用discard与import进行数据恢复

    背景
    硬件及软件环境: 联想服务器（IBM） 磁盘500G 没有raid centos 6.8 mysql 5.6.33 innodb引擎 独立表空间 备份没有，日志也没开 开发用户专用库: jira(bug追踪) 、 confluence(内部知识库) ———>LNMT
    故障描述
    断电了，启动完成后“/” 只读 fsck 重启,系统成功启动,mysql启动不了。 结果：confulence库在 ， jira库不见了
    求助内容
    1. A：这种情况怎么恢复？ 
    2. B：有备份没 
    3. A： 连二进制日志都没有，没有备份，没有主从 
    4. B： 没招了，jira需要硬盘恢复了。 
    5. A： 1、jira问题拉倒中关村了 2、能不能暂时把confulence库先打开用着，将生产库confulence拷贝到1:1虚拟机上/var/lib/mysql，无法直接访问
    6. A：有没有工具能直接读取ibd 
    7. B：我查查，最后发现没有
    办法
    1. 表空间迁移:
    2. create table xxx 
    3. alter table  confulence.t1 discard tablespace；
    4. alter table confulence.t1 import tablespace; 
    5. 虚拟机测试可行。
    解决思路
    1. confulence库中一共有107张表。
    2. 1、创建107和和原来一模一样的表。
    3. 他有2016年的历史库，我让他去他同时电脑上 mysqldump备份confulence库
    4. mysqldump -uroot -ppassw0rd -B  confulence --no-data >test.sql
    5. 拿到你的测试库，进行恢复
    6. 到这步为止，表结构有了。
    7. 2、表空间删除。
    8. select concat('alter table ',table_schema,'.'table_name,' discard tablespace;') from information_schema.tables where table_schema='confluence' into outfile '/tmp/discad.sql';
    9. source /tmp/discard.sql
    10. 执行过程中发现，有20-30个表无法成功。主外键关系
    11. 很绝望，一个表一个表分析表结构，很痛苦。
    12. set foreign_key_checks=0 跳过外键检查。
    13. 把有问题的表表空间也删掉了。
    14. 3、拷贝生产中confulence库下的所有表的ibd文件拷贝到准备好的环境中
    15. select concat('alter table ',table_schema,'.'table_name,' import tablespace;') from information_schema.tables where table_schema='confluence' into outfile '/tmp/discad.sql';
    16. 4、验证数据
    17. 表都可以访问了，数据挽回到了出现问题时刻的状态（2-8）