MySQL常见问题

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

MySQL架构分为Server层与存储引擎层，Server层负责管理连接、解析和执行SQL语句，存储引擎层负责数据的存储和提取。
一条SQL语句的执行主要会经历四个组件，分别是：连接器、查询缓存、解析器和执行器。

  1. 执行SQL前，用户首先需要连接MySQL，连接器主要用于建立连接，管理连接、校验用户身份；
  2. 连接建立后，MySQL对于查询语句会首先判断是否在查询缓存，若存在则直接返回；不过对于更新频繁的表，表中的查询缓存会频繁失效，导致缓存命中率很低，因此MySQL8.0以后默认关闭查询缓存；
  3. 解析器对 SQL 查询语句进行词法分析、语法分析，然后构建语法树，方便后续模块读取表名、字段、语句类型。
  4. 解析完成后，将执行SQL语句，主要包括预处理、优化和执行三个阶段。预处理阶段检查查询语句中的表或字段是否存在，并将select *中的*扩展为表上的所有列。优化阶段主要负责确定SQL语句的执行方案，如索引的使用。执行阶段从存储引擎读取记录，返回给客户端。

什么是索引？

索引是帮助存储引擎快速获取数据的一类数据结构。常见的索引有B+树索引、Hash索引、倒排索引等。

B+树是什么？

B+树是一种多叉查找树，它在非叶子节点存储索引，只在叶子节点存储数据，并且叶子节点按索引值顺序连接成了一个双向链表，方便范围查询。B+树存储千万级别的数据仅需3-4层，这意味着查询一次数据只需3到4次磁盘I/O。

索引的使用？

索引的好处是能加快查询速度，但索引也会占用物理空间，创建和维护索引也需要时间。因此，索引需要根据场景使用。
适合用索引的字段包括：1. 字段的区分度高，最好是唯一性限制；2. 经常用于WHERE语句查询条件或者GROUP BY或者ORDER BY的字段。

优化索引的方法？

  1. 前缀索引优化，在一些大字符串的字段用作索引时，可以使用其前几个字符作为前缀索引可以减少索引项大小。但ORDER BY无法使用前缀索引，也无法用作覆盖索引。
  2. 覆盖索引优化，如果辅助索引包含了查询所需要的所有字段，就可以避免回表查询。
  3. 主键索引最好自增，插入一条记录，都是追加操作，不容易造成B+树的页分裂。
  4. 防止索引失效，联合索引要能正确使用需要遵循最左匹配原则，避免因错误使用左或者左右模糊匹配，对索引列做了计算、函数、类型转换操作，OR条件句导致的索引失效。

like “%x“一定使索引失效吗？

在能使用覆盖索引的情况下，优化器可能会直接使用辅助索引，虽然无法用辅助索引来帮助快速检索，但因为辅助索引上记录的数据更少，直接遍历代价相比于主键索引更小。

事务特性？

事务的特性主要是ACID：

  1. 原子性：事务的操作要么全部成功执行，要么全部不执行。
  2. 一致性：事务执行前后，数据满足完整性约束，数据库保持一致性状态。
  3. 隔离性：事务并发执行不会互相影响。
  4. 持久性：事务提交后对数据的修改是永久的，就算系统故障也不会丢失。

并发事务问题？

  1. 脏读：一个事务读到了另一个未提交事务修改过的数据，就意味着发生了脏读现象。
  2. 不可重复读：在一个事务内多次读取同一个数据，前后两次读到的数据不一样的情况。
  3. 幻读：在一个事务内多次查询某个符合查询条件的记录数量，出现前后两次查询到的记录数量不一样。

事务隔离级别？

  1. 读未提交：事务可以读到其它事务未提交的变更；可能发生脏读、不可重复读和幻读现象。
  2. 读已提交：事务只可以读到其它事务已提交的变更；可能发生不可重复读和幻读现象，但是不可能发生脏读现象。
  3. 可重复读：事务执行过程中看到的数据始终时一致的；可能发生幻读现象，但是不可能脏读和不可重复读现象。
  4. 串行化：会对记录加上读写锁，并发读写冲突时后访问的事务需等待前一个完成；脏读、不可重复读和幻读现象都不可能会发生。

幻读怎么解决？

针对快照读（普通 select 语句），是通过 MVCC 方式解决了幻读；
针对当前读（select … for update 等语句），是通过 next-key lock（记录锁+间隙锁）方式解决了幻读。
但也也有些场景会发生幻读：

  1. T1 时刻：事务 A 先执行「快照读语句」：select * from t_test where id > 100 得到了 3 条记录。
  2. T2 时刻：事务 B 往插入一个 id= 200 的记录并提交；
  3. T3 时刻：事务 A 再执行「当前读语句」 select * from t_test where id > 100 for update 就会得到 4 条记录，此时也发生了幻读现象。

MVCC怎么实现？

MVCC是通过Read View和undo log实现的，Read View包含四个字段，分别是创建Read View的事务的事务ID、创建Read View时活跃且未提交的事务ID列表、活跃未提交的最小事务ID、应该给下个事务的ID。通过这四个字段可以区分出在Read View创建时已提交的、未提交的、还未开始的事务。
在数据表的行记录及其undo log记录上都有隐藏字段表示更新它的事务ID，通过Read View和这个隐藏字段就可以判断该记录的可见性，即只有Read View创建时已经提交的记录才是可见的。
对于可重复读的隔离级别，Read View是在创建事务时就会创建，事务执行过程中始终使用这一Read View，从而可以保证事务对记录的可见性始终不变。
对于读已提交的隔离级别，在每次读的时候都会创建新的Read View，从而可以保证事务每次能够读到已经提交的变更。

MySQL有什么锁？

  1. 全局锁：使用flush tables with read lock，整个数据库处于只读状态，主要用于全库逻辑备份。
  2. 表级锁：
    • 表锁：会限制表的读写；
    • 元数据锁：是为了保证当用户对表执行 CRUD 操作时，防止其他线程对这个表结构做了变更。
    • 意向锁：意向共享锁和意向独占锁是表级锁，不会和行级的共享锁和独占锁发生冲突，而且意向锁之间也不会发生冲突，只会和共享表锁（lock tables … read）和独占表锁（lock tables … write）发生冲突。
  3. 行级锁：
    • Record Lock：记录锁，锁住的是一条记录，分为 S 锁和 X 锁。
    • Gap Lock：间隙锁，只存在于可重复读隔离级别，目的是为了解决可重复读隔离级别下幻读的现象。
    • Next-Key Lock：临键锁，是 Record Lock + Gap Lock 的组合，锁定一个范围，并且锁定记录本身。
    • 插入意向锁：插入时，需判断插入位置是否加了间隙锁，插入操作就会阻塞直到间隙锁释放，在此期间会生成等待状态的插入意向锁，插入意向锁是一种行锁级别的特殊间隙锁。

MySQL怎么加锁的？

加锁的对象是索引，加锁的基本单位是 next-key lock，它是由记录锁和间隙锁组合而成的，next-key lock 是前开后闭区间，而间隙锁是前开后开区间。
但是，next-key lock 在一些场景下会退化成记录锁或间隙锁。在能使用记录锁或者间隙锁就能避免幻读现象的场景下， next-key lock 就会退化成记录锁或间隙锁。

  1. 唯一等值查询：唯一索引进行等值查询。
    • 查询记录存在，定位到这一记录后，间隙锁就会退化成记录锁。
    • 查询记录不存在，找到第一条大于该值的记录后，间隙锁就会退化成间隙锁。
  2. 唯一索引范围查询

MySQL死锁的产生？

插入意向锁与间隙锁是冲突的，所以当其它事务持有该间隙的间隙锁时，需要等待其它事务释放间隙锁之后，才能获取到插入意向锁。而间隙锁与间隙锁之间是兼容的，所以所以两个事务中 select ... for update 语句并不会相互影响。插入操作为了获取到插入意向锁，都在等待对方事务的间隙锁释放，于是就造成了循环等待，导致死锁。
避免死锁的方法：设置事务等待锁的超时时间；开启主动死锁检测。

MySQL日志？

  1. undo log（回滚日志）：是 Innodb 存储引擎层生成的日志，实现了事务中的原子性，主要用于事务回滚和 MVCC。
  2. redo log（重做日志）：是 Innodb 存储引擎层生成的日志，实现了事务中的持久性，主要用于掉电等故障恢复；
  3. binlog （归档日志）：是 Server 层生成的日志，主要用于数据备份和主从复制；

Redo log刷盘时机？

  1. 当设置该参数为 0 时，表示每次事务提交时 ，还是将 redo log 留在 redo log buffer 中 ，该模式下在事务提交时不会主动触发写入磁盘的操作。
  2. 当设置该参数为 1 时，表示每次事务提交时，都将缓存在 redo log buffer 里的 redo log 直接持久化到磁盘，这样可以保证 MySQL 异常重启之后数据不会丢失（刷盘）。
  3. 当设置该参数为 2 时，表示每次事务提交时，都只是缓存在 redo log buffer 里的 redo log 写到 redo log 文件（不刷盘）。

两阶段提交？

MySQL 为了避免出现两份日志之间的逻辑不一致的问题：

  1. 如果在将 redo log 刷入到磁盘之后， MySQL 突然宕机了，而 binlog 还没有来得及写入。
  2. 如果在将 binlog 刷入到磁盘之后， MySQL 突然宕机了，而 redo log 还没有来得及写入。
    使用了「两阶段提交」来解决
  • prepare 阶段：将 XID（内部 XA 事务的 ID） 写入到 redo log，同时将 redo log 对应的事务状态设置为 prepare，然后将 redo log 持久化到磁盘（innodb_flush_log_at_trx_commit = 1 的作用）；
  • commit 阶段：把 XID 写入到 binlog，然后将 binlog 持久化到磁盘（sync_binlog = 1 的作用），接着调用引擎的提交事务接口，将 redo log 状态设置为 commit，此时该状态并不需要持久化到磁盘，只需要 write 到文件系统的 page cache 中就够了，因为只要 binlog 写磁盘成功，就算 redo log 的状态还是 prepare 也没有关系，一样会被认为事务已经执行成功；