MYSQL体系结构

MYSQL的特点

1.安装简单，部署迅速，适合大批量快速部署
2.易于扩展，扩展性能极佳
3.架构灵活，可以根据业务特点配置适合自己的mysql集群
4.开源，可以根据自己的业务需求进行二次开发
5.使用广泛，几乎所有的互联网公司都在使用mysql数据库
6.对于OLTP业务，可以进行良好的支撑

MYSQL官方版本

mysql8.0下载地址：
https://dev.mysql.com/downloads/mysql/8.0.html#downloads

MYSQL服务器结构

mysql由以下几个部分组成：
1.连接层（连接池+连接器）
2.管理服务和工具组件
3.sql借口组件（sql interface）
4.查询分析器（Parser）
5.优化器组件（Optimizer）
6.缓冲组件（Cache & Buffer）
7.插件式存储引擎（Pluggable Storage Engines）
8.数据库物理文件（File Sysgtem & Logs）
MYSQL从概念上分为四层，这四层自顶向下分别时网络连接层，服务层（核心层），存储引擎层，系统文件层，我们开始逐一分析。

网络连接层
主要负责连接管理，授权认证，安全等等。每个客户端连接都对应着服务器上的一个线程。服务器上维护了一个线程池，避免为每个连接都创建销毁一个线程。当客户端连接到mysql服务器时，服务器对其进行认证。可以通过用户名与密码认证，也可以通过SSL证书进行认证。登陆认证后，服务器还会验证客户端是否有执行某个查询的操作权限。这一层并不是mysql所特有的技术。
mysql主要提供两种连接方式：TCP/IP,SOCKET建立一个与SQL层连接的线层。

服务层（MYSQL SERVER）
第二层服务层时mysql的核心，mysql的核心服务层都在这一层，查询解析，sql执行计划分析，sql执行计划优化，查询缓存。以及跨存储引擎的功能都在这一层实现：存储过层，触发器，视图等。
连接池：管理，缓冲用户的连接，线层处理等需要缓存的需求
管理服务和工具组件：系统管理和控制工具，例如备份恢复，mysql复制，集群等。
SQL接口：接受用户的sql命令，并返回用户需要查询的结果
查询解析器：SQL命令传递到解析器的时候会被解析器验证和解析（权限，语法结构）
查询优化器：SQL语句在查询之前会使用查询优化器对查询进行优化
缓存：如果查询缓存有命中的查询结果，查询语句就可以直接去查询缓存中取数据
而我们延伸一下SQL接口，查询解析器，查询优化器，缓存又组成了独立的一层叫SQL层。
那么这一层的主要工作职责再细化一些就是：
1.接受连接层传来的SQL语句
2.检测语法
3.检测语义（DML DDL DCL和基本MYSQL命令）
4.解析器：解析整条SQL语句生成多种执行计划
5.优化器：选择解析器生成的多种执行计划中最高效的一种
6.执行器：将优化器的最优一种执行方式执行
1）和存储引擎层建立连接–提供一个线程
2）接收存储引擎层传来的结构化成表的数据
7.将数据写入缓存
8.记录相关日志-binlog

存储引擎层
负责mysql中数据的存储与提取。服务器中的查询执行引擎通过API与存储引擎进行通信，通过接口屏蔽了不同存储引擎之间的差异。mysql采用插件式的存储引擎。mysql为我们提供了许多存储引擎，每种存储引擎又不同的特点。我们可以根据不同的业务特点，选择最适合的存储引擎。如果对于存储引擎的性能不满意，可以通过修改源码得到自己想要达到的性能。
各个存储引擎的描述参考下文。
特点：
1.mysql采用插件式的存储引擎。
2.存储引擎是针对于表的而不是针对库的（一个库中不同表可以使用不同的存储引擎），服务器通过API与存储引擎进行通信，用来屏蔽不同存储引擎之间的差异。
3.不管表采用什么样的存储引擎，都会在数据区，产生对应的文件（表结构睇你描述文件）

系统文件层
系统文件层，主要是数据库系统在操作系统的文件系统上生成的各种文件，这些文件里主要包含了容纳表数据，日志（redo，undo，binlog），索引数据，错误日志，查询记录，慢查询信息，pid信息，socket信息等的文件。

该层将数据库的数据存储在文件系统之上，并完成与存储引擎的交互。

存储引擎

show engines;
结果：
Engine			Support			Comment			Transactions	XA		Savepoints
InnoDB			DEFAULT								YES			YES			YES
MRG_MYISAM		YES									NO			NO			NO
MEMORY			YES									NO			NO			NO
BLACKHOLE		YES									NO			NO			NO
MyISAM			YES									NO			NO			NO
CSV				YES									NO			NO			NO
ARCHIVE			YES									NO			NO			NO
....					....						....				.....

InnoDB存储引擎
mysql 5.5及以后版本的默认存储引擎。设计目标主要面向OLTP应用
特点：
1.支持事务（ACID），支持4个事务隔离级别，支持多版本读。
2.行级锁定（更新时一般锁定当前行）
3.读写阻塞与事务隔离级别相关
4.具有非常高效的缓存特性：能缓存索引，也能缓存数据。
5.整个表和主键以Cluster（聚集）方式存储，组成一个平衡树。
6.所有Secondary Index 都会保存主键信息
7.支持分区，表空间，类似oracle数据库
8.支持外键约束，5.6之后支持全文索引。

InnoDB存储引擎

在技术上，InnoDB是一套放在MySQL后台的完整数据库系统，InnoDB在主内存中建立其专用的缓冲池，用于高缓冲数据和索引。InnoDB把数据和索引存放在表空间里，可能包含多个文件，这与其他的不一样，举例来说，在MyISAM中，表被存放在单独的文件中。InnoDB表的大小只受限于操作系统的文件大小，每个表可以使用独立的表空间，只需要启用选项 innodb_file_table.
由于innodb使用磁盘作为持久化介质，那么在数据处理的过程中，就有可能无法将所有的数据，都从磁盘读入到内存缓冲区，它只是把最近常用的数据缓存到内存中，然后在特定的时间对内存和硬盘数据进行同步，来保证双向的数据一致性。所以在这种情况下，磁盘和内存的交互就成了数据库主要的性能瓶颈。
前面已经说过，InnoDB存储引擎是基于磁盘存储的，也就是说数据都是存储在磁盘上的，由于内存读写速度和磁盘读写速度之间的巨大差异，所以InnoDB引擎使用缓冲池技术来提高数据库的整体性能。
INNODB缓冲池
缓冲池简单来说就是一块内存区域，在数据库中进行读取页的操作，首先将从磁盘读到的页存放到缓冲池中，下一次读取相同的页时，首先判断该页是不是在缓冲池中，若在，称该页在缓冲池中被命中，直接读取该页。否则，读取磁盘上的页。对于数据库中页的修改操作，首先修改在缓冲池中页然后再以一定的频率刷新到磁盘，并不是每次发生改变就刷新回磁盘。这是通过“CHECKPOINT”机制实现的。

缓冲池中缓冲的数据页类型有：索引页，数据页，undo页，插入缓冲，自适应哈希索引，InnoDB的锁信息，数据字典信息等。索引页和数据页占缓冲池的很大一部分，在InnoDB中，缓冲池中的页大小默认为16KB。而缓冲池中的页对应磁盘文件中的物理页。

MyISAM存储引擎
mysql 5.5版本之前的默认存储引擎，较多的系统表也还是使用这个存储引擎，系统临时表也会用到MyISAM存储引擎。
特点：
1.做count（*）查询的时候比InnoDB快。
2.数据（MYD）和索引（MYI）分开存储，只对索引进行缓存，虽然key_buffer可以大幅提高性能，减少磁盘IO，但对数据不缓存
3.表级锁
4.不支持事务
5.适合读业务比较多的生产环境，比如BLOG等，读的速度比较快
6.占用资源比较少，服务器硬件不好时，可以考虑使用。
7.数据恢复没有InnoDB引擎恢复的完美
8.支持全文索引，不支持外键约束。

MyISAM和InnoDB对比
1.InnoDB支持事务，MyISAM不支持，这一点非常重要。事务是一种高级的处理方式，如在一些列增删改中只要哪个出错还可以回滚还原，而MyISAM就不可以了。
2.MyISAM适合查询以及插入为主的应用，InnoDB适合频繁修改以及涉及到安全性较高的应用。
3.InnoDB支持外键，MyISAM不支持
4.MyISAM需要指定，InnoDB默认引擎
5.InnoDB中不保存表的行数，如 select count() from table 时，InnoDB需要扫描一边整个表来计算有多少行，但是MyISAM只要简单的独处保存好的行数即可。注意的是，当count()语句包含where条件时，MyISAM也需要扫描整个表。
6.对于自增长的字段，InnoDB中必须包含只有该字段的索引，但是MyISAM表中可以和其他字段一起建立联合索引。
7.清空整个表时，InnoDB是一行一行的删除，效率非常慢。MyISAM则会重建表。
8.InnoDB支持行锁而MyISAM不支持。

Memory存储引擎
数据都是存储在内存中，IO效率要比其他引擎高很多，服务器重启数据会丢失，内存数据表默认只有16M，一般我们不会使用到Memory存储引擎。
特点：
1.支持hash索引，Btree索引，默认hash（查找复杂度o（1））
2.字段长度都是固定长度 varchar（32）= char（32）
3.不支持大数据存储类型字段如blog，text
4.表级锁
应用场景：
等值查找热度较高数据
在排序，分组等操作中，当数据量小于16M（默认大小），由查询优化器建立的临时表就是Memory类型。
Archive存储引擎
压缩协议进行数据的存储，数据存储为ARZ文件格式。
特点：
1.只支持insert和select两种操作
2.只允许自增ID列建立索引
3.行级锁
4.不支持事务
5.数据占用磁盘少
应用场景：
1.日志系统
2.大量的设备数据采集
CSV存储引擎
数据存储以CSV文件。
特点：
1.不能定义索引，列定义必须为NOT NULL,不能设置自增列，不适用大表或者数据的在线处理
2.CSV数据的存储用逗号隔开，可直接编辑CSV文件进行数据的编排，数据安全性低。编辑之后，要生效使用 flush table xxx 命令
应用场景：
1.数据的快速导入
2.表格直接转换成CSV

文件结构大

物理文件主要包括：日志文件，数据文件，配置文件，PID文件 SOCKET文件

MYSQL5.7/8.0支持的几种日志文件

日志类型	日志信息
Error log(错误日志)	启动，运行或者停止mysql时遇到的问题
General query log(一般或者通用查询日志)	从已建立的客户端连接收到的语句
Binary log（二进制日志）	用来记录操作mysql数据库中的写入性操作（增删改，但不包括查询）
Relay log（中继日志）	从复制源服务器收到的数据更改
Slow query log（慢查询日志）	执行时间超过long_query_time秒的查询
DDL log（metadata log）（DDL语句日志）	DDL语句日志（元数据操作）

错误日志
mysql错误日志记录mysql运行过程中较为严重的警告和错误信息，以及mysql每次启动和关闭的详细信息。
mysql错误日志默认时开启的。可以通过mysql配置文件中的log-error=/var/log/mysqld.log配置，修改错误日志的配置信息。
可以通过如下sql查看错误日志的详细信息：

show variables like '%log_err%';
结果：
-------------------------------------------------
variable_name			|		value
------------------------------------------------
binlog_error_action		|	ABORT_SERVER
log_error				|	/data/mysql_log/mysql-error-3306.log
log_error_verbosity		|	3
------------------------------------------------

一般或通用查询日志
记录已连接MYSQL数据库的客户端所执行的语句。
可以通过如下sql查看当前通用日志是否开启：

SHOW VARIABLES LIKE '%general%';
结果：
--------------------------------------------------------
variable_name 		|	value
-------------------------------------------------------
general_log			| OFF
general_log_file	| /data/mysql_data/mysql57.log
-------------------------------------------------------

--开启通用查询日志
set global general_log = on;
--关闭通用查询日志：
set global general_log = off;

当日志时间和系统时间不一致时的处理方式
在mysql 5.7.2新增了 log_timestamps这个参数，该参数主要时控制error log，general query log等记录日志的显示时间参数，并且数据库安装后这些日志时间戳默认为UTC，因此造成与系统时间不一致，与北京时间相差8个小时。

--查看当前日志时间戳
SHOW GLOBAL VARIABLES LIKE  'log_timestamps';
结果：
------------------------------
variable_name		|	value
-----------------------------
log_timestamps		|	UTC
-------------------------------

因为log_timestamps 是一个GLOBAL的全局参数，所以直接在登陆后去set全局参数，重启后就会直接失效，因此需要在mysql的配置文件中[mysqld]中增减一条：

vi /etc/my.cnf
在这个文件中添加
log_timestamps = SYSTEM
保存后重启
service mysqld restart;

并且重启mysql后生效。

二进制日志
mysql的二进制日志是一个二进制文件，主要用于记录修改数据或者有可能引起数据变更的sql语句。二进制日志中记录了对mysql数据库更改的所有操作，并且记录了语句发生时间，执行时长，操作数据等其他额外信息，但是它不记录 select，show 等那些不修改数据的sql语句，二进制日志主要用于数据库恢复和主从复制，以及审计操作。

慢查询日志
记录所有执行时间超过long_query_time秒的查询sql或者没有使用索引的查询sql，默认情况下，mysql不开启慢查询日志，
long_query_time 值------>查询它的语句：show variables like ‘long_query_time’;
long_query_time值------->修改它的语句：set long_query_time = 秒数；

--查看当前慢查询日志的开启情况
show variables like '%query%';

slow_query_log: ON 表示开启慢查询日志，OFF 表示关闭慢查询日志
slow_query_log_file:记录慢查询日志的文件地址（默认为主机名.log）
long_query_time:指定了慢查询的阈值，单位是秒，即执行语句的时间若超过这个值则为慢查询语句。
log_queries_not_using_indexes:ON 表示会记录所有利用索引来进行查询的语句，前提是slow_query_log的值也是ON，否则，不会凑效，OFF表示不会记录所有没有lying索引来进行查询的语句。

配置文件
inux下mysql的配置文件是my.cnf，一般会放在/etc/my.cnf,/etc/mysql/my.cnf,MYSQL所有的配置信息都存放在改文件中。每次mysql服务（mysqld）在启动的时候都会去加载改文件里的参数。

数据文件
获取硬盘中数据存储的位置

show variables like 'datadir';
结果：
--------------------------------------
variable_name  	|	value
--------------------------------------
datadir			|	/data/mysql_data
--------------------------------------

db.opt文件：改文件记录这个库的默认使用的字符集和校验规则，文件存放在所属数据库的目录下。
FRM文件：不论使用什么存储引擎，每一张表都会有一个以表名命名的.frm文件，与表相关的元数据信息都会存放在此文件中，包括表结构的定义信息等，文件存放在所属数据库的目录下。
MYD文件：MyISAM存储引擎专用，存放MyISAM表的数据。每一张MyISAM表都会有一个.MYD文件，文件存放在所属数据库的目录下。
MYI文件：也是MyISAM存储引擎专用，存放MyISAM表的索引相关信息。每一张MyISAM表对应一个.MYI文件，文件存放在所属数据库的目录下。
IBD文件和IBDATA文件：存放InnoDB文件的数据文件（包括索引）。InnoDB存储引擎有两种表空间方式：独享表空间和共享表空间。
独享表空间：使用.ibd文件存放数据，且每一张InnoDB表对应一个.ibd文件，文件存放在所属数据库的目录下。
共享表空间：使用.ibdata文件，所有表共同使用一个（或多个，自行配置）.ibdata文件。
ibdata1 ibdata n文件：系统表空间（数据文件）undo段，文件存放在datadir目录下。
ib_logfile0,ib_logfile1文件：redlog文件，文件存放在datadir目录下。
pid文件：是mysqld应用程序在unix/linux环境下的一个进程文件，和许多其他unix/linux服务端程序一样，它存放着自己的进程id。
socket文件：是unix/linux环境下才有的，用户在unix/linux环境下客户端连接可以不通过TCP/IP网络而直接使用unix socket 来连接mysql。

MYSQL存储管理

mysql物理存储结构
innodb存储引擎的物理存储结构：
innodb存储引擎的物理存储结构相当于我们已经建立的 /data/mysql_data下的文件
以ib开头的文件就是innodb的核心文件。
ib_buffer_pool 缓存池文件 存储上次关机前查询的热数据 下次开机时首先把这些文件调到内存中去。
ibdata1 ibdata2 …存储数据字典信息 基表的元数据信息 统计信息 双写缓冲区和undo（回滚日志）信息等
ib_logfile0 ib_logfile1 存储REDO日志信息
ibtmp1 临时表空间 主要存储临时表
.frm 表的列信息
.ibd 表的数据行和索引
还有之前讲过的mv.cnf mysql.sock mysql.pid 等，还包括了各种MYSQL日志

表空间
InnDB存储引擎的逻辑存储结构和oracle大致相同，所有数据都被逻辑地存放在一个空间中，我们称之为表空间。表空间又由段，区，页组成。页在一些文档中有时也称为块。
表空间可以看作是innodb存储引擎逻辑结构的最高层，所有的数据都是存放在表空间中。默认情况下，innodb存储引擎有一个系统表空间，即innodb 元数据信息，双写缓存冲，改变缓冲区，undo logs都放在这个表空间内。如果我们启用了参数innodb_file_per_table,则每张表内的数据内的数据可以单独放到一个表空间。

show variables like 'innodb_file_per_table';

innodb_file_per_table 是否开启单表表空间，该参数为全局参数，可动态修改，默认为on，如果设置为off。在没有显示指定表空间情况下，数据以及索引默认存放到系统表空间ibdata下。
innodb_data_file_path:表空间大小以及文件个数由参数决定，该参数为全局参数，不可动态修改，默认值为：
ibdata1:12M：autoextend,默认的12M大小，是根据innodb_page_size按最大值指定的，为了确保其空间可以容纳 doublewrite buffer pages,则：
innodb_page_size 为16K时（mysql默认值），最小值3M
innodb_page_size为32K时，最小值6M
innodb_page_size 为64K时， 最小值为12M
而我们的设置为多个文件模式，则在my.cnf文件中指定：
innodb_data_file_path=ibdata1:50M;ibdata2:50M:autoextend[:max:maxfilesize]

innodb_data_file_path = ibdata1:128M;ibdata2:10M:autoextend

注意：autoextend及max两个参数只能指定在最后一个ibdata上，autoextend默认每次自动扩展64M，人为指定其扩展大小参数为innodb_autoextend_increment=32
比如你想指定你的文件最大就是32G，你可以写成：
[mysqld]下添加
innodb_data_file_path=ibdata1:50M;ibdata2:128M:autoextend:max:32G
innodb_data_home_dir : 表空间存放位置控制参数，该参数为全局参数，不可动态修改，默认值为空，即新建的表空间建立在属于自己的数据库初始化目录下。
也可以显示的指定innodb_tablespace文件的路径，如果指定innodb_data_home_dir,那么就不能为innodb tablespace不同文件指定不同路径
例如：
[mysqld]
innodb_data_home_dir=/data/mysql_data/mysql3306/
从5.7.6开始，增加了一种新的tablespace模式（称为 general tablespace），实际上它和共享表空间比较类似：创建一个单独的ibd，ibd中包含多个表，兼容不同的格式。general tablespace 没有库的概念，因此可以在多个库里建属于同一个tablespace的表。
存放路径：手工创建时指定的绝对路径，如果只指定了文件名则创建在数据库默认路径下
存放内容：用户创建表的数据及索引
控制参数：无直接控制参数
创建通用表空间：
CREATE TABLESPACE tablespace_name ADD DATAFILE ‘file_name’ [FILE_BLOCK_SIZE = value] [ENGINE][=]engine_name
例如：

CREATE TABLESPACE 'test' ADD DATAFILE 'test.ibd' Engine=InnoDB;
CREATE TABLESPACE 'test' ADD DATAFILE '/outside tablespace directory/test.ibd' Engine = InnoDB;

说明：如果不指定文件的绝对路径时，表空间默认建立数据库目录下，如果指定绝对路径需要提前创建该路径并保证相应的读写权限，外部建立表空间时同样会在数据库所在的目录下生成.isI的软连接文件。表空间文件需要以ibd结尾。可选参数[FILE_BLOCK_SIZE=value]，在启用压缩表时必须指定，[Engine [=] engine_name] 在default_storage_engine全局参数未指定时（default_storage_engine = InnoDB时默认值）需要显示定义。
通用表空间相比单表表空间占用内存少。支持Antelope and Barracuda文件格式以至于可以支持所有行格式。
通过alter table命令可以将表在系统表空间，单表表空间及通用表空间中任意移动。
如何在通用表空间中创建表？
新建表：CREATE TABLE test(c1 INT PRIMARY KEY) TABLESPACE test [ROW_FORMAT=COMPACT]
修改表：ALTER TABLE test TABLESPACE TEST;
ROW_FORMAT是可选项，如果不指定默认为dynamic。如果指定为compressed，创建表空间时需要指定FILE_BLOCK_SIZE项，建立表也要加入参数KEY_BLOCK_SIZE项，并且KEY_BLOCK_SIZE和FILE_BLOCK_SIZE为等值。以innodb_page_size为16为例，KEY_BLOCK_SIZE取值可以为（8，4，2，1），对应FILE_BLOCK_SIZE取值也应为（8，4，2，1）。
如何将表在3中表空间中移动？
ALTER TABLE tbl_name TABLESPACE = tablespace_name （通用表空间名字）
ALTER TABLE tbl_name TABLESPACE = innodb_system(系统表空间关键字)
ALTER TABLE tbl_name TABLESPACE = innodb_file_per_table (单表表空间关键字)
如何确认表与表空间的归属关系？
方法一：show create table test;
输出如果明确列出表空间信息为通用表空间，如果没有列出表空间信息则为单表表空间。
方法2: select * from infornation_schema.INNODB_SYS_TABLES；
输出项SPACE显示了表空间的ID，SPACE_TYPE显示了表空间的类型。或者用
select * from information_schema.INNODB_SYS_TABLESPACE;根据输出的SPACE ID进行关联，找到具体的NAME。
临时表空间用于存放用户创建的临时表和磁盘内部临时表。
参数innodb_temp_data_file_path定义了临时表空间的一些名称，大小，规格属性如下图：

show variables like 'innodb_temp_data_file_path';
---------------------------------------------------
variable_name				| value
--------------------------------------------------
innodb_temp_data_file_path	|	ibtmp1:12M:autoextend
--------------------------------------------------------

段-segement
表空间是由各个段组成的，常见的段友数据段，索引段，回滚段等。InnoDB存储引擎表是索引组织的，即索引组织表。因此数据即索引，索引即数据。那么数据段即B+树的叶子节点，索引段即为B+树的非叶子节点。
innodb存储引擎对于段的管理是引擎本身完成的。
在mysql中，并不是每个对象都有段，因此更准确的说，表空间是由分散的页和段组成的。

区-EXTENT
区是由64个连续的页组成的，每个页大小为16KB，即每个区的大小为1MB，对于大的数据段，InnoDB存储引擎最多每次可以申请4个区，以此来保证数据的顺序性能。

在我们启用了参数innodb_file_per_table后，创建的表默认是96KB。区是由64个连续的页，那创建的表的大小至少是1MB才对？其实这是因为在每个段开始时，先有32个页大小的碎叶片来存放数据，当这些页使用完之后才是64个连续页的申请。

页-PAGE
InnoDB有页的概念（也可以称为块），页是InnDB磁盘管理的最小单位，也可以理解为页是逻辑存储最小单元。Oracle的最小逻辑存储单元为块-block，默认大小为8K不同与InnoDB页的大小（16KB）。
页默认大小为16KB，一个页内必须存储2行记录，否则就不是B+tree,而是链表了。
常见的页类型：
1.数据页
2.UNDO页
3.系统页
4.事务数据页
5.插入缓冲页
6.插入缓冲空闲列表页
7.未压缩的二进制大对象页
8.压缩的二进制大对象页。

行-ROW

innodb存储引擎是面向行的，也就是说数据的存放按行存放。每个页存放的行记录也是硬性定义的，最多允许存放16KB/2-200行的记录，即7992行。
行结构
平时操作mysql时一行一行的数据为单位，表中的行也有它自己的行格式，每张表中所有的行共享一种格式，目前共有4种行格式，分别是：
Dynamic（动态行格式 5.7默认）
Compact(紧凑行格式)
Compressed（压缩行格式）
Redundant（冗余行格式 5.5废除）
/*创建时指定行格式/
CREATE TABLE 表名(列的信息) ROW_FORMAT = 行格式名称
/*修改行格式/
ALTER TABLE 表名 ROE_FORMAT = 行格式名称
/*查看表的行格式/
show table status like ‘表名’；

事务与锁

在数据库中，事务时工作的逻辑单元，一个事务是由一个或者多个完成一组相关行为的sql语句组成，通过事务机制确保这一组sql语句所做的操作要么完全成功执行，要么就彻底不执行，或者什么都不执行什么都不做。
说的再直白些：事务的任务是使数据从一种状态称为另一种状态。
产生事务的语句
DML语句：INSERT UPDATE DELETE
所有的DDL操作
DCL操作：GRANT REVOKE
…
在mysql中控制事务的语句称为TCL（事务控制语句），一共有3个动作：
COMMIT 提交
ROLLBACK 回滚
SEVEPOINT 保存点

事务的特性- ACID

原子性（Atomicity）A
事务的原子性是指事务中包含的所有操作要么完成，要么不做，也就是说所有的活动在数据库中要么全部反映，要么全部不反映，以保证数据库的一致性。
一致性（Consistency）C
事务的一致性是指数据库在事务操作前和事务处理后，数据库中的数据必须保持状态的一致，并且满足业务的规则约束。
隔离性（Isolation）I
隔离性是指数据库允许多个并发的事务同时对其中的数据进行读写或者修改的能力，隔离性可以防止多个事务的并发执行时，由于他们的操作命令交叉执行而导致数据的不一致性。简单点说就是两个事务之间是不能互相干扰的。
持久性（Durability） D
事务的持久性是指事务处理结束后，它对数据的修改应该是永久的。即便是系统在遇到故障的情况下也不会丢失，这是数据的重要性决定的。简单点说就是提交后不能回滚。

数据库并发下事务会产生的问题
1.脏读
就是指一个事务读取到了另一个事务尚未提交的数据。
2.不可重复读
就是指一个事务里面读取了两次同一数据，但是每次读取出来的结果不一致。
比如说一个事务中发生多次相同的查询语句，但在此期间，由于其他事务提交了对查询数据的修改或者删除，每次返回不同的结果。
3.幻读
就是指在一个事务中发生多次相同的查询语句，由于其他提交事务所做的插入操作，虽然查询条件相同，但是每次返回的结果集却不同。

事务的隔离级别
Read uncommitted
未提交读：就是一个事务可以读取另一个事务未提交的数据。（修改，删除，插入等）
Read committed
提交读：就是一个事务要等另一个事务提交后才能读取到已提交事务发生操作的数据。
Repeatable read
重复读：即在数据读出来之后加锁，类似“select* from xxx for update”，明确数据读出来就是为了更新用的，所以要加一把锁，防止别人修改它。
SERLALIZABLE
串行化，最高的事务隔离级别，不管多少事务，都是挨个运行，做完一个事务或其所有子事务之后才可以执行另一个事务或者其所有子事务，这样就解决了脏读，不可重复读和幻读的问题了。

mysql事务隔离级别的修改
set global |(session) transaction isolation level (READ UNCOMMITTED)|(READ COMMITTED)|(REPEATABLE READ)|(SERIALIZABLE)
永久修改：在my.cnf中添加
[mysqld]
transaction-isolation = READ-COMMITTED
查看隔离级别：
SELECT @@GLOBAL.transaction_isolation;
SELECT @@SESSION.traction_isolation;

事务注意事项
1.事务应该尽可能的短，即避免不必要的扩大事务。
2.根据具体业务需求与流程制定合适的事务大小
3.决定事务大小的关键是数据完整性
4.能决定事务大小的唯一约束就是控制系统的业务规则，不是redo，不是undo，不是锁等。

InnoDB中的事务-redo
在innodb存储引擎中，事务日志通过重做（redo）日志文件和innodb存储引擎的日志缓冲来实现，当开始一个事务时，会记录该事务的一个LSN；当事务执行时，会往innodb 存储引擎
的日志缓冲里插入事务日志；当事务提交时，必须将innodb存储引擎的日志缓冲写入磁盘，也就是写数据前，需要先写日志，称为预写日志方式（WAL）。

InnoDB存储引擎通过预写日志的方式，来保证事务的完整性，这意味着磁盘上存储的数据页和内存缓冲池中的页是不同步的，对于内存缓冲池中页的修改，显示写入重做日志文件，然后再写入磁盘，是一种异步方式。

重做日志记录了事务的行为，可以很好的通过其进行重做，但是事务有时还需要撤销，这时就需要undo，undo与redo正好相反，对于数据库进行修改时，数据库不但产生redo，而且还会产生一定量的undo，即使你执行的事务或语句由于某种原因失败了，或者如果你用的一条rollback语句请求回滚，就可以利用这些信息将数据回滚到修改之前的样子，与redo不同的是，redo存放在重做日志文件中，undo存放在数据库内部的一个特殊段中，undo段位于共享表空间ibdata内。

分布式事务
InnoDB存储引擎支持XA事务，通过XA事务可以来支持分布式事务的实现，分布式事务指的是允许多个独立的事务资源参与一个全局的事务中。事务资源通常是关系型数据库系统，但也可以是其他类型的资源。全局事务要求在其中所有参与的事务要么都提交，要么都回滚。在使用分布式事务时，innodb存储引擎的事务隔离级别必须设置为serialiable。

Lock 锁
防止多个会话同时更改同一数据（同一行数据）
发生事务时锁自动获得
一定要避免锁升级（行锁升级到表锁）
mysql的锁机制比较简单，最显著的特点就是不同的存储引擎支持不同的锁机制。

--------------------------------------------------------------------
				｜	行锁	｜	表锁	｜	页锁	
--------------------------------------------------------------------
MyISAM			｜		｜	是 	｜	
--------------------------------------------------------------------
BDB				｜		｜	是 	｜	是	
--------------------------------------------------------------------
MyISAM			｜	是	｜	是 	｜		
----------------------------------------------------------------------			

从开锁，加锁速度，死锁，粒度，并发性能方面：
表锁：开销小，加锁快；不会出现死锁；锁力度大，发生锁冲突概率高，并发度最低
行锁：开销大，加锁慢；会出现死锁；锁力度小，发生锁冲突的概率低，并发度高
页锁：开销和加锁速度介于表锁和行锁之间；会出现死锁；锁定粒度介于表锁和行锁之间，并发度一般
表锁更适合以查询为主，只有少量按索引条件更新数据的应用；行锁更适用于有大量按索引条件并发更新少量不同的数据，同时又有并发查询的应用。注意：BDB已经被innodb所取代。
MyISAM在执行查询语句前，会自动给涉及的所有表加读锁，在执行更新操作（update ，delete，insert等）前，会自动给涉及的表加写锁，这个过程并不需要用户干预，因此，用户一般不需要直接用LOCK TABLE命令给MyISAM表显式加锁。加锁只是为了模拟事务操作。
例如：
LOCAK TABLE orders read, order_detail read; —把表锁定只允许读
Unlock tables; -----完成业务后，将表锁释放。

对innodb表，在绝大部份情况下都应该使用行级锁，但是在个别特殊事务中，也可以考虑使用表级锁。
第一种情况：事务需要更新大部分或者全部数据，表又比较大，如果使用默认的行锁，不仅这个事务执行效率比较低，而且可能造成其他事务长时间锁等待和锁冲突，这种情况下可以考虑使用表锁来提高该事务的执行速度。
第二种情况：事务涉及多个表，比较复杂，很可能引起死锁，造成大量事务回滚。这种情况也可以考虑一次型锁定事务涉及的表，从而避免死锁，减少数据库因事务回滚带来的开销。

mysql也支持 lock tables 和unlock tables ，这都是在服务器层实现的，和存储引擎无关。

LOCK TABLES 可以锁定用于当前会话的表。如果表被其他会话锁定，则当前会话会等待，直到可以获取所有锁定为止。
UNLOCK TABLES 可以释放当前会话获得的任何锁定。当前会话执行另一个LOCK TABLES时，或当与服务器的连接被关闭时，所有由当前会话锁定的表被隐含地解锁。
在用LOCK TABLES 对InnoDB表加锁时要注意，要将AUTOCOMMIT 设为0，否则mysql不会给表加锁；事务结束前，不要用UNLOCK TABLES 释放表锁，因为UNLOCK TABLES会隐含地提交事务；
COMMIT 或ROLLBACK 并不能释放用 LOCK TABLES 加的表级锁，必须用UNLOCK TABLES 释放表锁。
例如需要写表t1 并从表t2读：

LOCK TABLES t1 WRITE,t2 READ;
INSERT INTO t2 values（10000）；--不能成功
select * from t1;--可以查询
COMMIT;
UNLOCK TABLES;

InnoDB行锁时通过给索引上索引项来实现的，这一点mysql与oracle不同，后者是通过在数据块中相应数据行来实现的。InnoDB这种行锁实现特点意味着：
只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁！

间隙锁（Next-Key锁）
当我们用范围条件而不是相等条件检索数据，并请求共享或拍他锁时，innodb会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录，叫 “间隙”，innodb也会对这个间隙加锁，这种锁机制就是所谓的间隙锁。
比如emp表中有107条记录，其中emp_id的值分别是：1，2，… 106,107,下面的sql：
select * from emp where emp_id > 100 for update;
是一个范围条件的检索，innodb不仅会对符合条件的emp_id值为107的记录加锁，也会 对大于107的间隙加锁。
innodb使用间隙锁的目的是，一方面是为了防止幻读，以满足相关隔离级别的要求另一方面是为了满足其恢复和复制的需要。有关其恢复和复制对锁机制的影响，以及不同隔离级别下innodb使用间隙锁的情况。
很显然，在使用范围条件检索并锁定记录时，innodb这种加锁机制会阻塞符合条件范围内键值的并发插入，这往往会造成严重的锁等待。因此尽量使用相等条件来访问更新数据，避免使用范围条件。
注意：innodb除了通过范围条件加锁时使用间隙锁外，如果使用相等条件请求一个不存在的记录加锁，innodb也会使用间隙锁。
解决锁冲突：
让持有锁的会话或者事务提交或者回滚
终止持有锁的会话或者事务，相关事务会自动回滚。

死锁
死锁时止两个或多个事务在同一资源上互相占用，并请求锁定对方占用的资源，从而导致恶性循环。
当事务试图以不同的顺序锁定资源时，就可能产生死锁。多个事务同时锁定同一资源也可能产生死锁。
死锁有双重原因：真正的数据冲突；存储引擎的实现方式。
检测死锁：数据库系统实现可各种死锁检测和死锁超时的机制。innodb存储引擎检测到死锁的循环依赖并立即返回一个错误。
死锁恢复：死锁发生以后，只有部分或者完全回滚其中一个事务，才能打破死锁，InnoDB处理死锁的方式是：将持有最少行排他锁的事务进行回滚。
外部锁的死锁检测：发生死锁后，innodb一般都能自动检测到，并使一个事务释放锁并回退，另一个事务获得锁，继续完成事务。但是涉及外部锁，或者涉及表锁的情况下，innodb并不能完全自动检测到死锁，这需要通过设置锁等待超时参数 innodb_lock_wait_timeout来解决。
死锁影响性能：死锁影响性能而不是会产生错误，意味innodb会自动检测死锁状况并回滚其中一个受影响的事务。在高并发系统上，当许多线程等待同一个锁时，死锁检测可能导致速度变慢。有时当发生死锁时，禁用死锁检测（使用innodb_deadlock_detect配置选项）可能会更有效，这时可以依赖innodb_lock_wait_timeout设置进行事务回滚。

REDO日志

REDO日志的作用：保证事务的持久性。
重做日志时一种基于磁盘的数据结构，因为REDO日志会在磁盘的相应位置产生记录。它的主要作用在于在崩溃恢复期间纠正不完整事务写入的数据。在正常操作期间，重做日志对由SQL语句或者低级API调用产生的更改表数据的请求进行记录。在初始化期间和接受连接之前，会自动重做在意外关闭之前未完成更新数据文件的修改。
REDO 日志可以分为物理redo日志和逻辑redo日志。
在innodb存储引擎中，大部分情况下redo时物理日志，记录的是数据页的物理变化。而逻辑redo日志，是记录修改页的一类操作，比如新建数据页时，需要记录逻辑日志。绝大数情况下，redo时物理日志，DML对页的修改操作，均需要记录Redo。
redo log可以简单分为以下两个部分：
1.是内存中重做日志缓冲，是易失的，在内存中；
2是重做日志文件，是持久的，保存在磁盘。

什么时候写Redo？
在数据页修改完成之后，在脏页刷到磁盘之前，写入redo日志。注意的是先修改数据（数据在内存中的变化，并提交），后写日志（提交后把日志写到磁盘里）。但是这里需要注意的是，在未提交前，redo日志在内存的日志缓冲区中，并没有写回到磁盘上。

redo日志比数据页先写回磁盘
mysql每执行一条DML语句，先将记录写入 redo log buffer，后续某个时间点再一次性将多个操作记录写到 redo log file。这种先写日志，再写磁盘的技术就是WAL技术。
需要注意的是，在计算机操作系统中，用户空间下的缓冲区数据一般情况下是无法直接写入磁盘的，中间必须经过操作系统内核空间缓冲区，因此，redo log buffer写入 redo log file 实际上是先写入 os buffer，然后通过系统调用fsync（）将其刷到redo log file中。

聚集索引，二级索引，undo页面的修改，均需要记录redo日志。

Redo的整体流程
1.先将原始数据从磁盘中读入内存中来，并确定要修改行数据是否有锁。
2.生成一条重做日志并写入到redo log buffer，记录的是数据被修改后的值。要修改的数据进入到change buffer ，并修改。
3.当事务commit时，将redo log buffer 中的内容刷新到redo log file，对redo log file采用追加写的方式
4.定期将内存中修改的数据刷新到磁盘中。
LSN日志序列编号
LSN称为日志的逻辑序列号，在innodb存储引擎中，lsn占用8个字节。lsn的值会随着日志的写入而逐渐增大。
根据lsn，可以获取几个有用的信息：
1.数据页的版本信息。
2.写入的日志总量，通过lsn开始号码和结束号码可以计算出写入的日志量。
3.可知道检查点的位置
lsn不仅存在redo log 中，还存在数据页中，在每个数据页的头部FILE_HEADER部分，有一个FILE_PAGE_LSN记录了该数据页最后被修改的日志序列位置，数据页中还存在FILE_PAGE_FILE_FLUSH_LSN，通过数据页中lsn值和redo log中的lsn值比较，如果页中的lsn值小于redo log中的lsn值，则表示数据丢失了一部分，这时候可以通过redo log的记录来恢复到 redo log中记录的lsn值时的状态。
redo log的LSN信息可以通过 show engine innodb status\G;命令来查看。
log sequence number 就是当前的redo log（in buffer） 中的lsn
log flushed up to 是刷到redo log file on disk中的lsn
pages flushed up to 是已经刷到磁盘数据页上的lsn
last checkpoint at 是上一次检查点所在位置的lsn

REDO日志与事务的持久性
mysql支持三种将REDO日志写入到磁盘的机制。
可以通过 innodb_flush_log_at_trx_commit参数配置
select @@innodb_flush_log_at_trx_commit; 结果值有0，1，2

0（延迟写）	事务提交时不会将 redo log buffer 中的日志写入到 os buffer，而是每秒写入 os buffer 并调用 fsync()写入到 redo log file中，也就是说设置为0时是（大约）每秒刷新写入到磁盘中的，当系统崩溃，会丢失1秒中的数据。
1（实时写，实时刷新）	事务每次提交都会将 redo log buffer中的日志写入到os buffer，并调用fsync（）刷新到 redo log file中。这种方式即使系统崩溃也不会丢失任何数据，但是因为每次提交都写入磁盘，IO的性能较差。
2（实时写，延迟刷新）	每次提交都仅写到 os buffer，然后是每秒调用 fsync（）将os buffer中的日志写入到 redo log file

innodb_log_files_in_group: redo log文件的个数，命名方式如：ib_logfile0,iblogfile1…iblogfilen.默认2个，最大100个。
innodb_log_file_size:文件设置大小，默认为48M，最大值为512G，注意最大值指的是整个redo log系列文件之和。
innodb_log_group_home_dir:文件存放路径
innodb_log_buffer_size:redo log缓存区，默认8M，可设置1-8M，延迟事务日志写入磁盘，把redo log 放到该缓冲区，然后根据innodb_flush_log_at_trx_commit参数设置。
redo log文件以ib_logfile[number]命名，redo log以顺序的方式写入文件，写满时则回溯到第一个文件，进行覆盖。
在mysql中，redo log时一个文件组，一般时3个文件，循环写入，写满的时候会做redo log层面的checkpoint，然后覆盖之前的redo log；而binlog时有归档功能的，每个binlog写满之后，都会重新开启下一个binlog开始写入。这也是为什么可以使用binlog来进行数据恢复的一个原因，就是因为它的归档功能。
mysql 8.0.17中引入了redo log的归档功能。
使用 show variables like ‘%archive%’;是否开启了归档。
redo log的归档路径有如下限制：
1.目录必须存在，而且其他用户不可访问，最好是700的权限
2.该用户目录不能和datadir，innodb_tmpdir，以及其他mysqld的运行目录重合，需要单独创建。

1.首先在系统下创建一个目录，并且赋予相关权限
mkdir /var/mysql_arch
chown -R mysql.mysql	/var/mysql_arch
chmod	700 /var/mysql_arch
2.mysql数据中设置相关参数
(1)
set global innodb_redo_log_archive_dirs = 'tmp_redo_dir:/var/mysql_arch';
(2)生成归档测试：
do innodb_redo_log_archive_start('tmp_redo_dir');
检查归档是否生成：
cd /var/mysql_arch
ll

3.关闭归档就一句话：
do innodb_redo_log_archive_stop();

UNDO日志

UNDO数据是
事务行为的记录
每次更改数据的旧数据记录
至少保留到事务结束
用于支持 回滚操作，MVCC机制的重要保障，在实例的恢复时起到重要作用。

UNDO信息存储在回滚段中，回滚段存储在UNDO表空间中。

UNDO表空间
仅用于undo段保存
有特殊的恢复注意事项
可能仅与一个实例相关联
mysql5.6之前，undo表空间位于共享表空间的回滚段中，共享表空间的默认名称时ibdata，位于数据文件目录中。
mysql5.6之后，undo表空间可以配置成独立的文件，但是提前需要在配置文件中配置，完成数据库初始化后生效且不可改变undo log文件的个数
如果初始化数据库之前没有进行相关配置，那么就无法配置成独立的表空间了。

mysql5.7之后的独立undo表空间配置参数：
innodb_undo_log_truncate:参数设置为1，即开启在线回收（收缩）undo log日志文件，支持动态设置。
innodb_undo_directory: undo表空间的位置
innodb_undo_logs: rollback seg数量，比如将2G的undo tablespace ，切分成多少分 rollback seg。默认128个，实例初始化后不可再修改。每个undo log seg 可以最多存放1024个事务。
innodb_undo_tablespaces:undo log 文件数，每个文件默认10MB，数量默认0，最大95个，最小2个，因为在truncate一个undo log文件时，需要保证另外一个是可用的，这样就无需停止业务了。
innodb_max_undo_log_size:控制最大undo tablespace文件的大小，当超过这个阈值（默认时1G），会触发truncate回收（收缩）动作，truncate后空间缩小到10M。

show variables like '%innodb_undo%';
--------------------------------------
variable_name				|	value
--------------------------------------
innodb_undo_directory		| 	./
innodb_undo_log_truncate	|	OFF
innodb_undo_logs			|	128
innodb_undo_tablespaces		|	0
----------------------------------------
这里我们可以看到没有开启独立的undo表空间，而是使用共享表空间去处理undo信息。

mysql5.7开启独立undo表空间，在my.cnf中添加

[mysqld]下添加
innodb_undo_directory=/data/mysql_data/undo
innodb_undo_tablespaces=4
innodb_undo_logs=128
innodb_max_undo_log_size=1G
innodb_undo_truncate=1
保存
#停止mysql
service mysqld stop
rm -rf /data/mysql_data/*
rm -rf /data/mysql_log/*
rm -rf /data/log-bin/*

--初始化
cd /usr/local/mysql/bin
./mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql_data

--启动数据库
service mysqld start;

--查看密码
cat /data/mysql_log/mysql-error-3306.log | grep password
mysql -uroot -p'密码'
set password = password('root');
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'root' WITH GRANT OPTION;
FLUSH PRIVILEGES;

mysql -uroot -proot

show variables like '%innodb_undo%';

mysql从8.0开始undo表空间管理已经发生了改变，在5.7版本中一旦mysql初始化以后，就不能再改变undo表空间了，所以我们在5.7版本中都是初始化的时候对undo表空间进行一些设置，类似这样：在my.cnf文件中加入innodb_undo_directory=/data/mysql/undologs和innodb_undo_tablespaces=5这两个参数，之所以这么修改，是因为我们想把undo表空间单独从系统表空间分离出来，这样就可以消除undo的问题造成对ibdata系统表空间的影响。如果不设置，那么在5.7版本中，undo还是默认会放在ibdata中。

从mysql8.0版本开始，mysql默认对undo进行了分离操作，也就是说，不需要在初始化中手动配置参数，默认会在datadir目录下生成两个大小相同的undo表空间文件undo_001和undo_002并且可以在线的增加和删除undo表空间进行动态扩容和收缩。
mysql8.0支持undo tablespace加密

SELECT TABLESPACE_NAME,FILE_NAME FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE LIKE 'UNDO LOG';
----------------------------------------------------
TABLESPACE_NAME			|	FILE_NAME
-----------------------------------------------------
innodb_undo_001			|	./undo_001
innodb_undo_002			|	./undo_002
-----------------------------------------------------

SHOW STATUS LIKE 'Innodb_undo_tablespaces%';
----------------------------------------------
variable_name						|	value
-----------------------------------------------
Innodb_undo_tablespaces_total		|	2
Innodb_undo_tablespaces_implicit	|	2
Innodb_undo_tablespaces_explicit	|	0
Innodb_undo_tablespaces_active		|	2
------------------------------------------------

#是否开启在线回收undo log日志文件，支持动态设置，默认开启
innodb_undo_log_truncate=1
#当超过这个阈值（默认1G），会触发truncate回收动作，truncate后空间缩小到10M
innodb_max_undo_log_size=4G
#没有配置则在数据文件目录下
innodb_undo_directory=/var/lib/mysql/undolog

mysql8.0手工管理UNDO表空间

CREATE UNDO TABLESPACE tablespace_name ADD DATAFILE ‘file_name.ibu’;
在线删除undo表空间
ALTER UNDO TABLESPACE tablespace_name SET INACTIVE;
DROP UNDO TABLESPACE tablespace_name;
需要2个步骤，先设置为inactive状态，然后再去删除。

SELECT TABLESPACE_NAME,FILE_NAME FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE LIKE 'UNDO LOG';
----------------------------------------------------
TABLESPACE_NAME			|	FILE_NAME
-----------------------------------------------------
innodb_undo_001			|	./undo_001
innodb_undo_002			|	./undo_002
-----------------------------------------------------
CREATE UNDO TABLESPACE undo_003	ADD DATAFILE '/data/mysql_data/undo_003.ibu';
SELECT TABLESPACE_NAME,FILE_NAME FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE LIKE 'UNDO LOG';
----------------------------------------------------
TABLESPACE_NAME			|	FILE_NAME
-----------------------------------------------------
innodb_undo_001			|	./undo_001
innodb_undo_002			|	./undo_002
indo_003				|	/data/mysql_data/undo_003.ibu
-----------------------------------------------------

SHOW STATUS LIKE 'Innodb_undo_tablespace%';
----------------------------------------------------
variable_name							|	value
-----------------------------------------------------
Innodb_undo_tablespace_total			|	3
Innodb_undo_tablespace_implicit			|	2
Innodb_undo_tablespace_explicit			|	1
Innodb_undo_tablespace_active			|	3
-----------------------------------------------------


--删除表空间
ALTER UNDO TABLESPACE undo_003 SET INACTIVE;
SHOW STATUS LIKE 'Innodb_undo_tablespace%';
----------------------------------------------------
variable_name							|	value
-----------------------------------------------------
Innodb_undo_tablespace_total			|	3
Innodb_undo_tablespace_implicit			|	2
Innodb_undo_tablespace_explicit			|	1
Innodb_undo_tablespace_active			|	2
-----------------------------------------------------
DROP UNDO TABLESPACE undo_003;
SHOW STATUS LIKE 'Innodb_undo_tablespace%';
----------------------------------------------------
variable_name							|	value
-----------------------------------------------------
Innodb_undo_tablespace_total			|	2
Innodb_undo_tablespace_implicit			|	2
Innodb_undo_tablespace_explicit			|	0
Innodb_undo_tablespace_active			|	2
-----------------------------------------------------

MVCC基本知识
事务的工作流程
1.所有被发起的事务首先被分配一个事务ID，事务ID依次递增
2.然后再给事务分配一个事务槽，将事务信息写入事务槽中
3.开始修改数据行前，数据行中存储事务ID，修改前老数据所使用的UNDO页地址
4.UNDO页中存放修改前的数据
5.属于一个事务的哥哥UNDO页连接起来
6.回滚段段头页中的地址指向UNDO页链表中最后一个UNDO页
7.一个UNDO页只能存放一个事务的数据
8.事务提交就是事务槽中将事务状态改成已提交。

回滚段分类
update_undo:只用于事务内的update和delete 语句，这里会将undo日志加入到其对应rollback segment的history list 数据页列表上，history list 长度随之加1。UNDO日志需要保障mvcc机制，因此不能在事务提交时就进行删除。事务提交后放入UNDO 日志链表，等待purge线程进行消除。

insert_undo：只用于事务内的insert语句新插入记录产生的undo，由于insert语句只针对于事务本身，所以其他会话的查询是不可见的，而且在事务提交后是直接释放undo，这里的undo log不会累加到history list上。切记：insert 操作的记录只对事务本身可见。所以该undo log在事务提交后直接删除，不需要进行purge操作。

事务rollback
撤销事务
对于已标记删除的记录清理标记
对于插入操作，直接删除聚簇索引和二级索引记录。

purge机制
Purge线程用来最终完成delete和update操作。他是一个周期运行的垃圾收集线程 ，主要用来回收undo回滚段空间，以及已经被废弃的索引数据。

相关参数
全局动态参数 innodb_purge_batch_size 用来设置每次purge操作需要清理的undo page数量。默认值为300.
全局动态参数 innodb_max_purge_log 用来控制history list的长度，若大于该参数时，其会延缓DML的操作。
全局动态参数 innodb_max_purge_log_delay 用来控制DML操作每行数据的最大延缓时间，单位为秒。
全局动态参数innodb_purge_rseg_truncate_frequency ,用于控制purge回滚段的频率，默认128，表示purge undo 轮询128次，进行一次undo 的truncate。
innodb_purge_threads:清理线程的个数，默认是4.

事务提交后的清理
1.清理事务提交后不需要的undo信息，从回滚段的history 文件链表（history list）上开始遍历释放undo rollback segment（回滚段），由于history链表是按照trx_id有序记录的，因此按序遍历直到完全清除，或者遇到一个还未purge的undo日志才停止。
2.清理索引上被打上delete标记的数据
3.相关参数变量 innodb_purge_threads,默认是4；
SELECT @@innodb_purge_threads;

undo什么时候释放
当事务提交后，undo log并不能立刻被删除，而是放入待清理的链表，由purge线程判断是否有其他事务在使用undo段中的上一个事务之前的版本信息，再决定是否可以清理undo段的空间。
如果事务rollback，innodb通过执行undo log中的所有反向操作，实现事务中所有操作的回滚，随后就会删除该事务关联的所有undo段。

undo log的写入时机：
DML操作修改聚簇索引前，记录undo日志
二级索引记录的修改，不记录undo日志
需要注意的是，undo页的修改，同样需要记录redo日志
再详细点：undo是在事务开始之前保存的被修改数据的一个版本，产生undo日志的时候，同样会伴随类似于保护事务持久化机制的redo log的产生。

undo结构的简单说明
1.undo的组织结构从上到下为undo表空间，回滚段，undo log slot，page。
2.undo表空间由128个回滚段构成，32个回滚段用于系统的临时表空间，96个回滚段用于事务。
3.每个回滚段维护了一个段头页，在该page中又划分了1024个slot，每个slot又对应到一个undo log对象，因此理论上innodb最多支持96*1024个普通事务。
4.独立表空间的space id都是从1开始，0被预留在ibdata中，space id必须是连续分配的，不能断档。
5.8.0之前的版本默认只能创建128个回滚段。

BINLOG日志

mysql的二进制日志-BINLOG，可以说是mysql最重要的日志，它记录了所有的DDL和DML语句（除了select ，show等），以时间形式记录，还包含语句所执行消耗的时间，mysql的二进制日志是事务安全型的。binlog的主要目的是复制和恢复。并且binlog日志并不是innodb存储引擎特有的，他是属于mysql service的，和哪种存储引擎没有关系。
BINLOG日志的两个最重要的使用场景：
1）mysql主从复制：mysql replication 在master端开启binlog，master把它的二进制日志传递给slaves来达到master-slave数据一致的目的。
2）数据恢复：通过使用mysqlbinlog工具来使恢复数据。

BINLOG 和REDO的区别
MySQL的BINLOG是属于MYSQL的服务层面上的二进制日志，而且他是一种逻辑日志，只记录了对应的SQL语句。
InnoDB存储引擎的REDO日志记录的是数据页被修改的相关过程，并且包括UNDO的REDO等信息，REDO属于物理日志。
两种日志的写入时间也不一样，BINLOG只在事务提交的时候写入，REDO需要在事务过程中不断的写入。

BINLOG的格式
binlog有三种格式：Statement,Row以及Mixed.

–基于SQL语句的复制（statement）
每一条会修改数据的sql都会记录在binlog中。
优点：不需要记录每一行的变化，减少了binlog日志量，节约了IO，提高性能。
缺点：由于记录的只是执行语句，为了这些语句能在slave上正确运行，因此还必须记录每条语句在执行的时候的一些相关信息，以保证所有语句能在slave得到和master端执行时候相同的结果。另外mysql复制一些向sysdate()这样的特定函数时，在同步过程中可能会出现一定的问题。
–基于行的复制（row）
5.1.5版本的mysql才开始支持row level的复制，它不记录sql语句上下文相关信息，仅保存那条记录被修改。
优点：binlog中可以不记录执行的sql语句的上下文相关的信息，仅需要记录哪一个记录被修改成什么了。所以row-level的日志内容会非常清楚的记录每一行数据修改的细节。而且不会出现某些特定情况下的存储过程，或function，以及trigger的调用和触发无法被正确复制的问题。
缺点：所有的执行语句当记录到日志中的时候，都将以每行记录的修改来记录，这样可能产生大量的日志内容并且占用大量的IO。
–混合模式复制（mixed）
从5.1.8版本开始，mysql提供了mixed格式，实际上就是statement和row的结合。
在mixed模式下，一般的语句修改使用statement格式保存binlog，如一些函数，statement无法完成主从复制的操作，则采用row格式保存binlog，mysql会根据执行的每一条具体的sql语句来区分对待记录的日志形式，也就是在statement和row之间选择一种。

BINLOG的相关参数
log-bin:决定了mysql-binlog的名字（这里的名字用的是binlog的绝对路径）
例如：
log-bin=/data/log-bin/mysql-bin-3306 那么产生的binlog日志文件为：
/data/log-bin/mysql-bin-3306.000xx
binlog-format:规定binlog的格式，binlog有三种格式statement，row，mixed，默认使用statement，建议使用row格式
例如：binlog-format=ROW

expire_logs_days:binlog日志的过期时间，这个根据自己的业务需求进行调整
例如：expire-logs-days=14 这里规定的bin-log过期时间为14天

binlog_do_db:此参数表示只记录指定数据库的二进制日志
例如：binlog_log_db = hr
如果想要记录多个的话，必须分开指定
binlog_log_db = hr
binlog_log_db = hr1
binlog_log_db = hr2

binlog_ignore_db:此参数表示不记录指定的数据库的二进制日志
例如：binlog_ignore_db=hr
如果想要记录多个的话，必须分开指定
binlog_ignore_db = hr
binlog_ignore_db = hr1
binlog_ignore_db = hr2

sync_binlog:在提交n次事务后，进行binlog的的落盘，0为不进行强行的刷新操作，而是由文件系统控制刷新日志文件，如果时在线交易和账目有关的数据建议设置成1，如果其他数据可以保持为0即可。
例如；sync_binlog=1
max_binlog_size:binlog文件的最大值，默认的最大时1GB
例如：max_binlog_size=500M
max_binlog_cache_size:表示的是binlog能够使用的最大cache内存大小，当我们执行并发事务的时候，所有session使用的内存超过max_binlog_cache_size的值时，就会报错。
例如：max_binlog_cache_size=1G

BINLOG日志的写入机制
当发生以下情况时BINLOG日志开始写入：
1.根据sync-binlog参数所设定的值
2.根据你所设定的BINLOG日志最大文件大小
3.重新启动mysql数据库的时候
4.mysql服务器执行flush logs命令可以产生新的binlog文件
5.这里BINLOG和REDO日志最大的区别在于，BINLOG写完以后自动生成下一个新文件，我们可以视为BINLOG的归档动作，而REDO必须指定其开启归档操作后才能归档，它不会自动生成归档日志。

--查看是否开启BINLOG
show variable like 'log_bin';
如果binlog没有开启，可以通过set sql_log_bin=1命令来启用；如果
想听用binlog可以使用set sql_log_bin=0.
或者直接去my.cnf文件中设定，然后重启mysql服务。

mysqlbinlog命令

1.将指定的binlog日志文件导出到操作系统的文件系统中：
mysqlbinlog --no-defaults /data/log-bin/mysql-bin-3306.000109 > /tmp/mysql_binlog.sql

2.将指定BINLOG文件中的指定时间内的日志内容导出到操作系统的文件系统中：
mysqlbinlog --no-defaults --start-datetime=“2021-10-31 0:00:00” -stop-datetime=“2021-10-31 02:00:00” /data/log-bin/mysql-bin-3306.000109 > /tmp/mysql_binlog_2.sql

查看BINLOG日志的相关信息

--只查看第一个binlog文件的内容
show binlog events;
--查看指定binlog文件的内容
show binlog events in 'mysql-bin-3306.000109';

删除BINLOG
1.使用linux命令删除binlog文件（rm -rf）
2.设置binlog的过期时间

3.手动删除binlog
reset master ;//删除master中的binlog
reset slave;//删除slave中的中继日志
purge master logs before ‘2021-10-30 17:20:00’;//删除指定日期以前的binlog日志文件
purge master logs to ‘mysql-bin-3306.000109’;//删除指定日志文件

MYSQL分区表

MyISAM存储引擎使用的就是堆表，INNODB存储引擎使用的就是索引聚簇组织表：
所谓的堆表，就是简单的把数据堆砌到某个数据文件中。
所谓的索引聚簇组织表就是将索引和数据放到一起进行存储，一次来加快查询速度并提高IO性能。而堆表一般是将索引数据和表数据分开存放的。
MYSQL分区表简单而言就是将一张大的表按照一定的规则划分为物理上很多小表，而逻辑上仍然维持一个大表，对应用是透明的。

分区的最大优势
性能
SELECT和DML操作只能访问指定分区
并行DML操作
Partition-wise Join

可管理性：数据删除，数据备份
历史数据清除
提高备份性能
指定分区的数据维护操作

可用性
将故障局限在分区中
缩短恢复时间

分区目标优先级
高性能->数据维护能力-》实施难度-〉高可用性（故障屏蔽能力）

分区优点：
1）提高访问速度
分区对应用是透明的，可以通过标准的sql语句对分区表进行操作
显著提高访问大表时的性能，使数据库操作得到更快相应。
2）提高数据可用性
在线增加，删除和改名，特定分区上的批处理
按分区备份
在维护过程中可以继续访问不受影响的分区
快速灾难恢复：恢复最关键的数据分区
减少MTTR
3）滚动窗口操作
添加删除分区其他数据和查询均不受影响

分区字段与分区索引的简单规则
索引方式：性能依次降低
1）主键分区
主键分区即字段是主键同时也是分区字段，性能最好
2）部分主键+分区索引
使用复合主键里面的部分字段作为分区字段，同时将分区字段建索引
3）分区索引
没有主键，只有分区字段且分区字段建索引
4）分区+分区字段没有索引
只建了分区，但是分区字段没有建索引

分区表的主要类型：
范围分区，哈希分区，列表分区以及 键值分区

分区表的创建
1.范围分区
范围分区是按照分区表达式的运算结果，判断结果落在某个范围内，从而将数据存储在对应的分区。各个分区定义之间需要连续且不能重叠，范围分区通过 partition by range子句定义，而分区的范围通过 values less than子句划分。
例如：根据员工id分区

create table emp(
	id int primary key,
	name varchar(30),
	hire_date date
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
partition by range(id)(
	partition p0 values less than (11),
	partition p1 values less than (21),
	partition p2 values less than (31),
	partition p3 values less than (41)
);

--分区结构查询
show create  table test.emp\G;

--可以指定分区查询
select * from emp partition(p0);

select * from emp partition(p0,p1);

注意：我们可以发现不在上面分区的数据插入不进去，该怎么办？

--添加分区
alter table emp add partition (partition p4 values less than (51));
--添加分区：分区值超出的分区
alter table emp add partition (partition pmax values less than(maxvalue));

--删除分区表和删除普通表一样
drop  table emp;

--我们创建了maxvalue这个值范围外的值，看一下是否还可以插入一个新的分区进来
alter table emp add partition (partition p4 values less than (51));
发现插入报错。
--分区删除语句
alter table emp drop partition pmax;
--我们发现pmax分区中的数据也同样删除了。所以以后要慎用pmax分区

分区重组织
如果一定要在分区之间插入新的分区，则可以采用重组织的方式，将已有的分区的数据重新划分，达到创建新分区的效果。
上面的例子中，我们删除p1分区后，不能再添加p1分区，因为每个分区都要依次递增，所以
我们可以把p2划分为2个分区，分别是11-20，21-30:

alter table emp reorganize partition into(
	partition p1 values less than (21),
	partition p2 values less than (31);
);

范围分区还有一个变种，叫做 range columns 分区。此分区方式允许使用多个column来作为分区字段，并且可以使用多个列的值来定义分区范围。但是此分区方式不能接受函数，只能直接用列的名称。但是对分区列的类型不再限制为整形INT，可以使用char，varchar，date等。

create table emp1(
	id int primary key,
	name	varchar(30),
	hire_date date 
)
partition by range columns(hire_date)(
	partition p0 values less than ('2020-01-01'),
	partition p1 values less than ('2021-01-01'),
	partition p2 values less than ('2022-01-01'),
	partition p3 values less than ('2023-01-01')
);
报错：A PRIMARY KEY must include all columns in the table's partitioning function

create table emp1(
	id int ,
	name	varchar(30),
	hire_date date 
)
partition by range columns(hire_date)(
	partition p0 values less than ('2020-01-01'),
	partition p1 values less than ('2021-01-01'),
	partition p2 values less than ('2022-01-01'),
	partition p3 values less than ('2023-01-01')
);
--我们发现去掉主键后 成功了

mysql主键的限制，每一个分区表中的分区字段，必须包含在主键中，或者他是个唯一键。
注意：在做分区表时，选择分区字段要谨慎，需要仔细判断这个字段拿来作为分区字段是否合适，这个字段加入到主键中作为复合主键是否适合。
所以添加主键`

alter table emp1 add primary key(id,hire_date);
--创建索引：
create index idx_hire_date on emp1(hire_date);

列表分区
列表分区和范围分区类似，主要区别是list partition的分区范围是预先定义好的一系列值，而不是连续的范围，列表分区采用partition by list 和 values in 子句
例子：

CREATE TABLE emp(
	id int(6) primary key,
	first_name varchar(20),
	last_name varchar(25),
	email varchar(25),
	salary int(8),
	department_id int (4)
)
PARTITION BY LIST(id)(
	PARTITION p0 values IN(101,103,105,107,109),
	PARTITION p1 values IN(102,104,106,108,110)
);

LIST COLUMNS分区表，分区字段支持使用char，varchar，date等数据类型。

CREATE TABLE emp(
	id int(6) ,
	first_name varchar(20),
	last_name varchar(25),
	email varchar(25),
	salary int(8),
	department_id int (4)
)
PARTITION BY LIST COLUMNS(last_name)(
	PARTITION p0 values IN('King',Grant'),
	PARTITION p1 values IN('Scott','Jim')
);

List partition在各类存储引擎的表现：
如果插入的值，在list分区范围中不存在的话，会返回错误，如果表使用的是事务型引擎，如innodb。则这个事务会完全回滚。如果使用的是非事务引擎，若MyISAM，虽然也会报错，但是已经插入的行无法回滚。(插入范围之外的数据 insert values（101-111）的数据，InnoDB和myisam都会报错，但是innodb没有插入数据，myisam插入了数据)。

哈希分区

hash partition 主要的应用场景是将数据平均的分布在指定数量的hash分区中。在range和list分区类型中，我们根据分区条件可以获取数据究竟存储在哪个分区，而hash分区中，数据存储在某个分区是由数据库自己决定的，你只需要指定分区的数量。
要创建hash分区，你需要使create table 的partition by hash(expr)子句，其中expr是整数类型的列或返还整数的表达式。另外还需要使用 partitions num来指定hash分区的数量。（若忽略partitions子句则默认只创建一个hash分区），数据存放在哪里取决于select mod（expr,num）,常规hash分区非常的简单，通过取模的方式可以让数据非常平均的分布在每一个分区。

CREATE TABLE hash_p(
	id int primary key,
	name varchar(1)
)
PARTITION BY HASH(id)
PARTITIONS 4;

--使用该语句知道分区状况
SELECT PARTITION_NAME,PARTITION_METHOD,PARTITION_EXPRESSION,PARTITION_DESCRIPTION FROM information_schema.PARTITIONS WHERE TABLE_SCHEMA=SCHEMA() AND TABLE_NAME = 'hash_p';

管理hash分区的数量：
hash分区无法像range和list那样添加和删除分区，但是你可以用 alter table 的coalesce partition子句来减少hash分区的数量，当然这种减少只是一种合并分区的策略。用add partition partitions N来增加指定数量的分区。

--将emp的hash分区数量由4调整为3（合并）：
ALTER TABLE hash_p COALESCE PARTITION 1; --移除一个分区
--为表新增3个hash分区
ALTER TABLE hash_p ADD PARTITION PARTITIONS 3; ---新增3个hash分区

对于hash partition和key partition,任何表达式对null运算，都会被当作返回为0.
另外hash分区只能针对整数进行hash，对于非整数的字段只能通过函数表达式将其转换成整数。表达式可以是mysql中任意有效的函数或者表达式，对于非整形的hash分区字段中的数据，往表插入数据的过程会多一步表达式的计算操作，所以不建议使用复杂的表达式，这样会影响性能。

CREATE TABLE hash_p(
	id INT  NOT NULL,
	hired DATE NOT NULL DEFAULT '1970-01-01'
)
PARTITION BY HASH(YEAR(hired))
partitions 4;

键值分区-key partition
key partition与hash分区类似，主要区别在于key partition的hash函数是由mysql server提供的，，当没有指定分区字段时，使用主键（或非空唯一键）作为分区列。

CREATE TABLE k_p(
	id int primary key,
	name varchar(20)
)
PARTITION BY KEY()
PARTITIONS 2;

另外对于key partition，partition字段也不想其他分区类型那样限制为整数类型，例如可以使用字符型作为分区字段

CREATE TABLE test1（
	card_id char(18) primary key,
	name varchar(200)
）
PARTITION BY KEY(card_id)
partitions 10;

子分区
复合分区是分区表中每个分区的再次分隔，复合分区既可以使用hash分区也可以使用key分区，在mysql中复合分区一般称为子分区。
提示：mysql的子分区只能使用hash/key分区，这也是与oracle的区别，而且也要知道只有range和list分区表才能做子分区。
那么子分区的最大优点是：可以将数据再次=进一步的分散，降低胆个文件的IO压力。其实我们的分区表，作用也是为了分散IO压力的，但是子分区可以让我们的分区表进一步的去分散压力。

子分区由两种创建方法：
一种是不定义每个子分区，子分区的名字由主分区决定；
二是由我们自己去定义每个子分区的分区名。
注意：如果你要是对某个分区表做子分区的话，那么你每个分区中子分区的数量必须相同。

1.不定义每个子分区
RANGE-HASH子分区：
create table range_hash_partition(
	id int auto_increment,
	count int,
	create_date date,
	notes varchar(16),
	verify int,
	primary key(id,create_date,count)
)
partition by range columns(create_date)
subpartition by hash(count)
subpartitions 2				/*每个分区再根据count拆分为2个hash子分区*/
(partition p1 values less than ('2022-02-01'),
partition p2 values less than('2022-03-01'),
partition p3 values less than('2022-04-01'),
partition p4 values less than('2023-05-01'));

添加与删除分区：

alter table range_hash_partition add partition(partition p5 values less than ('2024-06-01'));
alter table range_hash_partition drop partition p5;

RANGE-KEY子分区

create table range_hash_partition(
	id int auto_increment,
	count int,
	create_date date,
	notes varchar(16),
	verify int,
	primary key(id,create_date,count)
)
partition by range columns(create_date)
subpartition by key(count)
subpartitions 2
(
  partition p1 values less than ('2022-02-01'),
  partition p2 values less than ('2022-03-01'),
  partition p3 values less than ('2022-04-01'),
  partition px values less than maxvalue
)

LIST-HASH子分区表：

create table list_hash_partition(
  id int auto_increment,
  count int,
  create_date date,
  notes varchar(16),
  verify int,
  primary key(id,create_date,count)
)
partition by list columns(count)
subpartition by hash(year(create_date))
partitions 2
(
  partition p1 values in (1,3,5,7,9),
  partition p2 values in (2,4,6,8,10)
);
--查看分区结构
SELECT PARTITION_NAME,PARTITION_METHOD,PARTITION_EXPRESSION,PARTITION_DESCRIPTION,TABLE_ROWS,SUBPARTITION_NAME,SUBPARTITION_METHOD,SUBPARTITION_EXPRESSION FROM information_schema.PARTITIONS WHERE TABLE_SCHEMA=SCHECMA() AND TABLE_NAME='list_hash_partition';

select * from list_hash_partition;
select * from list_hash_partition partition(p1sp0);

LIST-KEY子分区表:

create table list_key_partition(
  id int auto_increment,
  count int,
  create_date date,
  notes varchar(16),
  verify int,
  primary key(id,create_date,count)
)
partition by list columns(count)
subpartition by key(year(create_date))
partitions 2
(
	partition by values in (1,3,5,7,9),
	partition by values in (2,4,6,8,10)
);

第二种定义每个子分区的分区名

create table range_hash_partition(
 id int auto_increment,
 count int,
 create_date date,
 notes varchar(16),
 verify int,
 primary key(id,create_date,count)
)
partition by range columns(create_date)
subpartition by hash(count)
(
  partition p1 less than ('2022-02-01')
  (
	SUBPARTITION sh0,
	SUBPARTITION sh1
   ),
   partition p2 less than ()
   (
     SUBPARTITION sh2,
     SUBPARTITION sh3
   ),
   partition p3 less than ('2022-04-01')
   (
     SUBPARTITION   sh4,
     SUBPARTITION	sh5
   ),
   partition p4 less than ('2023-05-01')
   (
	 SUBPARTITION sh7,
	 SUBPARTITION sh8
	)
);

--添加分区
alter table range_hash_partition add partition (
 partition p5 less than ('2024-05-01')
 (
  SUBPARTITION sh9,
  SUBPARTITION sh10
 )
);

--删除分区
alter table range_hash_partition drop partition p5;

--删除分区中的数据
alter table range_hash_partition truncate partition p5;
alter table range_hash_partition truncate partition p4,p5;