29各种存储引擎说明
功能
数据读写，数据安全和一致性，提高性能，热备份，自动故障恢复，高可用方面支持
存储引擎介绍
InnoDB，MyISAM，MEMORY，CSV
默认的存储引擎 InnoDB
第三方的存储引擎 RocksDB MyRocks TokuDB
压缩比较高，数据的插入性能高，其他功能和InnoDB没差
zabbix监控系统架构整改
环境 zabbix3.2 mariaDB5.5 centos7.3
现象 zabbix卡，每3-4个月都要重新搭建一遍zabbix，存储空间经常爆满
优化建议
数据库版本升级到5.7版本，zabbix升级更高版本
存储引擎改为tokudb
原生态支持TokuDB，另外经过测试环境，10版本要比5.5版本性能高
TokuDB：insert数据比InnoDB快，数据压缩比InnoDB要高
监控数据按月份进行切割(二次开发：zabbix数据保留机制功能重写，数据库分表)
监控数据按月份进行切割，为了能够truncate每个分区表，立即释放空间
关闭binlog
减少无关日志的记录
参数调整
安全性参数关闭，提高性能
InnoDB存储引擎核心特性说明
事务
行锁
MVCC
外键
ACSR自动故障恢复
热备
复制(多线程，GTID，MTS)
InnoDB MyISAM存储引擎的替换
环境 centos5.8 MySQL5.0版本，MyISAM存储引擎， 网站业务(LNMP)，数据量50G左右
现象问题 业务压力大的时候非常卡，经历过宕机，会有部分数据丢失
问题分析 MyISAM存储引擎表级锁，在高并发时会有很高锁等待；MyISAM存储引擎不支持事务，在断电时会有可能丢失数据
职责 监控锁的情况，有很多的表锁等待；存储引擎查看，所有表默认是MyISAM
解决方案 升级MySQL5.6.10版本，迁移所有表到新环境1，开启双1安全参数
存储引擎的查看
查看存储引擎设置
SHOW engines;
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 MySQL5.7.20 InnoDB存储引擎
业务特点 数据量极大，经常需要按月删除历史数据
问题 磁盘空间占用很大，不释放
处理方法
以前 将数据逻辑导出，手工truncate表，然后导入进去
现在 对表进行按月进行分表(partition，中间件)；业务替换为truncate方式，定期进行碎片整理
批量替换zabbix 100多张 innodb为tokudb
ALTER TABLE zabbix.a engine=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 表的数据行和索引
表空间
ibdata1 整个库的统计信息+Undo
ibd 数据行和索引
共享表空间
5.5版本默认模式，5.6版本中转换为了独立表空间
需要将所有数据存储到同一个表空间中，管理比较混乱
5.5版本出现的管理模式，也是默认的管理模式
5.6版本共享表空间保留，只用来存储：数据字典信息，undo，临时表
5.7版本，临时表被独立出来
8.0版本，undo也被独立出来
共享表空间设置(在搭建MySQL时，初始化数据之前设置到参数文件中)
SELECT @@innodb_data_file_path;
SHOW variables LIKE '%extend%';
独立表空间
从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操作产生临时数据，用完就自动清理
独立表空间设置问题
SELECT @@innodb_file_per_table;
独立表空间迁移
创建和原表结构一致的空表
将空表的ibd文件删除
ALTER TABLE city discard tablespace;
将原表的ibd文件拷贝过来，并且修改权限
将原表ibd进行导入
ALTER TABLE city import tablespace;
故障案例
背景 硬件及软件环境，联想服务器(IBM)，磁盘500G没有raid，centos6.8，mysql5.6.33，innodb引擎，独立表空间，备份没有，日志也没开
开发用户专用库 jira(bug追踪)，confluence(内部知识库)
故障描述
断电了启动完成后"/"只读
fsck 重启，系统成功启动，mysql启动不了
结果 confluence库在，jira库不见了
需求 恢复jira库
方案 没备份没主从，需要硬盘恢复
需求 暂时把confluence库先打开用着，将生产库confluence，拷贝到1:1虚拟机上/var/lib/mysql，直接访问时访问不了
方案 表空间迁移
InnoDB核心特性
事务的ACID特性
Atomic(原子性) 所有语句作为一个单元全部成功执行或全部取消，不能出现中间状态
Consistent(一致性) 如果数据库在事务开始时处于一致状态，则在执行该事务期间将保留一致状态
Isolated(隔离性) 事务之间不相互影响
Durable(持久性) 事务成功完成后，所做的所有更改都会准确地记录在数据库中，所做的更改不会丢失
事务的生命周期(事务控制语句)
如何开启事务 BEGIN;
标准的事务语句 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;
事务的结束
提交 COMMIT;
回滚 ROLLBACK;
自动提交机制(autocommit)
SELECT @@autocommit;
在线修改参数
SET autocommit=0; 会话级别,即时生效
SET global autocommit=0; 全局级别，断开窗口重连后生效
永久生效(重启生效)
vim /etc/my.cnf
autocommit=0
隐式提交的情况
触发隐式提交的语句
BEGIN
a
b
BEGIN
SET命令
导致提交事务的非事务语句
DDL语句 (ALTER CREATE DROP)
DCL语句 (GRANT REVOKE 和 SET PASSWORD)
锁定语句 (LOCK TABLES 和 UNLOCK TABLES)
导致隐式提交的语句示例
TRUNCATE TABLE
LOAD DATA INFILE
SELECT FOR UPDATE
事务的ACID如何保证
redo log
重做日志 ib_logfile0~1 50M 轮询使用
redo log buffer redo内存区域
ibd 存储数据行和索引
data buffer pool 缓冲区池，数据和索引的缓冲
LSN 日志序列号
磁盘数据页，redo文件，buffer pool，redo buffer
MySQL每次数据启动，都会比较磁盘数据页和redolog的LSN，必须要求两者LSN一致数据库才能正常启动
WAL(持久化)
write ahead log 日志优先写的方式实现持久化
脏页 内存脏页，内存中发生了修改，没写入到磁盘之前，我们把内存页称之为脏页
CKPT Checkpoint，检查点，就是将脏页刷写到磁盘的动作
TXID 事务号，InnoDB会为每一个事务生成一个事务号，伴随着整个业务
事务日志 redo 重做日志
保证"D"，AC也有一定的作用，记录了内存数据页的变化，提供快速的持久化功能(WAL)，CSR过程中实现前滚的操作(磁盘数据页和redo日志LSN一致)
redo日志位置
redo的日志文件 iblogfile0 iblogfile1
redo buffer
redo的buffer 数据页的变化信息+数据页当时的LSN号
redo的刷写策略
commit;
刷新当前事务的redo buffer到磁盘，还会顺带将一部分redo buffer中没有提交的事务日志也刷新到磁盘
MySQL在启动时，必须保证redo日志文件和数据文件LSN必须一致，如果不一致就会触发CSR，最终保证一致
undo
在ACID特性中，主要保证A的特性，同时对CT也有一定功效，记录了数据修改之前的状态
记录了数据修改之前的状态，rollback将内存的数据修改恢复到修改之前，在CSR中实现未提交数据的回滚操作，实现一致性快照，配合隔离级别保证MVC，读和写的操作不会互相阻塞
锁
实现了事务之间的功能，InnoDB中实现的是行级锁
row-level lock
gap
next-lock
隔离级别
RU 读未提交，可脏读，一般不易出现
RC 读已提交，可能出现幻读，可以防止脏读
RR 可重复读，功能是防止"幻读"现象，利用的是undo的快照技术+GAP(间隙锁)+NextLock(下键锁)
SR 可串行化，可以防止死锁，但是并发事务性能较差
在RC级别下，可以减轻GAP+NextLock锁的问题，但是会出现幻读现象，一般在为了读一致性会在正常select后添加for update语句，但是，执行完一定要commit，否则容易出现锁等待比较严重
transaction_isolation=read-uncommitted
transaction_isolation=read-committed
transaction_isolation=REPEATABLE-ERAD
InnoDB核心参数的介绍
存储引擎默认设置
default_storage_engine=innodb
表空间模式
innodb_file_per_table=1
共享表空间文件个数和大小
innodb_data_file_path=ibdata1:512M:ibdata2:512M:autoextend
双1标准的其中一个
innodb_flush_log_at_trx_commit=1
控制的是Redo buffer和buffer pool
innodb_flush_method=(O_DIRECT, fsybc)
建议模式 O_DIRECT
最高安全模式
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
innodb_log_files_in_group=3
脏页刷写策略
innodb_max_dirty_pages_pct=75
