十道MySQL必问面试题

已于 2024-08-31 18:54:54 修改
阅读量970 收藏 9
点赞数 30
文章标签： mysql java 后端
于 2024-08-31 18:53:42 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/u011919808/article/details/141756702
版权
  1. 你是如何理解最左前缀原则的？
  2. 你是如何理解行锁、GAP锁、临键锁的？
  3. 你是如何理解MVCC的？
  4. 你是如何理解count(*)和count(1)的？
  5. 你是如何理解Online DDL的？
  6. 你知道哪些情况下会导致索引失效？
  7. 你是如何理解filesort的？
  8. 你知道哪些情况下会锁表吗?
  9. 你是如何理解MySQL里面的死锁机制的？
  10. 你是如何优化慢查询的？

更多面试题在公众号：IT周瑜

你是如何理解最左前缀原则的？

创建一个表：

CREATE TABLE t1 (
  id int primary key auto_increment,
  a int,
  b int,
  c int,
  d int
);

创建了一个abc联合索引：

alter table t1 add index idx_abc(a, b, c);

执行：

explain select * from t1 where b=1 and c=1 and a=1;

以上SQL能走abc联合索引吗？注意a=1在where条件的最右边哦。

答案是可以的：
image.png

把a=1放到中间：

explain select * from t1 where b=1 and a=1 and c=1;

一样是可以的：
image.png

那把a=1放到最左边就肯定更没问题了

explain select * from t1 where a=1 and b=1 and c=1;

image.png

从这可以看出，不管a=1放在哪个位置，以上SQL都能走索引，那如果把a=1去掉呢？

explain select * from t1 where b=1 and c=1;

此时就不会走abc联合索引了，而是全表扫描：
image.png

从这可以看出，所谓的最左前缀原则中的“最左”，并不是指where条件中a=1一定要在最左边，而是指where条件中一定要给出定义联合索引时的最左边字段，比如我们定义abc联合索引的SQL为：

alter table t1 add index idx_abc(a, b, c);

其中a字段是最左边的字段，因此如果想要走idx_abc索引，那么SQL中就一定要给出a字段的条件，这才是“最左”的意思。

你是如何理解行锁、GAP锁、临键锁的？

假如有以下表：

CREATE TABLE t1 (
  id int primary key,
  name varchar(10)
);

假如表中有两行记录：
image.png
如果我现在执行以下SQL，想要更新id=3的这一行记录：

update t1 set name='dadudu' where id = 3;

此时当前事务会给id=3这一行加一个行锁，其他事务如果也想要更新这一行也需要给这一行加锁，而且两个事务加的都是X锁，也就是排他锁，两个排他锁是不兼容的，所以其他事务只能等待持有锁的事务提交后将锁释放掉才能获取到锁，从而更新数据，这也可以理解为悲观锁。

所谓行锁其实在InnoDB源码中是通过一个lock_t和一个hash表来实现的，这里就不展开分析了，想在面试的时候装B的同学可以联系我，我单独给你分析分析。

行锁锁的是某一行，而GAP锁锁的是行前面的间隙，注意只是行前面的间隙，你可能会问那表的最后一行前后都有间隙啊，最后一行后面的间隙不锁吗？

当然会所了，只不过是交给了一个叫做PAGE_NEW_SUPREMUM的记录来说的，你可以理解为PAGE_NEW_SUPREMUM记录是InnoDB默认的，它固定作为最后一条记录，因此只要锁住PAGE_NEW_SUPREMUM前面的间隙，就相当了锁住了我们所理解的最后一行后面的间隙

而临键锁，也就是next-key锁，它既锁行又锁行前面的间隙，而且我们经常遇到的是行锁或临键锁。

比如上面的SQL:

update t1 set name='dadudu' where id = 3;

由于是根据id进行的更新，id是主键，是唯一的，这种情况就只需要对id=3这条记录加行锁就可以了，并不需要加next-key锁，也就是并不需要锁id=3前面的间隙，因为id=3前面的间隙对应的记录一定不是id=3，所以不用锁间隙。

而如果是下面这个SQL：

update t1 set name='dadudu' where name = 'zhouyu';

就需要锁全部的间隙了，因为全部的间隙都有可能插入name='zhouyu’的记录，或者可以理解为会给所有记录都加临键锁，这样就锁住了所有的间隙。

你是如何理解MVCC的？

所谓MVCC就是多版本并发控制，MySQL为了实现可重复读这个隔离级别，而且为了不采用锁机制来实现可重复读，所以采用MVCC机制来实现。

当一个事务修改一条记录时，会将记录的历史版本也记录下来，从而一条记录会对应一条版本链，而且每次修改时都会将当前的事务ID保存下来，所以一条记录的多个版本可能对应了不同的事务ID，而且在InnoDB中，事务ID是自增的，是有顺序的。

因此，当一个事务在查询某一条记录时，会根据自己的事务ID以及这条记录的版本链，来判断当前事务可见的是哪个版本，从而读出这个版本对应的记录，从而实现了可重复读。

MVCC中还有一个概念就是ReadView，在一个事务首次执行查询时，会创建一个ReadView，而ReadView中会记录当前整个InnoDB中有哪些活跃的事务ID，最大的事务ID，最小的事务ID，然后根据这些信息来判断某条记录的某个版本是否可见，比如：

  1. 如果某个版本的事务ID，比ReadView中最小的事务ID，还要小，那么此版本可见，因为表示该版本的事务是在本事务之前就已经提交了
  2. 如果某个版本的事务ID，属于ReadView中活跃事务，那么此版本不可见，因为表示该版本的事务并不是本事务之前就已经提交了
  3. 如果某个版本的事务ID，比ReadView中最大的事务ID，还要大，那么此版本不可见，因为表示该版本的事务是本事务之后开启的新事务

如果某个版本对当前ReadView不可见，那就继续取上一个版本，直到对当前ReadView可见。

你是如何理解count(*)和count(1)的？

这两个并没有区别，不要觉得count()会查出全部字段，而count(1)不会，所以count()会更慢，你觉得MySQL作者会这么做吗？

我看了MySQL源码，也写了对应了文章麻烦不要再问我count(*)、count(1)、count(id)、count(name)之间的区别了

可以很明确的告诉你们count()和count(1)是一样的，而正确有区别的是count(字段)，如果你count()的是具体的字段，那么MySQL会判断某行记录中对应字段是否为null，如果为null就不会进行统计了，因此count(字段)的结果可能会小于count()和count(1)。

另外，直接执行select (*) from t1;也是可以利用到索引的，并不一定是全表扫描，也可以扫描某个索引B+树的叶子节点，从而得到总条数，因为不管是什么索引，主键索引还是辅助索引，实际上它们对应的B+树中的叶子节点中的数据条数是一样的，只不过字段数不一样，主键索引存了全部字段，而辅助索引只存了定义的索引字段+主键字段，所以通常辅助索引是要比主键索引小的，因此遍历起来也会更快，但是记录条数是一样的。

你是如何理解Online DDL的？

一般DDL操作，比如新增字段，会有三个步骤：

  • 第一，新建一个临时表，表的字段是原始表字段和新增字段
  • 第二，将原始表中的数据复制到临时表中
  • 第三，删除原始表，并将临时表重命名为原始表的表名

Online DDL表示在复制数据的过程中允许其他数据库连接读取数据、删除数据、修改数据、新增数据，但并不是所有的DDL操作都支持Online，那些需要修改现有表数据的DDL就不支持，比如新增带有默认值的字段，所谓Online，其实对应的是Inplace算法，在执行DDL时，MySQL会自动判断应该用Copy算法还是Inplace算法，能用Inplace算法的DDL就是Online DDL，MySQL8中，新增了一种效率更高的DDL算法，叫做Instant，算法效率更高，但支持的DDL场景更加有限。

你知道哪些情况下会导致索引失效？

最常见的就是对索引列做了运算、函数操作、类型转换了，比如:

CREATE TABLE t1 (
  id int primary key,
  name varchar(10),
  age varchar(2)
);

insert into t1(id, name, age) values(3, 'zhouyu', '18');
insert into t1(id, name, age) values(6, 'zhangfei', '19');

alter table t1 add index idx_name(name);
alter table t1 add index idx_age(age);

以下SQL是能走索引的：

explain select * from t1 where name = 'zhou';

image.png

但是如果改成：

explain select * from t1 where substring(name, 1, 4) = 'zhou';

image.png
就变成了全表扫描。

还有就是一种常见的索引失效就是对索引列进行类型转换，在MySQL中会将字符串转成数字，所以如果是以下SQL能走索引：

explain select * from t1 where age = '18';

image.png

但是如果是：

explain select * from t1 where age = 18;

image.png
就变成了全表扫描，因为age字段的类型是varchar，而给的值是数字，MySQL需要把age字段的所有内容都转成数字之后再进行where条件的过滤。

你是如何理解filesort的？

首先，order by、group by都有可能触发filesort，因为它们都需要对数据按指定字段进行排序，当然group by也可以不排序，只不过它默认会进行排序。

如果order by指定的字段能走索引，那就不会触发filesort，因为索引本身已经有顺序了，按索引上的顺序直接返回结果就可以了。

如果order by指定的字段不能走索引，此时就会出现filesort，但是不一定代表一定会用到文件排序，实际上MySQL中的order by的大体实现思路是这样的：

具体可参考我写的另外一篇文章假如你是MySQL作者，你会如何实现order by?

你知道哪些情况下会锁表吗?

  1. 表的元数据锁，比如在执行一些DDL操作时，比如给表增加字段，这其实就是在修改表的元数据，因此会给表加元数据锁，如果此时有其他DDL操作也来加元数据锁，那么就会阻塞，此时就出现了锁表
  2. 自增锁，如果一个表的主键是自增id，那么每次新增一条记录时，就需要先获取自增锁，然后得到最新的主键id，然后释放自增锁，然后插入新记录，在这个过程中也可能会短暂的锁表，因为自增锁也是表级别的锁
  3. 还有一种就是表中的所有行和间隙都被锁了，就像前面提到的update语句，也相当于出现了锁表现象

你是如何理解MySQL里面的死锁机制的？

所谓死锁就是：

  1. 事务A对记录1加锁
  2. 事务B对记录2加锁
  3. 事务A想对记录2加锁，阻塞
  4. 事务B想对记录1加锁，阻塞

两个事务都需要等待对方释放锁才能往下执行，此时就出现了死锁，而出现死锁的原因就是两个事务的加锁顺序不一样，如果都是先对记录1加锁，再对记录2加锁，就不会出现死锁现象了。

不过在MySQL中有死锁检查机制，在加锁时会自动判断当前是否出现了死锁，比如上面的第4步，MySQL一旦发现了死锁就会直接报错，从而导致事务B回滚并释放所有锁，从而导致事务A能正常执行下去，最终不会形成死锁。

image.png

另外需要注意，如果加锁请求比较多，死锁检查机制可能会比较消耗性能，此时可以选择关闭死锁检查机制，参数为innodb_deadlock_detect

你是如何优化慢查询的？

  1. 首先肯定是检查走索引的情况，特别是join查询、字查询、排序字段是否能走索引，如果没有走加上索引基本能解决问题
  2. 如果走了索引，则看是否能减少查询，让SQL不会表走索引覆盖或索引下推的逻辑
  3. 如果是join，或者某些子查询会自动优化为join的，如果MySQL所在机器内存够，可以增加join buffer的大小
  4. 如果是order by慢，那么也可以调整sort buffer的大小
  5. 如果还是慢，那就看是否是返回的数据确实太多了，是不是可以改成分页，这就需要修改应用层的代码了
  6. 如果还是慢，那肯定就是表中的数据确实太多了，那就看是不是能把一些数据进行归档，减少当前表的数据量
  7. 如果还是慢，那就要考虑分库分表，或者一些分布式数据库
IT周瑜
关注
mysql必备面试题，mysql必问的面试题
07-08
mysql必备面试题 请解释MySQL是什么，它的用途是什么？ 描述一下MySQL的主要特性和优点。 解释一下MySQL中的ACID和表的关系是什么？ 描述一下MySQL中的索引是什么，它的作用是什么？ 解释一下MySQL中的SQL和NoSQL有...
MySQL最常问的10道面试题（2023详解版）
weixin_46781683的博客
06-09 1万+
1.什么是聚集索引和非聚集索引简单来说，聚集索引就是基于主键创建的索引，除了主键索引以外的其他索引，称为非聚集索引，也叫做二级索引。由于在InnoDB引擎里面，一张表的数据对应的物理文件本身就是按照B+树来组织的一种索引结构，而聚集索引就是按照每张表的主键来构建一颗B+树，然后叶子节点里面存储了这个表的每一行数据记录。所以基于InnoDB这样的特性，聚集索引并不仅仅是一种索引类型，还代表着一种数据的存储方式。
MySQL 精选 60 道面试题（含答案）
热门推荐
hahazz233的博客
06-20 13万+
基础关系型数据库的优点非关系型数据库（NOSQL）的优点Server 层按顺序执行 SQL 的步骤为：根本原因扩展三种常见的索引底层数据结构：分别是哈希表、有序数组和搜索树。根据叶子节点的内容，索引类型分为主键索引和非主键索引。两个考虑因素：为什么选择 B+ 树：覆盖索引：索引下推：- 反过来，假设一个业务的更新模式是写入之后马上会做查询，那么即使满足了条件，将更新先记录在 change buffer，但之后由于马上要访问这个数据页，会立即触发 merge 过程。这样随机访问 IO 的次数不会减少，反而增加
【MySQL面试题（66道）】
独上高楼，望尽天涯路。
08-20 1万+
MySQL数据库基础知识问答，包含内外连接区别、表删除的区别、SQL语句执行流程等；数据库架构设计，采用哪些存储引擎、有何区别，MySQL日志有哪些、有何作用，SQL优化方式，索引的分类、适用场景、采用的数据结构、B+树有何优点，数据库锁有哪些、行锁的实现，事务的特性、如何保证ACID、隔离级别、脏读、幻读等，数据库读写分离的实现、主从复制原理、分表方法等，百万级大数据的删除与添加解决方法。
面试必问！10 道经典 MySQL 面试题
qq_39173779的博客
03-13 1898
如果有两个事务，运行在相同的时间内，执行 相同的功能，事务的隔离性将确保每一事务在系统中认为只有该事务在使用系统。当对某一表进行诸如UPDATE、INSERT、DELETE这些操作时，SQLSERVER就会自动执行触发器所定义的SQL语句，从而确保对数据的处理必须符合这些SQL语句所定义的规则。视图创建后，可以使用与表相同的方式利用它们。触发器与存储过程非常相似，触发器也是SQL语句集，两者唯一的区别是触发器不能用EXECUTE语句调用，而是在用户执行Transact-SQL语句时自动触发(激活)执行。
mysql面试常见问题和答案
liulanba的博客
01-14 7386
每当行被更改时，时间戳字段将获取当前时间戳。列设置为AUTO INCREMENT时，如果在表中达到最大值，会发生什么情况？它会停止递增，任何进一步的插入都将产生错误，因为密钥已被使用。怎样才能找出最后一次插入时分配了哪个自动增量？LAST_INSERT_ID将返回由Auto_increment分配的最后一个值，并且不需要指定表名称。CONCAT(A, B) – 连接两个字符串值以创建单个字符串输出。通常用于将两个或多个字段合并为一个字段。
MySQL面试题（面试必问）
07-25
MySQL 面试题（面试必问） MySQL 面试题是 MySQL 面试中常见的问题，涵盖了 MySQL 的慢查询、索引、执行计划、聚簇索引和非聚簇索引、回表查询、覆盖索引、索引创建原则、索引失效等知识点。 1. MySQL 中如何定位...
面试常问必备之MySQL面试55题.zip
06-30
本压缩包文件"面试常问必备之MySQL面试55题.zip"包含了55个常见的MySQL面试问题，这些问题涵盖了MySQL的基础知识、查询优化、事务处理、存储引擎、索引策略等多个方面。以下是对这些关键知识点的详细解释： 1. **...
mysql面试题 20道题 2023
08-18
MySQL 面试题 20 道题 2023 MySQL 是一种关系型数据库管理系统，广泛应用于 Web 应用程序和企业环境中。本文将从 MySQL 面试题的角度，总结和解释 MySQL 的一些重要概念和知识点。 首先，让我们从 MySQL 的索引...
2023最新MySQL100道面试题-附答案解析
03-30
MySQL 是最流行的关系型数据库管理系统之一，在 WEB 应用方面，MySQL是最好的 RDBMS (Relational Database Management System，关系数据库管理系统) 应用软件之一。在Java企业级开发中非常常用，因为 MySQL 是开源...
mysql面试题（最全）
QRLYLETITBE的博客
02-18 1万+
整理的最全的 mysql 面试题
115道MySQL面试题(含答案)，从简单到深入！
最新发布
m0_67912929的博客
05-31 4862
数据库事务是一个作为单个逻辑工作单元执行的一系列操作。事务具有ACID属性，即原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）和持久性（Durability）。这意味着事务内的操作要么全部成功，要么全部失败，保持数据完整性，并且独立于其他事务运行。索引是数据库对象，可以提高数据检索的速度。它类似于书的目录，使数据库能够快速定位并检索数据，而不必扫描整个表。索引尤其在处理大量数据时显著提高查询性能。视图是基于SQL语句的结果集的可视化表现。
MYSQL常见面试题汇总
一步一个脚印，踏实努力，向着目标前进
07-28 3823
INNER JOIN只返回两个表中匹配的行，而LEFT JOIN返回左表中的所有行和匹配的右表中的行。如何使用INNER JOIN和LEFT JOIN区别连接多个表？其中，offset表示偏移量，count表示每页的数量。如何使用UNION操作合并多个查询结果？
115 道 MySQL 面试题，从简单到深入！
码农小郑有话说的博客
01-03 2万+
最新115道MySQL面试题，会持续更新
MySQL数据库60道面试题 ( 附答案 )
软件测试技术资源分享官
04-14 2308
2023的4月招聘季如此卷，没点真本事真技术，没点面试经验，不了解点职场套路，如何过五关斩六将？找工作最重要的一步自然是面试，马上跳槽季，网上出现了各种面试题，一时会让人眼花缭乱，分不清最该看哪个。虽然不提倡背答案，但是该看的面试题还是得看，这里给大家总结了一些MySQL的通用面试题，不管面哪个行业都能派上用场，持续更新。1、关系型和非关系型数据库的区别？2、详细说一下一条 MySQL 语句执行的步骤3、MySQL 使用索引的原因？4、索引的三种常见底层数据结构以及优缺点。
15道常见的MySQL面试题及解析
2201_75869073的博客
07-08 8375
通过本文，我们回顾了面试中常见的15道MySQL查询语句面试题，并提供了每个问题的简洁解答。在 MySQL 的面试中，了解常见的问题和解决方法是非常重要的。本文将介绍15道常见的MySQL面试题，并提供详细的解析和答案。以如下形式显示：课程 ID，课程 name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列。
MySQL 面试常问的问题及答案
五六碗瓶
12-06 688
是的，MySQL 可以使用命令行界面 (CLI) 进行管理，通过命令行界面，用户可以输入 MySQL 命令并执行查询和管理操作。是的，MySQL 5.7 及以上版本支持 JSON 数据格式，用户可以将 JSON 数据存储在 MySQL 数据库中。是的，MySQL 5.1 及以上版本支持分区表格。是的，MySQL 支持全局临时表格，用户可以使用全局临时表格来在不同的 MySQL 连接之间共享数据。是的，MySQL 支持嵌套查询，用户可以在一个 SELECT 语句中使用另一个 SELECT 语句。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值