修订历史

• 2024.11.24 移出私密

基础架构：一条SQL查询语句是如何执行的

• 客户端
• 连接器：管理连接，权限验证
• 查询缓存：命中则直接返回结果
• 分析器：词法分析，语法分析
• 优化器：执行计划生成，索引选择
• 执行器：操作引擎，返回结果
• 存储引擎：存储数据，提供读写接口

建立连接过程比较复杂，尽量使用长连接。但是长连接长期使用会导致内存占用太大，被 OOM。有两种解决方案：

1. 定期断开长连接
2. 执行 mysql_reset_connection 来重新初始化连接资源（MySQL 5.7+）

只要对一个表的更新，这个表上所有的查询缓存都会被清空。因此不适合更新压力大的数据表。MySQL 8.0 后彻底删除查询缓存功能

如果表 T 中没有字段 k，执行 select * from T where k=1 报的错误是在哪个阶段产生的？

• 答：分析器

日志系统：一条SQL更新语句是如何执行的

redo log 固定大小，是 InnoDB 引擎特有的日志。当有一条记录需要更新的时候，InnoDB 引擎就会先把记录写到 redo log 里面，并更新内存，在适当的时候将操作记录更新到磁盘。

Server 层也有自己的日志，称为 bin log。bin log 记录所有的逻辑操作，用于备份恢复

两种日志有以下三点不同：

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

更新数据的两阶段提交：

1. 写 redo log，prepare 状态
2. 写 bin log
3. redo log 状态变更为 commit

在什么场景下，一天一备会比一周一备更有优势呢？或者说，它影响了这个数据库系统的哪个指标

• 答：恢复目标时间、存储空间

事务隔离：为什么你改了我还看不见

事务实现上，数据库里面会创建一个视图，访问的时候以视图的逻辑结果为准。在“可重复读”隔离级别下，这个视图是在事务启动时创建的，整个事务存在期间都用这个视图。在“读提交”隔离级别下，这个视图是在每个 SQL 语句开始执行的时候创建的。这里需要注意的是，“读未提交”隔离级别下直接返回记录上的最新值，没有视图概念；而“串行化”隔离级别下直接用加锁的方式来避免并行访问。

避免长事务，因为长事务意味着系统里面会存在很老的事务视图。由于这些事务随时可能访问数据库里面的任何数据，所以这个事务提交之前，数据库里面它可能用到的回滚记录都必须保留，这就会导致大量占用存储空间。

作为 ld 如何避免长事务

• 答：
• 1. set autocommit=1
• 1. 确认是否有不必要的只读事务
• 1. SET MAX_EXECUTION_TIME
• 1. 监控 information_schema.Innodb_trx 表，设置长事务阈值

深入浅出索引（上）

根据叶子节点的内容，索引类型分为主键索引和非主键索引：

• 主键索引的叶子节点存的是整行数据。在 InnoDB 里，主键索引也被称为聚簇索引（clustered index）。
• 非主键索引的叶子节点内容是主键的值。在 InnoDB 里，非主键索引也被称为二级索引（secondary index）。

使用非主键索引可能导致回表：基于非主键索引的查询需要多扫描一棵索引树

自增主键的插入数据模式是递增插入，每次插入一条新记录，都是追加操作，都不涉及到挪动其他记录，也不会触发叶子节点的分裂。有业务逻辑的字段做主键，则往往不容易保证有序插入，这样写数据成本相对较高。

主键长度越小，非主键索引的叶子节点就越小，占用的空间也就越小。

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

删除主键还是创建主键，都会将整个表重建

深入浅出索引（下）

覆盖索引:一个索引包含了满足查询结果的数据就叫做覆盖索引,不需要回表等操作。

通过建立联合索引，来支持覆盖索引，避免回表

最左前缀可以是联合索引的最左 N 个字段，也可以是字符串索引的最左 M 个字符。只要满足最左前缀，就可以利用索引来加速检索

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

全局锁和表锁 ：给表加个字段怎么有这么多阻碍

MySQL 提供了一个加全局读锁的方法，命令是 Flush tables with read lock (FTWRL)

官方自带的逻辑备份工具是 mysqldump。当 mysqldump 使用参数–single-transaction 的时候，导数据之前就会启动一个事务，来确保拿到一致性视图。前提是引擎要支持这个隔离级别

MySQL 里面表级别的锁有两种：一种是表锁，一种是元数据锁（meta data lock，MDL）。MDL 不需要显式使用，在访问一个表的时候会被自动加上读写锁，保证变更表结构操作的安全性

事务中的 MDL 锁等到整个事务提交后再释放。如果 MDL 写锁持有时间太长（自身长事务或者阻塞获取 MDL 读锁），会阻塞之后的请求。可以使用 DDL NOWAIT/WAIT n 语法，设定写锁的等待时间

行锁功过：怎么减少行锁对性能的影响

MySQL 的行锁是在引擎层实现的。MyISAM 引擎不支持行锁，InnoDB 支持行锁。

InnoDB中，事务结束才释放行锁。因此如果事务需要锁多行，把并发度最高的锁往后放。

两个事务可能互相等待行锁而发生死锁。有两种解决方法：

• 设置超时时间 innodb_lock_wait_timeout
• 开启死锁检测 innodb_deadlock_detect，发现死锁自动回滚

超时时间默认 50 秒，设置得太短容易误伤。并发量大，死锁检测性能损耗大，注意减少并发数，或者从业务层面减少锁冲突

事务到底是隔离的还是不隔离的

这一小节有些地方没搞明白。todo

begin/start transaction 执行到之后的第一个操作才真正启动事务，start transaction with consistent snapshot 可以马上启动事务

InnoDB 为每个事务构造了一个数组，用来保存这个事务启动瞬间，当前正在“活跃”的所有事务 ID。“活跃”指的就是，启动了但还没提交。数组里面事务 ID 的最小值记为低水位，当前系统里面已经创建过的事务 ID 的最大值加 1 记为高水位。这个视图数组和高水位，就组成了当前事务的一致性视图（read-view）

普通索引和唯一索引，应该怎么选择

更新一个数据页时，如果数据页没有在内存中的话，在不影响数据一致性的前提下，InooDB 会将这些更新操作缓存在 change buffer 中。在下次查询需要访问这个数据页的时候，将数据页读入内存，然后执行 change buffer 中与这个页有关的操作

对于唯一索引来说，所有的更新操作都要先判断这个操作是否违反唯一性约束，这必须要将数据页读入内存才能判断。如果都已经读入到内存了，那直接更新内存会更快

change buffer 只限于用在普通索引的场景下，而不适用于唯一索引。写多读少收益更大。写入之后马上会做查询，change buffer 反而起到了副作用。

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

MySQL为什么有时候会选错索引

一个索引上不同的值越多，这个索引的区分度就越好。一个索引上不同的值的个数，我们称之为“基数”（cardinality）。MySQL 通过采样统计估算索引的基数。

对于由于索引统计信息不准确导致的问题，你可以用 analyze table 来解决。

而对于其他优化器误判的情况，你可以在应用端用 force index 来强行指定索引，也可以通过修改语句来引导优化器，还可以通过增加或者删除索引来绕过这个问题。

怎么给字符串字段加索引

• 直接创建完整索引，这样可能比较占用空间；
• 创建前缀索引，节省空间，但会增加查询扫描次数，并且不能使用覆盖索引；
• 倒序存储，再创建前缀索引，用于绕过字符串本身前缀的区分度不够的问题；
• 创建 hash 字段索引，查询性能稳定，有额外的存储和计算消耗，跟第三种方式一样，都不支持范围扫描。

为什么我的MySQL会“抖”一下

innodb_io_capacity 建议设置成磁盘的 IOPS

innodb_flush_neighbors 在机械硬盘可以减少很多随机 IO。如果使用的是 SSD 这类 IOPS 比较高的设备的话，这时候 IOPS 往往不是瓶颈，innodb_flush_neighbors 设置为 0 能更快地执行完必要的刷脏页操作，减少 SQL 语句响应时间。

为什么表数据删掉一半，表文件大小不变

数据页复用

count(*)这么慢，我该怎么办

新增计数表，并事务更新

“order by”是怎么工作的？