MySQL索引（二）

页目录

MySQL中的页目录（Page Directory）是InnoDB存储引擎中B+树数据结构的一部分，主要用于提高数据页（data page）内的查找效率。每个数据页存储记录时，页目录作为索引，帮助快速定位记录的位置。页目录的存在，使得在大数据页中查找特定记录的速度大大提升。

页的结构

一个典型的InnoDB数据页（默认大小16KB）由多个部分组成，其中主要包括：

1. 文件头（File Header）：存储页的类型、页号等信息。
2. 页面头（Page Header）：存储页面的状态、记录数等信息。
3. 最小记录和最大记录（Infimum and Supremum Records）：用于标记页的最小和最大边界，便于记录管理。
4. 用户记录（User Records）：实际存储的记录数据。
5. 页目录（Page Directory）：存储每个记录的偏移量索引。
6. 页面尾（Page Trailer）：用于校验页面的完整性。

页目录的作用

页目录在页面中存储每个记录的偏移量，这些偏移量按顺序排列，可以认为页目录是一个快速索引结构。具体作用如下：

1. 快速查找：通过页目录，可以快速定位到需要查找的记录，而不需要从头到尾遍历整个页面。
2. 提高效率：对于B+树索引来说，每个叶子节点存储的数据量较大，通过页目录能显著提高查找效率。
3. 维护顺序：页目录的存在，使得数据页内部的记录顺序更容易维护和管理。

页目录的工作机制

当需要在数据页中查找某条记录时，MySQL会执行以下步骤：

1. 二分查找页目录：首先在页目录中进行二分查找，快速定位到可能包含目标记录的范围。
2. 顺序查找：在通过页目录确定的范围内，顺序查找具体的记录。

示例说明

假设一个数据页中存储了如下记录：

• 记录A：偏移量100
• 记录B：偏移量200
• 记录C：偏移量300

对应的页目录存储了记录A、B、C的偏移量100、200、300。如果要查找记录B，MySQL首先在页目录中二分查找，定位到偏移量200，然后直接访问记录B，而不需要遍历整个页中的所有记录。

B+树

B+树是一种自平衡的树数据结构，广泛应用于数据库和文件系统中。它具有以下特点：

特点

1. 所有叶子节点在同一层：B+树的所有数据都存储在叶子节点中，所有叶子节点都位于同一层。这使得B+树的高度非常稳定，查找任何数据项所需的路径长度相同。

2. 内部节点只存储键，不存储数据：B+树的内部节点只存储用于索引的键值和指向子节点的指针，而不存储实际数据。这些键值用于引导搜索路径。

3. 叶子节点存储实际数据：所有实际数据都存储在叶子节点中，叶子节点之间通过链表相连，这使得范围查询和顺序访问非常高效。

4. 高扇出率：由于每个节点可以包含多个子节点指针和键值对，B+树的扇出率很高，这导致树的高度较低。低高度意味着查找、插入和删除操作需要的磁盘I/O次数较少，从而提高了性能。

5. 平衡性：B+树是一种平衡树，插入和删除操作会保持树的平衡，确保所有叶子节点在同一层，保持树的高度不变。

为什么B+树是矮胖型树

在数据库系统中，B+树是一种广泛使用的数据结构，特别适用于数据库和文件系统中的索引管理。MySQL的InnoDB存储引擎使用B+树来实现其索引。我们常说B+树是“矮胖型”的树，原因如下：

1. 高扇出率（Fan-out）

B+树的每个节点（页）都可以包含多个子节点指针和键值对。由于每个页的大小固定（通常为16KB），可以存储大量的键值对和指针。这意味着B+树的分支因子（即每个节点的子节点数量）非常大，通常可以达到几百甚至上千。

2. 浅层次（Shallow Depth）

由于B+树的高扇出率，使得树的层次非常浅。例如，一个节点包含500个子节点，如果要管理1,000,000个数据项，树的高度计算如下：

• 根节点：1个
• 第一层：500个节点
• 第二层：500 * 500 = 250,000个节点
• 第三层：500 * 500 * 500 = 125,000,000个节点

在这种情况下，树的高度仅为3层，即使数据量达到数百万甚至数亿。

3. 高效的磁盘I/O

B+树的设计使得每次查找操作需要的磁盘I/O次数非常少。由于B+树是“矮胖型”的，每个节点包含大量的键值对和子节点指针，所以树的层次较少，查找数据时穿越的层次也较少，导致每次查询操作所需的磁盘访问次数较少。

4. 优化的顺序访问

B+树的所有叶子节点通过链表连接，这使得顺序访问非常高效。对于范围查询和顺序扫描，B+树提供了快速的访问路径，因为只需一次磁盘I/O就能加载多个数据项。

B树vsB+树

B树

B+树

  比较  

特性	B树	B+树
节点数据存储	内部节点和叶子节点均存储数据	仅叶子节点存储数据
内部节点	存储键值和数据	只存储键值，不存储数据
叶子节点	存储数据	存储数据，叶子节点通过链表连接
查找路径	可能在内部节点找到数据，路径较短	数据仅在叶子节点，查找路径较长
范围查询	不如B+树高效	非常高效，叶子节点通过链表顺序访问
空间利用率	相对较低，内部节点存储数据	相对较高，内部节点只存储键值和指针
扇出率	相对较低	相对较高

选择使用

• B树：适用于查找操作频繁，且需要快速找到数据的场景。
• B+树：适用于需要高效范围查询和顺序访问的场景，如数据库索引。

聚簇索引vs非聚簇索引

聚簇索引和非聚簇索引是数据库索引的两种主要类型，每种都有其独特的结构和用途。以下是对这两种索引的详细解释和比较：

聚簇索引（Clustered Index）

• 定义

  • 聚簇索引是将表中的数据行按照索引键的顺序进行物理排序的一种索引。一个表只能有一个聚簇索引，因为数据行只能按一种顺序排列。

• 数据存储

  • 数据行直接存储在叶子节点中。聚簇索引的叶子节点包含了实际的数据记录，索引和数据是一起存储的。

• 特点

  • 主键索引：在很多数据库系统中，主键通常默认会创建一个聚簇索引。
  • 数据存取：由于数据按索引键排序存储，范围查询和排序操作效率较高。
  • 插入和删除：在数据插入或删除时，可能需要移动数据行以保持排序，因此可能会导致页分裂和页合并，性能可能受影响。
  • 磁盘I/O：由于数据和索引在一起，读取索引的同时就可以读取到数据，减少了磁盘I/O操作。

• 使用场景

  • 适用于需要频繁进行范围查询的场景。
  • 适用于有大量排序操作的场景，如按日期排序的日志表。

非聚簇索引（Non-Clustered Index）

• 定义

  • 非聚簇索引是将索引和数据分开存储的一种索引。一个表可以有多个非聚簇索引。

• 数据存储

  • 索引的叶子节点存储索引键和指向数据行的指针（通常是聚簇索引键或行号）。实际数据存储在表中，索引只是一个指向数据的指针。

• 特点

  • 辅助索引：非聚簇索引用于加速对非主键列的查询。
  • 数据存取：由于索引和数据分开存储，查找数据时通常需要先查索引，再通过指针访问实际数据行，可能增加一次磁盘I/O操作。
  • 插入和删除：索引更新操作较快，因为不需要移动数据行，只需更新指针。
  • 磁盘I/O：可能需要额外的I/O操作，因为需要先读取索引再读取数据。

• 使用场景

  • 适用于频繁查询某些非主键列的场景。
  • 适用于需要多个索引的复杂查询。

特性	聚簇索引	非聚簇索引
数据存储	数据按索引键排序存储在叶子节点	索引存储在叶子节点，指向数据行
数据访问	直接通过索引访问数据，I/O效率高	通过索引访问数据，可能多一次I/O操作
插入/删除	插入删除需要保持排序，可能较慢	插入删除只需更新指针，通常较快
索引数量	一个表只能有一个	一个表可以有多个
排序效率	高效	需要额外排序操作
范围查询	高效	需要多次I/O操作，效率较低

举例说明

假设有一个包含以下字段的表：id（主键）、name、age、created_at。

• 聚簇索引：如果在id字段上创建聚簇索引，表中的数据行会按id的顺序物理存储。当查询范围内的id时，数据库可以快速找到数据行，因为数据是按顺序存储的。

• 非聚簇索引：如果在name字段上创建非聚簇索引，索引的叶子节点存储name和指向实际数据行的指针。当按name查询时，数据库先查找索引，找到对应的指针，再访问实际数据行。

InnoDB vs MyISAM

在表的设计和存储方面，InnoDB和MyISAM存储引擎有显著的区别。下面将详细讨论这两个存储引擎在表设计和存储方面的差异。

InnoDB表设计与存储

1. 聚簇索引（Clustered Index）

   • 定义：InnoDB使用聚簇索引作为其默认的存储结构。聚簇索引将数据行按主键的顺序物理存储。
   • 数据存储：每张表的数据文件（.ibd文件）中，数据按主键排序存储。聚簇索引的叶子节点包含实际数据行，非叶子节点存储索引键和子节点指针。
   • 主键：如果没有定义主键，InnoDB会选择一个唯一非空索引作为聚簇索引；如果没有合适的索引，InnoDB会自动生成一个隐式的6字节主键。

2. 辅助索引（Secondary Indexes）

   • 定义：除了聚簇索引，InnoDB还支持辅助索引，用于加速其他列的查询。
   • 数据存储：辅助索引的叶子节点存储主键值，而不是实际数据行。这意味着查找辅助索引时，需要先找到主键值，再通过主键查找聚簇索引获取实际数据。

3. 表空间（Tablespaces）

   • 共享表空间：InnoDB的默认配置下，所有表共享一个系统表空间（ibdata1文件），这包括数据和索引。
   • 独立表空间：可以配置为每个表使用独立的表空间（.ibd文件），每个表的数据和索引都存储在单独的文件中。这简化了备份和表的管理。

4. 自动增长列（AUTO_INCREMENT）

   • 定义：InnoDB支持AUTO_INCREMENT列，通常用于主键。默认情况下，InnoDB在插入新行时自动为该列生成唯一的增量值。
   • 存储：AUTO_INCREMENT值保存在内存中，并且会持久化到磁盘，确保在数据库重启后能继续生成唯一值。

MyISAM表设计与存储

1. 非聚簇索引（Non-Clustered Index）

   • 定义：MyISAM使用非聚簇索引结构。数据和索引是分开存储的。
   • 数据存储：数据存储在一个独立的数据文件（.MYD文件）中，索引存储在索引文件（.MYI文件）中。索引的叶子节点存储指向数据行的指针，而不是实际数据。

2. 索引结构

   • 主键索引：MyISAM中的主键索引也是非聚簇索引。主键索引的叶子节点存储主键值和指向数据行的指针。
   • 辅助索引：与主键索引类似，辅助索引也存储索引键和指向数据行的指针。

3. 表文件

   • 数据文件（.MYD）：每个MyISAM表的数据存储在一个.MYD文件中。
   • 索引文件（.MYI）：每个MyISAM表的索引存储在一个.MYI文件中。索引文件包含所有的索引，包括主键索引和辅助索引。

4. 自动增长列（AUTO_INCREMENT）

   • 定义：MyISAM也支持AUTO_INCREMENT列，用于生成唯一的增量值。
   • 存储：AUTO_INCREMENT值存储在索引文件中。与InnoDB不同，MyISAM不会在内存中保留AUTO_INCREMENT值，这意味着表重启后可能需要重新计算下一个增量值。

索引的用法

索引是数据库中用于提高查询性能的重要机制。通过在表的列上创建索引，可以加速数据检索、排序和过滤操作。以下是关于索引的详细用法、类型和最佳实践：

索引的基本用法

        创建索引

• 创建索引时，可以使用SQL语句CREATE INDEX。以下是一些常见的索引创建方式：

  -- 创建单列索引
  CREATE INDEX idx_column_name ON table_name(column_name);
  
  -- 创建多列（组合）索引
  CREATE INDEX idx_columns_name ON table_name(column1, column2);
  
  -- 创建唯一索引
  CREATE UNIQUE INDEX idx_unique_column_name ON table_name(column_name);
  

  删除索引

• 如果索引不再需要，可以使用DROP INDEX语句删除索引：

  DROP INDEX idx_column_name ON table_name;
  

  查看索引

• 使用SHOW INDEX命令查看表上的索引：

  SHOW INDEX FROM table_name;
  

  索引的类型

单列索引（Single-column Index）

CREATE INDEX idx_name ON employees(name);

多列索引（Multi-column Index）

CREATE INDEX idx_name_dob ON employees(name, date_of_birth);

唯一索引（Unique Index）

CREATE UNIQUE INDEX idx_unique_email ON users(email);

全文索引（Full-text Index）

CREATE FULLTEXT INDEX idx_fulltext_content ON articles(content);

空间索引（Spatial Index）

CREATE SPATIAL INDEX idx_location ON locations(coordinates);

索引适用的场景

索引在数据库中起着至关重要的作用，能显著提高查询性能。然而，创建索引也会带来额外的存储和维护开销，因此应在合适的情况下使用。以下是适合使用索引的几种典型情况：

1. 频繁查询的列

当某些列在查询中频繁出现时，为这些列创建索引可以显著提高查询速度。这些查询包括SELECT、WHERE、JOIN、ORDER BY和GROUP BY等子句中的列。

2. 主键和唯一键

主键和唯一键通常用来标识唯一的记录，数据库会自动为这些列创建唯一索引，以确保数据的唯一性和快速查找。

3. 连接操作（JOIN）

在涉及表之间的连接操作时，为连接条件列创建索引，可以显著提高连接查询的性能。