面试大厂 看这篇MySQL面试题就够了

MySQL一直是面试中的热点问题，也难道了很多的面试者。其实MySQL没那么难，只是大家没有系统化、实战性的过去学习、总结。同时很多开发者在实际的开发过程中也很少去接触一些偏向底层的知识。

今天这篇文章，将为大家总结MySQL中场景的面试题。围绕索引、事务、锁等几个方面的热点问题，系统化的总结。​​涉及到文章篇幅，可以通过该文阅读全文内容​​。大致分为如下大纲：

什么是索引？请简述常用的索引有哪些种类?

索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息。如果想按特定职员的姓来查找他或她，则在表中搜索所有的行相比，索引有助于更快地获取信息通俗的讲，索引就是数据的目录，就像看书一样，假如我想看第三章第四节的内容，如果有目录，我直接翻目录，找到第三章第四节的页码即可。如果没有目录，我就需要将从书的开头开始，一页一页翻，直到翻到第三章第四节的内容。

MySQL索引的分类

MySQL中索引分为B+Tree索引、hash索引、空间索引(选择什么样的索引方式，需要看具体的存储引擎)。B+Tree又分为聚集索引和非聚集索引。聚集索引包含了主键索引。非聚集索引包含，唯一索引、前缀索引、联合索引、覆盖索引和普通索引。

索引是个什么样的数据结构呢?

索引的数据结构和具体存储引擎的实现有关, 在MySQL中使用较多的索引有Hash索引,B+树索引等。而我们经常使用的InnoDB存储引擎的默认索引实现为:B+Tree索引。

Hash索引和B+树所有有什么区别或者说优劣呢?

首先要知道Hash索引和B+树索引的底层实现原理:

1. hash索引底层就是hash表，进行查找时，调用一次hash函数就可以获取到相应的键值，之后进行回表查询获得实际数据。
2. B+树底层实现是多路平衡查找树，对于每一次的查询都是从根节点出发，查找到叶子节点方可以获得所查键值，然后根据查询判断是否需要回表查询数据。

那么可以看出他们有以下的不同：

1. hash索引进行等值查询更快(一般情况下)，但是却无法进行范围查询。因为在hash索引中经过hash函数建立索引之后，索引的顺序与原顺序无法保持一致，不能支持范围查询。而B+树的的所有节点皆遵循(左节点小于父节点，右节点大于父节点，多叉树也类似)，天然支持范围。
2. hash索引不支持使用索引进行排序，原理同上。
3. hash索引不支持模糊查询以及多列索引的最左前缀匹配.原理也是因为hash函数的不可预测，AAAA和AAAAB的索引没有相关性。
4. hash索引任何时候都避免不了回表查询数据，而B+树在符合某些条件(聚簇索引，覆盖索引等)的时候可以只通过索引完成查询。
5. hash索引虽然在等值查询上较快，但是不稳定，性能不可预测。当某个键值存在大量重复的时候，发生hash碰撞，此时效率可能极差。而B+树的查询效率比较稳定，对于所有的查询都是从根节点到叶子节点，且树的高度较低。

因此，在大多数情况下，直接选择B+树索引可以获得稳定且较好的查询速度。而不需要使用hash索引。

上面提到了B+树在满足聚簇索引和覆盖索引的时候不需要回表查询数据,什么是聚簇索引?

在B+树的索引中，叶子节点可能存储了当前的key值，也可能存储了当前的key值以及整行的数据，这就是聚簇索引和非聚簇索引.。在InnoDB中，只有主键索引是聚簇索引，如果没有主键，则挑选一个唯一键建立聚簇索引，如果没有唯一键，则隐式的生成一个键来建立聚簇索引。当查询使用聚簇索引时，在对应的叶子节点，可以获取到整行数据,因此不用再次进行回表查询。

非聚簇索引一定会回表查询吗?

当数据库有并发事务的时候，可能会产生数据的不一致，这时候需要一些机制来保证访问的次序，锁机制就是这样的一个机制。就像酒店的房间，如果大家随意进出，就会出现多人抢夺同一个房间的情况，而在房间上装上锁，申请到钥匙的人才可以入住并且将房间锁起来，其他人只有等他使用完毕才可以再次使用。

MySQL的binlog有有几种录入格式?分别有什么区别?

有三种格式,statement,row和mixed。statement模式下，记录单元为语句。即每一个sql造成的影响会记录，由于sql的执行是有上下文的，因此在保存的时候需要保存相关的信息，同时还有一些使用了函数之类的语句无法被记录复制。 row级别下，记录单元为每一行的改动，基本是可以全部记下来但是由于很多操作，会导致大量行的改动(比如alter table)，因此这种模式的文件保存的信息太多，日志量太大。 mixed，一种折中的方案，普通操作使用statement记录，当无法使用statement的时候使用row。 此外，新版的MySQL中对row级别也做了一些优化，当表结构发生变化的时候，会记录语句而不是逐行记录。

聚簇索引和非聚簇索引的区别？

1. 聚簇索引的子节点都是存的整行数据，二非聚簇索引的子节点存储的是主键值。
2. 聚簇索引可以直接检索出数据，不需要在去表查询一次数据。而非聚簇索引需要回表查询，先根据索引的key得到主键值，在根据主键值回表查询数据。

什么是索引下推

索引下推定义

1. 索引下推（index condition pushdown ）简称ICP，在Mysql5.6的版本上推出，用于优化查询。
2. 在不使用ICP的情况下，在使用非主键索引（又叫普通索引或者二级索引）进行查询时，存储引擎通过索引检索到数据，然后返回给MySQL服务器，服务器然后判断数据是否符合条件 。
3. 在使用ICP的情况下，如果存在某些被索引的列的判断条件时，MySQL服务器将这一部分判断条件传递给存储引擎，然后由存储引擎通过判断索引是否符合MySQL服务器传递的条件，只有当索引符合条件时才会将数据检索出来返回给MySQL服务器 。
4. 索引条件下推优化可以减少存储引擎查询基础表的次数，也可以减少MySQL服务器从存储引擎接收数据的次数。

ICP查询机制

1. 存储引擎读取索引记录（不是完整的行记录）。
2. 判断WHERE条件部分能否用索引中的列来做检查，条件不满足，则处理下一行索引记录。
3. 条件满足，使用索引中的主键去定位并读取完整的行记录（就是所谓的回表）。
4. 存储引擎把记录交给Server层，Server层检测该记录是否满足WHERE条件的其余部分。

索引下推条件

1. 只能用于range、 ref、 eq_ref、ref_or_null访问方法。
2. 只能用于InnoDB和 MyISAM存储引擎及其分区表。
3. 对InnoDB存储引擎来说，索引下推只适用于二级索引（也叫辅助索引）。
4. 引用了子查询的条件不能下推。
5. 引用了存储函数的条件不能下推，因为存储引擎无法调用存储函数。

索引下推的目的是为了减少回表次数，也就是要减少IO操作。对于InnoDB的聚簇索引来说，数据和索引是在一起的，不存在回表这一说。

索引下推开关

索引条件下推默认是开启的，可以使用系统参数optimizer_switch来控制器是否开启。

索引下推举例

假设有一个需求，要求匹配姓名第一个字为陈，年龄为20岁的用户，此时的sql语句如下：

SELECT * from user where  name like '陈%' and age=20

5.6之前的版本是没有索引下推这个优化的，会忽略age这个字段，直接通过name进行查询，在(name,age)这课树上查找到了两个结果，id分别为2,3，然后拿着取到的id值一次次的回表查询，因此这个过程需要回表两次。

在5.6之后，InnoDB并没有忽略age这个字段，而是在索引内部就判断了age是否等于20，对于不等于20的记录直接跳过，因此在(name,age)这棵索引树中只匹配到了一个记录，此时拿着这个id去主键索引树中回表查询全部数据，这个过程只需要回表一次。

MySQL中varchar和char的区别是什么?

1. varchar长度是不固定的，char属于定长。例如，在定义表结构是char定义36个长度，如果字符内容没有36个长度，剩余的会用空格占满，而varchar不会，会根据字符的长度自动设置长度。
2. char会单独内置一个字段来存储字符长度，而varchar则是每次动态计算字符长度。
3. 当字符内容超过设置的长度，多余的部分不会被插入到字段中。

MySQL的timestamp和datetime之间的区别?

1. timestamp存储4个字节，datetime存储的是8个字节。
2. 对于TIMESTAMP，它把客户端插入的时间从当前时区转化为UTC（世界标准时间）进行存储。查询时，将其又转化为客户端当前时区进行返回。而对于DATETIME，不做任何改变，是原样输入和输出。
3. 存储的时间范围不一样，timestamp存储到2038年，datetime存储到9999年。

InnoDB索引与MyISAM索引实现的区别是什么？

• ​​MyISAM的索引方式都是非聚簇的，与InnoDB包含1个聚簇索引是不同的。​​
• 在InnoDB存储引擎中，我们只需要根据主键值对聚簇索引进行一次查找就能找到对应的记录，而在MyISAM中却需要进行一次回表操作，意味着MyISAM中建立的索引相当于全部都是二级索引 。
• InnoDB的数据文件本身就是索引文件，而MyISAM索引文件和数据文件是分离的 ，索引文件仅保存数据记录的地址。
• MyISAM的表在磁盘上存储在以下文件中： *.sdi（描述表结构）、*.MYD（数据），*.MYI（索引）
• InnoDB的表在磁盘上存储在以下文件中： .ibd（表结构、索引和数据都存在一起）
• InnoDB的非聚簇索引data域存储相应记录主键的值 ，而MyISAM索引记录的是地址 。换句话说，InnoDB的所有非聚簇索引都引用主键作为data域。
• MyISAM的回表操作是十分快速的，因为是拿着地址偏移量直接到文件中取数据的，反观InnoDB是通过获取主键之后再去聚簇索引里找记录，虽然说也不慢，但还是比不上直接用地址去访问。
• InnoDB要求表必须有主键 （ MyISAM可以没有 ）。如果没有显式指定，则MySQL系统会自动选择一个可以非空且唯一标识数据记录的列作为主键。如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整型。

说一下B+树索引实现原理（数据结构）

假设有一个表index_demo，表中有2个INT类型的列，1个CHAR(1)类型的列，c1列为主键：

CREATE TABLE index_demo(c1 INT,c2 INT,c3 CHAR(1),PRIMARY KEY(c1)) ;

index_demo表的简化的行格式示意图如下：

我们只在示意图里展示记录的这几个部分：

• ​​record_type：​​表示记录的类型， 0是普通记录、 2是最小记录、 3 是最大记录、1是B+树非叶子节点记录。
• ​​next_record：​​表示下一条记录的相对位置，我们用箭头来表明下一条记录。
• ​​各个列的值：​​这里只记录在 index_demo 表中的三个列，分别是 c1 、 c2 和 c3 。
• ​​其他信息：​​除了上述3种信息以外的所有信息，包括其他隐藏列的值以及记录的额外信息。

将​​其他信息​​项暂时去掉并把它竖起来的效果就是这样：

把一些记录放到页里的示意图就是（这里一页就是一个磁盘块，代表一次IO）：

name age sex

​​MySQL InnoDB的默认的页大小是16KB​​，因此数据存储在磁盘中，可能会占用多个数据页。如果各个页中的记录没有规律，我们就不得不依次遍历所有的数据页。​​如果我们想快速的定位到需要查找的记录在哪些数据页中​​，我们可以这样做 ：

• 下一个数据页中用户记录的主键值必须大于上一个页中用户记录的主键值
• 给所有的页建立目录项

以​​页28​​为例，它对应​​目录项2​​ ，这个目录项中包含着该页的​​页号28​​以及该页中用户记录的​​最小主键值 5​​。我们只需要把几个目录项在物理存储器上连续存储（比如：数组），就可以实现根据主键值快速查找某条记录的功能了。​​比如：查找主键值为 20 的记录，具体查找过程分两步：​​

1. 先从目录项中根据二分法快速确定出​​主键值为20的记录在目录项3中​​（因为 12 ≤ 20 < 209 ），​​对应页9​​。
2. 再到页9中根据二分法快速定位到主键值为 20 的用户记录。

至此，针对数据页做的简易目录就搞定了。这个目录有一个别名，称为​​索引​​ 。