MySQL面试题全集,面试必看

最新推荐文章于 2024-08-26 22:47:44 发布

我叫毕加索

最新推荐文章于 2024-08-26 22:47:44 发布

阅读量503

点赞数

分类专栏： MySQL 文章标签： java jvm servlet

本文链接：https://blog.csdn.net/jing5481/article/details/126248731

版权

MySQL 专栏收录该内容

9 篇文章 0 订阅

订阅专栏

活动地址：CSDN21天学习挑战赛

这篇文章源自于对秋招面试点的复习，在阿里数据大佬的提点下，总结MySQL的面试知识点主要有以下几点，以后要是想到还会不定时更新，其中知识大部分来自于书本、视频和优秀博客的总结，还有一些图不愿意自己做也是直接复制的。如果有什么错误和补充也希望大家能多多的指出来。

1 事务

参考博文：https://www.cnblogs.com/kismetv/p/10331633.html

事务：事务是访问和更新数据库的程序执行的一个逻辑单元；事务中可能包含一个或多个sql语句，这些语句要么都执行，要么都不执行。作为一个关系型数据库，MySQL支持事务。

事务的特性（ACID）：

原子性(Automicity)：即整个事务是最小的一个执行单元，不可再分。事务的操作要么完成，要么都不做；如果事务中一个sql语句执行失败，则已执行的语句也必须回滚，数据库退回到事务前的状态。
一致性(Consistency)：即事务执行前后的状态变化是一致的。
隔离性(Isolation)：隔离性是指事务内部的操作与其他事务是隔离的，并发执行的各个事务之间不能互相干扰。
持久性(Durability)：即事务执行完之后，结果是持久的，哪怕发生宕机，结果仍然是事务完成之后的状态
读操作可能会存在的问题：ACID事务特性，能够很好地保证单个事务的数据准确性。但是，在并发的情况下，多个事务共同操作一个数据库时，可能会产生脏读、不可重复读、幻读问题

脏读：当前事务(A)中可以读到其他事务(B)未提交的数据（脏数据），这种现象是脏读。
不可重复读：在事务A中先后两次读取同一个数据，两次读取的结果不一样，这种现象称为不可重复读。脏读与不可重复读的区别在于：前者读到的是其他事务未提交的数据，后者读到的是其他事务已提交的数据。
幻读：在事务A中按照某个条件先后两次查询数据库，两次查询结果的条数不同，这种现象称为幻读。不可重复读与幻读的区别可以通俗的理解为：前者是数据变了，后者是数据的行数变了。
事务的隔离级别：克服产生脏读、幻读等问题的方法是提高事务的隔离级别。SQL标准中定义了四种隔离级别，一般来说，隔离级别越低，开销越低，可支持并发性越高，但隔离性越差。隔离级别与会产生的问题如下：

读未提交隔离级别最低、可支持并发度最高，无法克服脏读、不可重复读和幻读任何一种问题；读已提交可以克服脏读现象
可重复读克服脏读和不可重复读；可串行化（序列化）可以克服全部三种问题，但是可串行化强制事务串行，并发效率很低，只有当对数据一致性要求极高且可以接受没有并发时使用，因此使用也较少。因此在大多数数据库系统中，默认的隔离级别是读已提交(RC:ORACLE,SQLSERVER)或可重复读（RR:MYSQL）

现在互联网工程一般默认选择RC,主要原因有：

1 在RR隔离级别下，存在间隙锁，导致出现死锁的几率比RC大的多！
此时执行语句“select * from test where id <3 for update” ，在RR隔离级别下，存在间隙锁，可以锁住(2,5)这个间隙，防止其他事务插入数据！而在RC隔离级别下，不存在间隙锁，其他事务是可以插入数据！

2 在RR隔离级别下，条件列未命中索引会锁表！而在RC隔离级别下，只锁行。

在RC隔离级别下，其先走聚簇索引，进行全部扫描加锁，但是MySQL做了优化，在MySQL Server过滤条件，发现不满足后，会调用unlock_row方法，把不满足条件的记录放锁。而在RR隔离级别下，会直接把整张表加锁。

MVCC（Multi-Version Concurrency Control，即多版本的并发控制协议）:在Mysql中，通过MVCC来利用RR解决脏读、不可重复读、幻读等问题。

数据的读取可以分为两种：快照读和当前读。快照读适用于简单的select语句，当前读是基于临键锁（行锁 + 间歇锁）来实现的，适用于 insert，update，delete， select ... for update， select ... lock in share mode 语句，以及加锁了的 select 语句

mysql默认是快照读
出现(delete、update、insert)时改为当前读，最新的数据也会被读取。
第一步：mysql会为每一条数据，隐式加上两个字段，一个是创建版本号赋值，另一个是删除版本号赋值。在快照读的状态下，表的数据发生变化即会制作成一个新的版本。select时读取数据的规则为：创建版本号<=当前事务版本号，删除版本号为空或>当前事务版本号。通过MVCC机制，虽然让数据变得可重复读，但我们读到的数据可能是历史数据，不是数据库最新的数据。这种读取历史数据的方式，我们叫它快照读 (snapshot read)，而读取数据库最新版本数据的方式，叫当前读 (current read)。

第二步：locks由record locks(行锁、索引加锁) 和 gap locks(间隙锁），读取数据的附近记录也加锁，保证能够读取到最新数据，附近数据不被修改

2 B树和B+树

参考博文：程序员小灰有关B树和B+树的讲解

这里之所以提到B树和B+树，是因为mysql里面最常用的索引就是B+树，有时候面试会问到为什么使用B+树，B+树有什么优势。

B数和B+树简单来说是一种多路搜索树。

B树：一个m阶的B树具有如下几个特征：

根结点至少有两个子女。
每个中间节点都包含k-1个元素和k个孩子，其中 m/2 <= k <= m
每一个叶子节点都包含k-1个元素，其中 m/2 <= k <= m
所有的叶子结点都位于同一层。
每个节点中的元素从小到大排列，节点当中k-1个元素正好是k个孩子包含的元素的值域分划。
如图，是一个三阶的B树

B树的关键字分布在整棵树中，任何关键字出现且仅出现一次在一个节点中，其查找复杂度相当于一个二分查找

B+树：B+树是B树的一种变体，有着比B树更好的查询性能。对于一个B+树，除了B树的特点之外，还有如下特点：

有k个子树的中间节点包含有k个元素（B树中是k-1个元素），每个元素不保存数据，只用来索引，所有数据都保存在叶子节点（这就是所谓的卫星数据。卫星数据就是指节点的具体信息）。
所有的叶子结点中包含了全部元素的信息，及指向含这些元素记录的指针，且叶子结点本身依关键字的大小自小而大顺序链接。
每一个父节点都出现在子节点中，是子节点元素中是最大（或最小）元素。
如图，一个三阶B+树

B+树的优势：

B+树的中间节点没有卫星数据，所以同样大小的磁盘页可以容纳更多的节点元素，使得查询的IO次数更少。
所有查询都要查找到叶子节点，查询性能稳定。
所有叶子节点形成有序链表，范围查询只需在链表上做遍历即可，便于范围查询。
hash索引和B+树索引区别：Mysql里面一共有四种索引，经常会问的索引除了B+数之外，还有hash索引（hash索引就是采用一定的hash算法建立索引，键值对）

如果是等值查询，那么哈希索引明显有绝对优势，只需要经过一次算法即可找到相应的键值；
如果是范围查询检索，原先是有序的键值，经过哈希算法后，有可能变成不连续的了，不能利用索引完成范围查询检索；
哈希索引也没办法利用索引完成排序，以及like ‘xxx%’ 这样的部分模糊查询
哈希索引也不支持复合索引的最左匹配规则；
B+树索引的关键字检索效率比较平均，不像B树那样波动幅度大，在有大量重复键值情况下，因为哈希碰撞问题，哈希索引的效率也会变低。
3 建立索引

索引：索引是帮助Mysql高效获取数据的数据结构。常用的索引有主键索引、普通索引、唯一索引和全文索引。

主键索引（PRIMARY ）：唯一且不能为空，在一个表里面，主键索引也就是这个表的主键。
普通索引（INDEX）：普通的索引。
唯一索引（UNIQUE）：唯一索引是普通索引的特殊情况，索引不允许有重复，主键索引就是一种唯一索引。
全文索引（FULLTEXT ）：用于在一篇文章中，检索文本信息的。
什么情况建立索引：

适合创建索引条件

主键自动建立主键索引
频繁作为查询条件的字段应该建立索引
查询中与其他表关联的字段，外键关系建立索引
单键/组合索引的选择问题，组合索引性价比更高
查询中排序的字段，排序字段若通过索引去访问将大大提高排序效率
查询中统计或者分组字段
不适合创建索引条件

数据量不大的
经常增删改的表或者字段
where条件里用不到的字段不创建索引
数据存在大量重复的
优势：提高数据检索的效率，降低数据库IO成本

劣势：索引也需要维护

4 组合索引（多列索引）

组合索引：MySQL能在多个列上创建索引。一个索引可以由最多15个列组成，组合索引的性价比相对来说更高。

最左原则：组合索引是先按照第一列进行排序，然后在第一列排好序的基础上再对第二列排序，如果跳过第一列直接访问第二列，直接访问后面的列就用不到索引了。例如，组合索引（a，b，c），一般都是先匹配a，然后匹配b，最后匹配c。

适用场景：

全字段匹配
匹配部分最左前缀
匹配第一列范围查询(可用用like a%,但不能使用like %b，最左原则)
精确匹配某一列和和范围匹配另外一列
索引失效的几种情况：

使用like '% '进行查询模糊查询
组合索引不符合最左匹配原则
使用了or关键字
where之后的使用了函数
mysql内部有一个优化器，在进行查询的时候，会把使用普通索引、主键索引、全表扫描的消耗都计算出来选择最优的方法，在某些情况下，全表扫描的性能更优就会出现索引失效。
5 聚簇索引和非聚簇索引（针对B+树索引）

无论是聚簇索引还是非聚簇索引，都不是一种单独的数据结构，而是一种数据存储方式。

聚簇索引：聚簇索引的叶子节点就是数据节点。在InnoDB引擎就是聚簇索引，聚簇索引默认是主键（如果表中没有定义主键，InnoDB会选择一个唯一的非空索引代替，也可以自己设置聚簇索引），一张表内只能有一个聚簇索引，在聚簇索引之上创建的索引称之为辅助索引，辅助索引访问数据需要二次查找。辅助索引叶子节点存储的不再是行的物理位置，而是主键值。通过辅助索引首先找到的是主键值，再通过主键值找到数据。

非聚簇索引：非聚簇索引（主要是为了区别聚簇索引，MyISAM引擎用的就是非聚簇索引）的叶子节点仍然是索引节点，只不过有指向对应数据块的指针。

优点：聚簇索引数据访问更快，因为聚簇索引将索引和数据保存在同一个B+树中；

缺点：聚簇索引更新代价特别高。

6 数据库引擎（主要就是MyISAM和InnoDB的区别）

区别

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

2. InnoDB支持外键，而MyISAM不支持。对一个包含外键的InnoDB表转为MYISAM会失败；

3. InnoDB是聚集索引，使用B+Tree作为索引结构，数据文件是和（主键）索引绑在一起的（表数据文件本身就是按B+Tree组织的一个索引结构），必须要有主键，通过主键索引效率很高。但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据。因此，主键不应该过大，因为主键太大，其他索引也都会很大。

MyISAM是非聚集索引，也是使用B+Tree作为索引结构，索引和数据文件是分离的，索引保存的是数据文件的指针。主键索引和辅助索引是独立的。

也就是说：InnoDB的B+树主键索引的叶子节点就是数据文件，辅助索引的叶子节点是主键的值；而MyISAM的B+树主键索引和辅助索引的叶子节点都是数据文件的地址指针。（具体参考上面的聚簇索引和非聚簇索引）

4. InnoDB不保存表的具体行数，执行select count(*) from table时需要全表扫描。而MyISAM用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快（注意不能加有任何WHERE条件）；

那么为什么InnoDB没有了这个变量呢？

因为InnoDB的事务特性，在同一时刻表中的行数对于不同的事务而言是不一样的，因此count统计会计算对于当前事务而言可以统计到的行数，而不是将总行数储存起来方便快速查询。InnoDB会尝试遍历一个尽可能小的索引除非优化器提示使用别的索引。如果二级索引不存在，InnoDB还会尝试去遍历其他聚簇索引。
如果索引并没有完全处于InnoDB维护的缓冲区（Buffer Pool）中，count操作会比较费时。可以建立一个记录总行数的表并让你的程序在INSERT/DELETE时更新对应的数据。和上面提到的问题一样，如果此时存在多个事务的话这种方案也不太好用。如果得到大致的行数值已经足够满足需求可以尝试SHOW TABLE STATUS

5. Innodb不支持全文索引，而MyISAM支持全文索引，在涉及全文索引领域的查询效率上MyISAM速度更快高；PS：5.7以后的InnoDB支持全文索引了

6. MyISAM表格可以被压缩后进行查询操作

7. InnoDB支持表、行(默认)级锁，而MyISAM支持表级锁

InnoDB的行锁是实现在索引上的，而不是锁在物理行记录上。潜台词是，如果访问没有命中索引，也无法使用行锁，将要退化为表锁。

t_user(uid, uname, age, sex) innodb;

uid PK

无其他索引

update t_user set age=10 where uid=1; 命中索引，行锁。

update t_user set age=10 where uid != 1; 未命中索引，表锁。

update t_user set age=10 where name='chackca'; 无索引，表锁。

8、InnoDB表必须有唯一索引（如主键）（用户没有指定的话会自己找/生产一个隐藏列Row_id来充当默认主键），而Myisam可以没有

9、Innodb存储文件有frm、ibd，而Myisam是frm、MYD、MYI

Innodb：frm是表定义文件，ibd是数据文件

Myisam：frm是表定义文件，myd是数据文件，myi是索引文件

如何选择：
1. 是否要支持事务，如果要请选择innodb，如果不需要可以考虑MyISAM；

2. 如果表中绝大多数都只是读查询，可以考虑MyISAM，如果既有读也有写，请使用InnoDB。

3. 系统奔溃后，MyISAM恢复起来更困难，能否接受；

4. MySQL5.5版本开始Innodb已经成为Mysql的默认引擎(之前是MyISAM)，说明其优势是有目共睹的，如果你不知道用什么，那就用InnoDB，至少不会差。

InnoDB为什么推荐使用自增ID作为主键？

答：自增ID可以保证每次插入时B+索引是从右边扩展的，可以避免B+树和频繁合并和分裂（对比使用UUID）。如果使用字符串主键和随机主键，会使得数据随机插入，效率比较差。

7 主从复制（读写分离、数据备份）

概念： mysql主从分离其实也就是读写分离，将读操作和协操作分别导入到不同的服务器集群；

原理：主从分离是如何工作的

在主从分离里面有主服务器（master）和从服务器（slaver），如图，其工作步骤主要分为三步：

首先主服务器（master）将对数据的操作都记录到二进制日志（binary log）中。
从服务器（slaver）将binary log拷贝到其中继日志（Relay log）中
slaver重做Relay log里面的事件，更新slaver里面的数据与master达到数据一致。
binary log有三种形式，分别是：

statement:记录的是修改SQL语句
row：记录的是每行实际数据的变更
mixed：statement和row模式的混合
Mysql在5.0这个版本以前，binlog只支持STATEMENT这种格式！而这种格式在读已提交(Read Commited)这个隔离级别下主从复制是有bug的，因此Mysql将可重复读(Repeatable Read)作为默认的隔离级别！

7 分库、分表、分区

8 SQL优化：

在说mysql优化之前，首先谈一谈explain关键字，如果面试的时候说mysql优化提到了explain关键字可能会给面试官你是真的做过mysql优化的感觉。

explain：explain被称为执行计划，如果在select语句前放上关键词explain，mysql将解释它如何处理select，提供有关表如何联接和联接的次序。

EXPLAIN SELECT * FROM tb_area,tb_shop WHERE tb_area.area_id=tb_shop.area_id
在select语句前面加 EXPLAIN 关键字，会出现如下

1. id相同时，执行顺序由上至下

2. 如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行

3.id如果相同，可以认为是一组，从上往下顺序执行；在所有组中，id值越大，优先级越高，越先执行

select_type 查询中每个select子句的类型

(1) SIMPLE(简单SELECT,不使用UNION或子查询等)

(2) PRIMARY(查询中若包含任何复杂的子部分,最外层的select被标记为PRIMARY)

(3) UNION(UNION中的第二个或后面的SELECT语句)

(4) DEPENDENT UNION(UNION中的第二个或后面的SELECT语句，取决于外面的查询)

(5) UNION RESULT(UNION的结果)

(6) SUBQUERY(子查询中的第一个SELECT)

(7) DEPENDENT SUBQUERY(子查询中的第一个SELECT，取决于外面的查询)

(8) DERIVED(派生表的SELECT, FROM子句的子查询)

(9) UNCACHEABLE SUBQUERY(一个子查询的结果不能被缓存，必须重新评估外链接的第一行)

Table:输出行所引用的表

表示MySQL在表中找到所需行的方式，又称“访问类型”。

常用的类型有： ALL, index, range, ref, eq_ref, const, system, NULL（从左到右，性能从差到好）

ALL：Full Table Scan， MySQL将遍历全表以找到匹配的行

index: Full Index Scan，index与ALL区别为index类型只遍历索引树

range:只检索给定范围的行，使用一个索引来选择行

ref: 表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值

eq_ref: 类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，就是多表连接中使用primary key或者 unique key作为关联条件

const、system: 当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。如将主键置于where列表中，MySQL就能将该查询转换为一个常量system是const类型的特例，当查询的表只有一行的情况下，使用system

NULL: MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成。

possible_keys : 指出能在该表中使用哪些索引有助于查询。如果为空,说明没有可用的索引。

key:实际从 possible_key 选择使用的索引。如果为 NULL,则没有使用索引。很少的情况下,MYSQL 会选择优化不足的索引。这种情况下,可以在 SELECT 语句中使用 USE INDEX (indexname)来强制使用一个索引或者用IGNORE INDEX(indexname)来强制 MYSQL 忽略索引

key_len: 使用的索引的长度。在不损失精确性的情况下,长度越短越好。

ref: 显示索引的哪一列被使用了

rows: 认为必须检查的用来返回请求数据的行数

extra中出现以下 2 项意味着根本不能使用索引,效率会受到重大影响。应尽可能对此进行优化。

我们可以利用EXPLAIN关键字来分析一个SELECT语句的执行情况。

总的来说，SQL优化的原则有三点：1，尽量避免放弃索引而导致全表扫描；2 避免使用select *返回多余数据；3 合理建立索引

优化方式如下：

在表中建立索引，优先考虑where、group by使用到的字段。

尽量避免使用select *，返回无用的字段会降低查询效率。如下：SELECT * FROM t

优化方式：使用具体的字段代替*，只返回使用到的字段。

尽量避免使用in 和not in，会导致数据库引擎放弃索引进行全表扫描。如下：

SELECT * FROM t WHERE id IN (2,3)