一、mysql笔记

mysql 笔记

一、mysql锁

（一）全局锁

对整个数据库实例加锁。
MySQL提供加全局读锁的方法：Flush tables with read lock(FTWRL)
这个命令可以使整个库处于只读状态。使用该命令之后，数据更新语句、数据定义语句和更新类事务的提交语句等操作都会被阻塞。
使用场景：全库逻辑备份。
风险：
1.如果在主库备份，在备份期间不能更新，业务停摆
2.如果在从库备份，备份期间不能执行主库同步的binlog，导致主从延迟
官方自带的逻辑备份工具mysqldump，当mysqldump使用参数–single-transaction的时候，会启动一个事务，确保拿到一致性视图。而由于MVCC的支持，这个过程中数据是可以正常更新的。

一致性读是好，但是前提是引擎要支持这个隔离级别。
如果要全库只读，为什么不使用set global readonly=true的方式？
1.在有些系统中，readonly的值会被用来做其他逻辑，比如判断主备库。所以修改global变量的方式影响太大。
2.在异常处理机制上有差异。如果执行FTWRL命令之后由于客户端发生异常断开，那么MySQL会自动释放这个全局锁，整个库回到可以正常更新的状态。而将整个库设置为readonly之后，如果客户端发生异常，则数据库就会一直保持readonly状态，这样会导致整个库长时间处于不可写状态，风险较高。

（二）表级锁

有两种，一种是表锁，一种是元数据所(meta data lock,MDL)

表锁的语法是:lock tables … read/write
可以用unlock tables主动释放锁，也可以在客户端断开的时候自动释放。lock tables语法除了会限制别的线程的读写外，也限定了本线程接下来的操作对象。
对于InnoDB这种支持行锁的引擎，一般不使用lock tables命令来控制并发，毕竟锁住整个表的影响面还是太大。
MDL：不需要显式使用，在访问一个表的时候会被自动加上。
MDL的作用：保证读写的正确性。
在对一个表做增删改查操作的时候，加MDL读锁；当要对表做结构变更操作的时候，加MDL写锁。
读锁之间不互斥。读写锁之间，写锁之间是互斥的，用来保证变更表结构操作的安全性。
MDL 会直到事务提交才会释放，在做表结构变更的时候，一定要小心不要导致锁住线上查询和更新。

（三）行锁

1. 两阶段锁的概念是什么? 对事务使用有什么帮助?

概念：在InnoDB事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放。这个就是两阶段锁协议。

帮助：如果你的事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放。

2. 死锁的概念是什么？

概念：当并发系统中不同线程出现循环资源依赖，涉及的线程都在等待别的线程释放资源时，就会导致这几个线程都进入无限等待的状态，称为死锁。

3. 死锁的处理策略有哪两种？

   策略一：直接进入等待，直到超时。这个超时时间可以通过参数innodb_lock_wait_timeout来设置，默认值为50s。

   策略二：发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数innodb_deadlock_detect设置为on，表示开启这个逻辑。

4. 等待超时处理死锁的机制什么?有什么局限?

   机制：在InnoDB中，innodb_lock_wait_timeout的默认值是50s，意味着如果采用第一个策略，当出现死锁以后，第一个被锁住的线程要过50s才会超时退出，然后其他线程才有可能继续执行。对于在线服务来说，这个等待时间往往是无法接受的。

   局限：我们又不可能直接把这个时间设置成一个很小的值，比如1s。这样当出现死锁的时候，确实很快就可以解开，但如果不是死锁，而是简单的锁等待呢？所以，超时时间设置太短的话，会出现很多误伤。

5. 死锁检测处理死锁的机制是什么? 有什么局限?

6. 有哪些思路可以解决热点更新导致的并发问题?

   方法一：临时把死锁检测关掉，对业务有损。

   方法二：客户端控制并发/在数据库服务端增加中间件，控制并发。

   方法三：将并发的一行改成逻辑上的多行减少锁的冲突。还是以影院账户为例，可以考虑放在多条记录上，比如10个记录，影院的账户总额等于这10个记录的值的总和。这样每次要给影院账户加金额的时候，随机选其中一条记录来加。这样每次冲突概率变成原来的1/10，可以减少锁等待个数，也就减少了死锁检测的CPU消耗。缺点：增加了代码复杂度。

二、mysql普通索引和唯一索引的选择

（一）查询过程

给 k字段加上索引。

• 对于普通索引，查找到满足条件的第一个记录后，需要查找下一个记录，直到碰到第一个不满足k=5条件的记录。
• 对于唯一索引，由于索引定义了唯一性，查找到第一个满足条件的记录后，就会停止继续检索。

普通索引会比唯一索引多做一次“查找和判断下一条记录”的操作,但是二者的性能差异可以忽略。

1. InnoDB的数据是按数据页为单位来读写的。也就是说，当需要读一条记录的时候，并不是将这个记录本身从磁盘读出来，而是以页为单位，将其整体读入内存。在InnoDB中，每个数据页的大小默认是16KB。
2. 因为引擎是按页读写的，所以说，当找到k=5的记录的时候，它所在的数据页就都在内存里了。那么，对于普通索引来说，要多做的那一次“查找和判断下一条记录”的操作，就只需要一次指针寻找和一次计算。

（二）change buffer

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

2. 持久化：change buffer是可以持久化的数据。在内存中有拷贝，也会被写入到磁盘上

3. merge:将change buffer中的操作应用到原数据页上，得到最新结果的过程，成为merge
   访问这个数据页会触发merge，系统有后台线程定期merge，在数据库正常关闭的过程中，也会执行merge.

4. 唯一索引的更新不能使用change buffer.

5. change buffer用的是buffer pool里的内存，change buffer的大小，可以通过参数innodb_change_buffer_max_size来动态设置。这个参数设置为50的时候，表示change buffer的大小最多只能占用buffer pool的50%。

6. 将数据从磁盘读入内存涉及随机IO的访问，是数据库里面成本最高的操作之一。
   change buffer 因为减少了随机磁盘访问，所以对更新性能的提升很明显。

7. 适用场景：

   在一个数据页做merge之前，change buffer记录的变更越多，收益就越大。
   对于写多读少的业务来说，页面在写完以后马上被访问到的概率比较小，此时change buffer的使用效果最好。这种业务模型常见的就是账单类、日志类的系统。

   反过来，假设一个业务的更新模式是写入之后马上会做查询，那么即使满足了条件，将更新先记录在change buffer,但之后由于马上要访问这个数据页，会立即触发merge过程。
   这样随机访问IO的次数不会减少，反而增加了change buffer的维护代价。所以，对于这种业务模式来说，change buffer反而起到了副作用。

8. changebuffer是否会丢失：（原因：1. changebuffer会持久化、2. 会写入redolog）

   change buffer不会丢失，因为change buffer是可以持久化的数据，在磁盘上占据了系统表空间ibdata，对应的内部系统表名为SYS_IBUF_TABLE。同时对changebuffer修改会写入redolog，可以通过redolog进行恢复。因此在异常关机的时候，不会丢失。但是未写入redolog的操作，会正常丢失这部分数据。

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

（三）更新过程

InnoDB在处理更新语句的时候，只做了写日志这一个磁盘操作。(redo log)

三、 mysql更新抖动

此时可能InnoDB在后台刷脏页，而刷脏页的过程是要将内存页写入磁盘。所以，无论是你的查询语句在需要内存的时候可能要求淘汰一个脏页，还是由于刷脏页的逻辑会占用IO资源并可能影响到了你的更新语句，都可能是造成你从业务端感知到MySQL“抖”了一下的原因。

如果刷太慢，可能原因：首先是内存脏页太多，其次是redo log写满。

当内存数据页跟磁盘数据页内容不一致的时候，我们称这个内存页为“脏页”。内存数据写入到磁盘后，内存和磁盘上的数据页的内容就一致了，称为“干净页”。

（一）场景

场景一：InnoDB的redo log写满了。这时候系统会停止所有更新操作，把checkpoint往前推进，redo log留出空间可以继续写，此时可能会进行flush刷盘。

场景二：系统内存不足。当需要新的内存页，而内存不够用的时候，就要淘汰一些数据页，空出内存给别的数据页使用。如果淘汰的是“脏页”，就要先将脏页写到磁盘。

场景三：MySQL认为系统“空闲”的时候，自动进行刷盘。

场景四：MySQL正常关闭的情况。这时候，MySQL会把内存的脏页都flush到磁盘上，这样下次MySQL启动的时候，就可以直接从磁盘上读数据，启动速度会很快。

（二）分析性能影响

1. 性能分析

场景三和场景4属于正常情况，不需要关注性能。

场景一：是InnoDB要尽量避免的。因为出现这种情况的时候，整个系统就不能再接受更新了，所有的更新都必须堵住。如果你从监控上看，这时候更新数会跌为0。

场景二：内存不够用了，要先将脏页写到磁盘”，这种情况其实是常态。InnoDB用缓冲池（buffer pool）管理内存，缓冲池中的内存页有三种状态：

• 第一种是，还没有使用的；

• 第二种是，使用了并且是干净页；

• 第三种是，使用了并且是脏页。

2. InnoDB刷脏页的控制策略

我们需要正确地告诉InnoDB所在主机的IO能力，这样InnoDB才能知道需要全力刷脏页的时候，可以刷多快。

这就要用到innodb_io_capacity这个参数了，它会告诉InnoDB你的磁盘能力。

这个值我建议你设置成磁盘的IOPS。磁盘的IOPS可以通过fio这个工具来测试，下面的语句是我用来测试磁盘随机读写的命令：

fio -filename=$filename -direct=1 -iodepth 1 -thread -rw=randrw -ioengine=psync -bs=16k -size=500M -numjobs=10 -runtime=10 -group_reporting -name=mytest

3. 设计策略控制刷脏页的速度，参考因素

如果刷太慢，会出现什么情况？首先是内存脏页太多，其次是redo log写满。

所以，InnoDB的刷盘速度就是要参考这两个因素：一个是脏页比例，一个是redo log写盘速度。

InnoDB会根据这两个因素先单独算出两个数字。

脏页比例计算：

select VARIABLE_VALUE into @a from global_status where VARIABLE_NAME = ‘Innodb_buffer_pool_pages_dirty’;

select VARIABLE_VALUE into @b from global_status where VARIABLE_NAME = ‘Innodb_buffer_pool_pages_total’;

select @a/@b;

四、收缩表空间

delete 表中不用的数据，表文件大小是不会变的，需要通过 alter table命令重建表。

重建表的两种方式：

（一）重建表方式一（mysql 5.6之前）

命令：alter table t engine=innodb,ALGORITHM=copy;

copy table。

原表A上有空洞，以新建一个与表A结构相同的表B，然后按照主键ID递增的顺序，把数据一行一行地从表A里读出来再插入到表B中。在这整个DDL过程中表A中不能有更新

（二）重建表方式二（mysql 5.6之后）

命令：alter table t engine=innodb,ALGORITHM=inplace;

提供了online DDL。允许在DDL过程中更新数据。

建表流程：

1. 建立一个临时文件，扫描表A主键的所有数据页；
2. 用数据页中表A的记录生成B+树，存储到临时文件中；
3. 生成临时文件的过程中，将所有对A的操作记录在一个日志文件（row log）中，对应的是图中state2的状态；
4. 临时文件生成后，将日志文件中的操作应用到临时文件，得到一个逻辑数据上与表A相同的数据文件，对应的就是图中state3的状态；
5. 用临时文件替换表A的数据文件。

（三）mysql对于 add index的处理方式

MySQL各版本，对于add Index的处理方式是不同的，主要有三种：

（1）Copy Table方式
这是InnoDB最早支持的创建索引的方式。顾名思义，创建索引是通过临时表拷贝的方式实现的。

新建一个带有新索引的临时表，将原表数据全部拷贝到临时表，然后Rename，完成创建索引的操作。

这个方式创建索引，创建过程中，原表是可读的。但是会消耗一倍的存储空间。

（2）Inplace方式
这是原生MySQL 5.5，以及innodb_plugin中提供的创建索引的方式。所谓Inplace，也就是索引创建在原表上直接进行，不会拷贝临时表。相对于Copy Table方式，这是一个进步。

Inplace方式创建索引，创建过程中，原表同样可读的，但是不可写。

（3）Online方式
这是MySQL 5.6.7中提供的创建索引的方式。无论是Copy Table方式，还是Inplace方式，创建索引的过程中，原表只能允许读取，不可写。对应用有较大的限制，因此MySQL最新版本中，InnoDB支持了所谓的Online方式创建索引。

InnoDB的Online Add Index，首先是Inplace方式创建索引，无需使用临时表。在遍历聚簇索引，收集记录并插入到新索引的过程中，原表记录可修改。而修改的记录保存在Row Log中。当聚簇索引遍历完毕，并全部插入到新索引之后，重放Row Log中的记录修改，使得新索引与聚簇索引记录达到一致状态。

与Copy Table方式相比，Online Add Index采用的是Inplace方式，无需Copy Table，减少了空间开销；与此同时，Online Add Index只有在重放Row Log最后一个Block时锁表，减少了锁表的时间。

与Inplace方式相比，Online Add Index吸收了Inplace方式的优势，却减少了锁表的时间。

五、日志问题

redo log支持崩溃恢复；bin log不支持崩溃恢复。

两阶段提交的图：

1. 两阶段提交的不同时刻，MySQL异常重启会出现什么现象

A时刻发生crash，写binlog和redolog之间发生crash：此时binlog还没写，redo log也还没提交，所以崩溃恢复的时候，这个事务会回滚。这时候，binlog还没写，所以也不会传到备库。

B时刻发生crash，binlog写完，redo log还没commit前发生crash：

• 如果redo log 里面事务完整，已经有了commit标识，则直接提交；
• 如果redo log里面的事务只有完整的的prepare，则判断对应的事务bin log是否存在并完整
  • 如果是，则提交事务
  • 不是，则回滚事务

2. mysql怎么知道binlog是完整

一个事务的bin log有完整的格式：

• statement格式的binlog，最后会有COMMIT；
• row格式的binlog，最后会有一个XID event。

3. redo log 和 bin log怎么关联起来

他们有共同的数据字段，XID。系统崩溃（crash）恢复数据时，会顺序扫描redo log.

• 如果碰到既有prepare、又有commit的redo log，就直接提交；
• 如果碰到只有parepare、而没有commit的redo log，就拿着XID去binlog找对应的事务。

另外，在MySQL 5.6.2版本以后，还引入了binlog-checksum参数，用来验证binlog内容的正确性。对于binlog日志由于磁盘原因，可能会在日志中间出错的情况，MySQL可以通过校验checksum的结果来发现。所以，MySQL还是有办法验证事务binlog的完整性的。

4. 处于prepare阶段的redo log加上完整binlog，重启就能恢复，MySQL为什么要这么设计

采用这个策略，主库和备库的数据就保证了一致性。

在时刻B，也就是binlog写完以后MySQL发生崩溃，这时候binlog已经写入了，之后就会被从库（或者用这个binlog恢复出来的库）使用。

5. 不引入两个日志，也就没有两阶段提交的必要了。只用binlog来支持崩溃恢复，又能支持归档，不就可以了？

不可以；

MySQL的原生引擎是MyISAM，设计之初就有没有支持崩溃恢复。

InnoDB在作为MySQL的插件加入MySQL引擎家族之前，就已经是一个提供了崩溃恢复和事务支持的引擎了。

binlog没有能力恢复“数据页”，binlog里面并没有记录数据页的更新细节，是补不回来的。

6. 只用redo log 不要bin log

两个重要原因：

1. redo log不具备bin log的归档能力。
2. bin log 复制，是mysql高可用的基础，现在mysql高可用的系统机制很多依赖与bin log。

业界各个公司的使用场景的话，就会发现在正式的生产库上，binlog都是开着的。因为binlog有着redo log无法替代的功能。

一个是归档。redo log是循环写，写到末尾是要回到开头继续写的。这样历史日志没法保留，redo log也就起不到归档的作用。

一个就是MySQL系统依赖于binlog。binlog作为MySQL一开始就有的功能，被用在了很多地方。其中，MySQL系统高可用的基础，就是binlog复制。

还有很多公司有异构系统（比如一些数据分析系统），这些系统就靠消费MySQL的binlog来更新自己的数据。关掉binlog的话，这些下游系统就没法输入了。

总之，由于现在包括MySQL高可用在内的很多系统机制都依赖于binlog，所以“鸠占鹊巢”redo log还做不到。你看，发展生态是多么重要。

7 .redo log 一般设置多大

redo log太小的话，会导致很快就被写满，然后不得不强行刷redo log，这样WAL机制的能力就发挥不出来了。

所以，如果是现在常见的几个TB的磁盘的话，就不要太小气了，直接将redo log设置为4个文件、每个文件1GB吧。

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

MySQL这么设计的主要原因是，

1. binlog是不能“被打断的”。一个事务的binlog必须连续写，因此要整个事务完成后，再一起写到文件里。

2. redo log并没有这个要求，中间有生成的日志可以写到redo log buffer中。redo log buffer中的内容还能“搭便车”（组提交），其他事务提交的时候可以被一起写到磁盘中。

3. binlog存储是以statement或者row格式存储的，而redo log是以page页格式存储的。page格式，天生就是共有的，而row格式，只跟当前事务相关

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

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

六、索引问题

1. insert into …on duplicate key update

类似oracle中的merge语句，无则插入有则更新。

INSERT INTO T(A,B,C,D) VALUES (a,b,c,d) ON DUPLICATE KEY UPDATE C=C，D=d ；

2.索引字段做函数操作，可能会破坏索引值的有序性，因此优化器就决定放弃走树搜索功能。

（1）条件字段函数操作

不走索引。

（1）隐式类型转换

数据库字段类型为varchar(32)，而输入的参数却是整型，所以需要做类型转换，将varchar转为int及逆行比较，此时不走索引。

数据库字段类型为int，而输入的字段类型却是varchar类型，这个情况走索引。

（2）隐式字符编码转换

两个表的字符集不同，一个是utf8，一个是utf8mb4，所以做表连接查询的时候用不上关联字段的索引。

为什么字符集不同就用不上索引呢？

字符集utf8mb4是utf8的超集，所以当这两个类型的字符串在做比较的时候，MySQL内部的操作是，先把utf8字符串转成utf8mb4字符集，再做比较。对索引字段做函数操作，优化器会放弃走树搜索功能。

七、order by 工作原理

（一）排序过程

1. MySQL会为每个线程分配一个内存（sort_buffer）用于排序该内存大小为sort_buffer_size
   （1）如果排序的数据量小于sort_buffer_size，排序将会在内存中完成（快速排序）
   （2）如果排序数据量很大，内存中无法存下这么多数据，则会使用磁盘临时文件来辅助排序，也称外部排序
   （3）在使用外部排序时，MySQL会分成好几份单独的临时文件用来存放排序后的数据，然后在将这些文件合并成一个大文件（归并排序）

2. mysql会通过遍历索引将满足条件的数据读取到sort_buffer，并且按照排序字段进行快速排序
   （1）如果查询的字段不包含在辅助索引中，需要按照辅助索引记录的主键返回聚集索引取出所需字段
   （2）该方式会造成随机IO，在MySQL5.6提供了MRR的机制，会将辅助索引匹配记录的主键取出来在内存中进行排序，然后在回表
   （3）按照情况建立联合索引来避免排序所带来的性能损耗，允许的情况下也可以建立覆盖索引来避免回表

（二）排序方式

全字段排序
1.通过索引将所需的字段全部读取到sort_buffer中
2.按照排序字段进行排序
3.将结果集返回给客户端

缺点：
1.造成sort_buffer中存放不下很多数据，因为除了排序字段还存放其他字段，对sort_buffer的利用效率不高
2.当所需排序数据量很大时，会有很多的临时文件，排序性能也会很差

优点：MySQL认为内存足够大时会优先选择全字段排序，因为这种方式比rowid 排序避免了一次回表操作

rowid排序
1.通过控制排序的行数据的长度来让sort_buffer中尽可能多的存放数据，max_length_for_sort_data
2.只将需要排序的字段和主键读取到sort_buffer中，并按照排序字段进行排序
3.按照排序后的顺序，取id进行回表取出想要获取的数据
4.将结果集返回给客户端

优点：更好的利用内存的sort_buffer进行排序操作，尽量减少对磁盘的访问

缺点：回表的操作是随机IO，会造成大量的随机读，不一定就比全字段排序减少对磁盘的访问

uffer的利用效率不高
2.当所需排序数据量很大时，会有很多的临时文件，排序性能也会很差

优点：MySQL认为内存足够大时会优先选择全字段排序，因为这种方式比rowid 排序避免了一次回表操作

八、一条sql执行慢情况和原因

（一）查询长时间不返回

mysql> select * from t where id=1;

一般碰到这种情况的话，大概率是表t被锁住了。

排查 方法：

   1. 首先执行一下show processlist命令，查看线程状态，看看当前语句处于什么状态。然后我们再针对每种状态，去分析它们产生的原因、如何复现，以及如何处理。
   2. 若出现等MDL锁问题，**这个状态表示的是，现在有一个线程正在表t上请求或者持有MDL写锁，把select语句堵住了**
             1. kill掉持有MDL锁的进程。
             2. 5.7以后的mysql增加了sys库。查询被锁的线程id。

mysql> select blocking_pid from sys.schema_table_lock_waits;

3. 若出现等flush问题，

有线程对t表执行了flush操作：

flush tables t with read lock;
或者
flush tables with read lock;

这两个flush语句，如果指定表t的话，代表的是只关闭表t；如果没有指定具体的表名，则表示关闭MySQL里所有打开的表。

但是正常这两个语句执行起来都很快，除非它们也被别的线程堵住了。

所以，出现Waiting for table flush状态的可能情况是：有一个flush tables命令被别的语句堵住了，然后它又堵住了我们的select语句。

4. 等行锁

如果这时候已经有一个事务在这行记录上持有一个写锁，我们的select语句就会被堵住。

怎么查出是谁占着这个写锁,查询方法：

mysql> select * from t sys.innodb_lock_waits where locked_table=`'test'.'t'`;

（二）排查锁表方法

-- 1.查看 是否锁表
show open tables where in_use > 0;

-- 2.查看数据库当前进程
show processlist;

-- 3.当前运行的所有事务
SELECT * FROM information_schema.INNODB_TRX;

-- 4.当前出现的锁
SELECT * FROM information_schema.INNODB_LOCKs;

-- 5.锁等待的对应关系
SELECT * FROM information_schema.INNODB_LOCK_waits;

-- 6.查询事务表中的事务
select
	p.id,
	p.time,
	i.trx_id,
	i.trx_state,
	p.info
from
	INFORMATION_SCHEMA.PROCESSLIST p,
	INFORMATION_SCHEMA.INNODB_TRX i
where
	p.id = i.trx_mysql_thread_id
	and i.trx_state = 'LOCK WAIT';

-- 7.kill掉锁表的语句的线程
kill 10;

九、幻读

（一） 何为幻读

幻读指的是一个事务在前后两次查询同一个范围的时候，后一次查询看到了前一次查询没有看到的行。

对“幻读”做一个说明：

1. 在可重复读隔离级别下，普通的查询是快照读，是不会看到别的事务插入的数据的。因此，幻读在“当前读”下才会出现。
2. select * from tablename for update。查询语句加上for update是当前读（相当于update语句），不加是快照读。

（二）造成原因

行锁只能锁住行，但是新插入记录这个动作，要更新的是记录之间的“间隙”，没办法上锁。

（三）间隙锁

为了解决幻读问题，InnoDB只好引入新的锁，也就是间隙锁(Gap Lock)。

间隙锁只能在可重复读隔离级别下生效。间隙锁存在冲突关系，是在往间隙中插入一个记录这个操作，间隙锁之间不存在冲突。

间隙锁的引入，可能导致同样的语句锁住更大范围，会影响并发。

（四） next-key lock

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

也就是说，我们的表t初始化以后，0,5,10,15,20,25，如果用select * from t for update要把整个表所有记录锁起来，就形成了7个next-key lock，分别是 (-∞,0]、(0,5]、(5,10]、(10,15]、(15,20]、(20, 25]、(25, +suprenum]。

间隙锁记为开区间，把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：唯一索引上的范围查询会访问到不满足条件的第一个值为止。
6. 锁是加在索引上的；

（六）加锁案例

CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `c` int(11) DEFAULT NULL,
  `d` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `c` (`c`)
) ENGINE=InnoDB;

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

1. 等值查询间隙锁

id为主键，表中不存在id=7的记录。有id=5和10的记录。

begin ：

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

加锁分析：

根据原则1加锁单位是next-key lock，会给（5,10]区间加锁。

根据优化2，next-key lock退化为间隙，则最终加锁的是（5，10）区间加间隙锁。

2. 非唯一索引等值锁（覆盖索引）

id为主键，c为普通索引。

事务1

begin:

select id from t where c= 5 lock in shaare mode;

事务2
update t set d=d+1 where id=5; (query ok)
事务3
insert t values(7,7,7); (blocked)

加锁分析:

• 根据原则1加锁单位是next-key lock，会给（0,5]区间加锁。

• 因为c是普通索引，因此仅访问c=5这一条记录是不能马上停下来的，需要向右遍历查到第一个不满足条件的才停止，查到c=10才放弃。故根据原则2，访问到的对象才会加锁，因此要给（5,10]加锁。

• 根据优化2，等值判断，向右遍历，最后一个不满足c=5等值条件，需要退化为间隙锁，故加锁区间为（5,10）;

• 根据原则2 ，只有访问到的对象才会加锁，这个查询使用覆盖索引，并不需要访问主键索引，所以主键索引上没有加任何锁，这就是为什么事务2的update语句可以执行完成。事务3被锁，是被间隙锁锁住。

在这个例子中，lock in share mode只锁覆盖索引，但是如果是for update就不一样了。 执行 for update时，系统会认为你接下来要更新数据，因此会顺便给主键索引上满足条件的行加上行锁。

十、mysql 如何保证数据不丢失

只要redo log和binlog保证持久化到磁盘，就能确保MySQL异常重启后，数据可以恢复。

生产环境通常设置双1策略（sync_binlog=1、innodb_flush_log_at_trx_commit=1 控制binlog和redolog持久化时机都是每次事务提交后就持久化，保证数据不丢失），一个事务完整提交前，需要等待两次刷盘，一次是redo log（prepare 阶段），一次是binlog。

（一）binlog写入流程

binlog写入逻辑：事务执行过程中，先把日志写入到binlog cache，事务提交的时候，再把binlog cache 写入到binlog文件，并清空binlog cache。

每个线程拥有一个binlog cache，多个线程共用一个binlog文件。

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

binlog调优参数：

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

• sync_binlog=0, 表示每次提交事务都只是write，不fsync。
• sync_binlog=1,表示每次提交事务都fsync；
• sync_binlog=N,(N>1),表示每次提交事务都write，但累积N个事务后才fsync。

因此，在出现IO瓶颈的场景里，将sync_binlog设置成一个比较大的值，可以提升性能。在实际的业务场景中，考虑到丢失日志量的可控性，一般不建议将这个参数设成0，比较常见的是将其设置为100~1000中的某个数值。

但是，将sync_binlog设置为N，对应的风险是：如果主机发生异常重启，会丢失最近N个事务的binlog日志。

（二）redolog 写入流程

redolog写入逻辑：事务执行过程中，生成的redolog 先写入redo log buffer，根据设置的参数选择时机将redo log buffer持久化到磁盘。

这三种状态：

• 存到redolog buffer， 是mysql进程中的内存。

• 写到磁盘，但没有fsync持久化，FS page cache是文件系统的页缓存。

• fsync持久化到磁盘。

redolog调优参数:

控制redolog写入策略是由参数innodb_flush_log_at_trx_commit控制的：

1. innodb_flush_log_at_trx_commit=0，表示每次事务提交时都只是把redo log留在redo log buffer中;
2. innodb_flush_log_at_trx_commit=1，表示每次事务提交时都将redo log直接持久化到磁盘；
3. innodb_flush_log_at_trx_commit=2，表示每次事务提交时都只是把redo log写到page cache。

InnoDB有一个后台线程，每隔1秒，就会把redo log buffer中的日志，调用write写到文件系统的page cache，然后调用fsync持久化到磁盘。

注意，事务执行中间过程的redo log也是直接写在redo log buffer中的，这些redo log也会被后台线程一起持久化到磁盘。也就是说，一个没有提交的事务的redo log，也是可能已经持久化到磁盘的。

如果把innodb_flush_log_at_trx_commit设置成1，那么redo log在prepare阶段就要持久化一次，因为有一个崩溃恢复逻辑是要依赖于prepare 的redo log，再加上binlog来恢复的。

每秒一次后台轮询刷盘，再加上崩溃恢复这个逻辑，InnoDB就认为redo log在commit的时候就不需要fsync了，只会write到文件系统的page cache中就够了。

（三）组提交机制

redolog持久化到磁盘时，是将一组多个事务一块提交，写盘时会带上每个事务的长度。大幅度降低磁盘的IOPS消耗。

十一、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。这样做的风险是，主机掉电的时候会丢数据。

我不建议你把innodb_flush_log_at_trx_commit 设置成0。因为把这个参数设置成0，表示redo log只保存在内存中，这样的话MySQL本身异常重启也会丢数据，风险太大。而redo log写到文件系统的page cache的速度也是很快的，所以将这个参数设置成2跟设置成0其实性能差不多，但这样做MySQL异常重启时就不会丢数据了，相比之下风险会更小。

十二、mysql主备

（一）mysql主备原理

M-S结构

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

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

一般把备库权限设置为只读（readonly），防止主备切换异常，造成双写，数据不一致。readonly对超级权限管理员不生效 ，主备同步更新的线程拥有的就是超级权限。

MM结构

双M结构和M-S结构，其实区别只是多了一条线，即：节点A和B之间总是互为主备关系。这样在切换的时候就不用再修改主备关系。

双M引入循环复制问题

问题：业务逻辑在节点A上更新了一条语句，然后再把生成的binlog 发给节点B，节点B执行完这条更新语句后也会生成binlog，在双M结构下，A和B间互为主备，会不断地循环执行这个更新语句，也就是循环复制了。

解决：规定两个库的server 必须不同，binlog中记录server id，每个库收到自己主库发过来的binlog，先判断server id是自己生成的，丢弃掉。

update语句在节点A执行，然后同步到节点B的完整流程图

库接收到客户端的更新请求后，执行内部事务的更新逻辑，同时写binlog。

备库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 格式：保存完整的sql，可能会导致主备不一致。
• row格式：恢复数据容易，保存操作的每一条数据，会很占空间，比如你用一个delete语句删掉10万行数据，用statement的话就是一个SQL语句被记录到binlog中，占用几十个字节的空间。但如果用row格式的binlog，就要把这10万条记录都写到binlog中。这样做，不仅会占用更大的空间，同时写binlog也要耗费IO资源，影响执行速度。
• mixed格式：MySQL自己会判断这条SQL语句是否可能引起主备不一致，如果有可能，就用row格式，否则就用statement格式。

（二）主备延迟

定义

同步延迟与数据同步有关的时间点主要包括以下三个：

1. 主库A执行完成一个事务，写入binlog，我们把这个时刻记为T1;
2. 之后传给备库B，我们把备库B接收完这个binlog的时刻记为T2;
3. 备库B执行完成这个事务，我们把这个时刻记为T3。

所谓主备延迟，就是同一个事务，在备库执行完成的时间和主库执行完成的时间之间的差值，也就是T3-T1。

查看主备延迟时间

在备库上执行 show slave status命令 返回seconds_behind_master,表示当前备库延迟多少秒。

seconds_behind_master计算方法：

1. 每个事务的binlog 里面都有一个时间字段，用于记录主库上写入的时间；
2. 备库取出当前正在执行的事务的时间字段的值，计算它与当前系统时间的差值，得到seconds_behind_master。
3. 备库连接主库时，会通过SELECT_UNIX_TIMESTAMP()函数获取主库系统时间，如果主库和备库机器的系统时间设置不一致，备库计算seconds_behind_master的时候会扣掉这个差值。

主备延迟可能原因

（1）网络原因

网络慢，导致日志从主库传给备库耗时增长，一般正常情况下，这个时间都很短。

网络正常情况下，主备延迟来源主要是接收完binlog执行完这个事务之间的时间差。

（2）主备机器性能差异

一般情况下，有人这么部署时的想法是，反正备库没有请求，所以可以用差一点儿的机器。或者，他们会把20个主库放在4台机器上，而把备库集中在一台机器上。

其实我们都知道，更新请求对IOPS的压力，在主库和备库上是无差别的。所以，做这种部署时，一般都会将备库设置为“非双1”的模式。

但实际上，更新过程中也会触发大量的读操作。所以，当备库主机上的多个备库都在争抢资源的时候，就可能会导致主备延迟了。

（3）备库压力大

常见场景：主库既然提供了写能力，那么备库可以提供一些读能力。或者一些运营后台需要的分析语句，不能影响正常业务，所以只能在备库上跑。

备库上的查询耗费了大量的CPU资源，影响了同步速度，造成主备延迟。

处理方案：

1. 一主多从。除了备库外，可以多接几个从库，让这些从库来分担读的压力。（主多从的方式大都会被采用。因为作为数据库系统，还必须保证有定期全量备份的能力。而从库，就很适合用来做备份。）
2. 通过binlog输出到外部系统，比如Hadoop这类系统，让外部系统提供统计类查询的能力。

（4）大事物

因为主库上必须等事务执行完成才会写入binlog，再传给备库。所以，如果一个主库上的语句执行10分钟，那这个事务很可能就会导致从库延迟10分钟。

1. 不要一次性地用delete语句删除太多数据。
2. 大表DDL.

（5） 备库并行复制能力

TODO

主备延迟场景分析

问题：假设，现在你看到你维护的一个备库，它的延迟监控的图像类似图6，是一个45°斜向上的线段，你觉得可能是什么原因导致呢？你又会怎么去确认这个原因呢？

看这曲线,应该是从库正在应用一个大事务（包括大表DDL、一个事务操作很多行）,或者一个大表上无主键的情况(有该表的更新)
应该是T3随着时间的增长在增长,而T1这个时间点是没变的,造成的现象就是
随着时间的增长,second_behind_master也是有规律的增长。

（三）主备切换

实际应用过程中，建议使用可靠性策略，在此基础上，通过减少主备延迟时间，提升系统可用性。

可靠性策略

在双M结构下，从状态1到状态2切换的详细过程是这样的：

1. 判断备库B现在的seconds_behind_master，如果小于某个值（比如5秒）继续下一步，否则持续重试这一步；
2. 把主库A改成只读状态，即把readonly设置为true；
3. 判断备库B的seconds_behind_master的值，直到这个值变成0为止；
4. 把备库B改成可读写状态，也就是把readonly 设置为false；
5. 把业务请求切到备库B。

这个切换流程，一般是由专门的HA系统来完成的，我们暂时称之为可靠性优先流程。

主备切换过程中存在不可用状态，就是主备库都是只读。在步骤2到步骤5之间，系统处于不可写状态，直到步骤5结束。

可用性策略

将可靠性优先策略过程中的步骤4、5调整到最开始执行，不能主备数据同步，直接把连接切到备库b并且让备库B可以读写，那么系统几乎就没有不可用时间了。

该过程可能出现数据不一致情况。

在可用性策略下，选择使用不同的binlog格式：

mixed或者statement格式，数据可能会悄悄不一致。不容易发现，

row格式，数据不一致容易发现，会在两边库的应用线程报错duplicate key error并停止，冲突的两条数据都不会执行。

十三、集群（一主多从）

一主多从结构图：

一主多从，一般是一个主库一个备库，多个从库。和A’互为主备， 从库B、C、D指向的是主库A。

当A主库发生故障，进行A’备库切换，从库B、C、D也要改连接到A‘。

（一）集群模式主备切换

（1）基于位点的主备切换

基于位点的主备协议，是由备库决定的，备库指定哪个位点，主库就发哪个位点，不做日志的完整性判断。从库从指定主库的指定日志文件的偏移量开始同步。

主备切换命令：

该命令有6个参数，前4个参数是要连接主库的账户密码等信息。

后两个参数：

MASTER_LOG_FILE：主库的日志文件

MASTER_LOG_POS：日志文件的坐标位置

主库对应的文件名和日志偏移量。

change master to 
MASTER_HOST=$host_name 
MASTER_PORT=$port 
MASTER_USER=$user_name 
MASTER_PASSWORD=$password 
MASTER_LOG_FILE=$master_log_name 
MASTER_LOG_POS=$master_log_pos

切换过程：

1. 找故障A库和A’的同步位点，无法精确获取，只能取大概位置，尽量往前取。

   1. 找同步位点：

      1. 等待新主库A‘把中转日志（relay log）全部同步完成；

      2. 在A’上执行show master status 命令，得到当前A‘上最新的file和 position。

      3. 取原主库A的故障时刻T；

      4. 用mysqlbinlog工具解析A’的日志文件，得到T时刻的位点。

         命令：

         mysqlbinlog File --stop-datetime=T --start-datetime=T
         

         [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-K9fi8rA9-1639643285851)(E:\soft2\typero\Typora\img\mysql-一主多从-mysqlbinlog.png)]

         end_log_pos后面的值123，表示A‘实例在T时刻写入新的binlog位置，将该值作为$master_log_pos。

2. 连接要切换的主库A’,并设定同步主库的日志文件及日志文件的偏移量。

（2）基于 GTID的主备切换

全局事务ID(Global Transaction Identifier),是一个事务在提交的时候生成，GTID=source_id:transaction_id.

• source_id:是一个mysql实例第一次启动的时候自动生成的，一个全局唯一的值。

• transaction_id：事务id，一个整数，初始值是1，每次提交事务的时候分配给这个事务，并加1。

GTID模式下，每个事务对会有一个GTID唯一对应

• 记录binlog时，会先记录这个事务的GTID， SET @@SESSION.GTID_NEXT=‘source_id:transaction_id’;
• 把这个GTID加入到本mysql实例的GTID集合中。

mysql实例都维护一个GTID集合，对应这个实例执行过的所有事务，找同步位点的工作有新主库比较和从库的GTID集合找差集。

主备切换命令：

启动GTID：启动一个MySQL实例的时候，加上参数gtid_mode=on和enforce_gtid_consistency=on就可以了。

master_auto_position=1就表示这个主备关系使用的是GTID协议。

CHANGE MASTER TO 
MASTER_HOST=$host_name 
MASTER_PORT=$port 
MASTER_USER=$user_name 
MASTER_PASSWORD=$password 
master_auto_position=1 

主备切换过程中出现主键冲突问题：在从库执行下面命令，提交一个空事务，在从库的GTID集合中加入主库冲突的GTID,这样主备继续同步时，备库的GTID集合中已存在，就会跳过该事务。

set gtid_next='aaaaaaaa-cccc-dddd-eeee-ffffffffffff:10';
begin;
commit;
set gtid_next=automatic;
start slave;

切换过程：

只需要执行下面切换主库命令。执行基于GTID的主备切换命令（连接主库，设置为GTID模式）。

（二）GTID模式下主备切换问题

在GTID模式下设置主从关系的时候，从库执行start slave命令后，主库发现需要的binlog已经被删除掉了，导致主备切换不成功。这种情况下，你觉得可以怎么处理呢？

1. 由于GTID具有全局唯一性，那么其它正常的gtid已经被复制到了其他从库上了，只需要切换gtid到其他从库，等待同步完毕后在切换回主库即可.

2. 需要主从数据一致，最好通过重新搭建从库。

3. 如果binlog有备份，可以现在备库上应用缺失的binlog，然后再执行start slave 启动主从复制。

（三） 解决过期读方案

1. 强制走主库方案

   将查询请求做分类，对于必须要拿到最新结果的请求，强制将其发到主库上；对于可以读到旧数据的请求，才将其发到从库上。

2. sleep方案

   主库更新后，读从库之前先sleep一下。具体的方案就是，类似于执行一条select sleep(1)命令。

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

   方法一：从库执行查询请求前，执行show slave status命令，先判断seconds_behind_master是否已经等于0。如果还不等于0 ，那就必须等到这个参数变为0才能执行查询请求。

   方法二：对比位点确保主备无延迟；执行show slave status命令，判断主库最新位点和备库执行的最新位点是否一致。Master_Log_File和Relay_Master_Log_File、Read_Master_Log_Pos和Exec_Master_Log_Pos这两组值完全相同，就表示接收到的日志已经同步完成。

   方法三：对比GTID集合确保主备无延迟：

4. 配合semi-sync（半同步复制）方案；

   客户端提交事务，主库执行完，把binlog发给从库

   从库收到binlog以后，恢复主库一个ack，表示收到

   主库收到这个ack，才能给客户端返回“事务完成”的确认。

5. 等主库位点方案；

6. 等GTID方案。

（四）对大表做DDL方案

现在备库上设置 set_log_bin=off 关闭binlog写入，然后执行ddl语句。

完成以后，进行主备切换，然后再主库上执行一下set_log_bin=off 关闭binlog写入，执行ddl语句。

完成后，在做一下主备切换。该方案不足：在主备切换过程中会有一段时间服务不可用。