MySQL索引“全家桶”大起底：主键、唯一、普通、全文、前缀……到底该Pick谁？
🍔MySQL索引“全家桶”大起底：主键、唯一、普通、全文、前缀……到底该Pick谁？🤔
同事突然凑过来问：“兄弟，这张表索引咋建啊？”
我淡定回：“看场景呗，主键、唯一、普通……”
同事眼睛一亮：“那干脆全给它安排上？！”
我：？？？🤯 你这是要建索引，还是给数据库做全身SPA啊？！
别急，今天咱就掰开揉碎了聊聊，MySQL这五大索引到底都是啥来头，啥场合该派谁上场！先上张“全家福”镇镇场子👇
🔑 主键索引：表的“户口本+身份证”
它是啥？简单说，就是数据库给每行数据发的**“唯一工牌”**。
CREATETABLEt(idINTPRIMARYKEY,-- 瞧，这就是主键索引nameVARCHAR(50));🌟它有啥脾气？
- 自带“聚簇”光环：数据都乖乖趴在叶子节点上，找它等于直接掏老底，速度嗖嗖的🚀
- 独生子女：一张表只能有一个，多了就打架🥊
- 铁面无私：必须唯一，且绝对不能为空（NULL？不存在的🙅♂️）
💣那些年我们踩过的坑：
- 💡主键用自增ID还是UUID？
👉自增ID（闭眼选它）：按顺序排队插入，页空间利用率高到感人；范围查询跑得比博尔特还快；还省地儿（4/8字节 vs UUID的16字节）。
👉UUID（慎选！）：随机插入，数据库天天忙着“页分裂”做手术；范围查询直接累趴；还占空间。除非搞分布式全局ID，否则别轻易招惹它🙅♀️ - 💡手滑没写主键咋办？
InnoDB可不惯着你：先找唯一非空索引顶包；要是连这都没有，就偷偷塞个6字节的row_id。虽然能用，但性能嘛……你懂的😅
⭐ 唯一索引：专治“反复横跳”的守门员 🚪🛡️
它是啥？就是给某些字段立个规矩：“你可以不来，但来了就别想冒充别人！”
CREATETABLEt(idINTPRIMARYKEY,emailVARCHAR(100)UNIQUE,-- 唯一索引安排上phoneVARCHAR(20));-- 或者单独建：CREATEUNIQUEINDEXidx_emailONt(email);🌟它有啥绝活？
- 拒绝撞衫：值绝对不能重复！
- 对NULL很宽容：允许有空值，而且可以有好几个NULL（毕竟NULL != NULL，数据库也觉得它们“谁也不认识谁”🤷♂️）
- 查起数据来：速度和普通索引差不多，平起平坐🤝
🆚主键 vs 唯一索引，到底差在哪？
| 特性 | 主键 (老大) | 唯一索引 (保镖) |
|---|---|---|
| 数量 | 只能有1个 👑 | 可以有多个 🎭 |
| 能空吗？ | 绝对不行 ❌ | 可以，还能多几个NULL 👻 |
| 聚簇吗？ | 是，数据跟它绑死 🤝 | 否，只是个快捷方式 🔗 |
| 干啥的？ | 标识这行是谁 | 防止数据“重名”捣乱 🚫 |
📄 普通索引：默默干活的“加速外挂” 🏎️💨
它是啥？数据库里的“老黄牛”，专治各种查询慢吞吞。
CREATETABLEt(idINTPRIMARYKEY,nameVARCHAR(50),ageINT,INDEXidx_name(name),-- 普通索引上线INDEXidx_age(age));🌟它有啥特点？
- 海纳百川：值可以随便重复，不挑食🍜
- 专业加速：就是为了让你在
WHERE里查得飞起✈️ - 多多益善？错！一张表可以建一堆，但别贪心！
💣避坑指南：
- 💡索引是不是越多越香？
大错特错！🙅♂️ 索引可是个“娇气包”，你INSERT/UPDATE/DELETE一下，它就得跟着做仰卧起坐。一般单表5~7个就顶天了，多了数据库直接累到罢工📉 - 💡
WHERE里出现的字段都要建索引？
看“区分度”啊兄弟！比如“性别”字段，非男即女，区分度就2。给它建索引，相当于在图书馆里用“男/女”分类找书……纯属脱裤子放屁🤣
🔍 全文索引：文本搜索的“显微镜+搜索引擎” 📖🔎
它是啥？专治各种“大海捞针”式的文本查找。
CREATETABLEarticles(idINTPRIMARYKEY,titleVARCHAR(200),contentTEXT,FULLTEXTINDEXft_idx(title,content)-- 全文索引已就位);-- 怎么用？这么搜：SELECT*FROMarticlesWHEREMATCH(title,content)AGAINST('MySQL索引');🌟它有啥黑科技？
- 专业对口：专门伺候大段文本搜索📝
- 倒排索引打底：不是一页页翻，而是直接按“词”反查，效率高到离谱🚀
- 自带分词+相关性打分：搜出来的结果还能按“靠谱程度”排座次🏆
🎯啥时候请它出山？
| 场景 | 建议 |
|---|---|
| 简单模糊查 | LIKE '%xxx%'直接全表扫描，慢到怀疑人生😴 全文索引直接救场！ |
| 文章/商品搜 | MATCH ... AGAINST跑起来飞快，真香🤤 |
| 复杂搜索需求 | 别硬刚！出门右转找 Elasticsearch/Sphinx，MySQL别背锅🙏 |
⚠️注意事项（划重点）：
- 只有
MyISAM和InnoDB（5.6+）支持它。 - 中文分词默认有点“智障”，得自己配插件（比如
ngram）。 - 默认最少搜4个字符，太短的词它懒得理你🤏
✂️ 前缀索引：长字符串的“精准瘦身术” 📏✨
它是啥？字段太长（比如长URL、巨长邮箱）存索引太占地儿？只截取前N个字建索引，省空间又提速！
-- email字段老长老长，咱只取前10位建索引CREATEINDEXidx_email_prefixONusers(email(10));🎯啥时候用它？
- 字符串长得能绕地球三圈🌍
- 前几个字就已经能精准区分开数据了（比如大部分邮箱
@前面都不一样）
📐前缀长度到底截多少合适？
别瞎猜，跑个SQL算算“区分度”就完事了：
SELECTCOUNT(DISTINCTLEFT(email,5))/COUNT(DISTINCTemail)assel5,COUNT(DISTINCTLEFT(email,10))/COUNT(DISTINCTemail)assel10,COUNT(DISTINCTLEFT(email,15))/COUNT(DISTINCTemail)assel15FROMusers;-- 挑那个区分度接近1，但长度最短的，性价比最高！💰⚖️优点 vs 缺点（大实话）：
| 优点 😎 | 缺点 😅 |
|---|---|
| 巨省空间，磁盘直呼内行 💾 | 不能拿来ORDER BY排序，一用就废 🚫 |
| 等值查询照样快 🏃♂️ | 没法搞“覆盖索引”，回表是逃不掉的 🔄 |
| 索引维护成本低，数据库少干活 💤 | 前缀长度没选对，索引直接变废铁 🗑️ |
🎯 索引选择决策树（照图抄作业！）🗺️
🛠 老司机避坑指南（敲黑板！）📝
- 💡主键和唯一索引能“同居”吗？
必须能啊！主键是“正宫”（聚簇索引），唯一索引是“侧妃”（二级索引），井水不犯河水，配合得贼好👌 - 💡全文索引和
LIKE '%xxx%'到底啥区别？LIKE '%xxx%'：通配符在左边，索引直接装死，全表扫描跑到CPU冒烟🔥
全文索引：走的是倒排索引高速路，不仅快，还能按“相关度”给你排好队，体验感拉满💯 - 💡前缀索引能搞“覆盖索引”吗？
想都别想！🙅♂️ 它只存了半截数据，查完整内容还得乖乖“回表”找原始数据，别指望它一步到位。
🎯 最后，说点“人话”总结（建议截图保存📸）
| 索引类型 | 核心作用（一句话版） | 啥时候用？ |
|---|---|---|
| 主键 | 给每行数据发身份证 🪪 | 建表必带，没它数据库会慌 |
| 唯一索引 | 防止数据“撞名”重婚 🚫💍 | 邮箱、手机号、工号等 |
| 普通索引 | 查询加速的“物理外挂” 🏎️ | WHERE里频繁查的字段 |
| 全文索引 | 文本搜索的“超级显微镜” 🔍 | 文章内容、商品描述大段文本 |
| 前缀索引 | 长字符串的“空间压缩术” 📦 | URL、超长邮箱等，前几位就能区分 |
📜老鸟设计原则（背下来不亏）：
- 主键？闭眼选自增ID，稳如老狗🐕
- 怕重复？果断上唯一索引
- 查得勤？悄悄加普通索引
- 字符串太长？试试前缀索引省空间
- 搜索太复杂？别跟MySQL死磕，Elasticsearch才是真神🙏
🙋来，唠两句！💬
你平时建索引是“闭着眼睛加”，还是“拿着EXPLAIN反复盘”？🧐
有没有踩过“索引建了一堆，查询照样慢成狗”的坑？或者“没建索引，结果数据库直接罢工”的社死现场？
👇 评论区吐吐槽、晒晒经验，咱们互相避雷，少走弯路！
👍 觉得这篇有点东西？点赞+收藏，就是对我最大的投喂～ 🍖
下期想听啥？复合索引的“左前缀法则”？覆盖索引怎么榨干性能？留言点名，安排！🎬
📌注：本文技术细节基于 MySQL 5.7.40 / 8.0.35 实测验证。生产环境水深，请务必结合你的实际版本+压力测试再动手，别拿线上库当试验田啊喂！⚠️
📚延伸阅读指路：《MySQL技术内幕：InnoDB存储引擎》、MySQL官方文档、Percona Blog（硬核玩家必看）