1. 索引到底是什么从一棵B树说起先说个扎心的事实很多写了三五年SQL的人对索引的理解还停留在查询慢就加索引这个层面。真到面试或线上排查被问到为什么加了索引还是慢为什么这个查询没用上索引就卡壳。我当年也是这样直到把索引的底层结构吃透才觉得这一块终于通了。索引本质上是一种数据结构目的是让数据库用更少的IO找到目标数据。MySQL默认的存储引擎InnoDB用的是B树不是二叉树也不是哈希表。为什么偏偏是B树你可以把磁盘想象成一个很慢的仓库每次取货IO都要花固定时间B树的核心优势就在于矮胖——树的高度低意味着查询时从根节点走到叶子节点需要的IO次数少。一棵三层的B树轻轻松松存上千万行数据意味着三次IO就能定位到数据。1.1 为什么不是哈希索引哈希索引的查询速度是O(1)理论上比B树的O(logN)快得多。但它有两个致命短板不支持范围查询也不支持排序。你写WHERE age 20这种条件哈希索引直接歇菜。而B树的叶子节点本身就是有序的双向链表范围查询只需要找到起点然后顺序扫过去。这也是InnoDB主键索引天然支持范围查找的原因。1.2 聚簇索引与非聚簇索引的本质区别InnoDB里每张表只有一个聚簇索引通常就是主键索引。聚簇索引的叶子节点存的是整行数据也就是说表数据本身就是按主键顺序组织的。你现在应该能get到一件事为什么InnoDB建表强烈建议用自增主键因为新数据的物理位置直接追加在末尾不需要频繁移动已有数据如果用UUID这种随机值做主键每次插入都可能引发页分裂写性能会明显下降。非聚簇索引也叫二级索引、辅助索引的叶子节点存的是索引列的值加上主键值不存完整行数据。所以走二级索引查询时往往需要回表——先通过索引找到主键再拿着主键去聚簇索引里取完整行。这就是为什么不是所有索引都能让你的查询起飞。1.3 回表索引查询中的隐形开销我见过最典型的例子一张订单表几十万行用户在order_no上建了索引然后执行SELECT * FROM orders WHERE order_no 20240915001。从执行计划看确实用了索引key字段显示order_no索引但实际响应时间没快多少。原因就是每条记录都要回表几十万次回表就是几十万次随机IO。怎么避免后面讲覆盖索引的时候细说。你现在只需要记住一个判断标准二级索引查到主键后还要回表拿数据如果查询的字段全都在索引里就完全不需要回表——这是所有索引优化的核心思路之一。2. 索引的完整分类主键、唯一、普通、全文、复合MySQL里的索引类型不少很多人只知道个大概。我建议你按索引的约束作用和索引的字段个数两个维度去理解这样分类就不会乱。2.1 主键索引不只是唯一那么简单主键索引有两大特性非空且唯一。它不是普通的唯一索引因为它就是聚簇索引本身。建表时可以显式声明CREATE TABLE users ( id BIGINT NOT NULL AUTO_INCREMENT, username VARCHAR(50) NOT NULL, PRIMARY KEY (id) ) ENGINEInnoDB;注意一个容易被忽略的点如果表没有显式定义主键InnoDB会找第一个非空唯一索引当聚簇索引如果也没有就生成一个隐藏的GEN_CLUST_INDEX。这会导致行物理组织方式完全不可控所以千万别偷懒不设主键。2.2 普通索引与唯一索引性能差距比你想的小普通索引INDEX和唯一索引UNIQUE INDEX的查询性能其实相差很小。唯一索引多做的一次判断是当前值是否已存在在查询路径上影响微乎其微。真正需要考虑的是写入侧唯一索引在插入时要额外做一次重复校验批量写入场景下会多一次索引查找。所以如果你的业务字段天然需要唯一约束手机号、身份证号、订单号直接建唯一索引不用纠结那点性能损耗如果只是加速查询用普通索引就够。-- 普通索引 ALTER TABLE users ADD INDEX idx_username (username); -- 唯一索引 ALTER TABLE users ADD UNIQUE INDEX uk_mobile (mobile);2.3 全文索引别把它当搜索解决方案全文索引适合对文本内容做关键词匹配但MySQL自带的全文索引在中文分词上比较弱现实里我基本不推荐用它做C端搜索。真要做中文全文检索Elasticsearch、Milvus这类专属方案要好得多。MySQL全文索引更适合日志表里的简单英文关键词过滤或者数据量不大、期望快速迭代的轻量场景。全文索引是倒排索引结构语法上要用MATCH...AGAINSTALTER TABLE articles ADD FULLTEXT INDEX ft_content (content); SELECT * FROM articles WHERE MATCH(content) AGAINST(数据库 IN NATURAL LANGUAGE MODE);2.4 复合索引最左前缀原则是绕不开的坎复合索引就是多个字段联合建一个索引。它最大的价值不是替代多个单列索引而是能在一个索引里同时处理多个等值/范围条件减少索引数量。但代价是它严格遵循最左前缀原则查询条件必须从索引的最左列开始连续匹配索引才会生效。举个例子建了idx_city_age (city, age)-- 生效city等值 age范围 SELECT * FROM users WHERE city 杭州 AND age 25; -- 生效只用city部分 SELECT * FROM users WHERE city 杭州; -- 不生效跳过了city直接用age SELECT * FROM users WHERE age 25;这段逻辑我在面试里问了无数次能答上来的人不到一半。最左前缀不是MySQL的bug而是B树对有序复合键的自然约束——先按city排序city相同时再按age排序想直接按age查询时age的顺序在全局是无序的。3. 索引失效的常见场景与根因分析聊完了分类重点来了。最让开发头疼的问题就是明明建了索引执行计划却显示没走。索引失效这四个字背后是复杂的SQL写法问题。我按实际踩坑频率给你捋一遍。3.1 违反最左前缀原则这个在上面已经提过。实际开发里最常见的错误是在复合索引idx_status_created (status, created_at)上直接写WHERE created_at BETWEEN ... AND ...。你的本意是查某段时间的订单但跳过了第一列status索引直接废掉。这不是MySQL笨而是符合B树排序逻辑的必然结果。3.2 函数操作与隐式类型转换索引列不能动这条必须划重点对索引列使用函数或表达式计算索引就会失效。因为MySQL无法直接利用索引列原始值的有序性去匹配计算结果。-- 失效对created_at用了DATE()函数 SELECT * FROM orders WHERE DATE(created_at) 2024-09-15; -- 生效直接写成范围条件 SELECT * FROM orders WHERE created_at 2024-09-15 00:00:00 AND created_at 2024-09-16 00:00:00;隐式类型转换更隐蔽。比如mobile字段是varchar类型你写WHERE mobile 13800138000MySQL会把字符串转成数字再比较等于给mobile套了层类型转换索引照样失效。3.3 LIKE查询和范围查询的陷阱LIKE %abc和LIKE %abc%走不了索引因为B树只能利用前缀匹配的有序性但LIKE abc%是可以走索引的。这个基础大家应该都知道但很多人没意识到范围查询的另一层含义-- 复合索引 idx_price_discount (price, discount) SELECT * FROM products WHERE price 100 AND discount 0.8;这个SQL里price用上了索引但discount不会用。因为price是范围条件范围右侧的列索引失效。这是最左前缀原则在范围查询下的延伸规则范围条件之后的列无法继续走索引。3.4 统计信息过期索引失效的隐形杀手这个坑很多人没意识到。优化器决定是否走索引靠的是表的统计信息包括行数、区分度、索引基数等。如果统计信息长期不更新优化器可能觉得全表扫描更快哪怕你建了完美的索引。解决方案是定期执行ANALYZE TABLEANALYZE TABLE orders;在数据量变化频繁的表上我习惯把它加进夜间维护任务里。另外小表全表扫描可能确实比走索引快这也是优化器的正常判断不代表索引坏了。4. 如何设计一套靠谱的索引联合索引与覆盖索引实战讲完失效场景再说怎么正向地设计索引。这是我做性能调优时最花时间的部分核心原则可以总结成一句话从业务查询出发设计最少数量的索引覆盖最核心的查询路径。4.1 索引设计流程先列查询再谈索引我通常按三步走把核心业务的查询SQL全部列出来包括等值条件、范围条件、排序字段、分组字段。分析每个查询的访问模式确定哪些字段适合建索引区分度高、查询频繁。设计复合索引核心原则是等值条件放前面范围条件放后面排序字段考虑能否命中索引。比如一个订单分页场景SELECT id, order_no, amount, status FROM orders WHERE user_id 123 AND status PAID ORDER BY created_at DESC LIMIT 20;推荐索引是idx_user_status_created (user_id, status, created_at)。user_id等值放左边status等值放中间created_at既承担范围/排序职责放最后。注意索引里的列顺序不能拍脑袋要对着SQL的实际条件逐个排。4.2 覆盖索引最被低估的优化手段前面说的回表问题最优雅的解法就是覆盖索引。如果查询的字段全部包含在索引里InnoDB就不用回表直接在二级索引的叶子节点上拿到结果。这能省掉几十倍甚至上百倍的随机IO。-- 假设 idx_user_created (user_id, created_at) SELECT user_id, created_at FROM orders WHERE user_id 123;这个查询的user_id和created_at都在索引里覆盖索引直接命中执行计划里Extra会显示Using index。注意这不是建索引的目的本身而是设计索引时的额外红利——在满足查询条件的前提下尽量把常用查询列“塞进”索引减少回表。但别走极端为了全覆盖把所有字段都塞进索引索引体积会膨胀写入性能惨不忍睹。覆盖索引适合高频查询且字段较少的场景。4.3 索引下推MySQL 5.6之后的隐形加速器索引下推Index Condition PushdownICP是5.6引入的优化理解它对排查为什么Extra里有Using index condition很有帮助。以前走二级索引查询时存储引擎把索引命中的记录一条条回表再在服务层过滤其他条件。有了ICP之后能在索引遍历过程中直接过滤掉不符合其他条件的记录减少回表次数。例子复合索引idx_city_age (city, age)执行SELECT * FROM users WHERE city 杭州 AND age 20;ICP会让存储引擎在索引层同时用city和age过滤只有完全符合的记录才回表。你看到Extra里的Using index condition说明这个优化已经自动生效了不用手动干预。4.4 一个真实的调优案例之前接手过一个线上慢查询交易记录表接近两千万行。原始SQL长这样SELECT order_id, pay_time, amount FROM payments WHERE merchant_id 88001 AND pay_time 2024-06-01 ORDER BY pay_time DESC LIMIT 50;原表只有主键索引每次查询扫全表平均耗时三秒多。我加了一个复合索引idx_merchant_paytime (merchant_id, pay_time)同样查询降到30毫秒以内。关键点有两个merchant_id等值条件放前面pay_time既是范围条件又是排序字段两者在一个索引里同时解决了过滤和排序连filesort都省了。还有个细节pay_time上建独立的单列索引同样能让查询不走全表但排序还是要filesort性能差一个量级。这就是复合索引比单列索引优势最直观的证明。5. 索引调优与维护从EXPLAIN到线上实战建立索引只是开始真正考验功力的是怎么验证索引效果、怎么维护索引、怎么避免过度索引。5.1 EXPLAIN 是索引优化的第一工具调优时我几乎每条核心SQL都会跑一遍EXPLAIN重点看几个字段字段关注点typeconst eq_ref ref range index ALL至少要达到range最好到refkey实际用到的索引为NULL说明没走索引rows预估扫描行数越小越好ExtraUsing index代表覆盖索引Using filesort代表需要额外排序Using temporary是性能杀手举例来说EXPLAIN SELECT id, order_no FROM orders WHERE user_id 123;如果type显示ref、rows只有几百行、Extra没有Using filesort这个查询基本可以放心交给线上。如果type是ALL哪怕rows只有一万也要警惕数据涨上去之后的退化风险。每次优化完别忘了对比rows的前后变化这是最直观的优化效果验证。5.2 索引碎片与统计信息维护工作不能停索引不是建完就不管了。频繁的增删改会让索引页产生碎片导致索引的逻辑顺序和物理存储顺序不一致范围扫描的效率打折。解决方案是重建索引或执行OPTIMIZE TABLEALTER TABLE orders DROP INDEX idx_user, ADD INDEX idx_user (user_id);对于核心大表建议安排在业务低峰期做因为OPTIMIZE TABLE会锁表。同时别忘了定期ANALYZE TABLE更新统计信息这是优化器正确选索引的前提。5.3 索引数量不是越多越好写入放缓你算过吗每次INSERT、UPDATE、DELETEMySQL都要同步维护所有索引。你每多建一个索引写入路径就多一份开销尤其是高频写入的表几个不常用的索引可能让写入性能掉一大截。我见过一张表上堆了12个索引仔细一看好几个查询模式重叠完全能合并。最后精简到5个写入快了约30%查询一条没慢。清理思路很粗暴查慢日志找出真正高频的查询把索引按照查询模式合并从不用的直接删。5.4 高频面试题背后的真实考点最后聊聊面试。MySQL索引是面试必考但很多人只背答案不懂原理。这里给出几个我面试候选人也常用的问题方向为什么最左前缀原则成立答到B树的有序性就够。覆盖索引和回表的关系答到索引包含所有查询列就不回表。为什么范围查询右边的列失效还是因为有序性——范围条件破坏了后续列的有序匹配。主键用自增还是UUID本质是聚簇索引的物理写入顺序和页分裂问题。我个人面试时的习惯是先听对方讲概念再看对方有没有线上事故经验。比如遇到过索引失效怎么排查、怎么用EXPLAIN定位慢查询。能讲出真实排查链路的人比只会背概念的人靠谱得多。最后再分享一个实操技巧每张表的核心查询建议控制在两三个以内为它们单独设计复合索引日常排查慢查询日志时重点关注Rows_examined远大于实际返回行数的SQL这类SQL往往是索引设计不合理的重灾区。索引这套东西理解了底层原理之后剩下的就是反复看执行计划和真实数据打磨手感。