前言
本专栏为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 | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| test_table | 0 | PRIMARY | 1 | id | A | 3 | BTREE |
- 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会自动为其创建一个聚集索引。
