MySQL储存引擎

目录

• MySQL储存引擎
  • 什么是储存引擎
  • 存储引擎的类型
  • 查看表的存储引擎(查全部的表)
    • innodb和myisam的区别
      • 物理区别
      • 逻辑区别
    • 存储引擎如何查看
    • 存储引擎设置
    • 修改已经创建的表的存储引擎
  • 企业项目案例
  • InnoDB特性——表空间
    • 共享表空间
      • 共享表空间存储：
      • 5.7共享表，临时表
      • 共享表空间切割
  • 企业案例
  • InnoDB核心特性——事务
    • 事务特性：ACID
    • 事务的生命周期
    • 事务控制语句
    • 自动提交
    • 事务的隐式提交
    • 事务日志（CSR 自动故障恢复）
      • redo
        • redo工作过程
      • undo
        • undo工作过程
      • redo和undo的储存位置
    • 事务中的锁
      • 锁的粒度
      • 多版本并发控制（MBVCC）
      • 事务的隔离级别

MySQL储存引擎

什么是储存引擎

MySQL引擎：

可以理解为，MySQL的“文件系统”，只不过功能更加强大。

存储引擎的类型

MySQL 提供以下存储引擎:

• 01）InnoDB
• 02）MyISAM
• 03）MEMORY HASH索引
• 04）ARCHIVE
• 05）FEDERATED
• 06）EXAMPLE
• 07）BLACKHOLE
• 08）MERGE
• 09）NDBCLUSTER
• 10）CSV

查看表的存储引擎(查全部的表)

root@localhost [(none)] >show create table world.city;
root@localhost [(none)] >select table_schema,table_name,engine from information_schema.tables where engine='innodb';
root@localhost [(none)] >select table_schema,table_name,engine from information_schema.tables where engine='myisam';
+--------------+------------------+--------+
| table_schema | table_name       | engine |
+--------------+------------------+--------+
| mysql        | columns_priv     | MyISAM |
| mysql        | db               | MyISAM |
| mysql        | event            | MyISAM |
| mysql        | func             | MyISAM |
| mysql        | ndb_binlog_index | MyISAM |
| mysql        | proc             | MyISAM |
| mysql        | procs_priv       | MyISAM |
| mysql        | proxies_priv     | MyISAM |
| mysql        | tables_priv      | MyISAM |
| mysql        | user             | MyISAM |
+--------------+------------------+--------+

innodb和myisam的区别

物理区别

[root@db04 ~]# ll /app/mysql/data/mysql/user.*
/app/mysql/data/mysql/user.frm
/app/mysql/data/mysql/user.MYD
/app/mysql/data/mysql/user.MYI

[root@db04 ~]# ll /app/mysql/data/world/city.*
/app/mysql/data/world/city.frm     // 表结构
/app/mysql/data/world/city.ibd     // 表空间（独立表空间）

format：格式，结构
.frm都是表结构信息

# 表结构
root@localhost [zls] >desc world.city;
+-------------+----------+------+-----+---------+----------------+
| Field       | Type     | Null | Key | Default | Extra          |
+-------------+----------+------+-----+---------+----------------+
| ID          | int(11)  | NO   | PRI | NULL    | auto_increment |
| Name        | char(35) | NO   |     |         |                |
| CountryCode | char(3)  | NO   | MUL |         |                |
| District    | char(20) | NO   |     |         |                |
| Population  | int(11)  | NO   | MUL | 0       |                |
+-------------+----------+------+-----+---------+----------------+

逻辑区别

innodb核心特性

重点:

• MVCC 多版本并发控制
• 独立表空间
• 事务
• 行级锁
• 热备份
• Crash Safe Recovery（自动故障恢复）
• ETREE

存储引擎如何查看

# 查看当前目录的存储引擎
SELECT @@default_storage_engine;
root@localhost [world] >show table status like 'city';

存储引擎设置

1）在启动配置文件中设置服务器存储引擎
#在配置文件的[mysqld]标签下添加
[mysqld]
default-storage-engine=<Storage Engine>

2）使用 SET 命令为当前客户机会话设置
#在MySQL命令行中临时设置
SET @@storage_engine=<Storage Engine>

3）建表指定存储引擎
root@localhost [world] >create table zls.t2_myisam(id int)engine myisam;

修改已经创建的表的存储引擎

## 修改单个表的
root@localhost [zls] >alter table t1_myisam engine=innodb;

## 修改库下所有的
1.把库用mysqldump导出数据库
2.通过vim修改，把ENGINE=MyISAM改为ENGINE=InnoDB
3.在导回去mysql -uroot -p123 < xx.sql

企业项目案例

项目背景：

公司原有的架构：一个展示型的网站，LAMT，MySQL5.1.77版本（MYISAM），50M数据量。

小问题不断：

1、表级锁：对表中任意一行数据修改类操作时，整个表都会锁定，对其他行的操作都不能同时进行。

2、不支持故障自动恢复（CSR）：当断电时有可能会出现数据损坏或丢失的问题。 如何解决：

1、提建议将现有的MYISAM引擎替换为Innodb，将版本替换为5.6.38

1）如果使用MYISAM会产生”小问题”，性能安全不能得到保证，使用innodb可以解决这个问题。

2）5.1.77版本对于innodb引擎支持不够完善，5.6.38版本对innodb支持非常完善了。

2、实施过程和注意要素

InnoDB特性——表空间

共享表空间

5.5版本以后出现共享表空间概念

表空间的管理模式的出现是为了数据库的存储更容易扩展

共享表空间存储：

5.7版本中默认会将undo和临时表独立出来，5.6版本也可以独立，只不过需要在初始化的时候进行配置

1）undo事务日志

2）系统数据

3）临时表

5.7共享表，临时表

-rw-r----- 1 mysql mysql 12582912 Jul 30 14:42 ibtmp1

共享表空间切割

mysql> show variables like '%path%';
+----------------------------------+------------------------+
| Variable_name                    | Value                  |
+----------------------------------+------------------------+
| innodb_data_file_path            | ibdata1:12M:autoextend |
+----------------------------------+------------------------+
# autoextend 自动扩容

## 文件配置（4864*16/1024=76)
[root@db01 data]# vim /etc/my.cnf
[mysqld]
innodb_data_file_path=ibdata1:76M;ibdata2:50M:autoextend

## 切割完成后会出来一个新的，
-rw-r----- 1 mysql mysql 79691776 Jul 30 15:16 ibdata1
-rw-r----- 1 mysql mysql 52428800 Jul 28 17:19 ibdata2

## 查看
root@localhost [(none)] >show variables like '%path%';
+----------------------------------+------------------------------------+
| Variable_name                    | Value                              |
+----------------------------------+------------------------------------+
| innodb_data_file_path            | ibdata1:76M;ibdata2:50M:autoextend |
+----------------------------------+------------------------------------+

企业案例

独立表空间

在没有备份数据的情况下，突然断电导致表损坏，打不开数据库。

故障复现

# 在数据库内导出数据
[root@db04 tmp]# mysqldump -uroot -p123 -B world > /tmp/city.sql

# 使用scp传送到03机器上
[root@db04 tmp]# scp city.sql 10.0.0.53:/root

# 03机器导入数据库
[root@db03 ~]# mysql -uroot -p123 < city.sql

# 表结构损坏

# 查看表
mysql> show tables;
+-----------------+
| Tables_in_world |
+-----------------+
| city            |
| country         |
| countrylanguage |
+-----------------+

# 查看表内容（无法查看）
mysql> select * from fity;
ERROR 1146 (42S02): Table 'world.fity' doesn't exist

恢复步骤（思路）

# 1.准备环境
cd /app/mysql/scripts
./mysql_install_db --basedir=/app/mysql --datadir=/app/mysql/data

# 2.建库
mysql> create database world;

# 3.管开发要建表语句
CREATE TABLE `city_new` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`Name` char(35) NOT NULL DEFAULT '',
`CountryCode` char(3) NOT NULL DEFAULT '',
`District` char(20) NOT NULL DEFAULT '',
`Population` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`ID`),
KEY `CountryCode` (`CountryCode`)
) ENGINE=InnoDB AUTO_INCREMENT=4080 DEFAULT CHARSET=latin1;

# 4.使用语句创建表
# 5.删除新表的表空间
mysql> alter table city_new discard tablespace;

# 6.将旧表的表空间拷贝到新表下
cd /app/mysql/data/world
rm -f city_new.ibd
[root@db03 world]# cp -a city.ibd city_new.ibd

# 7.导入新表的表空间
mysql> alter table city_new import tablespace;

# 8.删除旧表
[root@db02 world]# rm -f city.frm city.ibd
或--------------
use world;
drop table city;

# 9.修改表名
mysql> alter table city_new rename city;

# 9.9挂维护页

# 10.应用割接
- 开发改代码，修改连接数据库的IP地址
- 将新环境做一个全备，mysqldump -B world > /tmp/wd.sql

# 11.binlog截取新增数据，恢复到旧环境

# 12.取消维护页

InnoDB核心特性——事务

事务 伴随着交易

事务主要针对DML语句（insert、update、delete）

事务特性：ACID

A：原子性：将一个事务视为一个单元，要么全部成功，有一条失败，就全部回滚

C：一致性：事务执行之前和事务执行之后，状态保持一致

I：隔离性：事务与事务之间互相隔离 （隔离级别，锁）

D：持久性：当事务被提交后，永久写入磁盘

事务的生命周期

# 成功的事务：
begin;
DML
DML
DML
commit;

# 失败的事务
begin;
DML
DML
DML
rollback;

事务控制语句

begin;   start transaction;    // 开启一个事务

savepoint;                     // 将事务保存在某一状态
mysql> savepoint zls_4000;

rollback to savepoint;          // 回到指定的位置点
mysql> rollback to savepoint zls_4000;

release savepoint;              // 删除位置点
mysql> release savepoint zls_4000;

commit;                         // 提交事务
rollback;                       // 回滚事务

## 设置
SET AUTOCOMMIT：为当前连接禁用或启用默认 autocommit 模式

vim /etc/my.cnf
[msyqld]
autocommit=0

MySQL在默认情况下，只要执行一个DML语句，就会自动开启一个事务

自动提交

#查看自动提交
mysql> show variables like 'autocommit';

#临时关闭
mysql> set autocommit=0;

#永久关闭
[root@db01 world]# vim /etc/my.cnf
[mysqld]
autocommit=0

事务的隐式提交

1）在事务执行期间，如果执行了begin，开启下一个事务，自动提交上一个事务。
2）在事务执行期间，如果执行了DDL、DCL也会自动提交事务
3）在事务执行期间，如果执行锁表语句 lock，unlock也会自动提交上一个事务
4）load data in file，做备份
5）select for update 自动提交事务
6）在autocommit开启的时候，自动提交事务

事务日志（CSR 自动故障恢复）

redo

redo,"重做日志"，是事务日志的一种

特性:WAL(Write Ahead Log)日志优先写

REDO：记录的是，内存数据页的变化过程

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

undo

1）undo是什么？

undo,顾名思义“回滚日志”，是事务日志的一种。

Undo Log的原理：为了满足事务的原子性，在操作任何数据之前，首先将数据备份到一个地方（这个存储数据备份的地方称为Undo Log）。然后进行数据的修改。如果出现了错误或者用户执行了ROLLBACK语句，系统可以利用Undo Log中的备份将数据恢复到事务开始之前的状态。除了可以保证事务的原子性，Undo Log也可以用来辅助完成事务的持久化。

undo工作过程

A.事务开始.
B.记录zls=200到undo log.
C.修改zls=100
D.将undo log写到磁盘。
E.将数据写到磁盘。
F.事务提交

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版本会独立出来。

事务中的锁

锁的粒度

• MyIsam：低并发锁（表级锁）

• Innodb：高并发锁（行级锁）

  ​ 行级锁要以主键（索引）为搜索，要不然不识别行

  多版本并发控制（MBVCC）

  共享锁：在一个事务执行期间，不阻塞查询操作

  排他锁：在一个事务执行期间，阻塞其他修改操作

  乐观锁：谁先提交，以谁为准

  悲观锁：以事务开启为准，谁先执行修改操作，其他人查询会被阻塞

事务的隔离级别

1. RC：read commit 提交读
2. RU：read uncommit 未提交读
3. RR：repeatable read 可重复读
4. SERTALIZABLE: 串行化级别

• 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