索引是MySQL存储引擎用于快速定位数据的一种数据结构。它类似于书籍的目录，通过缩小扫描范围提升查询效率。InnoDB默认使用B+树索引，MyISAM则使用B树（非聚簇结构）。

B+树

  1. B+树是一个多叉的平衡查找树，他的左子树的所有结点值都小于根结点的值，右子树的所有结点的值都大于根结点的值。
  2. B+树的非叶子结点只存放索引，不存储数据。
  3. B+树的叶子节点存储完整数据记录（InnoDB聚簇索引），形成一个有序链表，从小到大排列。
  4. 支持高效的范围查询（>、<、BETWEEN）

B树与B+树对比

特性 B树 B+树
数据存储位置 所有节点 仅叶子节点
叶子节点链接 无 双向链表
范围查询效率 低 高
磁盘IO次数 不稳定 更稳定

索引的分类

从功能逻辑上可以分为：普通索引、唯一索引、主键索引、全文索引、联合索引。

索引类型 特性说明
普通索引 基本索引类型，无限制
唯一索引 保证字段值唯一性
主键索引 特殊的唯一索引，不允许NULL
全文索引 支持文本字段的模糊匹配
联合索引 多列组合索引，遵循最左匹配原则

从物理实现方式上分为：聚簇索引、非聚簇索引。

聚簇索引

  • 叶子节点存放数据与索引。
  • 每个表只能有一个聚簇索引（通常为主键）。
  • 物理存储按主键值有序排列。

非聚簇索引

  • 叶子节点存储主键值而非数据地址。
  • 需要二次查找（回表）获取完整数据。
  • MyISAM所有索引均为非聚簇结构。

对聚簇索引进行增、删、改操作时效率会比非聚簇索引低，因为要维护每一条记录。

什么是回表

使用二级索引查询时，会有一次回表。先找到聚簇索引，再找具体记录。使用二级索引查询时的步骤如下：

  1. 通过二级索引找到主键值

  2. 通过主键值在聚簇索引中定位完整数据

  3. 示例：

    1
    2
    -- email是一个二级索引
    SELECT id, username, email FROM users WHERE email='user@example.com'
    • 先查email索引获取主键id。
    • 再通过主键id检索数据行。

  4. 避免回表的技巧：使用联合索引时，确保查询字段都在索引中可以避免回表。

索引的代价与使用策略

优缺点对比

优点：总的来说，使用索引可以有效地提高查询效率。

  • 使用索引可以有效的减少磁盘的IO次数，提高查询效率。
  • 做关联查询时，关联字段是索引时，可以加快表与表之间的连接。
  • 使用索引做排序和分组时，可以减少在排序时花费的时间。
  • 通过建立唯一索引，能够保证数据的唯一性。

缺点：

  • 需要花费额外的磁盘空间去存储索引。
  • 虽然索引减少了查询时间，但是更新表需要花费更多的时间，在对数据表进行增、删、改操作时，需要去维护索引。
  • 创建和维护索引需要花费时间，并且随着数据量的增加，花费的时间也会增加。

最左匹配原则

最左匹配原则是针对联合索引来说的。

联合索引会首先根据最左边的字段进行排序，然后在保证第一个字段有序的情况下按顺序排序剩下的字段。因此使用联合索引时，当遇到范围查询会丢弃后面的字段；或者不按照顺序使用会导致索引失效。

MySQL有查询优化器，对于联合索引(a,b,c)，会把 b = 1 and a = 1 优化成 a = 1 and b = 1，让索引生效。

使用场景

索引并不是越多越好，应该适当控制索引数量，单表建议不超过5个。以下是适合添加索引的一些场景：

  1. 字段具有唯一性限制的情况（唯一索引）。

  2. 经常出现在 WHERE、GROUP BY、ORDER BY 后的字段。

  3. 经常用于 DISTINCT、JOIN 的字段。

  4. 区分度高的字段建议添加索引。

不适合添加索引跟以上相反：不出现在WHERE、GROUP BY/ORDER BY后的字段；数据量少的表；有大量重复数据的字段；频繁更新的字段；

索引失效场景

  1. 联合索引违反最左匹配原则。

    1
    2
    -- 联合索引(a,b,c)
    WHERE b=1 AND c=2 -- 索引失效

  2. 使用索引进行计算、函数、类型转换等导致失效。

    1
    2
    3
    4
    5
    -- 函数导致失效
    WHERE UPPER(username) =  'test'

    -- 类型转换导致索引失效（字符串转数字）
    WHERE phone = 13800138000

  3. 使用 != 或 <> 或 IS NOT NULL 导致索引失效。

    1
    2
    -- 使用 != 导致索引失效
    WHERE age != '25'

  4. 模糊查询时，索引左侧加上 % 导致失效。

    1
    2
    -- 索引失效
    WHERE nickname LIKE '%test%'

  5. OR 前后加上非索引字段，导致失效。

    1
    2
    -- 如果address无索引则会导致全表扫描
    WHERE age=25 OR address='test address'