mysql数据库的索引

作者

MySQL 索引完全指南

本文档专门讲 索引（Index）：什么是索引、为什么需要、有哪些类型、怎么创建和删除、什么时候该建、什么时候别乱建。每一步都配有详细说明和大量示例，适合零基础新手跟着做。

目录

  1. 什么是索引
  2. 为什么需要索引
  3. 索引的代价（不要乱建）
  4. 索引的类型
  5. 创建索引
  6. 查看索引
  7. 删除索引
  8. 单列索引与复合索引
  9. 什么时候该建索引
  10. 什么时候不宜建太多索引
  11. 复合索引的“最左前缀”原则
  12. 简单了解 EXPLAIN（是否用到索引）
  13. 常见错误与注意点
  14. 综合示例与速查表

1. 什么是索引

1.1 通俗理解

索引可以理解成书的目录或字典的拼音检字表：

  • 没有目录：找某一页要一页一页翻（全表扫描）。
  • 有目录：先查目录，直接翻到大概位置，再稍微找一下（走索引，通常快很多）。

在数据库里：

  • 表相当于一本“数据书”，每一行是一条记录。
  • 索引是建立在某一列或几列上的“目录”，按这些列的值排好序或做某种结构，方便按这些列查、排序、分组时快速定位，而不是整张表一行行扫。

1.2 专业一点的说法

  • 索引是一种数据结构（MySQL 常用 B+ 树），用来加快对表中数据的查找、排序。
  • 索引不改变表里的数据，只是额外存一份“按某列（或几列）组织的信息”，占空间，写数据时也要维护这份信息。

所以：索引 = 用空间和写性能换查询性能的“辅助结构”。

2. 为什么需要索引

2.1 没有索引时：全表扫描

当执行例如：

SELECT * FROM students WHERE name = '张三';

若 name 没有索引，MySQL 会从第一行扫到最后一行，逐行比较 name 是否等于 '张三'。表有 10 万行就要比 10 万次，慢。

2.2 有索引时：按索引查找

若在 name 上建了索引，MySQL 会先到“索引”里找 '张三' 对应的位置，再根据索引指向去拿那一行（或几行），比较次数少很多，查询变快。

2.3 索引能帮上忙的场景

  • WHERE 条件里用到的列（如 WHERE name = '张三'、WHERE age > 18）。
  • ORDER BY 排序列（如 ORDER BY created_at DESC）。
  • GROUP BY 分组列。
  • JOIN 时用来关联的列（如 ON a.user_id = b.id）。

给这些列建索引，往往能明显加快查询。
反之，从不参与查询、排序、分组的列上建索引，基本用不上，还白占空间、拖慢写入。

3. 索引的代价（不要乱建）

3.1 占空间

索引要额外存一份（或几份）数据，表越大、索引越多，占的磁盘越多。

3.2 拖慢写入

  • INSERT：除了写表，还要更新索引。
  • UPDATE：若改的列在索引里，要改索引。
  • DELETE：要从索引里删掉对应项。

索引越多，写操作越慢。所以不是索引越多越好，要针对“常查、常排序”的列建，且数量适中。

3.3 小结

  • 读多写少：多建几个索引往往划算。
  • 写很多、读一般：索引要少而精。
  • 小表（几千行以内）：有时全表扫也很快，可以不建或少建索引。

4. 索引的类型

4.1 按“约束/用途”分（常见）

类型 说明 何时产生
主键索引 主键自动带索引，唯一且非空 建主键时自动
唯一索引 UNIQUE 列自动有索引，值不重复 建 UNIQUE 时自动
普通索引 仅加速查找，不限制重复 手动 KEY/INDEX
全文索引 用于全文搜索（FULLTEXT） 手动，少用

说明：主键、UNIQUE 本身是“约束”，但实现时都会建索引，所以查主键列、唯一列时天然能利用索引。

4.2 按“列数”分

  • 单列索引：只在一列上建索引，如 INDEX (name)。
  • 复合索引（联合索引）：在多列上建一个索引，如 INDEX (class_name, age)。
    使用时要注意“最左前缀”（后面会讲）。

4.3 数据结构（了解即可）

MySQL 常用 B+ 树 存索引，所以默认说的“索引”一般指 B+ 树索引。
还有哈希索引（Memory 引擎等）、全文索引等，新手先掌握“普通 B+ 树索引”即可。

5. 创建索引

5.1 建表时创建（推荐，表设计阶段就想好）

语法一：在列定义后直接写

CREATE TABLE students (
    id          INT PRIMARY KEY AUTO_INCREMENT,   -- 主键自带主键索引
    name        VARCHAR(50),
    age         INT,
    class_name  VARCHAR(20),
    INDEX idx_name (name),           -- 普通索引：name
    INDEX idx_age (age),             -- 普通索引：age
    INDEX idx_class_age (class_name, age)   -- 复合索引
);

语法二：在表末尾统一写

CREATE TABLE students (
    id          INT PRIMARY KEY AUTO_INCREMENT,
    name        VARCHAR(50),
    age         INT,
    class_name  VARCHAR(20),
    INDEX idx_name (name),
    INDEX idx_class_age (class_name, age)
);

唯一索引（建表时）：

CREATE TABLE users (
    id       INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) UNIQUE,    -- UNIQUE 会自动建唯一索引
    email    VARCHAR(100),
    UNIQUE KEY uk_email (email)     -- 或这样写唯一索引
);

5.2 表已存在：用 CREATE INDEX 创建

语法：

CREATE INDEX 索引名 ON 表名 (列名);

示例：给 students 表的 name 列建普通索引

CREATE INDEX idx_name ON students (name);

示例：复合索引（多列）

CREATE INDEX idx_class_age ON students (class_name, age);

唯一索引：

CREATE UNIQUE INDEX uk_email ON users (email);

5.3 表已存在：用 ALTER TABLE 创建

语法：

ALTER TABLE 表名 ADD INDEX 索引名 (列名);
ALTER TABLE 表名 ADD UNIQUE INDEX 索引名 (列名);

示例：

ALTER TABLE students ADD INDEX idx_name (name);
ALTER TABLE students ADD INDEX idx_class_age (class_name, age);
ALTER TABLE users ADD UNIQUE INDEX uk_email (email);

5.4 索引命名建议

  • 普通索引：idx_列名 或 idx_表名_列名，如 idx_name、idx_students_age。
  • 唯一索引：uk_列名 或 uk_表名_列名，如 uk_email。
  • 复合索引：idx_列1_列2，如 idx_class_age。

名字只要不重复、能看出是哪个索引即可。

6. 查看索引

6.1 SHOW INDEX FROM 表名

SHOW INDEX FROM students;

会列出该表上所有索引，包括：

  • Table：表名
  • Key_name：索引名
  • Column_name：索引包含的列
  • Non_unique：0 表示唯一索引，1 表示非唯一
  • Seq_in_index：在复合索引中是第几列（1, 2, 3…）
  • Index_type：如 BTREE

示例输出（示意）：

+----------+------------+---------------+--------------+-------------+
| Table    | Key_name   | Seq_in_index  | Column_name  | Non_unique  |
+----------+------------+---------------+--------------+-------------+
| students | PRIMARY    | 1             | id           | 0           |
| students | idx_name   | 1             | name         | 1           |
| students | idx_class_age | 1          | class_name   | 1           |
| students | idx_class_age | 2          | age          | 1           |
+----------+------------+---------------+--------------+-------------+

6.2 SHOW CREATE TABLE 表名

SHOW CREATE TABLE students;

会显示建表语句，其中包含 KEY idx_xxx (...)、UNIQUE KEY uk_xxx (...) 等索引定义。

7. 删除索引

7.1 语法（索引名在 SHOW INDEX 里能看到）

DROP INDEX 索引名 ON 表名;

或：

ALTER TABLE 表名 DROP INDEX 索引名;

注意：主键的索引不能单独“删索引”，只能通过删主键（ALTER TABLE 表 DROP PRIMARY KEY）去掉，一般不会这么做。

7.2 示例

DROP INDEX idx_name ON students;
ALTER TABLE students DROP INDEX idx_class_age;

删除后，该列上的加速效果就没了，查询可能变慢，但表数据不受影响。

8. 单列索引与复合索引

8.1 单列索引

只在一列上建索引，适合条件/排序只涉及这一列的查询。

示例：

CREATE INDEX idx_name ON students (name);

适合：WHERE name = '张三'、ORDER BY name。

8.2 复合索引（多列一起建一个索引）

把多列按顺序建在一个索引里，适合条件或排序同时涉及这几列的查询。

示例：

CREATE INDEX idx_class_age ON students (class_name, age);

表示索引先按 class_name 排，再在同一 class_name 下按 age 排。

适合的查询示例：

  • WHERE class_name = '高一1班'（用上索引第一列）
  • WHERE class_name = '高一1班' AND age = 18（用上索引两列）
  • ORDER BY class_name, age（用上索引）

不适合的示例：

  • 只有 WHERE age = 18（没有 class_name，复合索引往往用不上，见下节“最左前缀”）

8.3 单列多索引 vs 一个复合索引

  • 若经常只按 class_name 查，也经常只按 age 查，可以建两个单列索引：idx_class、idx_age。
  • 若经常按 class_name 和 age 一起查，建一个复合索引 (class_name, age) 通常更合适，且能兼顾“只按 class_name 查”的情况（最左前缀）。

9. 什么时候该建索引

9.1 建议建索引的列（满足越多越值得建）

  • 经常出现在 WHERE 里：如 WHERE user_id = ?、WHERE status = ?。
  • 经常出现在 ORDER BY 里：如 ORDER BY created_at DESC。
  • 经常出现在 GROUP BY 里：如 GROUP BY category_id。
  • 用于 JOIN 关联：如 ON a.user_id = b.id，在 a.user_id 上建索引。
  • 区分度较高：列的值种类多（如 user_id、订单号），比“性别”这种只有两三种值的列更适合建索引。

9.2 示例：学生表

若查询经常是：

  • 按姓名查：WHERE name = ?
  • 按班级查：WHERE class_name = ?
  • 按班级+年龄：WHERE class_name = ? AND age = ?
  • 按年龄排序：ORDER BY age

可以这样建：

CREATE INDEX idx_name ON students (name);
CREATE INDEX idx_class_age ON students (class_name, age);
-- 若经常只按班级查，idx_class_age 已能用到第一列 class_name
-- 若还经常单独按 age 查且很多，可再考虑 idx_age，否则可不建

9.3 主键、唯一列

主键和 UNIQUE 列本身就有索引，不需要再单独建。

10. 什么时候不宜建太多索引

10.1 小表

表只有几百、几千行，全表扫描也很快，建很多索引收益小，还占空间、拖慢写入，可以少建或不建。

10.2 区分度很低的列

例如“性别”只有男/女，建索引后每次查还要回表，有时反而不如全表扫。这类列通常不单独建索引，除非在复合索引里做“最左前缀”的一部分（如性别+生日）。

10.3 很少参与查询、排序的列

从不或极少出现在 WHERE、ORDER BY、GROUP BY、JOIN 里的列，建索引用不上，不要建。

10.4 写多读少的表

插入、更新、删除很多，读不多，索引会明显拖慢写入，索引要少而精，只给最常查的列建。

10.5 小结

  • 索引不是越多越好，要按查询和排序习惯来建。
  • 先保证主键、外键/关联列、高频 WHERE/ORDER BY 列有索引，再考虑复合索引。

11. 复合索引的“最左前缀”原则

11.1 什么是“最左前缀”

复合索引是按列的顺序建的，例如 INDEX (A, B, C)：

  • 索引先按 A 排，再在相同 A 下按 B 排，再在相同 A、B 下按 C 排。
  • 使用索引时，MySQL 一般从最左边一列开始用，不能跳过左边的列直接用中间的列。

也就是说：能用到这个复合索引的查询，通常是“从左到右连续”的条件，例如 A、A+B、A+B+C；只用 B、只用 C、或 A+C（跳过 B）往往用不上或只用上前面一部分。

11.2 示例：INDEX (class_name, age)

  • 能用上索引的：
    • WHERE class_name = '高一1班'（用第一列）
    • WHERE class_name = '高一1班' AND age = 18（用两列）
    • ORDER BY class_name 或 ORDER BY class_name, age（用索引排序）
  • 用不上或只用一部分的：
    • WHERE age = 18（没有 class_name，最左列缺失）
    • WHERE age = 18 AND class_name = '高一1班'（条件都有时，优化器可能重排顺序，一般仍能用上 class_name 和 age，但写条件时把 class_name 放前面更清晰）
访问数: 8

发表评论