SQL语句在MySQL中的执行过程

查询语句的执行过程

可以大致看出MySQL一共分为两大部分

• Server层：包括连接器、查询缓存、分析器、优化器、执行器等，涵盖 MySQL 的大多数核心服务功能，以及所有的内置函数（如日期、时间、数学和加密函数等），所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图等
• 存储引擎：就是负责数据的存储读写，一般我们在开发中我们都是把引擎类型设置为InnoDB。不同的存储引擎使用的是同一个server层

各个模块的功能大致记一下：

• 连接器：连接器主要和身份认证和权限相关的功能相关，就好比一个级别很高的门卫一样。

• 查询缓存(MySQL 8.0 版本后移除)：如果缓存 key 被命中，就会直接返回给客户端，如果没有命中，就会执行后续的操作，完成后也会把结果缓存起来，方便下一次调用。

  为什么不建议使用缓存：因为查询缓存失效在实际业务场景中可能会非常频繁，假如你对一个表更新的话，这个表上的所有的查询缓存都会被清空。对于不经常更新的数据来说，使用缓存还是可以的。

• 分析器

  • 词法分析：sql语句每个词干什么
  • 语法分析：是否符合语法

• 优化器：选择一个好办法，比如多个索引的时候该如何选择索引，多表查询的时候如何选择关联顺序等。

• 执行器：最后去干事情。

更新语句的执行过程

这个过程涉及到两个日志bin log和redo log，具体内容查看[三大日志](#三大日志（bin log、redo log、undo log）)。

两阶段提交

两阶段提交主要是为了保证两个日志数据的一致性。

为什么要两阶段提交：

先写 redo log 直接提交，然后写 binlog，假设写完 redo log 后，机器挂了，binlog 日志没有被写入，那么机器重启后，这台机器会通过 redo log 恢复数据，但是这个时候 binlog 并没有记录该数据，后续进行机器备份的时候，就会丢失这一条数据，同时主从同步也会丢失这一条数据。

先写 binlog，然后写 redo log，假设写完了 binlog，机器异常重启了，由于没有 redo log，本机是无法恢复这一条记录的，但是 binlog 又有记录，那么和上面同样的道理，就会产生数据不一致的情况

三大日志（bin log、redo log、undo log）

redo log

这个东西为什么有：是因为MySQL如果每一次更新操作都是去写进磁盘，那么整个IO成本和资源是非常高的，所以就会采用先写日志，再写磁盘的方式。

redo log 它是物理日志，记录内容是“在某个数据页上做了什么修改”，属于 InnoDB 存储引擎。，它让MySQL拥有了崩溃恢复能力。保证了数据的持久性和完整性。相当于可以理解成一个草稿纸。

redo log的空间是有限的，采用是循环写的方式，可以理解成黑板那种感觉。

脏页刷新

脏页： 当内存数据页跟磁盘数据页内容不一致的时候，这个内存页被称为脏页。

干净页： 内容数据写入磁盘后，内存和磁盘上的数据页内容一致后，这个内存页就被称为干净页。

平时执行很快的更新操作，其实就是在写内存和日志，而MySQL偶尔抖一下的瞬间，可能就是在刷脏页（flush）。

四大刷新场景

场景1-redo log写满

场景1就好比在只有一张草稿纸的情况下，如果草稿纸已经写满了，你就只能把草稿纸一部分内容记录到试题卷上，记录完毕之后再擦掉这部分内容。

这个场景，对应的就是 InnoDB 的 redo log 写满了。这时候系统会停止所有更新操作，把 checkpoint 往前推进，redo log 留出空间可以继续写。

在上图中，checkpoint 位置从 CP 推进到 CP’，就需要将两个点之间的日志（浅绿色部分）所对应的所有脏页都flush到磁盘上。之后，图中write pos到CP’之间就是可以再写入到redo log的区域。

场景2-内存不足

假设A的记忆最多能记10件事情，现在已经记下了7件事情，现在又来了5件事情需要记录，由于记忆不到那么多，就需要把之前记下的事情先写入日记本。这个记忆就相当于内存，记忆不下那么多，相当于内存不够。

当需要新的内存页，而内存不够用的时候，就要淘汰一些数据页，空出内存给别的数据页使用。如果淘汰的是“脏页”，就要先将脏页写到磁盘。

场景3-空闲状态

MySQL认为系统空闲的时候，或者即使MySQL比较忙，但是也会忙中偷闲，只要找到机会就刷一点脏页。

场景4-关闭

MySQL 正常关闭的情况。这时候，MySQL会把内存的脏页都 flush 到磁盘上，这样下次 MySQL 启动的时候，就可以直接从磁盘上读数据，启动速度会很快。

bin log

binlog 是逻辑日志，记录内容是语句的原始逻辑，类似于“给 ID=2 这一行的 c 字段加 1”，属于MySQL Server 层。

可以说MySQL数据库的数据备份、主备、主主、主从都离不开binlog，需要依靠binlog来同步数据，保证数据一致性。

bin log三种格式

binlog 日志有三种格式，可以通过binlog_format参数指定。

• statement
• row
• mixed

statement

指定statement，记录的内容是SQL语句原文，比如执行一条update T set update_time=now() where id=1，但是这个存在一个问题，由于statement格式下，记录到binlog里的语句原文，因此就可能出现主从库执行SQL语句的时候使用索引不一样，或者使用时间函数等。

row

row 格式的 binlog 里没有了 SQL 语句的原文，而是替换成了两个 event：Table_map 和 Delete_rows。

• Table_map event，用于说明接下来要操作的表是 test 库的表 t;
• Delete_rows event，用于定义删除的行为。

相当于这个row格式的binlog做了两件事情：

1. 记录操作哪张表；
2. 记录对此表哪行数据进行了什么样的操作。

mixed

首先我们要分析一下statement格式和row格式的优缺点：

• 因为有些 statement 格式的 binlog 可能会导致主备不一致，所以要使用 row 格式。
• 但 row 格式的缺点是，很占空间。比如你用一个 delete 语句删掉 10 万行数据，用 statement 的话就是一个 SQL 语句被记录到 binlog 中，占用几十个字节的空间。但如果用 row 格式的 binlog，就要把这 10 万条记录都写到 binlog 中。这样做，不仅会占用更大的空间，同时写 binlog 也要耗费 IO 资源，影响执行速度。

所以，MySQL 就取了个折中方案，也就是有了 mixed 格式的 binlog。mixed 格式的意思是，MySQL 自己会判断这条 SQL 语句是否可能引起主备不一致，如果有可能，就用 row 格式，否则就用 statement 格式。也就是说，mixed 格式可以利用 statment 格式的优点，同时又避免了数据不一致的风险。

bin log 和redo log区别

• redo log 是 InnoDB 引擎特有的；binlog 是 MySQL 的 Server 层实现的，所有引擎都可以使用。
• redo log 是物理日志，记录的是“在某个数据页上做了什么修改”；binlog 是逻辑日志，记录的是这个语句的原始逻辑，比如“给 ID=2 这一行的 c 字段加 1 ”。
• redo log 是循环写的，空间固定会用完；binlog 是可以追加写入的。“追加写”是指 binlog 文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。

undo log

我们知道如果想要保证事务的原子性，就需要在异常发生时，对已经执行的操作进行回滚，在 MySQL 中，恢复机制是通过 回滚日志（undo log） 实现的，所有事务进行的修改都会先记录到这个回滚日志中，然后再执行相关的操作。如果执行过程中遇到异常的话，我们直接利用回滚日志（undo log）中的信息将数据回滚到修改之前的样子即可！

MySQL的事务隔离

基本事务的概念

事务的ACID（Atomicity、Consistency、Isolation、Durability，即原子性、一致性、隔离性、持久性）

当数据库上有多个事务同时执行的时候，就可能出现脏读（dirty read）、不可重复读（non-repeatable read）、幻读（phantom read）的问题。

• 脏读：是指事务读取到其他事务未提交的数据。
• 不可重复读：是指同一次事务前后查询不一致的问题。
• 幻读：一次事务中前后数据量发生变化，产生不可预料的问题。

不可重复读是和删除、修改有关的，幻读是和插入有关的。

MySQL的ACID

• 原子性：undo log
• 持久性：redo log
• 隔离性
  • 写写操作：锁
  • 写读操作：MVCC

MySQL事务启动方式

MySQL 的事务启动方式有以下两种：

1. 显式启动事务语句， begin 或 start transaction。配套的提交语句是 commit，回滚语句是 rollback。
2. set autocommit=0，这个命令会将这个线程的自动提交关掉。意味着如果你只执行一个 select 语句，这个事务就启动了，而且并不会自动提交。这个事务持续存在直到你主动执行 commit 或 rollback 语句，或者断开连接。

MySQL的四种隔离级别

SQL 标准的事务隔离级别包括：读未提交（read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（serializable ）。

• 读未提交是指，一个事务还没提交时，它做的变更就能被别的事务看到。
• 读提交是指，一个事务提交之后，它做的变更才会被其他事务看到。
• 可重复读是指，一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的。当然在可重复读隔离级别下，未提交变更对其他事务也是不可见的。
• 串行化，顾名思义是对于同一行记录，“写”会加“写锁”，“读”会加“读锁”。当出现读写锁冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行

下面以两个事务对某一行数据操作为例：

事务A	事务B
启动事务，查询得到值1	启动事务
	查询得到值1
	将1改成2
查询得到值V1
	提交事务
查询得到值V2
提交事务A
查询得到值V3

• 隔离级别为读未提交，由于该隔离机制的特点是事务所做的变更能被其他事务看到，所以V1、V2、V3 都是 2。
• 隔离级别为读提交，也就是事务做的变更只能提交后其他事务才能看到，所以事务B提交前，事务A看不到变更，V1就为1，而事务B提交后，事务A就能看到了，因此V2、V3都是2。
• 隔离级别是可重复读，则 V1、V2 是 1，V3 是 2。之所以 V2 还是 1，遵循的就是这个要求：事务在执行期间看到的数据前后必须是一致的。
• 隔离级别是串行化，由于事务B的修改操作会进行加锁，所以事务B必须等事务A提交之后才能继续执行，因此V1、V2 值是 1，V3 的值是 2。

事务隔离的实现（MVCC Multi-Version Concurrency Control 多版本并发控制）

在MySQL中，实际上每条记录在更新的时候都会同时记录一条回滚操作。记录上的最新值，通过回滚操作，都可以得到前一个状态的值。

假设一个值1被按顺序改成了2、3、4，在回滚日志中就会类似下面的记录：

当前值为4，但是不同时刻启动的事务会有不同的read-view，视图A、B、C记录的值是1、2、4，同一条记录在系统中可以存在多个版本，就是数据库的多版本并发控制（MVCC）。

回滚日志如何删除呢？

上述的回滚日志相当于记录了数据的变动日志，但这个变动日志不可能一直保留。系统会判断，当没有事务再需要用到这些回滚日志时，回滚日志会被删除。

什么时候不需要呢？

系统里面没有比这个回滚日志更早的read-view的时候。

避免使用长事务

长事务意味着系统里面存在很老的事务视图，由于这些事务随时可能访问数据库里面的任何数据，所以这个事务提交之前，数据库里面它可能用到的回滚记录都会保存，就会导致大量的内存占用。

MVCC达到了什么目的

MVCC在MySQL InnoDB中的实现主要是为了提高数据库的并发性能，用更好的方式去处理读-写或写-读之间的冲突，也能做到不加锁，非阻塞并发读，提高了数据库并发读写的性能。

MVCC中的快照

在可重复读隔离级别下，事务在启动的时候就对整个库拍了个照片。

InnoDB 里面每个事务有一个唯一的事务 ID，叫作 transaction id。它是在事务开始的时候向 InnoDB 的事务系统申请的，是按申请顺序严格递增的。

而每行数据也都是有多个版本的。每次事务更新数据的时候，都会生成一个新的数据版本，并且把 transaction id 赋值给这个数据版本的事务 ID，记为 row trx_id。同时，旧的数据版本要保留，并且在新的数据版本中，能够有信息可以直接拿到它。

相当于一行数据，在不同事务下有对应的版本，每个版本有自己的row_trx_id。

目前k=22，它是被transaction id为25的事务由11改变成22，因此它的row_trx_id为25。

其实这个就是我们的undo log，而 V1、V2、V3 并不是物理上真实存在的，而是每次需要的时候根据当前版本和 undo log 计算出来的。如果我们要得到V2，就是通过 V4 依次执行 U3、U2 算出来。

按照可重复读的定义，一个事务启动的时候，能够看到所有已经提交的事务结果。但是之后，这个事务执行期间，其他事务的更新对它不可见。

因此在启动的那一刻，事务只认在启动时刻之前的数据版本，对于超出启动时刻的数据版本，就需要通过undo log找到所认可的上一个版本。

当然，如果“上一个版本”也不可见，那就得继续往前找。还有，如果是这个事务自己更新的数据，它自己还是要认的。

一致性视图

在实现上， InnoDB 为每个事务构造了一个数组，用来保存这个事务启动瞬间，当前正在“活跃”的所有事务 ID。“活跃”指的就是，启动了但还没提交。

数组里面事务 ID 的最小值记为低水位，当前系统里面已经创建过的事务 ID 的最大值加 1 记为高水位。

这个视图数组和高水位，就组成了当前事务的一致性视图（read-view）。

这样，对于当前事务的启动瞬间来说，一个数据版本的 row trx_id，有以下几种可能：

• 如果落在红色区域，表示这个版本是将来事务创建的，肯定是不可见的。

• 如果落在绿色区域，表示这个版本是之前事务创建的，肯定是可见的。

• 如果落在黄色区域，存在以下两个情况：

  • 若 row trx_id 在数组中，表示这个版本是由还没提交的事务生成的，不可见；

  • 若 row trx_id 不在数组中，表示这个版本是已经提交了的事务生成的，可见。

row trx_id 在数组说明这个事务启动那一刻， row trx_id 所对应的事务是启动了但没有提交，该事务对该数据版本不可见。

一个数据版本，对于一个事务视图来说，除了自己的更新总是可见以外，有三种情况：

• 版本未提交，不可见；

• 版本已提交，但是是在视图创建后提交的，不可见；

• 版本已提交，而且是在视图创建前提交的，可见。

MySQL索引

索引基本概念

索引是一种用于快速查询和检索数据的数据结构，其本质可以看成是一种排序好的数据结构。索引的作用就相当于书的目录。

索引模型（有好几个，记住几个经典就行了）

哈希表

哈希表是通过key和value存储的数据结构，只需要key就能找到与之对应的value。哈希的实现是把value存储在数组中，通过hash函数确定key的对应位置，然后取出对应位置的value。

哈希表的话存在一个问题：当数据越来越多的时候，必然存在多个key通过hash函数映射到同一个位置。处理的方式就是在数组的基础上，进行链表的扩展，如图所示：

但这样仍存在一个问题，由于我们的key并非是有序的，如果我们需要查询某个区间的数值，就需要去遍历整个key了，因此哈希表比较适用于等值查询。

有序数组

为了解决上述的范围查询问题，我们可以引入有序数组。有序数组使得key和value一一对应，同时让key顺序递增。这时候如果我们要查询

某个具体的数值，就可以通过二分法来查询。同时对二分法查询进行适当扩展，就可以实现范围查询。

单从查询效率来说，有序数组可能是最好的数据结构。但是数组都有一个通病：修改操作成本太高。

所以，有序数组索引只适用于静态存储引擎，比如你要保存的是 2017 年某个城市的所有人口信息，这类不会再修改的数据。

搜索树

这个就是典型的二叉树或者多叉树，二叉树是搜索效率最高的，但是实际上大多数的数据库存储却并不使用二叉树。其原因是，索引不止存在内存中，还要写到磁盘上。

100万的数据，如果用二叉树来存储，需要树高20。那么意味着一次查询，可能会访问到20个节点，那么如果要在100万的数据进行查找其中一个节点，适用二叉树是很慢的。

为了让一个查询尽量少地读磁盘，就必须让查询过程访问尽量少的数据块。那么，我们就不应该使用二叉树，而是要使用“N 叉”树。这里，“N 叉”树中的“N”取决于数据块的大小。

N 叉树由于在读写上的性能优点，以及适配磁盘的访问模式，已经被广泛应用在数据库引擎中了。

索引的基本类型

按照底层存储方式角度划分：

• 聚簇索引（聚集索引）：索引结构和数据一起存放的索引，InnoDB 中的主键索引就属于聚簇索引。
• 非聚簇索引（非聚集索引）：索引结构和数据分开存放的索引，二级索引(辅助索引)就属于非聚簇索引。MySQL 的 MyISAM 引擎，不管主键还是非主键，使用的都是非聚簇索引。

按照应用维度划分：

• 主键索引：加速查询 + 列值唯一（不可以有 NULL）+ 表中只有一个。
• 普通索引：仅加速查询。
• 唯一索引：加速查询 + 列值唯一（可以有 NULL）。
• 覆盖索引：一个索引包含（或者说覆盖）所有需要查询的字段的值。
• 联合索引：多列值组成一个索引，专门用于组合搜索，其效率大于索引合并。
• 全文索引：对文本的内容进行分词，进行搜索。目前只有 CHAR、VARCHAR ，TEXT 列上可以创建全文索引。一般不会使用，效率较低，通常使用搜索引擎如 ElasticSearch 代替。

索引的优缺点

优点 ：

• 使用索引可以大大加快数据的检索速度（大大减少检索的数据量）, 这也是创建索引的最主要的原因。
• 通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。

缺点 ：

• 创建索引和维护索引需要耗费许多时间。当对表中的数据进行增删改的时候，如果数据有索引，那么索引也需要动态的修改，会降低 SQL 执行效率。
• 索引需要使用物理文件存储，也会耗费一定空间。

但是，使用索引一定能提高查询性能吗?

大多数情况下，索引查询都是比全表扫描要快的。但是如果数据库的数据量不大，那么使用索引也不一定能够带来很大提升。

InnoDB的索引模型（主键索引和非主键索引的区别）

假设，我们有一个主键列为 ID 的表，表中有字段 k，并且在 k 上有索引。

mysql> create table T(
id int primary key, 
k int not null, 
name varchar(16),
index (k))engine=InnoDB;

现有5行数据对应如下：

100 1 k1 200 2 k2 300 3 k3 500 5 k5 600 6 k6

那么这5行数据对应索引树如下：

可以看到索引类型分为主键索引和非主键索引.

主键索引的叶子节点存的是整行数据。在 InnoDB 里，主键索引也被称为聚簇索引（clustered index）。

非主键索引的叶子节点内容是主键的值。在 InnoDB 里，非主键索引也被称为二级索引（secondary index）。

再来思考一下基于主键索引的查询和基于非主键索引查询的区别。

主键索引的查询：

select * from T where ID=500

主键索引的查询是只有查询ID这棵B+树。

非主键索引的查询：

select * from T where k=5

非主键索引的查询是先去查询K索引树，得到ID的值为500，再把这个ID为500去ID索引树再查询一次。这个过程成为回表。

基于非主键索引的查询需要多扫描一棵索引树。因此，我们在应用中应该尽量使用主键查询。

索引的维护（为什么要尽量设定一个主键）

B+ 树为了维护索引有序性，在插入新值的时候需要做必要的维护。比如插入的数据在中间就需要逻辑移动，如果某数据行所在数据页满了，这时候需要申请一个新的数据页，然后挪动部分数据过去。这个过程称为页分裂。在这种情况下，性能自然会受影响。

一般情况下我们建表会设置一个自增主键。

这样插入新记录的时候可以不指定ID的值，系统会获取当前ID最大值加1作为下一条记录的ID值。

这样做有两个好处：

• 性能方面：由于我们每次插入的ID都是递增的，这样就不会导致主键索引发生叶子节点的分裂。
• 存储方面：主键长度越小，普通索引的叶子节点就越小，普通索引占用的空间就越小。

所以，从性能和存储空间方面考量，自增主键往往是更合理的选择。

覆盖索引

由于非主键索引会涉及到回表操作，覆盖索引就是把要查询出的列和索引是对应的，不做回表操作！

select * from T where k between 3 and 5

这条 SQL 查询语句的执行流程：

1. 在 k 索引树上找到 k=3 的记录，取得 ID = 300；
2. 再到 ID 索引树查到 ID=300 对应的 R3；
3. 在 k 索引树取下一个值 k=5，取得 ID=500；
4. 再回到 ID 索引树查到 ID=500 对应的 R4；
5. 在 k 索引树取下一个值 k=6，不满足条件，循环结束。

select ID from T where k between 3 and 5

这时只需要查ID的值，而ID的值已经在K索引树上了，因此可以直接得到结果，不需要发生回表。在这个查询里面，索引 k 已经“覆盖了”我们的查询需求，我们称为覆盖索引。

由于覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段。

联合索引（最左匹配原则）

使用表中的多个字段创建索引，就是 联合索引，也叫 组合索引 或 复合索引。

在建立联合索引的时候，如何安排索引内的字段顺序

比如我们已经建立了（name，age）的联合索引，如果要单独查询name，就不必为name单独建立一个索引了，

因为这条查询语句是可以通过最左前缀原则走（name，age）这条联合索引的。

所以我们的原则就是：索引的复用能力。如果通过调整顺序，可以少维护一个索引，那么这个顺序就是需要优先考虑的。

那么，如果既有联合查询，又有基于 a、b 各自的查询呢？查询条件里面只有 b 的语句，是无法使用 (a,b) 这个联合索引的，这时候你不得不维护另外一个索引，也就是说你需要同时维护 (a,b)、(b) 这两个索引。

name和age字段，一般来说age字段要小一点，所以我们就需要创建（name，age）和（age）这两个索引，这样无论是联合查询还是name单独查询或者age单独查询都能走到索引。

索引下推

索引下推（Index Condition Pushdown） 是 MySQL 5.6 版本中提供的一项索引优化功能，可以在非聚簇索引遍历过程中，对索引中包含的字段先做判断，过滤掉不符合条件的记录，减少回表次数。

select * from tuser where name like '张 %' and age=10 and ismale=1;

无索引下推：

有索引下推：

可以发现，在第一张图中，InnoDB引擎是不会去看age字段的数值的，找到符合条件的name，就会去回表。而第二张图中，对于age字段数值不为10的就直接不进行回表，这样减少了回表的次数，提高了查询的性能。

普通索引和唯一索引

普通索引：就是在创建索引时，不附加任何限制条件(唯一、非空等限制)。该类型的索引可以创建在任何数据类型的字段上。

唯一索引：就是在创建索引时，限制索引的值必须是唯一的。通过该类型的索引可以更快速地查询某条记录。

两者查询的区别

• 普通索引查找到第一个满足条件的记录后，继续向后遍历，直到第一个不满足条件的记录 。
• 唯一索引由于索引定义了唯一性，查找到第一个满足条件的记录后，直接停止继续检索。

查询性能

InnoDB 的数据是按数据页为单位来读写的。也就是说，当需要读一条记录的时候，并不是将这个记录本身从磁盘读出来，而是以页为单位，将其整体读入内存。在 InnoDB 中，每个数据页的大小默认是 16KB。

由于引擎是按页读写的，那么查找到的记录所在的数据页是在内存中的。普通索引需要额外做的事情，就是找到下一个不满足条件的记录，而一般来说下一个不满足条件的记录也是在该数据页中。

如果这个记录刚好是这个数据页的最后一个记录，那么要取下一个记录，必须读取下一个数据页，这个操作会稍微复杂一些。

所以两者查询性能差距不大。

更新性能

下面我们来分析一下两者在更新语句的差异：

1、如果更新的目标页在内存中：

• 对于唯一索引来说，找到对应的位置，判断到没有冲突，插入这个值，语句执行结束；
• 对于普通索引来说，找到对应的位置，插入这个值，语句执行结束。

如果更新的目标页在内存中，比较下来，唯一索引只是多了一个判断，只会耗费微小的CPU时间。

2、如果更新的目标页不在内存中：

• 对于唯一索引来说，需要将数据页读入内存，判断到没有冲突，插入这个值，语句执行结束；
• 对于普通索引来说，则是将更新记录在 change buffer，语句执行就结束了。

将数据从磁盘读入内存涉及随机 IO 的访问，是数据库里面成本最高的操作之一。change buffer 因为减少了随机磁盘访问，所以对更新性能的提升是会很明显的。

为什么唯一索引不使用change buffer

对于唯一索引来说，所有的更新操作之前都会进行一次唯一性的判断，而这个判断就需要把数据页读入内存，既然数据页已经读入内存了，那么直接更新内存就行了，就不需要使用change buffer。

字符串如何索引

1. 直接创建完整索引，这样可能比较占用空间；

2. 创建前缀索引，节省空间，但会增加查询扫描次数，并且不能使用覆盖索引；因为前缀相同的可能会有多个。

   如何确定前缀索引的长度

   索引最关键的就是区分度，区分度越高，重复的键值就越少

   我们可以通过distinct来算出这个列上有多少个不同的值，然后根据可以接受的损失区分度来预先设定一个可以接受的损失比例，最后通过前缀索引进行截取，找到适合长度的索引。

   比如现在有100个邮箱，我能接受的损失是5%，那么我就需要找到一个适合的索引长度来区别至少95个邮箱。

3. 倒序存储，再创建前缀索引，用于绕过字符串本身前缀的区分度不够的问题；

4. 创建 hash 字段索引，查询性能稳定，有额外的存储和计算消耗，跟第三种方式一样，都不支持范围扫描。

索引失效案例

条件字段函数操作

select count(*) from tradelog where month(t_modified)=7;

隐式类型转换

select * from tradelog where tradeid=110717;

交易编号tradeid上是存在索引的，但是通过explain命令分析出来这条SQL语句是没有走索引的。由于tradeid 的字段类型是 varchar(32)，而输入的参数却是整型，所以需要做类型转换。

隐私字符编码转换

两个表连表的时候，对应字段编码不同，也会发生转换，导致索引失效。

避免使用or语句

避免使用not in

加了索引一定会更快吗

不一定，MySQL会自行判断是否走索引，SQL语句执行过程中，会由优化器来决定具体执行哪项计划。

B树和B+树的区别（为什么选择B+树）

	B树	B+树
存储结构	B树叶子节点和非叶子节点都会存储数据	B+树只有叶子节点存储数据，非叶子节点存储索引值
查询速度	较大	较小，一般只需要三次I/O即可
查询稳定性	B树不够稳定，有些可能临近根节点就会查询到	B+树相对稳定

MySQL锁

全局锁

全局锁就是对整个数据库实例加锁。当你需要让整个库处于只读状态的时候，就可以使用Flush tables with read lock(FTWRL)，之后无论是数据更新语句（数据的增删改）、数据定义语句（建表、修改表结构）和更新类事务的提交语句都会被阻塞。

全局锁的典型使用场景是：做全库逻辑备份。

通过 FTWRL 确保不会有其他线程对数据库做更新,也就是在备份过程中让整个库完全处于只读状态，然后对整个库做备份。

但是让整个库只读存在两个问题：

• 如果在主库做备份，那么在备份期间都不能执行更新，业务基本上就得停摆。
• 如果在从库做备份，那么从库就不能执行主库同步过来的binlog，会导致主从延迟。

表级锁

表级锁分为两种：

• 表锁
• 元数据锁（meta data lock MDL）

表锁

如果某个线程A执行lock tables t1 read, t2 write;,相当于线程A给t1加了读锁，给t2加了写锁。则其他线程写 t1、读写 t2 的语句都会被阻塞。同时，线程 A 在执行 unlock tables 之前，也只能执行读 t1、读写 t2 的操作。连写 t1 都不允许，自然也不能访问其他表。

元数据锁

MDL 不需要显式使用，在访问一个表的时候会被自动加上。MDL 的作用是，保证读写的正确性。如果此刻线程A正在查询表T，而执行期间线程B对表T结构做变更，那么线程A拿到的结果跟表结构对不上。

因此，在 MySQL 5.5 版本中引入了 MDL，当对一个表做增删改查操作的时候，加 MDL 读锁；当要对表做结构变更操作的时候，加 MDL 写锁。

• 读锁之间不互斥，因此可以有多个线程同时对一张表增删改查。
• 读写锁之间、写锁之间是互斥的，用来保证变更表结构操作的安全性。因此，如果有两个线程要同时给一个表加字段，其中一个要等另一个执行完才能开始执行。

行锁

MySQL 的行锁是在引擎层由各个引擎自己实现的。行锁就是针对数据表中记录的锁。比如事务A更新了一行，而这时候事务B也要更新一行，则必须等事务 A 的操作完成后才能进行更新。

两阶段锁

在下面的操作序列中，事务B的update语句执行会发生什么?

事务A在开始的时候，是不会持有id=1和id=2这两条数据行的行锁，只有当执行两条update语句的时候，行锁才会加上。那么事务B由于id=1的行锁现在被事务A持有，所以事务B的update语句会被阻塞，直到事务 A 执行 commit 之后，事务 B 才能继续执行。

在 InnoDB 事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放。这个就是两阶段锁协议。

通过上面例子我们要注意一个细节：如果你的事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放。

死锁问题

当并发系统中不同线程出现循环资源依赖，涉及的线程都在等待别的线程释放资源时，就会导致这几个线程都进入无限等待的状态，称为死锁。

这时候，事务 A 在等待事务 B 释放 id=2 的行锁，而事务 B 在等待事务 A 释放 id=1 的行锁。 事务 A 和事务 B 在互相等待对方的资源释放，就是进入了死锁状态。当出现死锁以后，有两种策略：

• 一种策略是，直接进入等待，直到超时。这个超时时间可以通过参数 innodb_lock_wait_timeout 来设置。
• 另一种策略是，发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数 innodb_deadlock_detect 设置为 on，表示开启这个逻辑。

对于多个事务同时操作一行数据的情况

每个新来的被堵住的线程，都要判断会不会由于自己的加入导致了死锁，这是一个时间复杂度是 O(n) 的操作。假设有 1000 个并发线程要同时更新同一行，那么死锁检测操作就是 100 万（1000 * 1000）这个量级的。虽然最终检测的结果是没有死锁，但是这期间要消耗大量的 CPU 资源。因此，你就会看到 CPU 利用率很高，但是每秒却执行不了几个事务。

如何解决热点行更新导致的性能问题

主要是降低死锁检测耗费的CPU资源

• 如果确保业务一定不会出现死锁，可以临时把死锁检测关掉。
• 控制并发度，比如同一行同时最多只有10个线程在更新。

间隙锁（幻读）

幻读是针对insert导致的数据不一致。

幻读存在的问题

CREATE TABLE `t` (
	`id` INT ( 11 ) NOT NULL,
	`c` INT ( 11 ) DEFAULT NULL,
	`d` INT ( 11 ) DEFAULT NULL,
PRIMARY KEY ( `id` ),
KEY `c` ( `c` )) ENGINE = INNODB;

语义问题

session A在T1时刻的声明了：“d=5的数据行锁住，其他事务不能进行读写操作”。

由于session A只锁住了id = 5 一行数据，session B在T2时刻把id = 0的数据进行修改，修改后id = 0的数据d为5，接着第二条语句对id = 0的数据又进行修改，但是此刻这个d为5数据应该是被锁住的，但是能够进行修改，就破环了加锁声明。

数据一致性问题

锁的设计是为了保证数据的一致性：

• 数据库内部状态在此刻的一致性。
• 数据和日志逻辑上的一致性。

现在有如下的场景（假设只对命中数据行加锁）：

• T1时刻后，id = 5 这一行数据变为（5，5，100），最终结果是在T6时刻进行提交。
• T2时刻后，id = 5 这一行数据变为 （0，5，5）。
• T4时刻后，数据库里面多了一行数据（1，5，5）

现在我们来看一下binlog里面的内容：

• T2 时刻，session B 事务提交，写入了两条语句；
• T4 时刻，session C 事务提交，写入了两条语句；
• T6 时刻，session A 事务提交，写入了 update t set d=100 where d=5 这条语句。

// B事务
update t set d=5 where id=0; /*(0,0,5)*/
update t set c=5 where id=0; /*(0,5,5)*/

// C事务
insert into t values(1,1,5); /*(1,1,5)*/
update t set c=5 where id=1; /*(1,5,5)*/

// A事务
update t set d=100 where d=5;/*所有d=5的行，d改成100*/

如果我们拿这份binlog去备份或克隆，那么最终这三行结果都变成了(0,5,100)、(1,5,100) 和 (5,5,100)。也就是说，id=0 和 id=1 这两行，发生了数据不一致。

其实这个数据不一致的问题就是因为我们所假设的只对id=5这一行数据进行加锁。

现在我们把扫描过程中碰到的行，都加上写锁，再来分析上诉场景：

session A由于是全表扫描（字段d上没有索引），所以session B在执行第一个update语句的时候就被锁住了，需要等到T6时刻session A提交之后，session B才能继续执行。

下面我们来看一下binlog里面记录的：

// 事务C
insert into t values(1,1,5); /*(1,1,5)*/
update t set c=5 where id=1; /*(1,5,5)*/

// 事务A
update t set d=100 where d=5;/*所有d=5的行，d改成100*/

// 事务B
update t set d=5 where id=0; /*(0,0,5)*/
update t set c=5 where id=0; /*(0,5,5)*/

可以看到id=0这一行数据最终结果为（0，5，5），所以这一行数据的问题解决了。但是id = 1这一行数据，在数据库里面的结果是 （1，5，5），但是使用redo log最终执行出来结果为（1，5，100）。问题仍然没有解决，这是因为新插入的数据是没有加锁的。

行锁只能锁住行，但是新插入记录这个动作，要更新的是记录之间的“间隙”。因此，为了解决幻读问题，InnoDB 只好引入新的锁，也就是间隙锁 (Gap Lock)。

间隙锁

间隙锁： 锁的就是两个值之间的空隙。比如我们往表t中插入6个数据，就会产生7个间隙。

insert into t values(0,0,0),(5,5,5),(10,10,10),(15,15,15),(20,20,20),(25,25,25);

这样当我们执行select * from t where d = 5 for update的时候，就不止是给数据库中已经初始化的6条数据，还给这6条数据所在范围加上了7个间隙锁，这样就确保了无法再插入新的记录。

在一行行扫描的过程中，不仅将给行加上了行锁，还给行两边的空隙，也加上了间隙锁。

现在我们来对比一下行锁和间隙锁：

行锁分为读锁和写锁，两种类型锁的冲突关系如下：

	读锁	写锁
读锁	兼容	冲突
写锁	冲突	冲突

也就是说跟行锁冲突的是“另一个行锁”。

对于间隙锁而言，跟间隙锁存在冲突关系的是“往这个间隙中插入一个记录”这个操作，间隙锁之间是不存在冲突关系。

这里 session B 并不会被堵住。因为表 t 里并没有 c=7 这个记录，因此 session A 加的是间隙锁 (5,10)。而 session B 也是在这个间隙加的间隙锁。它们有共同的目标，即：保护这个间隙，不允许插入值。但，它们之间是不冲突的。

间隙锁和行锁合称 next-key lock，每个 next-key lock 是前开后闭区间。也就是说，我们的表 t 初始化以后，如果用 select * from t for update 要把整个表所有记录锁起来，就形成了 7 个 next-key lock，分别是 (-∞,0]、(0,5]、(5,10]、(10,15]、(15,20]、(20, 25]、(25, +supremum]。

supremum是InnoDB给每个索引加了一个不存在的最大值。

但是间隙锁和next-key lock的引入虽然解决了幻读的问题，但同时也带来一些“困难”。

• session A 执行 select … for update 语句，由于 id=9 这一行并不存在，因此会加上间隙锁 (5,10);

• session B 执行 select … for update 语句，同样会加上间隙锁 (5,10)，间隙锁之间不会冲突，因此这个语句可以执行成功；

• session B 试图插入一行 (9,9,9)，被 session A 的间隙锁挡住了，只好进入等待；

• session A 试图插入一行 (9,9,9)，被 session B 的间隙锁挡住了。

至此，两个 session 进入互相等待状态，形成死锁。当然，InnoDB 的死锁检测马上就发现了这对死锁关系，让 session A 的 insert 语句报错返回了。

由于间隙锁的引入，可能会导致同样的语句锁住更大的范围，这其实是影响了并发度的。

MySQL自增主键一定连续吗

由于自增主键可以让主键索引尽量地保持递增顺序插入，避免了页分裂，因此索引更紧凑。但是自增主键不能保证连续递增，自增主键是会出现“空洞”。

• 唯一键冲突导致自增主键 id 不连续。

  自增值修改的这个操作，是在真正执行插入数据的操作之前。

• 事务回滚

• 批量插入数据

MySQL数据删除流程

删除流程

删除一条记录

对于上图，假设我们删除R4这条记录，InnoDB引擎只会把R4这个记录标记为了删除，如果有一条新的记录(ID在300和600之间)，就会复用这个位置。相当于删除只是做逻辑删除，并没有进行物理删除。因此磁盘文件的大小并不会缩小。

删除整个数据页

上述情况是针对删除一条记录，如果删除Page A这个数据页的话，整个数据页还是可以被复用的，但是复用的情况和单条记录复用情况有所不同。

• 单条记录的复用，只限于符合范围条件的数据。比如上图删除了R4这条数据，那么该位置的复用只对ID在300-600的数据才能进行复用。
• 数据页的复用，是可以复用到任何位置。比如上图删除Page A这个数据页，那么这个数据页是可以复用到任何位置的，假设插入一条ID=50的数据，这个数据页是可以被该新数据复用的。

如果相邻的两个数据页利用率都很小，系统就会把这两个页上的数据合到其中一个页上，另外一个数据页就被标记为可复用。

删除整个表

如果使用delete命令把整个表的数据删除了，所有的数据页都会被标记为可复用。但是磁盘的文件大小不会变小。

delete命令其实只是把位置或者数据页标记为可复用，但是磁盘的大小是不会变化的。**也就是说通过delete命令是不能回收表的空间。**所以会出现即使删除大表一半的数据，磁盘的空间仍然没有变小。

插入数据造成空洞

如果数据是按照索引递增顺序插入的，那么索引是紧凑的。但如果数据是随机插入的，就可能造成索引的数据页分裂。

如上图所示，Page A数据页已经满了，如果此时再插入一条ID=550的数据，就会导致数据页分裂。分裂完成后，Page A的末尾就留下了空洞。

另外，更新索引上的值，可以理解为删除一个旧的值，再插入一个新值。不难理解，这也是会造成空洞的。

经过大量增删改的表，都是可能是存在空洞的。所以，如果能够把这些空洞去掉，就能达到收缩表空间的目的。

这种空洞的情况可以通过重建表的方式来让数据页的利用率更高（按照按照主键ID递增的顺序，把数据一行一行从表A（旧表）读出来再插入表B（新表）中）

MySQL慢日志查询

查询慢日志

show global variables like '%quer%';

开启慢查询日志

set global slow_query_log=on;

设置慢查询阈值

set global long_query_time=1;

分析语句

expain可以查看是否走了索引

expain的属性

• rows：扫描的行数
• key：实际选择的索引

代码层面

mybatis配置慢查询日志，配合logback来使用。