MySQL 存储引擎
1. 什么是存储引擎
相当于MySQL内置的文件系统。
与Linux中的文件系统打交道的层次结构。
2. MySQL存储引擎种类
2.1 Oracle MySQL
可以不同的表，设定不同的存储种类 mysql> show engines; +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ | Engine | Support | Comment | Transactions | XA | Savepoints | +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ | MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO | | CSV | YES | CSV storage engine | NO | NO | NO | | MyISAM | YES | MyISAM storage engine | NO | NO | NO | | BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO | | PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO | | InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES | | ARCHIVE | YES | Archive storage engine | NO | NO | NO | | MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO | | FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL | +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ 9 rows in set (0.00 sec)
2.2 其他分支
perqona : XtraDB MariaDB : InnoDB 其他的引擎： TokuDB, MyRocks https://www.percona.com/software/mysql-database/percona-tokudb https://www.percona.com/doc/percona-tokudb/index.html Compression: 25x for high compression. Fast Insertions and Deletions: 适合于，业务当中有大量插入或者删除操作的场景。 应用于，数据量较大的业务。
2.3 案例：
zabbix监控系统架构整改 2.3.1 环境: zabbix 3.2 + centos 7.3 + mariaDB 5.5 InnoDB引擎，zabbix系统 监控了2000多个节点服务 2.3.2 现象 : zabbix卡的要死 , 每隔3-4个月,都要重新搭建一遍zabbix,存储空间经常爆满. 2.3.3 问题 : 1. zabbix 版本 2. 数据库版本 3. zabbix数据库500G,存在一个文件里 ibdata1，手工删除1个月之前的数据，空间不释放。 2.3.4 优化建议: 1. 数据库版本升级到percona 5.7+ 版本 mariaDB 10.1+,zabbix升级更高版本 2. 存储引擎改为tokudb 3. 监控数据按月份进行切割(二次开发:zabbix 数据保留机制功能重写,数据库分表) 4. 关闭binlog和双1 5. 参数调整.... 优化结果: 监控状态良好 2.3.5 为什么? 1. 原生态支持TokuDB,另外经过测试环境,5.7要比5.5 版本性能 高 2-3倍 2. TokuDB:insert数据比Innodb快的多，数据压缩比要Innodb高 3. 监控数据按月份进行切割,为了能够truncate每个分区表,立即释放空间 4. 关闭binlog ----->减少无关日志的记录. 5. 参数调整...----->安全性参数关闭,提高性能. 参考内容： https://www.jianshu.com/p/898d2e4bd3a7 https://mariadb.com/kb/en/installing-tokudb/ https://www.percona.com/doc/percona-server/5.7/tokudb/tokudb_installation.html
3. InnoDB 核心特性介绍
Clustered index 聚簇索引 Change buffer 自适应hash索引：AHI MVCC Multi-Version Concurrency Control多版本并发控制 多缓冲区池 事务 Transaction 行级锁粒度 Row-level Lock 外键 更多复制特性 支持热备 Hot Backup 自动故障恢复 Replication: Group Commit , GTID (Global Transaction ID) ,多线程(Multi-Threads-SQL ) 请你列举MySQL InnoDB存储优点？ 请你列举 InooDB和MyIsam的区别？
小项目：
InnoDB个MyISAM存储引擎的替换 环境: centos 5.8 ,MySQL 5.0版本,MyISAM存储引擎,网站业务(LNMP),数据量50G左右 现象问题: 业务压力大的时候,非常卡;经历过宕机,会有部分数据丢失. 问题分析: 1.MyISAM存储引擎表级锁,在高并发时,会有很高锁等待 2.MyISAM存储引擎不支持事务,在断电时,会有可能丢失数据 职责 1.监控锁的情况:有很多的表锁等待 2.存储引擎查看:所有表默认是MyISAM 解决方案: 1.升级MySQL 5.6.10版本 2. 迁移所有表到新环境 3. 开启双1安全参数
4. 存储引擎的管理命令
4.1 使用 SELECT 确认会话存储引擎
SELECT @@default_storage_engine; mysql> SELECT @@default_storage_engine; +--------------------------+ | @@default_storage_engine | +--------------------------+ | InnoDB | +--------------------------+ 1 row in set (0.00 sec)
4.2 默认存储引擎(不代表生产操作)
4.2.1 会话级别:
set default_storage_engine=myisam;
4.2.2 全局级别(仅影响新会话):
set global default_storage_engine=myisam; 重启之后,所有参数均失效.
4.2.3 永久生效:
写入配置文件 vim /etc/my.cnf [mysqld] default_storage_engine=myisam 存储引擎是表级别的,每个表创建时可以指定不同的存储引擎,但是我们建议统一为innodb.
4.3 SHOW 确认每个表的存储引擎：
show create table city\G show table status like 'countrylanguage'\G
mysql> show create table city\G *************************** 1. row *************************** Table: city Create Table: CREATE TABLE `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 `idx` (`Population`), KEY `idx_c_p` (`CountryCode`,`Population`), CONSTRAINT `city_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `country` (`Code`) ) ENGINE=InnoDB AUTO_INCREMENT=4080 DEFAULT CHARSET=utf8mb4 1 row in set (0.00 sec) mysql> show table status like 'countrylanguage'\G *************************** 1. row *************************** Name: countrylanguage Engine: InnoDB Version: 10 Row_format: Dynamic Rows: 984 Avg_row_length: 99 Data_length: 98304 Max_data_length: 0 Index_length: 65536 Data_free: 0 Auto_increment: NULL Create_time: 2020-07-30 15:41:14 Update_time: NULL Check_time: NULL Collation: utf8mb4_general_ci Checksum: NULL Create_options: Comment: 1 row in set (0.00 sec)
4.4 INFORMATION_SCHEMA 确认每个表的存储引擎
select table_schema,table_name ,engine from information_schema.tables where table_schema not in ('sys','mysql','information_schema','performance_schema'); show table status; show create table city;
mysql> select table_schema,table_name ,engine from information_schema.tables where table_schema not in ('sys','mysql','information_schema','performance_schema'); +--------------+-----------------+--------+ | table_schema | table_name | engine | +--------------+-----------------+--------+ | world | city | InnoDB | | world | country | InnoDB | | world | countrylanguage | InnoDB | +--------------+-----------------+--------+ 3 rows in set (0.04 sec) mysql> show table status; +-----------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+ | Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment | +-----------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+ | city | InnoDB | 10 | Dynamic | 4046 | 101 | 409600 | 0 | 294912 | 0 | 4080 | 2020-08-02 11:48:33 | NULL | NULL | utf8mb4_general_ci | NULL | | | | country | InnoDB | 10 | Dynamic | 239 | 479 | 114688 | 0 | 0 | 0 | NULL | 2020-07-30 15:41:14 | NULL | NULL | utf8mb4_general_ci | NULL | | | | countrylanguage | InnoDB | 10 | Dynamic | 984 | 99 | 98304 | 0 | 65536 | 0 | NULL | 2020-07-30 15:41:14 | NULL | NULL | utf8mb4_general_ci | NULL | | | +-----------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+ 3 rows in set (0.00 sec) mysql> show create table city; +-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | city | CREATE TABLE `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 `idx` (`Population`), KEY `idx_c_p` (`CountryCode`,`Population`), CONSTRAINT `city_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `country` (`Code`) ) ENGINE=InnoDB AUTO_INCREMENT=4080 DEFAULT CHARSET=utf8mb4 | +-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
4.5 修改一个表的存储引擎
alter table t1 engine innodb;
注意：此命令我们经常使用他，进行innodb表的碎片整理
mysql> desc information_schema.tables; +-----------------+---------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------------+---------------------+------+-----+---------+-------+ | DATA_FREE | bigint(21) unsigned | YES | | NULL | | +-----------------+---------------------+------+-----+---------+-------+ mysql> select table_schema,table_name,data_free from information_schema.tables; +--------------------+------------------------------------------------------+-----------+ | table_schema | table_name | data_free | +--------------------+------------------------------------------------------+-----------+ | information_schema | CHARACTER_SETS | 0 | | information_schema | COLLATIONS | 0 | | information_schema | COLLATION_CHARACTER_SET_APPLICABILITY | 0 | | information_schema | COLUMNS | 8388608 | | information_schema | COLUMN_PRIVILEGES | 0 | +--------------------+------------------------------------------------------+-----------+
4.6 平常处理过的MySQL问题--碎片处理
环境:centos7.4,MySQL 5.7.20,InnoDB存储引擎 业务特点:数据量级较大,经常需要按月删除历史数据. 问题:磁盘空间占用很大,不释放 处理方法: 以前:将数据逻辑导出,手工drop表,然后导入进去 现在: 对表进行按月进行分表(partition,中间件)或者归档表(pt-archive) 业务替换为truncate方式
4.7 扩展:如何批量修改
需求1:将zabbix库中的所有表,innodb替换为tokudb select concat("alter table zabbix.",table_name," engine tokudb;") from information_schema.tables where table_schema='zabbix' into outfile '/tmp/tokudb.sql'; 需求2：将所有非 InnoDB 业务表查询出来，并修改为InnoDB select concat("alter table ",table_schema,".",table_name," engine=innodb;") from information_schema.tables where engine != 'InnoDB' and table_schema not in ('sys','performance_schema','information_schema','mysql') into outfile '/tmp/alter.sql' ; 问：2亿行的表，想要删除其中1000w，你们公司都怎么做的？ 假如是按照时间列条件 pt-archive 回答： 1. 如果2亿行数据表，还没有生成，建议在设计表时，采用分区表的方式（按月range）， 2. 如果2亿行数据表，已经存在，建议使用pt-archive 工具进行归档表，并且删除无用数据。
5. MySQL存储引擎体系结构
5.1 宏观结构
5.1.1 MyISM
mysql> use world mysql> create table myt(id int) engine=myisam; [root@db01 world]$ cd /data/3306/world/ myt.frm ：数据字典信息（列的定义和属性） myt.MYD ：数据行 myt.MYI ：索引 [root@db01 world]$ cd /data/3306/mysql/
5.1.2 InnoDB
[root@db01 mysql]$ cd ../world city.frm ：数据字典信息（列的定义和属性） city.ibd ：独立表空间/用户表空间文件 ---> 数据行，索引 [root@db01 ~]$ cd /data/3306 ibdata1 : 系统表空间/共享表空间文件 ---> 数据字典信息（总信息），UNDO，double write 磁盘区域，change buffer磁盘区域 说明：不同版本ibdata1中存储的数据不一样 5.5 ：ibdata1 还会存储临时表数据 + 用户数据（数据行+索引） 5.6 ：ibdata1 还会存储临时表数据 + 用户数据 8.0 ：ibdata1 取消存储数据 字典信息，undo独立了。 你可以理解为，MySQL在慢慢瘦身ibdata1共享表空间，把比较关键的数据独立出来了。。 [root@db01 ~]$ cd /data/3306 ib_logfile0 ：事务日志文件 ib_logfile1 ：事务日志文件 ib_logfile0~ib_logfileN: InnoDB 事务重做日志（redo log） [root@db01 ~]$ cd /data/3306 ibtmp1 ：临时表空间 文件（排序，分组，多表连接，子查询，逻辑备份等） [root@db01 ~]$ cd /data/3306 ib_buffer_pool ：正常关库的时候，存储环翠区的热数据。顺序IO，减少随机IO 查看InnoDB Architecture文档 所以：仅仅是拷贝ibd 和 frm 文件到新的数据库，是无法正常使用。
5.2 微观结构
5.2.1 磁盘
数据存储：
（1）表空间
1). 什么是表空间？
表空间概念是引入于Oracle数据库。
起初为了解决存储空间扩展的问题。MySQL5.5版本引入了共享表空间模式。
2). MySQL 表空间类型 Tablespaces
共享表空间 -->The System Tablespace : 在5.5版本引入了共享表空间(ibdata1),作为默认存储方式。 用来存储：系统数据，日志，undo，临时表，用户数据和索引。 独立表空间 -->File-Per-Table Tablespaces : 5.6版本默认独立表空间模式。单表单表空间 普通表空间 -->General Tablespaces : 完全和Oracle一致的表空间管理模式 undo表空间 -->Undo Tablespaces : 存储undo logs(回滚日志) 临时表空间 -->The Temporary Tablespace : 存储临时表。5.7默认独立。
3). 表空间管理
用户数据默认的存储方式，独立表空间模式。独立表空间和共享表空间是可以互相切换的。 -- 查看默认表空间模式？ mysql> select @@innodb_file_per_table; 说明： 1代表独立表空间 0代表共享表空间模式 -- 如何切换？ 临时： mysql> set global innodb_file_per_table=0; 重新登录会话: 永久： vim /etc/my.cnf innodb_file_per_table=0 -- 验证 mysql> select @@innodb_file_per_table; 说明： 修改完成之后，只影响新创建的表. -- 如何扩展共享表空间大小和个数？ 说明：通常是在初始化数据时，就设定好参数 mysql> select @@innodb_data_file_path; +-------------------------+ | @@innodb_data_file_path | +-------------------------+ | ibdata1:12M:autoextend | +-------------------------+ 1 row in set (0.05 sec) 方法1：初始化之前，需要在my.cnf加入以下配置即可： innodb_data_file_path=ibdata1:1G;ibdata2:1G:autoextend 方法2：已运行的数据库上扩展多个ibdata文件 错误的方式： innodb_data_file_path=ibdata1:128M;ibdata2:128M;ibdata3:128M:autoextend [root@db01 ~]$ vim /etc/my.cnf [mysqld] innodb_data_file_path=ibdata1:128M;ibdata2:128M;ibdata3:128M:autoextend Shutting down MySQL...... SUCCESS! Starting MySQL.... ERROR! The server quit without updating PID file (/data/3306/db01.pid). [root@db01 world]$ cd /data/3306 db01.err [root@db01 3306]$ hostname db01 [root@db01 3306]$ cat db01.err [ERROR] InnoDB: The innodb_system data file './ibdata1' is of a different size 4864 pages (rounded down to MB) than the 8192 pages specified in the .cnf file! 解决方法，在设置innodb_data_file_path参数时，已有的ibdata1文件大小应该和磁盘上真实大小一致，而不是随便指定的 正确的指定方法： [root@db01 3306]$ vim /etc/my.cnf [mysqld] innodb_data_file_path=ibdata1:76M;ibdata2:128M;ibdata3:128M:autoextend [root@db01 3306]$ ll ibdata1 ibdata2 ibdata3
（2）段 区 页
表 ----> 表空间 ----> 段 ----> 多个区 ----> 连续的page ----> 连续的block ----> 连续的扇区
事务日志：
redo log 重做日志 （1）文件位置： /data/3306/ib_logfile0~ib_logfileN （2）控制参数： mysql> show variables like '%innodb_log%'; innodb_log_file_size=50331648 # 设置文件大小 innodb_log_files_in_group=2 # 设置文件个数 innodb_log_group_home_dir=./ # 设置存储位置 （3）功能： 用来存储，MySQL在做修改类（DML）(insert update delete)操作时的数据页变化过程及版本号（LSN），属于物理日志。 默认两个文件存储redo，是循环覆盖使用的。
undo logs 回滚日志
（1）文件位置：（5.7 默认位置） [root@db01 ~]$ cd /data/3306 ibdataN ibtmp1 （2）参数： mysql> show variables like '%undo%'; mysql> show variables like '%segments%'; innodb_rollback_segments=128 （3）功能： 用来存储回滚日志，可以理解为记录了每次操作的反操作，属于逻辑日志。 1. 使用快照功能，提供InnoDB多版本并发读写。 2. 通过记录的反操作，提供回滚功能。
5.2.2 内存
（1）数据内存区域：
（1.1） 共享内存区域 buffer pool 缓冲区池： 参数： mysql> select @@innodb_buffer_pool_size; 功能： 缓冲 数据页 + 索引页 （1.2）会话内存缓冲区区域 mysql> show variables like '%buffer%'; join_buffer_size key_buffer_size read_buffer_size read_rnd_buffer_size sort_buffer_size
（2）日志
innodb_log_buffer_size=16777216 功能：负责redo日志的缓冲 MySQL总共使用内存=共享+会话内存*会话个数+额外的内存使用（文件系统缓存）
真实的学生案例
案例背景: 1. 硬件: 联想服务器（IBM） 磁盘500G 没有raid 2. 软件环境: centos 6.8 mysql 5.6.33 innodb引擎 独立表空间 3. 备份没有，日志也没开 4. 开发用户专用库: jira(bug追踪) 、 confluence(内部知识库) ------>LNMT 5. 故障描述: 断电了，启动完成后“/” 只读 开发 fsck 重启,系统成功启动,mysql启动不了。 6. 结果：confulence库在 ， jira库不见了 7. 学员求助内容: 求助： 这种情况怎么恢复？ 我问： 有备份没 求助： 连二进制日志都没有，没有备份，没有主从 我说： 没招了，jira需要硬盘恢复了。 求助： 1、jira问题拉倒中关村了 2、能不能暂时把confulence库先打开用着 将生产库confulence，拷贝到1:1虚拟机上/var/lib/mysql,直接访问时访问不了的 问：有没有工具能直接读取ibd 我说：我查查，最后发现没有 我想出一个办法来： 表空间迁移: （1） 创建一个一模一样的t1空表 create table t1 （2） 删掉空表的表空间 ibd alter table confulence.t1 discard tablespace; （3） 拷贝准备好的ibd，该权限，导入表空间到t1表 alter table confulence.t1 import tablespace; 虚拟机测试可行。 处理问题思路: confulence库中一共有107张表。 1、创建107和和原来一模一样的表。 他有2016年的历史库，我让他去他同时电脑上 mysqldump备份confulence库 mysqldump -uroot -ppassw0rd -B confulence --no-data >test.sql 拿到你的测试库，进行恢复 到这步为止，表结构有了。 2、表空间删除。 select concat('alter table ',table_schema,'.'table_name,' discard tablespace;') from information_schema.tables where table_schema='confluence' into outfile '/tmp/discad.sql'; source /tmp/discard.sql 执行过程中发现，有20-30个表无法成功。主外键关系 很绝望，一个表一个表分析表结构，很痛苦。 set foreign_key_checks=0 跳过外键检查。 把有问题的表表空间也删掉了。 3、拷贝生产中confulence库下的所有表的ibd文件拷贝到准备好的环境中 select concat('alter table ',table_schema,'.'table_name,' import tablespace;') from information_schema.tables where table_schema='confluence' into outfile '/tmp/discad.sql'; 4、验证数据 表都可以访问了，数据挽回到了出现问题时刻的状态（2-8）
还有个学生：
全备到上周六数据，rm -fr ibdata1
=======================================================
InnoDB 核心特性详解
1.1 事务
1.1.1 什么是事务？
事务是伴随着交易类的业务场景出现的工作机制。 保证交易的“和谐”。 交易？ 现实： 物换物 ：豆子换豆腐 货币换物 ：实物货币换货物，虚拟货币换货物。 法律或道德约束，和谐的交易是什么类型？ 计算机中： 例如： A 发红包 B 开启1个事务。 A账户（100元） ： （1）update A-100元 B账户（0元） ： （2）update B+100元 事务结束。
1.1.2 事务ACID标准特性介绍
A (atomicity) ：原子性
原子是物理的最小构成单元，具备不可再分的特性。
在一个事务工作单元中，所有标准事务语句（DML），要么全成功，要么全回滚。
C (consistency) ：一致性
事务发生前，中，后都应该保证数据是始终一致状态。
MySQL的各项功能的设计，都是最终要保证一致性。
I (isolation) ：隔离性
MySQL 可以支持多事务并发工作的系统。
某个事务工作的时候，不能受到其他事务的影响。
D (durability) ：持久性
当事务提交（commit命令执行成功后），此次事务操作的所有数据“落盘”，都要永久保存下去。
不会因为数据实例发生故障，导致数据失效。
1.1.3 事务生命周期管理
（1）标准事务控制语句
begin / start transaction; 开启事务
commit; 提交事务
rollback; 回滚事务
（2）标准的事务语句：
insert update delete select
例子：
begin;
DML1
DML2
DML3
rollback;
begin;
DML1
sp1
DML2
sp2
DML3
sp3
rollback;
（3）自动提交功能：
mysql> select @@autocommit; +--------------+ | @@autocommit | +--------------+ | 1 | +--------------+ 1 row in set (0.01 sec) 在autocommit=1的时候，在执行DML时，没有加begin（显示的开启事务）。 在你执行DML语句时，会自动在这个DML之前加一个begin， begin; ---> 自动 delect ...... commit；---> 自动 begin; ---> 自动 update commit; ---> 自动
应用场景： autocommit=1 ,一般适合于非交易类业务场景。 如果是交易类的业务： 方案1： autocommit=0; commit,手工提交才生效。 方案2； autocommit=0; 每次想要发生事务型操作。 begin 和 commit 都手工操作。
设置方法： （1）临时生效； mysql> set global autocommit=0; 重新开启会话生效。
（2）永久生效 vim /etc/my.cnf autocommit=0 重启数据库生效
（4）隐式事务控制
隐式提交： 1. 设置了autocommit=1 2. DDL,DCL等非DML语句是，会触发隐式提交 session 1: begin; DML1 DML2 commit; drop database world;
导致提交的非事务语句：
DDL语句： （ALTER、CREATE 和 DROP）
DCL语句： （GRANT、REVOKE 和 SET PASSWORD）
锁定语句：（LOCK TABLES 和 UNLOCK TABLES）
导致隐式提交的语句示例：
TRUNCATE TABLE
LOAD DATA INFILE
SELECT FOR UPDATE
mysql> use world Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> begin; Query OK, 0 rows affected (0.01 sec) mysql> delete from city where id=1; Query OK, 1 row affected (0.01 sec) mysql> create table t1(id int); Query OK, 0 rows affected (0.04 sec) mysql> show tables; +-----------------+ | Tables_in_world | +-----------------+ | city | | country | | countrylanguage | | t1 | +-----------------+ 4 rows in set (0.00 sec) mysql> select * from city; +----+----------------+-------------+---------------+------------+ | ID | Name | CountryCode | District | Population | +----+----------------+-------------+---------------+------------+ | 2 | Qandahar | AFG | Qandahar | 237500 | | 3 | Herat | AFG | Herat | 186800 | | 4 | Mazar-e-Sharif | AFG | Balkh | 127800 | | 5 | Amsterdam | NLD | Noord-Holland | 731200 | | 6 | Rotterdam | NLD | Zuid-Holland | 593321 | | 7 | Haag | NLD | Zuid-Holland | 440900 | | 8 | Utrecht | NLD | Utrecht | 234323 | | 9 | Eindhoven | NLD | Noord-Brabant | 201843 | | 10 | Tilburg | NLD | Noord-Brabant | 193238 | +----+----------------+-------------+---------------+------------+ 9 rows in set (0.00 sec) mysql> rollback; Query OK, 0 rows affected (0.00 sec) mysql> select * from city; +----+----------------+-------------+---------------+------------+ | ID | Name | CountryCode | District | Population | +----+----------------+-------------+---------------+------------+ | 2 | Qandahar | AFG | Qandahar | 237500 | | 3 | Herat | AFG | Herat | 186800 | | 4 | Mazar-e-Sharif | AFG | Balkh | 127800 | | 5 | Amsterdam | NLD | Noord-Holland | 731200 | | 6 | Rotterdam | NLD | Zuid-Holland | 593321 | | 7 | Haag | NLD | Zuid-Holland | 440900 | | 8 | Utrecht | NLD | Utrecht | 234323 | | 9 | Eindhoven | NLD | Noord-Brabant | 201843 | | 10 | Tilburg | NLD | Noord-Brabant | 193238 | +----+----------------+-------------+---------------+------------+ 9 rows in set (0.00 sec)
隐式回滚：
会话关闭
数据库宕机
事务语句执行失败
1.2 InnoDB 事务的ACID如何保证？
1.2.1 名词介绍
（1）重做日志 redo log：重做日志 ib_logfile0~N 48M , 轮询使用 记录的是数据页的变化。 redo log buffer：redo内存区域 （2）磁盘数据页存储位置 ibd ：存储 数据行和索引 buffer pool ：缓冲区池,数据和索引的缓冲 （3）LSN : 日志序列号 磁盘数据页,redo文件,buffer pool,redo buffer MySQL 每次数据库启动,都会比较磁盘数据页和redo log的LSN,必须要求两者LSN一致数据库才能正常启动 （4）WAL : write ahead log 日志优先数据页，写的方式实现持久化 （5）脏页: 内存脏页,内存中发生了修改,没回写入到磁盘之前,我们把内存页称之为脏页. （6）CKPT：Checkpoint,检查点,就是将脏页刷写到磁盘的动作 （7）TXID: 事务号,InnoDB会为每一个事务生成一个事务号,伴随着整个事务生命周期. （8）UNDO：ibdata1, 存储了事务工作过程中的回滚信息。
1.2.2 InnoDB 事务的工作流程
redo log 1) Redo是什么？ redo,顾名思义“重做日志”，是事务日志的一种。 2) 作用是什么？ 在事务ACID过程中，实现的是“D”持久化的作用。对于AC也有相应的作用 3) redo日志位置 redo的日志文件：iblogfile0 iblogfile1 4) redo buffer redo的buffer:数据页的变化信息+数据页当时的LSN号 LSN：日志序列号 磁盘数据页、内存数据页、redo buffer、redolog 5) redo的刷新策略 commit; 刷新当前事务的redo buffer到磁盘 还会顺便将一部分redo buffer中没有提交的事务日志也刷新到磁盘 补充：redo存储的是在事务工作过程中，数据页变化。 commit时会立即写入磁盘（默认），日志落盘成功commit。 正常MySQL工作过程中，主要的工作是提供快速D（持久化）的功能。 MySQL出现crash异常宕机时，主要提供的是前滚功能（CSR）。
mysql> select @@innodb_flush_log_at_trx_commit; +----------------------------------+ | @@innodb_flush_log_at_trx_commit | +----------------------------------+ | 1 | +----------------------------------+ 1 row in set (0.00 sec)
双一标准：
innodb_flush_log_at_trx_commit = 0 / 1 / 2 1：在每次事务体检时，会立即刷新redo到磁盘，commit才才能成功。 0：每次刷新日志到OS cache，再fsync到磁盘，异常宕机时，会有可能导致丢失ls内的事务 2：每次事务提交，都立即刷新redo buffer 到 os cache，再每秒fsync()磁盘，异常宕机时，会有可能导致丢失ls内的事务。 目前默认是1. 另外， 1. redo buffer 还和操作系统缓存机制有关，所以刷写策略可能和 innodb_flush_method参数有一定关系。 2. redo也有group commit；可以理解为，在每次刷新已提交的redo时，顺便可以将一些提交的事务redo也一次性刷写到磁盘。此时为了区分不同状态的redo，会加一些比较特殊的标记（是否提交标记）。
1.2.3 undo 回滚日志
1》undo是什么？ undo,顾名思义“回滚日志” 2》作用是什么？ 在事务ACID过程中，实现的是“A” 原子性的作用 另外CI也依赖于Undo 在rolback时,将数据恢复到修改之前的状态 在CSR实现的是,先redo前滚 ，在undo回滚。 3》什么是一致性快照？ 每个事务开启时（begin），都会通过undo生成一个一致性的快照。 undo提供快照技术,保存事务修改之前的数据状态. 保证了MVCC,隔离性,mysqldump的热备功能。 undo在生成过程中，也会记录redo信息。
11.3 概念性的东西:
redo怎么应用的
undo怎么应用的
CSR(自动故障恢复)过程
LSN :日志序列号
TXID:事务ID
CKPT(Checkpoint)
11.4 锁
“锁”顾名思义就是锁定的意思。 “锁”的作用是什么？ 在事务ACID过程中，“锁”和“隔离级别”一起来实现“I”隔离性和"C" 一致性 (redo也有参与). 悲观锁:行级锁定(行锁) 谁先操作某个数据行,就会持有<这行>的(X)锁. 乐观锁: 没有锁
11.5 隔离级别
影响到数据的读取,默认的级别是 RR模式. transaction_isolation 隔离级别(参数) 负责的是,MVCC,读一致性问题 RU : 读未提交,可脏读,一般部议叙出现 RC : 读已提交,可能出现幻读,可以防止脏读. RR : 可重复读,功能是防止"幻读"现象 ,利用的是undo的快照技术+GAP(间隙锁)+NextLock(下键锁) SR : 可串行化,可以防止死锁,但是并发事务性能较差 补充: 在RC级别下,可以减轻GAP+NextLock锁的问题,但是会出现幻读现象,一般在为了读一致性会在正常select后添加for update语句.但是,请记住执行完一定要commit 否则容易出现所等待比较严重. 例如: [world]>select * from city where id=999 for update; [world]>commit;
1.2.4 隔离级别和锁机制
作用：主要是提供I的特性，另外对于C的特性也有保证。
（1）transaction_isolation 事务隔离性介绍
RU：读未提交
RC：读已提交
RR：可重复读 ：默认级别
SR：可串行化
读什么意思？
这里的读不是SQL层的数据行的select，而指的是存储引擎的读，是page的读取。
（2）隔离级别说明
《1》RU（READ UNCOMMITTED） ：读未提交 出现的问题：脏页读，不可重复读，幻读 《2》RC（READ COMMITTED） ：读已提交 出现的问题 ：不可重复读，幻读 《3》RR（REPEATABLE READ） ：可重复读 出现的问题 ：有可能出现幻读，但是可以通过其他手段防止幻读出现。 《4》SR（SERIALIZABLE） ：可串行化 串行化事务。以上问题都能规避，但是不利于事务的并发。
（3）参数修改
mysql> select @@transaction_isolation; 临时调整： mysql> set global transaction_isolation='read-uncommitted'; 重启会话生效。 永久修改： vim /etc/my.cnf transaction_isolation='read-uncommitted' 重启生效
（4）例子演示：
调整成transaction_isolation='read-uncommitted' mysql> set global transaction_isolation='read-uncommitted'';
1）脏读：
保证两窗口统一： mysql> mylect @@transaction_isolation; mysql> use world mysql> select * from city; session1： 步骤1： mysql> begin; mysql> update city set name='oldguo' where id=2; session2： 步骤2： mysql> begin; mysql> select * from city; 结论：此时在session2事务中读取到了，session未提交数据的脏数据。这个现象就是脏读 对于脏读，在生产业务中是一般不允许出现的。
2）不可重复读现象
session1： 步骤1： mysql> begin; mysql> update city set name='oldguo' where id=2; mysql> rollback; mysql> update city set name='oldguo' where id=10; mysql> commit; session2： 步骤2： mysql> begin; mysql> select * from city; mysql> select * from city; mysql> select * from city; 结论：session2中的事务中，执行相同查询命令时，读到了session1正在发生变化的数据。 对于事务的隔离性和数据最终一致性要求比较高的业务，不允许出现的。 如果业务能够容忍，也是可以出现的。
3）幻读
保证两窗口统一： mysql> mylect @@transaction_isolation; mysql> use world mysql> create table test (id int,num int,name varchar(200)); mysql> insert into test values(1,10,'a'),(2,12,'b'),(3,104,'d'),(2,142,'f'),(7,104,'d'),(5,142,'f'),(8,164,'d'),(5,642,'f'),(6,164,'d'),(9,642,'f'); mysql> commit; mysql> select * from test; session1： 步骤1： mysql> begin; mysql> update test set num=100 where num>100; mysql> commit; session2： 步骤2： mysql> begin; mysql> insert into test values(40,333,'ddddd'); mysql> commit; mysql> select * from test; 在一个事务窗口中，更新操作，出现了别的插入数据的幻行
========================================================
调整成transaction_isolation='read-committed' mysql> set global transaction_isolation='read-committed'; 开两个窗口session1、session2： mysql> select @@transaction_isolation; mysql> use world mysql> select * from city;
1）不可重复读现象
session1： 步骤1： mysql> begin; mysql> update city set name='oldboy' where name='oldguo'; mysql> commit; 步骤3： mysql> update city set name='oldboy'; mysql> commit; session2： 步骤2： mysql> select * from city; 步骤4： mysql> select * from city;
2）幻读
mysql> select * from test; session1： 步骤1： mysql> begin; mysql> update test set num=30 where num<30; 步骤3： mysql> commit; 步骤4： mysql> select * from test; session2： 步骤2： mysql> begin; mysql> insert into test values(10,18,'assdf'); mysql> commit; 步骤4： mysql> select * from city;
=================================================================
调整成transaction_isolation='repeatable-read' mysql> set global transaction_isolation='repeatable-read';
1）防止不可重复读现象：
利用的就是undo的一致性快照读。MVCC重要功能。 mysql> use world mysql> begin; mysql> select * from city; session1： 步骤1： mysql> begin; mysql> update city set name='oldguo'; 步骤3： mysql> commit; 步骤5： mysql> begin; mysql> update city set name='oldgirl'; mysql> commit; session2： 步骤2： mysql> select * from city; 步骤4： mysql> select * from city; 步骤6： mysql> select * from city;
2）通过RR,已经可以解决99%以上的幻读的，为了更加严谨。加入了GAP锁，next-lock。加入了GAP锁，next-lock。
mysql> select * from test; mysql> truncate table test; mysql> insert into test values(1,10,'a'),(2,12,'b'),(3,13,'d'),(11,17,'f'),(22,23,'d'),(33,26,'f'),(111,34,'d'),(222,37,'f'),(333,47,'d'); mysql> commit; mysql> select * from test; mysql> alter table test add index idx(num); session1： 步骤1： mysql> begin; mysql> select * from test; 步骤3： mysql> update test set num=25 where num>25; 步骤5： mysql> commit; session2： 步骤2： mysql> begin; mysql> select * from test; 步骤4： mysql> insert into test values(2222,28,'x'); ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction mysql> insert into test values(2222,28,'x'); ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction 步骤6： mysql> insert into test values(2222,28,'x'); mysql> select * from test;
浙公网安备 33010602011771号