你是不是也有过这样的经历明明表里就几百万行数据一条带WHERE条件的查询却跑了十几秒把接口拖到超时DBA半夜打电话喊你起来看慢查询。我见过太多刚入门的朋友一听到MySQL索引与性能优化基础就觉得是DBA才需要学的东西结果自己负责的第一个线上模块就被一个全表扫描教会了做人。这篇博文我就用实际排查过的案例把索引的底层逻辑、失效场景、EXPLAIN解读和优化链路一次讲透适合正在学习MySQL基础教程的开发者也适合那些已经被慢查询折磨过但没系统梳理过索引知识的人。索引这事说难不难说简单也绝对不简单。难的不是建索引而是理解为什么这样建、什么场景下它不干活、以及怎么用工具去验证自己的判断。这篇文章不会给你背一大堆命令而是把索引为什么能加速、什么时候失效、怎么排查、怎么设计这四件事串起来看完你至少能独立处理工作中八成以上的慢查询问题。1. 从一次慢到离谱的查询说起索引到底在加速什么三年前有个后台管理系统的订单查询接口数据量大约五百万行前端一按查询按钮接口平均响应时间稳定在8秒左右。我当时接手的第一反应不是急着看SQL而是先确认了一件事这个表到底建没建索引建的是什么索引。结果发现订单表上除了主键索引之外一个二级索引都没有所有条件查询全在走全表扫描。这就是最典型的能用但很慢的案例。1.1 没有索引时MySQL在做什么先聊一个最基础的问题没有索引的时候一条带WHERE条件的查询是怎么执行的。你可以把存储引擎想象成一个大仓库数据行按插入顺序堆在里面。你要找status1且create_time在最近三天的订单MySQL没有任何捷径只能从第一行数据开始一行一行往下扫把每一行都读出来逐字段比对条件命中的塞进结果集。这个过程叫全表扫描时间复杂度是O(n)。表只有几百行的时候全表扫描无所谓秒回。但当数据量到几十万、几百万行每一行都涉及磁盘IO读取这个开销就完全失控了。IO是数据库最宝贵的资源机械硬盘随机读一次大概要10毫秒即便换成NVMe固态大量小尺寸随机读累积起来也扛不住。所以我一直跟新人强调索引优化的本质是减少存储引擎需要扫描的数据量把O(n)的查找变成O(log n)甚至更少。1.2 索引就是一本带目录的字典很多人问索引到底是个啥我习惯用字典来打比方。一本十万字的书你要找一个特定关键词没目录就只能从头翻到尾运气好翻到第一页运气不好翻到最后一页。有了目录之后你先查目录定位到章节页再跳到对应页码几秒钟就搞定。MySQL的索引干的就是这件事它把某几列的值单独拿出来按照一定的数据结构组织好并记录每个值对应的数据行位置。查询的时候先走这个目录定位再根据指针去拿完整的数据行。但这个目录不是白给的。它要占额外的磁盘空间而且每次插入、更新、删除数据时索引结构也得跟着维护写入性能会打折扣。这就是为什么索引不能乱建——建多了写库变慢建错了查询还是慢。后面第6节我会详细讲索引设计的取舍。索引有好几种存储结构MyISAM和InnoDB的差异也不小但现代MySQL默认都是InnoDB引擎所以我们聊优化都以InnoDB为前提。2. 索引在存储引擎里的真实形态B树、聚簇索引与回表光知道索引是目录还不够你得稍微往底层看一眼。很多索引失效问题根源就在于不理解InnoDB到底怎么组织索引的。这一节我尽量不堆术语用大白话把B树、聚簇索引、回表、覆盖索引这四件事讲明白。2.1 为什么偏偏是B树索引最核心的数据结构是B树。为什么不是二叉搜索树不是哈希表也不是红黑树答案藏在数据库的真实使用场景里。二叉搜索树在极端情况下会退化成链表高度不可控红黑树虽然是平衡的但树的高度依然随数据量上升而每次比较都伴随着磁盘IO哈希表等值查找极快但完全无法处理范围查询和排序B树之所以胜出核心在于它把树高控制得非常低而且叶子节点之间通过链表串了起来。InnoDB默认一页是16KB一个节点能放很多个键值通常一棵三四层的B树就能存储几千万行数据。这意味着查找一条记录磁盘IO次数基本就是树的层数稳定在三四次左右。而叶子节点用链表串联又让范围查询变得特别顺手——找到下限之后沿着链表往后扫就行。我见过不少初学者纠结B树和B树的区别其实在日常优化中你不用做太深的对比只要记住B树的杀手锏是矮胖加叶子有序链表这两点就够用了。2.2 聚簇索引与二级索引每一根索引背后都藏着回表InnoDB的表本身就是一个聚簇索引结构。表里的数据行物理存储顺序就是按照主键排列的这叫聚簇索引。你在建表时定义了主键那么这个主键就是聚簇索引的键如果没定义主键InnoDB会找第一个非空唯一键再没有就生成一个隐藏的行ID。聚簇索引的叶子节点直接存了整行数据所以通过主键查数据一次索引查找就能拿到全部字段。但我们建的普通索引包括联合索引在InnoDB里都叫二级索引。二级索引的叶子节点存的是什么是索引列的值加上一个主键值。这个设计非常巧妙它让二级索引不依赖数据行的物理地址即使数据页分裂移动了索引也不需要跟着改。但代价是如果你通过二级索引查到了主键还想拿其他字段就得拿着主键再去聚簇索引里查一次这个动作就叫回表。回表不是免费的。每回表一次就是一次主键B树的随机IO。假设二级索引命中了1000行那最多可能要回表1000次。这时候性能瓶颈就从索引查找变成了随机IO次数。理解了回表你才能真正理解覆盖索引为什么是性能利器——如果查询的字段都已经被包含在二级索引的叶子节点里那查询压根不需要回表直接在二级索引上就拿到了全部数据速度自然快得多。2.3 一张表只能有一个聚簇索引但可以有多个二级索引有次我让一个新人解释为什么通过主键查询最快他回答说因为主键有索引。这个回答不能算错但没说到点子上。准确说法是主键对应的聚簇索引上叶子节点已经带着整行数据所以查找过程没有回表这一环。而二级索引无论怎么优化要是查的字段超出索引范围几乎免不了回表。这个认知上的差异直接影响你怎么设计索引。我见过很多开发者在经常查询的字段上建了一堆单列索引却完全没考虑通过联合索引去覆盖查询字段。后面第5节讲EXPLAIN时你就能看到一个Extra列出现Using index的查询有多爽那代表MySQL连数据文件都没碰纯靠索引就给出了答案。3. 联合索引的最左前缀原则什么时候能用、什么时候必然失效联合索引是性能优化里最常用也最容易被误解的工具。一句话版本联合索引 (a, b, c) 可以理解为先按a排序a相同再按b排序b相同再按c排序。它本质是一个排序后的列表所以你在查询时只有从最左边的列开始连续命中索引才能生效。这就是最左前缀原则。3.1 哪些组合能走索引假设有一张模拟订单表我们建立联合索引 (user_id, status, create_time)。下面这些查询都能用上这个索引WHERE user_id 100WHERE user_id 100 AND status 1WHERE user_id 100 AND status 1 AND create_time 2024-01-01WHERE user_id 100 ORDER BY status这些查询的共性是条件里包含了联合索引最左边的user_id并且后续条件列是连续往右走的。MySQL可以用B树的排序特性先定位user_id100的范围再在这个范围内定位status1的数据再进一步筛选create_time。3.2 哪些组合必然失效反过来下面这些情况就很尴尬WHERE status 1没有user_id打头索引根本没法用WHERE user_id 100 AND create_time 2024-01-01user_id能定位但跨过了中间的status直接查create_timecreate_time条件用不上索引WHERE create_time 2024-01-01 AND user_id 100写反了顺序也没用优化器不是按你写的顺序执行的但索引匹配依然要求从最左列开始连续匹配这里有个很多人容易忽略的点所谓最左前缀跟你在SQL里写条件的先后顺序没有关系。MySQL的优化器会做条件重排但能不能走索引取决于条件列是不是索引列的前缀序列而不是SQL里谁在前谁在后。3.3 范围条件之后索引就罢工了比最左前缀更容易忽略的坑是范围查询对联合索引的截断。比如 (a, b) 联合索引你执行 WHERE a 1 AND b 10 AND b 20这个b上的范围条件依然能用索引定位。但如果是 WHERE a 1 AND b 5那么a用上了索引做范围扫描b条件就只能在a筛选后的结果里再过滤索引对b这一列就罢工了。为什么因为联合索引是先按a排序再按b排序的一旦a是范围不精确等于那么在这个范围内b的排序信息是不具备全局有序性的。MySQL只能一页一页地把a1的数据读出来再逐行判断b5。这个现象叫范围之后的列无法使用索引。所以设计联合索引的时候一个非常实用的经验是把等值条件放在前面范围条件放在后面。这个原则不光影响SQL能不能走索引还直接影响索引结构是不是合理。我在实际设计索引时总是先问自己这个查询里哪些条件是等值、哪些是范围、哪些只是排序需求然后按等值列、范围列、排序列的顺序去组织联合索引的列。4. 实战里的索引失效高发区函数、隐式转换、模糊查询与OR即使你建的索引完全符合最左前缀原则现实中的SQL还是可能悄无声息地绕过索引。这一节列出的四个场景几乎每个项目里都能抓到几个反例。我挑的都是线上真实遇到过的写法看完建议你把公司代码仓库里的SQL拉出来自查一遍。4.1 在索引列上做函数操作索引直接报废有个模拟会员系统的查询让我印象很深SELECT * FROM member WHERE DATE(create_time) 2024-06-01;开发者觉得建了create_time索引就该快结果执行计划一出来typeALL全表扫描。原因很简单当你对索引列做了函数处理后索引里存储的原始值在函数变换后的结果面前完全失去了顺序性B树没法通过比较原始值来定位目标索引自然失效。正确写法应该是SELECT * FROM member WHERE create_time 2024-06-01 00:00:00 AND create_time 2024-06-02 00:00:00;改成范围条件之后create_time索引就能正常工作了。这个调整对范围查询的影响很小效果却是天壤之别。类似的坑还包括在索引列上做算术运算比如 WHERE age 1 20同样会让索引失效。尽量把运算移到等号右边。4.2 隐式类型转换字段是字符串传进去数字MySQL里有一类特别隐蔽的失效就是隐式类型转换。表里的phone字段是VARCHAR类型结果SQL写成SELECT * FROM user WHERE phone 13800138000;用户传的数字会被MySQL转成字符串再去跟索引比较吗恰恰相反在这个场景里MySQL会尝试把列的值转换成数字来比较于是索引列上相当于发生了一次隐式CAST操作索引直接失效。解决方式很简单传参加引号或者干脆在应用层保证类型一致SELECT * FROM user WHERE phone 13800138000;顺带说一句这种问题在ORM框架里更容易发生。有些框架的查询构造器不会帮你判断字段类型你传什么它就拼什么排查起来更头疼。所以该类型就写对类型比事后优化省心得多。4.3 前置模糊查询%号放在了最前面后缀模糊xxx%是可以走索引的比如 LIKE abc%因为B树可以按前缀范围定位。但前置模糊%abc%完全没法利用索引的排序特性因为你要找的内容不固定出现在字符串开头。-- 能走索引 SELECT * FROM article WHERE title LIKE MySQL%; -- 索引失效 SELECT * FROM article WHERE title LIKE %索引%;线上业务如果有大量前置模糊搜索的需求老实说模糊查询本身就不适合关系型数据库硬抗更好的选择是引入专业的全文检索引擎。如果数据量不大、还是想用MySQL可以考虑全文索引但要注意全文索引的语法、中文分词和性能表现都跟普通索引不太一样不是一个简单的替代方案。4.4 OR条件只要有一个字段没有索引整体就可能全表扫这是做得最让人无语的优化陷阱。比如SELECT * FROM order_info WHERE user_id 100 OR order_no A12345;如果只有user_id上有索引order_no上没有索引你以为MySQL会先分别查两个条件再合并吗在绝大多数版本和配置下优化器会选择直接全表扫描因为OR意味着任意一个条件成立就得返回而另一个条件没有索引可用那就干脆把所有行都扫一遍再过滤。解决思路通常是改成UNIONSELECT * FROM order_info WHERE user_id 100 UNION SELECT * FROM order_info WHERE order_no A12345;这样两边都能各自走索引。或者更干脆一点把order_no也加上索引让优化器有更多的执行路径可选。这里有个原则OR两侧的条件要么都建索引要么做好全表扫的心理预期。5. 用EXPLAIN给SQL做体检关键字段与执行计划的读法前面讲的都是面粉EXPLAIN是检查面粉状态的那台机器。我的习惯是任何一条线上跑了超过200毫秒的查询第一件事就是把它复制出来在前面加上EXPLAIN看执行计划。这比你拍脑袋猜是不是该加索引要靠谱得多。5.1 EXPLAIN基础用法与输出列概览EXPLAIN SELECT * FROM order_info WHERE user_id 100 AND create_time 2024-06-01\G执行之后你会看到一张表每一行代表一个查询步骤。最常见的几个列我按重要程度排一下列名核心作用我关注的点type访问类型从好到坏排序至少要让主查询达到range最好到ref或eq_refkey实际用到的索引名没索引会显示NULLrows预估扫描行数这个数字越大越危险Extra额外信息出现Using filesort或Using temporary要警惕5.2 type字段从好到坏的顺序type的值从优到劣大致是这个顺序system const eq_ref ref range index ALLconst用主键或唯一索引等值查询最多返回一行这是理论最快eq_ref被驱动表通过主键或唯一索引等值匹配多表JOIN时的理想状态ref普通二级索引等值匹配不要求唯一性range索引范围扫描比如BETWEEN、、、IN等index遍历整个索引树比全表稍好一点因为读的是紧凑的索引页ALL全表扫描性能最差是优化要消灭的对象你不需要背这个列表只需要记住一条看到range及以上的访问方式说明索引基本上用上了看到index和ALL就必须多想一下为什么。我自己写SQL的底线是单表查询必须达到range级别如果只能做到ALL回去改SQL或者补索引。5.3 Extra里那些值得警惕的信号Extra列是执行计划里信息量最大的地方它经常直接告诉你问题出在哪。Using index俗称覆盖索引查询的所有字段都从索引里取到了不用回表。这是我最喜欢看到的ExtraUsing where先读数据再过滤通常表示索引只帮了部分忙Using filesort文件排序意味着排序操作没有用到索引的排序特性要额外排序。看到这个就要考虑把ORDER BY字段纳入联合索引Using temporary用了临时表常见于GROUP BY、DISTINCT这类操作往往伴随性能问题举个例子模拟订单表上有联合索引(user_id, status, create_time)你执行SELECT user_id, status, create_time FROM order_info WHERE user_id 100 ORDER BY create_time DESC;如果Extra只显示Using index说明查询不仅走了覆盖索引排序也直接利用了索引的有序性连排序操作都省了。这就是一条教科书级的完美SQL。同样一条SQL如果你SELECT多加一个不包含在索引里的字段Extra就变成Using index condition; Using filesort回表加上排序性能立马掉档。6. 一个模拟慢查询案例的完整优化链路光讲理论容易飘我完整复盘一个代表性的优化过程。案例背景一张模拟日志表operation_log大约两千万行核心字段有id、user_id、op_type、create_time、remark。线上有个报表接口要查某个用户最近一周的操作记录SQL长这样SELECT id, user_id, op_type, create_time, remark FROM operation_log WHERE user_id 73821 AND create_time 2024-05-01 ORDER BY create_time DESC;这个查询在线上稳定跑2秒左右。我先EXPLAIN看了一眼type是ALLrows预估扫描了快两千万行Extra里还有Using filesort。整条SQL的问题一目了然全表扫描加手动排序慢得毫无悬念。6.1 第一步确认业务需求再看建什么索引优化之前先跟业务对齐报表查询的过滤条件是不是永远是user_id加时间范围排序是不是永远按create_time倒序如果是那这个查询的最优索引就是联合索引 (user_id, create_time)。等值条件user_id放前面范围条件create_time放后面排序字段create_time又恰好是索引的一部分排序也能顺带解决。执行建索引ALTER TABLE operation_log ADD INDEX idx_user_create (user_id, create_time);这个索引建完之后我再跑EXPLAINtype变成了refrows降到用户的操作记录数级别Extra里不再出现Using filesort查询直接降到几十毫秒。从2秒到几十毫秒本质上就是把扫描行数从两千万减到几百行。6.2 第二步检查SELECT的字段能不能做覆盖索引优化上一版虽然已经够快但还能再压一点。因为我查询里还带着remark字段而remark是文本字段不在索引里所以每命中一行都要回表取remark。如果报表接口其实不需要remark内容只是习惯性SELECT *那就改成只查必要的字段SELECT id, user_id, op_type, create_time FROM operation_log WHERE user_id 73821 AND create_time 2024-05-01 ORDER BY create_time DESC;这样查询字段完全落在联合索引 (user_id, create_time) 里Extra变成Using index连回表都省了。注意我为了说明问题把id也算进去了因为二级索引叶子节点本身就带着主键值id不需要额外回表。这就是覆盖索引的实战意义。6.3 第三步验证索引对写入的影响每次加索引之前都得问这个表写多读少还是读多写少operation_log的写入频率很高加索引意味着每次INSERT都要多维护一棵B树。好在 (user_id, create_time) 这两个列的联合索引数据量不大且写入是追加型的维护成本相对可控。我在测试环境压过写入性能同样批量插入场景加索引后写入耗时上升在可接受范围内这才敢上生产。这个案例里还有个细节旧业务可能还有按op_type单独筛选的报表那就要评估是复用联合索引还是再单独建一个(op_type, create_time)索引。索引要么不做做了就要围绕真实查询模式来设计不能为了有索引而建索引。7. 索引设计的取舍与线上教训冗余索引、区分度与维护成本最后这部分聊的是更高一层的问题索引怎么设计才不浪费、不冗余。我接手过不少因为索引设计随意导致的问题有冗余索引撑爆磁盘的有索引区分度太低等于没建的还有联合索引列顺序排错导致慢查询反复出现的。这些都值得单独拎出来说。7.1 谁的区分度高谁就该站在联合索引的前面联合索引的列顺序除了遵循等值优先、范围靠后的原则还要考虑区分度。区分度可以粗略理解为一列里不同值的比例比如性别列只有男、女两个值区分度极低user_id如果一亿用户基本不重复区分度就很高。为什么区分度高的列要放前面回到B树查找过程低区分度的列放前面会导致索引树的第一个层级就聚集了大量相同值需要在这个大范围里继续往下查扫描的中间节点更多。高区分度列放前面能更快地把扫描范围缩小。有人做过很直观的对比一个 (sex, user_id) 索引查某个性别下的指定用户可能要扫描一半的索引页换成 (user_id, sex)哪怕后面带一个低区分度的sex查询定位也能一步到位。所以写联合索引的时候把区分度高的列往左放收益明显。7.2 冗余索引看着很全其实每个都在拖慢写库冗余索引是线上数据库最常见的浪费。比如已经有了索引 (a, b)又单独建一个 (a)这个单列索引基本是废的因为任何能用 (a) 的查询都能用 (a, b) 的最左前缀替代。还有一个典型场景给order_no建了唯一索引又给order_no加一个普通索引后者完全多余。我查过一次某业务库的索引清单发现一张表上挂了十几个索引其中至少四五个是冗余的。每个索引都意味着额外的磁盘占用和写入维护成本这相当于给每次写操作都背上了一堆行李。清理冗余索引的标准做法是先用performance_schema或者慢查询日志统计真实查询模式确认没有SQL依赖某个索引之后再把它drop掉并且观察一段时间。7.3 索引列上避免过长的字段前缀索引与它的代价如果要对一个很长的VARCHAR列建索引比如文章内容或长备注整列索引会让索引页装不下几个键值树变高查询变慢。这时候可以选择前缀索引只对字段的前N个字符建索引ALTER TABLE article ADD INDEX idx_title_prefix (title(20));但要记住前缀索引在排序和覆盖索引上会打折扣因为它存的不是完整值。如果你发现某个查询因为加了前缀索引Extra里本来能用的Using index变成了回表那就要权衡前缀长度。我一般建议能用整列就别用前缀非用不可时选一个能在业务上区分绝大部分数据的前缀长度比如20到30个字符。7.4 线上教训一次因为低区分度索引引起的慢查询说一个我印象很深的案例模拟支付流水表上建了一个 (status, create_time) 联合索引。status字段只有0、1、2、3四个值区分度非常低。业务上的报表经常查status1且create_time在某一天的数据。执行计划显示虽然走了索引但扫描行数几乎等于全表四分之一因为status1的数据量太大了还要在这个大集合里继续过滤create_time。这种索引有效果吗有。但效果非常有限。后来我把索引调整成 (create_time, status)同一个查询的扫描行数直接减少了两个数量级。原因就是create_time区分度高能先把范围缩到一天再在这一天里筛status。所以你看索引列的顺序不是建了就行顺序选错索引的效率可能连预期的十分之一都达不到。7.5 维护索引不是一次性工作索引设计不是上线时建完就完事了。数据量在涨业务查询模式在变索引也要跟着演进。我个人的习惯是每季度做一次索引体检导出所有索引清单配合慢查询日志和EXPLAIN结果找出那些长时间没被用到的索引、因为数据分布变化而失效的索引以及新业务需求下缺失的索引综合评估后统一调整。这个过程不用太频繁但一定要坚持做。有一点特别提醒在大表上做ALTER TABLE加索引或删索引会锁表或者至少带来很大的IO压力务必选择业务低峰期操作并且先在测试环境验证。我见过有人直接在千万级大表上跑ALTER TABLE ADD INDEX结果把主库的CPU打到满载整个服务的写请求全部卡住。数据库优化的前提是先保证稳定再谈性能。回到开头说的那个两千万行的日志表优化完之后我还特意对比了一组数据同一查询在优化前后的执行时间差异接近四十倍。这不是什么玄学就是扫描行数从两千万降到了几百。MySQL索引与性能优化基础这件事本质上就是让你学会用最小的代价把扫描的数据量降到最低同时不为这个目标付出过高的写入成本。如果你现在正准备系统地整理自己的索引知识我的建议是从这三点入手第一开口之前先EXPLAIN让数据说话第二建联合索引先想查询模式等值在前范围在后区分度高的靠左第三每次加索引都问一句值不值——查询快了多少写入慢了多少磁盘多了多少。把这几个问题想清楚你基本就不会在索引上栽大跟头了。
