一、索引是什么？能干嘛？
类比理解：索引就像书的目录。比如你想查《哈利波特》中 “伏地魔” 出现的页数，不用逐页翻书，直接看目录找关键词就行。数据库里的索引就是帮你快速找到数据的 “目录”。
核心作用：
- 提速查询：把 “全表扫描”（逐行找数据）变成 “精准定位”，查询速度从 “翻完整本书” 变成 “查目录找页码”。
- 约束数据：比如主键索引能保证数据不重复（像身份证号唯一）。
- 加速排序：索引本身是有序的，排序时不用临时整理数据。
二、索引的 4 种主要类型（附通俗例子）
1. BTree 索引（最常用的 “万能索引”）
- 原理：像字典的拼音目录，按顺序排列（如 a-b-c...），支持范围查询（如查 “年龄> 18”）和精准查询。
适用场景：90% 的场景都能用，比如：
|
1 |
CREATE INDEX idx_age ON users(age); -- 给年龄字段建索引 |
- 类比：查字典时，知道 “张三” 的拼音，直接按字母顺序翻到对应页。
2. Hash 索引（“快速匹配器”）
- 原理：把数据变成 “哈希值”（类似快递单号），查数据时直接 “对单号”，速度极快（O (1)）。
- 限制：只能精准匹配（如WHERE id=1），不能查范围（如id>100），且仅内存表（MEMORY 引擎）可用。
- 类比：快递柜取件，输入单号直接开门，无法 “找所有单号大于 100 的快递”。
3. 全文索引（“文本搜索神器”）
- 原理：专门针对文章、评论等长文本，把关键词拆分成 “词条” 存储（类似搜索引擎的关键词索引）。
- 适用场景：查 “包含‘MySQL’的文章”，用MATCH AGAINST语句：
|
1 |
CREATE FULLTEXT INDEX idx_article ON articles(content); |
- 注意：MySQL 5.7 + 优化后性能更好，别用 LIKE '% 关键词 %'（太慢）。
4. 空间索引（“地图专用索引”）
- 原理：存储地理坐标（如经纬度），支持 “查找附近 5 公里的咖啡店” 这类查询。
- 适用场景：外卖 APP 找附近商家、地图软件标地点。
- 限制：字段必须是 GEOMETRY 类型（如点、线、面），InnoDB 引擎从 5.7 开始支持。
三、啥时候该建索引？啥时候别建？
? 建议建索引的情况：
- 经常用来查询的字段：比如WHERE name='张三'中的 name 字段。
- 表关联字段：多表 JOIN 时的关联字段（如订单表的 user_id 关联用户表）。
- 唯一性字段：主键（id）、邮箱（唯一不重复）。
- 频繁排序的字段：如ORDER BY create_time，索引自带顺序，不用额外排序。
? 不建议建索引的情况：
- 数据重复率高的字段：比如 “性别”（只有男 / 女），建索引还不如直接全表扫描快。
- 频繁更新的字段：比如 “在线状态”，每次修改都要更新索引，影响性能。
- 小表数据：表只有 100 行数据，全表扫描比查索引更快（索引本身也占空间）。
- 不参与查询的字段：建了索引也用不上，纯属浪费空间。
四、复合索引：多个字段 “组队” 加速查询
1. 什么是复合索引？
- 给多个字段一起建索引，比如(name, age)，相当于 “组合目录”。
- 语法：
|
1 |
CREATE INDEX idx_name_age ON users(name, age); |
2. 最左前缀原则（必须掌握！）
- 规则：查询条件必须从左到右使用索引中的字段，不能跳过。
- 示例：索引是(name, age)，支持：
|
1 2 |
WHERE name='张三' AND age=18; -- 正确，用全索引 WHERE name='张三'; -- 正确，用name部分 |
不支持：
|
1 2 |
WHERE age=18; -- 错误，跳过了name，索引失效 WHERE name='张三' AND age=18 AND address='北京'; -- 正确，address不影响，前两个字段用上索引 |
- 类比：索引像 “省 - 市 - 区” 的地址，你必须先指定 “省”，才能用索引快速定位，直接查 “区” 无法用索引。
五、索引优化：让查询飞起来的技巧
1. 覆盖索引：“不回表” 的高效查询
- 定义：查询的所有字段都在索引里，不用再回表查数据（类似查目录时直接拿到所有需要的信息，不用翻书）。
- 示例：
|
1 2 3 |
-- 表结构：users(id, name, age) CREATE INDEX idx_name_age ON users(name, age); -- 索引包含name和age SELECT name, age FROM users WHERE name='张三'; -- 直接从索引取数据，不用回表 |
2. 索引失效场景（避坑指南）
- 用了函数或表达式：
|
1 |
WHERE UPPER(name)='ZHANGSAN'; -- 对name做了大写转换，索引失效 |
类型不匹配：
|
1 |
WHERE id='123'; -- id是数字类型，传字符串可能导致索引失效 |
模糊查询以通配符开头：
|
1 |
WHERE name LIKE '%张三'; -- 无法用索引（不知道从哪开始查） |
OR 条件分隔无关联字段：
|
1 |
WHERE id=1 OR name='张三'; -- 若id和name没有共同索引，可能失效 |
3. 索引管理命令（常用）
创建索引：
|
1 2 |
CREATE INDEX idx_name ON users(name); -- 普通索引 CREATE UNIQUE INDEX idx_email ON users(email); -- 唯一索引 |
删除索引：
|
1 |
DROP INDEX idx_name ON users; |
查看索引：
|
1 |
SHOW INDEX FROM users; |
分析查询是否用索引：
|
1 |
EXPLAIN SELECT * FROM users WHERE name='张三'; -- 看执行计划中的Key列 |
六、不同引擎的索引差异（简单了解）
| 引擎 | 支持的索引类型 | 特点 |
|---|---|---|
| InnoDB | BTree、全文、空间 | 数据和索引存一起（聚簇索引），适合事务 |
| MyISAM | BTree、全文、RTree | 索引和数据分开存，不支持事务 |
| Memory | Hash、BTree | 数据在内存，查询极快，但重启数据丢失 |
七、实战案例：电商订单表索引优化
场景：
查询 “近 30 天内，已支付（status=2）且金额> 1000 的订单”，按时间倒序。
表结构：
|
1 2 3 4 5 6 7 |
CREATE TABLE orders ( id BIGINT PRIMARY KEY, user_id BIGINT, order_time DATETIME, status TINYINT, amount DECIMAL(10,2) ); |
优化方案：
创建联合覆盖索引：
|
1 |
CREATE INDEX idx_status_time_amount ON orders(status, order_time, amount); |
查询语句：
|
1 2 3 |
SELECT id, user_id, amount FROM orders WHERE status=2 AND order_time >= NOW() - INTERVAL 30 DAY AND amount > 1000 ORDER BY order_time DESC; |
优化原理：
- 联合索引(status, order_time, amount)满足最左前缀原则，先按状态筛选，再按时间和金额过滤。
- order_time在索引中是有序的，查询时直接按倒序取，不用额外排序。
- 查询字段id, user_id, amount都在索引中（id是主键，默认在索引里），实现覆盖索引，不回表。
八、索引使用的核心原则（必记！）
- 少而精：单表索引不超过 5 个，避免过度索引（每个索引都增加写入开销）。
- 联合索引优先：多个字段频繁一起查询时，建联合索引比多个单列索引更高效。
- 覆盖索引优先：让查询字段尽量在索引中，减少 “回表” 操作。
- 定期维护：用ANALYZE TABLE更新索引统计信息，用EXPLAIN分析慢查询是否用了索引。
- 避免坑点：不用函数处理字段、不写SELECT *（只查需要的字段）、模糊查询用LIKE '关键词%'（别以通配符开头）。
通过合理设计索引，MySQL 查询性能能提升 10-100 倍！但记住：索引不是越多越好，要在 “查询速度” 和 “写入速度” 之间找平衡哦～
