存储引擎
简介
相当于Linux文件系统，只不过比文件系统强大
功能了解
-
数据读写
-
数据安全和一致性
-
提高性能
-
热备份
-
自动故障恢复
-
高可用方面支持
-
等.
存储引擎介绍
show engines
第三方存储引擎
RocksDB MyRocks TokuDB 压缩比较高，数据的插入性能高，其他功能和InnoDB没差
笔试题mysql常用的搜索引擎
InnoDB ,MyISAM ,MEMORY,CSV 默认的存储引擎:InnoDB
案例---zabbix监控系统架构整改(真实案例)
环境: zabbix 3.2 mariaDB 5.5(默认是innodb) centos 7.3 现象 : zabbix卡的要死 , 每隔3-4个月,都要重新搭建一遍zabbix,存储空间经常爆满. 问题 : 1. zabbix 版本 2. 数据库版本 3. zabbix数据库500G,存在一个文件里 优化建议: 1.数据库版本升级到mariaDB最新版本,zabbix升级更高版本 2.存储引擎改为tokudb 3.监控数据按月份进行切割(二次开发:zabbix 数据保留机制功能重写,数据库分表) 4.关闭binlog和双1 5.参数调整.... 优化结果: 监控状态良好 为什么? 1. 原生态支持TokuDB,另外经过测试环境,10版本要比5.5 版本性能 高 2-3倍 2. TokuDB:insert数据比Innodb快的多，数据压缩比要Innodb高 3.监控数据按月份进行切割,为了能够truncate每个分区表,立即释放空间 4.关闭binlog ----->减少无关日志的记录. 5.参数调整...----->安全性参数关闭,提高性能.
InnoDB替换MyISAM存储引擎 (客户案例)
环境: centos 5.8 ,MySQL 5.0版本,MyISAM存储引擎,网站业务(LNMP),数据量50G左右 现象问题: 业务压力大的时候,非常卡;经历过宕机,会有部分数据丢失. 问题分析: 1.MyISAM存储引擎表级锁,在高并发时,会有很高锁等待 2.MyISAM存储引擎不支持事务,在断电时,会有可能丢失数据 职责 1.监控锁的情况:有很多的表锁等待 2.存储引擎查看:所有表默认是MyISAM 解决方案: 1.升级MySQL 5.6.10版本 2. 迁移所有表到新环境 3. 开启双1安全参数
InnoDB存储引擎介绍
在MySQL5.5版本之后，默认的存储引擎，提供高可靠性和高性能。
优点
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 )
查看存储引擎设置
SELECT @@default_storage_engine;
修改默认的存储引擎
vim /etc/my.cnf [mysqld] default_storage_engine=InnoDB
查看表存储引擎状态
# 查看单张表的默认存储引擎
show create table t111;
# 查看所有表的存储引擎
SHOW TABLE STATUS LIKE 'CountryLanguage'\G
select table_schema,table_name ,engine from information_schema.tables where table_schema not in ('sys','mysql','information_schema','performance_schema');
存储引擎的修改
修改存储引擎
alter table t111 engine=innodb; show create table t111;
整理碎片
alter table t111 engine=innodb;
平常处理过的MySQL问题--碎片处理
环境:centos7.4,MySQL 5.7.20,InnoDB存储引擎 业务特点:数据量级较大,经常需要按月删除历史数据. 问题:磁盘空间占用很大,不释放 处理方法: 以前:将数据逻辑导出,手工truncate表,然后导入进去 现在: 对表进行按月进行分表(partition,中间件) 业务替换为truncate方式 定期进行碎片整理
批量替换zabbix 100多张 innodb为tokudb。
select concat("alter table ",table_schema,".",table_name," engine=tokudb;") from information_schema.tables
where table_schema='zabbix';
InnoDB存储引擎物理存储结构
最直观的存储方式(/data/mysql/data)
ibdata1：系统数据字典信息(统计信息)，UNDO表空间等数据 ib_logfile0 ~ ib_logfile1: REDO日志文件，事务日志文件。 ibtmp1： 临时表空间磁盘位置，存储临时表 frm：存储表的列信息 ibd：表的数据行和索引
表空间(Tablespace)
共享表空间(ibdata1~N)（了解即可）
5.5 版本的默认模式,5.6中转换为了独立表空间 需要将所有数据存储到同一个表空间中 ，管理比较混乱 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/5.8/en/innodb-architecture.html
共享表空间设置(在搭建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
例如:
mysqld --initialize-insecure --user=mysql --basedir=xxxxxx...... innodb_data_file_path=ibdata1:512M:ibdata2:512M:autoextend
独立表空间
从5.6，默认表空间不再使用共享表空间，替换为独立表空间。
主要存储的是用户数据
存储特点为：一个表一个ibd文件，存储数据行和索引信息
最终结论：
元数据 数据行+索引 一张InnoDB表= frm+idb+ibdata1 mysql表数据 =（ibdataX+frm）+ibd(段、区、页)
MySQL的存储引擎日志：
-
Redo Log: ib_logfile0 ib_logfile1，重做日志
-
Undo Log: ibdata1 ibdata2(存储在共享表空间中)，回滚日志
-
临时表:ibtmp1，在做join union操作产生临时数据，用完就自动清理
独立表空间设置问题
select @@innodb_file_per_table;
真实案例
案例背景:
硬件及软件环境: 联想服务器（IBM） 磁盘500G 没有raid centos 6.8 mysql 5.6.33 innodb引擎 独立表空间 备份没有，日志也没开 开发用户专用库: jira(bug追踪) 、 confluence(内部知识库) ------>LNMT
故障描述:
断电了，启动完成后“/” 只读 fsck 重启,系统成功启动,mysql启动不了。 结果：confulence库在 ， jira库不见了，先恢复confulence
独立表空间迁移
-
(1)创建和原表结构一致的空表
-
(2)将空表的ibd文件删除 alter table school.student discard tablespace;
-
(3)将原表的ibd拷贝过来,并且修改权限
-
(4)将原表ibd进行导入 alter table school.student 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）
这里用school下student表为例演示
1 查看建表语句
show create table student;
2 建一张空表
CREATE TABLE `student` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '学号',
`sname` varchar(255) NOT NULL COMMENT '姓名',
`sage` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT '年龄',
`ssex` enum('m','f','n') NOT NULL DEFAULT 'n' COMMENT '性别',
`sno` int(11) unsigned zerofill DEFAULT NULL,
`intime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入学时间',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4
3 将空表的ibd文件删除
alter table school.student discard tablespace;
4 把3306启动的mysql中的student.ibd拷贝到3307下，并修改权限
cp /data/mysql/data/school/student.ibd ./ chown mysql:mysql student.ibd
5 ibd导入
alter table school.student import tablespace;
InnoDB 核心特性事物
事务的ACID特性
Atomic（原子性）
- 所有语句作为一个单元全部成功执行或全部取消。不能出现中间状态。
Consistent（一致性）
- 如果数据库在事务开始时处于一致状态，则在执行该事务期间将保留一致状态。
Isolated（隔离性）
- 事务之间不相互影响。
Durable（持久性）
- 事务成功完成后，所做的所有更改都会准确地记录在数据库中。所做的更改不会丢失。
事务的生命周期
事务的开始
begin 说明:在5.5 以上的版本，不需要手工begin，只要你执行的是一个DML，会自动在前面加一个begin命令。
(2) 标准的事务语句
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：提交事务 完成一个事务，一旦事务提交成功 ，就说明具备ACID特性了。 rollback ：回滚事务 将内存中，已执行过的操作，回滚回去
自动提交策略
select @@autocommit;
在线修改参数关闭自动提交
(1) 会话级别: mysql> set autocommit=0; 及时生效,只影响当前登录会话 (2)全局级别: mysql> set global autocommit=0; 断开窗口重连后生效,影响到所有新开的会话 (3)永久修改(重启生效) 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
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会为每一个事务生成一个事务号,伴随着整个事务.
Redo是什么？
redo,顾名思义“重做日志”，是事务日志的一种。
主要功能
-
(1)记录了内存数据页的变化.
-
(2)提供快速的持久化功能(WAL)
-
(3)CSR过程中实现前滚的操作(磁盘数据页和redo日志LSN一致)
-
(4)在事务ACID过程中，实现的是“D”持久化的作用。对于AC也有相应的作用
redo日志位置
redo的日志文件：iblogfile0 iblogfile1
redo buffer
redo的buffer:数据页的变化信息+数据页当时的LSN号 LSN：日志序列号 磁盘数据页、内存数据页、redo buffer、redolog
redo的刷新策略
commit; 刷新当前事务的redo buffer到磁盘 还会顺便将一部分redo buffer中没有提交的事务日志也刷新到磁盘
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的"前滚操作"
undo 回滚日志
作用:
在事务ACID过程中，实现的是“A” 原子性的作用 另外CI也依赖于Undo 在rolback时,将数据恢复到修改之前的状态 在CSR实现的是,将redo当中记录的未提交的时候进行回滚. undo提供快照技术,保存事务修改之前的数据状态.保证了MVCC,隔离性,mysqldump的热备
锁和隔离级别
锁实现了事务之间的隔离功能,InnoDB中实现的是行级锁.
在事务ACID过程中，“锁”和“隔离级别”一起来实现“I”隔离性和"C" 一致性 (redo也有参与).
悲观锁:行级锁定(行锁)
谁先操作某个数据行,就会持有<这行>的(X)锁.
乐观锁: 没有锁
常用的锁
-
row-level lock（行级锁）
-
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是什么?
隔离级别
影响到数据的读取,默认的级别是 RR模式. transaction_isolation 隔离级别(参数) 负责的是,MVCC,读一致性问题 RU : 读未提交,可脏读,一般部议叙出现 RC : 读已提交,可能出现幻读,可以防止脏读. RR : 可重复读,功能是防止"幻读"现象 ,利用的是undo的快照技术+GAP(间隙锁)+NextLock(下键锁) SR : 可串行化,可以防止死锁,但是并发事务性能较差 补充: 在RC级别下,可以减轻GAP+NextLock锁的问题,但是会出现幻读现象,一般在为了读一致性会在正常select后添加for update语句.但是,请记住执行完一定要commit 否则容易出现所等待比较严重. 例如: [world]>select * from city where id=999 for update; [world]>commit;
修改隔离级别配置
vi /etc/my.cnf transaction_isolation=read-uncommitted transaction_isolation=read-committed transaction_isolation=REPEATABLE-READ
不同的隔离级别出现的问题
-
RU 会出现脏读 ,
-
RC 会出现不可重复读 ,也会出现幻读.
-
RR 通过MVCC基础解决了不可重复读,但是有可能会出现幻读现象
-
在RR模式下,GAP和Next-lock进行避免幻读现象,必须索引支持
幻读实现
t1 表数据
id name 1 ls 2 ls 3 ls 4 ls 5 ls 6 ls 7 ls 8 ls 9 ls 10 ls
用户1 执行操作（未提交）
update t1 set name='zhangsa' where id>2
在此同时用户2执行
insert into t1 values (11,'ls'); commit;
用户1提交执行
commit; select * from t1;
至此用户1出现幻读，发现id为11的名字为ls，不是自己更新的zhangsa
InnoDB存储引擎核心特性-参数补充
存储引擎相关
查看
show engines; show variables like 'default_storage_engine'; select @@default_storage_engine;
如何指定和修改存储引擎
(1) 通过参数设置默认引擎 (2) 建表的时候进行设置 (3) alter table t1 engine=innodb;
表空间
共享表空间
innodb_data_file_path 一般是在初始化数据之前就设置好 例子: innodb_data_file_path=ibdata1:512M:ibdata2:512M:autoextend
独立表空间
show variables like 'innodb_file_per_table';
innodb_flush_log_at_trx_commit (双一标准之一)
作用
主要控制了innodb将log buffer中的数据写入日志文件并flush磁盘的时间点，取值分别为0、1、2三个。
查询
select @@innodb_flush_log_at_trx_commit;
参数说明:
1，每次事物的提交都会引起日志文件写入、flush磁盘的操作，确保了事务的ACID；flush 到操作系统的文件系统缓存 fsync到物理磁盘. 0，表示当事务提交时，不做日志写入操作，而是每秒钟将log buffer中的数据写入文件系统缓存并且秒fsync磁盘一次； 2，每次事务提交引起写入文件系统缓存,但每秒钟完成一次fsync磁盘操作。 -------- 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=(O_DIRECT, fdatasync)
作用
控制的是,log buffer 和data buffer,刷写磁盘的时候是否经过文件系统缓存
查看
show variables like '%innodb_flush%';
参数值说明
O_DIRECT :数据缓冲区写磁盘,不走OS buffer fsync :日志和数据缓冲区写磁盘,都走OS buffer O_DSYNC :日志缓冲区写磁盘,不走 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 # 设置redo个数 innodb_log_files_in_group = 3 脏页刷写策略脏页内存占用率75%开始写入磁盘 innodb_max_dirty_pages_pct=75
还有哪些机制会触发写磁盘?
-
CSR
-
redo满了
