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隔离级别下:记录锁