MySQL面试题
MySQL面试题
结构划分
慢查询
面试官： MySQL中，如何定位慢查询?
候选人：
在科大讯飞实习时，我当时做压测的时候有的接口非常的慢，接口的响应时间超过了2秒以上，因为我们当时的系统部署了运维的监控系统Skywalking ，平台的报表中可以看到是哪一个后端接口比较慢，并且可以分析这个接口哪部分比较慢，这里可以看到SQL的具体的执行时间，所以可以定位是哪个sql出了问题，然后找到SQL对症下药解决满查询就可以了。
如果，项目中没有这种运维的监控系统，例如在我第一家公司实习中，公司没有部署监控平台，只能通过MYSQL提供的慢查询定位功能。可以在MySQL的系统配置文件中开启这个慢日志的功能：slow_query=1，并且也可以设置SQL执行超过多少时间来记录到一个日志文件中，我记得上一个项目配置的是2秒：long_query_time=2，只要SQL执行的时间超过了2秒就会记录到日志文件中，我们就可以在日志文件找到执行比较慢的SQL了，然后定位问题就可以了。
面试官： 那这个SQL语句执行很慢, 如何分析呢？（如何解决慢查询问题？如何分析问题根源？）
以下的问题可能导致SQL执行很慢：
- 聚和查询
- 多表查询
- 表数据量过大查询
- 前三个：聚和、多表、数据量大，可以通过SQL的执行计划，找到慢的原因
- 深度分页查询
SQL执行计划分析过程：
可以采用EXPLAIN、DESC命令获取MySQL如何执行SELECT语句的信息，语法如下：
explain select xxx from xxx where xxx;，就可以得到对应的查询语句的执行情况信息，如下表所示：
image-20231212003726786 possible_keys：可能使用到的索引
key：当前sql实际命中的索引
key_len：使用当前的索引占用的大小（通过key + key_len 检查是否命中索引、索引本身是否存在失效的情况）
extra：额外的优化建议（是否出现了回表的情况，如果存在了，可以尝试添加索引、修改返回字段来解决）
image-20231212004020717 type：这条SQL的连接的类型，性能由好到差依次为：NULL、system、const、eq_ref、ref、range、index、all，最后两种需要优化！（检查索引是否有需要进一步优化的空间，即此次查询是否涉及到全盘扫描、索引树扫描）
- system：此SQL查询的是系统的表
- const：根据主键查询
- eq_ref：主键索引查询或唯一索引查询
- ref：索引查询
- range：范围查询
- index：索引树扫描（需要优化）
- all：全盘扫描（需要优化）
候选人： 如果一条sql执行很慢的话，我们通常会使用mysql自动的执行计划explain来去查看这条sql的执行情况，比如在这里面可以通过key和key_len检查是否命中了索引，如果本身已经添加了索引，也可以判断索引是否有失效的情况，第二个，可以通过type字段查看sql是否有进一步的优化空间，是否存在全索引扫描或全盘扫描，第三个可以通过extra建议来判断，是否出现了回表的情况，如果出现了，可以尝试添加索引或修改返回字段来修复。
索引
主键索引是一种特殊的唯一索引，创建唯一索引的字段的值不能有相同的值，必须保证这个字段无重复值，否则会报错。对于可以包含 NULL 的列，UNIQUE 索引允许多个 NULL 值。
面试官： 了解过索引吗？（什么是索引）
候选人： 嗯，索引在项目中还是比较常见的，它是帮助MySQL高效获取数据的数据结构，主要是用来提高数据检索的效率，降低数据库的IO成本，同时通过索引列对数据进行排序，降低数据排序的成本，也能降低了CPU的消耗。
面试官： 索引的底层数据结构了解过嘛 ?
候选人： MySQL的默认的存储引擎InnoDB采用的B+树的数据结构来存储索引，选择B+树的主要的原因是：
- 阶数更多，路径更短
- 磁盘读写代价B+树更低，非叶子节点只存储指针，用于通过指针导航到指定的存储数据的叶子节点
- B+树便于扫库和区间查询，叶子节点是一个双向链表
- 在找到叶子节点存储的数据时，由于叶子节点之间是双向链表结构，并且叶子节点之间是有序的，所以找到一个叶子节点，可以找到一个任意范围的叶子节点。
面试官： B树和B+树的区别是什么呢？
候选人：
- 在B树中，非叶子节点和叶子节点都会存放数据，而B+树的所有的数据都会出现在叶子节点，在查询的时候，B+树查找效率更加稳定
- 在进行范围查询的时候，B+树效率更高，因为B+树都在叶子节点存储，并且叶子节点是一个双向链表
聚簇索引、非聚簇索引、回表查询
上面以表的id主键列构建的聚集索引为例、name列构建的二级索引为例。
聚集索引图中，叶子结点对应存储的数据是主键id对应的一整行的数据。
二级索引结构图中，叶子结点对应存储的数据是当前对应索引在数据表行中对应的主键值。
回表查询：例如select * from user where name = 'arm'，如上所示，我们对name这一字段添加了索引，所以这个查询语句会先走二级索引，找到对应主键，拿着主键到聚簇索引中查找整行的数据并返回。
面试官： 什么是聚簇索引（聚集索引）什么是非聚簇索引（二级索引） ?
候选人：
聚簇索引主要是指数据与索引放到一块，B+树的叶子节点保存了整行数据，有且只有一个，一般情况下主键在作为聚簇索引的
非聚簇索引（二级索引）指的是数据与索引分开存储，B+树的叶子节点保存对应的主键，可以有多个，一般我们自己定义的索引都是非聚簇索引
面试官： 知道什么是回表查询嘛 ?
候选人： 嗯，其实跟刚才介绍的聚簇索引和非聚簇索引是有关系的，回表的意思就是通过二级索引找到对应的主键值，然后再通过主键值找到聚集索引中所对应的整行数据，这个过程就是回表
【备注：如果面试官直接问回表，则需要先介绍聚簇索引和非聚簇索引】
覆盖索引
覆盖索引指的是：查询使用了索引，并且需要查询出来的列，在当前索引中已经可以全部找到。
第一个查询语句，用主键查询，走的是聚合索引，B+树中存储的是整行数据，所以属于覆盖索引。
第二个查询语句，用二级索引查询，走二级索引，B+树中存储的是主键，id，name都可返回，所以属于覆盖索引。
第三个查询语句，用二级索引查询，先走二级索引，二级索引无法直接拿到gender字段的数据，所以还需要拿着主键，再走聚合索引才能拿到gender字段的数据。所以第三个查询语句不是覆盖索引。需要回表查询。
- 相对来说，回表查询的效率相对较低。所以在开发过程中，应该尽量减少使用回表查询的情况，例如在实际开发过程中应该不使用 select * 来查询数据。（可以作为实习过程中学到的点）
- 在我进行实习的过程中，对之前的项目进行优化，取消了之前MyBatis中Mapper中的一些select * 的情况，大大优化了查询效率：查询效率优化了好几秒。
面试官： 知道什么叫覆盖索引嘛 ?
候选人： 覆盖索引是指select查询语句使用了索引，在返回的列，必须在索引中全部能够找到，如果我们使用id查询，它会直接走聚集索引查询，一次索引扫描，直接返回数据，性能高。（可以扩展的问题：为什么使用主键作为查询条件的话查询效率相对较高？）
如果按照二级索引查询数据的时候，返回的列中没有创建索引，有可能会触发回表查询，尽量避免使用select *，尽量在返回的列中都包含添加索引的字段。
MySQL超大分页处理（limit 导致的性能问题）
在讯飞实习过程中的问题！出的错，老大debug发现了这个问题，然后我修改过来了。
需要瞎编一个场景。
当数据量比较大时，如果进行limit分页查询，在查询时，越往后，分页查询的效率就越低，例如：
因为在进行分页查询时，如果执行 limit 9000000, 10，此时MySQL需要对前9000010条记录进行排序，仅仅返回 9000000-9000010的记录，其他的记录直接丢弃，所以查询过程中的排序操作代价很大。导致超大分页效率极低。
优化思路：一般进行分页查询时，通过创建覆盖索引能够比较好的提升性能，可以通过覆盖索引+子查询的方式进行优化：
面试官： MYSQL超大分页怎么处理 ?（作为实习过程中、项目中的学到的点）
候选人： 嗯，超大分页一般都是在数据量比较大时，我们使用了limit分页查询，并且需要对数据进行排序，这个时候效率就很低，我们可以采用覆盖索引和子查询来解决
先分页查询数据的id字段，确定了id之后，再用子查询来过滤，只查询这个id列表中的数据就可以了
因为查询id的时候，走的覆盖索引，所以效率可以提升很多
索引创建的原则
- 在工作中，那个地方使用到了索引？在科大讯飞实习过程中，设计表？索引。
- 主键索引
- 唯一索引：索引列的值不能有相同的，可以有多个NULL。
- 根据业务创建的索引（复合索引）
- 数据量较大（10w+），并且查询比较频繁的表
- 常作为查询条件，排序、分组的字段
- 内容较长，可以考虑使用前缀索引
- 尽量使用联合索引
- 控制索引的数量！因为索引多了，增删改所需要维护的成本也高了
面试官： 索引创建原则有哪些？
候选人： 嗯，这个情况有很多，不过都有一个大前提，就是表中的数据要超过10万以上，我们才会创建索引，并且添加索引的字段是查询比较频繁的字段，一般也是像作为查询条件，排序字段或分组的字段这些。
还有就是，我们通常创建索引的时候都是使用复合索引来创建，一条sql的返回值，尽量使用覆盖索引，如果字段的区分度不高的话，我们也会把它放在组合索引后面的字段。
如果某一个字段的内容较长，我们会考虑使用前缀索引来使用，当然并不是所有的字段都要添加索引，这个索引的数量也要控制，因为添加索引也会导致新增改的速度变慢。
索引失效？
违反最左前缀法则导致索引失效
当使用联合索引时，要遵守最左前缀法则。指的是查询从索引的最左前列开始，并且不跳过索引中的列。匹配最左前缀法则的例子如下所示：
上图展示了创建的联合索引 tb_seller_index，其中三个索引列是有顺序的：name、status、address
依次展示了以 name、name status、name status address 作为查询条件的查询情况，显示这三个查询过程都正确的命中了索引。
如果没有按照上述的索引顺序查询或者跳过了某些字段查询（违反最左前缀法则），则可能发生索引失效的场景：
上述的场景直接单独的使用排在第二、第三个的索引列，跳过了第一个，违反了最左前缀法则，明显是无法命中索引的。
如果符合最左前缀法则，但是出现跳过了某一列，只有当前查询条件的字段符合最左前缀法则的部分才会命中索引，例如：
上述查询跳过了 status 这一列，只有 name遵守了最左前缀法则，所以虽然命中了索引，但是只有一列name命中了索引。address没有生效。
范围查询导致的索引失效
范围查询右边的列，不能使用索引。
由explain的keylen字段，可以发现，第一个查询语句没有任何问题，三个条件字段都命中了索引，但是第二个查询语句，三个条件字段只有前两个命中了索引，第三个没有走索引，因为第二个status使用了范围查询，所以范围查询字段后面的所有字段都不能走索引，也就是address不能走索引，导致索引失效。
在索引列上执行运算导致索引失效
字符串不加单引号导致索引失效
这是因为没有对字符串加单引号，底层的优化器会自动进行类型转换，这样做会导致索引失效。
模糊查询可能会导致索引失效
使用%开头的Like 模糊查询（%xxx、%xxx%这两种情况），可能会导致索引失效。如果仅仅是尾部匹配，索引不会失效。如果是头部匹配，索引失效。
面试官： 什么情况下索引会失效 ?
候选人： 这种情况比较多，我说一些自己的经验，以前遇到过的：
比如，索引在使用的时候没有遵循最左匹配法则，第二个是，模糊查询，如果%号在前面也会导致索引失效。如果在添加索引的字段上进行了运算操作或者类型转换也都会导致索引失效。
我们之前还遇到过一个就是，如果使用了复合索引，中间使用了范围查询，右边的条件索引也会失效
所以，通常情况下，想要判断出这条sql是否有索引失效的情况，可以使用explain执行计划来分析
SQL优化经验
可以从下面的五个方向来解释SQL的优化经验：
表的设计优化(阿里开发手册《嵩山版》)
- 比如设置合适的数值（tinyint、int、bigint）
- 设置合适的字符串（char、varchar、text），char定长，效率高，varchar可变长度，效率低
索引优化（参见索引创建原则、索引失效部分）
SQL语句优化
select语句务必指定查询的字段名称，不能使用select *
SQL语句要避免索引失效的写法
尽量使用union all，不使用union，因为union会自带一次过滤操作，效率低：
select * from user where id > 2 union | union all select * from user where id < 5避免在where条件后面对字段进行表达式操作，避免联合索引失效
JOIN优化：能使用 inner join 就不要使用 left、right join，如必须使用，则一定要以小表为驱动。因为使用innerJOIN时，MySQL会自动优化，优先把小表放到外面（作为驱动），把大表放到里面。left join、right join不会自动优化，所以需要我们在查询时自己手动将小表设为驱动。
主从复制、读写分离
- 如果在当前项目组中，对于数据库的读操作远大于数据库的写操作，为了避免写操作对读操作的效率造成的影响，则可以考虑对数据库使用读写分离式架构，Master作为写入节点，Slave作为读节点。
分库分表（参见分库分表部分）
面试官： sql的优化的经验
候选人： 嗯，这个在项目还是挺常见的，当然如果直说sql优化的话，我们会从这几方面考虑，比如
建表的时候、使用索引、sql语句的编写、主从复制，读写分离，还有一个是如果量比较大的话，可以考虑分库分表
面试官： 创建表的时候，你们是如何优化的呢？
候选人： 这个我们主要参考的阿里出的那个开发手册《嵩山版》，就比如，在定义字段的时候需要结合字段的内容来选择合适的类型，如果是数值的话，像tinyint、int 、bigint这些类型，要根据实际情况选择。如果是字符串类型，也是结合存储的内容来选择char和varchar或者text类型
面试官： 那在使用索引的时候，是如何优化呢？
候选人：【参考索引创建原则 进行描述】
面试官： 你平时对sql语句做了哪些优化呢？
候选人： 嗯，这个也有很多，比如SELECT语句务必指明字段名称，不要直接使用select * ，还有就是要注意SQL语句避免造成索引失效的写法；如果是聚合查询，尽量用union all代替union ，union会多一次过滤，效率比较低；如果是表关联的话，尽量使用innerjoin ，不要使用用left join right join，如必须使用 一定要以小表为驱动
事务相关内容
面试官： 事务的特性是什么？可以详细说一下吗？
候选人： 嗯，这个比较清楚，ACID，分别指的是：原子性、一致性、隔离性、持久性；我举个例子：
A向B转账500，转账成功，A扣除500元，B增加500元，原子操作体现在要么都成功，要么都失败
在转账的过程中，数据要一致，A扣除了500，B必须增加500
在转账的过程中，隔离性体现在A像B转账，不能受其他事务干扰
在转账的过程中，持久性体现在事务提交后，要把数据持久化（可以说是落盘操作）
面试官：并发事务带来哪些问题？
候选人：
我们在项目开发中，多个事务并发进行是经常发生的，并发也是必然的，有可能导致一些问题
第一是脏读， 当一个事务正在访问数据并且对数据进行了修改，而这种修改还没有提交到数据库中，这时另外一个事务也访问了这个数据，因为这个数据是还没有提交的数据，那么另外一个事务读到的这个数据是“脏数据”，依据“脏数据”所做的操作可能是不正确的。
第二是不可重复读：比如在一个事务内多次读同一数据。在这个事务还没有结束时，另一个事务也访问该数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改导致第一个事务两次读取的数据可能不太一样。这就发生了在一个事务内两次读到的数据是不一样的情况，因此称为不可重复读。
第三是幻读（Phantom read）：幻读与不可重复读类似。它发生在一个事务（T1）读取了几行数据，接着另一个并发事务（T2）插入了一些数据时。在随后的查询中，第一个事务（T1）就会发现多了一些原本不存在的记录，就好像发生了幻觉一样，所以称为幻读。
面试官：怎么解决这些问题呢？MySQL的默认隔离级别是？
候选人：解决方案是对事务进行隔离
MySQL支持四种隔离级别，分别有：
第一个是，未提交读（read uncommitted）它解决不了刚才提出的所有问题，一般项目中也不用这个。第二个是读已提交（read committed）它能解决脏读的问题的，但是解决不了不可重复读和幻读。第三个是可重复读（repeatable read）它能解决脏读和不可重复读，但是解决不了幻读，这个也是mysql默认的隔离级别。第四个是串行化（serializable）它可以解决刚才提出来的所有问题，但是由于让是事务串行执行的，性能比较低。所以，我们一般使用的都是mysql默认的隔离级别:可重复读
面试官：undo log和redo log的区别
undo log：回滚日志，在 insert、delete、update的时候产生的便于数据回滚的日志。
当insert的时候，产生的undo log的日志只在数据回滚时使用，在事务提交之后，undo log的内容可以立即删除。
当update、delete的时候，产生的undo log的日志不仅在回滚时需要，mvcc版本访问时也需要，不会被立刻删除。
候选人：好的，其中redo log日志记录的是数据页的物理变化，服务宕机可用来同步数据，而undo log 不同，它主要记录的是逻辑日志，当事务回滚时，通过逆操作恢复原来的数据，比如我们删除一条数据的时候，就会在undo log日志文件中新增一条delete语句，如果发生回滚就执行逆操作；
redo log保证了事务的持久性，undo log保证了事务的原子性和一致性
面试官：事务中的隔离性是如何保证的呢？(你解释一下MVCC)
候选人：事务的隔离性是由锁和mvcc实现的。
其中mvcc的意思是多版本并发控制。指维护一个数据的多个版本，使得读写操作没有冲突，它的底层实现主要是分为了三个部分，第一个是隐藏字段，第二个是undo log日志，第三个是readView读视图
隐藏字段是指：在mysql中给每个表都设置了隐藏字段，有一个是trx_id(事务id)，记录每一次操作的事务id，是自增的；另一个字段是roll_pointer(回滚指针)，指向上一个版本的事务版本记录地址
undo log主要的作用是记录回滚日志，存储老版本数据，在内部会形成一个版本链，在多个事务并行操作某一行记录，记录不同事务修改数据的版本，通过roll_pointer指针形成一个链表，链表的头部记录的是最新的旧记录，链表的尾部记录的是最老的旧记录。
readView解决的是一个事务查询选择版本的问题，在内部定义了一些匹配规则和当前的一些事务id判断该访问那个版本的数据，不同的隔离级别快照读是不一样的，最终的访问的结果不一样。如果是rc隔离级别，每一次执行快照读时生成ReadView，如果是rr隔离级别仅在事务中第一次执行快照读时生成ReadView，后续复用
面试官：MySQL主从同步原理
候选人：MySQL主从复制的核心就是二进制日志(DDL（数据定义语言）语句和 DML（数据操纵语言）语句)，它的步骤是这样的：
第一：主库在事务提交时，会把数据变更记录在二进制日志文件 Binlog 中。
第二：从库读取主库的二进制日志文件 Binlog ，写入到从库的中继日志 Relay Log 。
第三：从库重做中继日志中的事件，将改变反映它自己的数据
面试官：你们项目用过MySQL的分库分表吗？
候选人：
嗯，因为我们都是微服务开发，每个微服务对应了一个数据库，是根据业务进行拆分的，这个其实就是垂直拆分。
面试官：那你之前使用过水平分库吗？
候选人：
嗯，这个是使用过的，我们当时的业务是(xxx)，一开始，我们也是单库，后来这个业务逐渐发展，业务量上来的很迅速，其中(xx)表已经存放了超过1000万的数据，我们做了很多优化也不好使，性能依然很慢，所以当时就使用了水平分库。
我们一开始先做了3台服务器对应了3个数据库，由于库多了，需要分片，我们当时采用的mycat来作为数据库的中间件。数据都是按照id（自增）取模的方式来存取的。
当然一开始的时候，那些旧数据，我们做了一些清洗的工作，我们也是按照id取模规则分别存储到了各个数据库中，好处就是可以让各个数据库分摊存储和读取的压力，解决了我们当时性能的问题