面试篇 -MySQL 100道题目，从基础到进阶（二）

枫蜜柚子茶

于 2024-07-25 22:46:02 发布

阅读量1k

点赞数 28

文章标签：面试 mysql 职场和发展数据库

本文链接：https://blog.csdn.net/m0_46013789/article/details/140701023

版权

花了一周时间收集大量MYSQL经典面试题目📚，内容涵盖了数据库范式、事务、引擎、索引、锁、日志、备份、分区、主从复制等知识点🏝️。适合准备MySQL面试的读者参考和复习🌟📢。

📖Mysql经典面试题目Top100道题🔥🔥

1 - 25 题
26 - 50 题 ◀️
51 - 75 题
76 - 100 题

🔸题目目录：

26. 一条SQL语句在MySQL中如何执行的？
27. InnoDB引擎中的索引策略，了解过吗？
28. 数据库存储日期格式时，如何考虑时区转换问题？
29. 一条sql执行过长的时间，你如何优化，从哪些方面入手？
30. MYSQL数据库服务器性能分析的方法命令有哪些?

31. mysql里记录货币用什么字段类型比较好？

32. Blob和text有什么区别？
33. 什么是数据库连接池?为什么需要数据库连接池呢?

34. mysql 的内连接、左连接、右连接有什么区别？

35. Hash索引和B+树区别是什么？你在设计索引是怎么抉择的？
36. 说说MySQL 的基础架构图
37. 什么是内连接、外连接、交叉连接、笛卡尔积呢？
38. 说一下数据库的三大范式
39. Mysql的binlog有几种录入格式？分别有什么区别？

40. mysql有关权限的表有哪几个呢？
41. InnoDB引擎的4大特性，了解过吗
42. 索引有哪些优缺点？
43. 索引有哪几种类型？
44. 创建索引有什么原则呢？
45. 创建索引的三种方式
46. 百万级别或以上的数据，你是如何删除的？
47. 什么是最左前缀原则？什么是最左匹配原则？

48. B+树在满足聚簇索引和覆盖索引的时候不需要回表查询数据？

49. B树和B+树的区别，数据库为什么使用B+树而不是B树？

50. 覆盖索引、回表等这些，了解过吗？

26. 一条SQL语句在MySQL中如何执行的？

先看一下Mysql的逻辑架构图吧~

查询语句：

先检查该语句是否有权限
如果没有权限，直接返回错误信息
如果有权限，在 MySQL8.0 版本以前，会先查询缓存。
如果没有缓存，分析器进行词法分析，提取 sql 语句select等的关键元素。然后判断sql 语句是否有语法错误，比如关键词是否正确等等。
优化器进行确定执行方案
进行权限校验，如果没有权限就直接返回错误信息，如果有权限就会调用数据库引擎接口，返回执行结果。

27. InnoDB引擎中的索引策略，了解过吗？

覆盖索引
最左前缀原则
索引下推
索引下推优化是 MySQL 5.6 引入的，可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

28. 数据库存储日期格式时，如何考虑时区转换问题？

datetime类型适合用来记录数据的原始的创建时间，修改记录中其他字段的值，datetime字段的值不会改变，除非手动修改它。
timestamp类型适合用来记录数据的最后修改时间，只要修改了记录中其他字段的值，timestamp字段的值都会被自动更新。

29. 一条sql执行过长的时间，你如何优化，从哪些方面入手？

查看是否涉及多表和子查询，优化Sql结构，如去除冗余字段，是否可拆表等
优化索引结构，看是否可以适当添加索引
数量大的表，可以考虑进行分离/分表（如交易流水表）
数据库主从分离，读写分离
explain分析sql语句，查看执行计划，优化sql
查看mysql执行日志，分析是否有其他方面的问题

30. MYSQL数据库服务器性能分析的方法命令有哪些?

Show status, 一些值得监控的变量值： Bytes_received和Bytes_sent 和服务器之间来往的流量。 Com_*服务器正在执行的命令。 Created_*在查询执行期限间创建的临时表和文件。 Handler_*存储引擎操作。 Select_*不同类型的联接执行计划。 Sort_*几种排序信息。
Show profiles 是MySql用来分析当前会话SQL语句执行的资源消耗情况

31. mysql里记录货币用什么字段类型比较好？

货币在数据库中MySQL常用Decimal和Numric类型表示，这两种类型被MySQL实现为同样的类型。他们被用于保存与金钱有关的数据。
salary DECIMAL(9,2)，9(precision)代表将被用于存储值的总的小数位数，而2(scale)代表将被用于存储小数点后的位数。存储在salary列中的值的范围是从-9999999.99到9999999.99。
DECIMAL和NUMERIC值作为字符串存储，而不是作为二进制浮点数，以便保存那些值的小数精度。

32. Blob和text有什么区别？

Blob用于存储二进制数据，而Text用于存储大字符串。
Blob值被视为二进制字符串（字节字符串）,它们没有字符集，并且排序和比较基于列值中的字节的数值。
text值被视为非二进制字符串（字符字符串）。它们有一个字符集，并根据字符集的排序规则对值进行排序和比较。

33. 什么是数据库连接池?为什么需要数据库连接池呢?

连接池基本原理：

数据库连接池原理：在内部对象池中，维护一定数量的数据库连接，并对外暴露数据库连接的获取和返回方法。

应用程序和数据库建立连接的过程：

通过TCP协议的三次握手和数据库服务器建立连接
发送数据库用户账号密码，等待数据库验证用户身份
完成身份验证后，系统可以提交SQL语句到数据库执行
把连接关闭，TCP四次挥手告别。

数据库连接池好处：

资源重用 (连接复用)
更快的系统响应速度
新的资源分配手段统一的连接管理，避免数据库连接泄漏

34. mysql 的内连接、左连接、右连接有什么区别？

Inner join 内连接，在两张表进行连接查询时，只保留两张表中完全匹配的结果集
left join 在两张表进行连接查询时，会返回左表所有的行，即使在右表中没有匹配的记录。
right join 在两张表进行连接查询时，会返回右表所有的行，即使在左表中没有匹配的记录。

35. Hash索引和B+树区别是什么？你在设计索引是怎么抉择的？

B+树可以进行范围查询，Hash索引不能。
B+树支持联合索引的最左侧原则，Hash索引不支持。
B+树支持order by排序，Hash索引不支持。
Hash索引在等值查询上比B+树效率更高。
B+树使用like 进行模糊查询的时候，like后面（比如%开头）的话可以起到优化的作用，Hash索引根本无法进行模糊查询。

36. 说说MySQL 的基础架构图

Mysql逻辑架构图主要分三层：

第一层负责连接处理，授权认证，安全等等
第二层负责编译并优化SQL
第三层是存储引擎。

37. 什么是内连接、外连接、交叉连接、笛卡尔积呢？

内连接（inner join）：取得两张表中满足存在连接匹配关系的记录。
外连接（outer join）：取得两张表中满足存在连接匹配关系的记录，以及某张表（或两张表）中不满足匹配关系的记录。
交叉连接（cross join）：显示两张表所有记录一一对应，没有匹配关系进行筛选，也被称为：笛卡尔积。

38. 说一下数据库的三大范式

第一范式：数据表中的每一列（每个字段）都不可以再拆分。
第二范式：在第一范式的基础上，分主键列完全依赖于主键，而不能是依赖于主键的一部分。
第三范式：在满足第二范式的基础上，表中的非主键只依赖于主键，而不依赖于其他非主键。

39. Mysql的binlog有几种录入格式？分别有什么区别？

有三种格式哈，statement，row和mixed。

statement，每一条会修改数据的sql都会记录在binlog中。不需要记录每一行的变化，减少了binlog日志量，节约了IO，提高性能。由于sql的执行是有上下文的，因此在保存的时候需要保存相关的信息，同时还有一些使用了函数之类的语句无法被记录复制。
row，不记录sql语句上下文相关信息，仅保存哪条记录被修改。记录单元为每一行的改动，基本是可以全部记下来但是由于很多操作，会导致大量行的改动(比如alter table)，因此这种模式的文件保存的信息太多，日志量太大。
mixed，一种折中的方案，普通操作使用statement记录，当无法使用statement的时候使用row。

40. mysql有关权限的表有哪几个呢？

MySQL服务器通过权限表来控制用户对数据库的访问，权限表存放在mysql数据库里，由mysql_install_db脚本初始化。这些权限表分别user，db，table_priv，columns_priv和host。

user权限表：记录允许连接到服务器的用户帐号信息，里面的权限是全局级的。
db权限表：记录各个帐号在各个数据库上的操作权限。
table_priv权限表：记录数据表级的操作权限。
columns_priv权限表：记录数据列级的操作权限。
host权限表：配合db权限表对给定主机上数据库级操作权限作更细致的控制。这个权限表不受GRANT和REVOKE语句的影响。

41. InnoDB引擎的4大特性，了解过吗

插入缓冲（insert buffer)
二次写(double write)
自适应哈希索引(ahi)
预读(read ahead)

42. 索引有哪些优缺点？

优点：

唯一索引可以保证数据库表中每一行的数据的唯一性
索引可以加快数据查询速度，减少查询时间

缺点：

创建索引和维护索引要耗费时间
索引需要占物理空间，除了数据表占用数据空间之外，每一个索引还要占用一定的物理空间
以表中的数据进行增、删、改的时候，索引也要动态的维护。

43. 索引有哪几种类型？

主键索引: 数据列不允许重复，不允许为NULL，一个表只能有一个主键。
唯一索引: 数据列不允许重复，允许为NULL值，一个表允许多个列创建唯一索引。
普通索引: 基本的索引类型，没有唯一性的限制，允许为NULL值。
全文索引：是目前搜索引擎使用的一种关键技术，对文本的内容进行分词、搜索。
覆盖索引：查询列要被所建的索引覆盖，不必读取数据行
组合索引：多列值组成一个索引，用于组合搜索，效率大于索引合并

44. 创建索引有什么原则呢？

最左前缀匹配原则
频繁作为查询条件的字段才去创建索引
频繁更新的字段不适合创建索引
索引列不能参与计算，不能有函数操作
优先考虑扩展索引，而不是新建索引，避免不必要的索引
在order by或者group by子句中，创建索引需要注意顺序
区分度低的数据列不适合做索引列(如性别）
定义有外键的数据列一定要建立索引。
对于定义为text、image数据类型的列不要建立索引。
删除不再使用或者很少使用的索引

45. 创建索引的三种方式

在执行CREATE TABLE时创建索引

CREATE TABLE `employee` (
  `id` int(11) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `date` datetime DEFAULT NULL,
  `sex` int(1) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

使用ALTER TABLE命令添加索引

ALTER TABLE table_name ADD INDEX index_name (column);

使用CREATE INDEX命令创建

CREATE INDEX index_name ON table_name (column);

46. 百万级别或以上的数据，你是如何删除的？

我们想要删除百万数据的时候可以先删除索引
然后批量删除其中无用数据
删除完成后重新创建索引。

47. 什么是最左前缀原则？什么是最左匹配原则？

最左前缀原则，就是最左优先，在创建多列索引时，要根据业务需求，where子句中使用最频繁的一列放在最左边。
当我们创建一个组合索引的时候，如(k1,k2,k3)，相当于创建了（k1）、(k1,k2)和(k1,k2,k3)三个索引，这就是最左匹配原则。。

48. B+树在满足聚簇索引和覆盖索引的时候不需要回表查询数据？

在B+树的索引中，叶子节点可能存储了当前的key值，也可能存储了当前的key值以及整行的数据，这就是聚簇索引和非聚簇索引。在InnoDB中，只有主键索引是聚簇索引，如果没有主键，则挑选一个唯一键建立聚簇索引。如果没有唯一键，则隐式的生成一个键来建立聚簇索引。
当查询使用聚簇索引时，在对应的叶子节点，可以获取到整行数据，因此不用再次进行回表查询。

49. B树和B+树的区别，数据库为什么使用B+树而不是B树？

在B树中，键和值即存放在内部节点又存放在叶子节点；在B+树中，内部节点只存键，叶子节点则同时存放键和值。
B+树的叶子节点有一条链相连，而B树的叶子节点各自独立的。
B+树索引的所有数据均存储在叶子节点，而且数据是按照顺序排列的，链表连着的。那么B+树使得范围查找，排序查找，分组查找以及去重查找变得异常简单。.
B+树非叶子节点上是不存储数据的，仅存储键值，而B树节点中不仅存储键值，也会存储数据。innodb中页的默认大小是16KB，如果不存储数据，那么就会存储更多的键值，相应的树的阶数（节点的子节点树）就会更大，树就会更矮更胖，如此一来我们查找数据进行磁盘的IO次数有会再次减少，数据查询的效率也会更快.