第一章：索引基础概念

1.1 什么是索引？
索引是数据库中一种用于提高数据检索效率的数据结构。它可以被理解为“书的目录”。通过目录，我们可以快速定位到想看的内容，而无需翻阅整本书。同样，数据库索引允许数据库引擎快速找到表中的特定数据行，而不用扫描整个表。这能显著减少磁盘I/O操作，从而加速查询。索引尤其适用于处理大规模数据表（如百万、千万级数据）。

1.2 索引的主要类型
MySQL中提供了多种索引类型，以适应不同的应用场景：

• B+Tree索引：MySQL的默认索引类型。它以一种平衡的多路搜索树形式存储数据，适用于范围查询和等值查询。绝大多数场景下，我们使用的都是B+Tree索引。
• Hash索引：基于哈希表实现，适用于精确的等值查询。其查询速度极快，但不支持范围查询。
• R-Tree索引：空间索引，主要用于存储和查询地理空间数据。
• Full-text索引：全文索引，用于在文本内容中进行关键词搜索。

第二章：深入理解B+Tree索引

2.1 为什么是B+Tree？
在了解B+Tree前，先简单看下其他树结构的不足：

• 二叉树：每个节点最多有两个子节点。在数据量大时，树的高度会变得很高，增加磁盘I/O次数。特别是在插入有序数据时，会退化成“链表”，查询效率极低。
• 红黑树：作为一种平衡二叉树，它能保证树的大致平衡，但在数据量巨大时，树的高度依然是个问题。

B+Tree正是为了解决这些问题而设计的：

• 平衡与多路：B+Tree是一种“平衡”的“多路”搜索树。它的“平衡”意味着从根节点到每个叶子节点的路径长度相同，保证了查询效率的稳定。“多路”则意味着每个节点可以拥有多个子节点，从而大幅降低树的高度，减少磁盘I/O。
• 数据存储位置：在B+Tree中，所有的实际数据行都存储在叶子节点上。非叶子节点（枝节点、根节点）仅存储用于导航的键值和指针（即指向子节点的范围信息）。
• 叶子节点链表：所有的叶子节点之间通过双向指针连接，形成了一个有序的链表。这使得范围查询变得非常高效：只需找到范围起始点，然后沿着链表顺序读取即可，无需每次都从根节点重新开始。

2.2 B+Tree的查询过程示例
假设我们有一棵B+Tree，叶子节点存储了1-100的数据，我们想查找数据54：

1. 根节点：首先访问根节点，根据根节点中存储的键值范围（如 (1-50), (51-100)）和指针，判断出54在(51-100)这个区间，从而定位到对应的枝节点。
2. 枝节点：访问该枝节点，它内部可能又细分了更小的范围（如 (51-75), (76-100)），再次判断54在(51-75)区间，从而定位到具体的叶子节点页。
3. 叶子节点：访问目标叶子节点页，在页内进行二分查找或顺序扫描，最终找到值为54的数据行。

整个过程经历了3次I/O操作（读取3个数据页），无论查找哪个数据，消耗的I/O次数都是稳定的，这就是“平衡”的体现。

第三章：索引的构建方式

3.1 聚簇索引 (Clustered Index)

• 是什么：聚簇索引决定了表中数据的物理存储顺序。可以理解为，表的数据行就是按照聚簇索引的顺序存储的。一个表只能有一个聚簇索引。
• 如何创建：
  1. 如果表定义了主键（PRIMARY KEY），那么主键就是聚簇索引。
  2. 如果没有定义主键，MySQL会选择第一个非空的唯一键（UNIQUE NOT NULL）作为聚簇索引。
  3. 如果以上都没有，InnoDB存储引擎会自动生成一个6字节的隐藏列作为聚簇索引。
• 特点：叶子节点直接包含了行的所有数据。因此，通过聚簇索引查询数据非常快，因为索引即数据，无需额外操作。

3.2 辅助索引 (Secondary Index)

• 是什么：除了聚簇索引以外的所有索引都是辅助索引，也称为非聚簇索引或二级索引。它是为了优化特定的查询条件而创建的。一个表可以有多个辅助索引。
• 如何创建：通过CREATE INDEX或ALTER TABLE ADD INDEX语句创建。
• 特点：辅助索引的叶子节点不存储完整的数据行，而是存储索引列的值 + 对应的主键值。
• 回表查询：当通过辅助索引查找数据时，流程分为两步：
  1. 索引查找：在辅助索引的B+Tree中找到满足条件的叶子节点，获取到对应的主键值。
  2. 回表：拿着这个主键值，再到聚簇索引的B+Tree中进行第二次查找，最终获取完整的数据行。这个过程称为“回表”。

第四章：执行计划与索引应用

4.1 什么是执行计划
执行计划是MySQL优化器为SQL语句生成的一种“操作指南”，它描述了MySQL将如何执行该语句，包括使用什么索引、以什么顺序连接表、扫描多少行数据等。通过分析执行计划，我们可以判断SQL语句的性能瓶颈在哪里。

4.2 获取执行计划
使用EXPLAIN或DESC关键字加在SQL语句前即可。

EXPLAIN SELECT * FROM employees WHERE last_name = 'Smith';

4.3 核心字段解读

• type：最重要的字段之一，表示访问类型，反映了索引的使用效率。性能由好到差依次为：
  • const/system：主键或唯一键的等值查询，性能最高。
  • eq_ref：多表连接时，被驱动表使用主键或唯一键进行等值连接。
  • ref：辅助索引的等值查询。
  • range：索引的范围查询（如 >, <, BETWEEN, IN, LIKE 'abc%'）。
  • index：全索引扫描，即遍历了整个索引树，比全表扫描好一点，但依然很慢。
  • ALL：全表扫描，没有使用索引，性能最差，应极力避免。
• key：实际使用的索引名称。
• key_len：重要字段，表示使用的索引字节总长度。可用于判断联合索引用到了几个列。值越大，通常意味着查询条件越精确，回表次数越少。
• rows：预计需要扫描的行数，这个值越小越好。
• Extra：重要字段，包含额外的补充信息。
  • Using index：表示使用了覆盖索引，查询的列全部在索引中，无需回表。
  • Using index condition：表示使用了索引下推优化。
  • Using where：表示在存储引擎层返回结果后，又在MySQL服务器层进行了条件过滤。
  • Using filesort：需要警惕，表示无法利用索引完成排序，需要额外的排序操作，会消耗大量CPU和内存。
  • Using temporary：需要警惕，表示使用了临时表来存储中间结果，常见于GROUP BY和DISTINCT操作。

4.4 联合索引的最左前缀原则
联合索引 (a, b, c) 的生效遵循最左前缀原则。这意味着查询条件必须从索引的最左侧列开始，并且不能跳过中间的列。

• 生效情况：
  • where a = 1 (用到a列)
  • where a = 1 and b = 2 (用到a, b列)
  • where a = 1 and b = 2 and c = 3 (用到a, b, c列)
  • where a = 1 and c = 3 (只用到了a列，因为跳过了b列)
• 范围查询的影响：当查询条件中出现范围查询（>, <, LIKE等）时，该列之后的索引列将无法继续使用。
  • where a = 1 and b > 10 and c = 3 (只用到了a和b列，c列无法用于索引过滤)
• 顺序无关性：MySQL优化器会自动调整where条件中的顺序，使其符合最左前缀原则。因此，where b = 2 and a = 1的效果等同于where a = 1 and b = 2。

第五章：索引失效场景与规范

5.1 索引失效的常见原因（开发规范）

1. 违反最左前缀原则：查询条件未包含联合索引的最左列。
2. 使用不等操作符：在辅助索引上使用 != 或 <>，可能导致索引失效，转为全表扫描。
3. 使用NOT IN：可能导致辅助索引失效。
4. 对索引列进行了计算或函数操作：如 WHERE id - 1 = 9 或 WHERE YEAR(date) = 2023。
5. 模糊匹配以通配符开头：LIKE '%abc'会导致辅助索引失效，但LIKE 'abc%'有效。
6. 数据类型隐式转换：当查询条件的数据类型与索引列的数据类型不匹配时，MySQL会进行隐式类型转换，导致索引失效。例如，在字符串类型的列上查询时，忘记加引号：WHERE varchar_col = 123。
7. 查询结果集过大：当优化器认为全表扫描比使用索引更快时（通常预计扫描行数超过全表的25%左右），会放弃使用索引。

5.2 索引建立原则（运维规范）

1. 必有主键：为每个表建立与业务无关的自增主键，作为聚簇索引。
2. 为高频查询列建索引：经常出现在WHERE、ORDER BY、GROUP BY、JOIN ON后的列，应考虑建立索引。
3. 选择区分度高的列：索引列的重复值越少越好（即基数大）。如果单列区分度不高，可以考虑创建联合索引。
4. 使用联合索引减少回表：尽量设计覆盖索引，将查询所需的列都包含在索引中，避免回表I/O。
5. 长列使用前缀索引：对于很长的字符串列（如TEXT、VARCHAR(255)），可以只对前N个字符建立索引，以节省空间并降低索引层级。
6. 索引数量适度：索引并非越多越好。每个索引都会占用磁盘空间，并增加写操作（增、删、改）的开销。定期清理无用索引。

第六章：数据库的自主优化特性

• AHI (Adaptive Hash Index) - 自适应哈希索引：InnoDB引擎会自动监控对索引页的查询，如果发现某些索引页被频繁访问，会在内存中自动为其建立一个哈希索引，使得下次查询可以直接通过哈希定位到内存中的该数据页，加速读取。
• Change Buffer - 变更缓冲区：一种特殊的数据结构，用于缓存对辅助索引的修改操作（INSERT、UPDATE、DELETE）。当这些操作发生时，聚簇索引是实时更新的，但辅助索引的更新会被暂存在Change Buffer中。这样避免了在操作时立即对辅助索引树进行昂贵的随机I/O更新。当辅助索引页被读取到内存中时，Change Buffer中的修改才会被合并进去。
• ICP (Index Condition Pushdown) - 索引下推：一种针对联合索引的优化。在没有ICP时，存储引擎只能根据索引的最左前缀条件来过滤数据，然后将数据行返回给MySQL服务器，再由服务器根据其他条件过滤。启用ICP后，MySQL服务器会将部分可以使用索引的过滤条件下推到存储引擎层。存储引擎在读取索引时，就直接使用这些条件进行过滤，只返回最终满足所有索引条件的数据行。这大大减少了回表次数和磁盘I/O。
• MRR (Multi-Range Read) - 多范围读取：一种优化，旨在减少磁盘的随机I/O。当通过辅助索引进行范围扫描并需要回表时，会得到大量分散的、无序的主键值。MRR会将这些主键值放入一个缓存中，排序后再统一到聚簇索引中查询数据。这样就将大量的随机I/O转换成了顺序I/O，显著提升I/O效率。