MySQL 索引

一、MySQL 索引到底是什么？（大白话解释）

索引就像书籍的目录：

• 没有索引时，MySQL 查数据要逐行扫描整张表（全表扫描），就像找书中某句话要一页页翻；
• 有了索引后，MySQL 先查索引（目录），快速定位到数据所在的行，直接读取，效率提升几十倍甚至上百倍。

从技术角度说，MySQL 索引是一种特殊的数据结构（主流是 B + 树），存储了表中一列 / 多列的值和对应数据的物理地址，核心作用是加速查询。

二、索引的核心作用

1. 加速查询：这是最核心的作用，尤其是表数据量上万、上百万时，差距极其明显；
2. 减少排序 / 分组开销：索引本身是有序的，查询需要排序（ORDER BY）、分组（GROUP BY）时，可直接用索引的有序性，无需额外排序；
3. 唯一性约束：比如主键索引（PRIMARY KEY）、唯一索引（UNIQUE），能保证列值不重复，避免数据冗余。

三、MySQL 常用索引类型（新手必懂）

索引类型	特点 / 用途	示例场景
主键索引	唯一、非空，一张表只能有 1 个，默认用 B + 树实现	用户表的 id 列（user_id）
唯一索引	列值唯一（可含 NULL），一张表可多个	用户表的手机号 / 邮箱列
普通索引	最基础的索引，无唯一性约束，加速普通查询	商品表的分类列（category_id）
复合索引	基于多列创建的索引，遵循 “最左前缀匹配” 原则	订单表的（user_id, create_time）
全文索引	针对文本内容的索引，支持模糊匹配（MATCH AGAINST），适合长文本（如文章）	文章表的 content 列

四、索引的创建与删除（实操示例）

1. 创建索引

sql

 

 

 

 

 

-- 1. 创建普通索引（建表后）
CREATE INDEX idx_goods_category ON goods(category_id);-- 2. 创建唯一索引
CREATE UNIQUE INDEX idx_user_phone ON user(phone);-- 3. 创建复合索引
CREATE INDEX idx_order_user_create ON `order`(user_id, create_time);-- 4. 建表时直接创建索引
CREATE TABLE user (id INT PRIMARY KEY AUTO_INCREMENT, -- 主键索引（自动创建）phone VARCHAR(11) UNIQUE, -- 唯一索引（自动创建）name VARCHAR(20),INDEX idx_user_name (name) -- 普通索引
);

 

2. 删除索引

sql

 

 

 

 

 

-- 删除普通/唯一/复合索引
DROP INDEX idx_goods_category ON goods;-- 删除主键索引（需先取消自增，再删除）
ALTER TABLE user MODIFY id INT;
ALTER TABLE user DROP PRIMARY KEY;

 

五、索引使用的核心规则（避坑关键）

1. 最左前缀匹配原则：复合索引(a,b,c)，只有查询条件包含a（最左列）时才会生效，比如：
   • 生效：WHERE a=1、WHERE a=1 AND b=2、WHERE a=1 AND b=2 AND c=3；
   • 不生效：WHERE b=2、WHERE b=2 AND c=3。
2. 这些情况索引会失效：
   • 索引列参与函数 / 运算：WHERE id+1=10（应写成WHERE id=9）；
   • 使用模糊查询前缀 %：WHERE name LIKE '%张三'（后缀 % 生效：WHERE name LIKE '张三%'）；
   • 使用 OR 连接非索引列：WHERE a=1 OR b=2（b 无索引时失效）；
   • 索引列用了 NULL 判断（尽量给列设默认值）。
3. 不是索引越多越好：索引会加速查询，但会减慢插入 / 更新 / 删除（因为修改数据时要同步更新索引），建议只给常用查询字段建索引。

六、如何验证索引是否生效？

用EXPLAIN命令分析 SQL 执行计划：

sql

 

 

 

 

 

-- 查看查询是否使用索引
EXPLAIN SELECT * FROM goods WHERE category_id=1;

 

重点看type列（越好：const > eq_ref > ref > range > ALL）和key列（显示使用的索引名，NULL 表示未用索引）。

总结

1. 核心本质：索引是 MySQL 的 “目录”，基于 B + 树实现，核心作用是加速查询；
2. 常用类型：主键索引、唯一索引、普通索引、复合索引（重点掌握复合索引的最左前缀原则）；
3. 避坑要点：避免索引失效场景，索引不是越多越好，用 EXPLAIN 验证索引是否生效。