windtreeland

Appearance

架构 ​

  1. 连接器：连接器负责跟客户端建立连接、获取权限、维持和管理连接。

    • 一个用户成功建立连接后，即使你用管理员账号对这个用户的权限做了修改，也不会影响已经存在连接的权限。修改完成后，只有再新建的连接才会使用新的权限
    • 客户端如果太长时间没动静，连接器就会自动将它断开。这个时间是由参数 wait_timeout 控制的，默认值是 8 小时。
    • 全部使用长连接后，你可能会发现，有些时候 MySQL 占用内存涨得特别快，这是因为 MySQL 在执行过程中临时使用的内存是管理在连接对象里面的。这些资源会在连接断开的时候才释放。所以如果长连接累积下来，可能导致内存占用太大，被系统强行杀掉（OOM），从现象看就是 MySQL 异常重启了。
      1. 定期断开长连接。使用一段时间，或者程序里面判断执行过一个占用内存的大查询后，断开连接，之后要查询再重连。
      2. 如果你用的是 MySQL 5.7 或更新版本，可以在每次执行一个比较大的操作后，通过执行 mysql_reset_connection 来重新初始化连接资源。这个过程不需要重连和重新做权限验证，但是会将连接恢复到刚刚创建完时的状态。

  2. 查询缓存

    对于更新压力大的数据库来说，查询缓存的命中率会非常低。除非你的业务就是有一张静态表，很长时间才会更新一次。比如，一个系统配置表，那这张表上的查询才适合使用查询缓存。

  3. 分析器 词法分析,语法分析

  4. 优化器 优化器是在表里面有多个索引的时候，决定使用哪个索引；或者在一个语句有多表关联（join）的时候，决定各个表的连接顺序。

  5. 执行器 开始执行的时候，要先判断一下你对这个表 T 有没有执行查询的权限，如果有权限，就打开表继续执行。

Write-Ahead Logging：WAL ​

先写日志，再写磁盘

redo log ​

具体来说，当有一条记录需要更新的时候，InnoDB 引擎就会先把记录写到 redo log 里面，并更新内存，这个时候更新就算完成了。

InnoDB 的 redo log 是固定大小的，比如可以配置为一组 4 个文件，每个文件的大小是 1GB，那么这块“粉板”总共就可以记录 4GB 的操作。从头开始写，写到末尾就又回到开头循环写，如下面这个图所示。

有了 redo log，InnoDB 就可以保证即使数据库发生异常重启，之前提交的记录都不会丢失，这个能力称为 crash-safe

写入机制 ​

redo log 可能存在的三种状态 ​

  1. 存在 redo log buffer 中，物理上是在 MySQL 进程内存中
  2. 写到磁盘 (write)，但是没有持久化（fsync)，物理上是在文件系统的 page cache 里面
  3. 持久化到磁盘，对应的是 hard disk

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 也是直接写在 redo log buffer 中的，这些 redo log 也会被后台线程一起持久化到磁盘。也就是说，一个没有提交的事务的 redo log，也是可能已经持久化到磁盘的。
除了后台线程每秒一次的轮询操作外，还有两种场景会让一个没有提交的事务的 redo log 写入到磁盘中。

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

binlog ​

redo log 是 InnoDB 引擎特有的日志，而 Server 层也有自己的日志，称为 binlog（归档日志）

写入机制 ​

事务执行过程中，先把日志写到 binlog cache，事务提交的时候，再把 binlog cache 写到 binlog 文件中。每个线程一个，参数 binlog_cache_size 用于控制单个线程内 binlog cache 所占内存的大小。如果超过了这个参数规定的大小，就要暂存到磁盘。事务提交的时候，执行器把 binlog cache 里的完整事务写入到 binlog 中，并清空 binlog cache

write, fsync ​
  1. write, 是指把日志写入到文件系统的 page cache，并没有把数据持久化到磁盘，所以速度比较快。
  2. fsync，才是将数据持久化到磁盘的操作。一般情况下，我们认为 fsync 才占磁盘的 IOPS。 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 日志。

组提交（group commit）机制 ​

日志逻辑序列号（log sequence number，LSN）的概念。LSN 是单调递增的，用来对应 redo log 的一个个写入点。每次写入长度为 length 的 redo log， LSN 的值就会加上 length。

LSN 也会写到 InnoDB 的数据页中，来确保数据页不会被多次执行重复的 redo log

redo log 和 binlog 区别 ​

  1. redo log 是 InnoDB 引擎特有的；binlog 是 MySQL 的 Server 层实现的，所有引擎都可以使用。
  2. redo log 是物理日志，记录的是“在某个数据页上做了什么修改”；binlog 是逻辑日志，记录的是这个语句的原始逻辑，比如“给 ID=2 这一行的 c 字段加 1 ”。
  3. redo log 是循环写的，空间固定会用完； binlog 是可以追加写入的。“追加写”是指 binlog 文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。

两阶段提交 ​

如果不使用“两阶段提交”，那么数据库的状态就有可能和用它的日志恢复出来的库的状态不一致。 redo log 和 binlog 都可以用于表示事务的提交状态，而两阶段提交就是让这两个状态保持逻辑上的一致。

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

  1. 如果在写入 redo log 处于 prepare 阶段之后、写 binlog 之前，发生了崩溃（crash），由于此时 binlog 还没写，redo log 也还没提交，所以崩溃恢复的时候，这个事务会回滚。这时候，binlog 还没写，所以也不会传到备库。
  2. binlog 写完，redo log 还没 commit 前发生 crash，那崩溃恢复的时候 MySQL 会怎么处理？
    1. 如果 redo log 里面的事务是完整的，也就是已经有了 commit 标识，则直接提交；
    2. 如果 redo log 里面的事务只有完整的 prepare，则判断对应的事务 binlog 是否存在并完整

MySQL 怎么知道 binlog 是完整的? ​

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

redo log 和 binlog 是怎么关联起来的 ​

  1. 它们有一个共同的数据字段，叫 XID。崩溃恢复的时候，会按顺序扫描 redo log
    1. 如果碰到既有 prepare、又有 commit 的 redo log，就直接提交；
    2. 如果碰到只有 prepare、而没有 commit 的 redo log，就拿着 XID 去 binlog 找对应的事务。

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

  1. binlog 没有能力恢复“数据页”。
  2. InnoDB 引擎使用的是 WAL 技术，执行事务的时候，写完内存和日志，事务就算完成了。如果之后崩溃，要依赖于日志来恢复数据页。

那能不能反过来，只用 redo log，不要 binlog？ ​

  1. 一个是归档。redo log 是循环写，写到末尾是要回到开头继续写的。这样历史日志没法保留，redo log 也就起不到归档的作用。
  2. 一个就是 MySQL 系统依赖于 binlog。binlog 作为 MySQL 一开始就有的功能，被用在了很多地方。其中，MySQL 系统高可用的基础，就是 binlog 复制。

正常运行中的实例，数据写入后的最终落盘，是从 redo log 更新过来的还是从 buffer pool 更新过来的呢？ ​

实际上，redo log 并没有记录数据页的完整数据，所以它并没有能力自己去更新磁盘数据页，也就不存在“数据最终落盘，是由 redo log 更新过去”的情况。

如果是正常运行的实例的话，数据页被修改以后，跟磁盘的数据页不一致，称为脏页。最终数据落盘，就是把内存中的数据页写盘。这个过程，甚至与 redo log 毫无关系。

在崩溃恢复场景中，InnoDB 如果判断到一个数据页可能在崩溃恢复的时候丢失了更新，就会将它读到内存，然后让 redo log 更新内存内容。更新完成后，内存页变成脏页，就回到了第一种情况的状态。

redo log buffer 是什么？是先修改内存，还是先写 redo log 文件？ ​

redo log buffer 就是一块内存，用来先存 redo 日志的。也就是说，在执行第一个 insert 的时候，数据的内存被修改了，redo log buffer 也写入了日志。

但是，真正把日志写到 redo log 文件（文件名是 ib_logfile+ 数字），是在执行 commit 语句的时候做的。

脏页 ​

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

什么情况会引发数据库的 flush 过程 ​

  1. InnoDB 的 redo log 写满了。这时候系统会停止所有更新操作，把 checkpoint 往前推进，redo log 留出空间可以继续写。
  2. 系统内存不足。当需要新的内存页，而内存不够用的时候，就要淘汰一些数据页，空出内存给别的数据页使用。如果淘汰的是“脏页”，就要先将脏页写到磁盘。
  3. MySQL 认为系统“空闲”的时候。也要见缝插针地找时间，只要有机会就刷一点“脏页”。
  4. MySQL 正常关闭的情况。这时候，MySQL 会把内存的脏页都 flush 到磁盘上，这样下次 MySQL 启动的时候，就可以直接从磁盘上读数据，启动速度会很快。

对性能的影响 ​

  1. “redo log 写满了，要 flush 脏页”，这种情况是 InnoDB 要尽量避免的。因为出现这种情况的时候，整个系统就不能再接受更新了，所有的更新都必须堵住。如果你从监控上看，这时候更新数会跌为 0。
  2. “内存不够用了，要先将脏页写到磁盘”，这种情况其实是常态。InnoDB 用缓冲池（buffer pool）管理内存
  3. 情况是属于 MySQL 空闲时的操作，这时系统没什么压力;数据库本来就要关闭了。这两种情况下，你不会太关注“性能”问题。

InnoDB 刷脏页的控制策略 ​

首先，你要正确地告诉 InnoDB 所在主机的 IO 能力，这样 InnoDB 才能知道需要全力刷脏页的时候，可以刷多快。这就要用到 innodb_io_capacity 这个参数了，它会告诉 InnoDB 你的磁盘能力。这个值我建议你设置成磁盘的 IOPS。磁盘的 IOPS 可以通过 fio 这个工具来测试，下面的语句是我用来测试磁盘随机读写的命令

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

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

  • 参数 innodb_max_dirty_pages_pct 是脏页比例上限，默认值是 75%。
  • InnoDB 会根据当前的脏页比例（假设为 M），算出一个范围在 0 到 100 之间的数字。
  • nnoDB 每次写入的日志都有一个序号，当前写入的序号跟 checkpoint 对应的序号之间的差值，我们假设为 N。
  • InnoDB 会根据这个 N 算出一个范围在 0 到 100 之间的数字，这个计算公式可以记为 F2(N)。
  • F2(N) 算法比较复杂，你只要知道 N 越大，算出来的值越大就好了。根据上述算得的 F1(M) 和 F2(N) 两个值，取其中较大的值记为 R，之后引擎就可以按照 innodb_io_capacity 定义的能力乘以 R% 来控制刷脏页的速度。

要合理地设置 innodb_io_capacity 的值，并且平时要多关注脏页比例，不要让它经常接近 75%。

储存引擎 ​

InnoDB ​

  • 支持事务，行锁，崩溃恢复
  • 聚簇索引

MyISAM ​

  • 非聚簇索引

Memory ​

  • Memory表至少比MyISAM 表要快一个数量级，数据文件是存储在内存中。Memory表的结构在重启以后还会保留，但数据会丢失。
  • Memory表支持 Hash索引，因此查找操作非常快。Memroy表是表级锁，因此并发写入的性能较低，每行的长度是固定的，可能导致部分内存的浪费。

事务 ​

acid ​

隔离性 ​

  • 未提交-脏读
  • 提交读-不可重复读
  • 可重复读-幻读

问题 ​

  1. 破坏语义上的加锁声明
  2. 数据一致性的问题：日志数据不一致（事务 commit 后才提交到日志，而其他会话可能在在该事务update后先插入了一行，这一行会日志会在update前），不论是拿到备库去执行，还是以后用 binlog 来克隆一个库，可能会造成数据异常

解决方法 ​

  1. 间隙锁 (Gap Lock)
    • 7个间隙锁
    • 跟间隙锁存在冲突关系的，是“往这个间隙中插入一个记录”这个操作。间隙锁之间都不存在冲突关系。
    • 间隙锁和行锁合称 next-key lock，如果用 select * from t for update 要把整个表所有记录锁起来，就形成了 7 个 next-key lock，分别是 (-∞,0]、(0,5]、(5,10]、(10,15]、(15,20]、(20, 25]、(25, +supremum]。
    • 读提交隔离级别 加 binlog_format=row
    • 间隙锁的引入，可能会导致同样的语句锁住更大的范围，这其实是影响了并发度的。
  2. 读提交隔离级别 加 binlog_format=row
  • 串行化

mvcc ​

MVCC是乐观锁的一种实现方式，它在很多情况下，避免了加锁操作，降低了开销；既然是基于多版本，即快照读可能读到的并不一定是数据的最新版本，而有可能是之前的历史版本。

底层实现原理 ​

MVCC实现原理主要是依赖记录中的隐式字段，undo日志，Read View 来实现的。 ​
  1. 每行数据也都是有多个版本的。每次事务更新数据的时候，都会生成一个新的数据版本，并且把 transaction id 赋值给这个数据版本的事务 ID，记为 row trx_id。同时，旧的数据版本要保留，并且在新的数据版本中，能够有信息可以直接拿到它
  2. undo log；而 V1、V2、V3 并不是物理上真实存在的，而是每次需要的时候根据当前版本和 undo log 计算出来的。比如，需要 V2 的时候，就是通过 V4 依次执行 U3、U2 算出来。
  3. 按照可重复读的定义，一个事务启动的时候，能够看到所有已经提交的事务结果。但是之后，这个事务执行期间，其他事务的更新对它不可见。
  4. 在实现上， InnoDB 为每个事务构造了一个数组，用来保存这个事务启动瞬间，当前正在“活跃”的所有事务 ID。“活跃”指的就是，启动了但还没提交。
  5. 数组里面事务 ID 的最小值记为低水位，当前系统里面已经创建过的事务 ID 的最大值加 1 记为高水位。
  6. 这个视图数组和高水位，就组成了当前事务的一致性视图（read-view）
  7. 而数据版本的可见性规则，就是基于数据的 row trx_id 和这个一致性视图的对比结果得到的。
  8. InnoDB 利用了“所有数据都有多个版本”的这个特性，实现了“秒级创建快照”的能力。

版本链 ​

读提交的逻辑和可重复读的逻辑类似，它们最主要的区别是 ​

  • 在可重复读隔离级别下，只需要在事务开始的时候创建一致性视图，之后事务里的其他查询都共用这个一致性视图
  • 在读提交隔离级别下，每一个语句执行前都会重新算出一个新的视图。

长事务意味着系统里面会存在很老的事务视图。由于这些事务随时可能访问数据库里面的任何数据，所以这个事务提交之前，数据库里面它可能用到的回滚记录都必须保留，这就会导致大量占用存储空间;除了对回滚段的影响，长事务还占用锁资源，也可能拖垮整个库 ​

间隙锁 ​

索引 ​

数据结构 ​

  1. 哈希表是一种以键 - 值（key-value）存储数据的结构，我们只要输入待查找的键即 key，就可以找到其对应的值即 Value。哈希的思路很简单，把值放在数组里，用一个哈希函数把 key 换算成一个确定的位置，然后把 value 放在数组的这个位置。缺点是，因为不是有序的，所以哈希索引做区间查询的速度是很慢的。哈希表这种结构适用于只有等值查询的场景
  2. 有序数组在等值查询和范围查询场景中的性能就都非常优秀。但是，在需要更新数据的时候就麻烦了，你往中间插入一个记录就必须得挪动后面所有的记录，成本太高。有序数组索引只适用于静态存储引擎
  3. 多叉树就是每个节点有多个儿子，儿子之间的大小保证从左到右递增。二叉树是搜索效率最高的，但是实际上大多数的数据库存储却并不使用二叉树。其原因是，索引不止存在内存中，还要写到磁盘上。

MySQL的索引要使用B+树而不是B树？ ​

而 B 树和B+树的最大区别就是，B 树不管叶子节点还是非叶子节点，都会保存数据，这样导致在非叶子节点中能保存的指针数量变少（有些资料也称为扇出），指针少的情况下要保存大量数据，只能增加树的高度，导致 IO 操作变多，查询性能变低。

InnoDB一棵B+树可以存放多少行数据？ ​

  • InnoDB 存储引擎也有自己的最小储存单元——页（Page），一个页的大小是 16K。Innodb 的所有数据文件（后缀为 ibd 的文件），他的大小始终都是 16384（16k）的整数倍。
  • 假设主键 ID 为 常用的bigint 类型，长度为 8 字节，而指针大小在 InnoDB 源码中设置为 6 字节，这样一共 14 字节，即 非叶子节点可以存放 16384/14=1170个。
  • 假设一行数据的大小是 1k，那么一个页可以存放 16 行这样的数据。
  • 那么可以算出一棵高度为2的B+树，存在一个根节点和若干个叶子节点能存放 117016=18720 条这样的数据记录;根据同样的原理我们可以算出一个高度为 3 的 B+树可以存放：11701170*16=21902400条这样的记录;所以在 InnoDB 中 B+ 树高度一般为 1-3 层，就能满足千万级的数据存储。

索引维护 ​

  • 主键乱序插入
  • 页分裂，页合并

MySQL 有时会选错索引 ​

优化器的逻辑

  • 优化器选择索引的目的，是找到一个最优的执行方案，并用最小的代价去执行语句。在数据库里面，扫描行数是影响执行代价的因素之一。扫描的行数越少，意味着访问磁盘数据的次数越少，消耗的 CPU 资源越少。
  • 扫描行数并不是唯一的判断标准，优化器还会结合是否使用临时表、是否排序等因素进行综合判断。

索引选择异常和处理 ​

  • 采用 force index 强行选择一个索引 force index 最主要的问题还是变更的及时性。因为选错索引的情况还是比较少出现的，所以开发的时候通常不会先写上 force index。而是等到线上出现问题的时候，你才会再去修改 SQL 语句、加上 force index。但是修改之后还要测试和发布，对于生产系统来说，这个过程不够敏捷。
  • 考虑修改语句，引导 MySQL 使用我们期望的索引。
  • 新建一个更合适的索引，来提供给优化器做选择，或删掉误用的索引。

锁 ​

全局锁 ​

表级锁 ​

  • 表锁的语法是 lock tables … read/write
  • 另一类表级的锁是 MDL（metadata lock)
    • 当对一个表做增删改查操作的时候，加 MDL 读锁；当要对表做结构变更操作的时候，加 MDL 写锁
      • 给一个小表加个字段，导致整个库挂了。给一个表加字段，或者修改字段，或者加索引，需要扫描全表的数据。
    • 如何安全地给小表加字段
      • 首先我们要解决长事务，事务不提交，就会一直占着 MDL 锁。在 MySQL 的 information_schema 库的 innodb_trx 表中，你可以查到当前执行中的事务。如果你要做 DDL 变更的表刚好有长事务在执行，要考虑先暂停 DDL，或者 kill 掉这个长事务。
      • 比较理想的机制是，在 alter table 语句里面设定等待时间，如果在这个指定的等待时间里面能够拿到 MDL 写锁最好，拿不到也不要阻塞后面的业务语句，先放弃。之后开发人员或者 DBA 再通过重试命令重复这个过程。
        • MariaDB 已经合并了 AliSQL 的这个功能，所以这两个开源分支目前都支持 DDL NOWAIT/WAIT n 这个语法。

行锁 ​

两阶段锁 ​

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

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

死锁和死锁检测 ​

  • 一种策略是，直接进入等待，直到超时。这个超时时间可以通过参数 innodb_lock_wait_timeout 来设置。
  • 另一种策略是，发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数 innodb_deadlock_detect 设置为 on，表示开启这个逻辑。

实战 ​

表设计 ​

  • 更小的通常更好，应该尽量使用可以正确存储数据的最小数据类型。
  • 对于货币记录，应该选择DECIMAL类型，但是DECIMAL类型是以字符串形式存放的，所以性能会有影响。 作为替代方案，可以在数据量比较大的而且要求精度时，虑使用BIGINT代替DECIMAL，将需要存储的货币单位根据小数的位数乘以相应的倍数即可。

覆盖索引 ​

最左前缀原则 ​

在建立联合索引的时候，如何安排索引内的字段顺序。

  • 第一原则是，如果通过调整顺序，可以少维护一个索引，那么这个顺序往往就是需要优先考虑采用的。
  • 考虑的原则就是空间

索引下推 ​

可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

表数据删除 ​

  • innodb_file_per_table
    • 这个参数设置为 OFF 表示的是，表的数据放在系统共享表空间，也就是跟数据字典放在一起
    • 这个参数设置为 ON 表示的是，每个 InnoDB 表数据存储在一个以 .ibd 为后缀的文件中。 我建议你不论使用 MySQL 的哪个版本，都将这个值设置为 ON。因为，一个表单独存储为一个文件更容易管理，而且在你不需要这个表的时候，通过 drop table 命令，系统就会直接删除这个文件。而如果是放在共享表空间中，即使表删掉了，空间也是不会回收的。
  • 数据删除流程
    • InnoDB 引擎只会把记录标记为删除。如果之后要再插入一个 ID 在 300 和 600 之间的记录时，可能会复用这个位置。但是，磁盘文件的大小并不会缩小。
    • 数据页的复用跟记录的复用是不同的。如果相邻的两个数据页利用率都很小，系统就会把这两个页上的数据合到其中一个页上，另外一个数据页就被标记为可复用。但是磁盘上，文件不会变小
    • 不止是删除数据会造成空洞，插入数据也会。
      • 如果数据是按照索引递增顺序插入的，那么索引是紧凑的。但如果数据是随机插入的，就可能造成索引的数据页分裂。

count(*) ​

  • 实现方式
    • MyISAM 引擎把一个表的总行数存在了磁盘上，因此执行 count(*) 的时候会直接返回这个数，效率很高；
    • 而 InnoDB 引擎就麻烦了，它执行 count(*) 的时候，需要把数据一行一行地从引擎里面读出来，然后累积计数。
  • 为什么 InnoDB 不跟 MyISAM 一样，也把数字存起来呢？
    • 这是因为即使是在同一个时刻的多个查询，由于多版本并发控制（MVCC）的原因，InnoDB 表“应该返回多少行”也是不确定的
    • InnoDB 是索引组织表，主键索引树的叶子节点是数据，而普通索引树的叶子节点是主键值。所以，普通索引树比主键索引树小很多。对于 count(*) 这样的操作，遍历哪个索引树得到的结果逻辑上都是一样的。因此，MySQL 优化器会找到最小的那棵树来遍历。在保证逻辑正确的前提下，尽量减少扫描的数据量，是数据库系统设计的通用法则之一。
  • 不同的 count 用法
    • 按照效率排序的话，count(字段)<count(主键 id)<count(1)≈count(*)
    • 对于 count(主键 id) 来说，InnoDB 引擎会遍历整张表，把每一行的 id 值都取出来，返回给 server 层。server 层拿到 id 后，判断是不可能为空的，就按行累加。
    • 对于 count(1) 来说，InnoDB 引擎遍历整张表，但不取值。server 层对于返回的每一行，放一个数字“1”进去，判断是不可能为空的，按行累加。
      • 如果这个“字段”是定义为 not null 的话，一行行地从记录里面读出这个字段，判断不能为 null，按行累加；
      • 如果这个“字段”定义允许为 null，那么执行的时候，判断到有可能是 null，还要把值取出来再判断一下，不是 null 才累加。
    • count() 是例外，并不会把全部字段取出来，而是专门做了优化，不取值。count() 肯定不是 null，按行累加。

不走索引案例 ​

  • 条件字段函数操作
  • 隐式类型转换
  • 隐式字符编码转换

全表扫描 ​

对 server 层的影响 ​

  • 实际上，服务端并不需要保存一个完整的结果集。取数据和发数据的流程是这样的
    • 获取一行，写到 net_buffer 中。这块内存的大小是由参数 net_buffer_length 定义的，默认是 16k。
    • 重复获取行，直到 net_buffer 写满，调用网络接口发出去。
    • 如果发送成功，就清空 net_buffer，然后继续取下一行，并写入 net_buffer。
    • 如果发送函数返回 EAGAIN 或 WSAEWOULDBLOCK，就表示本地网络栈（socket send buffer）写满了，进入等待。直到网络栈重新可写，再继续发送。
  • 也就是说，MySQL 是“边读边发的”，这个概念很重要。这就意味着，如果客户端接收得慢，会导致 MySQL 服务端由于结果发不出去，这个事务的执行时间变长。

对 InnoDB 的影响 ​

Buffer Pool 对查询的加速效果，依赖于一个重要的指标，即：内存命中率。 InnoDB 内存管理用的是最近最少使用 (Least Recently Used, LRU) 算法

  • 按照 5:3 的比例把整个 LRU 链表分成了 young 区域和 old 区域
  • 状态 1，要访问数据页 P3，由于 P3 在 young 区域，因此和优化前的 LRU 算法一样，将其移到链表头部，变成状态 2。
  • 之后要访问一个新的不存在于当前链表的数据页，这时候依然是淘汰掉数据页 Pm，但是新插入的数据页 Px，是放在 LRU_old 处。
  • 处于 old 区域的数据页，每次被访问的时候都要做下面这个判断：
    • 若这个数据页在 LRU 链表中存在的时间超过了 1 秒，就把它移动到链表头部；
    • 如果这个数据页在 LRU 链表中存在的时间短于 1 秒，位置保持不变。1 秒这个时间，是由参数 innodb_old_blocks_time 控制的。其默认值是 1000，单位毫秒。

join ​

Index Nested-Loop Join ​

  • 怎么选择驱动表
    • 使用 join 语句，性能比强行拆成多个单表执行 SQL 语句的性能要好；
    • 如果使用 join 语句的话，需要让小表做驱动表。

Block Nested-Loop Join (BNL) ​

两个表都做一次全表扫描，所以总的扫描行数是 M+N；内存中的判断次数是 M*N。

  • join_buffer 的大小是由参数 join_buffer_size 设定的，默认值是 256k。如果放不下表 t1 的所有数据话，策略很简单，就是分段放。
    • 在 join_buffer_size 足够大的时候，是一样的；在 join_buffer_size 不够大的时候（这种情况更常见），应该选择小表做驱动表。
  • 在决定哪个表做驱动表的时候，应该是两个表按照各自的条件过滤，过滤完成之后，计算参与 join 的各个字段的总数据量，数据量小的那个表，就是“小表”，应该作为驱动表。

Multi-Range Read 优化 ​

因为大多数的数据都是按照主键递增顺序插入得到的，所以我们可以认为，如果按照主键的递增顺序查询的话，对磁盘的读比较接近顺序读，能够提升读性能。

Batched Key Access ​

对 NLJ 算法的优化;使用 BKA 优化算法的话，你需要在执行 SQL 语句之前，先设置：set optimizer_switch='mrr=on,mrr_cost_based=off,batched_key_access=on';

  • BNL 算法的性能问题 如果一个使用 BNL 算法的 join 语句，多次扫描一个冷表，而且这个语句执行时间超过 1 秒，就会在再次扫描冷表的时候，把冷表的数据页移到 LRU 链表头部。
    • 执行语句之前，需要通过理论分析和查看 explain 结果的方式，确认是否要使用 BNL 算法。如果确认优化器会使用 BNL 算法，就需要做优化。优化的常见做法是，给被驱动表的 join 字段加上索引，把 BNL 算法转成 BKA 算法。
  • BNL 转 BKA
    • 不论是在原表上加索引，还是用有索引的临时表，我们的思路都是让 join 语句能够用上被驱动表上的索引，来触发 BKA 算法，提升查询性能。