Mysql 45讲总结面试题

科技 09-11 来源：程序员Seven

1.一条sql查询语句是如何执行的

1.1Server层

1.1.1连接器:管理连接,权限验证

负责跟客户端建立连接，获取权限，维持，管理连接
如果用户名和密码认证通过,连接器会到权限表里面查出你拥有的权限.之后,这个连接里面的权限判断逻辑,都将依赖于此时读到的权限
客户端长时间sleep,连接器会自动将它断开,时间默认8小时,由参数wait_timeout控制
长连接是指连接成功后,如果客户端持续有请求,则一直使用同一个连接.
短连接是指每次执行完很少的几次查询就断开连接，下次查询再重新建立一下
全部使用长连接，mysql占用内存涨的特别快，因为mysql在执行过程中临时使用的内存是管理在连接对象里面的，这些资源在连接断开时才释放
解决办法1:定期断开长连接，使用一段时间之后，或者程序有占用内存的大查询之后，断开连接，之后重连
解决办法2:mysql5.7版本之后,在执行一个比较大的操作后，通过执行mysql_reset_connection 来重新初始化连接资源，这个过程不需要重连

1.1.2查询缓存:命中则直接返回结果

执行过的语句与结果以key-value对的形式，被直接缓存在内存中
大多数情况下不要使用查询缓存,查询缓存的失效比较频繁，只要对一个表的更新，这个表上所有的查询缓存都会被清空
将参数query_cache_type设置成DEMAND,默认的sql语句都不使用查询缓存.对于确定要使用查询缓存的语句，可以用SQL_CACHE显示置顶
mysql8.0版本直接将查询缓存的整块功能删掉了。

1.1.3分析器:词法分析,语法分析

识别是查询语句还是修改语句，将字符串识别成表名，列id，判断表名列名是否有误,判断语法是否有误

1.1.4优化器:执行计划生成,索引选择

在表里面有多个索引，决定使用哪个索引
多表关联，决定各个表的连接顺序

1.1.5执行器:操作引擎,返回结果

判断对表是否有执行权限，如果没有，则返回报错
根据表的引擎定义，使用对应引擎提供的接口

1.2存储引擎:存储数据,提供读写接口

支持InnoDB,MyISAM,Memory等多个存储引擎,最常用InnoDB,mysql5.5.5版本开始成为默认存储引擎
不同的存储引擎共用一个Server层

2.一条sql更新语句是如何执行的

整个流程同查询语句
连接器:将该表有关的查询缓存失效
分析器:通过词法和语法解析知道是更新语句
优化器:决定使用那个索引
执行器:负责具体执行,找到该行数据，然后更新
执行器先找引擎取数据，引擎直接用树搜索找到这一行数据，如果数据页在内存中，直接返回给执行器，否则，需要先从磁盘读入内存，然后再返回
执行器拿到引擎给的数据，把值更新，再调用引擎接口写入这行新数据
引擎将这行数据更新到内存中，同时将这个更新操作记录到redo log里面，此时redo log处于prepare状态，然后告知执行器执行完成了，随时可以提交事务
执行器生成这个操作的binlog，并把binlog写入磁盘
执行器调用引擎的提交事务接口，引擎把刚刚写入的redo log改成提交commit状态，更新完成
执行流程图

3.redo log

InnoDB特有日志，称为重做日志
WAL技术:Write-Ahead Logging:先写日志,再写磁盘,当有一条记录需要更新的时候，InnoDB引擎就会先把记录写到redo log里面,并更新内存.等系统比较空闲，将该记录更新到磁盘里
InnoDB的redo log是固定大小的
crash-safe:write pos 和 checkPoint
redolog用于保证crash-safe能力.

4.binlog

Server层日志,称为归档日志

5.redo log 和binlog不同

redo log是InnoDb引擎特有的,binlog是Mysql的server层实现的,所有引擎都可以使用
redo log是物理日志,记录的是在某个数据页上做了什么修改;binlog是逻辑日志,记录的是这个语句的原始逻辑，比如"给Id=2这一行的c字段加1"
redo log是循环写的,空间固定会用完,binlog是可以追加写入的,"追加写"是指binlog文件写到一定大小后会切换到下一个，并不会覆盖以前的日志

6.为什么必须有两阶段提交

为了让两份日志之间的逻辑一致
如果不使用两阶段提交，那么数据库的状态就有可能和用它的日志恢复出来的库的状态不一致,redolog和binlog都可以用于表示事务的提交状态,而两阶段提交就是让这两个状态保持逻辑上的一致

6.1假如先写redo log后写binlog

binlog还没有写完的时候,MySQL进程异常重启。由于redolog写完之后,系统即使崩溃,仍然能够把数据恢复回来,所以恢复之后值是redolog值。但是由于binlog没写完就crash了，binlog跟redolog值不一致

6.2.先写binlog后写redolog

如果在binlog写完之后crash,由于redolog还没写,崩溃恢复以后这个事务无效,所以这一行值还是redolog中的值，但是通过binlog恢复数据的时候，会多了一个事务的值，与原库值不一致

7.参数配置

innodb_flush_log_at_trx_commit 设置为1.表示每次事务的redolog都直接持久化到磁盘,保证MySQL异常重启之后数据不丢失
sync_binlog 设置为1,表示每次事务的binlog都持久化到磁盘，保证Mysql异常重启之后binlog不丢失
query_cache_type参数设置成DEMAND,默认的sql语句都不使用查询缓存.对于确定要使用查询缓存的语句，可以用SQL_CACHE显示置顶
transaction-isolation 设置为READ-COMMITTED 代表将mysql的隔离级别设置为读提交.可以使用show variables like 'transaction_isolation'; 来查看当前值
MAX_EXECUTION_TIME，来控制每个语句执行的最长时间s
innodb_undo_tablespaces 设置成 2（或更大的值）。如果真的出现大事务导致回滚段过大，这样设置后清理起来更方便
innodb_lock_wait_timeout 超时时间设置,死锁设置等待超时时间,默认值是50s
innodb_deadlock_detect 设置为 on，表示开启这个逻辑。发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行，时间复杂度O(N)

8.隔离性与隔离级别

ACID(Atomicity Consistency Isolation Durability) 原子性一致性隔离性持久性
当数据库上有多个事务同时执行的时候,就可能出现脏读不可重复读幻读的问题,为了解决这些问题,就有了隔离级别的概念

8.1事务隔离级别

读未提交(read uncommitted) 一个事务还没提交时,它做的变更就能被别的事务看到
读提交(read committed) 一个事务提交之后,它做的变更才会被其他事务看到
可重复读(repeatable read) 一个事务执行过程中看到的数据,总是跟这个事务在启动时看到的数据是一致的,当然在可重复读隔离级别下,未提交变更对其他事务也是不可见的
串行化(serializable) 对于同一行记录,写会加写锁,读会加读锁,当出现读写锁冲突的时候,后访问的事务必须等前一个事务执行完成,才能继续执行

9.隔离级别的实现

在实现上,数据库里面会创建一个视图,访问的时候以视图的逻辑结果为准
可重复读隔离级别下,这个视图是在事务启动的时候创建的,整个事务存在期间都用这个视图
读提交隔离级别下,这个视图是在每个sql语句开始执行的时候创建的
读未提交隔离级别下直接返回记录上的最新值,没有视图概念
串行化隔离级别下直接用加锁的方式来避免并行访问

9.1可重复读的事务隔离实现

在mysql中,实际上每条记录在更新的时候都会同时记录一条回滚操作,记录上的最新值,通过回滚操作,都可以得到前一个状态的值
查询这条记录的时候,不同时刻启动的事务会有不同的read-view.同一条记录在系统中可以存在多个版本,就是数据库的多版本并发控制(MVCC)

10.mysql默认的隔离级别,oracle的默认隔离级别

mysql默认的隔离级别是可重复读
oracle数据库的默认隔离级别是读提交
对于从oracle迁移到mysql的应用,为保证数据库隔离级别的一致,你一定要记得将mysql的隔离级别设置为读提交

11.可重复读的使用场景

数据校对,一个表存了账户余额,一个表存了账单明细,月底做数据校对,判断上个月的余额和当前余额的差额是否与本月的账单明细一致
存在即是合理,每种隔离级别都有自己的使用场景

12.事务隔离级别行记录的回滚日志什么时候删除

在不需要的时候才删除,系统会判断,当没有事务再需要用到这些回滚日志时,回滚日志会被删除
什么时候不需要?当系统里没有比这个回滚日志更早的read-view的时候

13.为什么mysql不建议使用长事务

长事务意味着系统里面会存在很老的事务视图。由于这些事务随时可能访问数据库里面的任何数据,所以这个事务提交之前,数据库里面它可能用到的回滚就都必须保留,这就会导致大量占用存储空间
在Mysql5.5及以前的版本,回滚日志是跟数据字典一起放在ibdata文件里的,即使长事务最终提交,回滚段被清理,文件也不会变小
长事务还占用锁资源,也可能拖垮整个库

14.事务的启动方式

显示启动事务语句,begin 或 start transaction 配套的提交语句是commit 回滚语句是rollback
set autocommit =0 这个命令会将这个线程的自动提交关掉,意味着如果你只执行一个select语句,这个事务就启动了,而且并不会自动提交.这个事务持续存在直到commit或rollback或断开连接
建议总是使用set autocommit=1,通过显式语句方式开启事务
对于频繁使用事务的业务,每个事务开始时都需要主动执行一次begin,为了减少语句的交互次数,可以使用commit work and chain(提交事务并自动启动下一个事务)

15.怎么查找持续时间比较长的事务

select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(),trx_started))>60
information_schema库的innodb_trx表中可以查询长事务

16.如何避免长事务对业务的影响

开发端设置autocommit为1,在测试阶段把mysql的general_log开启，根据日志确认
有些框架在sql语句前以begin开启，但是有些就是单纯的select。把这些只读事务去掉
通过 SET MAX_EXECUTION_TIME 命令，来控制每个语句执行的最长时间，避免单个语句意外执行太长时间
数据库端监控information_scheam.innodb_trx表,设置长事务监控，超过时间报警或者kill
percona的pt-kill工具不错,推荐使用

17.索引的作用

索引的出现是为了提高查询效率

18.索引的常见模型

18.1哈希表

以键值存储数据的结构
适用于只有等值查询的场景,范围查询的话，只能全表扫描

18.2有序数组

值按照递增顺序保存
适用于静态存储引擎
有序数组在等值查询和范围查询场景中的性能都非常优秀,更新场景需要将插入位置之后的数据全部向后挪动,成本太高

18.3二叉搜索树

二叉搜索树查询更新的时间复杂度都是O(log(N))
二叉树树高比较高的话,一次查询需要访问的数据块太多,从磁盘一次寻址时间是10ms,单独访问一个行的时间需要10ms*数据块
为了让一个查询尽量少读磁盘，就必须让查询过程访问尽量少的数据块,使用N叉树
N叉树优于在读写性能上的优点，以及适配磁盘的访问模式,以及被广泛使用在数据库存储引擎中

19.索引类型

索引分为主键索引和非主键索引
主键索引的叶子节点存的是整行数据，innodb里，主键索引也成为聚簇索引
非主键索引的叶子节点内容是主键的值，在innodb里非主键索引也被成为二级索引

20.基于主键索引和普通索引的查询有什么区别

主键查询只需要扫描主键所在的B+树
普通索引查询，需要先扫描普通索引树,得到主键值,再到主键索引树搜素一次，这个过程成为回表
在应用中尽量使用主键查询

21.索引维护-页分裂过程

B+树为了维护索引有序性，在插入新值的时候需要做必要的维护
如果新值在旧有索引顺序的中间，需要挪动后面的数据，如果数据页已经满了，根据B+树算法，需要申请一个新的数据页,然后挪动部分数据过去，这个过程成为页分裂
页分劣影响性能，整体空间利用率降低
自增主键，每次插入一条记录都是追加操作，不涉及挪动其他数据，也不会触发叶子节点的分裂
主键索引的长度越小，普通索引的叶子节点就越小，普通索引占用的空间也就越小

22.什么场景适合业务字段直接做主键？

只有一个索引
该索引必须是唯一索引
由于没有其他索引，所以也就不用考虑其他索引的叶子节点大小的问题

23.innodb为什么使用B+树作为索引结构

B+树能够很好地配合磁盘的读写特性,减少单次查询的磁盘访问次数

24.覆盖索引

如果查询条件使用的是普通索引(或是联合索引的最左原则字段),查询结果是联合索引的字段或是主键，不用回表操作，直接返回结果，减少IO磁盘读写数据
覆盖索引必须要覆盖所有的查询条件中的列，同时必须将where后面的查询条件的列都覆盖
覆盖索引的目的是不回表
由于覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段

25.索引下推

在mysql5.6之前，只能从普通索引树找到id主键,开始一个一个回表，到主键索引上找出数据行，再对比字段值
在mysql5.6引入的索引下推优化，可以在索引遍历过程中,对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数

26.为什么要重建索引

索引可能因为删除,或者页分裂等原因,导致数据页有空洞,重建索引的过程会创建一个新的索引,把数据按顺序插入,这样页面的利用率最高,索引更紧凑,节省空间

27.根据加锁范围,mysql锁分类

全局锁表级锁行锁

28.全局锁

使用场景:做全库逻辑备份
使用方法:加全局读锁的方法,命令是 Flush tables with read lock(FTWRL)
官方自带的逻辑备份工具是Mysqldump,当mysqldump使用参数-single-transaction的时候,导数据之前就会启动一个事务,来确保拿到一致性视图.而由于MVCC的支持,这个过程是数据是可以正常更新的

28.1弊端:

在主库上备份，那么在备份期间都不能执行更新,业务基本上就得停摆
如果你在从库上备份,那么备份期间从库不能执行主库同步过来的binlog,会导致主从延迟

28.2为什么要加全局锁:

不加锁的话,备份系统备份得到的库不是一个逻辑时间点的,这个视图是逻辑不一致的

28.3为什么有mysqldump还要FTWRL

一致性读是好,但前提是引擎要支持这个隔离级别
比如MyISAM不支持事务的引擎,就需要使用FTWRL
-single-transaction方法只适用于所有的表使用事务引擎的库

28.4设置全局读锁,为什么不使用set global readonly=true

在有些系统中,readonly的值会被用来做其他逻辑,比如用来判断一个库是主库还是备库
在异常处理机制上有差异,如果执行FTWRL命令之后由于客户端发生异常断开,那么mysql会自动释放这个全局锁,整个库回到可以正常更新的状态,而readonly会一直保持

29.表级锁

mysql表级别锁有两种:一种是表锁,一种是元数据锁(mdl)

29.1表锁

语法:lock tables...read/write 可以用unlcok tables主动释放,也可以在客户端断开的时候自动释放
使用场景:在没有出现更细粒度的锁的时候,表锁是最常用的处理并发的方式,而对于Innodb这种支持行锁的引擎,不一般不使用lock tables命令来控制并发

29.2元数据锁(MDL)

使用:mdl不需要显示使用,在访问一个表的时候会被自动加上,mdl的作用是,保证读写的正确性
作用:在mysql5.5版本中引入了mdl,当对一个表做增删改查操作的时候,加mdl读锁,当要对表做结构变更操作的时候,加mdl写锁

29.3如何安全地给小表加字段?

首先要解决长事务,事务不提交,就会一直占着mdl锁,可以在infomation_schema库innodb_trx表中,你可以查到当前执行中的事务,要做ddl变更的表刚好有长事务在执行,考虑先暂停DDL,或者kill掉
如果ddl的表是热点表,请求比较频繁,kill可能不太管用,需要使用
ALTER TABLE tbl_name NOWAIT add column ...
ALTER TABLE tbl_name WAIT N add column ...

30.行锁

mysql的行锁是在引擎层由各个引擎自己实现的,但并不是所有的引擎都支持行锁,比如MyISAM引擎就不支持行锁
不支持行锁意味着并发控制只能使用表锁,对于这种引擎的表,同一张表上任何时刻只能有一个更新在执行,这就会影响到业务并发度。
innodb支持行锁

31.两阶段协议

在innodb事务中,行锁是在需要的时候才加上的,但并不是不需要了就立刻释放,而是要等到事务结束时才释放.这就是两阶段协议
因为两阶段协议,如果你的事务中需要锁多个行,要把最可能造成锁冲突,最可能影响并发度的锁尽量往后放

32.死锁

当并发系统中不同线程出现循环资源依赖,涉及的线程都在等待别的线程释放资源时,就会导致这几个线程都进入无限等待的状态,成为死锁

32.1解决死锁

设置线程超时时间,过长影响正常业务,过短也不好
死锁检测,时间复杂度太高O(N),容易导致CPU飙升

33.怎么解决由这种热点行更新导致的性能问题呢?

确保这个业务一定不会出现死锁,可以临时把死锁检测关掉。操作本身有一定风险,出现死锁，就回滚，重试就好了，如果关掉死锁检测意味着可能会出现大量的超时.
控制并发度,对于相同行的更新,在进入引擎之前排队,这样在innodb内部就不会有大量的死锁检测工作
设计优化:将一行的逻辑改成逻辑上的多行来减少锁冲突,比如给账户增加值,分成多行,增加时随机选一条记录来增加

34.事务的启动时机

begin/start transaction 命令并不是一个事务的起点,在执行到它们之后的第一个操作innodb表的语句,事务才真正启动,如果你想要马上启动事务可以用start transaction with consistent snapshot
begin/start transaction 一致性视图是在执行第一个快照读语句时创建的
第二种方式,一致性视图是在执行start transaction with consistent snapshot时创建的

35.视图

mysql有两个视图的概念
view:查询语句定义的虚拟表,在调用的时候执行查询语句并生成结果,创建视图的语法是create view...而它的查询方法与表一样
innodb在实现MVCC时用到的一致性读视图,即consistent read view,用于支持RC(Read Committed,读提交)和RR(Repeatable Read,可重复读)隔离级别的实现

36.快照在MVCC里是怎么工作的

在可重复读隔离级别下,事务在启动的时候就拍了个快照,注意,这个快照是基于整库的
innodb里面每个事务有一个唯一的事务Id,叫做transaction_id.它是在事务开始的时候向innodb的事务系统申请的,是按申请顺序严格递增的
每行数据也都有多个版本,每次事务更新数据的时候，都会生成一个新的数据版本，并且把transaction_id赋值给这个数据版本的事务ID,记为row trx_id
同时旧的数据版本要保留，并且在新的数据版本中,能够有信息可以直接拿到它
也就是说,数据表中的一行记录，其实可能有多个版本(row),每个版本有自己的row trx_id
innodb为每个事务构造了一个数组,用来保存这个事务启动瞬间,当前正在活跃的所有事务id,活跃指的是,启动了但还没提交
数组里面事务id的最小值记为低水位,当前系统里面已经创建过的事务id的最大值加1记为高水位,这个试图数组和高水位,就组成了当前事务的一致性视图(read-view)
数组版本的可见性规则,就是基于数据的row trx_id和这个一致性视图的对比结果得到的

如果落在绿色部分,表示这个版本是已提交的事务或者是当前事务自己生成的,这个数据是可见的
如果落在红色部分,表示这个版本是由将来启动的事务生成的,肯定是不可见的
如果落在黄色部分,包括两种情况若row trx_id在数组中,表示这个版本是由还没提交的事务生成的,不可见若row trx_id不在数组中,表示这个版本是已经提交了的事务生成的,可见
innodb利用了所有数据都有多个版本的这个特性,实现了秒级创建快照的能力
一个数据版本，对于一个事务视图来说，除了自己的更新总是可见以外，有三种情况
版本未提交，不可见
版本已提交，但是是在视图创建后提交的，不可见
版本已提交，而且是在视图创建前提交的，可见
更新数据都是先读后写的，而这个读，只能读当前的值，称为当前读