MySQL-存储引擎

1、简介

类型Linux中的文件系统，比文件系统要高级

2、mysql中的存储引擎类型

InnoDB（5.5以后的默认存储引擎）
MyISAM（5.5以前的默认存储引擎）
CSV
MEMORY
BLACKHOLE
FEDERATED（Oracle(dblink----->MySQL)）

2.1 第三方工具：

（1）TokuDB

优势：
    压缩比高
    插入性能很高
    
应用范围：监控 

（2）MyRocks
（3）RocksDB

3、InnoDB与MyISAM的区别？（面试题）

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

4、存储引擎查看及简单修改

4.1 查询存储引擎

4.1.1 查看所有支持的存储引擎：

show engines;

4.1.1 使用 SELECT 确认会话存储引擎：

select @@default_storage_engine;

4.1.1 show查询每个表的存储引擎：

SHOW CREATE TABLE city\G;
SHOW TABLE STATUS LIKE 'city'\G
show table status;
show create table city;

4.1.1 INFORMATION_SCHEMA 确认每个表的存储引擎：

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

4.2 修改默认存储引擎（不代表生产操作）

1> 会话级别修改：

set default_storage_engine=myisam;

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

set global default_storage_engine=myisam;

注意：以上修改为临时修改，重启之后,所有参数均失效。
如果想要永久生效：

写入配置文件
vim /etc/my.cnf
[mysqld]
default_storage_engine=myisam

然后重启mysql服务
注：存储引擎是表级别的,每个表创建时可以指定不同的存储引擎,但是我们建议统一为innodb.

4.3 修改一个表的存储引擎

alter table t engine=innodb;

扩展：批量修改表的存储引擎

-- 将world库下所有表的引擎替换为innoDB
select concat("alter table ",table_name," engine=innodb")  from information_schema.tables where  table_schema='world';

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

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

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

4.5 扩展:如何批量修改

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

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

5.1 InnoDB最直观的存储方式

*.frm：表的列定义
*.ibd：表的数据和索引     *****
ibdata1（5.7版本）：共享表空间文件（回滚数据--UNDO（8.0版本）、系统数据字典）   *****
ib_logfile0 ~ ib_logfileN：redo log文件    *****
ibtmp1（5.7版本）：存放临时表
ib_buffer_pool：缓冲区池的映射文件

5.2 InnoDB的表空间管理模式介绍

5.2.1 共享表空间模式(5.5 默认)

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

5.2.1 独立表空间模式(5.6以后默认)

一个表一个ibd文件

5.3、共享表空间的设置

1>查看控制表空间的设置：

wenjuan[world]>select @@innodb_data_file_path;
+-------------------------+
| @@innodb_data_file_path |
+-------------------------+
| ibdata1:12M:autoextend  |
+-------------------------+
1 row in set (0.01 sec)

wenjuan[world]>

------------------说明：-----------------
ibdata1：应用表空间的名字
12M：默认大小
autoextend：自动扩展，就是12M用完了，会在文件的基础上自动每次扩展64M

默认每次增长的大小：
wenjuan[world]>show variables like '%extend%';
+-----------------------------+-------+
| Variable_name               | Value |
+-----------------------------+-------+
| innodb_autoextend_increment | 64    |
+-----------------------------+-------+
1 row in set (0.00 sec)

wenjuan[world]>

不需要经常设置，一般在mysql初始化之前设定好就行：

mysql初始化之前，在vim /etc/my.cnf中加入innodb_data_file_path=ibdata1:512M;ibdata2:512M:autoextend

5.4 独立表空间（5.6以后，默认就支持）设置

1> 查询独立表空间模式

wenjuan[world]>select @@innodb_file_per_table;
+-------------------------+
| @@innodb_file_per_table |
+-------------------------+
|                       1 |
+-------------------------+
1 row in set (0.00 sec)

wenjuan[world]>

------------------说明：---------
值只有两种情况：
1-----on----打开独立表空间模式
0-----off---关闭独立表空间模式，就是共享表空间模式

2> 修改表空间模式-------共享表空间体验(不代表生产操作):

wenjuan[(none)]>set global innodb_file_per_table=0;

5.5 独立表空间迁移

alter table t1 discard tablespace;    ----清掉ibd文件
alter table t1 import  tablespace;    ----重新导入ibd文件
DDL  DCL  DML 

innoDB表 :  ibdata1 + frm + ibd

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

6.1 简介

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

6.2 ACID特性

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

6.3 事务的生命周期（事务控制语句）

6.3.1 开启事务

begin;
或
start transaction;

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

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

6.3.3 事务的结束

（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;

6.3.4 自动提交功能

查询事务开启状态：select @@autocommit;（默认开启事务）

（1）关闭会话级别事务：----------临时关闭，重启mysql之后失效

set autocommit=0;

（2）全局关闭事务：-----------临时关闭，重启mysql之后失效

set global autocommit=0;

（3）永久生效：

在 /etc/my.cnf中加入autocommit=0，然后重启mysql

6.3.5 隐式提交的语句

用于隐式提交的 SQL 语句：
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

7、InnoDB 事务的ACID如何保证？

7.1概念

redo log        : 重做日志, 记录数据页的变化. ib_logfile0-n
redo log buffer : redo log 的缓冲区(内存)
ibd             : 表空间的数据文件,以段区页方式规划存储数据行和索引
buffer pool     : 数据页缓冲区
LSN             : Log seq no 日志序列号,redo log\log buffer\ibd\buffer pool
WAL             : write ahead log 日志优先写磁盘
脏页            : dirty page 在内存中被修改的数据，并还没有写入磁盘
CKPT            : checkpoint,将内存脏页回写到磁盘的动作
TXID            : transaction_id,事务ID,伴随着事务的整个生命周期.
undo log        : 回滚日志,ibdata1

未完……