按数据结构分类

按数据结构分【创建索引时可以指定类型】

  • B+树索引

主键索引

叶子节点存放了完整的记录

二级索引

叶子节点只存放了主键值，需要==回表==（再根据主键索引去查全部数据）；当然如果通过二级索引只查询主键，或者查询的就是索引列，是不需要回表的（这种情况叫：==覆盖索引==）

  • 哈希索引

    InnoDb不支持

  • 全文索引

    关键词索引，但是大数据上elSearch

按物理存储分

  • 主键索引（聚簇索引）【全部数据】
  • 二级索引（辅助索引）【索引数据列+主键】

按字段特性分

  • 主键索引

  • 唯一索引

  • 普通索引

  • 前缀索引

    前缀索引目的减少存储空间，如果前缀匹配多条数据，需要回表

    1
    2
    3
    # 创建column_name前length长度的前缀索引
    CREATE INDEX index_name
    ON table_name(column_name(length));

按字段特性分

  • 单列索引
  • 联合索引
1
CREATE INDEX index_product_no_name ON product(product_no, name);

索引下推

索引下推（Index Condition Pushdown，ICP）将 WHERE 子句中可以由索引判断的过滤条件下推到存储引擎层执行，减少回表次数和返回给 Server 层的数据量

==发生在联合索引的情况下==

1
2
3
4
5
6
INDEX idx_dep_age (department, age)
-- 查询示例
SELECT * FROM employees 
WHERE department = '技术部' 
  AND age > 25 
  AND name LIKE '张%';

无ICP的流程：

image-20260522150026342

有ICP的流程：

image-20260522150052255

什么时候用索引

  1. 主键，自动加的
  2. 频繁作为 WHERE 条件的列
  3. 经常用于 JOIN 连接的列
  4. 排序和分组字段
  5. 覆盖索引场景
  6. 多列查询（复合索引）

不建议加索引的场景：

  1. 表数据量太小
  2. 频繁更新的==列==

联合索引

单独讲下，这个用的比较多

假设我们有表 employees和联合索引 idx_dept_age_salary(department, age, salary)

索引条目排序规则：

  1. 先按 department排序
  2. department相同的，按 age排序
  3. department和 age都相同的，按 salary排序
  4. 最后才是主键 id

场景1：等值查询（最左前缀匹配）

image-20260522153840093

场景2：多列等值查询

image-20260522153852813

场景3：范围查询

image-20260522153920483

执行流程：image-20260522154010399

场景4：范围查询中断最左前缀

image-20260522154041866

流程：image-20260522155645967

结果：key_len 只用到 department, age，salary 条件在存储引擎层无法使用索引，只能在Server层过滤

场景5：跳过中间列

只能使用索引的第一列 department

image-20260522155458630

索引失效场景

  • 使用 like 以通配符开头（’%abc’），因为这样非有序 %xx 或者 like %xx%

  • 索引列作了计算

  • 联合索引不符合最左匹配

  • 使用范围查询（>、<、between、like）导致后面的索引列失效

  • or的一边有非索引，索引字段会失效

索引优化

  • 使用前缀索引减少空间

  • 覆盖索引减少回表

  • 主键自增减少重构树的代价

  • 连查时，连接列要有索引，小表驱动大表

执行计划

Key：用到的索引

key_len：索引长度

Rows：扫描行数

Type：扫描类型：

​ ALL：全表扫描

​ index：全索引扫描

​ range：索引范围扫描

​ ref：非唯一索引的扫描

​ eq_ref：主键或者唯一索引扫描

​ const：常量级

Extra：Using index ：覆盖索引

问题

  1. 一定会使用索引吗？

    不一定，优化器会根据成本决定：

    image-20260522160410799