一、索引概述

索引是帮助MySQL高效获取数据的数据结构，相当于书的目录。

索引的作用：

  • 加快数据检索速度
  • 降低I/O成本
  • 加速排序和分组

索引的代价：

  • 占用存储空间
  • 降低写操作性能（需要维护索引）

二、索引数据结构

B+树

MySQL使用B+树作为索引结构。

特点：

  • 非叶子节点只存储键值和指针
  • 叶子节点存储所有数据和指针
  • 叶子节点通过双向链表连接
  • 高度低，减少磁盘I/O

优势：

  • 范围查询效率高
  • 单节点存储更多键值，树更矮
  • 天然排序

为什么不用B树

B树：

  • 每个节点存储数据
  • 范围查询需要中序遍历
  • 高度可能更高

B+树更适合：

  • 叶子节点存储所有数据，便于范围查询
  • 非叶子节点不存数据，单节点存储更多键值

三、索引类型

主键索引

数据表的主键自动创建索引。

1
2
3
4
CREATE TABLE user (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);

特点：

  • 唯一且非空
  • InnoDB使用聚簇索引

唯一索引

索引列的值必须唯一。

1
CREATE UNIQUE INDEX idx_email ON user(email);

普通索引

基本的索引类型，无约束。

1
CREATE INDEX idx_name ON user(name);

组合索引

多个列组成的索引。

1
CREATE INDEX idx_name_age ON user(name, age);

遵循最左前缀原则。

全文索引

用于文本搜索。

1
CREATE FULLTEXT INDEX idx_content ON article(content);

四、聚簇索引和非聚簇索引

聚簇索引

数据和索引存储在一起。

InnoDB主键索引：

  • 叶子节点存储完整行数据
  • 一张表只能有一个聚簇索引

非聚簇索引

数据和索引分开存储。

InnoDB辅助索引：

  • 叶子节点存储主键值
  • 需要回表查询获取完整数据

回表

辅助索引查找到主键后，再到聚簇索引查找完整数据。

1
2
3
4
5
6
-- 假设name上有索引
SELECT * FROM user WHERE name = '张三';

-- 执行过程：
-- 1. 在name索引中找到主键id
-- 2. 根据id在聚簇索引中找到完整数据

五、索引使用原则

最左前缀原则

组合索引按定义顺序使用。

1
2
3
4
5
6
7
8
9
10
-- 组合索引：(name, age, city)

-- 命中索引
WHERE name = '张三'
WHERE name = '张三' AND age = 20
WHERE name = '张三' AND age = 20 AND city = '北京'

-- 不命中索引
WHERE age = 20
WHERE city = '北京'

覆盖索引

查询字段都在索引中，无需回表。

1
2
3
4
5
6
7
-- 组合索引：(name, age)

-- 命中覆盖索引
SELECT name, age FROM user WHERE name = '张三';

-- 需要回表
SELECT * FROM user WHERE name = '张三';

索引下推

MySQL 5.6之后，索引遍历过程中执行条件过滤。

1
2
3
4
5
-- 组合索引：(name, age)
SELECT * FROM user WHERE name LIKE '张%' AND age = 20;

-- 无索引下推：根据name条件查到数据后，再过滤age
-- 有索引下推：在索引遍历时就过滤age

六、索引失效场景

对索引列使用函数

1
2
3
4
5
-- 索引失效
SELECT * FROM user WHERE YEAR(create_time) = 2023;

-- 正确写法
SELECT * FROM user WHERE create_time >= '2023-01-01' AND create_time < '2024-01-01';

隐式类型转换

1
2
3
4
5
-- name是varchar类型，索引失效
SELECT * FROM user WHERE name = 123;

-- 正确写法
SELECT * FROM user WHERE name = '123';

LIKE以通配符开头

1
2
3
4
5
-- 索引失效
SELECT * FROM user WHERE name LIKE '%张';

-- 命中索引
SELECT * FROM user WHERE name LIKE '张%';

OR条件

1
2
-- 如果某一列没有索引，整个索引失效
SELECT * FROM user WHERE name = '张三' OR age = 20;

NOT IN和<>操作

1
2
3
-- 索引失效
SELECT * FROM user WHERE status NOT IN (0, 1);
SELECT * FROM user WHERE status <> 0;

七、索引优化建议

索引创建建议

适合创建索引：

  • WHERE条件列
  • JOIN关联列
  • ORDER BY和GROUP BY列
  • 区分度高的列

不适合创建索引：

  • 区分度低的列（如性别）
  • 频繁更新的列
  • 小表的列

索引设计原则

  • 控制索引数量
  • 优先使用组合索引
  • 将选择性高的列放前面
  • 考虑覆盖索引

监控索引使用

1
2
3
4
5
6
7
8
9
10
-- 查看索引使用情况
SELECT * FROM sys.schema_unused_indexes;

-- 查看索引大小
SELECT 
    table_name,
    index_name,
    ROUND(stat_value * @@innodb_page_size / 1024 / 1024, 2) AS size_mb
FROM mysql.innodb_index_stats
WHERE stat_name = 'size';

八、总结

索引要点：

  • B+树结构，适合范围查询
  • 主键索引是聚簇索引
  • 辅助索引需要回表
  • 遵循最左前缀原则
  • 避免索引失效场景

合理使用索引可以大幅提升查询性能，但也需要权衡维护成本。