MySQL大厂面试题甄选，150道mysql数据库高频面试题讲解 MySQL数据库面试题宝典，mysql面试必考！msql工作必

# Mysql面试题 [toc] ## 一 Mysql索引 ### 001 Mysql如何实现的索引机制？ MySQL中索引分三类：B+树索引、Hash索引、全文索引 #### 002 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个字节，类型为长整型。  #### 003 一个表中如果没有创建索引，那么还会创建B+树吗？ 会 - 如果有主键会创建聚簇索引 - 如果没有主键会生成rowid作为隐式主键 ### 004 说一下B+树索引实现原理（数据结构） #### 讲义 假设有一个表index_demo，表中有2个INT类型的列，1个CHAR(1)类型的列，c1列为主键： ```sql 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 的用户记录。 至此，针对数据页做的简易目录就搞定了。这个目录有一个别名，称为`索引` 。 #### InnoDB中的索引方案 我们新分配一个编号为30的页来专门存储`目录项记录`，页10、28、9、20专门存储`用户记录`：   `目录项记录和普通的用户记录的不同点：` - 目录项记录 的 record_type 值是1，而 普通用户记录 的 record_type 值是0。 - 目录项记录只有主键值和页的编号两个列，而普通的用户记录的列是用户自己定义的，包含很多列，另外还有InnoDB自己添加的隐藏列。 `现在查找主键值为 20 的记录，具体查找过程分两步：` 1. 先到页30中通过二分法快速定位到对应目录项，因为 12 ≤ 20 < 209 ，就是页9。 2. 再到页9中根据二分法快速定位到主键值为 20 的用户记录。 **更复杂的情况如下：** 我们生成了一个存储更高级目录项的 页33 ，这个页中的两条记录分别代表页30和页32，如果用户记录的主键值在 `[1, 320)` 之间，则到页30中查找更详细的目录项记录，如果主键值 不小于320 的话，就到页32中查找更详细的目录项记录。**这个数据结构，它的名称是 B+树 。**  #### 005 聚簇索引与非聚簇索引b+树实现有什么区别？ ##### 聚簇索引 **特点：** - `索引和数据保存在同一个B+树中` - `页内的记录`是按照`主键`的大小顺序排成一个`单向链表` 。 - `页和页之间`也是根据页中记录的`主键`的大小顺序排成一个`双向链表` 。 - 非叶子节点存储的是记录的`主键+页号`。 - 叶子节点存储的是`完整的用户记录`。 **优点：** - 数据访问更快 ，因为`索引和数据保存在同一个B+树中`，因此从聚簇索引中获取数据比非聚簇索引更快。 - 聚簇索引对于主键的`排序查找`和`范围查找`速度非常快。 - 按照聚簇索引排列顺序，查询显示一定范围数据的时候，由于`数据都是紧密相连`，数据库可以从更少的数据块中提取数据，`节省了大量的IO操作` 。 **缺点：** - 插入速度严重依赖于插入顺序 ，按照主键的顺序插入是最快的方式，否则将会出现页分裂，严重影响性能。因此，对于InnoDB表，我们一般都会定义一个`自增的ID列为主键`。 - 更新主键的代价很高 ，因为将会导致被更新的行移动。因此，对于InnoDB表，我们一般定义`主键为不可更新`。 **限制：** - 只有InnoDB引擎支持聚簇索引，`MyISAM不支持聚簇索引`。 - 由于数据的物理存储排序方式只能有一种，所以`每个MySQL的表只能有一个聚簇索引`。 - 如果没有为表定义主键，InnoDB会选择`非空的唯一索引列代替`。如果没有这样的列，InnoDB会`隐式的定义一个主键`作为聚簇索引。 - 为了充分利用聚簇索引的聚簇特性，InnoDB中表的`主键应选择有序的id`，不建议使用无序的id，比如UUID、MD5、HASH、字符串作为主键，无法保证数据的顺序增长。 ##### 非聚簇索引 **（二级索引、辅助索引）** `聚簇索引`，只能在搜索条件是`主键值`时才发挥作用，因为B+树中的数据都是按照主键进行排序的，如果我们想以别的列作为搜索条件，那么需要创建`非聚簇索引`。 **例如，**`以c2列作为搜