Ⅰ. 索引的概念

在 MySQL 中，索引是一种用于提高查询效率的数据结构 。它可以帮助数据库系统快速定位和访问表中的数据。索引可以基于一个或多个列创建，并且可以应用于表中的任何列。

不用加内存、不用改程序、不用调sql、只要执行正确的 create index ，查询速度就可能提高成百上千倍。但是天下没有免费的午餐，查询速度的提高是以插入、更新、删除的速度为代价的 ，这些写操作，增加了大量的 IO。

MySQL 支持多种类型的索引，包括下面几种：

1. 主键索引 （ Primary Key Index） ：主键索引是一种唯一性索引，用于标识表中的每一行。每个表只能有一个主键索引，它可以跨多个列定义。

   • 推荐为每个表定义一个主键。如果没有逻辑上唯一且非空的列或列集可以使用主键，则 MySQL 会自动添加一个自增列。

2. 唯一索引 （ Unique Index） ：唯一索引确保索引列中的值是唯一的，但允许包含空值。一个表可以有多个唯一索引。

3. 普通索引 （ Normal Index） ：普通索引是最基本的索引类型，它没有唯一性或主键的限制。一个表可以有多个普通索引。

4. 全文索引 （ Full-Text Index） ：全文索引用于在大文本数据中进行全文搜索。它可以提供更高级的搜索功能，如关键字搜索和排序。

   • 这类大文本数据现在通常用专门的文档类型数据库来存储，如 mongodb，效率更高！

5. 组合索引 （ Composite Index） ：组合索引是基于多个列创建的索引。它可以提高多列查询的性能，但只有在查询中使用了索引的第一个列时才能发挥作用。

   • 最左前缀原则 ：组合索引只有在查询条件中【使用了】索引的第一个列时，才会被使用。这是组合索引的一个重要特性。

6. 聚簇索引 ：与主键索引是同义词

   1. 一个表只能有一个聚簇索引 ，它决定了数据的物理存储顺序。

   2. 索引的叶节点直接存储了完整的数据行 。

   3. 是否发生回表：

      • 当查询条件是【聚簇索引列】 时，不存在回表操作，因为可以直接从索引的叶节点获取完整的数据行。

      • 当查询条件是【非聚簇索引列】 时，会发生回表操作，因为需要通过非聚簇索引找到指向数据行的指针（通常是主键值），然后通过主键值去聚簇索引中查找完整的数据行。

   4. 自增列的选取：

      • 如果没有为表定义 PRIMARY KEY，则 InnoDB 使用第一个 UNIQUE 和 NOT NULL 的列作为聚集索 引。

      • 如果表中没有 PRIMARY KEY 或合适的 UNIQUE 索引，InnoDB 会为新插入的行生成一个行号并用 6 字节的 ROW_ID 字段记录，ROW_ID 单调递增，并使用 ROW_ID 做为索引。

7. 非聚簇索引 ：聚簇索引以外的索引，统称为非聚簇索引或二级索引

   1. 一个表可以有多个非聚簇索引 ，它们不改变数据的物理存储顺序，叶节点存储的是指向数据行的指针 。

   2. 是否发生回表：

      • 通常存在回表操作，因为索引的叶节点只存储了指向数据行的指针。

      • 在覆盖索引的情况下，可以避免回表操作。

8. 索引覆盖 ：当一个 select 语句使用了普通索引且查询列表中的列刚好是创建普通索引时的所有或部分列，这时就可以直接返回数据，而不用回表查询，这样的现象称为索引覆盖。

   • 要实现索引覆盖，查询中涉及的列必须在索引中都有覆盖

Ⅱ. MySQL与磁盘交互基本单位 -- page

一个扇区大小是 512B，而系统读取磁盘是以 块 为单位的，一般为 8个扇区，所以一个块一般为 4KB，如下所示：

而 MySQL进行 IO的基本单位是 16KB，也就是 "页" 。

我们可以通过 "innodb_page_size" 来查查看当前 mysql 的页大小：

mysql> show global status like 'innodb_page_size';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| Innodb_page_size | 16384 |  --16KB大小
+------------------+-------+
1 row in set (0.01 sec)

所以 MySQL 和操作系统、磁盘的关系大概就是如下图所示：

为什么要以页为交换单位呢❓❓❓

在 .ibd 文件中最重要的结构体就是 Page(页)，页是内存与磁盘交互的最小单元，默认大小为 16KB，每次内存与磁盘的交互至少读取一页，所以在磁盘中每个页内部的地址都是连续的，之所以这样做，是因为在使用数据的过程中，根据局部性原理 ，将来要使用的数据大概率与当前访问的数据在空间上是临近的，所以一次从磁盘中读取一页的数据放入内存中，当下次查询的数据还在这个页中时就可以从内存中直接读取，从而减少磁盘 I/O 提高性能。

Ⅲ. 页的结构

在 MySQL 中有多种不同类型的页，最常用的就是用来存储数据和索引的"索引页"，也叫做"数据页"（innodb中索引和数据是存储在一起的），但不论哪种类型的页都会包含页头和页尾，页的主体信息使用数据行进行填充，数据页的基本结构如下图所示：

注意上面图中的页头和页尾，应该更具体为页文件头(File Header)、页文件尾(File Trailer)，因为还有一个概念叫做页头(Page Header)，下面会介绍！

页文件头与页文件尾

1. 页文件头(File Header) ：文件头位于页面起始位置，占 38B。它记录了页面的全局信息，用于物理存储管理和一致性校验。常见字段包括：

2. 页文件尾(File Trailer) ：存储了关于数据页的校验和和其他元数据，其大小固定为 8B。页尾包含以下内容：

   • 校验和：用于验证数据页的完整性和一致性。

   • LSN（Log Sequence Number）：记录页的最后修改日志序列号，用于恢复和事务日志的管理。

可以看出页与页之间存在字段 FIL_PAGE_PREV 和 FIL_PAGE_NEXT 进行连接，即用双向链表连接页 。

页头

页头紧跟在页文件头之后，占用 56B，仅存在于索引类型页面（即存放数据或索引记录的“数据页”）中，页头记录了页面内部的数据状态和管理信息，用于控制行记录的存储与访问。典型字段包括：

页主体

数据行存储了实际的数据记录 。每行的大小取决于表的定义和存储的数据，每行的最小开销为 5B（记录头信息，包含记录的类型、删除标志、记录长度等）。

每当创建一个新页，都会自动分配两个行，一个是页内最小行 Infimun，另一个是页内最大行 Supremun，这两个行并不存储任何真实信息，而是做为数据行链表的头和尾，第一个数据行有一个记录下一行的地址偏移量的区域 next_record 将页内所有数据行组成了一个单向链表，此时新页的结构如下所示：

当向一个新页插入数据时，将 Infimun 连接第一个数据行，最后一行真实数据行连接 Supremun，这样数据行就构建成了一个单向链表 ，更多的行数据插入后，会按照主键从小到大的顺序进行链接，如下图所示：

举个例子，其中每行数据可以这样子计算：

CREATE TABLE students (
    student_id INT,
    name VARCHAR(50),
    age INT,
    score INT
) ENGINE=InnoDB;

• student_id：4字节

• name：假设存储的字符串长度为20字节（实际大小取决于存储的字符串长度）

• age：4字节

• score：4字节

• 记录头信息：5字节

因此，每行的总大小为：4 + 20 + 4 + 4 + 5 = 37字节

☠ 注意事项：

• 行溢出 ：如果一行数据太大（超过页的大小），InnoDB 会将部分数据存储在其他页中，并在当前页中存储一个指针。

• 页的填充因子 ：InnoDB 默认会将页填充到约 15/16 满，以保留一些空间用于插入和更新操作。

页目录

当按主键或索引查找某条数据时，最直接简单的方法就是从头行 infimun 开始，沿着链表顺序逐个比对查找，但一个页有 16KB，通常会存在数百行数据，每次都要遍历数百行，无法满足高效查询，为了提高查询效率，InnoDB 采用二分查找来解决查询效率问题！

具体实现方式是，在每一个页中加入一个叫做页目录 PageDirectory 的结构，将页内包括头行、尾行在内的所有行进行分组，约定头行单独为一组，其他每个组最多 8条数据 ，同时把每个组最后一行在页中的地址，按主键从小到大的顺序记录在页目录中 ，页目录中的每一个位置称为一个槽，每个槽都对应了一个分组 ，一旦分组中的数据行超过分组的上限 8个时，就会分裂出一个新的分组 。

后续在查询某行时，就可以通过二分查找，先找到对应的槽，然后在槽内最多 8 个数据行中进行遍历即可，从而大幅提高了查询效率，这时一个页的核心结构就完成了！

例如要查找主键为 6 的行，先比对槽中记录的主键值，定位到最后一个槽 2，再从最后一个槽中的第一条记录遍历，第二条记录就是我们要查询的目标行。

多页存储情况

以 innodb 为例，实际上存储的时候所有叶子节点都是数据页，而非叶子节点是管理数据页的索引页，如下图所示：（图中页目录指针应该指向每组的末尾数据才对，而图中画成了指向每组的开头数组，这个要注意一下！）

从上图我们也可以看出，其实 mysql 中 InnoDB 存储引擎的索引结构，就是我们数据结构所学的 B+ ** 树结构！

使用 B+ 树结构，意味着有什么优势呢❓❓❓

1. B+ 树中数据都是存放在叶子节点中的，也就是说非叶子节点中没有存放数据，只有目录项，这意味着非叶子节点可以存储更多的目录项 ，而只要目录页一多的话，那么这棵树，就会越趋向于 ”矮胖型 “ 的树，即深度很小的树，换言之就是 IO** 次数更少** 。

2. B+ 树的叶子节点是以链表的形式串联起来的 ，范围查询 非常方便。（且对于没有索引的字段进行全文扫描的时候也是很方便的）

3. 在相同树高的情况下，查找任一元素的时间复杂度都一样，性能均衡 。

• 使用平衡二叉树搜索树的缺陷：
  • 平衡二叉树搜索树的高度是 logn，这个查找次数在内存中是很快的。但是当数据都在磁盘中时，访问磁盘速度很慢，在数据量很大时，logn 次的磁盘访问，是一个难以接受的结果，因为深度太大了，这样子会导致 IO 次数过多而效率低！
• 使用哈希表的缺陷：
  • 哈希表的查找时间复杂度很优秀，为 O(1)，但是一些极端场景下某个位置冲突很多，导致访问次数剧增，也是难以接受的。并且哈希表对于范围查找的支持不是很友好，这点就很打击了！

Ⅳ. 聚簇索引 && 非聚簇索引

一、MyISAM -- 非聚簇索引

MyISAM 引擎是 MySQL5.5.8 版本之前默认的存储引擎，不支持事务，但支持全文检索。其使用 B+树作为索引结构，叶节点的 ** data 域存放的是数据记录的地址** 。

MyISAM 存储引擎的索引方式也叫做非聚集索引 ，这是为了方便索引树和主键树可以映射同样的数据 。

下图为 MyISAM 表的主索引，其中 Col1 是主键：

其中，MyISAM 存储引擎最大的特点就是，将索引 ** Page 和数据 ** Page** 分离，也就是叶子节点没有数据，只有对应数据的地址** 。

并且每次拿着数据的地址去主表里面查找真实数据的这个过程，叫做回表查询 ，这会导致 I/O 次数变多，使得效率降低！

当然，MySQL 除了默认会建立主键索引外，一般我们也有可能按照其它列信息建立索引，这种索引可以叫做普通索引。而对于 MyISAM，建立普通索引和主键索引本质没有什么差别，无非就是主键不能重复并且具有唯一性，而非主键可重复且可有多个。

简单地说，普通索引也是会创建其独立的一颗索引树的 ！

下图就是基于 MyISAM 的 Col2 建立的索引，和主键索引没有差别：

这个普通索引同样也是一棵 B+树，其中 data 域保存数据记录的地址。

但是现在的版本就不再使用 MyISAM 为默认索引引擎了，因为它不支持事务 ，而要知道的是我们现在生活中存在着大量的事务。比如说微信支付，这个时候要考虑收入和支出的问题，还有就是如果支付失败了，那么钱得返回去，这些就是事务的例子，这也是为什么 MyIASM 会被取消默认索引引擎的理由。而我们下面要介绍的 InnoDB 是支持事务的！

💥需要注意的是，表只有一张，而索引树是有多颗的，它们指向同一张表 ！

二、InnoDB -- 聚簇索引

InnoDB 存储引擎支持事务 ，其设计目标主要面向在线事务处理的应用，从 MySQL 5.5.8 版本开始，InnoDB存储引擎是默认的存储引擎。

但 InnoDB 使用 B+树作为索引结构时，具体实现方式却与 MyISAM 存储引擎截然不同，体现在节点的存储内容上。

它们的区别如下所示：

第一个区别是InnoDB** 的索引文件本身就是数据文件** ，而 MyISAM 的索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。而 InnoDB 索引的数据文件本身就是按 B+树组织的一个索引结构，树的叶节点 ** data 域保存了完整的数据记录** 。这个索引的 key 是数据表的主键，因此 InnoDB 的数据文件本身就是主索引。

第二个区别是InnoDB** 的辅助索引 ** data** 域存储相应记录主键的地址而不是值，所有辅助索引都引用主键作为 ** data** 域** 。

以下图为例，就是以 id 为主键，其中叶子节点数据本身就是一个表的一部分数据 ，这样子的话方便我们对数据的操作。同时若是有其它索引，比如下图中的 name 为辅助索引，那么我们不可能在 name 的索引树里面也存一份完整的数据呀，那也太浪费空间了 ！

此时因为 name就是一个非聚簇索引 ，所以形成了一个回表操作 ！在 name 的数据中存放聚簇索引 id 的地址，然后回表到主表中重新查询指定数据，最后获得想要的数据！

所以通过辅助索引 name，可以找到目标记录，然后需要进行两遍索引：首先检索辅助索引获得主键，然后用主键到主索引树中检索获得最终的完整数据记录！

可以看到主键树中叶子节点包含了完整的数据记录，这种索引叫做聚集索引。因为 ** InnoDB 的数据文件本身要按主键聚集，所以 ** InnoDB** 要求表必须有主键** （ MyISAM 可以没有主键）。

如果 InnoDB 没有显式指定主键的话，则 MySQL 系统会自动选择一个可以唯一标识数据记录的列作为主键**，** 如果不存在这种列，则 MySQL** 自动为 ** InnoDB** 表生成一个隐含字段作为主键** ，这个字段长度为 6 个字节，类型为长整型。

三、两种存储引擎的区别

Ⅴ. 三层树高的B+树可以存放多少条记录❓❓❓

一、每页可以存放多少条记录？

InnoDB 的页大小通常是 16KB，每条记录大小会因字段类型不同而有很大差异。我们用一个中等记录大小来估算：

• 假设每条记录平均大小为 100B。

• 页面中除去头部、尾部和目录等开销，真正可用于数据的约 13KB 左右。

那么，每页大概可存记录数：

每页可用空间 ≈ 13KB = 13312 字节
每页记录数 ≈ 13312 / 100 ≈ 133 条记录

✅ 我们保守取值：每页 100条记录

二、B+ 树结构与高度的关系

B+ 树的层次结构大致是：

Level 2 (Root 节点)        → 1 页
   ↓
Level 1 (中间节点)         → 每个节点指向若干页
   ↓
Level 0 (叶子页)           → 每个叶子页存储数据记录

每个非叶子节点存储的是 子页指针 + 键值对 ，每个指针项大小约为 8B~16B。

估算一下：

• 每个非叶节点页可指向的子页数 ：约 500 个（取中间值，约 8~16B/项 × 16KB）

• 所以一层能容纳下 约 500个子页

三、三层 B+ 树可存放的记录数

现在，我们来估算：

• 叶子节点（底层） ：每个叶子页能存 100 条记录

• 每个中间节点可指 500个叶子页

• 根节点再指 500个中间节点

所以总记录数估算为：

总记录数 ≈ 500（根） × 500（中间） × 100（叶子记录数）
        = 25,000,000 条记录

结论： 一个三层高度的 B+ 树，在 InnoDB 中大约可以容纳 2500 万条记录。（假设每条记录约 100B）

Ⅵ. 索引的操作

一、索引的创建原则

索引最大的好处是提高查询速度，但是索引也是有缺点的，比如：

1. 需要占用物理空间，数量越大，占用空间越大；

2. 创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增大；

3. 会降低表的增删改的效率，因为每次增删改索引，B+树为了维护索引有序性，都需要进行动态维护。

所以，索引并不是万能钥匙，它也是根据场景来使用的。

什么时候不适合创建索引❓❓❓

• 表数据太少 的时候，不适合创建索引。

• where条件、 group by和 order by里用不到的字段，不适合创建索引 。索引的价值是快速定位，如果起不到定位的字段通常是不需要创建索引的，因为索引是会占用物理空间的。

• 字段中存在大量重复数据 ，不适合创建索引。比如性别字段，只有男女，如果数据库表中，男女的记录分布均匀，那么无论搜索哪个值都可能得到一半的数据。在这些情况下，还不如不要索引，因为 MySQL 还有一个查询优化器，查询优化器发现某个值出现在表的数据行中的百分比很高的时候，它一般会忽略索引，进行全表扫描。

• 经常需要更新的字段 ，不适合创建索引。比如不要对电商项目的用户余额建立索引，因为索引字段频繁修改，由于要维护 B+树的有序性，那么就需要频繁的重建索引，这个过程是会影响数据库性能的。

• 唯一性太差 的字段不适合单独创建索引，即使该字段频繁地作为查询条件也不适合。

二、创建索引

① 主键

第一种方式：在创建表的时候，直接在字段名后指定 primary key：

create table user1(
    id int primary key,
    name varchar(30)
);

第二种方式：在创建表的最后，指定某列或某几列为主键索引：

create table user2(
    id int, 
    name varchar(30), 
    primary key(id)
);

第三种方式：创建表以后再添加主键：

alter table 表名 add primary key(列名);

主键索引的特点：

• 一个表中只能有一个主键索引 ，当然可以使用复合主键

• 主键索引的效率高

• 主键值不允许重复，且不能为 null

• 主键索引的列最好是整型比如 int

• 主键索引的列最好是自增类型 ，这样子能最大化的利用好索引的优势

② 唯一键

第一种方式：在表定义时，在某列后直接指定 unique 唯一属性：

create table user4(
    id int primary key, 
    name varchar(30) unique
);

第二种方式：创建表时，在表的后面指定某列或某几列为unique：

create table user5(
    id int primary key,
    name varchar(30),
    unique(name)
);

第三种方式：创建表以后再添加主键：

alter table 表名 add unique(列名);

唯一索引的特点：

• 一个表中，可以有多个唯一索引

• 查询效率高

• 如果在某一列建立唯一索引，必须保证这列不能有重复数据

• 如果一个唯一索引上指定 not null，等价于主键索引

③ 创建普通索引

第一种方式：在表的定义最后，指定某列为索引：

create table user8(
    id int primary key,
    name varchar(20),
    email varchar(30),
    index(name)                 --在表的定义最后，指定某列为索引
);

第二种方式：创建完表以后指定某列为普通索引：

alter table 表名 add index(列名);

第三种方式：创建表以后再添加普通索引，并且可以对索引进行命名：

create index 索引名 on 表名(列名);

④ 组合索引

上面我们的操作都是将单个字段设置为索引，其实我们也是可以通过将多个字段组合成一个索引的，该索引就被称为组合索引。

比如，将商品表中的 product_no 和 name 字段组合成联合索引 (product_no, name)，创建联合索引的方式如下：

create index index_product_no_name on product(product_no, name);

可以看到，联合索引的非叶子节点用两个字段的值作为 B+树的 key 值。当在联合索引查询数据时，先按 product_no 字段比较，在 product_no 相同的情况下再按 name 字段比较。

也就是说，联合索引查询的 B+树是先按 product_no 进行排序，然后再 product_no 相同的情况再按 name 字段排序。

因此，使用联合索引时，存在最左匹配原则 ，也就是按照最左优先的方式进行索引的匹配。在使用联合索引进行查询的时候，如果不遵循「最左匹配原则」，联合索引会失效 ，这样就无法利用到索引快速查询的特性了。

最左匹配原则 是指在数据库中使用复合索引进行查询时，索引会按照索引字段的顺序进行匹配。当查询条件中包含多个字段时，索引会从左到右逐个匹配字段，直到找到第一个不匹配的字段为止。这意味着，如果查询条件中只使用了索引的前缀字段，那么索引可以被充分利用；而如果查询条件中使用了索引的后续字段，那么索引的效率会降低。

比如，如果创建了一个 (a, b, c) 联合索引，如果查询条件是以下这几种，就可以匹配上联合索引：

where a=1；
where a=1 and b=2 and c=3；
where a=1 and b=2；

需要注意的是，因为有查询优化器，所以 a字段在 where子句的顺序并不重要 。

但是如果查询条件是以下这几种，因为不符合最左匹配原则，所以就无法匹配上联合索引，联合索引就会失效：

where b=2；
where c=3；
where b=2 and c=3；

上面这些查询条件之所以会失效，是因为(a, b, c) 联合索引，是先按 a 排序，在 a 相同的情况再按 b 排序，在 b 相同的情况再按 c 排序。所以，b** 和 ** c** 是全局无序，局部相对有序的** ，这样在没有遵循最左匹配原则的情况下，是无法利用到复合索引的！

三、查询索引

-- 三种方式：
show keys from 表名;
show index from 表名;
desc 表名;

四、删除索引

① 主键

alter table 表名 drop primary key;

② 其它索引

alter table 表名 drop index 索引名;