07-存储引擎-01

1.存储引擎介绍

相当于Linux 文件系统.组织存储表数据.

2.存储引擎的种类

• 查看存储引擎的种类

mysql> show engines;
InnoDB 
MyISAM
CSV
Memory

• 其他的存储引擎:

MariaDB  : InnoDB,TokuDB ,Myrocks  
percona  : xtradb ,TokuDB ,Myrocks 

TokuDB ,Myrocks : 比较适合于在写入操作较多的场景,数据量级大的场景.
原因是: 插入性能很高, 压缩比较高.
适用于监控类的业务.

• 案例:

• 环境
  zabbix 3.x mariaDB 5.5 centos 7.3

• 现象
  zabbix卡的要死 , 每隔3-4个月,都要重新搭建一遍zabbix,存储空间经常爆满.

• 问题
  1.zabbix 版本
  2.数据库版本 ---> 5.5 ----> ibdata1 ----> 5.7 ,8.0
  3.zabbix数据库500G,存在一个文件里

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

• 优化结果:
  监控状态良好

select concat("alter table zabbix.",table_name," engine=tokudb") from information_schema.tables
where table_schema='zabbix';

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

3.InnoDB存储引擎的核心特性

• 面试题:

问题：
1.InnoDB存储引擎的特性
2.InnoDB和MyISAM的区别
答案：

MVCC       多版本并发控制  
聚簇索引   PK
事务
行级锁     MyISAM支持表锁
外键       FK 
复制支持高级特性: GTID等高级复制
自适应hash索引 
支持热备,MyISAM支持温备份
ACSR(自动故障恢复)

4. 存储引擎的操作

• 查看存储引擎

mysql> show engines;
mysql> select @@default_storage_engine;
mysql> create table mt (id int) engine=myisam;
mysql> create table et (id int) engine=innodb;

• 查询所有非INNODB的表 , 并且提出修改建议

mysql> SELECT table_schema,table_name ,ENGINE ,CONCAT("alter table ",table_schema,".",table_name," engine=innodb;") AS "修改建议"
FROM information_schema.tables
WHERE table_schema NOT IN ('sys','information_schema','performance_schema','mysql')
AND ENGINE <> 'innodb';
+--------------+------------+--------+--------------------------------------+
| table_schema | table_name | ENGINE | 修改建议                             |
+--------------+------------+--------+--------------------------------------+
| test         | mt         | MyISAM | alter table test.mt engine=innodb;   |
| test         | test       | MyISAM | alter table test.test engine=innodb; |
+--------------+------------+--------+--------------------------------------+
2 rows in set (0.01 sec)

• 修改表的存储引擎

alter table test.test engine=innodb; 

• 扩展: 碎片问题解决.
  由于业务中有大量的delete操作,产生了大量的碎片.
  (1) 表数据逻辑导出,删除原表,重新导入.
  (2) mysql> alter table test.test engine=innodb;

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

5. InnoDB存储引擎物理存储结构

• 表空间(tablespace)
  (1) MySQL5.5版本出现了共享表空间模式(移植了Oracle)
  实现了较为方便的扩容功能,但是所有的表数据都集中在几个文件中,管理十分不方便.
  (2) MySQL 5.6中 ,默认使用独立表空间模式实现数据的存储.
  保留了共享表空间,只用来存储系统相关数据(数据字典+undo+tmp表空间)
  把用户表数据和索引单独存储(独立表空间)
  (3) MySQL 5.7
  保留了共享表空间ibdata1,只用来存储系统相关数据(数据字典+undo),undo在5.7 手工配置将他独立出来=
  (4) MySQL 8.0
  保留了共享表空间ibdata1,只用来存储系统相关数据(dw,cb)
  undo自动独立出来,移除了数据字典的存储.

• 表空间管理
  查看表空间模式:
  mysql> select @@innodb_file_per_table;
  共享表空间的设置:
  mysql> select @@innodb_data_file_path;
  ibdata1:332M;ibdata2:128M:autoextend

一般情况下: 安装MySQL ,提前设置好

[root@db01 ~]# cat /etc/my.cnf 
ibdata1:512M:ibdata2:512M:autoextend

• 表的物理存储介绍
  t1表:
  ibd : 数据行
  frm : 数据字典部分信息(列,列属性)
  ibdata1 : 整个数据库的数据字典(所有表的列信息,列属性....),undo
  ib_logfileN : redo事务日志

• 表空间迁移(快速迁移部分表数据)
  (1) 准备一个新环境
  (2) 创建和原表结构一样的表
  (3) 删除空表的ibd表空间文件
  alter table t1 discard tablespace;
  (4) cp 原表的ibd表空间到新环境
  [root@db01 test]# cp -a t1.ibd /data/mysql/data_3307/db1
  (5) 导入表空间文件.
  alter table t1 import tablespace;