一、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