Mysql 面试题整理

发表于 | 分类于 ， | 评论数：

Mysql架构介绍

mysql架构设计

image-20220104115229275

mysql数据存储目录结构

  • admin.idb 为表数据文件

image-20220104215957881

mysql数据存储方式

mysql数据的存储是按照 表空间->段->区->页->行的结构存储的

其中，页的大小默认为16k

image-20220104215906535

mysql 页的物理存储结构

image-20220104115454870

Mysql页、行结构

页的组成

  • 页头：记录页面的控制信息（共占56字节），其中 1. 包括页的左右兄弟页面指针 2. 页面空间使用情况等。
  • 页尾： 页面最后部分，占8个字节，主要存储页面的校验信息
  • 虚记录：
    • 最大虚记录：比页内最大主键还大
    • 最小虚记录：比页内最小主键还小
  • 记录堆： 行记录存储区，分为有效记录和已删除记录两种
  • 自由空间链表：已删除记录组成的链表
  • 未分配空间：页面未使用的存储空间
  • Slot区: 对业内的数据进行排序分组，可以加速页内元素的搜索
image-20220104115636790

注：一个页存储的数据行最少为两行

mysql页内数据维护方式

p: 表示物理指针

image-20220104115957328

  1. 顺序保证

    • 物理有序：需要进行磁盘地址的移动，时间消耗较大

    • 逻辑有序：每行记录next数据的位置，维持一个单向链表，通过链表顺序来保证有序，mysql就是采用来中方式来确保业内元素有序

      image-20220104120046435 image-20220104120107703 image-20220104120121046

  2. 插入策略

    • 自有空间链表：已删除的数据，空间不会被回收，用来分配给新插入的数据；

    • 未使用空间：未分配的存储空间；

      image-20220104120146606

  3. 页内查询

    • 遍历：挨个元素进行查找
    • Slot分组查找：通过slot槽将页内元素分为n个槽位，每个槽位最少4个元素最多8个元素，每个槽位记录该组最大元素。查找的时候可以通过二分查找来确定目标槽，再在槽内寻找目标元素。
    image-20220104120217197

溢出页(BLOB页)

当一条记录就超过16k的时候，一个页不能存储下一条记录的时候，需要将大数据字段数据存储到溢出页中(一个溢出页为16k 如果存不下可以用多个连续的溢出页)，在原数据行中记录溢出页的地址；

image-20220104222626969

Mysql的行结构

  • 头信息： 记录了next-record 等信息
  • 隐藏列：
    • Trx id: 事物id
    • Roll Pointer： 回滚指针（指向undolog）
    • Rowid：行id
  • 普通列：表字段数据

image-20220104222010811

Innodb内存管理

image-20220104120840589

在实际的内存和硬盘的io交互过程中，会拼盘的进行页数据的读取和写入，为了减少重复的页读取和写入。引入了缓冲区的概念(Buffer Pool)

Innodb内存管理

image-20220104121240331

  • 内存池 (Buffer pool)：预分配的内存池， 内存池默认大小： BufferPool size / 16k。

  • 页（page）：Buffer Poll 的最小单位

    按照页的用途可以分为：

    • 索引页：用来存储索引页
    • 数据页：用来存储数据页
    • undo页： 用来回滚

    按照状态可划分为：

    • 空闲页
    • 已使用页
    • 脏页：发生增删改的页

  • 内存页面管理：

    • Free-list：空闲Page组成的链表
    • LRU-list: 数据页组成的链表(热点数据)
    • flush-list：脏页链表

    checkpoint 机制：将LRU-list flush-list里的数据来落盘

  • 内外存数据映射(Page hash ): 维护内存Page和文件Page的映射关系

  • 页数据淘汰(LRU):

    • 页面装载: 磁盘数据到内存
    image-20220104121327291

    ​ 空闲页获取优先级： Free list中取 > LRU中淘汰 > LRU Flush

    • 页面淘汰种类：

      • LRU尾部淘汰
      • Flush LRU淘汰： LRU链表中将第一个脏页刷盘并“释放”，将空闲页放到LRU尾部或直接放入空闲页

    • LRU位置移动

      • 常规的LRU算法

        image-20220104121123882

        ​ 该种办法在进行全表扫描或者大范围查询的时候，会导致进行大量数据请求和操作时，导致热点数据被覆盖。

      • 怎么解决全表扫描类操作造成的热点数据被淘汰？ - 冷热分离

        • LRU_new：热数据LRU-list，业务访问大部分使用该区域
        • LRU_old： 冷数据LRU-list，热数据淘汰后进入 LRU_old，给大部分扫描的场景使用。
        • Midpoint：按照 5/8 3/8 的比例来进行划分
image-20220104121303233

​ old到new:

​ image-20220104232946565

​ 两次对页的查询时间间隔小于innodb_old_blocks_time，才会进入热点页，因为页存储的是范围数据，查询频率过高代表页大概率是在进行全表扫描等数据跨度很小的查询，不应该计入热点数据列表；

​ new到old:

​ image-20220104233039040

  • LRU页的操作频率很高，经常要将元素移动到表头，怎么解决 ? - LRU_new操作

​ 如果操作的页处于LRU链表前1/4的位置时，LRU_new 不进行移动

  • 淘汰之后的页怎么处理：
    • 脏页：如果淘汰的是脏页，则需要对页数据进行落盘操作
    • 数据页：如果淘汰的页仅为数据页，清空数据将页放入FreeList。

脏页刷盘时机

  1. 当MySQL数据库关闭时
  2. 有一个后台线程Master Thread会按照每秒或者每十秒的速度，异步的将Buffer Pool中一定比例的页面刷新回磁盘中。
  3. free-list 太少
  4. 脏页太多
  5. 当redo log不可用时，也会强制脏页列表中的脏页刷新回磁盘。这个机制同样由一个后台线程完成。

索引原理分析

聚簇索引

数据和索引存储在一颗树上

  • 数据存储在主键索引中
  • 数据按主键顺序存储
image-20220104140443496

二级索引

只存储索引值，叶子阶段记录索引和主键索引的映射关系

  • 除主键索引以外的索引
  • 叶子中存储主键值
  • 一次查询需要走两遍索引，需要先查询到主键索引的值，再去主键索引取查询行记录，除非使用覆盖索引的场景。
  • 主键大小会影响所有索引的大小
image-20220104140503860

联合索引

Key由多个字段组成

  • 最左匹配原则， a b c 联合索引，可以理解为建立了 a / a b / a b c 三个索引；
  • 一个索引只创建一棵树
  • 排序方式按第一列排序，第一列相同按第二列排序

如果不是按照最左开始查找，无法使用索引，不能跳过中间列
某列使用范围查询，后面的列不能使用索引

image-20220104140648062

索引使用优化

  • 存储空间

    • 尽量减小索引文件的大小

    • (字段越小，页内节点数约少，数的层数可以越低)字段大小->页内节点个数->树的层数

      BIGNT类型主键3层可以存储约10亿条数据， 16KB/(8B(key)+8B(指针)=1K， 10^3 * 10^3 * 10^3 = 10亿
      32字节主键3层可以存储6400W

  • 主键选择

    • 自增主键：写入磁盘利用率高，查询走两级索引；
    • 随机主键：写入磁盘利用率低，存在查询走两级索引；
    • 业务主键：写入、查询磁盘利用率都高，可以使用一级索引；
    • 联合主键：影响索引大小，不易维护，不建议使用

  • 联合索引的使用

    • 按索引区分度排序，区分度越高优先排序
    • 尽量使用覆盖索引
    • 索引下推，未满足最左前缀匹配的时候，innodb会通过索引下推，在引擎层就对查询结果进行过滤，减少没必要的io操作；
    • 在组合索引中，使用between、>、<、like等进行匹配都会导致后面的列无法继续走联合索引，因为通过以上方式匹配到的数据是不可知的，后面的列无法确定根据哪些数据继续向下进行索引。

  • 字符串索引

    • 设置合理长度
    • 不支持%开头的模糊查询

在group by 和 order by 上使用联合索引

创建联合索引, c1234(c1,c2,c3,c4)

1、只有where的情况，遵从最左原则

条件必须有左边的字段，才会用到索引，中间如果断开了，则都不会用到后面的索引，

例子： where c1 = ‘1’ and c2 = ‘1’ and c4 = ‘1’，这种情况因为c3没有在条件中，所以只会用到c1,c2索引。

特殊情况，使用范围条件的时候，也会使用到该处的索引，但后面的索引都不会用到

例子： where c1 = ‘1’ and c2 > ‘1’ and c3 = ‘1’，这种情况从c2处已经断开，会使用到c1,c2索引，不会再使用到后面的c3,c4索引。

2、group by和order by 其实一样，也是遵从最左原则

可以看做继承where的条件顺序，但需要where作为基础铺垫，即没有where语句，单纯的group by | order by 也是不会使用任何索引的，并且需要和联合索引顺序一致才会使用索引。

例子：

group by c1 | order by c1，由于没有where的铺垫，不使用任何索引

where c1 = ‘1’ group | order by c2，使用c1,c2索引

where c1 = ‘1’ group | order by c3，只使用c1索引

where c1 > ‘1’ group | order by c2，前面也说了，范围搜索会断掉连接，所以也只会使用c1索引

关于顺序：

where c1 = ‘1’ group | order by c2,c3，使用c1,c2,c3索引

where c1 = ‘1’ group | order by c3,c2，只使用c1索引

mysql 允许null 的字段设置索引哪些需要注意

mysql 进行is null 查询会使用索引的， 进行 not null 判断则不会使用索引。

虽然MySQL可以在含有null的列上使用索引，但不代表null和其他数据在索引中是一样的。

不建议列上允许为空。最好限制not null，并设置一个默认值，比如0和’’空字符串等，如果是datetime类型，可以设置成’1970-01-01 00:00:00’这样的特殊值。

对MySQL来说，null是一个特殊的值，s。比如：不能使用=,<,>这样的运算符，对null做算术运算的结果都是null，count时不会包括null行等，null比空字符串需要更多的存储空间等。

B 树

B树特征

  1. 根结点至少有两个子女。(m阶)
  2. 每个中间节点都包含k-1个元素和k个孩子，其中 m/2 <= k <= m
  3. 每一个叶子节点都包含k-1个元素，其中 m/2 <= k <= m
  4. 所有的叶子结点都位于同一层。
  5. 每个节点中的元素从小到大排列，节点当中k-1个元素正好是k个孩子包含的元素的值域分划。

B+ 树特征

B+ 树的阶数就是一个节点存储的最多键的数量

  1. 有k个子树的中间节点包含有k个元素（B树中是k-1个元素），每个元素不保存数据，只用来索引，所有数据都保存在叶子节点。
  2. 所有的叶子结点中包含了全部元素的信息，及指向含这些元素记录的指针，且叶子结点本身依关键字的大小自小而大顺序链接。
  3. 所有的中间节点元素都同时存在于子节点，在子节点元素中是最大（或最小）元素。

1588818370345

B+树的优势：

  1. 单一节点存储更多的元素， 另外由于层级更低， 使得查询的IO次数更少。
  2. 所有查询都要查找到叶子节点，查询性能稳定。
  3. 所有叶子节点形成有序链表，便于范围查询。

备注: m为阶数

索引失效场景

  1. A=xx OR B=xx

    5.1开始，引入Index merge技术
    对同一个表可以使用多个索引分别扫描

  2. 隐式类型转换

    WHER moblie = ‘135XX’；
    WHER moblie = 135XX

  3. 索引列包含计算

  4. 联合索引，未遵循最左前缀，字段范围查询右边列不会使用索引

  5. is null / is not null 查询

  6. 联合索引，范围查询

  7. like ‘%查询’

mysql 事务

mysql 事务基本概念

  • 事务特性
    • A（Atomicty原子性）：全部成功或全部失败
    • I（Isolation隔离性）：并行事务之间互不干扰
    • D（Durabilty持久性）：事务提交后，永久生效
    • C（Consitency一致性）：数据执行前执行后保持数据的一致，通过AID保证
  • 隔离并发读取问题
    • 脏读(Drity Read)：读取到未提交的数据
    • 不可重复读(Non-repatble rad)：前后读取的数据不一致；
    • 幻读(Phantom Read)：前后读取的记录数量不一致。
  • 隔离级别
    • Read Uncomited（读取未提交内容）：最低隔离级别，会读取到其他事务未提交的数据，脏读；
    • Read Comited（读取提交内容）：事务过程中可以读取到其他事务已提交的数据，不可重复读；
    • Repatble Read（可重复读）：每次读取相同结果集，不管其他事务是否提交，解决了脏读、不可重复读和幻读；
    • Serialzable（串行化）：事务排队，隔离级别最高，性能最差

事物的实现原理

undo log(版本链)

分为insert undo log 和update undo log (包含 update 和 delete)

  • 回滚日志： 用于事物的回滚操作；
  • 保证事务原子性
  • 实现数据多版本
  • insert undo log：用于回滚，提交即清理；
  • update undo log：用于回滚，同时实现快照读，不能随便删除

图中为行记录和undolog的对应图

DB_TRX_ID： 记录修改记录的事物id

DB_ROLL_PTR: 记录上一条undo log的地址，用来进行记录的回滚操作

image-20220104143417084

insert undo log

insert操作的记录只对事务本身可见。

对于其它事务此记录是不可见的，所以 insert undo log 可以在事务提交后直接删除而不需要进行purge操作。

update undo log

因为会对已经存在的记录产生影响，为了提供 MVCC机制，因此 update undo log 不能在事务提交时就进行删除，而是将事务提交时放到入 history list 上，等待 purge 线程进行最后的删除操作

为了更好的支持并发，InnoDB的多版本一致性读是采用了基于回滚段的的方式。另外，对于更新和删除操作，InnoDB并不是真正的删除原来的记录，而是设置记录的delete mark为1。因此为了解决数据Page和Undo Log膨胀的问题，需要引入purge机制进行回收

另外， 未来保证宕机后的事务准确性，只有undolog刷盘后脏数据才能刷盘

Read View

用来判断undolog里的版本数据是否对当前版本可见，其存储了当前活跃的事务id，把存储事物id的列表称为m_ids。

其中RC级别是在每次sql语句的时候建立read view, RR 级别是在事物第一次读建立ReadView

  • 可见性判断

    • 存储创建快照这一刻，还未提交的事务，对于当前快照版本不可见；
    • 创建快照之后创建的事务，晚于活跃的事物创建，对于当前快照版本更不可见；

  • Read View 在快照读的应用流程

    • 活跃事务列表: 提供当前版本
    • 列表中最小事务ID
    • 列表中最大事务ID
    image-20220104143320476

    如下图当read view m_ids list记录了(100, 200) 两个事物

    当select c from table 时，查询到的结果是刘备

image-20220105004942869

redo log

  • 实现事务持久性
  • 记录修改
  • 用于异常恢复
  • 循环写文件
    • Write Pos：写入位置
    • Check Point：刷盘位置
    • Check Point -> Write Pos：待落盘数据
image-20220104143656543

  • redo log 写入流程

    用来记录Buffer Pool脏页的数据修改，先写入到 redo log buffer pool， commit后 再持久化到redo log 文件，当持久化到redolog文件后，事务算持久化成功；

    image-20220104143730940

  • redo log 刷盘时机

    MTR：mini transaction 对底层页面进行一次原子访问的过程。如修改一次MAX ROW ID的值，B+树插入一条记录。 一个事务对应多个语句，一个语句对应多个MTR，一个MTR对应多个redo日志，即一组日志。

通过MTR生成的redo日志放在512字节的block中。innodb为了解决磁盘速度过慢的问题而引入buffer pool，写入redo日志也不能直接写入到磁盘当中。所以有redo log buffer（redo日志缓冲区），划分成若干个redo log block。log buffer在5.7.22当中默认值为16MB。

当MTR结束的时候，再将过程中产生的一组redo日志全部复制到log buffer当中。

两个事务的MTR可能是交替执行的，即不同事务的MTR对应的redo日志可能是交替写入log buffer的。

redo日志刷盘时机：也就是从buffer pool刷新到磁盘当中的时机。

1。log buffer 空间不足。

2。事务提交前。（持久性的真正体现）

3。后台有一个线程大约以每秒一次的频率刷盘。

  • commit redo log 刷盘策略

​ inodb_flush_log_atrx_comit 可调整

​ （1）设置为0 ：表示每次事务提交时不进行刷盘操作。默认master thread每隔1s进行一次重做日志的同步。

​ （2）设置为1 ：表示每次事务提交时都将进行同步，刷盘操作（ 默认值 ）

​ （3）设置为2 ：表示每次事务提交时都只把 redo log buffer 内容写入 page cache，不进行同步。由os自己决定什么时候同步到磁盘文件。

image-20220104143800491

redo log意义

体积小，记录页的修改，比写入页代价低
末尾追加，随机写变顺序写，发生改变的页不固定

redo log 、 binlog 区别

  1. redo log 是 InnoDB 引擎特有的；binlog 是 MySQL 的 Server 层实现的，所有引擎都可以使用。
  2. redo log 是物理日志，记录的是在某个数据页上做了什么修改；binlog 是逻辑日志，记录的是DDL和DML操作语句。
  3. redo log 是循环写的，空间固定会用完；binlog 是可以追加写入的。追加写是指 binlog 文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。
  4. redo log+bin log保证crash-safe，bin log日志用于数据恢复和主从复制。
image-20230107135340658

redolog、binlog 的2阶段提交

redo log 和 binlog 有一个共同的数据字段，叫 XID。崩溃恢复的时候，会按顺序扫描 redo log：

  1. 如果碰到既有 prepare、又有 commit 的 redo log，就直接提交；
  2. 如果碰到只有 parepare、而没有 commit 的 redo log，就拿着 XID 去 binlog 找对应的事务。
    binlog无记录，回滚事务
    binlog有记录，提交事务。

如果同一阶段提交，redo log写完提交，写bin log时mysql进程崩溃，但由于bin log日志没有修改记录，使用redo log+bin log恢复的数据就是数据库旧的数据
如果先写bin log后写redo log，在bin log写完提交之后崩溃，崩溃恢复以后这个事务需要回滚，在之后用redo log+bin log来恢复的时候就多了一个事务出来，

由此得出，如果不使用“两阶段提交”，那么数据库的状态就有可能和用它的日志恢复出来的库的状态不一致。

MVCC 多版本并发控制

全称Multi-Version COncurrentcy Control，多版本并发控制。指维护一个数据的多个版本，使得读写操作没有冲突，快照读为Mysql实现MVCC提供了一个非阻塞读功能。MVCC的具体实现，还需要依赖于数据库记录中的三个隐式字段、undo log日志，readVIew。（多版本并发控制，它是通过读取历史版本的数据，来降低并发事务冲突，从而提高并发性能的一种机制）

  • 当前读/快照读

    • 当前读： 读取目前时间最新的版本数据

    • 快照读： 读取小于快照时间版本的数据

      解决读-写冲突

image-20220104143227586

事物的实现总结

  • 事物的隔离性: 靠mvcc 实现了多版本控制并解决了脏读和不可重复读的问题 ， 间隙锁解决了并发读的幻读问题
  • 事物的原子性: undo log 的特性来实现
  • 事物的持久性: redo log 来实现
  • 事物的一致性: 是所有事物特性的共同来实现

mysql 锁

mysql锁的种类

类型划分

  • 共享锁（S）
    读锁，可以同时被多个事务获取，阻止其他事务对记录的修改；
  • 排他锁/独占锁（X）
    写锁，只能被一个事物获取，允许获得锁的事物修改数据；

锁粒度划分

全局锁

要使用全局锁，则要执行这条命令：

1
flush tables with read lock

执行后，整个数据库就处于只读状态了，这时其他线程执行以下操作，都会被阻塞：

  • 对数据的增删改操作，比如 insert、delete、update等语句；
  • 对表结构的更改操作，比如 alter table、drop table 等语句。

如果要释放全局锁，则要执行这条命令：

1
unlock tables

当然，当会话断开了，全局锁会被自动释放。

全局锁应用场景是什么？

全局锁主要应用于做全库逻辑备份，这样在备份数据库期间，不会因为数据或表结构的更新，而出现备份文件的数据与预期的不一样。

表级锁

MySQL 里面表级别的锁有这几种：

  • 表锁；
  • 元数据锁（MDL）;
  • 意向锁；
  • AUTO-INC 锁；

表锁

先来说说表锁。

如果我们想对学生表（t_student）加表锁，可以使用下面的命令：

1
2
3
4
5
//表级别的共享锁，也就是读锁；
lock tables t_student read;

//表级别的独占锁，也就是写锁；
lock tables t_stuent write;

需要注意的是，表锁除了会限制别的线程的读写外，也会限制本线程接下来的读写操作。

也就是说如果本线程对学生表加了「共享表锁」，那么本线程接下来如果要对学生表执行写操作的语句，是会被阻塞的，当然其他线程对学生表进行写操作时也会被阻塞，直到锁被释放。

要释放表锁，可以使用下面这条命令，会释放当前会话的所有表锁：

1
unlock tables

另外，当会话退出后，也会释放所有表锁。

元数据锁

我们不需要显示的使用 MDL，因为当我们对数据库表进行操作时，会自动给这个表加上 MDL：

  • 对一张表进行 CRUD 操作时，加的是 MDL 读锁；
  • 对一张表做结构变更操作的时候，加的是 MDL 写锁；

MDL 是为了保证当用户对表执行 CRUD 操作时，防止其他线程对这个表结构做了变更。

当有线程在执行 select 语句（ 加 MDL 读锁）的期间，如果有其他线程要更改该表的结构（ 申请 MDL 写锁），那么将会被阻塞，直到执行完 select 语句（ 释放 MDL 读锁）。

反之，当有线程对表结构进行变更（ 加 MDL 写锁）的期间，如果有其他线程执行了 CRUD 操作（ 申请 MDL 读锁），那么就会被阻塞，直到表结构变更完成（ 释放 MDL 写锁）。

MDL 不需要显示调用，那它是在什么时候释放的?

MDL 是在事务提交后才会释放，这意味着事务执行期间，MDL 是一直持有的。

那如果数据库有一个长事务（所谓的长事务，就是开启了事务，但是一直还没提交），那在对表结构做变更操作的时候，可能会发生意想不到的事情，比如下面这个顺序的场景：

  1. 首先，线程 A 先启用了事务（但是一直不提交），然后执行一条 select 语句，此时就先对该表加上 MDL 读锁；
  2. 然后，线程 B 也执行了同样的 select 语句，此时并不会阻塞，因为「读读」并不冲突；
  3. 接着，线程 C 修改了表字段，此时由于线程 A 的事务并没有提交，也就是 MDL 读锁还在占用着，这时线程 C 就无法申请到 MDL 写锁，就会被阻塞，

那么在线程 C 阻塞后，后续有对该表的 select 语句，就都会被阻塞，如果此时有大量该表的 select 语句的请求到来，就会有大量的线程被阻塞住，这时数据库的线程很快就会爆满了。

为什么线程 C 因为申请不到 MDL 写锁，而导致后续的申请读锁的查询操作也会被阻塞？

这是因为申请 MDL 锁的操作会形成一个队列，队列中写锁获取优先级高于读锁，一旦出现 MDL 写锁等待，会阻塞后续该表的所有 CRUD 操作。

所以为了能安全的对表结构进行变更，在对表结构变更前，先要看看数据库中的长事务，是否有事务已经对表加上了 MDL 读锁，如果可以考虑 kill 掉这个长事务，然后再做表结构的变更。

意向锁

  • 在使用 InnoDB 引擎的表里对某些记录加上「共享锁」之前，需要先在表级别加上一个「意向共享锁」；
  • 在使用 InnoDB 引擎的表里对某些纪录加上「独占锁」之前，需要先在表级别加上一个「意向独占锁」；

也就是，当执行插入、更新、删除操作，需要先对表加上「意向独占锁」，然后对该记录加独占锁。

而普通的 select 是不会加行级锁的，普通的 select 语句是利用 MVCC 实现一致性读，是无锁的。

不过，select 也是可以对记录加共享锁和独占锁的，具体方式如下：

1
2
3
4
5
//先在表上加上意向共享锁，然后对读取的记录加共享锁
select ... lock in share mode;

//先表上加上意向独占锁，然后对读取的记录加独占锁
select ... for update;

意向共享锁和意向独占锁是表级锁，不会和行级的共享锁和独占锁发生冲突，而且意向锁之间也不会发生冲突，只会和共享表锁（*lock tables … read*）和独占表锁（*lock tables … write*）发生冲突。

表锁和行锁是满足读读共享、读写互斥、写写互斥的。

如果没有「意向锁」，那么加「独占表锁」时，就需要遍历表里所有记录，查看是否有记录存在独占锁，这样效率会很慢。

那么有了「意向锁」，由于在对记录加独占锁前，先会加上表级别的意向独占锁，那么在加「独占表锁」时，直接查该表是否有意向独占锁，如果有就意味着表里已经有记录被加了独占锁，这样就不用去遍历表里的记录。

所以，意向锁的目的是为了快速判断表里是否有记录被加锁。

行级锁

记录锁 Record Lock

Record Lock 称为记录锁，锁住的是一条记录。而且记录锁是有 S 锁和 X 锁之分的：

  • 当一个事务对一条记录加了 S 型记录锁后，其他事务也可以继续对该记录加 S 型记录锁（S 型与 S 锁兼容），但是不可以对该记录加 X 型记录锁（S 型与 X 锁不兼容）;
  • 当一个事务对一条记录加了 X 型记录锁后，其他事务既不可以对该记录加 S 型记录锁（S 型与 X 锁不兼容），也不可以对该记录加 X 型记录锁（X 型与 X 锁不兼容）。

举个例子，当一个事务执行了下面这条语句：

1
2
mysql > begin;
mysql > select * from t_test where id = 1 for update;

就是对 t_test 表中主键 id 为 1 的这条记录加上 X 型的记录锁，这样其他事务就无法对这条记录进行修改了。

间隙锁 Gap Lock

Gap Lock 称为间隙锁，只存在于可重复读隔离级别，目的是为了解决可重复读隔离级别下幻读的现象。

假设，表中有一个范围 id 为（3，5）间隙锁，那么其他事务就无法插入 id = 4 这条记录了，这样就有效的防止幻读现象的发生。

image-20230507170604277

间隙锁虽然存在 X 型间隙锁和 S 型间隙锁，但是并没有什么区别，间隙锁之间是兼容的，即两个事务可以同时持有包含共同间隙范围的间隙锁，并不存在互斥关系，因为间隙锁的目的是防止插入幻影记录而提出的。

临键锁 Next-Key Lock

Next-Key Lock 称为临键锁，是 Record Lock + Gap Lock 的组合，锁定一个范围，并且锁定记录本身。

假设，表中有一个范围 id 为（3，5] 的 next-key lock，那么其他事务即不能插入 id = 4 记录，也不能修改 id = 5 这条记录。

image-20230507170639856

所以，next-key lock 即能保护该记录，又能阻止其他事务将新纪录插入到被保护记录前面的间隙中。

next-key lock 是包含间隙锁+记录锁的，如果一个事务获取了 X 型的 next-key lock，那么另外一个事务在获取相同范围的 X 型的 next-key lock 时，是会被阻塞的。

比如，一个事务持有了范围为 (1, 10] 的 X 型的 next-key lock，那么另外一个事务在获取相同范围的 X 型的 next-key lock 时，就会被阻塞。

虽然相同范围的间隙锁是多个事务相互兼容的，但对于记录锁，我们是要考虑 X 型与 S 型关系，X 型的记录锁与 X 型的记录锁是冲突的。

插入意向锁

一个事务在插入一条记录的时候，需要判断插入位置是否已被其他事务加了间隙锁（next-key lock 也包含间隙锁）。

如果有的话，插入操作就会发生阻塞，直到拥有间隙锁的那个事务提交为止（释放间隙锁的时刻），在此期间会生成一个插入意向锁，表明有事务想在某个区间插入新记录，但是现在处于等待状态。

举个例子，假设事务 A 已经对表加了一个范围 id 为（3，5）间隙锁。

image-20230507170849038

当事务 A 还没提交的时候，事务 B 向该表插入一条 id = 4 的新记录，这时会判断到插入的位置已经被事务 A 加了间隙锁，于是事物 B 会生成一个插入意向锁，然后将锁的状态设置为等待状态（PS：MySQL 加锁时，是先生成锁结构，然后设置锁的状态，如果锁状态是等待状态，并不是意味着事务成功获取到了锁，只有当锁状态为正常状态时，才代表事务成功获取到了锁），此时事务 B 就会发生阻塞，直到事务 A 提交了事务。

插入意向锁名字虽然有意向锁，但是它并不是意向锁，它是一种特殊的间隙锁，属于行级别锁。

如果说间隙锁锁住的是一个区间，那么「插入意向锁」锁住的就是一个点。因而从这个角度来说，插入意向锁确实是一种特殊的间隙锁。

插入意向锁与间隙锁的另一个非常重要的差别是：尽管「插入意向锁」也属于间隙锁，但两个事务却不能在同一时间内，一个拥有间隙锁，另一个拥有该间隙区间内的插入意向锁（当然，插入意向锁如果不在间隙锁区间内则是可以的）。

插入意向锁是一种特殊的间隙锁，和间隙锁互斥，但不同于间隙锁的是，该锁只用于并发插入操作。

临键锁加锁原则

  • 原则1：加锁的基本单位是next-key lock。next-key lock是前开后闭区间。
  • 原则2：查找过程中访问到的对象才会加锁。
  • 原则3：索引上的等值查询，给唯一索引加锁的时候，next-key lock退化为行锁。
  • 原则4：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock退化为间隙锁。
  • 原则5：唯一索引上的范围查询会访问到不满足条件的第一个值为止。【注释：其实我不认为这是bug，只是唯一索引和非唯一索引保持一致】

Mysql 是怎么加行级锁的

行级锁加锁规则比较复杂，不同的场景，加锁的形式是不同的。

加锁的对象是索引，加锁的基本单位是 next-key lock，它是由记录锁和间隙锁组合而成的，next-key lock 是前开后闭区间，而间隙锁是前开后开区间。

但是，next-key lock 在一些场景下会退化成记录锁或间隙锁。

那到底是什么场景呢？总结一句，在能使用记录锁或者间隙锁就能避免幻读现象的场景下， next-key lock 就会退化成记录锁或间隙锁。

死锁解决策略

当出现死锁以后，有两种策略：

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

减少死锁的主要方向，就是控制访问相同资源的并发事务量。

Mysql 其他

Mysql 引擎

两种存储引擎区别

InnoDB:
  • InnoDB 存储引擎支持事务、支持外键、支持非锁定读、行锁设计其设计主要面向OLTP 应用。
  • InnoDB 存储引擎表采用聚集的方式存储，因此每张表的存储顺序都按主键的顺序存放，如果没有指定主键，InnoDB 存储引擎会为每一行生成一个6字节的ROWID并以此作为主键。
  • InnoDB 存储引擎通过MVCC 获的高并发性，并提供了插入缓冲、二次写、自适应哈希索引和预读等高性能高可用功能
  • InnoDB 存储引擎默认隔离级别为REPEATABLE_READ（重复读）并采用next-key locking(间隙锁)和MVCC来避免幻读
MySIAM:
  • MYISAM 存储引擎不支持事务、表锁设计、支持全文索引其设计主要面向OLAP 应用
  • MYISAM 存储引擎表由frm、MYD 和MYI 组成，frm 文件存放表格定义，MYD 用来存放数据文件，MYI 存放索引文件。MYISAM 存储引擎与众不同的地方在于它的缓冲池只缓存索引文件而不缓存数据文件，数据文件的缓存依赖于操作系统。
    操作区别：

MYISAM 保存表的具体行数，不带where 是可直接返回。InnoDB 要扫描全表。
DELETE 表时，InnoDB 是一行一行的删除，MYISAM 是先drop表，然后重建表
InnoDB 跨平台可直接拷贝使用，MYISAM 不行
InnoDB 表格很难被压缩，MYISAM 可以

引擎选择

MyISAM相对简单所以在效率上要优于InnoDB。

如果系统读多，写少。对原子性要求低,那么MyISAM最好的选择。且MyISAM恢复速度快。可直接用备份覆盖恢复。

InnoDB 更适合系统读少，写多的时候，尤其是高并发场景。

Mysql 主从同步过程

在master机器上的操作

当master上的数据发生改变的时候，该事件(insert、update、delete)变化会按照顺序写入到binlog中。

当slave连接到master的时候，master机器会为slave开启binlog dump线程。如果读取的进度已经跟上了master，就进入睡眠状态并等待master产生新的事件。

当master 的 binlog发生变化的时候，binlog dump线程会通知slave，并将相应的binlog内容发送给slave。

在slave机器上的操作

当主从同步开启的时候，slave上会创建2个线程。

  • I/O线程。该线程连接到master机器，master机器上的binlog dump线程会将binlog的内容发送给该I/O线程。该I/O线程接收到binlog内容后，再将内容写入到本地的relay log。
  • SQL线程。该线程读取I/O线程写入的relay log。并且根据relay log的内容对slave数据库做相应的操作。

执行计划

mysql exlain 各参数解释

1、id 主要是用来标识sql的执行顺序，如果没有子查询，一般来说id只有一个，执行顺序也是从上到下

2、select_type 每个select子句的类型

　　a: simple 查询中不包含任何子查询或者union

　　b: primary 查询中包含了任何复杂的子部分，最外层的就会变为primary

　　c: subquery 在selecth或者where中包含了子查询

　　d: derived 在from中包含了子查询

　　e: union 如果第二个select 出现在union之后，则被标记为union,如果union包含在from子句的子查询中，外层select会被标记成derived

　　f：union result 从 union表中获取结果的select

3、type 是指mysql在表中找到所需行的方式，也就是访问行的类型。从a开始效率上升

　　a （All 全表扫描)

​ b （index 会根据索引树进行遍历）

​ c （range 根据索引范围扫描，返回匹配值域的行）

​ d:（ref 非唯一性索引扫描，返回匹配某个单独值的所有行。一般是指多列的唯一索引中的某一列）

​ e （eq_ref 唯一性索引扫描。表中只有一条与之匹配）

​ f （const、system 主要针对查询中有常量的情况，如果结果中只有一行，会变成System）

​ g （null即不走表 也不走索引）

4、possible_keys 预估计了mysql能为当前查询选择的索引。这个字段是完全独立于执行计划中输出的表的顺序。意味着在实际查询中，可能用不到这些索引。如果该字段为null则意味着没有可使用的索引。这个时候你可以考虑为where后面的字段加上索引

5、key 这个字段表示mysql真实使用的索引。如果mysql优化过程中没有加索引，可以强制加索引

6、key_len 顾名思义就是索引长度字段。表示mysql使用的索引的长度

7、ref 这个字段一般是指一些常量用于选择过滤

8、rows 预估结果集的条数，可能不一定完全准确

9、extra 包含额外的信息。

​ a: using filesort: 说明mysql无法利用索引进行排序，只能利用排序算法进行排序，会消耗额外的位置

​ explain select * from emp order by sal;

​ b: using temporary: 建立临时表来保存中间结果，查询完成之后把临时表删除

​ explain select ename,count(*) from emp where deptno = 10 group by ename;

​ c: using index: 这个表示当前的查询时覆盖索引的，直接从索引中读取数据，而不用访问数据表。如果同时出现using where 表名索引被用来执行索引键值的查找，如果没有，表面索引被用来读取数据，而不是真的查找

​ explain select deptno,count(*) from emp group by deptno limit 10;

​ d: using where: 使用where进行条件过滤

​ explain select * from t_user where id = 1;

​ e: using join buffer: 使用连接缓存

​ f: impossible where：where语句的结果总是false

​ explain select * from emp where empno = 7469;

mysql datetime 和timestamp 的区别

其中，datetime和timestamp这两种类型都是用于表示YYYY-MM-DD HH:MM:SS 这种年月日时分秒格式的数据，但两者还是有些许不同之处的。

  1. 存储范围不同：datetime的存储范围是 1000-01-01 00:00:00.000000 到 9999-12-31 23:59:59.999999，而timestamp的范围是 1970-01-01 00:00:01.000000 到 2038-01-19 03:14:07.999999；
  2. datetime存储与时区无关，而timestamp存储的是与时区有关，这也是两者最大的不同。
  3. datetime适用于记录数据的创建时间，因为这个时间是不会变的。而timestamp有自动修改更新的功能，也就是说，我们对表里的其他数据进行修改，timestamp修饰的字段会自动更新为当前的时间，这个特性称为自动初始化和自动更新(Automatic Initialization and Updating)。

一条SQL语句在MySQL中如何执行的？

客户端请求 —> 连接器（验证用户身份，给予权限） —> 查询缓存（存在缓存则直接返回，不存在则执行后续操作） —> 分析器（对SQL进行词法分析和语法分析操作） —> 优化器（主要对执行的sql优化选择最优的执行方案方法） —> 执行器（执行时会先看用户是否有执行权限，有才去使用这个引擎提供的接口） —> 去引擎层获取数据返回（如果开启查询缓存则会缓存查询结果）

一张表，里面有ID自增主键，当insert了17条记录之后，删除了第15,16,17条记录，再把Mysql重启，再insert一条记录，这条记录的ID是18还是15 ？

如果表的类型是MyISAM，那么是18。因为MyISAM表会把自增主键的最大ID 记录到数据文件中，重启MySQL自增主键的最大ID也不会丢失；

如果表的类型是InnoDB，那么是15。因为InnoDB 表只是把自增主键的最大ID记录到内存中，所以重启数据库或对表进行OPTION操作，都会导致最大ID丢失。

mysql utf8 和 utf8mb4 区别

mysql utf8 字符集最多支持3个字节的字符， 不支持4个字节的特殊unicode。 utf8mb4 增加了对4个字节字符的支持。

磁盘中数据的存取

以机械硬盘来说，先了解几个概念。

扇区：磁盘存储的最小单位，扇区一般大小为512Byte。

磁盘块：文件系统与磁盘交互的的最小单位（计算机系统读写磁盘的最小单位），一个磁盘块由连续几个（2^n）扇区组成，块一般大小一般为4KB。

磁盘读取数据：磁盘读取数据靠的是机械运动，每次读取数据花费的时间可以分为寻道时间、旋转延迟、传输时间三个部分，寻道时间指的是磁臂移动到指定磁道所需要的时间，主流磁盘一般在5ms以下；旋转延迟就是我们经常听说的磁盘转速，比如一个磁盘7200转，表示每分钟能转7200次，也就是说1秒钟能转120次，旋转延迟就是1/120/2 = 4.17ms；传输时间指的是从磁盘读出或将数据写入磁盘的时间，一般在零点几毫秒，相对于前两个时间可以忽略不计。那么访问一次磁盘的时间，即一次磁盘IO的时间约等于5+4.17 = 9ms左右，听起来还挺不错的，但要知道一台500 -MIPS的机器每秒可以执行5亿条指令，因为指令依靠的是电的性质，换句话说执行一次IO的时间可以执行40万条指令，数据库动辄十万百万乃至千万级数据，每次9毫秒的时间，显然是个灾难。

实际上，操作系统读取硬盘的时候，不会一个个扇区的读取，这样效率太低，一般一次性连续读取8个扇区(4KB)来当做一块，这种由多个扇区组成的块，是文件存取的最小单位。

一次IO，不光把当前磁盘地址的数据，而是把相邻的数据也读取到内存缓冲区中。这叫局部预读性。innodb存储引擎一页16k，即一次IO读16k导内存中。

为了减少io次数，我们迫切需要这样的数据结构和算法：

  1. 需要一种数据存储结构：当从磁盘中检索数据的时候能，够减少磁盘的io次数，最好能够降低到一个稳定的常量值
  2. 需要一种检索算法：当从磁盘中读取磁盘块的数据之后，这些块中可能包含多条记录，这些记录被加载到内存中，那么需要一种算法能够快速从内存多条记录中快速检索出目标数据

​ 因此，B+树得以在innodb引擎中得以使用

B+树的扇出系数、高度、存储容量 、io次数

  • h：统称索引的高度；
  • h1：聚簇索引的高度；
  • h2：二级辅助索引的高度；
  • k：中间结点的扇出系数。

B+树的io次数

从索引结构可以看出，每次查询都要访问到叶子结点，其访问的页面数正好就是索引的高度h。例如，一次主键上的点查询SELECT * FROM USER WHERE id=1，那么要查询h1个页面才能找到叶子结点里的行数据，也即进行h1次页面I/O。（假设索引未加载到内存中）

综上，查询对应的页面I/O数跟利用的索引有关，主要分为以下几种情况：

  • 点查询：
    • 聚族索引：h1
  • 二级索引：
    • 覆盖索引：h2
    • 回表查询：h2+h1
  • 范围查询：这种情况相对比较复杂，但跟点查询的原理类似，可以根据树的结构和页的存储形式进行分析；、
  • 全表查询：B+树的叶子结点是通过链表连接起来的，对于全表查询，需要从头到尾将所有的叶子结点访问一遍。

索引扇出系数、高度、存储容量的计算

innodb 的每一个叶子节点， 和中间节点， 都是按照页的方式进行存储的(16k)

我们假设中间结点的扇出系数为k、叶子结点的行记录数为n，则叶子结点数为k^(h-1)，总记录数为k^(h-1)*n。

在InnoDB里，每个页面默认16KB，假设主键是4B的int类型。对于中间节点，每个主键值后有个页号4B，还有6B的其他数据(参考《MySQL技术内幕：InnoDB存储引擎》)，那么扇出系数k=16KB/(4B+4B+6B)≈1170。我们再假设每行记录大小为1KB，则每个叶子结点可以容纳的记录数n=16KB/1KB=16。

在高度h=3时，叶子结点数=1170^2 ≈137W，总记录数=1170^2*16=2190W！！也就是说，InnoDB通过三次索引页面的I/O，即可索引2190W行记录。

同理，在高度h=4时，总行数=1170^3*16≈256亿条！！！

mysql binlog 里的内容和作用

概述：MySql二进制日志（binlog），也叫变更日志（update log），是Mysql中非常重要的日志。二进制日志中记录了对Mysql数据库执行变更的所有操作，并且记录了语句发生时间、执行时长、操作数据等其他额外信息，但是他不记录select、show等不改变数据的SQL语句。binlog主要用于数据库恢复和主从复制以及审计操作，如果Mysql数据库意外停止，可以使用binlog来查看用户执行了哪些操作，对于数据服务器文件做了哪些修改，然后根据binlog中的记录来恢复数据库服务器

内容：记录了对Mysql数据库执行变更的所有操作，并且记录了语句发生时间、执行时长、操作数据等其他额外信息，但是他不记录select、show等不改变数据的SQL语句。

作用:

（1）基于时间的恢复：在备份文件恢复的基础上，通过binlog可以将数据库恢复到某一个时间点。

（2）主从复制，在主从复制的模式下，必须在主服务器上开启binlog日志。

（3）操作审计：对所有更改数据的操作进行审计。

binlog 生成的时机是事物提交的时候

MySQL 里记录货币用什么字段类型好

NUMERIC 和 DECIMAL 类型被 MySQL 实现为同样的类型，这在 SQL92 标准允许。他们被用于保存值，该值的准确精度是极其重要的值，例如与金钱有关的数据。当声明一个类是这些类型之一时，精度和规模的能被(并且通常是)指定。

例如：

salary DECIMAL(9,2)77在这个例子中，9(precision)代表将被用于存储值的总的小数位数，而2(scale)代表将被用于存储小数点后的位数。因此，在这种情况下，能被存储在 salary 列中的值的范围是从-9999999.99 到9999999.99。

什么是临时表，何时删除临时表？

EXPLAIN 查看执行计划结果的 Extra 列中，如果使用 Using Temporary 就表示会用到临时表。

什么是临时表?MySQL 在执行 SQL 语句的过程中 通常会临时创建一些存储中间结果集的表，临时 表只对当前连接可见，在连接关闭时，临时表会被删除并释放所有表空间。

临时表分为两种:一种是内存临时表，一种是磁盘临时表，什么区别呢? 内存临时表使用的是 MEMORY 存储引擎，而临时表采用的是 MylSAM 存储引擎。

MySQL 会在下面这几种情况产生临时表。

  • 使用 UNION 查询:UNION 有两种，一种是 UNION，一种是 UNION ALL，它们都用于联合查询;区别是使用 UNION 会去掉两个表中的重复数据，相当于对结果集做了一下 去重(distinct)。使用 UNIONALL，则不会排重，返回所有的行。使用 UNION 查询会产生临时表。
  • FROM 中的子查询;
  • 使用 TEMPTABLE 算法或者是 UNION 查询中的视图。TEMPTABLE 算法是一种创建临时表的算法，它是将结果放置到临时表中，意味这要 MySQL 要先创建好一个临时表，然后将结果放到临时表中去，然后再使用这个临时表进行相应的查询。
  • ORDER BY 和 GROUPBY 的子句不一样时也会产生临时表。
  • DISTINCT 查询并且加上 ORDER BY 时;
  • SQL 中用到 SQL_SMALL_RESULT 选项时;如果查询结果比较小的时候，可以加上 SQL SMALL RESULT 来优化，产生临时表

Mysql count 效率

count(1)、 count(*)、 count(主键字段)在执行的时候，如果表里存在二级索引，优化器就会选择二级索引进行扫描。如果没有二级索引会进行主键扫描。

所以，如果要执行 count(1)、 count(*)、 count(主键字段) 时，尽量在数据表上建立二级索引，这样优化器会自动采用 key_len 最小的二级索引进行扫描，相比于扫描主键索引效率会高一些。

再来，就是不要使用 count(字段) 来统计记录个数，因为它的效率是最差的，会采用全表扫描的方式来统计。如果你非要统计表中该字段不为 NULL 的记录个数，建议给这个字段建立一个二级索引。

刘小恺(Kyle) wechat
如有疑问可联系博主