MySQL 索引特性：B+ 树索引、主键/唯一/联合索引与使用

先问你一个问题：如果你在一张有 800 万行数据的表里，想查某一行的某列，你觉得它快吗？

很多人下意识觉得"挺快，反正 MySQL 很强大"。可真把 SQL 写出来放到一张 800 万行的表上跑，结果可能让你吓一跳——一条简单的按主键外的列过滤的查询，可能卡在那里转上好几秒。是不是觉得有点反直觉？数据库不是号称"海量数据管理能力"的吗？

问题的根源，在于数据到底是怎么被"翻"出来的。今天这堂课我们就围绕一个核心话题展开——索引（Index）。我们会从"为什么需要索引"讲起，深入到磁盘的原理、B+ 树索引的构造，再讲到主键/唯一/联合等各类索引的创建与删除，最后把"最左前缀""覆盖索引""索引失效"以及看执行计划的 EXPLAIN 一次讲透。

这堂课是 MySQL 里含金量最高、面试最常考、也最容易踩坑的一块内容，值得你静下心来一行一行跟着读完。

你应该有的知识储备

在开始之前，我默认你已经具备以下基础，遇到不熟的可以翻回之前的章节：

• 基本的 SQL 写读能力：CREATE TABLE、INSERT、SELECT ... WHERE ...、ALTER TABLE 这些最常用的语句。
• 主键的概念：每张表里能唯一标识一行记录的列，比如学生表里的学号。
• 数据库和表的基本认识：数据落在磁盘上、通过 SQL 与服务器交互这些常识。
• 一点点数据结构概念：树、节点、链表、哈希这些名词你不需要精通，只要"听过"就行，我会用大白话重新解释。

如果你带着这些基础进来，上面大部分你已经会了；缺的个别点，恰好说明接下来这堂课会对你有大用。

没有索引，会有什么问题

先讲结论：索引（Index）是数据库中一种独立存储的、辅助快速检索的数据结构。它就像书的目录，不把书的内容抄一遍，却能让"翻到某一页"的速度从天翻地覆。 一句话——它是 MySQL 里"物美价廉"的性能优化手段，业界常说的"加索引就能救命"就是这个意思。

为什么说它"物美价廉"？因为它有三个"不用"：

• 不用加内存：数据库服务器的内存不用动；
• 不用改程序：应用层代码一行都不用改；
• 不用调 SQL：SQL 语句原样保留，只是让优化器有机会走索引。

你只要正确执行一条 CREATE INDEX，查询速度就可能提高成百上千倍。听起来是不是像白捡的便宜？

但是——天下没有免费的午餐。前面说的是读（查询）变快，代价却落在了写（增删改）上：插入、更新、删除时，MySQL 除了维护数据文件，还要同步维护索引结构。写操作变多了，自然要做更多次磁盘 IO。更现实的情况是，你在一个列上建的索引，本身也要占磁盘空间。所以索引的价值，在于"提高海量数据下的检索速度"这个 读场景上投最少的钱、赚最大的效率；而在写场景里它是要付出额外开销的。

那索引到底有哪些常见类型？我们从最简单的一版说起，后面每一类都会单独用一节详细展开：

先别急着把所有类型记下来，我们先用一个能真实复现"为什么慢"的案例，让你亲眼看到没索引时的问题。

案例：一张 800 万行的大表

为了演示"没有索引有多痛"，第一个计划是造一张海量数据表。数据太少测不出感觉，我们来造 800 万条记录。直接手动插 800 万条不现实，所以我们用 MySQL 的**存储过程（Stored Procedure）**来批量造数。这块代码你现在不需要全部理解，先"能跑、能出数据"就好，重头戏在后面的 select 与 alter：

-- 建一张模拟"员工表" EMP，字段模拟经典的员工信息
create table EMP(
    empno int primary key,           -- 员工编号，设为 int，作为主键
    ename varchar(20),               -- 姓名，varchar 变长字符串
    job varchar(20),                 -- 岗位
    mgr int,                         -- 上级编号
    hiredate date,                   -- 入职日期
    sal decimal(7,2),                -- 工资，定点小数
    comm decimal(7,2),               -- 提成
    deptno int                       -- 部门编号
) engine=InnoDB default charset=utf8;  -- 指定 InnoDB 引擎，字符集 utf8

-- 把 SQL 的语句分隔符临时改成 $$。因为存储过程函数体内部要用分号分隔多句，
-- 如果把分隔符定为 $$，里面的分号就不会被误认为"整条语句结束"
delimiter $$
 
-- 定义一个函数 rand_string(n)：返回 n 个随机字母组成的字符串
-- n 表示要生成多少个字符
create function rand_string(n int) returns varchar(255)
begin
    declare chars_str varchar(100) default
        'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';  -- 52 个大小写字母
    declare return_str varchar(255) default '';  -- 先置空返回值
    declare i int default 0;                     -- 循环计数器
    while i < n do                               -- 循环 n 次，生成 n 个字符
        set return_str = concat(return_str,                                    -- 把取到的字符拼到结果后面
            substring(chars_str, floor(1 + rand() * 52), 1));                 -- 每次从 52 个字母中随机取 1 个
        set i = i + 1;                           -- 计数器自增，推进循环
    end while;
    return return_str;                           -- 返回最终拼出来的随机字符串
end $$
 
-- 定义一个函数 rand_num()：返回 10 到 510 之间的一个随机整数
create function rand_num() returns int
begin
    declare i int default 0;                     -- 声明局部变量
    set i = floor(10 + rand() * 500);            -- rand()*500 是 0~500 的小数，加 10 得到 10~510，floor 取整
    return i;                                    -- 把随机整数返回出去
end $$

-- 把分隔符改回分号，这样后面写普通 SQL 就正常了
delimiter ;
 
-- 定义一个存储过程 insert_emp：从 start 开始，往 EMP 表里批量插入 max_num 条记录
delimiter $$
create procedure insert_emp(in start int, in max_num int)
begin
    declare i int default 0;                     -- 计数器
    set autocommit = 0;                          -- 关闭自动提交，攒一起提交以提速
    repeat                                       -- 开始循环
        set i = i + 1;                           -- 计数器自增
        insert into EMP values (
            start + i,                           -- 主键：起始值 + 循环计数，保证不重复
            rand_string(6),                      -- 姓名：6 个随机字母
            'SALESMAN',                          -- 岗位固定为 SALESMAN
            0001,                                -- 上级编号固定
            curdate(),                           -- 入职日期取当天
            2000,                                -- 工资固定 2000
            400,                                 -- 提成固定 400
            rand_num()                           -- 部门号：随机数
        );
    until i = max_num end repeat;                -- 直到插入满 max_num 条为止
    commit;                                      -- 一次性提交所有数据
end $$
delimiter ;

-- 真正执行存储过程：从 100001 号员工开始，插入 800 万条记录
call insert_emp(100001, 8000000);

等这 800 万条记录插完，我们来看一个真实的"剧痛时刻"。注意：EMP 表的 empno 是主键，但 ename 等其他列上我们并没有建任何索引。 现在按姓名这种玩意去查，会怎样呢：

-- 按姓名精确查一个员工
-- 因为 ename 上没有索引，MySQL 只能把整张表从第一行扫到最后一行，
-- 挨个比对是否符合条件，这叫"全表扫描"
select * from EMP where ename = 'abcDEF';

无损一句：如果这张表没有索引，查一条记录要做的，是把 800 万行从头到尾逐行比对一遍。哪怕你是在自己的本机、一个人操作，这种查询都要跑上好几秒钟；而真实项目里如果放到公网，1000 个人同时发这种查询，服务器的磁盘和 CPU 会瞬间被打满，很可能直接"死机"。

那怎么解决？做法就是给 ename 建一个索引：

-- 给 ename 列建一个普通索引，之后按 ename 查询就能走索引快查
alter table EMP add index(ename);

建完之后再执行同一条查询，你会惊讶地发现时间从一个数量级降到近乎瞬时。同样一条 SQL，什么都没改，只因多了个索引，效果天差地别——这就是索引的价值。

【思考题】上面案例里，为什么按主键 empno 查询会快、而按 ename 查询很慢？

不过，光知道"加个索引就快了"还不够。为了真正理解索引为什么能提速、以及它为什么是"目录"而非"数据本身"，我们必须先低下头看看磁盘这个硬件。数据在这个世界上的归宿，最终是磁盘。

认识磁盘：数据到底住在哪里

MySQL 给用户提供存储服务，而"存储"的本质，是把数据写到磁盘（Disk）这个外设上。磁盘是一种机械装置，它通过马达带动盘片高速旋转、磁头在盘面上移动来读写数据。相比于计算机里的 CPU、内存这些电子元件，磁盘是慢得多的角色——因为它靠"转"和"移"，是物理运动。

一台磁盘的内部，就像一个唱片机叠在一起：

• 盘片（Platter）：磁盘里一片片圆形的、用于记录数据的金属/玻璃圆盘；
• 扇区（Sector）：每个盘片被划分成一个个最小的存储格子，这就是我们说的一格一格，磁盘读写的基本单位（通常 512 字节，最新技术也有 4096 字节的，我们这里按 512 字节的经典模型理解）；
• 磁道（Track）：盘片上一个个同心圆；
• 柱面（Cylinder）：多张盘片叠起来，所有盘片"同一半径"的磁道合在一起，就构成了一个"柱面"；
• 磁头（Head）：每个盘面都有一个磁头，负责读写该盘面的数据，磁头和盘面是一一对应的。

所以要在一张磁盘上定位一个最基本的数据块，我们只需要知道它在哪个柱面（Cylinder）、哪个磁头（Head）、哪个扇区（Sector）——这种定位方式叫 CHS。不过操作系统用得更多的一套叫 LBA（Logical Block Addressing，逻辑块寻址）：它把整块磁盘的扇区按顺序编成一个线性地址，你可以把它想象成"虚拟地址与物理地址"的关系，系统先把 LBA 地址交给磁盘控制器，由控制器转成 CHS 去真正读数据。这些硬件细节我们不需要深究，知道存在这么回事、让逻辑自洽就够了。

数据库文件，本质上就是存放在磁盘盘片上的一个个文件。 你可以在 MySQL 的数据目录（通常是 /var/lib/mysql）里看到它们：

# 列出 MySQL 数据目录下的内容：一个目录就是一个数据库，一个表对应若干文件
[root@VM-0-3-centos mysql]# ls -l /var/lib/mysql
# 输出中能看到很多以数据库名命名的目录，以及 ibdata1、ib_logfile0 这类全局文件

它的含义是：我们创建的一个数据库，就是磁盘上一个目录；数据库里的一张表，就是该目录下的一个或几个文件。既然数据在磁盘上、而磁盘读写又慢，那么"如何高效地和磁盘打交道"就成了 MySQL 最核心的课题之一。

MySQL 与磁盘交互的基本单位：Page

聊到交互，有个问题必须想清楚：MySQL 和磁盘之间，到底一次传多少数据？

如果一次只传一个扇区（512 字节），那逻辑上最简单，但实际效率极低——因为单位太小，读取同样多的数据就需要发起很多次磁盘访问，每次都伴随着磁头的物理移动。所以文件系统在读磁盘时，从来不是按扇区，而是按更粗的**块（Block）**为单位，通常是 4KB。

而 MySQL（以最常用的 InnoDB 存储引擎为例）的 IO 单位还要更大——16KB。MySQL 把一个基本数据单元叫 page（页，注意和操作系统里的 page 页区分）。

你可以用下面这句查看当前 MySQL 的页大小：

-- 查看 InnoDB 的页大小全局变量
show global status like 'innodb_page_size';

结果里 innodb_page_size 的值是 16384，也就是 16 * 1024 = 16384 字节，正好 16KB。

那为什么要用"一页一页"这么粗的粒度去交互，而不是"用多少加载多少"呢？关键在一个词：局部性原理（Principle of Locality）。它说的是：某个数据刚被访问后，它旁边相邻的数据接下来大概率也会被访问到。所以一次性把一整页（16KB，能装下很多条记录）加载进内存，虽然单次搬的数据多了，但很可能你接下来要找的记录就在同一页里——这样用一次磁盘 IO 的代价，换来了多次内存查找的命中，整体 IO 次数大幅减少。

MySQL 在内存中专门申请了一块大空间来和磁盘交互、缓存数据，叫 Buffer Pool（缓冲池），它本质上就是"一大块内存，用来和磁盘数据做 IO 交互的缓存区"。数据不是每次查都去磁盘，而是优先在 Buffer Pool 里找。

于是我们建立了一个关键"共识"：

• MySQL 的数据文件，以 page 为单位存放在磁盘上；
• MySQL 的增删改查，都要通过计算找到目标数据的位置，再把对应的 page 加载进内存（Buffer Pool）操作，最后按策略刷回磁盘；
• 计算需要 CPU 参与，而 CPU 只能访问内存，所以**数据一定是"磁盘存一份、内存也有一份"**的状态；
• 要提升效率，最核心的是减少磁盘与内存之间的 IO 次数，而不是纠结单次 IO 的数据量。

理解了这个"共识"，我们就站在了理解索引的门口。下一个问题是：这些 16KB 的 page，在内存/磁盘里是如何被 MySQL 组织起来、从而能快速查找的？这就要讲到 B+ 树了。

从一页到一棵树：索引的底层结构是 B+ 树

为了搞清索引为何快，我们先做个"裸眼实验"：手动建一张小表，看看主键索引到底给数据做了什么。

先看：插入后数据居然是"有序"的

-- 建张测试表 user，id 作为主键
-- 注意：有了主键，InnoDB 就会默认为主键生成一个主键索引
create table if not exists user(
    id int primary key,     -- 一定加主键，这样 InnoDB 才会生成默认的主键索引
    age int not null,       -- 年龄
    name varchar(16) not null  -- 姓名
) engine=InnoDB default charset=utf8;  -- InnoDB 引擎（默认）

-- 注意：以下插入并不按主键大小顺序插入，而是乱序插入
insert into user(id, age, name) values(3, 18, '杨过');   -- 先插 id=3
insert into user(id, age, name) values(4, 16, '小龙女'); -- 再插 id=4
insert into user(id, age, name) values(2, 26, '黄蓉');   -- 再插 id=2
insert into user(id, age, name) values(5, 36, '郭靖');   -- 再插 id=5
insert into user(id, age, name) values(1, 56, '欧阳锋'); -- 最后插 id=1

插完之后查询，一个很有意思的现象出现了：

-- 查看全部数据
select * from user;
-- 结果竟然默认按 id 从小到大排好了：
--  1 | 56 | 欧阳锋
--  2 | 26 | 黄蓉
--  3 | 18 | 杨过
--  4 | 16 | 小龙女
--  5 | 36 | 郭靖

咦？我明明乱序插入，查出来却是有序的。这是谁干的？答案是 InnoDB 的**主键索引（聚簇索引）**悄悄做的。它为了让后续查询能走"目录"快速定位，会在插入时就按主键把数据整理有序。插入时排序的目的，正是为了优化查询的效率。

单页内部：页内"目录"

现在只有 5 行数据，很可能它们都在同一个 16KB 的 page 里。就算在一页里，数据内部是一种链表结构（用 prev/next 首尾相连），查找一条记录时如果逐个往后比，那本质上还是线性查找，慢。

怎么提速？像书一样加"目录"。打个比方：读《谭浩强C程序设计》，要找"指针"一章，有两种做法——一是从第 1 页往后一页页翻到目标；二是看书的目录，发现指针在第 234 页，直接翻过去。目录是"空间换时间"：多占用了一些纸张（空间），却大大提高了查找速度。

在单个 page 里，MySQL 也引入了类似的"页内目录"：把 page 里每几条记录的第一个键值（这里是 id）记成一份索引条目。于是查找 id=4 时，不一定要从头线性数 4 个，而是先看目录大致定位到一段，再在小范围内精准找到目标。这就是"为何 InnoDB 会自动给数据排序"的重要理由之一——有序，才有引入目录的前提；有了目录，查找才不至于一遍遍线性扫。

多个 Page：页与页之间也要目录

一个 page 只有 16KB，装不下无限数据。数据一多，自然要有多个 page。多个 page 彼此用指针连成双向链表，这就是初始状态——但这里有个尴尬：页之间依然是靠 Linear（线性）遍历的，也就是说要沿着一页一页的内存去找，每跳一页就可能要一次磁盘 IO 把下一页加载进内存，效率还是很低。

那怎么办？思路和"给数据加目录"一脉相承——给 page 也加目录。我们用一个目录项去指向某一张数据页，这个目录项里存的是"它指向的那一页里最小的键值"，结构大致是"键值 + 指向页的指针"。

如果你认真想，会发现：

• 页内目录，管理的最小单位是行；
• 页间目录，管理的最小单位是页。

这个"存了各数据页最小键值和指针"的页，叫目录页（index page）。它的本质也是一个普通的 16KB page，只不过普通页里存的是用户数据，而目录页里存的是普通页的地址（最小键值 + 指针）。查找数据时，先在目录页里用"比较大小"定位该访问哪一张数据页，顺着指针跳过去，就大大减少了 IO 次数。

可问题又来了：数据量继续涨，目录页也会增长，难道要在目录页之间也做线性遍历？那就再往上加一层目录页。如此一层套一层，最终你会得到一个"上层只存键值和指针、下层才存真正数据，从根到叶子逐层收敛"的结构。你大概已经猜到了——这玩意就是传说中的 B+ 树！ 到这一步，InnoDB 已经悄悄帮我们的 user 表构建完了主键索引。

我用 ASCII 画个简化的 B+ 树样子，帮你建立直观印象：

                        [ 目录层/根：20 ]          ← 一层（也可能多层），只存键值+指针
                       /                    \
              [ 目录：5 | 12 ]        [ 目录：20 | 25 ]
              /      |       \         /      |       \
          数据页   数据页     数据页  数据页  数据页    数据页
          [id:1..][id:6..]  [id:13..] [id:20..] [id:23..] [id:28..]
             |       |          |         |          |         |
          叶子节点彼此用指针首尾相连(便于范围查找)

归纳 B+ 树的关键特点：

• 目录页（内部/非叶子节点）只放每个下级页的最小键值 + 指针，不放用户数据；
• 真正的用户数据只存在叶子节点（数据页）；
• 叶子节点彼此相连（双向链表），方便范围查找；
• 查找时自顶向下，只需要加载部分目录页到内存就能完成整个查找，大大减少磁盘 IO 次数。

为什么 B+ 树，而不是别的数据结构？

你可能会问：世间数据结构那么多，为什么 InnoDB 偏偏选 B+ 树？官方教程里提到过，我们逐个分析一下候选者为什么"落选"：

• 链表：增删快、查询慢，查找是线性遍历，大表下直接出局；
• 二叉搜索树：有退化的风险——如果插入顺序恰好接近有序，树会退化成一条"链表"，变成线性查找；
• AVL / 红黑树：平衡或近似平衡，但它是二叉树，相比多阶的 B+ 树，树整体更高。大家都是从根往下找，树越矮，访问的层数越少，也就是与磁盘交互的 IO 次数越少。虽然二叉平衡树很秀，但"多阶"的 B+ 树更秀；
• Hash（哈希）：MySQL 确实支持 HASH 索引，但 InnoDB 和 MyISAM 这两个主流引擎并不支持它坐堂索引。哈希查找虽然最快是 O(1)，但面对范围查找（比如 where age >= 20 and age <= 30）就无能为力了，而且哈希表无序、无法做排序优化等，实际难以胜任通用场景；
• B 树：最值得和 B+ 树掰手腕的就是它。两者最大区别在于：
  • B 树：每个节点既存键值，又存（子页/数据的）指针，整棵树的节点都带数据；
  • B+ 树：只有叶子节点有数据，其他目录页只有键值和指针；同时 B+ 树的叶子节点全部相连。

为什么选择 B+ 树而放弃 B 树？核心有两点：

1. 节点不存 data，一个节点能容纳更多 key，让树更矮，从而减少 IO 次数：因为 B+ 树的非叶子节点只存键值和指针（不存大字段数据），每个 16KB 的页能放下的"目录项"就更多，操作系统的扇区/块单位相同的情况下，层数更少，路径更短；
2. 叶子节点相连，更便于范围查找：B+ 树叶子连成链表后，只要定位到区间的一端，就能顺着链表一路扫过去完成范围查询；B 树的叶子互不相连，范围查找要反复上下回跳。

结论一句话：InnoDB 用 B+ 树做索引，本质是"用一定的空间开销，换来层数更矮、IO 更少、范围查找更顺的高效检索"。

【思考题】为什么说 B+ 树层数越矮，查询性能越好？哈希索引为什么不适合做数据表的默认索引？

搞清楚 B+ 树这个"标准答案"后，还有一个绕不开的重要划分：聚簇索引与非聚簇索引，它直接关系到"数据到底存在哪"以及"回表"这个高频面试点。

聚簇索引 VS 非聚簇索引

同样是 B+ 树，不同存储引擎处理"数据放哪"的方式却大相径庭。先直观感受一下：我们用 InnoDB 和 MyISAM 各建一张表，然后去 MySQL 的数据目录看它们各自生成的文件。

-- 终端A：建一个 MyISAM 引擎的表
create database myisam_test;          -- 建库
use myisam_test;                      -- 使用该库
create table mtest(
    id int primary key,               -- 主键
    name varchar(11) not null         -- 姓名
) engine=MyISAM;                      -- 指定 MyISAM 存储引擎

# 终端B：在 MySQL 数据目录里查看 myisam_test 下的文件
[root@VM-0-3-centos mysql]# ls -l /var/lib/mysql/myisam_test/
# mtest.frm   → 表结构数据
# mtest.MYD   → 该表的数据（MYData），当前没数据所以大小是 0
# mtest.MYI   → 该表的主键索引数据（MYIndex）

再看 InnoDB 的表：

-- 终端A：建一个 InnoDB 引擎的表
create database innodb_test;          -- 建库
use innodb_test;                      -- 使用该库
create table itest(
    id int primary key,               -- 主键
    name varchar(11) not null         -- 姓名
) engine=InnoDB;                      -- 指定 InnoDB 存储引擎

# 终端B：查看 innodb_test 下的文件
[root@VM-0-3-centos mysql]# ls -l /var/lib/mysql/innodb_test/
# itest.frm  → 表结构数据
# itest.ibd  → 表空间文件，包含"主键索引 + 用户数据"，哪怕一行数据都没有，
#              它也不为 0，因为里面已经存了主键索引的结构

看出名堂没有？同一个概念，两种引擎的"归属"完全不同：

• MyISAM：数据（.MYD）和索引（.MYI）分开存放。它的主键索引也是一个 B+ 树，但叶子节点不再存用户数据，而是存"数据记录在磁盘上的地址"。这种"用户数据与索引数据分离"的方案就是非聚簇索引（Non-Clustered Index）；
• InnoDB：索引和数据放在一起。它的主键索引（聚簇索引）的叶子节点就是数据本身，一行一行的用户数据直接存储在叶子节点里。这种"用户数据与索引数据在一起"的方案就是聚簇索引（Clustered Index）。

在 InnoDB 里，除了主键索引，我们还可能按其他列建立辅助索引（也叫普通索引/次级索引）。关键区别来了：

• MyISAM 的普通索引和主键索引没差别，本质都是"叶子存记录的地址"，无非主键不能重复、普通列可重复；
• InnoDB 的普通索引（非主键索引）叶子节点并不存一行的全部数据，只存对应的主键值（key）。所以通过普通索引找到目标，实际上要两趟索引**：第一趟，检索普通索引获得目标行的主键；第二趟，再用这个主键到主键索引（聚簇索引）里检索，才取出整条完整的记录。这个过程，就叫回表查询（Table Lookup，简称回表）。

为什么 InnoDB 普通索引的叶子不直接附上完整数据？原因很简单——太浪费空间了。如果每个辅助索引叶子都拷一份整行数据，那建多少索引就复制多少份全表数据，磁盘和内存都受不了。所以 InnoDB 的普遍设计是：辅助索引只存聚簇索引的键（主键），需要整行再回表。

-- 演示回表的整体流程：先在非主键列上建普通索引
-- 假如 user 表里有 id 主键，以及 name 列
alter table user add index idx_name(name);
-- 查询 where name = '黄蓉'
-- 优化器会先走 idx_name 这个辅助索引找到 id=2，再拿 id=2 去主键索引（聚簇索引）里取整行
select * from user where name = '黄蓉';

到这里，我们把索引的"底料"（B+ 树、聚簇/非聚簇、回表）备齐了。接下来进入实操环节：如何创建、查询、删除各类索引，以及它们的特点和适用边界。这部分是日常工作中最常碰到的。

主键索引的创建与特点

主键索引（Primary Key）：以表的主键列建立的索引，是 InnoDB 里唯一的聚簇索引。三种创建方式：

-- 方式一：建表时，直接在字段后加 primary key
create table user1(id int primary key, name varchar(30));
-- 等价于告诉 InnoDB：这一列做主键，并为主键生成聚簇索引
 
-- 方式二：建表时，在表定义的最后指定某列/某几列为主键（可用于复合主键）
create table user2(id int, name varchar(30), primary key(id));
 
-- 方式三：表建好后，用 alter table 追加主键
create table user3(id int, name varchar(30));  -- 建表时没主键
alter table user3 add primary key(id);         -- 事后追加主键

主键索引的特点，务必烂熟于心：

• 一张表最多只能有一个主键（可以是"复合主键"，即由多列共同组成，但整体仍算一个主键）；
• 主键索引的效率高：因为主键值唯一，B+ 树查找时命中路径唯一，不会扫描多条候选；
• 主键列的值不能为 NULL、不能重复；
• 主键列的字段类型，实战中基本用 int（或 bigint），因为整数比较快、占空间小、方便自增。

唯一索引的创建与特点

唯一索引（Unique Index）：保证某列不重复的索引。和主键相比，最容易被初学绕晕的区别是——主键要求非空，而唯一索引允许 NULL（MySQL 中 NULL 与 NULL 不视为相同，允许多个 NULL）。三种创建方式：

-- 方式一：建表时，在某列后面直接指定 unique
create table user4(id int primary key, name varchar(30) unique);
-- 说明：name 列建唯一索引，且允许 NULL
 
-- 方式二：建表时，在表定义最后指定某列/某几列为 unique
create table user5(id int primary key, name varchar(30), unique(name));
 
-- 方式三：建表后，用 alter table 追加唯一索引
create table user6(id int primary key, name varchar(30));
alter table user6 add unique(name);

唯一索引的特点：

• 一张表可以有多个唯一索引（这是和主键最大的区别之一：主键只能一个，唯一可多个）；
• 查询效率高：同主键类似，唯一值让检索路径干脆；
• 在某列上建唯一索引，必须保证该列不能有重复数据，否则建索引会失败（若已有重复，需要先清洗数据）；
• 如果一个唯一索引列又同时指定了 NOT NULL，那就等价于主键索引（唯一 + 非空 ≈ 主键）。

普通索引的创建与特点

普通索引（Index）：不加唯一约束、允许重复值出现的索引，是开发中使用最广泛的一类。三种创建方式：

-- 方式一：建表时，在表定义最后指定某列为索引
create table user8(id int primary key, name varchar(20), email varchar(30), index(name));
 
-- 方式二：建表后，用 alter table 给某列加普通索引
create table user9(id int primary key, name varchar(20), email varchar(30));
alter table user9 add index(name);
 
-- 方式三：用 create index 语句创建，并可以为索引命名
create table user10(id int primary key, name varchar(20), email varchar(30));
create index idx_name on user10(name);   -- 建一个名为 idx_name 的普通索引

普通索引的特点：

• 一张表可以有多个普通索引；
• 实际开发中用得最多；
• 如果某列需要建索引，但该列的数据可能重复，就应该用普通索引（而不是 unique 或主键）。

全文索引的创建与特点

全文索引（FullText Index）：专门用于对大量文本字段做"关键词"检索的索引，它解决的是中文/英文的"全文检索"问题，而不是模糊 Like。

下面是一个标准例子——对文章表的 title 和 body 建全文索引。原版资料里注明过"要求引擎是 MyISAM"，这里需要更正一个关键点：自 MySQL 5.6 起，InnoDB 引擎也已经支持 FULLTEXT，所以现代版本建 InnoDB 表的全文索引完全没问题（下文会注明）。

-- 建一张文章表，并给 title、body 两个列建全文索引
create table articles(
    id int unsigned auto_increment not null primary key,  -- 自增主键，无符号 int
    title varchar(200),            -- 标题
    body  text,                    -- 正文（文本）
    fulltext(title, body)          -- 声明 (title,body) 为全文索引
);

-- 插入几条测试文章
insert into articles(title, body) values
('MySQL Tutorial','DBMS stands for DataBase ...'),
('How To Use MySQL Well','After you went through a ...'),
('Optimizing MySQL','In this tutorial we will show ...'),
('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
('MySQL vs. YourSQL','In the following database comparison ...'),
('MySQL Security','When configured properly, MySQL ...');

先看清一个"坑"：使用 like '%keyword%' 是无法发挥全文索引作用的，即使它真能查出数据，走的也只是全表扫描：

-- 这种写法能查出数据，但 NOT 使用全文索引，而是全表扫描
select * from articles where body like '%database%';

真正的全文索引，要用专门的 MATCH ... AGAINST 语法：

-- 这才是全文索引的用法：在 (title,body) 上匹配关键词 'database'
select * from articles
where match(title, body) against('database');

结果会反直觉地返回相关行（MySQL 全文检索会计算相关性并允许排序等）。两个都很重要，这里关于"中文全文索引"要额外提示一笔：原生 MyISAM 的 FULLTEXT 默认只支持英文，对中文需要专门的优化方案（如 sphinx/coreseek 或改用支持中文分词的其他全文方案）；而现代 InnoDB 的 FULLTEXT 在配合 ngram 全文解析器（MySQL 5.7.6+ 内置 ngram parser，可服务于中文）时也能对中文做全文检索。这块属于进阶内容，理解"有这条能力、有关键词 parser 之分"即可。

【思考题】既然能用 like '%xxx%' 查文本，为什么还要用全文索引 match ... against？

索引的查看与删除

拿到一张已有表，怎么知道它有哪些索引？三种常用办法：

-- 方法一：show keys from 表名
show keys from user10;
-- 结果里有 Key_name（索引名）、Column_name（索引在哪列）、
-- Non_unique（0 表示唯一索引，如主键）、Seq_in_index（索引里第几列）等
 
-- 方法二：show index from 表名（和 show keys 等价）
show index from user10;
 
-- 方法三：desc 表名（信息比较简略，只反映当前结构）
desc user10;

我要特别解释一下 show keys 里几个重要字段，因为它最能帮你看懂一张表的索引情况：

mysql> show keys from goods\G
  Non_unique: 0        <= 0 表示唯一索引（1 表示非唯一普通索引）
   Key_name: PRIMARY   <= 索引名叫 PRIMARY，说明是主键索引
 Seq_in_index: 1       <= 该列是这个索引里的第 1 列（联合索引会有 1、2、3）
 Column_name: goods_id <= 索引起作用的列
  Index_type: BTREE    <= 用的 B+ 树索引结构（BTREE）

删除索引，有三种方式，重点分清"删主键"和"删普通索引"绝对不是一个命令：

-- 方式一：删除主键索引
alter table user3 drop primary key;
 
-- 方式二：删除其他索引（普通/唯一/全文），语法是 drop index 索引名
-- 索引名就是 show keys 结果里的 Key_name
alter table user10 drop index idx_name;
 
-- 方式三：drop index 语法删除索引（等价于方式二，只是记法不同）
drop index name on user8;   -- 删除 user8 表上名为 name 的索引

索引创建原则与"建多还是建少"的取舍

索引不是"建得越多越好"，也不是"不建"。给它定个"分寸"，我们总结出一套经得起推敲的创建原则，同时回答面试里高频的"索引该加在哪列"：

1. 比较频繁作为查询条件（where）的字段，应该考虑建索引：这是索引最核心的用武之地；
2. 唯一性太差的字段，不适合单独建索引：比如一张表的"性别"列只有男女两种值，选择性（cardinality）太低，走索引和一页页扫差别不大，甚至可能帮倒忙；
3. 更新非常频繁的字段，不适合建索引：因为每次更新都要同步维护索引树，写开销会被放大；
4. 不会出现在 where 子句里的字段，不该建索引：建了也用不上，纯浪费空间和写开销。

联合索引（复合索引）

联合索引（Composite/Compound Index） 也叫复合索引，指由多列共同组成一个索引，比如把一个 B+ 树同时建立在 (a, b, c) 三列上。它和"建三个单列索引"完全是两码事。它最核心的配套规则，是下面要讲的重头戏——最左前缀原则。

-- 建一个由两列组成的联合索引：先按 a 排序，a 相同再按 b 排序
create index idx_a_b on t(a, b);

最左前缀原则

最左前缀原则（Leftmost Prefix Principle，也叫最左匹配原则） 是 MySQL 用联合索引时的"导航员"：联合索引查询能否命中，取决于是否能从联合索引的"最左边第一列"开始、连续地使用这些列。你可以把它理解成"查字典按拼音：必须先给第一个字母，再第二个，若跳过中间就直接跳到后列是查不到的"。

用联合索引 (a, b, c) 举例：

• where a = ? 命中（用了第 1 列）；
• where a = ? and b = ? 命中（用了第 1、2 列）；
• where a = ? and b = ? and c = ? 命中（用了第 1、2、3 列）；
• where b = ? 不命中（从第 2 列开始，跳过了最左列 a）；
• where b = ? and c = ? 不命中；
• where a = ? and c = ?：只命中 a 部分，c 部分用不上（a 用索引缩小范围，c 因中间断了 b 而使不上索引，只能拿到 a 的结果后再用条件过滤）。

由此引出两条实战要诀：

• 把最常作为过滤、且选择性最好的列放在联合索引的最左边；
• 合理控制联合索引的列数，并非越多越好，随时间增量调整。

索引失效的常见场景（边界与坑）

建了索引不代表一定被用上。下面这些"坑"会造成索引失效，面试常考，务必背下：

1. like 以 % 开头：like '%abc' 或 like '%abc%' 无法使用索引（前缀没了）；只有 like 'abc%' 这类"前缀是确定的值"才能走索引（当作范围查询）。普通 B+ 树索引本质是有序比较，"前缀未知"就没法利用顺序；
2. 对索引列做运算或函数：如 where year(create_time) = 2025 或 where id + 1 = 10，会让索引列失去原本的键值顺序，无法走索引；
3. 隐式类型转换：如一个字符串列 name 是 varchar，却拿一个数字参数去比，where name = 123，MySQL 常会对列做类型转换导致索引失效；
4. 联合索引"跳过最左列"：上面最左前缀第 5、6 条即索引失效的情形；
5. 对索引列做 not in、is not null、条件中用 or 连接了非索引列等，也容易让优化器走全表扫描；
6. 大范围扫描：当优化器估算要扫的行占比过高时，会认为还不如全表扫描，从而主动放弃索引。

-- 反例 1：like 前置 %，索引失效
select * from user where name like '%黄%';   -- NOT 使用 idx_name
 
-- 反例 2：对索引列做函数，索引失效
select * from user where year(create_time) = 2025;   -- create_time 索引失效
 
-- 正例示例：like 前缀确定，可以利用索引范围查询
select * from user where name like '黄%';   -- 可用 idx_name

覆盖索引：能免掉的回表

覆盖索引（Covering Index） 指的是：索引里已经包含了本次查询所需要的全部列，于是不需要回表去取整行数据。它常常会被用来"治回表"——当辅助索引中已经含有了查询需要的列时，MySQL 直接从索引拿结果即可。

-- 假如 user 有主键 id 和联合索引 (name, age)
-- 这条查询只取 id、name、age 三列，而这正好都"被索引覆盖"了 → 无需回表
select id, name, age from user where name = '黄蓉' and age = 26;

与之相对，只要查询里出现了"索引里没有的列"，就必然要回表，例如 select * ... 里的 * 通常包含索引没有的列，往往就会回表。

【思考题】"覆盖索引"和"回表"的关系是什么？什么时候我们可以利用覆盖索引减少回表？

用 EXPLAIN 看执行计划

前面反复说"走没走索引"，怎么眼见为实？答案就是 EXPLAIN。

EXPLAIN 是 MySQL 提供的"执行计划查看器"：你在一条 SELECT 前加 EXPLAIN，MySQL 不会真的去取大量数据，而是告诉你它打算怎么执行这条 SQL——走哪张表、用什么类型扫描、可能用哪个索引、实际用哪个索引、估摸扫多少行、有没有用临时表/能不能覆盖等等。

-- 在 sql 前加 EXPLAIN，观察这条语句的执行计划
explain select * from articles where body like '%database%'\G

它的关键输出列含义如下（我只挑最常用的讲，其余按需自查）：

• select_type：查询类型，常见 SIMPLE（简单查询，无子查询/联合）；
• table：正在访问哪张表；
• type：访问类型，由快到慢大致是 system > const > eq_ref > ref > range > index > ALL。其中 ALL 是全表扫描，是我们最不想看到的；range 是索引范围扫描（如 like 'abc%'、>/<）；ref/const 常代表走了索引；
• possible_keys：可能被用到的索引；
• key：实际使用的索引；key 为 NULL，说明这条 SQL 没有用到索引；
• rows：MySQL 估算要扫描的行数，越大越慢；
• Extra：额外信息，常见 Using where（用 where 过滤）、Using index（覆盖索引，不用回表）、Using temporary（用了临时表）、Using filesort（文件排序）等。

用之前那个 body like '%database%' 的例子：

              id: 1
      select_type: SIMPLE
           table: articles
            type: ALL        <= ALL 表示全表扫描
      possible_keys: NULL    <= 没有候选索引
               key: NULL     <= key 为 NULL，说明没有用到任何索引！
           key_len: NULL
               ref: NULL
              rows: 6        <= 估摸要扫 6 行（全扫）
             Extra: Using where  <= 在 where 里做了行级过滤

对比用 MATCH 走全文索引的执行计划：

              id: 1
      select_type: SIMPLE
           table: articles
            type: fulltext   <= type 是 fulltext，说明走了全文索引
      possible_keys: title   <= 候选索引里能看到 title
               key: title    <= key 用到了 title（这个全文索引）
           key_len: 0
               ref:
              rows: 1
             Extra: Using where

差别一目了然：一个是 type: ALL + key: NULL（全表扫描没索引），一个是 type: fulltext + key: title（真正用上了索引）。以后你调 SQL，一定要先 EXPLAIN 一下，看 key 是不是空、type 是不是已经差到 ALL，再对症下药——这是 DBA 和一线开发的基本功。

总结与收尾

这一路走下来，我们其实画的是一条完整的因果链：

• 为什么慢：数据在磁盘上、磁盘的 IO 很贵、没有索引只能全表扫描逐行比（800 万行案例、几秒起步）；
• 为什么一对 16KB 的 page：MySQL 用 Buffer Pool + 一页一页地和磁盘交互，靠局部性原理减少 IO 次数；
• 索引到底长啥样：从单页内目录，到给页加目录，最终长出 B+ 树——只有叶子存数据、叶子彼此相连、层矮、方便范围查找，所以 InnoDB 用它，而不是链表、二叉平衡树、哈希或 B 树；
• 聚簇 vs 非聚簇：InnoDB 主键索引（聚簇）数据就在叶子，普通索引（辅助）叶子只存主键，取整行要回表；MyISAM 数据和索引分离；
• 索引的种类：主键、唯一、普通、全文、联合（复合），各自的创建方式、特点和适用边界；
• 怎么看索引：show keys / show index / desc；删除用 alter table drop index 或 drop index，主键单独用 alter table drop primary key；
• 使用的分寸：索引创建四原则；联合索引配 最左前缀；提防 like 前置 %、函数运算、隐式转换、跳最左列等索引失效；能用 覆盖索引 就尽量免回表；
• 如何验证：EXPLAIN 看 type 和 key，判断到底走没走索引。

如果你是跟着存储过程一步步造数据、再亲手 EXPLAIN 观察到 key: NULL 与 key: title 的天壤之别，这堂课你就真正拿下了。索引不是"背两条规则就完事"，它的分寸感——在哪建、建哪几列、怎么用才不失效、怎么免回表——全都要在实际的千万级数据和真实 SQL 里碰撞出来。

最后落在价值观层面：索引是你成本最低、见效最快的一套性能武器，但它绝不是免费午餐，写操作和空间都要为它买单。在"读多写少、查询高频"的列上，舍得建好索引；在同一个人插入海量且不断更新的热点列上，务必三思而行。

下一篇文章，我们会站在这棵树之上，继续深入存储引擎与事务，看看 InnoDB 在"并发与一致"这场大考里如何交手。不过在那之前，请一定先把 CREATE INDEX 敲熟、把 EXPLAIN 读顺。准备好了吗？