在线脑图

先来看表结构：

1
2
3
4
5
6
7
8
9
10
11
12
13
14

CREATE TABLE a (
  `id`bigint AUTO_INCREMENT ,
  `a` int,
  `b` int,
  PRIMARY KEY (`id`),
  KEY `idx_a_b` (`a`,`b`)
);

CREATE TABLE b (
	`id`bigint AUTO_INCREMENT ,
  `b` int,
  `c` int,
  PRIMARY KEY (`id`)
)

看一下join语句，因为b上没有索引，所以mysql用的BLNJ：

1
2
3
4

explain select * from a 
join b using(b)
where a = 1
order by a, b;

如果b表有索引的话：

1
2
3
4
5
6
7

CREATE TABLE b (
	`id`bigint AUTO_INCREMENT ,
  `b` int,
  `c` int,
  PRIMARY KEY (`id`),
  KEY `idx_b` (`b`)
)

可以发现a表idx_a_b有序性没有利用上，至于原因，先看一下BNLJ执行的流程图:

执行过程为：

1. 扫描表 t1，顺序读取数据行放入 join_buffer 中，直到 join_buffer 满了，继续第 2 步；
2. 扫描表 t2，把 t2 中的每一行取出来，跟 join_buffer 中的数据做对比，满足 join 条件的，作为结果集的一部分返回；
3. 清空 join_buffer；
4. 继续扫描表 t1，顺序读取之后数据放入 join_buffer 中，继续执行第 2 步，直到所有数据读取完毕。

其中隐含的问题在于第二步：即使t1表的数据是有序读取到join_buffer中的，由于是先扫描t2表再关联join_buffer数据，导致join_buffer中的有序性失效。

如果表b有索引idx_b,那么使用BKA算法第二步的关联顺序与BNLJ相反，是先扫描join_buffer后通过索引关联t2,则可以利用join_buffer中的有序数据。

为了便于说明问题，我们先使用一个小一点儿的表，建表和初始化语句如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

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

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

下面的语句序列，是怎么加锁的，加的锁又是什么时候释放的呢？

1

select * from t where d = 5 for update;

比较好理解的是，这个语句会命中 d = 5 的这一行，对应的主键 id = 5，因此在 select 语句执行完成后，会在id = 5 这一行主键上加一个写锁，而且由于两阶段锁协议，这个写锁会在执行 commit 语句的时候释放。

由于字段 d 上没有索引，因此这条查询语句会做全表扫描。那么，其他被扫描到的，但是不满足条件的 5 行记录上，会不会被加锁呢？

我们知道，InnoDB 的默认事务隔离级别是可重复读，所以本文接下来没有特殊说明的部分，都是设定在可重复读隔离级别下。

幻读是什么？

现在，我们就来分析一下，假设只在 id = 5 这一行加锁，而其他行的不加锁的话，会怎么样。

下面先来看一下这个场景（这个结果是建立在前面假设之上，实际上是错误的）：

假设只在 id = 5 这一行加行锁，可以看到，session A 里执行了三次查询，分别是 Q1、Q2 和 Q3。它们的 SQL 语句相同，都是 select * fom t where d=5 for update。我们来看一下这三条 SQL 语句，分别会返回什么结果。

1. Q1 只返回 id = 5 这一行；
2. 在 T2 时刻，session B 把 id = 0 这一行的 d 值改成了 5，因此 T3 时刻 Q2 查出来的是 id = 0 和 id = 5 这两行；
3. 在 T4 时刻，session C 又插入一行（1,1,5），因此 T5 时刻 Q3 查出来的是 id = 0、id = 1 和 id = 5 的这三行。

其中，Q3 读到 id = 1 这一行的现象，被称为“幻读”。也就是说，幻读指的是一个事务在前后两次查询同一个范围的时候，后一次查询看到了前一次查询没有看到的行。

这里，我需要对“幻读”做一个说明：

1. 在可重复读隔离级别下，普通的查询是快照读，是不会看到别的事务插入的数据的。因此，幻读在当前读下才会出现。
2. 上面 session B 的修改结果，被 session A 之后的 select 语句用当前读看到，不能称为幻读。幻读仅专指新插入的行。

如果只从我们学到的事务可见性规则来分析的话，上面这三条 SQL 语句的返回结果都没有问题。

因为这三个查询都是加了 for update，都是当前读。而当前读的规则，就是要能读到所有已经提交的记录的最新值。并且，session B 和 sessionC 的两条语句，执行后就会提交，所以 Q2 和 Q3 就是应该看到这两个事务的操作效果，而且也看到了，这跟事务的可见性规则并不矛盾。

幻读有什么问题？

**首先是语义上的。**session A 在 T1 时刻就声明了，“我要把所有 d=5 的行锁住，不准别的事务进行读写操作”。所以我们假设只锁了id = 5这一行的语义与select * from t where d = 5 for update 不同。

其次，是数据一致性的问题。 **这个数据不一致到底是怎么引入的？**肯定是前面的假设有问题。

我们把扫描过程中碰到的行，也都加上写锁，再来看看执行效果。

由于 session A 把所有的行都加了写锁，所以 session B 在执行第一个 update 语句的时候就被锁住了。需要等到 T6 时刻 session A 提交以后，session B 才能继续执行。

这样对于 id = 0 这一行，在数据库里的最终结果还是 (0,5,5)。在 binlog 里面，执行序列是这样的：

1
2
3
4
5
6
7

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)，也就是说幻读的问题还是没有解决。为什么我们已经这么“凶残”地，把所有的记录都上了锁，还是阻止不了 id = 1 这一行的插入和更新呢？

原因很简单。在 T3 时刻，我们给所有行加锁的时候，id = 1 这一行还不存在，不存在也就加不上锁。

**也就是说，即使把所有的记录都加上锁，还是阻止不了新插入的记录，**这也是为什么“幻读”会被单独拿出来解决的原因。

如何解决幻读？

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

顾名思义，间隙锁，锁的就是两个值之间的空隙。比如文章开头的表 t，初始化插入了 6 个记录，这就产生了 7 个间隙。

这样，当你执行 select * from t where d=5 for update 的时候，就不止是给数据库中已有的 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]。

备注：这篇文章中，如果没有特别说明，我们把间隙锁记为开区间，把 next-key lock 记为前开后闭区间。

你可能会问说，这个 supremum 从哪儿来的呢？

这是因为 +∞是开区间。实现上，InnoDB 给每个索引加了一个不存在的最大值 supremum，这样才符合我们前面说的“都是前开后闭区间”。

间隙锁和 next-key lock 的引入，帮我们解决了幻读的问题，但同时也带来了一些“困扰”。

对应到我们这个例子的表来说，业务逻辑这样的：任意锁住一行，如果这一行不存在的话就插入，如果存在这一行就更新它的数据，代码如下：

1
2
3
4
5
6
7
8
9

begin;
select * from t where id=N for update;
 
/* 如果行不存在 */
insert into t values(N,N,N);
/* 如果行存在 */
update t set d=N set id=N;
 
commit;

这个逻辑一旦有并发，就会碰到死锁。你一定也觉得奇怪，这个逻辑每次操作前用 for update 锁起来，已经是最严格的模式了，怎么还会有死锁呢？

这里，我用两个 session 来模拟并发，并假设 N=9。

图 8 间隙锁导致的死锁

你看到了，其实都不需要用到后面的 update 语句，就已经形成死锁了。我们按语句执行顺序来分析一下：

1. session A 执行 select … for update 语句，由于 id = 9 这一行并不存在，因此会加上间隙锁 (5,10);
2. session B 执行 select … for update 语句，同样会加上间隙锁 (5,10)，间隙锁之间不会冲突，因此这个语句可以执行成功；
3. session B 试图插入一行 (9,9,9)，被 session A 的间隙锁挡住了，只好进入等待；
4. session A 试图插入一行 (9,9,9)，被 session B 的间隙锁挡住了。

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

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

你可能会说，为了解决幻读的问题，我们引入了这么一大串内容，有没有更简单一点的处理方法呢。

我在文章一开始就说过，如果没有特别说明，今天和你分析的问题都是在可重复读隔离级别下的，间隙锁是在可重复读隔离级别下才会生效的。所以，你如果把隔离级别设置为读提交的话，就没有间隙锁了。但同时，你要解决可能出现的数据和日志不一致问题，需要把 binlog 格式设置为 row。这，也是现在不少公司使用的配置组合。

第四章 Schema设计

选择优化的数据类型

更小的通常更好，但是要确保没有低估需要存储的值的范围，因为在schema中的多个地方增加数据类型范围是一个十分耗时的操作。

简单就好

简单的数据类型的操作通常需要更少的cpu时间。

尽量避免NULL

可为NULL的列使得索引、索引比统计和值比较都更为复杂。

当然也有例外，InnoDB使用单独的位存储NULL值， 所以对于稀疏数据有很好的空间效率。

选择标识符

一旦选定一种类型，要确保在所有的关联表中都使用同样的类型。类型之间需要精确匹配，包括像UNSIGNED这样的属性。尽量只用整型定义标识符。

注意可变长字符串

其在临时表和排序时可能导致悲观的按最大长度分配内存

范式与反范式

范式是好的，但是反范式有时也是必须的，并且能带来好处。

第五章 创建高性能索引

B-Tree 索引的查询类型

• 全值匹配： 指的是和索引的所有列进行匹配
• 匹配最左前缀： 查找索引前几列进行匹配
• 匹配列前缀: 只匹配某一列的值的开头部分
• 匹配范围值： 查找索引某一范围的值
• 精确匹配某一列并范围匹配另外一列
• 只访问索引的查询：覆盖索引

B-Tree 索引的限制

• 如果不是按照索引的最左列开始查找，则无法使用索引

• 不能跳过索引中的列

• 如果查询中有某个列的范围查询，则其右边的所有列都无法使用索引：

  例如查询 索引为key(last_name, fisrt_name, dob)

  1	where last_name = 'a' and first_name like 'J%' and dob = '1877-12-23'

索引的优点

1. 大大减少服务器需要扫描的数据量
2. 帮助服务器避免排序和临时表
3. 将随机I/O变为顺序I/O

高性能索引-独立的列

如果查询中列不是独立的，则mysql不会使用索引

1
2

select actor_id from sakila.actor where actor_id + 1 = 5;

高性能索引-前缀索引和索引选择性

有时候需要索引很长的字符列，通常可以索引开始部分的字符，同时也会降低索引的选择性。

索引的选择性指的是，不重复的索引值和数据表的记录总数的比值。索引的选择性越高表示查询效率越高，因为选择性高的索引可以过滤掉更多的行。

前缀索引是一种能使索引更小，更快的有效方法，但是也有其缺点：前缀索引无法做order by 和 group by，也无法使用前缀索引做覆盖索引。

高性能索引-多列索引

最容易遇到的困惑是多列索引的顺序，正确的顺序依赖于使用索引的查询，同时需要考虑如何更好的满足排序和分组需要。对于如何选择索引的列顺序有一个经验法则：将选择性最高的列放在索引的最前列。只有不需要考虑排序和分组时，将选择性跟高的列放在最前面通常是最好的，但是考虑问题需要更全面，避免随机I/O和排序更加重要。

高性能索引-覆盖索引

如果一个索引包含所需要查询的字段的值，我们就可以称之为“覆盖索引”

覆盖索引的好处：

• 索引条目通常远小于数据行大小，所以如果只需要读取索引，那mysql就会极大的减少数据访问量。
• 因为索引是按照列值顺序存储的，所以对于I/O密集型范围查询回避随机从磁盘读取每一行数据的I/O要小的多
• 由于Innodb的聚簇索引，覆盖索引对Innodb表特别有用，可以避免对主键索引的二次查询。

覆盖索引的陷阱：

1

select * from products where actor = 'SEAN CARREY' and title like '%APOLLO%';

• 没有索引能够覆盖这个查询，因为查询从表中选择了所有的列
• mysql不能再索引中执行like操作，只能做最左前缀匹配

高性能索引-使用索引扫描来做排序

mysql有两种方式可以生成有序的结果：通过排序操作；或者使用索引顺序扫描。mysql可以使用同一个索引既满足排序，有用于查找行。

只有当索引的列顺序和ORDER BY子句的顺序完全一致，并且所有列的排序方向都一样时，mysql才能使用索引来对结果做排序。如果查询需要关联多张表，则只有当ORDER BY子句引用的字段全部为第一个表时，才能用索引做排序。ORDER BY子句和查询的限制是一样的：需要满足索引的最左前缀的要求。

有一种情况下ORDER BY子句可以不满足索引的最左前缀要求：

1
2

select rental_id, staff_id from sakila.rental where rental_date = '2005-05-25'
order by inventory_id, customer_id;

索引为key(rental_date, inventory_id, customer_id)，前导列为常量的时候，如果where子句或者join子句中对这些列指定了常量，就可以弥补ORDER BY的不足。

1
2

where rental_date > '2005-12-25' order by inventory_id, customer_id;
where rental_date = '2005-12-25' and inventory_id in (1, 2) order by cusomter_id;

对于索引上是范围查询，mysql无法使用之后的索引列

高性能索引-使用索引扫描减少锁

索引可以让查询锁定更少的行，如果你的查询从不访问那些不需要的行，那么就会锁定更少的行。但这只有当innoDB在存储引擎层能够过滤掉所有不需要的行是才有效。如果索引无法过滤掉无效的行，那么innoDB检索到数据并返回给服务器层后，innoDB已经锁定这些行了（mysql 5.6后没有这个问题）。

高性能索引-避免多个范围条件

下面的查询：

1

where last_online > date_sub(now(), interval 7 day) and age bwtween 18 and 25

这个查询有一个问题：它有两个范围条件,last_online和age列，mysql可以使用last_online的索引或者是age列的索引，但是无法同时使用它们。

高性能索引-延迟关联优化分页

如果一个查询匹配结果有上百万行的话会怎样？

1

select * from profiles where sex = 'm' order by rating limit 10;

即使有索引，如果用户界面需要翻页，并且翻页到比较靠后的地方也会非常慢，如：

1

select * from profiles where sex = 'm' order by rating limit 1000000, 10;

无论如何创建索引，这种查询都是个严重的问题，mysql需要花费大量时间来扫描需要丢弃的数据。其中一个解决的办法是限制能够翻页的数量。

优化这类索引的另一个比较好的策略是使用延迟关联，通过使用覆盖索引返回需要的主建，再根据这些主建回主表获得需要的行，这样可以减少mysql扫描需要丢弃的行数。

1
2
3
4
5
6

select * from profiles innner join 
(
    select id fomr profiles p where p.sex = 'm' order by rating limit 1000000, 10

) as t using (id);

第六章 慢查询优化

优化数据访问

查询性能低下最基本的原因是访问数据太多。某些查询可能不可避免的需要筛选大量数据，单这并不常见。大部分性能低下的查询都可以通过减少访问的数据量的方式进行优化。对于低效的查询，我们发现通过下面几个步骤来分析总是很有效：

1. 确认应用程序时候检索大量超过需要的数据。
2. 确认mysql服务层是否在分许大量超过需要的数据行。

第一种情况可以使用limit和选择需要的列来解决。在确定查询只返回需要的数据之后，接下来应该看看查询为了返回结果是否扫描了过多的数据，对于mysql有三个衡量查询开销的指标：

• 响应时间
• 扫描的行数
• 返回的行数

没有那个指标能完美地衡量查询的开销，但它们大致反映了mysql内部查询时需要访问多少数据，并可以大概推算出查询运行的时间。

在评估查询开销的时候，需要考虑一下从表中找到某一行数据的成本。mysql有好几种访问方式可以查找并返回一行结果。有些访问方式可能需要扫描很多行才能返回一行结果，也有些访问方式可能无需扫描就能返回结果。

在explain语句中的type列反应了访问类型。访问类型有很多种，从全表扫描到索引扫描、范围扫描、唯一索引查询、常数引用等。这里列的这些，速度是从慢到快，扫描行数也是从大到小。如果查询没有办法找到合适是访问类型，那么解决的最好办法通常是增加一个合适的索引。

一般mysql能够使用如下三种方式应用where条件，从好到坏依次为：

• 从索引中使用where条件吗来过滤不匹配的记录，这是在存储引擎层完成的。
• 使用索引覆盖扫描来返回记录（extra出现using index），直接从索引中过滤不需要的数据并返回命中结果。这是在mysql服务层完成的，但无需再回表查询记录。
• 从数据表中返回数据，然后过滤不满足条件的记录（extra出现using where）。这是在mysql服务器层完成，mysql需要从数据表中读出记录然后过滤。

如果发现查询需要扫描大量的数据但只返回少数的行，那么可以使用下面的技巧去优化它：

• 使用覆盖索引，把需要用的列都放到索引中
• 改变库表结构。例如使用单独的汇总表
• 重写复杂的查询， 让mysql优化器能够以更加高效的方式执行这个查询

重构查询方式-切分查询

有时候对于一个大查询我们需要分而治之，将大查询分成小查询。删除旧数据是一个很好的例子，如果用一个大的语句一次性完成，则可能一次需要锁住很多数据、占满整个事务日志、耗尽系统资源、阻塞恨到重要的查询。

重构查询方式-分解关联查询

例如下面这个查询：

1
2
3
4

select * from tag
join tag_post on tag_post.tag_id = tag.id
join post on tag_post.post_id = post.id
where tag.tag = 'mysql';

可以分解成下面这个查询来代替：

1
2
3
4
5

select * from tag where tag = 'mysql';

select * from tag_post where tag_id = 1234;

select * from post where post.id in (123,456,567);

用分解关联查询的方式重构查询有如下的优势：

• 让缓存效率更高。许多应用程序可以方便地缓存单表查询对应的结果对象。
• 将查询分解后，执行单个查询可以减少锁的竞争。
• 在应用层做关联，可以更容易对数据库进行拆分，更容易做到高性能和高扩展。
• 查询本身效率可能提升，使用in代替关联查询，可能比随机关联更高效。
• 减少冗余记录的查询
• 相当于在应用层实现了哈希关联

重构查询方式-优化关联查询

• 确保on或者using子句上的列上有索引。
• 确保任何group by和 order by 的表达式中只涉及到一个表中的列，这样mysql才有可能使用索引来优化这个过程