Mysql知识点总结笔记

学习完丁奇老师的mysql专栏之后，想总结一下所学到的mysql知识，方便回顾和加深印象，当作自己的个人笔记，后面懒得加粗和分段了，当回顾看看就可以了，写的比较乱，还请谅解

基础结构

整个mysql可分为两大部分：server层，引擎层
server层：覆盖了mysql所有的核心功能，内置函数，存储过程等等，可分为五个部分组成。
引擎层：包括mysql自带的MyISAM，以及之后外部插件形式的InnoDB，还有内存形式的Memory等等。

连接器

用于管理连接，和权限的验证，通过用户名密码之后，连接器会在权限表中查到本次连接用户的权限，之后的所有执行都依赖于次权限。

show processlist命令看到连接数据库的状态，Command列显示Sleep表示该连接处于空闲状态，默认8小时断开，也可以通过参数wait_timeout设置。

长连接：客户端持续有请求，一直使用同一个连接。

短连接：客户每次执行很少的查询就断开，下次再重新建立连接。

由于建立连接过程比较复杂，消耗性能，所以尽可能使用长连接，但是长连接过多会导致内存占用过大，到一定程度会被系统kill掉，为了解决这个问题有两个方案：

• 定期断开长连接
• 5.7版本后，可用mysql_reset_connection命令初始化连接资源，不需要重连，就可以释放内存

查询缓存（8.0开始被删除）

mysql拿到查询请求时，会到查询缓存中检查是否之前已经执行过次命令，之前执行过的结果以key-value形式保存在内存中，key就是查询语句，value就是查询结果。

如果不在查询缓存中，则继续后面执行，执行完成后存入到查询缓存中。
但是一般情况下不建议用查询缓存：

• 只要有更新的操作，那么当前表上的所有查询缓存将被清空，也就是既占用了内存空间，且命中率也比较低，除非这个表是很长一段时间才更新的表
• 可以通过参数query_cache_type来开启（DEMAND：不使用，SQL_CACHE：使用）

分析器

顾名思义是对sql语句进行分析：

• 每个词义分别是什么，代表什么（select是查询关键字，ID则是列ID）
• sql语句是否满足MySQL的语法结构，错误就会收到错误提示，在xxxx附近，基本上按这个就可以找到语法的错误

优化器

经过分析器的验证，说明sql语句是正确的，那么优化器会对这个sql执行顺序，决定使用的索引等等进行优化选择，使效率达到最优

优化器会选择呢代价小区执行语句，在数据库里面，是否用了临时表，是否进行了排序，扫描行数都是判断依据。

扫描行数是通过索引上不同值的个数，也就是基数(cardinality)，可以通过show index方法来查看，基数是通过采样统计的方法来获取，所以并不准确，但是大体上相差不大。

但是优化器也有优化失败的情况，比如事务A开启之后select * from table，事务B这边delete from table之后再insert相同数据，这时候再查询的时候就会发现explain的rows扫描行数增加了一倍多，结果因为加上回表可能带来的损耗，直接全表扫描可能要更快的错觉，实际上是因为delete from 只是逻辑上删除了表，但是还没有释放资源，再次insert时，由于事务A开启了一致性视图，事务B无法重复利用delete的空间，insert要另开空间进行insert数据，所以这种情况下其实直接走普通索引更快。索引统计信息不准确时，可以用analyze table来重新统计。

如果发现优化器走错索引的情况，可以用force index强行指定索引，也可以通过修改语句来引导优化器走错索引的代价要很高，也可以增加或删除索引的方法来绕过这个问题。

执行器

• 通过连接器取得的权限来判断是否有执行sql语句的权限，没有则返回错误，如果命中查询缓存，也会返回前验证权限。
• 每个表创建时会指定引擎，执行器则根据表定义的引擎去调用这个引擎提供的接口
  • 调用xxx引擎接口取这个表的第一行，判断是否满足where条件，不是则跳过，是则将这行存在结果集中。
  • 继续调用接口取下一行，重复上面的逻辑，直到这个表的最后一行
  • 最后将结果集返回给客户端

InnoDB表

一个InnoDB表结构包含两部分，表结构定义和数据，8.0版本前，表结构是以.frm为后缀的文件里，8.0之后，则允许把表结构定义放在系统数据表中，因为表结构定义占用的空间很小。

表数据

表数据可以在共享表空间里，也可以是单独的文件，用参数innodb_file_per_table来控制，OFF就是放在系统共享表空间，ON就是每个InnoDB表数据存储在一个以.ibd为后缀的文件中，5.6.6开始默认是ON，也最好都设置ON，因为这样drop table时候直接回删除这个文件，要不然放在共享表空间中，表删掉了，但是空间也不会被回收。

如果用delete删除一行数据或一个数据页甚至整个表，他们只会被标记为删除，但是空间不会被释放，而是我们插入数据时可以复用此空间，要注意的是删除行的时候，只能复用满足前后区间条件的行，插入别的地方是不会复用的。

除了删除以外，更新和插入也会出现额外空余的空间，当插入新的索引值到数据页1的时候，此时数据页1已经满了，那么就不得不页分裂来保存数据，那么数据页1的末尾就会出现空洞，更新的时候，可以理解为标记删除一个旧的值，在插入一个新的值，如果位置不同，可能原来删除的地方就会出现空洞。

为了使去掉收缩空间的目的，我们可以使用重建表的操作，简单来说就是创建一个新的表，把旧的表的数据依次导入进去，再把新的表变更为使用的表，结构会变得更紧凑，可以使用alter table T engine=InnoDB命令。

5.6版本之后开始引入Online DDL，和上面的操作类似，只不过对旧表可以进行DML操作，并记录到row log中，等新表也就是临时文件生成后，再把row log中的操作记录应用到临时文件中。这里alter语句在开始的时候确实是拿到的MDL写锁，但是由于不堵塞DML操作，会退化成MDL的读锁，这样既可以操作DML操作，也可以防止其他线程做DDL操作，对于大表来说，很消耗IO和CPU资源，如果想要安全操作的话，推荐使用GitHub 开源的 gh-ost来做。

inplace：对于server层来说，没有把数据挪动到临时表，是一个替换的操作。所以重建表语句其实后面是ALGORITHM=inplace，如果是copy就是拷贝表，DDL如果过程是Online的，那么就一定是在InnoDB引擎中执行，在server看来就一定是inplace的，但是inplace的DDL，却不一定是Online，截止到8.0版本，有可能是全文索引和空间索引的情况，是会堵塞DML操作的。

如果重建表之后还重建索引统计的话，可以用optimize table T操作。

日志系统

有两个最重要的分别是redo log和binlog（undo log作为回滚日志会在事务中说明）

redo log

每次更新操作时要写进磁盘，且要在磁盘中找到对应的记录，整个过程所需要的IO访问次数和性能成本都很高，所以redo log是为了解决这些问题来提高更新效率，且是InnoDB引擎独有的日志系统，当使用其他引擎时，无法使用redo log。

redo log buffer：一个事务更改数据时，先把更改的redo log存在这个buffer中，此时，内存的数据也被修改，最后commit时，把redo log buffer写入到redo log中。

redo log写盘步骤为：redo log buffer物理上是在MySQL进程内存中，也是先写到文件系统的page cache中，然后再持久化到磁盘。

crash-safe：redo log可以保证即使数据库发生异常重启，之前redo log提交的记录也不会丢失，由于redo log里面是物理存储，保存了更改前的数据，更改后的数据，日志序列号等，所以可以还原到之前的状态，binlog保存的是逻辑存储，也就是语句的原始逻辑，给xx字段加一这样的。

WAL技术：Write-Ahead Logging，关键点就是先写日志，再写入磁盘，
当有一条记录需要更新时，InnoDB引擎会先把记录写到redo log中，在更新内存，那么下次查询时直接读取到内存就可以返回更新的结果，当InnoDB空闲或者redo log写满的时候再更新到磁盘中，由于redo log顺序写，所以要比直接去磁盘找记录更新要快的多。

当内存数据页跟磁盘数据页内容不一致的时候，这个数据页叫"脏页"，内存数据写入到磁盘后，内存和磁盘上的数据页内容就一致了，叫做"干净页"。内存写入磁盘的过程叫做flush。

redo log的大小是固定的，write pos负责写入，后面的check point是当前要擦除的位置，即write pos和check point中间的位置是要写入的位置，当write pos追上check point时，check point往后推移，并且把途径的数据更新到磁盘中，到末尾时，会重新从第一个文件头部开始循环写入。

还有就是系统内存不足，当需要新的内存页的时候就要淘汰一些内存页空出内存，如果淘汰的是脏页就要先把脏页写入到磁盘中，这样就保证了内存中的数据一定是正确的，不在内存中的数据，从磁盘读取是正确的数据，并读入内存后直接返回。

当然，如果MySQL认为系统"空闲"时，就会找机会刷脏页，还有就是正常关闭的时候会把内存的脏页都flush到磁盘上。

如果是日志写满后要flush，那么更新的所有操作都要等待刷出新的空间，此时写性能为0，对于业务来说是不能接受的，还有就是淘汰脏页个数太多，会导致响应时间过长。这两种情况要避免。

InnoDB刷脏页的控制策略

1.可以用innodb_io_capacity参数，这个值建议设置成磁盘的IOPS，可以通过fio工具来测试。

2.innodb_max_dirty_pages_pct是脏页比例上限，默认是75%，然后InnoDB会根据上限比例和当前日志序列号而算出的值来取两者最大值R%速度来刷脏页，所以要合理的设置
innodb_io_capacity值来避免接近脏页比例75%。脏页比例可以通过Innodb_buffer_pool_pages_dirty/Innodb_buffer_pool_pages_total 得到。

3.还有就是innodb_flush_neighbors参数可以控制将相邻的脏页也进行flush，但是这就导致flush的脏页变多，如果使用的是SSD较高IOPS的设备的话，建议设置成0，可以更快的flush，8.0版本默认就是0。

binlog

binlog属于Server层的日志，也就是所有引擎都可以使用，主要负责数据的归档的操作，binlog比起redolog的循环写入，是进行追加写，不会覆盖原来的日志。

因为这个特性，如果数据库误删，或者要恢复到之前的状态，就可以通过binlog，从特定的备份时间开始依次取出重放到误删之前的时刻。

执行过程是：事务执行过程中，先把日志写到binlog cache里，然后事务提交的时候，再把binlog cache写到binlog文件中。一个事务的binlog不能被拆分，所以都要一次性写入，可以用binlog_cache_size控制单个线程内的binlog cache大小，如果超过这个大小，就要暂存到磁盘中。

每个线程都有自己的binlog cache，但是共用一个binlog文件，在事务提交后，会把binlog cache里的内容写到文件系统的page cache，然后在持久化到磁盘。

write和fsync的时机，由参数sync_binlog控制，0代表只写，不持久化，1代表每次提交都会持久化到磁盘，N就是N个事务后才会持久化，一般设置子100到1000，但是相对应的就是如果主机发生异常重启，那么最近N个事务的binlog就会丢失。

binlog三种格式：row，statement，mixed，可以用show binlog events in 'master.000001’来查看。

statement：记录的是use 库名， sql语句。

row：与statement相比，用两个event来代替sql语句，Table_map就是操作的哪个数据库的哪张表，Delete_rows或Update_rows，定义删除或更新的行为。

row里面定义语句的行为，可以用mysqlbinlog -vv data/master.000001 --start-position=8900;来查看，start-position是binlog开始的位置。里面可以看到操作的行的所有字段的值，如果把 binlog_row_image 设置为 MINIMAL，则只会看到必要的值，如果binlog完整，则会看到Xid event。

如果更新时，传到备库时候，由于优化器的选择，可能在两个库中会执行不同的索引，导致更新的行发生偏差，所以statement是可能导致主备数据不一致，row则是定位到行的信息，所以执行时直接定位到行进行更新，就不会有主备数据不一致的问题。

mixed：由于row格式占用的空间较多，但statement在某些语句时会出现主备不一致，于是mixed就是两者折中的方案。

row的好处有很多，恢复数据就是其中之一，如果用mixed或者statement恢复数据时，不能直接用语句来执行，会导致数据不一致的情况，binlog恢复数据的标准做法是，用mysqlbinlog工具解析后，把整个发给MySQL执行。

两阶段提交

所以综合redo log和binlog，一个更新语句的流程为：

1. 查看符合条件的语句所在的数据页是否在内存中，是则直接返回给执行器，否则要先从磁盘中读入内存，再返回
2. 执行器拿到引擎返回的数据，再调用引擎接口写入新数据
3. 引擎将新数据更新到内存中，同事将更新操作记录到redo log中，此时redo log处于prepare状态，告知执行器执行完成，可以提交事务
4. 执行器生成这个操作的binlog，并把binlog写入到磁盘
5. 执行器调用引擎的提交事务接口，并把redo log改成commit状态，更新完成

将redo log分成prepare和commit提交就是两阶段，这样的好处是无论是在任何一步出现崩溃时，用来恢复数据都不会出现不一致的情况。

• prepare前如果崩溃，那么恢复时直接回滚就可以
• 3~4出现问题时，可分为两种情况：
  • binlog不完整，那么直接回滚
  • binlog完整，那么就将redo log改成commit，并提交事务
• 4~5出现问题时，由于binlog和redo log都写入了，redo log改成commit状态后提交即可

这里binlog完整性说明一下：
binlog有两种格式，statement格式下，最后会有COMMIT来确定是否完成，row格式最后会有一个XID event，5.6.2版本之后可以使用binlog-checksum参数来验证binlog正确性，由于磁盘的原因，可能会出现日志中间出错的情况。

redo log和binlog有共同的数据字段XID，恢复时候，如果prepare但是还没commit的redo log，就会用XID去binlog中找对应的事务。

如果不这么做，binlog先写完，redo log没写入，那么就会恢复到之前的状态，那么与binlog数据就不一致，redo log先写完，binlog没写，那么用binlog恢复数据时就会与之前的数据不一致，两阶段提交的目的就是确保数据的一致性。

innodb_flush_log_at_trx_commit参数是保证crash-safe能力，写多少次redo log在持久化到磁盘，0表示每次只是写到redo log buffer中，1则是每次提交都把redo log持久化到磁盘，2表示每次提交只写到文件系统的page cache中。
InnoDB有一个后台线程，每隔1秒，就会把redo log buffer中的日志写到page cache中，然后持久化到磁盘，或者redo log buffer占用空间要到innodb_log_buffer_size一半时候，后台线程会主动写page cache，而且并行事务提交的时候，也会顺带把这个事务的redo log buffer持久化到磁盘中，所以在事务执行还没提交的时候，redo log可能已经持久化到磁盘了。

已经这里建议1，每次prepare阶段就要持久化到磁盘，可以保证mysql异常重启数据不丢失。

每秒一次的后台轮询刷盘，加上崩溃恢复prepare就持久化磁盘的逻辑，InnoDB就认为redo log在commit时候就不要持久化了，只会write到page chache。

sync_binlog是binlog持久化到磁盘的能力，也建议是1，保证binlog不丢失。

这也是MySQL的双一配置，指这两个参数都设置为1，也就是每个事务提交完整前，都要redo log(prepare阶段)，和binlog持久化到磁盘。

组提交(group commit)

日志逻辑序列号(LSN)：LSN是单调递增，用来对应redo log的一个个写入点，每次写入长度为length的redo log，LSN的值就会加上length，LSN也会写到InnoDB的数据页中，保证数据页不会被多次执行重复的redo log。

例如有三个并发放事务，trx1，trx2，trx3在prepare阶段，都写完了redo log buffer，持久化到磁盘的过程中，LSN分别为50，120，160。

如果trx1先到达，这时候已经有三个事务，LSN是160，那么等trx1返回时，所有小于等于160的redo log，都已经被持久化到磁盘，那么trx2，trx3就可以直接返回。所以一次组提交里面，事务越多，节约磁盘IOPS(每秒IO读写次数)效果越好，为了每次带更多的事务组员一起提交，MySQL做了以下优化：

对于两阶段提交，是先写redo log prepare再binlog，再redo log commit，但是事实上提交的顺序为：

1. redo log prepare：write
2. binlog：write
3. redo log prepare：fsync
4. binlog：fsync
5. redo log commit：write

这样一来，binlog也可以进行组提交，有多个事务的binlog写完，也可以一起持久化，但是第三步执行的很快，如果想提升binlog组提交的效果可以用binlog_group_commit_sync_delay 参数，表示延迟多少微秒之后才调用fsync，binlog_group_commit_sync_no_delay_count 参数，表示累积多少次以后才调用 fsync，两个参数是或的关系。

MySQL性能出现瓶颈，而且主要在IO读写上，可以通过以下方式提升性能：

1.设置 binlog_group_commit_sync_delay 和 binlog_group_commit_sync_no_delay_count 参数，减少 binlog 的写盘次数。这个方法是基于“额外的故意等待”来实现的，因此可能会增加语句的响应时间，但没有丢失数据的风险。

2.将 sync_binlog 设置为大于 1 的值（比较常见是 100~1000）。这样做的风险是，主机掉电时会丢 binlog 日志。

3.将 innodb_flush_log_at_trx_commit 设置为 2。这样做的风险是，主机掉电的时候会丢数据。

事务

事务就是要保证一组数据库操作，要么全部成功，要么全部失败。

事务的启动方式为：

• 显式启动事务语句， begin 或 start transaction。配套的提交语句是 commit，回滚语句是 rollback，这里需要注意的是启动后执行第一个操作InnoDB表的语句，或者一开始直接使用start transaction with consistent snapshot，才算真正启动事务。
• set autocommit=0，这个命令会将这个线程的自动提交关掉。直到你主动执行 commit 或 rollback 语句，或者断开连接。

这里建议set autocommit=1，通过显式启动事务来开启。

事务的四大特性

• 原子性：一个事务中的所有操作，要么全部完成，要么全部失败，保证一个事务为一个整体。
• 一致性：事务开始之前和事务结束之后，数据库的完整性没有被破坏，比如一共100元，a用掉了50，那么100元就要变成50。
• 隔离性：数据库允许多个并发事务同时对数据进行读写的能力，根据隔离级别的不同，每个事务之间的可见性不同，下面会重点介绍。
• 持久性：事务结束后，对数据的更改是持久有效的，并不会有时间限制。

事务的隔离级别

因为同时可能会有多个事务的存在，所以会产生脏读，不可重复读，幻读的现象，所以才有了相应的隔离级别。

• 脏读：一个事务读到了另一个未提交事务所修改的数据。
• 不可重复读：一个事务读到了另一个已提交事务所修改的数据。
• 幻读：一个事务再前后两次查询同一范围时，后一次查询到了前一次查询没有看到过的行。再RR级别，幻读只出现于"当前读"，专指新插入的行。

说完以上现象就可以说明事务的隔离级别，隔离级别越高，效率就会越低，一共有四个隔离级别，包括：

• 读未提交：一个事务还没提交，做的变更就能被其他事务看到，也就是会出现脏读的情况。
• 读提交：一个事务提交之后，它做的变更才会被其他事务看到，也就是会出现不可重复读的情况。
• 可重复读：一个事务执行过程中看到的数据，总是跟这个事务启动时看到的数据是一致的。
• 串行化：更新会加写锁，读会加读锁，当另一个事务访问出现读写冲突时候，就必须等到前一个事务执行完成才可以继续执行。

事务在访问时会创建一个视图，访问时以视图的逻辑结果为准，可重复读下，是在事务启动时创建，读提交是在每个sql语句开始时创建，读未提交直接返回最新值，串行化直接枷锁来避免并行访问。

Oracle数据库默认是读提交，MySQL 的隔离级别是可重复读，可通过参数transaction-isolation来配置，show variables来查看当前的隔离级别。

可重复读级别

可重复读级别是mysql默认，也是并发时最常用的隔离级别。

每条记录更新时都会记录一条回滚操作来记录前一个状态的值。在不同时刻启动的事务会有不同的read-view（读视图），如果一个值分别从1被改成2，3，4，然后改成2的这个事务想得到1就要从4一次执行3，2，1的回滚操作才能得到。

所以当一个长事务存在时（read-view也随之开启），后面所有的事务进行更新提交后，回滚日志（undo log）都会存在于这个长事务中，只有当没有比回滚日志更早的read-view存在时，回滚日志才会被删除，那么回滚日志的增多会导致大量占用存储空间，只有当这个长事务提交后，read-view才会结束，那么所有的回滚日志才会被删除，而且长事务还占用锁资源，这也是为什么不建议开启长事务的原因。

MVCC(多版本并发控制)

视图有两种：

1.一个是view，查询语句定义的虚拟表，在调用的时候执行查询语句并生成结果，创建视图的语法是create view… ，查询方法与表一样

2.另一个视图则是MVCC用到的一致性读视图，consistent read view，用于支持Read Committed和Repeatable Read隔离级别的实现。

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

而每行数据也是有多个版本的，每次事务更新时候都会生成一个数据版本，并把transaction id赋值给这个数据版本的事务ID，记为row trx_id，同时旧版本也保留。也就是说，数据表的一行记录，可能有多个版本（row），每个版本都有自己的row trx_id。

也就是多个事务更新同一行时，不同时刻产生的row trx_id和修改前的值可以通过undo log计算出来。

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

已提交的最大row trx_id记为up_limit_id，当前系统里面已经创建过的事务ID最大值+1记为low_limit_id，于是这个视图数组和low_limit_id，就组成了当前事务的一致性视图（read-view）。

当事务更新一条语句时，会把该行之前的row trx_id写到undo log中，并把修改这条语句的transaction_id记在行头，变为该行当前最新的row trx_id。

一个事务要看一查看一条数据时，如果该行的row trx_id > low_limit_id，说明是这个事务创建视图之后，以后的事务进行更新的，所以不可见，去undo log中去取，如果发现小于up_limit_id，则是之前提交的事务更新的，可见，如果在up_limit_id和low_limit_id之间，就要看这个row trx_id是否提交，提交就可以查看到，没提交则不可查看。

更新数据时与查询不同，更新数据都是先读后写，都是当前读，也就是读取到这个数据的最新版本。如果这个记录的行锁被其他事务占用的话，就用进入锁等待。

读提交则会每次执行语句前都会创建一个新的视图。

索引

索引就是为了提高查询效率，快速找到匹配的数据。

索引的数据结构

• 哈希表：以键 - 值（key-value）存储数据的结构，用一个哈希函数把key换算成一个确定的位置，然后把value存放在数组中的这个位置，如果出现同一个位置的情况，会拉出一个链表。优点是追加很快，适合等值查询，但是由于不是有序，导致范围查找时比较慢。
• 有序数组：按照某一属性递增或递减的顺序保存，如果要查特定的值，通过二分法也可以很快找到，因为是有序的，对于范围查找效率也很高，但是缺点就是更新数据时候代价比较大，插入或删除时，要把数组后面所有的记录都要挪动，所以比较适用于静态存储的数据。
• 二叉树：二叉树的特点是每个节点的左节点小于父节点，父节点小于右节点，查询复杂度和更新复杂度都为O(log(N))，但是由于存储的数量量的增大，树高也随之变多，那么访问数据块的次数也变得多，消耗的时间就变多了。因此为了尽可能减少访问数据块的次数，就有了N叉树。N叉树的每层存储的数量都是N的幂次，数据量很庞大。

InnoDB索引模型

InnoDB引擎使用了B+树，只有叶子节点才保存完整数据，且叶子节点间的索引是按顺序排序的，可分为主键索引和非主键索引。

• 主键索引：叶子节点保存的是整行数据，InnoDB中也被成为聚簇索引。
• 非主键索引：叶子节点保存的是主键的值，InnoDB中也被称为二级索引。
• 回表：普通索引的查询先得到非主键索引也就是二级索引的主键值，再通过主键索引表得到这个主键的整行数据，多了一次查询动作。

多表关联join

join中会涉及到两种查询方式。

1.NLJ(Index Nested-Loop Join)

对驱动表进行扫描，根据字段去被驱动表查找，走的是树搜索过程，扫描符合条件的行作为结果集返回，可以用到被驱动表的索引，简称NLJ。

如果被驱动表行数是M，每次被驱动表查一行数据，先要搜索索引，再搜索主键索引，每次搜索一颗树近似复杂度是log2M，因为要回表，那么就是2log2M，加入驱动表行数是N，扫描N行后，对于每一行都要到被驱动表匹配，整个过程复杂度为：N + N2*log2M。所以应该让小表做驱动表。

2.Simple Nested-Loop Join

如果没有索引，那么就是两表行数相乘的数量，所以MuSQL也没有使用这个算法，而是另一种，下面的算法。

3.BNL(Block Nested-Loop Join)

这个流程为：

(1).把驱动表的数据读入线程内存join_buffer中

(2).扫描被驱动表，把每行数据取出来和join_buffer中的数据做对比，满足条件的作为结果集的一部分返回。

与Simple Nested-Loop Join算法的时间复杂度是一样的，但是由于判断在内存操作，速度会快很多，join_buffer的大小是由join_buffer_size决定，默认256k，放不下所有数据的话就会分段放，判断一组之后下一组再进行。

如果需要分段的清空，那么扫描行数可能就是N+λ* N * M，驱动表分成K段，K可以表示为λ*N，λ范围(0,1)，所以还是小表当驱动表结果更小，如果N固定，也就是驱动表行数固定，那么影响这个就是join_buffer_size，join_buffer_size越大，一次性可以放入的行越多，那么分成的段K就越小，那么整体就越小。

综上所述：如果能走NLJ，那么用join是没问题的，但是如果走BLJ，那么消耗的资源太多，比如直接分开走索引查询后再作为结果集返回是更快的。

选择小表：先按照各自的条件过滤，然后再计算参与join的各个字段的总数据量，数据量小的那个表为小表。

join语句优化

1.Multi-Range Read 优化

由于大多数的数据都是按照主键递增的顺序插入得到，那么可以认为按照主键的递增顺序查询的话，对磁盘的读比较接近顺序读，提升读性能。

那么流程就变为：

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

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

(3).按照排序后的id数组到主键id索引中查记录，并返回结果。

read_rnd_buffer 的大小是由 read_rnd_buffer_size 参数控制的，如果放满了，就会去主键id索引查完后清空，继续找索引的下个记录。

想要稳定地使用 MRR 优化的话，需要设置set optimizer_switch=“mrr_cost_based=off”。（官方文档的说法，是现在的优化器策略，判断消耗的时候，会更倾向于不使用 MRR，把 mrr_cost_based 设置为 off，就是固定使用 MRR 了。）

2.Batched Key Access(BKA)

5.6版本后以内的算法，对NLJ算法的优化。原来是一行一行从驱动表中取出，再去被驱动表去做join，每次都是匹配一个值，那么MRR的优势就用不上了，这个算法的原理就是：

把驱动表的数据取出一部分，放到临时内存，也就是join_buffer中，然后用join_buffer中的数据到被驱动表中的索引树上对应。参数是：set optimizer_switch=‘mrr=on,mrr_cost_based=off,batched_key_access=on’；前面两个参数是开启MRR，因为BKA算法的优化要依赖于MRR，因为拿一部分数据到join_buffer中时，可以利用MRR对主键id进行排序，进一步优化。

3.BNL转BKA

一些情况，可以直接在被驱动表的条件字段上加索引，就可以变成BKA算法了。

如果这个语句执行次数很少，那么建索引就很浪费空间，这时候就可以使用临时表：

(1).把被驱动表满足条件的数据放在临时表中

(2).为了让join使用BKA算法，给临时表的对应字段加上索引

(3)让驱动表和临时表做join操作。

这里insert进临时表的过程对被驱动表做全表扫描，之后驱动表的数量在临时表上进行索引树搜索，在大数据量时，性能提高很明显。

4.扩展hash

由于musql的优化器和执行器不支持hash join，所以我们可以在业务端实现，流程为：

(1).把驱动表的数据存入一个hash结构

(2).查询被驱动表条件满足的行

(3).把满足条件的行，一行一行到hash结构中寻找匹配的数据，最后作为结果集。

索引优化

1.主键长度越小，普通索引的叶子节点就越小，那么普通索引占用空间也就越小。

2.如果只有一个索引，且该索引必须是唯一索引，那么这个索引就可以设置为主键索引，减少回表的动作。

3.覆盖索引：查询结果已经在普通索引表上包含，不需要回表操作。

4.最左前缀原则：联合索引中可以是最左的N个字段，字符串则是最左的N个字符，如果跳过最左原则，则不会走索引。所以在创建联合索引时，可以通过调整顺序，少维护一个索引，如果需要创建的索引数量相同，则可以使空间小的单独作为索引。

5.索引下推：MySQL 5.6之前对于where条件的过滤是在主键索引上找出数据行再对比字段值，5.6之后则是现在普通索引中包含的字段优先进行筛选，再进行回表，这样可以减少回表的次数。

6.普通索引和唯一索引的选择

查询过程的话消耗的性能和时间都差不多，无非是普通索引需要多读取下一行，来判断是不是符合条件要求，重点在于更新过程。

这里要引入change buffer的概念，如果要更新的数据页不在内存中，为了减少读取磁盘IO操作，可以先将更新操作缓存在change buffer中，下次查询访问这个数据页时，再执行change buffer系相关操作(merge)，保证数据逻辑的正确性，除了访问这个数据页会触发以外，后台线程和数据库正常关闭时都会merge操作。

由于唯一索引在更新时，要判断更新的值是否为唯一性，要去读取数据页来判断，只有都满足唯一性，在、才会对此进行更新，实际上也只有普通索引可以使用，change buffer 用的是 buffer pool 里的内存，可以通过innodb_change_buffer_max_size来设置占用buffer pool的百分比。

根据业务要求不同，有的业务可能再更新后要求立即查询，如果数据页不存在还是会去磁盘读取数据页到内存中，这种情况反而起到了副作用。

主要考虑更新性能上，建议尽量选择普通索引，然后根据业务要求，是否关闭change buffer，对于数据量大的表更新优化是很明显的。

change buffer 和 redo log对更新和查询语句的执行：

如果更新的数据页在内存中，直接更新，没有则在change buffer区域中，记下更新xxx的xxx，然后将更新动作记入到redo log中。

查询时候先从磁盘读入到内存中，在应用change buffer里的操作日志，生成正确的版本返回。

综上，change buffer主要是节省随机读磁盘的IO消耗，而redo log是节省随机写磁盘的IO消耗，转换成顺序写。

7.给字符串加索引

如果要给整个字符串加索引的话很浪费空间，所以选择适合的前缀索引就很重要了，但是前缀索引可能会多几次扫描，所以要定义好前缀的长度，可以用count(dinstinct left(‘colum’, 长度))来查看前缀多少长度损失比例是可以接受的，比如前缀6个之后区分度为95%，100个数据中95个是不同的，那么就可以用前index(colum(6))来选择索引。

但是前缀索引用覆盖索引的时候，因为不确定条件的正确性，会进行回表确认，那么覆盖索引的优化也就用不上了。

如果本身前缀的区分度不够大，后缀的区分度较大的情况，可以倒序存储之后，在创建前缀索引。

也可以调用函数算出hash值来存储，这样冲突的概率非常小，基本上不会增加扫描行数，但是与倒序存储一样，不能使用范围查询。

8.对字段不做函数计算

如果对字段做函数计算，会破坏索引的有序性，比如日期，然后用month()，由于是按日期大小排序，单独月份就不是排序的状态，因此优化器对于这种情况，就不会按照执行月份查找，放弃树搜索功能，而是一行一行扫描，如果普通索引的大小比主键小，就会走全索引扫描。

还有一种情况就是字段类型不符，需要做类型转换，所以不走树搜索。

还有字符编码转换，比如两个表分别是utf8和utf8mb4，utf8mb4是utf8的超集，也就是类似Long和Integer的感觉，那么如果在左边，那么字段就要做转换成上级，也就走不了树索引了。Extra也变为NULL，表示走主键索引一行一行搜索。

所以这种情况下，最好把做函数的地方放在字段右边，那么优化器会先算出值或者转换后再通过值走树索引。

锁

数据库锁设计是为了处理并发问题，合理地控制资源的访问规则。

全局锁

就是给整个数据加锁，可通过命令Flush tables with read lock (FTWRL)，除了读状态以外的操作都会被堵塞。

既然全库只读，为什么不设置set global readonly=true的方法呢，第一是readonly可能会用来做逻辑判断，比如判断主库还是备库，影响比较大，二是如果客户端发生异常断开，那么MySQL会自动释放这个全局锁，readonly则会一直保持，风险较高。

使用场景则是做全库逻辑备份，把每个表select出来存成文本，防止数据发生变化，但是主库上备份，那么业务基本上就会停摆，备份期间，从库不能执行主库同步过来的binlog，会导致主从延迟。

InnoDB引擎自带逻辑备份工具mysqldump，使用–single-transaction时，导数据前会启动一个事务拿到一致性视图，且其他事务可以正常更新，但是并非所有引擎都可以使用事务，所以如果像MyISAM引擎无法使用事务就还是通过FTWRL方法。

表级锁

表级锁中有两种，一种是表锁，一种是元数据锁（meta data lock，MDL）。

表锁

表锁的语法是 lock tables … read/write。与 FTWRL 类似，可以用 unlock tables 主动释放锁，也可以在客户端断开的时候自动释放。lock tables除了限制别的线程外，本线程也会进行同样的限制。

MDL锁

MDL不需要显式使用，在访问一个表时候回自动加上，MDL的作用是保证读写的正确性，防止表结构发生变化，当对一个表做DML操作时，会加上MDL读锁，当要进行DDL操作时，会加MDLL写锁。

如果有一个线程开启事务进行DML操作，另一个线程想要DDL操作，申请MDL写锁则会被前一个DML操作开启的MDL读锁堵塞，那么后面所有的DML操作都要拿MDL读锁，都会被堵塞。

行锁

锁住数据表中行记录。

两阶段锁：行锁是在需要的时候加上，并在事务结束的时候才会释放。

因为两阶段锁的原因，如果事务中需要锁多个行，那么应该把最有可能发生锁冲突，最影响并发度的所放在后面执行，那么离事务结束的时间也就越近，锁住的时间越短，并发度越好。

如果查询时间过长没有返回，可以怎么操作释放锁呢？

1.可以使用select blocking_pid from sys.schema_table_lock_waits把这个连接kill掉。

2.也有情况是在等flush操作，show processlist查看State状态最开始Waiting for table flush的前一个连接是哪个，就可以排查到了。

3.等行锁的情况，可以通过select * from t sys.innodb_lock_waits where locked_table=’test.t’\G命令查到，然后kill掉这个线程，才会释放行锁。

4.还有一种情况就是其他线程更新次数很多很多，导致ungo log有很多，那么本线程通过回滚找到值需要的时间就很多，lock in share mode当前读，所以速度要快一些。

死锁

在不同线程中出现了循环资源依赖，且都在等待对方线程的释放，就会导致这几个线程进入无限等待的状态，就是死锁。

解决死锁的方式有两种：

1.可以设置参数innodb_lock_wait_timeout来设置超时时间，超过设定值，则退出释放锁资源，InnoDB默认值是50s，建议进行修改，50s对于业务处理等待来说时间过长，但也不建议设置时间过小的值，因为有可能是单纯的锁等待。

2.发起死锁检测，主动回滚死锁链条中的某一个事务，让其他事务继续执行，将参数 innodb_deadlock_detect 设置为 on。

但是这里就会因为死锁检测占用大量的CPU资源，因为每次被锁上都要去检测其他线程是否占用了此线程的锁，时间复杂度为O(n)，100个线程，每个都是O(n)，那么最后就是检查1万个，解决方法有两个：

(1).如果你确保这个业务一定不会发生死锁，那么可以暂时关闭死锁检测，但这个操作是有风险的，一旦出现死锁那么就会出现大量的超时。

(2).控制并发度，可以通过中间件或者修改MySQL源码，在进入引擎前进行排队，再或者说无法实现这种方案的前提的话，可以将一行锁逻辑改成多行锁逻辑，使并发量减少。

间隙锁

由于幻读会导致binlog的记录顺序和语义上的问题，因此InnoDB引入了新的锁，也就是间隙锁，锁住扫描行前后两个值之间的空隙。隔离级别为可重复读下才会生效，读提交就没有间隙锁了。

间隙锁的作用是防止在间隙中插入数据，而间隙锁之间是不冲突的。

间隙锁和行锁合称为 next-key lock，每个next-key lock是前开后闭区间，而间隙锁为开区间。

这里面有两个原则，两个优化和一个bug：

1. 原则 1：加锁的基本单位是 next-key lock，next-key lock 是前开后闭区间。
2. 原则 2：查找过程中访问到的对象才会加锁。
3. 优化 1：索引上的等值查询，给唯一索引加锁的时候，next-key lock 退化为行锁。
4. 优化 2：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock 退化为间隙锁。
5. 一个 bug：唯一索引上的范围查询会访问到不满足条件的第一个值为止。

这里的等值查询是，执行过程中通过数搜索的方式定位记录的时候。

如果用lock in share mode会只锁走过的索引表， 如果用到了覆盖索引，那么不会锁住主键索引表，for update会都锁住。

如果用limit的话，会在扫描到指定行数后停下，不会继续扫描到不满足条件为止。

如果用到了order by desc，那么会从最大值往前扫描行，要扫描到最小范围之后，还要往前多扫描到不满足条件的位置，所以next-key lock会锁上不满足条件的位置为右闭区间，左边为下一个往前扫描的值，比如<=15，就要还要往前扫描到10，那么(5,10]就会被锁住。

由于next-key lock是间隙锁和行锁合称，原理上是先加间隙锁，后加行锁，所以如果被堵住了，是被行锁堵住，而间隙锁是可以锁上，如果其他线程再这个间隙锁上插入数据就会被锁住，很可能会出现死锁。

自增主键

show create table 命令就可以看到表定义里面出现了一个AUTO_INCREMENT，表示下一次插入数据时，如果需要自增就会生成这个值。

不同表对于自增值的保存策略不同：

1.MyISAM引擎的自增值保存在数据文件中

2.InnoDB是5.7之前保存在内存中，每次重启后都会去找max(id)+1，如果删除这个数据，自增值不会变化，直到重启之后再根据最大值id变化。8.0之后，将自增值记录在了redo log中，重启可以依靠redo log恢复之前的值。

如果id字段指定为0，null或未指定值，那么就会把自增值填到id上，如果指定id值，就直接用这个值，如果指定的值大于当前自增值，那么自增值修改成指定的值+1，否则不变。

新的自增值生成算法是：从 auto_increment_offset 开始，以 auto_increment_increment 为步长，直到找到第一个大于这个的值，作为新的值。

如果发生唯一键冲突，自增值也不会回退，或者事务回滚也不会回退，回退则可能会发生主键冲突，所以只有这样才能保证自增id是递增，但不保证是连续的。

自增锁的优化

对于自增锁，5.1.22引入了参数innodb_autoinc_lock_mode，默认是1，如果设置0表示用5.0版本前的策略，语句执行结束后才释放锁；1的话表示普通的insert语句，申请之后马上释放，类似insert…select这样的批量插入的语句，还是要等到语句结束后才释放；2的时候表示都是申请自增主键后立即释放自增锁。

因为并发性的问题，同一时刻insert…select语句和其他事务insert语句同时执行时，可能会导致顺序不一致，然后statement格式的binlog来复现时，只能一个事务一个事务来写，那么就会导致数据不一致，所以当考虑并发性，又不会出现数据不一致的时候，可以设置innodb_autoinc_lock_mode=2 ，并且 binlog_format=row。

对于批量插入的语句，MySQL进行了优化，如果每次都申请一次自增id锁，那么多数据时候申请的次数很多，速度慢，还影响并发插入的性能，批量申请自增id的策略为：

1.第一次申请自增id，会分配1个

2.1个用完之后，第二次申请就会分配2个

3.2个用完，第三次分配4个，依次2倍的增长申请

所以如果批量插入4个时候，到第三次分配，但是只用了1个，那么剩下三个自增id就会浪费掉，下次insert时，插入的id就是8。

InnoDB系统自增row_id

如果创建的InnoDB表没有指定主键，那么InnoDB会创建一个不可见的，长度为6个字节的row_id，InnoDB维护了一个全局的dict_sys.row_id值，所以无主键的InnoDB表，每插入一行数据，都要讲当前的dict_sys.row_id值作为要插入数据的row_id，然后把dict_sys.row_id的值加1.

表的主键id到达峰值之后再次插入id值不会再增加，还是原来的值，那么就会发生主键冲突，但是row_id会从0重新开始，而且由于是系统定义的，不会发生主键冲突，但是重要的是会发生数据覆盖的现象。

Xid

MySQL 内部维护了一个全局变量 global_query_id，每次执行语句的时候将它赋值给 Query_id，然后给这个变量加 1。如果当前语句是这个事务执行的第一条语句，那么 MySQL 还会同时把 Query_id 赋值给这个事务的 Xid。

而 global_query_id 是一个纯内存变量，重启之后就清零了，那么同一个数据库实例中，不同事务的Xid有可能相同，但是MySQL重启之后会重新生成新的binlog文件，所以同一个binlog文件里Xid是唯一的。

global_query_id 定义的长度是 8 个字节，这个自增值的上限是 264-1，这个可能性不大。

Innodb trx_id

Xid 是由 server 层维护的。InnoDB 内部使用 Xid，就是为了能够在 InnoDB 事务和 server 之间做关联。但是，InnoDB 自己的 trx_id是为了事务的可见性，而且只读事务中，是不会分配trx_id，而显示的很大的数，只是显示用的。

只读书屋不分配trx_id的好处有：

1.减少事务视图里活跃事务数组的大小，所以，在创建事务的一致性视图时，InnoDB就只需拷贝读写事务的trx_id。

2.可以减少trx_id的申请次数，在InnoDB里，大大减少了并发事务盛情trx_id的锁冲突。

到达顶峰值，会出现脏读的现象，因为trx_id回回到0重新开始，峰值之前的事务都能看到0开始后的所有事务。

insert时候发生的锁

在insert…select时候，如果一边遍历数据，一边更新数据的情况，例如查自己的数据，然后更新自己的数据，这种情况就符合使用内部临时表，会把数据都读出来，然后根据排序后，再limit返回需要的数据，这时候可以主动创建内存临时表来先走索引limit后插入到临时表，再返回给自己，这样就不需要做全表扫描了。

也是insert…select时候，RR级别下，binlog_format=statement会给查询到的主键索引上加next-key lock，防止其他session在这个时候插入到里面，发生主备不一致的情况。

普通insert的时候，如果开启事务，insert发生主键冲突时，会加入冲突的索引上加next-key lock，其他线程就无法在这个区间进行insert，进行锁等待。

还有一种就是session A进行insert一个值，session B和C都往这个唯一键上插入，发现唯一键冲突，加上读锁，等A释放写锁，然后session A回滚，B C都加入写锁，但是都要等对方的读锁释放，就会发生死锁。

nsert into … on duplicate key update，插入一行数据，如果碰到唯一键约束，就更新这条语句，如果主键和唯一键都发生了冲突，因为主键id先判断，所以就会更新主键id所在的值，唯一键冲突的值不更新。

kill线程

kill并不是马上停止，而是让线程开始执行停止的逻辑，kill query thread_id时，把session的状态改成THD::KILL_QUERY ，给session发一个信号表示状态变更了，不需要等待了，来处理THD::KILL_QUERY状态。

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

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

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

在等行锁时，使用的是pthread_cond_timedwait 函数，时可以被唤醒，但是innodb_thread_concurrency不够用的时候，进入的等待是判断是否可以进入，不行就进入sleep状态，虽然设置成KILL_QUERY，但是这个过程中，没有判断线程的状态，所以不会终止逻辑阶段，这时可以用kill connection命令，会断开这个线程的网络连接，但是这个线程的状态依然是Killed，直到这个线程执行语句时候，才有可能判断线程状态，进而终止逻辑。

此外，IO压力过大，读写IO的函数一致不返回，也会不能及时判断线程状态。还有一种情况是终止逻辑耗时很长，有以下几种情况：

1.超达事务被kill，这时候就会回滚操作，由于期间新数据版本很多，回滚需要很多次，耗时很长。

2.大查询回滚，查询过程生成了大的临时文件，再加上文件系统压力大，删除临时文件需要等IO资源，导致耗时长。

3.DDL命令执行到最后阶段，被kill，需要删除中间过程的临时文件，也需要等IO资源。

关于客户端的误解：

由于客户端连接后会执行show databases，然后切换到库执行show tables，把这两个命令结果用于构建一个hash表，所以表数量多时，这一步花费的时间长，可以再连接命令中加-A，关掉这个自动补全的功能，–quick也可以跳过这个阶段，但是这个参数会关闭本地缓存，也不会把执行命令记录到本地的命令历史文件，所以连接是更快了，但是不缓存反而会使性能变慢。

排序

全字段排序

排序的流程为：

1.初始化sort_buffer，把查询的字段放入进去

2.从普通索引上找到满足的条件的主键索引，再从主键索引取出整行，再把查询的字段存入sort_buffer中

3.从普通索引中继续取下一个记录，直到不满足条件为止

4.根据需要排序的字段做快速排序，有limit的话返回前几个需要的行。

这里面的按字段排序可能在内存中完成，也有可能使用外部排序，取决于排序所需的内存和参数sort_buffer_size，如果排序数据量大于这个参数，就到磁盘临时文件辅助排序，可以使用OPTIMIZER_TRACE参数的number_of_tmp_files查看是否使用了外部排序，0代表在内存中排序，因为外部排序使用的是归并，多少数值就代表归并分成了多少分临时小文件，最后再合并返回。

OPTIMIZER_TRACE还有两个参数需要说明：

1.examined_rows：代表满足条件的记录，也就是参与排序的记录。

2.sort_mode 里面的 packed_additional_fields 的意思是，排序过程对字符串做了“紧凑”处理。即使 name 字段的定义是 varchar(16)，在排序过程中还是要按照实际长度来分配空间的。

rowid排序

如果要返回的查询长度过长，那么内存中存放的行数很少，可能要分成多个临时文件，导致性能很差，可以通过SET max_length_for_sort_data来设置最大长度，当查询的长度超过这个值的时候，就会触发rowid排序。

1.初始化sort_buffer，只放入排序的字段和主键id

2.与全字段查询一样的步骤，直到返回结果。

3.返回结果是根据主键id，回到原表中取出相关的字段返回给客户端。(如果有limit，那么先limit后再回原表)。

由于要回到原表这个操作，所以扫描的行数要比全表扫描多回表的数量。

此时，sort_mode 变成了<sort_key,rowid>，表示参与排序的只有排序字段和id 这两个字段。

不需要排序的情况

如果需要排序的字段本身就是有序排列的，比如联合索引，对第一个索引进行等值条件，第二个索引要求排序，此时Extra字段也没有Using filesort，不需要再次排序，如果将查询字段用上覆盖索引，那么性能会更快。

随机排序

如果要在数据库中实现的话可以使用rand()函数，但这个函数要比较复杂，Extra字段显示Using temporary，也就是用到了临时表，由于临时表是存储在内存中的，也就是没有额外的磁盘IO访问操作，所以全字段和rowid，它会优先选择rowid方式，毕竟都是访问内存，且后面占用大小更少，流程如下：

1.创建一个临时表，使用的是memory引擎，表里有两个字段，第一个是double类型，记为字段R，第二个是varcahr(64)类型，记为W，且没有索引。

2.从查询的表中,按照主键顺序取出查询字段值，之后调用rand()函数，生成大于0，小于1的随机小数，并把岁数小数和查询值存入临时表的R和W字段中。

3.之后初始化sort_buffer，有两个字段，一个是double类型，一个是整型。

4.从内存临时表中一行一行取出R值和位置信息，分别存入sort_buffer中。

5.在sort_buffer中根据R的值进行排序，排序后取出limit后结果的位置信息，再到临时表中取出查询值返回给客户端。

整个过程要扫描整个表数据的两倍加上limit的个数。

内存表也有大小限制，可通过tmp_table_size来设置，默认16M，超过这个大小就会转成磁盘临时表，这时使用的引擎就是InnoDB了。

5.6版本以后引入了一个新的排序算法：优先队列排序算法(TOP K)，sort_buffer表中，从临时表先顺序取出limit个数的R和位置信息，然后逐个与堆中的TOP K进行比较，最后得到最大的堆，然后去临时表中取出查询字段。

OPTIMIZER_TRACE 结果中，filesort_priority_queue_optimization 这个部分的 chosen=true，就表示使用了优先队列排序算法，这个过程不需要临时文件，因此对应的 number_of_tmp_files 是 0

如果limit后的个数太大，数据量大于sort_buffer_size大小的话就只能用归并排序算法了

不管怎么看，直接排序消耗的资源非常多，过程也比较复杂，如果需要大量的扫描行数，最好使用其他的方法。

代替rand()的随机排序方法

1.X = (M-N)*rand() + N，然后取出不小于X的第一个行。这个方法很快，只需要取最大值最小值两行，再加上X之后的第一行，扫描三行就可以得到，但是对于id不连续的表来说，选择的概率不一样，不算是真正的随机。

2.整个表的行数*rand()，再调用floor函数取整，Y = floor(C * rand())，再用limit Y,1，虽然要扫描整个表的行数，但是也要比rand()方法快很多。

3.取整个表的行数C，然后与2一样，得到三次Y，然后执行三次limit Y,1。

4.根据3，可以进一步再优化，取Y1，Y2，Y3中的最大值M和最小值N，然后limit N，M-N+1，然后在这里面取其中三条，这样整体的扫描行数就更少了。

MySQL有哪些提高性能的方法？(可能会有损)

1.短连接风暴：再业务高峰期时，可能会很多的短连接，max_connections 参数用来控制连接数量，一旦数量超过这个值，就会报错，拒绝连接，如果参数设置太高，会导致系统的负载进一步加大，大量的资源浪费在连接的逻辑上，为了解决这个问题，有两种方法：

(1)对于不需要保持的连接，可以通过kill connection主动释放掉，也可以用wait_timeout参数表示空闲到多少秒后，会被断开连接。但是客户端并不会马上收到，而是下一次发送请求时，收到报错，有的应用端收到这个错误后，继续用这个连接重试，导致一直无法连接。

(2)–skip-grant-tables 参数可以跳过权限验证，但意味着所有的连接都进行跳过，风险极高，8.0版本后会默认把 --skip-networking 参数打开，表示这时候数据库只能被本地的客户端连接。

2.慢查询性能问题：

(1)当索引没有设计好时，可以通过紧急创建索引来解决，因为5.6版本后创建索引都支持Online DDL，直接执行alter table语句，先在备库关闭binlog后，执行alter table，之后主备切换，在新的备库进行同样操作，之后打开binlog即可。也可以使用gh-ost方案，更加稳妥。

(2)语句没写好，5.7版本后可以用query_rewrite功能，替换指定语句的规则。

(3)选错索引，这时候也是使用上面的重写功能，加上force index强行指定索引。

3.QPS(每秒查询数)突增

可以暂时下掉这个功能，但是风险极高。

(1). 一种是由全新业务的 bug 导致的。假设你的 DB 运维是比较规范的，也就是说白名单是一个个加的。这种情况下，如果你能够确定业务方会下掉这个功能，只是时间上没那么快，那么就可以从数据库端直接把白名单去掉。

(2). 如果这个新功能使用的是单独的数据库用户，可以用管理员账号把这个用户删掉，然后断开现有连接。这样，这个新功能的连接不成功，由它引发的 QPS 就会变成 0。

(3). 如果这个新增的功能跟主体功能是部署在一起的，那么我们只能通过处理语句来限制。这时，我们可以使用上面提到的查询重写功能，把压力最大的 SQL 语句直接重写成"select 1"返回。

MySQL主备结构

现在的很多高可用的mysql架构都是以一主一备演化过来的。

主备切换

现在有两个数据库A,B。
客户端连接数据库A，B库设置只读，把A库的更新同步过来，需要切换时，客户端连接数据库B，A库设置为只读，把B库的更新同步，设置只读为了防止误操作，切换过程中出现双写，导致主备不一致，还可以通过readonly判断节点的角色。readonly对超级权限用户是无效的，所以可以同步数据。

备库和主库之间维持一个长连接，主库有专门的线程用于服务备库的长连接，一个事务日志同步的过程为：

1.备库上通过cahnge master命令，设置主库的IP，端口，用户名，密码以及从那个位置开始请求binlog，这个位置包含文件名和日志偏移量。

2.备库执行start slave命令，这时候备库会启动两个线程，分别是io_thread和sql_thread，io_thread负责与主库建立连接。

3.主库校验完用户名，密码后，开始按照备库传过来的位置，从本地读取binlog给备库。

4.备库拿到binlog后，写到本地文件，称为中转日志(relay log)。

5.sql_thread读取中转日志，解析出日志里的命令，然后执行。

现在生产上用的比较多的是双M结构，也就是主备库互为主备关系，但是这种时候就要解决循环执行binlog的问题，因为两者同为主库，也同为备库。

binlog中会记录这个命令第一次执行时所在的实例的server id，可以通过server id相同时，表示这个日志是自己生成的，丢弃这个日志，节点A更新事务，binlog里记录A的server id，节点B执行后，生成binlog的server id，这个server id也是A的，然后传回给A，A收到后判断里面的server id与自己相同，那么就不再处理这个日志。

主备延迟

主库A执行完一个事务，写入binlog，传给备库B，备库接收到后执行这个binlog，整个过程的时间差就是主备延迟。可以在备库上执行show slave status 命令，里面的seconds_behind_master用于表示当前备库延迟多少秒。

网络正常时，日志从主库传到备库的时间很短，主要的延迟来源于备库接收完binlog后，执行这个的时间差，最直接的表现时备库消费中转日志(relay log)的速度，比主库生产binlog的速度要慢。

形成主备延迟的来源：

1.备库所在机器的性能要比主库所在的机器性能差。

2.备库的压力大，备库可以提供读能力和后台需要的分析语句，为了不影响正常业务，所以在备库上执行，导致备库消耗大量的CPU资源，影响同步速度。这种情况，可以多接几个从库来分担读的压力，而且从库可以保证有定期全量备份的能力，也可以把binlog输出到外部系统，如Hadoop这类系统，让外部系统提供统计类查询的能力。

3.大事务：一个事物执行了很长时间，比如里面一次性delete语句删除很多数据，或者对大表进行DDL。

备库的并行复制能力

由于执行relay log的速度慢很多，因此5.6版本后，MySQL将sql_thread改成coordinator，coordinator只负责读取relay log之后分发给多线程的worker，worker的个数是由参数 slave_parallel_workers决定，32核物理机最好设置为8~16，毕竟备库还要提供读查询，所以不能占用所有的CPU。为了不破坏事务的隔离性和事务之间的执行顺序，coordinator要满足两个条件：

1.能造成更新覆盖，要求更新同一行的两个事务，必须分发到同一个worker中

2.同一个事务不能被拆分，必须放到一个worker中。

MySQL5.6的并行复制策略是按库并行，用于决定分发策略的hash表中，key就是库名，优点是由于DB数量不会特别多，因此构造hash值很快，也不用要求binlog格式，但是缺点也显而易见，如果主库的表都放在一个DB，或者同一时间的热点表都在一个DB，那么也起不到并行效果，所以这个策略用的不多。

MariaDB的并行复制策略：

用到了组提交的优化，如果在同一组里可以提交的事务，一定不会修改同一行，因为有行锁，主库上可以并行执行commit的事务，备库上也一定可以。步骤为：

1.一组里面一起提交的事务，有一个相同的commit_id，下一组就是commit_id+1

2.commit_id直接写到binlog里

3.传到备库应用时，相同commit_id的事务分发到多个worker执行

4.这一组执行完成后再取下一组。

但是主库上实现的并发是事务提交时，也有事务在进行，MariaDB则是要等一组事务完全执行完之后，第二组才能开始执行，系统的吞吐量就变低了、

MySQL5.7的并行策略：

通过MariaDB的实现，之后官方也提供了类似的功能，由参数slave-parallel-type来控制策略，DATABASE按库，LOGICAL_CLOCK就是类似MariaDB，这里，官方针对并行度做了优化。

对于InnoDB引擎，到达redo log prepare阶段，就代表事务已经通过锁冲突检验，所以优化是对于同时处于prepare状态和之后状态的事务，备库执行时是可以并行的。之前的拉长binlog write和fsync的时间，在这里就可以制造更多的prepare阶段的事务，提高了备库复制的并行度。

MySQL5.7。22的并行策略：

新增一个参数binlog-transaction-dependency-tracking，COMMIT_ORDER则是5.7根据prepare和commit来判断是否并行，WRITESET表示对于事务涉及的每一行，计算hash值，然后组成集合，如果两个事务没有操作同一行，也就是没有交集，就可以并行，WRITESET_SESSION比WRITESET多了一个约束，同一线程的两个事务执行顺序要与主库的执行顺序相同。这个 hash 值是通过"库名 + 表名 + 索引名 + 值"计算，如果有其他唯一索引，那么对于每个唯一索引，insert语句对应的writeset集合就要多增加一个hash值。

优点：

1.writeset是在主库生成后直接写道binlog里，备库执行时，不需要解析binlog的内容，节省计算量

2.不需要把整个事务的binlog都扫一遍才决定分发到哪个worker，节省内存

3.由于备库的分发策略不依赖于binlog内容，因此statement格式也是可以的。

当然，对于表上没主键和外键约束的场景，都无法实现并行。

可靠性优先策略

1.判断备库B现在的seconds_behind_master是否小于特定的值，否则继续重试判断。

2.把主库A改成只读

3.判断备库B的seconds_behind_master的值，直到这个值变为0秒为止。

4.把备库readonly设置为false

5.把业务请求切换到备库B

由于步骤3，两个数据库都处于不可写的状态，所以要保证seconds_behind_master足够小，但还是会有几秒的不可用时间。

可用性优先策略

可以把备库的readonly的值先改成fasle，然后业务请求先切换到备库。但是这样可能会导致数据不一致的情况，由于有延迟，直接切换后，主库上的binlog还没有写入到备库的relay log中，先执行了客户端的请求，然后生成的binlog再传给原来的主库，如果时insert语句，那么很有可能自增的主键顺序不一致，导致主备数据不一致。如果binlog是row格式的，那么就会出现主键冲突错误，不会插入。所以row格式的数据不一致问题更容易被发现，大多数情况下，可靠性要由于可用性。

所以延迟的时间越少，代表可靠性的前提下，可用性越高。

MySQL主从结构

大多数互联网应用场景都是读多写少，因此为了解决读性能的问题，就会涉及到一主多从架构。

一主多从的设置，一般用于读写分离，主库负责所有的写入和一部分读，其他的读操作由从库分担。

主备切换

从库切换它的主库时，要执行change master命令：

CHANGE MASTER TO MASTER_HOST=$hos{t}_{n}ameMASTE{R}_{P}ORT=$port MASTER_USER=$use{r}_{n}ameMASTE{R}_{P}ASSWORD=$password MASTER_LOG_FILE=$maste{r}_{l}o{g}_{n}ameMASTE{R}_{L}O{G}_{P}OS=$master_log_pos

前四个代表新主库的IP，端口，用户名和密码。最后两个参数表示新主库对应的文件名和日志偏移量。

一般找同步位点方法是：

1.等待新主库把relay log全部同步完成

2.在新主库上执行show master status命令，得到新主库上的最新File和Position

3.取原主库故障时刻T

4.用mysqlbinlog工具解析新主库的File，得到T时刻的位点。

可以得到end_log_pos的值，这个就是我们要找的位点，但是这个值不是很准确，如果原来的主库把binlog已经传给了备库也就是新主库和从库，然后传完的瞬间断电，那么从库上，由于同步了binlog，这一行已经存在，新主库上也存在，也就是end_log_pos的位置之后，那么从库上执行change master命令，指向新主库的这个位置，就会把这一行数据的binlog又同步回从库，那么从库就会报主键冲突的错误，然后停止同步，有两种方法，可以跳过这些错误：

1.主动跳过一个事务:

set global sql_slave_skip_counter=1;

start slave;

但是不一定只有一个事务会有重复执行的冲突，所以从库刚切换新主库后，每次碰到这些错误就停下来，执行跳过一的命令，直到不再出现停下来的情况。

2.通过设置 slave_skip_errors 参数，直接设置跳过指定的错误。

1062错误是插入数据时唯一键冲突，1032错误是删除数据时找不到行。但是这个设置是主备切换过程中，直接跳过这两个错误是无损的，才可以这么设置，等到主备间的同步关系建立完成，并稳定执行一段时间之后，把这个设置设为空，以免以后真出现问题也跳过了。

GTID

上面的方法比较复杂而且容易出错，所以5.6版本之后引入了GTID这个概念，来解决这个问题，GTID是全局事务ID，在一个事务提交时候生成，是这个事务的唯一标识，格式为：

GTID=source_id:transaction_id

source_id是一个MySQL实例启动时生成的，是一个全局唯一的值，后面的transaction_id并不是事务id，而是一个整数，初始值为1，每次提交事务，这个值就加1。启动方式是gtid_mode=on 和 enforce_gtid_consistency=on 就可以了，每次生成的GTID都会加入到本实例的GTID集合。

GTID生成方式有两种：

1.gtid_next=automatic，就会把这个实例的GTID=source_id:transaction_id分配给这个事务，记录binlog时候，先记录一行SET@@SESSION.GTID_NEXT=‘source_id:transaction_id’;

2.gtid_next=指定一个GTID的值，如果这个GTID已经存在于实例的GTID集合中，那么接下来执行的这个事务会被系统忽略，如果不存在，就把这个GTID分配给接下来的事务，transaction_id也不用加一，并且加到GTID集合。

根据GTID的主备切换前面都一样，后面两个文件名和位置变为：master_auto_position=1，然后逻辑为：

1.我们把这个时刻，新主库A的实例的GTID集合为set_a，从库B的实例GTID集合为set_b。

2.实例B把set_b发给新主库A

3.实例A算出set_a与set_b的差集，把不在set_b的GTID集合所需要的binlog事务发送给从库B，如果新主库A本地不包含需要的所有的binlog，说明新主库A把B需要的binlog给删掉了，返回错误，如果全部包含，就找出第一个需要事务，发给从库B。

4.依次按顺序取binlog发送给从库B去执行。

那么根据GTID和DDL，主备切换流程可以变为以下这样：

1.主库X的实例上执行stop slave

2.备库Y上执行DDL语句

3.执行完成后，查询这个DDL语句对应的GTID到主库X上执行：

set GTID_NEXT=“source_id_of_Y:transaction_id”;

begin;

commit;

set gtid_next=automatic;

start slave;

这样备库的更新有binlog记录，同时也可以确保不会在主库X上不会执行这条更新。

读写分离

一种是客户端做负载均衡，这种情况会把连接信息放在客户端的连接层，还有一种就是MySQL和客户端之间有一个中间代理层proxy，proxy根据请求类型和上下文决定请求的分发路由。优缺点如下：

1.客户端直连，由于少了一层转发，查询性能稍微好一点，但是由于要了解后端部署细节，所以出现主备切换，数据库迁移等操作时候，客户端都会需要调整数据库连接信息，一般采用这样的架构，一定会伴随一个负责管理后端的组件，Zookeeper这样的，让业务端只专注于业务逻辑开发。

2.带proxy的架构都是由proxy完成，但是对后端维护团队的要求会更高，proxy也需要有高可用架构，整体相对比较复杂。

由于主从延迟的情况，从库很可能会读取到更新之前的状态。

解决这个问题的方案：

1.强制走主库的方案，假如业务需要必须要读取的这个数据的最新状态，那么就必须走主库，如果是金融类业务，可能就要放弃读写分离。

2.sleep方案，读从库前先sleep一下，执行一条类似select sleep(1)命令。这个方案的假设是sleep的时间之内，有很大的可能拿到数据的最新状态，例如：

卖家发布商品后，通过Ajax把客户端输入的"新商品"显示在页面上，而没有真正地做数据库查询，等到卖家再刷新页面时，已经过了一段时间，那么数据的状态可能已经得到了更新到了从库上，达到了sleep的目的。

这个解决方案有两个问题：如果本来在0.5秒就可以拿到最新的状态，也要等到sleep，或者延迟超过了sleep时间，还是会出现过期读的情况。

3.判断主备无延迟的方案

(1)每次去从库执行查询时，先判断seconds_behind_master是否为0，直到等到0才执行查询。

(2)对比位点确保主备无延迟，Master_Log_File和 Read_Master_Log_Pos是主库的最新位点，Relay_Master_Log_File 和 Exec_Master_Log_Pos是备库的最新位点，如果两者的最新位点数据一致，表示已经同步完成。

(3)对比GTID集合确保无延迟

Retrieved_Gtid_Set，是备库收到的所有日志的 GTID 集合，Executed_Gtid_Set，是备库所有已经执行完成的 GTID 集合，如果两者相同，说明备库已经把收到的日志集合都执行完毕

但是主备无延迟的方案还是没有严格的完成同步，如果主库上的事务执行完成写入binlog，并反馈给客户端，但还没有传到从库中，那么从库已经认为没有同步延迟，还是没有查到这个事务，下面的方法可以解决这个问题。

4.配合semi-sync

引入半同步复制，也就是semi-sync replication。

semi-sync做了这样的设计，事务提交后，主库把binlog发给从库，从库收到binlog以后发回主库一个ack，表示收到，主库收到这个ack之后再返回给客户端。

但是一主多从的场景下，主库也只是等到一个从库的ack就返回，那么还是可能会产生过期读的情况，而且在业务高峰期，主库的位点或者GTID集合更新很快，那么就会出现主从两组一直不相等，那么从库上迟迟无法响应查询请求的情况。

其实并不需要等到完全相同，只需要等到本次查询需要的事务更新状态即可，下一个方案会解决一主多从下的semi-sync问题。

5.等主库位点方案

select master_pos_wait(file, pos[, timeout]);

这条命令是在从库上执行，参数file和pos指的是主库上的文件名和位置，timeout可选，设置为正整数N，表示这个函数最多等待N秒。

那么流程为：

(1).事务更新后，马上执行show master status得到当前主库执行到的File和Position

(2).选定一个从库执行查询语句

(3).在从库上执行select master_pos_wait(File, Position, 1)

(4).如果返回值大于等于0，那么就在这个从库执行查询语句，否则到主库执行查询语句。

6.GTID等主库位点方案

select wait_for_executed_gtid_set(gtid_set, 1);

于上面的类似，知道这个库执行的事务中包含传入的gtid_set，返回0，超时返回1.

在5.7.6版本中，允许在执行完更新类事务后，把这个事务的GTID返回给客户端，设置参数为session_track_gtids设置成OWN_GTID，然后通过 API 接口 mysql_session_track_get_first 从返回包解析出 GTID 的值即可，那么就可以减少一次查询，流程为：

1.主库更新完成后，从返回包直接获取到这个事务的GTID

2.选定一个从库执行查询语句

3.在从库上执行select wait_for_executed_gtid_set(gtid, 1)；

4.如果返回是0，那么就去从库执行，否则到主库执行。

主库被动出现问题

1.外部统计

select 1成功返回只能说明这个库的进程还在，不能说明主库没问题。一般可以在系统库里创建一个表比如health_check，然后定期执行select * from mysql.health_check;但是更新语句时候如果binlog所在磁盘的空间占用率100%，那么所有的更新和事务提交都要等待，但是读数据是正常的，所以我们可以改成更新判断update mysql.health_check set t_modified=now()；节点可用性的检测主库和备库都应包含。

在双M结构下，如果备库执行检测命令，那么执行后要发给主库，两个库都更新相同的行可能会出现行冲突，导致主备同步停止，所以可以根据主备库的server_id来设置多条行进行更新检测，但是由于IO资源分配原因，这个检测使用的update命令，需要的资源很少，很有可能在指定时间内拿到IO资源提交成功，但是这时候IO利用率已经100%了，所以还是不够准确。

在 InnoDB 中，innodb_thread_concurrency参数默认值是0，表示不受并发线程数量限制，但是一个机器的CPU核数有限，一般设置64~128之间，这里的并发连接是并发查询的数量，如果出现锁等待的情况，不会算在计数里，要不然整个系统可能因为锁等待都堵死，然后CPU还是很空闲的情况。

2.内部统计

MySQL5.6版本之后提供的performance_schema 库，就在file_summary_by_event_name 表里统计了每次 IO 请求的时间。event_name='wait/io/file/innodb/innodb_log_file’表示redo log的统计，binlog对应的是event_name = “wait/io/file/sql/binlog”。

第一组五列，是所有IO类型的统计，其中，COUNT_STAR 是所有 IO 的总次数，之后是总和，最小值，平均值，最大值。

第二组六列，是读操作的统计，最后一列 SUM_NUMBER_OF_BYTES_READ 统计的是，总共从 redo log 里读了多少个字节。

第三组六列，统计的是写操作。

最后的第四组数据，是对其他类型数据的统计。在redo log里，你可以认为它们就是对fsync 的统计。

但是我们每一次操作数据库，performance_schema都需要额外的统计这些信息，是有性能损耗的，大概下降10%左右。所以建议只打开自己需要的项进行统计，在这里，你可以通过MAX_Time的值，也就是最大值来判断数据库是否出问题，设定阈值，单次IO请求时间超过这个阈值就判断为异常，然后清空统计信息，后面再出现这个异常，就可以加入监控累计值了。

误删数据

1.如果误删行，那么可以用Flashback工具通过闪回恢复，原理是通过修改binlog的内容，拿回原库重放，前提是binlog_format=row和binlog_row_image=FULL。

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

对于 delete 语句，也是将 Delete_rows event 改为 Write_rows event；

对于 Update_rows 的话，binlog 里面记录了数据行修改前和修改后的值，对调这两行的位置即可。

如果是多个事务的话，要把事务的顺序调过来执行。建议在从库或者恢复出一个备份，执行操作再恢复回主库，因为主库上这些数据可能又进行了更改，防止出现二次破坏。

预防：sql_safe_updates设置on，delete或者update的where条件没有走索引或者没写where条件就会报错，代码上线前经过SQL审计。

2.误删库/表

这时候就需要线上有定期的全量备份，并且实时备份binlog，流程为：

(1).取最近一次全量备份

(2).用备份恢复出临时库

(3).从日志备份出备份时刻之后的日志

(4).把这些日志，除了误删数据外，全部应用到临时库

如果有多个库，可以用mysqlbinlog命令加个-database指定误删表所在的库，避免恢复数据时要应用其他库日志的情况，在应用日志时候，跳过误删操作的语句的binlog，如果有GTID，那么直接set这个GTID，就可以跳过了，没有则通过–stop-position执行到误操作之前的日志，再用–start-position从误操作之后的日志继续。

由于mysqlbinlog工具并不能指定只解析一个表的日志，而且应用日志的过程只能是单线程，所以速度很慢，一种加速方法是在用备份恢复出临时实例之后，将这个临时实例设置成线上备库的从库，步骤为：

(1).在start slave之间，通过change replication filter replicate_do_table = (tbl_name)命令，可以让临时库只同步误删操作的表，这样做可以用上并行复制技术，加速恢复过程。

(2).如果备库上的binlog缺少实例需要的binlog，那么就要从备份系统下载缺少的master文件，放到备库的日志目录下，打开日志目录下的master.index文件，在文件开头加入两行内容，就是缺少的binlog文件，例如"./master.000003"

(3).重启备库，让备库重新之别日志文件，临时库再与备库进行连接，就可以同步了。

如果非常核心的业务，不允许太长时间的恢复，那么可以搭建延迟复制的备库，这个功能是MySQL5.6版本之后的，通过CHANGE MASTER TO MASTER_DELAY = N来与主库有N秒的延迟，在延迟时间内发现误删操作，就可以到悲苦上执行stop slave，再跳过误操作命令，就可以恢复了。

预防措施：

(1)账号分离，避免写错命令，给业务开发DML权限，而不给truncate/drop 权限，如果需要DDL，可以通过开发管理系统得到支持，DBA团队成员日常也只使用只读账号，必要时才用更新权限账号

(2)指定操作规范，避免写错要删除的表名，在删除数据表前，必须对表做改名操作，一段时间内确保对业务无影响再进行删除，改表名时，给表名加固定的后缀，而且必须通过管理系统执行，管理系统删除表时候，只删除这个带有固定后缀的表。

可以使用show grants命令查看账户的权限，以免分配的权限过大。

数据库内存

server层传输数据是通过net_buffer中，这块内存的大小是由参数net_buffer_length定义的，默认为16k，流程为：

1.获取一行，写入到net_buffer中

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

3.如果发送成功，清空net_buffer，取下一行，继续写入net_buffer

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

如果客户端没有读到socket receive buffer的内容，那么状态就是Sending to client，对应的Sending data不一定是正在发送数据，而是执行器过程中任意阶段，可以认为它正在执行。

内存的数据页是在Buffer Pool (BP) 中管理的，在WAL里，Buffer Pool起到了加速更新的作用，它还有加速查询的作用，因为查询时候将数据页保存在Buffer Pool中，不需要走磁盘。用的次数的概率就是我们常说的内存命中率。

可以用show engine innodb status查看BP命中率，一般要求99%以上，InnoDB Buffer Pool 的大小是由参数 innodb_buffer_pool_size 确定的，一般建议设置成可用物理内存的 60%~80%。

InnoDB 内存管理用的是最近最少使用 (Least Recently Used, LRU) 算法，也就是淘汰最久未使用的数据页。在InnoDB实现上，按照5：3的比例把整个LRU链表分成了young区域和old区域。

1.如果要访问的数据页在young区，那么就移动到链表头部。

2.如果访问的数据页不在Buffer Pool中，那么就会淘汰old区最后一个最久未使用的数据页，新加入的数据页在old区域的头部。

3.处于old区域的数据页，访问时间超过一秒钟，那么就会放到链表头部，如果短于1秒钟，那么位置保持不变。

这个优化是为了全表扫描时不会使内存命中率降低的方案。

临时表

临时表可以使用各种引擎，不局限于使用某个引擎。

1.建表语句时create temporary table…

2.一个临时表只能被创建它的session访问，其他线程不可见

3.可以与普通表同名，且同时有的时候，show create和DML操作访问的都是临时表

4.show tables命令不显示临时表

在分库分表的情况下，如果查询条件没有按分区字段来查，那么就会到所有的分区查找满足条件的所有行，那么效率就会很低，有两种思路优化：

1.在proxy层的进程代码中实现排序，这种方式处理速度快，拿到分库的数据之后，直接在内存中参与计算，但缺点是需要的开发工作量比较大，如果语句较复杂，对中间层的开发能力要求比较高，而且对proxy端的阿里比较大，容易出现内存不够用和CPU瓶颈的问题。

2.把各个分库拿到的数据汇总到一个MySQL实例的表中，然后在这个汇总实例上做逻辑操作。

可以在汇总库(或者某个分库)上创建一个临时表，然后到各个分库上分别执行，并把结果插入到临时表中，再到临时表中再操作。

临时表为什么可以重名

再创建临时表的时候，MySQL要给InnoDB表创建一个frm文件保存表结构定义，还有有地方保存表数据，这个frm文件放在临时文件目录下，文件名的后缀是.frm，前缀是前缀是“#sql{进程 id}{线程 id} 序列号”。你可以使用 select @@tmpdir 命令，来显示实例的临时文件目录。

5.6版本以前，MySQL会在临时文件目录下创建以一个相同前缀，以.ibd为后缀的文件，用来存放数据文件

5.7版本之后，引入了一个临时文件表空间，专门用来存放临时文件的数据，就不需要再创建ibd文件了

由于前缀是进程id和线程id，那么两个线程创建出来的临时表名就可以相同，因为线程id不同。

在session结束的时候，对链表里的每个临时表，执行 “DROP TEMPORARY TABLE + 表名” 操作，但是这个操作会写到binlog中，因为主备复制。

主备复制

如果主库上用临时表来读数据，备库执行同样的sql语句，那么就会报错，这个表不存在，但是binlog格式为row就不会记录都binlog中，因为row格式记录的是这个操作的数据，这种情况下删除临时表是线程退出时自动删除，备库的同步线程是持续运行，所以要在主库写DROP TEMPORARY TABLE 传给备库执行。

如果主库上有两个线程都起了相同的名也没关系，因为MySQL在记录binlog时候，把主库执行这个语句的线程id写到binlog中，这样，备库的应用线程就可以通过线程id来构造临时表，所以两个表在备库的应用线程里面是不会冲突的。

内部临时表

除了手动创建临时表外，MySQL内部也会创建临时表。例如两个表做union操作，会先把第一个表查询的数据存入临时表，再把第二个表查询的数据一行一行插入到这个临时表，如果存在则跳过，如果是union all那么就没有去重，就直接执行子查询直接发给客户端，也就不需要临时表了。

group by时，也常会使用临时表：

select id%10 as m, count(*) as c from t1 group by m;为例

1.创建内存临时表，表里放入m和c，m是主键

2.扫描表t1的索引a，依次取出叶子节点上的id值，计算id%10的结果，记为x，如果临时表中没有主键为x的行，就插入一个记录(x,1)，存在就把这个c的值加1

3.遍历完成后对m字段进行排序，返回给客户端。

对于内存临时表的大小是有限制的，tmp_table_size 就是控制这个内存大小的，默认是 16M，如果超过这个大小，就会转成磁盘临时表，默认使用引擎是InnoDB，如果表的数据量很大，那么可能这个查询需要的磁盘临时表就会占用大量的磁盘空间。

内存表

内存表的数据部分是以数组的方式存放，而主键id索引里，存的是每个数据的位置，主键id是hash索引，索引上的key并不是有序的。

与InnoDB不同，InnoDB把数据放在主键索引上，其他索引上保留主键id，这种方式为所以组织表，而内存表，也就是Memory引擎采用的是把数据单独存放，所以上保存数据位置的数据组织形式，称为堆组织表。

两种引擎的不同点：

1.InnoDB表的数据总是有序存放，而内存报的数据是按照写入顺序存放

2.当数据文件有空洞时，InnoDB表在插入新数据的时候，为了保证数据的有序性，只能在固定的位置写入新值，而内存表找到空位就可以插入。

3.数据位置发生变化时，InnoDB表只需要修改主键索引，而内存表血药修改所有索引。

4.内存表中所有索引都是一样的存储方式，不会像InnoDB一样有回表的情况。

5.InnoDB支持变长数据类型，不同记录的长度可能不同，内存表不支持Blob和Text字段，即使定义了varchar
(N)，实际也当作char(N)，也就是固定长度字符串来存储，因此内存表每行数据长度相同。

事实上，内存表也可以创建B-Tree索引，就可以使用范围查询，但是如果force index强行使用主键id索引，还是会走原来的hash索引。

内存表的优势是存储在内存中，而且支持hash索引，读写速度很快，但是生产环境上还是不适用使用内存表。

内存表的问题

1.内存表不支持行锁，只支持表锁，所有只要有更新，就会锁住整个表，并发性不够好，处理并发事务时候，性能也不会很好。

2.因为是保存在内存中，所以一旦MySQL重启，内存表就会被清空，如果备库重启，那么主库binlog传过来的数据就可能会报错，比如这一行不存在，然后备库一旦重启，会在binlog上记录Delete from T，那么主库收到binlog之后就会清空这个内存表。

因此内存表不适用于并发度高的场景，而且很容易数据丢失，InnoDB支持行锁，而且读的时候，数据也会缓存在InnoDB Buffer Pool里，读性能也不会很差。

那么内存表适用于什么场景呢，那就是用户临时表，数据量可控，而且不会消耗过多内存，用完即删的情况，因为临时表是当前线程访问，所以不会有并发度的问题，速度要比InnoDB的临时表快一些。

group by 优化方法

1.索引

如果可以利用索引，那么扫描数据的过程就不需要临时表存数据再排序，5.7版本支持generated column 机制，创建一个列，然后在这个列上穿件一个索引alter table t1 add column z int generated always as(id % 100), add index(z);

5.6版本之前可以创建普通列和索引来解决这个问题。

2.直接排序

如果碰上不适合创建索引的场景，而且数据量比较大，正常执行就要先放到内存临时表，插入满了之后发现不够用，再转成磁盘临时转，这样就浪费时间，在group by 语句中加入 SQL_BIG_RESULT 这个提示（hint），直接用磁盘临时表，MySQL优化器觉得存储效率数组要优于B+树，从空间考虑，直接用数组来存。执行流程为：

(1).初始化sort_buffer，确定放入一个整型字段，记为m

(2).扫描表t1的索引a，一次取出里面的id值，把id%10的值m存入到sort_buffer中

(3).扫描完成后，对sort_buffer的字段m做排序，如果sort_buffer内存不够用，就会利用磁盘临时文件辅助排序

(4).排序完成后，等到一个有序数组

然后根据有序数组出现的次数，返回给客户端。

那么综上所述，MySQL在以下情况会用到内存表：

1.如果语句执行过程可以一边读数据，一边直接得到结果，是不需要额外内存，否则就需要临时表来保存中间结果

2.join_buffer是无序数组，sort_buffer是有序数组，临时表是二维表结构

3.如果执行逻辑需要用到二维表的特性，就会优先考虑使用临时表，比如union需要哦用到唯一索引约束，group by需要用另一个字段来存计数等等。

复制表的方法

mysqldump命令将数据到出一组insert语句：

mysqldump -h$host-P$port -u$user --add-locks=0 --no-create-info --single-transaction --set-gtid-purged=OFF db1 t --where=“a>900” --result-file=/client_tmp/t.sql

参数如下：

1.–single-transaction 的作用是，在导出数据的时候不需要对表 db1.t 加表锁，而是使用 START TRANSACTION WITH CONSISTENT SNAPSHOT 的方法；

2.–add-locks 设置为 0，表示在输出的文件结果里，不增加" LOCK TABLES t WRITE;" ；

3.–no-create-info 的意思是，不需要导出表结构；

4.–set-gtid-purged=off 表示的是，不输出跟 GTID 相关的信息；

5.–result-file 指定了输出文件的路径，其中 client 表示生成的文件是在客户端机器上的。

然后通过mysql -h127.0.0.1 -P13000 -uroot db2 -e "source /client_tmp/t.sql"到db2去执行。source是客户端命令，并不是sql语句。

导出CSV文件

select * from db1.t where a>900 into outfile ‘/server_tmp/t.csv’;

这条语句的结果保存在服务端，客户端和服务端不再一个机器上，那么客户端临时目录下是不会生成t.csv文件的，into outfile指定了文件的生成位置，这个位置必须接受参数secure_file_priv的限制。如果设置为empty，代表不限制文件生成的位置，是不安全的，设置为一个表示路径的字符串，就要求生成文件只能放在这个指定的目录或它的子目录，如果为NULL，就表示禁止在这个MySQL实例上执行into outfile操作，而且这条命令不会帮你覆盖文件，因此要确保路径下的这个文件不存在，否则就会报错，如果字段中包含换行符，那么文本中也会有类似换行符的符号，前面会跟上""转义符来跟字段之间数据行之间的分隔符分开。

之后load data infile ‘/server_tmp/t.csv’ into table db2.t;打开文件，以制表符\t作为分隔符，换行符\n作为记录之间的分隔符，进行读取，并启动事务，判断字段数与表db2.t是否相同，不同则报错，相同则调用InnoDB引擎接口写入，最后提交事务。

如果binlog_format=statement格式，备库没有/server_tmp/t.csv文件，所以到备库是这么存放的，主库执行完后往binlog里写入/server_tmp/t.csv 文件的内容和load data local infile ‘/tmp/SQL_LOAD_MB-1-0’ INTO TABLE db2.t，然后备库的apply线程执行这个事务时，先将t.csv的内容读出来写到本地临时目录/tmp/SQL_LOAD_MB-1-0，然后执行loaddata语句，往备库的db2.t插入与主库的相同数据。

这里面的语句多了local，意思是将执行这条语句的客户端所在机器的本地文件/tmp/SQL_LOAD_MB-1-0的内容加载到目标表中，如果有local，读取的是客户端的文件，只要mysql客户端有访问这个文件的权限就可以，然后等客户端先把本地文件传给服务端，在执行load data，如果不加local，是读取服务端的文件，这个文件必须在secure_file_priv 指定的目录或子目录下。

select …into outfile 方法不会生成表结构文件，所以到数据时还需要单独的命令得到表结构定义，mysqldump提供了一个-tab参数，可以同时到处表结构定义文件和csv数据文件。

物理拷贝方法

MySQL5.6引入了可传输表空间的方法，通过导出导入表空间的方法，实现物理拷贝表的功能。

假设我们现在的目标是在 db1 库下，复制一个跟表 t 相同的表 r，具体的执行步骤如下：

1.执行 create table r like t，创建一个相同表结构的空表；

2.执行 alter table r discard tablespace，这时候 r.ibd 文件会被删除；

3.执行 flush table t for export，这时候 db1 目录下会生成一个 t.cfg 文件；

4.在 db1 目录下执行 cp t.cfg r.cfg; cp t.ibd r.ibd；这两个命令（这里需要注意的是，拷贝得到的两个文件，MySQL 进程要有读写权限）；

5.执行 unlock tables，这时候 t.cfg 文件会被删除；

6.执行 alter table r import tablespace，将这个 r.ibd 文件作为表 r 的新的表空间，由于这个文件的数据内容和 t.ibd 是相同的，所以表 r 中就有了和表 t 相同的数据。

这里flush之后，整个t表数据只读状态，直到unlock tables命令才会被释放读锁，import tablespace时候，为了让表空间id和数据字典中的一致，会修改r.ibd的表空间id，如果时很大的文件，那么每个数据页都要被修改，就需要一段时间，但也比逻辑导入方法快很多

物理拷贝速度最快，但是只能全表拷贝，必须有数据库读写权限且可以登陆数据库主机，源表和目标表都是InnoDB才可以使用

mysqldump不能使用join这种比较复杂的where条件，普通的过滤条件可以

select … into outfile最灵活，但是每次只能导出一张表的数据，表结构也需要另备份。

用户权限操作

当使用create user命令创建用户时，做了两个动作，先在磁盘上，往mysql.user表里插入一行，由于没有指定权限，所有权限字段的值为N，内存里，往数据acl_users里插入一个acl_user对象，这个对象的access字段值为0，根据权限范围不同分为以下几种

全局权限

作用于整个MySQL实例，这些权限信息保存在mysql库的user表里，最高权限语句是：

grant all privileges on . to ‘ua’@’%’ with grant option;

磁盘上，将mysql.user表里的用户’ua’@’%'这一行所有权限都修改成Y，内存里，acl_users这个用户对应的对象的access值(权限位)修改成二进制的"全1"

这里grant对于全局权限，同时更新了磁盘和内存，并立即生效，对于已经存在的连接则不受grant命令的影响。回收为revoke all privileges on . from ‘ua’@’%’;回收所有权限。

db权限

privileges on db1.*就可以拥有bd1的所有权限，还是一样，往磁盘上的mysql.db表中插入一行记录，所有权限位字段设置为Y，内存里设置成"全1"

每次需要判断用户的数据库读写权限，都需要遍历acl_dbs数组，根据对应的对象权限位来判断，也就是说grant修改db权限时候，也是对磁盘和内存同时生效的，但是对于已经连接的对象，是可以影响到他们的数据库权限的，除非在这之前使用use，已经拿到这个库的权限。

表权限和列权限

privileges on db1.t1，就可以得到t1的表权限，GRANT SELECT(id), INSERT (id,a) ON mydb.mytbl可以得到列权限，表权限定义存放在表 mysql.tables_priv 中，列权限定义存放在表 mysql.columns_priv 中。这两类权限，组合起来存放在内存的 hash 结构 column_priv_hash 中。

flush privileges

flush privileges命令会清空acl_users 数组，然后从mysql.user表中读取数据重新加载，重新构造一个acl_users数组，也就是说，以数据表中的数据为准，将全局权限内存数组重新加载。一般是在权限数据可能存在不一致的情况下再使用，而这种不一致往往是直接用DML语句操作系统权限表导致的，所以尽量不要使用这类语句。

分区表

PARTITION BY RANGE (YEAR(ftime))

(PARTITION p_2017 VALUES LESS THAN (2017) ENGINE = InnoDB,

PARTITION p_2018 VALUES LESS THAN (2018) ENGINE = InnoDB,

PARTITION p_2019 VALUES LESS THAN (2019) ENGINE = InnoDB,

PARTITION p_others VALUES LESS THAN MAXVALUE ENGINE = InnoDB);

通过定义的分区规则，把表按照规则分到各个的区域，对于引擎层来说，是4个表，对于Server层来说是1个表，这就是分区表。

分区表在InnoDB间隙锁的时候，不会跨区锁，MyISAM表锁因为是在引擎层锁住，也只是锁区表，但是分区锁看着挺不错，但是实际生产生不让用，主要原因在Server层上，打开表的行为。

分区策略

每当第一次访问一个分区表的时候，都需要把所有的分区都访问一边，如果分区表的数量超过了open_files_limit参数的数量就会报错，MyISAM在8.0在之后就不允许创建分区表了，目前只有InnoDB和NDB有。

从Server层来看，一个分区表只是一个表，因此所有的分区公用同一个MDL锁，在引擎层，认为是不同的表，因此MDL锁之后的执行过程，会根据分区表规则，之访问必要的分区。

而且只能在单机情况下使用，集群下不能既使用innoDB，又使用分区表，所以现在主流是用成熟的分库分表中间件，没有额外的复杂性。

案例问题

1.一天一备份和一周一备份的对比？

答：一天一备份的好处是可以恢复到更近的时间点的binlog，但坏处就是频繁的全量备份需要消耗更多的存储空间，一周一备份最坏情况恢复数据只能恢复到一周前的状态，好处是消耗的存储空间更少。

2.如何避免长事务对业务的影响？

答：从开发端来看：

1.是否使用了set autocommit=0，可以开启general_log的日志确认，将它设置成1。
2.确认是否有不必要的只读事务，如果业务没有事务的需要，就不需要开启事务然后只读的操作。
3.可以设置SET MAX_EXECUTION_TIME命令控制每个语句的最长执行时间。

从数据库端来看：

1.监控 information_schema.Innodb_trx 表，设置长事务阈值，超过就报警 / 或者 kill。
2.Percona 的 pt-kill 这个工具不错，推荐使用。
3.在业务功能测试阶段要求输出所有的 general_log，分析日志行为提前发现问题。
4.MySQL 5.6版本以上，可以设置innodb_undo_tablespaces参数来设置独立表空间，比如设置成4，以后也方便清理

3.通过alter语句重建主键和普通索引是否合理？

答：重建索引的目的是因为删除或者页分裂等原因，会导致数据页有空洞，重建索引的过程会创建新的索引，将数据按顺序插入，使页面的利用率最高，索引更紧凑，更节省空间。

重建普通索引是合理的，但重建主键的过程是不合理的，会将整个表进行重建，那么前面重建的过程就白做了，可以使用alter table T engine=InnoDB来代替。

4.如何安全地给表加字段？

答：如果有长事务，首先要暂定DDL，或kill掉这个长事务，在MySQL的information_schema库的innodb_trx表中，可以查到当前执行中的事务。如果这是个热点表，可以在alter table语句里设定等待时间，拿不到则放弃，之后再重试，直到拿到MDL写锁进行修改。

5.当备库用–single-transaction 做逻辑备份的时候，如果从主库的 binlog 传来一个DDL语句会怎么样？

备份顺序如下：

1.Q1:SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
2.Q2:START TRANSACTION WITH CONSISTENT SNAPSHOT；
3.Q3:SAVEPOINT sp;
4./* 时刻 1 */
5.Q4:show create table `t1`;
6./* 时刻 2 */
7.Q5:SELECT * FROM `t1`;
8./* 时刻 3 */
9.Q6:ROLLBACK TO SAVEPOINT sp;
10./* 时刻 4 */

答：(1).如果在Q4之前到达，那么拿到的是DDL后的表结构，没有影响。

(2).如果在"时刻2"到达，则表结构被改过，拿到的是之前的表结构，在Q5执行时候会报错，mysqldump终止。

(3).如果在"时刻2"和"时刻3"之间到达，mysqldump占用着MDL读锁，binlog堵塞，出现主从延迟，直到Q6完成。

(4)."时刻4"开始，mysqldump释放了MDL读锁，备份拿到的是DDL前的表结构，没有影响。

6.如果写入使用了change buffer机制，之后主机异常重启了，是否会丢失change buffer和数据

答：不会丢失，因为在事务提交时候，redo log中也写入了change buffer的更改操作，所以崩溃恢复时候可以用redo log来恢复。

7.如果设置的redo log太小，但是机器性能很高，会出现什么情况？

答：因为redo log设置太小，空间会很快占满，check point就要去推进，那么所有的系统不得不停止更新，去flush，现象就是磁盘压力不大，但是数据库总是会出现性能下降的情况。

8.如果有个页面经常要显示操作记录总数，如果统计是最快的呢？

答：1.用缓存系统保存记录

比如用Redis，插入一行计数加1，但是会存在Redis异常重启，每次重启时，从数据库里单独执行count(*)也是可以接受的，但是由于插入数据库和更改Redis的计数属于两步操作，如果中间时刻查询Redis计数时就会有可能出现数据不准确的现象，并发情况下不推荐。

2.用单独的计数表来统计

由于有redo log，所以不会出现异常重启后数据不一致的现象，而且由于mvcc的机制，导致其他线程看到的数据要么是已经提交的，要么是没有提交的，两张表符合一致性要求，不会出现数据不一致的现象。

这里最好是先插入数据后再更新计数表，因为计数表其他线程也要使用，所以要减少事务之间的锁等待，提高并发度。

9.count(*),count(id),count(字段),count(1)哪个更快？

答：对于server层来说，参数不为null，则累积值加一。

count(字段)：如果字段为not null，一行行从记录中读出这个字段，判断不能为null，按行累加，如果可以为null，那么就要从行中取出这个字段的值，判断是否为null，不是才会累加。

count(id)：InnoDB把每一行的id取出来，返回给server层，server层看到id不可能为空，就累加

count(1)：InnoDB遍历整张表，但不取值，server层对于每一行，放一个数字1，判断不可能为空，累加。

count()：mysql专门对count()做了优化，也不会把全部字段取出，也不去值，count(*)肯定不为null，累加。

这么看来count(*)≈count(1) > count(id)（需要取id）> count(字段)

10.什么时候使用alter table T engin=InnoDB时候，会让一个表反而空间变大？

答：这个表本身没有空洞，也就是结构是紧凑的，如果在重建表的过程中，因为是Online DDL，刚好有DML操作，那么可能会引入新的空洞

11.为什么 binlog cache 是每个线程自己维护的，而 redo log buffer 是全局共用的？

答：MySQL 这么设计的主要原因是，binlog 是不能“被打断的”。一个事务的 binlog 必须连续写，因此要整个事务完成后，再一起写到文件里。而 redo log 并没有这个要求，中间有生成的日志可以写到 redo log buffer 中。redo log buffer 中的内容还能“搭便车”，其他事务提交的时候可以被一起写到磁盘中。

12.事务执行期间，还没到提交阶段，如果发生 crash 的话，redo log 肯定丢了，这会不会导致主备不一致呢？

答：不会。因为这时候 binlog 也还在 binlog cache 里，没发给备库。crash 以后 redo log 和 binlog 都没有了，从业务角度看这个事务也没有提交，所以数据是一致的。

13.如果 binlog 写完盘以后发生 crash，这时候还没给客户端答复就重启了。等客户端再重连进来，发现事务已经提交成功了，这是不是 bug？

答：不是。你可以设想一下更极端的情况，整个事务都提交成功了，redo log commit 完成了，备库也收到 binlog 并执行了。但是主库和客户端网络断开了，导致事务成功的包返回不回去，这时候客户端也会收到“网络断开”的异常。这种也只能算是事务成功的，不能认为是 bug。实际上数据库的 crash-safe 保证的是：

(1).如果客户端收到事务成功的消息，事务就一定持久化了；

(2).如果客户端收到事务失败（比如主键冲突、回滚等）的消息，事务就一定失败了；

(3).如果客户端收到“执行异常”的消息，应用需要重连后通过查询当前状态来继续后续的逻辑。此时数据库只需要保证内部（数据和日志之间，主库和备库之间）一致就可以了。

14.什么情况下双M结构会出现循环复制？

答：(1).在一个主库更新事务后，用set global server_id=x命令修改了server id的值，再传回来就发现和自己的不同，就继续执行了。

(2).另一种是有三个节点，事务是在第一个节点执行的，server id是1，然后binlog传给节点2，然后2和3搭建了双M结构，这时候就会出现循环复制。

这时候可以停止备库，然后改变server id的值，再开启备库，之后这个节点收到的日志是自己的server id后，不再执行，这时候再把server id改回来。

15.什么情况下，备库的延迟会表现为一个45度的线段？

答：1.大事务，包括大表的DDL，一个事务操作很多行

2.备库起了一个长事务，然后不释放，那么主库对相同的表进行产生锁冲突的操作，那么binlog在备库应用时候就会被堵住

16.如果GTID模式下，新主库上，从库需要的binlog已经没了，怎么办？

答:(1)业务允许主从不一致的情况，可以在主库上执行show global variables like ‘gtid_purged’，得到主库已经删除的GTID集合，假设是 gtid_purged1；然后先在从库上执行 reset master，再执行 set global gtid_purged =‘gtid_purged1’；最后执行 start slave，就会从主库现存的 binlog 开始同步。binlog 缺失的那一部分，数据在从库上就可能会有丢失，造成主从不一致。

(2)如果主从数据一致的话，通过重新搭建从库来做

(3)如果其他从库保留有全量的binlog的话，可以把新的从库先接到这个保留了全量binlog的从库，追上日志以后，如果有需要再接回主库

(4)如果binlog有备份的情况，可以先在从库上应用缺失的binlog，再执行start slave

17.再GTID等位点的方案做读写分离，在对大表做DDL时候会怎么样：

答：假设，这条语句在主库要执行10分钟，提交后传到备库就要10分钟，那么在主库DDL之后再提交的事务GTID，就会等待10分钟，那么肯定会超时，被迫走主库。

18.使用Block Nested-Loop Join(BNL) 算法时，可能会对被驱动表做多次扫描。如果这个被驱动表是一个大的冷数据表，除了会导致 IO 压力大以外，还会对系统有什么影响呢？

答：由于被驱动表是个大的表，如果这个语句执行时间超过了1秒，那么就会进入到Buffer Pool的young区域，对于内存命中率有很大的影响，只能通过后续的查询来逐渐恢复内存命中率，为了避免这种情况，可以增大join_buffer_size的值，减少对被驱动表的扫描次数。

19.MySQL 解析 statement 格式的 binlog 的时候，对于 load data 命令，解析出来为什么用的是 load data local。

答：确保备库应用binlog正常，因为备库可能配置了secure_file_priv=null或者empty，如果不用local的话，很可能导致失败，造成同步延迟。