MySQL学习笔记

查询语句执行流程

select * from table where id = 10;

一条语句的执行步骤：

1. 客户端发起语句查询；
2. 连接器进行链接管理，权限相关验证；
3. 查看查询缓存是否能命中，命中则直接返回数据信息；
4. 分析器对语句进行词法分析，语法分析；
5. 进入优化器处理，生成执行计划，索引选择；
6. 执行器进行引擎操作，返回结果数据。

总的来说，MySQL可以分为Server层和存储引擎层。
Server层包括连接器，查询缓存，分析器，优化器，执行器等。
存储引擎负责数据的存储和提取。支持InnoDB,MyISAM,Memory等存储引擎。

MySQL5.5.5版本以后，默认存储引擎是InnoDB，可以在create table语句中使用enengine=memory来指定使用内存引擎创建表。

连接器

• 负责和客户端建立连接，获取权限，维持和管理连接。

//连接命令
mysql -h<ip> -P<port> -u<user> -p<password>

• 客户端连接成功之后，没有采取任何操作的话，连接会处于一个空闲状态。可以通过show processlist命令查看到，command字段列显示为sleep。
• 客户端如果长时间没有进行任何操作，连接器就会自动进行断开。这个时间通过参数wait_timeout控制，默认8个小时。
• 数据库中，长连接是指连接成功后，客户端持续有请求，则一直会使用同一个连接。短连接是指每次执行完几次查询就断开连接，下次查询在重新创建一个连接。
• 在使用中，尽量减少建立连接的动作，也就是尽量使用长连接。
• 长连接使用过多的话，会出现MySQL占用内存涨的特别快，是因为MySQL在执行过程中临时使用的内存是管理在连接对象里面的。这些资源只有在连接断开时才会释放。

怎么解决长连接使用过多导致内存占用太高？

1. 定期断开长连接。
2. MySQL5.7或更新版本，可以执行mysql_reset_connection来重新初始化连接资源。这个过程不需要重连以及重新权限认证，会恢复到刚创建完连接的状态。

查询缓存

• 在建立连接成功后，就可以执行select语句了。执行逻辑就会进入查询缓存。MySQL在拿到一个查询请求后，会先到查询缓存看看，之前有没有执行过这条语句。之前执行过的语句会按照key-value对的形式存储在内存中。key是查询的语句，value是查询的结果。如果查询语句能够命中，则会直接返回结果。如果语句没有命中，就会继续执行后面的阶段。执行完成后，会将执行结果写入查询缓存中。
• 大多数情况下，不建议使用查询缓存。为什么呢？
  查询缓存的失效非常频繁，只要有对一个表的更新，这个表上所有的查询缓存都会被清空。因此很多情况下，缓存起来的结果还没有被使用就被清空掉了，缓存结果信息反而消耗更多时间。除非创建的表是一张静态表，很长时间才进行更新一次，这样才适合使用查询缓存。
• MySQL提供了参数query_cache_type控制查询缓存的配置开关。可以通过设置为DEMAND来控制，默认的sql查询都不会进行缓存。对于需要使用查询的sql，可以通过SQL_CACHE显示指定，比如：

select SQL_CACHE * from table where id = 10;

在MySQL8.0版本直接将查询缓存的整块功能删除掉了。

分析器

在没有命中查询缓存后，就要开始真正执行语句了。

• 分析器先会做“词法分析”。执行语句是由多个字符串和空格组成的一条SQL语句，MySQL需要识别出里面的字符串分别是什么，代表什么。MySQL可以从你输入的“select”这个关键字识别出来，这是一个查询语句。把字符串“table”识别成“表名table”，字符串“id”识别成列“列id”。
• 做完识别以后，就要做“语法分析”。根据词法分析的结果，语法分析器会根据语法规则，判断你输入的这个SQL语句是否满足MySQL语法。如果语句不对，就会收到报错信息，比如select少打了开头字母“s”。

优化器

经过分析器，MySQL就知道要做什么了。在开始执行之前，还要先经过优化器的处理。

-优化器是在表里面有多个索引的时候，决定使用哪个索引；或者在一个语句有多表关联（join）的时候，决定各个表的连接顺序。

执行器

MySQL经过分析器知道你要做什么，通过优化器知道了怎么做，于是就进入执行器阶段，开始执行语句。
开始执行的时候，要先判断一下你对这个表有没有执行查询的权限，如果没有，就会返回没有权限的错误。
如果有权限，就打开表继续执行。打开表的时候，执行器会根据表的引擎定义，去使用这个引擎提供的接口。
比如我们这个查询例子，假设id字段没有索引，执行器的执行流程：

1. 调用InnoDB引擎接口取这个表的第一行，判断id值是不是10，如果不是则跳过，如果是则将这行存在结果集中；
2. 调用引擎接口取“下一行”，重复相同的判断逻辑，知道取到这个表的最后一行；
3. 执行器将上述遍历过程中所有满足条件的行组成的记录集作为结果集返回给客户端。

对于有索引的表，执行的逻辑也差不多。第一次调用的是“取满足条件的第一行”这个接口，之后循环取“满足条件的下一行”这个接口，这些接口都是引擎中已经定义好的。

更新语句执行流程

update table set c=c+1 where id = 2

其实更新语句的流程也会按照查询语句的执行流程走一遍，先连接，查缓存，后分析，在优化，接着执行。前面我们说过，在一个表上有更新的时候，跟这个表有关的查询缓存会失效，所以这条语句会把table表上所有缓存结果清空。这也就是我们一般不建议使用查询缓存的原因。

redo log(重做日志)

在MySQL处理更新操作时，如果每一次的更新操作都需要写进磁盘，然后磁盘也要找到对应的那条记录，然后在更新，整个过程IO成本、查找成本都很高。为了解决这个问题，MySQL就引入了WAL技术，WAL的全称是Write-Ahead-Logging，它的关键点就是先写日志，再写磁盘。具体来说，当有一条记录需要更新的时候，InnoDB引擎就会先把记录写到redo log里面，并更新内存，这个时候更新就算完成了。同时，InnoDB引擎会在适当的时候，将这个操作记录更新到磁盘里面，而这个更新往往是在系统比较空闲的时候做。
InnoDB的redo log是固定大小的，比如可以配置为一组4个文件，每个文件的大小是1GB，那么总共就可以记录4GB的操作。从头开始写，写到末尾就又回到开头循环写，如下图所示：
write pos是当前记录的位置，一边写一边后移，写到第3号文件末尾后就回到0号文件开头。checkpoing是当前要擦除的位置，也是往后推移并且循环的，擦除记录前要把记录更新到数据文件。
write pos和checkpoint之间空着的部分，可以用来记录新的操作。如果write pos追上checkpoint，表示存储满了，这时候不能再执行新的更新，得停下来先擦掉一些记录，把checkpoint推进一下。
有了redo log，InnoDB就可以保证即使数据库发生异常重启，之前提交的记录都不会丢失，这个能力称为crash-safe。

binlog（归档日志）

MySQL整体来看，可以分为两大块：一块是Server层。它主要做的是MySQL功能层面的事情；还有一块是引擎层，负责存储相关的具体事宜。上面说的redo log是InnoDB引擎特有的日志，而Server层也有自己的日志，称为binlog（归档日志）。

• 为什么会有两份日志呢？
  因为最开始MySQL里并没有InnoDB引擎。MySQL自带的引擎是MyISAM，但是MyISAM没有crash-safe的能力，binlog日志只能用于归档。而InnoDB是另一个公司以插件形式引入MySQL的，既然只依靠binlog是没有crash-safe能力的，所以InnoDB使用另外一套日志系统–也就是redo log来实现crash-safe能力。

• 两种日志的区别？

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

经过对两个日志的概念性理解，我们再来看下执行器和InnoDB引擎在执行简单的update语句时的内部流程：

1. 执行器先找引擎取id=2这一行。id是主键，引擎直接使用树搜索找到这一行。如果id=2这一行所在的数据页本来就在内存中，就直接返回给执行器；否则，需要先从磁盘读入内存，然后在返回。
2. 执行器拿到引擎给的行数据，把这个值加上1，比如原来是N，现在就是N+1，得到新的一行数据，在调用引擎接口写入这行新数据。
3. 引擎将这行新数据更新到内存中，同时将这个更新操作记录到redo log里面，此时redo log处于prepare状态。然后告知执行器执行完成了，随时可以提交事务。
4. 执行器生成这个操作的binlog，并把binlog写入磁盘。
5. 执行器调用引擎的提交事务接口，引擎把刚刚写入的redo log改成提交（commit）状态，更新完成。

两阶段提交

在处理更新流程中，我们可能注意到，将redo log的写入拆成了两个步骤：prepare和commit，这就是两阶段提交。为什么日志需要“两阶段提交”？
这是为了让两份日志之间的逻辑一致。我们不妨用反证法来进行解释。由于redo log和binlog是两个独立的逻辑，如果不用两阶段提交，要么就是先写完redo log再写binlog，或者采用反过来的顺序。我们看看这两种方式会有什么问题。我们用前面的update语句来做例子。假设当前id=2的行，字段c的值是0，在假设执行update语句过程中在写完第一个日志后，第二个日志还没写完期间发生了crash，会出现什么情况呢？

1. 先写redo log后写binlog。假设在redo log写完，binlog还没有写完的时候，MySQL进程异常重启。由于我们前面说过的，redo log写完之后，系统即使崩溃，仍然能够把数据恢复回来，所以恢复后这一行c的值是1。但是由于binlog没写完就crash了，这时候binlog里面就没有记录这个语句。因此，之后备份日志的时候，存起来的binlog里面就没有这条语句。然后会发现，如果需要用这个binlog来恢复临时库的话，由于这个语句的binlog丢失，这个临时库就会少了这一次更新，恢复出来的这一行c的值就是0，与原库的值不同。
2. 先写binlog后写redo log。如果在binlog写完之后crash，由于redo log还没写，崩溃恢复以后这个事务无效，所以这一行c的值是0。但是binlog里面已经记录了“把c从0改成1”这个日志。所以，在之后用binlog来恢复的时候就多了一个事务出来，恢复出来的这一行c的值就是1，与原库的值不同。

FLUSH操作

前面我们说到了WAL机制。InnoDB在处理更新语句的时候，只做了更新内存并写入日志文件（redo log）这一个磁盘操作。最终还是需要把内存里的数据写入磁盘，这个过程术语就是flush。
当内存数据页跟磁盘数据页内容不一致的时候，我们称这个内存页为“脏页”。内存数据写入到磁盘后，内存和磁盘上的数据页的内容就一致了，称为“干净页”。
什么情况会引发数据库的flush过程呢？

• 第一种场景，InnoDB的redo log写满了。这时候系统会停止所有更新操作，把checkpoint往前推进，redo log留出空间可以继续写。在推进check point位置时，就会flush所需要推进区间段里存在的脏页数据到磁盘上。
• 第二种场景，系统内存不足。当需要新的内存页，而内存不够用的时候，就要淘汰一些数据页，空出内存给别的数据页使用。如果淘汰的是“脏页”，就要先将脏页写到磁盘。
• 第三种场景，MySQL认为系统“空闲”的时候。MySQL就会自动去刷掉一点“脏页”。
• 第四种场景：MySQL正常关闭的情况。这时候，MySQL会把内存的脏页都flush到磁盘上。

我们来分析这四种场景对性能的影响：
第三种情况是属于MySQL空闲时的操作，这时系统没什么压力，而第四种场景是数据库本来就要关闭了。这两种情况下，你不会太关注“性能”问题。
第一种是“redo log写满了，要flush脏页”，这种情况是InnoDB要尽量避免的。因为出现这种情况的时候，整个系统就不能再接受更新了，所有的更新都必须堵住。如果从监控上看，这时候更新数会跌为0.
第二种是“内存不够用了，要先将脏页写到磁盘”，这种情况其实是常态。InnoDB用缓冲池（buffer pool）管理内存，缓冲池中的内存页有三种状态：

• 第一种是，还没有使用的；
• 第二种是，使用了并且是干净页；
• 第三种是，使用了并且是脏页。

InnoDB的策略是尽量使用内存，因此对于一个长时间运行的库来说，未被使用的页面很少。
当要读入的数据页没有在内存的时候，就必须到缓冲池中申请一个数据页。这时候只能把最久不使用的数据页从内存中淘汰掉，如果淘汰的是一个干净页，就直接释放出来复用，但是如果是脏页，就必须将脏页先刷到磁盘，变成干净页后才能复用。

所以，刷脏页虽然是常态，但是出现以下这两种情况，都是会明显影响性能的：

1. 一个查询要淘汰的脏页个数太多，会导致查询的响应时间明显变长；
2. 日志写满，更新全部堵住，写性能跌为0，这种情况对敏感业务来说，是不能接受的。

InnoDB需要有控制脏页比例的机制，来尽量避免上面的这两种情况。
我们来看看MySQL中InnoDB脏页的控制策略，以及和这些相关的参数。
首先，需要正确的告诉InnoDB所在主机的IO能力，这样InnoDB才能知道需要全力刷脏页的时候，可以刷多快。这就要用到innodb_io_capacity这个参数了，它会告诉InnoDB你的磁盘能力。这个值建议设置成磁盘的IOPS。磁盘的IOPS可以通过fio这个工具来测试。
InnoDB的刷盘速度会考虑到两个因素：一个是脏页比例，一个是redo log写盘速度。
InnoDB会根据这两个因素先淡出算出两个数字。
参数innodb_max_dirty_pages_pct是脏页比例上限，默认值是75%。InnoDB会根据当前的脏页比例（假设为M），算出一个范围在0到100之间的数字。伪代码类似这样：

F1(M)
{
	if M >= innodb_max_dirty_pages_pct then 
		return 100;
	return 100 * M/innodb_max_dirty_pages_pct;
}

InnoDB每次写入的日志都有一个序号，当前写入的序号跟checkpoint对应的序号之间的差值，我们假设为N。InnoDB会根据这个N算出一个范围在0到100之间的数字，这个计算公式可以记为F2(N)。F2(N)算法比较复杂，只要知道N越大，算出来的值越大就好了。
然后，根据上述算得的F1(M)和F2(N)两个值，取其中较大的值记为R，之后引擎就可以按照innodb_io_capacity定义的能力乘以R%来控制刷脏页的速度。

MySQL在flush一个脏页数据时，有这么一个机制。在准备刷一个脏页的时候，如果这个数据页旁边的数据页刚好是脏页，就会把这个“邻居”也带着一起刷掉；而且这个把“邻居”拖下水的逻辑还可以继续蔓延，也就是对于每个邻居数据页，如果跟它相邻的数据页也还是脏页的话，也会被放到一起刷。在InnoDB中，innodb_flush_neighbors参数就是用来控制这个行为的，值为1的时候会有上述的“连坐”机制，值为0时表示不找邻居，自己刷自己的。
在MySQL8.0中，innodb_flush_neighbors参数的默认值已经是0了。

事务隔离

• 事务的四大特性ACID：

  • Atomicity 原子性
  • Consistency 一致性
  • Isolation 隔离性
  • Durability 持久性

• 多个事务同时执行的时候，会出现以下问题：

  • 脏读（dirty read）
  • 不可重复读（non-repeatable read）
  • 幻读（phantom read）

• 为了处理上续问题，就有了隔离级别：

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

示例：按照时间顺序执行两个事务的行为。

在不同的隔离级别下，事务A会有哪些不同的返回结果，也就是图里面的V1、V2、V3的返回值分别是什么？

• 若隔离级别是“读未提交”，则V1的返回值是2。这时候事务B虽然还没有提交，但是结果已经被A看到了。因此，V2、V3也都是2.
• 若隔离级别是“读提交”，则V1的返回值是1，V2，V3都是2。事务B的更新在提交后才能被A看到。所以，V3的值也是2.
• 若隔离级别是“可重复读”，则V1的返回值是1，V2也是1，V3是2。之所以V2还是1，遵循的就是这个要求：事务在执行期间看到的数据前后必须是一致的。
• 若隔离级别是“串行化”，则在事务B执行“将1改成2”的时候，会被锁住。直到事务A提交后，事务B才可以继续执行。所以从A的角度看，V1、V2值是1，V3的值是2。

在实现上，数据库里面会创建一个视图，访问的时候以视图的逻辑结果为准。在“可重复读”隔离级别下，这个视图是在事务启动时创建的，整个事务存在期间都用这个视图。在“读提交”隔离级别下，这个视图是在每个SQL语句开始执行的时候创建的。这里需要注意的是，“读未提交”隔离级别下直接返回记录上的最新值，没有视图概念；而“串行化”隔离级别下直接用加锁的方式避免并行访问。

索引

一句话简单来说，索引的出现其实就是为了提高数据查询的效率。

索引的常见模型

• 哈希表
  哈希表是一种以键-值（key-value）存储数据的结构，我们只要输入待查找的值即key，就可以找到其对应的值即value。哈希的思路很简单，把值放在数组里，用一个哈希函数把key换算成一个确定的位置，然后把value放在数组的这个位置。当遇到多个key经过哈希函数的换算后会得出同一个值的情况，就需要使用到链表来处理。这样的话我们在查找的时候，可以先根据哈希函数算出key的位置，然后再遍历链表得出匹配的value。
  哈希表这种结构在新增数据时速度会很快，只需要往后追加。但是因为不是有序的，所以哈希索引做区间查询的速度是很慢的。所以，哈希表这种结构适用于只有等值查询的场景。
• 有序数组
  有序数组在等值查询和范围查询场景中的性能就都非常优秀。在查找数据时，可以用二分法快速得到数据位置，这个时间复杂度是O(log(N))。如果仅仅看查询效率，有序数组就是最好的数据结构了。但是在需要更新数据的时候就麻烦了，你往中间插入一个记录就必须得挪到后面所有的记录，成本太高。所以，有序数组索引只适用于静态存储引擎，存储那些不会再修改的数据。
• 二叉搜索树
  二叉搜索树的特点是：每个节点的左二子小于父节点，父节点又小于右儿子。这个时间复杂度是O(log(N))。为了维持O(log(N))的查询复杂度，就需要保持这棵树是平衡二叉树。为了做这个保证，更新的时间复杂度也是O(log(N))。
  树可以有二叉，也可以有多叉。多叉树就是每个节点有多个儿子，儿子之间的大小保证从左到右递增。二叉树是搜索效率最高的，但是实际上大多数的数据库存储却并不使用二叉树。原因就是索引不止存在内存中，还要写到磁盘上。当存储很大的数据量时，二叉树就会导致树高会很大，从而导致一个查询需要遍历很多次，导致查询变慢。而使用多叉树的话，会降低树高，从而减少读取磁盘的次数。

InnoDB的索引模型

在InnoDB中，表都是根据主键顺序以索引的形式存放的，这种存储方式的表称为索引组织表。因为前面我们提到的，InnoDB使用了B+树索引模型，所以数据都是存储在B+树中的。每一个索引在InnoDB里面对应一颗B+树。
根据叶子节点的内容，索引类型分为主键索引和非主键索引。
主键索引的叶子节点存的是整行数据。在InnoDB里，主键索引也被称为聚簇索引（clustered index）。
非主键索引的叶子节点内容是主键的值。在InnoDB里，非主键索引也被称为二级索引（secondary index）。
根据上面的索引结构说明，基于主键索引和普通索引的查询有什么区别？

• 如果语句是select * from T where id = 500，即主键查询方式，则只需要搜索id这颗B+数；
• 如果语句是select * from T where k = 5，即普通索引查询方式，则需要搜索k索引数，得到id的值为500，再到id索引数搜索一次。这个过程称为回表。
  也就是说，基于非主键索引的查询需要多扫描一棵索引树。因此，我们在应用中应该尽量使用主键查询。

索引维护

B+数为了维护索引有序性，在插入新值的时候需要做必要的维护。当我们在插入一条中间的数据时，可能需要逻辑上挪到后面的数据，空出位置。更糟糕的情况是，如果插入的位置所在的数据页已经满了，根据B+数的算法，这时候需要申请一个新的数据页，然后挪到部分数据过去。这个过程称为页分裂。在这种情况下，性能自然会受影响。除了性能外，页分裂操作还影响数据页的利用率。原本放在一个页的数据，现在分到两个页中，整体空间利用率降低大约50%。当然有分裂就有合并。当相邻两个页由于删除了数据，利用率很低之后，会将数据页做合并。合并的过程，可以认为是分裂过程的逆过程。
为什么在建表规范里面，要求建表语句里一定要有自增主键？
自增主键是指自增列上定义的主键，插入新纪录的时候可以不指定id的值，系统会获取当前id最大值加1作为下一条记录的id值。也就是说，自增主键的插入数据模式，正符合了我们前面提到的递增插入的场景。每次插入一条新纪录，都是追加记录，都不涉及到挪到其他记录，也不会触发叶子节点的分裂。而有业务逻辑的字段做主键，则往往不容易保证有序插入，这样写数据成本相对较高。
除了考虑性能外，我们还可以从存储空间的角度来看。假设你的表中确实有一个唯一字段，比如字符串类型的身份证号，那应该用身份证号做主键，还是用自增字段做主键呢？由于每个非主键索引的叶子节点上都是主键的值。如果用身份证号做主键，那么每个二级索引的叶子节点占用约20个字节，而如果用整型做主键，则只要4个字节，如果是长整型（bigint）则是8个字节。显然，主键长度越小，普通索引的叶子节点就越小，普通索引占用的空间也越小。

覆盖索引

创建表，添加几条数据信息

create table T (
id int primary key,
k int NOT NULL DEFAULT 0,
s varchar(16) NOT NULL DEFAULT '',
index k(k)
) engine = InnoDB

insert into T values(100,1, 'aa'),(200,2,'bb'),(300,3,'cc'),(500,5,'ee'),(600,6,'ff'),(700,7,'gg');

InnoDB索引组织结构：

select * from T where k between 3 and 5

当我们执行上面这条查询语句时，执行流程：

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

在执行过程中，回到主键索引树搜索的过程，我们称为回表。可以看到，这个查询过程回表了两次。因为查询结果所需要的数据只在主键索引上有，所以不得不回表。那么，有没有可能经过索引优化，避免回表过程呢？

select id from T where k between 3 and 5

如果执行语句改成这样，只需要返回id的值，而id的值已经在k索引树上了，因此可以直接提供查询结果，不需要回表。也就是说，在这个查询里面，索引k已经“覆盖了”我们的查询需求，我们称为覆盖索引。
由于覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段。

最左前缀原则

B+树这种索引结构，可以利用索引的“最左前缀”，来定位记录。
假如我们创建一个联合索引(name,age)。

可以看到，索引项是按照索引定义里面出现的字段顺序排序的。
当你的逻辑需求是查到所有名字是“张三”的人时，可以快速定位到ID4，然后向后遍历得到所有需要的结果。
如果你要查的是所有名字第一个字是“张”的人，你的SQL语句的条件是“where name like ‘张%’”。这时，你也能够用上这个索引，查找到第一个符合条件的记录是ID3，然后向后遍历，直到不满足条件为止。
可以看到，不只是索引的全部定义，只要满足最左前缀，就可以利用索引来加速检索。这个最左前缀可以使联合索引的最左N个字段，也可以是字符串索引的最左M个字符。

索引下推

上面我们说到，最左前缀可以用于在索引中定位记录。但是如果那些不符合最左前缀的部分，会怎么样呢？
还是拿联合索引(name,age)来举例,加入执行下面这条查询语句。

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

通过最左前缀规则，我们只能用“张”，找到第一个满足条件的记录ID3。当然，这样总比全表扫描要好。然后就是判断其他条件是否满足。在MySQL5.6之前，只能从ID3开始一个个回表。到主键索引上找出数据行，在对比字段值。而MySQL5.6引入的索引下推优化，可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。在我们这个例子中，只需要对ID4、ID5这两条记录回表取数据判断，就只需要回表2次。如下图：

普通索引和唯一索引

查询过程

• 对于普通索引来说，查找到满足条件的第一个记录后，需要查找下一个记录，直到碰到第一个不满足条件的记录。
• 对于唯一索引来说，由于索引定义了唯一性，查找到第一个满足条件的记录后，就会停止继续检索。

其实这个不同的处理方式带来的性能差距是微乎其微的。InnoDB的数据是按数据页为单位来读写的。也就是说，当需要读一条记录的时候，并不是将这个记录本身从磁盘读出来，而是以页为单位，将其整体读入内存。在InnoDB中，每个数据页的大小默认是16kb。

更新过程

change buffer：当需要更新一个数据页时，如果数据页在内存中就直接更新，而如果这个数据页还没有在内存中的话，在不影响数据一致性的前提下，InnoDB会将这些更新操作缓存到change buffer中，这样就不需要从磁盘中读入这个数据页了。在下次查询需要访问这个数据页的时候，将数据页读入内存，然后执行change buffer中与这个页有关的操作。通过这种方式就能保证这个数据逻辑的正确性。change buffer实际上它是可以持久化的数据。change buffer在内存中有拷贝，也会被写入到磁盘上。
将change buffer中的操作应用到原数据页，得到最新结果的过程称为merge。除了访问这个数据页会触发merge外，系统有后台线程会定期merge。在数据库正常关闭（shutdown）的过程中，也会执行merge操作。
显然，如果能够将更新操作现金在change buffer，减少读磁盘，语句的执行速度会得到明显的提示。而且，数据读入内存是需要占用buffer pool的，所以这种方式还能够避免占用内存，提高内存利用率。

什么条件下可以使用change buffer呢？
对于唯一索引来说，所有的更新操作都要先判断这个操作是否违反唯一性约束。所以在更新数据的时候，就必须将数据所在的数据页读入内存才能判断。如果都已经读入到内存了，那直接更新内存会更快，就没必要使用change buffer了。因此，唯一索引的更新就不能使用change buffer，实际上也只有普通索引可以使用。
change buffer用的是buffer pool里的内存，因此不能无限增大。change buffer的大小，可以通过参数innodb_change_buffer_max_size来动态设置。这个参数设置为50的时候，表示change buffer的大小最多只能占用buffer pool的50%。

回过头，我们来看看在插入一个新纪录的话，针对不同的索引，InnoDB的处理流程是怎样的?
第一种情况：这个记录要更新的目标页在内存中。

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

两个索引的差别，只是一个判断，只会耗费微小的CPU时间。

第二种情况，这个记录要更新的目标页不在内存中。

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

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

change buffer的使用场景：
对于写多读少的业务来说，页面在写完以后马上被访问到的概率比较小，此时change buffer的使用效果最好。这种业务模型常见的就是账单类、日志类的系统。
反过来，假设一个业务的更新模式是写入之后马上会做查询，那么即使满足了条件，将更新先记录在change buffer，但之后由于马上要访问这个数据页，会立即触发merge过程。这样随机访问IO的次数不会减少，反而增加了change buffer的维护代价。所以，对于这种业务模式来说，change buffer反而起到了副作用。

索引选择

普通索引和唯一索引应该怎么选择。其实，这两类索引在查询能力上是没差别的，主要考虑的是对更新性能的影响。所以，建议尽量选择普通索引。
如果所有的更新后面，都马上伴随着对这个记录的查询，那么应该关闭change buffer。而在其他情况下，change buffer都能提升更新性能。

change buffer和redo log

更新过程：

insert into t(id,k) values(id1,k1),(id2,k2);

假设k1所在的数据页在内存（InnoDB buffer pool）中，k2所在的数据页不在内存中。
分析这条更新语句，会涉及四个部分：内存、redo log、数据表空间、系统表空间。
这条更新语句的操作：

1. k1所在数据页在内存中，直接更新内存；
2. k2所在数据页不在内存中，就在内存的change buffer区域，记录下“我要在k2所在数据页插入一行”这个信息；
3. 将上述两个动作计入redo log中。

可以看到，执行这条更新语句的成本很低，就是写了两处内存，然后写了一处磁盘（两次操作合在一起写了一次磁盘），而且还是顺序写的。

查询过程：

select * from t where k in (k1,k2);

针对上面更新语句执行结果，我们现在执行查询。如果查询语句在更新语句后不久，内存中的数据都还在，那么此时的这两个读操作就与系统表空间和redo log无关了。
查询操作的流程：

1. 读k1数据时，因为所在数据页在内存中，直接从内存返回。虽然磁盘上还是之前的数据，但是结果是直接从内存返回。
2. 要读k2数据时，因为所在数据页不在内存中，所以需要先将k2所在数据页先从磁盘读入内存中，然后应用change buffer里面的操作日志，生成一个正确的版本并返回结果。

可以看到，直到需要读取k2所在数据页的时候，这个数据页才会被读入内存。
所以，简单的对比这两个机制在提升更新性能上的收益的话，redo log主要节省的是随机写磁盘的IO消耗（转成顺序写），而change buffer主要节省的则是随机读磁盘的IO消耗。

MySQL索引选择

我们知道MySQL选择索引的逻辑是在优化器中处理的。而优化器选择索引的目的，是找到一个最优的执行方案，并用最小的代价去执行语句。在数据库里面，扫描行数是影响执行代价的因素之一。扫描的函数越少，意味着访问磁盘数据的次数越少，消耗的CPU资源越少。当然，扫描行数并不是唯一的判断标准，优化器还会结合是否使用临时表、是否排序等因素进行综合判断。

MySQL扫描行数是怎么判断的呢？
MySQL在真正开始执行语句之前，并不能精确的知道满足这个条件的记录有多少条，而只能根据统计信息来估算记录数。这个统计信息就是索引的“区分度”。显然，一个索引上不同的值越多，这个索引的区分度就越好。而一个索引上不同的值的个数，我们称之为“基数”（cardinality）。也就是说，这个基数越大，索引的却分度越好。我们可以使用 show index from table 方法，看到一个索引的基数。

MySQL是怎样得到索引的基数的呢？
介绍一下MySQL采样统计的方法。为什么要采样统计呢？因为吧整张表取出来一行行统计，虽然可以得到精确的结果，但是代价太高了，所以只能选择“采样统计”。
采样统计的时候，InnoDB默认会选择N个数据页，统计这些页面上的不同值，得到一个平均值，然后乘以这个索引的页面数，就得到了这个索引的基数。
而数据表是会持续更新的，索引统计信息也不会固定不变。所以，当变更的数据行数超过1/M的时候，会自动触发重新做一次索引统计。

MySQL中，有两种存储索引统计的方式，可以通过设置参数innodb_stats_persistent的值来选择：

• 设置为on的时候，表示统计信息会持久化存储。这时，默认的N是20，M是10。
• 设置为off的时候，表示统计信息只存储在内存中。这时，默认的N是8，M是16.。

如果发现统计信息不对，我们可以进行修正，通过执行 analyze table t命令，可以用来重新统计索引信息。如果在实际场景中，你发现explain的结果预估的rows值跟实际情况差距比较大，可以采用这个方法来处理。

当MySQL在选择索引的时候，选择的不是正确的索引，可以怎么处理呢？

• 我们可以通过force index强行选择一个索引。MySQL会根据词法解析的结果分析出可能可以使用的索引作为候选项，然后在候选列表中一次判断每个索引需要扫描多少行。如果force index指定的索引在候选索引列表中，就直接选择这个索引，不在评估其他索引的执行代价。
• 可以考虑修改语句，引导MySQL使用我们期望的索引。
• 可以新建一个更合适的索引，来提供给优化器做选择，或删除掉误用的索引。

给字符串字段建索引

1. 给字符串字段创建完整索引
   完整索引的好处是可以利用覆盖索引，减少回表的次数，但是完整索引占用的空间比较大。
2. 创建前缀索引
   前缀索引的话，可以减少空间，但是可能会增加查询扫描次数，因为创建可能区分度不高，导致每次都需要回表查询，在进行匹配，而且前缀索引不能使用覆盖索引。就算是你使用的长度足够满足字符串字段的长度，但是MySQL系统并不确定前缀索引的定义是否截断了完整信息。
3. 倒序存储索引
   可能有些字符串字段，前缀区分度不太高，我们可以利用倒序的方式去创建索引。但是不能支持范围查询。只是在每一次的读和写，都需要调用一下reserve函数处理。
4. hash字段索引
   我们可以在表中新增一个字段，存储需要索引字段的hash值，每次写和读都需要调用crc32函数，同样也不支持范围查询。但是性能相对稳定一些。

函数操作

对索引字段做函数操作，可能会破坏索引值的有序性，因此优化器就决定放弃走树搜索功能。需要注意的是，优化器并不是放弃使用这个索引。

1. 条件字段函数操作；
2. 隐式类型转换；
3. 隐式字符编码转换。

MySQL的锁

全局锁

顾名思义，全局锁就是对整个数据库实例加锁。MySQL提供了一个加全局读锁的方法，命令是flush tables with read lock（FTWRL）。当你需要让整个库处于只读状态的时候，可以使用这个命令，之后其他线程的以下语句会被阻塞：数据更新语句（数据的增删改）、数据定义语句（包括建表、修改表结构等）和更新类事务的提交语句。
全局锁的典型使用场景是，做全库逻辑备份。也就是把整库每个表都select出来存成文本。
如果你在主库上备份，那么在备份期间都不能执行更新，业务基本上就得停摆；
如果你在从库上备份，那么备份期间从库不能执行主库同步过来的binlog，会导致主从延迟。
为什么在备份的时候需要加锁呢？
不加锁的话，备份系统备份的得到的库不是一个逻辑时间点，这个视图是逻辑不一致的。
前面讲事务隔离的时候，我们知道可以通过设置可重复读隔离级别来拿到一致性视图的。
官方自带的逻辑备份工具是mysqldump。当mysqldump使用参数-single-transaction的时候，导数据之前就会启动一个事务，来确保拿到一致性视图。而由于MVCC的支持，这个过程中数据是可以正常更新的。
为什么有这个工具，还需要FTWRL呢？
一致性读是好，但前提是引擎要支持这个隔离级别。比如，MyISAM这种不支持事务的引擎，如果备份过程中有更新，总是只能取到最新的数据，那么就破坏了备份的一致性。这时，我们就需要使用FTWRL命令了。所以，single-transaction方法只适用于所有的表使用事务引擎的库。
既然要全库只读，为什么不使用set global readonly = true的方式呢？

• 在有些系统中，readonly的值会被用来做其他逻辑，比如用来判断一个库是主库还是备库。因此，修改global变量的方式影响面更大，不建议使用。
• 在异常处理机制上有差异。如果执行FTWRL命令之后由于客户端发生异常断开，那么MySQL会自动释放这个全局锁，整个库回到可以正常更新的状态。而将整个库设置为readonly之后，如果客户端发生异常，则数据库就会一直保持readonly状态，这样会导致整个库长时间处于不可写状态，风险较高。

表级锁

MySQL里面表级别的锁有两种：一种是表锁，一种是元数据锁（meta data lock，MDL）。
表锁的语法是lock tables … read/write。与FTWRL类似，可以用unlock tables主动释放锁，也可以在客户端断开的时候自动释放。需要注意，lock tables语法除了会限制别的线程的读写外，也限定了本线程接下来的操作对象。
比如，在某个线程A中执行lock tables t1 read,t2 write这个语句，则其他线程写t1、读写t2的语句都会被阻塞。同时，线程A在执行unlock tables之前，也只能执行读t1、读写t2的操作。连写t1都不允许，自然也不能访问其他表。
元数据锁（MDL）。MDL不需要显示使用，在访问一个表的时候会被自动加上。MDL的作用是，保证读写的正确性。
MySQL5.5版本中引入了MDL，当对一个表做增删改查操作的时候，加MDL读锁；当要对表结构变更操作的时候，加MDL写锁。

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

对表加一个字段导致整个库挂了。
比如有四个操作先后执行：

1. 对t表进行select查询；
2. 对t表进行select查询；
3. 修改t表结构，添加字段；
4. 对t表进行select查询。

解析流程：
在第一步操作启动时，会对表t加一个MDL读锁。由于第二步需要的也是MDL读锁，因此可以正常执行。之后执行第三步，这个时候会被阻塞，因为第一步的MDL读锁还没有释放，而第三步需要MDL写锁，因此只能被阻塞。第三步被阻塞之后，所有要在表t上新申请MDL读锁的请求也会被第三步阻塞。前面说到。所有对表的增删改查操作都需要先申请MDL读锁，就都被锁住，等于这个表现在完全不可读写了。如果某个表上的查询语句频繁，而且客户端有重试机制，也就是说超时后会再起一个新的请求，这个库的线程很快就会爆满。

那么如何解决呢：
首先要解决长事务，事务不提交，就会一直占着MDL锁。在MySQL的information_schema库的innodb_trx表中，你可以查到当前执行中的事务。如果你要做DDL变更的表刚好有长事务在执行，要考虑先暂停DDL，或者kill掉这个长事务。如果变更的表是一个热点表，请求会很频繁。这个时候kill可能未必管用，因为新的请求马上就来。比较理想的机制是，在alter table语句里面设定等待时间，如果在这个指定的等待时间里面能够拿到MDL写锁最好，拿不到也不要阻塞后面的业务语句，先放弃。之后开发人员或者DBA再通过重试命令重复这个过程。
MariaDB已经合并了AliSQL的这个功能，所以这两个分支目前都支持DDL NOWAIT/WAIT n这个语法。

alter table tb1_name NOWAIT add column ...
alter table tb1_name WAIT N add column ...

行锁

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

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

如果你的事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放。

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

事务A
begin;
update t set k = k+1 where id = 1;    <1>
update t set k = k+1 where id = 2;    <3>

事务B
begin;
update t set k = k+1 where id = 2;    <2>
update t set k = k+1 where id = 1;    <4>

如上述执行逻辑，存在两个事务，按照标注的顺序进行执行，事务A在执行第3步的时候，就会等待事务B第2步进行id=2的行锁释放，而事务B在第4步时，需要等待事务A在第1步执行id=1的行锁释放。前面说到，锁释放是在事务提交结束后才进行释放，从而导致事务A和事务B在互相等待对方的资源释放，就进入死锁状态。

出现死锁以后，怎么处理呢？

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

怎么解决热点行更新导致的性能问题呢？
在实际业务场景中，可能会有很大的并发量同时更新同一行，这时候死锁检测操作就会很大，虽然最终检测的结果是没有死锁，但是期间要消耗大量的CPU资源。因此会发现CPU利用率很高，但是每秒却执行不了几个事务。怎么解决呢？

1. 关掉死锁检测。如果确定这个业务一定不会出现死锁的话，可以临时把死锁检测关掉。但是这种操作本身带有一定的风险，因为业务设计的时候一般不会把死锁当做一个严重错误，毕竟出现死锁了，就回滚，然后通过业务重试一般就没问题了，这是业务无损的。而关掉死锁检测意味着可能出现大量的超时，这是业务有损的。
2. 控制并发度。可以去控制同时最多在容忍的限度内的线程去更新，那么死锁检测的成本很低，就不会出现这个问题。可以在客户端做并发控制，但是可能会存在多个客户端，从而汇总到数据库服务端以后，峰值并发数还是会很高。所以，并发控制要做在数据库服务端。可以通过中间件来实现，也可以修改MySQL源码。基本思路就是，对于相同行的更新，在进入引擎之前排队。
3. 设计上优化措施。可以考虑通过将一行改成逻辑上的多行来减少锁冲突。比如一个账户我们可以分为多个子账户在多条记录上，比如10个记录，账户总额等于这10个记录的值的综合。这样每次要给账户修改金额的时候，随机选其中一条记录来更新。这样每次冲突概率变成原来的1/10，可以减少锁等待个数，也就减少了死锁检测的CPU消耗。这种方案看上去是无损的，但其实这类方案需要根据业务逻辑做详细设计。比如需要考虑子账户不够减扣的情况，代码就需要做特殊处理。

事务与锁的结合

初始化语句：

CREATE TABLE `t` (  
  `id` int(11) NOT NULL,
  `k` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;
insert into t(id, k) values(1,1),(2,2);

事务A	事务B	事务C
start transaction with consistent snapshot；	…	…
…	start transaction with consistent snapshot；	…
…	…	update t set k=k+1 where id=1;
…	update t set k=k+1 where id=1; select k from t where id=1;	…
select k from t where id=1; commit;	…	…
…	commit;	…

1.begin/start transaction命令并不是一个事务的起点，在执行到它们之后的第一个操作InnoDB表的语句，事务才真正启动。
2.想要马上启动一个事务，可以使用start transaction with consistent snapshot这个命令。
3.事务C没有显示的使用begin/commit，表示这个update语句本身就是一个事务，语句完成的时候会自动提交。

通过上面三个事务的执行流程，我们来看看事务在可重复读隔离和读提交级别下各个事务中查询到的k是多少？

1. 首先我们在可重复读下隔离级别下进行分析。答案就是事务B查到的k是3，而事务A查到的k是1。

在MySQL里，有两个“视图”的概念：

• 一个是view。它是一个用查询语句定义的虚拟表，在调用的时候执行查询语句并生成结果。创建视图的语法是create view …，而它的查询方法与表一样。
• 另一个是InnoDB在实现MVCC（多版本并发控制）时用到的一致性读视图，即consistent read view，用于支持RC（read committed，读提交）和RR（repeatable read，可重复读）隔离级别的实现。它没有物理结构，作用是事务执行期间用来定义“我能看到什么数据”。

在可重复读隔离级别下，事务在启动的时候就“拍了个快照”。注意，这个快照是基于整库的。那快照是怎么实现的？存储的是什么呢？
InnoDB里面每个事务有一个唯一的事务ID，叫做transaction id。它是在事务开始的时候向InnoDB的事务系统申请的，是按申请顺序严格递增的。而每行数据也都是有多个版本的。每次事务更新数据的时候，都会生成一个新的数据版本，并且把transaction id赋值给这个数据版本的事务ID，记为row trx_id。同时，旧的数据版本要保留，并且在新的数据版本中，能够有信息可以直接拿到它。也就是说，数据表中的一行记录，其实可能有多个版本（row），每个版本都有自己的row trx_id。
InnoDB是怎么定义快照的呢？
按照可重复读的定义，一个事务启动的时候，能够看到所有已经提交的事务结果。但是之后，这个事务执行期间，其他事务的更新对它不可见。因此，一个事务只需要在启动的时候声明说，“以我启动的时刻为准，如果一个数据版本是在我启动之前生成的，就认；如果是我启动以后才生成的，我就不认，我必须要找到它的上一个版本”。当然，如果“上一个版本”也不可见，那就得继续往前找。还有，如果是这个事务自己更新的数据，它自己还是要认的。
在实现上，InnoDB为每个事务构造了一个数组，用来保存这个事务启动瞬间，当前正在“活跃”的所有事务ID。“活跃”指的就是，启动了但还没提交。
数组里面事务ID的最小值记为低水位，当前系统里面已经创建过的事务ID的最大值加1记为高水位。这个视图数组和高水位，就组成了当前事务的一致性视图（read-view）。而数据版本的可见性规则，就是基于数据的row trx_id和这个一致性视图的对比结果看到的。这个视图数组把所有的row trx_id分成了几种不同的情况。

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

1. 如果落在绿色部分，表示这个版本是已提交的事务或者是当前事务自己生成的，这个数据是可见的；
2. 如果落在红色部分，表示这个版本是由将来启动的事务生成的，是肯定不可见的；
3. 如果落在黄色部分，那就包括两种情况：
   a. 若row trx_id在数组中，表示这个版本是由还没提交的事务生成的，不可见；
   b. 若row trx_id不在数组中，表示这个版本是已经提交了的事务生成的，可见。

所以现在我们知道，InnoDB利用了“所有数据都有多个版本”的这个特性，实现了“秒级创建快照”的能力。
接下来，我们回过头来看下三个事务，分析下事务A的语句返回的结果，为什么是k=1。
我们不妨做如下假设：

1. 事务A开始前，系统里面只有一个活跃事务ID是99；
2. 事务A、B、C的版本号分别是100、101/102，且当前系统里只有这个四个事务；
3. 三个事务开始前，(1,1)这一行数据的row trx_id是90.

这样，事务A的视图数组就是[99,100],事务B的视图数组是[99,100,101]，事务C的视图数组是[99,100,101,102]。
从执行流程看，第一个有效更新是事务C，把数据从(1,1)改成了(1,2)。这时候，这个数据的最新版本的row trx_id是102，而90这个版本已经成为了历史版本。
第二个有效更新是事务B，把数据从(1,2)改成了(1,3)。这时候，这个数据的最新版本row trx_id是101，而102又成为了历史版本。
在事务A查询的时候，其实事务B还没有提交，但是它生成的(1,3)这个版本已经变成当前版本了。但这个版本对事务A必须是不可见的，否则就变成脏读了。
现在事务A要来读取数据了，它的视图数组是[99,100]。当然，读数据都是从当前版本读起的。所以，事务A查询语句的读数据流程是这样的：

1. 找到(1,3)的时候，判断出row trx_id=101，比高水位大，处于红色区域，不可见；
2. 接着，找到上一个历史版本，一看row trx_id=102，比高水位大，处于红色区域，不可见；
3. 在往前找，终于找到了(1,1)，它的row trx_id=90，比低水位小，处于绿色区域，可见。

这样执行下来，虽然期间这一行数据被修改过，但是事务A不论在什么时候查询，看到这行数据的结果都是一致的，所以我们称之为一致性读。

我们可以转义一下判断规则。一个数据版本，对于一个事务视图来说，除了自己的更新总是可见以外，有三种情况：

1. 版本未提交，不可见；
2. 版本已提交，但是是在视图创建后提交的，不可见；
3. 版本已提交，而且是在视图创建钱提交的，可见。

在分析过程中，按照这种一致性读的规则，事务B的update语句好像不太对呀。事务B的视图数组是先生成的，之后事务C才提交，不是应该看不见(1,2)么，怎么能算出(1,3)呢？
是的，如果事务B在更新之前查询一次数据，这个查询返回的k的值确实是1.
但是，当它要去更新数据的时候，就不能再在历史版本上更新了。否则事务C的更新就丢失了。因此，事务B此时的set k=k+1是在(1,2)的基础上进行的操作。
所以，这里就用到了这样一条规则：**更新数据都是先读后写的，而这个读，只能读当前的值，称为“当前读”（current read）。**因此，在更新的时候，当前读拿到的数据是(1,2)，更新后生成了新版本的数据(1,3),这个新版本的row trx_id是101。所以，在执行事务B查询语句的时候，一看自己的版本号是101，最新数据的版本号也是101，是自己的更新，可以直接使用，所以查询得到的k的值是3。
这里我们提到了一个概念，叫作当前读。其实，除了update语句外，select语句如果加锁，也是当前读。比如通过lock in share mode或者for update命令。比如如下两条select语句，就是分别加了读锁（S锁，共享锁）和写锁（X锁，排他锁）。

select k from t where id=1 lock in share mode;
select k from t where id=1 for update;

在往前一步，假设事务C不是马上提交的，而是变成了下面的事务C1，会怎么样呢？

事务A	事务B	事务C1
start transaction with consistent snapshot；	…	…
…	start transaction with consistent snapshot；	…
…	…	start transaction with consistent snapshot； update t set k=k+1 where id=1;
…	update t set k=k+1 where id=1; select k from t where id=1;	…
select k from t where id=1; commit;	…	commit;
…	commit;	…

事务C1 的不同是，更新后并没有马上提交，在它提交前，事务B的更新语句先发起了。前面说过，虽然事务C还没提交，但是(1,2)这个版本也已经生成了，并且是当前的最新版本，那么，事务B的更新语句会怎么处理呢？
这时候，就需要MySQL锁介绍中的“两阶段锁协议”了，事务C1没提交，也就是说(1,2)这个版本上的写锁还没释放。而事务B是当前读，必须要读最新版本，而且必须加锁，因此就被锁住了，必须等到事务C1释放这个锁，才能继续它的当前读。
这样，我们把一致性读、当前读、和行锁就串起来了。
可重复读的核心就是一致性读（consistent read）；而事务更新数据的时候，只能用当前读。如果当前的记录的行锁被其他事务占用的话，就需要进入锁等待。
而读提交的逻辑和可重复读的逻辑类似，主要区别是：

• 在可重复读隔离级别下，只需要在事务开始的时候创建一致性视图，之后事务里的其他查询都共用这个一致性视图；
• 在读提交隔离级别下，每一个语句执行前都会重新算出一个新的视图。

2. 在读提交隔离级别下，事务A和事务B的查询语句查到的k，分别是多少呢？
   根据前面的流程分析后，我们可以知道，事务A的查询语句的视图数组是在执行这个语句的时候创建的，时序上(1,2)、(1,3)的生成时间都在创建这个视图数组的时刻之前。但是，在这个时刻：
   • (1,3)还没提交，属于情况1，不可见；
   • (1,2)提交了，属于情况3，可见。
     所以，这时候事务A查询语句返回的k=2.事务B查询结果k=3。

总的来说：InnoDB的行数据有多个版本，每个数据版本有自己的row trx_id，每个事务或者语句有自己的一致性视图。普通查询语句是一致性读，一致性读会根据row trx_id和一致性视图确定数据版本的可见性。

• 对于可重复读，查询只承认在事务启动前就已经提交完成的数据；
• 对于读提交，查询只承认在语句启动前就已经提交完成的数据；

而当前读，总是读取已经提交完成的最新版本。

锁导致的查询慢

1. 等MDL锁
   当我们进行一条简单的查询语句时，发现相应时间很慢，我们可以通过show processlis命令查看当前查询处于什么状态，state字段展示的就是当前状态，当出现Waiting for table metadata lock时。这就表示，现在有一个线程正在你查询的表上请求或者持有MDL写锁，把select语句堵住了。那么怎么处理呢？
   找到谁持有MDL写锁，然后把它kill掉。我们可以通过performance_schema和sys系统库才进行查询（MySQL启动时需要设置performance_schema=on），通过查询sys.schema_table_lock_waits这张表，我们就可以直接找出造成阻塞的process id，然后把这个连接kill掉。

2. 等flush
   同样的我们可以通过show processlis命令查看当前查询处于什么状态，当出现Waiting for table flush时，就表示现在有一个线程正要对表做flush操作。MySQL里面对做flush操作的用法，一般有以下两个：

   flush tables t with read lock;
   flush tables with read lock;
   

   这两个flush语句，如果指定表t的话，代表的是只关闭表t；如果没有指定具体的表名，则表示关闭MySQL里所有打开的表。正常情况下这两个语句执行起来都很快，除非它们也被别的线程堵住了。从而导致后续查询接连被阻塞。

3. 等行锁

   select * from t where id = 1 lock in share mode;
   

   比如我们执行这个查询语句。当我们访问id=1这条记录的时候需要加读锁，如果这时候已经有一个事务在这行记录上持有一个写锁，我们的select语句就会被堵住。
   我们可以通过sys.innodb_lock_waits表查询到谁占用这个写锁。查询语句：

   select * from t sys.innodb_lock_waits where locked_table=`'test'.'t'
   

   返回结果中，会有sql_kill_blocking_connection这个字段可以看出。

4. 单纯的查询慢
   我们前面提到过MySQL在介绍事务隔离的时候，没开启一个事务都会生成一个快照，然后进行查询的时候，都会去比较row trx_id，刚好在查询的时候，有另一个事务对查询条件的字段进行了大量的修改，这个时候这个row trx_id就会很大，那么当前查询条件去匹配的时候，就会要经历大量的回滚比较获取到当前查询事务的版本，从而导致查询就变慢了。

MySQL的空间回收

一个InnoDB表包含两部分，即：表结构定义个数据。在MySQL8.0版本以前，表结构是存在以.frm为后缀的文件里。而MySQL8.0版本，则已经允许把表结构定义放在系统数据表中了。因为表结构定义占用的空间很小。

参数innodb_file_per_table

表数据既可以存在共享表空间里，也可以是单独的文件。这个行为是由参数innodb_file_per_table控制的：

• 设置为OFF表示：表的数据放在系统共享表空间，也就是跟数据字典放在一起；
• 设置为ON表示：每个InnoDB表数据存储在一个以.ibd为后缀的文件中。

从MySQL5.6版本开始，它的默认值就是ON了。
建议不论使用MySQL哪个版本，都将这个值设置为ON。因为，一个表单独存储为一个文件更容易管理，而且在你不需要这个表的时候，通过drop table命令，系统就会直接删除这个文件。而如果是放在共享表空间中，即使表删掉了，空间也是不会回收的。
但是平常我们操作的更多是删除数据行，那么数据删除是怎么处理的呢？

数据删除流程

我们知道InnoDB里的数据都是用B+数据的结构组织的。而且InnoDB的数据是按页存储的。当我们需要删除一行数据的时候，会找到当前行所在的数据页，然后对这条记录标记为删除。如果之后再插入一条数据，刚好匹配到这个数据页，而且正好在刚删除的记录位置，就会复用这个位置。但是，磁盘文件的大小并不会缩小。从而知道，删除某一行数据，其实是对其进行删除标记，并不会真正的进行删除，占用的位置进入了可复用的状态。那么将整个数据页的数据都进行删除了？答案是，整个数据页都可以被复用了。但是，数据页的复用跟记录的复用是不同的。
记录的复用，只限于符合范围条件的数据。
而当整个页从B+数据里面删除以后，可以复用到任何位置。如果相邻的两个数据页利用率都很小，系统就会把这两个页上的数据合并到其中一个页上，另一个数据页就会被标记为可复用。
如果我们用delete命令把整个表的数据删除呢？结果就是，所有的数据页都不会被标记为可复用。但是磁盘上，文件不会变小。
总结来说，delete命令其实只是把记录的位置，或者数据页标记为了“可复用”，但磁盘文件的大小是不会变的。也就是说，通过delete命令是不能回收表空间的。这些可以复用，而没有被使用的空间，看起来就像是“空洞”。
实际上，不止是删除数据会造成空洞，插入数据也会。
如果数据是按照索引递增顺序插入的，那么索引是紧凑的。但是如果数据是随机插入的，就可能造成索引的数据页分裂。比如要插入一条记录，刚好这条记录在所在的数据页已经满了，就需要申请一个新的数据页来保存数据。这就导致原来数据页上的部分数据会被移植到新的数据页上，从而导致原来的数据页上就留下了空洞。
那怎么将这些空洞去掉呢？

重建表

假设一个表A，需要做空间收缩，为了把表中存在的空洞去掉，怎么做呢？
我们可以新建一个与表A结构相同的表B，然后按照主键ID递增的顺序，把数据一行一行的从表A读出来在插入到表B中。由于表B是新建的表，所以表A主键索引上的空洞，在表B中就都不存在了。
也可以使用alter table A engine=InnoDB命令来重建表。在MySQL5.5版本之前，这个命令的执行流程和前面描述的差不多，区别只是这个表B不需要你自己创建，MySQL会自动完成转成数据、交换表名、删除旧表的操作。

Online DDL

在上面回收表空间的过程中，如果有新的数据写入到表A的话，就会造成数据丢失。因此，在整个DDL过程中，表A中不能有更新。也就是说，这个DDL不是Online的。
在MySQL5.6版本开始引入的Online DDL，对这个操作流程做了优化。
简单描述下引入Online DDL之后，重建表的流程：

1. 建立一个临时文件，扫描表A主键的所有数据页；
2. 用数据页中表A的记录生成B+树，存储到临时文件中；
3. 生成临时文件的过程中，将所有对A的操作记录在一个日志文件（row log）中；
4. 临时文件生成后，将日志文件中的操作应用到临时文件，得到一个逻辑数据上与表A相同的数据文件；
5. 用临时文件替换掉表A的数据文件。

我靠，又是这种先记录日志，在同步日志机制。

count(*)语句

在不同的MySQL引擎中，count(*)有不同的实现方式。

• MyISAM引擎把一个表的总行数存在了磁盘上，因此执行count(*)的时候会直接返回这个数，效率很高；
• 而InnoDB引擎就麻烦了，它执行count(*)的时候，需要把数据一行一行的从引擎里面读出来，然后累积计数。

我们这里说的是没有过滤条件的count(*)，如果加了where条件的话，MyISAM表也是不能返回这么快的。

MySQL针对count(1)操作的优化
InnoDB是索引组织表，主键索引树的叶子节点是数据，而普通索引树的叶子节点是主键值。所以，普通索引树比主键索引树小很多。对于count(1)这样的操作，遍历哪个索引树得到的结果逻辑上都是一样的。因此，MySQL优化器会找到最小的那棵树来遍历。在保证逻辑正确的前提下，尽量减少扫描的数据量，是数据库系统设计的通用法则之一。

总结来说：

• MyISAM表虽然count(*)很快，但是不支持事务；
• show table status命令虽然返回很快，但是不准确；
• InnoDB表直接count(*)会遍历全表，虽然结果准确，但会导致性能问题。

不同的count用法
首先我们弄清count()的语义。count()是一个聚合函数，对于返回的结果集，一行行的判断，如果count函数的参数不是NULL，累计值就加1，否则不加。最后返回累计值。
所以，count(*)、count(主键id)和count(1)都表示返回满足条件的结果集的总行数；而count(字段),则表示返回满足条件的数据行里面，参数“字段”不为NULL的总个数。

对于count(主键id)，InnoDB引擎会遍历整张表，把每一行的id值都取出来，返回给server层。server层拿到id后，判断是不可能为空的，就按行累加。
对于count(1)，InnoDB引擎遍历整张表，但不取值。server层对于返回的每一行，放一个数字“1”进去，判断是不可能为空的，按行累加。
对于count(字段)，

• 如果这个“字段”是定义为 not null的话，一行行地从记录里面读出这个字段，判断不能为null，按行累加；
• 如果这个“字段”定义允许为null，那么执行的时候，判断到有可能是null，还要把值取出来再判断一下，不是null才累加。

对于count(1)，并不会把全部字段取出来，而是专门做了优化，不取值。count(*)肯定不是null，按行累加。

所以结论是：按照效率排序的话，count(字段)<count(主键id)<count(1)≈count(*)，所以建议尽量使用count(1).

order by的机制

我们通过一个例子来认识下order by是怎么排序处理的，假设我们现在有一个存放市民基础信息的表，我们需要查询出城市是“深圳”的所有人名字，并且按照姓名排序返回前1000个人的姓名、年龄。
表定义：

CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `city` varchar(16) NOT NULL,
  `name` varchar(16) NOT NULL,
  `age` int(11) NOT NULL,
  `addr` varchar(128) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `city` (`city`)
) ENGINE=InnoDB;

查询SQL可以这么写

select city,name,age from t where city='深圳' order by name limit 1000;

全字段排序

通过explain命令来解析我们的查询语句，看到Extra这个字段中的“Using filesort”，表示的就是需要排序，MySQL会给每个线程分配一块内存用于排序，称为sort_buffer。
通常情况下，这个语句执行流程如下所示：

1. 初始化sort_buffer，确定放入name、city、age这三个字段；
2. 从索引city找到第一个满足city=‘深圳’条件的主键id；
3. 到主键id索引取出整行，取name、city、age三个字段的值，存入sort_buffer中；
4. 从索引city取下一个记录的主键id；
5. 重复步骤3、4直到city的值不满足查询条件为止；
6. 对sort_buffer中的数据按照字段name做快速排序；
7. 按照排序结构取前1000行返回给客户端。

我们暂且把这个排序过程，称为全字段排序。
在进行“按name排序”这个动作，可能在内存中完成，也可能需要使用外部排序，这取决于排序所需的内存和参数sort_buffer_size。
sort_buffer_size，就是MySQL为排序开辟的内存（sort_buffer）的大小。如果要排序的数据量小于sort_buffer_size，排序就在内存中完成。但如果排序数据量太大，内存放不下，则不得不利用磁盘临时文件辅助排序。
可以使用如下命令查看排序语句是否使用了临时文件：

//打开OPTIMIZER_TRACE,只对本线程有效
set optimizer_trace='enabled=on';
//执行查询语句
select city,name,age from t where city='深圳' order by name limit 1000;
//查看 OPTIMIZER_TRACE输出
select * from information_schema.OPTIMIZER_TRACE; 

通过查询optimizer_trace的结果来确认的，可以从number_of_tmp_files中看到是否使用了临时文件。
返回结果：

 "filesort_summary": {
              "rows": 141378,
              "examined_rows": 141378,
              "number_of_tmp_files": 42,
              "sort_buffer_size": 262112,
              "sort_mode": "<sort_key, packed_additional_fields>"
            }

number_of_tmp_files表示的是，排序过程中使用的临时文件数。这里看到使用了42个临时文件数，内存放不下时，就需要使用外部排序，外部排序一般使用归并排序算法。可以这么理解，MySQL将需要排序的数据分成12份，每一份单独排序后存在这些临时文件中。然后把这12个有序文件在合并成一个有序的大文件。
如果sort_buffer_size超过了需要排序的数据量的大小，number_of_tmp_files就是0，表示排序可以直接在内存中完成。否则就需要放在临时文件中排序。sort_buffer_size越小，需要分成的分数越多，number_of_tmp_files的值就越大。
examined_rows表示参与排序的行数。sort_mode里面的packed_additional_fields的意思是，排序过程对字符串做了“紧凑”处理。即使name字段的定义是varchar(16)，在排序过程中还是要按照实际长度来分配空间的。

rowid排序

在上面这个算法过程里面，只对原表的数据读了一遍，剩下的操作都是在sort_buffer和临时文件中执行的。但这个算法有一问题，就是如果查询要返回的字段很多的话，那么sort_buffer里面要放的字段数太多，这样内存里能够同时放下的行数很少，要分成多个临时文件，排序的性能会很差。
那么，如果MySQL认为排序的单行长度太大会怎么做呢？

set max_length_for_sort_data = 16;

max_length_for_sort_data，是MySQL中专门控制用于排序的行数据的长度的一个参数。意思就是，如果单行的长度超过这个值，MySQL就认为单行太大，要换一个算法。
city、name、age这三个字段的定义总长度是36，我把max_length_for_sort_data设置为16，在来看看计算过程有什么改变。
新的算法放入sort_buffer的字段，只有要排序的列（即name字段）和主键id。这时，排序的结果就因为少了city和age字段的值，不能直接返回了，整个执行流程就会变成如下所示：

1. 初始化sort_buffer，确定放入两个字段，即name和id；
2. 从索引city找到第一个满足city='深圳’条件的主键id；
3. 到主键id索引取出整行，取name、id这两个字段，存入sort_buffer中；
4. 从索引city取下一个记录的主键id；
5. 重复步骤3、4直到不满足city=‘深圳’条件为止；
6. 对sort_buffer中的数据按照字段name进行排序；
7. 遍历排序结果，取前1000行，并按照id的值回到原表中取出city、name和age三个字段返回给客户端。

这个执行流程，我们就叫为rowid排序。

全字段排序 VS rowid排序

如果MySQL实在是担心排序内存太小，会影响排序效率，才会采用rowid排序算法，这样排序过程中一次可以排序更多行，但是需要在回到原表去取数据。
如果MySQL认为内存足够大，会优先选择全字段排序，把需要的字段都放到sort_buffer中，这样排序后就会直接从内存里面返回查询结果了，不用在回到原表去取数据。
这体现了MySQL的一个设计思路：如果内存足够，就要多利用内存，尽量减少磁盘访问。
对于InnoDB表来说，rowid排序会要求回表多造成磁盘读，因此不会优先选择。

其实，并不是所有的order by语句，都需要排序操作的。从上面分析的执行过程，我们可以看到，MySQL之所以需要生成临时表，并且在临时表上做排序操作，其原因是原来的数据都是无序的。如果能够保证从city这个索引上取出来的行，天然就是按照name递增排序的话，是不是就可以不用排序了呢？答案是肯定的，我们可以创建一个city和name的联合索引。在这个索引中，我们依然可以用数据搜索的方式定位到第一个满足city=‘深圳’的记录，并且额外确保了，接下来按顺序取“下一条记录”的遍历过程中，只要city的值是深圳，name的值就一定是有序的。整个查询过程就变成如下：

1. 从索引(city,name)找到第一个满足city=‘深圳’条件的主键id；
2. 到主键id索引取出整行，取name、city、age三个字段的值，作为结果集的一部分直接返回；
3. 从索引(city,name)取下一个记录主键id；
4. 重复步骤2、3，直到查到第1000条记录，或者是不满足city=‘深圳’条件时循环结束。’

可以看到，这个查询过程不需要临时表，也不需要排序。可以通过explain来验证一下。可以看到Extra字段中没有Using filesort了，也就是不需要排序了。而且由于(city,name)这个联合索引本身有序，所以这个查询也不用把所有匹配city=‘深圳’的行全都读一遍，只要找到满足条件的前1000条记录就可以退出了。也就是说，在我们这个例子里，只需要扫描1000次。

那么，在这个例子中，还有没有更进一步的优化呢？
答案就是覆盖索引。覆盖索引是指索引上的信息足够满足查询请求，不需要再回到主键索引上取数据。我们的查询结果是city、name、age三个字段，那么我们可以创建一个三个字段的联合索引(city,name,age)。这个时候，对于city字段的值相同的行来说，还是按照那么字段的值递增排序的，此时的查询语句也就不再需要排序了。这样整个查询执行流程就变成了：

1. 从索引(city,name,age)找到第一个满足city=‘深圳’条件的记录，取出其中的city、name、age这三个字段的值，作为结果集的一部分直接返回；
2. 从索引(city,name,age)取下一个记录，同样取出这三个字段的值，作为结果集的一部分直接返回；
3. 重复步骤2，知道查询到第1000条记录，或者是不满足city=‘深圳’条件时循环结束。

这个时候，我们再来explain看看，Extra字段里面多了“Using index”，表示的就是使用了覆盖索引，性能上会快很多。

几个概念