MySQL存储引擎

标签：存储 rw Aug root MySQL 事务引擎 mysql 数据

MySQL的存储引擎

存储引擎简介

1.文件系统

- 操作系统组织和存储数据的一种机制
- 文件系统是一种软件

2.文件系统的类型：ext2 3 4 ，xfs数据

不管使用什么文件系统，数据内容不会变化
不同的是，存储空间、大小、速度

3.MySQL引擎

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

4.MySQL引擎功能

除了可以提供基本的存取功能，还有更多的事务功能、锁定、备份和恢复、优化及特殊功能

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

MySQL自带的存储引擎

01）InnoDB

02）MyISAM

03）MEMORY

04）ARCHIVE

05）FEDERATED

06）EXAMPLE

07）BLACKHOLE

08）MERGE
09）NDBCLUSTER

10）CSV

还可以使用第三方存储引擎:

01）MySQL当中插件式的存储引擎类型

02）MySQL的两个分支

	- perconaDB
	- mariaDB

查看MySQL的存储引擎

# 查看所有的存储引擎
root@localhost:world>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';

InnoDB和MyIsam的区别

物理区别

# innodb
-rw-r----- 1 mysql mysql 8841 Aug 12 08:33 student.frm # 表结构文件
-rw-r----- 1 mysql mysql 98304 Aug 12 08:42 student.ibd # 数据文件

# myisam
-rw-r----- 1 mysql mysql 10816 Aug 3 17:10 user.frm # 表结构文件
-rw-r----- 1 mysql mysql 1460 Aug 12 12:39 user.MYD # 数据文件
-rw-r----- 1 mysql mysql 4096 Aug 12 12:40 user.MYI # 数据文件

逻辑区别

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

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

innDB核心特性

重点

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

查看存储引擎

# 查看当前正在使用的存储引擎
root@localhost:(none)>SELECT @@default_storage_engine;

# 查看建表语句
root@localhost:world>show create table city;

root@localhost:world>select TABLE_SCHEMA,TABLE_NAME,ENGINE from information_schema.tables where engine='innodb' and table_name='city';

root@localhost:world>show table status like 'city'\G

设置存储引擎

# 编辑mysql配置文件
[mysqld]
default-storage-engine=<存储引擎名>
default-storage-engine=innodb
default-storage-engine=myisam

# 库内临时设置
mysql[(none)]> set global default_storage_engine=myisam;

# 建表是指定存储引擎
create table 表名(id int)engine='存储引擎名';

企业案例

项目背景：

公司原有的架构：一个展示型的网站，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、实施过程和注意要素

解决思路和过程

1.开会讨论，停机维护
2.将操作步骤，提前写在文档中

2.1准备新环境
新的CentOS系统
新的MySQL版本
配置文件优化好
服务启动好

3.关闭所有连接数据库的服务
systemctl stop php-fpm
systemctl stop tomcat

4.停数据库
systemctl stop mysqld

5.备份数据库中的全部数据
mysqldump -A -R --trigger --single-transaction --master-data=1

6.将备份的数据发送到新环境中
scp rsync

将表的myisam存储引擎改成innodb
mysql[zls]> alter table student engine='myisam';
mysql[zls]> alter table student charset='latin1';

# 方案一：
#!/bin/bash
for table in ` mysql -e 'show tables from zls'|awk 'NR>1'`;do
	mysql -e "alter table zls.$table engine='myisam'";
done

# 方案二
[root@db04 zls]# mysqldump -B zls > /tmp/zls.sql
:%s@ENGINE=MyISAM@ENGINE=InnoDB@g

7.将数据导入到新的数据库中

8.将几台web服务器，连接到新库，做测试

9.应用割接

表空间

共享表空间
独立表空间

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

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

5.6版本中默认是独立表空间

共享表空间

[root@db04 zls]# cd /application/mysql/data
[root@db04 data]# ll
-rw-r----- 1 mysql mysql 79691776 Aug 15 12:02 ibdata1

mysql[(none)]> show variables like '%path%';

共享表空间，初始大小默认值：12m：自动扩容

5.6版本中默认存储：
	1.系统数据
	2.undo
	3.临时表

-rw-rw---- 1 mysql mysql 12582912 Aug 9 10:24 ibdata1 # 共享表空间
-rw-rw---- 1 mysql mysql 50331648 Aug 9 10:24 ib_logfile0 # redo log
-rw-rw---- 1 mysql mysql 50331648 Aug 2 14:45 ib_logfile1 # redo log

5.7版本中的默认存储：
	1.系统数据
-rw-r----- 1 mysql mysql 79691776 Aug 15 12:02 ibdata1 # 共享表空间
-rw-r----- 1 mysql mysql 50331648 Aug 15 12:02 ib_logfile0 # redo log
-rw-r----- 1 mysql mysql 50331648 Aug 3 17:10 ib_logfile1 # redo log
-rw-r----- 1 mysql mysql 12582912 Aug 15 12:01 ibtmp1 # 临时表

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

# 修改共享表空间
# 修改共享表空间
[root@db04 data]# vim /etc/my.cnf
[mysqld]
innodb_data_file_path=ibdata1:76M;ibdata2:50M:autoextend
需要看错误日志设置文件大小

[root@db04 data]# systemctl restart mysqld

[root@db04 data]# du -sh ibdata2
-rw-r----- 1 mysql mysql 79691776 Aug 16 09:48 ibdata1
-rw-r----- 1 mysql mysql 52428800 Aug 16 09:48 ibdata2

独立表空间

对于用户自主创建的表，会采用此种模式，每个表由一个独立的表空间进行管理

[root@db01 ~]# ll /var/lib/mysql/world/
total 1236
-rw-r----- 1 mysql mysql   8710 Aug 15 18:06 city.frm	# 表结构
-rw-r----- 1 mysql mysql 704512 Aug 15 18:06 city.ibd	# 独立表空间

root@localhost:(none)>show variables like '%per_table%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | ON    |
+-----------------------+-------+
1 row in set (0.01 sec)
ON：开启
OFF：关闭

[root@db04 data]# strings world/city.ibd

事务

1.什么是事务

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

一组数据操作执行步骤，这些步骤被视为一个工作单元: 1）用于对多个语句进行分组 2）可以在多个客户机并发访问同一个表中的数据时使用

2.事务的通俗理解

伴随着交易出现的数据库概念

我们理解的“交易”是什么？

1）物与物的交换（古代）

2）货币现金与实物的交换（现代1）

3）虚拟货币与实物的交换（现代2）

4）虚拟货币与虚拟实物交换（现代3）

3.如何保证在数据库中，事务的和谐

靠事务性；来维持

A：原子性：所有语句作为一个单元全部成功执行或全部取消

C：一致性：如果数据库在事务开始时处于一致转态，则在执行该事务旗舰保留一致转态

I：隔离性：事务之间不相互影响

D: 持久性：事务成功完成后，所做的所有更改都会准确地记录在数据库中，所做的更改不会丢失。

4.事务流程

5.事务的流程控制语句

情况一：只要执行了DML语句，就会开启一个事务
insert update delete

情况二：begin执行后，会开启一个事务

# 开启事务语句
begin
start transaction

mysql> create table payment(id int,name varchar(10),money bigint);
mysql> insert into payment value(1,'cjk',1000),(2,'dsb',1000);
mysql> select * from payment;

start transaction（或 begin）：显式开始一个新事务
savepoint：分配事务过程中的一个位置，以供将来引用
savepoint abc;

commit：永久记录当前事务所做的更改
rollback：取消当前事务所做的更改
rollback to savepoint：取消在 savepoint 之后执行的更改
rollback to savepoint abc;

release savepoint：删除 savepoint 标识符
release savepoint sb_hl;

# 临时关闭
set autocommit：为当前连接禁用或启用默认 autocommit 模式
mysql> autocommit=1; 开启自动提交（临时）
mysql> autocommit=0; 关闭自动提交（临时）

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

# 事务的生命周期
# 一个失败的事务生命周期
begin 开启一个事务
DML
...
rollback;

# 一个成功的事务生命周期
begin 开启一个事务
DML
...
commit;

事务的隐式提交

1.在上一个事务没有执行完，就执行begin或者start transaction，就会将一个事务提交
2.在上一个事务没有执行完，就执行DDL和DCL就会隐式提交一个事务
3.在事务运行期间，执行锁定语句（lock tables 、unlock tables）
4.load data infile
5.autocommit=1（开启自动提交）

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

redo log (重做日志)
作用是什么

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

D：持久性：事务成功完成后，所做的所有更改都会准确地记录在数据库中。所做的更改不会丢失。

MySQL中：WAL Write Ahead Log 日志优先写

redo log 原理图

redo log 故障恢复过程

undo log

1.提交了，commit，数据保存到磁盘上redo log中了，断电了数据还没有写入独立表空间
2.没提交，数据也保存到磁盘上redo log中了，断电了，数据还没有写入独立表空间
3.没有提交，数据也没有保存到 redo log中，断电了，数据还没写入独立表空间

事务中的锁

1.什么是锁？

锁顾名思义就是锁定的意思。

2.锁的作用是什么？

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

MySQL中的锁：

排它锁：在事务操作期间，实现行级锁，保证数据的一致性

共享锁：在事务操作期间，其他事务不可以修改数据，但是可以查询数据

乐观锁：谁先提交谁

悲观锁：只要事务执行期间，其他事务均无法查询

MVCC多版本并发控制

只阻塞修改类操作，不阻塞查询类操作
乐观锁的机制（谁先提交谁为准）

事务的隔离级别

# 查看当前使用的事务隔离级别
root@localhost:wc>show variables like '%iso%';

read uncommitted (RU级别：未提交读)
允许事务查看其他事务所进行的为提交更改

read committed (RC级别，已提交读)
允许事务查看其他事务所进行的已提交更改

repeatable read （RR级别）
确保每个事务的SELECT 输出一致
InnoDB 的默认级别

serializable （串行化级别）
将一个事务的结果与其他事物完全隔离

# 修改隔离级别
[root@db02 world]# vim /etc/my.cnf
[mysqld]
autocommit=0
## 未提交读
transaction_isolation=read-uncommit

企业案例

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

解决思路

# 1.准备新环境，安装完数据库启动

# 2.将data目录备份出来导入新环境

# 3.要知道建表语句，管开发要
CREATE TABLE `city1` (
  `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`),
  KEY `inx_` (`Population`)
) ENGINE=InnoDB AUTO_INCREMENT=4080 DEFAULT CHARSET=latin1;

# 4.删除新表的表空间
root@localhost:world>alter table world.city1 discard tablespace;

# 物理层
[root@db01 world]# cp -a city.ibd city1.ibd

# 光物理拷贝表空间，会报错，country_new表的表空间已经被删除
mysql> select * from country_new;
ERROR 1814 (HY000): Tablespace has been discarded for table 'country_new'

# 6.将新表的表空间，导入进去
mysql> alter table world.country_new import tablespace;

# 7.两种解决方案：
- 跟开发说，去改代码，把所有要增删改成country表的代码改成country_new
- 删除已损坏的表，将新表改名为旧表名
[root@db02 world]# rm -f country.ibd
[root@db02 world]# rm -f country.frm
mysql> alter table country_new rename country;

# 8.旧业务先停机


# 9.使用binlog截取新数据，恢复到新环境中

# 10.业务应用割接到新环境

# 脏读：了解这个之前，首先要了解什么是脏数据。

# 脏数据是指源系统中的数据不在给定的范围内或对于实际业务毫无意义，或是数据格式非法，以及在源系统中存在不规范的编码和含糊的业务逻辑。

  脏读：在数据库技术中,脏数据在临时更新（ 脏读）中产生。事务A更新了某个数据项X，但是由于某种原因，事务A出现了问题，于是要把A回滚。但是在回滚之前，另一个事务B读取了数据项X的值(A更新后)，A回滚了事务，数据项恢复了原值。事务B读取的就是数据项X的就是一个“临时”的值，就是脏数据。

　　通俗的讲，当一个事务正在访问数据，并且对数据进行了修改，而这种修改还没有提交到数据库中，这时，另外一个事务也访问这个数据，然后使用了这个数据。因为这个数据是还没有提交的数据，那么另外一个事务读到的这个数据是脏数据，依据脏数据所做的操作可能是不正确的。

# 不可重复读：是指在数据库访问中，一个事务范围内两个相同的查询却返回了不同数据。
	这是由于查询时系统中其他事务修改的提交而引起的。比如事务T1读取某一数据，事务T2读取并修改了该数据，T1为了对读取值进行检验而再次读取该数据，便得到了不同的结果。
	一种更易理解的说法是：在一个事务内，多次读同一个数据。在这个事务还没有结束时，另一个事务也访问该同一数据。那么，在第一个事务的两次读数据之间。由于第二个事务的修改，那么第一个事务读到的数据可能不一样，这样就发生了在一个事务内两次读到的数据是不一样的，因此称为不可重复读，即原始读取不可重复。

# 幻觉读：指当事务不是独立执行时发生的一种现象，例如 第一个事务对一个表中的数据进行了修改，这种修改涉及到表中的全部数据行。同时，第二个事务也修改这个表中的数据，这种修改是向表中插入一行新数据。那么，以后就会发生操作第一个事务的用户发现表中还有没有修改的数据行，就好像发生了幻觉一样。

标签：存储,rw,Aug,root,MySQL,事务,引擎,mysql,数据
From： https://www.cnblogs.com/wangchengww/p/16592865.html