Mysql的储存引擎

储存引擎介绍

1. 文件系统
操作系统存取数据的一种机制
2. 文件系统类型
不管使用什么文件系统，数据内容不会变化
不同的是，存储空间、大小、速度
3. MySQL存储引擎
可以理解为，MySQL的“文件系统”，只不过功能更加强大
4. MySQL存储引擎功能
数据读写
数据安全和一致性
提高性能
热备份
自动故障恢复
高可用方面支持

MySQL储存引擎分类

1. MySQL提供的存储引擎类型如下：
InnoDB
MyISAM
CSV
MEMORY
ARCHIVE
#查看支持的存储引擎
show engines; 
#查看所有InnoDB的表
select table_schema,table_name,engine
from information_schema.tables
where engine='innodb';
#查看所有MyISAM的表
select table_schema,table_name,engine
from information_schema.tables
where engine='myisam';

2.其他MySQL分支的存储引擎
PeconaDB：默认XtraDB
MariaDB：默认InnoDB

3.其他存储引擎（压缩比高，插入数据性能更高）
TokuDB
RocksDB
MyRocks

4.InnoDB和MyISAM的区别
物理上的区别
InnoDB:
.frm：数据列信息
.ibd：数据行和索引信息
MyISAM：
.frm：数据列信息
.MYD：数据行信息
.MYI：索引信息
特性上的区别
InnoDB：支持事物，行锁，MVCC多版本并发控制，支持外键，支持热备，支持自动故障恢
复（CSR）
事务（Transaction）
MVCC（Multi-Version Concurrency Control多版本并发控制）
行级锁(Row-level Lock)
ACSR（Auto Crash Safey Recovery）自动的故障安全恢复
支持热备份(Hot Backup)
复制Replication: Group Commit , GTID (Global Transaction ID) ,多线程(MTS,MultiThreads-SQL )
MyISAM：不支持

5.查看存储引擎
#1.查看会话存储引擎
SELECT @@default_storage_engine;
show variables like '%engine%';
#2.查看指定表的存储引擎
SHOW CREATE TABLE city\G;
SHOW TABLE STATUS LIKE 'city'\G
#3.查看所有表的存储引擎
select table_schema,table_name,engine
from information_schema.tables
where table_schema not in
('sys','mysql','information_schema','performance_schema');

6.设置存储引擎
#1.临时修改存储引擎，重启失效
#1）当前会话
set default_storage_engine=innodb;
#2）新会话（全局）
set global default_storage_engine=innodb;
#2.永久修改存储引擎
#1）修改配置文件
vim /etc/my.cnf
[mysqld]
default_storage_engine=innodb
#2）创建表指定存储引擎
create table t (i INT) engine=innodb;
#3）修改指定表的存储引擎
alter table city engine innodb;

表空间概述

1.MySQL数据文件类型
ib_buffer_pool：热数据缓存数据，下次启动优先加载
ibdata1：系统数据字典信息(整个数据库统计信息，表的元数据)，UNDO（回滚）表空间等数据。
又被称为共享表空间
ib_logfile0 ~ ib_logfile1: REDO（重做日志）日志文件，事务日志文件
ibtmp1： 临时表空间磁盘位置，存储临时表
frm：存储表的列信息
ibd：表的数据行和索引

2.共享表空间
需要将所有数据存储到同一个表空间中 ，耦合性大，管理比较混乱
5.5版本出现的管理模式，也是默认的管理模式。（数据字典，undo，临时表，索引，表数据）
mysqldump -uroot -p123 -A --triggers -R --master-data=2 >/tmp/full.sql
sed -i 's#ENGINE=MyISAM#ENGINE=INNODB#g' /tmp/full.sql
mysql -uroot -p123 < /tmp/full.sql
5.6版本以前，共享表空间保留，只用来存储:数据字典信息,undo,临时表。
5.7版本,临时表被独立出来了
8.0版本,undo也被独立出去了

事务概述

1.事务介绍
主要针对DML语句（insert,update,delete）的一组数据操作执行步骤，这些步骤被视为一个工作单元
1）用于对多个语句进行分组
2）在在多个客户端并发访问同一个表中的数据时使用
3）如果所有步骤都成功，则执行
4）如果步骤出现错误或不完整，则取消

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

3.事务控制语句
#1.自动提交
#查看自动提交
mysql> show variables like 'autocommit';
#临时关闭
mysql> set autocommit=0;
#永久关闭
[root@db01 world]# vim /etc/my.cnf
[mysqld]
autocommit=0

4.隐式事务提交
现在版本在开启事务时，不需要手工begin，只要你输入的是DML语句，就会自动开启事务。
有些情况下事务会被隐式提交
在事务运行期间，手工执行begin的时候会自动提交上个事务
在事务运行期间，加入DDL、DCL操作会自动提交上个事务
在事务运行期间，执行锁定语句（lock tables、unlock tables）
load data infile 
在autocommit=1的时候（默认就开启了自动提交，生产环境需要关闭）

事务日志

mysql的工作机制
1.所有的操作都在内存进行
2.优先写日志
3.先看redo，在看undo 

事务锁

什么是锁
锁定数据

锁的作用
在事务ACID特性过程中，“锁”和“隔离级别”一起来实现“I”隔离性的作用，避免争抢资源

锁的类型
排他锁，保证多事务操作时，数据一致性。
共享锁，保证多事务工作期间的，数据查询不会被阻塞。

MVCC

只阻塞修改类操作，不阻塞查询类操作
乐观锁的机制（谁先提交谁为准，都可以进入付款页面，谁先付款算谁的）

隔离级别

 

四种隔离级别：
READ UNCOMMITTED
允许事务查看其他事务所进行的未提交更改
READ COMMITTED
允许事务查看其他事务所进行的已提交更改
REPEATABLE READ
确保每个事务的SELECT输出一致
InnoDB的默认级别
SERIALIZABLE

将一个事务的结果与其他事务完全隔离
#查看隔离级别
mysql> show variables like '%iso%';
#修改隔离级别为RU
[mysqld]
transaction_isolation=read-uncommit
mysql> use oldboy
mysql> select * from stu;
mysql> insert into stu(id,name,sex,money) values(2,'li4','f',123);
#修改隔离级别为RC
[mysqld]
transaction_isolation=read-commit