MySQL面试题

苍曦

已于 2024-11-19 19:10:11 修改

阅读量905

点赞数 9

文章标签： mysql 数据库

于 2024-11-19 11:33:05 首次发布

本文链接：https://blog.csdn.net/m0_68728226/article/details/143872692

版权

一、简单

1.1MySQL 在设计表(建表)时需要注意什么?

设计表的时候，在满足业务需求的情况下，需要额外考虑表结构的高效性、扩展性以及维护性。

1)选择合适的数据类型:为字段选择合适的数据类型可以有效减少存储空间，并提高查询效率。例如:

使用 INT而不是 BIGINT，前提是如果数据不会超出 INT范围。
使用 VARCHAR 而不是 TEXT，如果字段长度比较短且可变
使用 DATE、DATETIME或TIMESTAMP而不是VARCHAR来存储日期时间信息

2)主键与唯一约束

主键是表的唯一标识符，每个表应该有一个主键
如果需要确保某些字段唯一性，可以使用唯一约束(UNIQUE)。

3)索引的设计:索引是提高查询性能的关键。但是设计时应避免过多索引，以免对写操作造成负担

对于经常查询的字段(如WHERE、J0IN、ORDER BY 中使用的字段)应该创建索引。
考虑复合索引，可以将多个列组成一个索引，优化复合查询性能。
避免在低基数列(如布尔值、性别字段)上创建索引，因为它们不会带来太大的查询性能提升。

4)表的范式化(Normalization):规范化(通常遵循到 3NF)有助于消除数据冗余，提高数据致性，避免数据更新异常。但在某些场景下，可以选择一定的反规范化来提高查询性能。例如:·

冗余一些常用字段，避免关联表查询，提升性能。
预留一些扩展字段，例如 extendld 等，便于后续扩展。
部分格式不可控字段可以设计为json 格式，防止频繁变更表结构。

5)外键与关联设计

虽然在关系型数据的场景下，使用外键(FOREIGN KEY )可以确保数据一致性。但是外键约束会影响性能，所以一般现在互联网公司不使用外键。

设计时避免不必要的 NULL值

在设计数据库时，应该避免使用过多的 NULL值，因为:
NULL 值的比较和处理可能引入额外的计算开销。在业务逻辑中，NULL 值可能需要额外的处理(例如，IS NULL、IFNULL 等)

在可能的情况下，使用默认值或将空值通过其他方式(如空字符串、0)来代替 ULL。

1.2MySQL 中的数据排序是怎么实现的?

排序过程中，如果排序字段命中索引，则利用索引排序。
反之，使用文件排序。
文件排序中，如果数据量少则在内存中排序，具体是使用单路排序或者双路排序。
如果数据大则利用磁盘文件进行外部排序，一般使用归并排序。

1.3MySQL的索引类型有哪些?

从数据结构角度来看，MySQL索引可以分为以下几类:

B+树索引
哈希索引
倒排索引(Full-Text)
R-树索引(多维空间树)

从常见的基于InnoDB B+ 树索引角度来看，可以分为

聚簇索引(Clustered Index)
非聚簇索引(Non-clustered Index)

从索引性质的角度来看，可以分为:

普通索引(二级索引、辅助索引)
主键索引
联合索引
唯一索引
全文索引
空间索引

扩展知识
1)B+tree 索引:B+tree 是 MySQL 中最常用的索引类型。它使用平衡树结构来存储索引数据，适用于范围查询和排序。

2)哈希索引:哈希索引基于哈希算法，将索引列的值转换为哈希值，然后通过哈希表来进行快速的等值查询。主要用于 Memory 引擎。查找速度非常快，但不支持范围查询，只适用于精确匹配查询。

3)倒排索引:用于对文本字段进行全文搜索，允许在文本数据中进行关键字搜索和模糊匹配。

4)R-树索引:空间索引用于处理空间数据，如地理位置和几何形状。MySQL提供了专门的空间索引类型，可以用于空间数据的查询和分析。

1.4MySQL 索引的最左前缀匹配原则是什么?

MySQL索引的最左前缀匹配原则指的是在使用联合索引时，查询条件必须从索引的最左侧开始匹配。如果一个联合索引包含多个列，查询条件必须包含第一个列的条件，然后是第二个列，以此类推。
底层原理:因为联合索引在 B+树中的排列方式遵循“从左到右”的顺序，例如联合索引(first name,last name,age)会按照(first name,last name, age)的顺序在 B+树中进行排序MySQL在查找时会优先使用 first_name作为匹配依据，然后依次使用 1ast_name 和 age 。因此组合索引能够从左到右依次高效匹配，跳过最左侧字段会导致无法利用该索引。
按照(first_name，last_name，age)的顺序在 B+ 树中的排列方式如下

1.5在 MySQL 中建索引时需要注意哪些事项?

1)不能盲目的建立索引，索引并不是越多越好，索引会占用空间，且每次修改的时可能都需要维护索引的数据，消耗资源。
2)对于字段的值有大量重复的不要建立索引。比如说:性别字段，在这种重复比例很大的数据行中建立索引也不能提高检索速度。但是也不绝对，例如定时任务的场景，大部分任务都是成功，少部分任务状态是失败的，这时候通过失败状态去查询任务，实际上能过滤大部分成功的任务，效率还是可以的。
3)对于一些长字段不应该建立索引。比如 text、longtext 这种类型字段不应该建立索引。因为占据的内存大，扫描的时候大量加载至内存中还耗时，使得提升的性能可能不明显，甚至可能还会降低整体的性能，因为别的缓存数据可能因为它被踢出内存，下次查询还需要从磁盘中获取。
4)当数据表的修改频率远大于查询频率时，应该好好考虑是否需要建立索引。因为建立索引会减慢修改的效率，如果很少的查询较多的修改，则得不偿失。
5)对于需要频繁作为条件查询的字段应该建立索引。在 where 关键词后经常查询的字段，建立索引能提高查询的效率，如果有多个条件经常一起查询，则可以考虑联合索引，减少索引数量。
6)对经常在 order by、group by、distinct 后面的字段建立索引。这些操作通常需要对结果进行排序、分组或者去重，而索引可以帮助加快这些操作的速度。

1.6MySQL 是如何实现事务的?

MySQL 主要是通过:锁、RedoLog、UndoLog、MVCC来实现事务MySQL利用锁(行锁、间隙锁等等)机制，使用数据并发修改的控制，满足事务的隔离性。Redo Log(重做日志)，它会记录事务对数据库的所有修改，在崩溃时恢复未提交的更改，用来满足事务的持久性。
Undo Log(回滚日志)，它会记录事务的反向操作，简单地说就是保存数据的历史版本，用于事务的回滚，使得事务执行失败之后可以恢复之前的样子。实现原子性和隔离性
MVCC(多版本并发控制)，满足了非锁定读的需求，提高了并发度，实现了读已提交和可重复读两种隔离级别，实现了事务的隔离性。
看到这有小伙伴问一致性呢?
其实事务主要是为了实现一致性，具体是通过 AID，即原子性、隔离性和持久性来达到一致性的目的。

1.7MySQL 中的事务隔离级别有哪些?

在 MySQL中，事务隔离级别主要有以下四种:
1)读未提交(READ UNCOMMITTED)

这是最低的隔离级别，在该级别下，一个事务可以看到另一个事务尚未提交的数据修改。这可能会导致脏读问题，即读取到其他事务未提交的数据。
2)读已提交(READCOMMITTED)
在这个级别下，一个事务只能看到已经提交的其他事务所做的修改。这可以避免脏读问题，但是可能会引发不可重复读问题，即在同一个事务中，相同的查询可能返回不同的结果。
3)可重复读(REPEATABLE READ)
在这个级别下，确保在一个事务中的多个查询返回的结果是一致的。这可以避免不可重复读问题，但是可能会引发幻读问题，即在同一个事务中，多次查询可能返回不同数量的行(MVSQL默认的隔离级别)。
4)串行化(SERIALIZABLE):
这是最高的隔离级别，在这个级别下，事务串行执行，即每个事务都会等待前一个事务执行完毕才会开始执行。这可以避免所有的并发问题，但是会大大降低并发性能。

扩展知识
隔离级别的影响
较低的隔离级别 (如读未提交)提高了并发性，但可能导致数据不一致;较高的隔离级别(如串行化)保证数据一致性，但降低了并发性。因此，在设计应用时，需要在性能和数据一致性之间找到平衡。
般互联网大厂会选择读已提交隔离级别。

1.8MySQL 默认的事务隔离级别是什么?为什么选择这个级别?

MySQL默认的隔离级别是可重复读(Repeatable Read)，即 RR.原因是为了兼容早期 binlog 的 statement 格式问题，如果是使用读已提交、读未提交等隔离级别使用了 statement 格式的 binlog 会导致主从(备)数据库数据不一致问题。

1.9MySQL 中 count(*)、count(1)和 count(字段名) 有什么区别?

count(1)和 count(字段名)都是用来统计行数的聚合函数，但它们有些在MySQL中，count(*)许的区别:
功能上:
1)count(*)会统计表中所有行的数量，包括 nu11值(不会忽略任何一行数据)。由于只是计算行
数，不需要对具体的列进行处理，因此性能通常较高。count(1)和count(*)几乎没差别，也会统计表中所有行的数量，包括 nu11 值。count(字段名)会统计指定字段不为 nu11 的行数。这种写法会对指定的字段进行计数，只会统计字段值不为 nu11 的行。

1.10MySQL 中 int(11) 的 11 表示什么?

在 MySQL 中， int(11)中的 11 表示显示宽度，并不影响存储的大小或数值范围。具体来说:
显示宽度:当使用 ZEROFILL 属性时， int(11)表示如果数值的位数少于 11 位，则会在前面填充零。例如，数值 42将显示为88888888842 。不使用ZEROFILL时，显示结果是 42(前面有九个空格)
存储大小: int类型始终占用 4字节(32 位)，可以表示的范围是从-2,147,483,648 到2,147,483,647 。
所以， int(11)中的数字与整数的大小无关，只是显示格式的定义。这个配置主要是为了在查询结果中保持一致的显示格式，方便阅读和理解，但实际业务上基本用不到这个。
扩展知识
MySQL 8.0 显示宽度的变化
MySQL 8.0 版本中，整数类型的显示宽度(例如 INT(11)中的 11)被正式弃用。即定义的显示宽度不再影响任何存储或比较操作，显示宽度的设置被忽略。虽然显示宽度本身被弃用，但仍然可以使用 ZEROFILL属性来实现零填充效果。使用 ZEROFILL时数字会在左侧填充零以达到特定的宽度。

1.11MySQL 中 varchar 和 char 有什么区别?

CHAR 和 VARCHAR 是两种用于存储字符串的列类型，它俩最大的不同就是一个是固定长度，一个是可变长度。
CHAR(n):固定长度的字符串。CHAR 列的长度是固定的，即使存储的字符串长度小于定义的长度，MySQL也会在字符串的末尾填充空格以达到指定长度(如果char 类型的字符串后面有空格的话，innodb 会忽略)。
VARCHAR(n):可变长度的字符串。VARCHAR 列的长度是可变的，存储的字符串长度与实际数据长度相等，并且在存储数据时会额外增加1到2个字节(字符长度超过 255，则使用两个字节)用于存储字符串的长度信息。
理论上来说 CHAR 会比 VARCHAR 快，因为 VARCHAR 长度不固定，处理需要多一次运算，但是实际上这种运算耗时微乎其微，而固定大小在很多场景下比较浪费空间，除非存储的字符确认是固定大小或者本身就很短，不然业务上推荐使用 VARCHAR。

1.12什么是分库分表?分库分表有哪些类型!(或策略)?

分库分表是数据库性能优化的一种方法，通过将数据分散存储在多个数据库或表中，来提高系统的可扩展性、性能和可用性。
分库分表的类型(或策略)包括:
1)水平分表
。将同一张表的数据按行划分，分散到多个表中。例如，可以按用户ID的范围将数据分为多个表(如user1、user_2)。
2)垂直分表
将一张表的不同列拆分到多个表中，以减少每张表的字段数量和提高查询效率。例如，用户表可以分为基本信息表和详细信息表。
3)水平分库
将相同的表结构复制一份到另一个库中，每个库的表结构是一样的，可以减少单一数据库的读写压力，在大数量的情况下提高读写性能。例如，database1、database2 。
垂直分库:
将数据分散到不同的数据库实例中。可以根据业务功能或模块进行分库，如将用户数据、订单数据分别存储在不同的数据库中。

1.13MySQL 的 Doublewrite Buffer 是什么?它有什么作用?

MySQL 的 Doublewrite Buffer 是 InnoDB 存储引擎中的一个机制，用于确保数据的安全性和一致性。其作用是将数据首先写入一个内存缓冲区(双写缓冲区)，然后再将其写入数据文件。这种方式可以防止在写入过程中因崩溃或故障导致数据损坏，确保数据的一致性和完整性。
工作原理简述:
写入流程:当事务提交时，InnoDB 首先将数据写入 Doublewrite Buffer，再从该缓冲区将数据写入磁盘的实际数据文件。
恢复机制:在崩溃恢复时，InnoDB 会使用 Doublewrite Buffer 中的数据来修复损坏的页，保证数据不丢失。

1.14MySQL 中的 Log Buffer 是什么?它有什么作用?

MySQL 中的 Log Buffer 是一个内存区域，用于暂时存储事务日志(redo log)的数据。在 InnoDB存储引擎中，它的主要作用是提高性能，通过批量写入操作将日志数据从内存中写入磁盘，减少磁盘I/0 操作的频率。

1.15MySQL 中 INNER JOIN、LEFT JOIN 和 RIGHT JOIN 的区别是什么?

INNER JOIN:
只返回两个表中匹配的行。如果没有匹配，则该行不会出现在结果集中。
适用于只关心交集数据的场景。
LEFT JOIN(或 LEFT OUTER JOIN):
返回左表中的所有行，即使右表中没有匹配的行。如果右表没有匹配，则结果中的右侧列会显示为NULL
适用于需要保留左表所有数据的场景。
RIGHT JOIN(或 RIGHT OUTER JOIN):
返回右表中的所有行，即使左表中没有匹配的行。如果左表没有匹配，则结果中的左侧列会显示为NULL
适用于需要保留右表所有数据的场景。

1.16MySQL中`LIMIT 100000000,10`和`LIMIT 10`的执行速度是否相同?

速度差很多，limit 10 快很多!
原因如下:
1)LIMIT 18888888，18 需要先处理(通常是读取并跳过)前 100000000 条记录，然后再获取到需要的 10 条记录，开销成本很大，因为需要扫描100000000 数据才能得到后面的10 条数据，会导致大量的磁盘 O。
2)LIMIT 10 从结果集第一个记,录开始扫描直接返回前 10 条记录。
通常面对 LINIT 188888888，18 这种大分页的情况，可以先使用条件过滤掉，比如使用主键 ID 来进行范围过滤，然后再 limit 10。

1.17MySQL 中 DATETIME 和 TIMESTAMP 类型的区别是什么?

存储方式:

DATETIME:以字符串形式存储，范围为1088-81-0188:88:88'到9999-12-31 23:59:59'，占用8字节
TIMESTAMP:以Unix时间戳形式存储，范围为1970-01-0188:88:81'UTC到2838-81-19 03:14:87'UTC，占用4字节。

时区处理:

·DATETIME:不受时区影响，存储的时间是具体的日期和时间，不会进行自动转换TIMESTAMP:受时区影响，存储时会转换为 UTC，取出时会根据连接的时区进行转换，适合处理跨时区的数据。

扩展知识
默认值和自动更新

DATETIME:在 MYSQL5.6及更早版本中，DATETIME列不能有自动更新的默认值。在 MySQL5.6及以后版本中，可以使用 DEFAULT 和 ONUPDATE 子句来指定自动初始化和更新行为，但不像 TIMESTAMP 那么直观。
TIMESTAMP:在 MySQL5.6及更高版本中，TIMESTAMP 列可以有默认的当前时间戳 CURRENT TIMESTAMP，并且可以使用 ON UPDATECURRENT TIMESTAMP 使其在行更新时自动更新为当前时间戳。这使得 TIMESTAMP 非常适合记录行的创建和修改时间。

1.18数据库的三大范式是什么?

第-范式(1NF)
确保每个列的值都是原子值，表中的每个字段只能包含单一的数据项，不允许重复的列和多值字段。第二范式(2NF):
在满足第一范式的基础上，确保表中的每个非主键字段完全依赖于主键，而不是部分依赖。即，非主键字段必须依赖于整个主键。第三范式(3NF)
在满足第二范式的基础上，确保非主键字段之间不依赖，即消除传递依赖。所有非主键字段只能依赖于主键，不应相互依赖。

三大范式进一步理解
数据库的三大范式是数据库设计中常用的规范，它们的目的是减少数据冗余，提高数据的完整性和一致性，使得表的设计更清晰。

第一范式(1NF):规范化
目的:确保数据表的每一列都是单一值，消除重复的列，从而保证数据的原子性。
例如地址作为一个字段，实际上可以拆分成省、市、区等，所以这就不符合第一范式。

第二范式(2NF):消除部分依赖
目的:消除非主键字段对主键部分依赖，从而避免数据几余和更新异常，例如【员工ID、员工姓名、部门ID、部门名】为一张表，员工ID 为主键，此时，员工姓名依赖员工ID，部门名依赖部门ID，这就违反了第二范式,符合范式的设计是:员工表【员工ID、员工姓名、部门ID】，部门表【部门ID、部门名】。

第三范式(3NF):消除传递依赖
目的:消除非主键字段对主键的传递依赖，从而进一步减少数据冗余和更新异常。例如【员工ID、员工姓名、部门ID、部门名、经理|D】为一张表，员工 ID 为主键。此时，经理|D 依赖部门 ID，部门 |ID 依赖员工ID，这说明有依赖传递,违反了第三范式。
符合范式的设计是:员工表【员工ID、员工姓名、部门ID】，部门表【部门ID、部门名、经理ID】

小结
第一范式(1NF):确保每一列都是原子值，即是不可分割的基础数据项。
第二范式(2NF):在满足1NF的基础上，消除非主键字段对主键部分依赖
第三范式(3NF):在满足2NF的基础上，消除非主键字段对主键的传递依赖。

1.19在 MySQL 中，你使用过哪些函数?

1)字符串函数，用于处理文本数据

CONCAT:连接字符串。
SUBSTRING:提取子字符串。
LENGTH:返回字符串的长度，
REPLACE:替换字符串中的子字符串。
UPPER 和 LOWER:将字符串转换为大写或小写，
TRIM:去除字符串两端的空格。
LEFT 和 RIGHT:返回字符串左边或右边的字符,

2)数学函数，用于处理数字运算:

ABS:返回绝对值。
CEIL 和 FLOOR:返回大于或等于/小于或等于指定数的最小整数/最大整数。
MOD:返回除法的余数。
POWER:返回一个数的指定次幂。

3)日期函数，用于处理日期和时间:

NOW:返回当前日期和时间。
DATE ADD 和 DATE SUB:日期加上或减去一个时间间隔。
DATEDIFF:计算两个日期之间的差异。
YEAR、MONTH、DAY:提取日期的年份、月份、日期。
STR_TO_DATE:将字符串转换为日期。

4)聚合函数，用于汇总数据:

COUNT:计算行数。
SUM:计算总和。
AVG:计算平均值。
MAX 和 MIN:返回最大值和最小值。

5)条件函数，用于实现条件逻辑:

IF:条件判断函数。
IFNULL:返回第一个非 NULL值。
CASE:条件选择函数。

1.20在 MySQL 中存储金额数据，应该使用什么数据类型?

在数据库中业界常用两种类型来存储金额:bigint和 decimal。
1)bigint 代码中用 long。

范围:可以存储的整数范围为 -2^63 到 2^63-1(在 MySQL 中为 64 位有符号整数)
存储空间:占用8字节(64位)
精度:精确存储整数，但不支持小数部分，存储的金额单位是分

2)decimal 代码中使用 BigDecimal.
范围:可以存储的数字范围和小数位数由定义的精度和标度决定
存储空间:存储空间取决于定义的精度和标度，存储较大数值时会占用更多空间
精度:支持高精度的小数运算，精确存储定点数，一般用 decimal(18,6)，18 是总位数，6 是小数

扩展知识
项目中到底选择 Long 还是 BigDecimal?
long 类型保存到分，使得小数位(厘)的数据不好处理(需要手动处理，比较麻烦)，因此精度不够高.。在高精度金额计算场量下不合适，例如有些三方支付系统是需要抽成的，例如千分之三、万分之一等等，这类的抽成要求精度比较高，例如会得到1234.5678 这样的金额，乘以 100 后仍然无法存储为精确整数，还是小数，存储则会丢失精度。
0.0078 看似很少，但抽成的笔数一多，比如一天 30 万笔，每笔少个 0.0078，每天的差额也得 2340 元!一年不得 85万!
所以高精度的计算不推荐使用 bigint.
BigDecimal 则很适用于高精度金额场景，且非常灵活，只不过相对于long 性能会差一些，但是在大部分业务上我个人认为可以忽略这个性能问题，除非是特殊场景要求极端性能，所以一般情况下我推荐使用 BigDecimal。

1.21MySOL 中 VARCHAR(100)和 VARCHAR(10) 的区别是什么?

两者的区别就是能存储的字符串长度上限不同，字符数上限是由定义中的括号内的数字决定的。

VARCHAR(100)最多可以存储 100 个字符:
VARCHAR(10)最多可以存储 10 个字符,

两者存储相同字符串时占用的空间是一样的。除了存储字符本身外，还需要额外的1或2个字节来记录字符串的长度。对于长度小于 255 的字符串，使用1个字节:对于长度 255 及以上的字符串，使用2个字节。
虽然存储的空间一样，但是在查询时，即带上 SORT、ORDER 这些字段时，VARCHAR(100)字段会使得查询所占用的内存空间更多，因为在排序相关内存分配时，它是按照固定大小的内存块进行保存，VARCHAR(100)的内存块会大于VARCHAR(10)。

1.22MySQL 中 EXISTS 和 IN 的区别是什么?

基本功能:
EXISTS 用于判断子查询是否返回任何行，通常用于检查某个条件是否满足。
IN 用于检查某个值是否在指定的集合中，可以是一个子查询或静态值列表。
性能差异:
EXISTS 一旦找到匹配行就会立即返回，通常在子查询返回较大数据集时性能更好。IN 通常会评估整个子查询并构建一个值列表，然后再进行匹配，在处理较大数据集时可能性能较差
使用场景:
EXISTS 更适合用于大数据量或复杂逻辑的条件判断，
IN更适合简单的静态值集合或较小的子查询结果，

1.23什么是数据库的逻辑删除?数据库的物理删除和逻辑删除有什么区别?

逻辑删除是一种将数据标记为已删除但实际不会从数据库中移除的删除方式。
一般是在表中添加一个表示删除状态的字段，如isdeleted，默认是 0 表示未删除，1 表示已删除。
物理删除则是直接从数据库中删除记录。
一般业务上都是使用逻辑删除，便于后续的数据分析、追溯等，
扩展知识
物理删除的优缺点
优点:

节省存储空间:物理删除释放了数据占用的存储空间。
查询性能提高:由于数据被彻底删除，查询时不需要过滤已删除的数据。

缺点: