前言
索引就像是数据库的“目录”，能帮助数据库快速找到数据。但如果用错了索引类型，或者索引未生效，反而可能拖慢查询速度。因此我们需要了解索引类型，以及应该如何选择索引。
一、MySQL 的几大核心索引类型
1. 主键索引（PRIMARY KEY）
特点：唯一标识每行数据，不允许重复和空值（NULL）。
实例：用户表的 user_id 字段。
使用场景：
必须为表指定主键（如无显式定义，InnoDB 会自动生成隐藏主键）。
常用于 WHERE user_id = 1001 或联表查询。
CREATE TABLE users (
id INT PRIMARY KEY, -- 主键索引
username VARCHAR(50)
);
2. 唯一索引（UNIQUE）
特点：确保字段值唯一，允许空值（但只能有一个 NULL）。
实例：用户表的手机号（mobile）或邮箱（email）。
使用场景：
需要强制唯一性的字段，如防止重复注册。
查询条件包含该字段时加速。
CREATE TABLE users (
id INT PRIMARY KEY,
mobile VARCHAR(20) UNIQUE, -- 唯一索引
email VARCHAR(50) UNIQUE
);
3. 普通索引（NORMAL）
特点：最基本的索引，无唯一性约束。
实例：订单表的 status 字段（如待付款、已发货）。
使用场景：
常用于频繁查询但允许重复的字段。
支持 WHERE status = 'paid' 或 ORDER BY create_time。
CREATE TABLE orders (
id INT PRIMARY KEY,
status VARCHAR(20),
INDEX idx_status (status) -- 普通索引
);
4. 全文索引（FULLTEXT）
特点：针对大文本字段的关键词搜索，支持模糊匹配。
实例：文章表的 content 字段。
使用场景：
搜索文本内容中的关键词（如 WHERE MATCH(content) AGAINST('数据库')）。
替代低效的 LIKE '%关键词%' 查询。
CREATE TABLE articles (
id INT PRIMARY KEY,
content TEXT,
FULLTEXT INDEX ft_content (content) -- 全文索引
);
5. 空间索引（SPATIAL）
特点：专用于地理空间数据（如经纬度坐标），支持 GIS 查询。
实例：地图应用中存储坐标的 coordinate 字段。
使用场景：（比较冷门，使用场景较少）
快速查询地理范围内的数据（如 ST_Contains()）。
CREATE TABLE locations (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(100),
geom POINT NOT NULL,
SPATIAL INDEX (geom) -- 空间索引
) ENGINE=InnoDB;
6. 组合索引（复合索引）
特点：多个字段联合组成的索引，遵循“最左匹配原则”。
实例：商品表的 category_id + price。
使用场景：
查询条件同时涉及多个字段（如 WHERE category_id=3 AND price>100）。
注意字段顺序：高频查询字段放左侧。
CREATE TABLE products (
id INT PRIMARY KEY,
category_id INT,
price DECIMAL(10,2),
INDEX idx_category_price (category_id, price) -- 组合索引
);
❗️ 最左匹配原则示例：
有效查询：WHERE category_id=3 或 WHERE category_id=3 AND price>100。
无效查询：WHERE price>100（未使用最左字段 category_id，索引失效）。
二、如何选择合适的索引？
1. 根据查询需求选择
高频查询字段：如订单状态（status）适合普通索引。
唯一性约束：如手机号用唯一索引。
多条件查询：组合索引优先于多个单列索引。
文本搜索：全文索引替代 LIKE 模糊查询。
地理数据：空间索引加速位置范围查询。
2. 关注字段的选择性
高选择性字段：唯一值多的字段（如 user_id）更适合索引。
低选择性字段：如性别（只有男/女）建索引意义不大。
3. 控制索引数量
优点：索引加速查询。
缺点：索引占用空间，且增删改操作需维护索引，影响写入速度。
建议：单表索引不要太多，避免冗余索引。
4. 组合索引的优化技巧
字段顺序：高频查询字段放左侧。
覆盖索引：索引包含所有查询字段，避免回表查询。
-- 假设有索引 (category_id, price)
SELECT category_id, price FROM products WHERE category_id=3; -- 直接通过索引获取数据
三、常见误区与避坑指南
❌ 误区 1：所有字段都建索引
问题：索引过多导致写入性能下降。
案例：订单表有 10 个索引，批量新增多条订单耗时过长。
❌ 误区 2：盲目使用组合索引
问题：忽略最左匹配原则，导致索引失效。
案例：索引 (a, b, c)，但查询条件为 WHERE b=1 AND c=2。
✅ 建议：使用 EXPLAIN 分析 SQL 执行计划，验证索引是否生效。
四、总结
索引类型适用场景注意事项主键索引唯一标识行数据必须非空且唯一唯一索引防止重复值允许一个 NULL普通索引加速高频查询的非唯一字段避免过多索引全文索引大文本关键词搜索文本过短时效率不如普通索引空间索引地理坐标范围查询字段需为空间数据类型组合索引多条件联合查询注意字段顺序
通过合理使用索引，可以让数据库查询速度提升十倍甚至百倍。但记住：索引不是越多越好，适合的才是最优解！！！