面试准备：数据库常见面试题汇总

最新推荐文章于 2024-09-26 17:02:54 发布

No_Game_No_Life_

最新推荐文章于 2024-09-26 17:02:54 发布

阅读量1.9k

点赞数 4

分类专栏：面试准备文章标签：面试 mysql

本文链接：https://blog.csdn.net/No_Game_No_Life_/article/details/105411980

版权

面试准备专栏收录该内容

42 篇文章 16 订阅

订阅专栏

文章目录

基础
相关原理
优化
- 16.MySQL有哪几种索引？
- 17.索引对性能有哪些影响？
事务

基础

1.MySQL中varchar与char的区别？

CHAR和VARCHAR最大的不同就是一个是固定长度，一个是可变长度。
CHAR存储定长数据很方便，CHAR字段上的索引效率级高，比如定义 char(10)，那么不论你存储的数据是否达到了10个字节，都要占去10个字节的空间,不足的自动用空格填充（并且如果存储的char类型的字符串后面有空格的话，innodb会忽略）。
VARCHAR值保存时只保存需要的字符数，另加一个字节来记录长度(如果列声明的长度超过255，则使用两个字节)。

一般来说，CHAR比VARCHAR更快，因为CHAR是固定长度的，而VARCHAR需要增加一个长度标识，处理时需要多一次运算。但是有例外，参考：MySQL Innodb数据库性能实践——VARCHAR vs CHAR，但是如果使用的是Innodb引擎的话，推荐使用varchar代替char。特别是字符串的平均长度比最大长度要小很多的情况；当然，如果你的字符串本来就很短或者字符串长度固定，例如只有10个字符，那么就优先选CHAR了。

2.MySQL中int(11)与int(3)的区别？

当我们在选择使用int的类型的时候，不论是int(3)还是int(11)，它在数据库里面存储的都是4个字节的长度。

如果int的值为10
int（11）显示结果为00000000010
int（3）显示结果为010
就是显示的长度不一样而已，但都是占用四个字节的空间，所以存储的数据不会因为超过3位而不能存储。

类型长度，参考：MySQL数据类型。

更多知识点，参考高性能Mysql——Schema与数据类型优化。

3.date,datetime和timestamp数据类型有什么区别？

格式
一个完整的datetime日期格式如下：YYYY-MM-DD HH:MM:SS[.fraction]，它可分为两部分：date部分和time部分，其中，date部分对应格式中的“YYYY-MM-DD”，time部分对应格式中的“HH:MM:SS[.fraction]”。对于DATE字段来说，它只支持date部分，占用3个字节，对于TIME字段来说，只支持time部分，占用3个字节。
timestamp格式: YYYYMMDD HHMMSS，占用4字节
timestamp和datetime的不同点：

	DATETIME	TIMESTAMP
时间范围	1001——9999 年	1970——2038 年
时区	存储时间与时区无关	存储时间与时区有关，显示的值也依赖于时区
默认值	null	默认不为空(not null)，默认值为当前时间(CURRENT_TIMESTAMP)

4.union 与union all的区别

union 在进行表链接后会筛选掉重复的记录，所以在表链接后会对所产生的结果集进行排序运算，删除重复的记录再返回结果。 union all 则会显示重复结果,只是简单的两个结果合并并返回.所以效率比union高,在保证没有重复数据的情况下用union all.
参考：MySQL UNION操作符

5.各种join的区别？

参考：mysql 内连接、自然连接、外连接的区别

6.drop,delete与truncate的区别？

DROP语句：
直接删掉表。drop语句将表所占用的空间全释放掉。
TRUNCATE语句：
删除表中数据，再插入时自增长id又从1开始。当表被TRUNCATE 后，这个表和索引所占用的空间会恢复到初始大小。 并且不会把单独的删除操作记录记入日志保存，删除行是不能恢复的。
DELETE语句：
删除表中数据，可以加where字句，并且同时将该行的删除操作作为事务记录在日志中保存以便进行进行回滚操作。DELETE操作不会减少表或索引所占用的空间。

delete语句为DML（data maintain Language),这个操作会被放到 rollback segment中,事务提交后才生效。如果有相应的 tigger,执行的时候将被触发。
truncate、drop是DLL（data define language),操作立即生效，原数据不放到 rollback segment中，不能回滚。

7.MySQL如何获取当前日期？*

SELECT CURRENT_DATE();

8.视图的作用，视图可以更改么？*

视图是虚拟的表，与包含数据的表不一样，视图只包含使用时动态检索数据的查询；不包含任何列或数据。使用视图可以简化复杂的sql操作，隐藏具体的细节，保护数据；视图创建后，可以使用与表相同的方式利用它们。
视图不能被索引，也不能有关联的触发器或默认值，如果视图本身内有order by 则对视图再次order by将被覆盖。
创建视图：create view XXX as XXXXXXXXXXXXXX;
对于某些视图比如未使用联结子查询分组聚集函数Distinct Union等，是可以对其更新的，对视图的更新将对基表进行更新；但是视图主要用于简化检索，保护数据，并不用于更新，而且大部分视图都不可以更新。

9.简单解释数据库三范式？数据库五种约束？

实例解析参考
第一范式就是数据库表的每一列都是不可分割的基本数据项，同一列中不能有多个值。

第二范式要求实体的属性完全依赖于主关键字（即不存在部分依赖）。所谓完全依赖是指不能存在仅依赖主关键字一部分的属性，如果存在，那么这个属性和主关键字的这一部分应该分离出来形成一个新的实体

第三范式要求一个数据库表中不包含已在其它表中已包含的非主关键字信息（即消除传递依赖）。
例如，存在一个部门信息表，其中每个部门有部门编号（dept_id）、部门名称、部门简介等信息。那么在员工信息表中列出部门编号后就不能再将部门名称、部门简介等与部门有关的信息再加入员工信息表中。

更多知识点，参考：高性能Mysql——范式与反范式。

1.primary KEY:设置主键约束；
2.UNIQUE：设置唯一性约束，不能有重复值；
3.DEFAULT 默认值约束，height DOUBLE(3,2)DEFAULT 1.2 height不输入是默认为1,2
4.NOT NULL：设置非空约束，该字段不能为空；
5.FOREIGN key :设置外键约束。

10.不满足数据库三范式可能会出现什么情况？

数据冗余(想修改一个属性,就要更新多行数据)
插入异常(想要插入数据,结构因为表设计的问题,导致不能成功插入)
删除异常(只想删除其中的某些数据 ,结果把不该删的也删了)
更新异常(想更新一条数据,结果工作量大,还容易出错)

优化

16.MySQL有哪几种索引？

从数据结构角度：

MySQL索引使用的数据结构主要有B+Tree索引和哈希索引 。
对于哈希索引来说，底层的数据结构就是哈希表，如果哈希冲突很多的话，一些索引维护操作的代价也会很高。

从物理存储角度：

聚集索引是基于B+树的，非叶节点只做根据主键的索引，而由叶节点来保存主键记录的数据或者指针，这样使得B+树每个非叶子节点所能保存的关键字大大增加，使得B+树层级更少，IO操作也更少。B+树叶子节点的关键字从小到大有序排列，天然具备排序功能，也将随机I/O转换为顺序I/O；左边的数据会有一个向右的指针，使得全节点遍历更快。

如果没有主键被定义，那么该表的第一个唯一非空索引被作为聚集索引。
如果没有主键也没有合适的唯一索引，那么innodb内部会生成一个隐藏的主键作为聚集索引。

非聚集索引和聚集索引差不多，通过聚集索引可以查到需要查找的数据，而通过非聚集索引可以查到记录对应的主键值，再使用主键的值通过聚集索引查找到需要的数据。

参考：高性能Mysql——创建高性能的索引。

参考：其他索引方式

17.索引对性能有哪些影响？

优点：提高查询速度
将随机 I/O 变顺序I/O
减少数据库服务器需要扫描的数据量
帮助数据库服务器避免排序和临时表
唯一索引，能保证数据的唯一性

缺点：
索引的创建和维护耗时随着数据量的增加而增加
对表中数据进行增删改时，索引也要动态维护，降低了数据的维护速度
增大磁盘占用

事务

18.数据库事务四大特性？

原子性(Atomic)：一个事务中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚到事务开始前的状态，就像这个事务从来没有执行过一样
一致性(Consistency)：在事务开始之前和事务结束以后，数据库的完整性没有被破坏
隔离性(Isolation)：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）
持久性(Durability)：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失

19.ACID、BASE和CAP？

参考：DDBS BASE

参考：DDBS CAP

20.数据隔离级别：解释脏读、不可重复读，幻读，更新丢失

脏读(Dirty read)：在一个事务中读取到另一个事务已经修改但没有提交的数据。

例如，事务A对数据进行了修改，但是还没有提交，这时事务B读取这个数据，然后事务A回滚，那么事务B取的数据无效。

解决办法：把数据库的事务隔离级别调整到READ_COMMITTED

不可重复读(NonRepeatable Read)：不能读到相同的数据内容，事务A读取到了事务B已经提交的修改数据（即一个事务范围内两个相同的查询却返回了不同数据）。

例如事务A先读取数据，然后事务B对该同一数据修改并提交，那么事务A再次读取该数据时，由于事务B对该数据的修改，事务A两次读到的的数据可能是不一样的。

解决办法：把数据库的事务隔离级别调整到REPEATABLE_READ

幻读(Phantom Read)：事务在插入已经检查过不存在的记录时，惊奇的发现这些数据已经存在了(针对的insert操作) 。

在事务A查询结束后，事务B往User表中插入了一条id为1的数据。此时，由于事务A查询到id为1的用户不存在，因此插入1条id为1的数据，报错：主键冲突。

解决办法：把数据库的事务隔离级别调整到SERIALIZABLE_READ

更新丢失(Update lose)：两个事务同时操作相同数据，后提交的事务会覆盖先提交的事务处理结果。

解决办法：乐观锁

21.Mysql提供了哪几种事务隔离级别？

MySQL数据的四种隔离级别：
　　① Serializable (串行化)：可避免脏读、不可重复读、幻读的发生。
　　② Repeatable read (可重复读)：可避免脏读、不可重复读的发生。
　　③ Read committed (读已提交)：可避免脏读的发生。
　　④ Read uncommitted (读未提交)：最低级别，任何情况都无法保证。

通过SELECT @@transaction_isolation;(mysql 5.7以下是select @@tx_isolation)可以查看事务隔离级别：
在这里插入图片描述

隔离界别为可重复读，那么我写一个脏读和可重复读的事务：

# 事务A
begin;
	select * from orders where id="190827F4AK12R30H";
commit;
# 事务B
begin;
	UPDATE orders SET left_msg="new message" where id="190827F4AK12R30H";
	select sleep(10);
commit;

查询结果是：尽管事务B修改了left_msg为new message，但是事务A的查询结果是old message。

# 事务A
begin;
	select * from orders where id="190827F4AK12R30H";
	select sleep(10);
	select * from orders where id="190827F4AK12R30H";
commit;

# 事务B
begin;
	UPDATE orders SET left_msg="new message" where id="190827F4AK12R30H";
commit;

查询结果是：尽管事务B修改了left_msg为new message，但是两次事务A的查询结果都是 old message。

最后试试幻读：

# 事务A
begin;
	select * from stu where id=1;
	select sleep(10);
	insert into stu(id,name,age) values (1,"xiaoming",13);
commit;
# 事务B
begin;
	insert into stu(id,name,age) values (1,"xiaohong",15);
commit;

运行结果：
在这里插入图片描述
可把事务级别改成串行set session transaction isolation level SERIALIZABLE;。
使用set session transaction isolation level REPEATABLE READ;改回来

22.简要说明InnoDB事务是如何通过日志来实现的？

小结：
事务的原子性是通过 undo log 来实现的
事务的持久性性是通过 redo log 来实现的
事务的隔离性是通过 (读写锁+MVCC)来实现的

redo log和undo log：
redo log记录数据被修改后的信息。undo log主要记录的是数据的逻辑变化，为了在发生错误时回滚之前的操作，需要将之前的操作都记录下来，然后在发生错误时才可以回滚
参考：高性能Mysql——InnoDB事务是如何通过日志来实现的？

23.二进制日志（binlog）的作用?与redo log的区别？

区别：

redo log是在InnoDB存储引擎层产生，而binlog是MySQL数据库的上层产生的，并且二进制日志不仅仅针对INNODB存储引擎，MySQL数据库中的任何存储引擎对于数据库的更改都会产生二进制日志。
两种日志记录的内容形式不同。MySQL的binlog是逻辑日志，其记录是对应的DDL和DML语句。而innodb存储引擎层面的重做日志是物理日志，记录的是每个页的修改。
两种日志与记录写入磁盘的时间点不同，二进制日志只在事务提交完成后进行一次写入。而innodb存储引擎的重做日志在事务进行中不断地被写入，并日志不是随事务提交的顺序进行写入的。
binlog可以作为恢复数据使用，主从复制搭建，redo log作为异常宕机或者介质故障后的数据恢复使用。
binlog不是循环使用，在写满或者重启之后，会生成新的binlog文件，redo log是循环使用。

24.说一说MySQL中的锁机制

按粒度分：

表级锁：粒度最大的一种锁，表示对当前操作的整张表加锁。开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低
行级锁：粒度最小的一种锁，表示只针对当前操作的行进行加锁。开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度最高
页级锁：粒度介于行级锁和表级锁中间的一种锁。开销、加锁时间和并发度界于表锁和行锁之间；会出现死锁

按操作分：

读锁(共享锁)：针对同一份数据，多个读取操作可以同时进行，不互相影响
写锁(排它锁)：当前写操作没有完成前，会阻断其他写锁和读锁

行级死锁

25.InnoDB的行锁/表锁？*

mysql的行锁是通过索引加载的，即是行锁是加在索引响应的行上的。

要是对应的SQL语句没有走索引，则会全表扫描，此时取而代之的是表锁。

表锁：不会出现死锁，发生锁冲突几率高，并发低。（表锁总是一次性获得所需的全部锁，要么全部满足，要么全部等待。所以不会产生死锁。）
行锁：会出现死锁，发生锁冲突几率低，并发高。

行锁分共享锁和排它锁。
共享锁又称：读锁。当一个事务对某几行上读锁时，允许其他事务对这几行进行读操作，但不允许其进行写操作，也不允许其他事务给这几行上排它锁，但允许上读锁。
select math from zje where math>60 lock in share mode；
排它锁又称：写锁。当一个事务对某几个上写锁时，不允许其他事务写，但允许读。更不允许其他事务给这几行上任何锁。包括写锁。
select math from zje where math >60 for update;

26.什么是MVCC ？

MVCC全称是： Multiversion concurrency control，多版本控制: 指的是一种提高并发的技术。

一般我们认为MVCC有下面几个特点（也就是乐观锁的一种实现）：

每行数据都存在一个版本，每次数据更新时都更新该版本。（其实有删除的版本号和更新的版本号两种）
修改时Copy出当前版本（做快照）, 然后随意修改，各个事务之间无干扰
保存时比较版本号，如果成功(commit)，则覆盖原记录, 失败则放弃copy(rollback)
就是每行都有版本号，保存时根据版本号决定是否成功，听起来含有乐观锁的味道, 因为这看起来正是，在提交的时候才能知道到底能否提交成功

而InnoDB实现MVCC的方式是:

事务以排他锁的形式修改原始数据
把修改前的数据存放于undo log，通过回滚指针与主数据关联
修改成功（commit）啥都不做，失败则恢复undo log中的数据（rollback）

从某种意义上来说，InnoDB的MVCC并非真正的MVCC，他只是借用MVCC的名号实现了读的非阻塞而已。

MVCC只在 READ COMMITTED 和 REPEATABLE READ 两个隔离级别下工作。其他两个隔离级别够和MVCC不兼容, 因为 READ UNCOMMITTED 总是读取最新的数据行, 而不是符合当前事务版本的数据行。而 SERIALIZABLE 则会对所有读取的行都加锁。

参考：MVCC

27.什么是意向锁？*

在支持多层锁定的数据库中，若事务在某子节点持有读/写锁，则事务在其父节点持有意向锁。

意向锁的含义是如果对一个结点加意向锁，则说明该结点的下层结点正在被加锁；对任一结点加锁时，必须先对它的上层结点加意向锁。
例如，对任一元组加锁时，必须先对它所在的关系加意向锁。于是，事务T要对关系R1加 X锁时，系统只要检查根结点数据库和关系R1是否己加了不相容的锁，而不再需要搜索和检查尺中的每一个元组是否加了X锁。

下面介绍三种常用的意向锁：意向共享锁（Intent Share Lock，简称IS锁）；意向排它锁（Intent Exclusive Lock，简称IX锁）；共享意向排它锁（Share Intent Exclusive Lock，简称SIX锁）。

意向锁在原来的 X/S 锁之上引入了 IX/IS，IX/IS 都是表锁，用来表示一个事务想要在表中的某个数据行上加 X 锁或 S 锁。有以下两个规定:

一个事务在获得某个数据行对象的 S 锁之前，必须先获得表的 IS 锁或者更强的锁；
一个事务在获得某个数据行对象的 X 锁之前，必须先获得表的 IX 锁。

通过引入意向锁，事务 T 想要对表 A 加 X 锁，只需要先检测是否有其它事务对表 A 加了 X/IX/S/IS 锁，如果加了就表示有其它事务正在使用这个表或者表中某一行的锁，因此事务 T 加 X 锁失败。

引进意向锁是为了提高封锁子系统的效率。该封锁子系统支持多种封锁粒度。原因是：在多粒度封锁方法中一个数据对象可能以两种方式加锁 ― 显式封锁和隐式封锁。因此系统在对某一数据对象加锁时不仅要检查该数据对象上有无（显式和隐式）封锁与之冲突，还要检查其所有上级结点和所有下级结点，看申请的封锁是否与这些结点上的（显式和隐式）封锁冲突，显然，这样的检查方法效率很低。

考虑这个例子：
事务A锁住了表中的一行，让这一行只能读，不能写。之后，事务B申请整个表的写锁。如果事务B申请成功，那么理论上它就能修改表中的任意一行，这与A持有的行锁是冲突的。数据库需要避免这种冲突，就是说要让B的申请被阻塞，直到A释放了行锁。

如果不使用意向锁，那么需要两个判断：