MySQL知识回顾

MySQL自测题目

1. 谈谈MySQL的基础架构?

MySQL的基础架构可以分成是Server层和存储引擎层,其中Server层负责提供的是顶层的服务接口,顶层的服务接口通过调用底层的存储引擎来完成顶层的Server层的相关操作,而Server层是如何去调用存储引擎的API的话,它实际上可以通过一条SELECT的SQL语句来进行说明

首先,当用户编写了一条SQL,然后会将这条SQL发送MySQL进行执行

首先是用户发起登录请求,它会将用户名和密码基于TCP连接,将相关的消息发送到连接器上,连接器负责建立连接,并且在内部创建一个工作内存,此后此连接的所有操作都是基于连接中的内存实现的,因此当重置连接的时候,这个内存就会被释放

接着,会查询MySQL的内部中是否有一个缓存,通常来说,它的检查逻辑是这样的,首先检查你给的这条SQL是否执行过缓存,如果你执行的SQL在缓冲中命中了,那么就会直接返回,不会执行后面非常复杂的逻辑

否则的话,就会走下面的SQL编译执行流程

首先MySQL拿到你的SQL之后,它想要知道你的SQL想要干什么,因此它首先会经过一个分析器,这个所谓的分析器会经过两个流程,首先是经过一个词法分析,就是判断解析你的SQL中的关键字,比如说动作关键字,表名关键字,列名关键字等,然后解析完毕之后,就会经过语法分析,然后就判断你的语法是否正确,如果语法正确,那么就说明:MySQL已经知道了你的SQL想要干什么了

接着MySQL知道了你的SQL想要干什么,接下来它就会想要知道你的SQL要怎么做,怎么做是优化器决定的,它会根据你的SQL结构以及相关表中的索引等数据结构,来确定你的SQL具体如何执行

通过了优化器之后,MySQL就知道了怎么做了,然后就会将执行计划生成,下放到执行引擎中,执行引擎通过调用底层的存储引擎提供的接口,执行语句之前会先判断是否有权限

为什么要判断是否有权限?

这是因为在执行过程中,可能会涉及到触发器,那么触发器的执行是无法预料的,因此在这个过程还需要执行相关的权限验证

2. MySQL提供了哪些存储引擎?

可以通过show engines来查看MySQL提供的所有存储引擎

一般来说,MySQL提供了

InnoDB:默认存储引擎,提供事务,外键,行级锁、MVCC等高级特性,当读写操作较多,需要在并发环境下执行的话就可以使用这个引擎,核心设计理念是索引即数据

MyISAM:不支持事务,不支持外键,仅支持表级锁,在需要事务的是不要使用这个引擎,在并发下由于只支持标记锁,因此性能很差

Memory:内存存储驱动,不支持事务,不支持外键,只支持表锁,基于内存的读写快,不需要IO

MyISAM和InnoDB的区别是什么?

MyISAM通常来说支持读多写少的环境,但是它在崩溃后,没有日志来提供崩溃后的恢复

InnoDB通常来说在需要使用高并发的环境下使用

MyISAM为什么读的性能比InnoDB要高?

在InnoDB中是支持行锁和表锁的,并且在事务开启的时候,需要动态维护MVCC,在使用count的时候就它是通过扫描全表来执行的,它的索引结构是B+Tree,因此可以看出,MySQL在执行查询语句的时候,尽管你没有涉及到多事务的并发读写操作,它还是会在底层维护view等操作,这些额外的操作都会导致额外的性能开销

但是在MyISAM中,由于只有简单的表级锁X/S锁,因此在读多写少的情况下,只需要上一个共享的读锁就可以一直读写了

同时在索引的组织上,MyISAM和InnoDB索引存储方式是不一样的,InnoDB的表示根据主键展开的B+树的聚集索引,MyISAM中设计理念是说将索引和文件分开,然后通过索引文件来查询对应主键在数据文件中的偏移量,从索引的这个角度来说,InnoDB需要缓存数据块,MyISAM只需要缓存索引块,然后InnoDB寻址次数多,也就是说需要从索引找到块,然后还要从块找到行,而MyISAM的执行则是通过记录偏移量,将文件起始地址+记录偏移量就可以了

3. 什么是事务

事务就是MySQL通过begin开始，以rollback/submit结束的一组原子性的指令,这些指令是原子性执行的不可分割,要么一起成功,一起失败,事务有ACID四大特性,这些特性就是:

• 原子性,它在InnoDB是基于undo log实现的,undo log的具体功能就是撤销数据的操作,比如说在SQL中执行insert、update、delete等语句的时候,它就会相对的,记录下来这条记录的ID,如果要回滚,那么就删除,如果是update,那么就相关的字段修改回来,如果是delete,那么就将原来的记录插入回来,由此实现了原子性
• 持久性,它在InnoDB是基于redo log实现的,redo log相比于undo log,它更加底层,它记录的是某个数据块中具体的操作,比如说修改了某个表空间中的某个位置上的物理量,那么它就会将这个redo log写入到磁盘文件中,然后基于这个文件执行初始化
• 隔离性:简单地说,就是控制多个事务并发执行的时候,数据库中的这些共享的数据的访问不会错乱,通常它解决的是读写冲突/写写冲突的问题,写写冲突是脏写问题,因此是不允许发生的,但是在数据库中,一定程度的读写冲突是允许的,因此就有了事务的不同隔离级别,也就是可串行化、可重复读、已提交读、未提交读,在串行化的隔离级别下不会产生并发问题,可重复读会带来幻读问题,已经提交读会带来不可重复读的问题,未提交读会导致脏读的问题
• 一致性:一致性是通过上面三个特性来实现的

4. 不可重复读和幻读有什么区别?

首先要了解什么是不可重复读和幻读

不可重复读:相同的SQL语句,在同一个事务先后没有对相关数据做任何操作,查询出来的结果却不一样,它针对的是查询结果的记录中,这些记录的数据发生变化了

幻读:相同给的SQL语句,在同一个事务先后没有对相关数据作任何操作,查询出来的结果的条数却发生了变化,它针对的是查询结果的记录中,这些记录的条数发生了变化

幻读从定义上理解,其实不就是不可重复读吗?

可以这么理解,但是从解决这两个问题的角度上来讲,解决不可重复读只需要对相关数据加锁就可以解决数据被其他事务篡改的问题了,但是幻读引发的症结是新插入的数据,而MySQL不可能对还不存在的记录加锁,因此解决幻读和不可重复读的方案是不一样的,一般来说,解决了幻读问题,那么也就解决了不可重复读,基于这个现象,就可以将对应的事务隔离级别进行再次划分。

那么MySQL是如何解决幻读问题的?

MySQL行级锁+MVCC来解决幻读问题的,行级锁一般分为有next-key lock、redocrd lock、gap lock,这些锁锁住的是一个区间,从现象上来讲这些区间中不允许插入别的数据,但是它其实是锁住了索引,避免了数据的插入而已,同时在多事务并发查询+快照读的机制下,实现了MVCC,基于undo log版本链就可以实现不同的事务在一定的启动时机内,看到的数据内容和事务刚启动时的一样

这样就解决了在其他事务并发穿插执行的时候,对数据进行新写入的时候,本事务还能看到的问题

注意,但是它并没有彻底解决幻读问题,在当前事务存在一个当前读的情况下,还是不能够解决幻读问题,比如:

trx1:select * from tb where id = 1;#查询为null
trx2:insert into tb(id,name) values(1,'zhangsan');
trx1:update tb set name = 'lisi' where id = 1;#更新成功,这是因为update默认就是当前读

可以在开启事务的时候,同时使用锁定读,这样就可以给相关的数据上一个next-key lock了,避免了相关的间隙和相关的记录被修改

可以这样总结

• 当使用的是快照读的时候,它是基于MVCC来解决幻读问题的,在RR的隔离级别下,除非中途遇到当前读的语句,否则都将一直复用事务启动的时候的ReadView
• 当使用的是当前读的时候,会给相关的记录上一个next-key lock,从而避免了其他事务在这个区间插入数据,同时也避免了其他事务读这条记录的修改操作

5. 什么是MVCC?原理是什么?

MVCC的实现三剑客是undo log、roll poniter、trx_id,这三个字段共同实现了MVCC,在readview(快照读)的辅助下实现了MVCC

首先先来讲讲什么是快照读,快照读实际上就是当前事务执行环境的一个上下文环境,通常包括有四个字段

• m_ids:当前活跃中的事务id,还未提交
• min_id:活跃事务的最小id,这个字段用来标记那些一定已经提交的事务
• max_id:即将分配的下一个事务id
• creator_trx_id:当前事务ID

通过这个readview就可以实现快照读或者当前读,以可重复读的隔离级别下的运用来说:

首先当用户发起一条SQL,注意这个SQL是不加for update或者in share mode的,因此这样的话就是快照读,然后先去索引树中查询对应的索引的位置,然后找到记录中,注意,这时候记录中的隐藏字段中记录了当前记录被哪个事务更改了,也就是这条记录的操作者事务,通过这个id就可以判断记录是谁修改的,具体的规则是这样的:

• 当记录头中记录的trx_id是大于max_id的,那么就证明这个事务是晚于当前事务启动的,对当前事务不可见,于是顺着roll_poniter去查询下一个undo log中记录的记录
• 当记录头中记录的trx_id是小于min_id的,那么就证明这个记录在当前事务启动之前就提交了,因此可见,直接返回到上层
• 当记录中记录的trx_id介于[min_id,max_id]之间,那么就证明这个事务可能是并发执行的,因此就查询这个trx_id是否在m_ids中,如果在的话那么就证明这个事务还没提交,修改是不可见的,否则的话就是可见的。因为在事务开始启动的时候,这个事务已提交,可见

6. 详细解释一下InnoDB中的事务隔离级别和实现原理

读未提交的实现,对并发事务不做控制

串行化:通过加读写锁来实现,也就是说当对冲突数据进行操作的时候,基于S/X锁来实现事务的串行化读写

读提交:基于MVCC(快照读)中的ReadView来实现,它在每一次执行select的语句的时候都会重新生成一个readview,从而确保读取的数据是最新的事务提交的

可重复读:基于MVCC(快照读)和Next-key Lock实现的,在事务开始的时候就会生成一个ReadView,在之后就一直使用这个ReadView

7. 关于count()函数的使用

count()函数是一个聚合函数,它的主要作用是查询表中某个字段不为NULL的记录条数

因此通过这个函数的使用就可以实现表中记录条数的查询

用count(*)哪个存储引擎会更快?

使用MyISAM会更快,首先先来了解一下count()函数的执行流程,在通过count()函数来统计有多少个记录的时候,MySQL的server层会维护一个count的变量,在InnoDB下,它会通过调用底层存取数据的API,通过这个API,如果指定的字段的值不为NULL,那么就会将变量+1,直到退出循环,最后将count的值发送给客户端

而在MyISAM中,执行count()函数的方式是不一样的,通常在没有任何查询条件下的count(*),MyISAM速度要快于InnoDB,这是因为在底层的MyISAM所驱动的表中,表的元数据都会存储一个row_count的值,因此具体的查询只需要读取这个变量即可

当使用where的时候,两个的执行逻辑都需要对表进行全表扫描

8. 表级锁和行级锁有什么区别?

表级锁和行级锁的区别在于锁的作用范围,通常来说表级锁是一个大类,下面包含了不同的实现,具体来说就是针对不同场景的实现

• 当需要对表的结构进行修改的数据,那么就是上元数据锁
• 当需要对全表的数据进行修改的话,那么就是上表锁
• 当需要对全表的一个公共字段,比如说自增ID进行共享访问的时候,这时候就是上一个AUTO-INC锁

具体的,当给表上一个S锁的时候,那么其他任何线程都可以做一个读操作,并让自己持有这把锁,当有线程试图对这个表申请一个X锁的时候,就会被阻塞,当线程持有读锁,但是尝试进行写操作的时候,此时就会提示非法操作

当给表上一个X锁的时候,那么其他任何线程都不得操作,体现的是互斥

9. 什么是元数据锁?

元数据的具体实现就是MySQL中的information_schema,这个表中记录了各个表的名称以及外键索引等详细信息,那么元数据就是说在事务修改这个表的结构的时候,对这个表进行上锁,从而使得其他任何试图修改这个表的线程都会被阻塞

有什么注意点?

当使用元数据锁的时候,要避免阻塞,这是因为元数据锁在执行语句的时候上锁,在事务提交的时候解锁,因此如果在一个长事务中使用元数据锁,就有可能导致请求挤压

10. 什么是意向锁

假设这样一个场景,一个表中有100w条数据,其中有一条记录加了行锁,然后现在有一个线程来了,试图加一个表锁,那么它就要扫描这100w数据,效率是非常低的

但是如果我在上这个行锁之前，给表加一个意向锁,当有一个线程检测到这个表上有意向锁,那么就意味着这个表中有行锁,无法上表锁了,简单的一次检测就可以避免检查表中所有的数据

11. 什么是AUTO-INC锁?

Auto-INC实际上是对表中的共享数据自增ID进行保护的手段,这个锁的实现方式有三种

在整条数据执行完毕后才会释放锁

在字段被赋值之后就会释放锁

在插入的语句条数是可以提前预知的时候,就可以在字段被赋值后就释放锁,在语句的条数不可预知的时候就使用第一种策略

Auto-INC锁有什么问题?

它在主从复制场景下,如果使用第二种策略的话就可能导致主库和从库数据的不一致,在主从复制的情况,如果规定binlog的行格式为STATEMENT的时候,这时候记录的是原始逻辑,在这种情况下,因为binlog中规定事务的语句是必须记录在一起的

主库中的id不是连续的,因为发生了并发

而从库中的id是连续的,因为在重放binlog的时候是直接按照原始逻辑进行重放的,而在从库的binlog中, 这写语句都是有序的,最终导致主库和从库的数据不一致

可以将binlog的日志格式设置为row,这样的话就可以记录实际的值了,但是会提高binlog的存储占用

12. 什么是行级锁?行级锁有哪些?

什么是行级锁?

InnoDB是支持行级锁的,而MyISAM是不支持行级锁的,也支持事务,行级锁的意思是说锁的粒度是以行为单位的,它的锁定单位是在表以下的,也就是说InnoDB的行级锁在上一个行级锁的时候,并不会导致其他线程对表的全部操作都被阻塞了

什么是锁定读

锁定读和快照读是相对的,具体来说:

锁定读:锁定读就是说在select语句的执行记录下当前的结果集,同时给这个结果集加锁,在以后的其他线程对这个数据进行访问的时候,根据in share mode还是for update来判断是否可以共享数据,锁定读,对范围内的数据将会产生一个独占和互斥的效果

快照读:快照读就是说在select语句的执行记录下当前的结果集,但是不加锁,然后后续根据这个快照读中的上下文信息来判断哪些数据是可见的,从而到达事务读取数据的时候,读取的数据总是和事务开始的时候一致的情况

行级锁有哪些?

行级锁是InnoDB引擎所特有的,其他的存储引擎并不支持这个行级锁,它以最小粒度控制了对记录的修改的并发,锁的类型有:

• Record Lock:锁定记录,锁定区间是[x,x]
• Gap Lock:锁定一个范围,不包含单条记录,也就是(x,y)
• Next-key Lock:锁定一个范围,包含有单条记录,也就是(x,y]

什么是插入意向锁?

插入意向锁可以看成是一种特殊的间隙锁,如果间隙锁锁住的是一个区间,那么插入意向锁住的就是一个点:

与之发生冲突的主要是GapLock、NetKeyLock这两种锁

# 假设目前存在的是1,5这两条记录
txA:
insert into tb(id,name) values(3,'a')
...事务操作
commit;
txB:
insert into tb(id,name) values(4,'b');

在插入这条记录的时候,它看到已经被上了一个(1,5)的锁,这时候就会被阻塞,底层原理是先在内存中生成这个锁的结构,然后将这个锁的锁状态设置为等待,然后加入到等待队列中

当事务A提交了事务,释放锁的时候,就会将等待的锁的状态设置为就绪,此时就可以使用了

13. 什么SQL语句会加行级锁?

select * from tb where id = 1 for update;#加的是独占写锁
select * from tb where id = 1 in share mode;#加一个写锁
update tb set name = 'haha' where id = 1;
delete from tb where id = 1;

14. 到底是如何加锁的?

MySQL的加锁原则

• 只有访问到的对象才会加锁
• 锁定的是索引
• 加锁的基本单位是Next-Key Lock,在特殊情况下会退化成Record Lock和Gap Lock

唯一索引等值查询是如何加锁的?

首先要记住,加行级锁的目的是为了解决幻读,那么如果要理解如何加锁,那么就要理解幻读的问题是如何产生的

select * from tb where id = 1 for update;

查询这个数据,什么情况下会出现幻读?

第一种情况,当这条数据存在的时候,如果发生了幻读,那么就是说这个查询语句一开始查询出来是有的,后面就没有了,因此这时候上锁的是[1,1]这个区间,锁的是这个记录,因此只要我锁住了这条记录,就可以避免记录被删除,从而杜绝了幻读现象的产生

第二种情况,当这条数据不存在的时候,如果发生了幻读,那么就是说这条查询语句一开始查询出来是没有的,后面就有了,为了避免在两条数据的中间插入数据,因此要上的是间隙锁,是一个开区间的锁,比如说你有{0,5}这两条数据,那么你上的锁区间就是(0,5),如果说你有{5}这条数据,那么上锁的就是(-无穷,5)这个区间,上锁的是哪个索引?上锁的就是5这个索引,比如说:

当只有{5}这条数据的时候,那么它会走到最小记录上,然后检测下一条数据上是否上了间隙锁,如果上了间隙锁,那么就无法插入数据了

唯一索引的范围查询

假设有数据id=>{0,1,2}那么什么情况下会发生幻读呢?

select * from tb where id >= 1 for update;

首先确定遍历的区间是[1,+无穷),只有访问到的记录才会被上锁,因此对于每一条记录都会上一个next-key lock,也就是当id = 1的时候,它首先会给id = 1的记录上一个RecordLock,这是为了放置数据被篡改,然后为了避免幻读问题,会上一个next-key Lock,也就是(1,2]的锁,然后对于后续的记录,我们也不允许插入,于是上一个(2,+无穷)

+无穷如何在MySQL中表示

我们说页的数据表示中,第一条记录和最后一条记录都有特殊的行来代替,因此实际上是锁定了(2,特殊记录)

好了,知道了上面的逻辑,那么如何来判定呢?

select * from tb where id > 1 for update;

从解决幻读的角度来看待问题,我们的搜索区间是(1,+无穷),我们的数据是id=>{0,1,2}

因此首先为了避免(1,2)中产生新的数据,同时避免数据[2,2]被篡改,因此一开始上的是(1,2]的next-key lock,然后最终再上一个(2,特殊记录)

select * from tb where id <=1 for update;

从解决幻读的问题的角度来看,首先会上一个next-key lock,这里要注意上锁的顺序啊,它是从左向右扫描的,因此这样的话,就是从最左边开始遍历,遍历到特殊记录,也就是(特殊记录,0]

然后继续遍历,此时上的锁的区间应该会是(0,1],然后继续向右扫描,此时没有符合条件的了,结束

为什么不用锁(1,2)?

这是因为这个是唯一索引,因此可以避免再插入一条相同值的记录,因此不用锁这个区间

而在非唯一索引的情况下,就要考虑这个问题了

非唯一索引的等值查询

假设一条SQL

select * from tb where id <= 1 for update;

那么这时候加锁会发生什么呢?这时候数据是id=>{0,1,2}

首先从左到右执行

第一阶段加锁,先加上(-无穷,0]

第二阶段加锁,加上(0,1]

第三节阶段加锁(1,2)注意上一个间隙锁,这里是因为非唯一索引,可能会插入两条相同的记录

15. 没加索引会锁全表吗?

update没加索引会导致锁全表

这是因为next-key lock是为了避免幻读的,因为name这个字段是无序的,因此它在表中任意一个位置都有可能出现,那么为了避免幻读,就必须在所有的记录中都加上这个锁,从而就能够避免插入一些name = xxx的记录,这是因为update的时候可能导致并发,为了保证安全必须加一个锁,而且这个锁不是执行完update就会释放的,而是在等事务结束的时候才会释放掉

16. 什么是索引?索引有哪些类型?有什么优缺点?

关于什么是索引这个问题,可以查书为例,当我们需要查阅书籍的时候,就可以基于索引,实现快速查询,比如说查字典,如果我们想要查询张这个字,那么我们就可以从Zhang开始的那一页开始查找

换到数据库中,索引的定义就是帮助存储引擎快速获取数据的一种数据结构,形象地说,索引就是数据的目录

存储引擎,说白了就是如何存储数据,如何为存储的数据建立索引和如何更新,查询数据等技术的实现方法

常见的索引模型有B+树、hash、数组

**关于树 **

关于二叉搜索树，二叉树搜索树一个节点只有一个指针,因此一次的磁盘IO就只能够找到两个索引,这样的话就会导致树很高,比如说100w条数据需要20次的磁盘IO,这就导致加载速度慢了,而且索引的维护也是个问题,通常来说磁盘的IO是以数据块为单位的,如果一个数据块只能寻址两个数据,那么就太浪费了

那么解决这个问题的方法就是扩充一个数据块中的寻址限制,也就是通过一个数据块中的内容能够执行尽可能多的寻址操作

B-/B+树对寻址过程进行了优化,它允许一个节点具有多个指针,因此的话,一次IO操作就可以尽最大程度地传送尽可能多的数据项,数据项的个数和数据块的大小和索引项的大小相关,它设计的基本思路是一个数据块中可以存储多个指针,然后这些指针可以指向到下一层中的节点,其实这种数据结构就和操作系统中的多级页表是类似的,多级页表在页表项的基础上添加了页表项的页表,从而可以快速查找到每一项数据所在的页,而不需要将所有的页都装载到内存中,而是通过根节点到叶子节点的查询,就可以通过很少次的IO就可以将需要的页装载到内存中了

B-树和B+树有什么区别?

首先B-树是允许非叶子节点存储数据记录的,而B+树是仅在叶子节点中存储数据记录：

• B-树的查找效率不稳定,同时可能导致树的高度增高,导致IO的次数变多
• B+树的查找效率是稳定的,因为每次查找数据都需要到达叶子节点,由于索引项都是存在非叶子节点上,因此在这种情况下,能够保证非叶子没有冗余的数据项,最终的效果就是限制了树的高度

它的优点是存储量大,少量的IO次数就可以存储大量的数据,以InnoDB的整数字段作为一个索引的数据结构的情况下,在树高为4层的情况下可以存储17亿的数据,一个数据块可以存储1200个索引项,仅4次磁盘IO就可以完成17亿数据的检索

但是它的缺点就是需要维护数据块中的索引的有序性,有可能导致页溢出的问题,这个问题是这样描述的:

如果插入数据不是顺序插入而是在中间插入的,那么就有可能导致一个情况,就是一条记录从一个物理存储块中插入,然后导致后续所有的记录都需要移动,可能导致满的一页中的数据溢出到下一页,然后下一页的数据再溢出到下一页,最终到表的结尾

为什么不用Hash结构?

Hash类型的索引,其实就是{key => value}类型的索引,这种索引在定值查询的时候,效率非常高,因为它的原理是将输入的key基于hash()映射到具体的下标下,但是它的瓶颈就是在于它会出现哈希冲突,也就是说多个key映射到的是同一个下标,这时候通常会以拉链法进行解决,也就是将冲突的元素放到同一个下标下,组织成一个链表/红黑树

还有一种方法是再哈希法,就是依次组织{h1(),h2(),h3()},当发生哈希冲突的时候,就依次使用新的函数,直到哈希冲突不再发生,那么在取值的时候,就会先将key丢进去h(),在得到了结果之后,就将这个结果进行比较,如果对不上就继续哈希,缺点就是Hash()结构容易引起全表查询,因为hash()组成的哈希表是无序的,除非使用了链表来组织这些元素,这是因为元素在被插入到数组的时候,他们之间的顺序并不是按照插入顺序来决定的

为什么用数组结构?

数组结构在等值查询和范围查询都能够达到最好的效果,因为可以直接通过表头+偏移量直接定位到数据,但是因为数组的内存总是连续的,因此一旦遇到索引的修改,就会导致大量的元素搬动

为什么索引要用B+树而不用红黑树?

红黑树相比于AVL树,在高度限制这一块做了调整但它依然无法解决树的高度过高的问题,因为你一个页面只能存储两个指针,假设你有100w条数据,你就需要20次磁盘IO,假设一次IO需要100ms,那么对于百万级的表,就需要2s的查询时间,于是在这样的情况下,即使使用红黑树,而不能解决IO的瓶颈

B树适用于什么场景

B树是一种平衡多路搜索树,B树在进行单个索引查询的时候,最快在O(1)的代价内就可以查询,从平均时间代价来看,会比B+树快一些,它适合在那种查询只有单个结果集的查询出现

17. 索引的分类

总体来说,我们可以从四个角度来分析索引的设计

从索引的数据结构来说,索引可以分为哈希索引、数组索引、B+树索引

从索引的物理存储性质来说,索引可以分为聚簇索引,非聚簇索引

从索引的字段特性来说,索引可以分为唯一性索引,主键索引,普通索引,前缀索引

从索引所用的字段个数来说,索引可以分为联合索引和单列索引

按照数据结构的分类

在MySQL中：

InnoDB的数据结构的索引是基于B+树使用的,它是不支持哈希索引的,但是在内存结构中有一个自适应的哈希索引,同时它支持全文索引

MyISAM的数据结构是基于B+树实现的,它不支持哈希索引,支持全文索引

Memory的数据结构是基于B+数和Hash索引实现的,它不支持全文索引

InnoDB中的数据存储格式都是基于聚簇索引存储的,这就要求驱动表一定要有一个主键索引,但是在使用的时候,可以不设定主键,这时候怎么办?

• 如果表中存在有一个唯一的索引(不包含NULL值),如果有多个候选者的,那么在这些候选者中选择第一个即可
• 如果没有这种字段,InnoDB就会启用行格式中的隐藏字段row_id,用这个字段来进行索引数的组织

什么是辅助索引?

简单来说,除了主键索引就都是辅助索引,也被称为是二级索引或者是非聚簇索引,创建的主键索引和二级索引默认使用的都是B+Tree索引,辅助索引在一定情况下不可以不回表查询。

简述索引的查询流程?

补充:页目录(Page Directory),因为在一页中,各个记录之间的存放是有序的,因此可以使用二分是搜索的方式进行查找,页目录实现了这个操作,将所有正常的记录(包括有最大记录和最小记录,不包括标记为已删除的记录)，划分为几个组,每个组的最后一条记录(也就是组内最大的那条记录)的头信息中的n_owned,属性表示该记录拥有多少条记录,也就是该组内公有几条记录。

之后每次插入一条记录,都会从页目录中找到主键值比本记录的主键值大并且差值最小的槽(相当于说是找到第一个大于本记录的主键值的槽),然后把该槽对应的记录的n_owned的值+1,表示这个组中又添加了一条记录,直到这个组中的记录数等于8个

• 通过二分确定记录所在的槽,并且找到这个槽中主键值最小的那条记录
• 通过记录的next-record属性遍历该槽所在组中的各个记录

索引查询流程具体如下,使用主键索引的查询流程,首先从根节点出发,根据主键值确定要遍历哪一个子节点,比如说根节点有3个子节点,范围是{0~30,31~60,61~90},我们的id = 40,于是就到了{31~60}这个范围内,然后到了这个节点上,发现直接存储了数据,于是就开始使用槽定位的方式定位这个记录位于哪一组

{37~42}中假设分5个记录为一组,那么查询的时候,它会查询到第二组,也就是{37~42}这一组中,然后反手去找到前一组中的最后一条记录的位置,这样的话就能够得到{37}这条记录的位置了,然后顺着链表向下查找就可以了

由于数据库的索引和数据都是存储在硬盘中的,因此可以把读取一个节点当做是一个IO操作

通过二级索引查询商品的流程

首先通过二级索引查询到具体的记录所在的位置,然后要通过回表这个操作,回到聚簇索引所在的位置,然后再查询一次聚簇索引所组织的B+树来查询具体主键值所在的位置,获取整行数据,最终就完成了

具体是否需要回表需要看是否发生了覆盖索引,具体地说,查询的字段在二级索引组织的B+树中都有,这时候就不需要回表查询了。

主键查询:主键索引就是建立在主键字段上的索引,通常在创建表的时候一起创建,一张表最多只能有一个主键索引,索引列的值是不允许有空值的

PRIMARY KEY (index_col) USING BTREE;

唯一索引:唯一索引建立在UNIQUE字段上的索引,一张表可以有多个唯一索引,索引列的值是必须唯一的,但是允许有空值

CREATE UNIQUE INDEX index_name 
on table_name(index_col,...)

普通索引:普通索引就是建立在普通字段上的索引,既不要求字段为主键,也不要求字段为UNIQUE,在创建表的时候,创建普通索引的方式是这样的

CREATE index indexName
on tbale_name(index_col,index_col);

前缀索引

前缀索引可以建立在字段为char、varchar、binary、varbinary的列上,它的作用是对字符类型的前几个字符建立索引,而不是在整个字段上建立索引

create TABLE table_name(
    column_list;
    INDEX(column_name(length))
);

18. 关于联合索引

建立在单列上的索引称为是单列索引,比如说有主键索引

建立在多列上的索引称为是联合索引

联合索引:通过将多个字段组合成一个索引,这个索引就被称为是联合索引,联合索引的非叶子节点的两个字段的值作为B+Tree的key值,在使用联合索引查询数据的时候,它会先按照product_no字段进行比较,在product_no相同的情况再按照name的字段进行比较,也就是说,联合索引的查询的B+Tree是先按照pn的值进行排序,然后再通过name字段进行排序

因此在使用联合索引的时候,存在一个最左匹配的原则,也就是按照最左优先的方式进行索引的匹配,在使用联合索引进行查询的时候,如果不遵循最左匹配原则,联合索引就会失效,这样就无法利用到索引快速查询的特性了

比如说创建了一个(a,b,c)联合索引,如果查询条件是:

where a = 1
where a = 1 and b = 2 and c = 3
where a = 1 and b = 2

注意,由于存在查询优化器,因为a字段在where子句的顺序不重要

这样的话就可以匹配上联合索引,否则的话联合索引就会失效

where b = 2;
where c = 3;
where b = 2 and c =3;

如上面的SQL,他们的索引将会失效,为什么呢?

这是因为索引的组织是按照(a,b,c)进行组织的,因此是先a有序,b有序,c有序

首先分析b = 2这个语句,这个语句将会触发全表扫描,因为b = 2 这个条件在表中并不是有序的,而是杂乱的分布在表中的

c = 2同理

总结:只有在前一个字段是相同的情况下,后面的字段才能是有序的,从而起到过滤/筛选一批数据的功能

联合索引的范围查询,如何判断是否会走索引?

联合索引有一些特殊情况,并不是查询过程使用了联合索引查询,就代表着联合索引中的所有字段都用上了

这种特殊情况就发生在范围查询的情况下以及一些like %通配符的相关情形,范围查询的例子有:

SELECT * FROM tb WHERE a > 1 and b = 2

有没有用到联合索引,就要看这个字段到底有没有起到过滤数据的作用?

首先可以知道,首先第一个条件就确定了查询范围对于a来说是(1,+无穷),也就是说,它会去扫描后面所有的元素,那么b=2会起到一个过滤作用吗?不会

select * from tb where a >= 1 and b = 2

首先也是要看这个字段有没有起到过滤作用

首先可以知道,第一个条件就确定了查询范围对于a来说是[1,+无穷),那么在查询的时候,就可以分成两端

a=1的时候,此时在a=1的这个字段范围中,呈现出来的就是b是有序的,因此这样就可以通过b=2条件减少需要扫描的二级索引记录范围,b字段可以利用联合索引进行索引查询,也就是说,从符合a=1 and b = 2的条件第一条记录开始向后扫描,而不需要扫描第一个a字段值为1的记录开始扫描

如何知道是否使用了索引?

可以通过执行计划中的key_len、key知道这一点

select * from tb where a between 2 and 8 and b  = 2

这个查询条件中的查询a字段的值是在2 <= a <= 8的,由于它符合符合字段顺序编排,而且有等值查询的情况,因此这样的话,就意味着不需要从a=2的第一条记录开始查询,而是直接从a = 2 && b = 2 开始查询

select * from tb where name like 'j%' and age = 22;

首先这个索引的结构是j%,因此它首先先会定位符合前缀为j的name字段的第一条记录， 然后沿着记录所在的链表向后扫描，直到某条记录的name不为j为止

于是在确定需要扫描的二级索引的范围的时候,当二级索引记录的name的字段值为j的时候,可以通过age = 22的条件减少需要扫描的二级索引记录范围,age字段可以利用联合索引进行索引查询,也就是说从符合name = j and age = 22 条件的第一条记录时开始扫描,而不需要从第一个name为j的记录开始扫描

联合索引的最左匹配原则,在遇到范围查询如(>、<)的时候,就会停止匹配也就是范围查询的字段可以用到联合索引，但是在范围查询字段的后面的字段无法用到联合索引,但是对于>=,<=,between、 like前缀匹配的范围查询 不会停止匹配

看联合索引是否会走索引,关键就是看索引的字段能否过滤掉一批数据

19. 什么是索引下推?

所谓索引下推优化就是说在使用二级索引进行查询的时候，不需要取出主键一个个地回表，而是通过在二级索引本身的字段，来减少一些不符合查询条件的记录，从而减少回表的次数

select * from tb where name >= "zhangsan" and age = 3;

如果组织的索引是name和age那么在二级索引树中,它首先会找到name = zhangsan的第一条记录，然后向后扫描回表,如果有索引下推的话,那么就能够在二级索引中直接排除那些age != 3的数据了

如果没有索引下推的话,那么他在查询的时候是这样的,它会拿到name == 'zhangsan'的第一条数据然后向后查询,然后发现每一条数据都去回表,这样的效率肯定是很慢的

20. 如何设计联合索引?

建立联合索引的时候的字段顺序,对索引效率有很大影响,越靠前的字段被用于索引过滤的概率越高,在实际操作中,要将区分度大的字段排在前面,这样区分度大的字段就越有可能被更多的SQL使用到

所谓区分度,其实也是一个指标,就是某个字段的值的取值范围除于表的总行数就是区分度

比如,性别的区分度就很小,不适合建立索引或者不适合排在联合索引列靠前的位置,而UUID这类字段就比较做索引或者排在联合索引的靠前的位置

如果索引的区分度很小,假设字段的值分布很均匀,那么无论搜索哪个值都可能得到一半的数据,在这种情况下,还不如不用索引,因为MySQL有一个查询优化器,查询优化器发现某个值出现在表中的数据行的百分比很高的时候,它一般会忽略索引,进行全表索引

select * from tb 
where status = 1 
order by create_time asc;

这个字段要怎么建立联合索引?

最佳的选择是给(status,create_time)建立一个联合索引,因为这样可以避免MySQL数据库发生文件排序,因为在查询的时候,如果只用到status的索引,但是这条语句还要对create_time排序,这时候就会用到文件排序了,所谓文件排序需要对扫描出来的记录进行重排,但是如果我们建立了status,create_time这样的索引,那么在检索出来status = 1的情况,create_time就是天然有序的,因此就不需要文件排序了

21.什么时候不需要创建索引?

索引的优点是可以极大地提高查询效率,减少IO,但是索引也是有缺点的:

• 第一点,首先是索引的创建需要占用一定的空间,也就是在原本的数据之上,还需要建立多一层数据,这种创建本身就是一种性能的消耗
• 第二点,索引的创建和维护要消耗时间,随着数据量的增大而增大
• 第三点,最主要是索引的动态维护,这是因为索引数据结构要维护自身的有序性,因此一旦插入不符合要求的数据,那么这时候就可能会导致页分裂之类的严重性能问题

什么时候要建立索引?

• 字段有唯一性的限制的,这时候使用索引比较好,因为索引值是唯一的话,那么就能够提高命中率
• 经常用于where查询条件的字段,这样能够提高整个表的查询速度,如果查询速度不是一个字段的话,那么就可以建立联合所用
• group by或者是order by后接的字段,这样在查询的时候就不需要再去做一次排序了,避免文件排序

什么时候不用创建索引?

• 对于那些Where、group by、order by里面用不到的字段,索引的价值是快速定位,如果起不到快速定位的字段通常是不需要建立索引的,因为索引会占用物理空间,但是因为乱序插入的页分裂问题可能导致查询效率更慢
• 对于在表中的那些值区分度小的,这时候不如不要建立索引,在这些情况下,因为MySQL有一个查询优化器,查询优化器发现某个值出现在表的数据行的比例很高的时候,它一般会忽略索引,进行全表扫描
• 经常更新的字段不用创建索引,因为更新意味着索引在B+Tree中的顺序发生了变化,这时候也有可能导致索引的重建,页分裂的问题

22. 索引优化技巧

前缀索引优化:前缀索引就是使用某个字段中的字符串的前几个字符建立索引,使用前缀索引的好处是可以减少索引的大小,可以增加一个索引列中存储的索引个数,有效提高索引的查询速度,在一些大字符串的字段作为字段的时候,使用前缀索引可以减少索引项的大小,一般来说可以通过设计这样的字符串(前半段具有很强的区分性,后半段随机),这样的话既可以兼顾数据库字段值的安全,也可以充分利用索引

要注意:

• order by无法使用前缀索引
• 无法把前缀索引用作是覆盖索引

覆盖索引优化:使用二级索引可以提高查询的灵活性,但是比起主键索引来说,它的回表一直是一个痛点,于是为了减少回表这样的操作,就出现了覆盖索引优化这样的操作,所谓覆盖索引就是说,当你的查询字段在二级索引的树上都存在的话,那么就可以说发生了覆盖索引,这样的话就不需要回表,减少了大量的IO操作

主键索引最好是递增的

每当有一条新的数据插入的时候,分为两种情况:

• 当插入的数据是处于数据块中间的情况的时候,这时候就需要挪动后面的元素,如果要挪动的元素很多,那么就导致说需要加载很多个数据块,然后将数据重新写入,这样的操作是十分重量级的,然后最终可能导致数据库阻塞掉了
• 当插入的数据是处于数据块的最后面的情况的时候,这时候不需要挪动后面的元素,只需要将记录插入到最后即可,最坏的情况就是开辟一个新的物理页,然后放入记录即可,不会造成页分裂这样的情况

索引最好设置为NOT NULL的

为了更好的利用索引,索引列要设置为 NOT NULL约束的

• 第一个原因:索引列中存在NULL就会导致优化器在做索引选择的时候更加复杂,更加难以优化,这是因为NULL的列可能会导致索引、索引统计和值的统计都比较复杂,比如说在进行索引统计的时候,count()将会省略为NULL的行
• 第二个原因:NULL值是一个没有意义的值,但是它会占用内存空间,所以会带来存储空间的问题,因为InnoDB存储记录的时候,如果表中存在允许为NULL的字段,那么行格式至少会使用1个字节的空间存储NULL值列表

复习:Compact行格式

记录可以分为行头部和行数据体

行头部记录了每一个数据行的元信息

• 变长字段长度列表
• NULL值列表
• 记录头信息

行数据体就是记录的真实数据,它有三个隐藏字段

• row_id:当没有合适的主键的时候,这时候就会启用这个字段
• trx_id:修改这条记录的事务ID
• roll_pointer:回滚指针,用于指向上一条的历史记录,指向的是undo log中的记录

23. 索引什么时候会失效?

索引失效,就是索引没有起到过滤数据的作用,那么什么情况下会发生呢?

可以总结为两种情况,根据索引无法生成搜索区间,对索引的值做了篡改

首先第一种情况,模糊匹配产生的失效问题,首先正常来使用,a%这样的匹配它的搜索区间是[a,b),但是像:

• %a%,无法形成搜索区间
• %a,无法形成搜索区间

第二种情况,对索引列做了计算,函数,类型转换等操作,都会导致索引失效,这是因为索引树中存储的是原始值,你对它做了修改,怎么可能能够按照原来的值来做计算呢?

第三种情况,联合索引没有按照最左匹配原则进行匹配,也就是说没有利用到索引的有序性,此时也无法形成有效的搜索区间,因此导致了索引失效

什么是最左匹配原则

所谓的最左匹配原则,就是需要根据索引在索引树中的有序性排布来确定搜索区间

具体的原理就是,当存在多个索引的时候,如果这多个索引,每个索引在等值的范围内,能够通过其他索引值来判断这个值是否能被过滤，这个就叫做过滤原则

第四种情况,使用了索引,也能够生成正确的搜索区间,但是却因为or这样的字段,导致了额外的搜索区间,从而导致原来的索引扫出来的那个范围没有用上

24. 如何优化SQL?

SQL优化可以通过explain关键字来实现,一般来说就是先通过:

show VARIABLES like '%slow_query_log%';
show VARIABLES like '%log_output%';

通过这个指令来看出这哪些SQL的耗时严重,或者是通过开启慢查询日志的方式,然 后查询这个日志就能够知道哪个SQL是十分耗时的

然后再通过

explain SQL;

就能够知道SQL的具体执行情况了,然后我们再根据执行情况来指定优化策略:

• possible_keys这个说的是这个SQL可能用到哪些索引,然后查看是否存在索引失效现象,重新设计SQL
• key字段表示实际用到的索引,判断是否使用到了索引
• key_len表示索引的长度
• rows表示扫描的数据行数
• type表示数据的扫描类型

type字段是十分关键的,这是因为这个字段描述了所需数据时使用的扫描方式是什么?常见的扫描类型的执行效率从低到高的顺序为:

• All(全表扫描)
• index(全索引扫描)
• range(索引范围扫描)
• ref(非唯一索引扫描)
• eq_ref(唯一索引扫描)
• const(结果只有一条的主键或者唯一索引的扫描)

详细解释一下:

首先All是性能很差的情况,因为它意味着要扫描全表

index意味着它使用了索引进行全表扫描,这样做的好处是不需要对数据进行排序,甚至如果涉及到回表的话,可能会比全表扫描的开销更大

range表示说使用了索引范围扫描,一般来说使用了> 、<等关键字的时候,就是这种情况了,它的作用是索引起到了过滤一部分数据的作用,但是还是一个范围查询,意味着过滤掉了一批数据之后,还有一次遍历

ref表示了采用了非唯一索引,或者是唯一索引的非唯一性前缀,它虽然使用了索引,但是该索引列的值不是唯一的,这就意味着在查找到这一条数据后需要向后继续顺序查找。

eq_ref表示使用了唯一索引,通常使用在多表联查中,比如说用户表,关联条件是两张表的user_id是相等的,而且user_id是唯一索引,那么使用EXPLAIN进行执行计划查看的时候,type就会显示eq_ref

const类型表示使用了主键或者唯一索引与常量值进行比较

extra

• Using filesort:当查询语句中包含group by或者是order by操作的时候,当无法利用索引的天然有序的特性进行扫描的时候,这时候就可能会通过文件排序,效率很低
• Using temporary:使用了临时表保存中间会出现这种情况,比如说子查询,会通过在底层生成临时表的方式来实现,由于临时表是不包含索引的,因此如果数据量一大起来就会导致查询效率低了
• Using Index,所需要的数据在索引就可以全部获得,避免了回表操作

25. 数据库范式如何理解?

第一范式:第一范式要求一行中的每个单元都应该要有单一值,而且不能够出现重复,比如说像CS_101这样的设计是不符合第一范式的,第一范式可能带来插入异常,删除异常,更新异常

比如说数据库系统中想要删除一个系的时候,或更新一个系的名称的时候,这时候就可能导致这个与这个系关联的所有列都将迎来一次更新

第二范式:从学术上的角度上来说,第二范式消除了非主属性对码的部分函数依赖,非主属性必须完全依赖于候选键,不能够存在部分依赖,从现象上来看,就是表中的每一个列都是用来描述这个实体的,每一个表都只能够表现一个实体,如果有一列描述了其他的东西,就应该拿掉它,并且放入一张单独的表,比如说学生表

学生表应该要描述学生的个体信息,如果在学生表中还要存储这个学生的选课信息,那么我们就应该要新建一个选课表,然后将学生ID和选课信息加入到一张新的表中,这是因为选课信息描述了一门课,因此不符合第二范式

比如说表ORDERS(order_id,date_customer_name)因为这张表示存储订单了,但是customer_name描述了一名客户,而不是描述一个订单,因此不符合第二范式

如果一个用户订购了多个订单,首先第一个问题就是浪费空间,在这种情况下,每一个订单都会冗余存储订单的客户名称,如果后续还需要添加客户的其他信息,那么就会导致其他的列的更改,因此第二范式将会导致更新异常或者删除异常

第三范式:从学术的角度上来说,第三范式要求表中的所有属性只能由那组关系的候选键来决定,而不能是任何其他的非主属性

比如说有发票表INVOCIES(invoce_total,payment_total,balance)

说明,invoce_total是结余的总量,payment是支付的总量,balance是剩余的

那么在这个表中有一个关系,就是说invoice_total = patment+balance

那么这个将会导致什么问题呢?也就是说当更新payment的时候,意味着需要同时更新balance

从现象来看就是发生了更新异常

表中的列不应该从其他列中派生出来,就好像说payment可以由非主键的invoice_total和balance推断出来一样

26. 如何分析MySQL的死锁问题?

show engine innodb status;

首先先分下加锁情况,给出的表的情况是这样的:

CREATE TABLE `t_student` (
  `id` int NOT NULL,
  `no` varchar(255) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  `age` int DEFAULT NULL,
  `score` int DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

在这里面,唯一索引是id,其他的均为非唯一索引,首先分析第一条语句

update students set score = 100 where id = 25;

由于执行的是update语句,因此会上个锁来防止幻读,由于是唯一索引的等值查询,那么就来分析什么时候会发生幻读的情形:

首先是因为它是唯一索引,因此在这样的情况下,而且数据是不存在的,那么这时候为了防止幻读,因此会上一个间隙锁,也就是上一个(20,30)的间隙锁

update students set score = 100 where id = 26

首先是因为它是唯一索引,因此在这样的情况下,而且数据是不存在的,那么为了放置幻读,因此会上一个间隙锁,也就是上一个(20,30)的锁

然后执行

insert into students(id,no,name,age,score) values(25,'s0025','sony',28,90)

这时候它执行插入,此时因为是插入操作,插入区间是(20,30),这时候事务A就产生了阻塞,这时候就会产生一个插入意向锁

关于插入意向锁的时机,每插入一条新的记录,都需要看一下待插入的记录的下一跳记录上是加了间隙锁,如果已经加了间隙锁,那么这时候就产生一个插入意向锁,然后将锁的状态设置为等待

接着事务B交替执行,然后执行

insert into students(id,no,name,age,score) values(26,...);

然后这时候它也一样会因为事务A的(20,30)所产生的间隙锁而阻塞

27. MySQL中有哪些日志?

当执行一条update的语句将会涉及到三个日志,因为更新的过程涉及到并发、持久化等操作

undo log(回滚日志):回滚日志是Innodb实现的,实现了事务中的原子性,主要用于事务回滚和MVCC

redo log(重做日志):实现了事务的持久性

bin log(归档日志):是server层的日志,用来做数据备份和主从复制

28. 为什么需要undo log?

当在执行一条增删改的语句的时候,虽然没有使用begin开启事务和commit/rollback来结束事务,但是MySQL将会隐式的开启事务,执行一条语句是否自动提交事务,是由auto commit参数决定,当执行一条update语句的时候也是会使用事务的

它主要是为了实现数据库的崩溃后的恢复,也就是说,当事务执行到一半,发生错误或者数据库崩溃的时候,就可以通过undo log来实现之前所做的操作的撤销,也就是说undo log可以保证原子性

undo log的记录格式通常是这样的:

• 在插入一条记录的时候,要把这条记录的主键值记录下来,这样在之后回滚的只需要将这个主键值所对应的记录删除掉就可以了
• 在删除一条记录的时候,要把这条记录的内容都记录下来,这样的话回滚的时候就再把这些内容组成的记录插入到表中就可以了
• 在更新一条记录的时候,要把被更新的列旧值记录下来,这样的话回滚的时候再把这些值更新为旧值就可以了

不同的操作,需要记录的内容也是不同的,因此不同类型的操作产生的undo log的格式也都是不同的。

一条记录的每一次更新更新操作产生的undo log格式有一个roll_pointer和一个trx_id事务id

• 通过trx_id可以知道这个记录是哪个事务修改的
• 通过roll_pointer可以将这些undo log串成一个链表,这个链表就是版本链

undo log还有一个作用,就是可以基于ReadView+undo log实现MVCC的多版本的控制

对于读提交和可重复读隔离级别的事务来说,它们的快照读是通过Read View+undo log来实现的,它们的区别在创建ReadVuew的时机不同

读提交:是在每个select都会生成一个新的ReadView,也意味着事务期间的多次读取同一条数据,前后两次读的数据可能会出现不一致,因为可能这期间另外一个事务修改了该记录,并且提交了事务

可重复读:仅在事务启动的时候开启一个ReadView,然后在这之后的事务执行过程中,一直使用这个ReadView即可

总结:undo log的两大作用有

• 实现事务的回滚,保障事务的原子性,在事务的处理过程中,如果出现了错误或者需要回滚的时候,MySQL可以利用undo log中的历史数据将数据恢复到事务开始之前的状态
• 实现MVCC(多版本并发控制)关键因素之一,MVCC是通过ReadView+undo log实现的,undo log为每条记录保存多份历史记录,MySQL在执行快照读的时候,会根据事务执行过程中的Read View来找到哪些记录是可见的

undo log是如何刷盘的?

undo log在MySQL中存储是以Undo页面的方式存储的,既然是物理页面,那么它当然可以通过redo log来实现,这个Undo页面是存储在buffer pool中的,对undo页面的修改都会持久化到redo log中,redo log每秒都会刷盘。

29. 什么是buffer pool

MySQL中的数据都存储在磁盘中的,当我们需要更新一条数据的时候,得先从磁盘读取该记录,然后在内存中修改这条记录,那么修改完这条记录后是选择直接写回磁盘?还是缓存到一个读写速度较快的区域?

MySQL的性质决定了它是一个写操作比较多的RMDBS,因此在这样的情况下,如果每次写入都需要将数据刷回磁盘,意味着频繁的IO操作,这样的话性能必然是很差的

因此最终设计了一个Buffer Pool,这个Buffer Pool暂时存储了从磁盘上来的数据,它的设计思想我认为就像一个虚拟内存的技术,而且从Linux的角度上来看,Buffer Pool也是基于虚拟内存技术实现的

在MySQL启动的时候,可以看到buffer Pool的内存占用是0,只有在后续触发缺页中断的时候,才会将物理块缓存到Buffer Pool中

当读取数据的时候,如果数据在BufferPool中,那么客户端就会直接读取BufferPool中的数据,否则才会触发缺页中断

当修改数据的时候,如果数据存在于BufferPool中,那么就直接修改BufferPool中的数据,然后将其页设置为脏页,该页的内存数据和磁盘上的数据已经不一致了,为了减少磁盘IO,不会立即将脏页刷回磁盘,后续由后台线程选择一个合适的时机将脏页写回磁盘

30. BufferPool缓存了什么?

它实际上存储了从磁盘中加载的数据,在MySQL启动的时候,InnoDB会为BufferPool申请一篇连续的内存空间,然后按照默认的16kb的大小划分出一个个的页,BufferPool中的页就叫做缓存页,此时这些缓存页都是空闲的,之后随着程序的运行,才会有磁盘上的页被缓存到BufferPool中

BufferPool缓存的是数据库中存储数据的物理页,BufferPool除了缓存索引页和数据页

还包括有Undo页,插入缓存,自适应哈希索引,锁信息等

Undo页面记录的Undo log的具体信息

查询一条记录,就只需要缓冲一条记录吗?当我们查询一条记录的时候,InnoDB是会将整个页的数据加载到BufferPool中的,将页加载BufferPool后,再通过页里的页目录去定位到某条具体的面记录

31. 为什么需要redo log?

Buffer Pool确实提高了读写效率,但是Buffer Pool是基于内存的,如果一旦在Buffer Pool中的数据没有刷盘之前产生了数据库崩溃,那么这时候损失就会很惨重了,为了防止这样的问题发生,于是就当有记录需要更新的时候,InnoDB引擎就会先更新内存,然后将这个数据页标记为脏页,然后对这个页的修改以redo log的形式持久化下来,这时候更新就算完成了

这个技术就叫做WAL技术,就是在实际写操作之前先写日志,在合适的时机执行这些日志

什么是redo log

redo log是物理日志,记录某个数据页做了什么样的修改,比如说对XXX表空间中的YYY数据页ZZZ偏移量做了AAA更新,每当执行一个事务的就会产这样的一条日志,在事务提交的时候,只需要将redo log持久化到磁盘即可,可以不需要将缓存到BufferPool中的脏页数据持久化到磁盘

修改了Undo页面,需要记录redo log吗

需要,这是因为Undo页面本质上也算一个物理页,因此对物理页做修改,需要将被更新的列的旧值记下来,也就是要生成一条undo log,undo log会写入到BufferPool中的Undo页面

事务提交之前发生了崩溃,重启后会通过undo log回滚事务,事务提交后发生了崩溃,重启后会通过redo log恢复事务

redo log要写到磁盘,数据也要写到磁盘上,为什么要多此一举?

这是因为写入redo log的方式使用的是追加写,也就是说磁盘操作是顺序写,而随机写则是需要先查找到写的位置,才能将记录写入到磁盘,顺序写通常来说只需要一次磁盘寻道,而随机写几乎每一次都需要磁盘寻道,这是因为MySQL的写操作并不是马上更新到写磁盘上的,而是先记录到日志上,然后在合适的时间内更新到磁盘上

实现了事务的持久性,让MySQL有崩溃后恢复的能力,能够保证MySQL在任何时候突然崩溃,重启后那些数据都不会丢失

将写操作从随机写变成了顺序写,提升了MySQL写入磁盘的能力

redo log也是写一条记录然后刷一条记录回磁盘的吗?

不是的,实际上在执行一个事务的过程中,产生的redo log也不是立即写入磁盘的,因为这样会产生大量的IO操作,所以redo log也有自己的缓存,redo log buffer,每当产生一条redo log的时候,就会先写入到redo log buffer中,后续持久到磁盘中

刷盘时机?

• MySQL正常关闭的时候,这时候会将redo log buffer中内容全部写入到磁盘中
• 当redo log buffer中记录的写入量大于redo log buffer内存空间的一半的时候
• InnoDB的后台线程每隔1s,将redo log buffer持久化到磁盘
• 每次事务提交的时候,都会将缓存在redo log buffer中的redo log直接持久化到磁盘

当innodb_flush_log_at_trx_commit有不同的参数的时候,会触发不同的刷盘策略

• 当参数为0的时候,这时候事务提交不会主动触发写入磁盘的事件,后台线程每隔1s,就会通过write(),将数据写入到操作系统内核中,然后调用fsync(),MySQL进程的崩溃会导致上一秒钟的所有事务的数据的丢失
• 当参数为1的时候,表示每次事务提交的时候,都将缓存到redo log buffer中的redo log直接持久化到磁盘
• 当参数为2的时候,将redo log buffer中的数据持久化到pageCache中,也就是写入到了操作系统内核,每隔1s调用fsync()

redo log文件写满了怎么办?

默认情况下,InnoDB存储引擎下有一个重做日志文件组,这两个redo日志文件ib_logfile0和ib_logfile1,重做日志是以循环写的方式工作的,从头开始写,写到末尾就又回到开头,相当于一个环形

redo log是为了防止buffer Pool中的脏页丢失而设计的,如果buffer pool中的脏页被刷回到了磁盘中的话,那么此时redo log中的记录也就没用了,这时候擦除这些记录,以便腾出空间来使用

redo log是循环写的方式,相当于一个环形,InnoDB用write pos表示redo log当前写的位置,而check point到write pos表示还没有持久到磁盘中的脏页

而之前的就都可以刷新掉了

如果write pos == checkpoint的话,那么就意味着redo log文件满了,这时候MySQL就不能够再执行新的更新操作了,也就是说MySQL会被阻塞。

32. 为什么需要bin log?

bin log是Server层的日志,这意味着不仅InnoDb有,其他存储引擎也有。

它的特点是这样的:在事务提交的时候,会将这个事务执行过程中产生的所有binlog统一写入到binlog文件中

为什么有了binlog还要有redo log?

这是因为最开始并没有InnoDB引擎,只有MyISAM,而binlog设计之初并没有crash-safe的能力,binlog日志只能够用于归档,如果添加上check point等机制,实际上bin log可以实现其他日志的功能

33. redo log和bin log有什么区别?

适用对象不同:

bin log是MySQL的Server层实现的日志,所有存储引擎可以使用

redo log是InnoDB存储引擎实现的日志

文件格式不同:

• binlog有三种格式类型,分别是STATEMENT、ROW、MIXED

STATEMENT:逻辑归档日志,有动态函数的问题

ROW:记录物理数据日志,存储的是具体的数值

MIXED:包含了STATEMENT和ROW,根据具体情况使用

redo log物理日志,记录的是某个数据页的操作

写入的操作不同

binlog是追加写,写满了一个文件之后,就创建一个新的文件继续写,不会覆盖之前的日志

redo log是循环写,日志空间大小是固定,全部写满就从头开始,保存未被刷入磁盘的脏页日志

34. 主从复制的实现流程

MySQL的主从复制依赖于binlog,也就是说记录MySQL上的所有变化并以二进制的形式保存在磁盘上,复制的过程就是将binlog中的数据从主库传送到从库上

• 写入binlog:主库写入binlog日志,提交事务,更新本地缓存
• 同步binlog:通过dump线程,将binlog发送到从库上,然后从库上的IO线程读取dump线程发来的日志信息
• 回放binlog:从库中的SQL线程读取replay log,然后执行

bin log什么时候刷盘

在事务提交的时候,执行器把binlogcache中的完整事务写入到binlog中

当0 的时候,表示每次提交事务都只write,不fsync,后续操作由操作系统决定何时将数据持久化到磁盘中

当1的时候,表示每次提交事务都会write,然后马上执行fsync

当N的时候,表示每次提交事务都write,但是累计N个事务才fsync

35. 执行一条update语句会发生什么?

首先是通过链接器,验证你的权限和管理你的连接

然后是通过分析器,经过词法分析和语法分析,判断你的SQL是否正确以及知道你的SQL想要干什么

然后是通过是优化器,通过优化器就知道在知道怎么操作

然后就生是通过执行器:

执行器负责具体执行,它会执行优化器中解析出来的命令,然后交给底层的存储引擎,通过主键索引获取id= 1的这一条记录

如果数据本身就在bufferpool的话,那么就直接返回

如果数据不在bufferpool的话,那么就需要从磁盘中调入

执行器在得到聚簇索引记录后,会看一下更新前的数据和更新后的数据是否是一致的,如果是一致的话,那么就直接返回,否则进入更新流程

开启事务,InnoDB层更新记录前,首先要记录相应的undo log,因为这是更新操作,需要记录一个undo log,然后这个undo log会写入到buffer Pool中的undo页面中,执行完后写入对应的redo log

InnoDB层开始记录,会先更新内存,然后将记录写入到redo log中,这时候更新就算完成了,为了减少磁盘IO,不会立即将脏页写入到磁盘中,而是由后台选择一个合适的时机将脏页写入到磁盘中,这就是WAL技术