索引基础

索引用来加速查询。

没有索引时，MySQL 从头到尾扫描整张表（全表扫描）。数据少时感觉不明显，数据多了就慢。

一、索引怎么理解

书的目录：没有目录找某个章节只能一页一页翻，有目录直接跳到对应页码。

数据库索引同理：先通过索引快速定位数据位置，再读取对应行。

二、索引类型

MySQL 常见索引类型：

类型	说明	适用场景
`B-Tree`	默认索引类型，支持范围查询和排序	大多数场景
`Hash`	只支持等值查询，不支持范围	等值精确匹配（Memory 引擎）
`FULLTEXT`	全文索引，用于文本搜索	大段文字的关键词搜索

日常开发基本都是 B-Tree 索引，不用纠结选哪种。

三、主键自带索引

id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT

MySQL 自动为主键创建索引。按主键查询通常最快：

SELECT * FROM users WHERE id = 1;  -- 走主键索引

四、唯一约束也会创建索引

email VARCHAR(100) NOT NULL UNIQUE

UNIQUE 会创建唯一索引：既能加速查询，也能防止重复值。

SELECT * FROM users WHERE email = 'tom@example.com';  -- 走唯一索引

五、创建索引的语法

建表时创建索引

CREATE TABLE posts (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    user_id BIGINT UNSIGNED NOT NULL,
    title VARCHAR(255) NOT NULL,
    status VARCHAR(20) NOT NULL,
    created_at DATETIME NOT NULL,
    PRIMARY KEY (id),
    INDEX idx_posts_status (status),              -- 普通索引
    UNIQUE INDEX idx_posts_title (title)          -- 唯一索引
);

建表后创建索引

-- 普通索引
CREATE INDEX idx_posts_status ON posts (status);

-- 唯一索引
CREATE UNIQUE INDEX idx_posts_email_unique ON posts (title);

-- 联合索引
CREATE INDEX idx_posts_user_status ON posts (user_id, status);

用 ALTER TABLE 创建索引

ALTER TABLE posts ADD INDEX idx_posts_status (status);

ALTER TABLE posts ADD UNIQUE INDEX idx_posts_title (title);

删除索引

-- 方式一：按索引名删除
DROP INDEX idx_posts_status ON posts;

-- 方式二：用 ALTER TABLE
ALTER TABLE posts DROP INDEX idx_posts_status;

查看表上有哪些索引

SHOW INDEX FROM posts;

结果会列出索引名、字段名、是否唯一等信息。

六、普通索引

最常见的索引，没有唯一性要求，纯粹为了加速查询。

CREATE INDEX idx_posts_status ON posts (status);

查询就可能走这个索引：

SELECT id, title FROM posts WHERE status = 'published';

七、联合索引

给多个字段组合建一个索引。

CREATE INDEX idx_posts_user_status_created
ON posts (user_id, status, created_at);

适合这种查询：

SELECT id, title, created_at
FROM posts
WHERE user_id = 1 AND status = 'published'
ORDER BY created_at DESC;

三个字段都能用上。

八、最左前缀原则

联合索引 (user_id, status, created_at) 像一把钥匙有三截。

必须从左边开始用，不能跳着用：

-- ✅ 只用第一个字段
WHERE user_id = 1

-- ✅ 用前两个字段
WHERE user_id = 1 AND status = 'published'

-- ✅ 用全部三个字段
WHERE user_id = 1 AND status = 'published' ORDER BY created_at DESC

-- ❌ 跳过了 user_id，索引用不上
WHERE status = 'published'

-- ❌ 跳过了 user_id 和 status
WHERE created_at > '2024-01-01'

联合索引要从最左边字段开始连续使用。
跳过左边的字段，后面的字段用不上索引。

字段顺序怎么放

把查询中最常出现的字段放最左边。

如果查询大多是 WHERE user_id = ?，user_id 放第一个。

如果查询大多是 WHERE user_id = ? AND status = ?，user_id 放第一个，status 放第二个。

九、LIKE 和索引

-- ✅ 前缀匹配，可以走索引
WHERE title LIKE 'MySQL%'

-- ❌ 后缀或中间匹配，走不了普通索引
WHERE title LIKE '%MySQL'
WHERE title LIKE '%MySQL%'

%MySQL% 前面有通配符，数据库不能从索引开头定位，只能全表扫描。

需要大量全文搜索的话，用 MySQL 全文索引或 Elasticsearch。当前阶段先知道 LIKE '%关键字%' 数据多了会慢。

十、查看执行计划 EXPLAIN

用 EXPLAIN 查看 SQL 会不会走索引：

EXPLAIN
SELECT id, title
FROM posts
WHERE user_id = 1 AND status = 'published'
ORDER BY created_at DESC;

执行后会返回一张表，重点看这几个字段：

字段	含义	关注什么
`type`	访问方式	`ALL` 是全表扫描，`ref`/`range`/`const` 走了索引
`possible_keys`	可能用到的索引	看有没有候选索引
`key`	实际选择的索引	`NULL` 表示没走索引
`rows`	预估扫描行数	越小越好
`Extra`	额外信息	`Using index` 表示覆盖索引，`Using filesort` 表示额外排序

type 的常见值（从好到差）

system > const > eq_ref > ref > range > index > ALL

type	含义	示例
`const`	主键或唯一索引等值查询	`WHERE id = 1`
`ref`	普通索引等值查询	`WHERE user_id = 1`
`range`	索引范围查询	`WHERE created_at > '2024-01-01'`
`index`	全索引扫描	只查索引里的字段
`ALL`	全表扫描	最慢，需要优化

看到 ALL 就要考虑加索引了。

简单判断流程

EXPLAIN 后看 key 字段：
  → 有值 → 走了索引，再看 rows 够不够小
  → NULL  → 没走索引，考虑加索引或优化 SQL

不用一开始就背所有执行计划字段，先学会用它确认 SQL 有没有走索引。

十一、索引不是越多越好

索引有成本：

占用磁盘空间
INSERT / UPDATE / DELETE 时要同步维护索引
索引太多会让优化器选择变慢

适合建索引的字段

场景	示例
经常作为查询条件	`WHERE email = ?`
经常用于连接	`posts.user_id`
经常用于排序	`ORDER BY created_at`
需要唯一约束	`email UNIQUE`

不适合建索引的字段

很少查询的字段
频繁变化但很少过滤的字段
区分度很低的字段（比如只有 0 和 1 的布尔字段）

十二、博客表常用索引设计

-- 文章表：按用户+状态+时间查询
CREATE INDEX idx_posts_user_status_created
ON posts (user_id, status, created_at);

-- 文章表：按分类+状态+时间查询
CREATE INDEX idx_posts_category_status_created
ON posts (category_id, status, created_at);

-- 评论表：按文章+时间查询
CREATE INDEX idx_comments_post_created
ON comments (post_id, created_at);

-- 文章标签表：从标签反查文章
CREATE INDEX idx_post_tags_tag_post
ON post_tags (tag_id, post_id);

文章标签表已经有联合主键 PRIMARY KEY (post_id, tag_id)，能覆盖"按文章查标签"的场景。但从标签反查文章时方向反了，需要补一个 (tag_id, post_id) 的索引。

索引设计围绕真实查询来建，不要脱离业务凭感觉加。
先看 SQL 怎么写，再决定索引怎么建。

#索引基础

#一、索引怎么理解

#二、索引类型

#三、主键自带索引

#四、唯一约束也会创建索引

#五、创建索引的语法

#建表时创建索引

#建表后创建索引

#用 ALTER TABLE 创建索引

#删除索引

#查看表上有哪些索引

#六、普通索引

#七、联合索引

#八、最左前缀原则

#字段顺序怎么放

#九、LIKE 和索引

#十、查看执行计划 EXPLAIN

#type 的常见值（从好到差）

#简单判断流程

#十一、索引不是越多越好

#适合建索引的字段

#不适合建索引的字段

#十二、博客表常用索引设计