5 分钟搞懂索引

索引是什么？

为了快速查找而维护的辅助数据结构。 类比：一本书的目录。

没有索引：找"第 5 章"得一页一页翻——全表扫描。有索引：目录里直接看到第 5 章在第 123 页——索引查找。

怎么建？

-- 建表时
CREATE TABLE users (
  id INT PRIMARY KEY,           -- 主键自带索引
  email VARCHAR(255),
  name VARCHAR(64),
  INDEX idx_email (email),
  UNIQUE INDEX uq_email (email) -- 唯一索引
);

-- 后加
CREATE INDEX idx_name ON users (name);

-- 删
DROP INDEX idx_name ON users;

看是否命中索引

EXPLAIN SELECT * FROM users WHERE email = 'a@x.com';

看 type 列：

值	含义	好坏
`system` / `const`	一行命中（主键 / 唯一索引等于查）	最快
`eq_ref` / `ref`	走非唯一索引	快
`range`	范围查询走索引	较快
`index`	全索引扫描	一般
`ALL`	全表扫描	❌ 警报

复合索引和"最左前缀"

INDEX idx_user_status_created (user_id, status, created_at)

这一个索引能用来加速：

✅ WHERE user_id = ? ✅ WHERE user_id = ? AND status = ? ✅ WHERE user_id = ? AND status = ? AND created_at > ? ✅ WHERE user_id = ? AND status = ? ORDER BY created_at

但不能（或低效）：

❌ WHERE status = ?（跳过最左） ❌ WHERE created_at > ?（跳过两个）

规则：复合索引按从左到右匹配，断了就停。

覆盖索引（性能利器）

如果所有查询用到的列都在索引里，根本不用回表查数据：

INDEX idx_user_status (user_id, status, created_at)

-- 这条查询是"覆盖索引"，超快：
SELECT user_id, status, created_at FROM orders WHERE user_id = 42;

-- EXPLAIN 的 Extra 列会显示 "Using index"

什么时候不该建索引

小表（< 1 万行）：扫一遍也快，索引反而占空间
写多读少：每次 INSERT/UPDATE 都要维护索引，写性能下降
基数极低的列（如 gender 只有 2 值）：MariaDB 优化器会选全表扫
频繁更新的列：索引会被频繁调整
每列都建一个索引：是最常见的错误。复合索引几乎总是比多个单列索引更优。

索引太多怎么办？

-- 看一张表有哪些索引
SHOW INDEXES FROM orders;

-- 看哪些索引从来没用过（MariaDB 10.0+）
SELECT object_schema, object_name, index_name
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE index_name IS NOT NULL AND count_star = 0;

定期清掉没用的，能省不少 RAM。

索引常用模式

1. 时间序列

INDEX idx_user_time (user_id, created_at DESC)
-- 适合: "查某用户最近 N 条"
SELECT * FROM events WHERE user_id = 42 ORDER BY created_at DESC LIMIT 20;

2. 唯一约束 + 索引

UNIQUE INDEX uq_email (email)
-- 既保证唯一，又加速查询

3. JSON 函数索引（MariaDB 10.5+）

ALTER TABLE products
  ADD COLUMN price_cents INT AS (JSON_EXTRACT(meta, '$.price_cents')) STORED,
  ADD INDEX idx_price (price_cents);

-- 查询写法：
SELECT * FROM products WHERE JSON_EXTRACT(meta, '$.price_cents') > 100;

4. 函数索引（MariaDB 10.5+）

ALTER TABLE users
  ADD INDEX idx_lower_email ((LOWER(email)));

SELECT * FROM users WHERE LOWER(email) = LOWER(?);  -- 走索引

全文索引（中文搜索）

CREATE TABLE articles (
  id INT PRIMARY KEY,
  title VARCHAR(255),
  body TEXT,
  FULLTEXT KEY ft_body (title, body) WITH PARSER ngram  -- ngram 支持中文
);

SELECT * FROM articles WHERE MATCH(title, body) AGAINST('MariaDB');

详见全文检索。

向量索引（MariaDB 11.8+）

CREATE TABLE chunks (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  embed VECTOR(1536) NOT NULL,
  VECTOR INDEX (embed) M=16 DISTANCE=cosine
);

详见 RAG 教程。

常见错误

1. 在 WHERE 里对索引列做计算：WHERE YEAR(created_at) = 2026 不走索引。改成 WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01'。

2. LIKE '%xxx'：开头通配符不走索引。LIKE 'xxx%' 走。

3. 不同字符集 JOIN：两张表 collation 不同时，JOIN 会失效。建表时统一。

4. 隐式类型转换：WHERE user_id = '42'（字符串和 BIGINT），可能不走索引。

下一步

JOIN —— JOIN 怎么用索引
InnoDB 内部 → B+ 树 —— 索引的物理形态
优化器深度 —— MariaDB 怎么选索引

5 分钟搞懂索引

本页目录