MySQL索引“全家桶”大起底主键、唯一、普通、全文、前缀……到底该Pick谁同事突然凑过来问“兄弟这张表索引咋建啊”我淡定回“看场景呗主键、唯一、普通……”同事眼睛一亮“那干脆全给它安排上”我 你这是要建索引还是给数据库做全身SPA啊别急今天咱就掰开揉碎了聊聊MySQL这五大索引到底都是啥来头啥场合该派谁上场先上张“全家福”镇镇场子 MySQL索引类型一览✂️ 前缀索引INDEX(col(n))长字符串取前n位省空间 全文索引FULLTEXT INDEX文本搜索倒排索引结构 普通索引INDEX / KEY值可重复加速查询用⭐ 唯一索引UNIQUE INDEX值不能重复允许NULL可多个 主键索引PRIMARY KEY聚簇索引叶子存整行数据唯一 非空 主键索引表的“户口本身份证”它是啥简单说就是数据库给每行数据发的**“唯一工牌”**。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/SphinxMySQL别背锅⚠️注意事项划重点只有MyISAM和InnoDB5.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排序一用就废 等值查询照样快 ‍♂️没法搞“覆盖索引”回表是逃不掉的 索引维护成本低数据库少干活 前缀长度没选对索引直接变废铁 ️ 索引选择决策树照图抄作业️ 索引类型选择决策是否是否是否是否需要建索引?是主键? PRIMARY KEY自增ID推荐值必须唯一?⭐ UNIQUE INDEX是文本搜索? FULLTEXT INDEX或ES/Sphinx字符串很长?✂️ 前缀索引取前N位 普通 INDEXEXPLAIN验证执行计划上线 老司机避坑指南敲黑板主键和唯一索引能“同居”吗必须能啊主键是“正宫”聚簇索引唯一索引是“侧妃”二级索引井水不犯河水配合得贼好全文索引和LIKE %xxx%到底啥区别LIKE %xxx%通配符在左边索引直接装死全表扫描跑到CPU冒烟全文索引走的是倒排索引高速路不仅快还能按“相关度”给你排好队体验感拉满前缀索引能搞“覆盖索引”吗想都别想‍♂️ 它只存了半截数据查完整内容还得乖乖“回表”找原始数据别指望它一步到位。 最后说点“人话”总结建议截图保存索引类型核心作用一句话版啥时候用主键给每行数据发身份证 建表必带没它数据库会慌唯一索引防止数据“撞名”重婚 邮箱、手机号、工号等普通索引查询加速的“物理外挂” ️WHERE里频繁查的字段全文索引文本搜索的“超级显微镜” 文章内容、商品描述大段文本前缀索引长字符串的“空间压缩术” URL、超长邮箱等前几位就能区分老鸟设计原则背下来不亏主键闭眼选自增ID稳如老狗怕重复果断上唯一索引查得勤悄悄加普通索引字符串太长试试前缀索引省空间搜索太复杂别跟MySQL死磕Elasticsearch才是真神来唠两句你平时建索引是“闭着眼睛加”还是“拿着EXPLAIN反复盘”有没有踩过“索引建了一堆查询照样慢成狗”的坑或者“没建索引结果数据库直接罢工”的社死现场 评论区吐吐槽、晒晒经验咱们互相避雷少走弯路 觉得这篇有点东西点赞收藏就是对我最大的投喂 下期想听啥复合索引的“左前缀法则”覆盖索引怎么榨干性能留言点名安排注本文技术细节基于 MySQL 5.7.40 / 8.0.35 实测验证。生产环境水深请务必结合你的实际版本压力测试再动手别拿线上库当试验田啊喂⚠️延伸阅读指路《MySQL技术内幕InnoDB存储引擎》、MySQL官方文档、Percona Blog硬核玩家必看