MySQL面试知识点总结(持续更新)

1. InnoDB和MyISAM存储引擎的存储文件格式

InnoDB（聚簇）

.frm 文件：存储表结构

.ibd 文件：存储索引和表数据信息。所以说InnoDB为聚簇索引，即索引和表数据在一个文件中存储。

MyISAM（非聚簇）

.frm 文件：存储表结构。

.MYI 文件：存储索引信息。

.MYD 文件：存储表数据信息。

​​​​​​​

 

2. 为什么InnoDB表必须有主键？并且推荐使用整型的自增主键？

①因为InnoDB表的数据文件本身就是依照B+Tree数据结构组织的，即使用户没有显式的指定主键值，在Mysql底层也会默认维护一个字段作为表的主键值，如果没有字段则维护一个隐藏的主键值列。

②分为两个方面：

其一是整型：（比较简单，占用空间小）因为索引即B+Tree的特性一棵排序好的数据结构，左侧叶子结点一定小于右侧叶子结点的值，在进行主键索引查询的时候，需要进行比较冗余索引主键值的大小，显然整型的效率要高于其它类型。

其二是自增：（利于插入）因为B+Tree的特性涉及到树的自平衡，如果是乱序的整型主键，则在维护树的时候需要进行对比，确定位置，并且进行平衡，而如果是有序自增的主键，则只要默认将值添加到最右侧叶子结点再平衡即可。显然后者内存消耗更小。

3. 为什么非主键索引结构的叶子结点存储的是表的主键值？

①保持一致性：如果存储行数据，当在进行插入操作时，需要同时维护主键索引的data数据和非主键索引的data数据，效率低，并且会产生事务性的操作。

②存储效率：使得存储利用率更高。

4. 使用主键索引（聚簇索引）查询和非主键索引（二级索引）查询数据过程有什么区别？

主键索引：搜索主键索引的B+Tree找到对应的主键即可查询到data数据，

非主键索引：（回表操作）data里面存储的为主键值，所以需要先搜索非主键索引的B+Tree找到主键值，再根据主键值进行主键索引的B+Tree搜索，才可拿到data数据。如果是覆盖索引的话，则不需要回表操作。

5. Mysql数据库各种数据类型所占用的空间？

字符串：

char(n)：n字节长度

varchar(n)：2字节存储字符串长度，如果是utf-8，则长度 3n+ 2

数值类型：

tinyint：1字节

smallint：2字节

int：4字节

bigint：8字节

时间类型：

date：3字节

timestamp：4字节

datetime：8字节

6. 索引数据结构解析

二叉树：由于二叉树树的性质，并不是一个平衡二叉树，在索引递增的情况下，可能出现较高的层级，无法达到快速索引的效果。

红黑树：红黑树是一棵平衡树，首先当存储数据量较大的时候会有较大的树深，无法控制层数，其次会发生自旋导致维护复杂性能较低。

B-Tree：B树是一棵平衡二叉树，可进行多节点存储，一定程度上可以保证大数据量节点存储情况下的层高限制。但是如果每个节点都存储data数据的话，则会导致一层节点的索引数据占用存储空间变大，加载时间变长，存储的节点数变少，所以出现了变种B+Tree的数据结构。

B+Tree：B+Tree是B树的一个变种，主要区别在于，非叶子节点不再存储data数据（只存储索引字段值，可以存储更多的节点索引），且叶子节点之间有左右指针（指针实数是双向的，方便进行范围查询）

Hash：Hash的查询复杂度为1，即计算当前索引的hash值进行存储，缺点数据量较大是有小概率出现hash碰撞，另外无法进行范围查询。

7. 索引是怎么支撑千万级表查找的？

MySQL的索引B+Tree索引如下图：

MySQL中InnoDB引擎配置的默认节点大小为16KB。

假设为整型作为主键，则一个索引值占据为（8 byte）+ 紧跟的磁盘指针（6byte）=14byte，则一个节点可存储的索引值大概为1170个，而一个有data数据的叶子节点占用大小为1K，则叶子节点可存储16个数据，则改索引数共可存储 1170*1170*16 约为 2190W左右的数据，仅需要3次磁盘IO，足以保证高效率。