常见面试题之MySQL篇

川长思鸟来 2024-03-16 22:13 63阅读 0赞

### 1.`MySQL`中，如何定位慢查询? ###

我们当时做压测的时候有的接口非常的慢，接口的响应时间超过了2秒以上，因为我们当时的系统部署了运维的监控系统`Skywalking`，在展示的报表中可以看到是哪一个接口比较慢，并且可以分析这个接口哪部分比较慢，这里可以看到`SQL`的具体的执行时间，所以可以定位是哪个`sql`出了问题。

如果，项目中没有这种运维的监控系统，其实在`MySQL`中也提供了慢日志查询的功能，可以在`MySQL`的系统配置文件中开启这个慢日志的功能，并且也可以设置`SQL`执行超过多少时间来记录到一个日志文件中，我记得上一个项目配置的是2秒，只要`SQL`执行的时间超过了2秒就会记录到日志文件中，我们就可以在日志文件找到执行比较慢的`SQL`了。

### 2.那这个`SQL`语句执行很慢, 如何分析呢？ ###

如果一条`sql`执行很慢的话，我们通常会使用`mysql`自动的执行计划`explain`来去查看这条`sql`的执行情况，比如在这里面可以通过`key`和`key_len`检查是否命中了索引，如果本身已经添加了索引，也可以判断索引是否有失效的情况，第二个，可以通过`type`字段查看`sql`是否有进一步的优化空间，是否存在全索引扫描或全盘扫描，第三个可以通过`extra`建议来判断，是否出现了回表的情况，如果出现了，可以尝试添加索引或修改返回字段来修复。

### 3.了解过索引吗？（什么是索引？） ###

索引在项目中还是比较常见的，它是帮助`MySQL`高效获取数据的数据结构，主要是用来提高数据检索的效率，降低数据库的`IO`成本，同时通过索引列对数据进行排序，降低数据排序的成本，也能降低了`CPU`的消耗。

### 4.索引的底层数据结构了解过嘛 ? ###

`MySQL`的默认的存储引擎`InnoDB`采用的`B+`树的数据结构来存储索引，选择`B+`树的主要的原因是：第一阶数更多，路径更短，第二个磁盘读写代价`B+`树更低，非叶子节点只存储指针，叶子阶段存储数据，第三是`B+`树便于扫库和区间查询，叶子节点是一个双向链表。

### 5.`B`树和`B+`树的区别是什么呢？ ###

B-树,这里的 B 表示 balance( 平衡的意思),B-树是一种多路自平衡的搜索树（B树是一颗多路平衡查找树）。

![在这里插入图片描述][2542c20283d044868bfd302514a43f26.png_pic_center]

B+树是B-树的变体，也是一种多路搜索树。  
![在这里插入图片描述][16872e65f6794d35b648644d83649b0f.png_pic_center]

区别：

第一：在`B`树中，非叶子节点和叶子节点都会存放数据，而B+树的所有的数据都会出现在叶子节点，在查询的时候，`B+`树查找效率更加稳定；

第二：在进行范围查询的时候，`B+`树效率更高，因为`B+`树都在叶子节点存储，并且叶子节点是一个双向链表。

### 6.什么是聚簇索引？什么是非聚簇索引？ ###

聚簇索引主要是指数据与索引放到一块，`B+`树的叶子节点保存了整行数据，有且只有一个，一般情况下主键在作为聚簇索引的；

非聚簇索引值的是数据与索引分开存储，`B+`树的叶子节点保存对应的主键，可以有多个，一般我们自己定义的索引都是非聚簇索引。

### 7.知道什么是回表查询嘛 ? ###

嗯，其实跟刚才介绍的聚簇索引和非聚簇索引是有关系的，回表的意思就是通过二级索引找到对应的主键值，然后再通过主键值找到聚集索引中所对应的整行数据，这个过程就是回表。

![在这里插入图片描述][f149229099ff468b8476d6327f7c3419.png_pic_center]

【**备注**：如果面试官直接问回表，则需要先介绍聚簇索引和非聚簇索引】

### 8.知道什么叫覆盖索引嘛 ? ###

覆盖索引是指`select`查询语句使用了索引，在返回的列，必须在索引中全部能够找到，如果我们使用`id`查询，它会直接走聚集索引查询，一次索引扫描，直接返回数据，性能高。

如果按照二级索引查询数据的时候，返回的列中没有创建索引，有可能会触发回表查询，尽量避免使用`select *`，尽量在返回的列中都包含添加索引的字段。

### 9.`MYSQL`超大分页怎么处理 ? ###

超大分页一般都是在数据量比较大时，我们使用了`limit`分页查询，并且需要对数据进行排序，这个时候效率就很低，我们可以采用覆盖索引和子查询来解决。

先分页查询数据的`id`字段，确定了`id`之后，再用子查询来过滤，只查询这个`id`列表中的数据就可以了。

因为查询`id`的时候，走的覆盖索引，所以效率可以提升很多。

### 10.索引创建原则有哪些？ ###

就是表中的数据要超过10万以上，我们才会创建索引，并且添加索引的字段是查询比较频繁的字段，一般也是像作为查询条件，排序字段或分组的字段这些。

还有就是，我们通常创建索引的时候都是使用复合索引来创建，一条`sql`的返回值，尽量使用覆盖索引，如果字段的区分度不高的话，我们也会把它放在组合索引后面的字段。

如果某一个字段的内容较长，我们会考虑使用前缀索引来使用，当然并不是所有的字段都要添加索引，这个索引的数量也要控制，因为添加索引也会导致新增改的速度变慢。

### 11.什么情况下索引会失效 ? ###

比如，索引在使用的时候没有遵循最左匹配法则，第二个是，模糊查询，如果`%`号在前面也会导致索引失效。如果在添加索引的字段上进行了运算操作或者类型转换也都会导致索引失效。

我们之前还遇到过一个就是，如果使用了复合索引，中间使用了范围查询，右边的条件索引也会失效。

所以，通常情况下，想要判断出这条`sql`是否有索引失效的情况，可以使用`explain`执行计划来分析。

### 12.`sql`的优化的经验？ ###

如果直说`sql`优化的话，我们会从这几方面考虑，比如：

建表的时候、使用索引、`sql`语句的编写、主从复制，读写分离，还有一个是如果量比较大的话，可以考虑分库分表。

### 13.创建表的时候，你们是如何优化的呢？ ###

1.  尽量选择小的数据类型，数据类型选择上尽量`tinyint`(1字节)>`smallint`(2字节)>`int`(4字节)>`bigint`(8字节)，比如逻辑删除`y/n`字段上（1代表可用，0代表）就可以选择`tinyint`（1字节）类型；
2.  尽量保证字段数据类型长度固定；
3.  尽量避免使用`null`，使用`null`的字段查询很难优化，影响索引，可以使用0或`''`代替；
4.  避免宽表，能拆分就拆分，一个表往往跟一个实体域对应，就像设计对象的时候一样，保持单一原则；
5.  尽量避免使用`text`和`blob`，如果非使用不可，将类型为`text`和`blob`的字段在独立成一张新表，然后使用主键对应原表；
6.  禁止使用`float`或`double`类型，这个坑超大，`float`或`double`存在精度问题，在进行比较或者加减操作的时候会丢失精度导致数据异常，凡是使用`float`或`double`类型的时候考虑下可不可使用`int`或`bigint`代替。比如金额，以元为单位使用`float`或`double`类型的时候，可以考虑以分为单位使用`int`，`bigint`类型代替，然后由业务代码进行单位的转换；
7.  每张表都加上`createUser`、`createTime`、`updateUser`、`updateTime`字段；
8.  起名字要规范，包括：库名，表名，字段名，索引名；
9.  查询频繁使用的字段记得加索引；
10. 尽量避免使用外键，不用外键约束，性能更高，然后数据的完整性有程序进行管理；
11. 如果表的数量可以预测到非常大，最好在建表的时候，就进行分表，不至于一时间数据量非常大导致效率问题。

### 14.那在使用索引的时候，是如何优化呢？ ###

1.  维度高的列创建索引。
2.  数据列中不重复值出现的个数，这个数量越高，维度就越高。
3.  如数据表中存在8行数据`a`,`b` ,`c`,`d`,`a`,`b`,`c`,`d`这个表的维度为4。
4.  要为维度高的列创建索引，如性别和年龄，那年龄的维度就高于性别。
5.  性别这样的列不适合创建索引，因为维度过低。
6.  对 `where`,`on`,`group by`,`order by`中出现的列使用索引。
7.  对较小的数据列使用索引，这样会使索引文件更小，同时内存中也可以装载更多的索引键。
8.  为较长的字符串使用前缀索引。
9.  不要过多创建索引，除了增加额外的磁盘空间外，对于`DML`操作的速度影响很大，因为其每增删改一次就得从新建立索引。
10. 使用组合索引，可以减少文件索引大小，在使用时速度要优于多个单列索引。

### 15.你平时对`sql`语句做了哪些优化呢？ ###

1.  查询的时候一定要记得使用`limit`进行限制
2.  对于结果只需要一条数据的查询用`limit 1`进行限制
3.  使用`count(*)`来统计行数或者使用`count(主键)`来查询，使用`count(列)`的时候，不会统计此列为`null`的情况
4.  不要使用`select *`来查数据，使用`select`需要的列名，这样的方式去查询
5.  使用`join`链接代替子查询
6.  不要使用外键，外键的约束可以放在程序里解决
7.  控制一下`in`操作的集合数量，不要太大了
8.  针对慢查询使用`explain`去分析原因，然后优化`sql`，让其尽量走索引
9.  注意`SQL`语句避免造成索引失效的写法；
10. 如果是聚合查询，尽量用`union all`代替`union` ，`union`会多一次过滤，效率比较低；
11. 如果是表关联的话，尽量使用`inner join` ，不要使用用`left join` 、`right join`，如必须使用 一定要以小表为驱动。

### 16.事务的特性是什么？可以详细说一下吗？ ###

这个比较清楚，事务的特性：`ACID`，分别指的是：原子性、一致性、隔离性、持久性；

我举个例子：

A向B转账500，转账成功，A扣除500元，B增加500元，原子操作体现在要么都成功，要么都失败；

在转账的过程中，数据要一致，A扣除了500，B必须增加500；

在转账的过程中，隔离性体现在A像B转账，不能受其他事务干扰；

在转账的过程中，持久性体现在事务提交后，要把数据持久化（可以说是落盘操作）。

### 17.并发事务带来哪些问题？ ###

我们在项目开发中，多个事务并发进行是经常发生的，并发也是必然的，有可能导致一些问题。

第一是脏读， 当一个事务正在访问数据并且对数据进行了修改，而这种修改还没有提交到数据库中，这时另外一个事务也访问了这个数据，因为这个数据是还没有提交的数据，那么另外一个事务读到的这个数据是“脏数据”，依据“脏数据”所做的操作可能是不正确的。

第二是不可重复读：比如在一个事务内多次读同一数据。在这个事务还没有结束时，另一个事务也访问该数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改导致第一个事务两次读取的数据可能不太一样。这就发生了在一个事务内两次读到的数据是不一样的情况，因此称为不可重复读。

第三是幻读（`Phantom read`）：幻读与不可重复读类似。它发生在一个事务（`T1`）读取了几行数据，接着另一个并发事务（`T2`）插入了一些数据时。在随后的查询中，第一个事务（`T1`）就会发现多了一些原本不存在的记录，就好像发生了幻觉一样，所以称为幻读。

### 18.并发事务怎么解决这些问题呢？`MySQL`的默认隔离级别是？ ###

解决方案是对事务进行隔离。

`MySQL`支持四种隔离级别，分别有：

第一个是，未提交读（`read uncommitted`）它解决不了刚才提出的所有问题，一般项目中也不用这个。第二个是读已提交（`read committed`）它能解决脏读的问题的，但是解决不了不可重复读和幻读。第三个是可重复读（`repeatable read`）它能解决脏读和不可重复读，但是解决不了幻读，这个也是`mysql`默认的隔离级别。第四个是串行化（`serializable`）它可以解决刚才提出来的所有问题，但是由于让是事务串行执行的，性能比较低。所以，我们一般使用的都是`mysql`默认的隔离级别：可重复读。

### 19.`undo log`和`redo log`的区别？ ###

其中`redo log`日志记录的是数据页的物理变化，服务宕机可用来同步数据，而`undo log`不同，它主要记录的是逻辑日志，当事务回滚时，通过逆操作恢复原来的数据，比如我们删除一条数据的时候，就会在`undo log`日志文件中新增一条`delete`语句，如果发生回滚就执行逆操作；

`redo log`保证了事务的持久性，`undo log`保证了事务的原子性和一致性。

![在这里插入图片描述][bce98ae0ce24424c9ddc2aec825b3d01.png_pic_center]

![在这里插入图片描述][ee685e58f34e43e8bd975f497671ab21.png_pic_center]

### 20.事务中的隔离性是如何保证的呢？(你解释一下`MVCC`) ###

事务的隔离性是由锁和`mvcc`实现的。

其中`mvcc`的意思是多版本并发控制。指维护一个数据的多个版本，使得读写操作没有冲突，它的底层实现主要是分为了三个部分，第一个是隐藏字段，第二个是`undo log`日志，第三个是`readView`读视图。

隐藏字段是指：在`mysql`中给每个表都设置了隐藏字段，有一个是`trx_id`(事务`id`)，记录每一次操作的事务`id`，是自增的；另一个字段是`roll_pointer`(回滚指针)，指向上一个版本的事务版本记录地址。

`undo log`主要的作用是记录回滚日志，存储老版本数据，在内部会形成一个版本链，在多个事务并行操作某一行记录，记录不同事务修改数据的版本，通过`roll_pointer`指针形成一个链表。

`readView`解决的是一个事务查询选择版本的问题，在内部定义了一些匹配规则和当前的一些事务`id`判断该访问那个版本的数据，不同的隔离级别快照读是不一样的，最终的访问的结果不一样。如果是`rc`隔离级别，每一次执行快照读时生成`ReadView`，如果是`rr`隔离级别仅在事务中第一次执行快照读时生成`ReadView`，后续复用。

### 21.`MySQL`主从同步原理 ？ ###

`MySQL`主从复制的核心就是二进制日志(`DDL`（数据定义语言）语句和`DML`（数据操纵语言）语句)，它的步骤是这样的：

第一：主库在事务提交时，会把数据变更记录在二进制日志文件`Binlog`中。

第二：从库读取主库的二进制日志文件`Binlog`，写入到从库的中继日志`Relay Log`。

第三：从库重做中继日志中的事件，将改变反映它自己的数据。

### 22.你们项目用过`MySQL`的分库分表吗？ ###

因为我们都是微服务开发，每个微服务对应了一个数据库，是根据业务进行拆分的，这个其实就是垂直拆分。

### 23.那你之前使用过水平分库吗？ ###

这个是使用过的，我们当时的业务是(`xxx`)，一开始，我们也是单库，后来这个业务逐渐发展，业务量上来的很迅速，其中(`xx`)表已经存放了超过1000万的数据，我们做了很多优化也不好使，性能依然很慢，所以当时就使用了水平分库。

我们一开始先做了3台服务器对应了3个数据库，由于库多了，需要分片，我们当时采用的`mycat`来作为数据库的中间件。数据都是按照`id`（自增）取模的方式来存取的。

当然一开始的时候，那些旧数据，我们做了一些清洗的工作，我们也是按照id取模规则分别存储到了各个数据库中，好处就是可以让各个数据库分摊存储和读取的压力，解决了我们当时性能的问题。

### 24.`MySQL`支持哪些存储引擎？ ###

`MySQL`支持多种存储引擎，比如`InnoDB`、`MyISAM`、`Memory`、`Archive`等等.在大多数的情况下，直接选择使用 `InnoDB`引擎都是最合适的，`InnoDB`也是`MySQL`的默认存储引擎。

`MyISAM`和`InnoDB`的区别有哪些：

*  `InnoDB`支持事务，`MyISAM`不支持
 *  `InnoDB`支持外键，而`MyISAM`不支持
 *  `InnoDB`是聚集索引，数据文件是和索引绑在一起的，必须要有主键，通过主键索引效率很高；`MyISAM`是非聚集索引，数据文件是分离的，索引保存的是数据文件的指针，主键索引和辅助索引是独立的。
 *  `Innodb`不支持全文索引，而`MyISAM`支持全文索引，查询效率上`MyISAM`要高；
 *  `InnoDB`不保存表的具体行数，`MyISAM`用一个变量保存了整个表的行数。
 *  `MyISAM`采用表级锁(`table-level locking`)；`InnoDB`支持行级锁(`row-level locking`)和表级锁,默认为行级锁。

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

超键：在关系中能唯一标识元组的属性集称为关系模式的超键。一个属性可以为作为一个超键，多个属性组合在一起也可以作为一个超键。超键包含候选键和主键。  
候选键：是最小超键，即没有冗余元素的超键。  
主键：数据库表中对储存数据对象予以唯一和完整标识的数据列或属性的组合。一个数据列只能有一个主键，且主键的取值不能缺失，即不能为空值（`Null`）。  
外键：在一个表中存在的另一个表的主键称此表的外键。

### 26.`SQL`约束有哪几种？ ###

`NOT NULL`: 用于控制字段的内容一定不能为空（`NULL`）。  
`UNIQUE`: 控件字段内容不能重复，一个表允许有多个`Unique`约束。  
`PRIMARY KEY`: 也是用于控件字段内容不能重复，但它在一个表只允许出现一个。  
`FOREIGN KEY`: 用于预防破坏表之间连接的动作，也能防止非法数据插入外键列，因为它必须是它指向的那个表中的值之一。  
`CHECK`: 用于控制字段的值范围。

### 27.`MySQL`中的`varchar`和`char`有什么区别？ ###

`char`是一个定长字段，假如申请了`char(10)`的空间，那么无论实际存储多少内容。该字段都占用 10 个字符，而 `varchar`是变长的，也就是说申请的只是最大长度，占用的空间为实际字符长度`+1`，最后一个字符存储使用了多长的空间。

在检索效率上来讲，`char`> `varchar`,因此在使用中，如果确定某个字段的值的长度,可以使用`char`，否则应该尽量使用`varchar`。例如存储用户`MD5`加密后的密码,则应该使用`char`。

### 28.`MySQL`中`in`和`exists`区别？ ###

`MySQL`中的`in`语句是把外表和内表作`hash`连接，而`exists`语句是对外表作`loop`循环，每次`loop`循环再对内表进行查询。一直大家都认为`exists`比`in`语句的效率要高，这种说法其实是不准确的。这个是要区分环境的。

如果查询的两个表大小相当，那么用`in`和`exists`差别不大。  
如果两个表中一个较小，一个是大表，则子查询表大的用`exists`，子查询表小的用`in`。  
`not in`和`not exists`：如果查询语句使用了`not in`，那么内外表都进行全表扫描，没有用到索引；而`not extsts`的子查询依然能用到表上的索引。所以无论那个表大，用`not exists`都比`not in`要快。

### 29. `drop`、`delete`与`truncate`的区别？ ###

三者都表示删除，但是三者有一些差别：  
![在这里插入图片描述][02e8b049770747a49f95bffa13d08952.png_pic_center]

[2542c20283d044868bfd302514a43f26.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/15/727a29490c644c21a5698c4c7fb07db4.png
[16872e65f6794d35b648644d83649b0f.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/15/6b0270dfe7e84d48874411f8f7676aff.png
[f149229099ff468b8476d6327f7c3419.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/15/7b032d71c4494861911386fee3ea542d.png
[bce98ae0ce24424c9ddc2aec825b3d01.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/15/d3701b120f504f7cabd279aa281158f5.png
[ee685e58f34e43e8bd975f497671ab21.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/15/f18e9fd5368f4984b164cb7c03dad3c4.png
[02e8b049770747a49f95bffa13d08952.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/15/dc2f042c6e1f452ab7d2cfe0c20b063a.png