MySQL面试总结

目录

基础架构

  • 连接器

    建立连接成本过高，尽量使用长连接，维护长连接的方式有两种：定期断开长连接或定期执行mysql_reset_connection 来重新初始化连接资源

  • 查询缓存

    （key-value形式）key是查询语句，value是查询结果，查询缓存命中率不高，弊大于利，在mysql8.0已被移除

  • 分析器

    判断表是否存在，列是否存在，判断sql语句是否满足语法规则，不满足返回 You have an error in your SQL syntax

  • 优化器

    选择索引（依据扫描行数，是否使用临时表，是否排序），存在join时，决定各表的连接顺序

  • 执行器

    判断表权限，调用引擎接口

日志系统

redo log

  • 是InnoDB引擎特有的日志

  • 异常重启恢复

  • 只能重建到最新状态

binlog（归档日志）

事务执行过程中，先把日志写到binlog cache，事务提交的时候，再把binlog cache写到binlog文件中。

如图，每个线程有自己的 binlog cache，但是共用同一份 binlog 文件

图中的 write，指的就是指把日志写入到文件系统的 page cache，并没有把数据持久化到磁盘，所以速度比较快

图中的 fsync，才是将数据持久化到磁盘的操作。一般情况下，我们认为 fsync 才占磁盘的 IOPS

  • Server层自己的日志

  • 备份迁移，归档

  • 可以重建到任何历史状态

binlog的格式

  • statement，记录了SQL语句原文，最后会有 COMMIT 确保完整性

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

  • row，记录了操作的表和行，最后会有一个 XID event 确保完整性

    当 binlog_format 使用 row 格式的时候，binlog 里面记录了真实删除行的主键 id，这样 binlog 传到备库去 的时候，就肯定会删除真实的行，不会有主备删除不同行的问题

  • mixed，即 statement + row

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

现在越来越多的场景要求把 MySQL 的 binlog 格式设置成 row。这么做的主要理由是恢复数据

不同

  • redo log是InnoDB引擎特有的；binlog是MySQL的Server层实现的，所有引擎都可以使用。

  • redo log是物理日志，记录的是“在某个数据页上做了什么修改”；binlog是逻辑日志，记录的是这个语句的原始逻辑，比如“给ID=2这一行的c字段加1 ”。

  • redo log是循环写的，空间固定会用完；binlog是可以追加写入的。“追加写”是指binlog文件写到一定大小后会切换到下一 个，并不会覆盖以前的日志。

binlog cache 是每个线程自己维护的，而 redo log buffer 是全局共用的原因：

binlog 是一种逻辑性的日志，记录的是一个事务完整的语句。当用来做主从同步，如果分散写，可能造成事务不完整，分多次执行，从而导致不可预知的问题。 而 redo log 属于物理性的日志，记录的是物理地址的变动，因此，分散写也不会改变最终的结果

事务隔离

事务的特性

  • 原子性

  • 一致性

  • 隔离性

  • 持久性

多事务同时执行的时候，可能会出现的问题

  • 脏读

  • 不可重复读

  • 幻读

    在可重复读隔离级别下，普通的查询是快照读，是不会看到别的事务插入的数据的。因此，幻读在“当前读”下才会出现

    幻读产生的原因

    即使给所有行加上了锁，也避免不了幻读，这是因为给行加锁的时候，这条记录还不存在，没法加锁

    • 为了解决幻读问题，InnoDB 引入了新的锁（间隙锁）

事务隔离级别

  • 读未提交

    一个事务还未提交，它所做的变更就可以被别的事务看到

  • 读提交

    一个事务提交之后，它所做的变更才可以被别的事务看到

  • 可重复读

    一个事务执行过程中看到的数据是一致的。未提交的更改对其他事务是不可见的

  • 串行化

    对应一个记录会加读写锁，出现冲突的时候，后访问的事务必须等前一个事务执行完成才能继续执行

多版本并发控制（MVCC）

隔离级别的实现依靠于视图，不同时刻启动的事务都有不同的视图，一行记录在系统中可以存在多个版本，这就是多版本并 发控制（MVCC）

  • 系统会自行判断，当没有视图使用回滚段时候，回滚日志会被删除，而此处也是尽量不要使用长事务的原因，长事务意味着会保留古老的视图，十分占用内存空间

  • 每个事务都有一个事务ID,叫做 transaction id (严格递增)

  • 事务在启动时，找到已提交的最大事务ID记为 up_limit_id

  • 事务在更新一条语句时，比如 id=1 改为了 id=2，会把 id=1 和该行之前的 row trx_id 写到undo log 里，并且在数据页上把id的值改为 2，并且把修改这条语句的 transaction id 记在该行行头

一个事务要查看一条数据时，必须先用该事务的 up_limit_id 与该行的 transaction id做比对，如果 up_limit_id >= transaction id，那么可以看，如果 up_limit_id < transaction id，则只能去 undo log 里去取。去 undo log 查找数据的时候， 也需要做比对，必须 up_limit_id > transaction id，才返回数据

当前读

由于当前读都是先读后写，只能读当前的值，所以为当前读，会更新事务内的 up_limit_id 为该事务的 transaction id，如果有其他事务占用行锁，则进入锁等待

快照读和当前读

当前读指的是 select for update 或者 select in share mode，指的是在更新之前必须先查寻当前的值，因此叫当前读。 快照读指的是在语句执行之前或者在事务开始的时候会创建一个视图，后面的读都是基于这个视图的，不会再去查询最新的值

读提交的逻辑和可重复读的区别

  • 在可重复读隔离级别下，只需要在事务开始的时候创建一致性视图，之后事务里的其他查询都共用这个一致性视图

  • 在读提交隔离级别下，每一个语句执行前都会重新算出一个新的视图

两种视图概念

  • view

    查询语句虚拟表，查询方法与表一样

  • 一致性读视图

    InnoDB 实现 MVCC 时的一致性读视图（consisitent read view），用于支持 RC（Read Committed，读提交）和 RR（Repeatable Read，可重复读）隔离级别的实现

配置方法：启动参数transaction-isolation

事务隔离的实现：每条记录在更新的时候都会同时记录一条回滚操作。同一条记录在系统中可以存在多个版本，这就是数据库的多版本并发控 制（MVCC）

回滚日志什么时候删除

系统会判断当没有事务需要用到这些回滚日志的时候，回滚日志会被删除

什么时候不需要了

当系统里么有比这个回滚日志更早的read-view的时候。

为什么尽量不要使用长事务

长事务意味着系统里面会存在很老的事务视图，在这个事务提交之前，回滚记录都要保留，这会导致大量占用存储空间。除 此之外，长事务还占用锁资源，可能会拖垮库

事务启动方式

  • 显式启动事务语句，begin或者start transaction,提交commit，回滚rollback

  • set autocommit=0，该命令会把这个线程的自动提交关掉。这样只要执行一个select语句，事务就启动，并不会自动提交， 直到主动执行commit或rollback或断开连接。

建议使用方法一，如果考虑多一次交互问题，可以使用commit work and chain语法。在autocommit=1的情况下用begin显式启动事务，如果执行commit则提交事务。如果执行commit work and chain则提交事务并自动启动下一个事务。

索引

索引的作用

提高数据查询效率

常见索引模型

  • 哈希表

    键 - 值(key - value)

    哈希思路：把值放在数组里，用一个哈希函数把key换算成一个确定的位置，然后把value放在数组的这个位置

    哈希冲突的处理办法：链表

    适用场景：等值查询的场景

  • 有序数组

    按顺序存储。查询用二分法就可以快速查询，时间复杂度是：O(log(N))

    特点：查询效率高，更新效率低

    适用场景：静态存储引擎。

  • 搜索树

    • 二叉搜索树

      每个节点的左儿子小于父节点，父节点又小于右儿子

      时间复杂度：查询时间复杂度O(log(N))更新时间复杂度O(log(N))

      适用：数据库存储大多不适用二叉树，因为树高过高，会适用N叉树

    • B+Tree：InnoDB中的索引模型

      B+ 树能够很好地配合磁盘的读写特性，减少单次查询的磁盘访问次数

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

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

    主键查询方式只需要搜索 ID 这颗 B+ 树，非主键索引需要先搜索非主键索引树，拿到 ID值，再回到主键 索引树再搜索一次，这个过程就是回表

索引类型

主键索引（聚簇索引）：主键索引的叶子节点存的是整行的数据(聚簇索引)

非主键索引（二级索引）：非主键索引的叶子节点内容是主键的值(二级索引)

区别

  • 主键索引只要搜索ID这个B+Tree即可拿到数据

  • 普通索引先搜索索引拿到主键值，再到主键索引树搜索一次(回表)

索引维护

一个数据页满了，按照B+Tree算法，新增加一个数据页，叫做页分裂，会导致性能下降。空间利用率降低大概50%。当相邻的两个数据页利用率很低的时候会做数据页合并，合并的过程是分裂过程的逆过程。

从性能和存储空间方面考量，自增主键往往是更合理的选择。

  • 保证插入记录的有序性，所有的操作都是追加操作，不会触发页分裂

  • 从存储空间的角度，主键长度越小，普通索引的叶子节点就越小，普通索引占用的空间也就越小

重建索引也是维护索引的重要手段：

索引可能因为删除，或者页分裂等原因，导致数据页有空洞，重建索引的过程会创建一个新的索引，把数据按顺序插入，这样页面的利用率最高，也就是索引更紧凑、更省空间

覆盖索引

如果查询条件使用的是普通索引（或是联合索引的最左原则字段），查询结果是联合索引的字段或是主键，不用回表操作， 直接返回结果，减少IO磁盘读写读取正行数据

特点：减少树的搜索次数，显著提升查询性能

最左前缀

  • 联合索引的最左 N 个字段，也可以是字符串索引的最左 M 个字符，遇到<、>、and、like

联合索引

根据创建联合索引的顺序，以最左原则进行where检索，比如（age，name）以age=1 或 age= 1 and name=‘张三’可以使用索引，单以name=‘张三’ 不会使用索引，考虑到存储空间的问题，还请根据业务需求，将查找频繁的数据进行靠左创建索引

索引下推

在查询语句中存在 where 子句且 where 子句条件字段存在索引，那么mysql会在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数，这个优化是在 mysql5.6 后推出的

  • like 'hello%’and age >10 检索，MySQL5.6版本之前，会对匹配的数据进行回表查询。5.6版本后，会先过滤掉age<10的数据，再进行回表查询，减少回表率，提升检索速度

索引失效

三种索引失效的情况：

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

  • 隐式转换字段类型

  • 字符集不同（隐式字符编码转换）

锁

全局锁

对整个数据库实例加锁

MySQL提供加全局读锁的方法如下：

Flush tables with read lock(FTWRL)
  • 这个命令可以使整个库处于只读状态。使用该命令之后，数据更新语句、数据定义语句和更新类事务的提交语句等操作都会被阻塞

使用场景：全库逻辑备份，使全库只读

风险

  • 如果在主库备份，在备份期间不能更新，业务停摆

  • 如果在从库备份，备份期间不能执行主库同步的binlog，导致主从延迟

官方自带的逻辑备份工具mysqldump，当mysqldump使用参数--single-transaction的时候，会启动一个事务，确保拿到一致 性视图。而由于MVCC的支持，这个过程中数据是可以正常更新的

一致性读是好，但是前提是引擎要支持这个隔离级别。

如果要全库只读，为什么不使用set global readonly=true的方式？

  • 在有些系统中，readonly的值会被用来做其他逻辑，比如判断主备库。所以修改global变量的方式影响太大

  • 在异常处理机制上有差异。如果执行FTWRL命令之后由于客户端发生异常断开，那么MySQL会自动释放这个全局锁，整个库回到可以正常更新的状态。而将整个库设置为readonly之后，如果客户端发生异常，则数据库就会一直保持readonly状态，这样会导致整个库长时间处于不可写状态，风险较高

表级锁

表锁

lock tables ... read/write

  • 可以用unlock tables主动释放锁，也可以在客户端断开的时候自动释放。lock tables语法除了会限制别的线程的读写外，也限定了本线程接下来的操作对象。

  • 对于InnoDB这种支持行锁的引擎，一般不使用lock tables命令来控制并发，毕竟锁住整个表的影响面还是太大。

元数据锁(meta data lock,MDL)

不需要显式使用，在访问一个表的时候会被自动加上。

加锁

  • MDL读锁

    • 对一个表做增删改查操作
    • 读锁之间不互斥

  • MDL写锁

    • 对表做结构变更操作
    • 写锁之间是互斥的，用来保证变更表结构操作的安全性

作用：保证读写的正确性

MDL会直到事务提交才会释放，在做表结构变更的时候，一定要小心不要导致锁住线上查询和更新。

行锁

InnoDB支持

事务

如下业务：

  • 从顾客A账户余额中扣除电影票价

  • 给影院B的账户余额增加这张电影票价

  • 记录一条交易日志

按照3、1、2这样的顺序，影院账户余额这一行的锁时间就最少

  • 最大程度地减少了事务之间的锁等待，提升了并发度

死锁

策略

  • 直接进入等待，直到超时。这个超时时间可以通过参数innodb_lock_wait_timeout来设置

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

解决热点行更新导致的性能问题

策略

  • 若能确保这个业务一定不会出现死锁，可以临时把死锁检测关掉

    业务有损

  • 控制并发度

    并发控制要做在数据库服务端。

    • 可以考虑在中间件实现
    • 修改MySQL源码，也可以做在MySQL里面

基本思路：对于相同行的更新，在进入引擎之前排队

  • 从设计上优化，将热更新的行数据拆分成逻辑上的多行来减少锁冲突，但是业务复杂度可能会大大提高

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

间隙锁 (Gap Lock)

锁的就是两个值之间的空隙

比如下表，初始化插入了6个记录，这就产生了7个间隙。

  • 间隙锁的出现是为了解决幻读问题

  • 间隙锁在可重复读隔离级别下才会生效

与行锁的对比

  • 对于行锁来说：读锁之间不冲突, 写锁与读锁冲突, 写锁与写锁冲突，所以与行锁冲突的是另外一个锁，而跟间隙锁存在冲突关系的，是“往这个间隙中插入一个记录”这个操作

  • 间隙锁的引入也带来了一些新的问题，比如：降低并发度，可能导致死锁

  • 间隙锁之间不会冲突

间隙锁加锁规则

  • 对主键或唯一索引，如果当前读时，where条件全部精确命中（ = 或者 in），这种场景本身就不会出现幻读，所以只 会加行记录锁

  • 没有索引的列，当前读操作时，会加全表Gap间隙锁

  • 非唯一索引列，如果 where 条件部分命中（>、<、like 等）或者全未命中，则会加附近Gap间隙锁

next-key lock

间隙锁和行锁合称 next-key lock

加锁规则：

  • 加锁的基本单位是 next-key lock。next-key lock 是前开后闭区间

  • 查找过程中访问到的对象才会加锁，当使用覆盖索引时，主键索引不会加锁

  • 索引上的等值查询，给唯一索引加锁的时候，next-key lock 退化为行锁

  • 索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock 退化为间隙锁，同时，在删除数据的时候尽量加 limit 可以减小锁的范围

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

主备高可用

主备一致性

binlog 保证了主备的一致

主备流程

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

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

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

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

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

循环复制问题

  • 当两库互为主备时，两库可能会互传binlog造成循环复制，解决方法：

  • 规定两个库的 server id 必须不同，如果相同，则它们之间不能设定为主备关系

  • 一个备库接到 binlog 并在重放的过程中，生成与原 binlog 的 server id 相同的新的 binlog

  • 每个库在收到从自己的主库发过来的日志后，先判断 server id，如果跟自己的相同，表示这个日志是自己生成的，就直接丢弃这个日志

主备延迟

主备延迟的来源

  • 备库所在机器的性能要比主库所在的机器性能差，解决方案是采用对称部署或配置“双1”

  • 备库压力大，解决方案是一主多从部署或通过 binlog 输出到外部系统，比如 Hadoop 这类系统，让外部系统提供统计类查询的能力

  • 大事务，主库必须等事务结束才能写 binlog ，这个时间会扩大主备延迟

可靠性优先策略

  • 在进行主备切换时，会先检查备库 seconds_behind_master 的值是否小于 5s ，小于则把主库的 readonly 设置为 true 表示主库只读，后再次检查备库 seconds_behind_master 的值是否为 0s ，为 0s 则将备库的 readonly 设置为 false 表示备库可 写，进而进行主备切换

  • 在这个过程中系统存在主备库均只可读的阶段，也就是存在不可用时间

可用性优先策略

不等主备数据同步，直接把连接切到备库，并且让备库可以读写，那么系统几乎就没有不可用时间，但是可能存在数据不一致的代价

备库并行复制能力

如果备库执行日志的能力低于主库生成日志的能力，那么主备延迟会剧增，备库追不上主库节奏，会造成主备不一致问题

而并行复制能力，也就是多线程复制，可以很好的解决这个问题

  • coordinator 就是原来的 sql_thread, 不过现在其不再直接更新数据了，只负责读取中转日志和分发事务。真正更新日志的，变成了 worker 线程，而 work 线程的个数，就是由参数 slave_parallel_workers 决定

    • 不能造成更新覆盖。这就要求更新同一行的两个事务，必须被分发到同一个 worker 中

    • 同一个事务不能被拆开，必须放到同一个 worker 中

  • 每个事务在分发的时候，跟所有 worker 的冲突关系包括以下三种情况：

    • 如果跟所有 worker 都不冲突，coordinator 线程就会把这个事务分配给最空闲的 woker

    • 如果跟多于一个 worker 冲突，coordinator 线程就进入等待状态，直到和这个事务存在冲突关系的 worker 只剩下 1 个

    • 如果只跟一个 worker 冲突，coordinator 线程就会把这个事务分配给这个存在冲突关系的 worker

  • 分发策略

    • 按表分发策略

    如果两个事务更新不同的表，它们就可以并行。因为数据是存储在表里的，所以按表分发，可以保证 两个 worker 不会更新同一行，当事务与多于一个 worker 冲突时，则进入队列等待

    • 按行分发策略

    如果两个事务没有更新相同的行，它们在备库上可以并行执行。显然，这个模式要求 binlog 格式必 须是 row，因为 statement 记录的是语句，无法看出来该事务更新了哪几行

    优化：设置一个阈值，单个事务如果超过设置的行数阈值则退化为单线程执行，具体过程：coordinator 暂时先 hold 住这个事务，等所有 worker 执行完毕，coordinator 自己执行这个事务，执行完后再恢复并行

    由于按行分发粒度更小，相比较按表并行分发策略，按行并行策略在决定线程分发的时候，需要消耗更多的计算资源，同时两策略都有一些约束条件：

    • 要能够从 binlog 里面解析出表名、主键值和唯一索引的值。也就是说，主库的 binlog 格式必须是 row
    • 表必须有主键
    • 不能有外键。表上如果有外键，级联更新的行不会记录在 binlog 中，这样冲突检测就不准确

MySQL 5.6 版本的并行复制策略

MySQL 5.6 版本的并行复制策略是按库分发，粒度很大，相比于前面两个策略，按库分发在判断冲突时无需消耗大量 CPU 资源及不要求 binlog 的格式

MariaDB 的并行复制策略

  • 利用了 redo log 组提交 (group commit) 优化，即能够在同一组里提交的事务，一定不会修改同一行与能够在同一组里提交的事务，一定不会修改同一行，具体实现时 MariaDB 会将 commit_id 相同的事务分发到同一个 worker 上

  • 之前业界的思路都是在“分析 binlog，并拆分到 worker”上。而 MariaDB 的这个策略，目标是“模拟主库的并行模式”，十分具有创新性

  • 但这个策略容易被大事务拖后腿，当遇到大事务时，仅有一个 worker 在工作，并行也就退化成了串行

MySQL 5.7.22 的并行复制策略

由参数 binlog-transaction-dependency-tracking 控制，这个参数的可选值有以下三种

  • COMMIT_ORDER，表示在两阶段提交时，同时进入 prepare 和 commit 的事务可以并行的策略

  • WRITESET，表示的是对于事务涉及更新的每一行，计算出这一行的 hash 值，组成集合 writeset。如果两个事务没有操作相同的行，也就是说它们的 writeset 没有交集，就可以并行

  • WRITESET_SESSION，是在 WRITESET 的基础上多了一个约束，即在主库上同一个线程先后执行的两个 事务，在备库执行的时候，要保证相同的先后顺序

优势：

  • writeset 由主库生成，直接写入到 binlog ，备库执行时不需要解析 binlog 内容（event 里的行数据），节省了很多计算量

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

  • 由于备库的分发策略不依赖于 binlog 内容，所以 binlog 是 statement 格式也是可以的

一主多从的主备切换过程

  • 基于位点的主备切换

    当通过 change master 命令将节点 B 设置成节点 A’ 的从库时，不可避免需要设置位点参数，但是位点存在不精确的问题

  • 基于 GTID 的主备切换

    GTID 的全称是 Global Transaction Identifier，也就是全局事务 ID，是一个事务在提交的时候生成的，是这个事务的唯一标识，由两部分组成，格式是 GTID=server_uuid:gno

    server_uuid 是一个实例第一次启动时自动生成的，是一个全局唯一的值

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

    transaction_id 与 gno 的区别

    transaction_id 就是指事务 id，事务 id 是在事务执行过程中分配的，如果这个事务回滚了，事务 id 也会递 增，而 gno 是在事务提交的时候才会分配，两个都是递增，不同点是事务id自增但不一定连续，因为会被回滚，而gno在提交时分配，所以是连续递增的

    每个 MySQL 实例都维护了一个 GTID 集合，用来对应“这个实例执行过的所有事务”

    切换逻辑

    • 实例 B 指定主库 A’，基于主备协议建立连接

    • 实例 B 把 set_b 发给主库 A’。实例 A’算出 set_a 与 set_b 的差集，也就是所有存在于 set_a，但是不存在 于 set_b 的 GTID 的集合，判断 A’本地是否包含了这个差集需要的所有 binlog 事务

      • 如果不包含，表示 A’已经把实例 B 需要的 binlog 给删掉了，直接返回错误

      • 如果确认全部包含，A’从自己的 binlog 文件里面，找出第一个不在 set_b 的事务，发给 B

    • 之后就从这个事务开始，往后读文件，按顺序取 binlog 发给 B 去执行

如何判断主库异常

  • select 1 判断

    select 1 成功返回，只能说明这个库的进程还在，并不能说明主库没问题，比如当并发线程数达到了最大值，系统死 锁后，select 1 依旧能成功返回

  • 查表判断

    为了能够检测 InnoDB 并发线程数过多导致的系统不可用情况，需要设计一个访问 InnoDB 的场景。一般的做法是，在系统库（mysql 库）里创建一个表，比如命名为 health_check，里面只放一行数据，然后定期执行

    但是更新事务要写 binlog，而一旦 binlog 所在磁盘的空间占用率达到 100%，那么所有的更新语句和事务提交的 commit 语句就都会被堵住。但是，系统这时候还是可以正常读数据

  • 更新判断

    要放个有意义的字段，常见做法是放一个 timestamp 字段，用来表示最后一次执行检测的时间

    但是，备库的检测也是要写 binlog，如果主库 A 和备库 B 都用相同的更新命令，就可能出现行冲突，也就是可能会 导致主备同步停止

    为了让主备之间的更新不产生冲突，可以在 mysql.health_check 表上存入多行数据，并用 A、B 的 server_id 做主键

    但是，当 IO 利用率 100% 时，update 语句仍能正常运行，原因在于外部检测的随机性，对主库可用性检测不可控

内部统计

MySQL 的 performance_schema 表信息，可以详细检查其内部的流程是否有异常

posted @ 2022-05-16 22:43  请务必优秀  阅读(126)  评论(0)  编辑  收藏  举报