MySQL 高频面试题目

一、一条SQL查询语句是如何执行的？

(1)客户端与数据库建立连接
(2)查询缓存,如果命中缓存，则立即返回存储在缓存中的数据。
(3)解析器先进行词法分析，将SQL语句打碎成一个个单词，根据构词规则识别单词中的关键字和非关键字，接着进行语法解析，判断是否满足MySQL的语句，接着生成语法树。如果语法错误则返回

(4)解释器是分析语法有没有错误，但是它无法知道数据库中有没有数据表和字段，预处理器根据MySQL规则进一步检查解析树是否合法。如检查表名，列名是否正确，是否有表权限等。
(5)查询优化器对解析树进行优化，然后生成不同的执行计划，然后选择一种最优的执行计划，MySQL里边使用的是基于开销的优化器，哪种执行计划开销最小就使用哪一种。(可以用EXPLAIN来查看执行计划，其能够看出多表关联查询，先查询哪一张表？执行查询的时候用到了什么索引)
(6)执行引擎根据执行计划调用存储引擎的API完成整个查询。

二、MyISAM，InnoDB，Memory三种存储引擎比较

1，MyISAM
应用范围较小，表级别的锁定限制了读/写的性能，因此在Web和数据仓库的配置中，它通常用于只读或以读为主的工作。
特点：支持表级别的锁(插入和更新会锁表)，不支持事务
拥有较高的插入（Insert）和查询（select）速度
存储了表的行数（Count速度更快）
表结构：.frm:存储表定义；myd(MYData):存储数据；MYI（MYIndex）存储引擎
适合：只读之类的数据分析的项目
(怎么快速向数据库插入100万条数据？我们有一种先用ISAM插入数据，然后用存储引擎修改为InnoDB的操作)

2，InnoDB
特点：支持事务，支持外键，因此数据的完整性、一致性更高。
支持行级别的锁和表级别的锁。
支持读写并发，写不阻塞读（MVCC）
特殊的索引存放方式，能够减少IO，提升查询效率
表结构：.frm存储表定义 idb:存储数据和索引，在同一文件中
适合：经常更新的表，存在并发读写或者有事务处理的业务系统。
3，Memory
特点：将所有的数据存储在RAM中，以便在需要快速查找非关键数据的环境中快速访问。
特点：把数据放在内存中，读写的速度很快，但是数据库重启或者崩溃，数据会全部消失，只适合做临时表。
表结构：.frm存储表的定义，数据存储在内存中
适合：做临时表，将表中的数据存储到内存中

存储引擎选择：
如果对数据一致性要求比较高，需要事务支持，可以选择InnoDB
如果数据查询多更新少，对查询性能要求比较高，可以选择MyISAM
如果需要一个用于查询的临时表，可以选择Memory。

为什么MyIsam与InnoDB快？

1）数据块，INNODB要缓存，MYISAM只缓存索引块， 这中间还有换进换出的减少；

2）innodb寻址要映射到块，再到行，MYISAM记录的直接是文件的OFFSET，定位比INNODB要快

3）INNODB还需要维护MVCC一致；虽然你的场景没有，但他还是需要去检查和维护
MVCC (Multi-Version Concurrency Control)多版本并发控制

三 InnoDB内部原理

为了提升数据库的读写效率，查询的时候会先查询缓存Buffer pool是否存在，存在则返回，修改数据的时候也是先写入到buffer pool,而不是直接写到磁盘。内存的数据页和磁盘数据不一致的时候，就称之为脏页。每隔一段时间就会有专门的线程写入磁盘。但是写的过程可能碰到宕机。

引入缓存
为了解决这个问题，引入了Redo log，InnoDB把所有对页面的修改操作专门写入到日志文件Redo log，会将DDL和DML语句都存储起来。如果有未同步到磁盘的数据，数据库启动的时候，会将这个日志文件进行恢复操作（实现crash-safe）。事务中的ACID里边的D（持久性），就是用它来实现的。同样是写磁盘，之所以不直接写到Db File里边，是因为和磁盘寻址的过程有关。刷盘是随机I/O（需要的数据随机分散在磁盘的不同扇叶中，相应的数据需要等到磁臂旋转到指定的页，然后盘片找到对应的扇区，才能找到所需要的一块数据，一次进行此过程知道找完所有数据，就是随机I/O），而记录日志是顺序I/O(连续写的,假定我们找到了第一块数据，并且其他所需的数据就在这一块数据后边，那么就不需要重新寻址),顺序I/O效率更高，本质上是数据集中存储和分散存储的区别。因此先把修改写入日志文件，在保证内存数据安全性的情况下，可以延迟输盘时机，进而提升系统吞吐。
redo log不是记录数据页更新之后的状态，而记录的是“在某个数据页上做了什么修改”，属于物理日志。redo log的大小是固定的，前面的内容会被覆盖，一旦写满，就会触发buffer poll到磁盘的同步，以便腾出空间记录后边的修改。

InnoDB的页和操作系统的页大小不一致，InnoDB页大小一般为16K，操作系统页大小为4K，InnoDB的页写入磁盘中，一个页需要分4次写。 如果存储引擎正在写入页的数据到磁盘时发生了宕机，可能出现页只写了一部分的情况，比如只写了4K，就宕机了，这种情况叫做部分写失效，可能会导致数据丢失。

所以在对于应用redo log之前，需要一个页的副本，如果出现了写入失效，就用页的副本来还原这个页，然后再应用redo log。这个页的副本就是double write,InnoDB的双写奇数，通过它实现了数据页的可靠性。double write由两部分组成，一部分是内存的double write,一个部分是磁盘上的double write。因为double write是顺序写入的，不会带来很大的开销。

预读机制

InnoDB有一个预读机制（read ahead）。也就是说，访问某个page的数据的时候， 相邻的一些page可能会很快被访问到，所以先把这些page放到buffer pool中缓存起来。
这种预读的机制又分为两种类型，一种叫线程预读（异步的）(Linear read-ahead)。为了便于管理，InnoDB把64个相邻的page叫做一个extent(区)。如果顺序地访问一个extent的56个page,这个时候InnoDB就会把下一个extent（区）缓存到buffer pool中。

第二种叫做随机预读 （Random read-ahead）,如果buffer pool已经缓存了同一个extent（区）的数据页的个数超过13时候，就会把这个extent剩余的所有page全部缓存到buffer pool。

很明显，线性预读或者异步预读，能够把可能即将用到的数据提前加载到buffer pool，肯定能提升I/O的性能，所以是一种非常有用的机制。但是也会带来副作用，就是导致占用的内存空间更多，剩余的空闲页更少。如果buffer pool size不是很大，而预读的数据很多，很有可能哪些真正的需要被缓存的热点数据被预读的数据挤出buffer pool，淘汰掉了。下次访问的时候又要先去磁盘。

预读机制：考虑到缓存是有限制的，想要保存高频热点数据，所以使用了LRU缓存。
底层数据结构：链表 + 数组
将LRU LIst分为两部分，靠近head的叫做new sublist,用来放热数据（我们把它叫做热区）。靠近tail的叫做old sublist,用来放冷数据（我们把它叫做冷区）。中间的分割线叫做midpoint。也就是对buffer pool做一个冷热分离。所有新数据加入到buffer pool的时候，一律先放到冷数据区的head,不管是预读的，还是普通的读操作。所以如果有一些预读的数据没有被用到，会在old sublist(冷区)直接被淘汰。
放到LRU List以后，如果再次被访问，都把它移动到热区的head。
如果热区的数据长时间没有被访问，会被先移动到冷区的head部，最后慢慢在tail被淘汰。
默认情况下，热区占了5/8的大小，冷区占了3/8，这个值可以通过innodb_old_blocks_pct参数控制。
假设这一次加载然后被立即访问的冷区数量非常大，比如我们查询了一张几千万数据的达标，没有使用索引，做了一个全表扫描，或者dump全表备份数据。

如果短时间之内被访问了一次，导致他们他们全部被移动进热区的head,它会导致很多热点数据被移动到冷区甚至被淘汰，造成了缓存污染。InnoDb里边通过innodb_old_blocks_time这个参数来控制，默认是1秒钟。也就是说1秒钟之内被访问的，不算数，待在冷区不动。只有1秒钟以后被访问的，才从冷区移动到热区的head。很大程度上避免全表扫描或者预读的数据污染真正的热数据。

为了避免并发的问题，对于LRU链表的操作是要加锁的。也就是说每一次链表的移动，都会带来资源的竞争和等待。从这个角度来说，如果进一步提升LRU的效率，就要尽量地减少LRU链表的移动。

比如：把热区一个非常靠近head的page移动到head，有没有必要呢？
InnoDB对于new 区还有一个特殊的优化：
如果一个缓存页处于热数据区域，且在热数据区域的前1/4区域（注意是热数据区域的1/4，不是整个链表的1/4），那么当访问这个缓存页的时候，就不用把它移动到热区域的头部；如果缓存也处于热区域的头部；如果缓存也处于热区的3/4区域，那么当访问这个缓存也的时候就会把它移动到头部。

预写机制 changBuffer

使用场景：数据页不是唯一所以，不存在数据从夫的情况，也就不需要从磁盘加载索引页判断数据是否不是重复（唯一性检查）。这种情况下可以先把修改记录在内存的缓冲池钟，从而提升更新语句（Insert，Delete，Update）的执行速度。

Undo log
undo log(撤销日志或回滚日志)记录了事务发生之前的数据状态，分给insert undo log 和 update undo log。如果修改数据时出现异常，可以用undo log来实现回滚操作（保持原子性）。
可以理解为 undo log记录的是反向的操作，比如insert会记录delete，update 会记录update 原来的值，跟redo log记录在哪个物理页面做了什么操作不同，所以叫做逻辑格式的日志。

Bin log
bin log以事件的形式记录了所有的DDL和DML语句（因为它记录的是操作而不是数据值，属于逻辑日志），可以用来做主从赋值和数据恢复。
根据redo log不一样，它的文件内容是可以追加的，没有固定大小限制。
在开启了binlog功能的情况下，我们可以把binglog导出成SQL语句，把所有的操作重放一边，来实现数据的恢复。

binlog的另一个功能就是用来实现主从赋值，它的原理就是从服务器读取主服务器的binlog，然后执行一遍。

事务 与 Bin log 与 redo log
两段式提交

为什么需要两阶段提交？
举例：
如果我们执行的是把name李四修改为张三，如果写完redo log，还没有写binlog的时候，Mysql重启了。
因为redo log可以在重启的时候用于恢复数据，所以写入磁盘的是张三，但是binlog没有记录这个逻辑日志，所以这时候用binlog区恢复数据或者同步到从库就会出席那数据不一致的情况。
所以在写两个雷子的情况下，binlog就充当了一个事务的协调者，通知InnoDB来执行prepare或者commit或者rollback。
如果binlog写入失败，就不会提交。
简单地来说，这里有两个写日志的操作，类似于分布式事务，不用两阶段提交，就不能保证都成功或者都失败。

崩溃恢复的时候，如何判断事务是否需要提交?
1、binlog无记录，redolog无记录：在redolog写之前crash，恢复操作：回滚事务
2、Binlog无记录，redolog状态prepare:在binlog写完之前的crash,恢复操作：回滚事务
3、Binlog有记录，redolog状态prepare:在binlog写完提交事务之前的crash:t提交事务
4、Binlog有记录，redolog状态commit:正常完成的事务，不需要恢复

关于Sql更新(修改)语句的流程或逻辑

一个SQL语句的更新流程是怎样的？

1. 加载数据到缓存池,例如加载名字为张三的记录所在的整页数据(相当于索引树的一个节点,16kb)。
   2.写入更新数据的旧值到undolog日志(回滚日志,是一种逻辑日志,记录与当前SQL相反的操作)中,方便回滚。
   3.执行器更新Buffer Pool缓存池中的内存数据。
   4.写入redolog日志，将操作结果写入Redo Log Buffer缓冲区（redolog日志为物理日志，例如记录的是更新哪个地址中的哪一条数据）。
   5.准备提交事务，redo日志写入磁盘文件中。
   6.提交事务，将更新操作写入到binlog日志（需要开启，默认关闭状态）,binlog日志写入磁盘(binlog主要用来恢复数据库磁盘里的数据,一般做备份或者同步数据使用)。
   7.写入commit标记到redo日志文件里，提交事务完成，该标记为了保证事务提交后的redolog与binlog数据一致。
   8.将Buffer Pool缓存池中修改后的数据随机写入磁盘,以Page为单位写入,执行完后,磁盘中的name=李四;

Innodb存储引擎会监控对表上二级索引的查找，如果发现某二级索引被频繁访问，二级索引成为热数据，建立哈希索引可以带来速度的提升

经常访问的二级索引数据会自动被生成到hash索引里面去(最近连续被访问三次的数据)，自适应哈希索引通过缓冲池的B+树构造而来，因此建立的速度很快。
哈希（hash）是一种非常快的等值查找方法，在一般情况下这种查找的时间复杂度为O(1),即一般仅需要一次查找就能定位数据。而B+树的查找次数，取决于B+树的高度，在生产环境中，B+树的高度一般3-4层，故需要3-4次的查询。

innodb会监控对表上个索引页的查询。如果观察到建立哈希索引可以带来速度提升，则自动建立哈希索引，称之为自适应哈希索引（Adaptive Hash Index，AHI）。
AHI有一个要求，就是对这个页的连续访问模式必须是一样的。
例如对于（a,b）访问模式情况：
where a = xxx
where a = xxx and b = xxx

特点
　　1、无序，没有树高
　　2、降低对二级索引树的频繁访问资源，索引树高<=4，访问索引：访问树、根节点、叶子节点
　　3、自适应
3、缺陷
　　1、hash自适应索引会占用innodb buffer pool；
　　2、自适应hash索引只适合搜索等值的查询，如select * from table where index_col=‘xxx’，而对于其他查找类型，如范围查找，是不能使用的；
　　3、极端情况下，自适应hash索引才有比较大的意义，可以降低逻辑读。

预读(read ahead)
InnoDB使用两种预读算法来提高I/O性能：线性预读（linear read-ahead）和随机预读（randomread-ahead）
为了区分这两种预读的方式，我们可以把线性预读放到以extent为单位，而随机预读放到以extent中的page为单位。线性预读着眼于将下一个extent提前读取到buffer pool中，而随机预读着眼于将当前extent中的剩余的page提前读取到buffer pool中。

线性预读（linear read-ahead）

方式有一个很重要的变量控制是否将下一个extent预读到buffer pool中，通过使用配置参数innodb_rea