mysql面试题总结（存储、查询、写操作、实现事务、加锁）

数据存储

• 
• 表数据是存储到用户表空间的数据文件(ibd)和系统表空间文件(ibdata1)
• 磁盘和内存交互的最小单位：数据页
• 内存中数据页和索引页

MySQL磁盘文件介绍

• 查看磁盘文件 show variables LIKE ‘%dir’ 默认在/var/lib/mysql
• 重做日志文件(redolog) ib_logfile0 ib_logfile1
• 回滚日志(undo log) ibdata1
• MySQL都会为表生成一个frm为后缀名的文件，这个文件记录了这个表的表结构定义。
• InnoDB数据文件
  • ibd文件，使用独享表空间存储表数据和索引信息，一张表对应一个ibd文件
  • ibdata1文件，使用共享表空间存储表数据和索引信息，所有表共同使用一个或者多个ibdata文件
• MyISAM数据文件
  • myd文件，主要用来存储数据信息
  • myi文件，存储数据文件中任何索引的数据树

MySQL索引结构

• 

• 表空间中的数据，逻辑上的关系:一个表空间–>多个数据段–>多个区–>多个页(B+树的叶子节点)–>行记录

• 表空间，所有数据都被逻辑的存放在一个空间中，从功能分为系统表空间、独占表空间、通用表空间、临时表空间、Undo表空间。

• 常见的段有 数据段、索引段、回滚段

• 一个区由64个连续的页组成，一个区的大小=1M=64个页（16k）

• 页默认大小16kb，是InnoDB管理磁盘的最小单位，也是InnoDB中磁盘和内存交互的最小单位，

• 索引数上一个节点就是一个页

如何查询一条SQL语句

• 

• 连接器：客户端和服务器建立连接

• 查询缓存：MySQL不推荐使用查询缓存，查询缓存往往弊大于利 8.0版本之后删除这个功能

• 解析器:将客户端传递的SQL文本，进行词法解析和与语法解析，并通过预处理器，检测是否合法，形成解析树

• 优化器:MySQL里使用的是基于成本模型的优化器，优化处理包括：

  • 选择合适的执行计划(索引的选择)
  • 一个语句有多表关联（join）时，决定各个表的连接顺序，以哪个表为基准表

• 执行器:先判断是否有权限，再调用存储引擎接口，提取数据

索引介绍

• 主键索引的叶子节点会存储数据行，辅助索引只会存储主键值。
• InnoDB要求表必须有一个主键索引(MyISAM 可以没有)
• InnoDB的辅助索引只会存储主键值而非磁盘地址
• 是一棵B+数
• 

磁盘数据加载到InnoDB内存

• 扇区是磁盘存储的最小单位，大小是512字节
• 操作系统一般是以4kb的单位读取磁盘，读取后数据会被缓存在内存，这个操作为预读
• 读取数据
  • 磁头移动到数据所在磁道
  • 磁盘旋转,将数据所在的扇区移至磁头之下
  • 磁盘继续旋转,所有所需的数据都被磁头从扇区中读出。
• 随机写文件 数据 寻道 慢
• 顺序写文件 日志
• 数据–>随机文件(数据增删很频繁)
• 配套日志–> 顺序文件(写很快，都是增量数据) – 数据恢复
• 存储引擎层: 内存: Buffer Pool(数据页和索引页)
• 磁盘 数据文件(ibd)
• 查询根据查询计划，调用InnoDB的存储引擎,从磁盘文件中读取数据。将数据先存储到InnoDB的内存中，也就是Buffer Pool（包含数据页和索引页）
• 脏页(内存中的数据和磁盘中的数据不一致时，就称为脏页)
• 发生增删改，是先改的InnoDB中内存数据
• 脏页落盘(数据的写操作) WAL (预写日志)先写日志，再脏页落盘
• 记账 （当日的记账流水，总账单undo log）

MySQL的写操作

• 不是一旦发生修改操作，就将数据写入磁盘，频繁的写磁盘，会增大IO消耗 ，可根据checkpoint检查点择时刷新脏页
• innodb_flush log_at_trx_commit :取值0、1(每秒写一次磁盘)、2
• 只要数据页发生了修改，就会将该页的数据写入redo log Buffer
• WAL: 预写Redo Log,为了提高Redolog的写文件的性能提升，又给Redolog使用了内存缓存。
• Force Log at Commit:为了保证redolog buffer在MySQL服务器发生宕机的情况下，保证一定将数据写入磁盘。
• 只要事务提交，立即刷新redolog buffer
• redo log file(重做日志文件)：循环使用的文件 ib_logfile0 ib_logfile1 如果没有数据丢失，一般不会使用redolog中
• 当redolog中数据不够时、Buffer Pool不够用时，会触发检查点机制，触发脏页落盘，对应的数据已经落盘，可以删除
• 为了保证脏页落盘：double write 双写日志
• mysql 16k（索引最小单位）操作系统 4k（磁盘空间）
• 
• 
• 

脏页(数据页被修改)落盘

• checkpoint检查点：脏页什么时候落盘
  • Buffer Pool满了 触发落盘
  • redo log文件满了 触发落盘
• redolog的预写日志机制WAL：保证脏页落盘失败，数据已经完成持久化
• redolog buffer
• force log at commit

MySQL是如何实现事务的

• 原子性，持久性和一致性主要是通过redo log、undo log、Force Log at Commit和Double Write机制来完成的。
• redo log用于在崩溃时恢复数据
• undo log用于对事务回滚时进行撤销，也会用于隔离性的多版本控制。
• Force Log at Commit机制保证事务提交后redo log日志都已经持久化。
• Double Write机制用来提高数据库的可靠性，用来解决脏页落盘时部分写失效问题。

事务并发问题回顾

• 脏读: A会话读到了B会话未提交的数据。
• 不可重复读: A会话两次查询,查询的结果记录数是一致的,但是记录本身不一致。B会话在两次查询之间发生update语句
• 幻读: A会话两次查询,查询的结果记录数是不一致的。 B会话在两次查询之间发生insert delete语句

事务隔离级别

• RU（READ UNCOMMITTED）：读未提交 会产生脏读
• RC（READ COOMITTED）：读已提交 会产生不可重复读、幻读
• RR（REPEATBLE READ）：可重复读 会产生脏读（MySQL的RR级别下，脏读问题不存在）可解决幻读问题
• Serializable 不存在事务并发问题，但是效率不高，因为读写都加锁

MVCC机制

• MySQL的RC和RR都是通过MVCC机制去实现的。
• MVCC：多版本并发控制，实现的逻辑就是读不加锁，写加锁，读写不冲突
• ibdate1中的回滚段
  • id、name、trx_id、roll_point、row_Id
  • roll_point 指向上一版本的trx_id

ReadView

• 在使用MVCC进行读写操作的时候，select 语句到底应该读哪个版本，取决于ReadView
• ReadView里面存储的就是当前所有的活跃事务ID（最大事务ID、xxxx、最小事务ID），把事务id放在一个列表中，把这个列表命民为m_ids
• ReadView什么时候产生
  • RC 事务中每个SELECT语句，都会实时产生一个ReadView
  • RR 事务中第一个SELECT语句，产生一个ReadView
• trx_id < 最小事务Id，可见
• trx_id > 最大事务id 不可见
• 最小事务Id <= trx_id <= 最大事务Id ，trx_id在m_ids中 不可见 否则可见

MySQL是如何加锁的

• 

• insert、update、delete ： 加锁

• for update 加锁

• MySQL如果要加行锁，首先明确我们使用的是InnoDB存储引擎

• InnoDB的存储引擎加行锁，其实是针对索引去添加行锁的

• RR隔离级别下：记录锁、间隙锁（解决幻读）、临建锁

• RC隔离级别下：记录锁