mysql面试题超详细（2024最新）_mysql 面试

1、请你谈谈 MySQL 事务隔离级别，MySQL 的默认隔离级别是什么？
为了达到事务的四大特性，数据库定义了 4 种不同的事务隔离级别：

READ-UNCOMMITTED（读取未提交）：最低的隔离级别，允许脏读，也就是可能读取 到其他会话中未提交事务修改的数据，可能会导致脏读、幻读或不可重复读。
READ-COMMITTED（读取已提交）： 只能读取到已经提交的数据。Oracle 等多数数 据库默认都是该级别 （不重复读），可以阻止脏读，但是幻读或不可重复读仍有可能发 生。
REPEATABLE-READ（可重复读）：对同一字段的多次读取结果都是一致的，除非数据 是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。
SERIALIZABLE（可串行化）：最高的隔离级别，完全服从 ACID 的隔离级别。所有的 事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏 读、不可重复读以及幻读。
MySQL 默认采用的 REPEATABLE_READ 隔离级别。

2、对 SQL 慢查询会考虑哪些优化 ？
分析语句，是否加载了不必要的字段/数据。
分析 SQL 执行计划（expl索引信息）。
如果 SQL 很复杂，优化 SQL 结构。
按照可能的优化点执行表结构变更、增加索引、SQL 改写等操作。
查看优化后的执行时间和执行计划。
如果表数据量太大，考虑分表。
利用缓存，减少查询次数

3、MySQL 为什么 InnoDB 是默认引擎？
聚簇索引是指数据库表行中数据的物理顺序与键值的逻辑（索引）顺序相同。一个表只能有一 个聚簇索引，因为一个表的物理顺序只有一种情况，所以，对应的聚簇索引只能有一个。聚簇 索引的叶子节点就是数据节点，既存储索引值，又在叶子节点存储行数据。 Innodb 创建表后生成的文件有： frm:创建表的语句 idb:表里面的数据+索引文件

4、MySQL 索引底层结构为什么使用 B+树？
B+树的所有叶节点可以通过指针相互连接，能够减 少顺序遍历时产生的额外随机 I/O；
B 树一个节点里存的是数据，而 B+树存储的是索引（地址），所以 B 树里一个节 点存不了很多个数据，但是 B+树一个节点能存很多索引，B+树叶子节点存所有的数据。
B+树叶子节点是一个双向链表的数据结构，便于范围查找。

5、索引失效的情况有哪些？
like 以%开头索引无效，当 like 以&结尾，索引有效。
or 语句前后没有同时使用索引，当且仅当 or 语句查询条件的前后列均为索引时，索引生效。
组合索引，使用的不是第一列索引时候，索引失效，即最左匹配规则。
数据类型出现隐式转换，如 varchar 不加单引号的时候可能会自动转换为 int 类型，这 个时候索引失效。
在索引列上使用 IS NULL 或者 IS NOT NULL 时候，索引失效，因为索引是不索引空值的。
在索引字段上使用，NOT、 <>、！= 、时候是不会使用索引的，对于这样的处理只会进行全表扫描。
对索引字段进行计算操作，函数操作时不会使用索引。
当全表扫描速度比索引速度快的时候不会使用索引。

6、MySQL 事务的特性有什么，说一下分别是什么意思？
原子性：即不可分割性，事务要么全部被执行，要么就全部不被执行。
一致性或可串性：事务的执行使得数据库从一种正确状态转换成另一种正确状态。
隔离性：在事务正确提交之前，不允许把该事务对数据的任何改变提供给任何其他事务。
持久性：事务正确提交后，其结果将永久保存在数据库中，即使在事务提交后有了其他故障，事务的处理结果也会得到保存。

7、介绍下 MySQL 聚簇索引与非聚簇索引的区别（InnoDB 与 Myisam 引 擎）？
聚簇索引：数据和索引放在一块
非聚簇索引： 数据和索引分开存储，索引结构的叶子节点指向数据的对应行
聚簇索引是指数据库表行中数据的物理顺序与键值的逻辑（索引）顺序相同。一个表只能有一 个聚簇索引，因为一个表的物理顺序只有一种情况，所以，对应的聚簇索引只能有一个。 聚簇索引的叶子节点就是数据节点，既存储索引值，又在叶子节点存储行数据。 Innodb 创建表后生成的文件有： frm:创建表的语句 idb:表里面的数据+索引文件 非聚集索引（MyISAM 引擎的底层实现）的逻辑顺序与磁盘上行的物理存储顺序不同。
非聚簇索引的叶子节点仍然是索引节点，只不过有指向对应数据块的指针。索引命中后，需要回表查 询。 Myisam 创建表后生成的文件有： frm:创建表的语句 MYD:表里面的数据文件（myisam data） MYI:表里面的索引文件（myisam index） innodb 的次索引指向对主键的引用 (聚簇索引) myisam 的次索引和主索引都指向物理行 (非聚簇索引)

8、谈谈你对SQL注入式攻击的理解？
所谓 SQL 注入式攻击，就是攻击者把 SQL 命令插入到 Web 表单的输入域或页面请求的查询字符串，欺骗服务器执行恶意的SQL命令。 如何防范 SQL 注入式攻击？ 在利用表单输入的内容构造 SQL 命令之前，把所有输入内容过滤一番就可以了。过滤输入内 容可以按多种方式进行。
对于动态构造 SQL 查询的场合 a. 替换单引号，即把所有单独出现的单引号改成两个单引号，防止攻击者修改 SQL 命令的含义。 b. 删除用户输入内容中的所有连字符 c. 对于用来执行查询的数据库帐户，限制其权限。用不同的用户帐户执行查询、插入、更新、 删除操作。
用存储过程来执行所有的查询。
限制表单或查询字符串输入的长度。
检查用户输入的合法性。
将用户登录名称、密码等数据加密保存。
检查提取数据的查询所返回的记录数量。

9、简单描述 MySQL 中，索引，主键，唯一索引，联合索引的区别，对数据库的性能有什么影响（从读写两方面）？
索引是一种特殊的文件(InnoDB 数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里所有记录的引用指针。
普通索引(由关键字 KEY 或 INDEX 定义的索引)的唯一任务是加快对数据的访问速度。
普通索引允许被索引的数据列包含重复的值。如果能确定某个数据列将只包含彼此各不相同的值，在为这个数据列创建索引的时候就应该用关键字UNIQUE 把它定义为一个唯一 索引。也就是说，唯一索引可以保证数据记录的唯一性。
主键，是一种特殊的唯一索引，在一张表中只能定义一个主键索引，主键用于唯一标识一 条记录，使用关键字 PRIMARY KEY 来创建。
索引可以覆盖多个数据列，如像 INDEX(columnA, columnB)索引，这就是联合索引。
索引可以极大的提高数据的查询速度，但是会降低插入、删除、更新表的速度，因为在执行这些写操作时，还要操作索引文件。

10、limit 1000000 加载很慢的话，你是怎么解决的呢？
方案一：如果 id 是连续的，可以这样，返回上次查询的最大记录(偏移量)，再往下 limit select id，name from employee where id>1000000 limit 10.
方案二：在业务允许的情况下限制页数： 建议跟业务讨论，有没有必要查这么后的分页啦，因为绝大多数用户都不会往后翻太多页。
方案三：order by + 索引（id 为索引） select id，name from employee order by id limit 1000000，10
方案四：利用延迟关联或者子查询优化超多分页场景。（先快速定位需要获取的 id 段，然后 再关联） SELECT a.* FROM employee a, (select id from employee where 条件 LIMIT 1000000,10 ) b where a.id=b.id

11、MySQL 中有哪几种锁？
表级锁：【是对整张表进行加锁】开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。
行级锁：【是对数据表中的每一行进行加锁】开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。
页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般

12、谈谈sql优化的经验
查询语句，where左侧的条件查询字段不要使用函数或者表达式；
使用Explain语句分析sql可能优化的地方，进行优化
select查询语句只需要使用一条记录时，limit 1;
不要直接select*，而应该使用具体需要查询的字段；
避免在where子句中对字段进行NULL;
为搜索字段创建索引；
使用 LIKE%abc%不会走索引，而使用 LIKE abc%会走索引;
字段设计尽可能使用NOT NULL;
选择合适的字段类型，选择标准是尽可能小，尽可能定长，尽可能使用整数；
对于枚举类型的字段，尽量使用enum,不要使用varchar

13、百万级别或以上的数据如何删除
删除数据的速度和创建索引的数量成正比
先删除索引；
再删除无用的数据；
最后删除完成后，重新创建索引

14、数据库死锁
数据库死锁是指多个事务并发操作同一份数据时，由于事务之间的相互竞争和依赖关系等原因而导致的一种阻塞状态。在这种状态下，各个事务被阻塞，无法继续执行，也无法释放锁。

当两个或多个事务同时请求同一个资源时，如果它们的请求顺序不一致，就有可能出现死锁。例如，事务 A 先申请了资源 X，并等待资源 Y，而事务 B 先申请了资源 Y，并等待资源 X，这样就会产生死锁。

为了避免数据库死锁，可以采取以下措施：
1.尽量缩短事务的执行时间，减少事务之间的并发冲突。
2.降低事务隔离级别，减少事务对同一资源的竞争。
3.合理设计数据库表结构、索引和查询语句，尽可能降低死锁的风险。
4.使用行级锁或乐观锁，避免全表扫描和锁定。
5.对于必须使用悲观锁的场景，可以先获取较小的锁再逐渐升级到更高级别的锁，避免直接获取最高级别的锁导致死锁。
6.在数据库中设置超时时间，当事务执行时间过长时，可主动回滚事务并释放锁。
7.数据库死锁对数据一致性和性能都有很大的影响，需要开发者在设计和实现中多加考虑和优化

15、mysql的执行流程

16、MyISAM 和 InnoDB 实现 B 树索引方式的区别是什么？
InnoDB 存储引擎：B+ 树索引的叶子节点保存数据本身，其数据文件本身就是索引文件。

MyISAM 存储引擎：B+ 树索引的叶子节点保存数据的物理地址，叶节点的 data 域存放的是数据记录的地址，索引文件和数据文件是分离的。

17、InnoDB 为什么设计 B+ 树索引？
两个考虑因素：

InnoDB 需要执行的场景和功能需要在特定查询上拥有较强的性能。

CPU 将磁盘上的数据加载到内存中需要花费大量时间。

为什么选择 B+ 树：

哈希索引虽然能提供O（1）复杂度查询，但对范围查询和排序却无法很好的支持，最终会导致全表扫描。

B 树能够在非叶子节点存储数据，但会导致在查询连续数据可能带来更多的随机 IO。

而 B+ 树的所有叶节点可以通过指针来相互连接，减少顺序遍历带来的随机 IO。

普通索引还是唯一索引？

由于唯一索引用不上 change buffer 的优化机制，因此如果业务可以接受，从性能角度出发建议你优先考虑非唯一索引。

18、原本可以执行得很快的 SQL 语句，执行速度却比预期的慢很多，原因是什么？如何解决？
原因：从大到小可分为四种情况

MySQL 数据库本身被堵住了，比如：系统或网络资源不够。

SQL 语句被堵住了，比如：表锁，行锁等，导致存储引擎不执行对应的 SQL 语句。

确实是索引使用不当，没有走索引。

表中数据的特点导致的，走了索引，但回表次数庞大。

解决：

考虑采用 force index 强行选择一个索引

考虑修改语句，引导 MySQL 使用我们期望的索引。比如把“order by b limit 1” 改成 “order by b,a limit 1” ，语义的逻辑是相同的。

第三种方法是，在有些场景下，可以新建一个更合适的索引，来提供给优化器做选择，或删掉误用的索引。

如果确定是索引根本没必要，可以考虑删除索引。

19、MySQL 是如何保证数据不丢失的？
只要redolog 和 binlog 保证持久化磁盘就能确保MySQL异常重启后回复数据

在恢复数据时，redolog 状态为 commit 则说明 binlog 也成功，直接恢复数据；如果 redolog 是 prepare，则需要查询对应的 binlog事务是否成功，决定是回滚还是执行。

20、B树和B+树的区别
1.B树只适合随机检索，B+树支持随机检索和顺序检索
2.B+树空间利用率高，可以减少IO次数，磁盘读写代价更低。
一般来说索引本身也很大，往往以索引文件的形式存储在磁盘上，这样索引查找过程就要产生磁盘IO消耗。B+树的内部节点只作为索引使用，其内部节点（非叶子节点）比B树更小，判断能容纳的节点中关键字更多，一次读取到的键更多。
3.B+树查询效率更稳定，因为数据存放在叶子节点。
4.B树在一定程度上也提高了磁盘IO性能，但没有解决遍历效率低下的问题。B+树的叶子节点都使用指针顺序连接在一起，只要遍历叶子节点就可以实现所有值。
5.增删文件时，B树需要重新调整树结构。B+树不需要调整树结构，因此B+树效率更高。

可以关注，微信订阅号“猿来编码”的文章查看更多面试题或者其他工具分享