一、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 startStarting MySQL. ERROR! The server quit without updating PID file (/usr/local/mysql/data/db01.pid).#3.看日志报错[root@db01 ~]# less /usr/local/mysql/data/db01.err2020-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
9.作业：
1.升级数据库操作流程
2.练习数据库表损坏如何修复
