mysql 一文搞定 面试知识点

一.引擎对比

只说俩常用的，其实工作中，MySQL也只会用到InnoDB,再知道一个mylsam，纯属为了面试

1.innoDB

支持事务，支持外键，行级锁，mvcc，有redolog undolog两个文件 （其实，innoBD的事务就是由mvcc，行锁来保证的，所以，它支持的特性，其实就是为了事务）

2.mylsam

支持全文索引，不支持事务，不支持外键，表级锁

二.存储结构

1.行结构

行结构 Compact

mysql存储行结构大概如下：

整体可以分为两部分，额外信息，真实数据。额外信息中的记录头信息中有指向下一条记录的指针，有删除标识等

一行数据最长只能是700字节，如果太长，会放进溢出页

2.页结构

首先理解一个概念，mysq的页并不是操作系统的页，mysql的页是mysql这个程序定义的一种数据结构，大概长这样：

 

页与页之间，通过file header中的指针互相连接。

除了file header，还有个page header，这个东西只有数据页有。索引页，溢出页，都没有。

多个页之间，大概长这样：

上图中的一个大块就是一个mysql的页，记录1->记录n 存放在UserRecords中，UserRecords的数据是以一个链表形式存储，新增数据时候，在UserRecords中申请空间，然后改变前一个及后一个数据的的指针，便完成了插入操作。由于使用的是链表，所以记录数据在物理上是不连续的，即UserRecords中的数据物理上是乱序的。另外，当空间不足插入新数据时候，会进行页的分裂，开辟一个新页，新页的Infimum指针指向当前页，当前页的supremum指向新页。注意，如果插入数据的主键比要插入的页最大数据要小时，在页分裂时还会进行页数据的迁移，此过程比较耗费性能，所以mysql才会建议主键自增。

3.区概念

mysql区是由若干页组成，大小固定1M。区是向磁盘申请空间的单位。由于mysql的页是双向链表结构，如果直接向系统申请页空间，两个页可能非常的远，为了让相邻的页的物理空间尽可能页相邻，所以引入了区，以区为单位向系统申请物理空间。

4.段概念

段是InnoBD的概念，它是为了方便管理页而引入的一个逻辑概念，简单来讲，就是将不同类型的页分属于不同的段，可以理解它为一种页的管理单位。

三.索引知识

1.索引前置知识B+树

前言：其实B+树属于一种相对复杂的数据结构，如果对节点(node)，数据(data)，树结构，平衡树这些东西完全没有概念的话，建议了解一下这部分知识。本文对B+树的介绍不会展开去讲，只从结构，插入，搜索三个角度简单阐述B+树，希望我的分享能让大家对B+树有一个初步的认知。

1）理解B+树

首先，我们了解一下，为什么要存在红黑树木，B-树，B+树这些复杂的结构，其实，它们出现的原因都很简单，为了进行搜索。对数据以一定的结构进行排序然后存储，从而提升搜索数据的效率，这就是这些结构存在的意义。既然是进行了排序，那么一定就有比较的操作，既然要比较，就一定要指定比较的字段，所以，对我们使用者来说，B+树它的目的就是：将用户的数据中的某一个(或者某几个)数据作为key值，以key值进行比较排序，然后组成一个方便进行后续查询的数据结构。

2）B+树的结构

B+树的结构可以分三部分来看：

非叶子节点： B+树中，非叶子节点只存储用来做数据比较的key值，不存放完整的用户data数据

叶子节点：叶子节点中存放完整的用户data数据

节点间关联指针：父节点以一定的规则存放不连续但是有序的key值，每个key值对应左右两个指针，分别指向最小key>=当前key，最大key

一个最简单的B+树结构示意图：

3）B+树的新增

B+树的新增从叶子节点开始，且不能破坏数据的有序性，具体的插入算法比较复杂。我们可以逆向思考一下，插入要完成的工作，从而理解原理。插入数据，首先要保证叶子数据整体的有序性，然后，每一个B+数节点的数据量是有上限的，如果节点的数据数量超过上限，节点需要进行分裂，然后，上级节点需要更新索引，更新索引时，也可能导致上级节点发生分裂。想明白了要做的事情，其实B+树的插入也过程也就能猜出来了，插入数据->判断是否分裂->若是分裂了，处理分裂，向上更新数据->.....此流程直到达到根节点。

4）B+数的搜索

首先，理解搜索的概念，我们是拥有一个能做比较的key值，找这个key值对应的完整数据。所以我们的操作是，从根节点开始，比较key值，根据比较结果拿到节点间关联指针从而找到key对应的下一层节点，然后逐级找到叶子节点，最后在叶子节点中找到真实数据。另外，由于叶子节点中，数据是有序的，所以此时的查找可以通过二分搜索加速而非遍历节点。

2.Mysql的索引

了解了B+树之后，理解mysql的索引就简单多了，Mysql的索引其实就是一个B+树结构

mysql的索引分为两类，聚簇索引和非聚簇索引。

聚簇索引是指，一个B+树，它的叶子节点存放了完整的行数据，mysql中，主键索引就是一个聚簇索引，对于InnoDB引擎，只能创建一个聚簇索引。

非聚簇索引是指，一个B+树，它的叶子节点不存放完整的行数据，而是存放聚簇索引的key值信息。mysql的InnoDB引擎中，除了主键之外的索引，无论是单列索引还是复合索引，都是非聚簇索引。

一张图大致表示一下聚簇索引和非聚簇索引

3.索引知识结语

其实，理解了B+树和聚簇，非聚簇索引之后，mysql的索引原理及面试常见问题就基本都能想通了，比如以下这几个面试题：

·回表是什么，为什么mysql搜索时候会有回表操作？答：因为非聚簇索引没有存放全量行数据信息，它的叶子节点只存放了作为B+数比较key值的一些列（即我们指定为索引或复合索引的列），所以如果查询的字段不在非聚簇索引的索引列+主键列里中，需要拿着查询到的主键列信息去聚簇索引中照全量数据。

·最左原则是什么？答：创建一个复合索引其实就是创建了一个B+树，复合索引的几个字段作为B+树进行比较的key，而由于该key由多个字段组成，所以比较时候肯定是先比较第一个字段，第一个字段相同再比较第二个字段。

·为什么有了索引之后，数据新增，删除操作会慢？答：因为创建B+树了，B+树的插入和删除操作涉及到分裂，调整这些复杂操作，都是要消耗性能的。

所以，其实索引的原理就在这些，遇到索引的问题多去思考B+树的原理，基本都能解决

四.日志，脏页，刷盘

mysq的日志有三种，分别为：binlog,redolog,undolog，这部分内容面试经常问

1.binlog

mysql的server级别的log。需要手动在配置文件中开启。其中记录了所有的写数据，不会记录读操作，在事务提交后写入。该日志的记录有三种模式，statement模式，row模式，mixed模式。

statement模式记录的是sql语句，其优点是日志比较小，缺点是一些函数，比如now()函数这种，在恢复数据时候仍然用这个函数，导致恢复前后数据不一致。

row模式记录所有的细节，缺点是日志比较大

mixed模式是statement和row模式的综合，mysql自动选择statement模式和row模式，规则是普通语句用statement，遇到函数，存储过程，触发器就用row。

2.redolog

InnoDB引擎级别的日志，记录数据页的变动，。 binlog和redolog相比，binlog偏向记录逻辑(一个完整sql)，redolog偏向物理记录(一个页中的某条记录的某个值从A变成了B)，redoLog是一个二进制日志。另外，redolog日志不是无限递增的，它的记录数量有限，目的是做数据故障恢复，而非binlog那种，记录全量日志。另外，redolog和脏页知识强相关，或者说，redolog就是为脏页存在也不为过，脏页概念在下面会聊到。

3.undolog

InnoDB引擎级别的日志，其记录内容和mvcc有关，是用于做数据的版本控制。在InnoDB中，每行数据有三个隐藏字段，分别是row_id(行号，如果没有主键，它会当聚簇索引的key),trx_id(事务id),roll_pointer（上一个版本的数据指针）。以一行数据来说明大致流程：

对于某一行数据，每一次改动，它的roll_pointer都会指向改动之前版本的数据（对于某一行树，trx_id不一定连续的，因为在表操作的一个事务里，不一定会改变这一行的数据）

可以看到，一行数据其实有很多个版本，undolog就是存放这些东西的，只有最新的一个版本的数据才是放在真实数据页中的，其他版本的数据都在undolog中，结构如下：

一些关于undolog页深入的内容：undolog除了日志文件之外，还存在undo页，和数据页结构类似， 数据页的增删改也会影响对应的undo页， undo页在数据页执行操作之后，是新增还是复用，又涉及到一堆复杂的判断，个人觉得，除非是做数据库二开的，否则真没有必要研究那么深入，对头发不好。

4.脏页，flush，redolog的故障恢复

当我们写入一条数据到某个页时，页数据不会立刻刷盘将数据写到idb文件中，这时候就出现了内存数据和磁盘idb文件数据不一致问题，我们称这种内存数据和磁盘数据不一致的页数据为脏页。对于脏页，mysq的解决方案是，当数据写入页，提交事务时候，同步写redolog日志文件，然后到达一定数量，再进行idb文件的写入。之所以这么设计，是因为页数据写入idb文件是一随机写(页和页的内存本来就不连续，从磁盘中找随机位置的页写入就是一种随机写)，而redolog是一种顺序写，顺序写的速度比随机写快的多得多。我们常说的故障恢复，其实就是脏页数据的恢复，这种恢复就是靠redolog来完成。（一个细节，如果一定要说什么情况无法故障恢复的话，就是在数据写入内存中的页之后，在同步写入redolog文件之前，发生了宕机等故障，这种情况确实无法恢复，不过基本不可能发生，问这种问题的面试官纯属闲的）

五.事务隔离级别

事务的四种隔离级别：

串行化： 最高隔离级别，对表的操作完全串行，性能影响大。

可重复读： 事务中的读操作每次能读到相同的数据，该功能实现基于版本控制 （mysql的默认事务隔离级别）。

读已提交： 事务提交后的数据才能被看到。

读未提交· 相当于完全没有事务间的隔离，数据没提交之前也能被读到。

***如何记这几个：想记住其实还是要理解一些原理，从实现的轻量级到重量级来记。如果数据不做任何保护措施，那就是读未提交；如果对数据做了一定控制，将事务中改变的数据和可读的数据做了隔离，就是读已提交；再进一步完善，每个数据读操作都有一个完整的副本，保证每次读到的数据一样，就是可重复读；最后，完全的做串行化，就是最高级的数据安全。

脏读，不可重复读，幻读：

脏读，就是读到了别的事务没提交的数据，然后别的数据回滚了，这不就读错了么，所以就是脏读。数据库隔离级别达到读已提交就能解决这个问题。

不可重复度读，就是同样的where条件，每次读取到的数据不一样，这不就不重复了么，所以叫不可重复读。数据库隔离级别达到可重复读能解决这个问题。

幻读，很多博客把这个和不可重复度读说成一个是行数，一个是一行数据的变化，其实这个说法是不完全的。幻读的正确理解应该是数据状态，读到了一个状态不足以支撑业务的数据就叫幻读，比如，有一个业务，先判断 id = 3的数据是否存在呢，不存在然后插入。这时，执行查询的时候查id=3的数据为null，认为该数据不存在，然后进行insert插入操作，但是insert的时候数据库报错，id=3的数据已存在（即在本事务中，读和写之间的时候，别的事务提交了一个id=3的数据）。这种情况就称为幻读。数据库隔离级别达到串行化才能解决。

六.mvcc与锁

1.mvcc

mysql的的innoDB的多版本控制机制，其实上文在聊undolog时候已经说了很多关于数据的事务版本号的问题。关于mvcc，我个人认为，如果不是做数据库二开的同学，没有必要特别详细的去扣细节，只需要明白几个事情就可以：

1）基于 undolog的事务号做版本控制

2）在可重复读的隔离级别时候，select语句会根据事务版本号生成一个readView的视图，在事务提交之前，该视图内容不会变，从而达到可重复读

2.锁

mysql中的锁主要分行锁和表锁两大类

1）行锁

行锁分为五种

.记录锁： 例如，在update XXX set column = 3 where id = 1 这条语句，它是写操作，并且指定了具体的id，这时就对id=1的数据进行了一个记录锁，在本事务提交之前，其他事务操作本行数据会被阻塞。

.间隙锁（gap）：回忆索引知识，在非叶子节点中，每个节点的比较key值都是多个不连续的，在两个key之间的间隙指向下一级的节点，B+树的这个间隙，就是间隙锁所谓的间隙。简单来说，间隙锁是一个范围，比如， update XXX set column = 3 where id < 5,就得到了 一个负无穷到5的开区间的锁

.next-key（记录+间隙） update XXX set column = 3 where id < 5得到的是一个间隙锁，update XXX set column = 3 where id

.插入意向锁。当某个区间被间隙锁锁定，然后事务向该区间申请插入一条数据，就会得到一个插入意向锁，当该区间的间隙锁释放时，该插入操作可以执行

.隐式锁：隐式锁是针对insert的一种锁，insert默认时候是不加锁的，如果在insert的事务中，另外一个事务访问了正在insert的这个数据，会先进行事务版本号（trx_id）的对比，看是不是已经提交的事务，如果不是，则会对该数据进行加锁。

2）表锁

表锁分读锁和写锁

Lock table use read； 加读锁

Lock table use write； 加写锁

表锁性能很差，基本不用，了解一下就好

3）锁的查看

select * from performance_schema.data_locks; # 锁的概况

select * from information_schema.innodb_locks; # 版本锁的概况

show engine innodb status; #InnoDB整体状态，其中包括锁的情况

七.mysql的优化

1.慢查询日志，EXPLAIN分析sql

慢查询日志：默认情况下，MySQL没有开启慢查询日志。需要手动打开。一般不太建议开启，影响性能。实际开发中顶多在测试环境开一下。

开启方式：set global slow_query_log = 1; 只对当前库生效 set long_query_time = n; 设置慢查询的阈值。也可以在配置文件开，搜关键字slow_query_log

EXPLAIN分析sql

用法： sql语句之前加一个 EXPLAIN关键字就行，长这样：

一般我们分析的时候，看的最多的就是 type列，再就是key列。说一下这俩，key列指的是这条sql用的索引键，type指的是这条sql的执行方式，详细看一下type的各种情况：

• ALL：表示全表扫描，性能最差。
• index：表示基于索引的全表扫描，先扫描索引再扫描全表数据，有回表。
• range：表示使用索引范围查询。使用>、>=、
• ref：表示使用非唯一索引进行单值查询。
• eq_ref：一般情况下出现在多表join查询，表示前面表的每一个记录，都只能匹配后面表的一行结果。
• const：表示使用主键或唯一索引做等值查询，常量查询。
• NULL：表示不用访问表，速度最快。

参数调优

调优参数强烈建议直接在官网看，位置：

官网-->document-->MySQL Server & MySQL Cluster-->MySQL Reference Manual-->The InnoDB Storage Engine

以下介绍几个常用的调优参数及意义
- innodb_buffer_pool_size  可以缓存索引和行数据，值越大，IO读写就越少，如果单纯的做数据库服务，该参数可以设置到电脑物理内存的75-80%
- query_cache_size  查询缓存大小，MySQL查询缓存保存查询返回的完整结果。当查询命中该缓存，会立刻返回结果，跳过了解析，优化和执行阶段（其实这个东西用处不大，等下聊sql执行流程会具体说为什么）
- innodb_flush_log_at_trx_commit  Redo Log写入优化的设置， Redo Log默认和事务同步写入磁盘，通过这个设置，可以让数据不立刻入盘
- innodb_read_io_threads / innodb_write_io_threads 读写的线程，线程可以适当多些

八.额外补充，sql的执行流程

这个图可以看到几种buffer的具体意义，buffer_pool是innoDb的内存缓冲池，它存放页数据，它越大，IO进行的越少。而query_cache是一种server层的查询缓存，它的命中很苛刻，必须同条件，在执行数据写入时还会被删掉，而且，我们使用的持久层框架比如mybatis一般也支持缓存，所以除非项目中90%以上操作都是读，否则这个东西的意义真不那么大。