MySQL数据库常考面试题

基础篇

1.数据库的三大范式

第一范式：原子性，字段不可分割

第二范式：就是完全依赖，不能部分依赖

第三范式：不能存在传递依赖

第一范式1NF

确保数据库表字段的原子性。

比如字段 userInfo: 广东省 10086' ，依照第一范式必须拆分成 userInfo: 广东省 userTel:10086两个字段。

第二范式2NF

首先要满足第一范式，另外包含两部分内容，一是表必须有一个主键；二是非主键列必须完全依赖于主键，而不能只依赖于主键的一部分。

举个例子。假定选课关系表为student_course(student_no, student_name, age, course_name, grade, credit)，主键为(student_no, course_name)。其中学分完全依赖于课程名称，姓名年龄完全依赖学号，不符合第二范式，会导致数据冗余（学生选n门课，姓名年龄有n条记录）、插入异常（插入一门新课，因为没有学号，无法保存新课记录）等问题。可以拆分成三个表：学生：student(stuent_no, student_name, 年龄)；课程：course(course_name, credit)；选课关系：student_course_relation(student_no, course_name, grade)。

第三范式3NF

首先要满足第二范式，另外非主键列必须直接依赖于主键，不能存在传递依赖。即不能存在：非主键列 A 依赖于非主键列 B，非主键列 B 依赖于主键的情况。

假定学生关系表为Student(student_no, student_name, age, academy_id, academy_telephone)，主键为"学号"，其中学院id依赖于学号，而学院地点和学院电话依赖于学院id，存在传递依赖，不符合第三范式。

可以把学生关系表分为如下两个表：学生：(student_no, student_name, age, academy_id)；学院：(academy_id, academy_telephone)。

2.主键和外键

主键：能够唯一表示数据表中的每条记录的字段或者字段的组合就称为主键。不能有重复的，不允许为空 。

外键：若有两个表A，B，x是A的主键，而B中也有x字段，则x就是表B的外键，外键约束主要用来维护两个表之间数据的一致性。外键可以有重复的, 可以是空值。

关系：外键一定是另外某个表的主键。

3.SQL语句分类

4.SQL基本语句

数据操作语言 (DML)：

SELECT - 从数据库表中获取数据

UPDATE - 更新数据库表中的数据

DELETE - 从数据库表中删除数据

INSERT INTO - 向数据库表中插入数据

数据定义语言 (DDL) :

CREATE DATABASE - 创建新数据库

ALTER DATABASE - 修改数据库

CREATE TABLE - 创建新表

ALTER TABLE - 变更（改变）数据库表

DROP TABLE - 删除表

CREATE INDEX - 创建索引（搜索键）

DROP INDEX - 删除索引

数据查询语言（DQL）:

SELECT
字段列表
FROM
表名列表
WHERE
条件列表
GROUP BY
分组字段列表
HAVING
分组后条件列表
ORDER BY
排序字段列表
LIMIT
分页参数

数据控制语言(DCL):

   查询用户

select * from mysql.user;

   创建用户

CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';

  修改用户密码

ALTER USER '用户名'@'主机名' IDENTIFIED WITH mysql_native_password BY '新密码' ;

  删除用户

DROP USER '用户名'@'主机名' ;

5.函数

MySQL 中的函数主要分为以下四类： 字符串函数、数值函数、日期函数、流程函数。

字符串函数

数值函数

日期函数

流程函数

6.约束

概念：约束是作用于表中字段上的规则，用于限制存储在表中的数据。

目的：保证数据库中数据的正确、有效性和完整性。

分类 :

注意：约束是作用于表中字段上的，可以在创建表 / 修改表的时候添加约束。

7.多表查询

7.1 多表关系

项目开发中，在进行数据库表结构设计时，会根据业务需求及业务模块之间的关系，分析并设计表结 构，由于业务之间相互关联，所以各个表结构之间也存在着各种联系，基本上分为三种：

• 一对多(多对一)
• 多对多
• 一对一

7.2 多表查询分类

连接查询

1. 内连接：相当于查询A、B交集部分数据
2. 外连接：
   1. 左外连接：查询左表所有数据，以及两张表交集部分数据
   2. 右外连接：查询右表所有数据，以及两张表交集部分数据
3. 自连接：当前表与自身的连接查询，自连接必须使用表别名

子查询

7.3子查询概述

SELECT * FROM t1 WHERE column1 = ( SELECT column1 FROM t2 ); 

8.事务

        事务 是一组操作的集合，它是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系 统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败。

9.事务的四大特性？

事务必须满足的4个条件：原子性、一致性、隔离性、持久性。

事务特性ACID：原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Durability）。

• 原子性（Atomicity）：事务是不可分割的最小操作单元，要么全部成功，要么全部失败。
• 一致性（Consistency）：事务完成时，必须使所有的数据都保持一致状态。
• 隔离性（Isolation）：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立 环境下运行。
• 持久性（Durability）：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的

10.并发事务问题

1. 赃读：一个事务读到另外一个事务还没有提交的数据。
2. 不可重复读：一个事务先后读取同一条记录，但两次读取的数据不同，称之为不可重复读。
3. 幻读：一个事务按照条件查询数据时，没有对应的数据行，但是在插入数据时，又发现这行数据已经存在，好像出现了 " 幻影 " 。

11.事务隔离级别

为了解决并发事务所引发的问题，在数据库中引入了事务隔离级别。主要有以下几种：

1). 查看事务隔离级别

SELECT @@TRANSACTION_ISOLATION; 1

2). 设置事务隔离级别

SET [ SESSION | GLOBAL ] TRANSACTION ISOLATION LEVEL { READ UNCOMMITTED |
READ COMMITTED | REPEATABLE READ | SERIALIZABLE }

注意：事务隔离级别越高，数据越安全，但是性能越低。

进阶篇

1.MySQL体系结构

1). 连接层

        最上层是一些客户端和链接服务，包含本地sock 通信和大多数基于客户端 / 服务端工具实现的类似于 TCP/IP的通信。主要完成一些类似于连接处理、授权认证、及相关的安全方案。在该层上引入了线程 池的概念，为通过认证安全接入的客户端提供线程。同样在该层上可以实现基于SSL 的安全链接。服务器也会为安全接入的每个客户端验证它所具有的操作权限。

2). 服务层

        第二层架构主要完成大多数的核心服务功能，如SQL 接口，并完成缓存的查询， SQL 的分析和优化，部 分内置函数的执行。所有跨存储引擎的功能也在这一层实现，如 过程、函数等。在该层，服务器会解 析查询并创建相应的内部解析树，并对其完成相应的优化如确定表的查询的顺序，是否利用索引等， 最后生成相应的执行操作。如果是select 语句，服务器还会查询内部的缓存，如果缓存空间足够大， 这样在解决大量读操作的环境中能够很好的提升系统的性能。

3). 引擎层

        存储引擎层， 存储引擎真正的负责了MySQL 中数据的存储和提取，服务器通过 API 和存储引擎进行通 信。不同的存储引擎具有不同的功能，这样我们可以根据自己的需要，来选取合适的存储引擎。数据库 中的索引是在存储引擎层实现的。

4). 存储层 数据存储层

        主要是将数据( 如 : redolog 、 undolog 、数据、索引、二进制日志、错误日志、查询日志、慢查询日志等) 存储在文件系统之上，并完成与存储引擎的交互。

        和其他数据库相比，MySQL 有点与众不同，它的架构可以在多种不同场景中应用并发挥良好作用。主要 体现在存储引擎上，插件式的存储引擎架构，将查询处理和其他的系统任务以及数据的存储提取分离。 这种架构可以根据业务的需求和实际需要选择合适的存储引擎。

2.存储引擎

        存储引擎就是存储数据、建立索引、更新/ 查询数据等技术的实现方式 。存储引擎是基于表的，而不是 基于库的，所以存储引擎也可被称为表类型。我们可以在创建表的时候，来指定选择的存储引擎，如果没有指定将自动选择默认的存储引擎。

3.存储引擎选择

在选择存储引擎时，应该根据应用系统的特点选择合适的存储引擎。对于复杂的应用系统，还可以根据实际情况选择多种存储引擎进行组合。

• InnoDB: 是Mysql的默认存储引擎，支持事务、外键。如果应用对事务的完整性有比较高的要 求，在并发条件下要求数据的一致性，数据操作除了插入和查询之外，还包含很多的更新、删除操作，那么InnoDB存储引擎是比较合适的选择。
• MyISAM ： 如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不是很高，那么选择这个存储引擎是非常合适的。
• MEMORY：将所有数据保存在内存中，访问速度快，通常用于临时表及缓存。MEMORY的缺陷就是对表的大小有限制，太大的表无法缓存在内存中，而且无法保障数据的安全性。

4.索引概述

介绍

索引（index）是帮助MySQL高效获取数据的数据结构(有序) 。在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据， 这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。

优缺点：

5.索引结构

MySQL的索引是在存储引擎层实现的，不同的存储引擎有不同的索引结构，主要包含以下几种：

上述是 MySQL 中所支持的所有的索引结构，接下来，我们再来看看不同的存储引擎对于索引结构的支持情况

注意： 我们平常所说的索引，如果没有特别指明，都是指B+树结构组织的索引。

二叉树

假如说 MySQL 的索引结构采用二叉树的数据结构，比较理想的结构如下：

如果主键是顺序插入的，则会形成一个单向链表，结构如下：

如果选择二叉树作为索引结构，会存在以下缺点：

• 顺序插入时，会形成一个链表，查询性能大大降低。
• 大数据量情况下，层级较深，检索速度慢。

红黑树

此时大家可能会想到，我们可以选择红黑树，红黑树是一颗自平衡二叉树，那这样即使是顺序插入数 据，最终形成的数据结构也是一颗平衡的二叉树, 结构如下 :

但是，即使如此，由于红黑树也是一颗二叉树，所以也会存在一个缺点： 大数据量情况下，层级较深，检索速度慢。

所以，在 MySQL 的索引结构中，并没有选择二叉树或者红黑树，而选择的是 B+Tree ，那么什么是

B+Tree 呢？在详解 B+Tree 之前，先来介绍一个 B-Tree

B-Tree

B-Tree ， B 树是一种多叉路衡查找树，相对于二叉树， B 树每个节点可以有多个分支，即多叉。

以一颗最大度数（ max-degree ）为 5(5 阶 ) 的 b-tree 为例，那这个 B 树每个节点最多存储 4 个 key ， 5

个指针

知识小贴士 : 树的度数指的是一个节点的子节点个数。

特点：

• 5阶的B树，每一个节点最多存储4个key，对应5个指针。
• 一旦节点存储的key数量到达5，就会裂变，中间元素向上分裂。
• 在B树中，非叶子节点和叶子节点都会存放数据

B+Tree

B+Tree 是 B-Tree 的变种，我们以一颗最大度数（ max-degree ）为 4 （4阶）的 b+tree 为例，来看一下其结构示意图：

我们可以看到，两部分：

• 绿色框框起来的部分，是索引部分，仅仅起到索引数据的作用，不存储数据。
• 红色框框起来的部分，是数据存储部分，在其叶子节点中要存储具体的数据。

最终我们看到， B+Tree 与 B-Tree 相比，主要有以下三点区别：

• 所有的数据都会出现在叶子节点。
• 叶子节点形成一个单向链表。
• 非叶子节点仅仅起到索引数据作用，具体的数据都是在叶子节点存放的。

MySQL 索引数据结构对经典的 B+Tree 进行了优化。在原 B+Tree 的基础上，增加一个指向相邻叶子节点 的链表指针，就形成了带有顺序指针的B+Tree ，提高区间访问的性能，利于排序。

Hash

MySQL 中除了支持 B+Tree 索引，还支持一种索引类型 ---Hash 索引。

1). 结构

哈希索引就是采用一定的 hash 算法，将键值换算成新的 hash 值，映射到对应的槽位上，然后存储在 hash表中

如果两个 ( 或多个 ) 键值，映射到一个相同的槽位上，他们就产生了 hash 冲突（也称为 hash 碰撞），可以通过链表来解决。

2). 特点

A. Hash 索引只能用于对等比较 (= ， in) ，不支持范围查询（ between ， > ， < ， ... ）

B. 无法利用索引完成排序操作

C. 查询效率高，通常 ( 不存在 hash 冲突的情况 ) 只需要一次检索就可以了，效率通常要高于 B+tree 索

引

3). 存储引擎支持

在 MySQL 中，支持 hash 索引的是 Memory 存储引擎。 而 InnoDB 中具有自适应 hash 功能， hash 索引是 InnoDB存储引擎根据 B+Tree 索引在指定条件下自动构建的。

6.为什么InnoDB存储引擎选择使用B+tree索引结构?

A. 相对于二叉树，层级更少，搜索效率高；

B. 对于 B-tree ，无论是叶子节点还是非叶子节点，都会保存数据，这样导致一页中存储 的键值减少，指针跟着减少，要同样保存大量数据，只能增加树的高度，导致性能降低；

C. 相对 Hash 索引， B+tree 支持范围匹配及排序操作；

7.索引的作用？

数据是存储在磁盘上的，查询数据时，如果没有索引，会加载所有的数据到内存，依次进行检索，读取磁盘次数较多。有了索引，就不需要加载所有数据，因为B+树的高度一般在2-4层，最多只需要读取2-4次磁盘，查询速度大大提升。

8.索引分类

 

9.聚集索引&二级索引

聚集索引选取规则:

• 如果存在主键，主键索引就是聚集索引。
• 如果不存在主键，将使用第一个唯一（UNIQUE）索引作为聚集索引。
• 如果表没有主键，或没有合适的唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚集索

引。

10.什么情况下需要建索引？

经常用于查询的字段，经常用于连接的字段建立索引，可以加快连接的速度，经常需要排序的字段建立索引，因为索引已经排好序，可以加快排序查询速度。

11.什么情况下不建索引？

where条件中用不到的字段不适合建立索引，表记录较少，需要经常增删改，参与列计算的列不适合建索引，区分度不高的字段不适合建立索引，如性别等。

15.索引语法

#创建索引
CREATE [ UNIQUE | FULLTEXT ] INDEX index_name ON table_name (
index_col_name,... ) ;

#查看索引
SHOW INDEX FROM table_name ;

#删除索引
DROP INDEX index_name ON table_name ;

16.SQL优化

1. 在项目上线初期，业务数据量相对较少，SQL的执行效率对程序运行效率的影响可能不太明显，因此开发和运维人员可能无法判断SQL对程序的运行效率有多大。但随着时间的积累，业务数据量的增多，SQL的执行效率对程序的运行效率的影响逐渐增大，此时对SQL的优化就很有必要。
2. 优化SQL可以使其更有效地使用索引、减少硬盘I/O等，从而提高程序的运行效率。
3. 优化SQL可以使其更好地利用缓存，从而降低程序的响应时间。
4. 优化SQL可以使其更好地处理大数据量，从而减少程序的运行时间。
5. 优化SQL可以使其更符合程序的业务需求，从而提高程序的性能和用户体验。

插入数据优化

1. 批量插入数据
2. 手动控制事务
3. 主键顺序插入，性能要高于乱序插入。

主键优化

1. 数据组织方式
2. 页分裂
3. 页合并
4. 索引设计原则

order by优化

1. Using filesort : 通过表的索引或全表扫描，读取满足条件的数据行，然后在排序缓冲区sort buffer中完成排序操作，所有不是通过索引直接返回排序结果的排序都叫 FileSort 排序。
2. Using index : 通过有序索引顺序扫描直接返回有序数据，这种情况即为 using index，不需要 额外排序，操作效率高。

对于以上的两种排序方式， Using index 的性能高，而 Using filesort 的性能低，我们在优化排序

操作时，尽量要优化为 Using index 。

group by优化

在分组操作中，我们需要通过以下两点进行优化，以提升性能：

A. 在分组操作时，可以通过索引来提高效率。

B. 分组操作时，索引的使用也是满足最左前缀法则的。

limit优化

在数据量比较大时，如果进行limit分页查询，在查询时，越往后，分页查询效率越低。

优化思路 : 一般分页查询时，通过创建 覆盖索引 能够比较好地提高性能，可以通过覆盖索引加子查

询形式进行优化

count优化

如果数据量很大，在执行count操作时，是非常耗时的。

主要的优化思路：自己计数

update优化

17.视图

        视图（View ）是一种 虚拟存在的表 。视图中的数据并不在数据库中实际存在，行和列数据来自定义视图的查询中使用的表，并且是在使用视图时动态生成的。 通俗的讲，视图只保存了查询的SQL 逻辑，不保存查询结果。所以我们在创建视图的时候，主要的工作就落在创建这条SQL 查询语句上。

18.存储过程

        存储过程是事先经过编译并存储在数据库中的一段 SQL 语句的集合，调用存储过程可以简化应用开发人员的很多工作，减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效率是有好处的。存储过程思想上很简单，就是数据库 SQL 语言层面的代码封装与重用。

特点：

• 封装，复用 -----------------------> 可以把某一业务SQL封装在存储过程中，需要用到的时候直接调用即可。
• 可以接收参数，也可以返回数据 --------> 再存储过程中，可以传递参数，也可以接收返回值。
• 减少网络交互，效率提升 -------------> 如果涉及到多条SQL，每执行一次都是一次网络传输。 而如果封装在存储过程中，我们只需要网络交互一次可能就可以了。

19.触发器

触发器是与表有关的数据库对象 ，指在insert/update/delete 之前 (BEFORE) 或之后 (AFTER) ， 触发并执行触发器中定义的SQL语句集合 。触发器的这种特性可以协助应用在数据库端确保数据的完整性 , 日志记录 , 数据校验等操作。

使用别名 OLD 和 NEW 来引用触发器中发生变化的记录内容，这与其他的数据库是相似的。现在触发器还只支持行级触发，不支持语句级触发。

语法

#创建
CREATE TRIGGER trigger_name
BEFORE/AFTER INSERT/UPDATE/DELETE
ON tbl_name FOR EACH ROW -- 行级触发器
BEGIN
trigger_stmt ;
END;

#查看
SHOW TRIGGERS ; 

#删除
DROP TRIGGER [schema_name.]trigger_name ; -- 如果没有指定 schema_name，默认为当前数
据库 。

20.锁

        锁是 计算机协调多个进程或线程并发访问某一资源的机制 。在数据库中，除传统的计算资源（CPU、 RAM、 I/O ）的争用以外，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说，锁对数据库而言显得尤其重要，也更加复杂。

MySQL 中的锁，按照锁的粒度分，分为以下三类：

• 全局锁：锁定数据库中的所有表。
• 表级锁：每次操作锁住整张表。
• 行级锁：每次操作锁住对应的行数据。

21.数据库在什么情况下会发生死锁？

数据库发生死锁的主要情况是在多个并发事务同时访问数据库资源时，出现了循环等待的情况。以下是一些可能导致数据库发生死锁的情况：

1. 事务之间的交叉依赖：当多个事务按照不同的顺序请求和释放资源，并且存在环路依赖关系时，可能会导致死锁。

2. 数据库锁定策略不当：如果数据库的锁定策略过于保守，即对资源的锁定范围过大或时间过长，就会增加死锁的可能性。

3. 并发访问资源：当多个事务同时请求相同的资源，并且每个事务在持有自己的锁的同时还需要其他事务持有的锁时，可能会进入死锁状态。

4. 长时间的事务处理：如果某个事务执行时间过长，在等待资源的过程中可能会引发其他事务的死锁。

需要注意的是，死锁并不是所有情况下都会发生，它是由于特定的并发访问模式和资源争用导致的。因此，在设计数据库和应用程序时，应该考虑避免死锁的可能性，采取相应的措施来管理并发访问。

22.说说数据库死锁的解决办法

数据库死锁是指两个或多个事务在等待对方释放资源时被阻塞的情况。要解决数据库死锁问题，可以采取以下几种方法：

1. 优化查询语句，减少锁定资源的时间和范围，以减少死锁的可能性。

2. 加强事务的隔离级别，例如将隔离级别提高到SERIALIZABLE，以减少死锁的发生。

3. 在应用程序中使用合适的锁定机制，例如悲观锁或乐观锁，以避免死锁的发生。

4. 通过增加资源或调整资源分配来减少死锁的可能性。例如，增加内存、CPU 或磁盘空间等。

5. 在数据库中设置超时时间，当事务长时间等待时，自动回滚事务，以避免死锁的发生。