本文参考 嗨客网 Java 随笔

前言

本章节记录了一些常见的 MySQL 面试题及详细答案，目录如下：

文章目录

• 前言
• MySQL常见面试题
• • Mysql内连接与外连接区别
  • 什么是数据库范式
  • MySQL权限控制
  • MySQL事务ACID
  • MySQL事务隔离级别
  • 什么是MySQL MVCC
  • 什么是MySQL binlog
  • MySQL锁
  • MySQL索引
  • MySQL索引原理
  • Mysql explain使用
  • MySQL索引优化与SQL优化
  • Mysql聚簇索引
  • 平衡二叉树B树和B+树
  • Mysql死锁解决
  • Mysql慢查询日志
  • innodb引擎四大特性
  • MySQL各引擎之间区别
  • MySQL innodb与myisam引擎区别
  • MySQL innodb与myisam引擎区别
  • MySQL Select for update
  • MySQL乐观锁与悲观锁
  • MySQL触发器
  • MySQL存储过程
  • MySQL视图
  • MySQL查询缓存
  • 更多

MySQL常见面试题

Mysql内连接与外连接区别

• 内连接：指连接结果仅包含符合连接条件的行，参与连接的两个表都应该符合连接条件。
• 外连接：连接结果不仅包含符合连接条件的行同时也包含自身不符合条件的行。包括左外连接、右外连接和全外连接。
• 左外连接：左边表数据行全部保留，右边表保留符合连接条件的行。
• 右外连接：右边表数据行全部保留，左边表保留符合连接条件的行。
• 全外连接：左外连接 union 右外连接，Mysql 中暂不支持。

详细说明：链接

什么是数据库范式

设计关系数据库时，遵从不同的规范要求，设计出合理的关系型数据库，这些不同的规范要求被称为不同的范式，各种范式呈递次规范，越高的范式数据库冗余越小。范式来自英文 Normal form，简称 NF。

要想设计—个好的关系，必须使关系满足一定的约束条件，此约束已经形成了规范，分成几个等级，一级比一级要求得严格。满足这些规范的数据库是简洁的、结构明晰的，同时，不会发生插入(insert)、删除(delete)和更新(update)操作异常。

目前关系数据库有六种范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF）、第四范式(4NF）和第五范式（5NF，又称完美范式）。满足最低要求的范式是第一范式（1NF）。在第一范式的基础上进一步满足更多规范要求的称为第二范式（2NF），其余范式以次类推。一般来说，数据库只需满足第三范式(3NF）就行了。

详细说明：链接

MySQL权限控制

对 MySQL 的权限的控制可以大致分为三个层面，即连接层面、数据库层面以及数据表层面，具体解释如下：

1. 连接层面，即连接上允不允许（是否能登录到 MySQL 服务器）。
2. 数据库层面上，即允许用户操作哪些数据库。
3. 数据表层面上，允许用户操作哪些表，并可以定义对表的操作权限：比如 insert，create，update 等。如果还觉得不够精细，MySQL 还可以精确到对某表某列控制操作权限。

详细说明：链接

MySQL事务ACID

什么是事务？

事务（Transaction）是访问和更新数据库的程序执行单元；事务中可能包含一个或多个 sql 语句，这些语句要么都执行，要么都不执行。作为一个关系型数据库，MySQL 支持事务。

详细说明：链接

MySQL事务隔离级别

MySQL 的事务隔离级别可以分为四个等级，即：READ UNCOMMITTED（读未提交数据）、READ COMMITTED（读已提交数据）、REPEATABLE READ（可重复读）和 SERIALIZABLE（串行化）。

详细说明：链接

什么是MySQL MVCC

MVCC 就是多版本并发控制。MVCC 是一种并发控制的方法，一般在数据库管理系统中，实现对数据库的并发访问。

详细说明：链接

什么是MySQL binlog

MySQL 的 binlog 是记录所有数据库表结构变更（例如 CREATE、ALTER TABLE）以及表数据修改（INSERT、UPDATE、DELETE）的二进制日志。binlog 不会记录 SELECT 和 SHOW 这类操作，因为这类操作对数据本身并没有修改，但你可以通过查询通用日志来查看 MySQL 执行过的所有语句。

二进制日志包括两类文件：二进制日志索引文件（文件名后缀为 .index）用于记录所有的二进制文件，二进制日志文件（文件名后缀为 .00000*）记录数据库所有的 DDL 和 DML(除了数据查询语句)语句事件。

MySQL binlog 以事件形式记录，还包含语句所执行的消耗的时间，MySQL 的二进制日志是事务安全型的。binlog 的主要目的是复制和恢复。

详细说明：链接

MySQL锁

锁是计算机协调多个进程或纯线程并发访问某一资源的机制。在数据库中，除传统的计算资源（CPU、RAM、I/O）的争用以外，数据也是一种供许多用户共享的资源。

如何保证数据并发访问的一致性、有效性是所在有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说，锁对数据库而言显得尤其重要，也更加复杂。

详细说明：链接

MySQL索引

用来加快查询的技术很多，其中最重要的是索引。通常索引能够快速提高查询速度。如果不适用索引，MySQL 必须从第一条记录开始然后读完整个表直到找出相关的行。表越大，花费的时间越多。但也不全是这样。

MySQL 官方对索引的定义为：索引（Index）是帮助 MySQL 高效获取数据的数据结构。提取句子主干，就可以得到索引的本质：索引是数据结构。

数据库查询是数据库的最主要功能之一。我们都希望查询数据的速度能尽可能的快，因此数据库系统的设计者会从查询算法的角度进行优化。最基本的查询算法当然是顺序查找（linear search），这种复杂度为 O(n) 的算法在数据量很大时显然是糟糕的，好在计算机科学的发展提供了很多更优秀的查找算法，例如二分查找（binary search）、二叉树查找（binary tree search）等。

如果稍微分析一下会发现，每种查找算法都只能应用于特定的数据结构之上，例如二分查找要求被检索数据有序，而二叉树查找只能应用于二叉查找树上，但是数据本身的组织结构不可能完全满足各种数据结构（例如，理论上不可能同时将两列都按顺序进行组织），所以，在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法。这种数据结构，就是索引。

详细说明：链接

MySQL索引原理

索引是一种数据结构，用于帮助我们在大量数据中快速定位到我们想要查找的数据。索引最形象的比喻就是图书的目录了。注意这里的大量，数据量大了索引才显得有意义，如果我想要在 [1,2,3,4] 中找到 4 这个数据，直接对全数据检索也很快，没有必要费力气建索引再去查找。

详细说明：链接

Mysql explain使用

MySQL 中的 explain 命令显示了 mysql 如何使用索引来处理 select 语句以及连接表。explain 显示的信息可以帮助选择更好的索引和写出更优化的查询语句。

详细说明：链接

MySQL索引优化与SQL优化

日常在 CURD 的过程中，都避免不了跟数据库打交道，大多数业务都离不开数据库表的设计和 SQL 的编写，那如何让你编写的SQL语句性能更优呢？先来整体看下 MySQL 逻辑架构图：

MySQL 整体逻辑架构图可以分为 Server 和存储引擎层。

详细说明：链接

Mysql聚簇索引

聚簇索引是将数据存储与索引放到了一块，索引结构的叶子节点保存了行数据。非聚簇索引是指将数据与索引分开存储，索引结构的叶子节点指向了数据对应的位置。

详细说明：链接

平衡二叉树B树和B+树

平衡二叉树是基于二分法的策略提高数据的查找速度的二叉树的数据结构。

特点
平衡二叉树是采用二分法思维把数据按规则组装成一个树形结构的数据，用这个树形结构的数据减少无关数据的检索，大大的提升了数据检索的速度；平衡二叉树的数据结构组装过程有以下规则：

非叶子节点只能允许最多两个子节点存在。

每一个非叶子节点数据分布规则为左边的子节点小当前节点的值，右边的子节点大于当前节点的值(这里值是基于自己的算法规则而定的，比如 hash 值)。

详细说明：链接

Mysql死锁解决

所谓死锁：是指两个或两个以上的进程在执行过程中，因争夺资源而造成的一种互相等待的现象，若无外力作用，它们都将无法推进下去。此时称系统处于死锁状态或系统产生了死锁，这些永远在互相等待的进程称为死锁进程。

由于资源占用是互斥的，当某个进程提出申请资源后，使得有关进程在无外力协助下，永远分配不到必需的资源而无法继续运行，这就产生了一种特殊现象死锁。

详细说明：链接

Mysql慢查询日志

MySQL 的慢查询日志是 MySQL 提供的一种日志记录，它用来记录在 MySQL 中响应时间超过阀值的语句，具体指运行时间超过 long_query_time 值的 SQL，则会被记录到慢查询日志中。long_query_time 的默认值为 10，意思是运行 10S 以上的语句。

默认情况下，Mysql 数据库并不启动慢查询日志，需要我们手动来设置这个参数，当然，如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。慢查询日志支持将日志记录写入文件，也支持将日志记录写入数据库表。

详细说明：链接

innodb引擎四大特性

innodb引擎四大特性
innodb 的引擎四大特性为：插入缓冲，二次写，自适应哈希和预读。

详细说明：链接

MySQL各引擎之间区别

InnoDB 是 MySQL 默认的事务型引擎，也是最重要、最广泛的存储引擎。它的设计是用来处理大量短期事务，短期事务大部分是正常提交的，很少回滚。InnoDB 的性能和自动崩溃恢复特性，使得它在非事务型存储的需求中，也很流行。除了非常特别的原因需要使用其他引擎，InnoDB 也是非常好值得花时间研究的对象。

详细说明：链接

MySQL innodb与myisam引擎区别

MyISAM 是 MySQL 的默认数据库引擎（5.5 版之前），由早期的 ISAM（Indexed Sequential Access Method：有索引的顺序访问方法）所改良。虽然性能极佳，但却有一个缺点：不支持事务处理（transaction）。不过，在这几年的发展下，MySQL 也导入了 InnoDB（另一种数据库引擎），以强化参考完整性与并发违规处理机制，后来就逐渐取代 MyISAM。

详细说明：链接

MySQL innodb与myisam引擎区别

MyISAM引擎

MyISAM 是 MySQL 的默认数据库引擎（5.5 版之前），由早期的 ISAM（Indexed Sequential Access Method：有索引的顺序访问方法）所改良。虽然性能极佳，但却有一个缺点：不支持事务处理（transaction）。不过，在这几年的发展下，MySQL 也导入了 InnoDB（另一种数据库引擎），以强化参考完整性与并发违规处理机制，后来就逐渐取代 MyISAM。

InnoDB引擎

InnoDB，是 MySQL 的数据库引擎之一，为 MySQL AB 发布 binary 的标准之一。InnoDB 由 Innobase Oy 公司所开发，2006 年五月时由甲骨文公司并购。与传统的 ISAM 与 MyISAM 相比，InnoDB 的最大特色就是支持了 ACID 兼容的事务（Transaction）功能，类似于 PostgreSQL。目前 InnoDB 采用双轨制授权，一是 GPL 授权，另一是专有软件授权。

详细说明：链接

MySQL Select for update

Select for update定义
for update 是一种行级锁，又叫排它锁。一旦用户对某个行施加了行级加锁，则该用户可以查询也可以更新被加锁的数据行，其它用户只能查询但不能更新被加锁的数据行。

如果其它用户想更新该表中的数据行，则也必须对该表施加行级锁。即使多个用户对一个表均使用了共享更新，但也不允许两个事务同时对一个表进行更新，真正对表进行更新时，是以独占方式锁表，一直到提交或复原该事务为止。

详细说明：链接

MySQL乐观锁与悲观锁

什么是悲观锁
悲观锁的特点是先获取锁，再进行业务操作，即 “悲观” 的认为获取锁是非常有可能失败的，因此要先确保获取锁成功再进行业务操作。通常所说的 “一锁二查三更新” 即指的是使用悲观锁。

通常来讲在数据库上的悲观锁需要数据库本身提供支持，即通过常用的 select … for update 操作来实现悲观锁。　当数据库执行 select for update 时会获取被 select 中的数据行的行锁，因此其他并发执行的 select for update 如果试图选中同一行则会发生排斥（需要等待行锁被释放），因此达到锁的效果。select for update 获取的行锁会在当前事务结束时自动释放，因此必须在事务中使用。

mysql 还有个问题是 select… for update 语句执行中，如果数据表没有添加索引或主键，所有扫描过的行都会被锁上，这一点很容易造成问题。因此如果在 mysql 中用悲观锁务必要确定走了索引，而不是全表扫描。

详细说明：链接

MySQL触发器

什么是触发器

触发器（trigger）是 MySQL 提供给程序员和数据分析员来保证数据完整性的一种方法，它是与表事件相关的特殊的存储过程，它的执行不是由程序调用，也不是手工启动，而是由事件来触发，比如当对一个表进行操作（insert，delete， update）时就会激活它执行。简单理解为：你执行一条 sql 语句，这条 sql 语句的执行会自动去触发执行其他的 sql 语句。

详细说明：链接

MySQL存储过程

什么是存储过程
存储过程（Stored Procedure）是一组为了完成特定功能的 SQL 语句集，存储在数据库中，经过第一次编译后再次调用不需要再次编译，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。类似于 Java 中的方法，定义好之后可以多次调用。

详细说明：链接

MySQL视图

什么是视图
视图是一个虚拟表，是从数据库中一个或多个表中导出来的表，其内容由查询定义。同真实表一样，视图包含一系列带有名称的列和行数据。但是，数据库中只存放了视图的定义，而并没有存放视图中的数据。这些数据存放在原来的表中。使用视图查询数据时，数据库系统会从原来的表中取出对应的数据。因此，视图中的数据是依赖于原来的表中的数据的。一旦表中的数据发生改变，显示在视图中的数据也会发生改变。

视图是存储在数据库中的查询的 SQL 语句，它主要出于两种原因：安全原因，视图可以隐藏一些数据，例如，员工信息表，可以用视图只显示姓名、工龄、地址，而不显示社会保险号和工资数等；另一个原因是可使复杂的查询易于理解和使用。

详细说明：链接

MySQL查询缓存

什么是MySQL查询缓存
MySQL 缓存机制就是缓存 sql 文本及缓存结果，用 KV 形式保存再服务器内存中，如果运行相同的 sql，服务器直接从缓存中去获取结果，不需要在再去解析、优化、执行 sql。

如果这个表修改了，那么使用这个表中的所有缓存将不再有效，查询缓存值得相关条目将被清空。表中得任何改变是值表中任何数据或者是结构的改变，包括 insert, update, delete, truncate, alter table, drop table 或者是 drop database 包括那些映射到改变了的表的使用 merge 表的查询，显然，对于频繁更新的表，查询缓存不合适，对于一些不变的数据且有大量相同 sql 查询的表，查询缓存会节省很大的性能。

详细说明：链接

更多

原文大纲： 链接

更多文章，可以关注下方公众号：