dcddc

西米大人的博客

0%

系统学习MySQL

发表于 更新于 分类于 热度: ℃ 评论: ℃
字数： 24k 阅读时长 ≈ 40 分钟
系统学习MySQL

启动与连接

知识点

1、MySQL 的 Server 层分为哪几个模块？每个模块的功能是？

  • 连接器，负责与 mysql 客户端建连和权限校验。分析器，负责 mysql 语句的词法和语法分析，判断对哪些表做什么操作。优化器，制定执行计划，选择索引。执行器，调用存储引擎层 api 执行数据操作

2、MySQL 客户端使用长连接的好处和坏处？

  • 好处，减少建连和验权产生的耗时，提高 sql 执行效率。坏处，容易产生 OOM，因为执行 sql 临时创建的对象管理在连接对象里，只有断开长连接时才会被 GC

3、如何解决客户端长连接造成 OOM 的问题？

  • 执行完一个较大操作后执行 mysql_reset_connection 初始化连接资源，不会重新建连和验权

4、如何解决客户端连接数激增打满数据库连接池问题？

  • 设置连接 timeout，空闲超过一定时间自动断开。或者执行”kill connection 连接 id”命令主动断连

正文

MySQL 是 CS 模型，包括 MySQL 服务端和客户端。
MySQL 服务端进程占用 3306 端口
启动服务端的命令：mysql.server start
关闭服务端的命令：mysql.server stop

服务端又分为 Server 层和存储引擎层
Server 层按模块划分包括：连接器、分析器、优化器、执行器。

  • 连接器：负责客户端连接管理、权限管理
    • MySQL 客户端使用长连接的好处和坏处
      • 客户端尽量与服务端建立长连接，减少比较耗时的建连次数。但使用长连接容易造成 OOM，使得系统强杀掉 MySQL 进程。原因是 MySQL 执行过程中临时使用的内存都管理在连接对象里，在断连时才会释放。
    • 如何解决客户端长连接造成 OOM 的问题
      • 每次执行一个较大操作后，执行mysql_reset_connection来重新初始化连接资源，这个过程不会做重连和权限校验
    • 如何解决客户端连接数激增打满数据库连接池问题
      • 可以设置连接的wait_timeout参数或kill connection +连接id，使得处于空闲状态的连接在指定时间后断开或主动断开，来减少连接数。不过处于空闲的连接，也可能处于事务中，断开连接会回滚事务，所以这种方式也是有损的
  • 分析器：包括词法分析和语法分析，解析出 SQL 语句要对哪张表执行哪些操作
  • 优化器：生成 SQL 操作的执行计划。比如决定使用哪个索引、多表连接时各个表的连接顺序
  • 执行器：调用存储引擎提供的接口执行 SQL 操作

存储引擎层提供读写接口，负责数据的存储和提取。其架构模式是插件式的，支持 InnoDB（最常用）、MyISAM（MySQL 自带）、Memory 等多个存储引擎

我们常用的命令mysql -hlocalhost -uroot -p123456是启动客户端，建立与 mysql 服务端的连接
客户端启动后，输入quit关闭客户端，断开与 MySQL 服务端的连接

数据类型

知识点

1、数值类型字段超出范围存储时，MySQL 会如何处理？

  • 严格模式下插入失败，非严格模式截断处理

2、DATETIME 和 TIMESTAMP 区别？

  • DATETIME 存储年月日时分秒，占 8 字节。TIMESTAMP 存储时间戳，占 4 字节。可存储时间范围上，DATETIME 更大

正文

描述数据类型时，有以下约定：

  • M 表示整数类型的最大显示宽度。对于浮点和定点类型，M 是可以存储的总位数（精度）。对于字符串类型，M 是最大长度。允许的最大值 M 取决于数据类型。
  • D 适用于浮点和定点类型，并指示小数点后面的位数。最大可能值为 30，但不应大于 M-2。
  • [ ]表示类型定义的可选部分。

常用数据类型分为三类：数值型、日期型、字符串型

数值型

数值类型包括整数型、浮点型、定点型

整数型（精确值）

  • TINYINT[(M)] [UNSIGNED] [ZEROFILL]
    • 范围非常小的整数，有符号的范围是 -128 到 127，无符号的范围是 0 到 255
  • SMALLINT[(M)] [UNSIGNED] [ZEROFILL]
    • 范围较小的整数，有符号的范围是 -32768 到 32767，无符号的范围是 0 到 65535
  • MEDIUMINT[(M)] [UNSIGNED] [ZEROFILL]
    • 中等大小的整数，有符号的范围是 -8388608 到 8388607，无符号的范围是 0 到 16777215。
  • INT[(M)] [UNSIGNED] [ZEROFILL]
    • 正常大小的整数，有符号的范围是 -2147483648 到 2147483647。无符号的范围是 0 到 4294967295。
  • BIGINT[(M)] [UNSIGNED] [ZEROFILL]
    • 大整数，有符号的范围是 -9223372036854775808 到 9223372036854775807，无符号的范围是 0 到 18446744073709551615。

注意，如果不手动指定 UNSIGNED，那么默认就是有符号的

默认的字段显示宽度与是否有符号有关。以 TINYINT 为例，如果定义字段a TINYINT，字段 a 的数据类型最终会描述为TINYINT(4)，4 表示最大长度，因为默认有符号，范围是-128~127，所以最长需要 4 个宽度来显示。如果定义时指定为 UNSIGNED，字段最终描述就会变为TINYINT(3) UNSIGNED
字段显示宽度其实可以在定义时自由指定，如果字段定义时增加了ZEROFILL约束，则不够长度的数据前面会默认补 0，且ZEROFILL约束会自动添加UNSIGNED约束

浮点型

  • FLOAT[(M,D)] [UNSIGNED] [ZEROFILL]
    • 单精度浮点数。允许值是-3.402823466E+38 到-1.175494351E-38，0 以及 1.175494351E-38 到 3.402823466E+38。M 是总位数（小数点和负号不计入），D 是小数点后面的位数
  • DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL]
    • 双精度浮点数。允许值是-1.7976931348623157E+308 到-2.2250738585072014E-308，0 以及 2.2250738585072014E-308 到 1.7976931348623157E+308。M 是总位数（小数点和负号不计入），D 是小数点后面的位数

定点型

  • DECIMAL[(M[,D])] [UNSIGNED] [ZEROFILL]
    • 常用于存储精确的小数，M 是总位数（小数点和负号不计入），D 是小数点后的位数。如果 D 为 0，则值没有小数点或小数部分。如果 D 省略，则默认值为 0，如果 M 省略，则默认值为 10。M 的范围是 1 到 65。D 范围为 0 到 30，且不得大于 M

注意，浮点型和定点型的数据在存储时，超出位数会进行四舍五入，所以都存在精度丢失问题

对于数值类型，如果存储时超出范围，会根据 SQL 当前模式做相应处理：

  • 严格模式，插入失败
  • 非严格模式，截断后存储

字符串型

  • CHAR[(M)]
    • 固定长度的字符串，在存储时始终用空格填充指定长度。M 表示以字符为单位的列长度。M 的范围为 0 到 255.如果 M 省略，则长度为 1。无论字符串多长，存储时始终占用 M 个字节
  • VARCHAR(M)
    • 可变长度的字符串，M 表示字符的最大列长度，M 的范围是 0 到 65535（2^16 -1），存储时占用 L+1（L<=M,L 为实际字符的长度）个字节
  • TINYTEXT[(M)]
    • 不能有默认值，占用 L+1 个字节，L<2^8
  • TEXT[(M)]
    • 不能有默认值，占用 L+2 个字节，L<2^16
  • MEDIUMTEXT[(M)]
    • 不能有默认值，占用 L+3 个字节，L<2^24
  • LONGTEXT[(M)]
    • 不能有默认值，占用 L+4 个字节，L<2^32
  • ENUM(‘value1’,’value2’,…)
    • ENUM 是一个字符串对象，其值从允许值列表中选择，它只能有一个值，从值列表中选择，最多可包含 65535 个不同的元素
  • SET(‘value1’,’value2’,…) 字符串对象，该对象可以有零个或多个值，最多可包含 64 个不同的成员

日期类型

  • TIME
    • 范围是’-838:59:59.000000’ 到’838:59:59.000000’
    • TIME 类型的数据，存储时完整格式为D HH:MM:SS。D 表示天数，当指定该值时，存储时小时会先乘以该值。我们通常按年-月-日 时:分:秒存储时，只会存储时分秒
  • DATE
    • 支持的范围是 ‘1000-01-01’到 ‘9999-12-31’
  • DATETIME
    • 日期和时间组合。支持的范围是 ‘1000-01-01 00:00:00.000000’到 ‘9999-12-31 23:59:59.999999’。
    • 存储空间 8 字节
  • TIMESTAMP
    • 时间戳。范围是’1970-01-01 00:00:01.000000’UTC 到’2038-01-19 03:14:07.999999’UTC。
    • 设置字段为 null 时，timestamp 会自动存储当前时间，datetime 存储的仍然是 null
    • 存储空间 4 字节
  • YEAR
    • 范围是 1901 到 2155

约束条件

Mysql 表字段在创建时，可以指定约束条件，这里说一些相关的重要知识点。

  • 主键不一定必须是自增 id，也可以同时指定多个字段组合作为主键，但主键一定是唯一且非空的
  • 唯一性约束的字段或组合可以为空，但如果非空，需要保证唯一性
  • 主键和唯一性约束都默认增加了字段索引
  • 外键用来关联父表和子表，父表作为外键的字段或组合必须建立索引

日志系统

知识点

1、binLog 和 redoLog 的作用是？

  • 主备数据同步 && crashSafe

2、写 binLog 和 redoLog 如何降低磁盘 IOPS 消耗

  • 降低写盘频率，N 次事务提交后一次性写盘

3、什么是双 1 配置？

  • binLog 和 redoLog 的写盘频率都为 1

4、binLog 日志有几种格式？它们各自有哪些优缺点？

  • statement、row、mix。statement 占用存储空间最小，但主备数据同步时可能因为选错索引造成主备数据不一致。row 存储变更前后完整的数据，误操作时可通过 row binLog 恢复数据。mix 根据是否会造成主备不一致来决定存 statement 还是 row。

5、误删库表后重建数据库有哪几种方案？

  • 1.定期全量备份的临时库应用增量 binLog，跳过误操作 binLog。临时库执行 mysqlbinLog 命令只能单线程应用 binLog，优化方案是将临时库设为从库，执行 start slave 从备库应用增量 binLog，这样能利用并行复制能力。2.延时备库，也要跳过误操作 binLog。

6、什么是 redoLog 的二阶段提交？

  • 保证 binLog 和 redoLog 数据一致性，事务提交时先写 redoLog 为 prepare 状态，然后再写 binLog，成功后再 commit redoLog

正文

在执行写操作时，会涉及到两个重要的日志模块：redoLog 和 binLog

redoLog

作用：
redoLog 是 InnoDB 存储引擎层的，作用是提供异常重启时数据恢复（crash-safe）的能力
工作机制：
每次执行写操作后，会记物理日志（在某个数据页做了什么修改）到 redoLog 文件。当系统崩溃重启后，可以根据 redoLog 找到之前的操作记录，恢复数据
redoLog 文件大小是固定的，因此写 redoLog 的方式是循环写，需要两个指针来指向当前可写的位置（write pos）和已经写入磁盘的位置（check pos）。当 write pos 追上 check pos 后，就需要强制执行一次写磁盘操作

binLog

作用：
binLog 是 Server 层的日志，用于主备数据同步。例如在定期做线上库备份后，以备库的结束时间为起点，读取 binLog，执行回放操作，将备库恢复到和线上库一样的状态。
工作机制：
每次写操作前，会先写日志到 binLog 文件。具体的写入方式是先写内存 binlog cache，等事务提交时再 fsync 到磁盘里的 binLog 文件，参数sync_binlog控制 fsync 的频率，当设为 N 时，表示 N 个事务后再执行一次 fsync，能大幅降低磁盘 IOPS 消耗。注意，当 N>1，数据库 crash 时存在丢 binlog 的可能性，是有风险的
redoLog 使用innodb_flush_at_trx_commit参数和组提交机制写盘，也能大幅降低磁盘 IOPS 消耗
当sync_binlog和innodb_flush_at_trx_commit都设置为 1，表示一个事务提交前要写两次磁盘，称为 MySQL 的双1配置。

binLog 的几种日志格式：
binLog 有三种日志格式，分别是statement、row、mix

  • statement 是原始 SQL 语句，好处是能最大程度降低 binLog 文件大小，因为一个操作 1000 条记录的写操作也只存一行 SQL 语句，但弊端就是在主备数据同步时，备库执行 statment 的结果可能与主库不同，造成主备数据不一致。例如 delete 操作选错索引就会删除不同的记录
  • row 保存写前和写后的完整数据，虽然占用较大的存储空间，但能保证主备数据一致，且能利用写前数据做回滚操作，目前的数据库一般都是 row 格式的 binLog
    • 误操作数据后可以利用 row binLog 重放来恢复，但这些操作限于增删改查，对于 drop、truncate 命令，binLog 里还是一条 statement，因此无法回滚。对于这些命令导致的误删除操作，只能通过对定期全量备份库恢复出的临时实例应用增量 binLog 来重建数据库
      • 如果使用mysqlbinlog命令来应用增量 binLog，只能单线程执行，无法用到并行复制能力，重建数据库耗时较长。优化方案是将临时实例设为从库，执行 start slave 命令从备库上应用 binLog，这能用到并行复制。不过要注意，如果备库没有重建数据库依赖的所有增量 binLog，需要将缺失 binLog 提前写入备库
      • 对于需要应用很多天增量 binLog 的重建库操作，重建时间依然不可控，因此另一种方案是延时备库，即备库会延时指定时间后再执行主库 binLog，只要误操作发现的时间小于延时时间，就可以修改延时备库 binLog，跳过误操作事务，延时备库就可以作为重建后的数据库
  • mix 格式会判断 statment 是否会产生主备数据不一致，如果会，使用 row 格式，否则使用 statment 格式

binLog 和 redoLog 的区别：

  • binLog 是 Server 层的，redoLog 属于 InnoDB 存储引擎，提供 crash-safe 数据恢复能力，binLog 不具备 crash-safe 能力
  • binLog 写入方式是追加写，redoLog 是循环写。因此 redoLog 相比 binLog 要小得多，在设计之初 binLog 就被用来做数据备份，redoLog 做 crash-safe
  • binLog 写入的是逻辑日志，redoLog 是物理日志

二阶段提交

redoLog 和 binLog 各司其职，在写操作时都需要先更新这两个日志文件，因此必须保证写操作的最终一致性，否则用 binLog 做数据备份后的备库和线上库会存在数据不一致
为了保证两个日志文件的最终一致性，在写redoLog时采用二阶段提交：

  • 先写 redoLog，处于 prepare 状态
  • 写 binLog 成功
  • 写 redoLog，处于 commit 状态
    即，只有 binLog 写入成功，redoLog 才处于 commit 状态，只有 commit 状态的 redoLog 记录的操作会写入磁盘

主备架构

知识点

1、主备数据如何同步？

  • 备库设为只读，然后从主库拉取 binLog，写到 relayLog，多线程消费 relayLog，并行复制数据到备库

2、什么是并行复制？并行复制的实现有哪些策略？

  • 为降低主备数据同步延迟，备库并发消费同步的 binLog，称为并行复制。为保证主备一致，同一事务的 sql 操作放到一线程里，同一行写入的多个事务分发到同一线程里
  • 策略包括按库并行、WriteSet 模式并行，后者指 binLog 里写入事务更新的所有行的 hashCode，两个事务写的行没有交集时，分发给不同线程并发执行

3、什么是循环复制？如何解决循环复制？

  • 双主架构下，两个实例间互相同步 binLog，产生循环复制。例如 A 实例把 binLog 同步给 B，B 执行后又同步回 A，这样一直重复
  • 解决方式是 binLog 里写入执行的实例 id，当校验 binLog 的实例 id 就是自己时，不执行 binLog

4、什么场景下可能产生主备延迟？

  • 主库写入速率大于备库同步 binLog 速率。造成的原因可能是大事务、备库读压力激增产生性能瓶颈

5、主备切换有哪几种策略？和主备延迟有什么关系？

  • 主备切换时，主库也要设为 readOnly，防止主备双写产生数据不一致，因此这期间数据库不可写
  • 可靠性优先时，需要备库同步 binLog 追上主库，主备延迟为 0 时，才主备切换
  • 可用性优先时，直接主备切换，新主库继续同步剩下的 binLog，这可能产生主备数据不一致

6、什么是 GTID？一主多从架构下做主备切换时，GTID 的作用是什么？

  • GTID 是全局事务 id。一主多从架构下主备切换时，从库通过新主库 GTID SET 和从库已同步的 GTID SET 差集，判断从新主库的哪个 binLog 位点开始同步

7、什么情况下存在客户端过期读问题？

  • 主备延迟读备库存在过期读问题

8、半同步复制 semi-sync 机制如何解决过期读问题？该机制适合一主多从架构吗？

  • 主库同步发 binLog 给备库，拿到备库 binLog 的 ACK 后，才返回客户端事务提交成功，同步方式保证主备数据强一致来解决过期读问题，称为半同步复制 semi-sync 机制
  • 不适合一主多从架构，因为有一个从库 ACK 后事务就提交成功，不能保证其他从库也已经同步完成

9、GTID 方案能完美解决过期读问题吗？如何解决的？

  • 能。客户端读操作先请求主库，主库选择一个从库，传入主库执行的最新 GTID，如果从库在限定时间内执行了这个事务，认为主备数据一致，可以读从库，否则读主库

10、MHA 数据库高可用性保障中，是如何检测库实例是否可用的？

  • “执行”select 1 语句”或”更新系统表”来判断实例是否可用

正文

主备数据同步的流程

备库设置为 readOnly 模式，防止双写造成主备不一致。备库使用一个长连接与主库建联，内部启动一个 io 线程拉取主库 binLog 日志，并写入relayLog，同时内部有多个线程消费 relayLog，执行 binLog 到备库

并行复制

为了提升备库的数据同步性能，降低主备延迟，备库使用多线程消费 relayLog 同步主库数据，称为并行复制，并行复制要做到：

  • 不能产生覆盖更新，即对同一行更新的多个事务必须分发到同一个线程，避免主备数据不一致
  • 同一个事务的多次更新必须分发到同一个线程，避免出现查询时只更新了一半的情况

MySQL 为了实现并行复制，制定了一些复杂的策略，核心是要保障主备库执行时的逻辑时序性（不一定是严格时序，因为事务间对不同行的更新是可以并行的，因此才可以利用并行复制提高备库的同步数据速度）

  • 按库并行
    • 最简单的并行复制策略是按库并行，每个线程绑定一些库，操作同一个库的事务被分配给对应线程。粒度较粗，在不同库的压力均衡的场景中有不错的效果
  • writeset 模式
    • 主库在写 binLog 时，计算更新行的 hash 值一并写入，组成writeset。当两个事务更新的 writeset 没有交集，说明没有并发更新行，就可以在备库不同线程中并行执行

循环复制

双 M 结构下，库实例间会互相同步 binLog，为了避免造成循环复制（即库 A 执行的 binLog 同步给库 B，库 B 执行完又同步给库 A，如果库 A 再次执行就出现循环复制），拿到 binLog 后会校验执行这个 binLog 的库实例 id 是否为当前库，如果是，就不执行 binLog

主备延迟

备库消费 relayLog 的速度比主库生成 binLog 的速度慢时，就会存在主备延迟（备库写 binLog 时刻与主库写 binLog 时刻的差，忽略主备间 binLog 网络传输速度，通常是很快的）
主备延迟用参数seconds_behind_master描述，造成主备延迟的原因可能有这么几种：

  • 激增的查询压力
    • 备库的 IOPS 压力不会比主库小，且备库往往还要承担一些运营数据统计相关的读操作。解决办法是一主多从（从库分担查询压力）和对称部署（主备性能一致）
  • 大事务
    • 主库的大事务提交后才写 binLog、传输到从库的 relayLog，而从库上消费 relayLog 执行时也可能是大事务，造成消费 relayLog 滞后于主库生成 binLog

主备切换

主备切换时，在可靠性优先策略下，会判断主备延迟（secondes_behind_master）足够小才执行。这是因为切换的过程中，主库也会置为 readOnly，这段时间数据库是不可写的，直到主备不存在延时，即备库追上主库后，才把备库切为主库，所以切换前主备延迟越小，切换过程中数据库不可写时间越短，且切换的前提一定是主备不存在延迟，保证了可靠性。
另一种策略是可用性优先，不会等待主备延迟为 0 就直接切换主备库，这可能造成主备数据不一致，因为存在主备延时会导致主备库部分写操作的时序不一致
由此可知，主备延迟越小，主备切换时数据库的可用性越高，而主备数据同步和切换也是 MySQL 高可用系统的基础

一主多从架构下的主备切换：
该架构作用是把主库的读压力分担给从库，但是当主库 crash 切换到备库时，从库的数据同步需要从主库切换到备库，读取的源 binLog 文件和位点必然都会改变。那么从库该从备库 binLog 的哪个位点开始同步数据呢？
我们知道，备库作为主库，需要将主库的 binLog 全部同步到 relayLog。所以从库的策略是取备库执行主库 crash 时刻的 binLog 位置，从该位点开始拉取从库的 binLog，即拉取到的就是主库 crash 后，备库作为主库后执行的事务 binLog，这样不会丢数据。但保守起见，这个位点可能在 crash 位点前面，即位点后面的部分事务可能在从库已经执行过，所以从库在刚开始执行备库 binLog 事务时可能出现唯一键冲突或删除行不存在，这时将这两类错误跳过就好，因为跳过是无损的

GTID 解决主备切换的位点问题：
主备切换时，从库同步时跳过错误的做法依然不够优雅和可靠（当然需要执行一段时间后再关闭这个跳过策略），因此 MySQL 为每个提交了的事务定义了一个GTID全局事务 id 来标识，GTID 通过 binLog 传递给从库（所谓备库也是在从库中选择一个），每个数据库都会维护一个GTID Set，表示它执行过的事务 id。当从库同步数据时，如果发现 GTID 一致，则跳过这个事务，这就解决了主备切换时，从库因不能精准定位位点，可能会重复执行事务的问题
实际应用上，MySQL 是通过新主库的 GTID SET 和从库的 GTID SET 取差集（从库真正需要执行的 binLog 事务集）来计算从库在新主库需要的 binLog 位点

主从架构下的过期读问题

当客户端读从库时，因为存在主备延迟，那么可能读到未更新的数据，称为过期读，解决过期读有这几种方案：

  • 强制走主库
  • 对比从库读到主库的最新位点和备库执行的最新位点，二者一致说明主从数据完全同步（前提是忽略 binLog 网络传输延时）
  • 对比从库接收到的 binLog 里的 GTID SET 和已经执行的 GTID SET，相同说明数据完全同步（前提是忽略 binLog 网络传输延时）
  • 半同步复制 semi-sync 机制
  • 等主库位点/GTID 方案

半同步复制 semi-sync 机制：
上面所述的后两种方案都是建立在主库 binLog 传输给备库无延迟的前提下，因此这些方案也不能完全解决过期读问题。不过加上 MySQL 提供了半同步复制semi-sync机制就可以。当开启 semi-sync 时，主库的 binLog 会同步发给备库，只有收到备库 ack 后，才返回客户端事务提交成功，这就保障了主备库 binLog 的绝对同步。但 semi-sync 不适合一主多从架构，因为这时只要有一个从库返回 ack 就会认为主从 binLog 同步成功，读到未及时返回 ack 的从库依然可能出现过期读

等主库位点/GTID 方案：
该方案能绝对保证不存在过期读。原理是客户端查询请求先到主库，主库选择一个从库，传入主库最新的位点/GTID 以及等待超时时间，如果在超时时间内从库执行到了这个位点/执行了这条事务，就可以到从库查询，否则查主库
位点和 GTID 稍有不同，获取主库最新位点要多一次主库查询，但 GTID 可以在每次执行完事务后就能拿到，因此少了一次查主库的开销
当遇到大事务时，主备延迟增加，等待时间很可能超时，这时读请求还是会落在主库，所以应该尽量避免大事务

MHA

MHA 一般是如何实现的？
MHA（MasterHighAvailability），其实就是数据库的高可用性是如何保障的呢？
数据库实例可用，意味着并发 SQL 未超过阈值、日志盘的 IO 利用率未达到 100%，因此有如下几种检测方法：

  • select 1
    • 这种方法无法检测到并发 SQL 达到阈值的场景，因为 select 1 不会使并发 SQL 数+1（注意，如果 SQL 操作被阻塞，也不会被统计到并发 SQL 数），也无法检测到日志盘利用率，因为不是写操作不会写日志，但它依然是默认的检测方法
  • update 系统表
    • 根据主从架构的实例数，在系统表里创建对应数目的记录，主键为库实例的 server_id。定期轮询执行更新系统表操作，每个实例更新自己的记录，执行 relayLog 更新的是其他实例的记录，避免出现行冲突。如果更新失败，说明库实例不可用，需要执行主备切换
      • 如果系统表只有一条记录，那么发给实例的更新命令和实例通过 binLog 同步过来的操作可能同时更新同一行，产生行冲突
    • 因为是写操作，所以 SQL 并发数和日志盘利用率都能检测到，但有时候日志盘利用率达到 100%时，更新系统表依然可能成功，因此检测的实时性稍差一些

索引

知识点

1、从 InnoDB 实现索引的角度，说说为什么主键不适合选择占用较大存储空间的数据类型？

  • InnoDB 存储数据使用主键索引树和普通索引树，普通索引树上存储的是主键值，因此主键占据存储空间小可有效节省整体的数据存储空间

2、什么是回表？

  • 普通索引树上缺失查询的字段时，回查主键索引树获取需要的字段值，称为回表

3、什么是覆盖索引？

  • 索引树上包含了查询需要的所有字段，无需回表

4、哪些查询能用到索引的最左前缀原则？

  • 多个查询条件字段匹配联合索引从左边开始的几个索引字段
  • 命中字符串索引查询时，查询条件是从左边开始的几个字符

5、影响 MySQL 选择索引的因素有哪些？

  • 索引的基数、是否回表、是否排序

6、什么是基数？

  • 索引字段不同值的个数

7、为什么 MySQL 有时会选错索引？有什么解决办法？

  • 对索引基数的判断是一个抽样后的估值，可能不准确
  • 解决办法是查询时使用 force xx 强制走某个索引，或者执行命令”analyze table xx”来重新统计索引基数

8、字符串加前缀索引有什么利弊？

  • 优点是减少了索引的存储空间，数据页上可覆盖更多的索引值
  • 缺点是基数变小，查询效率可能受影响，且不能走索引覆盖，必须回表

9、有哪些操作看起来会走索引但实际不会

  • 索引字段使用函数处理
  • 索引字段是字符串型，但查询条件是数字型，mysql 对索引字段增加强转函数
  • 多表级联查询，前查的表使用 utf8mb4 字符集，后查的表使用 utf8 字符集，这时会对后查表的索引字段使用强转函数，转成 utf8mb4 字符集避免精度损失

正文

索引的作用是提高查询效率，做到这一点与索引使用的数据结构密切相关
下面介绍几个数据结构并分析其利弊

  • 哈希表
    • 适合等值查询，不适合范围查询。写入效率高
  • 有序数组
    • 只适用于静态存储引擎，适合等值和范围查询，但写入效率低
  • 二叉搜索树
    • 查询和写入效率比较平均，都是 logN，但当数据量大时，查询一次访问节点个数较多，不适合写入磁盘
  • N 叉搜索树
    • 当 N=1200，树高为 4，就可以存储 17 亿数据，且查询一次最多访问 3 次磁盘，减少查询时磁盘访问次数，这也是 B+树能作为 InnoDB 的索引存储结构的重要原因

InnoDB 的索引实现

索引的实现放在存储引擎中，以 InnoDB 为例，使用B+树来存储索引
每一个索引都对应一个 B+树，主键索引和普通索引对应的 B+树存储数据有区别
主键索引树上挂载的是整行数据，因此也被称为聚簇索引
普通索引树上挂载的是主键的值，因此通过普通索引树查询后需要再回到主键索引树查询才能获取整行记录，这称为回表。正是基于回表的查询策略，所以普通索引也被称为二级索引

选取主键的权衡

一般情况我们的主键选择自增 id，因为这能使得我们用相对较小的存储空间（bigint 是 8 字节）表示相对较大的数据范围。上面提到，普通索引存储的是主键值，所以主键一般不宜选择占用较大存储空间的数据类型。

如何避免回表

如果查询需要的列在索引中已存在，或者只查询主键 id，那就没必要再回表查询整行记录了，这称为覆盖索引，使用覆盖索引是一种常用的数据库性能优化手段。例如，建立联合索引 a+b，当需要以 a 为条件查询 b 时，就会命中覆盖索引，无需回表
另一种避免回表的优化手段是索引下推。当查询命中联合索引时，会尽可能在联合索引树上对查询条件进行判断，只有当出现需要的查询条件不在联合索引里时，才会回表

最左前缀原则

B+树这种索引结构，可以利用索引的最左前缀来定位记录，称为最左前缀原则，这里有两层含义：

  • 最左前缀可以是联合索引的最左 N 个字段
  • 最左前缀可以是字符串索引的最左 M 个字符
    • 例如有联合索引“姓名+年龄”，那么当查询条件是姓名 like’张%’，依然会命中这个联合索引

MySQL 索引选择

MySQL 的 Server 层优化器负责在查询时选择索引，但有时也会选择错误的索引导致耗时较长。我们人为判断应该选择哪条索引时，通常选择“扫描行数最少”的那个索引，但优化器还会综合考虑是否需要回表、是否需要排序、扫描行数这些情况来总和判断，所以有时也会选错索引
索引的基数（Cardinality）是优化器选择索引很重要的参考之一。基数表示索引上不同值的个数，因此基数越大，索引的区分度就越高，选择索引后需要扫描的行数就越少。例如一个 1000 条记录的表，索引 a 的基数是 1000，索引 b 的基数是 10，那么查询一条数据的操作，选择索引 a 扫描的行数是 1，选择索引 b 扫描的行数就是 100
需要说明的是，基数是采样统计的，只是一个估计值，这也可能造成 MySQL 选错索引
如果发现 MySQL 选错索引导致查询超时，可以这么做：

  • 使用analyze table t来重新统计索引信息，这时会重新统计索引的基数
  • 可以在查询命令里使用force index(a)来强制指定使用哪条索引

字符串加前缀索引的利弊

好处：

  • 减少索引树占用的磁盘存储空间
    • 增加相同的数据页存放的索引值，提高查询效率

弊端：

  • 降低索引的区分度（基数），造成更多的扫描行数，性能会受影响
  • 使用了前缀索引就无法使用覆盖索引，因为前缀索引匹配的索引结果是不精确的，还是需要回表

那么如何能高效对字符串加索引呢？

  • 倒序存储。例如身份证加索引，可以在存储时倒序，则前缀就能具备较大的区分度
    • 弊端是不支持范围查询，且依然不能完全解决额外的扫描行数问题
  • 增加 hash 字段。存储字符串的哈希值，对哈希值加索引
    • 弊端是不支持范围查询，写入和查询时增加了额外的 CPU 计算损耗

一些实际不会走索引的操作

1、如果对索引字段做了函数操作，可能破坏索引值的有序性，因此优化器会放弃走索引，而是对普通索引树或主键索引树做全表扫描
2、当需要类型转换时，MySQL只能默认将字符串转成数字，所以如果表里索引字段是数字型，查询条件是字符串，那么 MySQL 默认类型转换后，依然能命中索引，但如果反过来，MySQL 就会加上强转函数，原因同问题 1，无法走索引而是扫全表，所以拿不准时，使用字符串作为查询条件总是保险的
3、如果做多表级联查询时，假设优化器最终选择先查表 a 再查表 b，如果查表 b 时想命中索引，需要保证表 b 和表 a 的连接字段使用相同的字符集，或者表 b 的连接字段是 utf8mb4 字符集（这样不会强转表 b 的字段），否则 MySQL 会对表 b 的字段强转字符集，查询表 b 就会走全表扫描，原因同问题 1
目前 mysql 的两种字符集utf8、utf8mb4，其中后者是前者的超集，所以当需要字符集转换时，总是 utf8 要转成 utf8mb4 避免精度丢失

锁

知识点

1、加全局锁有什么应用场景？加全局锁有什么风险？

  • 全库备份。风险是如果被长事务阻塞，全局锁后续的事务（DML、DDL 操作）也会被同步阻塞

2、元数据锁的作用是？什么操作需要加元数据读锁、写锁？

  • 保证对表数据读取和写入时，表结构不会发生变化
  • DDL 操作加元数据写锁，DML 操作加元数据读锁

3、DDL 操作造成长时间锁表的原因是什么？怎么解决？

  • 因为 DDL 操作加元数据写锁，而 DML 操作加元数据读锁，所以 DDL 长时间不释放元数据写锁时，会阻塞后续所有 DML 操作

4、从 MySQL 锁的角度，说说 InnoDB 取代 MyISAM 的原因是什么？

  • InnoDB 支持行锁，MyISAM 不支持，所以对于 MyISAM，所有可以加行锁的场景都会升级为表锁，严重影响 mysql 的并发度

5、什么是行锁的二阶段协议？

  • 在一个事务里，行锁在需要的时候添加，在事务提交后才释放

6、死锁检测的策略是什么？为什么死锁检测会造成热点行更新出现性能问题？

  • 当事务被锁时，检测和锁相关的每个事务是否可能被这中间的其他事务锁住，时间复杂度为 O(n²)，一旦检测到，主动回滚死锁链路里的一个事务
  • 因为死锁检测的时间复杂度为 O(n²)，对于热点行，n 很大，死锁检测的性能会产生瓶颈，因此要避免热点行更新

正文

根据加锁范围，Mysql 的锁分为三类：全局锁、表级锁、行锁
锁的类型分为读锁和写锁
读锁为共享锁，写锁为排它锁

全局锁

MySQL 提供了加全局读锁（FlushTableWithReadLock，FTWRL）的方法，加全局读锁后，后续的任何 DML（增删改）、DDL(建表、修改表结构)和更新事务的提交都会被同步阻塞。加全局锁操作也会被前面进行中的事务阻塞，因此需要避免长事务，否则会导致后续的事务因为加全局锁操作被长时间阻塞
全局锁主要用于全库备份，加全局锁保证了所有表在备份时的一致性。当然也可以通过设置事务隔离级别为可重复读来实现，但前提是所有表都使用支持事务的存储引擎如 InnoDB，如果使用了不支持事务的 MyISAM，则只能使用全局锁来做全库备份

表级锁

表级锁细分为表锁和元数据锁
对于支持行锁的存储引擎如 InnoDB，一般不使用表锁
元数据锁（MDL,MetaDataLock）保证在对表的读取和写入正确性，即不会在读取写入时执行任何 DDL 操作修改表结构
DML 操作需要加元数据读锁，DDL 操作需要加元数据写锁
如果 DDL 操作“时机不对”，很容易因为元数据写锁和读锁互斥，导致很多 DML 操作被阻塞，所以安全变更表结构需要做到以下几点：

  • 提前干掉长事务，否则 DDL 因为需要等待前面长事务释放元数据读锁而长时间阻塞，而 DDL 后续的 DML 操作也会同步阻塞，造成较长时间的锁表
  • 对于热点表，设置一个等待获取元数据写锁的超时时间，如果超时拿不到则放弃，改为在“非热点时段”重新尝试

行锁

对于不支持行锁的存储引擎，例如 MyISAM，并发写只能升级为表锁，这严重限制了数据库的并发度，所以这也是 InnoDB 取代 MyISAM 的重要原因，InnoDB 支持行锁

行锁的二阶段协议：
在 InnoDB 事务中，在需要的时候会加行锁，但直到事务结束才会释放行锁，这就要求在 InnoDB 事务中，尽量将影响并发度的操作放到后面以减少行锁占据的时间

死锁检测：
当两个事务各自依赖对方释放行锁，即循环等待时，就会产生死锁。解决死锁有两个方法：设置等待锁超时时间和死锁检测。前者超时时间很难定义清楚，容易造成误伤，因此通常采取死锁检测。
死锁检测的策略是：当一个事务被锁时，检测与锁相关的线程（包括持有锁的线程和与它竞争同一把锁的线程）是否可能被其他线程锁住，如此循环，如果判断出现了循环等待，就认为存在死锁，主动回滚死锁链条中的某一个事务来解开死锁。
需要注意，死锁检测的性能损耗会随并发度的增加而成倍増长。当 n 个线程并发竞争同一把行锁时，每一个线程的死锁检测时间复杂度是 O(n)，总的时间复杂度就是 O(n²)，会极大地占用 CPU 资源，这就是热点行更新带来的性能问题，解决的思路有两种：

  • 控制热点行更新的并发度
  • 将热点行散列成多行来降低并发更新的概率

事务隔离

知识点

1、什么是脏读、不可重复读、幻读？

  • 读未提交的事务对数据的变更，当事务回滚后，已读的数据失效，产生脏读
  • 一个事务内相同查询条件，两次读取到数据不一致，称为不可重复读，通常是其他事务对数据做了变更
  • 幻读和不可重复度类似，其他事务对数据做了增删，导致事务内相同查询条件，下次读取的记录数发生变化

2、InnoDB 如何实现事务隔离？

  • undoLog+MVCC 多版本并发控制。记录每次发生变更时，会在回滚日志 undoLog 记录数据版本，即修改后的值和对应的事务 id。不同事务隔离级别会在不同时刻创建视图数组，保存当前时刻已创建但未提交的事务数组。读取数据时，如果数据最新版本的事务 id 在视图数组内，就通过 undoLog 回滚到上一版本，直到回滚到不在视图数组里的那个版本，读取该版本的值，即只会读取创建视图数组时刻已提交的事务。称为 MVCC 多版本并发控制
  • 对于可重复读 RR，创建视图数组的实际发生在事务启动时，即只会读事务启动前已提交的事务。对于读已提交 RC，执行每个 SQL 时创建视图数组，因此每次都能读取已提交的事务。读未提交即每次读取最新版本的数据，没用到视图数组。串行化对读写加锁，不会用到 MVCC
  • 需要说明一点，如果读取到数据版本的事务 id 就是当前线程，直接读取，即事务内部的修改总是对自己可见

3、什么是当前读？哪些 SQL 操作会使用当前读？

  • update、delete 操作，或者 select…lock in share mode 或者 select… for update，做这些 sql 操作会触发当前读，读取最新版本的数据

4、什么是 gap 锁？gap 锁为什么容易造成死锁？

  • gap 锁用于限制在记录间插入新的数据记录。因为加 gap 锁没有任何限制，所以不同事务可能阻塞在对方加的 gap 锁上，产生死锁

5、当前读的实现原理？

  • RR 隔离级别下，因为要在事务内满足读一致性，对于当前读，需要在事务结束前，对”扫描到的记录加读锁、gap 锁”，如果是 update、delete 这种变更操作或者 select…for update，还会对扫描到的记录加写锁防止其他事务修改。加 gap 锁的目的也是防止新增满足条件的记录。
  • RC 隔离级别下，因为运行每次读提交，所以只会对满足条件的记录加读锁

6、RR 级别的当前读什么情况下会造成大面积锁表？

  • 当 update、delete 这种变更操作或者 select…for update，没有命中索引时，会走主键索引全表扫描，RR 级别下对扫描到的记录加写锁，还会加 gap 锁，相当于锁表的效果

7、MVCC 使用的回滚日志会一直保存吗？如果系统中存在长事务，会有什么问题？

  • 不会。只会保存到最老的一个事务需要回滚到的数据版本为止。如果存在长事务，undoLog 为了保证能回滚到该事务创建的视图数组之前的那个版本，需要维护很长的回滚日志

正文

在事务执行过程中，需要控制其他事务更新数据后对当前事务查询数据的可见性级别，称为事务隔离，不同的可见性对应不同级别的事务隔离
理解事务隔离级别前需要先知道什么是脏读、幻读、不可重复读

脏读、不可重复读、幻读

脏读
一个事务读到另一个事务尚未提交的修改，就是脏读
脏读的后果：如果后一个事务回滚，那么它所做的修改，统统都会被撤销。前一个事务读到的就是垃圾数据

不可重复读
一个事务多次读取同一条记录，前后读取的数据不同，称为不可重复读
造成这种情况说明前后两次读取过程中记录被修改，可能是已提交事务对记录的修改，也可能是未提交事务的修改，这时后一次读取就是脏读

幻读
和不可重复读基本含义相同，只不过是针对其他事务的 insert 操作造成前后读的结果不一致，称为幻读

事务隔离就是解决上面所述多个事务同时进行时可能出现的脏读、不可重复读、幻读问题

InnoDB 实现事务隔离

事务隔离的几个级别：

  • 读未提交：一个事务还未提交，他做的变更就能被其他事务看到
  • 读提交：一个事务提交后，他做的变更才能被其他事务看到
  • 可重复读：一个事务执行过程中看到的数据和他启动时看到的数据一致。这是 mysql 的默认隔离级别
  • 串行化：读写都会加锁，当出现读写锁冲突，后访问的事务必须等前一个事务执行完才能继续执行

InnoDB 对事务隔离的实现方式：undoLog+MVCC
每次数据更新，都会生成一个新的数据版本，保存最新的值和对应做了更新操作的事务 id。同时记录回滚日志undoLog，通过回滚日志能回退到上一个版本的值并拿到上一个版本对应的事务 id
根据不同的事务隔离级别，在不同时刻会创建视图数组。视图数组里并不是物理上的视图，数组里存储的其实是当前时刻已经启动但还未提交的事务 id。通过视图数组和读取到数据的事务 id 进行比对，来决定是读取当前数据，还是通过回滚日志继续回溯之前版本的数据并重复这一过程直至读到满足隔离级别的数据，这称为事务内的一致性读。InnoDB 在实现上使用的就是多版本并发控制（MVCC）的一致性读视图

回滚日志不会一直存在，只会保留到能回滚到最老的一个事务可读取的记录版本为止。因此尽量避免使用长事务，因为这意味着系统里会存在很老的事务视图，在长事务提交前，回滚记录都必须保留，这会占据大量存储空间
另外需要注意，如果事务需要执行多次回滚才能拿到隔离级别下的数据，可能产生性能问题

针对不同的隔离级别，分别在以下时刻创建视图数组

  • 读未提交级别可以理解为没有任何事务隔离，所以没有用到视图数组
  • 读提交(RC,ReadCommited)在执行每个 SQL 时创建
  • 可重复读(RR,ReadRepeat)在事务启动时创建视图，所以整个事务期间读取的数据是一致的
  • 串行化通过加锁来避免了不同事务并发访问数据，所以也不用视图数组

当一个事务内部进行查询（读）操作时，视图数组和查询到的记录版本中的事务 id 比对规则如下：

  • 如果记录的最新版本对应的事务 id 就是当前事务，就可以读取值，即事务内部对记录做的更新，对自己总是可见的
  • 如果记录的最新版本对应的事务 id 不在视图数组内
    • 如果是在视图数组创建前已经提交的事务，就可以读取值
    • 如果不是在视图数组创建前已经提交的事务，说明是之后创建的事务，不可以读取值，通过回滚日志访问上一个版本的记录，重复这个过程
  • 如果记录的最新版本对应的事务 id 在视图数组内，说明是在创建视图数组时还未提交的事务，不可以读取值，通过回滚日志访问上一个版本的记录，重复这个过程

总结一下：事务可以读取到的值限定为两种情况：

  • 本次事务内部对记录的更新
  • 创建视图数组前已经提交的事务对记录的更新

当前读

与事务隔离实现的一致性读相对应的是当前读
事务里每次读取记录的最新值，称为当前读

哪些情况是当前读？

  • 事务里的 update、delete 操作就是当前读，然后更新
    • 因为是更新，所以需要加写锁
  • select xx from t where ... lock in share mode
    • 只读，加读锁
  • select xx from t where ... for update
    • 因为 for update 目的是更新，所以需要加写锁

gap锁：gap 锁没有读写锁的概念，就是起到限制在相邻两行记录间插入数据的作用，且加 gap 锁不会被阻塞
但正是因为加 gap 锁不会阻塞，所以gap锁很容易造成死锁，因为两个事务很可能都阻塞在对方加的 gap 锁，就会产生死锁

当前读的实现原理：
在 RR 隔离级别下，对于只读型或更新型当前读，其语义是需要事务内独占满足条件的记录（更新操作还需要基于当前最新值进行更新），这需要满足：

  • 不允许在事务提交前，查询到的记录被修改，所以会加行读锁
    • 如果查询使用了索引覆盖，对于只读型当前读，只会对索引树上的记录加锁，不会对主键索引树上的整行记录加锁，但对于更新型，MySQL 认为你还是会更新数据的，所以依然对整行记录加锁
  • 不允许新增满足条件的记录，所以会对扫描到的记录间加 gap 锁
    • 如果允许新增记录，会造成主备数据不一致，原因是：当主库里更新型当前读 update 后又新增满足条件的记录，这些新增记录都不会被 update，但 binlog 里这些新增记录的 insert 在 update 前，如果备库拿 binLog 执行，这些新增记录就都会被 update，造成主备数据不一致
    • 特别地，如果查询命中唯一索引，不会加 gap 锁，因为不会新增满足条件的记录
  • 更新型当前读还不允许满足条件的记录在事务提交前被其他线程读取，所以会对扫描到的行加行写锁

RC 隔离级别下，因为允许读到其他事务的更新，所以相对于 RR，不会加 gap 锁，且只会对满足条件的记录加行锁，即加锁的范围相比 RR 小很多，这也是很多业务默认使用 RC 的原因

  • RC 级别不加 gap 锁，因此需要处理主备数据不一致问题

RR 隔离级别下，当前读可能造成大范围的锁表：

  • 当查询没命中索引，走主键索引树做全表扫描，且对主键索引树上扫描的行都加行锁和 gap 锁。如果是更新型当前读，行锁用的是写锁，这会大大降低数据库的并发度。所以当前读尽量要命中索引，减少 gap 锁和行锁的范围

InnoDB

知识点

1、InnoDB 中 changeBuffer 的作用是？changeBuffer 适用于读多还是写多的场景？

  • 写操作时，如果数据页未加载到内存中，会将数据变更先缓存在 changeBuffer
  • 写多场景，changeBuffer 可以缓存同一数据页中的大量变更，数据页被加载到内存后，再更新到数据页，这种方式效率最高。所以适合于写多场景

2、InnoDB 是如何实现数据更新的？

  • 如果数据页加载到内存，写数据页，事务提交后写 redoLog
  • 如果数据页未加载到内存，写 changeBuffer，事务提交后写 redoLog

3、什么是脏页？

  • 内存里的数据页还未刷入磁盘时，会出现内存磁盘数据不一致，称内存中的数据页为脏页

4、哪些场景下会执行脏页 flush 到磁盘？

  • redoLog 写满
  • 内存满，无法加载进新的数据页
  • 数据库不繁忙时
  • 数据库正常关闭时

5、为什么脏页 flush 时感觉 MySQL 性能有抖动

  • 因为需要将脏页刷入磁盘，涉及到大量 IO 操作

6、使用唯一索引还是普通索引在写操作时性能更好？为什么？

  • 普通索引在写操作时性能更好。因为唯一索引要先校验记录唯一性，需要额外从磁盘加载数据页到内存，增加了大量 IO 开销

7、InnoDB 加载新数据页到内存时，需要淘汰旧数据页，使用的是什么算法？

  • LRU（最近最少使用），链表实现，最近访问的数据页放在头结点，淘汰尾结点

8、对于历史数据表的全表扫描，会加载大量数据页到内存，影响查询时的内存命中率，LRU 算法针对这种场景是如何优化的？

  • 链表按 5 比 3 分为 Young 区和 Old 区，Young 在前。最近被加载的数据页先放在 Old 区，如果数据页下次被访问超过 1 秒，则移到 Young 区，这样临时加载的数据页因为不会二次访问，所以一直停在 Old 区，很快被淘汰，保证了内存缓存的都是活跃的数据页，提高查询时的内存命中率

9、InnoDB 是如何实现数据删除的？

  • 只是逻辑删除，被标记删除的记录所在空间可以被后续新增记录复用

10、哪些场景下可能造成存储空间的空洞？有什么解决办法？

  • 被逻辑删除的空间未被复用时产生空洞，这通常在插入的新数据无法存储在现有数据页，而进行页分裂时产生空洞
  • 解决存储空洞的方法是重建表，即创建一个新表，按主键顺序重新插入一遍数据到新表

11、什么是 OnlineDDL？

  • DDL 期间对客户端无感知，依然可以正常执行 CRUD

12、MyISAMcount*查询直接使用磁盘上记录的表的总行数，为什么 InnoDB 不行？

  • MyISAM 不支持事务，所以可以简单地把总行数记在磁盘上。InnoDB 支持事务，不同事务执行期间因为存在事务隔离机制，所以 count*查询结果可能不同，因此不能简单地把总行数记在磁盘上

13、InnoDB 如何实现 count 查询？count 的字段不同，性能上有什么差别？

  • InnoDB 会扫全表并筛选事务可读的所有记录，由 Server 层统计行数后返给客户端
  • count*和 count1 都是性能最优的 count 查询，因为不涉及到记录上的字段解析和拷贝。count 字段需要字段解析和拷贝，所以性能不好

14、如何解决表很大时，count 操作的性能问题？

  • 表里存储总行数，插入操作和总行数+1 做到一个事务里，尽量先插入后总行数+1，因为基于事务的二阶段提交协议，并发高的操作放到后面能尽可能减少锁竞争对事务的影响

15、对于 order by 操作，InnoDB 的全字段排序和 rowId 排序各有什么优劣？什么条件下优先使用 rowId 排序？

  • 全字段排序需要排序内存空间足够大，rowId 排序每行记录只包括主键 id 和排序字段，对排序内存空间要求低，但排序后需要二次回表取完整字段数据，性能不如全字段排序
  • 源表是内存临时表时，rowId 排序后，二次回表拿数据不走磁盘，直接从内存取，性能基本不受影响，这时优先使用 rowId 排序

16、什么条件下，order by 操作对查询到的结果集不需要再做排序？

  • 查询条件+orderBy 命中联合索引时，普通索引树上就已经是排序后的结果了，无需再做排序。另外，如果查询又用到了索引覆盖，还不用回表，这种场景性能最优

17、全表扫描会不会打满 MySQL 的数据库内存？

  • 不会，MySQL 服务端全表扫描时，采取边读边发的方式处理读到的记录，每次发送的记录数取决于网络带宽。所以不是全部读完再一次性发给客户端，不会产生 OOM

正文

InnoDB 更新(写)操作

在 InnoDB 引擎中，读取操作会将数据以数据页的维度从磁盘读取到内存，每个数据页默认 16KB。
更新操作时，如果数据页在内存中，直接更新内存，如果不在内存中，将更新操作缓存在一块称为changeBuffer的内存中，下次读取到数据页时，再执行更新操作到数据页中，称为merge

changeBuffer 适用于写多读少的场景，因为在 merge 前，buffer 的更新操作显然是越多越好，如果是读写平均或者写少读多的场景，写完之后立刻再查询，不仅要频繁加载数据页到内存，还多了 merge 操作的代价，changeBuffer 反而起了副作用

无论是直接更新内存中的数据页，还是写 changeBuffer，都会把这些内存操作记录在 redoLog，这就是为什么 redoLog 具备crash-safe的能力，当数据库 crash 后重启并从磁盘加载数据页到内存后，依然能通过 redoLog 找回之前未执行到磁盘的操作，不会丢数据

因为这套内存缓存机制，内存中的数据页和磁盘中的数据页会出现不一致，称内存中的数据页为脏页。数据库会在一些情况下将内存数据页刷入磁盘，称为flush
执行 flush 操作时会影响当前时刻的 SQL 执行效率，感觉就像是 MySQL 抖了一下，因此需要清楚哪些时刻会 flush：

  • redoLog 写满时，需要预留日志空间，所以 redoLog 对应的更新操作需要 flush 到磁盘，redoLog 才能“放心地擦除这些日志”。注意，redoLog 写满时，所有更新操作都会阻塞，这也很好理解，因为写 redoLog 会失败
  • 内存满时（通常是一个查询需要加载新的数据页造成），需要清空一些内存，对于脏页，就需要执行 flush，这时对于查询操作响应时间明显边长
    • 注意，这种脏页淘汰产生的 flush，不会擦除 redoLog 对应的日志（因为可能散落在不同的位置，实现很麻烦）。基于 redoLog 在重放时，会跳过已经 flush 到磁盘的操作
  • 数据库在认为不繁忙时，会执行 flush
  • 数据库正常关闭时，所有脏页都需要 flush

对于脏页 flush 产生的 MySQL 抖动性能问题，数据库提供了脏页 flush 的速率控制参数，可以综合磁盘的性能设置一个合理值来尽可能降低 flush 对性能的影响。同时 InnoDB 也会基于 redoLog 的写入速率和当前脏页比例，动态调整 flush 速率，在 redoLog 写入速率较快或脏页占比高时，会增加 flush 速率。

唯一索引和普通索引在查询时性能表现几乎一致，只不过普通索引在查询成功后，会多做一些指针移动和计算，寻找后续的记录是否也满足条件，这对于现代 CPU 来说不会有任何性能问题，但唯一索引和普通索引在数据更新时的性能有明显差距
对于使用唯一索引的更新操作，因为要校验唯一性，所以必然要将数据页从磁盘读取到内存中，这涉及到随机 IO 访问，是数据库成本最高的操作之一！校验无误后，更新内存中的数据页
由此可知，唯一索引的更新不会使用changeBuffer，但对于普通索引的更新，即使要更新的记录不在数据页中，可以使用 changeBuffer 避免加载数据页，因此在性能上优于唯一索引
因此，一般情况下，使用 changeBuffer 机制+普通索引，在更新操作的性能表现上优于唯一索引。如果业务不依赖数据库做唯一校验，推荐使用普通索引而非唯一索引

InnoDB 查询(读)操作

查询请求在 InnoDB 层会优先查询BufferPool管理的内存数据页，使用内存命中率来表示可从 BufferPool 直接查到的概率，一般线上系统要稳定在 99%以上，BufferPoolSize 一般设为可用物理内存的 60%~80%
当查询不能命中内存时，就需要走磁盘，并将新的数据页加载到内存，同时淘汰一个旧数据页，淘汰算法使用的是LRU（LeastRecentUsed，最近最少使用），链表实现，头结点表示最近一次访问到的数据页

但如果做全表扫描一个历史数据表（平时没有查询调用），那么查询过程中就会有很多这张表的数据页被加载到 BufferPool，这必然造成内存命中率急剧下降，因此 InnoDB 对 LRU 算法做了优化：
链表按 5:3 比例分为 Young 区和 Old 区，Young 在前。每次需要加载到 BufferPool 的数据页，会被放在 Old 区，如果下一次访问数据页的时间间隔超过 1 秒（默认），则移到 Young 区，否则还是在 Old 区。这就保障了临时查询的数据页一直在 Old 区，很快会被替换掉。这样大表扫描的数据页只会短暂存在 Old 区，对内存命中率不会产生过度影响

InnoDB 删除操作

delete 操作不会对磁盘上的记录做物理删除(表文件大小不变)，只会标记为删除（逻辑删除），后续新增的记录可以复用这条记录的位置
由此可知，delete 后标记为“删除”的空间，如果未被复用，就会成为空洞
另一方面，插入数据也可能造成空洞，当数据页不能存放新增的记录时，会进行页分裂成两个数据页，分裂出的新数据页会存储原数据页一部分数据，因此原数据页的这部分空间如果未被再利用，就会成为空洞
解决空洞的办法是重建表，命令是alter table t engine=InnoDB,algorithm=inplace，这是一个 DDL 操作。因为重建表会按主键顺序重新执行一遍插入操作而非业务过程中的随机插入、删除，所以不存在页分裂和逻辑删除，也就不存在空洞
InnoDB 在重建表时，先加 MDL 读锁防止其他 DDL 操作（DML 操作加的也是 MDL 读锁，所以依然能增删改查），然后生成一个临时文件存储表中的所有数据，生成过程中，对原表所有操作记录在 rowLog 日志文件，临时文件生成后，将 rowLog 所有操作应用到临时文件，最后用临时文件代替原表的数据文件，整个过程对客户端来说是无感知的，重建表期间依然可以进行增删改查，因此这种方式又称为Online DDL

inplace 和 online 的区别
InnoDB 使用的这种 online 重建表，使用的临时文件也是 InnoDB 生成的，所以也是inplace（原地操作）的解决方案。另一种重建表的方式是在 Server 层新建一个真正的拷贝表（altable table t engine=InnoDB,algorithm=copy），这种方式不是 online 的，需要阻塞增删改查操作。但 inplace 方式和 online 效果并不是一定等价的，有些 inplace 方式的操作也不是 online 的

count 操作的性能分析

MyISAM 因为不支持事务，所以可以很简单地把表的总行数写在磁盘上，count()时直接返回即可
对于支持事务的 InnoDB，因为事务中的读操作是事务隔离的，因此每个事务在 MVCC 的实现里，当前时刻可读取的表总行数可能是不同的。例如事务 A 查询 count()，而事务 B 同时新增了一条记录后查询 count(*)，在可重复度隔离级别下，事务 A 查到的总记录数就比事务 B 少一个。所以 InnoDB 不能简单地把总行数记在磁盘上

那么 InnoDB 是如何实现 count 查询呢？
InnoDB会扫全表并筛选事务可读的所有记录，由Server层统计行数后返给客户端，这就是为什么当表很大时，count 操作会有性能问题
count 的字段不同，也会有性能差距，按性能从优到差排序后如下：

  • count(*)，InnoDB 并不会把全部字段取出，而是专门做了优化，不取值，省去了整行数据解析和字段拷贝操作，性能是最优的
  • count(1)，每行记录都替换为 1 后返给 Server 层，因此性能和 count(*)基本相同
  • count(主键 id)，因为主键 id 不可能为空，则不需要对字段进行判空，把每行记录的 id 取出后返给 Server 层，需要数据解析和字段拷贝
  • count(字段)，因为字段可能为空，所以相对于 count(主键 id)，需要对扫到的字段进行额外判空校验，通过后将字段返给 Server 层，需要数据解析和字段拷贝

所以结论是推荐使用 count(*)
随着数据变多，无法接受 count 操作的性能问题时，可以将行数存储在表里，每次插入操作后该值+1，这两步做到一个事务里，由事务隔离的数据读取一致性保证事务间查询 count 操作的正确性（不会出现事务能读到 N+1 行，但只能 count 出 N 行，类似这种 case）。
另外，事务里先插入后更新行数还是反过来呢？基于行锁的二阶段协议，应该后执行并发高的更新行数操作，最大程度减少事务里阻塞在行锁的时间

order by 操作的性能分析

对使用”order by field”的查询，MySQL 会对满足条件的数据集用一块sort buffer内存空间做排序（快排），当 sort buffer 不足时，会使用磁盘上的临时文件排序（归并排序）
排序时每行记录有多少字段，取决于排序内存大小，原则是排序时尽量全字段排序，这样排序后的结果直接返给 Server 层，性能较优。否则，使用rowid排序，即每行记录只有要排序的字段和主键 id，排序后还需要再回表拿到需要的字段数据，增加了磁盘访问次数，性能较差

不过有时也不需要对结果集再做排序，当查询条件+orderby 命中联合索引时，从索引树上就能拿到排序后的结果，只要回表拿到数据就能直接返回。如果查询利用了索引覆盖，还可以省去回表，性能是最优的

全字段排序相比于 rowid 排序，省去了二次回表的性能损耗，但如果回表不需要磁盘访问，例如源表是内存临时表，那么回表的性能损耗是很低的（只针对内存临时表，如果数据量更大时使用磁盘临时表，回表依然有性能损耗），这时rowid排序相比全字段排序更省空间，所以内存临时表的排序就优先使用 rowid 排序
例如 order by rand()随机排序，会使用临时表存储对要查询的字段调用 rand 函数生成的随机数，然后对临时表应用上面的排序流程。不过对于随机查询，不建议使用这种方式，因为涉及到生成临时表、随机数、排序，可以有一种性能更优的替代方案：count(*)取行数 N，基于行数计算随机数 X，用 limit X,1 查询到随机行，这种方式只会扫描 N+X+1 行

全表扫描做了些什么

当一个表很大时，做全表扫描会不会把数据库内存打满造成 OOM？必然不会，虽然做全表扫描查询，但 MySQL 服务端是边读边发的，每次查询的数据量依赖于网络通信的可传输 buffer 大小，所以不会造成 OOM
但这种方式对客户端是有要求的，默认客户端使用 mysql_store_result 接口，会将查询结果放入本地内存后就返回成功，但如果客户端使用 mysql_use_result 接口，会读一行处理一行，当处理逻辑耗时较长，导致服务端结果发不出去，就变成了长事务