MySQL45讲(第31~35讲)

目录

预防、恢复误删数据

误删行

误删库/表

延迟复制备库

预防误删库/表的方法

rm删除数据

kill语句

收到kill以后,线程做什么?

另外两个关于客户端的误解

全表扫描的影响与实现

全表扫描对server层的影响

全表扫描对InnoDB的影响

Join执行分析

Index Nested-Loop Join

Simple Nested-Loop Join

Block Nested-Loop Join

join语句怎么优化

Multi-Range Read优化

Batched Key Access

BNL算法的性能问题

BNL转BKA

扩展-hash join


预防、恢复误删数据

分类:

  1. 使用delete语句误删数据行;

  2. 使用drop table或者truncate table语句误删数据表;

  3. 使用drop database语句误删数据库;

  4. 使用rm命令误删整个MySQL实例。

误删行

如果是使用delete语句误删了数据行,可以用Flashback工具通过闪回把数据恢复回来。

Flashback恢复数据的原理,是修改binlog的内容,拿回原库重放。而能够使用这个方案的前提是,需要确保binlog_format=row 和 binlog_row_image=FULL

具体恢复数据时,对单个事务做如下处理:

  1. 对于insert语句,对应的binlog event类型是Write_rows event,把它改成Delete_rows event即可;

  2. 同理,对于delete语句,也是将Delete_rows event改为Write_rows event;

  3. 而如果是Update_rows的话,binlog里面记录了数据行修改前和修改后的值,对调这两行的位置即可。

如果误操作不是一个,而是多个,会怎么样呢?比如下面三个事务:

(A)delete ...
(B)insert ...
(C)update ...

现在要把数据库恢复回这三个事务操作之前的状态,用Flashback工具解析binlog后,写回主库的命令是:

(reverse C)update ...
(reverse B)delete ...
(reverse A)insert ...

也就是说,如果误删数据涉及到了多个事务的话,需要将事务的顺序调过来再执行。

需要说明的是,我不建议你直接在主库上执行这些操作。

恢复数据比较安全的做法,是恢复出一个备份,或者找一个从库作为临时库,在这个临时库上执行这些操作,然后再将确认过的临时库的数据,恢复回主库。

为什么要这么做呢?

这是因为,一个在执行线上逻辑的主库,数据状态的变更往往是有关联的。可能由于发现数据问题的时间晚了一点儿,就导致已经在之前误操作的基础上,业务代码逻辑又继续修改了其他数据。所以,如果这时候单独恢复这几行数据,而又未经确认的话,就可能会出现对数据的二次破坏。

预防建议:

  1. 把sql_safe_updates参数设置为on。这样一来,如果我们忘记在delete或者update语句中写where条件,或者where条件里面没有包含索引字段的话,这条语句的执行就会报错。

  2. 代码上线前,必须经过SQL审计。

你可能会说,设置了sql_safe_updates=on,如果我真的要把一个小表的数据全部删掉,应该怎么办呢?

如果你确定这个删除操作没问题的话,可以在delete语句中加上where条件,比如where id>=0。

但是,delete全表是很慢的,需要生成回滚日志、写redo、写binlog。所以,从性能角度考虑,你应该优先考虑使用truncate table或者drop table命令。

使用delete命令删除的数据,你还可以用Flashback来恢复。而使用truncate /drop table和drop database命令删除的数据,就没办法通过Flashback来恢复了。为什么呢?

这是因为,即使我们配置了binlog_format=row,执行这三个命令时,记录的binlog还是statement格式。binlog里面就只有一个truncate/drop 语句,这些信息是恢复不出数据的。

误删库/表

这种情况下,要想恢复数据,就需要使用全量备份,加增量日志的方式了。这个方案要求线上有定期的全量备份,并且实时备份binlog。

在这两个条件都具备的情况下,假如有人中午12点误删了一个库,恢复数据的流程如下:

  1. 取最近一次全量备份,假设这个库是一天一备,上次备份是当天0点;

  2. 用备份恢复出一个临时库;

  3. 从日志备份里面,取出凌晨0点之后的日志;

  4. 把这些日志,除了误删除数据的语句外,全部应用到临时库。

这个流程的示意图如下所示:

关于这个过程,我需要和你说明如下几点:

  1. 为了加速数据恢复,如果这个临时库上有多个数据库,你可以在使用mysqlbinlog命令时,加上一个–database参数,用来指定误删表所在的库。这样,就避免了在恢复数据时还要应用其他库日志的情况。

  2. 在应用日志的时候,需要跳过12点误操作的那个语句的binlog:

    • 如果原实例没有使用GTID模式,只能在应用到包含12点的binlog文件的时候,先用–stop-position参数执行到误操作之前的日志,然后再用–start-position从误操作之后的日志继续执行;
    • 如果实例使用了GTID模式,就方便多了。假设误操作命令的GTID是gtid1,那么只需要执行set gtid_next=gtid1;begin;commit; 先把这个GTID加到临时实例的GTID集合,之后按顺序执行binlog的时候,就会自动跳过误操作的语句。

不过,即使这样,使用mysqlbinlog方法恢复数据还是不够快,主要原因有两个:

  1. 如果是误删表,最好就是只恢复出这张表,也就是只重放这张表的操作,但是mysqlbinlog工具并不能指定只解析一个表的日志;

  2. 用mysqlbinlog解析出日志应用,应用日志的过程就只能是单线程。我们之前介绍的那些并行复制的方法,在这里都用不上。

一种加速的方法是,在用备份恢复出临时实例之后,将这个临时实例设置成线上备库的从库,这样:

  1. 在start slave之前,先通过执行
    change replication filter replicate_do_table = (tbl_name) 命令,就可以让临时库只同步误操作的表;

  2. 这样做也可以用上并行复制技术,来加速整个数据恢复过程。

这个过程的示意图如下所示。

可以看到,图中binlog备份系统到线上备库有一条虚线,是指如果由于时间太久,备库上已经删除了临时实例需要的binlog的话,我们可以从binlog备份系统中找到需要的binlog,再放回备库中。

假设,我们发现当前临时实例需要的binlog是从master.000005开始的,但是在备库上执行show binlogs 显示的最小的binlog文件是master.000007,意味着少了两个binlog文件。这时,我们就需要去binlog备份系统中找到这两个文件。

把之前删掉的binlog放回备库的操作步骤,是这样的:

  1. 从备份系统下载master.000005和master.000006这两个文件,放到备库的日志目录下;

  2. 打开日志目录下的master.index文件,在文件开头加入两行,内容分别是 “./master.000005”和“./master.000006”;

  3. 重启备库,目的是要让备库重新识别这两个日志文件;

  4. 现在这个备库上就有了临时库需要的所有binlog了,建立主备关系,就可以正常同步了。

不论是把mysqlbinlog工具解析出的binlog文件应用到临时库,还是把临时库接到备库上,这两个方案的共同点是:误删库或者表后,恢复数据的思路主要就是通过备份,再加上应用binlog的方式。

也就是说,这两个方案都要求备份系统定期备份全量日志,而且需要确保binlog在被从本地删除之前已经做了备份。

但是,一个系统不可能备份无限的日志,你还需要根据成本和磁盘空间资源,设定一个日志保留的天数。如果你的DBA团队告诉你,可以保证把某个实例恢复到半个月内的任意时间点,这就表示备份系统保留的日志时间就至少是半个月。

另外,我建议你不论使用上述哪种方式,都要把这个数据恢复功能做成自动化工具,并且经常拿出来演练。为什么这么说呢?

这里的原因,主要包括两个方面:

  1. 虽然“发生这种事,大家都不想的”,但是万一出现了误删事件,能够快速恢复数据,将损失降到最小,也应该不用跑路了。

  2. 而如果临时再手忙脚乱地手动操作,最后又误操作了,对业务造成了二次伤害,那就说不过去了。

延迟复制备库

如果一个库的备份特别大,或者误操作的时间距离上一个全量备份的时间较长,比如一周一备的实例,在备份之后的第6天发生误操作,那就需要恢复6天的日志,这个恢复时间可能是要按天来计算的。

那么,我们有什么方法可以缩短恢复数据需要的时间呢?

如果有非常核心的业务,不允许太长的恢复时间,我们可以考虑搭建延迟复制的备库。这个功能是MySQL 5.6版本引入的。

一般的主备复制结构存在的问题是,如果主库上有个表被误删了,这个命令很快也会被发给所有从库,进而导致所有从库的数据表也都一起被误删了。

延迟复制的备库是一种特殊的备库,通过 CHANGE MASTER TO MASTER_DELAY = N命令,可以指定这个备库持续保持跟主库有N秒的延迟。

比如你把N设置为3600,这就代表了如果主库上有数据被误删了,并且在1小时内发现了这个误操作命令,这个命令就还没有在这个延迟复制的备库执行。这时候到这个备库上执行stop slave,再通过之前介绍的方法,跳过误操作命令,就可以恢复出需要的数据。

这样的话,你就随时可以得到一个,只需要最多再追1小时,就可以恢复出数据的临时实例,也就缩短了整个数据恢复需要的时间。

预防误删库/表的方法

第一条建议是,账号分离。这样做的目的是,避免写错命令。比如:

  • 我们只给业务开发同学DML权限,而不给truncate/drop权限。而如果业务开发人员有DDL需求的话,也可以通过开发管理系统得到支持。
  • 即使是DBA团队成员,日常也都规定只使用只读账号,必要的时候才使用有更新权限的账号。

第二条建议是,制定操作规范。这样做的目的,是避免写错要删除的表名。比如:

  • 在删除数据表之前,必须先对表做改名操作。然后,观察一段时间,确保对业务无影响以后再删除这张表。
  • 改表名的时候,要求给表名加固定的后缀(比如加_to_be_deleted),然后删除表的动作必须通过管理系统执行。并且,管理系删除表的时候,只能删除固定后缀的表。

修改生产的数据,或者添加索引优化,都要先写好四个脚本:备份脚本、执行脚本、验证脚本和回滚脚本。备份脚本是对需要变更的数据备份到一张表中,固定需要操作的数据行,以便误操作或业务要求进行回滚;执行脚本就是对数据变更的脚本,为防Update错数据,一般连备份表进行Update操作;验证脚本是验证数据变更或影响行数是否达到预期要求效果;回滚脚本就是将数据回滚到修改前的状态。

rm删除数据

其实,对于一个有高可用机制的MySQL集群来说,最不怕的就是rm删除数据了。只要不是恶意地把整个集群删除,而只是删掉了其中某一个节点的数据的话,HA系统就会开始工作,选出一个新的主库,从而保证整个集群的正常工作。

这时,你要做的就是在这个节点上把数据恢复回来,再接入整个集群。

当然了,现在不止是DBA有自动化系统,SA(系统管理员)也有自动化系统,所以也许一个批量下线机器的操作,会让你整个MySQL集群的所有节点都全军覆没。

应对这种情况,我的建议只能是说尽量把你的备份跨机房,或者最好是跨城市保存。


kill语句

在MySQL中有两个kill命令:一个是kill query +线程id,表示终止这个线程中正在执行的语句;一个是kill connection +线程id,这里connection可缺省,表示断开这个线程的连接,当然如果这个线程有语句正在执行,也是要先停止正在执行的语句的。

现象:使用了kill命令,却没能断开这个连接。再执行show processlist命令,看到这条语句的Command列显示的是Killed。

其实大多数情况下,kill query/connection命令是有效的。比如,执行一个查询的过程中,发现执行时间太久,要放弃继续查询,这时我们就可以用kill query命令,终止这条查询语句。

还有一种情况是,语句处于锁等待的时候,直接使用kill命令也是有效的。我们一起来看下这个例子:

可以看到,session C 执行kill query以后,session B几乎同时就提示了语句被中断。这,就是我们预期的结果。

收到kill以后,线程做什么?

但是,这里你要停下来想一下:session B是直接终止掉线程,什么都不管就直接退出吗?显然,这是不行的。

当对一个表做增删改查操作时,会在表上加MDL读锁。所以,session B虽然处于blocked状态,但还是拿着一个MDL读锁的。如果线程被kill的时候,就直接终止,那之后这个MDL读锁就没机会被释放了。

这样看来,kill并不是马上停止的意思,而是告诉执行线程说,这条语句已经不需要继续执行了,可以开始“执行停止的逻辑了”。

其实,这跟Linux的kill命令类似,kill -N pid并不是让进程直接停止,而是给进程发一个信号,然后进程处理这个信号,进入终止逻辑。只是对于MySQL的kill命令来说,不需要传信号量参数,就只有“停止”这个命令。

实现上,当用户执行kill query thread_id_B时,MySQL里处理kill命令的线程做了两件事:

  1. 把session B的运行状态改成THD::KILL_QUERY(将变量killed赋值为THD::KILL_QUERY);

  2. 给session B的执行线程发一个信号。

为什么要发信号呢?

因为像图1的我们例子里面,session B处于锁等待状态,如果只是把session B的线程状态设置THD::KILL_QUERY,线程B并不知道这个状态变化,还是会继续等待。发一个信号的目的,就是让session B退出等待,来处理这个THD::KILL_QUERY状态。

上面的分析中,隐含了这么三层意思:

  1. 一个语句执行过程中有多处“埋点”,在这些“埋点”的地方判断线程状态,如果发现线程状态是THD::KILL_QUERY,才开始进入语句终止逻辑;

  2. 如果处于等待状态,必须是一个可以被唤醒的等待,否则根本不会执行到“埋点”处;

  3. 语句从开始进入终止逻辑,到终止逻辑完全完成,是有一个过程的。

到这里你就知道了,原来不是“说停就停的”。

接下来,我们再看一个kill不掉的例子,也就是 innodb_thread_concurrency 不够用的例子。

首先,执行set global innodb_thread_concurrency=2,将InnoDB的并发线程上限数设置为2;然后,执行下面的序列:

可以看到:

  1. sesssion C执行的时候被堵住了;

  2. 但是session D执行的kill query C命令却没什么效果,

  3. 直到session E执行了kill connection命令,才断开了session C的连接,提示“Lost connection to MySQL server during query”,

  4. 但是这时候,如果在session E中执行show processlist,你就能看到下面这个图。

这时候,id=12这个线程的Commnad列显示的是Killed。也就是说,客户端虽然断开了连接,但实际上服务端上这条语句还在执行过程中。

为什么在执行kill query命令时,这条语句不像第一个例子的update语句一样退出呢?

在实现上,等行锁时,使用的是pthread_cond_timedwait函数,这个等待状态可以被唤醒。但是,在这个例子里,12号线程的等待逻辑是这样的:每10毫秒判断一下是否可以进入InnoDB执行,如果不行,就调用nanosleep函数进入sleep状态。

也就是说,虽然12号线程的状态已经被设置成了KILL_QUERY,但是在这个等待进入InnoDB的循环过程中,并没有去判断线程的状态,因此根本不会进入终止逻辑阶段。

而当session E执行kill connection 命令时,是这么做的,

  1. 把12号线程状态设置为KILL_CONNECTION;

  2. 关掉12号线程的网络连接。因为有这个操作,所以你会看到,这时候session C收到了断开连接的提示。

那为什么执行show processlist的时候,会看到Command列显示为killed呢?其实,这就是因为在执行show processlist的时候,有一个特别的逻辑:

如果一个线程的状态是KILL_CONNECTION,就把Command列显示成Killed。

所以其实,即使是客户端退出了,这个线程的状态仍然是在等待中。那这个线程什么时候会退出呢?

答案是,只有等到满足进入InnoDB的条件后,session C的查询语句继续执行,然后才有可能判断到线程状态已经变成了KILL_QUERY或者KILL_CONNECTION,再进入终止逻辑阶段。

这个例子是kill无效的第一类情况,即:线程没有执行到判断线程状态的逻辑。跟这种情况相同的,还有由于IO压力过大,读写IO的函数一直无法返回,导致不能及时判断线程的状态。

另一类情况是,终止逻辑耗时较长。这时候,从show processlist结果上看也是Command=Killed,需要等到终止逻辑完成,语句才算真正完成。这类情况,比较常见的场景有以下几种:

  1. 超大事务执行期间被kill。这时候,回滚操作需要对事务执行期间生成的所有新数据版本做回收操作,耗时很长。

  2. 大查询回滚。如果查询过程中生成了比较大的临时文件,加上此时文件系统压力大,删除临时文件可能需要等待IO资源,导致耗时较长。

  3. DDL命令执行到最后阶段,如果被kill,需要删除中间过程的临时文件,也可能受IO资源影响耗时较久。

之前有人问过我,如果直接在客户端通过Ctrl+C命令,是不是就可以直接终止线程呢?

答案是,不可以。

这里有一个误解,其实在客户端的操作只能操作到客户端的线程,客户端和服务端只能通过网络交互,是不可能直接操作服务端线程的。

而由于MySQL是停等协议,所以这个线程执行的语句还没有返回的时候,再往这个连接里面继续发命令也是没有用的。实际上,执行Ctrl+C的时候,是MySQL客户端另外启动一个连接,然后发送一个kill query 命令。

所以,你可别以为在客户端执行完Ctrl+C就万事大吉了。因为,要kill掉一个线程,还涉及到后端的很多操作。

另外两个关于客户端的误解

在实际使用中,我也经常会碰到一些同学对客户端的使用有误解。接下来,我们就来看看两个最常见的误解。

第一个误解是:如果库里面的表特别多,连接就会很慢。

有些线上的库,会包含很多表(我见过最多的一个库里有6万个表)。这时候,你就会发现,每次用客户端连接都会卡在下面这个界面上。

而如果db1这个库里表很少的话,连接起来就会很快,可以很快进入输入命令的状态。因此,有同学会认为是表的数目影响了连接性能。

每个客户端在和服务端建立连接的时候,需要做的事情就是TCP握手、用户校验、获取权限。但这几个操作,显然跟库里面表的个数无关。

但实际上,正如图中的文字提示所说的,当使用默认参数连接的时候,MySQL客户端会提供一个本地库名和表名补全的功能。为了实现这个功能,客户端在连接成功后,需要多做一些操作:

  1. 执行show databases;

  2. 切到db1库,执行show tables;

  3. 把这两个命令的结果用于构建一个本地的哈希表。

在这些操作中,最花时间的就是第三步在本地构建哈希表的操作。所以,当一个库中的表个数非常多的时候,这一步就会花比较长的时间。

我们感知到的连接过程慢,其实并不是连接慢,也不是服务端慢,而是客户端慢。

图中的提示也说了,如果在连接命令中加上-A,就可以关掉这个自动补全的功能,然后客户端就可以快速返回了。

这里自动补全的效果就是,你在输入库名或者表名的时候,输入前缀,可以使用Tab键自动补全表名或者显示提示。

实际使用中,如果你自动补全功能用得并不多,我建议你每次使用的时候都默认加-A。

其实提示里面没有说,除了加-A以外,加–quick(或者简写为-q)参数,也可以跳过这个阶段。但是,这个–quick是一个更容易引起误会的参数,也是关于客户端常见的一个误解。

你看到这个参数,是不是觉得这应该是一个让服务端加速的参数?但实际上恰恰相反,设置了这个参数可能会降低服务端的性能。为什么这么说呢?

MySQL客户端发送请求后,接收服务端返回结果的方式有两种:

  1. 一种是本地缓存,也就是在本地开一片内存,先把结果存起来。如果你用API开发,对应的就是mysql_store_result 方法。

  2. 另一种是不缓存,读一个处理一个。如果你用API开发,对应的就是mysql_use_result方法。

MySQL客户端默认采用第一种方式,而如果加上–quick参数,就会使用第二种不缓存的方式。

采用不缓存的方式时,如果本地处理得慢,就会导致服务端发送结果被阻塞,因此会让服务端变慢。

那你会说,既然这样,为什么要给这个参数取名叫作quick呢?这是因为使用这个参数可以达到以下三点效果:

  • 第一点,就是前面提到的,跳过表名自动补全功能。
  • 第二点,mysql_store_result需要申请本地内存来缓存查询结果,如果查询结果太大,会耗费较多的本地内存,可能会影响客户端本地机器的性能;
  • 第三点,是不会把执行命令记录到本地的命令历史文件。

所以你看到了,–quick参数的意思,是让客户端变得更快


如果你碰到一个被killed的事务一直处于回滚状态,你认为是应该直接把MySQL进程强行重启,还是应该让它自己执行完成呢?为什么呢?

因为重启之后该做的回滚动作还是不能少的,所以从恢复速度的角度来说,应该让它自己结束。

当然,如果这个语句可能会占用别的锁,或者由于占用IO资源过多,从而影响到了别的语句执行的话,就需要先做主备切换,切到新主库提供服务。

切换之后别的线程都断开了连接,自动停止执行。接下来还是等它自己执行完成。这个操作属于我们在文章中说到的,减少系统压力,加速终止逻辑。


全表扫描的影响与实现

全表扫描对server层的影响

假设,我们现在要对一个200G的InnoDB表db1. t,执行一个全表扫描。当然,你要把扫描结果保存在客户端,会使用类似这样的命令:

mysql -h$host -P$port -u$user -p$pwd -e "select * from db1.t" > $target_file

你已经知道了,InnoDB的数据是保存在主键索引上的,所以全表扫描实际上是直接扫描表t的主键索引。这条查询语句由于没有其他的判断条件,所以查到的每一行都可以直接放到结果集里面,然后返回给客户端。

那么,这个“结果集”存在哪里呢?

实际上,服务端并不需要保存一个完整的结果集。取数据和发数据的流程是这样的:

  1. 获取一行,写到net_buffer中。这块内存的大小是由参数net_buffer_length定义的,默认是16k。

  2. 重复获取行,直到net_buffer写满,调用网络接口发出去。

  3. 如果发送成功,就清空net_buffer,然后继续取下一行,并写入net_buffer。

  4. 如果发送函数返回EAGAIN或WSAEWOULDBLOCK,就表示本地网络栈(socket send buffer)写满了,进入等待。直到网络栈重新可写,再继续发送。

这个过程对应的流程图如下所示。

从这个流程中,你可以看到:

  1. 一个查询在发送过程中,占用的MySQL内部的内存最大就是net_buffer_length这么大,并不会达到200G;

  2. socket send buffer 也不可能达到200G(默认定义/proc/sys/net/core/wmem_default),如果socket send buffer被写满,就会暂停读数据的流程。

MySQL是“边读边发的”。这就意味着,如果客户端接收得慢,会导致MySQL服务端由于结果发不出去,这个事务的执行时间变长。

比如下面这个状态,就是我故意让客户端不去读socket receive buffer中的内容,然后在服务端show processlist看到的结果。

如果你看到State的值一直处于“Sending to client”,就表示服务器端的网络栈写满了。

如果客户端使用–quick参数,会使用mysql_use_result方法。这个方法是读一行处理一行。你可以想象一下,假设有一个业务的逻辑比较复杂,每读一行数据以后要处理的逻辑如果很慢,就会导致客户端要过很久才会去取下一行数据,可能就会出现如图2所示的这种情况。

因此,对于正常的线上业务来说,如果一个查询的返回结果不会很多的话,我都建议你使用mysql_store_result这个接口,直接把查询结果保存到本地内存。

当然前提是查询返回结果不多。有同学说到自己因为执行了一个大查询导致客户端占用内存近20G,这种情况下就需要改用mysql_use_result接口了。

另一方面,如果你在自己负责维护的MySQL里看到很多个线程都处于“Sending to client”这个状态,就意味着你要让业务开发同学优化查询结果,并评估这么多的返回结果是否合理。

而如果要快速减少处于这个状态的线程的话,将net_buffer_length参数设置为一个更大的值是一个可选方案。

与“Sending to client”长相很类似的一个状态是“Sending data”,这是一个经常被误会的问题。有同学问我说,在自己维护的实例上看到很多查询语句的状态是“Sending data”,但查看网络也没什么问题啊,为什么Sending data要这么久?

实际上,一个查询语句的状态变化是这样的(注意:这里,我略去了其他无关的状态):

  • MySQL查询语句进入执行阶段后,首先把状态设置成“Sending data”;
  • 然后,发送执行结果的列相关的信息(meta data) 给客户端;
  • 再继续执行语句的流程;
  • 执行完成后,把状态设置成空字符串。

也就是说,“Sending data”并不一定是指“正在发送数据”,而可能是处于执行器过程中的任意阶段。比如,你可以构造一个锁等待的场景,就能看到Sending data状态。

可以看到,session B明显是在等锁,状态显示为Sending data。

也就是说,仅当一个线程处于“等待客户端接收结果”的状态,才会显示"Sending to client";而如果显示成“Sending data”,它的意思只是“正在执行”。

现在你知道了,查询的结果是分段发给客户端的,因此扫描全表,查询返回大量的数据,并不会把内存打爆。

全表扫描对InnoDB的影响

介绍WAL机制的时候,和你分析了InnoDB内存的一个作用,是保存更新的结果,再配合redo log,就避免了随机写盘。

内存的数据页是在Buffer Pool (BP)中管理的,在WAL里Buffer Pool 起到了加速更新的作用。而实际上,Buffer Pool 还有一个更重要的作用,就是加速查询。

由于有WAL机制,当事务提交的时候,磁盘上的数据页是旧的,那如果这时候马上有一个查询要来读这个数据页,是不是要马上把redo log应用到数据页呢?

答案是不需要。因为这时候内存数据页的结果是最新的,直接读内存页就可以了。你看,这时候查询根本不需要读磁盘,直接从内存拿结果,速度是很快的。所以说,Buffer Pool还有加速查询的作用。

而Buffer Pool对查询的加速效果,依赖于一个重要的指标,即:内存命中率

你可以在show engine innodb status结果中,查看一个系统当前的BP命中率。一般情况下,一个稳定服务的线上系统,要保证响应时间符合要求的话,内存命中率要在99%以上。

执行show engine innodb status ,可以看到“Buffer pool hit rate”字样,显示的就是当前的命中率。比如图5这个命中率,就是99.0%。

如果所有查询需要的数据页都能够直接从内存得到,那是最好的,对应的命中率就是100%。但,这在实际生产上是很难做到的。

InnoDB Buffer Pool的大小是由参数 innodb_buffer_pool_size确定的,一般建议设置成可用物理内存的60%~80%。

在大约十年前,单机的数据量是上百个G,而物理内存是几个G;现在虽然很多服务器都能有128G甚至更高的内存,但是单机的数据量却达到了T级别。

所以,innodb_buffer_pool_size小于磁盘的数据量是很常见的。如果一个 Buffer Pool满了,而又要从磁盘读入一个数据页,那肯定是要淘汰一个旧数据页的。

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

下图是一个LRU算法的基本模型。

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

  1. 在图6的状态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. 图7中状态1,要访问数据页P3,由于P3在young区域,因此和优化前的LRU算法一样,将其移到链表头部,变成状态2。

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

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

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

这个策略,就是为了处理类似全表扫描的操作量身定制的。还是以刚刚的扫描200G的历史数据表为例,我们看看改进后的LRU算法的操作逻辑:

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

  2. 一个数据页里面有多条记录,这个数据页会被多次访问到,但由于是顺序扫描,这个数据页第一次被访问和最后一次被访问的时间间隔不会超过1秒,因此还是会被保留在old区域;

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

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


由于客户端的性能问题,对数据库影响更严重的例子?

这个问题的核心是,造成了“长事务”。

至于长事务的影响,就要结合我们前面文章中提到的锁、MVCC的知识点了。

  • 如果前面的语句有更新,意味着它们在占用着行锁,会导致别的语句更新被锁住;
  • 当然读的事务也有问题,就是会导致undo log不能被回收,导致回滚段空间膨胀。

MySQL回滚日志(undo log)总结_每天都要进步一点点的博客-CSDN博客_undo log


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?

假设不使用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的对数,记为log2M,所以在被驱动表上查一行的时间复杂度是 2*log2M。

假设驱动表的行数是N,执行过程就要扫描驱动表N行,然后对于每一行,到被驱动表上匹配一次。

因此整个执行过程,近似复杂度是 N + N*2*log2M。

显然,N对扫描行数的影响更大,因此应该让小表来做驱动表。

如果你没觉得这个影响有那么“显然”, 可以这么理解:N扩大1000倍的话,扫描行数就会扩大1000倍;而M扩大1000倍,扫描行数扩大不到10倍。

到这里小结一下,通过上面的分析我们得到了两个结论:

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

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

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

Simple Nested-Loop Join

现在,我们把SQL语句改成这样:

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

由于表t2的字段b上没有索引,因此再用图2的执行流程时,每次到t2去匹配的时候,就要做一次全表扫描。

你可以先设想一下这个问题,继续使用图2的算法,是不是可以得到正确的结果呢?如果只看结果的话,这个算法是正确的,而且这个算法也有一个名字,叫做“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条件的,作为结果集的一部分返回。

这个过程的流程图如下:

对应地,这条SQL语句的explain结果如下所示:

可以看到,在这个过程中,对表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。如果放不下表t1的所有数据话,策略很简单,就是分段放。我把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+λ*N*M;

  2. 内存判断 N*M次。

显然,内存判断次数是不受选择哪个表作为驱动表影响的。而考虑到扫描行数,在M和N大小确定的情况下,N小一些,整个算式的结果会更小。

应该让小表当驱动表。

当然,你会发现,在N+λ*N*M这个式子里,λ才是影响扫描行数的关键因素,这个值越小越好。

刚刚我们说了N越大,分段数K越大。那么,N固定的时候,什么参数会影响K的大小呢?(也就是λ的大小)答案是join_buffer_size。join_buffer_size越大,一次可以放入的行越多,分成的段数也就越少,对被驱动表的全表扫描次数就越少。

这就是为什么,你可能会看到一些建议告诉你,如果你的join语句很慢,就把join_buffer_size改大。

第一个问题:能不能使用join语句?

  1. 如果可以使用Index Nested-Loop Join算法,也就是说可以用上被驱动表上的索引,其实是没问题的;

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

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

第二个问题是:如果要使用join,应该选择大表做驱动表还是选择小表做驱动表?

  1. 如果是Index Nested-Loop Join算法,应该选择小表做驱动表;

  2. 如果是Block Nested-Loop Join算法:

    • 在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的表t1”是那个相对小的表。

在决定哪个表做驱动表的时候,应该是两个表按照各自的条件过滤,过滤完成之后,计算参与join的各个字段的总数据量,数据量小的那个表,就是“小表”,应该作为驱动表


使用Block Nested-Loop Join(BNL)算法时,可能会对被驱动表做多次扫描。如果被驱动表是一个大表,并且是一个冷数据表,除了查询过程中可能会导致IO压力大以外,你觉得对这个MySQL服务还有什么更严重的影响吗?

下一篇中的BNL性能问题


join语句怎么优化

创建两个表t1、t2来和你展开今天的问题。

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

在表t1里,插入了1000行数据,每一行的a=1001-id的值。也就是说,表t1中字段a是逆序的。同时,我在表t2中插入了100万行数据。

Multi-Range Read优化

Multi-Range Read优化(MRR)。这个优化的主要目的是尽量使用顺序读盘

回表是指,InnoDB在普通索引a上查到主键id的值后,再根据一个个主键id的值到主键索引上去查整行数据的过程。

回表过程是一行行地查数据,还是批量地查数据?

我们先来看看这个问题。假设,我执行这个语句:

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

主键索引是一棵B+树,在这棵树上,每次只能根据一个主键id查到一行数据。因此,回表肯定是一行行搜索主键索引的,基本流程如图1所示。

如果随着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了。)

下面两幅图就是使用了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 Acess(BKA)算法了。这个BKA算法,其实就是对NLJ算法的优化。

我们再来看看上一篇文章中用到的NLJ算法的流程图:

NLJ算法执行的逻辑是:从驱动表t1,一行行地取出a的值,再到被驱动表t2去做join。也就是说,对于表t2来说,每次都是匹配一个值。这时,MRR的优势就用不上了。

那怎么才能一次性地多传些值给表t2呢?方法就是,从表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算法到底要怎么启用呢?

如果要使用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的LRU算法的时候提到,由于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区域的数据页没有被合理地淘汰。

也就是说,这两种情况都会影响Buffer Pool的正常运作。

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

图8就是这个语句序列的执行效果。

可以看到,整个过程3个语句执行时间的总和还不到1秒,相比于前面的1分11秒,性能得到了大幅提升。接下来,我们一起看一下这个过程的消耗:

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

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

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

扩展-hash join

看到这里你可能发现了,其实上面计算10亿次那个操作,看上去有点儿傻。如果join_buffer里面维护的不是一个无序数组,而是一个哈希表的话,那么就不是10亿次判断,而是100万次hash查找。这样的话,整条语句的执行速度就快多了吧?

确实如此。

这,也正是MySQL的优化器和执行器一直被诟病的一个原因:不支持哈希join。并且,MySQL官方的roadmap,也是迟迟没有把这个优化排上议程。

实际上,这个优化思路,我们可以自己实现在业务端。实现流程大致如下:

  1. select * from t1;取得表t1的全部1000行数据,在业务端存入一个hash结构,比如C++里的set、PHP的dict这样的数据结构。

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

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


现在有一个三个表join的需求,假设这三个表的表结构如下:

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

create table t2 like t1;
create table t3 like t2;
insert into ... //初始化三张表的数据

语句的需求实现如下的join逻辑:

select * from t1 join t2 on(t1.a=t2.a) join t3 on (t2.b=t3.b) where t1.c>=X and t2.c>=Y and t3.c>=Z;

现在为了得到最快的执行速度,如果让你来设计表t1、t2、t3上的索引,来支持这个join语句,你会加哪些索引呢?

同时,如果我希望你用straight_join来重写这个语句,配合你创建的索引,你就需要安排连接顺序,你主要考虑的因素是什么呢?

如果改写成straight_join,要怎么指定连接顺序,以及怎么给三个表创建索引。

第一原则是要尽量使用BKA算法。需要注意的是,使用BKA算法的时候,并不是“先计算两个表join的结果,再跟第三个表join”,而是直接嵌套查询的。

具体实现是:在t1.c>=X、t2.c>=Y、t3.c>=Z这三个条件里,选择一个经过过滤以后,数据最少的那个表,作为第一个驱动表。此时,可能会出现如下两种情况。

第一种情况,如果选出来是表t1或者t3,那剩下的部分就固定了。

  1. 如果驱动表是t1,则连接顺序是t1->t2->t3,要在被驱动表字段创建上索引,也就是t2.a 和 t3.b上创建索引;

  2. 如果驱动表是t3,则连接顺序是t3->t2->t1,需要在t2.b 和 t1.a上创建索引。

同时,我们还需要在第一个驱动表的字段c上创建索引。

第二种情况是,如果选出来的第一个驱动表是表t2的话,则需要评估另外两个条件的过滤效果。

总之,整体的思路就是,尽量让每一次参与join的驱动表的数据集,越小越好,因为这样我们的驱动表就会越小。

  • 1
    点赞
  • 2
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值