执行效率

做开发的可能经常会遇到查询数据库需要很久才会返回查询结果，这时就应该分析是不是慢查询导致的。如果确实有慢查询，我们又应该怎么去分析 SQL 执行效率呢？所以问题来了，Sql如何优化？优化 SQL 的前提是能定位到慢 SQL 并对其进行分析，因此此次交流分享如何定位到慢查询和如何分析 SQl 执行效率。下面是一些简单的示例。
定位慢Sql

  • 通过慢日志查询

慢Sql个人理解就是执行起来较慢的Sql语句，如何找呢？默认情况下数据库对这些功能都是关闭的，要想查看慢Sql日志首先需要开启
SQL优化 - 图1

SQL优化 - 图2

  • 开启慢查询日志，由参数 slow_query_log 决定是否开启，在 MySQL 命令行下输入下面的命令：

SQL优化 - 图3

  • 设置慢查询时间阀值
    一般情况下，该阀值可以为1，但是如果要求比较高的 QPS，可设置慢查询为 0.1 秒。本次实验我的设置为0.5

SQL优化 - 图4

SQL优化 - 图5

  • 确定慢查询日志路径、确定慢查询日志的文件名
    查看上述结果可以看到我的机器的慢查询日志文件文件路径和文件名称，由于默认情况下慢日志管理是关闭的，日志文件和路径是可见的，但是在实际位置是没有的，开启慢查询日志后会自动创建。

    查看一下日志文件里面的内容，起初我使用 select * from college 查询了下该表。表college 里面记录了我从网上爬取的所有国内大学（全不全的不重要了）共计2614条记录。日志内容如下：
    SQL优化 - 图6

  • Time：慢查询发生的时间

  • User@Host：客户端用户和 IP
  • Query_time：查询时间
  • Lock_time：等待表锁的时间
  • Rows_sent：语句返回的行数
  • Rows_examined：语句执行期间从存储引擎读取的行数
    查看慢日志文件的方式有很多，上述采用的是默认的，比如其他的mysqldumpslow 具体使用方法可以在帮助中查看（只是在输出格式上有所差异）

SQL优化 - 图7

  • 通过show processlist
    有时慢查询正在执行，已经导致数据库负载偏高了，而由于慢查询还没执行完，因此慢查询日志还看不到任何语句。此时可以使用 show processlist 命令判断正在执行的慢查询。show processlist 显示哪些线程正在运行。

SQL优化 - 图8
Time：表示执行时间 Info：表示 SQL 语句

explain分析慢查询

  • 用法 explain + Sql语句即可
    下面Sql一是过滤name字段包含北京的，第二条是查询所有

SQL优化 - 图9

  • 参数理解：
    id 执行顺序
  • select_type
    显示本行的查询类型
    (1) SIMPLE(简单SELECT，不使用UNION或子查询等)
    (2) PRIMARY(子查询中最外层查询，查询中若包含任何复杂的子部分，最外层的select被标记为PRIMARY)
    (3) UNION(UNION中的第二个或后面的SELECT语句)
    (4) DEPENDENT UNION(UNION中的第二个或后面的SELECT语句，取决于外面的查询)
    (5) UNION RESULT(UNION的结果，union语句中第二个select开始后面所有select)
    (6) SUBQUERY(子查询中的第一个SELECT，结果不依赖于外部查询)
    (7) DEPENDENT SUBQUERY(子查询中的第一个SELECT，依赖于外部查询)
    (8) DERIVED(派生表的SELECT, FROM子句的子查询)
    (9) UNCACHEABLE SUBQUERY(一个子查询的结果不能被缓存，必须重新评估外链接的第一行)
  • table
    查询涉及到的表
  • type
    对表的访问方式表示MySQL在表中找到所需行的方式，又称“访问类型”常用的类型有：ALL、index、range、 ref、eq_ref、const、system、NULL（从左到右，性能从差到好）
    (1) ALL：Full Table Scan， MySQL将遍历全表以找到匹配的行
    (2) index: Full Index Scan，index与ALL区别为index类型只遍历索引树
    (3) range:只检索给定范围的行，使用一个索引来选择行
    (4) ref: 表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值
    (5) eq_ref: 类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，就是多表连接中使用primary key或者 unique key作为关联条件
    (6) const、system: 当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。如将主键置于where列表中，MySQL就能将该查询转换为一个常量，system是const类型的特例，当查询的表只有一行的情况下，使用system
    (7) NULL: MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成。
  • possibile_keys
    可能选择的索引
  • key
    实际选择的索引
  • key_len
    被选择的索引长度：一般用于判断联合索引有多少列被选择了
  • ref
    与索引比较的列
  • rows
    预计需要扫描的行数，对 InnoDB 来说，这个值是估值，并不一定准确
  • filtered
    按条件筛选的行的百分比
  • Extra
    附加信息

对比下不同的查询
SQL优化 - 图10

条件字段有索引，为什么查询也这么慢

函数操作

  • 表设计

    1. CREATE TABLE `t1` 
    2. `id` int(11) NOT NULL AUTO_INCREMENT,
    3. `a` varchar(20) DEFAULT NULL,
    4. `b` int(20) DEFAULT NULL,
    5. `c` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,  
    6. PRIMARY KEY (`id`),
    7. KEY `idx_a` (`a`) USING BTREE,
    8. KEY `idx_b` (`b`) USING BTREE,
    9. KEY `idx_c` (`c`) USING BTREE
    10. ) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4;

  • 索引

image.png
案例 查找具体某一天的记录

  1. select * from t1  where date(c)='2019-05-21'

查看执行计划，发现没有走索引，而是进行全表扫描 

  1. explain CC
  1. mysql> explain select * from t1 where date(c) = '2019-05-21';
  2. +----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
  3. | id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra       |
  4. +----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
  5. |  1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 10302 |   100.00 | Using where |
  6. +----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
  7. 1 row in set, 1 warning (0.00 sec)

image.png

  • 分析

在索引树中存储的是主键值和索引列的实际值，由于条件选项是c=’2020=05-21’ 所以无法匹配实际值从而走全表扫描。
SQL优化 - 图13

  • 优化

    可以把条件项修改为该列所匹配的模式 ，如选择区间范围进行选择 

    1. select * from t1 where c>='2019-05-21 00:00:00' and c<='2019-05-21 23:59:59';

    执行计划如下，可以看到此次执行是走索引的
    image.png
    所以遇到类似的情况，尽量避免对字段进行函数处理，把条件筛选方式改成和原值相匹配的模式 因为对索引字段做函数操作时，优化器会放弃使用索引。

    隐式转换

    有些时候，在做查询时候我们可能会拿数字类型和字符串做对比，在一定情况下查询时没问题的，如t1表的a字段为varchar 字符串类型（内容全部时数字）我们拿类似 a=100这样的条件去查询数据是没问题的（原因就是mysql在处理的时候进行了隐式转换）。而这样的操作也会使得查询索引字段也不会按照索引去检索

  • 查询t1表中a=1000的记录 

    1. select * from t1 where a=1000;

  • 查看执行计划

image.png
可以看到此次查询进行了全表扫描

  • 优化

在做类似查找时，先查看数据库设计，确定字段的类型，再进一步写sql 会避免很多坑
image.png
严格按照字段类型进行SQL书写，否则会遇到很多坑

  • 踩坑

    比如字段a是字符串类型，但是在设计上加上业务上的种种条件，使得a记录存在多种标识，如为0是哪种标识，为xxx（非数字 包含英文）是哪种标识等。现在存在一个需求就是找出这些记录中a为0的记录，可能很多开发会这么做

    1. select * from table_name where a=0;

    这段sql看上去没问题，但是查询结果令人诧异，就是很多如a字段为”sasas3232i3232nbds” 这样的记录也会被筛选出来，为什么 原因就是隐式转换所引起的，mysql在处理上述sql的时候，会把字段a的内容转换成数字类型，他会从a字段的第一个字符开始转换只要存在数字类型就会转化为该字符所对应的数字，但如果遇到非字符类型则统一会转换为0，这就是为什么结果差异的原因。所以在查询之前一定要按照SQL类型做条件筛选！

    模糊查询

    很多时候会遇到通过某个字段的关键字进行模糊查询，大多数时候都是类似%xxxx%的条件筛选，但同样这样查询是不进行索引检索的。如何查询才能使其索引检索？

  • 具体sql如下 

    1. select * from t1 where a like '%10000%';

  • 执行计划

image.png

  • 优化

    可以结合业务，改变匹配模式，如确定字段前面的值
    image.png

    优化查询范围

    优化器会根据检索比例、表大小、I/O块大小等进行评估是否使用索引。比如单次查询的数据量过大，优化器将不走索引。

  • 查找a字段在1-2000中的记录 

    1. select * from t1 where a between '1' and '2000';

  • 执行计划

image.png
可以看到没有使用索引检索。

  • 优化

可以把数据量减少或者条件范围减少 

  1. select * from t1 where a between '1' and '2000' limit 100;

image.png

计算操作

与有对条件字段做计算操作的需求时

  • 查询 b-1=1000的记录 

    1. select * from t1 where b-1=1000;

  • 执行计划

image.png

  • 优化

换一种思路 

  1. select * from t1 where b=1000+1;

image.png
可以正常使用索引。
如果是实在避免不了运算之类的，可以先查出数据再用程序去处理计算。毕竟sql主要是用来存储数据和获取数据的，计算可以交给其他工具。
总结下：
image.png SQL优化 - 图24

Mysql索引

对于 MySQL 而言，使用最频繁的就是 B+ 树索引，所以我们必须要知道 B+ 树的结构，而 B+ 树是借鉴了二分查找法、二叉查找树、平衡二叉树、B树的一些思想构建的。

二叉树

对于二叉树来讲，如果数据是单边增长的数据则可以发现其弊端（树高度太高）查询效率低，相当于是顺序查找，如果要查询数据6，可以发现要查询的次数是6。
image.png

红黑数（平衡二叉树）

平衡二叉树在之前二叉树数的基础上做了改进，基本条件还是满足二叉树，但是任何一个节点的左子树与右子树都是平衡二叉树，并且高度之差的绝对值不超过 1，这样做的目的就是确保该树是平衡的。同样如果要查找数据6，可以发小只需要查找2次就行，效率远大于二叉树。但是红黑数每个节点最多只有两个分支，如果数据量比较大，要经历多层节点才能查询在叶子节点的数据。
image.png

B 树

  • B 树可以理解为一个节点可以拥有多于 2 个子节点的多叉查找树。
  • B 树中同一键值不会出现多次，要么在叶子节点，要么在内节点上

与平衡二叉树相比，B 树利用多个分支（平衡二叉树只有两个分支）节点，减少获取记录时所经历的节点数。B 树也是有缺点的，因为每个节点都包含 key 值和 data 值，因此如果 data 比较大时，每一页存储key 会比较少，当数据比较多时，同样要经历多层节点才能查询在叶子节点的数据。
image.png

B+树

B+ 树是 B 树的变体，定义基本与 B 树一致，与 B 树的不同点：

  • 所有叶子节点中包含了全部关键字的信息
  • 各叶子节点用指针进行连接
  • 非叶子节点上只存储 key 的信息，这样相对 B 树，可以增加每一页中存储 key 的数量。
  • B 树是纵向扩展，最终变成一个 “瘦高个”，而 B+ 树是横向扩展的，最终会变成一个 “矮胖子”

在 B+ 树中，所有记录节点都是按键值的大小顺序存放在同一层的叶子节点上。B+ 树中的 B 不是代表二叉 (binary) 而是代表（balance），B+ 树并不是一个二叉树。
image.png

B+树索引

InnoDB中B+树索引分为聚集索引和辅助索引

聚集索引

聚集索引是按照每张表的主键构建的一个B+树,其叶子节点存放的是整行数据,innoDB的主键一定是聚集索引,如果没有定义主键,聚集索引就是第一个不允许为null的唯一索引.查询优化器倾向于采用聚集索引,因为聚集索引能够把在B+树的叶子节点直接找找到数据

  • 创建测试表并插入数据

image.png

  • 聚集索引大致数据结构

SQL优化 - 图30

  • 说明

根据主键创建了B+树
叶子节点存储的是该主键对应的整行数据

辅助索引

InnoDB 存储引擎辅助索引的叶子节点并不会放整行数据，而存放的是键值和主键值。当通过辅助索引来寻找数据时，InnoDB 存储引擎会遍历辅助索引树查找到对应记录的主键，然后通过主键索引来找到对应的行数据。
比如一颗高度为 3 的辅助索引树中查找数据，那需要对这颗辅助索引树遍历 3 次找到指定主键，如果聚集索引树的高度也为 3，那么还需要对聚集索引树进行 3 次查找，最终找到一个完整的行数据所在的页，因此获取数据一共需要 6 次逻辑 IO 访问。辅助索引 idx_a 结构如下：

SQL优化 - 图31

  • 说明

根据 a 字段的值创建了 B+ 树结构
每个叶子节点保存的是 a 字段自己的键值和主键值

示例

对于如下SQL 

  1. select * from t2 where a = 3

该sql的执行过程是先在辅索引中找到主键id的值为3,然后在聚集索引中找到对应的行数据

  1. select * from t2 where id=3

该sql 的执行过程是直接在聚集索引中找到主键id值为3的行数据,可以看出只进行扫描一个B+树,效率远高于上面sql的执行效率.

普通索引和唯一索引的差别

Insert buffer & change buffer

  • insert buffer

对于非聚集索引的插入时，先判断插入的非聚集索引页是否在缓冲池中。如果在，则直接插入；如果不在，则先放入 Insert Buffer 中，然后再以一定频率和情况进行 Insert Buffer 和辅助索引页子节点的 merge 操作。这时通常能将多个插入合并到一个操作中（因为在一个索引页中），就大大提高了非聚集索引的插入性能。

  • change buffer

InnoDB 存储引擎可以对 insert、delete、update 都进行缓存。唯一索引的更新不会使用change buffer，因为唯一索引必须要将数据页读入内存中才能判断是否违反唯一约束，所以必须要把数据读取到内存中，直接进行更新。

具体差别

  • 如果对数据有修改操作，普通索引可以使用change buffer，而唯一索引不可以
  • 对于普通索引的查找，需要查询全部数据，直到不满足，而唯一索引的查找找到第一个记录就直接返回
  • 数据修改时，唯一索引在 RR 隔离级别下，更容易出现死锁

联合索引

是指对表上的多个列进行索引。适合 where 条件中的多列组合，在某些场景可以避免回表。

SQL优化 - 图32

联合索引的键值数量大于 1（比如上图中有 a 和 b 两个键值），与单个键值的 B+树一样，也是按照键值排序的。比如图中 a、b 两个字段的值为 (1,1),(1,2),(1,3),(2,1),(2,2),(2,3)，是按(a,b) 进行排序的。因此，对于 a、b 两个字段都做为条件时，查询是可以走索引的；对于单独 a 字段查询也是可以走索引的。但是对于 b 字段单独查询就走不了索引了，因为在上图，b 字段对应的值为 1,2,3,1,2,3，显然不是有序的，所以走不了 b 字段的索引。
image.png

  • 示例1 当联合索引各字段都作为条件，会完整使用联合索引,且与各字段的位置无关 

    1. select a,b,c from test_index where a=1 and b=1 and c=1; 
    2. select a,b,c from test_index where b=1 and c=1 and a=1;

    执行计划
    image.png

  • 示例2 当联合索引前面字段使用范围查找，后面字段仍然作为条件时，仍然使用完整联合索引 

    1. select a,b,c from test_index where a=1 and b in (1,2) and c = 1;

    执行计划
    image.png

  • 示例 3 当联合索引前面字段使用范围查找，后面字段排序，仍然使用完整联合索引 

  1. select a,b,c from test_index where a=1 and b in (1,2) order by c;

执行计划
image.png

  • 示例 4 当使用联合索引前面的部分字段的时候，会使用部分联合索引

image.png

覆盖索引

从辅助索引中就可以查询到结果，不需要回表查询聚集索引中的记录。使用覆盖索引的优势：因为不需要扫描聚集索引，因此可以减少 SQL 执行过程的 IO 次数。

  1. select b,c from test_index where a = 1;

可以直接在辅助索引中查询到b，c的值，所以不会回表查询聚集索引。

不能使用联合索引

铭记：
联合索引中，如果第一个字段在条件中没有出现，那么联合索引的后面所有字段作为条件都无法使用这个联合索引。
遵循最左匹配原则。

Mysql锁

全局锁

mysql全局锁会关闭所有打开的表，并且使用全局读锁锁定所有表 

  1. FLUSH TABLES WITH READ LOCK;

解锁方式 

  1. UNLOCK TABLES;

全局锁是针对整个数据库的，当使用全局锁锁定之后,对于当前连接来将只有查询是有效果的，更新插入删除都会报错。对于其他连接会一致阻塞等待，直到锁释放。全局锁一般用在数据库在做备份的时候，但是如果在做备份的时候把表变成只读还是存在风险的。如果是主库备份，会导致业务数据不能修改，如果是从库则导致主从延迟。

表级锁

表级锁分为表锁和元数据锁

表锁

  • 表锁使用场景

事务需要更新某张表大部分数据时候，如果使用默认的行锁，执行效率底下，而且有可能造成其他事务长时间等待锁和锁冲突
事务涉及多个表，比较复杂， 可能会引起死锁，导致大量事务回滚，可以使用表锁

  • 表锁分为表读锁以及表写锁

加表读锁 

  1. lock tables tablename read;

如果对其表进行了表读锁，那么所有连接只能查询，执行更新添加等操作当前连接会报错，其余连接会阻塞等待直到锁释放。

加表写锁

  1. lock tables tablename write;

释放锁 

  1. unlock tables；

如果对表进行表写锁，只有本连接可进行操作，其余连接所有操作都会阻塞等待，直到锁释放。

元数据锁MDL

MDL 元数据锁不会显式的使用，在访问表的时候会自动添加，MDL保证了数据的读写的正确性。当对表做增删改查的时候会加读锁，当对表做表结构修改的时候会添加写锁。
读锁之间不会互斥，所以多个线程可以同时对一个表增删改查。读写锁，写锁之间是互斥的

行锁

Mysql的行锁是在引擎层由引擎实现，MyISAM是不支持行锁的，行锁是针对表中行记录的锁,事务A更新了一行数据，事务B也要更新同一行数据，必须等事务A的操作完成才能进行更新。

  • 两阶段锁

加锁阶段和解锁阶段，且加锁阶段和解锁阶段互不相交。InnoDB事务中，行锁是需要的时候才加上的，不是不需要了就立即释放，是要等到事务结束才能释放。

事务

ACID特性

  • atomicity（原子性） ：要么全执行，要么全都不执行即要么全部执行成功要么全部执行失败；
  • consistency（一致性）：在事务开始和完成时，数据都必须保持一致状态—不能破坏关系数据的完整性以及业务逻辑上的一致性，入转账逻辑无论操作正常与否最终转账两端的总额应该是不会发生变化的；
  • isolation（隔离性） ：事务处理过程中的中间状态对外部是不可见的—一个事务在所作的修改在最终提交之前，对其他事务是不可见的；
  • durability（持久性） ：事务完成之后，它对于数据的修改是永久性的。

InnoDB采用Redo log 机制保证事务更新的一致性和持久性

Redo Log

redo log（重做日志） 用于记录事务操作变化，记录的是数据被修改之前的值，由

  • 内存中的重做日志缓冲 redo log buffer
  • 重做日志文件 read log file

数据更新都会先更新 redo log buffer，然后根据innodb_flush_log_at_trx_commit来控制redo log buffer 更新到redo file的时机。innodb_flush_log_at_trx_commit有三个可选值
0：由MySQL主线程每秒将log buffer中的redo日志写入到logfile，并调用文件系统的sync操作，将日志刷新到磁盘。
1：每次事务提交时log buffer中的redo日志写入到logfile，并调用文件系统的sync操作，将日志刷新到磁盘。
2：每次事务提交时log buffer中的redo日志写入到logfile，不会马上刷新IO缓存，而是每秒只做一次IO缓存刷新机制。

  • Mysql中该值默认为1，符合持久化的要求也是安全的，但是这种会有较大的性能损耗。
  • 将参数设置为0，如果遇到数据库崩溃，最后一秒的redo log 可能会由于未及时写入磁盘文件而丢失。这种方式效率最高，但是不安全。

  • 将参数设置为2时，当遇到数据库崩溃，最后一秒的redo log已经执行了写入磁盘操作，只是没有刷新，所以只要不是OS崩溃，数据不会丢失，该种方式是对性能和安全的折中考虑。

    Binlog

    二进制日志记录了所有的DDL，DML，但是不包括select show等操作，binlog作用

  • 恢复 数据恢复时可以使用二进制日志

  • 复制 通过传输二进制文件到从库，然后进行恢复，可以实现主从同步
  • 审计 通过二进制日志进行审计数据的变更操作

可以通过参数sync_binlog 来控制累计多少个事务之后才将二进制日志fsync到磁盘

  • sync_binlog=0 每次事务提交都只write 不 fsync
  • sync_binlog=1 每次事务提交都fsync
  • sync_binlog= N 每次事务提交都write 累计N个事务后fsync

如果设置为N，遇到数据库崩溃，则可能会丢失最近N个事务的二进制日志。

确保不丢数据

设置 innodb_flush_log_at_trx_commit 和 sync_binlog都为1 ，可以确保数据库断电重启后数据不丢失。所以再比较重要的库，可以设置双1，在开发测时 innodb_flush_log_at_trx_commit=0 ，sync_binlog设置为较大值，这样可以提高更新效率。

MVCC 多版本并发控制

隐藏列

对于InniDB,每行除了创建的字段外，还包换三个隐藏的列

  • Row ID: 如果没有主键，Mysql会自己创建一个自增的ID
  • 事务ID 记录最后一次操作该记录的事务ID

  • 回滚指针：指向该记录的上一个版本

    Undo log

    Undo log 是逻辑日志，将数据库恢复到之前的样子。如果事务时insert 其对应的回滚操作就是delete，如果时delete，回滚操作就是insert等。Undo的作用就是回滚操作，另一个作用就时MVCC。当用户读取一行记录时，如果当前记录已经被其他事务占用，则当前事务通过undo log来读取之前的行版本信息，因为没有事务需要对历史数据做修改操作，所以不需要加锁，已此来实现非锁定读取。

    ReadView

    read view是指事务进行快照操作的那一刻，产生数据库系统中当前活跃事务列表的一个快照。readview大致包含以下内容：

  • trx_ids 数据库系统当前活跃事务ID集合

  • low_limit_id 活跃事务中最大事务ID+1
  • up_limit_id 活跃事务id中最小事务id
  • creator_trx_id:创建这个read view事务的id

如果一个事务创建了read view ，那他的creator_trx_id就为这个事务的id，如果要访问某一行数据，加该行的隐藏事务id为t_id。则可能出现的情况有

  • t_id< up_limit_id 说明这行记录在这些活跃的事务创建之前提交了，那么这行数据对该事务就是可见的
  • t_id >=low_limit_id 说明这行记录是在活跃事务开始后创建的，这行记录对该事务时不可见的。
  • up_limit_id<=t_id<low_limit_id 说明改行记录可能时在这些活跃事务中创建的，如果t_id也在trx_ids中则说明t_id还未提交，那么该记录就是对当前事务不可见的，如果不在里面则说明已提交，该记录对当前是事务是可见的。

对于一个不可见的事务可以通过undo log查询之前的记录。所以可以看到readView规则可以判断当前版本的数据是否对当前事务是可见的。最终查询一条记录的大致步骤如下：

  • 获取事务本身的事务ID
  • 获取Read View
  • 查询得到的数据，然后与Read View中事务的版本进行比较
  • 如果可以查询，则直接查询对应的记录，如果不能则通过Undo log 中获取历史快照

  • 最终返回结果

    MVCC

    场景 事务1往表里插入一条记录 事务2修改此记录（还未提交）执行Update整体步骤如下：

  • 数据库会先对满足条件的行加排他锁

  • 然后将原纪录复制到Undo log中
  • 修改数据 并修改事务id
  • 通过隐藏的回滚指针指向Undo log中的历史记录
  • 事务提交，释放之前的排他锁

优势：读不加锁，读写不冲突，极大增加Mysql的并发，通过MVCC 保证了事务中的隔离性

事务隔离级别

  • 读未提交 READ-UNCOMMITTED

(读未提交 RU) 在该隔离级别中，所有的事务都可以看到其他事务未提交事务的执行结果，可能会出现脏读。
eg：
当事务1处理了数据之后，在还未提交之前，其他隔离级别为RU的连接可以看到事务1的执行结果。

D session1 session2
1 set session transaction_isolation=‘READ-UNCOMMITTED’; set session transaction_isolation=‘READ-UNCOMMITTED’;
2 begin; begin;
3 select * from t21 where a=1;
SQL优化 - 图38
4 insert into t21(a,b) values (1,3);
5 select * from t21 where a=1;
SQL优化 - 图39
6 commit; commit;
  • 读已提交 READ-COMMITTED

(RC) 一个事务只能看到已经提交事务所做的变化，因为同一事务的其他实例在该实例处理期间会有新的commit，所以可能会出现幻读。

| ID | session1 | session2 | | 1 | set session transaction_isolation=‘READ-COMMITTED’; | set session transaction_isolation=‘READ-COMMITTED’; | | —- | —- | —- | | 2 | begin; | begin; | | 3 | select from t21 where a=1;
SQL优化 - 图40 | | | 4 | | insert into t21(a,b) values (1,3); | | 5 | select from t21 where a=1;
SQL优化 - 图41 | | | 6 | | commit; | | 7 | select * from t21 where a=1;
SQL优化 - 图42 | | | 8 | commit; | |

  • 可重复读 Repeatable Read

RR Mysql默认的隔离级别，确保同一事务的多个实例在并发读取数据时，会得到同样的数据行，消除了脏读，幻读，不可重复读。只有当事务提交，查询结果才可能发生变化，否则查询结果一致。

| ID | session1 | session2 | | 1 | 添加数据 | | | —- | —- | —- | | 2 | set session transaction_isolation=‘REPEATABLE-READ’; | set session transaction_isolation=‘REPEATABLE-READ’; | | 3 | begin; | begin; | | 4 | select from t21 where a=1;SQL优化 - 图43 | | | 5 | | insert into t21(a,b) values (1,3); | | 6 | select from t21 where a=1;SQL优化 - 图44 | | | 7 | | commit; | | 8 | select from t21 where a=1;SQL优化 - 图45 | | | 9 | commit; | | | 10 | select from t21 where a=1;SQL优化 - 图46 | |

session2 写入了新数据未提交的情况下，session1 无法查看到新记录，等到 session2 提交但是 session1 还未提交时，session1 还是不能看到新记录，需要等 session1 事务提交之后，才能查看到第 5 步 session2 写入的新数据。

  • Serializable 串行 最高级别的隔离，强制事务排序，使之不可能相互冲突 | ID | session1 | session2 | | 1 | 准备数据 | | | —- | —- | —- | | 2 | set session transaction_isolation=‘SERIALIZABLE’; | set session transaction_isolation=‘SERIALIZABLE’; | | 3 | begin; | begin; | | 4 | select from t21 where a=1;SQL优化 - 图47 | | | 5 | | insert into t21(a,b) values (1,3);
    （等待） | | 6 | select     from t21 where a=1;SQL优化 - 图48 | | | 7 | commit;
    | session1 提交后，第 5 步中的写入操作执行成功 | | 8 | | commit; | | 9 | select * from t21 where a=1;SQL优化 - 图49 | |

当 session1 中有事务查询 a=1 这行记录时，在 session2 就不能插入 a=1 的记录，进入等待。必须等 session1 提交后，session2 才能执行成功。也就是让事务串行进行。

SQL优化 - 图50