MySQL 教程

MySQL 索引原理与应用

🎉摘要：本文详细介绍了MySQL数据库索引的核心概念，包括索引的优缺点（提升查询速度与占用存储空间），以及普通索引、唯一索引、前缀索引、复合索引等主要类型。深入解析了B+树数据结构、聚簇索引与非聚簇索引的区别，并提供了索引设计五大原则（如在高选择性字段、WHERE/JOIN/ORDER BY条件上建索引，避免过多索引等）和通过EXPLAIN分析查询性能的实战方法，帮助开发者编写高效SQL。

索引是数据库为数据表字段建立的快速查找目录，大幅提升查询速度，代价是占用存储空间、降低插入 / 更新 / 删除效率。

假如将表比作一本书，索引就是书的目录，查内容不用逐行全表扫描，直接通过目录定位数据。索引是提高查询效率的关键。理解索引原理，才能写出高效的查询。

索引的基本概念

就像前面介绍的一样，可以把索引想象成书的目录。没有目录，你需要一页一页翻找；有了目录，可以直接定位到章节。因此，索引拥有如下优缺点：

优点：

  • 大大加快数据查询速度

  • 可以加速排序和分组操作

  • 唯一索引可以保证数据唯一性

缺点：

  • 占用额外的存储空间（索引需要单独存储）

  • 降低写入速度（需要维护索引）

  • 过多的索引会增加优化器选择成本

索引的类型

普通索引（INDEX）

普通索引是最基础的索引类型，仅仅用作提升查询速度，没有唯一性、非空限制，字段值允许重复、允许存 NULL。普通索引的特点如下：

  • 一张表可以创建多个普通索引。

  • 列内数据可重复，多条记录能存相同值。

  • 仅优化 where、order by、join 查询，不做数据校验。

  • 写入数据时同步维护索引，增删改性能轻微下降。

示例：

-- 创建数据表，且指定索引
mysql> CREATE TABLE index_demo1 (
    ->     id INT PRIMARY KEY,
    ->     name VARCHAR(50),
    ->     email VARCHAR(100),
    ->     INDEX idx_name (name), -- 添加索引
    ->     INDEX idx_email (email) -- 添加索引
    -> );
Query OK, 0 rows affected (0.01 sec)

-- 或者表创建后添加
CREATE INDEX idx_name ON index_demo1(name);

 

唯一索引（UNIQUE INDEX）

唯一索引兼具查询加速与字段去重功能，允许一条 null，单表可创建多个。唯一索引的核心作用如下：

  • 加速查询：和普通索引一样，提升 WHERE、排序查询效率；

  • 唯一性约束：索引字段所有值不能重复，保证数据不重复；

  • 允许存在一个 NULL（NULL 不参与唯一性比对）。

和主键索引、普通索引区别：

  • 对比普通索引：多了去重校验，插入重复值直接报错；

  • 对比 PRIMARY 主键：主键不允许 NULL，一张表只能 1 个主键；唯一索引允 1 条 NULL，一张表可建多个。

例如：

-- 创建表时指定唯一索引
mysql> CREATE TABLE index_demo2 (
    ->     id INT PRIMARY KEY,
    ->     name VARCHAR(50),
    ->     phone VARCHAR(20),
    ->     UNIQUE INDEX uk_phone (phone) -- phone字段唯一索引
    -> );
Query OK, 0 rows affected (0.01 sec)

-- 也可以单独创建唯一索引
mysql> CREATE UNIQUE INDEX uk_name ON index_demo2(name);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

  

前缀索引

前缀索引作用于字符串类型（CHAR/VARCHAR/TEXT），不把完整字段内容建索引，只截取字段前面一部分字符建立索引。前缀索引的作用如下：

  • 减小索引占用磁盘空间，索引体积更小；

  • 降低索引维护成本，提升索引读写速度；

  • 长文本字段直接全字段建索引会非常占空间，前缀索引是折中方案；

  • 对于长字符串，可以只索引前面一部分：

示例：

-- 创建数据表
mysql> CREATE TABLE index_demo3 (
    ->     id INT PRIMARY KEY,
    ->     email VARCHAR(200),
    ->     INDEX idx_email (email(10))  -- 只索引前10个字符
    -> );
Query OK, 0 rows affected (0.01 sec)

注意：前缀索引虽然可以节省空间，但是也可能导致索引选择性不足。

  

复合索引（联合索引）

复合索引指把多个字段组合在一起创建一个索引，也叫联合索引、多列索引。

符合索引核心规则：最左匹配原则

假如索引顺序 (a, b, c)，查询时必须先使用左边前列，索引才会生效：

  • where a=?、where a=? and b=?、where a=? and b=? and c=? 走完整索引

  • where a=? and c=? 只会用到 a 这一段索引

  • where b=?、where b=? and c=?、where c=? 完全不走索引，全表扫描

例如：

-- 创建数据表，且指定两个复合索引
mysql> CREATE TABLE index_demo4 (
    ->     id INT PRIMARY KEY,
    ->     last_name VARCHAR(50),
    ->     first_name VARCHAR(50),
    ->     age INT,
    ->     INDEX idx_name (last_name, first_name), -- 基于 last_name 和 first_name 创建复合索引
    ->     INDEX idx_name_age (last_name, first_name, age) -- 创建三个字段的复合索引
    -> );
Query OK, 0 rows affected (0.01 sec)

复合索引遵循“最左前缀”原则：查询条件必须从索引的最左边开始使用。例如：

-- 可以使用索引 idx_name
SELECT * FROM index_demo4 WHERE last_name = 'Zhang';
SELECT * FROM index_demo4 WHERE last_name = 'Zhang' AND first_name = 'San';

-- 不能使用索引 idx_name（缺少 last_name 条件）
SELECT * FROM index_demo4 WHERE first_name = 'San';

索引的数据结构

MySQL 的 InnoDB 引擎主要使用 B+树索引：

B+树的特点：

  • 所有数据都存储在叶子节点

  • 叶子节点之间有链表连接，方便范围查询

  • 查询、插入、删除的时间复杂度都是 O(log n)

  • 相比二叉树，B+树更矮更宽，减少磁盘 IO

聚簇索引 vs 非聚簇索引：

  • 聚簇索引：叶子节点存储的是完整的数据行。InnoDB 的主键索引就是聚簇索引。

  • 非聚簇索引（二级索引）：叶子节点存储的是主键值。查询时先找到主键，再回表查完整数据。

示例：

-- 假设表结构
mysql> CREATE TABLE users (
    ->     user_id INT PRIMARY KEY,  -- 聚簇索引
    ->     username VARCHAR(50),
    ->     email VARCHAR(100),
    ->     INDEX idx_username (username)  -- 非聚簇索引
    -> );
Query OK, 0 rows affected (0.01 sec)

使用 DBeaver 工具，向上面的 users 表插入 100 万行数据，这里使用存储过程进行插入数据（点击学习MySQL存储过程）：

-- 如果存储过程存在则删除
mysql> DROP PROCEDURE IF EXISTS batch_insert_users;
Query OK, 0 rows affected (0.00 sec)

-- 设置分隔符，以什么符号结束一行语句
mysql> DELIMITER //

-- 开始创建存储过程
mysql> CREATE PROCEDURE batch_insert_users()
    -> BEGIN
    ->     DECLARE batch INT DEFAULT 1000;
    ->     DECLARE total INT DEFAULT 1000000;
    ->     DECLARE curr INT DEFAULT 1;
    ->
    ->     ALTER TABLE users DISABLE KEYS;
    ->     SET autocommit = 0;
    ->     SET unique_checks = 0;
    ->
    ->     WHILE curr <= total DO
    ->         INSERT INTO users(user_id, username, email)
    ->         SELECT
    ->             curr + (a.t1 * 100 + b.t2 * 10 + c.t3) AS user_id,
    ->             CONCAT('user_', curr + (a.t1 * 100 + b.t2 * 10 + c.t3), '_', FLOOR(RAND() * 999)),
    ->             CONCAT('test_', curr + (a.t1 * 100 + b.t2 * 10 + c.t3), '@demo.com')
    ->         FROM (SELECT 0 t1 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
    ->               UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) a,
    ->              (SELECT 0 t2 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
    ->               UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) b,
    ->              (SELECT 0 t3 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
    ->               UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) c
    ->         LIMIT batch;
    ->
    ->         SET curr = curr + batch;
    ->         COMMIT;
    ->     END WHILE;
    ->
    ->     ALTER TABLE users ENABLE KEYS;
    ->     SET autocommit = 1;
    ->     SET unique_checks = 1;
    -> END //
Query OK, 0 rows affected (0.00 sec)

-- 将分隔符还原
mysql> DELIMITER ;

-- 调用存储过程，模拟100万数据
mysql> CALL batch_insert_users();
Query OK, 0 rows affected (11.10 sec)

-- 查看数据
mysql> select count(*) from users;
+----------+
| count(*) |
+----------+
|  1000000 |
+----------+
1 row in set (0.10 sec)

数据准备完成后，执行下面查询，看看它们之间的效率：

（1）使用主键，直接定位数据（快），如下：

SELECT * FROM users WHERE user_id = 899998;

使用 DBeaver 执行上面 SQL，如下图：

image.png

上图可知，耗时 0.001s。

（2）使用二级索引，先查索引找到user_id，再回表查完整数据（稍慢），如下：

SELECT * FROM users WHERE username = 'user_899998_828';

如下图：

image.png

耗时 0.002s。

（3）覆盖索引，只需要 username 和 user_id，不需要回表（快），例如：

SELECT user_id, username FROM users WHERE username = 'user_899998_828';

如下图：

image.png

耗时 0.001s。上面仅仅做了一个非常简单的验证，你可以写代码测试，使用不同的值查询n次，求平均数。

到这里我们已经学会了如何创建索引，以及索引的分类，下面将介绍索引设计的基本原则：

索引设计原则

原则1：在WHERE条件、JOIN条件、ORDER BY字段上建索引

查询中经常出现的过滤条件、关联条件、排序字段，是索引最优落地场景，建索引能避免全表扫描、文件排序（filesort），大幅提升 SQL 执行速度。例如：

-- 常用的查询条件字段应该建索引
SELECT * FROM users WHERE email = 'test@example.com';  -- email建索引
SELECT * FROM orders WHERE user_id = 1 ORDER BY created_at DESC;  -- user_id和created_at建索引

原则2：选择性高的字段适合建索引

选择性 = 唯一值数量 ÷ 总行数，取值范围 0~1。

数值越接近 1，代表字段区分度越高，索引效果越好。

举例：

  • user_id 主键：每条记录值都不同，选择性 = 1，索引最优；

  • username、手机号、邮箱：几乎无重复，选择性极高；

  • sex 性别只有男 / 女，选择性 ≈ 0.5；

  • status 状态只有 0/1，选择性极低；

为什么低选择性字段不适合单独建索引？

比如 WHERE sex='男'，表里一半数据都是男生，MySQL 优化器会判断：走索引还要回表，开销比直接全表扫描更大，索引直接失效，等于白建索引，还额外占用磁盘、拖慢写入。

总起接起来就是字段唯一值越多、区分度越高，索引收益越大；重复值极多的字段单独建索引基本无效。

原则3：避免过多索引

每个额外的索引都会增加写入开销。一般建议：

  • 单表索引数不超过5个

  • 单个索引字段数不超过5个

因为插入和更新数据会维护索引，增加了额外开销。

原则4：优先使用复合索引

一条 SQL 同时用到多个查询、排序条件时，创建一个多字段复合索引，替代多个独立单字段索引，性能更好、索引数量更少。

优势如下：

  • 满足最左匹配，一个索引同时覆盖 WHERE + JOIN + ORDER BY；

  • 减少索引总量，降低插入 / 更新 / 删除时维护索引的开销；

  • 更容易实现覆盖索引，避免回表查询。

例如：

-- 如果经常有这些查询
SELECT * FROM users WHERE last_name = 'Zhang';
SELECT * FROM users WHERE last_name = 'Zhang' AND first_name = 'San';

-- 建一个复合索引即可，不需要两个单独索引
CREATE INDEX idx_name ON users(last_name, first_name);

原则5：避免对经常修改的字段建索引

MySQL 索引是独立磁盘结构（B + 树），每当字段执行 UPDATE 修改值时：

  • 要定位这条记录对应的索引节点；

  • 删除旧索引条目、插入新索引条目；

  • 维护 B + 树平衡，可能产生页分裂，大量 IO 开销；

字段更新越频繁，索引维护成本越高，会严重拖慢写入性能。

查看和分析索引

（1）查看表的索引

mysql> SHOW INDEX FROM users \G
*************************** 1. row ***************************
        Table: users
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: user_id
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
      Visible: YES
   Expression: NULL
*************************** 2. row ***************************
        Table: users
   Non_unique: 1
     Key_name: idx_username
 Seq_in_index: 1
  Column_name: username
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment:
Index_comment:
      Visible: YES
   Expression: NULL
2 rows in set (0.00 sec)

注意：\G 是 mysql 客户端中格式化命令

（2）查看查询是否使用索引

-- 基于邮箱查询没有索引，全表扫描
mysql> EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | users | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 1004409 |    10.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
1 row in set, 1 warning (0.02 sec)

-- 基于用户姓名查询，有普通索引
mysql> EXPLAIN SELECT * FROM users WHERE username = 'user_899998_828';
+----+-------------+-------+------------+------+---------------+--------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key          | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+--------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | users | NULL       | ref  | idx_username  | idx_username | 203     | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+--------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

-- 基于ID查询
mysql> EXPLAIN SELECT * FROM users WHERE user_id=899998;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | users | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

性能排序：主键索引 (const) > 普通二级索引 (ref) > 无索引全表扫描 (ALL)；

关键字段说明：

  • type 字段是性能核心判断依据：const > ref > ALL；

  • key 不为 NULL 代表命中索引；为 NULL 代表全表扫描；

  • rows 预估扫描行数越小，查询越快；

（3）分析查询性能

-- 基于邮箱查询没有索引，全表扫描
mysql> EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'test@example.com' \G
*************************** 1. row ***************************
EXPLAIN: -> Filter: (users.email = 'test@example.com')  (cost=101939 rows=100441) (actual time=223..223 rows=0 loops=1)
    -> Table scan on users  (cost=101939 rows=1e+6) (actual time=0.062..148 rows=1e+6 loops=1)

1 row in set (0.22 sec)

说明：

  • Table scan = 全表扫描，逐条读取整张表数据；

  • 实际遍历整整 100 万行，IO 开销巨大；

  • 总执行时间 223ms；

-- 基于用户姓名查询，有普通索引
mysql> EXPLAIN ANALYZE SELECT * FROM users WHERE username = 'user_899998_828' \G
*************************** 1. row ***************************
EXPLAIN: -> Index lookup on users using idx_username (username='user_899998_828')  (cost=0.35 rows=1) (actual time=0.0258..0.0278 rows=1 loops=1)

1 row in set (0.00 sec)

说明：

  • Index lookup 索引快速定位，不用扫全表；

  • 直接通过索引找到匹配行，仅读取 1 条数据；

  • 耗时 0.0278ms，速度远超全表扫描；

-- 基于ID查询
mysql> EXPLAIN ANALYZE SELECT * FROM users WHERE user_id=899998 \G
*************************** 1. row ***************************
EXPLAIN: -> Rows fetched before execution  (cost=0..0 rows=1) (actual time=79e-6..137e-6 rows=1 loops=1)

1 row in set (0.00 sec)

说明：

  • 主键是聚簇索引，等值查询优化到极致；

  • MySQL 可直接通过主键哈希定位数据，甚至预读取；

  • 耗时微秒级别，三者中性能天花板，几乎无延迟；

EXPLAIN 的输出重点关注：

  • type：访问类型，从好到差：system > const > eq_ref > ref > range > index > ALL

  • key：实际使用的索引

  • rows：估计需要扫描的行数

  • Extra：额外信息，如"Using index"表示覆盖索引

更多知识请阅读后续章节……谢谢！！！

  

上一章：MySQL 约束的使用
说说我的看法
全部评论（）
没有评论
关于
本网站专注于 Java、数据库（MySQL、Oracle）、Linux、软件架构及大数据等多领域技术知识分享。涵盖丰富的原创与精选技术文章，助力技术传播与交流。无论是技术新手渴望入门，还是资深开发者寻求进阶，这里都能为您提供深度见解与实用经验，让复杂编码变得轻松易懂，携手共赴技术提升新高度。如有侵权，请来信告知：hxstrive@outlook.com
链接
其他应用
公众号
©2018-2020 hxstrive.com 版权所有 ICP证：蜀ICP备14031461号-2   川公网安备51010802032098