MySQL面试题（一）——数据库相关

最新推荐文章于 2024-09-20 10:58:59 发布

原创最新推荐文章于 2024-09-20 10:58:59 发布 · 1.6k 阅读

3 ·

CC 4.0 BY-SA版权

数据库专栏收录该内容

8 篇文章

订阅专栏

本文深入探讨数据库事务的概念，包括ACID特性、隔离级别、并发控制及其实现机制，如乐观锁与悲观锁。同时，文章覆盖了SQL优化、存储过程的创建与调用，以及数据库设计原则，如范式、主键选择与表结构优化，为读者提供全面的数据库管理和性能提升指南。

事务相关

什么是事务?
ACID是什么?可以详细说一下吗?
同时有多个事务在进行会怎么样呢?
怎么解决这些问题呢?MySQL的事务隔离级别了解吗?
Innodb使用的是哪种隔离级别呢?
对MySQL的锁了解吗?
MySQL都有哪些锁呢?像上面那样子进行锁定岂不是有点阻碍并发效率了?

表结构设计

为什么要尽量设定一个主键?
主键使用自增ID还是UUID?
字段为什么要求定义为not null?
如果要存储用户的密码散列,应该使用什么字段进行存储?

1.什么是存储过程？有哪些优缺点？

　　存储过程是一些预编译的SQL语句。

　　更加直白的理解：存储过程可以说是一个记录集，它是由一些T-SQL语句组成的代码块，这些T-SQL语句代码像一个方法一样实现一些功能（对单表或多表的增删改查），然后再给这个代码块取一个名字，在用到这个功能的时候调用他就行了。

存储过程是一个预编译的代码块，执行效率比较高
一个存储过程替代大量T_SQL语句，可以降低网络通信量，提高通信速率
可以一定程度上确保数据安全

例子：https://www.runoob.com/w3cnote/mysql-stored-procedure.html

3.什么是事务？

　　事务（Transaction）是并发控制的基本单位。

　　所谓的事务，它是一个操作序列，这些操作要么都执行，要么都不执行，它是一个不可分割的工作单位。事务是数据库维护数据一致性的单位，在每个事务结束时，都能保持数据一致性。

　　如果你对索引还不太熟悉，建议阅读：数据库事务 - Mr. David 专栏

2.事务四大特性（ACID）原子性、一致性、隔离性、持久性？

原子性（Atomicity）

原子性是指事务包含的所有操作要么全部成功，要么全部失败回滚，因此事务的操作如果成功就必须要完全应用到数据库，如果操作失败则不能对数据库有任何影响。

一致性（Consistency）

事务开始前和结束后，数据库的完整性约束没有被破坏。比如A向B转账，不可能A扣了钱，B却没收到。

隔离性（Isolation）

隔离性是当多个用户并发访问数据库时，比如操作同一张表时，数据库为每一个用户开启的事务，不能被其他事务的操作所干扰，多个并发事务之间要相互隔离。

同一时间，只允许一个事务请求同一数据，不同的事务之间彼此没有任何干扰。比如A正在从一张银行卡中取钱，在A取钱的过程结束前，B不能向这张卡转账。

关于事务的隔离性数据库提供了多种隔离级别，稍后会介绍到。

持久性（Durability）

持久性是指一个事务一旦被提交了，那么对数据库中的数据的改变就是永久性的，即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作。

1.事务的并发？事务隔离级别，每个级别会引发什么问题，MySQL默认是哪个级别？

从理论上来说, 事务应该彼此完全隔离, 以避免并发事务所导致的问题，然而, 那样会对性能产生极大的影响, 因为事务必须按顺序运行， 在实际开发中, 为了提升性能, 事务会以较低的隔离级别运行，事务的隔离级别可以通过隔离事务属性指定。

2.1事务的并发问题

（1）脏读：事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的数据是脏数据

（2）不可重复读：事务 A 多次读取同一数据，事务 B 在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果因此本事务先后两次读到的数据结果会不一致。

（3）幻读：幻读解决了不重复读，保证了同一个事务里，查询的结果都是事务开始时的状态（一致性）。

例如：事务T1对一个表中所有的行的某个数据项做了从“1”修改为“2”的操作这时事务T2又对这个表中插入了一行数据项，而这个数据项的数值还是为“1”并且提交给数据库。而操作事务T1的用户如果再查看刚刚修改的数据，会发现还有跟没有修改一样，其实这行是从事务T2中添加的，就好像产生幻觉一样，这就是发生了幻读。

小结：不可重复读的和幻读很容易混淆，不可重复读侧重于修改，幻读侧重于新增或删除。解决不可重复读的问题只需锁住满足条件的行，解决幻读需要锁表。

2.2事务的隔离级别

事务隔离级别

面试被问MySQL总回答不好：100道MySQL面试题和21题MySQL性能优化

读未提交：另一个事务修改了数据，但尚未提交，而本事务中的SELECT会读到这些未被提交的数据脏读
不可重复读：事务 A 多次读取同一数据，事务 B 在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果因此本事务先后两次读到的数据结果会不一致。
可重复读：在同一个事务里，SELECT的结果是事务开始时时间点的状态，因此，同样的SELECT操作读到的结果会是一致的。但是，会有幻读现象
串行化：最高的隔离级别，在这个隔离级别下，不会产生任何异常。并发的事务，就像事务是在一个个按照顺序执行一样

MySQL默认的事务隔离级别为repeatable-read

MySQL 支持 4 中事务隔离级别.
事务的隔离级别要得到底层数据库引擎的支持, 而不是应用程序或者框架的支持.
Oracle 支持的 2 种事务隔离级别：READ_COMMITED , SERIALIZABLE

补充

SQL规范所规定的标准，不同的数据库具体的实现可能会有些差异
MySQL中默认事务隔离级别是“可重复读”时并不会锁住读取到的行

事务隔离级别：未提交读时，写数据只会锁住相应的行。
事务隔离级别为：可重复读时，写数据会锁住整张表。
事务隔离级别为：串行化时，读写数据都会锁住整张表。

隔离级别越高，越能保证数据的完整性和一致性，但是对并发性能的影响也越大，鱼和熊掌不可兼得啊。对于多数应用程序，可以优先考虑把数据库系统的隔离级别设为Read Committed，它能够避免脏读取，而且具有较好的并发性能。尽管它会导致不可重复读、幻读这些并发问题，在可能出现这类问题的个别场合，可以由应用程序采用悲观锁或乐观锁来控制。

4.数据库的乐观锁和悲观锁是什么？

　　数据库管理系统（DBMS）中的并发控制的任务是确保在多个事务同时存取数据库中同一数据时不破坏事务的隔离性和统一性以及数据库的统一性。

　　乐观并发控制(乐观锁)和悲观并发控制（悲观锁）是并发控制主要采用的技术手段。

悲观锁：假定会发生并发冲突，屏蔽一切可能违反数据完整性的操作
乐观锁：假设不会发生并发冲突，只在提交操作时检查是否违反数据完整性。

　　如果你对两种锁还不太熟悉，建议阅读：深入理解乐观锁与悲观锁

https://www.cnblogs.com/tinywan/p/9655664.html

6.简单说一说drop、delete与truncate的区别

　　SQL中的drop、delete、truncate都表示删除，但是三者有一些差别

delete和truncate只删除表的数据不删除表的结构
速度,一般来说: drop> truncate >delete
delete语句是dml,这个操作会放到rollback segement中,事务提交之后才生效;
如果有相应的trigger,执行的时候将被触发. truncate,drop是ddl, 操作立即生效,原数据不放到rollback segment中,不能回滚. 操作不触发trigger.

　　如果你对这三者的用法还不太熟悉，建议阅读： drop、truncate和delete的区别

7.超键、候选键、主键、外键分别是什么？

　　超键：在关系中能唯一标识元组的属性集称为关系模式的超键。一个属性可以为作为一个超键，多个属性组合在一起也可以作为一个超键。超键包含候选键和主键。

　　候选键：是最小超键，即没有冗余元素的超键。

　　主键：数据库表中对储存数据对象予以唯一和完整标识的数据列或属性的组合。一个数据列只能有一个主键，且主键的取值不能缺失，即不能为空值（Null）。

　　外键：在一个表中存在的另一个表的主键称此表的外键。

8.什么是视图？以及视图的使用场景有哪些？

　　视图是一种虚拟的表，具有和物理表相同的功能。可以对视图进行增，改，查，操作，试图通常是有一个表或者多个表的行或列的子集。对视图的修改不影响基本表。它使得我们获取数据更容易，相比多表查询。

只暴露部分字段给访问者，所以就建一个虚表，就是视图。
查询的数据来源于不同的表，而查询者希望以统一的方式查询，这样也可以建立一个视图，把多个表查询结果联合起来，查询者只需要直接从视图中获取数据，不必考虑数据来源于不同表所带来的差异

9.说一说三个范式。

　　第一范式（1NF）：数据库表中的字段都是单一属性的，不可再分。这个单一属性由基本类型构成，包括整型、实数、字符型、逻辑型、日期型等。

　　第二范式（2NF）：数据库表中不存在非关键字段对任一候选关键字段的部分函数依赖（部分函数依赖指的是存在组合关键字中的某些字段决定非关键字段的情况），也即所有非关键字段都完全依赖于任意一组候选关键字。

　　第三范式（3NF）：在第二范式的基础上，数据表中如果不存在非关键字段对任一候选关键字段的传递函数依赖则符合第三范式。所谓传递函数依赖，指的是如果存在"A → B → C"的决定关系，则C传递函数依赖于A。因此，满足第三范式的数据库表应该不存在如下依赖关系：关键字段 → 非关键字段 x → 非关键字段y

　　如果你对三个还不太了解，建议阅读：解释一下关系数据库的第一第二第三范式？

10.数据库优化

　　https://www.cnblogs.com/clsn/p/8214048.html

　　http://blog.itpub.net/31555484/viewspace-2565387/

11.数据库优化

　　SQL优化方向：执行计划、索引、SQL改写。

　　架构优化方向：高可用架构、高性能架构、分库分表。

15.存储过程:

存储过程：就是一块PLSQL语句包装起来，起个名称

语法上:相当于plsql语句戴个帽子。

相对而言:单纯plsql可以认为是匿名程序。

存储作用：

1，在开发程序中，为了一个特定的业务功能，会向数据库进行多次连接关闭(连接和关闭是很耗费资源)。这种就需要对数据库进行多次I/O读写，性能比较低。如果把这些业务放到PLSQL中，在应用程序中只需要调用PLSQL就可以做到连接关闭一次数据库就可以实现我们的业务,可以大大提高效率.

2， ORACLE官方给的建议：能够让数据库操作的不要放在程序中。在数据库中实现基本上不会出现错误，在程序中操作可以会存在错误.(如果在数据库中操作数据,可以有一定的日志恢复等功能.)

提示：

l plsql是存储过程的基础。

l java是不能直接调用plsql的，但可以通过存储过程这些对象来调用。

存储过程的语法:

create or replace PROCEDURE 过程名(参数名) AS/IS plsql子程序体

根据参数的类型，我们将其分为3类讲解：

l 不带参数的

l 带输入参数的

l 带输入输出参数的。

<1>无参存储:

创建存储:建议使用PROCEDURE这个窗口

create or replace procedure p_hello IS

begin

dbms_output.put('b'); --写入buffer但不输出

dbms_output.new_line; --回车(换行)，输出

dbms_output.put_line('hello world!'); --输出并换行

end p_hello

测试存储:

调用方法:

如何调用执行，两种方法：

l 一种是是用exec命令来调用—用来测试存储

exec过程名

l 一种是用其他的程序（plsql和java）来调用

l 程序调用

BEGIN

sayhelloworld;

END;

带书输入参数IN

示例

查询并打印某个员工（如7839号员工）的姓名和薪水--存储过程：要求，调用的时候传入员工编号，自动控制台打印:

create or replace procedure p_queryempsal(i_empno IN emp.empno%TYPE)

--声明变量

v_ename emp.empname%TYPE;

v_sal emp.empsal%TYPE;

select empname,empsal into v_ename,v_sal from emp where empno=i_empno;

dbms_output.put_line('姓名：'||v_ename||'，薪水：'||v_sal);

end p_queryempsal;

--命令调用

exec p_queryempsal(7878);

--程序调用

declare

i_empno emp.empno%TYPE:=8989;

BEGIN

p_queryempsal(i_empno );

END;

带输入参数IN和输出结果OUT-----主要是其他程序调用

示例:

----输入员工号查询某个员工（7839号（老大）员工）信息，要求，将薪水作为返回值输出，给调用的程序使用。

CREATE OR REPLACE PROCEDURE p_queryempsal_out( i_empno IN emp.empno%TYPE,o_sal OUT emp.sal%TYPE)

BEGIN

--赋值:将薪水的值赋给输出的参数o_sal

SELECT sal INTO o_sal FROM emp WHERE empno=i_empno;

END;

调用:

DECLARE

--输入参数值

v_empno emp.empno%TYPE:=7839;

--声明一个变量来接收输出参数

v_sal emp.sal%TYPE;

BEGIN

p_queryempsal_out(v_empno,v_sal);--第二个参数是输出的参数，必须有变量来接收！！

--当上面的语句执行之后，v_sal就有值了。

dbms_output.put_line('员工编号为：'||v_empno||'的薪资为：'||v_sal);

END;

java程序如何调用存储过程:

//获取连接

Connection conn = JDBCUtils.getConnection();

String sql="{call p_queryempsal_out(?,?)}";//转义sql

CallableStatement call = conn.prepareCall(sql);

//1.输入参数

call.setInt(1, 7839);//索引位置

call.registerOutParameter(2, OracleTypes.DOUBLE);//第一个参数是占位符，第二个参数数据类型

//执行存储

call.execute();//执行的时候，会自动将参数传入数据库,将输出参数返回的数据，封装会call对象中。

//获取输出参数的值

double sal = call.getDouble(2);

System.out.println("薪资是："+sal);

//释放资源

JDBCUtils.release(conn, call, null);

结果如下:

一、什么是存储过程：
存储过程
是在大型数据库系统中，一组为了完成特定功能的SQL 语句集，存储在数据库中，经过第一次编译后再次调用不需要再次编译，
用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。存储过程是数据库中的一个重要对象，任何一个设计良好的数据库应用程序都应该用到存储过程。
二、为什么要用存储过程呢？
存储过程真的那么重要吗，它到底有什么好处呢？存储过程说白了就是一堆SQL 的合并。中间加了点逻辑控制。
1.存储过程处理比较复杂的业务时比较实用。具体分为两个方面：
(一)响应时间上来说有
优势：如果你在前台处理的话。可能会涉及到多次数据库连接。但如果你用存储过程的话，就只有一次。存储过程可以给我们带来运行效率提高的好处；
(二)、从安全上使用了存储过程的系统更加稳定：程序容易出现BUG 不稳定，而存储过程，只要数据库不出现问题，基本上是不会出现什么问题的。
2.数据量小的项目不用存储过程也可以正常运作。
三、那么什么时候才需要用存储过程？
存储过程不仅仅适用于大型项目，对于中小型项目，使用存储过程也是非常有必要的。
其优势主要体现在：
1.存储过程只在创造时进行编译，以后每次执行存储过程都不需再重新编译，而一般SQL 语句每执行一次就编译一次所以使用存储过程可提高数据库执行速度。
2.当对数据库进行复杂操作时(如对多个表进行Update,Insert,Query,Delete 时）可将此复杂操作用存储过程封装起来与数据库提供的事务处理结合一起使用。
这些操作，如果用程序来完成，就变成了一条条的SQL 语句，可能要多次连接数据库。而换成存储，只需要连接一次数据库就可以了。
3.存储过程可以重复使用
可减少数据库开发人员的工作量。
4.安全性高
可设定只有某此用户才具有对指定存储过程的使用权。
5.更强的适应性：由于存储过程对数据库的访问是通过存储过程来进行的，因此数据库开发
人员可以在不改动存储过程接口的情况下对数据库进行任何改动，
而这些改动不会对应用程
序造成影响。
6.分布式工作：应用程序和数据库的编码工作可以分别独立进行，而不会相互压制。
一般来说，存储过程的编写比基本
SQL
语句复杂，编写存储过程需要更高的技能，更
丰富的经验。
四、系统开发中存储过程使用的优势和劣势
优点如下：
1.执行效率高.
2.安全性能好。
3.对于一些场合非常容易实现需求。

缺点如下：
1.可维护性比较差。
2.可读性也差。

因此我个人认为在开发系统时存储过程不要滥用，用多了后期维护就比较麻烦了，有些
能够在代码中实现而且对系统性能影响不大的操作就不用写在存储过程中了，而且考虑发布
容易的问题，可以考虑在服务端完成一些业务操作，使用服务代替一部分存储过程的功能