💕9、Mysql存储引擎和DTL

mysql存储引擎&DTL

.idb 结尾的文件叫表空间


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


MySQL 提供以下存储引擎:
'01）InnoDB'    #主流的
'02）MyISAM'
03）MEMORY
04）ARCHIVE
05）FEDERATED
06）EXAMPLE
07）BLACKHOLE
08）MERGE
09）NDBCLUSTER
10）CSV

数据定义语言，建库建表

mysql自带的存储引擎

#查看mysql自带的存储引擎
root@localhost:(none) > show engines;
+--------------------+
| Engine             |
+--------------------+
| FEDERATED          |
| MRG_MYISAM         |
| MyISAM             |#
| BLACKHOLE          |
| CSV                |
| MEMORY             |
| ARCHIVE            |
| InnoDB             | #
| PERFORMANCE_SCHEMA |
+--------------------+

#查看mysql数据库中哪些表使用的存储引擎是InnoDB
root@localhost:(none) > select * from information_schema.tables  where ENGINE='InnoDB'\G;

#查看数据库中哪些表是myisam
root@localhost:(none) > select * from information_schema.tables where engine='myisam'\G

InnoDB和myisam的区别

#面试问题InnoDB和myisam的区别
mysiam的物理文件有3个，Inodb的物理文件有2个
Inodb是行级锁，mysiam是表级锁
inodb支持事务，redo和undo是实现CSR过程的2个事务日志，所以有CSR自动故障恢复；
mysiam不支持事务，没有redo和undo，没有CSR自动故障恢复
Inodb是行级锁，支持MVCC高版本并发控制；mysiam不支持，因为mysiam是表级锁(改一行，整个表都锁了)
高版本并发控制类似于行级锁，你改这行数据的时候，其他行的数据别人也是可以改的，因为有共享锁+排他锁+乐观锁构成
Inodb支持热备，mysiam不支持
(热备是按照事务的提交做的备份)

1、物理区别：最底层的数据文件不同

#mysiam数据文件  frm=fotmat 结构
#myisam的数据文件由3个组成
1、查看mysql的安装目录下的所有user文件
[root@db01 ~]# cd /app/mysql-5.6.50/data/mysql

2、查看所有user的文件
[root@db01 mysql]# ll user*
-rw-rw---- 1 mysql mysql 10684 Aug  7 23:30 user.frm
-rw-rw---- 1 mysql mysql   664 Aug 13 15:27 user.MYD
-rw-rw---- 1 mysql mysql  2048 Aug 13 22:01 user.MYI


#InoDd的数据文件由2个组成
进入随意一个库文件
[root@db01 student]# cd /app/mysql-5.6.50/data/student
[root@db01 student]# ll
-rw-rw---- 1 mysql mysql   8726 Aug 13 03:17 stu1.frm
-rw-rw---- 1 mysql mysql  98304 Aug 15 20:52 stu1.ibd

2、逻辑区别

mvcc叫多版本进化控制

锁定粒度：在修改一行数据中，别的用户过来是不可以修改的

把改的数据放到缓存里，先搞一个消息队列，把改的头像放到缓存里面，等上一个人修改完成后，再把你的头像放到数据库中，所以改头像要刷新一会才看到新改的头像

冷备：把服务停掉，做备份
热备：不停服务器，做备份
温备：


#innodb核心特性
重点:
MVCC
事务
行级锁
热备份
Crash Safe Recovery（自动故障恢复）

mysiam没有事务 就没有事务日志redo undo

#查看默认的存储引擎的3种方法：
1、#使用 SELECT 确认会话存储引擎
root@localhost:(none) > SELECT @@default_storage_engine;
+--------------------------+
| @@default_storage_engine |
+--------------------------+
| InnoDB                   |
+--------------------------+

2、#使用 show 确认每个表的存储引擎
root@localhost:(none) > show create table world.city;
建表语句最后一行显示存储引擎
ENGINE=InnoDB AUTO_INCREMENT=4080 DEFAULT CHARSET=latin1

3、#使用  informattion_schema 确认每个表的存储引擎
select table_name, engine from information_schema.tables where table_name='city' and table_schema='world'\G

*************************** 1. row ***************************
table_name: city
    engine: InnoDB

存储引擎的设置

#.存储引擎的设置     #不要去瞎改，使用默认的就行
1）#在配置文件的[mysqld]标签下添加 (永久生效)
[mysqld]
default-storage-engine=InnoDB  #这行配置就改你需要的存储引擎
注意：改了配置文件需要重启

2）#在MySQL命令行中临时设置(临时生效)
SET @@storage_engine=InnoDB

3）#建表的时候指定存储引擎
create table t (i int) engine = InnoDB;

企业案例1

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

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

如何解决：
1、建议将现有的myisam引擎替换为Innodb，将版本替换为5.7
1）如果使用MYISAM会产生”小问题”，性能安全不能得到保证，使用innodb可以解决这个问题。
2）5.7版本对于innodb引擎支持不够完善，5.7版本对innodb支持非常完善了。

2、实施过程和注意要素：但是这个代码上面有很大的改动，开发改好代码才做升级

升级的时候不能影响到用户

#不停库的操作
1、准备一个新环境，来一台新机器，因为有问题的机器不能动

2、在新环境中二进制安装一个mysql5.7.44，如果是容器的话就docker起一个

3、给当前正在提供服务的生产库，进行一个全备（打点全备，记录当前备份的位置点）
为什么做全备：因为在生产的数据库虽然不用了，但里面有很多系统表，mysql的user表,创建的用户，给开发开的用户，开发写的程序连接数据库的用户都在里面，包括要求的授权，脱敏的授权，如果再去一个一个创建就比较麻烦，如果要保证这些都还在，就要做全备
[root@db02 ~]# vim /etc/my.cnf
[mysql]   #这个标签所有客户端都识别
prompt="\\u@\\h:\\d > "
[root@db02 ~]# mysqldump -uroot -p123 -A --triggers -R --master-data=2 >/tmp/full.sql
-A         导出所有数据库。
--triggers 确保触发器也被导出
-R         导出存储过程和函数。
--master-data=2 导出的SQL文件中包含CHANGE MASTER TO语句，这些语句包含了二进制日志的位置信息，这对于设置复制环境非常有用。=2 的意思是，这些语句会被注释掉，使得它们默认不会被执行，但你可以根据需要取消注释它们。

4、将全备恢复到新环境
[root@db01 ~]# mysql -uroot -p123 < /tmp/full.sql

5、从新环境备份出程序库的所有表数据(做一个单库的备份)
mysqldump -uroot -p123 -B 程序库 > /tmp/程序库.sql
[root@db01 ~]# mysqldump -uroot -p123 -B student > /tmp/student.sql

6、修改表的存储引擎
[root@db01 ~]# sed -i 's#ENGINE=MYISAM#ENGINE=INNODB#g' /tmp/student.sql

7、将修改后的程序库数据到新环境
#新环境没开bin_log的导入方法
[root@db01 student]# mysqldump -uroot -p123 < /tmp/student.sql

#新环境开bin_log的导入方法：需要加入数据库
1）关闭bin_log的记录
root@localhost:(none) > set sql_log_bin=0;

2）导入数据
root@localhost:(none) > source /tmp/student.sql

3）开启bin_log的记录
root@localhost:(none) > set sql_log_bin=1;


#停库操作
8、先挂维护页，旧环境停止连接数据库的服务、数据库
[root@db02 ~]# systemctl stop tomcat;systemctl stop php-fpm;systemctl stop mysql

9、从bin_log中截取增量数据

10、启动新环境数据库

11、恢复截取的增量数据到新环境

12、测试环境连接新库，测试所有功能
    
13、应用割接到新数据库(改程序代码连接到数据库的信息,如果是云主机就改云主机的ip,物理机修改网卡的ip很麻烦,改程序代码连接到数据库)

14、启动连接数据库的服务

15、取消维护页

项目结果：
解决了'小问题'

mysql中的表空间

在mysql中表空间有2种
1、共享表空间(需要做切割)，所有表都能共享这里的数据
– 用来存储系统数据的
– 存undo
– 临时表(类似于连表查询出来的表)
2、独立表空间(inodb看到的idb文件)
– 存储用户真实数据
– 每一张inodb存储引擎的表都有一个自己独立表空间

• 共享表空间：用来存储系统数据、存undo、存临时表

#查看共享表空间
#物理查看
[root@db01 ~]# ll /app/mysql-5.6.50/data/
-rw-rw---- 1 mysql mysql       56 Aug  7 23:44 auto.cnf  #UUID文件
-rw-rw---- 1 mysql mysql 79691776 Aug 15 20:56 ibdata1   #共享表空间
-rw-rw---- 1 mysql mysql 50331648 Aug 15 20:56 ib_logfile0
-rw-rw---- 1 mysql mysql 50331648 Aug  7 23:30 ib_logfile1
...
...

#查看共享表空间的配置
root@localhost:(none) > show variables like '%path%';
+----------------------------------+------------------------+
| Variable_name                    | Value                  |
+----------------------------------+------------------------+
| innodb_data_file_path            | ibdata1:12M:autoextend |
+----------------------------------+------------------------+
这个表空间默认大写只能存储12M数据   autoextend自动扩展：如果说共享表空间越来越大，存储系统数据的、存undo、临时表的速度越来越慢，所以共享表空间要做切割


#共享表空间的切割
1、编辑配置文件
[root@db01 ~]# vim /etc/my.cnf
[mysqld]
innodb_data_file_path=ibdata1:50M;ibdata2:50M:autoextend  #后面再切的话改ibdata3 ibdata4就行了

2、重启数据库失败
[root@db01 ~]# /etc/init.d/mysqld restart
Shutting down MySQL.... SUCCESS! 
Starting MySQL.... ERROR! The server quit without updating PID file (/app/mysql-5.6.50/data/db01.pid).

3、查看日志
[root@db01 ~]# tailf -200 /app/mysql-5.6.50/data/db01.err 

#分析：
mysql里面最小的数据单位是页，一页16K
报错显示idbdata1的大小是4864页,而我在配置文件配置的是3200页
(4864*16)/1024=76M                (3200*16)/1024=50M
原本ibdata1共享表空间的大小为76M，但是配置文件中只给了50M不够

4、所以我在改的时候一定要看一下ibdata1共享表空间多大，改成一模一样的大小就好,后面的无所谓，因为后面的还没有生成
[root@db01 ~]# du -sh /app/mysql-5.6.50/data/ibdata1
76M     /app/mysql-5.6.50/data/ibdata1
表示ibdata1 文件占用了大约 76M 的磁盘空间

5、重新修改配置文件，再启动
[root@db01 ~]# vim /etc/my.cnf
[mysqld]
innodb_data_file_path=ibdata1:76M;ibdata2:50M:autoextend

[root@db01 ~]# /etc/init.d/mysqld restart

6、切割完成查看多出ibdata2，以后就往ibdata2保存系统数据，undo、临时表，ibdata1就固定在76M了
[root@db01 ~]# ll /app/mysql-5.6.50/data/
-rw-rw---- 1 mysql mysql 79691776 Aug 16 11:07 ibdata1
-rw-rw---- 1 mysql mysql 52428800 Aug 16 11:07 ibdata2  #会新增这个

• 独立表空间：存储用户真实数据、每一张inodb存储引擎的表都有一个自己独立表空间

所有的idb文件，就是每一张表的独立表空间

#查看独立表空间
[root@db01 ~]# ll /app/mysql-5.6.50/data/student/stu*
-rw-rw---- 1 mysql mysql   8726 Aug 13 03:17 stu1.frm
-rw-rw---- 1 mysql mysql  98304 Aug 15 20:52 stu1.ibd    #独立表空间

#查看独立表空间是否开启，默认是开启的
root@localhost:(none) > show variables like '%per_table%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | ON    |
+-----------------------+-------+

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

#5.7的临时表
[root@db03 ~]# ll /app/mysql-5.7.44/data/
-rw-r----- 1 mysql mysql 12582912 Aug  9 05:40 ibtmp1

企业案例2

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

环境准备：

主机名	ip	角色
db01	10.0.0.51 / 172.16.1.51	新环境
bdb02	10.0.0.52 / 172.16.1.52	旧环境：表结构损坏的数据库

模拟数据损坏

#模拟表结构损坏
准备数据库的备份文件传递到其他机器
[root@db01 ~]# cd /app/mysql-5.6.50/data/
[root@db01 data]# tar zcf world.tgz world
[root@db01 data]# scp world.tgz 172.16.1.52:/root

bdb02操作：
1、将库文件解压到mysql的data目录
[root@db02 ~]# tar xf world.tgz -C /app/mysql-5.6.50/data/

2、重启mysql
[root@db02 ~]# /etc/init.d/mysqld restart

3、连接数据库，查看库是否导入成功
[root@db02 ~]# mysql
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| world              |

4、查看表里面的数据，报错
mysql> use world
mysql> show tables;
+-----------------+
| Tables_in_world |
+-----------------+
| city            |
| country         |
| countrylanguage |
| score           |
+-----------------+
mysql> select * from city;
ERROR 1146 (42S02): Table 'world.city' doesn't exist

mysql> select * from country;
ERROR 1146 (42S02): Table 'world.country' doesn't exist


#如下恢复的操作需要准备新环境，但是想保留2个机器之前的数据，建议给db01、db02拍摄快照

恢复思路

1、#先停止db02连接mysql的所有服务，停止mysql数据库，准备新环境
由于我把db01当作新环境，并且拍摄了快照，我可以把之前的数据文件清理掉
[root@db01 ~]# /etc/init.d/mysqld stop
[root@db01 ~]# rm -rf /app/mysql-5.6.50/data/

重新初始化，就可以当作新环境的数据库
[root@db01 ~]# /app/mysql-5.6.50/scripts/mysql_install_db --user=mysql --basedir=/app/mysql-5.6.50 --datadir=/app/mysql-5.6.50/data
[root@db01 ~]# echo $?
0

新环境OK,启动mysql
[root@db01 ~]# /etc/init.d/mysqld start
Starting MySQL.Logging to '/app/mysql-5.6.50/data/db01.err'.
.. SUCCESS! 

[root@db01 ~]# ll /app/mysql-5.6.50/data/
total 229028
-rw-rw---- 1 mysql mysql       56 Aug 17 02:16 auto.cnf
-rw-rw---- 1 mysql mysql     1995 Aug 17 02:16 db01.err
-rw-rw---- 1 mysql mysql        5 Aug 17 02:16 db01.pid
-rw-rw---- 1 mysql mysql 79691776 Aug 17 02:16 ibdata1
-rw-rw---- 1 mysql mysql 52428800 Aug 17 02:15 ibdata2
-rw-rw---- 1 mysql mysql 50331648 Aug 17 02:16 ib_logfile0
-rw-rw---- 1 mysql mysql 50331648 Aug 17 02:15 ib_logfile1
drwx------ 2 mysql mysql     4096 Aug 17 02:15 mysql
-rw-rw---- 1 mysql mysql    69408 Aug 17 02:15 mysql-bin.000001
-rw-rw---- 1 mysql mysql  1640526 Aug 17 02:15 mysql-bin.000002
-rw-rw---- 1 mysql mysql      120 Aug 17 02:16 mysql-bin.000003
-rw-rw---- 1 mysql mysql       57 Aug 17 02:16 mysql-bin.index
drwx------ 2 mysql mysql     4096 Aug 17 02:15 performance_schema
drwx------ 2 mysql mysql        6 Aug 17 02:15 test

2、#旧环境的表结构(字段，数据类型，约束)已经损坏，需要找开发要表结构（就是建表语句）
#删掉外键创建语句:  CONSTRAINT `city_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `country` (`Code`)

 CREATE TABLE world.city (
  `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 `Population` (`Population`),
  KEY `inx_aa` (`CountryCode`)
) ENGINE=InnoDB AUTO_INCREMENT=4080 DEFAULT CHARSET=latin1;



3、#登陆新环境的数据库，创建库、表
[root@db01 ~]# mysql 
root@localhost:(none) > create database world;

root@localhost:(none) > CREATE TABLE world.city (
  `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 `Population` (`Population`),
  KEY `inx_aa` (`CountryCode`)
) ENGINE=InnoDB AUTO_INCREMENT=4080 DEFAULT CHARSET=latin1;

查看表结构完整，但是没数据
root@localhost:(none) > 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       |                |
+-------------+----------+------+-----+---------+----------------+
root@localhost:(none) > select * from world.city;
Empty set (0.00 sec)

那么数据在哪里呢，数据在独立表空间
[root@db01 ~]# ll /app/mysql-5.6.50/data/world/
total 16
-rw-rw---- 1 mysql mysql 8710  Aug 17 02:24 city.frm
-rw-rw---- 1 mysql mysql 147456 Aug 17 02:24 city.idb #独立表空间
-rw-rw---- 1 mysql mysql   61 Aug 17 02:22 db.opt

4、#删除新环境的独立表空间
root@localhost:(none) > alter table world.city discard tablespace;

[root@db01 ~]# ll /app/mysql-5.6.50/data/world/
total 16
-rw-rw---- 1 mysql mysql 8710 Aug 17 02:24 city.frm
-rw-rw---- 1 mysql mysql   61 Aug 17 02:22 db.opt
#独立表空间被删除

5、#从旧环境db02将city表的表空间复制过来
[root@db02 ~]# cd /app/mysql-5.6.50/data/world/
[root@db02 world]# scp city.ibd 172.16.1.51:/app/mysql-5.6.50/data/world/

6、#新环境db01给独立表空间授权
[root@db01 ~]# chown -R mysql.mysql /app/mysql-5.6.50/data/world

7、#新环境db01导入表空间
root@localhost:(none) > alter table world.city import tablespace;

8、#查询数据
root@localhost:(none) > select * from world.city;

9、#程序还在旧数据库上，测试没问题之后，就用于割接
   - 方法一：开发修改代码连接数据库的IP，开发改完代码要走代码上线的流程，审批，测试，..发布
   - 方法二：运维导出新环境的数据，恢复到旧环境

10、#启动连接数据库的服务

Innodb核心特性——事务

什么是事务
主要针对DML语句（update，delete，insert）

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

**事物的生命周期：**所有步骤都成功或都失败
1）如果所有步骤正常，则执行
2）如果步骤出现错误或不完整，则取消

#成功的事物
#开启事物
begin：
然后执行DML语句

#提交事物
commit;

#失败的事物
#开启事物
begin：
然后执行DML语句

#回滚事物
rollback     

执行commit或者rollback,事物不会直接写在磁盘里，是先写在日志里面，写入日志比较快，日志优先写

事物的通俗理解：伴随着“交易”出现的数据库概念。我们理解的“交易”是什么？
1）物与物的交换（古代）
2）货币现金与实物的交换（现代1）
3）虚拟货币与实物的交换（现代2）
4）虚拟货币与虚拟实物交换（现代3）

事物的特性

A：原子性：开启一个事物，要么操作全部成功，事物才会结束，如果失败一次，就回滚
C：一致性：事物开启之前和事物执行结束后，数据状态保存一致
I：隔离性：事物与事物之间是隔离的(和锁有关系，锁又和隔离级别有关系)
D：持久性：事物操作完成后要持久化

事物流程举例

事物的流程控制语句 (DTL ：Databases Transaction Language)

DTL也叫TCL(Transaction Control Language)

#模拟操作事物(银行取钱)
1、#创建一个交易表，并插入数据(一开始两个人的初始余额是500)
root@localhost:student > create table jiaoyi(money int,name varchar(10));
root@localhost:student > insert into  jiaoyi values(500,'zhang3'),(500,'lisi');
root@localhost:student > select * from jiaoyi;
+-------+--------+
| money | name   |
+-------+--------+
|   500 | zhang3 |
|   500 | lisi   |
+-------+--------+

2、#开启事物
root@localhost:student > start transaction;  #或者begin：都可以显示开启一个新事物

3、#进行交易，zhang3给lisi转账300
root@localhost:student > update jiaoyi set money=800 where name='lisi';
root@localhost:student > update jiaoyi set money=200 where name='zhang3';
root@localhost:student > select * from jiaoyi;
+-------+--------+
| money | name   |
+-------+--------+
|   200 | zhang3 |
|   800 | lisi   |
+-------+--------+

4、#开启一个新终端B，查看数据，没有变化，因为事物还没有结束
root@localhost:student > select * from jiaoyi;
+-------+--------+
| money | name   |
+-------+--------+
|   500 | zhang3 |
|   500 | lisi   |
+-------+--------+

5、#结束事物
A终端结束事物
root@localhost:student > commit;

B终端查看数据
root@localhost:student > select * from jiaoyi;
+-------+--------+
| money | name   |
+-------+--------+
|   200 | zhang3 |
|   800 | lisi   |
+-------+--------+

现在的这个表对于mysql来说，就是一行，现在看到的格式化的表，是存储引擎做的格式化，如果想让mysql认为是两行数据，表里面必须要加主键
1、#给表增加主键，初始化余额
root@localhost:student > alter table jiaoyi add id int primary key auto_increment first;
root@localhost:student > update jiaoyi set money=500 where id=2;

root@localhost:student > select * from jiaoyi;
+----+-------+--------+
| id | money | name   |
+----+-------+--------+
|  1 |   500 | zhang3 |
|  2 |   500 | lisi   |
+----+-------+--------+

2、#A终端开启一个事物，进行交易，zhang3给lisi转账300
root@localhost:student > begin;
root@localhost:student > update jiaoyi set money=800 where id=2;#这个时候条件必须用id,如果用name,对于MySQL来说，不能识别为一行
root@localhost:student > update jiaoyi set money=200 where id=1;
root@localhost:student > select * from jiaoyi;
+----+-------+--------+
| id | money | name   |
+----+-------+--------+
|  1 |   200 | zhang3 |
|  2 |   800 | lisi   |
+----+-------+--------+

3、#B终端开启一个事物，有人往zhang3的账号转1000元
root@localhost:student > begin;
root@localhost:student > update jiaoyi set money=1000 where id=1;
不同的行，事物与事物之间不影响
如果是同一行，都在操作事物，就要等先开启的事物完成操作之后，后开启的事物才进行操作

4、AB终端结束事物
root@localhost:student > commit;
root@localhost:student > select * from jiaoyi;
+----+-------+--------+
| id | money | name   |
+----+-------+--------+
|  1 |  1200 | zhang3 |
|  2 |   800 | lisi   |
+----+-------+--------+

事物的自动提交

mysql不是执行begin的时候开启事物，是执行DML语句后，会自动开启事物，因为mysql里面默认有一个配置：
root@localhost:student > show variables like '%auto%';
+-----------------------------+-------+
| Variable_name               | Value |
+-----------------------------+-------+
| auto_increment_increment    | 1     |
| auto_increment_offset       | 1     |
| autocommit                  | ON    |#默认mysql开启自动提交
| automatic_sp_privileges     | ON    |
| innodb_autoextend_increment | 64    |
| innodb_autoinc_lock_mode    | 1     |
| innodb_stats_auto_recalc    | ON    |
| sql_auto_is_null            | OFF   |
+-----------------------------+-------+

#查看bin_log日志
[root@db01 ~]# mysqlbinlog --base64-output=decode rows -vvv /app/mysql-5.6.50/data/mysql-bin.000010


1、#在事物执行期间，如果执行DDL、DCL语句，都会导致事物自动提交

2、#在事物执行期间，如果执行begin，会自动提交上一次事物，开启一个新事物

3、#在事物执行期间，如果执行锁定语句(lock tables、unlock tables),会自动提交事物

4、#在事物执行期间，执行导出数据(load data infile)

5、#在事物执行期间，执行(select for update)

6、#在执行autocommit=1的时候


## 关于事务的语句
START TRANSACTION（或 BEGIN）：显式开始一个新事务
# SAVEPOINT：分配事务过程中的一个位置，以供将来引用
root@localhost:(none):47: >savepoint lzd_1000;
# COMMIT：永久记录当前事务所做的更改
# ROLLBACK：取消当前事务所做的更改
# ROLLBACK TO SAVEPOINT：取消在 savepoint 之后执行的更改
root@localhost:(none):48: >rollback to savepoint lzd_1000;
# RELEASE SAVEPOINT：删除 savepoint 标识符
root@localhost:(none):49: >release savepoint lzd_1000;
# 临时设置自动提交
SET AUTOCOMMIT：为当前连接禁用或启用默认 autocommit 模式
root@localhost:(none):50: >set autocommit=1;
root@localhost:(none):50: >set autocommit=0;

事物日志redo

MySQL特性：WAL特性 Write Ahead Log 日志优先写

#查看mysql的redo   在磁盘上面存着
[root@db01 ~]# ll /app/mysql-5.6.50/data/
-rw-rw---- 1 mysql mysql 50331648 Aug 18 00:20 ib_logfile0
-rw-rw---- 1 mysql mysql 50331648 Aug  7 23:30 ib_logfile1

redo的意思：重做

redolog的工作流程

自动故障恢复CSR 把数据该模拟的模拟，该变化的变化，写入到表空间

断电：

1、数据commit,数据写入ib_logfile(redolog)时，突然断电

2、没有commit,数据写入ib_logfile(redo log)时

3、数据没有commit,没有记录在redo log中

这3种情况的数据如何处理

#情况1:数据commit,数据写入ib_logfile(redolog)时，突然断电，表空间的数据怎么恢复，如果不恢复，用户看到数据还是500，是不对的

#情况2：没有commit,数据写入ib_logfile(redo log)时，突然断电

这种情况会出现问题，数据不能恢复，只能将变化过程记录到redo log，不能把模拟出来的800写入独立表空间,所以需要配合undo

#情况3：没有commit,也没有写入ib_logfile(redo log)时，突然断电

没有commit,也没有写入ib_logfile(redo log)时，就不会有变化过程，原数据还是原数据，没什么变化

事物日志undo

#查看mysql的undo
[root@db01 ~]# ll /app/mysql-5.6.50/data/
-rw-rw---- 1 mysql mysql 79691776 Aug 18 00:20 ibdata1
-rw-rw---- 1 mysql mysql 52428800 Aug 18 00:20 ibdata2

undo：不做   



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

_2）作用是什么？
在事务ACID过程中，实现的是“A”原子性的作用。当然CI的特性也和undo有关

面试问题：
#mysql CSR的过程和什么有关
CSR和2个日志文件有关，事物日志redo、undo，redo重做数据，undo不做数据，他们分别在MySQL的data目录下
redo就在数据目录下
undo:在MySQL5.6版本中undo是在ibdata(共享表空间)文件中，在MySQL5.7版本会独立出来。

MYSQL CSR的过程 断电之后，undo结合redo，恢复数据

#情况1:数据commit,数据写入ib_logfile(redolog)时，突然断电，表空间的数据怎么恢复，

突然断电，内存里面的数据没了

#情况2：没有commit,数据写入ib_logfile(redo log)时，突然断电

断电时，redo配合undo恢复数据
#情况3:没有commit,没有写入ib_logfile(redolog)时，突然断电,表空间的数据怎么恢复

事物中的锁

1）什么是“锁”？
“锁”顾名思义就是锁定的意思。

2）“锁”的作用是什么？
在事务ACID特性过程中，“锁”和“隔离级别”一起来实现“I”隔离性的作用。

最后的数据A=2 因为谁先提交，以谁为准，条件后面的A=1没有了，被第二个事务改掉了

排他锁：所谓的行及锁就是排他锁造成的，在事物执行期间,不允许其他事物执行修改操作
共享锁：在事物执行期间，允许所有事物执行查询操作

他两结合就是：阻塞修改删除操作，不阻塞查询操作

乐观锁：谁先提交以谁为准
悲观锁：在事物执行期间，阻塞其他事物的查询操作。剩1张票，我抢了，不管是否付款，你看不到这个票

多版本并发控制(MVCC)

1）只阻塞修改类操作，不阻塞查询类操作
2）乐观锁的机制（谁先提交谁为准），mysql不能演示，因为隔离级别存在

#锁的粒度
MyIsam：低并发锁（表级锁）
Innodb：高并发锁（行级锁）

事务的隔离级别

#查看隔离级别
root@localhost:(none) > show variables like '%iso%';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| tx_isolation  | REPEATABLE-READ |
+---------------+-----------------+

四种隔离级别：
1、#READ UNCOMMITTED（未提交读） #RU级别
允许事务查看其他事务所进行的未提交更改

2、#READ COMMITTED(已提交读)  #RC级别
允许事务查看其他事务所进行的已提交更改

3、#REPEATABLE READ（重复读） #RR级别
确保每个事务的 SELECT 输出一致，比如说，你冲钱了，要刷新或者退出，才可以看到更新后的钱
InnoDB 的默认级别

4、#SERIALIZABLE (串行化)  #不用这个级别
将一个事务的结果与其他事务完全隔离
在事物执行期间，查询到的数据是undo里面=的数据，
查询的时候，阻塞修改

#查看隔离级别
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

#mysql以前版本存在的问题：分别是怎么产生的，简单了解解决方案
1、脏读:隔离级别是RU级别就产生脏读，脏读读取到的是其他事务中未提交的数据。比如说有一个事务，A给B转钱，一开始两个人都是500，A给B转300，B就有800并去查，查看到800，但是A没有提交，B看到的就是脏数据，因为A的余额可能不足，回滚转钱     

解决办法：提高隔离基本RC级别，可以解决脏读的问题

2、幻读：已提交读开2个事务，去查看同一行数据
在一个主键中明明没有查到主键为X的数据，但主键为X的数据就是插不进去，就像某种幻觉一样
解决方案：使用RR级别解决，使用事务的隔离级别来避免幻读。与脏读类似，通过设置合适的隔离级别可以解决幻读问题。此外，还可以使用行级锁或表级锁来限制查询的范围，从而避免幻读的发生。


3、不可重复读：一个事务先后执行同一条SQL,但2次读取到的数据不同，就是不可重复读

解决方案：同样可以使用事务的隔离级别和行级锁来避免不可重复读。另外，MySQL还提供了一个特殊的锁——可重复读锁（Repeatable Read），它可以避免不可重复读的问题。要使用可重复读锁，可以在查询语句前加上FOR REPLICATE READ关键字：


#脏读是读到了其他事务未提交的数据，而不可重复读是读到了其他事务已经提交的数据，但前后查询的结果不同，而幻读则是明明查询不到，但就是插入不了。


#redis存在的问题
缓存击穿：当有1个key，设置有生命周期，当一个请求过来后，查询这个可以时，刚好这个key到期了，直接穿过缓存，打到数据库上。当有大量的并发请求打到数据库上，会导致数据库被打垮

缓存穿透：意味着这个数据，数据库里面没有，所以不会把数据放到redis缓存里，只要有人来查询，就一定缓存中查不到，所以一定要走数据库
那么，假设很多人，故意去查那些数据库里也没有的记录，我们的 redis 就起不到屏障的作用，因为 redis 里不可能有数据，所以并发查询就一定会打到数据库的身上。


击穿：数据库里'有'数据
穿透：数据库里'没有'数据

缓存雪崩：大面积的key同时过期，大量的并发打到数据库
不像缓冲击穿，只是因为一个key的过期，所以，对于雪崩来说，一般少量的key失效，所带来的数据库并发压力不是很大，而大量的key同时失效，导致key的并发加起来，会影响到我们的数据库。那就算一个key失效，也会对数据库造成很大的影响，那么把雪崩的所有key拆除一个一个过期的key来看，也就是雪崩可以拆分成一个一个缓存击穿的集合，把数据库打崩

解决这些问题都是开发写代码去解决