MySQL系列文章

MySQL索引是数据库性能优化的核心知识之一。正确理解索引的原理和使用场景，对于编写高效的SQL语句和设计合理的表结构至关重要。本文将系统介绍MySQL索引的相关知识，包括常见的数据结构、不同存储引擎的索引实现方式，以及聚簇索引和非聚簇索引的区别。

一、索引的常见数据结构及其优缺点

索引的本质是一种数据结构，用于快速定位数据，就像书的目录一样，可以帮助我们快速找到需要的内容，而不必逐页翻阅。不同的数据结构适用于不同的场景，常见的有以下几种：

1. 哈希表

哈希表是一种基于键值对（Key-Value）的数据结构，通过哈希函数将键映射到某个位置，从而快速访问值。

• 优点：等值查询效率高，时间复杂度为O(1)
• 缺点：不支持范围查询，因为哈希表内部数据是无序的；哈希冲突会影响性能
• 适用场景：适用于只有等值查询的场景，如Redis等NoSQL数据库

2. 有序数组

有序数组在等值查询和范围查询场景下都非常高效。

• 优点：等值查询（二分查找）和范围查询性能都很优秀
• 缺点：更新数据时需要移动大量元素，成本高
• 适用场景：适用于静态存储引擎，如存储历史数据

3. 二叉树与平衡二叉树

二叉树是经典的数据结构，每个节点的左子节点小于父节点，右子节点大于父节点。

• 优点：查询和更新时间复杂度均为O(log N)
• 缺点：数据量较大时树高较高，查询时需要多次磁盘IO，效率低

4. B树与B+树

B树和B+树是为了减少磁盘IO而设计的多路平衡搜索树。

• B树：每个节点既存储数据也存储索引，节点大小通常与磁盘块对齐
• B+树：
  • 非叶子节点只存储索引+指针，叶子节点存储数据
  • 叶子节点之间通过指针连接，支持范围查询
  • 更适合磁盘读写特性，减少磁盘IO次数

将数据从磁盘读入内存涉及随机IO的访问，是数据库里面成本最高的操作之一。

innodb主键索引图

主键索引（聚簇索引）：对应B+树结构示意图如上。 B+树是多层的，B+树每一层中的页都会形成一个双向链表。只有叶子节点存储数据，非叶子节点存储索引值（主键）+指针。

二级索引（非聚簇索引）：结构和主键索引类似，只是叶子节点存储的不是完整行数据，而是索引键 + 主键ID

为什么InnoDB使用B+树？

• 磁盘IO次数少：B+树的树高较低，通常只需1-3次磁盘IO即可查询到数据
• 范围查询高效：叶子节点通过指针连接，便于范围查询
• 数据有序：B+树叶子节点存储的数据是有序的，适合排序和分组操作
• 节点利用率高：非叶子节点只存储键值，可以容纳更多索引项

为什么说磁盘IO次数通常在1-3次？

原因：通常来说B+数索引中第一层数据块常驻内存中，第二层其实也极有可能存在内存中，而一个表数10亿数据通常来说也只有4层树高。（下列数据量化）

B+树存储容量量化分析

假设我们使用bigint作为主键类型（8字节），指针占用6字节，InnoDB页大小为16KB，那么：

每个非叶子节点可存储的键值对数量 = 页大小 / (键大小 + 指针大小) = 16384 / (8 + 6) ≈ 1170

假设每条记录大小约为1KB，那么：

• 每个叶子节点可存储的记录数 = 页大小 / 记录大小 ≈ 16
• 2层B+树：可存储约1170 × 16 ≈ 18,720条记录
• 3层B+树：可存储约1170 × 1170 × 16 ≈ 21,902,400条记录（约2200万）
• 4层B+树：可存储约1170³ × 16 ≈ 25,625,808,000条记录（约256亿）

这意味着即使存储数十亿条记录，B+树也只需要3-4次磁盘IO即可找到所需数据，效率极高。

下表总结了常见数据结构的优缺点：

二、MySQL中常见的索引类型

MySQL支持多种索引类型，不同存储引擎对索引的支持也有所不同。

1. BTree索引

BTree索引是MySQL中最常用的索引类型，基于B+树实现。适用于全值匹配、范围查询和排序操作。

2. 哈希索引

哈希索引基于哈希表实现，适用于等值查询，但不支持范围查询和排序。Memory存储引擎默认支持哈希索引。

3. 全文索引

全文索引用于文本内容的模糊搜索和分词查询，适用于CHAR、VARCHAR和TEXT类型的列。InnoDB和MyISAM支持全文索引。

4. 空间索引（RTree）

空间索引用于地理数据查询，支持几何数据类型，但使用较少，通常由专用搜索引擎（如ElasticSearch）代替。

不同存储引擎的索引支持

三、聚簇索引与非聚簇索引

1. 聚簇索引

聚簇索引是一种将数据存储与索引结合的方式，索引的叶子节点直接存储行数据。

• 特点：
  • 一个表只能有一个聚簇索引
  • 数据存储顺序与索引顺序一致
• 优点：查询速度快，避免了回表操作
• 缺点：插入和更新操作可能引起页分裂，影响性能

InnoDB中，主键索引就是聚簇索引。如果没有定义主键，InnoDB会选择一个唯一的非空索引代替，如果没有这样的索引，会隐式定义一个主键作为聚簇索引。

2. 非聚簇索引

非聚簇索引的叶子节点存储的是主键值（InnoDB）或数据行指针（MyISAM），而不是行数据本身。查询时需要根据主键值再次查询聚簇索引，这个过程称为回表。

• 特点：
  • 一个表可以有多个非聚簇索引
  • 叶子节点不包含行数据，仅存储定位信息
• 优点：索引占用空间小，维护成本低
• 缺点：查询需要回表，性能略低于聚簇索引

重要设计原则：主键长度越小，普通索引的叶子节点就越小，普通索引占用的空间也就越小。这就是为什么推荐使用自增整型作为主键，而不是较长的字符串。

3. 回表的概念

回表是指通过非聚簇索引查询时，首先在非聚簇索引树中查找主键值，然后再到聚簇索引树中根据主键值获取行数据的过程。例如：

SELECT * FROM T WHERE k = 5;

如果k字段上有非聚簇索引，查询过程如下：

1. 在k索引树中查找k=5的记录，获取主键值（例如500）
2. 在主键索引树中查找ID=500的记录，获取行数据

4. 索引维护

索引在提供查询加速的同时，也需要维护成本。当对表中的数据进行增加、删除、修改操作时，数据库需要同步更新相关的索引结构，以保持数据一致性。

B+树为了保持平衡，在数据修改时可能需要分裂或合并节点：

• 插入操作：可能导致叶子节点分裂，产生额外的磁盘IO
• 删除操作：可能导致节点合并，产生空间碎片
• 更新操作：相当于先删除后插入，可能同时触发分裂和合并操作

因此，索引不是越多越好，需要权衡查询性能和维护成本。对于写多读少的场景，应谨慎添加索引。（通常单表不超过5个为最佳）

5. MyISAM与InnoDB的索引实现对比

尽管MyISAM和InnoDB都使用B+树作为索引结构，但它们的实现方式有本质区别：

• MyISAM：

  • 使用非聚簇索引，所有索引都是二级索引
  • 索引叶子节点存储的是数据文件的指针
  • 数据文件和索引文件是分离的（.MYD和.MYI文件）
  • 表数据存储顺序与索引无关

• InnoDB：

  • 主键索引是聚簇索引，叶子节点直接存储行数据
  • 非主键索引是非聚簇索引，叶子节点存储主键值
  • 表数据文件本身就是按主键组织的一个索引结构
  • 支持"索引组织表"特性，数据按主键顺序存储

关键区别：InnoDB是"索引即数据"，主键索引的叶子节点就是数据行；而MyISAM是"索引+数据"，索引和数据分离存储，通过指针关联。

总结

本文介绍了MySQL索引的常见数据结构、不同类型的索引及其适用场景，以及聚簇索引和非聚簇索引的区别。理解这些基础知识有助于在实际工作中更好地设计表结构和优化查询性能。

索引的出现是为了提高数据查询效率，就像书的目录一样，可以帮助我们快速定位到需要的内容。InnoDB选择B+树作为索引模型，主要是为了减少磁盘IO次数，提高查询效率。通过量化分析可以看出，即使是海量数据，B+树也能在极少的磁盘IO次数内完成查询。

聚簇索引通过将数据存储与索引结合，避免了回表操作，但插入和更新操作可能带来页分裂的问题。非聚簇索引虽然需要回表，但占用空间小，适用于多索引场景。需要注意的是，主键长度越小，普通索引的叶子节点就越小，普通索引占用的空间也就越小，这也是推荐使用自增整型作为主键的重要原因。

MyISAM和InnoDB虽然都使用B+树结构，但实现方式不同：InnoDB采用"索引即数据"的聚簇索引设计，而MyISAM使用"索引+数据"的分离式设计。这一根本区别导致了两者在性能特性上的差异。

在实际应用中，建议根据查询需求和数据特性选择合适的索引类型，并尽量避免回表操作，以提高查询性能。同时需要注意索引维护带来的开销，在读写性能之间找到平衡点。