MySQL存储引擎

一.存储引擎简介

  • 1、文件系统：
    • 1.1 操作系统组织和存取数据的一种机制。
    • 1.2 文件系统是一种软件。
  • 2、文件系统类型：ext2 3 4 ，xfs 数据
    • 2.1 不管使用什么文件系统，数据内容不会变化
    • 2.2 不同的是，存储空间、大小、速度。
  • 3、MySQL引擎：
    • 3.1 可以理解为，MySQL的“文件系统”，只不过功能更加强大。
  • 4、MySQL引擎功能：
    • 4.1 除了可以提供基本的存取功能，还有更多功能事务功能、锁定、备份和恢复、优化以及特殊功能

总之，存储引擎的各项特性就是为了保障数据库的安全和性能设计结构。

二.MySQL自带的存储引擎类型

MySQL 提供以下存储引擎:
01）InnoDB
02）MyISAM
03）MEMORY
04）ARCHIVE
05）FEDERATED
06）EXAMPLE
07）BLACKHOLE
08）MERGE
09）NDBCLUSTER
10）CSV

查看引擎类型

#查看当前MySQL支持的存储引擎类型
mysql> show engines
#查看innodb的表有哪些
mysql> select table_schema,table_name,engine from information_schema.tables where engine='innodb';
#查看myisam的表有哪些
mysql> select table_schema,table_name,engine from information_schema.tables where engine='myisam';

1.innodb和myisam的区别

物理上的区别:

#进入mysql目录
[root@db01~l]# cd /application/mysql/data/mysql
#查看所有user的文件
[root@db01 mysql]# ll user.*
-rw-rw---- 1 mysql mysql 10684 Mar  6  2017 user.frm
-rw-rw---- 1 mysql mysql   960 Aug 14 01:15 user.MYD
-rw-rw---- 1 mysql mysql  2048 Aug 14 01:15 user.MYI
#进入word目录
[root@db01 world]# cd /application/mysql/data/world/
#查看所有city的文件
[root@db01 world]# ll city.*
-rw-rw---- 1 mysql mysql   8710 Aug 14 16:23 city.frm
-rw-rw---- 1 mysql mysql 688128 Aug 14 16:23 city.ibd

2.innidb 存储引擎的介绍

在MySQL5.5版本之后，默认的存储引擎，提供高可靠性和高性能。

  • 优点:
  • 01）事务安全（遵从 ACID）
  • 02）MVCC（Multi-Versioning Concurrency Control，多版本并发控制）
  • 03）InnoDB 行级别锁定
  • 04）Oracle 样式一致非锁定读取
  • 05）表数据进行整理来优化基于主键的查询
  • 06）支持外键引用完整性约束
  • 07）大型数据卷上的最大性能
  • 08）将对表的查询与不同存储引擎混合
  • 09）出现故障后快速自动恢复
  • 10）用于在内存中缓存数据和索引的缓冲区池

innodb核心特性 

  • 重点:
  • MVCC
  • 事务
  • 行级锁
  • 热备份
  • Crash Safe Recovery（自动故障恢复）

3.查看存储引擎

1）使用 SELECT 确认会话存储引擎

#查询默认存储引擎
SELECT @@default_storage_engine;

2）使用 SHOW 确认每个表的存储引擎

#查看表的存储引擎
SHOW CREATE TABLE CityG
SHOW TABLE STATUS LIKE 'CountryLanguage'G

3）使用 INFORMATION_SCHEMA 确认每个表的存储引擎

#查看表的存储引擎
SELECT TABLE_NAME, ENGINE FROM 
INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'City'
AND TABLE_SCHEMA = 'world'G

4.存储引擎的设置

1）在启动配置文件中设置服务器存储引擎

#在配置文件的[mysqld]标签下添加
[mysqld]
default-storage-engine=<Storage Engine>

2）使用 SET 命令为当前客户机会话设置

#在MySQL命令行中临时设置
SET @@storage_engine=<Storage Engine>

3）在 CREATE TABLE 语句指定

#建表的时候指定存储引擎
CREATE TABLE t (i INT) ENGINE = <Storage Engine>;

三.Innodb 核心特性---事务

1.什么是事务

主要针对DML语句（update，delete，insert）

  • 一组数据操作执行步骤，这些步骤被视为一个工作单元:
  • 1）用于对多个语句进行分组
  • 2）可以在多个客户机并发访问同一个表中的数据时使用
  • 所有步骤都成功或都失败
  • 1）如果所有步骤正常，则执行
  • 2）如果步骤出现错误或不完整，则取消

2.事务的通俗理解

陪伴着"交易"出现的数据库概率

  • 数据库中的“交易”是什么？
  • 1）事务又是如何保证“交易”的“和谐”？
  • 2）ACID

3.事务ACID特性

Atomic(原子性)

所有语句作为一个单元全部成功执行或全部执行

Consistent(一致性)

如果数据库在事务开始时处于一致状态,则在执行该事务期间将保留一致状态.

Isolated(隔离性)

事务之间不相互影响

Durable(持久性)

事务成功完成后,所做的所有更改都会准确地记录在数据库中,所做的更改不会丢失.

4.事务的控制语句

  • 如下:
  • START TRANSACTION（或 BEGIN）：显式开始一个新事务
  • SAVEPOINT：分配事务过程中的一个位置，以供将来引用
  • COMMIT：永久记录当前事务所做的更改
  • ROLLBACK：取消当前事务所做的更改
  • ROLLBACK TO SAVEPOINT：取消在 savepoint 之后执行的更改
  • RELEASE SAVEPOINT：删除 savepoint 标识符
  • SET AUTOCOMMIT：为当前连接禁用或启用默认 autocommit 模式

1.一个成功的事务周期

begin;
sql1
sql2
sql3
...
commit;

2.一个失败事务的生命周期

begin;
sql1
sql2
sql3
...
rollback;

3.自动提交

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

5.事务演示

1)成功演示

mysql> create table stu(id int,name varchar(10),sex enum('f','m'),money int);
mysql> begin;
mysql> insert into stu(id,name,sex,money) values(1,'zhang3','m',100), (2,'zhang4','m',110);
mysql> commit;

2)事务回滚

mysql> begin;
mysql> update stu set name='zhang3';
mysql> delete from stu;
mysql> rollback;

6.事务隐式提交情况

1) 现有版本在开启事务时,不需要手工begin,只要你有输入的是DML语句,就会自动开启事务

2)有些情况下事务会被隐式提交

例如:
在事务运行期间，手工执行begin的时候会自动提交上个事务
在事务运行期间，加入DDL、DCL操作会自动提交上个事务
在事务运行期间，执行锁定语句（lock tables、unlock tables）
load data infile
select for update
在autocommit=1的时候

7.事务日志redo基本功能

1)Redo是什么?

redo,顾名思义"重做日志",是事务日志的一种

2)作用是什么?

在事务ACID过程中,实现的是"D"持久化的作用

特性:WAL(Write Ahead Log)日志优先写
REDO：记录的是，内存数据页的变化过程

3)REDO工作过程

#执行步骤
update t1 set num=2 where num=1;

1）首先将t1表中num=1的行所在数据页加载到内存中buffer page
2）MySQL实例在内存中将num=1的数据页改成num=2
3）num=1变成num=2的变化过程会记录到，redo内存区域，也就是redo buffer page中

#提交事务执行步骤
commit;

1）当敲下commit命令的瞬间，MySQL会将redo buffer page写入磁盘区域redo log
2）当写入成功之后，commit返回ok

8.事务日志undo

1) undo是什么?

undo,顾名思义"回滚日志",是事务日志的一种

2)作用是什么?

在事务ACID过程中,实现的是"A"原子性的作用.当然CI的特性也和undo有关 

9.redo和undo的存储位置

#redo位置
[root@db01 data]# ll /application/mysql/data/
-rw-rw---- 1 mysql mysql 50331648 Aug 15 06:34 ib_logfile0
-rw-rw---- 1 mysql mysql 50331648 Mar  6  2017 ib_logfile1
#undo位置
[root@db01 data]# ll /application/mysql/data/
-rw-rw---- 1 mysql mysql 79691776 Aug 15 06:34 ibdata1
-rw-rw---- 1 mysql mysql 79691776 Aug 15 06:34 ibdata2

在MySQL5.6版本中undo是在ibdata文件中,在MySQL5.7版本会独立出来

10.事务中的"锁"?

1) 什么是"锁"?
"锁"顾名思义就是锁定的意思

2) "锁"的作用是什么?

在事务ACID特性过程中,"锁"和"隔离级别"一起来实现"I"隔离性的作用

排他锁:  保证在多事务操作时, 数据的一致性.

共享锁:  保证在多事务工作期间, 数据查询时不会被阻塞.

11.多版本并发控制(MVCC)

1) 只阻塞修改类操作,不阻塞查询类操作

2)乐观锁的机制(谁先提交谁为准)

12.锁的粒度

MyIsam: 低并发锁(表级锁)

Innodb: 高并发锁(行级锁)

13.事务的隔离级别

四种隔离级别:

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

 14隔离级别的解决的问题

1）脏读

　　脏读是指在一个事务处理过程里读取了另一个未提交的事务中的数据。

　　当一个事务正在多次修改某个数据，而在这个事务中这多次　　的修改都还未提交，这时一个并发的事务来访问该数据，就会造成两个事务得到的数据不一致。例如：用户A向用户B转账100元，对应SQL命令如下

    update account set money=money+100 where name=’B’;  (此时A通知B)
    update account set money=money - 100 where name=’A’;

当只执行第一条SQL时，A通知B查看账户，B发现确实钱已到账（此时即发生了脏读），而之后无论第二条SQL是否执行，只要该事务不提交，则所有操作都将回滚，那么当B以后再次查看账户时就会发现钱其实并没有转。

2）不可重复读

　　不可重复读是指在对于数据库中的某个数据，一个事务范围内多次查询却返回了不同的数据值，这是由于在查询间隔，被另一个事务修改并提交了。

　　例如事务T1在读取某一数据，而事务T2立马修改了这个数据并且提交事务给数据库，事务T1再次读取该数据就得到了不同的结果，发送了不可重复读。

　　不可重复读和脏读的区别是，脏读是某一事务读取了另一个事务未提交的脏数据，而不可重复读则是读取了前一事务提交的数据。

　　在某些情况下，不可重复读并不是问题，比如我们多次查询某个数据当然以最后查询得到的结果为主。但在另一些情况下就有可能发生问题，例如对于同一个数据A和B依次查询就可能不同，A和B就可能打起来了……

3）幻读

　　幻读是事务非独立执行时发生的一种现象。例如事务T1对一个表中所有的行的某个数据项做了从“1”修改为“2”的操作，这时事务T2又对这个表中插入了一行数据项，而这个数据项的数值还是为“1”并且提交给数据库。而操作事务T1的用户如果再查看刚刚修改的数据，会发现还有一行没有修改，其实这行是从事务T2中添加的，就好像产生幻觉一样，这就是发生了幻读。

　　幻读和不可重复读都是读取了另一条已经提交的事务（这点就脏读不同），所不同的是不可重复读查询的都是同一个数据项，而幻读针对的是一批数据整体（比如数据的个数）。

　　现在来看看MySQL数据库为我们提供的四种隔离级别：

　　① Serializable (串行化)：可避免脏读、不可重复读、幻读的发生。

　　② Repeatable read (可重复读)：可避免脏读、不可重复读的发生。

　　③ Read committed (读已提交)：可避免脏读的发生。

　　④ Read uncommitted (读未提交)：最低级别，任何情况都无法保证。

【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/gongcheng-/p/10139877.html