📚 课程目录第1章索引的本质

日志数据检索优化让查询速度翻倍 - 第1节

索引的本质：为什么没有索引会慢？

我记得刚入行那会儿，带我的老工程师丢给我一个慢查询，说：「你看看这张表，才 50 万行数据，一个简单的 WHERE 查了 3 秒多。」我打开执行计划，好家伙，全表扫描。那时候我才真正意识到——没有索引的数据库，就像一本没有目录的百科全书。

你想想看，一本 1000 页的书，你要找「B+树」这个词。没有目录，你得从第 1 页翻到第 1000 页。有目录呢？直接翻到索引页，找到「B+树 → 第 342 页」，三步到位。数据库也是一样的道理。

核心结论：索引的本质，就是给数据建立一套「快速定位」的结构。没有它，数据库只能逐行扫描——时间复杂度 O(n)。有了它，查找可以降到 O(log n) 甚至 O(1)。

为什么全表扫描这么慢？

全表扫描，说白了就是 MySQL 把整张表的每一行都读一遍。我做过一个测试：一张 1000 万行的用户表，没有索引，查一个 WHERE user_id = 9999999，耗时 4.2 秒。加上主键索引后，同样的查询，0.3 毫秒。差距超过一万倍。

为什么会这样？因为磁盘 I/O 是计算机最慢的操作之一。一次磁盘随机读取大约需要 10 毫秒，而内存读取只要 0.1 微秒。全表扫描意味着大量的随机 I/O，而索引能把这些随机 I/O 变成几次有序的查找。

我曾经踩过的坑：刚做 DBA 时，给一张日志表加了 8 个索引，觉得「索引越多查得越快」。结果写入性能直接崩了——每次插入要维护 8 个 B+ 树，磁盘 I/O 直接打满。索引不是越多越好，这是个血泪教训。

B+树数据结构深度解析

MySQL 的 InnoDB 引擎为什么选 B+树？不是二叉树，不是哈希表，偏偏是 B+树。我个人习惯从三个维度去理解它：树的高度、节点存储、范围查询。

B+树长什么样？

先看一张图，这是我画的一个简化版 B+树结构。它分两层：上面是「索引层」，下面是「数据层」。

B+树的三个关键特性

矮胖结构：每个节点可以存几百个键值（扇出大）。3 层 B+树就能存几千万条数据。查找任何一条数据，最多 3 次磁盘 I/O。
叶子节点存数据：所有数据都在叶子节点，非叶子节点只存「路标」。这样非叶子节点能塞进内存，查找更快。
叶子节点双向链表：这是 B+树比 B 树强的地方。范围查询时，找到起点后顺着链表往后读就行，不用回溯树。

我个人的理解：B+树就像一本带「章节索引」和「页码连续」的书。索引页告诉你「第 3 章从第 100 页开始」，然后你直接翻到第 100 页，顺着页码往后读。B 树呢？每翻一页都得回索引页重新查——这就是为什么 InnoDB 选 B+树而不是 B 树。

聚簇索引与非聚簇索引的区别

这是面试高频题，也是实际开发中最容易搞混的概念。我直接说结论：InnoDB 的表一定是聚簇索引，MyISAM 的表一定是非聚簇索引。但事情没这么简单。

聚簇索引（Clustered Index）

聚簇索引，说白了就是「数据和索引放在一起」。InnoDB 里，主键就是聚簇索引。叶子节点直接存整行数据。

特点：

表数据物理顺序 = 索引顺序（近似）
通过主键查找，一次 I/O 就能拿到整行数据
每个表只能有一个聚簇索引（因为数据只能有一种物理顺序）

非聚簇索引（Secondary Index / MyISAM 索引）

非聚簇索引，索引和数据分开存。叶子节点存的是「指向数据行的指针」（MyISAM）或「主键值」（InnoDB 的二级索引）。

特点：

索引顺序和数据物理顺序无关
通过非聚簇索引查找，可能需要「回表」——先查索引拿到主键，再查聚簇索引拿数据
一个表可以有多个非聚簇索引

对比维度	聚簇索引（InnoDB 主键）	非聚簇索引（MyISAM / InnoDB 二级索引）
数据存储位置	索引的叶子节点直接存数据	索引的叶子节点存指针或主键
回表	不需要	需要（二级索引场景）
每表数量	只能 1 个	可以多个
范围查询	极快（数据连续）	较慢（需要多次回表）
插入性能	顺序插入快，随机插入可能页分裂	插入不影响数据物理顺序

一个让我印象深刻的案例

我曾经优化过一个订单查询系统。表结构是这样的：

CREATE TABLE orders (
    order_id BIGINT PRIMARY KEY,        -- 聚簇索引
    user_id INT,
    order_time DATETIME,
    amount DECIMAL(10,2),
    INDEX idx_user_id (user_id),        -- 二级索引（非聚簇）
    INDEX idx_order_time (order_time)   -- 二级索引（非聚簇）
) ENGINE=InnoDB;

业务方经常查「某个用户最近 10 笔订单」。SQL 是这样的：

SELECT * FROM orders 
WHERE user_id = 12345 
ORDER BY order_time DESC 
LIMIT 10;

执行计划显示：先用 idx_user_id 找到所有该用户的订单（拿到主键），然后回表查数据，再排序。200 万行数据里，这个查询跑了 800 毫秒。

优化方案：建一个联合索引 (user_id, order_time)。这样索引本身已经排好序了，而且覆盖了查询条件，不需要回表。优化后，同样的查询降到 5 毫秒。

关键点：InnoDB 的二级索引叶子节点存的是「主键值」，不是行指针。所以每次通过二级索引查数据，都要先拿到主键，再去聚簇索引里找。这就是「回表」。如果查询的字段都在索引里（覆盖索引），就不需要回表。

如何选择？

我个人习惯这样判断：

主键查询多、范围查询多：用 InnoDB 聚簇索引，天然适合
写入量大、主键是 UUID 等随机值：小心页分裂。我建议用自增 ID 做主键
查询条件多、需要多个索引：用 InnoDB，二级索引虽然要回表，但可以通过覆盖索引优化
读多写少、全文索引需求：MyISAM 在某些场景仍有优势，但大部分场景 InnoDB 更优

我曾经犯过的错：给一张表用 UUID 做主键，结果插入性能惨不忍睹。因为 UUID 是随机的，每次插入都可能插在 B+树的中间位置，导致频繁的页分裂。后来改成自增 ID，写入速度提升了 5 倍。记住：聚簇索引的主键，最好是有序递增的。

嗯，这一章的内容就这些。索引的本质、B+树的结构、聚簇与非聚簇的区别，这三块是检索优化的地基。地基打不牢，后面讲查询计划、索引优化、SQL 改写，你听着会飘。先把这些概念刻在脑子里，后面我们才好动手实战。