MySQL 知识总结

【01】基础架构

mysql分为两个部分[server层]和[存储引擎层]

server层：连接器，查询缓存，分析器，优化器，执行器等。

存储引擎层：数据的存储和提取，支持IsnnoDB(默认)，myISAM，Memory等。

连接器：负责和客户端连接，获取权限和维持管理连接。

查询缓存：执行查询请求先缓存中找相同sql语句的结果，版本8.0后删掉了此功能模块。

分析器：对sql语句做词法分析，语法校验等。

优化器：选择走哪个索引，多表join时选择各表连接顺序。

执行器：调引擎的取数据接口取一行，符合则放入结果集，调用多少次则引擎扫描多少行。

引擎：数据存储和提取，实现事务特性等。

【02】redolog/binlog，2PC以及buffer pool脏页落盘

数据插入/更新语句会涉及到 redolog(重做日志)和binlog(归档日志)，数据是先写日志，再写磁盘。

redolog(引擎层InnoDB特有模块)：

记录更新时[WAL机制]，InnoDB引擎将记录写redolog中，同时更新内存【数据页在buffer pool中直接更新，不在则将数据操作记录到change buffer中】，然后更新操作返回，注意是先更新内存再记录redo log。

引擎空闲时将redolog更新内存到buffer pool

此时内存和磁盘数据不一致内存中是脏页，需要将buffer pool的脏页更新到磁盘。

redolog文件大小是固定的，环型循环写，写满时会触发更新数据操作到磁盘以腾出空间，所以一般会设置比较大如4G。

redolog提供的crash-safe能力即数据库异常重启之前提交的记录不丢失。

redolog记录的物理日志(对数据库的某种修改操作)。

binlog(Server层特有模块)：

记录更新时同上，区别在于binlog是原始逻辑日志(对某行某字段的修改)，且是追加写入，binlog文件写到一定大小后切换到下一个文件。

内存部分的buffer pool和change buffer：

(1)当要更新数据页时，如果数据页不在缓存中，则会将【更新操作】记录到change buffer缓存中【buffer pool中分配】，而不是立马将数据页从磁盘载入缓存进行更新再刷回磁盘。

(2)这些操作会在两种情况下应用【merge】到目标数据页。

一是系统定期自动merge

二是该数据页正好被某个请求访问时，会加载到buffer pool内存中，再将 操作merge应用到数据页中

(3)这样操作缓存下来再异步执行，减少磁盘操作，又减少了buffer pool占用。

(4)merge操作的是内存中的数据页，此时属于脏页，由flush过程刷回磁盘。

change buffer适用场景：

由于change buffer在下一次merge之前缓存的操作越多，减少的读磁盘次数越多收益越大，所以适用于多写少读【因为数据不会马上访问则不会马上触发merge】。

如果数据更新之后马上被访问，change buffer还是白白占用内存去维护，起了副作用。

redo log和change buffer收益：

redo log是将随机写磁盘转为顺序写【依靠flush将一个脏数据页的数据操作组合成一次磁盘写操作】，优化了写IO。

change buffer是减少了读磁盘的次数【缓存操作异步执行】，优化了读IO。

buffer pool内存管理算法【LRU】：

LRU即淘汰最久未使用数据。innodb对LRU做了改进【因为全表扫描时旧数据页全部淘汰不在内存中io操作急剧增加，buffer pool内存命中率急剧下降】。

按照5:3的比例把整个LRU链表分成了young区域和old区域。图中LRU_old指向的就是old区域的第一个位置，是整个链表的5/8处。也就是说，靠近链表头部的5/8是young区域，靠近链表尾部的3/8是old区域。

二阶段提交说明

update  feild  from table  where  id=1;

更新流程：

(1)引擎查询1这一行，先找内存，该行所在数据页在内存则返回，否则找磁盘并载入内存

(2)引擎将改行数据修改得到新数据

(3)引擎记录redolog，此时redolog为prepare，引擎告知执行器处理完毕

(4)执行器记录binlog，调用引擎接口将redolog的prepare改为commit。

特点：

binlog记录数据原逻辑操作且追加写，用于数据库状态快照的恢复。误删某个表时也可以从binlog中找出数据库某状态时该表的状态然后读出来恢复到生产库中。

redolog也将循环写改为全部持久化到磁盘，需要设置innodb_flush_log_at_trx_commit=1。

数据库异常重启如何利用两个日志保证数据完整性/主备之间的数据一致性：

(1)如果redolog写完， 在prepare阶段之后、写binlog之前，发生了崩溃crash，由于此时binlog还没写，redo log也还没提交，所以崩溃恢复的时候，这个事务会回滚。

(2)如果binlog写完，redo log还没commit前发生crash，若redo log里面的事务是commit标识，则直接提交；则判断对应的事务binlog是否存在并完整：

(2)如果binlog写完，redo log还没commit前发生crash，若redo log里面的事务是prepare标识，则返回检查binlog，binlog完整则提交事务。

(2)如果binlog写完，redo log还没commit前发生crash，若redo log里面的事务是prepare标识，则返回检查binlog，binlog不完整则回滚事务。

可以发现异常重启后只需要检查两个日志的完整性就可以继续执行正常操作。

binlog完整性检查：

statement格式则最后会有一个commit

row格式则最后会有一个XID event

redolog和binlog如何关联起来：

有共同字段XID，重启后会顺序扫描redolog

扫描到有prepare和commit的redolog则直接提交

扫描到有prepare无commit的redolog则用XID去binlog里找对应的事务

为什么会有性能抖动：

主要因为运行过程中出发了脏页的flush。

(1)redolog写满，则要将redolog中操作落盘，即redolog->bufferpool->磁盘。注：此时所有更新操作都停止。

(2)redolog未写满，但是bufferpool内存不足，则需要将bufferpool中脏页罗盘，即bufferpool->磁盘。

(3)系统较为空闲，主动flush脏页。

(4)系统正常关闭时，主动flush脏页。

其中2/3/4都是常态，其中redolog设置太小会出现(1)表现为磁盘IO压力小但DB性能间歇下降。

DB运行过程中获取数据是从内存中读，如果内存中有直接返回如果没有则需要先磁盘载入内存，此时内存不足则需要淘汰脏页(淘汰前flush)会出现(2)，此时查询耗时也变长。

控制Innodb引擎的fulsh刷盘速度：

(1)设置innodb_io_capacity为磁盘的IOPS，即告知引擎的磁盘IO能力。

引擎会按照设置算出全力flush的速度并按照该速度执行，设置太小则引擎变懒flush脏页的速度比产生脏页的速度还慢。

(2)设置innodb_max_dirty_pages_pct默认值是75%，告知引擎脏页比例上限。

引擎不可能一直全力flush，而是在到达脏页比例时才全力flush脏页。

(3)innodb_flush_neighbors设置flush脏页时不同时刷隔壁脏页。

机械磁盘减少IO次数可以设置打开，SSD随机性能高则只刷自己会更快。

binlog写入到磁盘【持久化】机制：

事务执行过程中，日志先写入binlog cache，事务提交时将binlog cache写入binlog，一个事务不能被隔开即需要一次性写入。有参数binlog_cache_size控制内存占用的大小。

每个线程有自己的binlog cache公用同一个binlog文件，binlog日志是write写到文件系统的page cache中即未操作磁盘，速度会比较快。

binlog日志写完之后的数据落盘操作叫fsync。

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可设置为较大值提升性能，常规设置为100~1000中，只是需要考虑日志丢失数据恢复的影响。

redolog写入到磁盘【持久化】机制：

事务执行过程中，日志先写入redolog buffer，redolog有三种存在状态：

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

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

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

日志写到redo log buffer是很快的，wirte到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，也是可能已经持久化到磁盘的。下面两种情况就有这个现象:

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

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

binlog和redolog组合磁盘写入：

通常我们说MySQL“双1”配置，指的就是sync_binlog和innodb_flush_log_at_trx_commit都设置成 1。也就是说，一个事务完整提交前，需要等待两次刷盘，一次是redo log（prepare 阶段），一次是binlog。不过磁盘TPS不会是数据操作TPS的两倍，因为使用了“组提交”机制。

【03】事务与ACID

读未提交：事务还没提交时，它做的变更能被别的事务看到。【直接返回记录上的最新值】

读提交：事务提交之后，它做的变更才会被其他事务看到。【每个SQL语句开始执行的时候都创建一个视图】

可重复读：事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的。且当前事务未提交变更对其他事务也是不可见的。【事务启动时创建一个视图，整个事务存在期间都用这个视图】

串行化：对于同一行记录，写加写锁读加读锁，读写锁冲突的时候，后访问的事务必须等前一个事务执行完成才能继续执行。【用加锁的方式来避免并行访问】

为什么会出现“脏读”？因为没有“select”操作没有规矩。

为什么会出现“不可重复读”？因为“update”操作没有规矩。

为什么会出现“幻读”？因为“insert”和“delete”操作没有规矩。

“读未提（Read Uncommitted）”能预防啥？啥都预防不了。

“读提交（Read Committed）”能预防啥？使用“快照读（Snapshot Read）”，避免“脏读”，但是可能出现“不可重复读”和“幻读”。

“可重复读（Repeated Red）”能预防啥？使用“快照读（Snapshot Read）”，锁住被读取记录，避免出现“脏读”、“不可重复读”，但是可能出现“幻读”。

“串行化（Serializable）”能预防啥？排排坐，吃果果，有效避免“脏读”、“不可重复读”、“幻读”，不过效果谁用谁知道。

MVVC版本控制实现【可重复读】：

1改为2  ---->  2改为3  ---->  3改为4  ---->  当前值4

    |                       |                      |                       |

视图A(2)         视图B(3)          视图C(4)          视图D(4)     

上面不同时刻启动的事务在自己的视图里拿到的值只是一个历史版本，只是具体版本值需要由当前值执行逻辑回滚才能得到。  

MVVC的一致性视图：

(1)即我能看见什么数据，用于支持实现 读已提交 和 重复读 两种事物隔离级别。

(2)InnoDB中每个事务开始时申请一个唯一递增的ID(transactionId)，同时创建一个数组保存当前时刻的活跃事务ID集合(即那些启动了但是未提交的事务)。

(3)数据表中的每行记录，有多个版本(row)，每个版本有字段trx_id记录了事务ID。

(4)可重复读【实现方式】：

事务会沿着当前版本往前找，找到事务自身启动前的那一个版本或自身修改的那个版本。

(5)读已提交【实现方式】：

因为事务保存了当前未提交的活跃事务ID集合，事务会判断活跃事务ID集合中 min和max

一个数据版本的trx_id<min(说明该版本数据已经提交或者是当前事务自身修改的)，则数据该版本对当前事务可见

一个数据版本的trx_id>max(说明该版本数据在将来才会提交)，则数据该版本对当前事务不可见

一个数据版本的min<trx_id<max，且trx_id在数组中(说明该数据版本未提交)，则数据该版本对当前事务不可见

一个数据版本的min<trx_id<max，且trx_id不在数组中(说明该数据版本已提交)，则数据该版本对当前事务可见

【04】索引

哈希表：数组+链表。同HashMap，用key找value。仅适用于等值查询。

有序数组：数组。适用于等值查询和范围查询。插入慢。

搜索树：N叉数。如InnoDB的整数字段索引差不多1200叉树，

Innodb的索引使用B+树：

主键索引的叶子节点存的是整行数据。在InnoDB里，主键索引也被称为聚簇索引（clustered index）。

非主键索引的叶子节点内容是主键的值。在InnoDB里，非主键索引也被称为二级索引（secondary index）。

区别在于，使用普通索引只能找到主键，需要到主键索引树上找整行数据。称为“回表”。

 索引的维护：

数据保存在页上，页的目录就是索引，为了维护有序性，新插入数据会造成其他数据的移动，启动过程中数据页满了还需要申请新的数据页并挪动部分数据过去，索引性能受到影响。

所以索引因为数据的删除导致数据页由空洞，此时重建索引，把数据按顺序重新插入一边可以提高空间利用率。

索引覆盖：

假如一个组合索引 index(feild1,feild2,feild3)，即索引数叶子节点已经包含Id，feild1，feild2，feild3三个字段。如果以feild1为条件查询feild3，则不需要回主键索引树去找整行数据了。

即索引值已经覆盖了我们的查询需求了。

最左匹配原则：

最左匹配是针对联合索引来说的，为每一个查询去创建索引不至于，最左前缀可以是联合索引的最左N个字段，也可以是字符串索引的最左M个字符。

例如(feild1,feild2)的联合索引，查询条件where里面只要有feild1=xx则feild1和feild2都能用上索引。因为索引树以feild1顺序建立的，在每个相同的feild1下feild2也是有序的，

而从整个搜索树来看feild2是无序的。

索引下推：

在5.6版本之后推出以减少回表次数。假如一个组合索引 index(feild1,feild2,feild3)，在查询时使用最左匹配原则 where  field1=a  and  field2=b：

有索引下推：先通过feild1找到索引值，取出feild2判断是否符合条件。

无索引下推：先通过feild1找到索引值，从索引值取出id回表查找整条记录，取出feild2判断是否符合条件。

索引失效：

(1)对索引字段做了函数计算则索引失效，因为计算破环了索引值的有序性，如month(feild1)。

(2)对索引字段进行了隐式类型转换，也破坏了其有序性。如整形加引号变字符型。

普通索引和唯一索引的选择：

这其实是数据更新时的change buffer问题，由于change buffer会缓存数据操作并延后执行减少磁盘访问

唯一索引：更新的数据必须从磁盘载入内存，进行唯一性检查再执行更新并返回。

普通索引：更新的数据直接缓存操作到change buffer,然后直接返回。

所以更新操作在普通索引下会更快【数据页刚好不在内存中需要从磁盘载入内存】。

对于大数据量的表，普通索引配合change buffer的优化效果还是很明显。

优化器与索引基数与扫描行数：

(1)一个索引上不同值的个数称为【基数】，基数越大说明索引区分度越大索引收益越高。show index语句可以查询一个索引的基数。

(2)优化器会根据索引基数判断是否使用索引，mysql统计采样的方法得到索引基数。

(3)具体采样过程：

InnoDB选N个page取各自不同值取平均再乘page总数。当变更的数据行数超过1/M的时候，会自动触发重新做一次索引统计。

NM两个参数可以由innodb_stats_persistent指定，所以统计采样肯定是不准确的。

假设总共10w数据

A：selece  *  from  table  where  a  between  10000  and  20000            预计扫描行数：10w

B：selece  *  from  table  force  index(a) where  a  between  10000  and  20000            预计扫描行数：3w

影响优化器最终选择使用a索引或全表扫描的因素：

(1)预估扫描行数

(2)索引基数，

(3)普通索引需要回表的开销

(4)主键索引不需要回表

所以，A选择了扫描10w行数据走主键，虽然走索引a只扫描3w行但是回表的开销过大。

当然实际情况可能强制使用索引a会更快，因为优化器的代价估算最终选择也不是最优的。

引导索引选择：

(1)force  index(a)可以强制使用索引

(2)可以修改sql语句使索引生效：order  by  b  limit  1  ----->   order  by  a,b  limit  1

字符串添加索引，前缀索引：

(1)指定字符串字段前n个字符作为索引，则在索引树上会根据该字段前n个字符串建立索引。称为【前缀索引】。

(2)不指定字段长度，则默认整个字段作为索引。

(3)定义好长度，让索引即有区分度，又能节省空间。比如身份证倒序存储并定义前6位前缀索引。

(4)前缀索引会影响索引覆盖，因为引擎需要回表查询前缀索引字段的完整取值。

优化：

(1)组合索引时，用一个字段查询另外一个字段，或者用单个索引查询Id此时索引覆盖了，不用回表减少了树的搜索次数。

Multi-Range Read优化回表：

如果where条件是针对某个索引a的范围查询时，可以使用MRR优化。

回表是通过a索引找id然后取整行数据，但是随着a有序增大id会变成无序的，并出现随机访问【按照id】，虽然一次回表只能查询一条数据，但是调整顺序也能对回表进行优化。

MRR就是先按照a索引找出一堆id，将id放入read_rnd_buffer并排序，依次到主键id索引中查记录，并作为结果返回，转成顺序访问【磁盘磁头可以在当前位置接着往下找】。

read_rnd_buffer的大小是由read_rnd_buffer_size参数控制的，方案之后会先处理这一批id查询再去处理下一批。

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

【05】全局锁，表锁，行锁，幻读，加锁规则

MySQL InnoDB支持三种行锁定方式：

l   行锁（Record Lock）:锁直接加在索引记录上面，锁住的是key。

l   间隙锁（Gap Lock）:锁定索引记录间隙，确保索引记录的间隙不变。间隙锁是针对事务隔离级别为可重复读或以上级别而已的。

l   Next-Key Lock ：行锁和间隙锁组合起来就叫Next-Key Lock。

全局锁：

对整个数据库实例加锁，常用于全库逻辑备份。

表锁：

对整张表加锁。分两类。

一种是lock tables … read/write手动加锁并用unlock tables释放，限制其他线程进行读写。

一种是访问表时自动加上的MDL锁，对表CRUD时是读MDL锁【允许多线程同时CRUD表】，修改表结构是是写MDL锁【多线程不允许同时修改表结构】

Innodb行锁两阶段协议：

一个事务开始begin后，中间的sql语句需要时会自动加行锁(如update)，但是sql语句执行完并不会释放，会在事务提交commit后才释放。

死锁：

(1)等待，直到超时，超时时间由innodb_lock_wait_timeout设置。

(2)开启死锁检测，杀掉其中一个事务，innodb_deadlock_detect设置为on。但是每个被堵塞的请求线程都执行死锁检测很耗cpu，需要由业务放控制小数量的线程，这样小规模死锁检测成本很低。

行锁优化：

(1)如果一个事务中有很多sql语句，把可能有冲突的锁的sql语句往后放，使得加锁和释放过程更短。例如减少用户余额放在增加商户余额前面执行，因为后者锁冲突更大。

select  ...  locl in share mode：

共享锁(S锁, share locks)。其他事务可以读取数据，但不能对该数据进行修改，直到所有的共享锁被释放。

如果事务对某行数据加上共享锁之后，可进行读写操作；其他事务可以对该数据加共享锁，但不能加排他锁，且只能读数据，不能修改数据。

select  ...  for update：

排他锁(X锁, exclusive locks)。如果事务对数据加上排他锁之后，则其他事务不能对该数据加任何的锁。获取排他锁的事务既能读取数据，也能修改数据。

注：普通 select 语句默认不加锁，而CUD操作默认加排他锁。

当前读：

UPDATE和SELECT加锁，都是读取数据最新版本，不管是否可见。

(1)UPDATE语句都是在数据最新版本上修改，是先读出来，在set值进行修改。即当前读就是读取数据最新版本的意思。

(2)SELECT语句加锁时也是读取数据最新版本，加上lock in share mode 或 for update锁之后，也是当前读。

一致性读：

MVVC的可见性，只能读取可见版本的读叫做一致性读。

幻读与间隙锁

幻读以可重复读隔离级别为前提，间隙锁也只在可重复读级别下生效。

(1)幻读的定义：

幻读指的是一【个事务内】，【前后两次查询】同一个范围的时候，后一次查询看到了前一次查询没有看到的行，这一行来自于别人的【插入操作】。

(3)幻读的出现：

普通的查询都是快照读，是看不到别人的【插入操作】，也就不会出现幻读。

但是当前读【读取最新数据版本】能看见别人的新【插入操作】，就会出现幻读。

因为加不了锁，即便是被全表所有数据都上了锁，但是阻止不了新数据的插入，数据不存在也就不能加锁。

(4)解决方案：

使用间隙锁【GAP锁】：幻读出现是因为只能锁住数据行却锁不住数据间隙间的插入操作。插入6条数据就产生了7个间隙。

(5)间隙锁冲突：

间隙锁之间不冲突，间隙锁只与插入操作冲突。

如果一个事务是开启间隙锁之后插入数据，这个事务并发就会形成死锁。

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;

这个事务在办法时会有冲突形成死锁：sessionA间隙锁锁住了sessionB的insert操作，sessionB间隙锁锁住了sessionA的insert操作。

mysql加锁规则：

【两个“原则”】

(1)加锁的基本单位是next-key lock。next-key lock是按索引顺序前开后闭区间。

(2)查找过程中访问到的对象才会加锁。

【两个“优化”】

(3)索引上的等值查询，给唯一索引加锁的时候，next-key lock退化为行锁，或者向右遍历时且最后一个值不满足等值条件的时候，next-key lock退化为间隙锁。

【一个“bug”】

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

加锁示例分析：

表t中，id为主键索引，c为普通索引。t(id,c,d)

插入数据：

(0,0,0)

(5,5,5)

(10,10,10)

(15,15,15)

(20,20,20)

(25,25,25)

(1) 【等值查询间隙锁】

sessionA：update t set d=d+1 where id =7;

按照next-key锁，因为没有id=7的记录会访问两边的数据对象，加锁范围(5,10]，由条件id=7是索引上的等值查询，从5向10遍历，10不符合id=7，则退化为间隙锁即(5,10)之间的间隙锁。

由于间隙锁仅对插入操作生效，所以插入id在(5,10)之间的数据会失败，但是修改id为5和10的数据没问题。

(2) 【非唯一索引等值锁】

sessionA：select id from t where c=5 lock in share mode;  

sessionB：update t set d=d+1 where id=5;

sessionA按照next-key锁，因为有c=5的记录，加锁范围(0,5]，c是普通索引会访问到下一个不满足条件的索引值即10才停止，查找过程中访问到的对象都要加锁，加锁范围要添加新的(5,10]

索引范围变成(0,5]，(5,10]，又因为c=5是索引上等值查询，且10不满足c=5，退化成(5,10)的间隙锁。

因为仅给访问的对象加锁，这里只给c的索引树(5,10)之间加了间隙锁，这个查询又是使用的索引覆盖，不会回表也不会访问id索引树，所以id索引树并未被加锁。

sessionB的update语句访问的是id和d，此时id=5也能修改成功。

当然如果sessionA是select id变成select d则索引无法覆盖，回表需要访问id索引树则sessionB也会执行失败。

如果

sessionA：select id from t where c=5 for update;  

sessionB：update t set d=d+1 where id=5;

这里和上面唯一区别是lock in share mode换成了for update，情况就不一样了。

sessionA的for update按照语义会认为加锁是想更新数据，会同时给主键id刚好满足where条件的行加上行锁，这一行id=5，则sessionB会执行失败。

(3)【主键索引范围锁】

sessionA： select * from t where id=10 for update;

sessionB： select * from t where id>=10 and id<11 for update;

这两条语句的逻辑等价，但是加锁有区别。 

seesionA按照netx-lock，加锁范围(5,10]，主键id的等值where条件则退化为行锁，只锁id=10这一行，继续向下找到id=15，并加了netx-lock锁(10,15]

这个时候想要插入或者修改主键id=10以及(10,15]之间的数据行会被锁住

sessionB按照next-lock，加锁范围

(4)【非唯一索引范围锁】

(5)【唯一索引范围锁bug】

(6)【非唯一索引"等值"】

(7)【in的等值查询】

sessionA：select id from t where c in(5,20,10) lock in share mode;

explain可看出这条in语句使用了索引c并且rows=3，说明这三个值都是通过B+树搜索定位的。

在查找c=5的时候，先锁住了(0,5]。但是因为c不是唯一索引，为了确认还有没有别的记录c=5，就要向右遍历，找到c=10才确认没有了，这个过程满足优化2，所以加了间隙锁(5,10)。

同样的，执行c=10这个逻辑的时候，加锁的范围是(5,10] 和 (10,15)；执行c=20这个逻辑的时候，加锁的范围是(15,20] 和 (20,25)。这条语句在索引c上加的三个记录锁的顺序是：先加c=5的记录锁，再加c=10的记录锁，最后加c=20的记录锁。因为我要跟你强调这个过程：这些锁是“在执行过程中一个一个加的”，而不是一次性加上去的。

InnoDB行锁实现方式：

InnoDB行锁是通过给索引上的索引项加锁来实现的，只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁

1.在不通过索引条件查询的时候，InnoDB确实使用的是表锁

2.由于MySQL的行锁是针对索引加的锁，不是针对记录加的锁，所以虽然是访问不同行的记录，但是如果是使用相同的索引键，是会出现锁冲突的。

3.当表有多个索引的时候，不同的事务可以使用不同的索引锁定不同的行，另外，不论是使用主键索引、唯一索引或普通索引，InnoDB都会使用行锁来对数据加锁。

4.即便在条件中使用了索引字段，但是否使用索引来检索数据是由MySQL通过判断不同执行计划的代价来决定的，如果MySQL认为全表扫描效率更高，比如对一些很小的表，它就不会使用索引，这种情况下InnoDB将使用表锁，而不是行锁。因此，在分析锁冲突时，别忘了检查SQL的执行计划，以确认是否真正使用了索引。

InnoDB行锁优化建议：

1.要想合理利用InnoDB的行级锁定，做到扬长避短，我们必须做好以下工作：

(a)尽可能让所有的数据检索都通过索引来完成，从而避免InnoDB因为无法通过索引键加锁而升级为表级锁定；

(b)合理设计索引，让InnoDB在索引键上面加锁的时候尽可能准确，尽可能的缩小锁定范围，避免造成不必要的锁定而影响其他Query的执行；

(c)尽可能减少基于范围的数据检索过滤条件，避免因为间隙锁带来的负面影响而锁定了不该锁定的记录；

(d)尽量控制事务的大小，减少锁定的资源量和锁定时间长度；

(e)在业务环境允许的情况下，尽量使用较低级别的事务隔离，以减少MySQL因为实现事务隔离级别所带来的附加成本。

2.由于InnoDB的行级锁定和事务性，所以肯定会产生死锁，下面是一些比较常用的减少死锁产生概率的小建议：

(a)类似业务模块中，尽可能按照相同的访问顺序来访问，防止产生死锁；

((b)在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁产生概率；

c)对于非常容易产生死锁的业务部分，可以尝试使用升级锁定颗粒度，通过表级锁定来减少死锁产生的概率。

3.可以通过检查InnoDB_row_lock状态变量来分析系统上的行锁的争夺情况：

【6】表空间

表存储：

(1)表由结构定义和数据组成

(2)innodb_file_per_table=off设置表放在系统共享表空间里面。drop table删表后空间没有被回收

(2)innodb_file_per_table=on设置表放在单独的.ibd文件中。drop table删表后会直接删除这个文件。推荐设置为on。

复用：

(1)innodb数据按照B+树来构建，然后按照顺序将数据插入page中，比如某个page保存id从400到600

(2)数据行复用：

数据行id=500被删除，则该行数据所在page将该位置标记为可复用，后续id在400到600的数据可能会复用该位置。

(3)数据页复用：

如果相邻两个page利用率低，则会全部移动到一个page上，而另外一个page标记为可复用。

(4)标记为可复用，但是磁盘空间并没有被回收。

数据页空洞：

(1)删除的数据行，该位置标记为可复用，这时是空洞的。

(2)顺序插入的数据，会紧密插入到page上然后申请下一个page。而随机插入的数据会造成page分裂，但page利用率都很低。

(3)所以通过大量的增删改的表，会存在很多空洞，想要收缩表空间只能重建表。

重建表：

(1)新建一个与表A结构相同的表B，然后按照主键ID递增的顺序，把数据一行一行地从表A里读出来再插入到表B中。

显然地，表B的主键索引更紧凑，数据页的利用率也更高。用表B替换A，从效果上看，就起到了收缩表A空间的作用。

(2)alter table A engine=InnoDB

(3)alter table t engine = InnoDB（也就是recreate）默认的就是上面图4的流程了；

analyze table t 其实不是重建表，只是对表的索引信息做重新统计，没有修改数据，这个过程中加了MDL读锁；

optimize table t 等于recreate+analyze。

【7】内存临时表/磁盘临时表

临时表：

(1)内存临时表：表在内存里，数据在内存里。

(2)磁盘临时表：表在磁盘里，数据在内存里。

(3)磁盘临时表只是内存临时的一个替代品。这就好像操作系的虚拟内存一样。当内存不够用时，可以在硬盘上的一个空间作为其替代品

(4)执行计划中包含有“Using temporary”时，会使用到临时表

内存表：

指的是使用Memory引擎的表，建表语法是create table … engine=memory。这种表的数据都保存在内存里，系统重启的时候会被清空，但是表结构还在。

临时表：

可以使用各种引擎类型 。如果是使用InnoDB引擎或者MyISAM引擎的临时表，写数据的时候是写到磁盘上的。当然，临时表也可以使用Memory引擎。

临时表特点：

1. 建表语法是create temporary table

2. 一个临时表只能被创建它的session访问，对其他线程不可见。所以，图中session A创建的临时表t，对于session B就是不可见的。

3. 临时表可以与普通表同名。

4. session A内有同名的临时表和普通表的时候，show create语句，以及增删改查语句访问的是临时表。

5. show tables命令不显示临时表。

6. 这个session结束的时候，会自动删除临时表

union使用临时表过程：

(select 1000 as f) union (select id from t1 order by id desc limit 2);

这个语句的执行流程是这样的：

1. 创建一个内存临时表，这个临时表只有一个整型字段f，并且f是主键字段。

2. 执行第一个子查询，得到1000这个值，并存入临时表中。

3. 执行第二个子查询：

    * 拿到第一行id=1000，试图插入临时表中。但由于1000这个值已经存在于临时表了，违反了唯一性约束，所以插入失败，然后继续执行；

    * 取到第二行id=999，插入临时表成功。

4. 从临时表中按行取出数据，返回结果，并删除临时表，结果中包含两行数据分别是1000和999。

里的内存临时表起到了暂存数据的作用，而且计算过程还用上了临时表主键id的唯一性约束，实现了union的语义。如果把上面这个语句中的union改成union all的话，就没有了“去重”的语义。这样执行的时候，就依次执行子查询，得到的结果直接作为结果集的一部分，发给客户端。因此也就不需要临时表了。

group by会使用到临时表：

执行select id%10 as m, count(*) as c from t1 group by m order by null;

这条语句能使用到a的索引覆盖不需要回表，这个语句的执行流程是这样的：

1. 创建内存临时表，表里有两个字段m和c，主键是m；用于计算group by结果；

2. 扫描表t1的索引a，依次取出叶子节点上的id值，计算id%10的结果，记为x；

    * 如果临时表中没有主键为x的行，就插入一个记录(x,1);

    * 如果临时表中有主键为x的行，就将x这一行的c值加1；

3. 遍历完成后，再根据字段m做排序，得到结果集返回给客户端。

MySQL什么时候会使用内部临时表？

临时表什么情况会被使用：

排序时使用sort buffer，join查询时使用join buffer。

union【两条select取并集】时会使用内部临时表

group by【分组】时会使用临时表

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

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

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

内存表的数据组织形式：

【8】count(*)处理

COUNT(*)处理方式：

(1)myIsam保存了表的总行数，但是如果带有where条件的话这个值也用不上了。

(2)innoDB需要一行一行从引擎读取出来再计数。因为基于MVVC的多版本并发方式，每一行都要判断一下对自己是否可见是否符合count条件，所以很慢。

(3)innoDB遍历索引树找最小索引数【一般是主键索引】遍历并计数。

(4)一般基于MVVC的数据库并发策略，count(*)一般是业务系统自己用缓存来实现，但是缓存不准确，最正确的做法是利用数据库事务将count(*)计数保存在数据库。根据业务来自行实现数据库层的count(*)比较合适。

【9】order  by 处理

全字段排序：

(1)排序是使用内存sort buffer，这个是专门用于排序的内存空间【内部排序】，如果排序数据量太大则需要额外申请磁盘临时空间辅助排序【外部排序】。

(2)sort  buffer的大小由sort_buffer_size参数控制。

(3)首先按照where条件走字段索引扫描(需要回表)或主键索引全表扫描，把查询到的每行数据放入sort buffer内存，如果sort buffer不够则需要开辟磁盘空间。

(4)然后在sort buffer或磁盘空间中，将数据按照排序字段进行排序。

(5)全字段排序，是吧说句整行载入后进行排序，所有字段都被加载。

排序算法：

(1)临时文件归并排序算法

(2)优先队列排序算法

是否用到磁盘临时空间辅助排序：

(1)查看方法

/* 打开optimizer_trace，只对本线程有效 */

SET optimizer_trace='enabled=on';

/* @a保存Innodb_rows_read的初始值 */

select VARIABLE_VALUE into @a from performance_schema.session_status where variable_name = 'Innodb_rows_read';

/* 执行语句 */

select city, name,age from t where city='杭州' order by name limit 1000;

/* 查看 OPTIMIZER_TRACE 输出 */

SELECT * FROM `information_schema`.`OPTIMIZER_TRACE`\G

/* @b保存Innodb_rows_read的当前值 */

select VARIABLE_VALUE into @b from performance_schema.session_status where variable_name = 'Innodb_rows_read';

/* 计算Innodb_rows_read差值 */

select @b-@a;

(2)分析结果

number_of_tmp_files：12

说明本次排序使用了12个临时文件【归并排序算法】

行ID排序：

(1)全字段把整行数据载入，mysql认为单行数据字段太多太长会使用行ID排序。

(2)单行字段长度之和超过阈值，mysql就会启用行ID排序，这个阈值由max_length_for_sort_data参数设置。

(3)行ID排序只将主键id和排序字段加载到sort buffer，排序之后再将id回表找整条数据，载入内存之后再返回。

(4)相比之下多了第二次的回表访问磁盘的操作，但是单行较小一次性可以排更多行排序会更快。

全字段排序和行ID排序比较：

(1)如果MySQL担心排序内存太小会影响排序效率才会采用rowid排序算法，这样排序过程中一次可以排序更多行但是需要再回到原表去取数据。

(2)如果MySQL认为内存足够大会优先选择全字段排序，把需要的字段都放到sort_buffer中这样排序后就会直接从内存里面返回查询结果了不用再回到原表去取数据。

(3)设计思想：如果内存够，就要多利用内存，尽量减少磁盘访问。

无临时表的排序：

(1)全字段排序和行ID排序都需要建立临时表再进行排序，如果加载入排序空间的数据直接就是有序的就没有建立临时表和排序的过程了。

(2)数据从where条件查询的行本身就按照order by条件排序时，是最好的优化：

如果有(city,name)索引

select city,name,age from t where city='杭州' order by name limit 1000 ;

按照组合索引最左匹配可知，从city索引出来的数据【city索引然后回表】都是按照name排序的，可以直接从内存返回而无需排序。

如果有(city,name,age)索引，则利用索引覆盖则【回表】过程也省掉了。

(3)数据从where条件查询的行并未按照order by条件排序时，也是需要在排序空间排序的：

select * from t where city in (“杭州”," 苏州 ") order by name limit 100：

这条语句按照索引最左匹配得到的数据行在各自的city下name有序，但是在两个city组合下name无序，也是需要排序的，可以在业务层优化：

各自执行后得到200条数据，然后在业务层排序后取前100。

select * from t where city=“xx” order by name limit 100

临时表中的排序：

(1)对于内存临时表，行ID排序相比全字段排序的优势不明显，因为内存临时表回表也只是在内存中访问整条数据，不涉及磁盘操作。

(2)内存临时表也有大小，由tmp_table_size设置，如果该值设置小的话内存临时表会转为磁盘临时表。

【10】保证主备一致

binlog可以用来归档，也可以用来做主备同步，而它的几乎所有的高可用架构，都直接依赖于binlog。

主备切换：

在状态1中，客户端的读写都直接访问节点A，而节点B是A的备库，只是将A的更新都同步过来，到本地执行。这样可以保持节点B和A的数据是相同的。

当需要切换的时候，就切成状态2。这时候客户端读写访问的都是节点B，而节点A是B的备库。

在状态1中，虽然节点B没有被直接访问，但是我依然建议你把节点B（也就是备库）设置成只读（readonly）模式。这样做，有以下几个考虑：

1. 有时候一些运营类的查询语句会被放到备库上去查，设置为只读可以防止误操作；

2. 防止切换逻辑有bug，比如切换过程中出现双写，造成主备不一致；

3. 可以用readonly状态，来判断节点的角色。

事务日志同步：

备库B跟主库A之间维持了一个长连接。主库A内部有一个线程，专门用于服务备库B的这个长连接。一个事务日志同步的完整过程是这样的：

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

2. 在备库B上执行start slave命令，这时候备库会启动两个线程，就是图中的io_thread和sql_thread。其中io_thread负责与主库建立连接。

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

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

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

binlog三种格式：

一种是statement，一种是row。可能你在其他资料上还会看到有第三种格式，叫作mixed，其实它就是前两种格式的混合。

(1)当binlog_format=statement时，binlog里面记录的就是SQL语句的原文

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

运行这条delete命令产生了一个warning，原因是当前binlog设置的是statement格式，并且语句中有limit，所以这个命令可能是unsafe的

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

(2)格式改为binlog_format=‘row’时，binlog记录两个event：Table_map和Delete_rows

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

Delete_rows event，用于定义删除的行为。

里面记录了真实删除行的主键id，这样binlog传到备库去的时候，就肯定会删除id=4的行，不会有主备删除不同行的问题。

(3)为什么会有mixed格式的binlog？

有些statement格式的binlog可能会导致主备不一致，所以要使用row格式。的缺点是，很占空间。比如你用一个delete语句删掉10万行数据，用statement的话就是一个SQL语句被记录到binlog中，占用几十个字节的空间。但如果用row格式的binlog，就要把这10万条记录都写到binlog中。

所以折中方案，也就是有了mixed格式的binlog。mixed格式的意思是，MySQL自己会判断这条SQL语句是否可能引起主备不一致，如果有可能，就用row格式，否则就用statement格式。

也就是说，mixed格式可以利用statment格式的优点，同时又避免了数据不一致的风险。

row格式的binlog用于恢复数据：

即使我执行的是delete语句，row格式的binlog也会把被删掉的行的整行信息保存起来。

所以，如果你在执行完一条delete语句以后，发现删错数据了，可以直接把binlog中记录的delete语句转成insert，把被错删的数据插入回去就可以恢复了。

如果你是执行错了insert语句呢？那就更直接了。

row格式下，insert语句的binlog里会记录所有的字段信息，这些信息可以用来精确定位刚刚被插入的那一行。这时，你直接把insert语句转成delete语句，删除掉这被误插入的一行数据就可以了。

如果执行的是update语句的话，binlog里面会记录修改前整行的数据和修改后的整行数据。

所以，如果你误执行了update语句的话，只需要把这个event前后的两行信息对调一下，再去数据库里面执行，就能恢复这个更新操作了。

其实，由delete、insert或者update语句导致的数据操作错误，需要恢复到操作之前状态的情况，也时有发生。

MariaDB的Flashback工具就是基于上面介绍的原理来回滚数据的。

【11】主备延迟和并行复制

主备延迟：

备库执行一些大量查询，或备库机器性能和主库差太多就不能及时消费主库的binlog文件。

主库要在事务执行完才会将binlog发出去，如果事务执行了十分钟(大事务，比如大量delete或者大表的ddl)提交后发出binlog文件，备库就延迟比较大。

seconds_behind_master：

这个值用show slave status命令查询到，该值代表slave落后master的秒数【SQL thread 和I/O thread之间的差值】。

sql thread用于备库执行日志，io thread用于主库写入数据。

MariaDB主备并行复制：

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

2. commit_id直接写到binlog里面；

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

4. 这一组全部执行完成后，coordinator再去取下一批。

总结：这个策略出来的时候是相当惊艳的。因为，之前业界的思路都是在“分析binlog，并拆分到worker”上。而MariaDB的这个策略，目标是“模拟主库的并行模式”。

并行复制需要考虑的问题：

1.不能有更新覆盖，用一行的两事务需要按顺序执行，所以只能分到同一个worker中

2.同一个事务不能拆开，也只能分发到统一的个worker中

按表分发：

如果两个事务更新不同的表，则可以并行执行，可以分发到两个worker中。worker内部维护一个hash表，键为 "database.table"表示某一张表，值为"n"表示当前worker执行队列中有多少个事务修改这个表。事务分发时：

冲突检测，如果待分发事务是修改表t1，某个worker执行队列中有事务执行任务也是修改表t1，则判定为冲突。

如果待分发事务与所有worker都不冲突则随意分发给某个worker

如果待分发事务与1个worker冲突，则将该事务分发给这个worker塞入执行队列，排队执行

如果待分发事务与n个worker冲突，则分发线程阻塞直到n降为1，后续按照1个worker冲突的方式处理

按行分发：

worker内部维护一个hash表，键为 "库名+表名+索引a的名字+a的值"，值为"n"表示当前worker执行队列中有多少个事务修改这一行。事务分发时：

因此，coordinator在解析这个语句的binlog的时候，这个事务的hash表就有三个项:

1. key=hash_func(db1+t1+“PRIMARY”+2), value=2; 这里value=2是因为修改前后的行id值不变，出现了两次。

2. key=hash_func(db1+t1+“a”+2), value=1，表示会影响到这个表a=2的行。

3. key=hash_func(db1+t1+“a”+1), value=1，表示会影响到这个表a=1的行。

该策略要求

1. 要能够从binlog里面解析出表名、主键值和唯一索引的值。也就是说，主库的binlog格式必须是row；

2. 表必须有主键；

3. 不能有外键。表上如果有外键，级联更新的行不会记录在binlog中，这样冲突检测就不准确。

缺点：

1. 耗费内存。比如一个语句要删除100万行数据，这时候hash表就要记录100万个项。

2. 耗费CPU。解析binlog，然后计算hash值，对于大事务，这个成本还是很高的。

我在实现这个策略的时候会设置一个阈值，单个事务如果超过设置的行数阈值（比如，如果单个事务更新的行数超过10万行），就暂时退化为单线程模式。

并行的的事务的binlog不能并行复制：

因为，可能有由于锁冲突而处于锁等待状态的事务。如果这些事务在备库上被分配到不同的worker，执行顺序不一样就会出现备库跟主库不一致的情况。

MariaDB策略的核心，是“所有处于commit”状态的事务可以并行。事务处于commit状态，表示已经通过了锁冲突的检验了。

InnoDB能够达到redo log prepare阶段，就表示事务已经通过锁冲突的检验了，因此mysql5.7并行复制策略是：

1. 同时处于prepare状态的事务，在备库执行时是可以并行的；

2. 处于prepare状态的事务，与处于commit状态的事务之间，在备库执行时也是可以并行的。

过期读：

由于master和slave之间的主备延迟，如果向master插入的数据立即向slave发起查询可能会查询不到，这个现象叫做过期读。

过期读解决方案：

1.强制走master读方案

2.sleep方案

(a)曲线救国异步ajax加载

3.判断主备无延迟方案

(a)show slave status结果里的seconds_behind_master参数的值可以用来衡量主备延迟时间的长短，0说明无延迟

(b)【读到的主库的最新位点】master_log_file和relay_master_log_file、【备库执行的最新位点】read_master_log_pos和exec_master_log_pos这两组值完全相同，就表示接收到的日志已经同步完成

(c)对比GTID集合确保主备无延迟，* Auto_Position=1 ，表示这对主备关系使用了GTID协议。* Retrieved_Gtid_Set，是备库收到的所有日志的GTID集合；* Executed_Gtid_Set，是备库所有已经执行完成的GTID集合。

4.配合semi-sync方案

semi-sync做了这样的设计：事务提交的时候，主库把binlog发给从库； 从库收到binlog以后，发回给主库一个ack，表示收到了； 主库收到这个ack以后，才能给客户端返回“事务完成”的确认。也就是说，如果启用了semi-sync，就表示所有给客户端发送过确认的事务，都确保了备库已经收到了这个日志。

5.等主库位点方案

执行select master_pos_wait(file, pos[, timeout]);这个命令正常返回的结果是一个正整数M，表示从命令开始执行，到应用完file和pos表示的binlog位置，执行了多少事务。

6.等GTID方案

select wait_for_executed_gtid_set(gtid_set, 1);1. 等待，直到这个库执行的事务中包含传入的gtid_set，返回0；2. 超时返回1。从备库查询前执行等待，如果等待超时还没查到则从主库去查询。

【12】主备高可用策略

主备切换【可靠性优先】策略：

1. 判断备库B现在的seconds_behind_master，如果小于某个值（比如5秒）继续下一步，否则持续重试这一步；

2. 把主库A改成只读状态，即把readonly设置为true；

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

4. 把备库B改成可读写状态，也就是把readonly 设置为false；

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

总结：即先保证数据一致性，中间AB都处于只读时，有一段时间不可写。

主备切换【可用性优先】策略：

如果我强行把步骤4、5调整到最开始执行，也就是说不等主备数据同步，直接把连接切到备库B，并且让备库B可以读写，那么系统几乎就没有不可用时间了。

只是数据一致性会被破环。

用row格式的binlog时，数据不一致的问题更容易被发现。而使用mixed或者statement格式的binlog时，数据很可能悄悄地就不一致了。

异步复制：

在主库异常掉电的时候可能会丢数据。这个大家知道以后，有一些就改成semi-sync【半同步】了，但是还是有一些就留着异步复制的模式，因为semi-sync有性能影响（一开始35%，现在好点15%左右，看具体环境），而可能这些业务认为丢一两行，可以从应用层日志去补。 就保留了异步复制模式。

半同步复制：

MySQL的半同步复制-semisync是基于默认的异步复制和完全同步复制之间。

它是在master在执行完客户端提交的事务后不是立刻返回给客户端，而是等待至少一个slave收到并写到relay log中才返回给客户端。

【13】主备切换

主备切换的产生：

A和A'互为主备【即双M结构】，BCD等为从。如果旧A断电，A'变为新主，BCD需要和A'建立链接并从A'上拿binlog进行执行，保持最终一致。

主备切换关注的问题：

主要是slave需要链接新的master，然后保持和新master日志同步，则需要判断同步位点。

在一主一备的双M架构里，主备切换只需要把客户端流量切到备库；而在一主多从架构里，主备切换除了要把客户端流量切到备库外，还需要把从库接到新主库上。

基于位点的主备切换：

1.slave链接新的master，执行change master命令，需要计算同步位点(主库的master_log_name文件的master_log_pos这个偏移量)。

2.同步位点只能“稍微往前寻找”，然后跳过已经执行的事务。

3.设置主动跳过错误1032【insert时主键冲突】和1062【delete时数据不存在】，切换完成后slave_skip_errors再修改回去。

GTID的主备切换：

通过sql_slave_skip_counter跳过事务和通过slave_skip_errors忽略错误的方法，虽然都最终可以建立从库B和新主库A’的主备关系，但这两种操作都很复杂，而且容易出错。GTID彻底解决。

1.GTID两部分组成，GTID=source_id【实例Id】:transaction_id【事务提交时分配的】

2.这里transaction_id不同于事务id【是在新建事务时分配的，即便该事务回滚，下一个事务id也会递增】

3.每个mysql实例都有个GTID集合，保存了当前实例执行过的所有事务

4.新master的GTID集合，和其他slave的GTID，会有一个差集，差集不为空说明slave有需要同步的事务，差集为空说明slave和新master已经保持一致了

5.判断新master本地，差集GTID对应的所有binlog事务是否齐全。

6.如果齐全则从最早的一个GTID开始取出binlog发给slave去执行，且这个点位往后就是正常的执行流程了。

7.如果不齐全则说明新master缺了slave需要的binlog，则报错直接返回。

GTID主备切换时GTID差集中binlog丢失解决：

1. 如果业务允许主从不一致的情况，那么可以在主库上先执行show global var iables 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。

主备异常检查：

0.默认的基本建成：

select 1语句，但是这个语句只能检查server进程是否还在，目前广泛使用的MHA就是默认这种方式

1.外部监查：

建立一个health检查专用表，保存server_id和time_stemp，然后定期执行updat操作通过响应/超时时间来判断，但是这个不准确，因为sql执行请求随机性太大【cpu和io调度分配】，有可能业务请求执行得很慢了而health请求执行很快。

2.内部检查：

ySQL 5.6版本以后提供的performance_schema库，就在file_summary_by_event_name表里统计了每次IO请求的时间。执行下面语句查询是否有响应时间超过200ms的sql请求：

select event_name,MAX_TIMER_WAIT FROM performance_schema.file_summary_by_event_name where event_name in ('wait/io/file/innodb/innodb_log_file','wait/io/file/sql/binlog') and MAX_TIMER_WAIT>200*1000000000;

【14】备份与恢复

误删数据情况分类：

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

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

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

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

delete语句删除行的恢复：

可以用Flashback工具通过闪回把数据恢复回来。Flashback恢复数据的原理，是修改binlog的内容，拿回原库重放。而能够使用这个方案的前提是，需要确保binlog_format=row 和binlog_row_image=FULL。

通常是备份一个临时库，在临时库上恢复，再将回复的数据应用到正式库上

误删表/库的恢复：

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

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

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

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

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

延迟备份防止误删表/库：

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

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

这时候到这个备库上执行stop slave，再通过之前介绍的方法，跳过误操作命令，就可以恢复出需要的数据。

rm删除实例的恢复：

这种情况只要集群还在，HA系统就会开始工作，选出一个新的主库，从而保证整个集群的正常工作。这时，你要做的就是在这个节点上把数据恢复回来，再接入整个集群。

【15】kill连接和查询

mySQL中有两个kill命令：

1.kill query +线程id，表示终止这个线程中正在执行的语句；

2.kill connection +线程id，这里connection可缺省，表示断开这个线程的连接，当然如果这个线程有语句正在执行，也是要先停止正在执行的语句的。

session处于锁等待时，执行kill有效：

1.当用户执行kill query thread_id_B时，MySQL里处理kill命令的线程做了两件事：

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

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

4.session收到信号退出锁等待，处理session的THD::KILL_QUERY状态

【16】全表扫描

全表扫描对server层的影响：

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

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

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

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

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

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

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

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

全表扫描对innode引擎层的影响：

引擎获取数据主要和内存命中率有关，页page会保存在Buffer Pool中，对查询的加速效果，依赖于一个重要的指标，即：内存命中率。

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

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

show processlist：

如果客户端在发出全表扫描请求后，不去读取socket receive buffer中的内容【即客户端接收的很慢】，请求的state状态为Sending to client，会导致mysql服务端由于结果发不出去，事务执行时间变长。为了解决这个问题，客户端在请求时设置-quick【使用mysql_usr_result】即读一行处理一行，这个在返回数据量不大时建议设置。

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

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

【17】join过程

t1【100条数据】和t2【1000条数据】表结构完全相同，a上均有索引

NLJ算法：

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

这个过程是先遍历表t1【全表扫描】，然后根据从表t1中取出的每行数据中的a值，去表t2中查找满足条件的记录【走树搜索】。在形式上，这个过程就跟我们写程序时的嵌套查询类似，并且可以用上被驱动表的索引，所以我们称之为“Index Nested-Loop Join”，简称NLJ。【straight_join优化器按照指定的方式join】

在这个join语句执行过程中，驱动表是走全表扫描，而被驱动表是走树搜索。

扫描行数100【t1全表】+100【t2.a索引】一共200行

BKA算法：

按照MRR的性能提升原理，mysql推出了BKA算法，是对NLJ算法的优化。

NLJ算法执行的逻辑是：从驱动表t1，一行行地取出a的值，再到被驱动表t2去做join。BKA将取出来的驱动表t1的值缓存到join buffer，然后再批量传给被驱动表t2做join。

如果要使用BKA优化算法的话，你需要在执行SQL语句之前，先设置set optimizer_switch='mrr=on,mrr_cost_based=off,batched_key_access=on';因为BKA算法依赖MRR。

BNL算法：

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

由于b字段没有索引，全表扫描t1，每条结果再全表扫描一次t2进行匹配，如果走NLJ会扫描100*1000行，而是选择Block Nested-Loop Join方式：

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

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

区别就在于，将小表载入内存进行对比，

扫描行数100【t1全表】+1000【t2全表】一共1100行

判断次数100【t1全表】* 1000【t2全表】一共100000次

join_buffer的大小是由参数join_buffer_size设定的，默认值是256k。如果放不下表t1的所有数据话，策略很简单，就是分段放。

这个流程才体现出了这个算法名字中“Block”的由来，表示“分块去join”。join_buffer_size越大，一次可以放入的行越多，分成的段数也就越少，对被驱动表的全表扫描次数就越少。

BNL算法的性能问题：

BNL可能会导致Buffer Pool的热数据被淘汰，影响内存命中率。【大表IO影响在查询结束之后就没有了但是对buffer pool的影响时持续，后续buffer pool的内存命中率只能慢慢提升】

1.InnoDB对Bufffer Pool的LRU做了优化，一个正常访问的数据页，需要隔1秒后再次被访问到则才能进入young区域由于我们的join语句在循环读磁盘和淘汰内存页，

进入old区域的数据页很可能在1秒之内就被淘汰了。这样，就会导致这个MySQL实例的Buffer Pool在这段时间内，young区域的数据页没有被合理地淘汰。

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

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

BNL转BKA：

1.直接在被驱动表上建索引，这时就可以直接转成BKA算法了

2.临时表。使用临时表的大致思路是：把表t2中满足条件的数据放在临时表tmp_t中；为了让join使用BKA算法，给临时表tmp_t的字段b加上索引；让表t1和tmp_t做join操作。

hash join：

如果join_buffer里面维护的不是一个无序数组，而是一个哈希表的话，查询就会很快。【也正是MySQL的优化器和执行器一直被诟病的一个原因：不支持哈希join】

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

无序数组：先全表扫描t1【m条】并存入join_buffer，由于是无序数组，每条取出来，再全表扫描t2【n条】，每条进行等值判断，等值判断次数为m*n次

hash join：先全表扫描t1【m条】并存入join_buffer，存入hash结构，再全表扫描t2获取满足where条件d的数据【m条】，用t2数据按照join条件去hash结构中找对应的t1数据。

能不能使用join语句？

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

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

要使用join，应该选择大表做驱动表还是选择小表做驱动表？

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

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

    * 在join_buffer_size足够大的时候，是一样的；

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

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

【18】group by

执行

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

这条语句能使用到a的索引覆盖不需要回表，这个语句的执行流程是这样的：

1. 创建内存临时表，表里有两个字段m和c，主键是m；用于计算group by结果；

2. 扫描表t1的索引a，依次取出叶子节点上的id值，计算id%10的结果，记为x；

    * 如果临时表中没有主键为x的行，就插入一个记录(x,1);

    * 如果临时表中有主键为x的行，就将x这一行的c值加1；

3. 遍历完成后，再根据字段m做排序，得到结果集返回给客户端。

需要用到临时表：

但是，内存临时表的大小是有限制的，参数tmp_table_size就是控制这个内存大小的，默认是16M。超过时就会把内存临时表转成磁盘临时表，磁盘临时表默认使用的引擎是InnoDB。不论是使用内存临时表还是磁盘临时表，group by逻辑都需要构造一个带唯一索引的表，执行代价都是比较高的。如果表的数据量比较大，上面这个group by语句执行起来就会很慢，我们有什么优化的方法呢？

执行group by语句为什么需要临时表：

group by的语义逻辑，是统计不同的值出现的个数。但是，由于每一行的id%100的结果是无序的，所以我们就需要有一个临时表，来记录并统计结果。

group by 优化方法 --索引：

可以看到因为group by使用临时表的原因是待分组的数据是无序的，如果可以确保输入的数据是有序的，那么计算group by的时候，就只需要从左到右，顺序扫描，依次累加。

如果有索引z，则select z, count(*) as c from t1 group by z;这个查询group by不需要临时表，也不需要排序。

group by优化方法 --直接排序：

如果我们明明知道，一个group by语句中需要放到临时表上的数据量特别大，却还是要按照“先放到内存临时表，插入一部分数据后，发现内存临时表不够用了再转成磁盘临时表”，看上去就有点儿傻。那么，我们就会想了，MySQL有没有让我们直接走磁盘临时表的方法呢？在group by语句中加入SQL_BIG_RESULT这个提示（hint），就可以告诉优化器：这个语句涉及的数据量很大，请直接用磁盘临时表。

select SQL_BIG_RESULT id%100 as m, count(*) as c from t1 group by m;

的执行流程就是这样的：

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

2. 扫描表t1的索引a，依次取出里面的id值, 将 id%100的值存入sort_buffer中；

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

4. 排序完成后，就得到了一个有序数组。

【19】自增主键

自增主键不能保证连续递增。

自增主键的保存位置：

MyISAM：引擎的自增值保存在数据文件中。

InnoDB：引擎的自增值，其实是保存在了内存里，并且到了MySQL 8.0版本后，才有了“自增值持久化”的能力，也就是才实现了“如果发生重启，表的自增值可以恢复为MySQL重启前的值”。在MySQL 5.7及之前的版本，自增值保存在内存里，并没有持久化。每次重启后，第一次打开表的时候，都会去找自增值的最大值max(id)，然后将max(id)+1作为这个表当前的自增值。在MySQL 8.0版本，将自增值的变更记录在了redo log中，重启的时候依靠redo log恢复重启之前的值。

自增值修改机制：

在MySQL里面，如果字段id被定义为AUTO_INCREMENT，在插入一行数据的时候，自增值的行为如下：

1. 如果插入数据时id字段指定为0、null 或未指定值，那么就把这个表当前的 AUTO_INCREMENT值填到自增字段；

2. 如果插入数据时id字段指定了具体的值，就直接使用语句里指定的值。

根据要插入的值和当前自增值的大小关系，自增值的变更结果也会有所不同。假设，某次要插入的值是X，当前的自增值是Y。

1. 如果X<Y，那么这个表的自增值不变；

2. 如果X≥Y，就需要把当前自增值修改为新的自增值。

新的自增值生成算法：

从auto_increment_offset开始，以auto_increment_increment为步长，持续叠加，直到找到第一个大于X的值，作为新的自增值。

其中，auto_increment_offset 和 auto_increment_increment是两个系统参数，分别用来表示自增的初始值和步长，默认值都是1。

备注：在一些场景下，使用的就不全是默认值。比如，双M的主备结构里要求双写的时候，我们就可能会设置成auto_increment_increment=2，让一个库的自增id都是奇数，另一个库的自增id都是偶数，避免两个库生成的主键发生冲突。

当auto_increment_offset和auto_increment_increment都是1的时候，新的自增值生成逻辑很简单，就是：

1. 如果准备插入的值>=当前自增值，新的自增值就是“准备插入的值+1”；

2. 否则，自增值不变。

自增值的修改时机：

假设，表t里面已经有了(1,1,1)这条记录，这时我再执行一条插入数据命令：

insert into t values(null, 1, 1);

这个语句的执行流程就是：

1. 执行器调用InnoDB引擎接口写入一行，传入的这一行的值是(0,1,1);

2. InnoDB发现用户没有指定自增id的值，获取表t当前的自增值2；

3. 将传入的行的值改成(2,1,1);

4. 将表的自增值改成3；

5. 继续执行插入数据操作，由于已经存在c=1的记录，所以报Duplicate key error，语句返回。

事务回滚也会产生类似的现象，这就是第二种原因。自增值不能回退。

子增值为什么不能回退：

假设有两个并行执行的事务，在申请自增值的时候，为了避免两个事务申请到相同的自增id，肯定要加锁，然后顺序申请。

【20】insert

insert … select 语句：

语句从一个表复制数据，然后把数据插入到一个已存在的表中，实现批量插入，所以会给t表所有行和间隙加锁。

insert into t2(c,d) select c,d from t;

在可重复读隔离级别下，binlog_format=statement时执行这个语句时，需要对表t的所有行和间隙加锁呢？因为加入有session同时执行：

insert into t values(-1,-1,-1);

binlog里面就记录了这样的语句序列：

insert into t values(-1,-1,-1);

insert into t2(c,d) select c,d from t;

这个语句到了备库执行，就会把id=-1这一行也写到表t2中，出现主备不一致。

insert循环写入：

插入t2表，仅仅锁住访问到的对象：

insert into t2(c,d) (select c+1, d from t force index(c) order by c desc limit 1);

这个语句的加锁范围，就是表t索引c上的(3,4]和(4,supremum]这两个next-key lock，以及主键索引上id=4这一行。

它的执行流程也比较简单，从表t中按照索引c倒序，扫描第一行，拿到结果写入到表t2中。

因此整条语句的扫描行数是1。

插入t表，会锁住整个t表：

insert into t(c,d) (select c+1, d from t force index(c) order by c desc limit 1);

1. 创建临时表，表里有两个字段c和d。

2. 按照索引c扫描表t，依次取c=4、3、2、1，然后回表，读到c和d的值写入临时表。这时，Rows_examined=4。

3. 由于语义里面有limit 1，所以只取了临时表的第一行，再插入到表t中。这时，Rows_examined的值加1，变成了5。

这个语句会导致在表t上做全表扫描，并且会给索引c上的所有间隙都加上共享的next-key lock。所以，这个语句执行期间，其他事务不能在这个表上插入数据。

insert 唯一键冲突：

t(a,b,c)且s上游索引，sessionA的c索引冲突：

sessionA：insert into t values(10,10,10)

sessionB：insert into t values(12,9,9)

可重复读（repeatable read）隔离级别下执行的。可以看到，session B要执行的insert语句进入了锁等待状态。也就是说，session A执行的insert语句，发生唯一键冲突的时候，并不只是简单地报错返回，还在冲突的索引上加了锁。我们前面说过，一个next-key lock就是由它右边界的值定义的。这时候，session A持有索引c上的(5,10]共享next-key lock（读锁）。

至于为什么要加这个读锁，其实我也没有找到合理的解释。从作用上来看，这样做可以避免这一行被别的事务删掉。

这里官方文档有一个描述错误，认为如果冲突的是主键索引，就加记录锁，唯一索引才加next-key lock。但实际上，这两类索引冲突加的都是next-key lock。

insert into … on duplicate key update：

这个语义的逻辑是，插入一行数据，如果碰到唯一键约束，就执行后面的更新语句。

【21】表数据复制

如果可以控制对源表的扫描行数和加锁范围很小的话，我们简单地使用insert … select 语句即可实现。

当然，为了避免对源表加读锁，更稳妥的方案是先将数据写到外部文本文件，然后再写回目标表。

mysqldump：

一种方法是，使用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

导出CSV：

另一种方法是直接将结果导出成.csv文件。MySQL提供了下面的语法，用来将查询结果导出到服务端本地目录：

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

【22】分区表

分区表：

我在表t中初始化插入了两行记录，按照定义的分区规则，这两行记录分别落在p_2018和p_2019这两个分区上。

可以看到，这个表包含了一个.frm文件和4个.ibd文件，每个分区对应一个.ibd文件。也就是说：

* 对于引擎层来说，这是4个表；

* 对于Server层来说，这是1个表。

分区表的引擎层行为：

分区表和手工分表，一个是由server层来决定使用哪个分区，一个是由应用层代码来决定使用哪个分表。因此，从引擎层看，这两种方式也是没有差别的。

分区表的server层行为

如果从server层看的话，一个分区表就只是一个表。

到这里我们小结一下：

1. MySQL在第一次打开分区表的时候，需要访问所有的分区；

2. 在server层，认为这是同一张表，因此所有分区共用同一个MDL锁；

3. 在引擎层，认为这是不同的表，因此MDL锁之后的执行过程，会根据分区表规则，只访问必要的分区。

分区表的应用场景：

分区表的一个显而易见的优势是对业务透明，相对于用户分表来说，使用分区表的业务代码更简洁。还有，分区表可以很方便的清理历史数据。如果一项业务跑的时间足够长，往往就会有根据时间删除历史数据的需求。这时候，按照时间分区的分区表，就可以直接通过alter table t drop partition …这个语法删掉分区，从而删掉过期的历史数据。这个alter table t drop partition …操作是直接删除分区文件，效果跟drop普通表类似。与使用delete语句删除数据相比，优势是速度快、对系统影响小。

分区表问题：

实际使用时，分区表跟用户分表比起来，有两个绕不开的问题：一个是第一次访问的时候需要访问所有分区，另一个是共用MDL锁。

因此，如果要使用分区表，就不要创建太多的分区。我见过一个用户做了按天分区策略，然后预先创建了10年的分区。这种情况下，访问分区表的性能自然是不好的。这里有两个问题需要注意：

1. 分区并不是越细越好。实际上，单表或者单分区的数据一千万行，只要没有特别大的索引，对于现在的硬件能力来说都已经是小表了。

2. 分区也不要提前预留太多，在使用之前预先创建即可。比如，如果是按月分区，每年年底时再把下一年度的12个新分区创建上即可。对于没有数据的历史分区，要及时的drop掉。

至于分区表的其他问题，比如查询需要跨多个分区取数据，查询性能就会比较慢，基本上就不是分区表本身的问题，而是数据量的问题或者说是使用方式的问题了。