MySQL面试

1 基础

MySQL执行一条Select语句期间发生了什么？

1. 通过连接器建立连接，管理链接、校验用户身份。

2. 查询缓存：命中直接返回。

3. 解析SQL，解析器对SQL查询语句进行词法语法分析。

4. 执行SQL：

   • 预处理：检查表或者字段是否存在；

   • 优化：选择查询成本最小的执行计划；

   • 执行：根据查询计划执行SQL语句，从存储引擎读取数据，返回给客户端。

MySQL 的 NULL 值是怎么存放的？

MySQL 的 Compact 行格式中会用「NULL值列表」来标记值为 NULL 的列，NULL 值并不会存储在行格式中的真实数据部分。

NULL值列表会占用 1 字节空间，当表中所有字段都定义成 NOT NULL，行格式中就不会有 NULL值列表，这样可节省 1 字节的空间。

MySQL 怎么知道 varchar(n) 实际占用数据的大小？

MySQL 的 Compact 行格式中会用「变长字段长度列表」存储变长字段实际占用的数据大小。

varchar(n) 中 n 最大取值为多少？

一行记录最大能存储 65535 字节的数据，但是这个是包含「变长字段字节数列表所占用的字节数」和「NULL值列表所占用的字节数」。所以， 我们在算 varchar(n) 中 n 最大值时，需要减去这两个列表所占用的字节数。

行溢出后，MySQL 是怎么处理的？

如果一个数据页存不了一条记录，InnoDB 存储引擎会自动将溢出的数据存放到「溢出页」中。

Compact 行格式针对行溢出的处理是这样的：当发生行溢出时，在记录的真实数据处只会保存该列的一部分数据，而把剩余的数据放在「溢出页」中，然后真实数据处用 20 字节存储指向溢出页的地址，从而可以找到剩余数据所在的页。

Compressed 和 Dynamic 这两种格式采用完全的行溢出方式，记录的真实数据处不会存储该列的一部分数据，只存储 20 个字节的指针来指向溢出页。而实际的数据都存储在溢出页中。

2 索引

索引的分类

按数据结构分类：B+tree索引、Hash索引、Full-text索引

按物理存储分类：聚簇索引、非聚簇索引

按字段特性分类：主键索引、唯一索引、普通索引、前缀索引

按字段个数：单列索引、联合索引

为什么选择B+Tree作为索引的数据结构？

相比于二叉树，二叉树在相同数据量下层数更高，并且极端情况会退化成链表。

相比于Hash，hash表不适合做范围查询。

相比于Btree，B+Tree旨在叶子节点存放数据，并且用链表连接叶子节点，更适合做范围查询。

什么时候需要索引，什么时候不需要？

需要：字段有唯一性限制，常用于where查询，常用于group by和order by的字段。

不需要：不用于where、group by、order by，字段中重复数据高，表数据太少，经常更新的字段。

优化索引的方法有哪些？

前缀索引优化、覆盖索引优化、索引设置为Not Null、防止索引失效、主键最好是自增的

如何防止索引失效（索引失效有哪些情况）？

• LIKE开头或者开头结尾使用模糊匹配

• 查询条件中对索引列进行了计算、函数、类型转换等操作

• 联合索引没有遵循最左匹配原则

• where中使用or，并且or的条件不全是索引

什么是自适应哈希？

InnoDB存储引擎注意到某些索引值被频繁使用时，会在内存中基于B-Tree索引之上创建一个哈希索引，以加速查找。

3 事务

事务有什么特性？

原子性、一致性、隔离性、持久性

并行事务会出现什么问题？

脏读、不可重复读、幻读

事务隔离级别？

读未提交、读已提交、可重复读、串行化

ReadView在MVCC中如何工作？

ReadView有四个字段：

• creator_trx_id: 创建readview的事务id

• m_ids: 创建readview时，活跃的事务id集合

• min_trx_id: 活跃事务中id最小的事务

• max_trx_id: 活跃事务中id最大的事务+1

聚簇索引两个跟事务相关的隐藏列：

• trx_id：最新对该列修改的事务id

• roll_pouinter：指向上一个版本的记录

可见性：

• trx_id < min_trx_id：对当前事务可见

• trx_id < max_trx_id：对当前事务不可见

• min_trx_id < trx_id < max_trx_id

  • trx_id [in] m_ids：对当前事务不可见

  • trx_id [not in] m_ids：对当前事务可见

可重复读是如何工作的？

先后启动的两个事务，根据自己的ReadView，沿着版本链找到自己应该读取的版本

读提交是如何工作的？

每次读取时，重新创建ReadView。

InnoDB如何解决幻读问题的？

• 快照读：通过MVCC解决了幻读

• 当前读：通过next-key lock解决了幻读，此时其他事务的更新、插入、删除会被阻塞

4 锁

锁的分类

按照加锁范围，可以分为：全局锁、表级锁、行级锁三种

表级锁中有：表锁、元数据锁、意向锁、自增锁

行级锁中有：记录锁、间隙锁、临键锁

什么SQL语句会加锁？

select … lock in share mode

select … for update

update …

delete …

update没加索引会锁全表？

Innodb 源码里面在扫描记录的时候，都是针对索引项这个单位去加锁的， update 不带索引就是全表扫扫描，也就是表里的索引项都加锁，相当于锁了整张表，所以大家误以为加了表锁。

产生死锁怎么办？

• 为什么会产生：InnoDB为了解决可重复读级别下的欢度问题，引入了next-key lock。

  • 当两个事务同时要插入新数据时，都会获取到next-key lock（间隙锁之间相互兼容），后续两个事务都尝试获取插入意向锁，但是插入意向锁和next-lock不兼容，所以阻塞死锁。

  • 间隙锁兼容情况：一个间隙锁包含的范围是另一个的子集，两个间隙锁的范围相同且没有锁住真实记录（如果是真实的记录则会阻塞）。

  • 插入意向锁：是一种特殊的间隙锁，但是这个锁只用于并发插入，插入意向锁锁住的是一个点。

• 发生后打破死锁：设置事务等待锁的超时时间（超时回滚并释放锁）；开启主动检测死锁（检测到自锁，主动回滚事务）

• 排查：

  • 正在运行的任务：show full processlist; 找到卡主的进程

  • 解开死锁：UNLOCK TABLES ；

  • 查看当前运行的事务：SELECT * FROM information_schema.INNODB_TRX;

  • 当前出现的锁：SELECT * FROM information_schema.INNODB_LOCKS;

  • 观察错误日志

  • 查看InnoDB锁状态：show status like "innodb_row_lock%";

  • kill id 杀死进程

• 解决：

  • 死锁无法避免，上线前要进行严格的压力测试

    • 快速失败：innodb_lock_wait_timeout 行锁超时时间

    • 拆分sql，严禁大事务

    • 充分利用索引，优化索引，尽量把有风险的事务sql使用上覆盖索，优化where条件前缀匹配，提升查询速度，引减少表锁

    • 无法避免时：

      • 操作多张表时，尽量以相同的顺序来访问避免形成等待环路

      • 单张表时先排序再操作

      • 使用排它锁 比如 for update

5 日志

为什么需要undo log？

MySQL会隐式开启事务来执行增删改语句，为了在执行过程中异常崩溃后进行回滚，在执行事务时，需要把回滚时需要的信息都记录到undo log中。undo log保证了事务的原子性。

在事务没有提交前，MySQL会先记录更新前的数据到undo log中。

• 插入：undo log记录主键，回滚时删除；

• 删除：undo log记录全部内容，回滚时插入；

• 更新：undo log记录更新列的旧值，回滚时更新回旧值。

一条记录每一次更新操作产生的undo log都有一个roll_pointer和trx_id，roll_pointer用于指向前一个undo log，串成版本链，trx_id用于记录是哪一个事务进行的修改。从而与ReadView实现MVCC。

undo log如何持久化的？

undo log和数据页的刷盘策略相同，都需要通过redo log保证持久化。

Buffer pool中有undo页，对undo页的修改会记录到redo log中，redo log会每秒刷盘，提交事务也会刷盘，数据页和undo页都是靠这个机制保证持久化的。

为什么需要redo log？

由于Buffer pool是基于内存的，为了防止断电导致数据丢失的问题，当有一条记录需要更新时，InnoDB会先更新内存，然后将针对这个数据页的修改以redo log的形式记录下来，这时更新才算完成。后续InnoDB在适当的时候，通过后台线程将Buffer Pool中的脏页刷新到磁盘，这就是WAL（Write-Ahead Logging）技术，即写操作先写日志，后刷盘。

redo log是物理日志，记录的是某个数据也做了什么修改，事务提交时，先讲redo log持久化到磁盘即可，不需要等到将缓存在Buffer Pool里的脏页数据持久化到磁盘。系统崩溃时，由于redolog已经持久化，可以根据redolog将数据恢复。

redolog保证了持久性，让MySQL有crash-safe（崩溃恢复）的能力。

同时WAL技术将写操作从随机写变成了顺序写，进一步提升性能。

被修改的undo页，需要记录对应的redo log吗？

开启事务后，InnoDB更新前，先记录响应的undo log到Undo页，内存修改该Undo页后，会记录对应redolog。

redo log 和 undo log 区别在哪？

• redo log 记录此次事务完成后的数据状态，记录的是更新之后的值，事务提交后崩溃，根据redolog恢复。

• undo log记录此次事务开始前的数据状态，记录的是更新之前的值，事务提交前崩溃，根据undolog恢复。

redolog是直接写入磁盘的吗？

不是，redo log有自己的redo log buffer。redo log buffer 默认大小 16 MB，可以通过 innodb_log_Buffer_size 参数动态的调整大小，增大它的大小可以让 MySQL 处理「大事务」是不必写入磁盘，进而提升写 IO 性能。

redo log在以下几个时机会刷盘：

• MySQL正常关闭。

• redo log buffer写入量大于一半。

• InnoDB每隔1秒进行一次刷盘。

• 每次事务提交都会进行刷盘（可以通过参数改变策略）

redo log文件写满了怎么办？

循环写策略，当写入太多导致当前写的位置追上了要擦除的位置时，MySQL将不能进行更新操作，即会被阻塞，此时MySQL会将Buffer Pool中的脏页刷新到磁盘，并标记可擦除位置，对redolog进行擦除。

为什么需要bin log？

bin log是MySQL Server层实现的日志，有STATEMENT、ROW、MIXED三种格式，并且是追加写的方式，写满一个文件会新建一个文件继续写，不会覆盖日志。

bin log主要用于备份恢复、主从复制，而 redo log主要用于掉电回复等故障。

• 如果不小心整个数据库的数据都被删了，只能用binlog恢复，因为binlog是全量日志。

主从复制是怎么实现的（过程）？

1. 主库接受到事务提交请求后，写入binlog，然后提交事务，更新存储引擎中的数据，事务提交完成后，返回客户端成功响应。

2. 从库创建专门的IO线程，连接主库的log dump线程，接受来自主库的binlog，然后把binlog写入relay log中继日志中，在返回给主库复制成功的响应。

3. 从库创建一个用于回放binlog的线程，读取relaylog中的日志，回放更新存储引擎中的数据，实现主从的数据一致性。

主从复制有哪些模型？

• 同步复制：所有从库复制成功，才返回客户端响应。

• 异步复制（默认）：主库事务提交成功后，返回响应，主库宕机数据会发生丢失。

• 半同步复制：只要数据成功复制到任何一个从库上，就返回响应。

bin log什么时候刷盘？

• 每次提交事务，仅写入内核缓冲区，后续交给操作系统决定何时刷盘。

• 每次提交事务，写入内核缓冲区后，立即刷盘。

• 每次提交事务，都写入内核缓冲区，累计够N个事务后刷盘。

事务没提交时，redo log会被持久化到磁盘吗？

会的。事务执行中间过程的 redo log 也是直接写在 redo log buffer 中的，这些缓存在 redo log buffer 里的 redo log 也会被「后台线程」每隔一秒一起持久化到磁盘。

也就是说，事务没提交的时候，redo log 也是可能被持久化到磁盘的。

两阶段提交有什么问题？

• 磁盘 I/O 次数高：对于“双1”配置，每个事务提交都会进行两次 fsync（刷盘），一次是 redo log 刷盘，另一次是 binlog 刷盘。

• 锁竞争激烈：两阶段提交虽然能够保证「单事务」两个日志的内容一致，但在「多事务」的情况下，却不能保证两者的提交顺序一致，因此，在两阶段提交的流程基础上，还需要加一个锁来保证提交的原子性，从而保证多事务的情况下，两个日志的提交顺序一致。

执行一条 update 语句，期间发生了什么？

1. 客户端通过连接器建立连接

2. （update不查询缓存）清空缓存

3. 解析器进行语法分析，判断是否符合语法

4. 预处理器判断表和字段是否存在

5. 优化器确定执行计划

6. 执行器负责执行

   1. 调用存储引擎结构查询索引（如果有用到，否则查全表）

   2. 得到聚簇索引记录，查看更新前和更新后是否一样：一样则不执行后续流程

   3. 开启事务，记录相应的undo log，写入到Buffer Pool中的Undo页中，同时记录Undo log的redo log。

   4. InnoDB开始更新记录，先更新内存，并标记为脏页，然后将记录写入到redo log，随后由后台线程刷盘。（WAL技术）

   5. 更新完成，开始记录对应的bin log，并将对应的bin log保存到binlog cache中，在事务提交时同一将该事务过程中所有的binlog刷盘。

   6. 事务提交：

      1. prepare阶段：将redo log对应的事务状态设置为prepare，然后将redo log刷盘。

      2. commit阶段：将binlog刷盘，并将redo log状态设置为commit。

   7. 结束。

6 内存

为什么需要Buffer pool？

为了提高读写性能，InnoDB存储引擎设计了一个缓冲池。

• 读取时，命中直接返回。

• 修改时，命中直接修改buffer pool中的页，然后标记为脏页，后续由一个后台线程写入到磁盘。

Buffer中存储数据页、索引页、插入缓存页、undo页、自适应哈希索引、锁信息。

其中undo页记录生成的undo log。

查询一条数据时，会将整个数据页放入缓存，然后通过页目录查询。

7 开发

TIMESTAMP和DATATIME的区别

TIMESTAMP插入数据库会转为UTC，查询时又转为当前时区。

DATATIME不做任何改变。

DATATIME所能存储的时间范围更大。

预编译SQL

将SQL语句中的值用占位符替代，将SQL模板化或者参数化，可以做到一次编译，多次运行，可以防止SQL注入。

子查询和Join

子查询更加灵活，但是会创建临时表，导致效率比Join低，Join最多关联61张表。

MySQL调优工具

• EXPLAIN

• MySQLdumpslow

• show profiles 时间

• optimizer_trace

何时进行分库分表？

1. 能不分就不分

2. 单机性能下降明显时：先尝试缓存，再尝试读写分离，最后才是分库分表

count(col) 和count(*)的区别

• count(col)不会统计NULL

• count(*)会统计NULL