MySQL面试总结

问题1. 如何定位慢查询

       以下几种情况有可能会导致出现慢查询:

•  聚合查询
• 多表查询
• 表数据量过大查询
• 深度分页查询

表现形式：页面加载过慢、接口压测响应时间过长（超过1s）

解决方式：

方案一：使用开源工具，如调试工具：Arthas ， 运维工具： Prometheus 、 Skywalking

方案二 ： Mysql自带慢日志

慢查询日志记录了所有执行时间超过指定参数（long_query_time，单位： 秒，默认10s)的所有SQL语句的日志

面试回答示例：

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

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

问题2.一个SQL语句执行很慢如何去判断

可以采用EXPLAIN 或者DESC 命令获取MySQL如何执行SELECT语句的信息。

语法：

#直接在select语句之前加上关键字 explain/desc
explain select 字段列表 FROM 表名 WHERE 条件;

• possible_key当前sql可能会使用到的索引
• key 当前sql实际命中的索引
• key_len 索引占用的大小
• Extra 额外的优化建议

面试回答示例：

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

问题3 MySQL支持的存储引擎有哪些，有什么区别？

存储引擎就是存储数据、建立索引、更新/查询数据等技术的实现方式。存储索引是基于表的，而不是基于库的，所以存储索引也可以被称为表类型。

特性	MySIAM	innoDB	MEMORY
事务安全	不支持	支持	不支持
锁机制	表锁	表锁/行锁	表锁
外键	不支持	支持	不支持

存储引擎的特点

InnoDB

        InnoDB是一种兼顾高可靠性和高性能的通用存储引擎，在MySQL 5.5 之后，InnoDB是默认的MySQL存储引擎，特点是

特点：

• DML操作遵循ACID模型，支持事务
• 行级锁，提高高并发访问性能
• 支持外键，FOREIGEN KEY约束，保证数据的完整性和正确性

文件：

• xxx.ibd：xxx代表的是表名，InooDB引擎的每张表都会对应这样一个表空间文件，存储该表的表结构（frm，sdi）、数据和索引
• xxx.frm 存储表结构（MySQL8.0时，合并在表名.ibd中）

问：MYSQL支持的存储索引有哪些，有什么区别

答：在mysql中提供了很多存储引擎，比较常见的有InnoDB、MyISAM,Memory

• InnoDB存储引擎是mysql5.5之后默认的引擎，它支持事务、外键、表级锁和行级锁
• MyISAM是早期的引擎，它不支持事务、只有表级锁、也没有外键，用的不多
• Memory主要把数据存储在内存，支持表级锁，没有外键和事务，使用比较少

问：存储引擎在mysql的体系结构哪一层，主要特点是什么

• MySQL 体系结构
• InnoDB 存储特点

问题4： 索引

什么是索引：

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

数据结构对比：

MySQL默认使用的索引底层数据结构是B+树。

B树和B+树：

B-Tree，B树是一种多叉路平衡查找树，相对于二叉树，B树每个结点可以有多个分支，即为多叉，一棵最大度数为n的B树，它每个结点可以存储多个 n-1 个key.

B+树是B树的基础上的一种优化，使其更适合实现外存储索引结构，InnoDB存储引擎就是用B+树实现其索引结构。

对比：1.B+树的磁盘读写代价更低。 2. 查询效率B+树更加稳定 3. B+树便于扫库和区间查询

问：了解过索引吗（什么是索引）

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

问：索引的底层数据结构了解过吗？

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

问：B树和B+树的区别是什么？

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

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

问： 知道什么叫覆盖索引吗？

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

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

问：MySQL超大分页怎么处理？

答： 超大分页一般都是数据量比较大时，我们使用了limit分页查询，并且需要对数据进行排序，这个时候效率就很低，我们可以采用覆盖索引和子查询来解决。先分页查询数据的id字段，确定了id之后，再用子查询来过滤，只查询这个id列表中的数据就可以了，因为查询id的时候，走的时覆盖索引，所以效率可以提升很多。

 索引的创建原则：

1） 针对数据量较大，且查询比较频繁的表建立索引。（单表超过10w+数据）

2）针对常作为查询条件（where）、排序（order by），分组（group by）操作的字段建立索引

3）尽量选择区分度高的列作为索引，尽量建立唯一索引、区分度越高、使用索引效率就越高

4）如果是字符串类型的字段，字段的长度较长，可以针对字段的特点，建立前缀索引

5）尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节约空间，避免回表，提高查询效率

6）控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率

7）如果索引不能存储NULL值，请在创建表时使用NOT NULL约束。当优化器知道每列是否包含NULL值时，它可以更好地确定哪个索引最有效地用于查询

问：索引创建原则有哪些？

答： 这个情况有很多，不够都有一个大前提，就是表中的数据要超过10万条以上，我们才需要创建索引，并且添加索引的字段是查询比较频繁的字段，一般也是像作为查询条件，排序字段或分组的字段这些。还有就是，我们通常创建索引的时候都是使用复合索引来创建，一条sql语句的返回值尽量使用覆盖索引，如果字段的区分度不高的话，我摸嗯也会把它放在组合索引后面的字段。如果某个字段的内容较长，我们会考虑使用前缀索引来使用，当然并不是所有的字段都要添加索引，这个索引的数量也要控制，因为添加索引也会导致新增改的速度变慢。

问：什么情况下索引会失效？

答：这个情况比较多，我说一下自己的经验，以前有遇到过的，比如，索引在使用的时候没有遵循最左匹配法则，第二个是，模糊查询，如果%号在前面也会导致索引失效。如果在添加索引的字段上进行了运算操作或者类型转换也都会导致索引失效。

我们之前还遇到一个就是，如果使用了符合索引，中间使用了范围查询，右边的条件索引也会失效，所以，通常情况下想要判断这条sql是否有索引失效的情况，也可以用explain执行计划来分析。

问：sql的优化经验

答： sql的优化经验的话，在项目中还是比较常见的，当然，如果直说sql优化的话，我们会从这几个方面考虑，比如建表的时候、使用索引，sql语句编写、主从复制，读写分离，还有一个就是如果量比较大的话，可以考虑分库分表

问：创建表的时候，你们是如何优化的呢？

答： 这个我们主要参考的阿里出的那个开发手册《嵩山版》，就比如，在自定义字段的时候需要结合字段内容选择合适的类型，如果是数值的话，像tinyint、int、bigint这些类型，要根据实际情况选择，要是字符串类型，也是结合存储的内容进行选择char和或者varchar或者text类型

问： 使用索引的时候是如何优化的？

答： （tips：参考索引创建原则，进行描述）

问： 你平时有对sql语句做了哪些优化？

答： 嗯，这个也有很多，如select语句务必指明字段名称，不要直接使用select * ，还有就是要注意sql语句避免造成索引失效的写法，如果是聚合查询，尽量用union all代替union，union会多一次过滤，效率比较低；如果是表关联的话，尽量使用innerjoin，不要使用left join 和right join，如必须使用一定要以小表为驱动。

其他面试题：

事务相关：事务特性、隔离级别、MVCC

主从同步原理

分库分表

问题5：事务的特性是什么?

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

ACID是什么？

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

问：事务的特性是什么？可以详细说一下吗？

答：这个比较清楚，ACID，分别原子性、一致性、隔离性、持久性；我举一个例子，A向B转账500，转账成功，A就扣除500，B就增加500元，原子操作体现在要么都成功，要么都是失败，在转账过程中，数据要一致，A扣除了500元，B必须增加500

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

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

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

并发事务带来哪些问题？怎么解决这些问题呢？MySQL的默认隔离级别是什么？

并发事务的问题：脏读、不可重复读、幻读

隔离级别：读未提交、读已提交、可重复读、串行化

并发事务问题：

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

怎么解决并发事务的问题？

解决方案：对事物进行隔离

隔离级别	脏读	不可重复读	幻读
Read uncommitted 未提交读	true	true	true
Read committed 读已提交	false	true	true
Repeated Read(默认） 可重复读	false	false	true
Serializable 串行化	false	fasle	false

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

问：并发事务带来哪些问题？

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

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

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

第三个就是幻读，幻读和不可重复读类似，它发生在一个事务(T1）读取了几行数据，接着另一个并发事务（T2）插入了一些数据，在随后的查询中，第一个事务（T1）就会发现了多了一些原本不存在的记录，就有点类似发生了幻觉一样，这个就是幻读。

问：怎么解决了这些问题？Mysql默认的隔离级别是什么？

答：这个解决方案就是对事务进行隔离；MySQL支持4种隔离级别，分别有：

第一个是，未提交读，它解决不了之前提出的问题，一般项目中不考虑才有这种方式；

第二个是读已提交，它能解决脏读问题，但是解决不了不可重复读和幻读。第三个是可重复读，它能解决脏读和不可重复读，但是解决不了幻读，同时，读已提交也是MySQL默认的隔离级别，第四个是串行执行，性能比较低，所以我们一般使用 的是可重复读。

问题6：undo log 和redo log的区别

• 缓冲池（buffer pool）： 主内存中的一个区域，里面可以缓存磁盘上经常操作的真实数据，在执行增删改查操作时，先操作缓冲池中的数据（若缓冲池没有数据，则从磁盘加载并缓存），以一定频率刷新到磁盘，从而减少磁盘IO，从而减少磁盘IO，加快处理速度
• 数据页（page）：是InnoDB存储引擎磁盘管理的最小单元，每个页的大小默认未16kB，页中存储的是行数据。

redo log 重做日志：记录的是事务提交数据页的物理修改，是用来实现事务的持久性

该日志文件由两部分组成：重做日志缓冲（redo log buffer）以及重做日志（redo log file），前者是在内存中，后者在磁盘中。当事务提交之后会把所有更改信息都存在该日志文件中，用于在刷新脏页，发生错误时，进行数据恢复使用。

undo log 回滚日志，用于记录数据被修改前的信息，作用包含两个：提供回滚和MVCC（多版本并发控制），undo log和redo log记录物理日志不一样，它是逻辑日志。

• 可以认为当delete一条记录时，undo log中会记录一条对应的insert记录，反之亦然
• 当update一条记录时，它记录一条相反的upadte记录，当执行rollback 时，就可以从undo log中的逻辑记录读取相应的内容中并进行回滚。

undo log和redo log区别：

• redo log：记录的是数据页的物理变化，服务宕机可用来同步数据
• undo log： 记录的是逻辑日志，当事务进行回滚时，通过逆操作恢复原来的数据
• redo log：保证了事务的持久性，undo log保证了事务的原子性和一致性

问：undo log和redo log的区别

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

事务中的隔离性如何保证？

锁：排他锁（如果一个事务获取了一个数据行的排他锁，其他事务就不能再获取该行的其他锁）

什么时mvcc？

全称 Multi-Version Concurrency Control ，多版本并发控制。指维护一个数据的多个版本，使得读写操作没有冲突，MVCC的具体实现是：主要依赖于数据库记录中的隐式字段，undo log日志、readView

问：事务的隔离性如何保证（解释一下MVCC）

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

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

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

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

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

主从复制

MySQL主从复制的核心就是二进制日志binlog(DDL（数据定义语言）语句和DML（数据操纵语言）语句）

1. 主库在事务提交的时候，会把数据变更记录在二进制文件BInlog中
2. 从库读取主库的二进制日志文件Binlog，写入到从库的中继日志Relay Log
3. 从库重做中继日志中的事件，将改变反映它自己的数据