【建议收藏】Mysql知识干货（mysql八股文）汇总

这一篇文章分享mysql的面试知识，涵盖点比较多。下面我们来从总体到局部来看完mysql相关的面试知识。预告下一篇是网络面试知识，用图解的方式呈现给大家。希望大家多多支持，点赞，转发，在看 三连。

问：mysql整体架构是怎么样的？

mysql整体架构大概可以分为：网络连接层、服务层、存储引擎层和系统文件层。 关于mysql官方的架构图如下，虽然经历多个版本迭代，但整体架构还是差不多

1）网络连接层 Connectors组件，是mysql向外提供的交互组件，如java,.net,php等语言可以通过该组件来操作SQL语句，实现与SQL的交互。 2）服务层 服务层是mysql Server的核心。主要包含系统管理和控制工具、连接池、SQL接口、解析器、查询优化器和缓存Cache&Buffer六个部分。

• 连接池（Connection Pool）：负责存储和管理客户端与数据库的连接，一个线程负责管理一个连接。
• 系统管理和控制工具（Management Services & Utilities）：例如备份恢复、安全管理、集群 管理等
• SQL接口（SQL Interface）：用于接受客户端发送的各种SQL命令，并且返回用户需要查询的结果。
• 解析器（Parser）：负责将请求的SQL解析生成一个"解析树"。然后根据一些mysql规则进一步检查解析树是否合法。
• 查询优化器（Optimizer）：当“解析树”通过解析器语法检查后，将交由优化器将其转化成执行计划，然后与存储引擎交互。
• 缓存（Cache&Buffer）：缓存机制是由一系列小缓存组成的。比如表缓存，记录缓存，权限缓存，引擎缓存等。如果查询缓存有命中的查询结果，查询语句就可以直接去查询缓存中取数据。

3）存储引擎层（Pluggable Storage Engines） 存储引擎负责mysql中数据的存储与提取，与底层系统文件进行交互。mysql存储引擎是插件式的，服务器中的查询执行引擎通过接口与存储引擎进行通信，接口屏蔽了不同存储引擎之间的差异 。现在有很多种存储引擎，各有各的特点，最常见的是MyISAM和InnoDB。 4）系统文件层（File System） 该层负责将数据库的数据和日志存储在文件系统之上，并完成与存储引擎的交互，是文件的物理存储层。主要包含日志文件，数据文件，配置文件，pid 文件，socket 文件等。

问：mysql集群架构

关于mysql的架构，最底层的只有主从模式，关于主从模式是简单灵活，能满足多种需求，比较主流的用法，但是写操作高可用需要自己考虑。我们常见还有双主模式，该模式从主从模式演变为双主模式，有双主双写、双主单写两种方式，一般建议使用双主单写。关于主从架构的模式参考下面的图。

问：mysql的查询和插入的执行流程

下面我们图解mysql一条查询语句是怎么运行的：

mysql查询图解

1. mysql客户端对mysql server的监听端口发起请求
2. 在连接池组件创建连接，分配线程，并验证用户名，密码，库表>权限。
3. 查询query_cache，如果有数据直接返回，没有则继续执行。
4. 通过sql接口组件接收sql语句，sql会通过查询分析器分解成数据结构，并且这个结构传递给后续步骤
5. 查询优化器组件组成查询路径树，并选举一条最优的查询路径。
6. 调用存储引擎接口，打开表，执行查询，检查存储引擎缓存中是否有对应的缓存记录，如果没有就继续往下执行。
7. 到磁盘物理文件中寻找数据。
8. 当查询到所需要的数据之后，先写入存储引擎缓存中，并往query_cache写进去。
9. 返回数据给客户端。
10. 关闭表。
11. 关闭线程。
12. 关闭连接。

mysql插入的过程如下

mysql插入流图解

1. mysql客户端对mysql server的监听端口发起请求
2. 在连接池组件创建连接，分配线程，并验证用户名，密码，库表>权限。
3. 检查没有问题之后，便进入引擎层开始正式的提交。我们知道 InnoDB 会将数据页缓存至内存中的 buffer pool，所以 insert 语句到了这里并不需要立刻将数据写入磁盘文件中，只需要修改 buffer pool 当中对应的数据页就可以了。
4. 在开启redo log刷盘策略的时候，当 innodb_flush_log_at_trx_commit=1 时，每次事务提交都会触发一次 redo log 刷盘。（redo log 是顺序写入，相比直接修改数据文件，redo 的磁盘写入效率更加高效）
5. 如果开启了 binlog 日志，我们还需将事务逻辑数据写入 binlog 文件，且为了保证复制安全，建议使用 sync_binlog=1 ，也就是每次事务提交时，都要将 binlog 日志的变更刷入磁盘。
6. 返回数据给客户端。
7. 关闭表。
8. 关闭线程。
9. 关闭连接。

问：mysql有哪些字段类型

关于mysql的数据类型主要以上几种，一般我们创建表结构都是使用innodb引擎，后面我们会深入innodb引擎的知识。特别我们需要注意的是，为了获取更好的兼容性，建议使用utf8mb4字符集，主要是用来兼容四字节的unicode。

utf8mb4与utf8的区别

mysql在5.5.3版本之后增加了utf8mb4编码，mb4就是most bytes 4的意思，专门用来兼容四字节的unicode。其实，utf8mb4是utf8的超集，理论上原来使用utf8，然后将字符集修改为utf8mb4，也不会对已有的utf8编码读取产生任何问题。mysql支持的utf8编码最大字符长度为3字节，如果遇到4字节的宽字符就插入异常。

char和varchar区别

char 表示定长，长度固定，varchar表示变长，即长度可变。char如果插入的长度小于定义长度时，则用空格填充；varchar小于定义长度时，还是按实际长度存储，插入多长就存多长。

char的存取速度还是要比varchar要快得多，方便程序的存储与查找；但是char也为此付出的是空间的代价，因为其长度固定，所以会占据多余的空间，可谓是以空间换取时间效率。varchar则刚好相反，以时间换空间。

对 char 来说，最多能存放的字符个数 255，和编码无关。而 varchar 呢，最多能存放 65532 个字符。varchar的最大有效长度由最大行大小和使用的字符集确定。整体最大长度是 65,532字节。

timestamp和datatime区别

• timestamp占4个字节。datetime占用8个字节
• timestamp记录是1970-01-01 00:00:01到现在的数数,时间范围：‘1970-01-01 00:00:01’ UTC ~ ‘2038-01-19 03:14:07’ UTC，受时区影响。datetime不受时区影响，时间范围:‘1000-01-01 00:00:00’ ~ ‘9999-12-31 23:59:59’
• timestamp存储占用的空间和INT类型相同，客户端插入的时间从当前时区转化为UTC，查询时，将其又转化为客户端当前时区进行返回。datetime，不做任何改变，基本上是原样输入和输出。

数据库三范式

• 第一范式(1NF)是指数据库表的每一列都是不可分割的基本数据线；也就是说：每列的值具有原子性，不可再分割。
• 第二范式(2NF)是在第一范式(1NF)的基础上建立起来的，满足第二范式(2NF)必须先满足第一范式(1NF)。如果表是单主键，那么主键以外的列必须完全依赖于主键；如果表是复合主键，那么主键以外的列必须完全依赖于主键，不能仅依赖主键的一部分。
• 第三范式(3NF)是在第二范式的基础上建立起来的，即满足第三范式必须要先满足第二范式。第三范式(3NF)要求：表中的非主键列必须和主键直接相关而不能间接相关；也就是说：非主键列之间不能相关依赖。在规范和性能之间需要平衡，如何表拆得过细，会导致查表关联过多，导致性能问题。因此需要在规范性和性能之间做取舍。有时候会采用反三范式。

问：mysql存储引擎有哪些？innodb有什么特点？

innodb，myisam，memory，merge，archive，ndb innodb引擎，从mysql5.5版本之后，mysql的默认内置存储引擎已经是innodb了

• 支持事务，默认的事务隔离级别是可重复度，通过mvcc（并发版本控制）来实现。
• 行级锁，可以支持更高的并发。
• 支持外键，虽然有这个功能，但实际业务中不怎么用。
• 在innodb中有缓冲管理，通过缓冲池，将索引和数据全部缓存起来，加速查询速度。
• 在innodb中数据的物理组织形式是聚簇表。所有的数据按照主键来组织。数据和索引放在一块，都位于B+数的叶子节点上。
• innodb引擎如果没有设置主键索引，innodb则会选择内置的6字节的row-id作为隐含的聚集索引。
• innodb支持分区，表空间。
• innodb引擎对硬件要求比较高。
• innodb灾难恢复性比较好。

问：mysql索引有哪些，特征是怎么样的？

什么是索引？

• 索引是帮助存储引擎高效获取数据的一种数据结构。
• 索引按照物理存储的类型分聚簇索引，辅助索引
• 索引按照字段特性分类：主键索引，唯一索引，普通索引，前缀索引，组合索引，全文索引（mysql5.6.4之后支持）

优点： 1）检索：可以提高数据检索的效率，降低数据库的IO成本，2）排序：通过索引列对数据进行排序，降低了CPU的消耗 缺点： 1）占磁盘空间，2）降低更新表的效率 下面我们看下不同的索引的含义和解释

• 聚簇索引：主键索引(聚集索引)的叶子结点会存储数据行，也就是说数据和索引在一起
• 辅助索引：同样用B+Tree,data域存储相应记录主键的值而不是地址，首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。
• 覆盖索引：覆盖索引一般针对的是辅助索引，整个査询结果只通过辅助索引就能拿到结果，不需要通过辅助索引树找到主键，再通过主键去主键索引树里获取其它字段值。
• 主键索引：主键索引是一种特殊的唯一索引，一个表只能有一个主键且不允许有空值；索引列只能出现一次且必须唯一，InnoDB要求表必须有主键，如果没有显示设置主键索引，那么会自动为数据表创建一个隐含的字段 row-id 作为主键，这个字段为 6 字节的长整型。
• 唯一索引：建立在unique字段上的索引就是唯一索引，不允许具有索引值相同的行，索引列的值可以允许为null
• 普通索引：要求字段不为主键也不要求字段为unique的索引叫普通索引。
• 前缀索引：前缀索引是指对字符类型字段的前几个字符或对二进制类型字段的前几个bytes建立的索引。例子：name(varchar(16))
• 组合索引：在表中的对个字段组合上创建的索引。并且遵循最左前缀原则匹配。
• 全文索引：只能在char,varchar,text类型字段上使用全文索引。全文索引,通过建立倒排索引,可以极大的提升检索效率,解决判断字段是否包含的问题。

问：讲讲mysql事务？

事务：事务是逻辑上的一组操作，要么都执行，要么都不执行。

事务的特性：

1. 原子性（atomicity）：事务是最小的执行单位，不允许分割。事务的原子性确保动作要么全部完成，要么完全不起作用。
2. 一致性（consistency）：执行事务前后，数据保持一致，例如转账业务中，无论事务是否成功，转账者和收款人的总额应该是不变的。
3. 隔离性（ioslation）：并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的。
4. 持久性（durability）：一个事务被提交之后，对数据库中数据的改变是持久的，即使数据库发生故障也不应该对其有任何影响。

事务的隔离级别

• 读取未提交 READ-UNCOMMITTED

允许读取尚未提交的数据变更，最低的隔离级别，可能导致脏读、幻读或不可重复读。

读取已提交 READ-COMMITTED

允许读取并发事务已经提交的数据，可以避免脏读，可能导致幻读或不可重复读。

可重复读 REPEATABLE-READ

同一个事务下多次读取结果都是一致的，除非数据是被自己的事务所修改，可以避免脏读、不可重复读，但可能导致幻读。

串行化 SERIALIZABLE

脏读(读取未提交数据)：

脏读指的是读到了其他事务未提交的数据，未提交意味着这些数据可能会回滚，也就是可能最终不会存到数据库中，也就是不存在的数据。读到了并一定最终存在的数据，这就是脏读

不可重复读(前后多次读取，数据内容不一致)：

一个事务内两个相同的查询却返回了不同数据。这是由于查询时系统中其他事务修改的提交而引起的。

幻读(前后多次读取，数据总量不一致)：

一个事务对一个表中的数据进行了修改，这种修改涉及到表中的全部数据行。同时，另一个事务也修改这个表中的数据，这种修改是向表中插入一行新数据。那么，操作前一个事务的用户会发现表中还有没有修改的数据行，就好象发生了幻觉一样。

mysql创建事务方式

START TARNSACTION |BEGIN：显式地开启一个事务。
COMMIT：提交事务，使得对数据库做的所有修改成为永久性。
ROLLBACK：回滚会结束用户的事务，并撤销正在进行的所有未提交的修改。

mysql默认的事务隔离级别是：可重复读

问：讲讲mysql锁相关知识

mysql的锁按照范围可以分为全局锁、表锁、行锁，其中行锁是由数据库引擎实现的，并不是所有的引擎都提供行锁。我这里只讲innodb引擎的锁。

全局锁

mysql提供全局锁来对整个数据库实例加锁。

// 锁表
FLUSH TABLES WITH READ LOCK
// 解锁
unlock tables

上面语句一般都是用来备份的，当执行这条语句后，数据库所有打开的表都会被关闭，并且使用全局读锁锁定数据库的所有表，同时，其他线程的更新语句（增删改），数据定义语句（建表，修改表结构）和更新类的事务提交都会被阻塞。

mysql 8.0 以后，对于备份，mysql可以直接使用备份锁

// 加锁
LOCK INSTANCE FOR BACKUP
// 解锁
UNLOCK INSTANCE

备份锁范围更广，会阻止文件的创建，重命名，删除，包括 REPAIR TABLE TRUNCATE TABLE, OPTIMIZE TABLE操作以及账户的管理都会被阻塞。当然这些操作对于内存临时表来说是可以执行的，为什么内存表不受这些限制呢？因为内存表不需要备份，所以也就没必要满足这些条件。

表锁

表级别锁分为两类，一类是元数据锁（Metadata Lock，MDL），一种是表锁。

表锁分为读锁和写锁，读锁不互斥，但是获取读锁不能写入数据,其他没有获取到读锁的session也是可以读取表的，所以读锁的目的就是限制表被写。

写锁被获取后可以对表进行读写，写锁是互斥的，一旦某个session获取到表的写锁，另外的session无法访问这个表，直到写锁被释放。

表的解锁可以使用unlock tables解锁，也可以客户端口自动解锁。

元数据锁(MDL) 不需要显式使用，在访问一个表的时候会被自动加上。这个特性需要MySQL5.5版本以上才会支持，当对一个表做增删改查的时候，该表会被加MDL读锁；当对表做结构变更的时候，加MDL写锁。

MDL锁有一些规则

1. 读锁之间不互斥，所以可以多线程多同一张表进行增删改查。
2. 读写锁、写锁之间是互斥的，为了保证表结构变更的安全性，所以如果要多线程对同一个表加字段等表结构操作，就会变成串行化，需要进行锁等待。
3. MDL的写锁优先级比MDL读锁的优先级高，但是可以设置max_write_lock_count系统变量来改变这种情况，当写锁请求超过这个变量设置的数后，MDL读锁的优先级会比MDL写锁的优先级高。（默认情况下，这个数字会很大，所以不用担心写锁的优先级下降）
4. MDL的锁释放必须要等到事务结束才会释放

行锁

共享锁

共享锁能允许事务获取到锁后进行读操作，共享锁是不互斥的，一个事务获取到共享锁后，另外一个事务也可以获取共享锁，获取共享锁后不能进行写操作

排它锁

排他锁允许事务获取到锁后进行更新一行或者删除某一行操作，排他锁顾名思义是互斥的，一个事务获取到排他锁后，其他事务不能获取到排他锁，直到这个锁被释放。

意向锁

innodb支持多种粒度的锁，允许行锁和表锁共存，这里说的意向锁其实是一种表级别的锁，但是我把它放在行锁里面是因为它不会单独存在，它的出现肯定会伴随着行锁（共享锁或者排他锁），它主要的目的就是表示将要锁定表中的行或者正在锁定表中的行。意向锁的获取必须在行锁获取之前，也就是说获取共享锁之前必须先要获取共享意向锁，对于排他锁也是一样的道理。

死锁

是指两个或两个以上的进程在执行过程中,因争夺资源而造成的一种互相等待的现象,若无外力作用，它们都将无法推进下去.此时称系统处于死锁状态或系统产生了死锁，这些永远在互相等待的进程称为死锁进程。表级锁不会产生死锁.所以解决死锁主要还是针对于最常用的innodb。

问：讲讲mysql mvcc的机制

多版本并发控制技术的英文全称是 Multiversion Concurrency Control，简称 MVCC。 多版本并发控制（MVCC） 是通过保存数据在某个时间点的快照来实现并发控制的。也就是说，不管事务执行多长时间，事务内部看到的数据是不受其它事务影响的，根据事务开始的时间不同，每个事务对同一张表，同一时刻看到的数据可能是不一样的。 多版本并发控制的思想是保存数据的历史版本，通过对数据行的多个版本管理来实现数据库的并发控制。这样我们就可以通过比较版本号决定数据是否显示出来，读取数据的时候不需要加锁也可以保证事务的隔离效果。 MVCC解决了哪些问题？

• 读写之间阻塞的问题。通过 MVCC 可以让读写互相不阻塞，即读不阻塞写，写不阻塞读，这样就可以提升事务并发处理能力。
• 降低了死锁的概率。因为 InnoDB 的 MVCC 采用了乐观锁的方式，读取数据时并不需要加锁，对于写操作，也只锁定必要的行。
• 解决一致性读的问题。一致性读也被称为快照读，当我们查询数据库在某个时间点的快照时，只能看到这个时间点之前事务提交更新的结果，而不能看到这个时间点之后事务提交的更新结果。快照读（SnapShot Read） 是一种一致性不加锁的读，是InnoDB并发如此之高的核心原因之一。

innodb 的MVCC是如何工作的？ 事务版本号：每开启一个事务，我们都会从数据库中获得一个事务 ID（也就是事务版本号），这个事务 ID 是自增长的，通过 ID 大小，我们就可以判断事务的时间顺序。 行记录的隐藏列：innodb的叶子段存储了数据页，数据页中保存了行记录，而在行记录中有一些重要的隐藏字段：

• DB_TRX_ID(6字节)：它是最近一次更新或者插入或者删除该行数据的事务ID(若是删除，则该行有一个删除位更新为已删除。但并不是真正的进行物理删除，当InnoDB丢弃为删除而编写的更新撤消日志记录时，它才会物理删除相应的行及其索引记录。此删除操作称为清除，速度非常快)
• DB_ROLL_PTR(7字节)：回滚指针，指向当前记录行的undo log信息(指向该数据的前一个版本数据)
• DB_ROW_ID(6字节)：随着新行插入而单调递增的行ID。InnoDB使用聚集索引，数据存储是以聚集索引字段的大小顺序进行存储的，当表没有主键或唯一非空索引时，innodb就会使用这个行ID自动产生聚簇索引。如果表有主键或唯一非空索引，聚簇索引就不会包含这个行ID了。这个DB_ROW_ID跟MVCC关系不大。

undo log 将行记录快照保存在里面，我们可以在回滚段中找到它们。 在可重复读的隔离级别下： 查询：符合下面两个条件的记录作为返回结果：1）innodb只查找版本早于当前事务版本的数据行（也就是，行的系统版本号小于或等于事务的系统版本号），这样可以确保事务读取的行，要么是在事务开始前已经存在的，要么是事务自身插入或者修改过的。2）行的删除版本要么未定义，要么大于当前事务版本号。这可以确保事务读取到的行，在事务开始之前未被删除。 插入：innodb为新插入的每一行保存当前系统版本号作为行版本号。 删除：innodb为删除的每一行保存当前系统版本号作为行删除标识。删除在内部被视为更新，行中的一个特殊位会被设置为已删除。 更新：innodb为插入一行新记录，保存当前系统版本号作为行版本号，同时保存当前系统版本号到原来的行作为行删除标识。

问：讲讲的mysql主从复制

什么是主从复制？ mysql主从复制是指数据可以从一个mysql数据库服务器主节点复制到一个或者多个从节点。mysql默认使用异步复制方式，这样从节点不用一直访问主服务器来更新自己的数据，数据的更新可以在远程连接上进行，从节点可以复制主数据库中的所有数据库或者特定的数据库，或者特定的表。 主从复制实现原理

1. master服务器将数据的改变记录二进制binlog日志，当master上的数据发生改变时，则将其改变写入二进制日志中。
2. slave服务器会在一定时间间隔内对master二进制日志进行探测其是否发生改变，如果发生改变，则开始一个I/OThread请求master二进制事件。
3. 同时主节点为每个I/O线程启动一个dump线程，用于向其发送二进制事件，并保存至从节点本地的中继日志中，从节点将启动SQL线程从中继日志中读取二进制日志，在本地解析执行，使得其数据和主节点的保持一致，最后I/OThread和SQLThread将进入睡眠状态，等待下一次被唤醒。

基于gtid的复制模式 什么是gitd？ gtid（Global Transaction ID）对于一个已提交事务的编号，并且是一个全局唯一的编号。GTID 实际上 是由UUID+TID组成的。其中UUID是一个mysql实例的唯一标识。TID代表了该实例上已经提交的事务数量，并且随着事务提交单调递增。 gtid的复制原理

1. 当一个事务在主库端执行并提交时，产生gtid，一同记录到binlog日志中。
2. binlog传输到slave,并存储到slave的relaylog后，读取这个gtid的这个值设置gtid_next变量，即告诉Slave，下一个要执行的gtid值。
3. sql线程从relay log中获取gtid，然后对比slave端的binlog是否有该gtid。
4. 如果有记录，说明该gtid的事务已经执行，slave会忽略。
5. 如果没有记录，slave就会执行该gtid事务，并记录该gtid到自身的binlog，在读取执行事务前会先检查其他session持有该gtid，确保不被重复执行。
6. 在解析过程中会判断是否有主键，如果没有就用二级索引，如果没有就用全部扫描。

问：mysql如何保证写入数据不丢失？

总结：保证redo log和bin log可以持久化到磁盘，并且确保mysql在异常重启后进行数据恢复。 bin log的写入机制：

1. 事务执行过程中，先把日志写到binlog cache（内存）
2. 事务提交的时候（mysql客户端执行commit指令），再把bin log cache中写到bin log文件中，并清空bin log cache
3. 每个线程都有自己的一个bin log cache，但是共同使用同一份bin log
4. write把binlog cache写入到文件系统的page cache，不会真正将数据持久化到磁盘。
5. fsync才是将数据持久化到磁盘（此时会占用磁盘的IOPS）

redo log的写入机制：

1. 事务在执行过程中，生成的redo log首先会写到redo log buffer
2. redo log会在一些特定条件下写入日志文件
3. write到磁盘（存储在Page Cache中），此时没有实际调用fsync写入磁盘
4. 红色部分，持久化到磁盘，调用了fsync

问：mysql的MRR是什么？

MRR，全称「Multi-Range Read Optimization」。 官方说法：MRR 通过把「随机磁盘读」，转化为「顺序磁盘读」，从而提高了索引查询的性能。 其实MRR是基于索引的查询做的一个优化，对于innodb，则会按照聚簇索引键值排好序，在内存上索引和磁盘上的索引存储也是有序的，通过顺序的读取聚簇索引。索引本身就是为了减少磁盘 IO，加快查询，而 MRR，则是把索引减少磁盘 IO 的作用，MRR在本质上是一种用空间换时间的算法。

问：讲讲mysql如何分库分表？

分库分表主要解决IO瓶颈，CPU瓶颈。 分库分表：水平分库分表，垂直分库分表等 具体分库分表的方式经验是：日志类的拆分策略是按照日期，另外拆分策略就是hash法。

参考文献

《高性能mysql第三版》

《Mysql技术内幕 Innodb存储引擎》

https://dev.mysql.com/doc/refman/5.6/en/mrr-optimization.html