存储引擎

简介

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

MySQL 存储引擎类型(笔试:3-4种)

image

oldguo[world]>show engines;
oldguo[world]>select @@default_storage_engine;
oldguo[world]>show variables like '%engine%';

InnoDB  (5.5以后默认存储引擎) *****
MyISAM  (5.5 以前的默认存储引擎) ***
MEMORY
ARCHIVE 
EXAMPLE
BLACKHOLE
MERGE
NDBCLUSTER
CSV
FEDERATED (Oracle(dblink) ----> MySQL)**

第三方的存储引擎:
TokuDB 
MyRocks 
RocksDB

TokuDB优势：
  1\. 压缩比高
  2\. 插入性能很高

案例:

环境: zabbix 3.2     mariaDB 5.5   centos 7.3 
现象 : zabbix卡的要死 ,  每隔3-4个月,都要重新搭建一遍zabbix
问题 : 
    1\. zabbix 版本   
    2\. 数据库版本
    3\. zabbix数据库500G,存在一个文件里

优化建议:
    * 1.数据库版本升级到10.x最新版本，zabbix升级更高版本
    * 2 .存储引擎改为tokudb 
    3.监控数据按月份进行切割(二次开发：zabbix 数据保留机制功能重写，数据库分表)
    * 4.关闭binlog和双1
    * 5.参数调整....

优化结果:
     监控状态良好

为什么?
    1\. 原生态支持tokudb,另外经过测试环境mariaDB 版本性能高, 2-3倍
    2\. TokuDB:insert数据比Innodb快的多，数据压缩比要Innodb高
    3.监控数据按月份进行切割,为了能够truncate每个分区表,立即释放空间
    4.关闭binlog ----->减少无关日志的记录
    5.参数调整...----->安全性参数关闭,提高性能

InnoDB 与 MyISAM 的区别(笔试题)

image

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 )
7 索引 B+tree B*TREE

项目:某急送

环境: centos 5.8 ,MySQL 5.0版本,MyISAM存储引擎,网站业务(LNMPT),数据量50G左右

现象问题: 业务压力大的时候,非常卡;经历过宕机,会有部分数据丢失.

问题分析:
1.MyISAM存储引擎表级锁,在高并发时,会有很高锁等待
2.MyISAM存储引擎不支持事务,在断电时,会有可能丢失数据

职责
1.监控锁的情况:有很多的表锁等待
2.存储引擎查看:所有表默认是MyISAM

解决方案:
1.升级MySQL 5.6.10版本
2. 迁移所有表到新环境
3. 开启双1安全参数 

存储引擎查看简单修改

show engines 
select @@default_storage_engine;
select table_name ,engine from information_schema.tables where table_schema='world';    

oldguo[world]>create table t (id int) engine=myisam;
oldguo[world]>show create table   t;
oldguo[world]>alter table t engine=innodb;

小扩展: 将world库下所有表的引擎替换为innoDB

select concat("alter table ",table_name," engine=innodb") from information_schema.tables where table_schema='world';
oldguo[world]>alter table t engine=innodb; 整理碎片

存储引擎查看

使用 SELECT 确认会话存储引擎

SELECT @@default_storage_engine;

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

会话级别:
set default_storage_engine=myisam;

全局级别(仅影响新会话):
set global default_storage_engine=myisam;

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

如果要永久生效:
写入配置文件
vim /etc/my.cnf
[mysqld]
default_storage_engine=myisam

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

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

SHOW CREATE TABLE city\G;

INFORMATION_SCHEMA 确认每个表的存储引擎

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

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

修改一个表的存储引擎

mysql[oldboy]>alter table t1 engine innodb;
注意：此命令我们经常使用他，进行innodb表的碎片整理

平常处理过的MySQL问题--碎片处理

环境:
centos7.4,MySQL 5.7.20,InnoDB存储引擎
业务特点:
数据量级较大,经常需要按月删除历史数据.
问题:
磁盘空间占用很大,不释放
处理方法:
  以前:
    将数据逻辑导出,手工drop表,然后导入进去
  现在:
    对表进行按月进行分表(partition,中间件)
业务替换为truncate方式

扩展:如何批量修改

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

需求:将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存储引擎物理存储结构

InnoDB 最直观的存储方式

city.frm   表的列定义
city.ibd   表的数据和索引 ☆☆☆☆☆
ibdata1(5.7)  共享表空间文件(UNDO回滚数据(8.0独立),系统数据字典) ☆☆☆☆☆
ib_logfile0 ~ ib_logfileN   redo log文件  ☆☆☆☆☆
ibtmp1(5.7)     存放临时表
ib_buffer_pool  缓冲区池的映射文件

image

InnoDB 的表空间管理模式

https://www.cnblogs.com/littlehb/archive/2013/05/08/3067095.html

共享表空间模式(5.5 默认) 
ibdata1:目前遗留下来了,用来存储系统数据.
=============================================
5.5版本出现的管理模式，也是默认的管理模式。
5.6版本以，共享表空间保留，只用来存储:系统数据字典信息,undo,临时表。
5.7 版本,临时表被独立出来了
8.0版本,undo也被独立出去了
=============================================
独立表空间模式(5.6以后默认)
一个表一个ibd文件

共享表空间的设置

mysql[world]>select @@innodb_data_file_path;    ---一般是在初始化数据之前

vim /etc/my.cnf 
innodb_data_file_path=ibdata1:512M:ibdata2:512M:autoextend
oldguo[(none)]>show variables like '%extend%';

独立表空间设置

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

1：on    0:off

共享表空间体验(不代表生产操作):
MySQL[(none)]>set global innodb_file_per_table=0;

独立表空间迁移

alter table t1 discard tablespace;
alter table t1 import  tablespace;
DDL  DCL  DML 
city表  ----> 独立表空间 ------> 表空间数据文件:city.ibd ----->  段   区   页

innoDB表 :  ibdata1 + frm + ibd

---

真实的学生案例

案例背景:
硬件及软件环境:
联想服务器（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）

InnoDB核心特性--事务（Transaction）

简介

事务：保证在一个完整业务逻辑中,所有涉及到的语句,要么全成功,要么全失败

ACID

资料链接：https://blog.csdn.net/dengjili/article/details/82468576

Atomic（原子性）
所有语句作为一个单元全部成功执行或全部取消。不能出现中间状态。
Consistent（一致性）
如果数据库在事务开始时处于一致状态，则在执行该事务期间将保留一致状态。
Isolated（隔离性）
事务之间不相互影响。
Durable（持久性）
事务成功完成后，所做的所有更改都会准确地记录在数据库中。所做的更改不会丢失。

image

原子性

image

这个过程包含两个步骤
A： 800 - 200 = 600
B: 200 + 200 = 400
原子性表示，这两个步骤一起成功，或者一起失败，不能只发生其中一个动作

一致性（Consistency）

image

操作前A：800，B：200
操作后A：600，B：400

一致性表示事务完成后，符合逻辑运算

持久性（Durability）
表示事务结束后的数据不随着外界原因导致数据丢失

操作前A：800，B：200
操作后A：600，B：400
如果在操作前（事务还没有提交）服务器宕机或者断电，那么重启数据库以后，数据状态应该为
A：800，B：200
如果在操作后（事务已经提交）服务器宕机或者断电，那么重启数据库以后，数据状态应该为
A：600，B：400

隔离性（Isolation）

image

两个事务同时进行，其中一个事务读取到另外一个事务还没有提交的数据，B

image

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

开启事务

begin; 
或者: 
start transaction;

标准的事务语句(DML: insert update delete)

oldguo[world]>delete from city where id>1000;
oldguo[world]>delete from city where id<500;

事务的结束

(1) rollback;  回滚
oldguo[world]>begin;
oldguo[world]>delete from city where id>1000;
oldguo[world]>delete from city where id<500;
oldguo[world]>rollback;

(2) commit ;
oldguo[world]>begin;
oldguo[world]>delete from city where id>1000;
oldguo[world]>delete from city where id<500;
oldguo[world]>commit;

自动提交功能

select @@autocommit;
set  autocommit=0;
set global autocommit=0;

vim /etc/my.cnf
autocommit=0

隐式提交的语句

用于隐式提交的 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. InnoDB 与 MyISAM
   事务(ACID):原子性，一致性，隔离性，持久性
   行(记录)锁+表锁
   热备份(事务)
   ACSR(自动故障恢复)
   MVCC
   复制 GC GTID MTS

案例

环境：贵州某交管系统，违章信息

MySQL5.7,70多张表

备份策略：每周六 mysqldump全备，每天binlog备份

问题描述：在网上找了个参数调整ibtmp1大小，直接在生成中修改，由于手误，删除了ibdata1文件，数据库无法启动

解决思路：
  1.mysqldump全备(周六) +  日志恢复

中间出的问题：
  1.全备有问题，经过调整，好用了
  2\. binlog日志只有2,3,4三天日志

换解决方案:  表空间迁移
  我们有什么? 
    1\. 上周六的完整数据(完整表结构)
    2\. 周四宕机时的ibd

演练:
1\. 搭建一个临时库
[root@db01 /data/3306/data]# mysql -S /data/3307/mysql.sock

2\. 恢复上周六的备份到临时库
ldguo[world]>source /root/world.sql

3\. 将测试库中ibd清掉
[root@db01 /data/3306/data]# vim /data/3307/my.cnf 
secure-file-priv=/tmp
[root@db01 /data/3306/data]# systemctl restart mysqld3307.service 
oldguo[(none)]>select concat("alter table world.",table_name," discard tablespace;") from information_schema.tables where table_schema='world' into outfile '/tmp/discard.sql';
oldguo[(none)]>set foreign_key_checks=0;
oldguo[(none)]>source /tmp/discard.sql

4\. 将故障库ibd文件,拷贝指定位置

5\. 将ibd进行import 
oldguo[(none)]>select concat("alter table world.",table_name," import tablespace;") from information_schema.tables where tablee_schema='world' into outfile '/tmp/import.sql';
oldguo[world]>source /tmp/import.sql

InnoDB 事务的ACID如何保证？

一些概念

redo log        : 重做日志, 记录数据页的变化. ib_logfile0,ib_logfile1
redo log buffer : redo log 的缓冲区(内存)
ibd             : 表空间的数据文件,以段区页方式规划存储数据行和索引
buffer pool     : 数据页缓冲区
LSN             : 日志序列号
WAL             ：write ahead log，日志有限写磁盘
脏页            ：diryty page，在内存中被修改的数据页
CKPT            ：checkpoint，将内存脏页回写到磁盘的动作
TXID            ：transaction id，事务ID，伴随着事务的整个生命周期
undo log        ：回滚日志，iddata1

redo 功能

redo简介

1.redo简介:重做日志, ib_logfile0,ib_logfile1
2.记录数据页的变化

3. 主要 保证ACID 中的 "D",持久化功能,对于AC也有相应的保证
   加快了commit命令的速度,提高了事务的并发.
   并且实现了,在MySQL Crash时,ACSR中前滚的功能.

undo 功能

undo简介

回滚日志, 5.7 存储在ibdata1 ,8.0 undo单独存储

undo记录的内容

记录逆操作

作用

主要保证了 A 的特性，CI 也有相应的功能

---

隔离级别和锁机制☆☆☆☆☆

隔离级别
RU	读未提交，脏读，不可重复读，幻读
RC	读已提交，不脏读，有不可重复读，有幻读
RR	防止脏读，防止不可重复读(快照)，可能会出现幻读(gap间隙,next-lock下一键锁)
SR	串行化

功能

主要提供了ACID中的 "I" 隔离性的功能,C的功能受到一定影响

隔离级别介绍

oldguo[(none)]>select @@tx_isolation;
oldguo[(none)]>select @@transaction_isolation;
oldguo[(none)]>show variables like '%tx%';

vim /etc/my.cnf 
transaction_isolation=REPEATABLE-READ
transaction_isolation=READ-COMMITTED

RU : 读未提交

transaction_isolation=READ-COMMITTED
A:  
mysql -uroot -p
use world 
1\. oldguo[world]>begin;
2\. oldguo[world]>update city set population=1000 where id=10;
4\. oldguo[world]>commit;

B:
mysql -uroot -p
use world 
3\. select * from city where id=10;
5\. select * from city where id=10;

防止脏读.
会出现,不可重复读.
会出现,幻读.

RC : 读已提交

transaction_isolation=READ-COMMITTED
A:  
mysql -uroot -p
use world 
1. oldguo[world]>begin;
2. oldguo[world]>update city set population=1000 where id=10;
4. oldguo[world]>commit;

B:
mysql -uroot -p
use world 
3. select * from city where id=10;
5. select * from city where id=10;

防止脏读.
会出现,不可重复读.
会出现,幻读.

create table t1 (id int ,name varchar(20));
insert into t1 values(1,'a'),(2,'b'),(4,'c'),(6,'d');
commit;

RR : 可重复度

防止脏读
防止不可重复读 (MVCC)
可能会出现幻读 (GAP netlock)

SR : 可串行化

InnoDB 的锁 (主要解决I)

record lock  记录锁,行级锁
gap
next-lock

InnoDB 核心参数介绍

默认存储引擎

oldguo[(none)]>select @@default_storage_engine;
Percona :  XtraDB  默认
MariaDB :  InnoDB  默认 myrocks  tokudb

innodb_buffer_pool_size☆☆☆☆☆

show variables like '%pool%';

vim /etc/my.cnf
innodb_buffer_pool_size=2G

image

官方建议：最多95%
生产建议：50%~80% 不超过80%

面试如何回答：
  1\. 业务够用的
  2\. 公司硬件有预留
  3\. MySQL还有可能额外使用到其他的内存结构
  4\. 我们公司做的多实例

innodb_log_buffer_size

oldguo[(none)]>show engine innodb status \G

oldguo[(none)]>show variables like '%log_buffer%';
oldguo[(none)]>select @@innodb_log_buffer_size;

redo的参数

show variables like '%innodb_log_file%';

image

---

relog的刷写策略("双一") ☆☆☆☆☆重要！

select @@innodb_flush_log_at_trx_commit;

image

默认:

• 1，在事务提交时,立即刷写redo buffer中日志到OS buffer ,立即SYNC到磁盘
• 0，每秒，刷写redo buffer中日志到OS buffer ，每秒SYNC到磁盘
• 2，在每个事物提交时，立即刷写redo buffer中日志到OS buffer,每秒SYNC到磁盘

--------
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.
-------

innodb_flush_method

作用: 控制 buffer pool ,和 redo buffer的刷盘策略

select @@innodb_flush_method;

O_DIRECT  :数据缓冲区写磁盘,不走OS buffer
fsync :日志和数据缓冲区写磁盘,走OS buffer
O_DSYNC  :日志缓冲区写磁盘,不走 OS buffer

image

#最高安全模式
innodb_flush_log_at_trx_commit=1
Innodb_flush_method=O_DIRECT

#最高性能:
innodb_flush_log_at_trx_commit=0
Innodb_flush_method=fsync

---

---

 -------- __@      __@       __@       __@      __~@
 ----- _`\<,_    _`\<,_    _`\<,_     _`\<,_    _`\<,_
 ---- (*)/ (*)  (*)/ (*)  (*)/ (*)  (*)/ (*)  (*)/ (*)

3. 小结

1. InnoDB核心特性
2. 事务 ACID 
3. redo 
4. undo 
5. 隔离级别 
6. 索引
7. 核心参数