mysql索引机制和建索引原则

1. 索引基础

1.1 分类

• 按底层数据结构划分
  • B-Tree 索引 (B-Tree/B+Tree)：
  • 哈希索引 (Hash Index)： 基于哈希表实现，对索引列计算哈希值，然后存储哈希值和行指针。
  • 全文索引 (Full-Text Index)：它使用“倒排索引”等更复杂的数据结构。
  • 空间索引 (R-Tree Index)：用于处理地理空间数据（如坐标、区域），能高效地进行范围和邻近查询。
• 按与数据物理存储的关系划分
  • 聚集索引 (Clustered Index)：
  • 非聚集索引 (Non-Clustered Index / Secondary Index)：
• 按应用和逻辑功能划分
  • 主键索引 (Primary Key Index)： 索引列的值必须唯一且不允许为 NULL。
  • 唯一索引 (Unique Index)：索引列的值必须唯一，但允许有 NULL 值（可以有多个 NULL）。
  • 普通索引 (Normal/Regular Index)：
  • 联合索引 (Composite/Compound Index)：

1.2 B树（B-数）和B+树

特性	B树 (B-Tree)	B+树 (B+Tree)
数据存储位置	所有节点都存储数据。一个节点包含 [键, 值, 指针]，这里的“值”就是完整的行数据或其一部分。	只有叶子节点存储数据。非叶子节点（内部节点）只存储 [键, 指针]，作为纯粹的索引目录，不包含任何真实数据。
叶子节点连接	叶子节点之间是相互独立的，没有指针相连。	所有叶子节点通过一个双向链表连接在一起，形成一个有序的序列。
查询性能	点查询性能优秀，因为数据可能在任何节点找到。范围查询性能较差，可能需要进行多次“中序遍历”式的树深度访问。	点查询性能稳定，因为每次都必须走到叶子节点。范围查询性能极高，只需在叶子节点链表上做顺序扫描即可。
I/O效率	较差。因为非叶子节点存储了数据，导致每个节点能容纳的指针（扇出）变少，树的高度可能更高，需要更多的I/O。	极高。非叶子节点不存数据，体积小，扇出(fan-out)极大，使得树非常“矮胖”，大大减少了磁盘I/O次数。
冗余数据	没有冗余的键。	在非叶子节点中的键，都会在叶子节点中再次出现（作为叶子节点数据的索引），存在一定的键冗余。

1.3 B+树和二叉树

二叉树为内存而生，B+树为磁盘而生。

1. 节点“胖瘦”不同（扇出 Fan-out）

   • 二叉树： 每个节点最多只有两个子节点（左、右），是“高瘦”型结构。
   • B+树： 每个节点可以有成百上千个子节点，是“矮胖”型结构。这个“子节点数量”就是扇出。

2. 为谁优化

   • 二叉树： 优化的是CPU计算次数。它假设数据都在内存中，访问任何一个节点的成本都一样且很低。
   • B+树： 优化的是磁盘I/O次数。它深刻理解磁盘I/O是数据库最昂贵的瓶颈。从磁盘读取1B和读取一个块（如16KB）的时间几乎没差别。B+树的目标就是用一次I/O读取尽可能多的信息（一个包含大量指针的“胖”节点），从而用最少的I/O次数完成查找。

3. 高度决定效率

   • 二叉树： 存储百万级数据，树的高度会非常可观（log_2 N）。例如，log_2(1,000,000) = 20。在磁盘上，这意味着最坏可能需要20次I/O。

   • B+树： 由于极大的扇出（假设为1000），存储百万级数据，树的高度通常只有2-4层（log{1000} N）。例如，log{1000}(1,000,000,000) = 3。这意味着查找十亿条数据，最多只需要3次磁盘I/O！这是一个天壤之别的性能优势。

1.4 为什么选用 B+ 树作为索引

1. 极大地减少了I/O次数（核心原因）：
   B+树的“矮胖”结构，意味着从根节点到叶子节点的路途极短，通常只需2-4次磁盘I/O就能定位到任何数据。这是它击败二叉树等内存数据结构的根本原因。
2. 完美支持范围查询和排序：
   数据库的WHERE id > 100，BETWEEN...AND...，ORDER BY，GROUP BY等操作极为常见。B+树叶子节点的双向链表结构，使得执行这些操作变成了简单的顺序扫描，效率极高，避免了B树那种低效的“跳跃式”回溯。
3. 更高效的缓存利用：
   B+树的非叶子节点不存储数据，只存键和指针，所以体积很小。这意味着数据库的缓存（如InnoDB的Buffer Pool）可以容纳下更多的索引“目录页”。当一个索引的非叶子节点被全部加载到内存中后，后续的查询可能只需要一次磁盘I/O（去读叶子节点的数据页）甚至零次I/O（如果数据页也已在缓存中）。

1.5 B+树存储和读取流程

页（Page）是 InnoDB 管理存储空间的最小单元，也是数据库与磁盘进行 I/O 操作的最小单元。你可以把它想象成一个固定大小的“数据盒子”。数据库向操作系统请求读取数据时，不会说“请给我第 500 行数据”，而是说“请把包含第 500 行数据的那个‘盒子’从磁盘给我搬到内存里”。

• 页的大小是多少？
  在 InnoDB 中，页的默认大小是 16KB。这个值可以在初始化 MySQL 实例时设置，但通常我们都使用默认值。
• 为什么要用“页”而不是“行”？
  这是为了解决计算机体系结构中最核心的矛盾：磁盘 I/O 的速度远慢于内存和 CPU。
  • 磁盘的特性： 磁盘寻道和旋转的耗时是 ms 级别，而一旦找到位置，连续读取一小块数据（比如 16KB）的时间则快得多。读取 1B 和读取 16KB 的主要时间都花在了“找位置”上。
  • 页的意义： 因此，一次 I/O 就读取一个 16KB 的“页”，而不是一行几十个字节的数据，可以最大化每次 I/O 的价值。同时，根据局部性原理，当访问一行数据时，其附近的数据（在同一个页内）也很有可能在不久后被访问。一次性读入一个页，可以大大减少后续的 I/O 次数。

B+ 树的存储流程 (INSERT)

你要 INSERT 一条新数据时，B+ 树会经历一个“自上而下的查找，可能自下而上的分裂”的过程。

1. 从根节点开始查找：
   • B+ 树会加载根节点页到内存中。
   • 通过比较新插入记录的主键值和根节点中的键，确定下一步应该去哪个分支节点。例如，要插入 id=300，根节点有 [key=500, ptr_A]，它会选择走向包含小于500范围的 ptr_A 指针所指向的子节点。
2. 递归下降到叶子节点：
   • 这个过程会一直重复，从分支节点不断向下，直到找到最终应该容纳这条新记录的叶子节点（数据页）。
3. 在叶子节点中插入数据：
   • 情况一：叶子节点未满。 这是最理想的情况。直接将新数据行插入到这个 16KB 的页中，并保持页内数据按主键有序。操作结束。
   • 情况二：叶子节点已满。 这是 B+ 树最精妙的操作——页分裂 (Page Split)。
     a. 创建新页： InnoDB 会创建一个新的空叶子节点页。
     b. 移动数据： 将原已满的叶子节点中的大约一半数据，按主key顺序移动到这个新页中。
     c. 插入新数据： 根据新记录的主键值，决定将其插入到原页还是新页中。
     d. 更新父节点： 最关键的一步！需要在当前叶子节点的父节点（一个分支节点）中，增加一个指向这个新创建页的[键, 指针]条目。
4. 可能发生的连锁反应（级联分裂）：
   • 如果在第 3 步中更新父节点时，发现父节点也满了，那么父节点也需要进行一次分裂。这个分裂过程和叶子节点分裂类似，但分裂的是索引条目，并且会继续向上更新它的父节点。
   • 在极端情况下，这种分裂可能会一直级联到根节点。如果根节点也需要分裂，那么 B+ 树的高度就会增加 1。

B+ 树的读取流程 (SELECT)

读取流程相对简单，就是一个纯粹的“自上而下”的查找过程。

• 点查询 (如 WHERE id = 300)
  1. 从根节点开始，通过二分查找等方式，快速定位到包含 300 的范围，并找到指向下一层节点的指针。
  2. 逐层向下，直到到达包含 300 这条记录的叶子节点。这个过程通常只需要 2-4 次 I/O。
  3. 在叶子节点（数据页）内部，因为行是按主键排序的，也可以通过二分查找快速定位到具体的行数据。
• 范围查询 (如 WHERE id BETWEEN 300 AND 400)
  1. 定位起点： 首先，像点查询一样，快速定位到 id=300 所在的叶子节点。
  2. 顺序扫描： 从该叶子节点开始，读取所有满足条件的数据。
  3. 利用链表： 当这个页的数据都读取完后，不需要再返回上层节点，而是直接通过该页的“下一页指针”（next-page pointer），直接访问到逻辑上相邻的下一个叶子节点。
  4. 重复步骤 3，直到读取到的数据主键值超过 400。

这个叶子节点间的双向链表，是 B+ 树支持高效范围查询的“秘密武器”。

1.6 B+ 树的高度一般是多少

一个千万甚至上亿条记录的 InnoDB 表，其 B+ 树的高度通常只有 3 到 4 层。

这个结果是惊人的，也是 B+ 树查询效率极高的保障。树的每一层，最多对应一次磁盘 I/O。3-4 的高度意味着绝大多数查询最多只需要 3-4 次磁盘 I/O。

前置假设：

1. 页大小 (Page Size): 16 KB = 16384 Bytes.
2. 主键类型 (Key): BIGINT，占用 8 字节。
3. 指针大小 (Pointer): 在 InnoDB 的页结构中，指针（指向子节点的页号）通常占用 6 字节。
4. 行数据大小 (Row Data): 假设我们表的一行数据平均大小为 1 KB = 1024 Bytes。

计算开始：

• 非叶子节点（分支节点）能存多少个指针？
  • 非叶子节点存储的是 [主键值, 指针] 的组合。
  • 每个组合的大小约为 8 bytes (key) + 6 bytes (pointer) = 14 bytes。
  • 一个 16KB 的页能存放的指针数量（也称为扇出 Fan-out） = 16384 / 14 ≈ 1170 个。
• 叶子节点（数据页）能存多少行数据？
  • 叶子节点存储的是完整的行数据。
  • 一个 16KB 的页能存放的行数 = 16384 / 1024 = 16 行。

基于以上计算，我们来看不同高度的树能存多少数据：

• 高度为 2 的 B+ 树：
  • 结构：1个根节点（非叶子） + N个叶子节点。
  • 根节点可以指向 1170 个叶子节点。
  • 最大存储行数 = 1170 (叶子节点数) * 16 (行/页) ≈ 1.8 万。
  • 结论：几万条数据的小表，2 层就够了。
• 高度为 3 的 B+ 树：
  • 结构：1个根节点 -> N个分支节点 -> M个叶子节点。
  • 根节点可以指向 1170 个分支节点。
  • 每个分支节点可以指向 1170 个叶子节点。
  • 最大存储行数 = 1170 (一级指针) * 1170 (二级指针) * 16 (行/页) ≈ 21,902,400。
  • 结论：一个高度为 3 的 B+ 树，已经可以轻松存储超过 2000 万条记录！这就是为什么我们说千万级别的表，查询依然飞快。
• 高度为 4 的 B+ 树：
  • 最大存储行数 = 1170 * 1170 * 1170 * 16 ≈ 25,625,808,000。
  • 结论：一个高度为 4 的 B+ 树，理论上可以存储超过 256 亿条记录！这已经远远超出了绝大多数单表的承载极限。

2. 聚集索引和非聚集索引

聚集索引和非聚集索引是 MySQL (特别是 InnoDB 存储引擎) 中性能优化的核心。

• 聚集索引 (Clustered Index)：它不是一种独立的索引“类型”，而是数据表的物理存储方式。在聚集索引中，数据行的物理顺序与索引键的逻辑（排序）顺序完全相同。一张表只能有一个聚集索引。在 MySQL 的 InnoDB 引擎中，主键 (Primary Key) 就是聚集索引。
• 非聚集索引 (Non-Clustered Index)：也叫二级索引 (Secondary Index)。它的逻辑顺序与磁盘上行的物理存储顺序不同。索引的叶子节点存储的不是完整的数据行，而是索引键的值和指向对应数据行的“书签”（在 InnoDB 中，这个“书签”就是该行的主键值）。一张表可以有多个非聚集索引。

解释：

• 聚集索引就像这本字典本身。单词（数据）就是按照 A-Z 的顺序（索引）物理排列的，索引和数据融为一体。你要找 “Database”，直接翻到 D 开头的部分就行，找到了索引也就找到了数据。
• 非聚集索引就像这本书末尾的“术语表”。比如你想找“关系模型”这个概念，术语表按拼音排序，你找到它后，它会告诉你：“详见第 88 页”。你拿到页码（主键），再去第 88 页找到完整的解释（数据）。这是一个“二次查找”的过程。

2.1 聚集索引 (主键索引) 的工作原理

【查询流程：SELECT * FROM users WHERE id = 25;】

1. B+ 树从根节点开始查找
     |
2. 沿着树的分支向下，比较 id 值
     |
3. 到达叶子节点，直接命中 id=25 的节点
     |
4. 节点内包含了 id=25 的【整行数据】(id, name, email, ...)
     |
5. 直接返回数据，查找结束。 (一次定位)

2.2 非聚集索引 (二级索引) 的工作原理

【查询流程：SELECT * FROM users WHERE name = 'Alice';】 (假设 name 列有索引, id 是主键)

1. 在 name 索引的 B+ 树中查找 'Alice'
     |
2. 到达叶子节点，命中 'Alice'
     |
3. 节点内只包含【name 的值 'Alice' 和 主键 id 的值，比如 42】
     |
4. 拿到主键 id=42，现在需要完整数据，所以...
     |
5. 回到聚集索引 (主键索引) 的 B+ 树，再次查找 id=42
     |
6. 定位到 id=42 的叶子节点，获取【整行数据】
     |
7. 返回数据，查找结束。 (两次定位，这个过程也叫“回表”)

2.3 创建索引注意点

1. 谨慎选择主键：由于聚集索引就是主键，主键的选择至关重要。最佳实践是使用一个单调递增、非业务相关的、短小的列作为主键，比如 BIGINT 类型的 AUTO_INCREMENT。这可以减少数据页分裂，保持物理存储的紧凑性。
2. 避免使用 UUID 作主键：UUID 是随机、无序且长的字符串。用它作主键会导致新插入的数据随机分布在磁盘上，引发大量的页分裂 (Page Split) 和磁盘碎片，严重影响插入性能和未来的查询性能。
3. 善用“覆盖索引”：覆盖索引 (Covering Index) 是一个重要的优化技巧。如果一个查询所需的所有列（SELECT 和 WHERE 中用到的）都恰好在一个非聚集索引中，那么引擎就无需回表去查聚集索引了，直接从该索引的叶子节点就能获取所有数据，速度极快。
4. 索引不是越多越好：每个额外的索引都会占用磁盘空间，并且在进行 INSERT, UPDATE, DELETE 操作时，都需要维护索引结构，这会降低写入性能。只创建真正需要的索引。
5. SELECT * 症：无脑使用 SELECT * 是覆盖索引优化的大敌。你可能本来只需要 2 个字段，它们都在一个二级索引里，查询本可以飞快。但 SELECT * 强制引擎必须“回表”去获取所有其他字段，性能瞬间下降。按需索取字段是好习惯。
6. 在低基数（低区分度）列上建索引：比如在“性别”列 (gender) 上建索引。由于该列只有“男”、“女”、“未知”等寥寥几个值，索引的选择性 (Selectivity) 非常低。查询优化器很可能会认为走索引还不如直接全表扫描来得快，从而放弃使用该索引。

2.4 提问问题

1. 主键就是聚集索引吗？

InnoDB 表必须有且只有一个聚集索引。它优先选择主键来充当，如果没有主键，则按以下规则寻找替代品或自动创建。

1. 首选：显式定义的主键 (PRIMARY KEY)
2. 次选：第一个 UNIQUE NOT NULL 索引
3. 最终选择：内部生成的隐藏 ROW_ID

2. 联合索引可以做主键吗？

完全可以。联合索引不仅可以做主键，而且在某些特定设计场景下是必须且高效的选择。

当主键是联合主键时，聚集索引（即数据的物理存储）的排序规则会变得更加精妙：

• 数据会首先按照联合主key的第一个列 (student_id) 进行排序。
• 在第一个列的值相同的情况下，再按照第二个列 (course_id) 进行排序。
• 以此类推，直到所有主键列。

3. MyISAM 全是非聚集索引吗

MyISAM 存储引擎中所有的索引，包括主键索引，都是非聚集索引。
MyISAM 将数据和索引完全分离开来存储。

• 数据存储在 .MYD (MYData) 文件中。
• 所有索引（包括主键索引和二级索引）都存储在 .MYI (MYIndex) 文件中。

4. 聚集索引在物理上是连续的吗

理想中是，但现实中不完全是。更准确的说法是：聚集索引保证了数据在“逻辑上”的连续性，并“尽力”维持物理上的连续性。

当你使用一个 BIGINT AUTO_INCREMENT 主键时，每次插入 (INSERT) 新数据，它的主键值都比之前的大。因此，这行新数据会被追加到表的末尾，即最后一个数据页的末尾。

现实世界的数据操作远比简单的追加要复杂，以下几种情况会打破物理上的连续性：

• 页分裂 (Page Split) - 最大的“破坏者”：
  • 场景： 假设一个数据页（比如 Page 10，存储了 ID 100-200）已经满了。现在你需要插入一条主键为 150 的数据（比如使用 UUID 主键，或者手动指定了一个中间值）。
  • 过程：InnoDB 无法将这条数据挤进已满的 Page 10。它必须进行“页分裂”：
    1. 创建一个新的数据页 (比如 Page 50)。
    2. 将 Page 10 中大约一半的数据（比如 ID 151-200）移动到新的 Page 50 中。
    3. 将新的数据（ID 150）插入到 Page 10 的末尾。
  • 结果： 现在，逻辑上连续的 ID 100-200，在物理上被存储在了两个可能相距甚远的 Page 10 和 Page 50 上。物理连续性被打破了。
• 数据删除 (DELETE)： 删除数据会在数据页内部留下“空洞”。虽然这个空间以后可以被重用，但它造成了页内部的碎片化，破坏了页内数据的物理紧凑性。
• 数据更新 (UPDATE)： 如果你更新了主键列的值（虽然极不推荐），或者更新了变长字段（如 VARCHAR）导致行变长或变短，也可能导致行需要在页内移动，甚至移动到其他页，从而产生碎片。

5. 聚集索引的本身和其他字段的存储是怎么组成和排列的

在 InnoDB 中，一张表本身就是它的聚集索引。聚集索引的叶子节点包含了表中的所有数据。

                        +----------------------+
                        |      根节点 (Root)   |
                        | [1, Ptr_A][500, Ptr_B]|  <-- (键值范围, 指向下一层的指针)
                        +----------------------+
                               /         \
                              /           \
                 +-----------------+   +------------------+
                 | 分支节点 (Branch) |   | 分支节点 (Branch)  |
                 | [1, Ptr1][250, Ptr2]|   | [500, Ptr3][750, Ptr4]|
                 +-----------------+   +------------------+
                     /       \             /         \
                    /         \           /           \
+<------->+---------+<------>+---------+<------>+---------+<------>+---------+
| 叶子节点  |         | 叶子节点  |         | 叶子节点  |         | 叶子节点  |
| (数据页1) |<-------->| (数据页2) |         | (数据页3) |<-------->| (数据页4) |
|-----------|         |-----------|         |-----------|         |-----------|
| - Row(id=1, name='A', ...)|         | - Row(id=250,...)|         | - Row(id=500,...)|         | - Row(id=750,...)|
| - Row(id=2, name='B', ...)|         | - Row(id=251,...)|         | - Row(id=501,...)|         | - Row(id=751,...)|
| ...       |         | ...       |         | ...       |         | ...       |
| - Row(id=249,...)|         | - Row(id=499,...)|         | - Row(id=749,...)|         | - Row(id=999,...)|
+-----------+         +-----------+         +-----------+         +-----------+
  ^
  |
  +-- 双向链表指针，保证叶子节点的逻辑顺序

结构组成与排列：

1. 整体结构：一个巨大的 B+ 树
   • 整张表被组织成一个 B+ 树。树的键（Key）就是你定义的主键。
2. 非叶子节点 (根节点和分支节点)
   • 组成： [主键值, 指针] 对。它们不存储任何“其他字段”的数据。
   • 作用： 它们是“路标”或“目录”。比如根节点中的 [500, Ptr_B] 意味着“所有主键值大于等于 500 的数据，请去 Ptr_B 指向的分支节点查找”。
   • 排列： 节点内部的“路标”是按主键值排序的。
3. 叶子节点 (Leaf Nodes) - 数据的最终归宿
   • 组成：这才是最关键的部分！每个叶子节点（在 MySQL 中也叫数据页，默认 16KB）包含：
     • 主键值 (Primary Key Value)
     • 事务 ID 和回滚指针 (Transaction ID & Rollback Pointer): 用于 MVCC 和事务处理。
     • 该行的所有其他列的数据 (ALL other columns’ data): 比如 name, email, registration_date 等所有字段都原封不动地存储在这里。聚集索引和数据是真正地“长在了一起”。
   • 排列：
     • 页内排列： 在一个数据页内部，所有的行都是按照主键的顺序严格排列的。
     • 页间排列： 所有的叶子节点（数据页）通过一个双向链表互相连接。这确保了即使发生了页分裂导致物理位置不连续，我们依然可以按主键顺序从第一个数据页一路遍历到最后一个数据页。

3. 索引实践

3.1 核心原则

• 为最频繁的查询条件创建索引 (WHERE 子句)

  -- BAD: 如果 user_id 列没有索引，将会非常慢
  SELECT * FROM orders WHERE user_id = 12345;

• 为表连接的关联字段创建索引 (JOIN … ON 子句)

  在 JOIN 操作中，ON 后面的关联字段，特别是“被连接”的表（通常是右边的表）的关联字段，必须建立索引。否则 MySQL 会采用一种效率极低的“嵌套循环连接”算法，性能堪称灾难。

  SELECT o.*, u.user_name
  FROM orders o
  JOIN users u ON o.user_id = u.id;
  -- GOOD: 必须在 orders.user_id 和 users.id 上都建立索引
  -- (通常 users.id 是主键，已经有索引了，所以重点是 orders.user_id)

• 为需要排序的字段创建索引 (ORDER BY 子句)

  B+ 树本身就是有序的。如果 ORDER BY 的字段有索引，MySQL 可以直接利用索引的有序性返回结果，避免了额外的“文件排序”（Filesort）操作，这是一个巨大的性能提升。

  -- 如果在 create_time 上有索引，此查询效率会高很多
  -- 注意： 这种优化通常需要 WHERE 和 ORDER BY 的字段能组合成一个索引才能生效。
  SELECT * FROM orders WHERE status = 'paid' ORDER BY create_time DESC LIMIT 10;

• 为需要分组的字段创建索引 (GROUP BY 子句)

  原理同上，GROUP BY 也需要先对数据进行排序，利用索引的有序性同样可以避免昂贵的排序操作。

3.2 最左前缀匹配原则[最最最重要]

这是联合索引的基石。当你创建一个联合索引 INDEX(a, b, c) 时，它实际上等价于创建了三个索引：

• INDEX(a)
• INDEX(a, b)
• INDEX(a, b, c)

查询必须从索引的最左边的列开始，并且不能跳过中间的列。

技巧： 在建立联合索引时，将选择性最高（区分度最大） 的列放在最左边。

3.3 拥抱覆盖索引 (Covering Index)

覆盖索引是指一个查询语句中，所需要的所有字段（SELECT, WHERE, ORDER BY 等）都能从一个索引中直接获取，而无需再回到主键索引中去查找（即“回表”）。这是 InnoDB 中一个极重要的性能优化手段。

-- 假设我们有一个索引  INDEX idx_age(age)
-- 这个查询会先通过 idx_age 找到所有 age=30 的主键id，然后拿着每个id去主键索引里查 name，这个过程就是“回表”。
SELECT id, name FROM users WHERE age = 30;

-- 优化：建立一个覆盖索引 INDEX idx_age_name(age, name)
-- 这个查询会直接从 idx_age_name 索引树中拿到所有需要的数据，无需回表，性能极高。
SELECT id, name FROM users WHERE age = 30;

3.4 索引注意点

• “保持列的‘纯洁’”——不要在索引列上使用函数、进行计算或类型转换。这会导致索引失效，MySQL 放弃索引转而进行全表扫描。

• 尽量选择区分度高的列作为索引，区分度的公式是 count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1

• 选择合适的列长度 (前缀索引)，对于 VARCHAR 或 TEXT 这样很长的字符串列，完全索引整个列会使索引变得非常大，效率低下。我们可以只索引其一部分前缀。

  可以通过 SELECT COUNT(DISTINCT LEFT(column_name, N)) / COUNT(*) FROM table; 来计算不同长度 N 的选择性，选择一个在选择性和索引大小之间达到最佳平衡的长度。

• 如果用户确定指定某个索引来完成查询，那么最可靠的是使用FORCE INDEX，而不是USE INDEX。

• 索引的列不要为 NULL

3.5 提问问题

1. 如何查看索引的区分度

SHOW INDEX FROM tasks_video;
-- 在返回的结果中，有一列叫做 Cardinality。这个数值就是 MySQL 对该索引基数的估算值。

tasks_video	0	PRIMARY	1	id	A	6028592				BTREE			YES	
tasks_video	0	task_id	1	task_id	A	6042012				BTREE			YES	

在绝大多数情况下，是的，区分度越高，索引的效率就越好。

• 高区分度（如 user_email）： 假设有 1000 万用户，email 基本不重复，基数接近 1000 万。当你 WHERE email = 'a@b.com' 时，索引能迅速定位到几乎唯一的一行，筛选效率极高。
• 低区分度（如 gender）： 只有 ‘男’, ‘女’, ‘未知’ 等几个值，基数极小。如果你通过 gender 索引查询，MySQL 发现即使使用了索引，也需要扫描表中近一半的数据（比如找所有女性用户），然后还要进行大量的“回表”操作。此时，优化器可能认为还不如直接全表扫描来得快。

2. 如果区分度高的是范围查询，低的是等值查询怎么办?

标准答案：将等值查询的列放在前面，范围查询的列放在后面。

有一个查询需求：SELECT * FROM employees WHERE status = 'active' AND age > 30;

• status：区分度低（可能只有 ‘active’, ‘inactive’, ‘pending’ 几个值）。
• age：区分度高。

黄金法则：在设计联合索引时，排序的优先级是「等值查询 > 范围查询」。将所有等值查询的列放在前面，然后把范围查询的列放在最后面。

3. ESR VS ERS （看范围和排序的程度）

它代表了联合索引中列的推荐顺序：

• E (Equals): 等值查询的列。即在 WHERE 子句中使用 = 或 IN 进行精确匹配的列。
• S (Sort): 排序的列。即在 ORDER BY 子句中使用的列。
• R (Range): 范围查询的列。即在 WHERE 子句中使用 >、<、BETWEEN、LIKE 'prefix%' 等进行范围匹配的列。

确保相等字段始终排在第一位。将相等性应用于复合索引的前导字段，可让您利用其余字段值按排序顺序排列的优势。接下来，根据索引的特定需求，选择使用排序字段还是范围字段：

• 如果避免内存中排序很重要，请将排序字段放在范围字段之前 (ESR)
  • 劣势：先索引扫描，逐行过滤时，如果不是覆盖索引，会产生大量的回表
• 如果查询中的范围谓词具有很强的选择性，请将其放在排序字段 (ERS) 之前
  • 劣势：还是会 filesort

4. 联合索引的最佳实践是什么

• 第一法则：最左前缀匹配原则 (Leftmost Prefix Matching)
• 等值优先，范围断后
• 选择性（区分度）优先原则
  • 如果一个区分度低的列是等值查询，而一个区分度高的列是范围查询，依然要把等值查询的列放在前面。
• 追求覆盖索引 (Covering Index)

5. 哪些情况创建了索引，但是没有使用上

• 优化器认为全表扫描更快
• 索引列上进行了运算、函数或类型转换
• LIKE 查询以通配符 % 开头
• 使用了 OR，且 OR 前后的字段没有各自独立的索引
• 联合索引未遵循最左前缀原则

6. 索引的列可以为 NULL吗

可以。 无论是普通索引、唯一索引还是全文索引，都允许列值为 NULL。最佳实践是尽量避免。

4. 头脑风暴

• B+树走索引是从上往下，全表扫描是从左往右。
• InnoDB只能有一个聚集索引，索引和数据在一起。
• InnoDB其他的索引就做辅助索引，走索引后，找到的数据时在用聚集索引，再次回表查询。
• 建立索引：最左匹配，区分度高，不要为 NULL，扩展索引，数量控制。
• 查询索引：复合索引，不参与函数，不强转类型。

5. 参考资料

• https://tech.meituan.com/2014/06/30/mysql-index.html
• https://www.infoq.cn/article/OJKWYykjoyc2YGB0Sj2c
• https://www.cs.usfca.edu/~galles/visualization/BPlusTree.html
• https://xiaolincoding.com/mysql/index/index_lose.html