mysql笔记（锁、事务、性能优化、压测结果）

Mysql

该笔记，主要根据kkb课程并结合网上资料和自己的理解而形成。

一、Mysql架构

1.1 逻辑架构

这是从网上copy过来的图。网上一些教程会把mysql也进行分层。

1. 连接层：Connectors(连接器)，一看图中就看到了我们Java程序员最熟悉的JDBC，再结合ConnectionPool 用于连接我们的Mysql服务器。

   注意：系统管理和控制工具（图中：Enterprise Management Services& Utilities）也属于该层，运维人员所关心的，一看名字就知道是什么作用了。

2. 服务层（也称SQL Layer MySql业务层）： SQL接口（SQL Interface),用于接收SQL；Parser(解析器)，将sql语句进行词法分析，语法分析，形成正确的语法树；Optimizer(查询优化器)，mysql觉得你写的SQL不是完美的，比如 select * from user where id =1 and sex='男' mysql执行这条语句的时候，其实是从右往左执行的。在用户中找男的过滤力度没有直接找id大，mysql会进行更改过滤条件顺序，实际执行where sex ='男'and id =1。 Caches & Buffer(查询缓存)，myql会把查询语句进行hash为key,结果为value。如果sql有变化，就清除缓存。这个Mysql8就不再使用了。

3. 存储引擎层：由图中Pluggable Storage Engines组成。存储引擎以表为单位,比如我们平时创建表，如果不指定engines默认就为InnoDB

4. 存储层：就是真正存放数据的位置了。

1.1.1 Mysql执行流程

既然知道了mysql的架构，我们试着想一下一条sql语句会怎么执行呢？

​ 看完这个图是不是小小的脑袋大大的疑惑呢？不急，我们先要记住，一条sql命令会从客户端 通过连接器发往mysql服务器，mysql会先进行语法/词法分析，如果是mysql5会将sql语句进行hash然后去查询缓存，如果有结果返回结果，如果没有，进行优化执行。存储引擎进行具体的操作。

1.2 物理架构

​ 我们都说mysql是数据库，可是数据到底存放在哪里呢？mysql部署在linux服务器上，而真正的数据存放在/var/lib/mysql 这个目录下。这个目录下我们可以看到一些包含log名称的日志文件，也可以看到一个包含我们数据库名的文件夹。

1.2.1 日志文件

​ 为了加快日志的记录速度，日志文件采用了顺序IO方式存储，也就是不断的追加写入，在物理上日志文件在磁盘上是连续的，优点是只需要记住首地址和偏移量，缺点是浪费空间。

• 错误日志（errorlog）:默认开启，mysql5.5.7之后无法关闭错误日志，记录了运行中遇到的所有错误信息，以及Mysql每次启动或关闭的详细信息。
• 二进制日志（bin log）: 主要是记录数据变化。binlog记录了数据库所有的DDL（数据定义语句，比如创建creat、Alter、Drop等语句）和DML（数据操作语句，增删改语句），不包含select语句。语句以事件的形式保存，描述了数据的变更顺序，binlog还包括了每个更新语句的执行事件信息。如果是DDL语句，则直接记录到binlog日志，则DML语句，必须通过事务提交才能记录到binlog日志中。生产中开启，用于数据备份、恢复、主从。
• 通用查询日志（general query log）: 啥都记录，耗费性能，生产中不开启
• 慢查询日志（slow query log） SQL调优，定位慢的select。默认是关闭的。通过以下设置进行开启

#开启慢查询日志
slow_query_log=ON
#慢查询的阈值
long_query_time=3
#日志记录文件如果没有给出file_name值， 默认为主机名，后缀为-slow.log。如果给出了文件名，但不
是绝对路径名，文件则写入数据目录。
slow_query_log_file=file_name

• 重做日志（redo log）：顺序读写，新数据的备份
• 回滚日志（undo log）：随机读写，旧数据的备份
• 中继日志（relay log）

1.2.2 数据文件

查看数据文件存放目录

SHOW VARIABLES LIKE '%datadir%';

在我的mysql8上，我创建了两个表一个是goods表和user表，goods采用了myisam存储引擎，user采用了innodb存储引擎。由上图我们可以看出mysql8为不同的引擎，创建了不同的文件。

• InnoDB: mysql8只创建了一个*.ibd,文件，包含了表的元数据与数据还有索引信息。
• MyIsam：.myd 用来存储数据信息；.myi用来存储数据文件中任何索引的数据树。sdi用来存储表的元数据。

由上图我们可以看出InnoDB存储引擎和MyIsam存储引擎在文件存储就有一些不一样，那么他们还在那里有一些不一样呢?那么还有哪些不一样呢？

二、Mysql存储引擎

再创建表时，会给每张表指定一个存储引擎。如果没指定，默认是innodb。

2.1 存储引擎选型

存储引擎	说明
MyISAM	高速引擎，拥有较高的插入，查询速度，但不支持事务。不支持行锁、支持2种不同的存储格式。包括静态型、动态型和压缩型。
InnoDB	5.5版本后Mysql的默认数据库，支持事务和行级锁定，事务处理、回滚、崩溃修复能力和多版本并发控制的事务安全，比MyISAM处理速度稍慢、支持外键（FOREIGN KEY）
ISAM	MyISAM的前身，MySQL5之后不再默认安装
MERGE	将多个表联合成一个表使用，在超大规模数据存储时很有用
Memory	内存存储引擎，拥有极高的插入，查询和查询效率。但是会占用和数据量成正比的内存空间。只在内存保存数据，意味着数据可能会丢失。
Falcon	一种新的存储引擎，支持事务处理，传言可能是Inno DB的替代者
Archive	将数据压缩后进行存储，非常适合大量的独立的，作为历史记录的数据，但是只能进行插入和查询操作
CSV	CSV 存储引擎是基于 CSV 格式文件存储数据(应用于跨平台的数据 交换)

xtraDB存储引擎是由Percona公司提供的存储引擎，该公司还出品了Percona Server这个产品，它是基 于MySQL开源代码进行修改之后的产品。 阿里对于Percona Server服务器进行修改，衍生了自己的数据库（alisql）。

2.2 InnoDB和MyISAM存储引擎区别：

存储引擎	InnoDB	Myisam
存储文件（mysql8）	.myd	myd myi sdi
锁	表锁、行锁	表锁
事务	支持	不支持
CRDU	读、写	读多
count	扫表	专门存储的地方
索引结构	B+Tree
外键	支持	不支持

三、索引

3.1 索引介绍

3.1.1 索引是什么

索引是帮助Mysql高效获取数据的数据结构。更通俗的说，数据库索引好比是一本书前面的目录，能加快数据库的查询速度。

3.1.2 索引的优势和劣势

优势：

• 可以提高数据检索的效率，降低数据库的IO成本，类似于书的目录
• 通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗

劣势:

• 索引会占磁盘空间
• 会降低更新表的效率。对比增删改，索引结构也需要变化。

3.2 索引的分类

• 单列索引
• 组合索引
• 全文索引
• 空间索引
• 位图索引 Oracle

3.3 索引的使用

3.3.1 创建索引

• 单列索引之普通索引

CREATE INDEX index_name ON table(column(length)) ;
ALTER TABLE table_name ADD INDEX index_name (column(length)) ;

• 单列索引值唯一索引

CREATE UNIQUE INDEX index_name ON table(column(length)) ;
alter table table_name add unique index index_name(column);

• 全文索引

CREATE FULLTEXT INDEX index_name ON table(column(length)) ;
alter table table_name add fulltext index_name(column)

• 组合索引

ALTER TABLE article ADD INDEX index_titme_time (title(50),time(10)) ;

3.3.2 删除索引

DROP INDEX index_name ON table

3.3.3 查看索引

SHOW INDEX FROM table_name

3.4 索引原理分析

3.4.1 索引的存储结构

• 索引是在存储引擎中实现的，也就是说不通的存储引擎，会使用不同的索引。
• MyISAM和InnoDB存储引擎：只支持B+tree索引，也就是默认使用B+ TREE
• Memory/Heap 存储引擎：支持HASH和BTree索引

3.4.2 B树和B+树

数据结构示例网站： https://www.cs.usfca.edu/~galles/visualization/Algorithms.html

加深理解B+树：https://blog.csdn.net/qq_35571554/article/details/82759668

• B树的高度一般都是在2-4这个高度，树的高度直接影响IO读写的次数。
• 如果是三层树结构，支撑的数据可以达到20G，四层可以达到几十T。什么概念？我们公司有一张表10w条才几十m。
• B和B+树的区别：B+Tree只在叶子节点才会存储数据，并且存储的数据都在一行上，而且这些数据都是有指针指向的，有顺序。

3.5 非聚集索引（MyISAM)

之前我们查看InnoDB和myIsam存储引擎的区别的时候，可以发现。MyISAM的索引和数据不在同一个文件内存储。所以在MyIsam的B+树实现的索引中，叶子节点存放的是数据文件的指针值。

3.5.1 主键索引

3.5.2 次要索引

也就是非主键创建的索引，和主键索引相同。

3.6 聚集索引（InnoDB）

为啥叫聚集索引？B+树的叶子节点存放的不是数据的指针，而是数据本身。

3.6.1 主键索引

如果表中没有创建主键，innodb会自动生成伪列 当主键。主键最好用自增主键或者雪花算法，尽量不要用uuid。

3.6.2 辅助索引（次要索引）

叶子节点存的是主键值。比如select * from user where name= ‘alice’。 那么会根据辅助索引，去查找到name = alice。然后通过主键值，再去主键索引，去查找所有的字段。这个过程就称为“回表”。 那么怎么提高查询效率，防止回表呢？思路是“覆盖索引”，借助组合索引，比如说我们要查某个用户名和某个年龄的用户在不在，那么我们只需要对name和age加索引，select name,age where name = ‘zhangsan’ and age= 13 。 这样就不会产生回表查询。

3.7 索引的使用场景

3.7.1 那些情况需要产生索引

1. 主键自动建立唯一索引
2. 频繁作为查询的字段应该建立索引
3. 多表关联查询中，关联字段应该创建索引。on两边都要创建索引
4. 查询中排序的字段，应该创建索引。 因为B+Tree叶子节点是有序的
5. 覆盖索引，不需要回表
6. 统计和分组字段，应该创建索引

3.7.2 哪些情况不需要创建索引

1. 表记录太少，索引是要有存储的开销
2. 频繁更新，索引要维护
3. 查询字段使用频率不高

3.7.3 为什么使用组合索引

在一颗索引树上有多个字段

优点：效率高，省空间，容易形成覆盖索引

使用：

• 前缀索引：like采用常量% 如果是%常量，则索引失效
• 最左前缀：从左向右匹配直接遇到范围查询>< between索引失效。跟sql语句书写的顺序无关，比如索引顺序是（a,b,c），sql语句是select a,b,c from a=1 and c>1 and b =1,那么该索引会全部匹配上。遇到c这个范围，后面失效。

3.8 索引失效

3.8.1 索引失效场景

• 组合索引没有遵守最佳左前缀法则。面对组合索引，比如a,b,c建立索引，如果 b 条件是> ,那么c无法匹配
• 在索引列上做计算。 比如进行函数、自动/手动类型转换，会导致索引失效并进行全表扫描
• 在索引字段上使用 ！=、<、> null、or
• 索引字段是字符串没有加单引号
• 索引字段模糊查询以%开头

3.8.2 如何防止索引失效

口诀：

1. 全值匹配我最爱
2. 最佳左前缀法则
3. 不要在索引上做计算
4. 范围条件右边的列失效
5. 尽量使用覆盖索引
6. 索引字段上不要使用不等
7. 主键索引字段上不可以判断null
8. 索引字段使用like不以通配符开头
9. 索引字段字符串要加单引号

3.9 查看sql执行计划

命令：

explain   sql命令

字段说明：

• id: 每个SELECT语句都会自动分配一个唯一标识符。如果id相同，顺序从上到下执行，如果不同，id越大，优先级越高。id为null表示是一个结果集，不需要使用它来进行查询

• select_type:查询类型，主要用于区别普通查询，联合查询、子查询等复杂查询。

  参数有：

  ​ simple:表示不需要union操作或者不包含子查询的简单select查询。有连接查询时，外层的查询

  ​ 为simple,只有一个.

  ​ primary:一个需要union操作或者含有子查询的select，位于最外层的单位查询的select_type即为

  ​ primary。且只 有一个

  ​ subquery: 除了from字句中包含的子查询外，其他地方出现的子查询都可能是subquery

  ​ dependent subquery: 与dependent union类似，表示这个subquery的查询要受到外部表查询

  ​ 的影响

  ​ union: union连接的两个select查询，第一个查询是PRIMARY，除了第一个表外，第二个以后的

  ​ 表select_type 都是union

  ​ dependent union: 与union一样，出现在union 或union all语句中，但是这个查询要受到外部

  ​ 查询的影响

  ​ union result: 包含union的结果集，在union和union all语句中,因为它不需要参与查询，所以id

  ​ 字段为null

  ​ derived: from字句中出现的子查询，也叫做派生表，其他数据库中可能叫做内联视图或嵌套

  ​ select.

• table: 显示的查询表名，如果查询使用了别名，那么这里显示的是别名 如果不涉及对数据表的操作，那么这显示为null 如果显示为尖括号括起来的就表示这个是临时表，后边的N就是执行计划中的id，表示结果来自于 这个查询产生。 如果是尖括号括起来的，与类似，也是一个临时表，表示这个结果来自于union查 询的id为M,N的结果集.

• type: 有好到坏依次是system，const，eq_ref，ref，fulltext，ref_or_null，unique_subquery， index_subquery，range，index_merge，index，ALL。除了all之外，其他的type都可以使用到索引，除了index_merge之外，其他的type只可以用到一个索引。优化器会选用最优索引一个。最差应该达到range级别。

• ref:如果是使用的常数等值查询，这里会显示const 如果是连接查询，被驱动表的执行计划这里会显示驱动表的关联字段 如果是条件使用了表达式或者函数，或者条件列发生了内部隐式转换，这里可能显示为func

• rows:这里是执行计划中估算的扫描行数，不是精确值（InnoDB不是精确的值，MyISAM是精确的值，主要原 因是InnoDB里面使用了MVCC并发机制）

• extra: 这个列包含不适合在其他列中显示但十分重要的额外的信息，这个列可以显示的信息非常多，有几十种。

  using filesort：排序时无法使用到索引时，就会出现这个。常见于order by和group by语句中 说明

  ​ MySQL会使用一个外部的索引排序，而不是按照索引顺序进行读取。 MySQL中无法

  ​ 利用索引完成的排序操作称为“文件排序”

​ using index：查询时不需要回表查询，直接通过索引就可以获取查询的数据。

​ using temporary： 表示使用了临时表存储中间结果。 MySQL在对查询结果order by和group by时

​ 使用临时表 临时表可以是内存临时表和磁盘临时表，执行计划中看不出来，需要

​ 查看status变量， used_tmp_table，used_tmp_disk_table才能看出来。

​ distinct 在select部分使用了distinct关键字 （索引字段）

​ using where：表示存储引擎返回的记录并不是所有的都满足查询条件，需要在server层进行过滤。

四、Mysql锁

4.1元数据锁

4.1.1 元数据锁介绍

​ MDL(metaDataLock)元数据锁：锁表结构信息。

​ 在Mysql5.5版本中引入了MDL，当对一个表做增删改查操作的时候，加MDL读锁；当对表结构变更操作的时候，加MDL写锁。

​ 共享读锁，排他写锁

4.1.2 案例

1、session1: begin;--开启事务
             select * from mylock;--加MDL读锁
2、session2: alter table mylock add f int; -- 修改阻塞
3、session1：commit; --提交事务 或者 rollback 释放读锁
4、session2：Query OK, 0 rows affected (38.67 sec) --修改完成
Records: 0 Duplicates: 0 Warnings: 0

注意：关于DDL语句（数据定义语句，alter table等等） 默认提交，就算你开启事务，再事务后执行，他也会直接执行成功。

4.2 行级锁

4.2.1 行级锁介绍

InnoDB存储引擎实现

InnoDB的行级锁，按照锁定范围来说，分为三种

• 记录锁（Record Locks）: 锁定索引中一条记录。
• 间隙锁（Gap Locks）:锁定记录前、记录中、记录后的行RR隔离级（可重复读） --MySQL默认隔离级别
• Next-key锁：记录锁+间隙锁

4.2.2 行级锁分类

按照功能来说，分为两种：

• 共享读锁（S）：允许一个事务去读一行，阻止其他事务获得相同数据集的排它锁。当事务T对数据A加上共享锁后，则其他事务只能对A再加共享锁，不能再加排它锁。获得共享锁的事务只能读数据，不能修改数据。
• 排他写锁（X）：允许获得排他写锁的事务更新数据，阻止其他事务对该数据进行加任何锁。

mysql什么时候会加排他写锁呢？

1. DML（数据操作语句，增删改语句，不包含select语句）自动加。
2. 手动加

select * from table_name where ... For UPDATA

4.2.3 行锁演示

innoDB行锁是通过给索引上的索引项加锁来实现的，因innodb这种行锁实现特点意味着:只有通过索引条件检索的数据，InnoDB才使用行级锁，否则，innodb将使用表锁。

where 索引 =》 走行锁

否则 走表锁。

行读锁升级为表锁

session1:begin; --开启事务未提交
         select * from table01 where name = 'zs' lock in share mode; --手动加name ='zs'的行读锁，未使用索引
session2: update table01 set name ='ls' where id =2 --修改阻塞 未使用索引行锁升级为表锁。
session1: commit; --提交事务释放锁
session2: update table01 set name ='ls' where id =2 执行成功。

使用索引默认加行锁，未锁定的可以访问

1、session1: begin;--开启事务未提交
select * from mylock where ID=1 lock in share mode; --手动加id=1的行读
锁,使用索引
2、session2：update mylock set name='y' where id=2; -- 未锁定该行可以修改
3、session2：update mylock set name='y' where id=1; -- 锁定该行修改阻塞
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
-- 锁定超时
4、session1: commit; --提交事务 或者 rollback 释放读锁
5、session2：update mylock set name='y' where id=1; --修改成功

主键索引产生记录锁

session1: begin;--开启事务未提交
--手动加id=1的行写锁,
select * from mylock where id=1 for update;
2、session2：select * from mylock where id=2 ; -- 可以访问
3、session2: select * from mylock where id=1 ; -- 可以读 不加锁
4、session2: select * from mylock where id=1 lock in share mode ; -- 加读锁
被阻塞
5、session1：commit; -- 提交事务 或者 rollback 释放写锁
5、session2：执行成功

4.2.4 间隙锁

行级锁按范围又分为记录锁和间隙锁。间隙所（Gap lock）是Innodb在可重复读默认隔离级别下为了解决幻读问题时引入的锁机制。

4.2.4.1 何为幻读？

事务A按照一定条件进行数据读取，期间事务B插入了相同搜索条件的新数据，事务A先按条件进行读取时，发现了事务B新插入的数据称为幻读。

session1:   begin;
            select * from user where role_id = '1'
            比如查出2条
session2:   insert into user values (...., 1(role_id),...)
session1:   update user set name = 'cz' where role_id =1   此时更新成功3条，发生幻读

4.2.4.2 间隙锁

间隙锁是锁的是索引记录中的间隔。

	id(主键)	number（二级索引）
123锁	1	2	234加间隙锁
3456锁	3	4	4-5之间 4加锁
	6	5

产生间隙锁的条件（RR事务隔离级别下）

• 使用普通索引锁定（单条多条都会产生）
• 使用多列唯一索引
• 使用唯一索引锁定多行记录或锁住不存在的时候

不会产生间隙锁举例

select * from table where id = 100 for update

只会产生记录锁，不会产生间隙锁。对于使用唯一索引来搜索并给某一行记录加锁的语句，不会产生间隙锁。（这不包括搜索条件仅包括多列唯一索引的一些列的情况；在这种情况下，会产生间隙锁。）例如，如果id列具有唯一索引，则下面的语句仅对具有id值100的行使用记录锁，并不会产生间隙锁：

间隙锁防止两种情况

1. 防止插入间隙内的数据
2. 防止已有数据更新为间隙内的数据。

4.2.4.3间隙锁案例

非唯一索引的等值

session 1:
start transaction ;
update news set number=3 where number=4;
session 2:
start transaction ;
insert into news value(2,3);#（均在间隙内，阻塞）
insert into news value(7,8);#（均在间隙外，成功）
insert into news value(2,8);#（id在间隙内，number在间隙外，成功）
insert into news value(4,8);#（id在间隙内，number在间隙外，成功）
insert into news value(7,3);#（id在间隙外，number在间隙内，阻塞）
insert into news value(7,2);# (id在间隙外，number为上边缘数据，阻塞)
insert into news value(2,2);#（id在间隙内，number为上边缘数据，阻塞）
insert into news value(7,5);#（id在间隙外，number为下边缘数据，成功）
insert into news value(4,5);#（id在间隙内，number为下边缘数据，阻塞）

结论：只要number（where后面的）在间隙里（2 3 4），不包含最后一个数（5）则不管id是多少都 会阻塞。

主键索引范围

--主键索引范围
session 1:
start transaction ;
update news set number=3 where id>1 and id <6;
session 2:
start transaction ;
insert into news value(2,3);#（均在间隙内，阻塞）
insert into news value(7,8);#（均在间隙外，成功）
insert into news value(2,8);#（id在间隙内，number在间隙外，阻塞）
insert into news value(4,8);#（id在间隙内，number在间隙外，阻塞）
insert into news value(7,3);#（id在间隙外，number在间隙内，成功）

结论：只要id（在where后面的）在间隙里(2 4 5)，则不管number是多少都会阻塞。

非唯一索引无穷大

session 1: begin;
update news set number=3 where number=13 ;
session 2:
start transaction ;
insert into news value(11,5);#(执行成功)
insert into news value(12,11);#(执行成功)
insert into news value(14,11);#(阻塞)
insert into news value(15,12);#(阻塞)
检索条件number=13,向左取得最靠近的值11作为左区间，向右由于没有记录因此取得无穷大作为右区间，因
此，session 1的间隙锁的范围（11，无穷大）
结论：id和number同时满足
注：非主键索引产生间隙锁，主键范围产生间隙锁

4.2.5 查看行锁命令

use mysql;
show STATUS like 'innodb_row_lock%';

• Innodb_row_lock_current_waits: 当前正在等待锁定的数量；
• Innodb_row_lock_time: 从系统启动到现在锁定总时间长度；
• Innodb_row_lock_time_avg: 每次等待所花平均时间；
• innodb_row_lock_time_max:从系统启动到现在等待最常的一次所花的时间；
• innodb_row_lock_waits: 系统启动后到现在总共等待的次数；

4.3 表级锁

InnoDB也实现了表级锁，也就是意向锁，意向锁是mysql内部使用的，不需要用户干预。

• 意向共享锁（IS）：事务打算给数据行加行共享锁，必须先取得该表的IS锁
• 意向排他锁（IX）：事务打算给数据行加行排它锁，必须先取得该表的IX锁。

作用

意向锁的主要作用是为了【全表更新数据】时的性能提升。否则在全表更新数据时，需要先检测该表某些记录上是否有行锁。

4.4两阶段锁（2pl）

事务	MYSQL
begin:
insert into ();	加锁阶段
	加insert对应的锁
update table …	加update对应的锁
delete from	加delete对应的锁
commit	解锁阶段：释放以上的锁

锁操作分为两个阶段：加锁阶段与解锁阶段。

加锁阶段与解锁阶段不相交。

加锁阶段：只加锁不放锁。

解锁阶段：只放锁，不加锁。

4.5 死锁

两个session互相等待对方的资源释放之后，才能释放自己的资源，造成了死锁。

1、session1: begin;--开启事务未提交
--手动加行写锁 id=1 ，使用索引
update mylock set name='m' where id=1;
2、session2：begin;--开启事务未提交
--手动加行写锁 id=2 ，使用索引
update mylock set name='m' where id=2;
3、session1: update mylock set name='nn' where id=2; -- 加写锁被阻塞
4、session2：update mylock set name='nn' where id=1; -- 加写锁会死锁，不允许操作
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting
transaction

五、事务

5.1 事务介绍

在mysql中的事务是由存储引擎实现的，而且支持事务的存储引擎不多，我们主要关心得就是innoDB存储引擎中的事务。

事务处理可以用来维护数据库的完整性，保证成批的SQL语句要么全部执行，要么全部不执行。

事务用来管理DDL、DML、DCL操作，比如insert、update、delete语句，默认是自动提交的。

事务的四大特性（ACID）

• Atomicit(原子性)：构成事务的所有操作必须是一个逻辑单元，要么全部执行，要么全部不执行
• Consistency（一致性）：数据库在事务执行前后状态都必须是稳定的或者是一致的。事务执行前后的数据的可见性都是对外可见
• Isolation（隔离性）： 事务之间不会相互影响。由锁机制和MVCC机制来实现的。MVCC（多版本并发控制）：优化读写性能（读不加锁、读写不冲突）
• Durability(持久性)：事务执行成功后必须全部写入磁盘。

事务开启

BEGIN 或 START TRANSACTION；显式地开启一个事务； COMMIT 也可以使用 COMMIT WORK ，不过二者是等价的。COMMIT会提交事务，并使已对数据库进行的 所有修改称为永久性的； ROLLBACK 有可以使用 ROLLBACK WORK，不过二者是等价的。回滚会结束用户的事务，并撤销正在 进行的所有未提交的修改；

5.2 InnoDB架构图

由图可见：InnoDB存储引擎由内存池、后台线程和磁盘文件三大部分组成。

5.3 Innodb内存结构

由5.2节图中我们看出了InnoDB由内存池、线程和磁盘文件组成。这节我们去了解一下InnoDB的内存池。

5.3.1 Buffer Pool缓冲池

​ 我们在对数据库执行增删改操作的时候，不可能直接更新磁盘上的数据的，因为如果你对磁盘进行随机读写操作，那速度是相当的慢，随便一个大磁盘文件的随机读写操作，可能都要几百毫秒。如果要是那么搞的话，可能你的数据库每秒也就只能处理几百个请求了！ 在对数据库执行增删改操作的时候，实际上主要都是针对内存里的Buffer Pool中的数据进行的，也就是实际上主要是对数据库的内存里的数据结构进行了增删改，同时配合了后续的redo log、刷磁盘等机制和操作。

​ 那么问题就来了，我们的操作都在内存上，如果mysql服务崩溃了，那么内存中的数据该怎么办呢？由此我们再看innodb内存中的Redo log Buffer 重做日志缓冲的概念。

Buffer Pool里到底是什么？

数据页（Page）是InnoDB存储的最基本结构，也是Innodb磁盘管理的最小单位。Buffer Pool中存储的就是如上图的结构。如果我们要更新一行数据，此时数据库会找到这行数据所在的数据页，然后从磁盘文件里把这行数据所在的数据页直接给加载到Buffer Pool里去。

​ 默认情况下，磁盘中存放的数据页默认是16kb,Buffer Pool中的页和磁盘上是一一对应的。 Buffer pool中除了包含数据的数据页（缓存页），也包含描述数据的数据页，这类描述数据的页只有缓存页的5%左右大小。

Buffer Pool在生产中如何配置呢？

​ buffer pool应该配置为物理内存大小的50%~60%，应当为操作系统和其他的应用保留足够的空间。

​ buffer pool总大小=（chunk大小*buffer pool数量）的两倍值。chunk大小默认128m。

5.3.2 Redo log Buffer重做日志缓冲

重做日志（Redo log）: Redo Log 如果要存储数据则先存储数据的日志，一旦内存崩了，可以从日志找。

重做日志保证了数据的可靠性，InnoDB采用了Write Ahead Log(预写日志)策略，即当事务提交时，先写重做日志，然后再择时将脏页写入磁盘。如果发生宕机导致数据丢失，就通过日志进行数据恢复。

举例

insert into xxxx
commit;    //当Redo Log File 写入成功，则commit

Redo log: ib_logfile0 ib_logfile1默认为8MB，当事务提交时，必须先将该事务的所有日志写入到重做日志文件进行持久化，然后事务的提交操作完成才算完成。为了确保每次日志都写入到从左日志文件中，在每次将重做日志缓冲写入重做日志后，必须调用一次fsync操作（操作系统），将缓冲文件从文件系统缓存中真正写入磁盘。

重做日志的落盘机制

可以通过 innodb_flush_log_at_trx_commit 来控制重做日志刷新到磁盘的策略。该参数默认值为 1，表示事务提交必须进行一次fsync操作，还可以设置为0和2。0表示事务提交时不进行写入重做日志 操作，该操作只在主线程中完成，2表示提交时写入重做日志，但是只写入文件系统缓存，不进行fsync 操作。由此可见，设置为0时，性能最高，但是丧失了事务的一致性。

​ 再看下图来梳理一下整个流程。

5.3.3 Double write双写

上节我们知道了Redo log保证了mysql的可靠性，那么数据是如何落盘并保证可靠性的呢？答案就是Double Write。

如上图所示，Double Write由两部分组成，一部分是内存中的double write buffer,大小为2MB，另一部分是物理磁盘上共享表空间连续的128个页，大小也为2MB。在对缓冲池的脏页进行刷新时，并不直接写磁盘，而是通过memcpy函数将脏页先复制到内存中的double write buffer区域，之后通过double write buffer再分两次，每次1MB顺序地写入共享表空间的物理磁盘上，然后马上调用fsync函数，同步磁盘，避免操作系统缓冲写带来的问题。在完成double write页的写入后，再将double wirite buffer中的页写入各个表空间文件中。如果操作系统在将页写入磁盘的过程中发生了崩溃，在恢复过程中，InnoDB存储引擎可以从共享表空间的double write页找到该页的一个副本，将其复制到表空间中，再应用重做日志。

5.3.4 checkpoint:检查点

检查点，表示脏页写入磁盘的时机，所以检查点也就意味着脏数据的写入。

checkpoint的目的

1. 缩短数据库的恢复时间
2. buffer pool 空间不够用时，将脏页刷新到磁盘
3. redolog不可用时，刷新脏页

检查点分类：

1. sharp checkpoint: 完全检查点，数据库正常关闭时，会触发把所有的脏页都写入到磁盘上。
2. fuzzy checkpoint: 正常使用时，模糊检查点，部分页写入磁盘
3. master thread checkpoint: 以每秒或每十秒的速度从缓冲池的脏页列表中刷新一定比例的页回磁盘，这个过程是异步的。
4. flush_lru_list checkpoint: 读取lru(Least Recently Used)list,找到脏页，写入磁盘。最近最少使用
5. async/sync flush checkpoint: redo log file快满了，会批量的触发数据页回写，这个事件触发的时候又分为异步和同步，不可被覆盖的redolog占log file 的比值：75% ----》 异步、 90%----》同步。
6. dirty page too much checkpoint: 默认是脏页占比75%的时候，就会触发刷盘，将脏页写入磁盘。

5.4 InnoDB磁盘文件

5.4.1 系统表空间和用户表空间

innodb_data_file_path 的格式如下：

innodb_data_file_path=datafile1[,datafile2]...

用户可以通过多个文件组成一个表空间，同时制定文件的属性：

innodb_data_file_path = /db/ibdata1:1000M;/dr2/db/ibdata2:1000M:autoextend

这里将/db/ibdata1和/dr2/db/ibdata2两个文件组成系统表空间。

如果这两个文件位于不同的磁盘上

系统表空间（共享表空间）

1. 数据字典(data dictionary)：记录数据库相关信息
2. doublewrite write buffer：解决部分写失败（页断裂）
3. insert buffer：内存insert buffer数据，周期写入共享表空间，防止意外宕机
4. 回滚段(rollback segments)
5. undo空间：undo页

用户表空间（独立表空间）

1. 每个表的数据和索引都会存在自已的表空间中
2. 每个表的结构
3. undo空间：undo页 （需要设置）
4. doublewrite write buffer

5.4.2 重做日志和归档文件

ib_logfile0 和 ib_logfile1 在日志组中每个重做日志文件的大小一致，并以【循环写入】的方式运行。 InnoDB存储引擎先写入重做日志文件1，当文件被写满时，会切换到重做日志文件2，再当重做日志文 件2也被写满时，再切换到重做日志文件1。

5.5 innoDB的事务分析

5.5.1 数据库事务的特性

数据库事务具有ACID四大特性。ACID是以下4个词的缩写：

• 原子性(atomicity) ：事务最小工作单元，要么全成功，要么全失败 。
• 一致性(consistency)： 事务开始和结束后，数据库的完整性不会被破坏 。
• 隔离性(isolation) ：不同事务之间互不影响，四种隔离级别为RU（读未提交）、RC（读已提 交）、RR（可重复读）、SERIALIZABLE （串行化）。
• 持久性(durability) ：事务提交后，对数据的修改是永久性的，即使系统故障也不会丢失 。

5.5.2 Mysql中事务特性的实现

总的来说，mysql的事务的隔离性由多版本控制机制和锁来实现，而原子性、一致性和持久性通过InnoDB的redo log、 undo log和force log at commit机制来实现。

5.5.3 原子性、一致性、持久性 innoDB的实现

再谈RedoLog

数据库日志和数据落盘机制，如下图所示。

redo log写入磁盘时，必须进入一次操作系统的fsync操作，防止redo log只是写入了操作系统的磁盘缓存中。参数innodb_flush_log_at_trx_commit可以控制redo log日志刷新到磁盘的策略。

UndoLog

数据库崩溃重启后需要从redo log中把未落盘的脏页数据恢复出来，重新写入磁盘，保证用户的数据不丢失。当然，在崩溃恢复中还需要回滚没有提交的事务。由于回滚操作需要undo日志的支持，undo日志的完整性和可靠性需要redo日志来保证，所以崩溃恢复先做redo恢复数据，然后做undo回滚。

在事务执行的过程中，除了记录redo log，还会记录一定量的undo log。 undo log记录了数据在每个操作前的状态，如果事务执行过程中需要回滚，就可以根据undo log进行回滚操作。

​ undo log的存储不同于redo log，它存放在数据库内部的一个特殊的段(segment)中，这个段称为回滚 段。回滚段位于共享表空间中。undo段中的以undo page为更小的组织单位。undo page和存储数据 库数据和索引的页类似。因为redo log是物理日志，记录的是数据库页的物理修改操作。所以undo log（也看成数据库数据）的写入也会产生redo log，也就是undo log的产生会伴随着redo log的产 生，这是因为undo log也需要持久性的保护。如上图所示，表空间中有回滚段和叶节点段和非叶节点 段，而三者都有对应的页结构。

​ 我们再来总结一下数据库事务的整个流程，如下图所示。

事务进行过程中，每次sql语句执行，都会记录undo log和redo log，然后更新数据形成脏页，然后 redo log按照时间或者空间等条件进行落盘，undo log和脏页按照checkpoint进行落盘，落盘后相应的 redo log就可以删除了。此时，事务还未COMMIT，如果发生崩溃，则首先检查checkpoint记录，使用 相应的redo log进行数据和undo log的恢复，然后查看undo log的状态发现事务尚未提交，然后就使用 undo log进行事务回滚。事务执行COMMIT操作时，会将本事务相关的所有redo log都进行落盘，只有 所有redo log落盘成功，才算COMMIT成功。然后内存中的数据脏页继续按照checkpoint进行落盘。如果此时发生了崩溃，则只使用redo log恢复数据。

5.5.4 隔离性

在事务的并发操作中可能会出现一些问题：

• 丢失更新：两个事务针对同一数据都发生修改操作时，会存在丢失更新问题。
• 脏读：一个事务读取到另一个事务未提交的数据。
• 不可重复读：一个事务因读取到另一个事务已提交的update或者delete数据。导致对同一条记录读取两次以上的结果不一致。
• 幻读：一个事务因读取到另一个事务已经提交的insert数据。导致对同一张表读取两次以上的结果不一致。

5.5.4.1 事务隔离级别

四种隔离级别（SQL92标准）

• Read uncommited(读未提交)：最低级别，任何情况都无法保证
• Read committed (RC，读已提交)：可避免脏读的发生。
• Repeatable read (RR，可重复读)：可避免脏读、不可重复读的发生。 （注意事项：InnoDB的RR还可以解决幻读，主要原因是Next-Key（Gap）锁，只有RR才能使用 Next-Key锁）
• serializable(串行化)：可避免脏读、不可重复度、幻读的发生。

5.5.5隔离性-转账案例

5.5.5.1 问题

考虑一个现实场景： 管理者要查询所有用户的存款总额，假设除了用户A和用户B之外，其他用户的存款总额都为0，A、B用 户各有存款1000，所以所有用户的存款总额为2000。但是在查询过程中，用户A会向用户B进行转账操 作。转账操作和查询总额操作的时序图如下图所示。

如果没有任何的并发控制机制，查询总额事务先读取了用户A的账户存款，然后转账事务改变了用户A和 用户B的账户存款，最后查询总额事务继续读取了转账后的用户B的账号存款，导致最终统计的存款总额 多了100元，发生错误。

--创建账户表并初始化数据
create table tacount(id int , aname varchar(100),acount int , primary key(id));
alter table tacount add index idx_name(aname);
insert into tacount values(1,'a',1000);
insert into tacount values(2,'b',1000);
--设置隔离级读未提交（read-uncommitted）
mysql> set session transaction isolation level read uncommitted;
--session 1
mysql> start transaction ; select * from tacount where aname='a';
+----+-------+--------+
| id | aname | acount |
+----+-------+--------+
| 1 | a | 1000 |
+----+-------+--------+
--session 2
mysql> start transaction; update tacount set acount=1100 where aname='b';
--session 1
mysql> select * from tacount where aname='b';
+----+-------+--------+
| id | aname | acount |
+----+-------+--------+
| 2 | b | 1100 |
+----+-------+--------+

5.5.5.2 使用锁机制解决隔离性问题

​ 使用锁机制(LBCC)可以解决上述的问题。查询总额事务会对读取的行加锁，等到操作结束后再释放所有 行上的锁。因为用户A的存款被锁，导致转账操作被阻塞，直到查询总额事务提交并将所有锁都释放。 使用锁机制：

​ 但是这时可能会引入新的问题，当转账操作是从用户B向用户A进行转账时会导致死锁。转账事务会先锁 住用户B的数据，等待用户A数据上的锁，但是查询总额的事务却先锁住了用户A数据，等待用户B的数据上的锁。

死锁演示

--设置隔离级别为串行化（serializable） 死锁演示
mysql> set session transaction isolation level serializable;
--session 1
mysql> start transaction;select * from tacount where aname='a';
--session 2
mysql> start transaction ; update tacount set acount=900 where aname='b';
-- session 1
mysql> select * from tacount where aname='b';
-- session 2
mysql> update tacount set acount=1100 where aname='a';
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting
transaction

5.5.5.3 使用MVCC机制可以解决这个问题

​ 查询总额事务先读取了用户A的账户存款，然后转账事务会修改用 户A和用户B账户存款，查询总额事务读取用户B存款时不会读取转账事务修改后的数据，而是读取本事 务开始时的数据副本(在REPEATABLE READ隔离等级下)。

	使用MVCC机制（RR隔离级别下的演示情况）：

MVCC使得数据库读不会对数据加锁，普通的SELECT请求不会加锁，提高了数据库的并发处理能力。借 助MVCC，数据库可以实现READ COMMITTED，REPEATABLE READ等隔离级别，用户可以查看当前数 据的前一个或者前几个历史版本，保证了ACID中的I特性（隔离性)。

-- 显示当前隔离级别为 REPEATABLE-READ MySQL默认隔离级别
mysql> select @@tx_isolation;
-- session 1
mysql> start transaction ; select * from tacount where aname='a';
+----+-------+--------+
| id | aname | acount |
+----+-------+--------+
| 1 | a | 1000 |
+----+-------+--------+
-- session 2
mysql> start transaction; update tacount set acount=1100 where aname='a';
-- session 1
mysql> select * from tacount where aname='a';
+----+-------+--------+
| id | aname | acount |
+----+-------+--------+
| 1 | a | 1000 |
+----+-------+--------+
-- session 2 提交事务
mysql> commit;
-- session 1 显示在session 1 事务开始时的数据
mysql> select * from tacount where aname='a';
+----+-------+--------+
| id | aname | acount |
+----+-------+--------+
| 1 | a | 1000 |
+----+-------+--------+
-- 设置事务隔离级别为REPEATABLE-COMMITTED 读已提交
-- session 1
mysql> set session transaction isolation level read committed;
mysql> start transaction ; select * from tacount where aname='a';
+----+-------+--------+
| id | aname | acount |
+----+-------+--------+
| 1 | a | 1000 |
+----+-------+--------+
-- session 2
mysql> set session transaction isolation level read committed;
mysql> start transaction; update tacount set acount=1100 where aname='a';
-- session 1
mysql> select * from tacount where aname='a';
+----+-------+--------+
| id | aname | acount |
+----+-------+--------+
| 1 | a | 1000 |
+----+-------+--------+
-- session 2 提交事务
mysql> commit;
-- session 1 显示最新事务提交后的数据
mysql> select * from tacount where aname='a';
+----+-------+--------+
| id | aname | acount |
+----+-------+--------+
| 1 | a | 1100 |
+----+-------+--------+

5.5.6 InnoDB的MVCC实现

我们先看一下wiki上对Mvcc的定义：

Multiversion concurrency control (MCC or MVCC), is a concurrency control method
commonly used by database management systems to provide concurrent access to the
database and in programming languages to implement transactional memory.

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

MVCC，就是多版本并发控制。在INnodb引擎 RC和RR隔离级别下，对SELECT操作会访问数据的版本，达到读写并发执行，提高了系统的性能。

MVCC在mysql中的实现依赖是undo log 与read view。

5.5.6.1 当前读和快照读

在MVCC并发控制中，读操作可以分成两类：快照读（snapshot read）与当前读（current read）

• 快照读，读取的是记录的可见版本（有可能是历史版本），不用加锁。比如普通的select语句，属于快照读，不加读锁，读历史版本。

• 当前读，读取的是记录的最新版本，并且当前读返回的记录，都会加上锁，保证其他事务不会再并发修改这条记录。特殊的读操作，插入/更新、删除操作，属于当前读，需要加锁。加行写锁，读当前版本。

5.5.6.2 一致性非锁定读

一致性非锁定读（consistent nonlocking read）是指InnoDB存储引擎通过多版本控制（MVCC）读取当前数据库中行数据的方式。

如果读取的行正在执行DELETE或update操作，这时读取操作不会因此去等待行上锁的释放。相反地，InnoDB会去读取行的一个最新可见快照。

去哪里找数据的版本呢？答案就是undolog

undolog的作用

1. 回滚记录
2. 让mvcc读取历史版本

5.5.6.3 undo log

​ InnoDB行记录有三个隐藏字段:分别对应该行的rowid、事务号db_trx_id和回滚指针db_roll_ptr,其中db_trx_id表示最近修改的事务的id,db_roll_ptr指向回滚段中的undo log。

​ 根据行为的不同，undo log分为两种： insert undo log 和update undo log

• insert undo log: 是在insert操作中产生的undo log。因为insert操作的记录只对事务本身可见，rollback在该事务中直接删除，不需要进行purge操作（purge Thread）
• update undo log：是update或者delete操作中产生的undo log,因为会对已经存在的记录产生影响，rollback MVCC机制会找他的历史版本进行恢复。为了提供MVCC机制，因此update undo log不能在事务提交时就进行删除，而是将事务提交时放到history list上等待purage线程进行最后的删除操作。

5.5.6.4 回滚演示过程

（1）事务1插入一条数据，此时的记录详情如下图所示。

(2) 第一次修改

(3)第二次修改

5.5.6.5 事务链表和ReadView

当前事务能读到哪个历史版本？

MYSQL中的事务在开始到提交这段过程中，都会保存到一个叫trx_sys的事务链表中，这是一个基本的链表结构：

ct-trx-->trx11-->trx9-->trx6-->trx5-->trx3

事务链表中保存的都是还未提交的事务，事务一旦被提交，则会从事务链表中摘除。

RR隔离级别下，每个事务开始时，会将当前系统中的所有的活跃事务拷贝到一个列表中（read view）

RC隔离级别下，在每个语句开始的时候，会将当前系统中的所有的活跃事务拷贝到一个列表中（read view）

查看事务列表命令

show engine innodb status

当前事务能读到哪个历史版本的数据由ReadView决定

Read View是事务开启时当前所有事务的一个集合，这个类中存储了当前ReadView中最大事务ID及最小事务ID。

举例
下面是当前活跃的事务列表

ct-trx --> trx11 --> trx9 -->trx6 -->trx5 -->trx3

对应的Read_view数据结构如下：

read_view->creator_trx_id = ct-trx;
read_view->up_limit_id = trx3; 低水位
read_view->low_limit_id = trx11; #高水位 
read_view->trx_ids = [trx11, trx9, trx6, trx5, trx3];

low_limit_id是“高水位”，即当时活跃事务的最大id，如果读到row的db_trx_id>=low_limit_id，说明这 些id在此之前的数据都没有提交，如注释中的描述，这些数据都不可见。

if (trx_id >= view->low_limit_id) {
return(FALSE);
}
注：readview 部分源码

up_limit_id是“低水位”，即当时活跃事务列表的最小事务id，如果row的db_trx_id<up_limit_id,说明这些数据在事务创建的id时都已经提交，如注释中的描述，这些数据均可见

if (trx_id < view->up_limit_id) {
return(TRUE);
}

row的db_trx_id在low_limit_id和up_limit_id之间，则查找该记录的db_trx_id是否在自己事务的 read_view->trx_ids列表中，如果在则该记录的当前版本不可见，否则该记录的当前版本可见。

不太懂？没关系。

比如在rr隔离级别下，更新一条数据时，会将当前活跃事务拷贝到一个列表中。

ct-trx --> trx11 --> trx9 -->trx6 -->trx5 -->trx3

如果要访问的记录版本的事务未trx2，说明该版本已经被提交，对当前活动的事务均可见。

如果要访问的记录版本的事务大于等于trx11（高水位），则说明这个版本是在ReadView之后产生的，所以，不能被访问。

如果要访问的记录版本的事务在高低水位之间，会去看看该记录在不在，在说明

还未提交不能访问，不在说明已经提交，可以访问。

不同隔离级别ReadView实现方式

1. read-commited

函数：ha_innobase::external_lock
if (trx->isolation_level <= TRX_ISO_READ_COMMITTED
&& trx->global_read_view) {
/ At low transaction isolation levels we let
each consistent read set its own snapshot /
read_view_close_for_mysql(trx);

即：在每次语句执行的过程中，都关闭read_view, 重新在row_search_for_mysql函数中创建当前 的一份read_view。这样就会产生不可重复读现象发生。

2. repeatable read

在repeatable read的隔离级别下，创建事务trx结构的时候，就生成了当前的global read view。使 用trx_assign_read_view函数创建，一直维持到事务结束。在事务结束这段时间内 每一次查询都不会重 新重建Read View ， 从而实现了可重复读

六、行锁原理分析

谈锁前提

select * from t1 where id = 10;
delete from t1 where name = 'zs'

前提一：id列是不是主键？

前提二：当前系统的隔离级别是什么？

前提三：id列如果不是主键，那么id列上有索引吗？

以下各个操作不包括查找

6.1 RC隔离级别下

6.1.1 id主键

id是主键，给定SQL

delete from t1 where id =10

只需要将主键上id=10的记录加上X锁（排他锁）

6.1.2 id唯一索引

id不是主键，而是一个unique的二级索引键值。

delete from t1 where id =10

innodb的索引是聚集索引，没有主键INnoDB内部会基于Row_id值列生成一个隐式的聚集索引。（这个字段值的生成都是通过了线程同步，因此可能碰到随机性卡顿，以及使用不了主键索引，需要回表查询，速度慢）

所以，这条语句的加锁过程为。

首先在次要索引中，找到合适条件的记录，加X锁。

在次要索引中找到符合条件的记录之后，接着拿到隐式主键，去主键索引去查找记录，找到加X锁。

6.1.3 非唯一索引

会先对次要索引相关的记录加X锁，在对主键索引加X锁

6.1.4 id无索引

Table: t1(name primary key,id)

where id =10

只能全表扫描，对所有记录加锁

6.2 RR隔离级别下

6.2.1 id主键

where id =10

只需要将主键上id=10的记录加上X锁（排他锁），

如果id 的条件是范围 则加间隙锁

6.2.2 id唯一索引

同RC下

6.2.3 非唯一索引

Table: T1(name primary key,id key)

name主键、id 非唯一索引

delete from t1 where id =10

次要索引 GAP锁，主键索引记录X锁

6.2.4 条件无索引

Table: T1(name primary key,id )

name主键，id无索引

间隙锁、记录X锁

6.3 Serializable

只要有SQL就锁，而且无索引，表锁。

select也是加锁的。

七、一条复杂SQL的加锁分析

假定在RR隔离级别下，图中的SQL，会加什么锁呢？

delete from t1 where pubtime >1 and pubtime <20 and userid ='hdc' and comment is not null;

在详细分析这条SQL的加锁情况前，还需要有一个知识储备，那就是一个SQL中的where条件如何拆分，结果如下：

• index key: pubtime >1 and pubtime <20。此条件，用于确定SQL在idx_t1_pu索引上的查询范围
• index filter: userid=“hdc”。此条件，可以在idx_t1_pu索引上进行过滤，但不属于index key.
• Table filter: comment is not Null。此条件，在Idx_t1_pu索引上无法过滤，只能在聚簇索引上过滤。

在where条件过滤时，先过滤index key(索引列为范围查询，其实条件为index First key,截止条件为 index Last key)，再过滤Index Filter（索引列），最后过滤（Table Filter（非索引列）。在ICP过程中下推Index Filter。（Server会将index Filter下推到引擎层）

结论

​ 在Repeatable Read隔离级别下，针对一个复杂的SQL，首先需要提取其where条件。

• Index Key确定的范围，需要加上GAP锁；

  • Index Filter过滤条件，视MySQL版本是否支持ICP，若支持ICP，则不满足Index Key和Index Filter的记录，不加X锁，否则需要X锁；
    • Table Filter过滤条件，无论是否满足，都需要加X锁。 server层

7.1索引下推

将 Index Filter 从 Server 层 Push Down 到了引擎层，减少了因回表产生的磁盘 I/O，也减少了与 Server 层的交互，提高了 SQL 执行效率

适用条件

• 当 SQL 需要全表访问时，ICP 的优化策略可用于 range, ref, eq_ref, ref_or_null 类型的数据访问方式

• 只适用于 InnoDB 和 MyISAM 两种存储引擎

• 在 InnoDB 中，ICP 只适用于二级索引

  ICP 的目的就是为了减少回表导致的磁盘 I/O，而 InnoDB 的聚簇索引的叶子节点存放的就是完整的数据记录，只要索引数据被读到内存了，那么索引项对应的完整数据记录也就读到内存了，那么通过索引项获取数据记录的过程就在内存中进行了，无需进行磁盘 I/O；也就说聚簇索引上应用 ICP，不会减少磁盘 I/O，也就没有使用的意义了

• 不支持覆盖索引

• 不支持子查询条件的下推

• 不支持存储过程条件、触发器条件的下推

7.2 死锁原理与分析

本文前面的部分，基本上已经涵盖了MySQL/InnoDB所有的加锁规则。深入理解MySQL如何加锁，有 两个比较重要的作用：

• 可以根据MySQL的加锁规则，写出不会发生死锁的SQL
• 可以根据MySQL的加锁规则，定位出线上产生死锁的原因；

7.2.1 死锁的产生

记录锁产生

1、session1: begin;--开启事务未提交
--手动加行写锁 id=1 ，使用索引
update mylock set name='m' where id=1;
2、session2：begin;--开启事务未提交
--手动加行写锁 id=2 ，使用索引
update mylock set name='m' where id=2;
3、session1: update mylock set name='nn' where id=2; -- 加写锁被阻塞
4、session2：update mylock set name='nn' where id=1; -- 加写锁会死锁，不允许操作
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting
transaction

间隙锁产生

1、session1：start transaction ;
select * from news where number=6 for update--产生间隙锁
2、session2：start transaction ;
select * from news where number=7 for update--产生间隙锁
3、session1：insert into news values(9,7);--阻塞
4、session2：insert into news values(9,7);
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting
transaction

结论

死锁的发生与否，并不在于事务中有多少条SQL语句，【死锁的关键在于】：两个(或以上)的Session【加锁
的顺序】不一致。而使用本文上面提到的，分析MySQL每条SQL语句的加锁规则，分析出每条语句的加锁顺
序，然后检查多个并发SQL间是否存在以相反的顺序加锁的情况，就可以分析出各种潜在的死锁情况，也可以
分析出线上死锁发生的原因。

7.2.2 如何解决死锁呢？

查看死锁命令

show engine innodb status \G

如何解决死锁呢？

1. 注意程序的逻辑，根本的原因是程序逻辑的顺序，最常见的是交叉更新。
2. 保证事务的轻量。越是轻量的事务，占有越少的锁资源，这样发生死锁的几率就越小。
3. 提高运行的速度。避免使用子查询，尽量使用主键等。
4. 尽量快提交事务，减少持有锁的时间。

八、性能分析和性能优化

8.1 性能分析的思路

1. 首先需要使用【慢查询日志】功能，去获取所有查询时间比较长的SQL语句
2. 其次【查看执行计划】查看有问题的SQL的执行过程explain
3. 最后可以使【show profile[s]】 查看有问题的SQL的性能使用情况。

8.2慢查询

8.2.1 慢查询日志开启

配置文件开启

# /etc/my.cnf配置文件开启
slow_query_log = ON
long_query_time =3
slow_query_log_file =/var/lib/mysql/slow-log.log

命令开启

show variables like '%slow_query%'; -- 可以用这个查询所有的变量

//第一步
set global log_output='TABLE'; -- 开启慢日志,纪录到 mysql.slow_log 表
set global long_query_time=2; -- 设置超过2秒的查询为慢查询
set global slow_query_log='ON';-- 打开慢日志记录

//第二步 运行一下比较慢的功能,执行下面的语句
select convert(sql_text using utf8) sql_text from mysql.slow_log -- 查询慢sql的 日志
//第三步 记得关上日志
set global slow_query_log='OFF'; -- 如果不用了记得关上日志

----------------------------------------------------------------
// 清除日志
SET GLOBAL slow_query_log = 'OFF';

ALTER TABLE mysql.slow_log RENAME mysql.slow_log_drop;


 CREATE TABLE `mysql`.`slow_log` (
  `start_time` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
  `user_host` mediumtext NOT NULL,
  `query_time` time(6) NOT NULL,
  `lock_time` time(6) NOT NULL,
  `rows_sent` int(11) NOT NULL,
  `rows_examined` int(11) NOT NULL,
  `db` varchar(512) NOT NULL,
  `last_insert_id` int(11) NOT NULL,
  `insert_id` int(11) NOT NULL,
  `server_id` int(10) unsigned NOT NULL,
  `sql_text` mediumblob NOT NULL,
  `thread_id` bigint(21) unsigned NOT NULL
) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='Slow log';


 

SET GLOBAL slow_query_log = 'ON';

DROP TABLE mysql.slow_log_drop; 

8.2.2分析慢查询日志的工具

8.2.2.1使用mysqldumpslow工具

Mysql自带的慢查询日志工具，可以使用mysqldumpslow工具搜索慢查询日志中的SQL语句。

例如

得到按照时间排序的前10条里面含有左连接的查询语句。

mysqldumpslow -s t -t 10 -g “left join”
/var/log/mysql/slow.log

常用参数说明

-s：是表示按照何种方式排序
-c: 访问计数
-l: 锁定时间
-r:返回记录
-t:查询时间
al:平均锁定时间
ar:平均返回记录数
at:平均查询时间
-t: 是top n的意思，即为返回前面多少条的数据
-g:后边可以写一个正则匹配模式，大小写不敏感的

8.2.2.2 使用percona-toolkit工具

percona-toolkit是一组高级命令行工具的集合，可以查看当前服务的摘要信息、磁盘检测，分析慢查询日志，查找重复索引，实现表同步等等。

下载

https://downloads.percona.com/downloads/percona-toolkit/3.0.11/binary/tarball/percona-toolkit-3.0.11_x86_64.tar.gz

wget https://www.percona.com/downloads/perconatoolkit/3.0.11/binary/tarball/percona-toolkit-3.0.11_x86_64.tar.gz

安装

tar -xf percona-toolkit-3.0.11_x86_64.tar.gz
cd percona-toolkit-3.0.11
perl Makefile.PL
make
make install

调错

#Can't locate ExtUtils/MakeMaker.pm in @INC 错误的解决方式:
yum install -y perl-ExtUtils-CBuilder perl-ExtUtils-MakeMaker
#Can't locate Time/HiRes.pm in @INC
yum install -y perl-Time-HiRes
# Can't locate Digest/MD5.pm in @INC
yum install perl-Digest-MD5.x86_64

使用pt-query-digest查看慢查询日志

pt-query-digest /var/lib/mysql/localhost-slow.log

**语法及重要选项**
pt-query-digest [OPTIONS] [FILES] [DSN]
--create-review-table 当使用--review参数把分析结果输出到表中时，如果没有表就自动创建。
--create-history-table 当使用--history参数把分析结果输出到表中时，如果没有表就自动创建。
--filter 对输入的慢查询按指定的字符串进行匹配过滤后再进行分析
--limit 限制输出结果百分比或数量，默认值是20,即将最慢的20条语句输出，如果是50%则按总响应时
间占比从大到小排序，输出到总和达到50%位置截止。
--host mysql服务器地址
--user mysql用户名
--password mysql用户密码
--history 将分析结果保存到表中，分析结果比较详细，下次再使用--history时，如果存在相同的语句，
且查询所在的时间区间和历史表中的不同，则会记录到数据表中，可以通过查询同一CHECKSUM来比较某类型
查询的历史变化。
--review 将分析结果保存到表中，这个分析只是对查询条件进行参数化，一个类型的查询一条记录，比较简
单。当下次使用--review时，如果存在相同的语句分析，就不会记录到数据表中。
--output 分析结果输出类型，值可以是report(标准分析报告)、slowlog(Mysql slow log)、
json、json-anon，一般使用report，以便于阅读。
--since 从什么时间开始分析，值为字符串，可以是指定的某个”yyyy-mm-dd [hh:mm:ss]”格式的时间
点，也可以是简单的一个时间值：s(秒)、h(小时)、m(分钟)、d(天)，如12h就表示从12小时前开始统计。
--until 截止时间，配合—since可以分析一段时间内的慢查询。

分析pt-query-digest输出结果

第一部分：总体统计结果 Overall：总共有多少条查询 Time range：查询执行的时间范围 unique：唯一查询数量，即对查询条件进行参数化以后，总共有多少个不同的查询 total：总计 min：最小 max：最大 avg：平均 95%：把所有值从小到大排列，位置位于95%的那个数，这个 数一般最具有参考价值 median：中位数，把所有值从小到大排列，位置位于中间那个数

# 该工具执行日志分析的用户时间，系统时间，物理内存占用大小，虚拟内存占用大小
# 340ms user time, 140ms system time, 23.99M rss, 203.11M vsz
# 工具执行时间
# Current date: Fri Nov 25 02:37:18 2016
# 运行分析工具的主机名
# Hostname: localhost.localdomain
# 被分析的文件名
# Files: slow.log
# 语句总数量，唯一的语句数量，QPS，并发数
# Overall: 2 total, 2 unique, 0.01 QPS, 0.01x concurrency ________________
# 日志记录的时间范围
# Time range: 2016-11-22 06:06:18 to 06:11:40
# 属性 总计 最小 最大 平均 95% 标准 中等
# Attribute total min max avg 95% stddev median
# ============ ======= ======= ======= ======= ======= ======= =======
# 语句执行时间
# Exec time 3s 640ms 2s 1s 2s 999ms 1s
# 锁占用时间
# Lock time 1ms 0 1ms 723us 1ms 1ms 723us
# 发送到客户端的行数
# Rows sent 5 1 4 2.50 4 2.12 2.50
# select语句扫描行数
# Rows examine 186.17k 0 186.17k 93.09k 186.17k 131.64k 93.09k
# 查询的字符数
# Query size 455 15 440 227.50 440 300.52 227.50

第二部分：查询分组统计结果 Rank：所有语句的排名，默认按查询时间降序排列，通过–orderby指定 Query ID：语句的ID，（去掉多余空格和文本字符，计算hash值） Response：总的响应 时间 time：该查询在本次分析中总的时间占比 calls：执行次数，即本次分析总共有多少条这种类 型的查询语句 R/Call：平均每次执行的响应时间 V/M：响应时间Variance-to-mean的比率 Item： 查询对象

# Profile
# Rank Query ID Response time Calls R/Call V/M Item
# ==== ================== ============= ===== ====== ===== ===============
# 1 0xF9A57DD5A41825CA 2.0529 76.2% 1 2.0529 0.00 SELECT
# 2 0x4194D8F83F4F9365 0.6401 23.8% 1 0.6401 0.00 SELECT wx_member_base

第三部分：每一种查询的详细统计结果 由下面查询的详细统计结果，最上面的表格列出了执行次 数、最大、最小、平均、95%等各项目的统计。 ID：查询的ID号，和上图的Query ID对应 Databases：数据库名 Users：各个用户执行的次数（占比） Query_time distribution ：查询时 间分布, 长短体现区间占比，本例中1s-10s之间查询数量是10s以上的两倍。 Tables：查询中涉及 到的表 Explain：SQL语句

# Query 1: 0 QPS, 0x concurrency, ID 0xF9A57DD5A41825CA at byte 802 ______
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: all events occurred at 2016-11-22 06:11:40
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 50 1
# Exec time 76 2s 2s 2s 2s 2s 0 2s
# Lock time 0 0 0 0 0 0 0 0
# Rows sent 20 1 1 1 1 1 0 1
# Rows examine 0 0 0 0 0 0 0 0
# Query size 3 15 15 15 15 15 0 15
# String:
# Databases test
# Hosts 192.168.8.1
# Users mysql
# Query_time distribution
# 1us
# 10us
# 100us
# 1ms
# 10ms
# 100ms
# 1s ################################################################
# 10s+
# EXPLAIN /*!50100 PARTITIONS*/
select sleep(2)\G

语法用例
1.直接分析慢查询文件：

pt-query-digest slow.log > slow_report.log

2.分析最近12小时内的查询：

pt-query-digest --since=12h slow.log > slow_report2.log

3.分析制定时间范围内的查询：

pt-query-digest slow.log --since '2017-01-07 09:30:00' --until '2017-01-07
10:00:00'> > slow_report3.log

4.分析指含有select语句的慢查询

pt-query-digest --filter '$event->{fingerprint} =~ m/^select/i' slow.log>
slow_report4.log

5.针对某个用户的慢查询

pt-query-digest --filter '($event->{user} || "") =~ m/^root/i' slow.log>
slow_report5.log

6.查询所有的全表扫描或full join 的慢查询

pt-query-digest --filter '(($event->{Full_scan} || "") eq "yes") ||(($event->
{Full_join} || "") eq "yes")' slow.log> slow_report6.log

7.把查询保存到query_review表

pt-query-digest --user=root –password=abc123 --review
h=localhost,D=test,t=query_review--create-review-table slow.log

8.把查询保存到query_history表

pt-query-digest --user=root –password=abc123 --review
h=localhost,D=test,t=query_history--create-review-table slow.log_0001
pt-query-digest --user=root –password=abc123 --review
h=localhost,D=test,t=query_history--create-review-table slow.log_0002

9.通过tcpdump抓取mysql的tcp协议数据，然后再分析

tcpdump -s 65535 -x -nn -q -tttt -i any -c 1000 port 3306 > mysql.tcp.txt
pt-query-digest --type tcpdump mysql.tcp.txt> slow_report9.log

10. 分析binlog

mysqlbinlog mysql-bin.000093 > mysql-bin000093.sql
pt-query-digest --type=binlog mysql-bin000093.sql > slow_report10.log

11.分析general log

pt-query-digest --type=genlog localhost.log > slow_report11.log

8.3 profile分析语句

Query Profiler是MySQL自带的一种query诊断分析工具，通过它可以分析出一条SQL语句的硬件性能 瓶颈在什么地方。比如CPU，IO等，以及该SQL执行所耗费的时间等。不过该工具只有在MySQL 5.0.37 以及以上版本中才有实现。默认的情况下，MYSQL的该功能没有打开，需要自己手动启动。

8.3.1 开启Profile功能

Profile 功能由MySQL会话变量 : profiling控制,默认是OFF关闭状态。

#查看是否开启了Profile功能:
select @@profiling
show variables like ‘%profil%’;
#开启profile功能
set profiling=1; --1是开启、0是关闭

示例

mysql> select @@profiling;
+-------------+
| @@profiling |
+-------------+
| 0 |
+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> set profiling=1;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> select @@profiling;
+-------------+
| @@profiling |
+-------------+
| 1 |
+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> select count(id) from tuser;
ERROR 1046 (3D000): No database selected
mysql> use kkb_2;
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> select count(id) from tuser;
+-----------+
| count(id) |
+-----------+
| 10000000 |
+-----------+
1 row in set (4.62 sec)
mysql> show profiles;
+----------+------------+-----------------------------+
| Query_ID | Duration | Query |
+----------+------------+-----------------------------+
| 1 | 0.00016275 | select @@profiling |
| 2 | 0.00009200 | select count(id) from tuser |
| 3 | 0.00014875 | SELECT DATABASE() |
| 4 | 0.00066875 | show databases |
| 5 | 0.00021050 | show tables |
| 6 | 4.61513875 | select count(id) from tuser |
+----------+------------+-----------------------------+
6 rows in set, 1 warning (0.13 sec)
mysql> show profile for query 6;
+----------------------+----------+
| Status | Duration |
+----------------------+----------+
| starting | 0.000228 |
| checking permissions | 0.000018 |
| Opening tables | 0.000035 |
| init | 0.000204 |
| System lock | 0.000071 |
| optimizing | 0.000013 |
| statistics | 0.000067 |
| preparing | 0.000027 |
| executing | 0.000004 |
| Sending data | 4.614239 |
| end | 0.000045 |
| query end | 0.000009 |
| closing tables | 0.000026 |
| freeing items | 0.000019 |
| logging slow query | 0.000124 |
| cleaning up | 0.000011 |
+----------------------+----------+
16 rows in set, 1 warning (0.00 sec)
mysql> show profile cpu,block io,swaps for query 6;
+----------------------+----------+----------+------------+--------------+------
---------+-------+
| Status | Duration | CPU_user | CPU_system | Block_ops_in |
Block_ops_out | Swaps |
+----------------------+----------+----------+------------+--------------+------
---------+-------+
| starting | 0.000228 | 0.000361 | 0.000000 | 0 |
0 | 0 |
| checking permissions | 0.000018 | 0.000000 | 0.000000 | 0 |
0 | 0 |
| Opening tables | 0.000035 | 0.000000 | 0.000000 | 0 |
0 | 0 |
| init | 0.000204 | 0.000224 | 0.000000 | 0 |
0 | 0 |
| System lock | 0.000071 | 0.000000 | 0.000000 | 0 |
0 | 0 |
| optimizing | 0.000013 | 0.000000 | 0.000000 | 0 |
0 | 0 |
| statistics | 0.000067 | 0.000131 | 0.000000 | 0 |
0 | 0 |
| preparing | 0.000027 | 0.000000 | 0.000000 | 0 |
0 | 0 |
| executing | 0.000004 | 0.000000 | 0.000000 | 0 |
0 | 0 |
| Sending data | 4.614239 | 3.648639 | 0.543410 | 55280 |
0 | 0 |
| end | 0.000045 | 0.000202 | 0.000000 | 0 |
0 | 0 |
| query end | 0.000009 | 0.000000 | 0.000000 | 0 |
0 | 0 |
| closing tables | 0.000026 | 0.000000 | 0.000000 | 0 |
0 | 0 |
| freeing items | 0.000019 | 0.000000 | 0.000000 | 0 |
0 | 0 |
| logging slow query | 0.000124 | 0.000155 | 0.000000 | 0 |
8 | 0 |
| cleaning up | 0.000011 | 0.000000 | 0.000000 | 0 |
0 | 0 |
+----------------------+----------+----------+------------+--------------+------
---------+-------+

8.4 SQL层面优化

该章节之前将性能分析的思路和工具说了一下，现在我们正式开始进行性能优化。优化主要有两个层面：服务器层面优化和SQL语句优化。先来说SQL层面的优化，

从表设计层面

• 设计中间表，一般针对于统计分析功能，或者实时性不高的需求（OLTP、OLAP）
• 为减少关联查询，创建合理的冗余字段（考虑数据库的三范式和查询性能的取舍，创建冗余字段还 需要注意数据一致性问题
• 对于字段太多的大表，考虑拆表（比如一个表有100多个字段） 人和身份证
• 对于表中经常不被使用的字段或者存储数据比较多的字段，考虑拆表（比如商品表中会存储商品介 绍，此时可以将商品介绍字段单独拆解到另一个表中，使用商品ID关联）
• 每张表建议都要有一个主键（主键索引），而且主键类型最好是int类型，建议自增主键（不考虑 分布式系统的情况下 雪花算法）。

语句优化

主要就是索引优化等，前些章节讲过，这里再大概提一下。

where 字段 、组合索引 （最左前缀） 、 索引下推 （非选择行 不加锁） 、索引覆盖（不回表）

on 两边 排序 分组统计

不要用 *

LIMIT优化

小结果集关联大结果集

count (*) 找普通索引 ,找到最小的那棵树来遍历 包含空值

count（字段） 走缓存 不包含空值

count(1) 忽略字段 包含空值

8.5 服务器层面的优化

8.5.1 配置Buffer pool

前文中，我们讲InnoDB架构图的时候我们了解了，我们的增删改操作主要都是在Buffer pool中，我们可以将buffer poll 从默认的128M调整到物理内存的50-60%，网上也有推荐3/4，和4/5的，看具体的场景吧。一般都是在50~80%之间。

8.5.2 内存预热

在数据库压力很大的情况下，重启完数据库，通过手工执行下列语句，把热数据加载到innodb_buffer_pool缓冲池中进行预热，从而避免早高峰连接数升高，程序报错。

select count(id) from tuser;

8.5.3 降低磁盘写入次数

1. 增大redolog，落盘次数就少了。innodb_log_file_size 设置成 innodb_buffer_pool_size * 0.25
2. 通用日志、慢查询日志可以不开。
3. Redolog写策略更改，见前文。

九、Mysql压测

9.1 Sysbench压测工具

1.ubuntu下安装

其他linux发行版下的安装方法请自行百度

wget https://github.com/akopytov/sysbench/archive/1.0.20.tar.gz
mv 1.0.20.tar.gz sysbench.tar.gz
tar -zxvf sysbench.tar.gz
sudo apt install -y  automake libtool pkg-config libmysqlclient-dev
cd sysbench-1.0.20/
./autogen.sh
./configure
sudo make -j
sudo make install
sysbench --version # 安装成功查看版本

2.使用

先准备数据

sysbench --db-driver=mysql --time=300 --threads=4 --report-interval=1 --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=root --mysql-password=root --mysql-db=test --tables=50 --table_size=1000000 oltp_read_write --db-ps-mode=disable prepare

参数介绍：

--mysql-host=  MySQL服务器IP

--mysql-port=  端口

--mysql-user= 账户名

--mysql-password= 密码

--mysql-db= 数据库名

--tables=表数

--table-size= 表行数

--threads= 进程数

--time = 限制测试时长（秒）

测试数据

sysbench --db-driver=mysql --time=300 --threads=4 --report-interval=1 --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=root --mysql-password=root --mysql-db=test --tables=4 --table_size=1000000 oltp_read_write --db-ps-mode=disable --report-interval=3  --time=60 run

queries performed (脚本执行的操作)

• read 读
• write 写
• other #其他操作总数(SELECT、INSERT、UPDATE、DELETE之外的操作，例如COMMIT等)
• total: 全部总数

transactions： 总事务数(每秒事务数)，括号内的内容是tps 是指一个客户机向服务器发送请求然后服务器做出反应的过程。客户机在发送请求时开始计时，收到服务器响应后结束计时，以此来计算使用的时间和完成的事务个数。

queries： 总读写操作数（QPS）。主要看括号内的内容，括号内为QPS，每秒读写数量

95th percentile: 超过95%的平均耗时。

清除测试数据

sysbench --db-driver=mysql --time=300 --threads=4 --report-interval=1 --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=root --mysql-password=root --mysql-db=test --tables=50 --table_size=1000000 oltp_read_write --db-ps-mode=disable cleanup

9.2 压测结果

由上图我们可以得知，在1核心2G内存的云服务器上，mysql8压测最终的结果

数据库	硬件	表	每表平均数据量	tps	QPS	超过95%平均耗时(ms)
mysql8	1核心2G内存	4	100w	159.24	3184.88	63.32