【MySQL 面试】Java面试八股必背系列之MySQL（上篇）

MySQL 基础

什么是关系型数据库？（★）

关系型数据库，就是采用了关系模型来组织数据的数据库。关系型数据库模型是把复杂的数据结构归结为简单的二元关系（即二维表格形式）。它是以行和列的形式存储数据，以便于用户理解，这一系列的行和列被称为表，一组表组成了数据库。关系模型表明了数据库中所存储的数据之间的联系（比如一对一、一对多、多对多）。

在关系型数据库中，我们的数据都被存放在了各种表中（比如用户表），表中的每一行就存放着一条数据（比如一个用户的信息）。

大部分关系型数据库都使用 SQL 来操作数据库中的数据。并且，大部分关系型数据库都支持事务的四大特性(ACID)。

有哪些常见的关系型数据库呢？

常见主流的关系型数据库有MySQL、PostgreSQL、Oracle、SQL Server、SQLite（微信本地的聊天记录的存储就是用的 SQLite） ......。

关系型和非关系型数据库的区别？（★★）

1. 成本：在安装部署上，Nosql数据库易部署，并且大多都是开源免费的，无需昂贵的经济成本，不用像Oracle那般花费较高成本选购。在学习成本上，Nosql数据库没有采用关系模型组织数据，相较于关系型数据库来说，不易于理解，学习成本较高。

2. 查询速率：Nosql数据库大多将数据储存于缓存当中，也就是内存级别，无需历经SQL层的优化分析；而关系型数据库将数据储存在电脑硬盘中，所以查询速率远不如Nosql数据库。

3. 存储格式：Nosql的储存文件格式可以是key,value方式、文本文档方式、照片方式这些，能储存的对象种类灵活；关系型数据库天然就是表格式的，因此存储在数据表的行和列中，数据表可以彼此关联协作存储，提取数据比较方便。

4. 可扩展性：要支持更多并发量，SQL数据库是纵向扩展，也就是说提高处理能力，使用速度更快速的计算机，这样处理相同的数据集就更快了。因为数据存储在关系表中，操作的性能瓶颈可能涉及很多个表，这都需要通过提高计算机性能来客服。虽然SQL数据库有很大扩展空间，但最终肯定会达到纵向扩展的上限。而NoSQL数据库是横向扩展的。关系型数据库有join那样的多表查询机制限定造成拓展性较差。Nosql依据键值对，数据中间沒有耦合度，因此容易水平拓展。

5. 数据一致性：非关系型数据库注重最终一致性；关系型数据库注重数据整个生命周期的强一致性。

6. 事务处理：如果数据操作需要高事务性或者复杂数据查询需要控制执行计划，那么传统的SQL数据库从性能和稳定性方面考虑是最佳选择。SQL数据库支持对事务原子性细粒度控制，并且易于回滚事务。虽然NoSQL也支持事务处理，但可靠性不足，其价值在于可扩展性和大数据量处理。

关系型数据库的优缺点（★）

优点：

• 容易理解，因为它采用了关系模型来组织数据。

• 可以保持数据的一致性。

• 事务支持使得对于安全性能很高的数据访问要求得以实现。

• 支持复杂查询，可以用SQL语句方便的在一个表以及多个表之间做非常复杂的数据查询，以及带 where 子句的查询。

缺点：

• 读写性能比较差，尤其是海量数据的高效率读写；

• 固定的表结构，灵活度稍欠；

• 高并发读写需求，传统关系型数据库来说，硬盘I/O是一个很大的瓶颈。

非关系型数据库的优缺点（★）

优点：

• 格式灵活：存储数据的格式可以是key,value形式、文档形式、图片形式等等，使用灵活，应用场景广泛，而关系型数据库则只支持基础类型；

• 速度快：nosql可以使用硬盘或者随机存储器作为载体，而关系型数据库只能使用硬盘；

• 高扩展性，基于键值对，读写性能很高，易于扩展；

• 成本低：nosql数据库部署简单，基本都是开源软件；

• 无需经过 SQL 层的解析，读写效率高。

缺点：

• 不提供sql支持，学习和使用成本较高；

• 由于Nosql约束少，所以也不能够像sql那样提供where字段属性的查询，因此适合存储较为简单的数据；

• 数据结构相对复杂，复杂查询方面稍欠。

简单介绍一下MySQL（★★）

• 首先，MySQL 是一种常用的关系型数据库管理系统（RDBMS），主要用于持久化存储以及操作、管理我们的应用程序运行中产生的一些数据。

• 目前，MySQL 属于 Oracle 公司旗下的一款产品，MySQL6.x 版本之后分为社区版和商业版。社区版也就是免费的，并且开源，所以不需要支付额外的费用，降低使用成本。另外，我们可以在 GPL(General Public License) 的许可下下载并根据个性化的需要对其进行修改，进行定制化专有的MySQL系统。

• MySQL支持大型的数据库，可以处理拥有上千万条记录的大型数据库。

• MySQL使用标准的SQL数据语言去操作数据。

• MySQL可以允许运行于多个系统上，并且支持多种语言。这些编程语言包括C、C++、Python、Java、Perl、PHP和Ruby等。

• MySQL采用客户端/服务端架构，用户通过客户端程序向服务端发送增删改查等请求，也就是对应的SQL语句，服务器程序再收到请求后，对其进行处理并将结果返回给客户端。

• MySQL 的默认端口号是3306。

为什么选择使用MySQL，MySQL的优势？（★★）

1. MySQL是可以处理拥有上千万条记录的大型数据库管理系统。同时有社区版，并且开放源代码，使用成本低。

2. 性能卓越，服务稳定。

3. 软件体积小，使用简单，并且易于维护。

4. 历史悠久，社区用户非常活跃，遇到问题可以寻求帮助。

5. 许多互联网公司在用，经过了时间的验证。

MySQL有关权限的表有哪几个呢？（★★）

MySQL服务器通过权限表来控制用户对数据库的访问，权限表存放在mysql数据库里，由mysqlinstalldb脚本初始化。这些权限表分别user，db，tablepriv，columnspriv和host。

• user权限表：记录允许连接到服务器的用户帐号信息，里面的权限是全局级的。

• db权限表：记录各个帐号在各个数据库上的操作权限。

• table_priv权限表：记录数据表级的操作权限。

• columns_priv权限表：记录数据列级的操作权限。

• host权限表：配合db权限表对给定主机上数据库级操作权限作更细致的控制。这个权限表不受GRANT和REVOKE语句的影响。

简单说说一条 MySQL 语句执行的步骤（★★★★）

Server 层按顺序执行 SQL 的步骤为：

• 客户端请求 -> 连接器（验证用户身份，给予权限）

• 查询缓存（存在缓存则直接返回，不存在则执行后续操作）

• 分析器（对 SQL 进行词法分析和语法分析操作）

• 优化器（主要对执行的 SQL 优化选择最优的执行方案方法）

• 执行器（执行时会先看用户是否有执行权限，有才去使用这个引擎提供的接口）-> 去引擎层获取数据返回（如果开启查询缓存则会缓存查询结果）

讲一下 MySQL 架构？（★★★★）

MySQL 主要分为 Server 层和存储引擎层：

• Server 层：主要包括连接器、查询缓存、分析器、优化器、执行器等，所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图，函数等，还有一个通用的日志模块 binglog 日志模块。

• 存储引擎：主要负责数据的存储和读取。server 层通过 api 与存储引擎进行通信。

Server 层基本组件

• 连接器：当客户端连接 MySQL 时，server 层会对其进行身份认证和权限校验。

• 查询缓存: 执行查询语句的时候，会先查询缓存，先校验这个 sql 是否执行过，如果有缓存这个 sql，就会直接返回给客户端，如果没有命中，就会执行后续的操作。

• 分析器: 没有命中缓存的话，SQL 语句就会经过分析器，主要分为两步，词法分析和语法分析，先看 SQL 语句要做什么，再检查 SQL 语句语法是否正确。

• 优化器： 优化器对查询进行优化，包括重写查询、决定表的读写顺序以及选择合适的索引等，生成执行计划。

• 执行器： 首先执行前会校验该用户有没有权限，如果没有权限，就会返回错误信息，如果有权限，就会根据执行计划去调用引擎的接口，返回结果。

一条 SQL 查询语句在 MySQL 中如何执行的？（查询语句执行流程）（★★★）

查询语句的执行流程如下：权限校验、查询缓存、分析器、优化器、权限校验、执行器、引擎。

举个例子，查询语句如下：

select * from user where id > 1 and name = 'YJS';

1. 首先由Server层的连接器对客户端进行身份认证，校验权限，若没有权限则返回错误；

2. 然后MySQL8.0 以前会查询缓存，若缓存命中则直接返回，没有则执行下一步；

3. 下面开始真正的查询操作，由分析器对SQL语句进行词法分析和语法分析。提取表名、查询条件，检查语法是否有错误；

4. 接着就是优化器对SQL语句进行优化，比如选择合适的索引，如这里两种执行方案，先查 id > 1 还是 name = 'YJS'，优化器根据自己的优化算法选择执行效率最好的方案，最后制定执行计划；

5. 最后通过存储引擎层去与存储在磁盘上的数据按照执行计划去查询数据，需要校验权限，有权限就调用数据库引擎接口，返回引擎的执行结果。

更新语句执行过程？（★★★）

举个例子，更新语句如下：

update user set name = 'YJS' where id = 2;

1. 连接器会先检查权限，若没有权限就直接返回错误信息，如果有权限就会清除查询缓存（MySQL 8.0版本之前），将user表所有缓存结果都清空（一般不建议使用查询缓存）。接下来执行下一步。

2. 通过分析器先进行词法分析，提取sql语句里面的关键字。示例SQL取的update，然后提取要更新的表名user，更新条件是id= 2 ，然后进行语法分析，判断sql语句是否正确，如果有错会返回报错信息，否则执行下一步。

3. 优化器确定执行方案。优化器根据自己的优化算法选择一个执行效率最好的一个方案。比如：优化器会去找id字段有没有索引，使用id主键索引，执行计划确定后就会执行下一步。

4. 执行器首先会判断当前用户对user表是否有更新的权限，如果没有权限就会返回权限错误，若有权限会打开表执行，根据表的引擎定义调用引擎提供的接口，返回引擎执行结果。

首先查询到 id 为 2 的记录，有缓存会使用缓存，拿到查询结果，将 name 更新为YJS，然后调用引擎接口，写入更新数据，innodb 引擎将数据保存在内存中，同时记录redo log，此时redo log进入 prepare状态。然后执行器收到通知后记录binlog，然后调用引擎接口，提交redo log为commit状态。更新完成。

说一下数据库的三大范式（★★★）

• 第一范式：数据表中的每一列（每个字段）都不可以再拆分。

• 第二范式：在第一范式的基础上，非主键列完全依赖于主键，而不能是依赖于主键的一部分。（消除了部分依赖）

• 第三范式：在满足第二范式的基础上，表中的非主键只依赖于主键，而不依赖于其他非主键。（消除了传递依赖）

什么是内连接、外连接、交叉连接、笛卡尔积呢？（★★）

连接的方式主要有三种：外连接、内链接、交叉连接

外连接(OUTER JOIN)：外连接分为三种，分别是左外连接、右外连接、全外连接，

• 内连接: 只取得两张表中满足存在连接匹配关系的记录。

• 左外连接: 包含左边表的全部行（不管右边的表中是否存在与它们匹配的行），以及右边表中全部匹配的行。

• 右外连接: 包含右边表的全部行（不管左边的表中是否存在与它们匹配的行），以及左边表中全部匹配的行。

• 全外连接: 包含左、右两个表的全部行，不管另外一边的表中是否存在与它们匹配的行。

• 交叉连接: 生成笛卡尔积，它不使用任何匹配或者选取条件，而是直接将一个数据源中的每个行与另一个数据源的每个行都一一匹配。

• 笛卡尔积：是数学中的一个概念，例如集合A={a,b}，集合B={1,2,3}，那么A×B={<a,o>,<a,1>,<a,2>,<b,0>,<b,1>,<b,2>}。

MySQL的内连接、左连接、右连接有什么区别？（★★）

MySQL的连接主要分为内连接和外连接，外连接常用的有左外连接、右外连接。

• 内连接(INNER JOIN)，在两张表进行连接查询时，只保留两张表中查询条件完全匹配的结果集。

• 左外连接(LEFT OUTER JOIN 或 LEFT JOIN) 在两张表进行连接查询时，会返回左表所有的行，即使在右表中没有匹配的记录。

• 右外连接(RIGHT OUTER JOIN 或 RIGHT JOIN) 在两张表进行连接查询时，会返回右表所有的行，即使在左表中没有匹配的记录。

varchar与char有什么区别？（★★★）

char：

• char表示定长字符串，长度是固定的；

• 如果插入数据的长度小于char的固定长度时，则用空格填充；

• 因为长度固定，所以存取速度要比varchar快很多，甚至能快50%，但正因为其长度固定，所以会占据多余的空间，是空间换时间的做法；

• 对于char来说，最多能存放的字符个数为255，和编码无关

varchar：

• varchar表示可变长字符串，长度是可变的；

• 插入的数据是多长，就按照多长来存储；

• varchar在存取方面与char相反，它存取慢，因为长度不固定，但正因如此，不占据多余的空间，是时间换空间的做法；

• 对于varchar来说，最多能存放的字符个数为65532

日常的设计，对于长度相对固定的字符串，可以使用char，对于长度不确定的，使用varchar更合适一些。

Blob和Text有什么区别？（★★★）

主要差别

TEXT与BLOB的主要差别就是BLOB保存二进制数据，TEXT保存字符数据。

类型区别

BLOB有4种类型：TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB。它们只是可容纳值的最大长度不同。

TEXT也有4种类型：TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT。这些类型同BLOB类型一样，有相同的最大长度和存储需求。

字符集

BLOB列没有字符集，并且排序和比较基于列值字节的数值值。TEXT列有一个字符集，并且根据字符集的校对规则对值进行排序和比较。

大小写

在TEXT或BLOB列的存储或检索过程中，都不存在大小写转换。

严格模式

运行在非严格模式时，如果为BLOB或TEXT列分配一个超过该列类型的最大长度的值，值被截取以保证适合。如果截掉的字符不是空格，将会产生一条警告。使用严格SQL模式，会产生错误，并且值将被拒绝而不是截取并给出警告。

其它

当保存或检索BLOB和TEXT列的值时不删除尾部空格。

对于BLOB和TEXT列的索引，必须指定索引前缀的长度。

BLOB和TEXT列不能有默认值。

当排序时只使用该列的前max_sort_length个字节。max_sort_length的 默认值是1024。

DATETIME和TIMESTAMP有什么区别？（★★★）

相同点：

1. 两个数据类型存储时间的表现格式一致。均为 YYYY-MM-DD HH:MM:SS

2. 两个数据类型都包含「日期」和「时间」部分。

3. 两个数据类型都可以存储微秒的小数秒（秒后6位小数秒）

区别：

1. 日期范围：DATETIME 的日期范围是 1000-01-01 00:00:00.000000 到 9999-12-31 23:59:59.999999；TIMESTAMP 的时间范围是1970-01-01 00:00:01.000000 UTC到 ``2038-01-09 03:14:07.999999 UTC

2. 存储空间：DATETIME 的存储空间为 8 字节；TIMESTAMP 的存储空间为 4 字节

3. 时区相关：DATETIME 存储时间与时区无关；TIMESTAMP 存储时间与时区有关，显示的值也依赖于时区

4. 默认值：DATETIME 的默认值为 null；TIMESTAMP 的字段默认不为空(not null)，默认值为当前时间(CURRENT_TIMESTAMP)

EXISTS和 IN 有什么区别？（★★★）

• EXISTS是对外表做循环，每次循环再对内表（子查询）进行查询，那么因为对内表的查询使用的索引（内表效率高，故可用大表），而外表有多大都需要遍历，不可避免（尽量用小表），故内表大的使用EXISTS，可加快效率；

• IN 是把外表和内表做hash连接，先查询内表，再把内表结果与外表匹配，对外表使用索引（外表效率高，可用大表），而内表多大都需要查询，不可避免，故外表大的使用in，可加快效率。

• 如果查询的两个表大小相当，那么用in和exists差别不大。如果两个表中一个较小，一个是大表，则子查询表大的用exists，子查询表小的用in。

子查询的表比较大的时候，使用EXISTS可以有效减少总的循环次数来提升速度；当外查询的表比较大的时候，使用IN可以有效减少对外查询表循环遍历来提升速度。

TRUNCATE、DELETE与 DROP区别？（★★★）

相同点：

1. TRUNCATE和不带where子句的DELETE、以及DROP都会删除表内的全部数据。

不同点：

1. SQL分类不同：

   DELETE语句为DML，这个操作会被放到 rollback segment中,事务提交后才生效，可以回滚；TRUNCATE、DROP是DLL，会隐式提交，原数据不放到 rollback segment中所以，不能回滚，不会触发触发器。

2. 删除方式不同：

   DELETE语句执行删除的过程是每次从表中删除一行，并且同时将该行的删除操作作为事务记录在日志中保存以便进行进行回滚操作，而且DELETE可根据条件删除表中满足条件的数据，如果不指定where子句，那么删除表中所有记录。

   TRUNCATE TABLE 则一次性地从表中删除所有的数据并不把单独的删除操作记录记入日志保存，删除行是不能恢复的。并且在删除的过程中不会激活与表有关的删除触发器，执行速度快。

   TRUNCATE TABLE 在功能上与不带 WHERE 子句的 DELETE 语句相同：二者均删除表中的全部行。但 TRUNCATE TABLE 比 DELETE 速度快，且使用的系统和事务日志资源少。DELETE 语句每次删除一行，并在事务日志中为所删除的每行记录一项。TRUNCATE TABLE 通过释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放。

   对于外键（foreignkey ）约束引用的表，不能使用 truncate table，而应使用不带 where 子句的 delete 语句。

3. 执行速度不同：

   一般来说，执行速度: DROP> TRUNCATE> DELETE。

4. 删除表结构：

   TRUNCATE和 DELETE只删除数据不删除表的结构；DROP语句将删除表的结构被依赖的约束、触发器、索引。

5. 释放空间不同：

   当表被TRUNCATE 后，这个表和索引所占用的空间会恢复到初始大小，DELETE操作不会减少表或索引所占用的空间。 DROP语句将表所占用的空间全部释放掉。

HAVING和 WHERE的区别？（★★★）

WHERE是一个约束声明，在查询数据库的结果返回之前对数据库中的查询条件进行约束，即在结果返回之前起作用，且WHERE后面不能使用聚合函数

HAVING是一个过滤声明，所谓过滤是在查询数据库的结果返回之后进行过滤，即在结果返回之后起作用，并且HAVING后面可以使用聚合函数。

二者作用对象不同，WHERE子句作用于表和视图，HAVING作用于组。作用时间不同，WHERE在数据分组前进行过滤，HAVING在数据分组后进行过滤。HAVING一般跟在group by之后，执行记录组选择的一部分来工作的，WHERE则是执行所有数据来工作的。

count(1)、count(*) 与 count(列名) 的区别？（★★）

执行效果：

• count(*)包括了所有列，相当于行数，在统计结果的时候，不会忽略列值为NULL

• count(1)包括了所有列，用1代表代码行，在统计结果的时候，不会忽略列值为NULL

• count(列名)只包括列那一列，在统计结果的时候，会忽略列值为空的计数，即某个字段值为NULL时，不统计。

执行速度：

• 列名为主键，count(列名)会比count(1)快

• 列名不为主键，count(1)会比count(列名)快

• 如果表多个列并且没有主键，则 count（1） 的执行效率优于 count（*）

• 如果有主键，则 select count（主键）的执行效率是最优的

• 如果表只有一个字段，则 select count（*）最优。

UNION与UNION ALL的区别？（★★）

• UNION：对两个结果集进行并集操作，不包括重复行，同时进行默认规则的排序；

• UNION ALL：对两个结果集进行并集操作，包括重复行，不进行排序；

• UNION的效率高于 UNION ALL

数据库存储日期格式时，如何考虑时区转换问题？（★★）

TIMESTAMP类型是标准的UNIX TIMESTAMP，它存储的是1970-1-1到现在经过的秒数，4字节存储。MySQL中使用这个类型很方便，有很多内置的函数和trigger来处理它，比如CURRENT_TIMESTAMP宏，最关键的是在取数据的时候MySQL会自动处理DST和时区的问题。

DATETIME类型的范围更大，好像可以从0000-00-00 00:00:00到9999-12-31 23:59:59，8字节存储，但DATETIME不带时区，比如我在程序里生成了一个2022-08-17 18:26:00的时间，存到MySQL里，再从不同时区的地方拿出来，这个时间可能就不一致。

但TIMESTAMP也有两个很大的问题：

1. 4字节长度限制，它只能到2038年

2. 很多时候我们希望根据用户所在地的时区显示时间而不是光显示一个服务器时间

所以比较好的做法是，数据库中使用DATETIME，然后存时间的时候一律用程序生成UTC时间（而不是local时区的时间）存进去，取出来的时候不管想显示服务器时间还是显示用户的时间都可以处理。

MySQL里记录货币用什么字段类型比较好？（★★★）

• 货币在数据库中MySQL常用Decimal和Numric类型表示，这两种类型被MySQL实现为同样的类型。他们被用于保存与金钱有关的数据。

• 比如定义字段salary DECIMAL(9,2)，9代表将被用于存储值的总的小数位数，而2代表将被用于存储小数点后的位数。存储在salary列中的值的范围是从-9999999.99到9999999.99。

• DECIMAL和NUMERIC值作为字符串存储，而不是作为二进制浮点数，以便保存那些值的小数精度。

为什么要使用视图？什么是视图？（★★）

为了提高复杂SQL语句的复用性和表操作的安全性，MySQL数据库管理系统提供了视图特性。视图是一个虚拟的表，是一个表中的数据经过某种筛选后的显示方式，视图由一个预定义的查询select语句组成。

视图有哪些特点？哪些使用场景？（★★★）

视图特点：

• 视图的列可以来自不同的表，是表的抽象和在逻辑意义上建立的新关系。

• 视图是由基本表(实表)产生的表(虚表)。

• 视图的建立和删除不影响基本表。

• 对视图内容的更新(添加，删除和修改)直接影响基本表。

• 当视图来自多个基本表时，不允许添加和删除数据。

视图用途： 简化SQL查询，提高开发效率，兼容老的表结构。

视图的常见使用场景：

• 重用SQL语句；

• 简化复杂的SQL操作。

• 使用表的组成部分而不是整个表；

• 保护数据

• 更改数据格式和表示。视图可返回与底层表的表示和格式不同的数据。

视图的优点，缺点？（★★★）

优点

• 查询简单化。视图不仅可以简化用户对数据的理解，也可以简化他们的操作。那些被经常使用的查询可以被定义为视图，从而使用户不必为以后的操作每次都指定全部的条件。

• 数据安全性。通过视图用户只能查询和修改他们所能见到的数据。数据库中的其他数据则既看不见也取不到。通过视图，用户可以被限制在数据的不同子集上，能够对机密数据提供安全保护。

• 逻辑数据独立性。视图可以使应用程序和数据库表在一定程度上独立。如果没有视图，应用一定是建立在表上的。有了视图之后，程序可以建立在视图之上，从而程序与数据库表被视图分割开来。

缺点

• 性能：从数据库视图查询数据可能会很慢，特别是如果视图是基于其他视图创建的。

• 表依赖关系：将根据数据库的基础表创建一个视图。每当更改与其相关联的表的结构时，都必须更改视图。

什么是游标？（★★）

游标提供了一种对从表中检索出的数据进行操作的灵活手段，就本质而言，游标实际上是一种能从包括多条数据记录的结果集中每次提取一条记录的机制。

什么是存储过程？有哪些优缺点？（★★★）

存储过程，就是一些编译好了的SQL语句，这些SQL语句代码像一个方法一样实现一些功能（对单表或多表的增删改查），然后给这些代码块取一个名字，在用到这个功能的时候调用即可。

优点：

• 存储过程是一个预编译的代码块，执行效率比较高

• 存储过程在服务器端运行，减少客户端的压力

• 允许模块化程序设计，只需要创建一次过程，以后在程序中就可以调用该过程任意次，类似方法的复用

• 一个存储过程替代大量T_SQL语句 ，可以降低网络通信量，提高通信速率

• 可以一定程度上确保数据安全

缺点：

• 调试麻烦

• 可移植性不灵活

• 重新编译问题

什么是触发器？触发器的使用场景有哪些？（★★★）

触发器，指一段代码，当触发某个事件时，自动执行这些代码。

使用场景：

• 可以通过数据库中的相关表实现级联更改。

• 实时监控某张表中的某个字段的更改而需要做出相应的处理。

• 例如可以生成某些业务的编号。

• 注意不要滥用，否则会造成数据库及应用程序的维护困难。

MySQL中都有哪些触发器？（★）

MySQL 数据库中有六种触发器：

• Before Insert

• After Insert

• Before Update

• After Update

• Before Delete

• After Delete

SQL 约束有哪几种呢？（★★）

• NOT NULL: 约束字段的内容一定不能为NULL。

• UNIQUE: 约束字段唯一性，一个表允许有多个 Unique 约束。

• PRIMARY KEY: 约束字段唯一，不可重复，一个表只允许存在一个。

• FOREIGN KEY: 用于预防破坏表之间连接的动作，也能防止非法数据插入外键。

• CHECK: 用于控制字段的值范围。

主键使用自增ID还是UUID，为什么？（★★）

如果是单机的话，选择自增ID；如果是分布式系统，优先考虑UUID。

• 自增ID：数据存储空间小，查询效率高。但是如果数据量过大,会超出自增长的值范围，多库合并，也有可能有问题。

• uuid：适合大量数据的插入和更新操作，但是它无序的，插入数据效率慢，占用空间大。

字段为什么要求定义为NOT NULL？（★）

NULL值会占用更多的字节，并且NULL容易引发问题。

存储引擎

存储引擎相关的命令（★）

查看 MySQL 提供的所有存储引擎

mysql> show engines;

MySQL 当前默认的存储引擎是 InnoDB，并且在 5.7 版本所有的存储引擎中只有 InnoDB 是事务性存储引擎，也就是说只有 InnoDB 支持事务。

查看 MySQL 当前默认的存储引擎

我们也可以通过下面的命令查看默认的存储引擎。

mysql> show variables like '%storage_engine%';

查看表的存储引擎

show table status like "table_name" ;

常见的存储引擎有哪些？（★★★★）

常见的主要存储引擎以及功能如下：

MySQL 中常用的四种存储引擎分别是：MyISAM、InnoDB、MEMORY、ARCHIVE。MySQL5.5之前，默认存储引擎是MylSAM，5.5之后变成了InnoDB。

• InnoDB 存储引擎

  InnoDB 是 MySQL 默认的事务型存储引擎，使用最广泛，基于聚簇索引建立的。InnoDB 内部做了很多优化，如能够自动在内存中创建自适应 hash 索引，以加速读操作。

  优点：支持事务和崩溃修复能力；引入了行级锁和外键约束。

  缺点：占用的数据空间相对较大。

  适用场景：需要事务支持，并且有较高的并发读写频率。

• MyISAM 存储引擎

  数据以紧密格式存储。对于只读数据，或者表比较小、可以容忍修复操作，可以使用 MyISAM 引擎。MyISAM 会将表存储在两个文件中，数据文件.MYD和索引文件.MYI。

  优点：访问速度快。

  缺点：MyISAM 不支持事务和行级锁，不支持崩溃后的安全恢复，也不支持外键。

  适用场景：对事务完整性没有要求；表的数据都是只读的。

• MEMORY 存储引擎

  MEMORY 引擎将数据全部放在内存中，访问速度较快，但是一旦系统崩溃的话，数据都会丢失。

  MEMORY 引擎默认使用哈希索引，将键的哈希值和指向数据行的指针保存在哈希索引中。

  优点：访问速度较快。

  缺点：

  1. 哈希索引数据不是按照索引值顺序存储，无法用于排序。

  2. 不支持部分索引匹配查找，因为哈希索引是使用索引列的全部内容来计算哈希值的。

  3. 只支持等值比较，不支持范围查询。

  4. 当出现哈希冲突时，存储引擎需要遍历链表中所有的行指针，逐行进行比较，直到找到符合条件的行。

• ARCHIVE 存储引擎

  ARCHIVE 存储引擎非常适合存储大量独立的、作为历史记录的数据。ARCHIVE 提供了压缩功能，拥有高效的插入速度，但是这种引擎不支持索引，所以查询性能较差。

存储引擎的选择（不同存储引擎的适用场景）（★★★）

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

• InnoDB:是MySQL的默认存储引擎，支持事务、外键。如果应用对事务的完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询之外，还包含很多的更新、删除操作，那么InnoDB存储引擎是比较合适的选择。

• MyISAM:如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不是很高，那么选择这个存储引擎是非常合适的。

• MEMORY:将所有数据保存在内存中，访问速度快，通常用于临时表及缓存。MEMORY的缺陷就是对表的大小有限制，太大的表无法缓存在内存中，而且无法保障数据的安全性。

MyISAM 和 InnoDB 的区别？（★★★★）

MySQL 5.5 之前，MyISAM 引擎是 MySQL 的默认存储引擎，可谓是风光一时。

虽然，MyISAM 的性能还行，各种特性也还不错（比如全文索引、压缩、空间函数等）。但是，MyISAM 不支持事务和行级锁，而且最大的缺陷就是崩溃后无法安全恢复。

5.5 版本之后，MySQL 引入了 InnoDB（事务性数据库引擎），MySQL 5.5 版本后默认的存储引擎为 InnoDB。

1. 存储结构：每个使用MyISAM引擎的数据库在磁盘上存储成三个文件；InnoDB所有的表都保存在同一个数据文件中（也可能是多个文件，或者是独立的表空间文件），InnoDB表的大小只受限于操作系统文件的大小，一般为2GB。

2. 是否支持行级锁 : MyISAM 只有表级锁，更新时会锁住整张表，导致其它查询和更新都会被阻塞，而InnoDB 支持行级锁和表级锁，默认为行级锁。

3. 是否支持事务和崩溃后的安全恢复：MyISAM 不提供事务支持，而InnoDB提供事务支持，具有事务、回滚和崩溃修复能力。

4. 是否支持外键： MyISAM不支持，而InnoDB支持。

5. 是否支持MVCC ：MyISAM不支持，InnoDB支持。应对高并发事务，MVCC 比单纯的加锁更高效。

6. 是否支持聚集索引：MyISAM使用的是非聚集索引，InnoDB使用的聚集索引。

7. 是否支持全文索引：InnoDB不支持全文索引，而MyISAM支持全文索引（5.7以后的InnoDB也支持全文索引）。

8. 是否必须有主键：InnoDB表必须有主键，如果没有设定主键或者非空唯一索引，就会自动生成一个6字节的主键(用户不可见)，而MyISAM允许没有任何索引和主键的表存在。

9. 记录插入顺序：InnoDB按主键大小有序插入，MyISAM记录插入顺序是，按记录插入顺序保存。

10. select count(*) from table时，MyISAM更快，因为它有一个变量保存了整个表的总行数，可以直接读取，InnoDB就需要全表扫描。

11. InnoDB表需要更多的内存和存储，而MyISAM可被压缩，存储空间较小。

12. InnoDB 存储引擎提供了具有提交、回滚、崩溃恢复能力的事务安全，与 MyISAM 比 InnoDB 写的效率差一些，并且会占用更多的磁盘空间以保留数据和索引。

MyISAM 和 InnoDB 实现 B 树索引方式的区别是什么？（★★★）

• InnoDB 存储引擎：B+ 树索引的叶子节点保存数据本身，其数据文件本身就是索引文件。

• MyISAM 存储引擎：B+ 树索引的叶子节点保存数据的物理地址，叶节点的 data 域存放的是数据记录的地址，索引文件和数据文件是分离的。

InnoDB 为什么设计 B+ 树索引？（★★★★）

两个考虑因素：

• InnoDB 需要执行的场景和功能需要在特定查询上拥有较强的性能。

• CPU 将磁盘上的数据加载到内存中需要花费大量时间。

为什么选择 B+ 树：

• 哈希索引虽然能提供O（1）复杂度查询，但对范围查询和排序却无法很好的支持，最终会导致全表扫描。

• B 树能够在非叶子节点存储数据，但会导致在查询连续数据可能带来更多的随机 IO。

• 而 B+ 树的所有叶节点可以通过指针来相互连接，减少顺序遍历带来的随机 IO。

• 普通索引还是唯一索引？

  由于唯一索引用不上 change buffer 的优化机制，因此如果业务可以接受，从性能角度出发建议你优先考虑非唯一索引。

InnoDB引擎中的索引策略，了解过吗？（★★★）

• 覆盖索引：覆盖索引是指在普通索引树中可以得到查询的结果，不需要在回到主键索引树中再次搜索。

• 最左前缀原则：如果我们建立了联合索引，我们不需要使用索引的全部定义，只要用到了索引中的最左边的那个字段就可以使用这个索引。

• 索引下推：索引下推优化是 MySQL 5.6 引入的， 可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

技术 | 聊一聊 InnoDB 引擎中的这些索引策略 (qq.com)

InnoDB引擎的4大特性，了解过吗？（★★★）

• 插入缓冲（insert buffer)

• 二次写(double write)

• 自适应哈希索引(ahi)

• 预读(read ahead)

MySQL中InnoDB引擎的行锁是怎么实现的？（★★★）

基于索引来完成行锁的。比如：select * from t where id = 666 for update;for update 可以根据条件来完成行锁锁定，并且 id 是有索引键的列，如果 id 不是索引键那么InnoDB将实行表锁。

AI模拟面试

本文章中全部题目已经收录到了我的AI模拟面试客户端软件中，关于这个软件看这里：使用Python开发一个AI模拟面试系统 (imyjs.cn)

微信关注

编程那点事儿