MySQL 存储引擎

1. 介绍 plugin storage engine

存储引擎相当于Linux 文件系统。
插件的模式存在。作用在表的一种属性。

2. MySQL中的存储引擎类型

笔试题： 请你列举MySQL 支持的存储引擎种类br> InnoDB、MyISAM、CSV、Memory…
MySQL 5.5 版本以后，默认的引擎是InnoDB。

Percona , MariaDB 其他的存储引擎种类：
TokuDB ， Myrocks等。
特性：
1. 更高的压缩比。
2. 更快速的插入性能。

晚上作业：
参考文档：
https://www.jianshu.com/p/898d2e4bd3a7

3. InnoDB核心特性的介绍

聚簇索引
AHI 自适应hash索引
change buffer
事务
MVCC 多版本并发控制
行级锁
外键
复制特性
支持热备
自动故障恢复（ACSR）
双写机制

4. 存储引擎替换生产案例

5. 存储引擎基础管理

5.1 使用 SELECT 确认会话存储引擎

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

5.2.1 会话级别:

5.2.2 全局级别(仅影响新会话):

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

5.4 INFORMATION_SCHEMA 确认每个表的存储引擎

5.5 修改一个表的存储引擎

注意：此命令我们经常使用他，进行innodb表的碎片整理

5.6 案例：平常处理过的MySQL问题–碎片处理

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

5.7 扩展:如何批量修改

需求:将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’;

6. InnoDB 体系结构

6.1 物理存储结构

6.1.1 宏观

city.frm ： 数据字典信息（列定义）
city.ibd ： 表空间数据文件，存储数据行和索引
ibdata1 ： 共享（系统）表空间文件
ib_logfileN ： redo log文件，重做日志。
ibtmp1 ： 临时表空间
ib_buffer_pool ： 内存预热文件

6.1.2 微观

6.1.3 表空间存储管理方式

#介绍
在数据库引擎层，加入的逻辑的存储结构。来实现灵活的存储空间扩容。
MySQL 5.5 版本引入了表空间存储管理模式。
#表空间类型
– 共享表空间 ：ibdata1~ibdataN
5.5 版本的默认模式。用来存储所有的系统数据+用户数据(数据行+索引)。
ibdata文件默认是一个，不够用了可以进行扩容。

独立表空间 ：
5.6 版本+：
默认采用独立表空间模式。每张表都是独立的表空间（t1.ibd）。
共享表空间保留下来，只保存系统相关的数据。

5.7 和 8.0 版本中，将ibdata1进一步的瘦身。

操作
查看和配置表空间模式
mysql> select @@innodb_file_per_table;
mysql> set global innodb_file_per_table=0;
mysql> set global innodb_file_per_table=1;

==============================================

交流
学员求助内容:
求助：这种情况怎么恢复br> 我问：有备份没
求助：没有备份，没有主从，连二进制日志都没有。
我说：没招了，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/import.sql’;

4、验证数据
表都可以访问了，数据挽回到了出现问题时刻的状态（2-8）

6.2 内存结构

6.2.1 innodb_buffer_pool

介绍：
InnoDB最大的内存区域，官方建议最多可设置大小物理内存的80%。生产建议70%以下。
作用：
缓冲数据页、索引页、数据字典、AHI、change buffer 、DW
数据页修改完成之后，会写入到磁盘表空间中。ibd frm ibdata1文件。
参数：
mysql> select @@innodb_buffer_pool_size;

6.2.2 innodb_log_buffer

介绍：
用来存储内存数据页的变化。数据修改完成之后，会将日志写入到磁盘日志文件中（ib_logfileN）。
参数：
mysql> select @@innodb_log_buffer_size;

晚上作业：
作业1 ：
使用工具从frm文件中取出建表语句，实现表空间迁移。
https://pdf.us/2019/01/10/2620.html
作业2：
https://www.jianshu.com/p/898d2e4bd3a7

文章知识点与官方知识档案匹配，可进一步学习相关知识MySQL入门技能树数据库组成存储引擎31345 人正在系统学习中