MySQL实战45讲记录

一.性能问题

1.1 长连接

数据库里面，长连接是指连接成功后，如果客户端持续有请求，则一直使用同一个连接。短连接则是指每次执行完很少的几次查询就断开连接，下次查询再重新建立一个。建立连接的过程通常是比较复杂的，所以推荐使用长连接。

但是全部使用长连接后，会发现，有些时候MySQL占用内存涨得特别快，这是因为MySQL在执行过程中临时使用的内存是管理在连接对象里面的。这些资源会在连接断开的时候才释放。所以如果长连接积累下来，可能导致内存占用太大，被系统强行杀掉，从现象看就是MySQL异常重启了。

解决方案：

1.定期断开长连接。使用一段时间，或者程序里面判断执行过一个占用内存的大查询后，断开连接，之后要查询再重连。

2.如果使用的是MySQL5.7或更新版本，可以再每次执行一个比较大的操作后，通过执行mysql_reset_connection来重新初始化连接资源。这个过程不需要重连和重新做权限验证，但是会将连接恢复到刚刚创建完时的状态。

1.2 刷脏页

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

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

1.一个查询要淘汰的脏页个数太多，会导致查询的响应时间明细变长。（查询触发刷脏）

2.日志写满，更新全部堵住，写性能跌为0，这种情况对敏感业务来说，是不能接受的。（更新触发刷脏）

所以，InnoDB需要有控制脏页比例的机制，来尽量避免上述的问题。

接下来，还有一个策略，一旦一个查询请求需要在执行过程中先flush掉一个脏页时，这个查询就可能要比平时慢了。而MySQL中的一个机制，可能会让查询更慢：在准备刷一个脏页的时候，如果这个数据页旁边的数据页刚好是脏页，就会把这个邻居也带着一起刷掉；而且这个把邻居拖下水的逻辑还可以继续蔓延，也就是对于每个邻居数据页，如果跟他相邻的数据页也还是脏页的话，也会被放到一起刷。

在InnoDB中，innodb_flush_neighbors 参数就是用来控制这个行为的，值为1的时候会有上述的连坐机制，值为0时表示不找邻居，自己刷自己的。找邻居这个优化在机械硬盘时代是很有意义的，可以减少很多随机IO，机械硬盘的随机IOPS一般只有几百，相同的逻辑操作减少随机IO就意味着系统性能的大幅度提升。

而如果使用的是SSD这类IOPS比较高的设备的话，建议把innodb_flush_neighbors 的值设置成0。因为这时候IPOS往往不是瓶颈，而只刷自己，就能更快地执行完必要的刷脏页操作，减少SQL语句响应时间。在MySQL8.0中，innodb_flush_neighbors 参数的默认值已经是0了。

1.3 重建表

InnoDB里的数据都是用B+树的数据结构组织的。假设，我们要删掉R4这个记录，InnoDB引擎只会把R4这个记录标记为删除。如果之后要再插入一个ID在300和600之间的记录时，可能会复用这个位置。但是，磁盘的大小不会缩小。

进一步地，如果我们用delete命令把整个表的数据删除，所有的数据页都会被标记为复用，但是磁盘上，文件不会变小。delete命令其实只是把记录的位置，或者数据页标记为了可复用，但磁盘文件的大小是不会变的。也就是说，通过delete命令是不能回收表空间的。这些可以复用，而没有被使用的空间，看起来就像是空洞。

如果现在有一个表A，需要做空间收缩，为了把表中存在的空洞去掉，可以新建一个与表A结构相同的表B，然后按照主键ID递增的顺序，把数据一行一行地从表A里读出来在插入到表B中。由于表B是新建的表，所以表A主键索引上的空洞，在表B中就都不存在了。显然地，表B的主键索引更紧凑，数据页的利用率也更高。如果我们把表B作为临时表，数据从表A导入表B的操作完成后，用表B替换A，从效果上看，就起到了收缩表A空间的作用。这里，可以使用alter table A engine=InnoDB命令来重建表。

一张表重建以后，反而占用空间变大了。原因在于这个表已经刚刚重建过一次了，第二次重建的时候，有新的DML的语句造成了空洞，并且InnoDB重建并不会把表占满，每个页留了1/16给后续的更新用。

假如是这样的一个过程：

1.将表重建一次；

2.插入一部分数据，但是插入的这些数据，用掉了一部分预留空间。

3.再次重建，就会出现问题中的现象。

1.4 排序优化

在开发应用的时候会有排序的需求，假设以市民表为例，查询城市是"杭州"的所有人名字，并且按照姓名排序返回前1000个人的姓名、年龄。SQL如下：

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;
select city,name,age from t where city='杭州' order by name limit 1000;

用explain命令查看语句的执行情况，Extra这个字段中的"Using filesort"表示的就是需要排序，MySQL会给每个线程分配一块内存用于排序，称为sort_buffer。

排序这个动作，可能在内存中完成，也可能需要使用外部排序，这取决于排序所需的内存和参数sort_buffer_size。sort_buffer_size就是MySQL为排序开辟的内存(sort_buffer)的大小，如果要排序的数据量小于sort_buffer_size，排序就在内存中完成。但如果排序数据量太大，内存放不下，则不得不利用磁盘临时文件辅助排序。

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

所以如果单行很大，这个方法效率不够好。接下来，修改一个参数，让MySQL采用另外一种算法。

SET max_length_for_sort_data = 16;

max_length_for_sort_data是MySQL中专门控制用于排序的行数据的长度的一个参数，它的意思是，如果单行的长度查过这个值，MySQL就认为单行太大，要换一个算法。

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

作为与city索引的对比，可以看看这个索引的示意图：

在这个索引里面，我们依然可以用树搜索的方式定位到第一个满足city="杭州"的记录，并且额外确保了，接下来按顺序取下一条记录的遍历过程，只要city的值是杭州，name的值就一定是有序的。

如果是查询语句select * from t where city in (“杭州”," 苏州 ") order by name limit 100，可以将其拆为两个语句执行，得到杭州和苏州两个有序的name数组，最后在归并寻找取出前面最小的100个值。

对于临时内存表来说，MySQL会选择rowid排序，因为对于InnoDB表来说，执行全字段排序会减少磁盘访问，而对于内存表，回表过程只是简单地根据数据行的位置，直接访问内存得到数据，根本不会导致多访问磁盘。优化器没有了这一层的顾虑，那么它会优先考虑的，就是用于排序的行越小越好了。

1.5 Join语句问题

创建表t1和t2：

CREATE TABLE `t2` (
  `id` int(11) NOT NULL,
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `a` (`a`)
) ENGINE=InnoDB;

drop procedure idata;
delimiter ;;
create procedure idata()
begin
  declare i int;
  set i=1;
  while(i<=1000)do
    insert into t2 values(i, i, i);
    set i=i+1;
  end while;
end;;
delimiter ;
call idata();

create table t1 like t2;
insert into t1 (select * from t2 where id<=100)

可以看到，这两个表都有一个主键索引id和一个索引a,字段b上无索引。存储过程idata()往表t2里插入了1000行数据，再表t1里插入的是100行数据。

Index Nested-Loop Join

select * from t1 straight_join t2 on (t1.a=t2.a);

如果直接使用join语句，MySQL优化器可能会选择表t1或t2作为驱动表，这样会影响分析 SQL 语句的执行过程。所以，为了便于分析执行过程中的性能问题，改用 straight_join 让 MySQL 使用固定的连接方式执行查询，这样优化器只会按照指定的方式去 join。在这个语句里，t1 是驱动表，t2 是被驱动表。explain结果如下：

可以看到，在这条语句里，被驱动表t2的字段a上有索引，join过程用上了这个索引，因此语句的执行流程如下：

1.从表t1中读入一行数据R；

2.从数据行R中，取出a字段到表t2里去查找；

3.取出表t2中满足条件的行，跟R组成一行，作为结果集的一部分。

4.重复执行步骤1到3，直到表t1的末尾循环结束。

这个过程是先遍历表t1,然后根据从表t1中取出的每行数据中的a值，去表t2中查找满足条件的记录，在形式上，这个过程就跟我们写程序时的嵌套查询类似，并且可以用上被驱动表的索引，所以我们称之为Index Nested-Loop Join，简称NLJ。

在这个流程里：

1.对驱动表t1做了全表扫描，这个过程需要扫描100行；

2.而对于每一行R，根据a字段去表t2查找，走的是树搜索过程。由于我们构造的数据都是一一对应的，因此每次的搜索过程都是只扫描一行，也是总共扫描100行。

3.所以，整个执行流程，总扫描行数是200.

假设不使用join,那我们就只能用单表去查询：

1.执行select * from t1,查询表t1的所有数据，这里有100行；

2.循环遍历这100行数据：

• 从每一行R取出字段a的值$R.a
• 执行select * from t2 where a=$R.a
• 把返回的结果和R构成结果集的一行

可以看到，在这个查询过程，也是扫描了200行，但是总共执行了101条语句，比直接join多了100次交互。除此之外，客户端还要自己拼接SQL语句和结果。显然，这么做还不如直接join好。

在这个join语句执行过程中，驱动表是走全表扫描，而被驱动表是走树搜索。假设被驱动表的行数是M。每次在被驱动表查一行数据，要先搜索索引a，再搜索主键索引。每次搜索一颗树近似复杂度是以2为底的M的对数，记为log2 (M),所以在被驱动表上查一行的时间复杂度是2*log2 (M)。

假设驱动表的行数是N，执行过程就要扫描驱动表N行，然后对于每一行，到被驱动表上匹配一次。因此整个执行过程，近似复杂度是N+N*log2^M
所以可以得出结论：

1.使用join语句，性能比强行拆成多个单表执行SQL语句的性能要好。

2.如果使用join语句的话，需要让小表做驱动表。

但是需要注意的是，这个结论的前提是可以使用被驱动表的索引。

Simple Nested-Loop Join

如果将SQL语句改成这样：

select * from t1 straight_join t2 on (t1.a=t2.b);

由于表t2的字段b上没有索引，因此就不能用上面的流程，每次到t2匹配的时候，就要做一次全表扫描。如果继续使用上面的算法，只看结果的话，这个算法是正确的，而且这个算法也有一个名字，叫做“Simple Nested-Loop Join”。但是，这样算来，这个 SQL 请求就要扫描表 t2 多达 100 次，总共扫描 100*1000=10 万行。

这还只是两个小表，如果 t1 和 t2 都是 10 万行的表，就要扫描 100 亿行，这个算法看上去太“笨重”了。当然，MySQL 也没有使用这个 Simple Nested-Loop Join 算法，而是使用了另一个叫作“Block Nested-Loop Join”的算法，简称 BNL。

Block Nested-Loop Join

这时候，被驱动表上没有可用的索引，算法的流程如下：

1.把表t1的数据读入线程内存join_buffer中，由于我们这个语句中写的是select *，因此是把整个表t1放入了内存。

2.扫描表t2,把表t2中的每一行取出来，跟join_buffer中的数据做对比，满足join条件的，作为结果集的一部分返回

可以看到，在这个过程中，对表t1和表t2都做了一次全表扫描，因此总的扫描行数是1100。由于join_buffer是以无序数组的方式组织的，因此对表t2中的每一行，都要做100次判断，总共需要在内存中做的判断次数是：100*1000=10万次。前面我们说过，如果使用 Simple Nested-Loop Join 算法进行查询，扫描行数也是 10 万行。因此，从时间复杂度上来说，这两个算法是一样的。但是，Block Nested-Loop Join 算法的这 10 万次判断是内存操作，速度上会快很多，性能也更好。

假设小表的行数N，大表的行数是M，那么在这个算法里：

1.两个表都做一次全表扫描，所以总的扫描行数是M+N。

2.内存中的判断次数是M*N。

可以看到，调换这两个算式中的 M 和 N 没差别，因此这时候选择大表还是小表做驱动表，执行耗时是一样的。这个例子里的表t1才100行，要是表t1是一个大表，join_buffer就需要分段放。join_buffer的大小是由参数join_buffer_size 设定的，默认值是256K。如果把join_buffer_size 改成1200，再执行：

select * from t1 straight_join t2 on (t1.a=t2.b);

执行流程就变成了：

1.扫描表t1，顺序读取数据行放入join_buffer 中，放完第88行join_buffer满了，继续第2步。

2.扫描表t2，把t2中的每一行取出来，跟join_buffer中的数据做对比，满足join条件的，作为结果集的一部分返回。

3.清空join_buffer。

4.继续扫描表t1，顺序读取最后的12行数据放入join_buffer中，继续执行第2步。

图中的步骤4和5，表示清空join_buffer再复用。这个流程才体现出了这个算法名字中“Block”的由来，表示“分块去 join”。可以看到，这时候由于表t1被分成了两次放入join_buffer中，导致表t2会被扫描两次。虽然分成两次放入join_buffer，但是判断等值条件的次数还是不变的，依然是(88+12)*1000=10万次。

假设，驱动表的数据行数是N，需要分K段才能完成算法流程，被驱动表的数据行数是M。注意，这里的K不是常数，N越大K就会越大，因此把K表示为λ*N，显然λ的取值范围是(0,1)。所以，在这个算法的执行过程中：

1.扫描的行数是N+λNM。

2.内存判断N*M次。

显然，内存判断次数是不受选择哪个表作为驱动表影响的，而考虑到扫描行数，在M和N大小确定的情况下，N小一些，整个算式的结果会更小。所以结论是，应该让小表驱动大表。当然，可以发现，在N+λNM这个式子里，λ才是影响扫描行数的关键因素，这个值越小越好。

除了N越大，分段数K越大的情况，join_buffer_size 越大，一次可以放入的行越多，分成的段数也就越少，对被驱动表的全表扫描次数就越少。所以如果join语句很慢，就把join_buffer_size 放大。

结论：

1.如果可以使用Index Nested-Loop Join算法，也就是说可以用上被驱动表上的索引，其实是可以用join语句的；

2.如果使用Block Nested-Loop Join算法，扫描行数就会过多。尤其是在大表上的join操作，这样可能要扫描被驱动表很多次，会占用大量的系统资源。所以这种join尽量不要用。

3.所以在判断要不要使用join语句时，就是看explain结果里面，Extra字段里面有没有出现Block Nested Loop字样。

4.如果是Index Nested Loop算法，应该选择小表做驱动表。

5.如果是Block Nested Loop算法：

• 在 join_buffer_size 足够大的时候，是一样的。
• 在 join_buffer_size 不够大的时候（这种情况更常见），应该选择小表做驱动表

所以问题的结论就是，总是应该使用小表做驱动表，说明一下什么叫做小表，前面的例子是没有加条件的。如果在语句的 where 条件加上 t2.id<=50 这个限定条件，再来看下这两条语句：

select * from t1 straight_join t2 on (t1.b=t2.b) where t2.id<=50;
select * from t2 straight_join t1 on (t1.b=t2.b) where t2.id<=50;

注意，为了让两条语句的被驱动表都用不上索引，所以 join 字段都使用了没有索引的字段 b。但如果是用第二个语句的话，join_buffer只需要放入t2的前50行，显然是更好的。所以，这里，t2的前50行是那个相对较小的表，也就是小表。

另外一组例子：

select t1.b,t2.* from  t1  straight_join t2 on (t1.b=t2.b) where t2.id<=100;
select t1.b,t2.* from  t2  straight_join t1 on (t1.b=t2.b) where t2.id<=100;

这个例子里，表t1和t2都是只有100行参加join。但是，这两条语句每次查询放入join_buffer 中的数据是不一样的：

• 表t1只查字段b，因此如果把t1放到join_buffer 中，则join_buffer 中只需要放入b的值
• 表t2需要查所有的字段，因此如果把表t2放到join_buffer 中的话，就需要放入三个字段id、a和b

这里，我们应该选择表t1作为驱动表。也就是说这个例子里，只需要一列参与join_buffer的表t1是小表。所以，更准确地说，在决定哪个表做驱动表的时候，应该是两个表按照各自的条件过滤，过滤完成之后，计算参与 join 的各个字段的总数据量，数据量小的那个表，就是“小表”，应该作为驱动表。

1.6 Join优化

Join语句有两种算法，分别是Index Nested-Loop Join(NLJ)和Block Nested-Loop Join(BNL)。使用 NLJ 算法的时候，其实效果还是不错的，比通过应用层拆分成多个语句然后再拼接查询结果更方便，而且性能也不会差。但是，BNL 算法在大表 join 的时候性能就差多了，比较次数等于两个表参与 join 的行数的乘积，很消耗 CPU 资源。

创建两个表t1、t2说一下关于两个算法的优化，在表t1里，插入了1000行数据，每一行的a=1001-id的值。也就是说，表t1中字段a是逆序的。同时，在表t2中插入了100万行数据。

create table t1(id int primary key, a int, b int, index(a));
create table t2 like t1;
drop procedure idata;
delimiter ;;
create procedure idata()
begin
  declare i int;
  set i=1;
  while(i<=1000)do
    insert into t1 values(i, 1001-i, i);
    set i=i+1;
  end while;
  
  set i=1;
  while(i<=1000000)do
    insert into t2 values(i, i, i);
    set i=i+1;
  end while;

end;;
delimiter ;
call idata();

Multi-Range Read优化

Multi-Range Read优化(MRR)，这个优化的主要目的是尽量使用顺序读盘。首先我们执行下面的语句：

select * from t1 where a>=1 and a<=100;

我们需要查到索引a上的数据，然后根据索引a上的主键id去回表，主键索引是一颗B+树，在这颗树上，每次只能根据一个主键id查到一行数据。因此，回表肯定是一行行搜索主键索引的：

如果随着a的值递增顺序查询的话，id的值就变成随机的，那么就会出现随机访问，性能相对较差。虽然一行行查这个机制不能改，但是调整查询的顺序，还是能够加速的。因为大多数的数据都是按照主键递增顺序插入得到的，所以我们可以认为，如果按照主键的递增顺序查询的话，对磁盘的读比较接近顺序读，能够提升读性能。

这就是MRR优化的设计思路。此时，语句的执行流程变成了这样：

1.根据索引a,定位到满足条件的记录，将id值放入read_rnd_buffer 中。

2.将read_rnd_buffer 中的id进行递增排序。

3.排序后的id数组，依次到主键id索引中查记录，并作为结果返回。

这里，read_rnd_buffer 的大小是由read_rnd_buffer _size参数控制的。如果步骤1中，read_rnd_buffer 放满了，就会先执行完步骤2和3，然后清空read_rnd_buffer。之后继续找索引a的下个记录，并继续循环。另外如果想要稳定地使用MRR优化的话，需要设置set optimizer_switch=“mrr_cost_based=off”。（官方文档的说法，是现在的优化器策略，判断消耗的时候，会更倾向于不使用 MRR，把 mrr_cost_based 设置为 off，就是固定使用 MRR 了。）

图2和图3就是MRR优化后的执行流程和explain结果。

从图3的explain结果中，可以看到Extra字段多了Using MRR,表示的是用上了MRR优化。而且，由于我们在read_rnd_buffer中按照id做了排序，所以最后得到的结果集也是按照主键id递增顺序的，也就是与图1结果集中行的顺序相反。

MRR能够提升性能的核心在于，这条查询语句在索引a上做的是一个范围查询(也就是说，这是一个多值查询)，可以得到足够多的主键id。这样通过排序以后，再去主键索引查数据，才能体现出顺序性的优势。

Batched Key Access

理解了 MRR 性能提升的原理，就能理解 MySQL 在5.6版本后开始引入的 Batched Key Access(BKA) 算法了。这个 BKA 算法，其实就是对 NLJ 算法的优化。NLJ算法的流程图如下：

NLJ 算法执行的逻辑是：从驱动表 t1，一行行地取出 a 的值，再到被驱动表 t2 去做 join。也就是说，对于表 t2 来说，每次都是匹配一个值。这时，MRR 的优势就用不上了。所以我们需要从表t1里一次性地多拿些行出来，一起传给表t2。既然如此，就把表t1的数据取出来一部分，先放到一个临时内存，就是join_buffer。

join_buffer 在 BNL 算法里的作用，是暂存驱动表的数据。但是在 NLJ 算法里并没有用。那么，我们刚好就可以复用 join_buffer 到 BKA 算法中。如图 5 所示，是上面的 NLJ 算法优化后的 BKA 算法的流程。

图中，在join_buffer中放入的数据是P1-P100，表示的是只会去查询需要的字段。当然，如果join_buffer 放不下P1-P100的所有数据，就会把这100行数据分成多段执行上图的流程，如果要使用BKA优化算法的话，需要在执行SQL语句之前，先设置：

set optimizer_switch='mrr=on,mrr_cost_based=off,batched_key_access=on';

其中，前两个参数的作用是要启用 MRR。这么做的原因是，BKA 算法的优化要依赖于 MRR。

BNL算法的性能问题

使用Block Nested-Loop Join(BNL)算法时，可能会对被驱动表做多次扫描。如果这个被驱动表是一个大的冷数据表，除了会导致IO压力大以外，还会造成一些其余问题。比如：由于 InnoDB 对 Bufffer Pool 的 LRU 算法做了优化，即：第一次从磁盘读入内存的数据页，会先放在 old 区域。如果 1 秒之后这个数据页不再被访问了，就不会被移动到 LRU 链表头部，这样对 Buffer Pool 的命中率影响就不大。

但是，如果一个使用BNL算法的join语句，多次扫描一个冷表，而且这个语句执行时间超过1秒，就会在再次扫描冷表的时候，把冷表的数据页移到LRU链表头部。这种情况对应的，是冷表的数据量小于整个Buffer Pool的3/8，能够完全放入old区域的情况。

如果这个冷表很大，就会出现另外一种情况：业务正常访问的数据页，没有机会进入young区域。由于优化机制的存在，一个正常访问的数据页，要进入young区域，需要隔1秒后再次被访问到。但是，由于我们的join语句在循环读磁盘和淘汰内存页，进入old区域的数据页，很可能在1秒之内就被淘汰了。这样，就会导致这个MySQL实例的Buffer Pool在这段时间内，young区域的数据页没有被合理地淘汰。

大表join操作虽然对IO有影响，但是在语句执行结束后，对IO的影响也就结束了。但是，对Buffer Pool的影响就是持续性的，需要依靠后续的查询请求慢慢恢复内存命中率。为了减少这种影响，可以考虑增大join_buffer_size 的值，减少对被驱动表的扫描次数，也就是说，BNL算法对系统的影响主要包括三个方面：

1.可能会多次扫描被驱动表，占用磁盘IO资源。

2.判断join条件需要执行M*N次对比(M、N分别是两张表的行数)，如果是大表就会占用非常多的CPU资源。

3.可能会导致Buffer Pool的热数据被淘汰，影响内存命中率。

我们执行语句之前，需要通过理论分析和查看 explain 结果的方式，确认是否要使用 BNL 算法。如果确认优化器会使用 BNL 算法，就需要做优化。优化的常见做法是，给被驱动表的 join 字段加上索引，把 BNL 算法转成 BKA 算法。

BNL转BKA

一些情况下，我们可以直接在被驱动表上建索引，这时就可以直接转成BKA算法了，但是，有时候确实会碰到一些不适合在被驱动表上建索引的情况。比如下面的语句：

select * from t1 join t2 on (t1.b=t2.b) where t2.b>=1 and t2.b<=2000;

表t2中插入了100万行数据，但是经过where条件过滤后，需要参与join的只有2000行数据。如果这条语句同时是一个低频的SQL语句，那么再为这个语句在表t2字段的b上创建一个索引就很浪费了。但是，如果使用BNL算法来join的话，这个语句的执行流程是这样的：

1.把表t1的所有字段取出来，存入join_buffer中。这个表只有1000行，join_buffer_size默认值是256K，可以完全存入。

2.扫描表t2,取出每一行数据跟join_buffer中的数据进行对比

• 如果不满足t1.b=t2.b，则跳过
• 如果满足t1.b=t2.b，在判断其他条件，也就是是否满足t2.b处于[1,2000]的条件，如果是，就作为结果集的一部分返回，否则跳过

对于表t2的每一行，判断join是否满足的时候，都需要遍历join_buffer中的所有行。因此判断等值条件的次数是1000*100万=10亿次，这个判断的工作量很大。

可以看到，explain结果是Extra字段显示使用了BNL算法。在测试环境里，这条语句需要执行1分11秒。在表t2的字段b上创建索引会浪费资源，但是不创建索引的话这个语句的等值条件要判断10亿次，这时候，我们可以考虑使用临时表。使用临时表的大致思路是：

1.把表t2中满足条件的数据放在临时表tmp_t中

2.为了让join使用BKA算法，给临时表tmp_t的字段b加上索引

3.让表t1和tmp_t做join操作

此时，对应的SQL语句的写法如下：

create temporary table temp_t(id int primary key, a int, b int, index(b))engine=innodb;
insert into temp_t select * from t2 where b>=1 and b<=2000;
select * from t1 join temp_t on (t1.b=temp_t.b);

语句序列的执行效果如下：

可以看到，整个过程3个语句执行时间的总和还不到1秒，相比于前面的1分11秒，性能得到了大幅度提升。语句的执行流程如下：

1.执行 insert 语句构造 temp_t 表并插入数据的过程中，对表 t2 做了全表扫描，这里扫描行数是 100 万。

2.之后的 join 语句，扫描表 t1，这里的扫描行数是 1000；join 比较过程中，做了 1000 次带索引的查询。相比于优化前的 join 语句需要做 10 亿次条件判断来说，这个优化效果还是很明显的。

总体来看，不论是在原表上加索引，还是用有索引的临时表，我们的思路都是让 join 语句能够用上被驱动表上的索引，来触发 BKA 算法，提升查询性能。

扩展 -hash join

如果 join_buffer 里面维护的不是一个无序数组，而是一个哈希表的话，那么就不是 10 亿次判断，而是 100 万次 hash 查找。这样的话，整条语句的执行速度就快多了，这也正是 MySQL 的优化器和执行器一直被诟病的一个原因：不支持哈希 join。并且，MySQL 官方的 roadmap，也是迟迟没有把这个优化排上议程。实际上，这个优化思路，我们可以自己实现在业务端。实现流程大致如下：

1.select * from t1;取得表 t1 的全部 1000 行数据，在业务端存入一个 hash 结构。

2.select * from t2 where b>=1 and b<=2000; 获取表 t2 中满足条件的 2000 行数据。

3.把这 2000 行数据，一行一行地取到业务端，到 hash 结构的数据表中寻找匹配的数据。满足匹配的条件的这行数据，就作为结果集的一行。

理论上，这个过程会比临时表方案的执行速度还要快一些。

小结：

在这些优化方法中：

1.BKA 优化是 MySQL 已经内置支持的，建议默认使用；

2.BNL 算法效率低，建议你都尽量转成 BKA 算法。优化的方向就是给被驱动表的关联字段加上索引；

3.基于临时表的改进方案，对于能够提前过滤出小数据的 join 语句来说，效果还是很好的；

4.MySQL 目前的版本还不支持 hash join，但你可以配合应用端自己模拟出来，理论上效果要好于临时表的方案。

二.存储数据相关

2.1 WAL技术

MySQL里的WAL技术，全称是Write-Ahead Logging，他的关键点是先写日志，再写磁盘。具体来说，当有一条记录需要更新的时候，InnoDB引擎就会先把记录写到redo log里面，并更新内存，这个时候更新就算完成了。同时，InnoDB会在空闲的时候，将这个操作记录更新到磁盘里面。

执行器和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)状态，更新完成。

2.2 存在两种日志的原因

binlog不能去掉的原因在于：

1.redo log只有InnoDB有，别的引擎没有。

2.redo log是循环写的，不持久保存，binlog的归档功能，是redo log不具备的。

redo log不能去掉的原因：

1.redo log是顺序写，数据文件是随机写。虽然都是写盘，顺序写还是快很多。

2.更新操作都是基于内存更新的，并且写redo log，等空闲时间在将其redo log所做的改变更新到磁盘中。

3.binlog不支持回滚，binlog只在事务提交时记录一下，redo log在事务进行中不断地被写入。

2.3 redo log buffer

redo log buffer是什么？是先修改内存，还是先写redo log文件？

在一个事务的更新过程中，日志是要写多次的。比如下面这个事务：

begin;
insert into t1 ...
insert into t2 ...
commit;

这个事务要往两个表中插入记录，插入数据的过程中，生成的日志都得先保存起来，但又不能在还没commit的时候就直接写到redo log文件里。所以，redo log buffer就是一块内存，用来先存redo日志的。也就是说，在执行第一个insert的时候，数据的内存被修改了，redo log buffer也写入了这条日志。

但是，真正把日志写到redo log文件(文件名是ib_logfile+数字)，是执行commit语句的时候做的。单独执行一个更新语句的时候，InnoDB会自己启动一个事务，在语句执行完成的时候提交。过程跟上面是一样的，只不过是压缩到了一个语句里面完成。

2.4 数据持久化

正常运行中的实例，数据写入后的最终落盘，是从redo log更新过来还是从buffer pool(MySQL保存数据的一块内存空间)更新过来呢？
实际上，redo log并没有记录数据页的完整数据，所以它并没有能力自己去更新磁盘数据页，也就不存在数据最终落盘，是由redo log更新过去的情况。

1.如果是正常运行的实例的话，数据页被修改以后，跟磁盘的数据页不一致，称为脏页。最终数据落盘，就是把内存中的数据页写盘。跟redo log毫无关系。

2.在崩溃恢复场景中，InnoDB如果判断到一个数据页可能在崩溃恢复的时候丢失了更新，就会将它读到内存，然后让redo log更新内存的内容。更新完成后，内存页变成脏页，就回到了第一种情况的状态。

2.5 binlog写入机制

其实，binlog的写入逻辑比较简单：事务执行过程中，先把日志写到binlog cache，事务提交的时候，再把binlog cache写到binlog文件中。一个事务的binlog是不能被拆开的，因此不论这个事务多大，也要确保一次性写入。这就涉及到了binlog cache的保存问题。

系统给binlog cache分配了一片内存，每个线程一个，参数binlog_cache_size用于控制单个线程内binlog cache所占内存的大小。如果超过了这个参数规定的大小，就要暂存到磁盘。事务提交的时候，执行器把binlog cache里的完整事务写入到binlog中，并清空binlog cache。

可以看到，每个线程有自己的binlog cache，但是共用同一份binlog文件。

• 图中的write，指的就是把日志写入到文件系统page cache(文件系统内存)，并没有把数据持久化到磁盘，所以速度比较快。
• 图中的fsync，才是将数据持久化到磁盘的操作。一般情况下，我们认为fsync才占磁盘的IOPS。

write和fsync的时机，是由参数sync_binlog 控制的：

1.sync_binlog=0的时候，表示每次提交事务都只write，不fsync。

2.sync_binlog=1的时候，表示每次提交事务都会执行fsync。

3.sync_binlog=N(N>1)的时候，表示每次提交事务都write，但累积N个事务后才fsync。

因此，在出现IO瓶颈的场景里，将sync_binlog设置成一个比较大的值，可以提升性能。在实际的业务场景中，考虑到丢失日志量的可控性，一般不建议将这个参数设成0，比较常见的是将其设置为100-1000中的某个数值。但是，将sync_binlog设置为N，对应的风险是：如果主机发生异常重启，会丢失最近N个事务的binlog日志。

2.6 redo log写入机制

事务在执行过程中，生成的redo log是要先写到redo log buffer的。而redo log buffer里面的内容，并不是每次生成后都要直接持久化到磁盘的。如果事务执行期间MySQL发生异常重启，那这部分日志就丢了。由于事务并没有提交，所以这时日志丢了也不会有损失。

当然在事务还没提交的时候，redo log buffer中的部分日志也有可能被持久化到磁盘中，redo log存在三种状态，如图：

1.存在redo log buffer中，物理上是在MySQL进程内存中，就是图中的红色部分。

2.写到磁盘(write)，但是没有持久化(fsync)，物理上是在文件系统的page cache里面，也就是图中的黄色部分。

3.持久化到磁盘，对应的是hard dish，也就是图中的绿色部分，

日志写到redo log buffer是很快的，write到page cache也差不多，但是持久化到磁盘的速度就慢多了。为了控制redo log的写入策略，InnoDB提供innodb_flush_log_at_trx_commit参数，它有三种可能取值：

1.设置为0的的时候，表示每次事务提交时都只是把redo log留在redo log buffer中。

2.设置为1的时候，表示每次事务提交时都将redo log直接持久化到磁盘。

3.设置为2的时候，表示每次事务提交时都只是把redo log写到page cache。

InnoDB有一个后台线程，每隔1秒，就会把redo log buffer中的日志，调用write写到文件系统的page cache，然后调用fsync持久化到磁盘。注意，事务执行中间过程的redo log也是直接写到redo log buffer中的，这些redo log也会被后台线程一起持久化到磁盘，也就是说，一个没有提交事务的redo log，也是可能已经持久化到磁盘的。

实际上，除了后台线程每秒一次的轮询操作外，还有两种场景会让一个没有提交事务的redo log写入到磁盘中：

1.一种是，redo log buffer占用的空间即将达到innodb_log_buffer_size一半的时候，后台线程会主动写盘。注意，由于这个事务并没有提交，所以这个写盘动作只是write。而没有调用fsync，也就是只留在了文件系统的page cache。

2.另一种是，并行的事务提交的时候，顺带将这个事务的redo log buffer持久化到磁盘。假设一个事务A执行到一半，已经写了一些redo log到redo log buffer中，这时候有另外一个线程的事务B提交，如果innodb_flush_log_at_trx_commit设置的是1，那么按照这个参数的逻辑，事务B要把redo log buffer里的日志全部持久化到磁盘。这时候，就会带上事务A在redo log buffer里的日志一起持久化到磁盘。

这里需要说明的是，两阶段提交的时候说过，时序上redo log先prepare，再写binlog，最后再把redo log commit。

如果把innodb_flush_log_at_trx_commit设置成1，那么redo log在prepare阶段就要持久化一次，因为有一个崩溃恢复逻辑是要依赖于prepare的redo log，再加上binlog来恢复的。每秒一次后台轮询刷盘，再加上崩溃恢复这个逻辑，InnoDB就认为在commit的时候就不需要fsync了，只会write到文件系统的page cache中就够了。

通常我们说MySQL的双1配置，指的就是sync_binlog和innodb_flush_log_at_trx_commit 都设置成1。也就是说，一个事务完整提交前，需要等待两次刷盘，一次是redo log(prepare阶段)，一次是binlog。

2.7 binlog的三种格式

mysql> CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `a` int(11) DEFAULT NULL,
  `t_modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `a` (`a`),
  KEY `t_modified`(`t_modified`)
) ENGINE=InnoDB;

insert into t values(1,1,'2018-11-13');
insert into t values(2,2,'2018-11-12');
insert into t values(3,3,'2018-11-11');
insert into t values(4,4,'2018-11-10');
insert into t values(5,5,'2018-11-09');

binlog有三种格式，一种是statement，一种是row，还有一种是mixed，当我们执行下面的SQL语句时：

mysql> delete from t /*comment*/  where a>=4 and  t_modified<='2018-11-10' limit 1;

当binlog_format=statement时，binlog里面记录的就是SQL语句的原文。可以用

mysql> show binlog events in 'master.000001';

命令看binlog中的内容。

• 第一行SET@@SESSION.GTID_NEXT='ANONYMOUS’可以暂时忽略。
• 第二行是一个BEGIN，跟第四行的commit对应，表示中间是一个事务。
• 第三行就是真实执行的语句了，可以看到，在真实执行的delete命令之前，还有一个use test命令。这条命令不是我们主动执行的，而是MySQL根据当前要操作的表所在的数据库，自行添加的。这样做可以保证日志传到备库去执行的时候，不论当前的工作线程在哪个库里，都能够正确地更新到test库的表t。
  usetest命令之后的delete语句，就是我们输入的SQL原文了。可以看到，binlog忠实地记录了SQL命令，甚至连注解也一并记录了。
• 最后一行是一个COMMIT。可以看到里面写着xid=61，这个XID用来保证binlog的一个事务是完整执行的，并且用于和redo log的XID进行管理的。

注意delete 带 limit，很可能会出现主备数据不一致的情况。比如上面这个例子：

1.如果delete语句使用的是索引a，那么会根据索引a找到第一个满足条件的行，也就是说删除的是a=4这一行。

2.但如果使用的是索引t_modified，那么删除的就是t_modified='2018-11-09’也就是a=5这一行。

由于statement格式下，记录到binlog里的是语句原文，因此可能会出现这样一种情况：在主库执行这条SQL语句的时候，用的是索引a；而在备库执行这条SQL语句的时候，却使用了索引t_modifed。

如果把 binlog的格式改为 binlog_format=‘row’， 我们来看看这时候 binog中的内容

可以看到，与statement格式的binlog相比，前后的BEGIN和COMMIT是一样的。但是，row格式的binlog里没有了SQL语句的原文，而是替换成了两个event：Table_map和Delete_rows。

1.Table_map event用于说明接下来要操作的表是test库的表t

2.Delete_rows event用来定义删除行为

其实，我们通过图 5 是看不到详细信息的，还需要借助 mysqlbinlog 工具，用下面这个命令解析和查看 binlog 中的内容。因为图 5 中的信息显示，这个事务的 binlog 是从 8900 这个位置开始的，所以可以用 start-position 参数来指定从这个位置的日志开始解析。

mysqlbinlog  -vv data/master.000001 --start-position=8900;

从图中可以看到以下几个信息：

• server id 1，表示这个事务是在server_id=1的这个库上执行的。
• 每个event都有CRC32的值，这时因为把参数binlog_checksum 设置成了CRC32。
• Table_map event显示了接下来要打开的表，map到数字226。现在这条SQL语句只操作了一张表，如果要操作多张表，每个表都有一个对应的Table_map event、都会map到一个单独的数字，用于区分对不同表的操作。
• 在mysqlbinlog的命令中，使用了-vv参数是为了把内容都解析出来，所以从结果里面可以看到各个字段的值(比如@1=4、@2=4的值)
• binlog_row_image的默认配置是FULL，因此Delete_event里面，包含了删掉的行的所有字段的值。如果把binlog_row_image设置为MINIMAL，则只会记录必要的信息，在这个例子里，就是只会记录id=4这个信息。
• 最后的XID event用于表示事务被正确地提交了。

可以看到，当 binlog_format 使用 row 格式的时候，binlog 里面记录了真实删除行的主键 id，这样 binlog 传到备库去的时候，就肯定会删除 id=4 的行，不会有主备删除不同行的问题(主备使用到的索引可能不同)。

用 binlog 来恢复数据的标准做法是，用 mysqlbinlog 工具解析出来，然后把解析结果整个发给 MySQL 执行。类似下面的命令：

mysqlbinlog master.000001  --start-position=2738 --stop-position=2973 | mysql -h127.0.0.1 -P13000 -u$user -p$pwd;

这个命令的意思是，将 master.000001 文件里面从第 2738 字节到第 2973 字节中间这段内容解析出来，放到 MySQL 去执行。

为什么会有mixed格式的binlog？

• 因为有些statement格式的binlog可能会导致主备不一致，所以要使用row格式。
• 但row格式的缺点是，很占空间。比如用一个delete语句删掉10万行数据，用statement的话就是一个SQL语句被记录到binlog中。但如果用row格式的binlog，就要把这10万条记录都写到binlog中。这样做，不仅会占用更大的空间，同时写binlog也要耗费IO资源，影响执行速度。
• 所以，MySQL就取了个折中方案，也就是有了mixed格式的binlog。mixed格式的意思是，MySQL自己会判断这条SQL语句是否可能引起主备不一致，如果有可能，就用row格式，否则就用statement格式。

2.8 内存命中率

WAL机制的作用是，是保存更新的结果，再配合redo log，就避免了随机写盘。内存的数据页是在Buffer Pool中管理的，在WAL里Buffer Pool起到了加速更新的作用。而实际上，Buffer Pool(BP)还有一个更重要的作用，就是加速查询。由于有WAL机制，当事务提交的时候，磁盘上的数据页是旧的，如果这时候马上有一个查询要来读这个数据页，并不需要马上把redo log应用到数据页。

因为这时候内存数据页的结果是最新的，直接读内存页就可以了。直接从内存拿结果速度是很快的，所以说，Buffer Pool 还有加速查询的作用。而Buffer Pool 对查询的加速效果，依赖于一个重要的指标，即：内存命中率。可以在 show engine innodb status结果中，查看一个系统当前的BP命中率。一般情况下，一个稳定服务的线上系统，要保证响应时间符合要求的话，内存命中率要99%以上。

执行 show engine innodb status，可以看到Buffer pool hit rate字样，显示的就是当前的命中率。如下图：

如果所有查询需要的数据页都能够直接从内存得到，那是最好的，对应的命中率就是 100%。但，这在实际生产上是很难做到的。InnoDB Buffer Pool的大小是由参数innodb_buffer_pool_size 确定的，一般建议设置成可用物理内存的60%-80%。innodb_buffer_pool_size 小于磁盘的数据量是很常见的。如果一个Buffer Pool满了，而又要从磁盘读入一个数据页，那肯定是要淘汰一个旧数据页的。

InnoDB内存管理用的是最近最少使用(Least Recently Used, LRU) 算法，这个算法的核心就是淘汰最久未使用的数据。

InnoDB管理Buffer Pool的LRU算法，是用链表来实现的。

1.在上图的状态1里，链表头部是P1，表示P1是最近刚刚被访问过的数据页；假设内存里只能放下这么多数据页。

2.这时候有一个读请求访问P3，因此变成状态2，P3被移到最前面；

3.状态3表示，这次访问的数据页是不存在于链表中的，所以需要在Buffer Pool中新申请一个数据页Px,加到链表头部。但是由于内存已经满了，不能申请新的内存。于是，会清空链表末尾Pm这个数据页的内存，存入Px的内容，然后放到链表头部。

4.从效果上看，就是最久没有被访问的数据页Pm,被淘汰了。

假设按照这个算法，要扫描一个200G的表，而这个表是一个历史数据，平时没有业务访问它。那么，按照这个算法扫描的话，就会把当前的Buffer Pool里的数据全部淘汰掉，存入扫描过程中访问到的数据页的内容。也就是说Buffer Pool里面主要放的是这个历史数据表的数据。

这会导致Buffer Pool的命中率急剧下降，磁盘压力增加，SQL语句响应变慢。所以，InnoDB不能直接使用这个LRU算法。实际上InnoDB对LRU算法做了改进。

在InnoDB实现上，按照5:3的比例把整个LRU链分成了young区域和old区域。图中LRU_old指向的就是old区域的第一个位置，是整个链表的5/8处。也就是说，靠近链表头部的5/8是young区域，靠近链表尾部的3/8是old区域。改进后的LRU算法执行流程变成了下面这样：

1.上图状态1，要访问数据页P3，由于P3在young区域，因此和优化前的LRU算法一样，将其移到链表头部，变成状态2。

2.之后要访问一个新的不存在于当前链表的数据页，这时候依然是淘汰掉数据页Pm，但是新插入的数据页Px，是放在LRU_old处。

3.处于old区域的数据页，每次被访问的时候都要做下面这个判断：

• 若这个数据页在LRU链表中存在的时间超过了1秒，就把它移动到链表头部。
• 如果这个数据页在LRU链表中存在的时间短于1秒，位置保持不变。1秒这个是由参数innodb_old_blocks_time 控制的，其默认值是1000，单位毫秒。

使用这个策略，看刚刚扫描200G历史数据的例子：

1.扫描过程中，需要新插入的数据页，都被放到old区域。

2.一个数据页里面有多条记录，这个数据页会被多次访问到，但由于是顺序扫描，这个数据页第一次被访问和最后一次被访问的时间间隔不会超过1秒，因此还是会被保留在old区域。(200G不能一次读进去，后面的数据会顶掉前面的数据)

3.在继续扫描后续的数据，之前的这个数据页之后也不会再被访问到，于是始终没有机会移到链表头部(也就是young区域)，很快就会被淘汰出去。

可以看到，这个策略最大的收益，就是在扫描这个大表的过程中，虽然也用到了 Buffer Pool，但是对 young 区域完全没有影响，从而保证了 Buffer Pool 响应正常业务的查询命中率。

三.事务相关

3.1 事务隔离的实现

在MySQL中，实际上每条记录在更新的时候都会同时记录一条回滚操作。记录上的最新值，通过回滚操作，都可以得到前一个状态的值。假设一个值从1被按顺序改成了2、3、4，那么在回滚日志里面会有类似下面的记录：

当前值是4，但是在查询这条记录的时候，不同时刻启动的事务会有不同的read-view。如图中看到的，在视图A、B、C里面，这一条记录的值分别是1、2、4。对于read-writeA，要得到1，就必须将当前值依次执行图中所有的回滚操作得到。同一条记录在系统中可以存在多个版本，就是数据库的多版本并发控制(MVCC)

当系统里没有比这个回滚日志更早的read-view的时候，回滚日志就会被删除，即没有事务需要用到这些回滚日志。即一个事务提交时，事务会用到这个read-view时，就会删除掉。

这就是为什么不推荐使用长事务的原因，长事务意味着会保存很老的事务视图，由于这些事务随时可能访问数据库里面的任何数据，所以这个事务提交之前，数据库里面他可能用到的回滚记录都必须保留，这就会导致大量占用存储空间。

3.2 可重复读隔离级别

在可重复读隔离级别下，事务T启动的时候会创建一个视图read-view，之后事务T执行期间，即使有其他事务修改了数据，事务T看到的仍然跟在启动时看到的一样。在行锁中，一个事务要更新一行，如果刚好有另外一个事务拥有这一行的行锁，它就会被锁住，进入等待状态。问题在于等到这个事务自己获取到行锁要更新数据的时候，它读到的值又是什么。

mysql> 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);

begin/start transaction命令并不是一个事务的起点，在执行到它们之后的第一个操作InnoDB表的语句，事务才真正启动。如果你想马上启动一个事务，可以使用start tranaction with consistent snapshot这个命令。

事务C没有显示地使用begin/commit，表示这个update语句本身就是一个事务，语句完成的时候会自动提交。事务B在更新行之后查询；事务A在一个只读事务中查询，并且时间顺序是在事务B之后。这时候的结果是事务B读到的值为3，事务A读到的值为1；

简单来说，产生这个区别的原因是：

1.事务A启动的时候，就拍了个快照。这个快照是将之前事务做的修改的数据保存起来的。而之后事务做的修改数据，对事务A来说都是不相关的，所以事务A读出来的值是1，这里的读为一致性读。
2.当事务B执行第一条update语句的时候，事务C已经将值修改为2了，之后事务B更新的逻辑是，先读后写的，而这个读，只能读当前的值，称为当前读，因此，在更新的时候，当前读拿到的数据是(1,2)，更新后生成了新版本的数据(1,3)。所以，事务B的查询语句执行的时候，看到是自己的更新，可以直接使用，所以查询得到的k=3。

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

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

如果事务C不是马上提交的，而是变成了下面的事务C’。事务C’不同是，更新后并没有马上提交，在它提交前，事务B的更新语句先发起了，虽然事务C’还没提交，但是(1,2)这个版本已经生成了，并且是当前的最新版本，而事务B根据两阶段锁协议，事务C’没提交，也就是说这行的写锁还没释放，而事务B是当前读，必须读最新版本，而且必须加锁，因此就被锁住了，必须等到事务C’释放这个锁，才能继续它的当前读。

3.3 读提交和可重复读的区别

读提交的逻辑和可重复读的逻辑类似，最主要的区别是：

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

那么，在读提交级别下，事务A和事务B查询的语句分别是多少呢？这时候事务B的结果不变，因为最后一次的更新就是它自己做的，但是对于事务A来说(1,2)这个结果，在事务A执行查询语句创建一个视图前，属于情况3，是可见的，即事务A的K=2(事务B未提交，读不到K=3)；

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

四.索引相关

4.1 联合索引的使用

如果有索引:(a,b),©,(c,a),(c,b)，如果有如下字段。

select * from geek where c=N order by a limit 1;
select * from geek where c=N order by b limit 1;

那么其实索引(c,a)是可以去掉的，原因在于使用索引(c,a)时根据最左前缀原则，其实是和单独使用索引©的效果是一致的

InnoDB会把主键字段放到索引定义字段后面，当然同时也会去重。所以，当主键是(a,b)的时候，定义为c的索引，实际上是（c,a,b);定义为(c,a)的索引，实际上是(c,a,b)。

4.2 普通索引和唯一索引

查询过程：

唯一索引和普通索引在查询语句中，两者的性能并不会相差太大，对于普通索引来说，要多做的那一次"查找和判断下一条记录是否符合条件"的操作，就只需要一次指针寻找和一次计算，这个操作并不会影响过多的性能。

更新过程：

当需要更新一个数据页时，如果数据页在内存中就直接更新，而如果这个数据页还没有在内存中的话，在不影响数据一致性的前提下，InnoDB会将这些更新操作缓存在change buffer中，这样就不需要从磁盘中读入这个数据页了。在下次查询需要访问这个数据页的时候，将数据页读入内存，然后执行change buffer中与这个页有关的操作。

change buffer在内存中有拷贝，也会被写入到磁盘上。将change buffer中的操作应用到原数据，得到最新结果的过程称为merge。除了访问这个数据页会触发merge外，系统有后台线程会定期merge。在数据库正常关闭的过程中，也会执行merge操作。

显然，如果能够将更新操作先记录在change buffer，减少读磁盘，语句的执行速度会得到明显的提升。而且，数据读入内存是需要占用buffer pool的，所以这种方式还能够避免占用内存，提高内存利用率。

对于唯一索引来说，所有的更新操作都要先判断这个操作是否违反唯一性约束。比如，要插入(4,400)这个记录，就要先判断现在表中是否已经存在k=4的记录，而这必须要将数据页读入内存才能判断。如果都已经读入内存了，那直接更新内存会更快，没有必要使用change buffer了。

所以在更新操作中，前者每次都要将数据也加载到内存中，而将数据从磁盘读入内存涉及随机IO的访问，是数据库里面成本最高的操作之一，而后者只需要将更新记录在change buffer中。change buffer的操作也记录到redo log里，主机异常重启，是不会丢失change buffer和数据的。

4.3 索引选择异常和处理

大多数时候优化器都能找到正确的索引，但偶尔还是会碰到选择错误的情况，原本可以执行得很快的SQL语句，执行速度却比预期慢很多，有以下方法可以去解决：

一种方法是，采用force index强行选择一个索引。MySQL会根据词法解析的结果分析出可能可以使用的索引作为候选项，然后再候选列表中依次判断每个索引需要扫描多少行。如果force index指定的索引在候选索引列表中，就直接选择这个索引，不再评估其他索引的执行代价。

也就是说，优化器没有选择正确的索引，force index起到了矫正的作用，不过很多程序员不喜欢使用force index，一来这么写不优美，二来如果索引改了名字，这个语句也得改，显得很麻烦。其实选错索引的情况还是比较少出现的，所以开发的时候通常不会先写上force index。而是等到线上出现问题的时候，才会去修改SQL语句。

第二种方法就是，我们可以考虑修改语句，引导MySQL使用我们期望的索引。比如，把order by b limit 1改成order by b,a limit1，语义的逻辑是相同的。
之前优化器选择使用索引b，是因为他认为使用索引b可以避免排序(b本身是索引，已经是有序的了，如果选择索引b的话，不需要再做排序，只需要遍历)，所以即使扫描行数多，也判定为代价更小。现在order by b，a这种写法，要求按照b，a排序，就意味着使用这两个索引都需要排序。因此，扫描行数成了影响决策的主要条件。

当然修改语义这种办法，不并具备通用性，第三种方法是，在有些场景下，我们可以新建一个更适合的索引，来提供给优化器做选择，或删掉误用的索引。

4.4 前缀索引

前缀索引长度定义：

mysql> alter table SUser add index index1(email);
或
mysql> alter table SUser add index index2(email(6));

使用前缀索引，定义好长度，就可以做到既节省空间，又不用额外增加太多的查询成本。实际上，我们建立索引时关注的是区分度，区分度越高越好。因为区分度越高，意味着重复的键值越少。因此，我们可以通过统计索引上有多少个不同的值来判断要使用多长的前缀索引。
首先，可以使用下面这个语句，算出这个列上有多少个不同的值：

mysql> select count(distinct email) as L from SUser;

然后，依次选取不同长度的前缀来看这个值，比如我们要看一下4-7个字节的前缀索引，可以用这个语句：

mysql> select 
  count(distinct left(email,4)）as L4,
  count(distinct left(email,5)）as L5,
  count(distinct left(email,6)）as L6,
  count(distinct left(email,7)）as L7,
from SUser;

当然，使用前缀索引很可能会损失区分度，所以需要预先设定一个可以接受的损失比例，比如5%。然后，在返回的L4-L7中，找出不小于L*95%的值，假设这里L6、L7都满足，就可以选择前缀长度为6。
前缀索引的影响不止上述的说明，如下SQL语句，需要返回id和email字段。所以，如果使用不指定长度的索引，可以利用覆盖索引，从索引查到结果后直接就返回了，不需要回到ID索引再去查一次，而如果使用前缀索引的话，就不得不回到ID索引再去判断email字段的值。也就是说，使用前缀索引就用不上覆盖索引对查询性能的优化了。

select id,email from SUser where email='zhangssxyz@xxx.com';

对于类似邮箱这样的字段来说，使用前缀索引的效果可能还不错。但是，遇到前缀的区分度不够好的情况时，比如，我们国家的身份证号，一共18位，其中前6位是地址码，所以同一个县的人的身份证号前6位一般会是相同的。
如果我们能够确定业务需要里面只有按照身份证进行等值查询的需求，就还有其余的方法来保证既可以占用更小的空间，也能达到相同的查询效率。

第一种方式就是使用倒序存储，如果你存储身份证号的时候把他倒过来存，每次查询的时候可以这么写。

mysql> select field_list from t where id_card = reverse('input_id_card_string');

由于身份证号的最后6位没有地址码这样的重复逻辑，所以最后这6位很可能就提供了足够的区分度，当然了实践的时候还是需要用count(distinct)方法去做个验证。

第二种方式是使用hash字段，可以在表上再创建一个整数字段，来保证身份证的校验码，同时在这个字段上创建索引。

mysql> alter table t add id_card_crc int unsigned, add index(id_card_crc);

然后每次插入新记录的时候，都同时用crc32()这个函数得到校验码填到这个新字段。由于校验码可能存在冲突，也就是说两个不同的身份证号通过crc32()函数得到的结果可能是相同的，所以查询语句where部分要判断Id_card的值是否精确相同。

mysql> select field_list from t where id_card_crc=crc32('input_id_card_string') and id_card='input_id_card_string'

这样，索引的长度变成了4个字节，比原来小了很多。对比两种的异同点，首先它们的相同点是，都不支持范围查询。倒序存储的字段上创建的索引是按照倒序字符串的方式排序的，已经没有办法利用索引方式查出身份证号码在[ID_X，ID_Y]的所有市民了。同样地，hash字段的方式也只能支持等值查询。

区别主要体现以下三个方面：

1.从占用的额外空间来看，倒序存储方式在主键索引上，不会消耗额外的存储空间，而hash字段方法需要增加一个字段。当然，倒序存储方式使用4个字节的前缀长度应该是不够的，如果再长一点，这个消耗跟额外这个hash字段也差不多抵消

2.在CPU消耗方面，倒序方式每次写和读的时候，都需要额外调用一次reverse函数，而hash字段的方式需要额外调用一次crc32()函数。如果只从这两个函数的计算复杂度来说，reverse函数额外消耗的CPU资源会更小些。

3.从查询效率上看，使用hash字段方式的查询性能相对更稳定一些因为crc32算出来的值虽然有冲突的概率，但是概率非常小，可以认为每次查询的平均扫描行数接近1。而倒序存储方式毕竟还是用的前缀索引的方式，也就是说还是会增加扫描行数。

4.5 使用索引注意事项

对索引字段做函数操作，可能会破坏索引值的有序性，因此优化器就决定放弃走树搜索功能。即使是对于不改变有序性的函数，也不会考虑使用索引。比如，select * from tradelog where id+1=10000这个SQL语句，这个加1操作并不会改变有序性，但是MySQL优化器还是不能用id索引快速定位到9999这一行。必须改成where id=10000-1才可以。

在交易编号tradeid这个字段上，本来就有索引，但是explain的结果却显示，这条语句需要走全表扫描。原因是因为tradeid的字段类型是varchar(22)，而输入的参数却是整型，所以需要做类型转换。

mysql> select * from tradelog where tradeid=110717;

那么，这里就有两个问题：

1.数据类型转换的规则是什么？

2.为什么有数据类型转换，就需要走全索引扫描？

有一个简单的方法，可以解决第一个问题，看select “10”>9的结果：

1.如果规则是"将字符串转成数字"，那么就是做数字比较，结果应该是1；

2.如果规则是"将数字转成字符串"，那么就是做字符串比较，结果应该是0；

验证结果为1，所以可以确认MySQL里的转换规则：字符串和数字做比较的话，是将字符串转换成数字。于是该SQL语句，在优化器看来相当于：

mysql> select * from tradelog where CAST(tradid AS signed int) = 110717;

也就是说，这条语句触发了我们上面说到的规则：对索引字段做函数操作，优化器会放弃走树搜索功能。

隐式字符编码转换utf8和utf8mb4也会触发索引的函数转换

a表有100条记录，b表有10000条记录，两张表做关联查询时，是将a表放前面效率高，还是b表放前面效率高。
每次在树搜索里面做一次查找都是log(n), 所以对比的是100log(10000)和 10000log(100)哪个小，显然是前者，所以结论应该是让小表驱动大表。

五.锁相关

5.1 MDL

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

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

5.2 死锁检测

问题：
MySQL突然挂了，CPU消耗接近100%，但整个数据库每秒就执行不到100个事务。

原因：
主动死锁检测在发生死锁的时候，是能够快速发现并进行处理的，但是他也有额外的负担，每当一个事务被锁的时候，就要看看它所依赖的线程有没有被别人锁住，如此循环，最后判断是否出现了循环等待，也就是死锁。

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

解决办法：
一种简陋的方法是，如果你能确定这个业务不会出现死锁，可以临时把死锁检测关掉。但是关掉死锁检测意味着可能会出现大量的超时，这时业务有损的。

另一个思路是控制并发度，控制每一行同时最多只有10个线程在更新，基本思路是，对于相同行的更新，在进入引擎之前排队。这样就不会有大量的死锁检测工作了。这种操作最好做在中间层或者MySQL里面，做在客户端是不能解决的。

最后一种方案，可以考虑通过将一行改成逻辑上的多行来减少锁冲突，可以考虑放在多条记录上，最后的总额等于多条记录相加，当然需要对业务逻辑做详细涉及，比如某行记录为0时，发生退票的情况。

5.3 关于幻读

建表和初始化语句如下，这个除了主键id外，还有一个索引c，初始化语句在表中插入了6行数据。

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;

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

锁的设计是为了保证数据的一致性，而这个一致性，不止是数据库内部数据状态在此刻的一致性，还包含了数据和日志在逻辑上的一致性。这时给sessionA在T1时刻再加一个更新语句。

sessionA声明说要给d=5语句加上锁，就是为了要更新数据，新加的这条update语句就是把它认为加上了锁的这一行的d值修改成了100。

分析一下图3执行完成后，数据库的结果：

1.经过T1时刻，id=5这一行变成(5,5,100)，当然这个结果最终是再T6时刻正式提交的；

2.经过T2时刻，id=0这一行变成(0,5,5)；

3.经过T4时刻，表里面多了一行(1,5,5)；

这样看，这些数据内部没什么问题，但是这时候看binlog里面的内容，就有问题了，注意binlog里的内容是在事务提交的时候在写入的。

1.T2 时刻，session B 事务提交，写入了两条语句；

2.T4 时刻，session C 事务提交，写入了两条语句；

3.T6 时刻，session A 事务提交，写入了 update t set d=100 where d=5 这条语句。

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

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

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这两行，发生了数据不一致。所以可以得出，我们上面的设定：select * from t where d=5 for update 这条语句只给 d=5 这一行，也就是 id=5 的这一行加锁是不合理的。

而如果我们假设改成把扫描过程中碰到的行，也都加上写锁，执行效果如下：

由于sessionA把所有的行都加了写锁，所有sessionB在执行第一个update语句的时候就被锁住了。需要等到T6时刻sessionA提交以后，sessionB才能继续执行。这样对于id=0这一行，在数据库里的最终结果还是(0,5,5)。在binlog里面，执行序列是这样的：

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

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

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=0这一行的问题解决了。但同时也可以看到，id=1这一行，在数据库里面的结果是(1,5,5)，而根据binlog的执行结果是(1,5,100)，也就是说幻读的问题还是没有解决。原因在于，在T3时刻，我们给所有行加锁的时候，id=1这一行还不存在，不存在也就加不上锁。

间隙锁和行锁的区别在于，跟间隙锁存在冲突关系的，是往这个间隙中插入一个记录这个操作。间隙锁之间都不存在冲突关系。如下例：

这里sessionB并不会被堵住。因为表t并没有c=7这个记录，因此sessionA加的是间隙锁(5,10)。而sessionB也是在这个间隙加的间隙锁。它们有共同的目标，即：保护这个间隙，不允许插入值。但，它们之间是不冲突的。间隙锁和行锁合称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是一个不存在的最大值。

后面的update语句都不需要执行，就已经形成了死锁了。按语句执行顺序分析一下：

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

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

3.sessionB试图插入一行(9,9,9)，被sessionA的间隙锁挡住了，只好进入等待；

4.sessionA也同样插入的时候被sessionB挡住了；

至此，两个session进入互相等待状态，形成写锁。当然，InnoDB的死锁检测马上就会发现了这对死锁关系，让sesssionA的insert语句报错返回了。间隙锁的引入，可能会导致同样的语句锁住更大的范围，这其实是影响了并发度的。当然这些内容都是隔离级别为可重复读的隔离级别，间隙锁也是在可重复读隔离级别下才会生效的。

5.4 间隙锁规则

加锁规则里面包含了两个原则、两个优化和一个bug：

1.原则1：加锁的基本单位是next-key lock(前开后闭区间)。

2.原则2：查找过程中访问到的对象才会加锁。

3.优化1：索引上的等值查询，给唯一索引加锁的时候，next-key lock退化为行锁。

4.优化2：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock退化为间隙锁(前开后开)。

5.一个bug：唯一索引上的范围查询会访问到不满足条件的第一个值为止。

1.非唯一索引等值锁

这里sessionA要给索引c上c=5的这一行加上读锁：

1.根据原则1，加锁单位是next-key lock，因此会给(0,5]加上next-key lock。

2.要注意c是普通索引，因此访问c=5这一条记录是不能马上停下来的，需要向右遍历，查到c=10才放弃。根据原则2，访问到的都要加锁，因此要给(5,10]加next-key lock。

3.但是同时这个符合优化2：等值判断，向右遍历，最后一个值不满足c=5这个等值条件，因此退化成间隙锁(5,10)。

4.根据原则2，只有访问到的对象才会加锁，这个查询使用覆盖索引，并不需要访问主键索引，所有主键索引上没有加任何锁，这就是为什么sessionB的update语句可以执行完成。但sessionC要插入一个记录，就会被sessionA的间隙锁(5,10)锁住。

需要注意，在这个例子中，lock in share mode只锁覆盖索引，但是如果是for update就不一样了。执行for update时，系统会认为你接下来要更新数据，因此会随便给主键索引上满足条件的行加上行锁。这个例子说明，锁是加在索引上的；同时，它给我们的指导是，如果你要用lock in share mode来给行加读锁避免数据被更新的话，就必须绕过覆盖索引优化。

2.主键索引范围锁

1.开始执行的时候，要找到第一个id=10的行，因此本该是next-key lock(5,10]。根据优化1，主键id上的等值条件，退化成行锁，只加了id=10这一行的行锁。

2.范围查找就往后继续找，找到id=15这一行停下来，因此需要加next-key lock(10,15]。

所以，sessionA这时候锁的范围就是主键索引上，行锁id=10和next-key lock(10,15]。需要注意的一点是，首次sessionA定位查找id=10的行的时候，是当作等值查询来判断的，而向右扫描到id=15的时候，用的是范围查询判断。

3.唯一索引范围锁bug

sessionA是一个范围查询，按照原则1的话，应该是索引id只加(10,15]这个next-key lock，并且因为id是唯一键，所以循环判断到id=15这一行就应该停止了。但是实现上，InnoDB会往前扫描到第一个不满足条件的行为止，也就是id=20。而且由于这是个范围扫描，因此索引id上的(15,20]这个next-key lock也会被锁上。

所以可以看到sessionB和sessionC都被锁住了。

4.非唯一索引上存在等值的例子

1.sesssionA启动事务后执行查询加lock in share mode，在索引c加上next-key lock(5,10]和间隙锁(10,15)；

2.sessionB的update语句也要在索引c上加next-key lock(5,10]，进入锁等待；

3.然后sessionA要再插入一行，被sessionB的间隙锁锁住。由于出现了死锁，InnoDB会让sessionB回滚。

之所以会出现死锁，是因为sessionB的加next-key lock(5,10]操作，实际上分成了两步，先是加(5,10)的间隙锁，加锁成功(修改允许加间隙锁)；然后加c=10的行锁，这时候才被锁住的。