两万字 MySQL 保姆级面试教程，收藏细看！

点击上方"数据与人", 右上角选择“设为星标”

分享干货，共同成长！

今天给大家分享MySQL常考的面试题，看看你们能答对多少。

目录大纲：

事务的四大特性？

事务特性ACID：原子性（Atomicity
）、一致性（Consistency
）、隔离性（Isolation
）、持久性（Durability
）。具体含义：

• 原子性（atomicity
  )
  一个事务必须被视为一个不可分割的最小工作单元，整个事务中的所有操作要么全部提交成功，要么全部失败回滚，对于一个事务来说，不可能只执行其中的一部分操作。
• 一致性（consistency
  )
  数据库总是从一个一致性的状态转换到另外一个一致性的状态。如：拿转账来说，假设用户A和用户B两者的钱加起来一共是1000，那么不管A和B之间如何转账，转几次账，事务结束后两个用户的钱相加起来应该还得是1000，这就是事务的一致性。
• 隔离性（isolation
  )
  隔离性是当多个用户并发访问数据库时，比如操作同一张表时，数据库为每一个用户开启的事务，不能被其他事务的操作所干扰，多个并发事务之间要相互隔离。
  即要达到这么一种效果：对于任意两个并发的事务T1和T2，在事务T1看来，T2要么在T1开始之前就已经结束，要么在T1结束之后才开始，这样每个事务都感觉不到有其他事务在并发地执行。
• 持久性（durability
  )
  一旦事务提交，则其所做的修改就会永久保存到数据库中。此时即使系统崩溃，修改的数据也不会丢失。持久性是个有点模糊的概念，因为实际上持久性也分很多不同的级别。有些持久性策略能够提供非常强的安全保障，而有些则未必。而且「不可能有能做到100%的持久性保证的策略」否则还需要备份做什么。

事务隔离级别有哪些？

MySQL四种隔离级别是什么，分别解决了什么问题，一张图说清楚：脏读、不可重复读、幻读具体含义：

脏读：在事务A修改数据之后提交数据之前，这时另一个事务B来读取数据，如果不加控制，事务B读取到A修改过数据，之后A又对数据做了修改再提交，则B读到的数据是脏数据，此过程称为脏读Dirty Read
。

不可重复读：一个事务内在读取某些数据后的某个时间，再次读取以前读过的数据，却发现其读出的数据已经发生了变更、或者某些记录已经被删除了。

幻读：事务A在按查询条件读取某个范围的记录时，事务B又在该范围内插入了新的满足条件的记录，当事务A再次按条件查询记录时，会产生新的满足条件的记录（幻行 Phantom Row
）

不可重复读与幻读有什么区别？

• 不可重复读的重点是修改：在同一事务中，同样的条件，第一次读的数据和第二次读的「数据不一样」。（因为中间有其他事务提交了修改）；
• 幻读的重点在于新增或者删除：在同一事务中，同样的条件，第一次和第二次读出来的「记录数不一样」。（因为中间有其他事务提交了插入/删除）。

索引

索引的本质？

MySQL官方对索引的定义为：索引（Index
）是帮助MySQL高效获取数据的数据结构。提取句子主干，就可以得到索引的本质：索引是数据结构。

索引分类？

主键索引：名为primary
的唯一非空索引，不允许有空值。唯一索引：索引列中的值必须是唯一的，但是允许为空值。唯一索引和主键索引的区别是：唯一约束的列可以为null
且可以存在多个null
值。唯一索引的用途：唯一标识数据库表中的每条记录，主要是用来防止数据重复插入。组合索引：在表中的多个字段组合上创建的索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用，使用组合索引时需遵循最左前缀原则。全文索引：只有在MyISAM
引擎上才能使用，只能在CHAR
、VARCHAR
和TEXT
类型字段上使用全文索引。

索引的优缺点？

优点：

• 提高数据检索的效率，降低数据库的IO
  成本；
• 通过索引列对数据进行排序，降低数据排序的成本，降低了CPU
  的消耗；

所以记住，索引功能是：搜索+排序缺点：

• 索引提升查询效率的同时也会降低更新的效率，更新表时，MySQL不仅要保存数据，还要保存一下索引文件，每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息；
• 实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占用空间的。

索引的作用？

数据是存储在磁盘上的，查询数据时，如果没有索引，会加载所有的数据到内存，依次进行检索，读取磁盘次数较多。有了索引，就不需要加载所有数据，因为B+树的高度一般在2-4层，最多只需要读取2-4次磁盘，查询速度大大提升。

索引的使用场景？

1. 主键自动建立唯一索引；
2. 频繁作为查询条件的字段应该创建索引；
3. 查询中与其它表关联的字段，外键关系建立索引；
4. 单键/组合索引的选择问题， 组合索引性价比更高；
5. 查询中排序的字段，如order by
create_time
   ，排序字段若通过索引去访问将大大提高排序速度；
6. 查询中统计或者分组字段；

索引的失效场景？

1. 以%开头的LIKE
   查询不能够利用B+树索引

2. 数据类型中出现隐式转换时不会用到索引

3. 复合索引的情况下，查询条件不满足最左原则不会用到索引

4. 用or分隔的条件，如果or前条件中的列有索引，而后面的列没有索引，那么涉及的索引都不会被用到(前面的索引也不会用到)。

5. 如果Mysql估计使用索引比全表扫描更慢，则不使用索引。

索引的数据结构

MySQL 索引一般是哈希表或 B+ 树，常用的 InnoDB
引擎默认使用的是 B+ 树来作为索引的数据结构。B+树索引B+ 树是基于B 树和叶子节点顺序访问指针进行实现，它具有B树的平衡性，并且通过顺序访问指针来提高区间查询的性能。在 B+ 树中，节点中的 key
 从左到右递增排列，如果某个指针的左右相邻 key
 分别是 keyi 和 keyi+1，则该指针指向节点的所有 key
 大于等于 keyi 且小于等于 keyi+1。进行查找操作时，首先在根节点进行二分查找，找到key
所在的指针，然后递归地在指针所指向的节点进行查找。直到查找到叶子节点，然后在叶子节点上进行二分查找，找出key
所对应的数据项。MySQL 数据库使用最多的索引类型是BTREE
索引，底层基于B+树数据结构来实现。

mysql> show index from blogG;
*************************** 1. row ***************************
Table: blog
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: blog_id
Collation: A
Cardinality: 4
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
Visible: YES
Expression: NULL

哈希索引哈希索引是基于哈希表实现的，对于每一行数据，存储引擎会对索引列进行哈希计算得到哈希码，并且哈希算法要尽量保证不同的列值计算出的哈希码值是不同的，将哈希码的值作为哈希表的key值，将指向数据行的指针作为哈希表的value
值。这样查找一个数据的时间复杂度就是O(1)，一般多用于精确查找。

Hash索引和B+树索引的区别？

• 哈希索引不支持排序，因为哈希表是无序的。
• 哈希索引不支持范围查找。
• 哈希索引不支持模糊查询及多列索引的最左前缀匹配。
• 因为哈希表中会存在哈希冲突，所以哈希索引的性能是不稳定的，而B+树索引的性能是相对稳定的，每次查询都是从根节点到叶子节点。

为什么B+树比B树更适合实现数据库索引？

B+树是B树的变种，是基于B树来改进的。为什么B+树会比B树更加优秀呢？
B树：有序数组+平衡多叉树； 
B+树：有序数组链表+平衡多叉树；
B+树的关键字全部存放在叶子节点中，非叶子节点用来做索引，而叶子节点中有一个指针指向一下个叶子节点。

B+ 树查找过程：

磁盘块 1 中存储 17 和 35 数据项，还有 P1、P2、P3 指针，P1 表示数据项小于 17 的磁盘块，P2 表示数据项在 17 和 35 之间的数据项，P3 表示数据项大于 35 的数据项。非叶子节点不储存数据，只储存指引搜索方向的数据项。
我们知道每次 IO 读取一个数据页的大小，也就是一个磁盘块。
假设我们要查找 29 这个数据项，首先进行第一次 IO 将磁盘块 1 读进内存，发现17 < 29 < 35，然后选用 P2 指针进行第二次 IO 将磁盘块 3 读进内存，发现26 < 29 < 30，然后选用 P2 指针将磁盘块 8 读进内存，在内存中做二分查找，找到 29，结束查询。
通过分析查询过程，我们可以知道 IO 次数和 B+ 树的高度成正比。H 为树的高度，M 为每个磁盘块的数据项个数，N 为数据项总数。
从下面的公式可以看出如果数据量N一定，M越大相应的H越小。

M 等于磁盘块的大小除以数据项大小，由于磁盘块大小一般是固定的，所以减小数据项大小才能使得 M 更大从而让树更矮胖。这也是为什么 B+ 树把真实数据放在叶子节点而不是非叶子节点的原因。
如果真实数据放在非叶子结点，磁盘块存储的数据项会大幅度减少，树就会增高相应查询数据时的 IO 次数就会变多。

什么是最左匹配原则？

如果 SQL 语句中用到了组合索引中的最左边的索引，那么这条 SQL 语句就可以利用这个组合索引去进行匹配。当遇到范围查询(>
、 2 and c = 3
，那么a、b个字两段能用到索引，而c无法使用索引，因为b字段是范围查询，导致后面的字段无法使用索引。如下图，对(a, b) 建立索引，a 在索引树中是全局有序的，而 b 是全局无序，局部有序（当a相等时，会根据b进行排序）当a的值确定的时候，b是有序的。例如a = 1
时，b值为1，2是有序的状态。当执行a = 1 and b = 2
时a和b字段能用到索引。而对于查询条件a < 4 and b = 2
时，a字段能用到索引，b字段则用不到索引。因为a的值此时是一个范围，不是固定的，在这个范围内b的值不是有序的，因此b字段无法使用索引。

什么是聚集索引？

InnoDB使用表的主键构造主键索引树，同时叶子节点中存放的即为整张表的记录数据。聚集索引叶子节点的存储是逻辑上连续的，使用双向链表连接，叶子节点按照主键的顺序排序，因此对于主键的排序查找和范围查找速度比较快。聚集索引的叶子节点就是整张表的行记录。InnoDB
主键使用的是聚簇索引。聚集索引要比非聚集索引查询效率高很多。对于InnoDB
来说，聚集索引一般是表中的主键索引，如果表中没有显示指定主键，则会选择表中的第一个不允许为NULL
的唯一索引。如果没有主键也没有合适的唯一索引，那么InnoDB
内部会生成一个隐藏的主键作为聚集索引，这个隐藏的主键长度为6个字节，它的值会随着数据的插入自增。

什么是覆盖索引？

select
的数据列只用从索引中就能够取得，不需要回表进行二次查询，也就是说查询列要被所使用的索引覆盖。对于innodb
表的二级索引，如果索引能覆盖到查询的列，那么就可以避免对主键索引的二次查询。不是所有类型的索引都可以成为覆盖索引。覆盖索引要存储索引列的值，而哈希索引、全文索引不存储索引列的值，所以MySQL使用b+树索引做覆盖索引。对于使用了覆盖索引的查询，在查询前面使用explain
，输出的extra列会显示为using index
。

什么是前缀索引？

有时需要在很长的字符列上创建索引，这会造成索引特别大且慢。使用前缀索引可以避免这个问题。前缀索引是指对文本或者字符串的前几个字符建立索引，这样索引的长度更短，查询速度更快。创建前缀索引的关键在于选择足够长的前缀以保证较高的索引选择性。索引选择性越高查询效率就越高，因为选择性高的索引可以让MySQL
在查找时过滤掉更多的数据行。建立前缀索引的方式：

// email列创建前缀索引
ALTER TABLE table_name ADD KEY(column_name(prefix_length));

索引的设计原则？

• 索引列的区分度越高，索引的效果越好。比如使用性别这种区分度很低的列作为索引，效果就会很差。
• 尽量使用短索引，对于较长的字符串进行索引时应该指定一个较短的前缀长度，因为较小的索引涉及到的磁盘I/O
  较少，查询速度更快。
• 索引不是越多越好，每个索引都需要额外的物理空间，维护也需要花费时间。
• 利用最左前缀原则。

MySQL架构？

整体架构图：

MySQL的架构不同于其他数据库，它的插件式的存储引擎架构可以在多种不同场景中应用并发挥良好作用。这种架构可以根据业务的需求和实际需要选择合适的存储引擎。
各层介绍：
1.1 连接层
最上层是客户端，包含本地sock
通信和大多数基于客户端/服务端工具实现的类似于tcp/ip
的通信。
1.2 服务层

1.3.引擎层

存储引擎负责MySQL中数据的存储和提取，服务器通过API
与存储引擎进行通信。不同的存储引擎具有的功能不同，这样我们可以根据自己的实际需要进行选取。
show engines
:查看所有的数据库引擎

show variables like '%engine%' 
查看默认的数据库引擎
1.4.存储层
数据存储层，主要是将数据存储在运行于裸设备的文件系统之上，并完成与存储引擎的交互。
2、查询流程
MySQL的查询流程大致是：
客户端通过协议与DB服务器建连接，发送查询语句，先检查查询缓存，如果命中，直接返回结果，否则进行语句解析。
语法解析器和预处理：首先MySQL通过关键字将SQL语句进行解析，并生成一颗对应的“解析树”。解析器将使用语法规则验证和解析查询；预处理器则根据一些规则进一步检查解析数是否合法。
查询优化器当解析树被认为是合法的了，并且由优化器将其转化成执行计划。一条查询可以有很多种执行方式，最后都返回相同的结果。优化器的作用就是找到这其中最好的执行计划。
流程图：
3、SQL的执行顺序一般SQL语法：
SQL解析：
真正执行的顺序：

常见的存储引擎有哪些？

MySQL中常用的四种存储引擎分别是：MyISAM
、InnoDB
、MEMORY
、ARCHIVE
。MySQL 5.5版本后默认的存储引擎为InnoDB
。InnoDB存储引擎InnoDB是MySQL默认的事务型存储引擎，使用最广泛，基于聚簇索引建立的。InnoDB内部做了很多优化，如能够自动在内存中创建自适应hash索引，以加速读操作。优点：支持事务和崩溃修复能力；引入了行级锁和外键约束。缺点：占用的数据空间相对较大。适用场景：需要事务支持，并且有较高的并发读写频率。MyISAM存储引擎数据以紧密格式存储。对于只读数据，或者表比较小、可以容忍修复操作，可以使用MyISAM引擎。MyISAM会将表存储在两个文件中，数据文件.MYD
和索引文件.MYI
。优点：访问速度快。缺点：MyISAM不支持事务和行级锁，不支持崩溃后的安全恢复，也不支持外键。适用场景：对事务完整性没有要求；表的数据都会只读的。MEMORY存储引擎MEMORY引擎将数据全部放在内存中，访问速度较快，但是一旦系统奔溃的话，数据都会丢失。MEMORY引擎默认使用哈希索引，将键的哈希值和指向数据行的指针保存在哈希索引中。优点：访问速度较快。缺点：

1. 哈希索引数据不是按照索引值顺序存储，无法用于排序。
2. 不支持部分索引匹配查找，因为哈希索引是使用索引列的全部内容来计算哈希值的。
3. 只支持等值比较，不支持范围查询。
4. 当出现哈希冲突时，存储引擎需要遍历链表中所有的行指针，逐行进行比较，直到找到符合条件的行。

ARCHIVE存储引擎ARCHIVE存储引擎非常适合存储大量独立的、作为历史记录的数据。ARCHIVE提供了压缩功能，拥有高效的插入速度，但是这种引擎不支持索引，所以查询性能较差。

MyISAM和InnoDB的区别？

MVCC 实现原理？

MVCC(Multiversion concurrency control
) 就是同一份数据保留多版本的一种方式，进而实现并发控制。在查询的时候，通过read view
和版本链找到对应版本的数据。作用：提升并发性能。对于高并发场景，MVCC比行级锁开销更小。MVCC 实现原理如下：MVCC 的实现依赖于版本链，版本链是通过表的三个隐藏字段实现。

• DB_TRX_ID
  ：当前事务id，通过事务id的大小判断事务的时间顺序。
• DB_ROLL_PRT
  ：回滚指针，指向当前行记录的上一个版本，通过这个指针将数据的多个版本连接在一起构成undo log
  版本链。
• DB_ROLL_ID
  ：主键，如果数据表没有主键，InnoDB会自动生成主键。

举例说明：

create table mvcctest( id int primary key auto_increment, name varchar(20));

transaction 1
:

start transaction;insert into mvcctest values(NULL,'mi');insert into mvcctest values(NULL,'kong');commit;

假设系统初始事务ID为1；transaction 2
:

start transaction;select * from mvcctest; (1)select * from mvcctest; (2)commit

SELECT：

假设当执行事务2的过程中，准备执行语句(2)时，开始执行事务3：transaction 3
:

start transaction;insert into mvcctest values(NULL,'qu');commit;

事务3执行完毕，开始执行事务2 语句2，由于事务2只能查询创建时间小于等于2的，所以事务3新增的记录在事务2中是查不出来的，这就通过乐观锁的方式避免了幻读的产生。

UPDATE：

假设当执行事务2的过程中，准备执行语句(2)时，开始执行事务4：transaction 4
:

start transaction;update mvcctest set name = 'fan' where id = 2;commit;

InnoDB执行UPDATE，实际上是新插入了一行记录，并保存其创建时间为当前事务的ID，同时保存当前事务ID到要UPDATE的行的删除时间。事务4执行完毕，开始执行事务2 语句2，由于事务2只能查询创建时间小于等于2的，所以事务修改的记录在事务2中是查不出来的，这样就保证了事务在两次读取时读取到的数据的状态是一致的。

DELETE：

假设当执行事务2的过程中，准备执行语句(2)时，开始执行事务5：transaction 5:

start transaction;delete from mvcctest where id = 2;commit;

事务5执行完毕，开始执行事务2 语句2，由于事务2只能查询创建时间小于等于2、并且过期时间大于等于2，所以id=2的记录在事务2 语句2中，也是可以查出来的,这样就保证了事务在两次读取时读取到的数据的状态是一致的。

快照读和当前读

表记录有两种读取方式。

• 快照读：读取的是快照版本。普通的SELECT
  就是快照读。通过mvcc来进行并发控制的，不用加锁。
• 当前读：读取的是最新版本。UPDATE、DELETE、INSERT、SELECT … LOCK IN SHARE MODE、SELECT … FOR UPDATE
  是当前读。

快照读情况下，InnoDB通过mvcc
机制避免了幻读现象。而mvcc
机制无法避免当前读情况下出现的幻读现象。因为当前读每次读取的都是最新数据，这时如果两次查询中间有其它事务插入数据，就会产生幻读。那么MySQL是如何避免幻读？

• 在快照读情况下，MySQL通过mvcc
  来避免幻读。
• 在当前读情况下，MySQL通过next-key
  来避免幻读（加行锁和间隙锁来实现的）。

next-key包括两部分：行锁和间隙锁。行锁是加在索引上的锁，间隙锁是加在索引之间的。Serializable
隔离级别也可以避免幻读，会锁住整张表，并发性极低，一般不会使用。

共享锁和排他锁

SELECT 的读取锁定主要分为两种方式：共享锁和排他锁。

select * from table where id delete。

having和where的区别？

• 二者作用的对象不同，where
  子句作用于表和视图，having
  作用于组。
• where
  在数据分组前进行过滤，having
  在数据分组后进行过滤。

show processlist详解？

show processlist
 或 show full processlist
 可以查看当前 MySQL 是否有压力，正在运行的SQL
，有没有慢SQL
正在执行。返回参数如下：

1. id：线程ID，可以用kill id
   杀死某个线程
2. db：数据库名称
3. user：数据库用户
4. host：数据库实例的IP
5. command：当前执行的命令，比如Sleep
   ，Query
   ，Connect
   等
6. time：消耗时间，单位秒
7. state：执行状态，主要有以下状态：

• Sleep
  ，线程正在等待客户端发送新的请求
• Locked
  ，线程正在等待锁
• Sending data
  ，正在处理SELECT
  查询的记录，同时把结果发送给客户端
• Kill
  ，正在执行kill
  语句，杀死指定线程
• Connect
  ，一个从节点连上了主节点
• Quit
  ，线程正在退出
• Sorting for group
  ，正在为GROUP BY
  做排序
• Sorting for order
  ，正在为ORDER BY
  做排序

更多精彩内容，关注我们▼▼