第九章 数据库的存储引擎

一、MySQL存储引擎

1.MySQL支持的存储引擎

1.InnoDB
2.MyISAM
3.memory

2.InnoDB和MyISAM的物理区别

#MyISAM存储引擎文件
-rw-rw---- 1 mysql mysql  10684 10月 19 17:09 user.frm			#表结构
-rw-rw---- 1 mysql mysql    728 10月 23 20:02 user.MYD			#用户和密码
-rw-rw---- 1 mysql mysql   2048 10月 27 08:51 user.MYI			#用户

#InnoDB存储引擎文件
-rw-rw---- 1 mysql mysql    8710 10月 28 19:53 city.frm			#表结构
-rw-rw---- 1 mysql mysql 2097152 10月 28 19:54 city.ibd			#数据文件

3.InnoDB存储引擎核心特性

事务		CSR
备份

4.存储引擎相关命令

1）查看当前存储引擎

#查看当前所在库的存储引擎
mysql> SELECT @@default_storage_engine;
+--------------------------+
| @@default_storage_engine |
+--------------------------+
| InnoDB                   |
+--------------------------+
1 row in set (0.00 sec)

mysql> show variables like 'default_storage_engine';
+------------------------+--------+
| Variable_name          | Value  |
+------------------------+--------+
| default_storage_engine | InnoDB |
+------------------------+--------+
1 row in set (0.01 sec)

#查看指定表的存储引擎
mysql> select table_schema,table_name,engine from information_schema.tables where table_name='city';
+--------------+------------+--------+
| table_schema | table_name | engine |
+--------------+------------+--------+
| world        | city       | InnoDB |
+--------------+------------+--------+
1 row in set (0.00 sec)

2）修改默认的存储引擎

#在配置文件的[mysqld]标签下添加，永久设置
[root@db01 ~]# vim /etc/my.cnf
[mysqld]
default-storage-engine=InnoDB

#在MySQL命令行中临时设置
SET @@storage_engine=MyISAM

3）建表时指定存储引擎

mysql> create table test4(id int) engine=myisam;
Query OK, 0 rows affected (0.01 sec)

二、企业案例一

1.项目背景

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

1）经常出现小问题

1.表级锁：对表中任意一行数据修改类操作时，整个表都会锁定，对其他行的操作都不能同时进行。
2.不支持故障自动恢复（CSR）：当断电时有可能会出现数据损坏或丢失的问题

2）提出问题解决方案

1.提建议将现有的MYISAM引擎替换为Innodb，将版本替换为5.6.38
	1）如果使用MYISAM会产生”小问题”，性能安全不能得到保证，使用innodb可以解决这个问题。
	2）5.1.77版本对于innodb引擎支持不够完善，5.6.38版本对innodb支持非常完善了。
2.实施过程和注意要素

2.实施过程

#1.准备一台新数据库服务器
#2.设置新数据库密码
#3.备份旧数据库数据
[root@db01 ~]# mysqldump -uroot -p -A > /tmp/full.sql
[root@db01 ~]# mysqldump -uroot -p -B  -R --triggers --single-transaction --master-data=2 > /tmp/full1.sql
#4.将数据推到远端服务器
[root@db01 ~]# scp /tmp/full.sql 172.16.1.52:/tmp/
#5.修改存储引擎为InnoDB
[root@db02 ~]# sed -i 's#ENGINE=MyISAM#ENGINE=InnoDB#g' /tmp/full.sql
#6.将数据导入新库
[root@db02 ~]# mysql -uroot -p < /tmp/full.sql 
Enter password:
#7.查看新库数据的存储引擎
#8.将业务切换到新库进行存储

#9.根据binlog打点备份获取到数据迁移过程中产生的新数据，导入新库

三、InnoDB存储引擎的表空间

1.共享表空间（ibdata1）

1）存储的内容

1.系统数据
2.临时表
3.undo 日志		事务的日志 redo undo

2）查看共享表空间

mysql> show variables like '%path%';
+-----------------------+------------------------+
| Variable_name         | Value                  |
+-----------------------+------------------------+
| innodb_data_file_path | ibdata1:12M:autoextend |
| ssl_capath            |                        |
| ssl_crlpath           |                        |
+-----------------------+------------------------+
3 rows in set (0.00 sec)

3）配置共享表空间

#1.编辑配置文件
[root@db01 ~]# vim /etc/my.cnf
[mysqld]
innodb_data_file_path=ibdata1:50M;ibdata2:50M:autoextend

#2.启动数据库报错
[root@db01 ~]# systemctl restart mysqld.service
#为了查看报错
[root@db01 ~]# /etc/init.d/mysqld start
Starting MySQL. ERROR! The server quit without updating PID file (/usr/local/mysql/data/db01.pid).

#3.看日志报错
[root@db01 ~]# less /usr/local/mysql/data/db01.err
2020-10-29 18:10:47 16917 [ERROR] InnoDB: Data file ./ibdata1 is of a different size 4864 pages (rounded down to MB) than specified in the .cnf file 768 pages!

#4.错误原因
共享表空间ibdata1设置的结束大小与实际ibdata1大小不符合

#5.解决问题
[root@db01 ~]# vim /etc/my.cnf
[mysqld]
#修改ibdata1大小与数据目录下的大小一致即可
innodb_data_file_path=ibdata1:76M;ibdata2:12M:autoextend

#6.重启服务
[root@db01 ~]# systemctl restart mysqld.service

2.独立表空间

1）查看独立表空间

#1.物理层面查看
[root@db01 ~]# ll /usr/local/mysql/data/xiangqin
总用量 144
-rw-rw---- 1 mysql mysql   8785 10月 28 19:05 user.frm
-rw-rw---- 1 mysql mysql 131072 10月 28 19:05 user.ibd

#2.数据库查看
mysql> show variables like '%per_table%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | ON    |
+-----------------------+-------+
1 row in set (0.00 sec)

四、企业案例二

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

1.模拟断电表损坏

#1.打包某个独立表空间
[root@db01 /usr/local/mysql/data]# tar zcf world.tar.gz world

#2.将打包的文件传输到一台数据库
[root@db01 /usr/local/mysql/data]# scp world.tar.gz 172.16.1.52:/tmp/

#3.将新数据库的文件解压到新数据库的数据目录下
[root@db02 ~]# tar xf /tmp/world.tar.gz -C /service/mysql/data/

#4.新数据库查看数据
mysql> use world;
mysql> show tables;
+-----------------+
| Tables_in_world |
+-----------------+
| city            |
| country         |
| countrylanguage |
| jixiao          |
+-----------------+
4 rows in set (0.00 sec)

#5.操作表数据
mysql> select * from city;
ERROR 1146 (42S02): Table 'world.city' doesn't exist

2.解决数据库表损坏的问题

#1.找一台新的数据库重新建新表
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`),
  KEY `index_key` (`Name`),
  KEY `idx_key` (`ID`),
  KEY `population_key` (`Population`),
  KEY `District_key` (`District`)
) ENGINE=InnoDB AUTO_INCREMENT=4080 DEFAULT CHARSET=latin1;

#2.数据库操作city_new清除自己的表空间
mysql> alter table city_new discard tablespace;

#3.物理拷贝city的数据文件
[root@db02 /service/mysql/data/world]# cp city.ibd city_new.ibd
[root@db02 /service/mysql/data/world]# chown -R mysql.mysql city_new.ibd

#4.city_new读取自己的表空间数据
mysql> alter table city_new import tablespace;

#5.数据查询
mysql> select * from city_new;

#6.删除损坏的表
mysql> drop table city;
ERROR 1051 (42S02): Unknown table 'world.city'		#只是说不认识，没说不能删除

[root@db02 /service/mysql/data/world]# rm city.ibd		#物理删除表数据

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

3.恢复业务

1.开发修改数据库连接信息
2.将数据重新导出再导入旧机器

五、InnoDB核心特性-事务

1.什么是事务

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

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

2.事务演示

#1.创建一个表
mysql> create table jiaoyi(id int,name varchar(10),money int);

#2.插入数据
mysql> insert jiaoyi values(1,'qiudao',300),(2,'lhd',200);

#3.开启一个事务
mysql> begin;
mysql> update jiaoyi set money=400 where id=2;
mysql> update jiaoyi set money=100 where id=1;

#4.提交事务之前，再开一个窗口查看数据，数据没有发生改变
mysql> select * from jiaoyi;
+------+--------+-------+
| id   | name   | money |
+------+--------+-------+
|    1 | qiudao |   300 |
|    2 | lhd    |   200 |
+------+--------+-------+
2 rows in set (0.00 sec)

#5.提交事务
mysql> commit;

#6.再次到新窗口查看数据
mysql> select * from jiaoyi;
+------+--------+-------+
| id   | name   | money |
+------+--------+-------+
|    1 | qiudao |   100 |
|    2 | lhd    |   400 |
+------+--------+-------+
2 rows in set (0.00 sec)

#7.再次开启事务修改数据
mysql> begin;
mysql> update jiaoyi set money=-100 where id=1;
mysql> update jiaoyi set money=600 where id=2;
mysql> select * from jiaoyi;
+------+--------+-------+
| id   | name   | money |
+------+--------+-------+
|    1 | qiudao |  -100 |
|    2 | lhd    |   600 |
+------+--------+-------+
2 rows in set (0.00 sec)

#8.结束事务之前，由程序判断，发现money钱数不能为负数，所以这次修改数不符合逻辑，只能回滚
mysql> rollback;
mysql> select * from jiaoyi;
+------+--------+-------+
| id   | name   | money |
+------+--------+-------+
|    1 | qiudao |   100 |
|    2 | lhd    |   400 |
+------+--------+-------+
2 rows in set (0.00 sec)

3.事务的通俗理解

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

我们理解的“交易”是什么？
	1）物与物的交换（古代）
	2）货币现金与实物的交换（现代1）
	3）虚拟货币与实物的交换（现代2）
	4）虚拟货币与虚拟实物交换（现代3）

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

4.事务完整流程

#成功的事务
begin;
sql1;
sql2;
....
commit;

#失败的事务
begin;
sql1;
sql2;
....
rollback;

5.事务的特性（ACID）

Atomic（原子性）
所有语句作为一个单元全部成功执行或全部取消。

Consistent（一致性）
如果数据库在事务开始时处于一致状态，则在执行该事务期间将保留一致状态。

Isolated（隔离性）
事务之间不相互影响。

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

6.事务的控制语句

#事务常用的语句
BEGIN：		开始一个新事务
COMMIT：		永久记录当前事务所做的更改
ROLLBACK：	回滚当前事务所做的更改

#事务指定回滚
SAVEPOINT：	分配事务过程中的一个位置，以供将来引用
ROLLBACK TO SAVEPOINT：取消在 savepoint 之后执行的更改
RELEASE SAVEPOINT：删除 savepoint 标识符

#自动提交
SET AUTOCOMMIT：为当前连接禁用或启用默认 autocommit 模式

7.自动提交

#临时关闭自动提交
mysql> set autocommit=0;

#永久关闭自动提交
[root@db01 ~]# vim /etc/my.cnf
[mysqld]
autocommit=0

8.隐式提交

1.现在版本在开启事务时，不需要手工begin，只要你输入的是DML语句，就会自动开启事务。
2.有些情况下事务会被隐式提交
	1）在事务运行期间，手工执行begin的时候会自动提交上个事务
	2）在事务运行期间，加入DDL、DCL操作会自动提交上个事务
	3）在事务运行期间，执行锁定语句（lock tables、unlock tables）
	
	4）load data infile
	5）select for update