MySQL高性能实战——part1——MySQL架构与历史

前言：
此文借鉴《MySQL高性能》一书，还有MySQL官方文档，笔者将通过自身的一些实战经验和阅读习惯对本书进行一个总结，整理，归纳出企业级开发中常用的优化案列和部分概念！！
​

官方文档地址：
https://dev.mysql.com/doc/refman/5.7/en/
​

本文：由于数据库知识庞大且复杂，笔者将以，以下几个模块进行拆分解读，请大家按需自取！！！

1. MySQL高性能实战——part1——MySQL架构与历史
2. MySQL高性能实战——part2——Schema和数据类型优化
3. MySQL高性能实战——part3——分析SQL,定位慢SQL(性能优化的前提)
4. MySQL高性能实战——part4——高性能索引使用
5. MySQL高性能实战——part5——查询性能优化
6. MySQL主从复制与读写分离，分库分表

​

​

​

版本说明：
不同数据库版本可能对SQL的执行以及部分特性产生巨大的影响，所以在这里明确一下版本
参考书籍版本：5.1或5.5
实战案列实际使用版本：5.7
​

MySQL高性能实战——part1——MySQL架构与历史

为什么要学MySQL架构与历史？

和其他数据系统相比，Mysql有点与众不同，它的逻辑架构可以在多种不同的场景中应用并发挥很好的作用，但同时也会带来一些选择上的困难！MySQL并不完美但却足够灵活，能够适应高要求的环境！同时列如：Web应用！！同时！MySQL既可以嵌入到应用程序中，也可以支持数据仓库，内容索引和部署软件，高可用的沉余系统，在线事务处理系统（OLTP）等各种类型！！
​

为了充分发挥MySQL的性能，我们必须了解MySQL的整个架构， 如：执行了一条SQL,每一步做了什么，底层优化了什么，优化的地方逻辑并顺利使用，就必须理解其设计。//todo

一.MySQL的逻辑架构

第一层：
即最上一层，所包含的服务并不是MySQL所独有的技术。大多数基于网络的客户或服务器都有类似的架构！！比如 ：连接处理，身份验证，安全性等等。
​

第二层
是MySQL比较有意思的部分，大多数MySQL的核心功能都在这一层。包括查询解析，分析，优化，缓存以及所有的内置函数（如日期,时间,数学运算,加密），所有跨存储引擎的功能都在这一层实现：存储过程，触发器，视图等！
​

第三层
包含了存储引擎，存储引擎负责存储和获取所有存储在MySQL中的数据。就像Linux众多的文件系统 一样。
每个存储引擎都有自己的优点和缺陷。服务器通过API与存储引擎进行通信。这些接口屏蔽了不同存储引擎之间的差异，使得这些差异对上层的查询过程透明。
存储引擎API包含几十个底层函数，用于执行，比如：“开始一个事务”或者“根据主键提取一行记录”等操作。但存储引擎不会去解析SQL（InnoDB是一个列外，因为它解析外键定义，因为MySQL服务器本身没有实现此功能），不同存储引擎也不会相互通讯，只会简单地响应上层服务器的请求！
​

1.连接管理和安全

每个客户端连接都会在服务器进程中有一个线程，这个连接的查询都在一个单独的线程中执行。（这就是为什么分布式事务XA，强一致性需要等待其他服务器提交事务，而导致的cpu资源的占用和浪费）这些线程轮流运行在某一个CPU内核(多核CPU)或者CPU中。服务器缓存了线程，因此不需要为每个client连接单独创建和销毁线程 。
​

当客户端（也就是应用程序）连接到了MySQL服务器。服务器需要对它进行认证(Authenticate)。认证是基于用户名，主机，以及密码。
对于使用了SSL（安全套接字层）的连接，还使用了X.509证书。客户端一连接上，服务器就验证它的权限 （如是否允许客户端可以查询db0数据库下的user表的数据）。
[

](https://blog.csdn.net/weixin_42358062/article/details/80730694)

2.优化和执行

MySQL会解析查询，并创建了一个内部数据结构（解析树）。然后对其进行各种优化。这些优化包括了，查询语句的重写，读表的顺序，索引的选择等等。（所以我们写的SQL并非按照我们编写的顺序，或者我们以为的逻辑进行执行，所以我们需要使用Explain关键字，来模拟优化器执行SQL语句的表读取顺序与索引选择，语句重写等，然后进行分析优化）用户可以通过特殊的关键词（hit）优化器，影响它的决策过程！！！//todo


​

优化器并是不关心表使用了哪种存储引擎，但是存储引擎对服务器优化查询的方式是有影响的。
优化器会请求存储引擎提供容量或某个具体的某个操作的开销信息，以及表数据的统计信息！列如：某些存储引擎的某种索引，可能会对一些特定的查询有优化！！
//todo
​

对于SELECT语句，在解析查询之前，服务器会将检查查询缓存（Query Cache），如果能够在其中找到对应的查询， 服务器就不必再执行查询解析，优化和执行的过程。而是直接返回缓存中的结果集！
​

​

3.SQL语句执行流程与SQL关键字的执行顺序

SQL语句执行流程：
也就是SQL语句从客户端发出，到服务端执行，并拿取到执行结果的一系列流程发生了什么？

​

SQL关键字的执行顺序：

4.优化点总结

1. 查询缓存可以避免，我们的SQL语句到分析器—>优化器—>存储引擎,直接从查询缓存中，返回我们需要的数据。
   1. 查询缓存是默认开启的，我们可以通过具体的配置来优化我们的查询缓存
   2. 比如：查询缓存的大小，类型，能过缓存的最大结果等等（优化项自行百度）
2. 每个客户端连接都会在服务器进程中有一个线程，这个连接的查询都在一个单独的线程中执行，创建和销毁线程都需要浪费对应的资源，所以服务器缓存了线程，因此不需要为每个client连接单独创建和销毁线程 ，当客户端连接数量过多时可以适当thread_cache_size以及最大连接数等等！

二.并发控制

无论何时，只要有多个查询需要在同一时刻修改数据们都会产生并发控制问题。本章的目的是讨论MySQL在两个层面的并发控制：服务器层与存储引擎层！
​

锁是计算机协调多个进程或线程并发访问某一资源的机制。锁保证数据并发访问的一致性、有效性；锁冲突也是影响数据库并发访问性能的一个重要因素
​

加锁是消耗资源的，锁的各种操作，包括获得锁、检测锁是否是否已解除、释放锁等。
​

1.读写锁

定义：
共享锁（shard lock）和排它锁（exclusive lock），也叫读锁（read lock）和写锁（write lock）

读锁：

是共享的，或者说相互不阻塞的。多个客户在同一时刻可以同事读取同一资源，而不相互干扰。加了读锁之后只能共享读，不能写
​

​

读锁举个列子：
目前有2个连接，操作同一数据库的同一张表
执行顺序如下：
1.A连接加读锁
2.A连接查询（正常不受影响）
3.A连接进行插入（报错 Table ‘user’ was locked with a READ lock and can’t be updated（user表被上了读锁无法修改））
4.B连接查询（正常不受影响）
5.B连接进行插入(不报错被阻塞)
​

#--------------------------连接A-----------------------------------------
#给user表加上读锁 
LOCK TABLE user READ;
#读操作 
select * from user where id=1
#写操作
INSERT INTO `user`(`name`, `pwd`, `power`) VALUES ( 'xx', '123', '1');

#--------------------------连接B-----------------------------------------
#读操作 
select * from user where id=1
#写操作
INSERT INTO `user`(`name`, `pwd`, `power`) VALUES ( 'xx', '123', '1');    
    
#解锁 
unlock table

​

写锁：

是排他的，也就是说一个写锁会阻塞其他的写锁和读锁，这是处于安全策略的考虑，只有这样，才能保证同一时间内，只有一个用户能执行写入，并防止其他用户读取正在写入的同一资源。
​

​

写锁举个列子：
目前有2个连接，操作同一数据库的同一张表
执行顺序如下：
1.A连接进行加写锁
2.A连接进行插入（正常不受影响）
3.A连接查询（被阻塞）
4.B连接查询（被阻塞）
5.B连接进行插入(被阻塞)

#--------------------------连接A-----------------------------------------
#给user表加上写锁 
LOCK TABLE user write;
#写操作
INSERT INTO `user`(`name`, `pwd`, `power`) VALUES ( 'xx', '123', '1');
#读操作 
select * from user where id=1


#--------------------------连接B-----------------------------------------
#读操作 
select * from user where id=1
#写操作
INSERT INTO `user`(`name`, `pwd`, `power`) VALUES ( 'xx', '123', '1');

#解锁 
unlock table

​

2.锁粒度

一种提高共享资源并发性的方式就是让锁定的对象更有选择性。尽量只锁定需要修改的部分数据，而不是所有资源。
​

更理想的方式是，只会对修改的数据片进行精准的锁定。任何时候，在给定的资源上，锁定的数据量越少，系统的并发程度越高，只要相互之间不发生冲突即可。
​

问题是加锁也需要消耗资源。锁的各种操作，包括获取锁，检查锁是否已经解决，释放锁等，都会增加系统的开销。如果系统话费大量的时间来管理锁，而不是存取数据，那么系统的性能可能因为此受到影响。
​

所谓的锁策略，就是在锁的开销和数据的安全性之间寻求平衡，这种平衡当然也会影响到性能。
​

每种MySQL存储引擎都可以实现自己的锁策略和锁粒度。在存储引擎的设计中，锁管理是个非常重要的决定。将锁粒度固定在某个级别，可以为某些特定的应用场景提供更好的性能，但同时却会失去对另外一些应用场景的良好支持。好在MySQL支持多个存储引擎架构，所以不需要单一的通用解决方案。

表锁

表锁是MySQL中最基本的锁策略，也是开销最小的锁策略。它会锁定整张表。一个用户在对表进行写操作（插入，删除，更新等）需要先获得写锁，这会阻塞其他用户对该表的所有读写操作。只有没有写锁时，其他读取的用户才能获得读锁，读锁之间是不相互阻塞的！！！
​

​

产生表锁的情况：

1. 手动加锁如LOCK TABLE user write;（为表加上读锁）
2. 在使用where条件时进行增删改时（查询除外），如果where条件后面的字段没有索引会导致表锁

如下案列：
目前有2个连接，操作同一数据库的同一张表
执行顺序如下：
1.A连接开启事务
2.A连接进行修改操作（name字段没有索引，产生表锁）
3.A连接修改完成
4.B连接开启事务
5.B连接进行修改操作（name字段没有索引，产生表锁）（被A连接产生的表锁阻塞）
6.A连接提交事务事务
7.B连接修改完成
8.B连接提交事务

#--------------------------连接A-----------------------------------------
#开启一个事务
START TRANSACTION; 
#条件修改 (name字段没有索引，产生表锁)
update user set name ='x_x' where name='xx'
#提交事务
commit;    

#--------------------------连接B-----------------------------------------
#开启一个事务
START TRANSACTION; 
#条件修改(name字段没有索引，产生表锁)
update user set name ='承承' where name='承承xx'
#提交事务
commit;    
    

3.服务器会为诸如：alter table之类的语句使用表锁，而忽略存储引擎的锁机制！
​

行级锁

行级锁可以最大程度的支持并发处理（同样也带来了最大的锁开销）。众所周知，在InnoDB和XtraDB，以及其他的一些存储引擎中实现了行级锁。行级锁只在存储引擎层实现，而MySQL服务层没有实现。服务层完全不了解存储引擎中的锁实现，所有的存储引擎都可以自己的方式实现锁机制。




产生行级锁的情况：

1. 对数据库进行增删改时，筛选条件上是索引字段（隐式加锁），如：delete from user where id=1这里的id字段（假如是主键）那么我们没显示的创建索引，也会有默认的主键索引
2. 编写SQL时加上for update语句（显示加锁），列如select * from user where id =1 for update

间隙锁

innoDB的锁定规则是通过在指向数据记录的第一个索引键之前和最后一个索引键之后的空域空间上标记锁定信息而实现的。 Innodb的这种锁定实现方式被称为“ NEXT-KEY locking” （间隙锁），因为Query执行过程中通过范围查找的话，它会锁定整个范围内所有的索引键值，即使这个键值并不存在。
​

​

举个列子：
目前有2个连接，操作同一数据库的同一张表
执行顺序如下：
1.A连接开启事务
2.A连接进行范围查询（ id字段有索引 ，行锁变间隙锁， 所有id大于1的数据都会被锁住 ）
3.A连接查询完成
4.B连接开启事务
5.B连接进行插入操作（由于id是自动生成的，被A连接产生的间隙锁阻塞）
6.A连接提交事务
7.B连接查询完成
8.B连接提交事务

​

所以我们在使用范围查找的时候导致的间隙锁，尽量避免以上这种情况，会导致大批量的数据被锁！和表锁的并发粒度不会有多大的区别，尽可能的减少间隙锁的范围！！
​

3.优化总结

1. 如果一定要使用表锁，在业务场景合适的情况下加读锁比写锁更合适，因为读锁不影响其他连接的读取，只影响增删改操作，但写锁会阻塞所有其他连接的所有操作，以及自身的读操作！
2. 行锁的优先级>间歇锁的优先级>表锁的优先级 建议使用行锁 或 间歇锁 来避免表锁导致大量查询被阻塞!
3. 尽量避免索引失效，导致行锁变表锁
4. 避免间隙锁的大范围取值，和表锁无差别，比如：ID为自增 where id>1 也相当于锁住全表
5. 尽量能避免锁 的使用就避免 ，乐观锁的优先级大于悲观锁

​

​

三.事务

1.基本概念

在理解事务的概念之前，接触数据库系统的其他高级特性还言之过早。**事务就是一组原子性的SQL查询，或者说一个独立的工作单元。**如果数据库引擎能够成功地对数据库应用该组查询的全部语句，那么就执行该组查询。如果其中有任何一条语句因为崩溃或其他原因无法执行，那么所有的语句都不会执行。
也就是说，事务内的语句，要么全部执行成功，要么全部执行失败。
​

​

2.举个需要事务的经典列子：

假设一个银行的数据库有两张表：支票（checking）和储蓄（savings），现在用户A的支票账户转移了200美元到他的储蓄账户，那么至少经历一下3个步骤：

1. 检查支票账户的余额高于200美元
2. 从支票账户余额中减去200美元
3. 在储蓄账户余额中增加200美元

​

上述的三个步骤的操作必须打包在一个事务中，任何一个步骤失败，则必须回滚所有的步骤。
可以使用start transaction语句开始一个事务，然后要么使用commit提交事务将修改的数据持久保留，要么使用rollback撤销所有的修改。

start transaction;
select balance from checking where customer_id=6666;
update cheking set balance =balance-200.00 where customer_id=6666;
update savings set balance =balance+200.00 where customer_id=6666;
commit;

在这任何一个步骤出错！！！我们都要保证数据的原子性，否则就会导致我们的数据不一致！

3.事务ACID原则

这里就需要带一下ACID原则：

1.原子性（Atomicity）

是指一个事务要么全部执行，要么完全不执行。
​

2.一致性(Consistency)

事务在开始和结束时，应该始终满足一致性约束。比如系统要求A+B=100,那么事务如果改变了A的数值，则B的数值也要相应修改来满足这样一致性要求；与CAP中的C代表的含义是不同的。
​

3.事务独立（Isolation)

如果有多个事务同时执行，彼此之间不需要知晓对方的存在，而且执行时互不影响，事务之间需要序列化执行，有时间顺序。
​

4.持久性(Durability)

事务的持久性是指事务运行成功以后，对系统状态的更新是永久的，不会无缘无故回滚撤销。
​

事务的ACID特性可以确保银行不会弄丢你的钱。而且在应用逻辑中，要实现这一点非常困难，甚至可以说是不可能完成的任务。一个兼容ACID的数据库系统，需要做很多复杂但可能用户却没有觉察到的工作，才能保证ACID原则！
​

就像锁粒度的升级会增加系统开销，这种事务处理过程中额外的安全性，也会需要数据库系统做更多的额外工作。一个实现ACID的数据库，相比没有实现ACID的数据库，通常需要更强的CPU处理能力，更大的内存和更多的磁盘空间。正如本章不断重复的，这也是MySQL的存储引擎架构可以发挥优势的地方。用户可以根据自己的业务需求，来决定是否需要事务支持，来选择合适的存储引擎！！！可以获得更高的性能。
​

如：
MyIASM就不支持事务！
​

​

​

4.事务隔离级别

隔离性其实比想象的要复杂。在SQL标准中定义了四种隔离级别,每一种级别都规定了个事务中所做的修改,哪些在事务内和事务间是可见的,哪些是不可见的。较低级别的隔离通常可以执行更高的并发,系统的开销也更低。
​

1.READ UNCOMMITTED(未提交读)

在 READ UNCOMMITTED级别,事务中的修改,即使没有提交,对其他事务也都是可见的。事务可以读取未提交的数据,这也被称为脏读( Dirty Read)。这个级别会导致很多问题,从性能上来说, READ UNC0 MMITTED不会比其他的级别好太多,但却缺乏其他级别的很多好处,除非真的有非常必要的理由,在实际应用中一般很少使用。
​

2.READ COMMITTED(提交读）

大多数数据库系统的默认隔离级别都是 READ C0MMITTED(但 MySQL不是)。READ C0MMITTED满足前面提到的隔离性的简单定义:一个事务开始时,只能“看见”已交的事务所做的修改。换句话说,一个事务从开始直到提交之前,所做的任何修改对其他事务都是不可见的。这个级别有时候也叫不可重复读( nonrepeatable read）因为两次执行同样的查询,可能会得到不一样的结果。
​

3.REPEATABLE READ(可重复读)

REPEATABLE READ解决了脏读的问题。该级别保证了在同一个事务中多次读取同样记录的结果是一致的。但是理论上,可重复读隔离级别还是无法解决另外一个幻读(phantom Read)的问题。所谓幻读,指的是当某个事务在读取某个范围内的记录时,另外一个事务又在该范围内插入了新的记录,当之前的事务再次读取该范围的记录时,会产生幻行( Phantom row)。 InnoDB和 XtraDB存储引擎通过多版本并发控制(MvCC, Multiversion Concurrency Control)解决了幻读的问题。本章稍后会做进一步的讨论。
可重复读是 MySQL的默认事务隔离级别
​

4.SERIALIZABLE(可串行化)

SERIALIZABLE是最高的隔离级别。它通过强制事务串行执行,避免了前面说的幻读的问题。简单来说, SERIALIZABLE会在读取的每一行数据上都加锁,所以可能导致大量的超时和锁争用的问题。实际应用中也很少用到这个隔离级别,只有在非常需要确保数据的一致性而且可以接受没有并发的情况下,才考虑采用该级别。
​

隔离级别	脏读	不可重复读	幻读	加锁读
READ UNCOMMITTED	✔	✔	✔	×
READ COMMITTED	×	✔	✔	×
REPEATABLE READ	×	×	✔	×
SERIALIZABLE	×	×	×	✔

5.事务日志

1.基本描述：

事务日志可以帮助提高事务的效率。使用事务日志,存储引擎在修改表的数据时只需要修改其内存拷贝,再把该修改行为记录到持久在硬盘上的事务日志中,而不用每次都将修改的数据本身持久到磁盘。事务日志采用的是追加的方式,因此写日志的操作是磁盘上一小块区域内的顺序IO,而不像随机IO需要在磁盘的多个地方移动磁头,所以采用事务日志的方式相对来说要快得多。事务日志持久以后,内存中被修改的数据在后台可以慢慢地刷回到磁盘。目前大多数存储引擎都是这样实现的,我们通常称之为预写式日志( Write-Ahead Logging),修改数据需要写两次磁盘。如果数据的修改已经记录到事务日志并持久化,但数据本身还没有写回磁盘,此时系统崩溃,存储引擎在重启时能够自动恢复这部分修改的数据。具体的恢复方式则视存储引擎而定。
​

**MySQL事务日志分为2种 **

1. 重做日志（redo log）
2. 回滚日志（undo log）

​

2.重做日志（redo log）

重做日志（redo log）是InnoDB引擎层的日志，用来记录事务操作引起数据的变化，记录的是数据页的物理修改。
​

重做日记的作用其实很好理解，我打个比方。数据库中数据的修改就好比你写的论文，万一哪天论文丢了怎么呢？以防这种不幸的发生，我们可以在写论文的时候，每一次修改都拿个小本本记录一下，记录什么时间对某一页进行了怎么样的修改。这就是重做日志。


InnoDB引擎对数据的更新，是先将更新记录写入redo log日志，然后会在系统空闲的时候或者是按照设定的更新策略再将日志中的内容更新到磁盘之中。这就是所谓的预写式技术（Write Ahead logging）。这种技术可以大大减少IO操作的频率，提升数据刷新的效率。
​

​

3.回滚日志（undo log）

回滚日志同样也是InnoDB引擎提供的日志，顾名思义，回滚日志的作用就是对数据进行回滚。当事务对数据库进行修改，InnoDB引擎不仅会记录redo log，还会生成对应的undo log日志；如果事务执行失败或调用了rollback，导致事务需要回滚，就可以利用undo log中的信息将数据回滚到修改之前的样子。
​

但是undo log不redo log不一样，它属于逻辑日志。它对SQL语句执行相关的信息进行记录。当发生回滚时，InnoDB引擎会根据undo log日志中的记录做与之前相反的工作。比如对于每个数据插入操作（insert），回滚时会执行数据删除操作（delete）；对于每个数据删除操作（delete），回滚时会执行数据插入操作（insert）；对于每个数据更新操作（update），回滚时会执行一个相反的数据更新操作（update），把数据改回去。undo log由两个作用，一是提供回滚，二是实现MVCC


​

4.二进制日志（binlog）（不是事务日志哦，注意！！！）

二进制日志binlog是服务层的日志，还被称为归档日志。binlog主要记录数据库的变化情况，内容包括数据库所有的更新操作。所有涉及数据变动的操作，都要记录进二进制日志中。因此有了binlog可以很方便的对数据进行复制和备份，因而也常用作主从库的同步。
​

这里binlog所存储的内容看起来似乎与redo log很相似，但是其实不然。redo log是一种物理日志，记录的是实际上对某个数据进行了怎么样的修改；而binlog是逻辑日志，记录的是SQL语句的原始逻辑，比如”给ID=2这一行的a字段加1 "。binlog日志中的内容是二进制的，根据日记格式参数的不同，可能基于SQL语句、基于数据本身或者二者的混合。一般常用记录的都是SQL语句。
​

**更多详细的日志内容，见次blog，篇幅太长 **
https://blog.csdn.net/u014398524/article/details/106505549/
​

常见面试题：
讲一下binlog的日志文件格式?（鹅厂，蔚来汽车都被问到过）
https://dev.mysql.com/doc/internals/en/binary-log-overview.html(可以参照Mysql官网)
​

mysql的binlog日志作用是用来记录mysql内部增删改等对mysql数据库有更新内容的记录（对数据库进行改动的操作），对数据库查询的语句如show，select开头的语句，不会被binlog日志记录，最大的作用是用来数据增量恢复和主从库复制
​

ROW
ROW格式会记录每行记录修改的记录，这样可能会产生大量的日志内容,比如一条update语句修改了100条记录，那么这100条记录的修改都会被记录在binlog日志中，这样造成binlog日志量会很大，这种日志格式会占用大量的系统资源，mysql5.7和myslq8.0安装后默认就是这种格式。
​

STATEMENT
记录每一条修改数据的SQL语句（批量修改时，记录的不是单条SQL语句，而是批量修改的SQL语句事件）所以大大减少了binlog日志量，节约磁盘IO，提高性能,看上面的图解可以很好的理解row和statement 两种模式的区别。但是STATEMENT对一些特殊功能的复制效果不是很好，比如：函数、存储过程的复制。由于row是基于每一行的变化来记录的，所以不会出现类似问题
​

MIXED
实际上就是前两种模式的结合。在Mixed模式下，MySQL会根据执行的每一条具体的sql语句来区分对待记录的日志形式，也就是在Statement和Row之间选择一种。
相对较老的版本中默认使用的格式可能是STATEMENT，但是我装了5.7和mysql8.0的版本，默认的格式都是ROW，即便如此，还是比较推荐使用STATEMENT或MIXED
​

#1.查看当前日志格式
mysql> mysql> show variables like '%binlog_format%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |
+---------------+-------+
1 row in set (0.00 sec)


#2.查看binlog详细相关参数
mysql> show variables like '%binlog%';
+-----------------------------------------+----------------------+
| Variable_name                           | Value                |
+-----------------------------------------+----------------------+
| binlog_cache_size                       | 32768                |
| binlog_checksum                         | CRC32                |
| binlog_direct_non_transactional_updates | OFF                  |
| binlog_error_action                     | ABORT_SERVER         |
| binlog_format                           | ROW                  |
| binlog_group_commit_sync_delay          | 0                    |
| binlog_group_commit_sync_no_delay_count | 0                    |
| binlog_gtid_simple_recovery             | ON                   |
| binlog_max_flush_queue_time             | 0                    |
| binlog_order_commits                    | ON                   |
| binlog_row_image                        | FULL                 |
| binlog_rows_query_log_events            | OFF                  |
| binlog_stmt_cache_size                  | 32768                |
| innodb_api_enable_binlog                | OFF                  |
| innodb_locks_unsafe_for_binlog          | OFF                  |
| log_statements_unsafe_for_binlog        | ON                   |
| max_binlog_cache_size                   | 18446744073709547520 |
| max_binlog_size                         | 1073741824           |
| max_binlog_stmt_cache_size              | 18446744073709547520 |
| sync_binlog                             | 1                    |
+-----------------------------------------+----------------------+
20 rows in set (0.00 sec)

3.修改binlog日志格式,修改my.cnf

​

binlog中几个重要的参数设置
binlog_cache_size：在事务过程中容纳二进制日志SQL语句的缓存大小。二进制日志缓存是服务器支持事务存储引擎并且服务器启用了二进制日志(—log-bin选项)的前提下为每个客户端分配的内存，注意，是每个Client都可以分配设置大小的binlogcache空间。如果系统中经常会出现多语句事务的话，可以尝试增加该值的大小，以获得更好的性能。可以通过MySQL的以下两个状态变量来判断当前的binlog_cache_size的状况：Binlog_cache_use和Binlog_cache_disk_use。
​

max_binlog_cache_size：和”binlog_cache_size”相对应，但是所代表的是binlog能够使用的最大cache内存大小。当我们执行多语句事务的时候，max_binlog_cache_size如果不够大的话，系统可能会报出“Multistatementtransactionrequiredmorethan’max_binlog_cache_size’bytesofstorage”的错误。
​

max_binlog_size：Binlog日志最大值，一般来说设置为512M或者1G，但不能超过1G。该大小并不能非常严格控制Binlog大小，尤其是当到达Binlog比较靠近尾部而又遇到一个较大事务的时候，系统为了保证事务的完整性，不可能做切换日志的动作，只能将该事务的所有SQL都记录进入当前日志，直到该事务结束。这一点和Oracle的Redo日志有点不一样，因为Oracle的Redo日志所记录的是数据文件的物理位置的变化，而且里面同时记录了Redo和Undo相关的信息，所以同一个事务是否在一个日志中对Oracle来说并不关键。而MySQL在Binlog中所记录的是数据库逻辑变化信息，MySQL称之为Event，实际上就是带来数据库变化的DML之类的Query语句。
​

sync_binlog：这个参数是对于MySQL系统来说是至关重要的，他不仅影响到Binlog对MySQL所带来的性能损耗，而且还影响到MySQL中数据的完整性。对于“sync_binlog”参数的各种设置的说明如下：
​

sync_binlog=0，当事务提交之后，MySQL不做fsync之类的磁盘同步指令刷新binlog_cache中的信息到磁盘，而让Filesystem自行决定什么时候来做同步，或者cache满了之后才同步到磁盘。
​

sync_binlog=n，当每进行n次事务提交之后，MySQL将进行一次fsync之类的磁盘同步指令来将binlog_cache中的数据强制写入磁盘。
在MySQL中系统默认的设置是sync_binlog=0，也就是不做任何强制性的磁盘刷新指令，这时候的性能是最好的，但是风险也是最大的。因为一旦系统Crash，在binlog_cache中的所有binlog信息都会被丢失。而当设置为“1”的时候，是最安全但是性能损耗最大的设置。因为当设置为1的时候，即使系统Crash，也最多丢失binlog_cache中未完成的一个事务，对实际数据没有任何实质性影响。从以往经验和相关测试来看，对于高并发事务的系统来说，“sync_binlog”设置为0和设置为1的系统写入性能差距可能高达5倍甚至更多
​

6.MySQL中的事务

MySQL提供了两种事务型的存储引擎: InnodB和 NDB Cluster。另外还有一些第三方存储引擎也支持事务,比较知名的包括 XtraDB和PBXT。
​

1.事务的自动提交( AUTOCOMMIT)

MySQL默认采用自动提交(AUTOCOMMIT)模式。也就是说,如果不是显式地开始个事务,则毎个査询都被当作一个事务执行提交操作。在当前连接中,可以通过设置AUTOCOMMIT变量来启用或者禁用自动提交模式

mysql> show variables like '%autocommit%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
1 row in set, 1 warning (0.00 sec)

1或者0N表示启用,0或者0FF表示禁用。
当AUTOCOMMIT=0时,所有的查询都是在一个事务中,直到显式地执行 COMMIT提交或者 ROLLBACK回滚,该事务结束,同时又开始了另一个新事务。
​

修改AUTOCOMMIT对非事务型的表,比如 MyISAM或者内存表,不会有任何影响。
对这类表来说,没有commit或者 ROLLBACK的概念,也可以说是相当于一直处于AUTOCOMMIT启用的模式。
​

另外还有一些命令,在执行之前会强制执行C0MMIT提交当前的活动事务。
典型的例子,在数据定义语言(DDL)中,如果是会导致大量数据改变的操作,比如 ALTER TABLE,就是如此。另外还有LOCK TABLES等其他语句也会导致同样的结果。如果有需要,请检查对应版本的官方文档来确认所有可能导致自动提交的语句列表。
​

2.在事务中混合使用存储引擎

MySQL服务器层不管理事务,事务是由下层的存储引擎实现的。所以在同一个事务中,使用多种存储引擎是不可靠的。
如果在事务中混合使用了事务型和非事务型的表(例如 Innode和 MyISAM表)
在正常提交的情况下不会有什么问题。
但如果该事务需要回滚,非事务型的表上的变更就无法撤销,这会导致数据库处于不致的状态,这种情况很难修复,事务的最终结果将无法确定。
所以,为每张表选择合适的存储引擎非常重要。
在非事务型的表上执行事务相关操作的时候, My SQL通常不会发出提醒,也不会报错。
有时候只有回滚的时候才会发出一个警告:“某些非事务型的表上的变更不能被回滚”。
但大多数情况下,对非事务型表的操作都不会有提示。
​

3.隐式和显式锁定

InnodB采用的是两阶段锁定协议(two- phase locking protocol)。在事务执行过程中,随时都可以执行锁定,锁只有在执行C0MMT或者R0LLBACK的时候才会释放,并且所有的锁是在同一时刻被释放。前面描述的锁定都是隐式锁定, InnodB会根据隔离级别在需要的时候自动加锁。
​

另外, InnoDB也支持通过特定的语句进行显式锁定,这些语句不属于SQL

1. SELECT…LOCK IN SHARE MODE
2. SELECT… FOR UPDATE

MySQL也支持LOCK TABLES和UNLOCK TABlES语句,这是在服务器层实现的,和存储引擎无关。它们有自己的用途,但并不能替代事务处理。如果应用需要用到事务,还是应该选择事务型存储引擎。经常可以发现,应用已经将表从 MyISAM转换到 InnodB,但还是显式地使用LOCK TABLES语句。这不但没有必要,还会严重影响性能,实际上 Innode的行级锁工作得更好
​

四.MVCC多版本并发控制

​

MVCC，全称Multi-Version Concurrency Control，即多版本并发控制。MVCC是一种并发控制的方法，一般在数据库管理系统中，实现对数据库的并发访问，在编程语言中实现事务内存。
​

多版本控制:
指的是一种提高并发的技术。最早的数据库系统，只有读读之间可以并发
读写，写读，写写都要阻塞。
引入多版本之后，只有写写之间相互阻塞，其他三种操作都可以并行，这样大幅度提高了InnoDB的并发度。在内部实现中，与Postgres在数据行上实现多版本不同，InnoDB是在undolog中实现的，通过undolog可以找回数据的历史版本。找回的数据历史版本可以提供给用户读(按照隔离级别的定义，有些读请求只能看到比较老的数据版本)，也可以在回滚的时候覆盖数据页上的数据。在InnoDB内部中，会记录一个全局的活跃读写事务数组，其主要用来判断事务的可见性。
MVCC是一种多版本并发控制机制。
​

在这之前 我们先了解一下 当前读和快照读

1.什么是当前读和快照读？

在学习MVCC多版本并发控制之前，我们必须先了解一下，什么是MySQL InnoDB下的当前读和快照读?

• 当前读
  像select lock in share mode(共享锁), select for update ; update, insert ,delete(排他锁)这些操作都是一种当前读，为什么叫当前读？就是它读取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁
• 快照读
  像不加锁的select操作就是快照读，即不加锁的非阻塞读；快照读的前提是隔离级别不是串行级别，串行级别下的快照读会退化成当前读；之所以出现快照读的情况，是基于提高并发性能的考虑，快照读的实现是基于多版本并发控制，即MVCC,可以认为MVCC是行锁的一个变种，但它在很多情况下，避免了加锁操作，降低了开销；既然是基于多版本，即快照读可能读到的并不一定是数据的最新版本，而有可能是之前的历史版本

说白了MVCC就是为了实现读-写冲突不加锁，而这个读指的就是快照读, 而非当前读，当前读实际上是一种加锁的操作，是悲观锁的实现
​

2.事务和undo log日志怎么配合的

一·如一个有个事务插入persion表插入了一条新记录，记录如下，name为Jerry, age为24岁，隐式主键是1，事务ID和回滚指针，我们假设为NULL



二·现在来了一个事务1对该记录的name做出了修改，改为Tom

• 在事务1修改该行(记录)数据时，数据库会先对该行加排他锁
• 然后把该行数据拷贝到undo log中，作为旧记录，既在undo log中有当前行的拷贝副本
• 拷贝完毕后，修改该行name为Tom，并且修改隐藏字段的事务ID为当前事务1的ID, 我们默认从1开始，之后递增，回滚指针指向拷贝到undo log的副本记录，既表示我的上一个版本就是它
• 事务提交后，释放锁

三、 又来了个事务2修改person表的同一个记录，将age修改为30岁

• 在事务2修改该行数据时，数据库也先为该行加锁
• 然后把该行数据拷贝到undo log中，作为旧记录，发现该行记录已经有undo log了，那么最新的旧数据作为链表的表头，插在该行记录的undo log最前面
• 修改该行age为30岁，并且修改隐藏字段的事务ID为当前事务2的ID, 那就是2，回滚指针指向刚刚拷贝到undo log的副本记录
• 事务提交，释放锁

上面，我们就可以看出，不同事务或者相同事务的对同一记录的修改，会导致该记录的undo log成为一条记录版本线性表，既链表，undo log的链首就是最新的旧记录，链尾就是最早的旧记录（当然就像之前说的该undo log的节点可能是会purge线程清除掉，向图中的第一条insert undo log，其实在事务提交之后可能就被删除丢失了，不过这里为了演示，所以还放在这里）
​

​

3.怎么理解？

这是笔者的理解
当我们同时有A，B二个事务在操作同一条数据的时候，可能都发生了修改和 查询的操作
大家都知道修改操作会导致数据加锁
导致其他事务的查询或修改操作被阻塞，为了提高性能，我们引入了MVCC
为了保证读写不被加锁，不阻塞冲突
通过事务的隔离级别，如果事务的隔离级别为读已提交以上
A事务在查询，B事务在修改，他们两个都在同时操作同一条数据
在没有添加MVCC之前B事务肯定会阻塞A事务的查询（假如B事务先获取到锁）
但现在 由于存在MVCC，A事务应该读到的是，B事务没有修改数据之前的样子
通过undo.log找到这条记录小于或等于A事务的事务ID的数据，并返回，
而不是等待B事务，提交事务之后再进行读取导致被阻塞，性能下降
避免了锁的冲突
​

​

更加详细的版本见下链接：
https://www.cnblogs.com/xuwc/p/13873611.html
​

​

五.存储引擎todo