面试突击系列之：高频面试题解说四-CSDN博客

本文链接：https://blog.csdn.net/qq_38665235/article/details/120417160

概述

上次说都是redis的，今天咱们看看mysql的高频面试点，5年前可能懂点索引、常规sql优化就完事了，随着互联网对我们的影响，用户越来越多，使得公司对开发人员的技术要求也越来越高。

对mysql要求也到了更深一步的层次了，来看下我们今天准备的面试题：

有条sql没有走索引导致性能很慢如何定位问题
说下mysql中b+tree索引
mysql默认隔离级别是什么，如何支持它的？
说下mysql几种常见log
mysql主从模式下如何解决同步延时问题

全篇5600多字，字数有点偏多，建议细心看完。

定位慢sql原因

实际开发时由于项目初期用户量少，数据量也随之较小，我们写的sql执行效率很快，即便没有做过什么优化也是毫秒级运行。

一旦系统推广成功，业务量呈现爆发式增长我们的sql运行效率也会越来越慢，甚至达到分钟级。

我们就需要定位这些慢sql的原因了，从而进行针对性的优化，不然盲目的优化都是无用功。

个人认为较为合适的回答：

在数据库服务端开启慢日志记录，将那些执行耗时过长的sql记录下来，在逐个分析他们的执行计划，对其针对性的优化，提高sql运行效率。

关于执行计划我一般关注type、key这2个字段，type字段是实际执行时采用的方式，key是可能会用到，type字段一般有这几个值 ALL(全表扫描) 、ref(根据索引定位)、range（对索引树范围扫描）、index(扫描整个索引树)、const（根据主键定位）。

然后再说下哪些语法导致索引失效基本就ok了。

可能有的人没怎么关注sql执行计划，咱们通过几个案例看下，首先我们在mysql server端开启慢日志，有这么3个参数：

slow_query_log ：慢查询开启状态，ON开启，OFF关闭
slow_query_log_file ：慢查询日志存放的位置
long_query_time ：查询超过多少秒才记录

如果慢日志过多的话，需要专门的软件进行处理，我们自己也可以写程序解析出来具体的sql。

其实通过explain来查看sql的执行计划，通过分析执行找到原因，进行相应的优化，来看下sql的执行计划到底长什么样。


mysql> EXPLAIN select * from cb_log;
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | cb_log | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    6 |   100.00 | NULL  |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

这是最简单sql的执行计划，我们可以看到type字段值为ALL（全表查的意思），key字段为null什么也没用到。


mysql> EXPLAIN select * from cb_log where operator_name like '123%';
+----+-------------+--------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+-----------------------+
| id | select_type | table  | partitions | type  | possible_keys         | key                   | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+--------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | cb_log | NULL       | range | idx_log_operator_name | idx_log_operator_name | 243     | NULL |    1 |   100.00 | Using index condition |
+----+-------------+--------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

这是一个模糊查询sql,并且operator_name字段上有索引，此时type字段值为ref(对索引树范围查询)，key字段为idx_log_operator_name 索引名称。

好现在我们看如果书写不正常导致索引失效是怎样的：


mysql> EXPLAIN select * from cb_log where operator_name like '%123';
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | cb_log | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    6 |    16.67 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

可以看到如果%在左侧此时mysql会放弃索引扫描，执行全表扫描，测试表里面没有数据的，索引不要上面关注耗时，如果数据量上来我们就可以看到这2种扫描方式性能差别还是很大的。

这就是左前缀原则，也就是说对于like模糊匹配的时候只有左前缀是可以利用到索引的，这个和多个字段建立联合索引有点类似，其实这个要从索引树的存储形式说起。

咱们再看一个例子：

mysql> EXPLAIN select * from cb_log where created>now();
+----+-------------+--------+------------+-------+-----------------+-----------------+---------+------+------+----------+-----------------------+
| id | select_type | table  | partitions | type  | possible_keys   | key             | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+--------+------------+-------+-----------------+-----------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | cb_log | NULL       | range | idx_log_created | idx_log_created | 6       | NULL |    1 |   100.00 | Using index condition |
+----+-------------+--------+------------+-------+-----------------+-----------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

mysql> EXPLAIN select * from cb_log where DATE_ADD(created,INTERVAL 1 DAY) >now();
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | cb_log | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    6 |   100.00 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

第一个是根据时间字段做条件查询的sql，采用的时间字段idx_log_created索引，但是第二个sql我们在create上做运算，导致索引失效了进行的全表扫描。

明明时间字段有索引但是mysql执行过程中并没有采用，因为咱们对索引列进行了运算操作。

此时有朋友就说了这些对于有经验的我平时书写的时候注意下就行了，用不着执行计划，那你就打错特错了，我们只是举了2个简单例子，对于多表联查，或者多个子查询嵌套等等情况下就不是那么容易肉眼分辨了，此时explian就是我们定位问题的利器。

有些情况下即便是多层嵌套多表联查并且都走索引情况下仍然耗时过长，这种情况需要注意是否存在回表操作。

如果大量的数据存在回表问题这个耗时那是相当严重的。

说下mysql中b+tree索引

一般了解完面试者对sql常见问题处理能力后就会b+tree ，还是先看如何回答面试官，再详解。

mysql的innodb存储引擎就是采用b+tree存储数据的，因为它的根节点和支节点没有存储数据，这样可以存储更多的主键，从而降低数据的高度，这也是它和btree的区别。

通常树高度为3-4层，层数越多查询效率就越慢（层数越多与磁盘交互次数也就越多），b+tree可以有效降低树的高度，所有数据保存在树的叶子节点，所以我们根据主键查询的时候基本与磁盘交互3-4次就可以定位到数据。

并且所有叶子节点又构成了链表结构用于全表查询，支节点中保存到是主键值以及与之对应数据的指针。

支节点按照主键大小从左到右排序（普通索引，联合索引也是如此），这是为什么左前缀可以用索引的原因。

我们给别的字段创建普通索引的时候也是用b+tree存储的，这时候叶子节点存储的是该字段的值以及主键值，通过这个主键值再去遍历主键的b+tree从而拿到数据。

以上回答完基本重要点都说到了，其余可以自由发挥，下面画个图看下具体b+tree长什么样子：

再来看下普通索引的主键是什么样，以及和主键索引的关系:

通过这两张图相信大家已经很清晰了吧，多个字段的联合索引和图中的name字段索引是一样的，按照你创建索引是字段顺序排。

相信大多人也理解为什么左前缀了，为什么主键查询最快了吧，b+tree到这里也就差不多了。

mysql中默认隔离级别是什么，如何支持它的？

笔者最开始是在学习spring的时候接触这个概念的，曾经一度认为这是spring的，随着对mysql接触越来越多才了解到这是数据库提出的标准概念，每种数据库又对其进行的不同的实现和优化。

mysql默认的隔离级别是rr(全称Repeatable Read)，项目开发中常用的有两种 rc（读已提交）与rr（可重复读）。每种隔离级别面临的问题。。。。。

面试官可能会继续追问mysql到底是如何支持rc rr进而考察你对mysql的mvcc是否了解。

标准的rr是会出现幻读的，但是mysql就比较牛逼它就可以解决所有问题这里可以参考我之前写的MYSQl深入探索系列三 MVCC机制 把这篇读懂了回答这个问题就是毛毛雨

咱们这里说下标准的隔离级别会带来的常见问题：

幻想读：

脏读

不可重复度

说下mysql中的log的都哪些

这个问题其实也是和mvcc关联的，mysql中的log有三种 binlog、undo log、redo lof，后面两者参考我之前写的：

具体的回答方式自己总结，咱们这里说下binlog，binlog不是innodb特有的，它是整个service层的日志，所以它占用空间还是很大的。

mysql主从同步就是基于binlog做的，它记录了事务完成的执行过程，所以别人得到它也就得到了你的数据（非全量）,它有三种记录方式Statement、Row以及Mixed。

基于SQL语句的复制(statement-based replication,SBR)。

优点：不需要记录每一行的变化，减少了binlog日志量，节约了IO，提高性能。

缺点：由于记录的只是执行语句，为了这些语句能在slave上正确运行，因此还必须记录每条语句在执行的时候的一些相关信息，以保证所有语句能在slave得到和在master端执行时候相同的结果，所以性能较慢，体积较大。
基于行的复制(row-based replication,RBR)。

优点：非常清楚的记录下每一行数据修改的细节。

缺点：所有的执行的语句当记录到日志中的时候，都将以每行记录的修改来记录，这样可能会产生大量的日志内容。
混合模式复制(mixed-based replication,MBR)。

实际就是前两种的结合，一般的语句修改使用statment格式保存binlog，如一些函数，statement无法完成主从复制的操作，则采用row格式保存binlog，MySQL会根据执行的每一条具体的sql语句来区分对待记录的日志形式，也就是在Statement和Row之间选择一种。