前言

本专栏为150道MySQL大厂高频面试题讲解分析，这些面试题都是通过MySQL8.0官方文档和阿里巴巴官方手册还有一些大厂面试官提供的资料。
MySQL应用广泛，在多个开发语言中都处于重要地位，所以最好都要掌握MySQL的精华面试题，这也是面试官最喜欢问的，现在面试官在面试的时候更关心的是某个技术点的深度，所以专栏的内容也会从底层开始讲解，本专栏会一直不断的进行更新，欢迎大家一起交流学习。

InnoDB索引与MyISAM索引实现的区别

本文知识点梳理：

一、InnoDB索引实现

聚集索引：

• InnoDB采用聚集索引（Clustered Index）的方式存储数据。在这种方式中，表的数据行与主键一起存储在索引的叶子节点中。也就是说，InnoDB的数据文件本身就是索引文件，按照主键的顺序组织成B+树结构。
• 由于数据行与主键一起存储，因此按主键查询的效率非常高。
• InnoDB要求每张表必须有主键。如果没有显式指定主键，MySQL会自动选择一个可以唯一标识数据记录的列作为主键。如果这样的列也不存在，MySQL会自动为InnoDB表生成一个隐含字段（长整型、长度为6个字节）作为主键。

辅助索引：

• InnoDB的辅助索引（Secondary Index）在叶子节点中存储的是主键的值，而不是数据行的地址。这意味着，通过辅助索引查询数据时，需要先通过辅助索引找到主键值，再通过主键值回表查询到完整的数据记录。因此，按辅助索引检索实际上进行了二次查询，效率低于按主键检索。
• 为了减小辅助索引所占空间，建议将InnoDB表中的主键索引尽量定义得小一些。

内存管理：

• InnoDB不仅将索引载入内存，还将数据也载入内存缓冲，以提高查询效率。

索引状态信息：

• InnoDB通过在启动时随机读取索引来估计索引的状态信息，因此使用SHOW INDEX命令查看的索引信息可能不是绝对精准的。

二、MyISAM索引实现

非聚集索引：

• MyISAM的索引文件和数据文件是分离的。索引文件的叶子节点中保存的是数据记录所在页的地址（物理存储位置），通过这些地址来读取页，进而读取被索引的行数据。
• MyISAM的主索引和辅助索引在结构上没有任何区别，只是主索引要求key是唯一的，而辅助索引的key可以重复。

内存管理：

• MyISAM只将索引载入内存，数据缓存依赖于操作系统。

索引状态信息：

• MyISAM保存索引的状态信息在磁盘里，每次执行ANALYZE TABLE命令会更新这个信息。因此，使用SHOW INDEX命令查看的MyISAM表的索引信息是精准的。

字符串索引：

• 对于字符串索引，MyISAM默认采用增量保存的方式，以减小索引的尺寸。例如，第一个索引值是“perform”，第二个索引的值是“performance”，在索引文件中第二个索引被保存为“7,ance”。

索引碎片：

• MyISAM可能会出现两种索引碎片：一行数据被保存在不同的数据段，以及连续的表空间或行在磁盘上被分散地保存。对于InnoDB，只会出现后一种碎片，因为InnoDB不会把短行保存到不同的数据段。

三、总结

数据结构：

InnoDB的数据文件本身就是索引文件，采用聚集索引的方式存储数据；而MyISAM的索引文件和数据文件是分离的。

索引存储内容：

InnoDB的主索引存储完整的数据记录，辅助索引存储主键值；而MyISAM的主索引和辅助索引都存储数据记录的地址。

内存管理：

InnoDB将索引和数据都载入内存缓冲；MyISAM只将索引载入内存。

索引状态信息：

InnoDB的索引状态信息是估计值；MyISAM的索引状态信息是精准的。

主键要求：

InnoDB要求必须有主键；MyISAM可以没有主键。

查询效率：

InnoDB按主键查询的效率非常高；MyISAM按主键或辅助索引查询的效率相对较低，但可以通过优化索引和查询语句来提高效率。

一个表中如果没有创建索引，那么会创建B+树吗

在数据库中，索引是一种关键的数据结构，用于加速数据查找和访问。其中，B+树是最常见且最重要的索引数据结构之一。

一、B+树的基本概念

B+树是一种自平衡的树状数据结构，通常用于数据库中的索引。它具有以下特点：

• 每个节点可以包含多个子节点，这意味着B+树可以高效地处理大量的数据。
• 具有自平衡性质，确保树的高度保持较小，从而保持高效的查询性能。
• 节点是有序的，这使得范围查询非常高效。
• 叶子节点存储实际的数据记录，而内部节点仅存储索引键（或键值对的键部分）。

二、数据库表中的索引与B+树

在数据库系统中，索引通常用于加速数据的检索操作。对于MySQL的InnoDB存储引擎来说，它使用B+树来实现索引结构。

主键索引（聚集索引）：

• 当在表中指定了主键时，InnoDB会自动为主键创建一个聚集索引，该索引的叶子节点包含整个数据行。
• 聚集索引决定了数据在磁盘上的物理存储顺序。

辅助索引（非聚集索引）：

• 除了主键索引外，InnoDB还支持创建其他类型的索引，如唯一索引、普通索引等。
• 这些索引通常也是B+树索引，但它们的叶子节点包含的是指向主键索引的指针，而不是完整的数据行。

三、没有创建索引时的情况

InnoDB存储引擎：

• 如果没有显式地在表中创建索引，InnoDB会自动为主键（如果存在）创建聚集索引。
• 如果表中没有主键，InnoDB会选择一个唯一索引（如果存在）作为聚集索引。
• 如果表中既没有主键也没有唯一索引，InnoDB会生成一个隐藏的6字节的row ID作为主键，并为其创建聚集索引。
• 因此，在InnoDB中，即使没有显式创建索引，也会有一个B+树索引存在（即聚集索引）。

MyISAM存储引擎：

• MyISAM的索引结构与InnoDB有所不同。在MyISAM中，主键索引和辅助索引都是非聚集的。
• 如果没有在MyISAM表中创建索引，则不会有B+树索引存在。但是，MyISAM表仍然可以通过数据文件的顺序扫描来查找数据。

四、案例演示

以下是一个在MySQL中使用InnoDB存储引擎创建表并观察索引创建的示例：

-- 创建一个没有显式索引的表  
CREATE TABLE test_table (  
    id INT AUTO_INCREMENT,  
    name VARCHAR(50),  
    age INT,  
    PRIMARY KEY (id) -- 这里指定了主键，InnoDB会自动为其创建聚集索引  
);  
  
-- 插入一些数据  
INSERT INTO test_table (name, age) VALUES ('Alice', 30), ('Bob', 25), ('Charlie', 35);  
  
-- 观察表的索引情况（可以使用SHOW INDEX命令或查询information_schema.STATISTICS表）  
SHOW INDEX FROM test_table;

查询结果：

• Table: 表名，这里是 test_table。
• Non_unique: 如果索引不能包含重复词，则为0。如果可以，则为1。对于主键索引，这个值总是0，因为主键要求唯一性。
• Key_name: 索引的名称。这里是 PRIMARY，表示这是主键索引。
• Seq_in_index: 索引中的列序号。对于单列索引，这个值总是1。
• Column_name: 索引中的列名。这里是 id。
• Collation: 列以什么顺序存储在索引中。‘A’ 表示升序，‘D’ 表示降序，NULL 表示不适用。对于InnoDB的B+树索引，通常不直接显示排序方式在此列（特别是当它是主键时），而是隐含在索引结构中；这里的’A’可能是特定输出格式的默认表示，或者识别时的误差，因为对于主键索引，其排序是隐含的且由主键的唯一性约束保证。
• Cardinality: 索引中唯一值的估计数量。这个值是一个估计值，可能不准确。对于小表，它可能等于表中的行数。
• Sub_part: 如果索引只是列的一部分，则该列显示索引的字符数。如果索引是整列，则为NULL。这里是NULL，表示索引是整个 id 列。
• Packed: 指示关键字是否被压缩。如果没有被压缩，则为NULL。对于InnoDB的B+树索引，这通常不是相关属性。
• Null: 如果列可以包含NULL，则该列含有YES。如果不可以，则该列含有’'（空字符串）。由于 id 是主键，它不能包含NULL值。
• Index_type: 使用的索引方法（BTREE, FULLTEXT, HASH, RTREE）。这里是 BTREE，表示使用的是B+树索引。
• Comment: 关于索引的额外信息。如果没有，则为空。
• Index_comment: 索引的注释。如果没有，则为空。

根据上述输出的结果可以看出尽管没有在test_table中显式创建辅助索引，但由于指定了主键id，InnoDB会自动为其创建一个聚集索引。