Mysql 经典面试题总结

🍓 简介：java系列技术分享(👉持续更新中…🔥)
🍓 初衷:一起学习、一起进步、坚持不懈
🍓 如果文章内容有误与您的想法不一致,欢迎大家在评论区指正🙏
🍓 希望这篇文章对你有所帮助,欢迎点赞 👍 收藏 ⭐留言 📝

🍓 更多文章请点击

一、Mysql的工作流程是什么？

Mysql是关系型数据库，关系型数据库就是由二维表格建立的数据组织和二维表格之间的联系构成的数据关联。

当输入一条 MySQL 语句时，这条语句是如何被执行的？ 需要经过 MySQL 哪些过程？ 并以此了解 MySQL 的组成部分。

图1

图2

图3

从图1或图2看出,MySQL可以分为server层和存储引擎层两部分。

1. server层包括连接器、查询缓存、分析器、优化器、执行器等，涵盖MySQL的大多数核心服务功能,以及所有的内置函数（如时间、日期、数学、加密等），所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图等。
2. 存储引擎层：存储引擎层负责数据的存储和提取。其架构模式是插件式的，支持InnoDB、MyISAM、Memory等多个存储引擎
   现在最常用的存储引擎是InnoDB，它从Mysql5.5.5版本成为了默认的存储引擎。

连接器：连接器负责跟客户端建立连接、获取权限、维持和管理连接。

查询缓存：连接建立完成后，你就可以执行select语句了，此时会先进行查询缓存(缓存是key-value格式；key是sql语句，value 是sql语句的查询结果)。

分析器：

1. 词法分析： MySQL需要识别出里面的字符串分别是什么，代表什么。
2. 语法分析：根据词法分析的结果，语法分析器会根据语法规则，判断你输入的这个SQL语句是否满足MySQL语法。

优化器：优化器是在表里面有多个索引的时候，决定使用哪个索引；或者在一个语句有多表关联（join）的时候，决定各个表的连接顺序。

执行器：调用存储引擎接口，执行sql语句，得到结果

总结:　首先客户端发送查询命令给MYSQL服务器，服务器会先检查缓存，如果缓存命中，则返回缓存中的数据，否则服务器进行SQL解析、预处理，再通过优化器生成执行计划。服务器根据执行计划，调用对应引擎的API来执行查询，最后再将结果返回给客户端。

二、数据库引擎有哪些？

MySQL提供了插件式的存储引擎架构。所以MySQL存在多种存储引擎，可以根据需要使用相应引擎，或
者编写存储引擎。存储引擎是基于表的，而不是基于库的。所以存储引擎也可被称为表类型。MySQL5.0支持的存储引擎包
含 ： InnoDB 、MyISAM 、BDB、MEMORY、MERGE、EXAMPLE、NDB Cluster、ARCHIVE、CSV、BLACKHOLE、
FEDERATED等，其中InnoDB和BDB提供事务安全表，其他存储引擎是非事务安全表。

三、InnoDB与MyISAM的区别？

1. InnoDB支持事务，MyISAM不支持，对于InnoDB每一条SQL语言都默认封装成事务，自动提交，这样会影响速度，所以最好把多条SQL语言放在begin和commit之间，组成一个事务；
2. InnoDB支持外键，而MyISAM不支持。
3. InnoDB是支持表锁和行级锁，MyISAM只支持表锁

四、如何选择存储引擎？

如果没有特别的需求，使用默认的 Innodb 即可。

MyISAM：以读为主的应用程序，比如博客系统、新闻门户网站。

Innodb：更新（删除）操作频率也高，或者要保证数据的完整性；并发量高，支持事务和外键。比如OA自动化办公系统。

五、存储引擎常用命令？

5.1 查看MySQL提供的所有存储引擎

show engines;   

创建新表时如果不指定存储引擎，那么系统就会使用默认的存储引擎，MySQL5.5之前的默认存储引擎是MyISAM，5.5之后就改为了InnoDB。

5.2 查看mysql默认的存储引擎

show variables like '%storage_engine%'; 

六、索引是什么？

MySQL官方对索引的定义为：索引（index）是帮助MySQL高效获取数据的数据结构（有序）。

数据库索引就是一种加快海量数据查询的关键技术。

索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里所有记录的引用指针。

索引是一种数据结构。数据库索引，是数据库管理系统中一个排序的数据结构，以协助快速查询、更新数据库表中数据。索引的实现通常使用B树及其变种B+树。更通俗的说，索引就相当于目录。为了方便查找书中的内容，通过对内容建立索引形成目录。而且索引是一个文件，它是要占据物理空间的。

MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。比如我们在查字典的时候，前面都有检索的拼音和偏旁、笔画等，然后找到对应字典页码，这样然后就打开字典的页数就可以知道我们要搜索的某一个key的全部值的信息了。

七、为什么 MySQL索引要用 B+tree?

我们要了解【B+树】，首先要了解一下【B树】，这里的 B 表示 balance( 平衡的意思)，B-树是一种【多路自平衡的搜索树】

7.1 B树

B树中允许一个结点中包含多个key，可以是3个、4个、5个甚至更多，并不确定，需要看具体的实现。现在我们选择一个参数M，来构造一个B树，我们可以把它称作是M阶的B树，那么该树会具有如下特点：

1. 所有键值分布在整颗树中；
2. 任何一个关键字出现且只出现在一个结点中；
3. 搜索有可能在非叶子结点结束；

举例M为4阶的 B树演示:

• 每个结点最多有M-1个key，并且以升序排列；
• 每个结点最多能有M个子结点；
• 根结点至少有两个子结点；
  
  

在实际应用中B树的阶数一般都比较大（通常大于100），所以，即使存储大量的数据，B树的高度仍然比较小，这样在某些应用场景下，就可以体现出它的优势。

7.2 B+树

B+树是对B树的一种变形树，它与B树的差异在于：

1. 非叶结点仅具有索引作用，也就是说，非叶子结点只存储key，不存储value；
2. 树的所有叶结点构成一个有序链表，可以按照key排序的次序遍历全部数据。
3. 为所有叶子结点增加了一个双向指针

7.3 磁盘IO原理

磁盘用磁头来读写存储在盘片表面的位，而磁头连接到一个移动臂上，移动臂沿着盘片半径前后移动，可以将磁头定位到任何磁道上，这称之为寻道操作。一旦定位到磁道后，盘片转动，磁道上的每个位经过磁头时，读写磁头就可以感知到该位的值，也可以修改值。对磁盘的访问时间分为 寻道时间，旋转时间，以及传送时间。

由于存储介质的特性，磁盘本身存取就比主存慢很多，再加上机械运动耗费，因此为了提高效率，要尽量减少磁盘I/O，减少读写操作。 为了达到这个目的，磁盘往往不是严格按需读取，而是每次都会预读，即使只需要一个字节，磁盘也会从这个位置开始，顺序向后读取一定长度的数据放入内存。这样做的理论依据是计算机科学中著名的局部性原理：当一个数据被用到时，其附近的数据也通常会马上被使用。由于磁盘顺序读取的效率很高（不需要寻道时间，只需很少的旋转时间），因此预读可以提高I/O效率。

页是计算机管理存储器的逻辑块，硬件及操作系统往往将主存和磁盘存储区分割为连续的大小相等的块，每个存储块称为一页（1024个字节或其整数倍），预读的长度一般为页的整倍数。主存和磁盘以页为单位交换数据。当程序要读取的数据不在主存中时，会触发一个缺页异常，此时系统会向磁盘发出读盘信号，磁盘会找到数据的起始位置并向后连续读取一页或几页载入内存中，然后异常返回，程序继续运行。

文件系统的设计者利用了磁盘预读原理，将一个结点的大小设为等于一个页（1024个字节或其整数倍），这样每个结点只需要一次I/O就可以完全载入。那么3层的B树可以容纳1024\*1024\*1024差不多10亿个数据，如果换成二叉查找树，则需要30层！假定操作系统一次读取一个节点，并且根节点保留在内存中，那么B树在10亿个数据中查找目标值，只需要小于3次硬盘读取就可以找到目标值，但红黑树需要小于30次，因此B树大大提高了IO的操作效率。

7.4 B+树和B树的对比

B+ 树的优点在于：

1. 由于B+树在非叶子结点上不包含真正的数据，只当做索引使用，因此在内存相同的情况下，能够存放更多的key。
2. B+树的叶子结点都是相连的，因此对整棵树的遍历只需要一次线性遍历叶子结点即可。而且由于数据顺序排列并且相连，所以便于区间查找和搜索。而B树则需要进行每一层的递归遍历。

B树的优点在于：

1. 由于B树的每一个节点都包含key和value，因此我们根据key查找value时，只需要找到key所在的位置，就能找到value，但B+树只有叶子结点存储数据，索引每一次查找，都必须一次一次，一直找到树的最大深度处，也就是叶子结点的深度，才能找到value。

7.5 为什么选择B+树？

预读其实就是利用了局部性原理，具体过程是：对于每个文件的第一个读请求，系统读入所请求的页面并读入紧随其后的少数几个页面（通常是三个页面），这时的预读称为同步预读。 我们知道对大部分操作系统来说，磁盘的一页数据是4kb,那么算上预读的3页
4kb(磁盘一页的大小) + 12kb(预读三个页面) = 16kb

这个16kb是innodb默认的页大小，为什么会有这个概念呢，因为当涉及到数据库读写的时候，规定数据库每次读写都是以16k为单位的，一次最少从磁盘中读取16KB的内容到内存中，一次最少把内存中的16KB内容刷新到磁盘中。

1. 我们知道innodb使用【页】保存数据，因此大小为16kb,B+树中的每个节点都是一页数据。B+树非叶子结点只存储key，不存储value,因此在内存相同的情况下，B+树能够存放更多的key
2. B+树能使每个节点能检索的【范围更大、更精确，极大的减少了I/O操作，保证b+树的层高较低，通常3到4层的层高就能支持百万级别的访问】。
3. Mysql是一种关系型数据库，【区间访问】是很常见的一种情况，由于B+树数据顺序排列并且相连，B+树叶节点增加的双向指针，加强了区间访问性，可使用在范围区间查询的情况。

八、索引的分类

8.1 聚簇索引和非聚簇索引

【主键和数据】共存的索引被称之为【聚簇索引】，其他的，比如我们使用【姓名列+主键】建立的索引，可以称为【非聚簇索引】，或者【辅助索引】，或者【二级索引】，同时聚簇索引只有在innodb引擎中才存在，而在myIsam中是不存在的
聚簇索引【默认使用主键】，如果表中没有定义主键，InnoDB 会选择一个【唯一且非空】的列代替。如果没有这样的列，InnoDB 会隐式定义一个主键【类似oracle中的RowId】rowid来作为聚簇索引的列。

8.2 主键索引 （primary key）

• 主键是一行数据的唯一标识,要求非空值 且 唯一
• 主键列不允许为空值
• 主键可以被其他表引用为外键
• 一个表最多只能创建一个主键

8.3 唯一索引（UNIQUE ）

对列的要求：索引列的值不能重复,表示唯一的，不允许重复的索引，如果该字段信息保证不会重复例如身份证号用作索引时，可设置为UNIQUE。

• 唯一索引列允许空值
• 可以创建多个唯一索引

8.4 普通索引 （常规索引）(normal)

就是普普通通的索引，没有什么特殊要求，理论上任何列都可以当做普通索引,纯粹为了查询数据更快一点

8.5 复合索引（联合索引）

当【查询语句】中包含【多个查询条件，且查询的顺序基本保持一致】时，我们推荐使用复合索引，索引的【组合使用】效率是低于【复合索引】的。
比如：我们经常按照A列、B列、C列进行查询时，通常的做法是建立一个由三个列共同组成的【复合索引】而不是对每一个列建立【普通索引】。

8.6 全文索引（FULLTEXT）

• 使用 like + % 实现的模糊匹配有点类似全文索引。但是对于大量的文本数据检索，全文索引比 like + % 快 N 倍，速度不是一个数量级，但是全文索引可能存在【精度问题】。同时普通索引在使用like时如果%放在首位，索引会失效。
• 但是这种场景，我们有更好的替代品，如：ElacticSearch，所以实际使用不多，只当了解。

8.7 hash索引

hash索引是Memory存储引擎的默认方式，而且只有memory引擎支持hash索引

• hash是一种key-value形式的数据结构。实现一般是数组+链表的结构，通过hash函数计算出key在数组中的位置，然后如果出现hash冲突就通过链表来解决。当然还有其他的解决hash冲突的方法。hash这种数据结构是很常用的，比如我们系统使用HashMap来构建热点数据缓存，存取效率很好。
• 即使是相近的key，hash的取值也完全没有规律，索引hash索引不支持范围查询。
• hash索引存储的是hash值和行指针，所以通过hash索引查询数据需要进行两次查询（首先查询行的位置，然后找到具体的数据）。
• hash索引查询数据的前提就是计算hash值，也就是要求key为一个能准确指向一条数据的key，所以对于like等一类的匹配查询是不支持的。
• 只需要做等值比较查询，而不包含排序或范围查询的需求，都适合使用哈希索引。

8.8 空间索引（SPATIAL）

MySQL在5.7之后的版本支持了空间索引，而且支持OpenGIS几何数据模型。这是在地理位置领域使用的一种索引，其他场景用的很少

九、常见的索引约束有哪些？

约束名称	描述	关键字
非空约束	保证列中所有数据不能有null值	NOT NULL
唯一约束	保证列中所有数据各不相同	UNIQUE
主键约束	主键是一行数据的唯一标识,非空且唯一	PRIMARY KEY
检查约束	保证列中的值满足某一条件	CHECK
默认约束	保存数据时,未指定值采用默认值	DEFAULT
外键约束	连个表的数据建立连接,保证数据一致性和完整性	FOREIGN KEY
自增长约束	每增加一条数据,主键都会自增	AUTO_INCREMENT
零填充约束	插入数据时,字段值长度小于定义长度,会在前面补0	ZEROFILL

MYSQL不支持检查约束

十、怎么看到为表格定义的所有索引？

show index from table_name;

十一、唯一索引比普通索引快吗, 为什么？

唯一索引不一定比普通索引快, 还可能慢。
1、查询时, 在未使用 limit 1 的情况下, 在匹配到一条数据后, 唯一索引即返回, 普通索引会继续匹配下一条数据, 发现不匹配后返回. 如此看来唯一索引少了一次匹配, 但实际上这个消耗微乎其微。
2、更新时, 这个情况就比较复杂了. 普通索引将记录放到 change buffer 中语句就执行完毕了。而对唯一索引而言, 它必须要校验唯一性, 因此, 必须将数据页读入内存确定没有冲突, 然后才能继续操作。对于写多读少的情况 , 普通索引利用 change buffer 有效减少了对磁盘的访问次数, 因此普通索引性能要高于唯一索引.

十二、索引的优缺点？

1. 优点

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

2. 缺点

• 虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行 INSERT、UPDATE 和DELETE。因为更新表时，MySQL 不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息。
• 实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占用空间的。

十三、索引失效？

1. 违背了最左前缀法则
   • 如果索引了多列【复合索引】，要遵守最左前缀法则。指的是查询从索引的最左前列开始，并且不跳过索引中的列。违反了最左前缀法则 ， 索引失效
2. 范围查询右边的列，不能使用索引 。
3. 在索引列上进行运算操作,索引失效
4. 字符串不加单引号,索引失效
5. 避免 select *
6. 用 or 分隔开的条件
   • 每个条件都带有索引则索引生效,如果有一个条件没有索引,则整个索引全部失效
     
7. 以 % 开头的like模糊查询,索引失效
8. in 和 not in
   • 普通索引 in: (使用索引) , not in (不使用索引)
   • 主键索引 in 或 not in (都使用索引)
9. 如果MYSQL评估使用索引比全表更慢,则不使用索引,-----(由数据本身特点决定)

十四、在建立索引的时候,都有哪些需要考虑的因素呢？

1. 建立索引的时候一般要考虑到字段的使用频率,经常作为条件进行查询的字段比较适合。
2. 如果需要建立联合索引的话,还需要考虑联合索引中的顺序。
3. 此外也要考虑其他方面,比如防止过多的索引对表造成太大的压力

十五、那些情况下适合建索引?

1. 频繁作为where条件语句查询的字段
2. 排序字段可以建立索引
3. 分组字段可以建立索引
4. 统计字段可以建立索引 例如:count() , max( ) ,min()

十六、那些情况下不适合建索引?

1. 频繁更新的字段不适合建立索引
2. where条件中用不到的字段不适合建立索引
3. 表数据确定比较少不需要建立索引
4. 数据重复且比较均匀的字段不适合建立索引(唯一性太差的字段不适合),例如:性别,真假等等
5. 参加列计算的列不适合建立索引,索引会失效

十七、如何优化SQL？

1. SQL语句中IN包含的值不应过多,数值较多，产生的消耗也是比较大的
2. select id from table_name where num in(1,2,3) 对于连续的数值，能用between 就不要用in了。
3. SELECT语句务必指明字段名称
4. 如果排序字段没有用到索引，就尽量少排序
5. 如果限制条件中其他字段没有索引，尽量少用or
6. 不建议使用%前缀模糊查询

十八、MySQL事务？

举一个例子：我们有如下的销售业务，一个销售业务可能包含很多步骤，比如记录订单、添加积分、管理库存、扣减金额等等，每一个操作都可能对应一条或多条sql语句，但是这个业务却是不可分割的，不能下了订单，不扣减库存。此时我们就需要事务来统一管理这个业务当中的一系列sql语句了。

• 在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。

• 事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行。

• 由多个操作组成的一个逻辑单元，组成这个逻辑单元的多个操作要么都成功，要么都失败。

十九、ACID是什么？

• A=Atomicity原子性：
  一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。如果事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
• C=Consistency一致性：
  在事务【开始之前和结束以后】，数据库的完整性没有被破坏，数据库状态应该与业务规则保持一致。举一个例子：A向B转账，不可能A扣了钱，B却没有收到，也不可能A和B的总金额，在事务前后发生变化，产生数据不一致。其他的三个特性都在为他服务。
• I=Isolation隔离性:
  数据库【允许多个并发事务同时对其数据进行读取和修改】，隔离性可以防止多个事务在并发修改共享数据时产生【数据不一致】的现象，这里要联想到我们学习过的多线程。事务隔离级别分为不同等级，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable
• D=Durability持久性：
  事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失

二十、并发事务带来哪些问题？

在典型的应用程序中，多个事务并发运行，经常会操作相同的数据来完成各自的任务（多个用户对同一数据进行操作）。并发虽然是必须的，但可能会导致
以下的问题。

• 脏读（Dirty read）: 当一个事务正在访问数据并且对数据进行了修改，而这种修改还没有提交到数据库中，这时另外一个事务也访问了这个数据，然后使
  用了这个数据。因为这个数据是还没有提交的数据，那么另外一个事务读到的这个数据是“脏数据”，依据“脏数据”所做的操作可能是不正确的。

• 不可重复读（Unrepeatableread）：指在一个事务内多次读同一数据。在这个事务还没有结束时，另一个事务也访问该数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改导致第一个事务两次读取的数据可能不太一样。这就发生了在一个事务内两次读到的数据是不一样的情况，因此称为不可重复读。

• 幻读（Phantom read）：幻读与不可重复读类似。它发生在一个事务（T1）读取了几行数据，接着另一个并发事务（T2）插入了一些数据时。在随后的查询中，第一个事务（T1）就会发现多了一些原本不存在的记录，就好像发生了幻觉一样，所以称为幻读。

二十一、怎么解决上述问题呢？MySQL的默认隔离级别是？

• 读未提交(RU)：
  说的简单一点就是，一个事务可以读取其他【未提交的事务】修改的数据，这种隔离级别最低，一般情况下，数据库隔离级别都要高于该级别，该隔离级别下，可能会存在脏读、不可重复度，幻读的问题。
  • 脏读：指的是一个事务读到了其他事务未提交的数据，未提交意味着这些数据可能会回滚，读到的数据不一定准确。
• 读已提交（RC）
  • 说的是当前事务只能读到别的事物已经提交的数据，该隔离级别可能会产生不可重复读和幻读。【不可重复读】的官方解释是：【一个事务】（A事务）修改了【另一个未提交事务】（B事务）读取过的数据。那么B事务【再次读取】，会发现两次读取的数据不一致。也就是说在一个原子性的操作中一个事务两次读取相同的数据，却不一致，一行数据不能重复被读取。主要是【update】语句，会导致不可重复读。
• 可重复读（RR）
  • 同一个事务中发出同一个SELECT语句【两次或更多次】，那么产生的结果数据集总是相同的，在RR隔离级别中可能出现幻读。
  • 幻读：一个事务按照某些条件进行查询，事务提交前，有另一个事务插入了满足条件的其他数据，再次使用相同条件查询，却发现多了一些数据，就像出现了幻觉一样。幻读主要针对针对delete和insert语句。
  • 不可重复读强调的是两次读取的数据【内容不同】，幻读前调的是两次读取的【行数不同】。
• 串行化
  • 事务A和事务B，事务A在操作数据库时，事务B只能排队等待
  • 这种隔离级别很少使用，吞吐量太低，用户体验差
  • 这种级别可以避免“幻读”，每一次读取的都是数据库中真实存在数据，事务A与事务B串行，而不并发。

二十二、MySQL中有哪几种锁？

从对数据操作的粒度分 ：

1. 表锁：操作时，会锁定整个表。
2. 行锁：操作时，会锁定当前操作行。
3. 页面锁：会锁定一部分的数据

从对数据操作的类型分：

1. 读锁（共享锁）：针对同一份数据，多个读操作可以同时进行而不会互相影响。
2. 写锁（排它锁）：当前操作没有完成之前，它会阻断其他写锁和读锁。
   结论：就是读锁会阻塞写，但是不会阻塞读。而写锁，则既会阻塞读，又会阻塞写。

二十三、InnoDB 情况下什么时候使用行锁？

如果按照索引列进行检索加的就是行级锁，如果没有按照索引进行检索加的就是表级锁。

二十四、如何查看MySQL当前版本号？

1. 在linux系统命令行下：mysql -V
2. 连接上MySQL命令行输入: select version()