数据库跑得慢十次里有八次是索引没设计对。这不是夸张是我接手过的几个业务系统里最常见的共性。一条SQL从几秒钟优化到几十毫秒通常不是靠调参数而是靠一个合适的索引。反过来索引建错了写入变慢、磁盘膨胀甚至把本该走索引的查询带偏都是家常便饭。这篇东西我不打算给你抄官方文档而是把索引从原理到实战、从创建到排查讲成一个可以照着用的完整思路。包括B树为什么能扛住千万级数据、聚簇索引和非聚簇索引到底差在哪、联合索引的最左前缀怎么用、哪些写法会让索引原地失效以及我实际处理慢查询时常用的排查路径和几个印象深刻的坑。适合谁看刚接触MySQL、被索引概念绕晕的新手写过一些SQL但对执行计划没把握的开发者以及准备面试想系统过一遍索引高频知识点的同学。看完你能做到三件事能判断一个表该建哪些索引能解释一条SQL为什么慢能在生产环境里安全地加索引。1. 索引的本质为什么B树能扛住千万级数据1.1 用翻字典的方式理解索引想象一本新华字典没有拼音目录也没有部首检字表你想找一个字只能从第一页翻到最后一页。全表扫描就是这个感觉MySQL把整个表的数据页从头到尾读一遍一行一行比对条件。数据量小的时候没感觉表里有个几百万行一次全表扫描就会把磁盘IO打满。索引本质上就是那本字典的“拼音检字表”。它把某一列或某几列的值提取出来按照一定规则排好序额外存储一份结构查询的时候先在这个结构里快速定位再根据定位到的位置去拿原始数据。这个“有序”很关键因为有序结构才能做二分查找一类的快速定位。数据库里最常用的索引结构是B树不是二叉树也不是哈希表。哈希索引能做精确等值匹配O(1)的复杂度看着很美但你一旦做范围查询比如大于、小于、between哈希结构就完全帮不上忙。二叉树的层数又太深千万级数据下树高可能有二十多层每层都是一次磁盘IO性能扛不住。B树是矮胖的多路搜索树。它的特点包括所有数据都挂在叶子节点叶子节点之间用指针串联非叶子节点只存索引键值不存数据。一次查询从根节点走到叶子节点层数一般在三到四层意味着即使数据量上千万也只需要三四次磁盘IO就能定位到目标数据这就是它被InnoDB选为主索引结构的根本原因。1.2 聚簇索引与非聚簇索引数据到底存哪InnoDB的索引和数据是放在一起的这叫聚簇索引。表里的主键索引就是聚簇索引它的叶子节点直接存放整行数据。也就是说你按主键查数据B树查到最后拿到的就是完整的一行不需要再回表这也是为什么InnoDB要求每个表必须有主键没有主键时它会自动选一个唯一列再没有就隐式生成一个主键列。非聚簇索引也叫二级索引或辅助索引它的叶子节点存的是索引列的值加上主键值。比如你在name列上建了一个普通索引这个索引的叶子节点存的是“名字主键id”。查询的时候如果只需要这两个字段直接从二级索引就能拿到不需要再回表但如果要查其他字段就必须拿着主键id再回到聚簇索引里查一次完整数据这个过程叫回表。回表不是免费午餐。每回一次表就是一次随机IO数据量一大性能损耗非常明显。所以我常说能用覆盖索引解决的就别查多余字段。所谓覆盖索引就是查询需要的所有列都包含在同一个二级索引里查询引擎在索引树上就能拿到全部数据连回表都省了。注意MyISAM和InnoDB在这点上差别很大。MyISAM的索引和数据是分开的索引叶子存的是数据行的物理地址无论主键索引还是普通索引都不存放整行数据所以MyISAM没有聚簇索引的概念。你现在新建表默认都是InnoDB但遇到老库的时候要能区分这两个引擎。2. 索引类型选型该建哪些索引才能稳准狠2.1 主键索引、唯一索引、普通索引怎么选很多新手一上来就给所有经常查询的列都建索引结果索引建了一大堆写入慢、磁盘占用高查询也没快多少。选类型之前先搞清楚它们各自的约束。主键索引是聚簇索引一个表只能有一个通常用自增id或者业务上唯一且稳定的字段。它天然不允许null约束最为严格。唯一索引允许null值但同一列除null外不能出现重复值它在约束数据完整性之外还能帮优化器更准确地估算行数查询该列时选择性很高。普通索引不约束重复只负责加速查询建得太多就是负担。实践里我的选型逻辑很明确先找出查询的where条件里出现频率最高的列再看看这些列是否需要唯一性约束如果能接受重复值就建普通索引需要防重复就建唯一索引。比如用户手机号在业务上绝对唯一那就应该建唯一索引而不是普通索引既能保证数据质量又顺带做了查询加速。如果只是商品分类ID这种会出现大量重复值的字段建普通索引就够了。2.2 联合索引和最左前缀原则单列索引解决的是单列查询但实际业务查询条件经常是多个字段组合的比如按“城市 状态 创建时间”筛选订单。这种情况你有两种选择建三个单列索引或建一个三列联合索引。绝大多数场景下应该选后者。联合索引的底层依然是B树只是索引键变成了多个列的有序组合先按第一个列排序第一列相同再按第二列排序以此类推。这带来一个非常重要的规则查询必须从最左侧的列开始匹配跳过最左列直接用第二列索引就用不上。这就是最左前缀原则。建联合索引前先想清楚查询模式。比如订单表上高频查询是“按城市和状态筛列表”同时还有个低频场景是“只按状态统计”那么建(city, status)这个联合索引时“只按status”的查询虽然不能直接用整个索引但如果把status放在前面就能覆盖这两种情况。实际上联合索引(b, a)能同时服务“a单独查”和“a和b联合查”反之则不能。所以列顺序安排是联合索引设计的核心要反复权衡高频查询的匹配条件放在最左侧。2.3 回表、覆盖索引和索引下推联合索引还有一个隐藏收益通过包含更多查询需要用到的列来形成覆盖索引省掉回表。比如订单表最常见的查询是select id, city, status from orders where city 北京这时如果建的是(city, status)联合索引id本来就是聚簇索引键的一部分整个查询所需字段都能在二级索引树里找到零回表查询效率是最优的。MySQL 5.6以后还有个优化叫索引下推英文Index Condition Pushdown简称ICP。它允许在索引遍历过程中直接对索引包含的字段做where过滤只有满足条件才回表取完整数据。举个具体例子联合索引(city, status)查询条件where city北京 and status1如果没有ICPMySQL会先在索引里找到所有城市为北京的记录然后一条条回表再在完整行上过滤status。有了ICPstatus这个判断在遍历索引时就完成了回表次数大幅减少。判断一条SQL是否用上了覆盖索引和下推最好的工具是执行计划。下面马上讲怎么建索引、怎么看执行计划这些概念会串起来。3. 实操索引的创建方法与执行计划解读3.1 建索引的标准姿势和参数细节先看最基本的建索引语法我以一张常见的订单表为例CREATE TABLE orders ( id bigint NOT NULL AUTO_INCREMENT, order_no varchar(64) NOT NULL, user_id bigint NOT NULL, city varchar(32) NOT NULL, status tinyint NOT NULL, amount decimal(10,2) NOT NULL, created_at datetime NOT NULL, PRIMARY KEY (id), UNIQUE KEY uk_order_no (order_no), KEY idx_city_status (city, status) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;这张表里已经有了三种典型索引主键索引约束ID唯一索引约束订单号并加速订单号查询联合索引加速“城市状态”的组合筛选。这样的设计满足了大部分查询场景。如果表已经存在单独加索引用ALTER TABLE或者CREATE INDEXALTER TABLE orders ADD INDEX idx_user_created (user_id, created_at); CREATE INDEX idx_amount ON orders(amount);生产环境加索引要特别注意锁表问题。MySQL 5.6之前加索引默认会锁住整张表大表加索引期间写入直接堵塞。5.6之后可以用在线DDL语法上多了ALGORITHM和LOCK选项比如ALTER TABLE orders ADD INDEX idx_user_created (user_id, created_at), ALGORITHMINPLACE, LOCKNONE;在官方支持的前提下INPLACE算法加LOCKNONE允许DDL期间继续读写但并发的写操作也有自身开销并且大表上的索引创建过程依然可能很长。建议任何线上加索引都要放在业务低峰期并且先在测试库执行一遍记录耗时和影响。3.2 EXPLAIN执行计划一条SQL慢不慢答案全在这建完索引到底有没有生效SQL走的是哪个索引、扫描了多少行、有没有回表EXPLAIN能直接回答。我最常用的查看方式是EXPLAIN加完整SQLEXPLAIN SELECT id, city, status FROM orders WHERE city 北京 AND status 1;执行结果里的几个关键列要重点看type访问类型。从好到差依次是system、const、eq_ref、ref、range、index、ALL。看到ALL就要警醒这是全表扫描。看到index说明索引全扫虽然用了索引但效率未必高。ref和range是比较健康的范围查询。key实际使用的索引名。如果是NULL说明没走任何索引。key_len索引使用的字节数。这个值能看出联合索引到底用到了几个列数值越大说明用到的列越多。rows预估扫描的行数。这个数字越小越好。Extra额外信息。出现Using index说明是覆盖索引出现Using filesort说明排序没走索引需要优化Using temporary更是要避免的临时表操作。我实际操作中对一条SQL的体检顺序是先看type是不是ALL再看key是不是NULL然后看rows预估量最后看Extra里有没有Using filesort和Using temporary。这四个地方都没问题了这条SQL基本就健康了。3.3 索引长度key_len的计算方法能看懂key_len你才能真正判断联合索引的利用程度。key_len的计算公式取决于列类型和字符集。以utf8mb4为例每个字符占4个字节。varchar类型还要额外加2个字节记录长度允许NULL的列加1个字节。假设字段是这样定义的city varchar(32) NOT NULL在utf8mb4下单列索引的key_len 32*4 2 130。如果联合索引是(city, status)status tinyint占1个字节NOT NULL不加字节那么key_len 130 1 131。执行计划里如果key_len显示130说明只用了city这一列显示131说明city和status都用上了。这是排查联合索引生效情况的绝招。很多面试官问“联合索引到底用了几个列”其实就是在考key_len的计算。3.4 索引字段长度别贪多前缀索引怎么用如果一个字段很长比如存文章正文、URL地址这种Text类型或超长Varchar给整个字段建索引会非常臃肿B树的每个节点能存放的索引键数量急剧减少树变高查询效率反而下降。这时候可以只取字段的前若干字符建立索引叫前缀索引。ALTER TABLE articles ADD INDEX idx_title_prefix (title(20));前缀索引的缺点是可能遇到重复值选择性降低。所谓选择性可以理解为“这一列里不同值的比例”。比例越高索引区分度越好。选前缀长度时我一般先用SQL统计对比看不同前缀长度下的distinct数量变化找一个既能控制长度又能保持区分度的值。注意前缀索引无法用于覆盖索引因为索引里只存了前缀而不是完整值。查询需要完整字段时只能回表。4. 索引失效的场景与排查经验索引不是建了就万事大吉。我见过太多开发把索引建好了但一条SQL因为写法问题硬是让索引废掉性能图标直接拉满。这部分的坑我在生产环境里基本都踩过一遍逐个说。4.1 函数运算和隐式类型转换在索引列上做函数操作索引必然失效。典型的例子SELECT * FROM orders WHERE DATE(created_at) 2025-01-01;created_at列上就算有索引DATE函数把它包裹之后MySQL无法直接使用索引排序好的值进行查找只能全表扫描。正确的写法是范围查询SELECT * FROM orders WHERE created_at 2025-01-01 00:00:00 AND created_at 2025-01-02 00:00:00;隐式类型转换是更隐蔽的问题。比如user_id是varchar类型但查询时传入了数字SELECT * FROM users WHERE user_id 123456;MySQL会把varchar的user_id转成数字再比较索引列上发生了类型转换索引失效。这类问题在排查慢查询时经常发现SQL看表面没问题执行计划却是ALL。解决方式就是保持类型一致参数类型和字段类型相同。4.2 like模糊匹配和not in的坑最经典的坑是like。以通配符开头的模糊查询无法使用索引这基本是共识了SELECT * FROM users WHERE name LIKE %张%; -- 索引失效 SELECT * FROM users WHERE name LIKE 张%; -- 前缀匹配索引可用这里有一个延伸技巧如果确实需要做包含匹配并且数据量不大可以维持原样如果数据量大到无法接受全表扫描常规方案就是改用全文索引或引入专用搜索组件。不要把精力花在想办法让%张%走索引上方向就错了。not in和not exists是另一个常见坑。优化器通常认为全表扫描比用索引做反向匹配更划算所以大概率不走索引。改为left join加is null或者用exists改写往往效果更好。当然现在的MySQL版本优化器有时候会聪明地转换成anti join但不要依赖这个实际跑一下EXPLAIN最靠谱。4.3 联合索引列顺序写错联合索引最怕WHERE条件里没有最左列。比如索引是(city, status)但查询是SELECT * FROM orders WHERE status 1 AND amount 100;status不在联合索引的最左列而且amount也不在索引中这个查询大概率全表扫描。这种情况要么把status当最左列重新设计联合索引要么为status单建索引。最左前缀原则是铁的纪律设计时就要考虑所有可能出现的高频查询组合。4.4 order by、group by和distinct的隐性陷阱排序和分组同样可以使用索引但条件很苛刻排序顺序必须和联合索引列顺序一致且排序方向要和索引定义一致。以(city, status)为例下面这几种写法就可能触发Using filesortSELECT * FROM orders WHERE city 北京 ORDER BY amount; -- amount不在索引里 SELECT * FROM orders ORDER BY status, city; -- 列顺序和索引不一致Using filesort意味着MySQL要把查询结果放进一块内存或磁盘空间自行排序数据量大时耗时极高。优化方法要么调整索引列顺序以匹配排序需求要么把排序列加进索引。但这里要平衡新索引能不能覆盖其他查询不能让索引无限膨胀。group by和distinct本质上是先排序再分组走不上索引就会产生临时表。Extra里出现Using temporary时先检查group by和order by的列是否构成联合索引的前缀。5. 性能调优实战从定位慢查询到安全上线5.1 用慢查询日志圈定问题SQL与其盲目猜慢在哪不如让MySQL自己告诉你。打开慢查询日志把执行时间超过阈值的SQL记录下来SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1; SET GLOBAL log_queries_not_using_indexes ON;执行时间超过1秒且没走索引的SQL都会被记录下来日志文件默认在数据目录下的hostname-slow.log。拿到日志后分析哪些SQL出现频率最高把top N拿出来EXPLAIN。我处理生产经验是80%的性能问题集中在20%的慢SQL上优化这部分就能解决大多数隐患。5.2 大表加索引的完整操作方案大表加索引不能上来就执行ALTER TABLE风险太大。我的标准操作流程是这样在测试环境导出同样的表结构和数据模拟执行加索引记录耗时。检查线上磁盘空间。InnoDB加索引会重建表空间开销大概是原表大小的一倍多磁盘不够直接失败。选择业务低峰期执行先用SHOW PROCESSLIST确认当前没有长时间运行的事务。用在线DDL语法执行同时监控主从延迟。如果是从库也配置了一致的索引主库加索引引起的重放操作会让从库延迟变大。加完后跑一遍核心查询的EXPLAIN确认走了新索引。有一次给一张两千万行的订单表加联合索引在测试环境执行了40分钟磁盘占用翻了1.5倍。临时决定把操作推迟到凌晨执行并提前清理了30%的磁盘空间才顺利跑完。所以大表DDL前必须把空间和时长都测算清楚。5.3 冗余索引的排查与清理索引不是越多越好多出来的索引除了浪费磁盘还会拖慢写入。每次insert和update都要同步维护所有索引树。清理冗余索引是性能调优里投入产出比很高的一件事。最常见的是重复索引同列建了普通索引又建了唯一索引比如idx_user_id和uk_user_id同时存在。另一种是联合索引的左前缀重复已有(city, status)索引又单独建一个city索引后者就是冗余。因为(city, status)已经能覆盖单独查city的场景。排查冗余索引用information_schemaSELECT table_name, index_name, GROUP_CONCAT(column_name ORDER BY seq_in_index) AS cols FROM information_schema.statistics WHERE table_schema your_db GROUP BY table_name, index_name;拿到所有索引后比对列组合删除完全冗余的索引。删除索引用DROP INDEX这在InnoDB里也是在线操作但同样要低峰期执行。5.4 一个真实的优化案例说个我实际处理的例子。业务反馈订单列表页越翻越慢最严重的接口耗时到了4.8秒。定位后发现慢SQL长这样SELECT id, order_no, user_id, amount, status FROM orders WHERE city 上海 AND status IN (1, 2, 3) ORDER BY created_at DESC LIMIT 20;表结构里原有的索引是city单列索引和created_at单列索引。执行计划显示SQL先走city索引找到约20万行上海订单然后回表过滤status最后把所有匹配行进行filesort排序再取前20条。问题就出在回表和filesort上。我的优化方案是把索引改成三列联合索引ALTER TABLE orders ADD INDEX idx_city_status_created (city, status, created_at);改成这个索引后city和status在索引树中就能完成过滤created_at天然有序排序也不需要filesort。这条SQL的执行时间从4.8秒降到30毫秒左右效果立竿见影。这个案例特别能说明联合索引的设计价值——把查询、过滤、排序三方面需求一次性打包进索引里。6. 面试高频问题和常见问题排查速查6.1 高频面试题怎么答索引这块是MySQL面试的必考点我把几个高频问题的答题思路整理成表每个问题背后都有一个考点面试题核心考点建议回答框架为什么InnoDB用B树不用哈希表索引结构选型哈希支持等值但无法范围查询B树有序且树高固定磁盘IO次数少聚簇索引和二级索引的区别索引存储结构聚簇索引叶子存整行二级索引叶子存索引值和主键需要回表联合索引最左前缀具体指什么联合索引原理索引按列顺序逐层排列查询必须包含最左列且匹配顺序一致索引失效场景有哪些索引使用的边界函数运算、隐式转换、like前导通配、最左列缺失、排序不一致等什么情况下要回表怎么避免覆盖索引二级索引无法覆盖查询字段时就回表用联合索引包含查询字段可避免面试官如果让你现场分析一个SQL为什么慢一定要养成先EXPLAIN的习惯。从type、key、rows、Extra入手快速定位是全表扫描、回表太多、还是filesort这比背概念有说服力得多。6.2 日常问题排查速查表把自己遇到的常见问题整理成速查表排查问题的时候看一眼效率很高症状可能原因排查方向查询越来越慢数据量没涨多少索引失效或统计信息失真EXPLAIN看key列和rows回表次数是否异常写入很慢磁盘IO高索引太多每个索引都要维护用information_schema查冗余索引并清理SQL走了索引还是慢回表次数太多或rows估算高检查是否覆盖索引考虑联合索引优化order by字段导致filesort排序字段不在索引里或顺序不一致调整联合索引包含排序字段某列选择性很高但没建索引低基数误判实际过滤效果差算distinct比例高选择性列必须建索引主从延迟在加索引后飙升大表DDL引起从库回放开销低峰期执行评估并行复制参数我在实际项目里还有个习惯定期把线上数据库的慢查询日志和分析脚本跑一遍把执行计划里出现ALL和Using filesort的SQL统一收集下来每周花半小时评审一次。这种制度化的巡检比临时救火效果好得多很多问题在变成线上事故之前就能被拦下来。6.3 关于索引统计信息容易忽略的细节还有一个容易忽略但实际影响很大的点MySQL优化器选索引时依赖的是索引的统计信息而不是实时数据。当你表数据量发生大幅变化比如大批量删数据后又灌入大量新数据统计信息可能过期优化器会选错索引。解决办法很简单执行ANALYZE TABLE手动更新统计信息ANALYZE TABLE orders;这点在面试里问的人不多但实际生产中真的会因为统计信息不准导致一条慢SQL突然出现。排查时候如果发现EXPLAIN显示优化器选的索引明显不对比如明明有更好的索引偏不走可以先ANALYZE TABLE再看效果。最后再分享一点我的个人体会处理索引问题这些年最深的感悟是索引设计没有银弹每个方案都是在读取速度、写入成本、磁盘占用之间做权衡。不要迷信“索引越多越好”也不要因为怕麻烦就只建主键索引。最好的做法是先把高频查询梳理清楚理解业务真正的访问模式再针对性地设计联合索引让每个索引都有明确的“服务对象”。另外一个小技巧每次上线新的索引之后过几天回头看一眼慢查询日志和索引的使用情况确认真实效果必要时候用SHOW INDEX FROM table查看索引基数。新的索引长期没被使用说明它没有命中核心查询该考虑优化或删掉这套“设计—上线—复盘”的循环比什么高级理论都管用。 MySQL索引优化全解：从B+树原理到实战排查
数据库跑得慢十次里有八次是索引没设计对。这不是夸张是我接手过的几个业务系统里最常见的共性。一条SQL从几秒钟优化到几十毫秒通常不是靠调参数而是靠一个合适的索引。反过来索引建错了写入变慢、磁盘膨胀甚至把本该走索引的查询带偏都是家常便饭。这篇东西我不打算给你抄官方文档而是把索引从原理到实战、从创建到排查讲成一个可以照着用的完整思路。包括B树为什么能扛住千万级数据、聚簇索引和非聚簇索引到底差在哪、联合索引的最左前缀怎么用、哪些写法会让索引原地失效以及我实际处理慢查询时常用的排查路径和几个印象深刻的坑。适合谁看刚接触MySQL、被索引概念绕晕的新手写过一些SQL但对执行计划没把握的开发者以及准备面试想系统过一遍索引高频知识点的同学。看完你能做到三件事能判断一个表该建哪些索引能解释一条SQL为什么慢能在生产环境里安全地加索引。1. 索引的本质为什么B树能扛住千万级数据1.1 用翻字典的方式理解索引想象一本新华字典没有拼音目录也没有部首检字表你想找一个字只能从第一页翻到最后一页。全表扫描就是这个感觉MySQL把整个表的数据页从头到尾读一遍一行一行比对条件。数据量小的时候没感觉表里有个几百万行一次全表扫描就会把磁盘IO打满。索引本质上就是那本字典的“拼音检字表”。它把某一列或某几列的值提取出来按照一定规则排好序额外存储一份结构查询的时候先在这个结构里快速定位再根据定位到的位置去拿原始数据。这个“有序”很关键因为有序结构才能做二分查找一类的快速定位。数据库里最常用的索引结构是B树不是二叉树也不是哈希表。哈希索引能做精确等值匹配O(1)的复杂度看着很美但你一旦做范围查询比如大于、小于、between哈希结构就完全帮不上忙。二叉树的层数又太深千万级数据下树高可能有二十多层每层都是一次磁盘IO性能扛不住。B树是矮胖的多路搜索树。它的特点包括所有数据都挂在叶子节点叶子节点之间用指针串联非叶子节点只存索引键值不存数据。一次查询从根节点走到叶子节点层数一般在三到四层意味着即使数据量上千万也只需要三四次磁盘IO就能定位到目标数据这就是它被InnoDB选为主索引结构的根本原因。1.2 聚簇索引与非聚簇索引数据到底存哪InnoDB的索引和数据是放在一起的这叫聚簇索引。表里的主键索引就是聚簇索引它的叶子节点直接存放整行数据。也就是说你按主键查数据B树查到最后拿到的就是完整的一行不需要再回表这也是为什么InnoDB要求每个表必须有主键没有主键时它会自动选一个唯一列再没有就隐式生成一个主键列。非聚簇索引也叫二级索引或辅助索引它的叶子节点存的是索引列的值加上主键值。比如你在name列上建了一个普通索引这个索引的叶子节点存的是“名字主键id”。查询的时候如果只需要这两个字段直接从二级索引就能拿到不需要再回表但如果要查其他字段就必须拿着主键id再回到聚簇索引里查一次完整数据这个过程叫回表。回表不是免费午餐。每回一次表就是一次随机IO数据量一大性能损耗非常明显。所以我常说能用覆盖索引解决的就别查多余字段。所谓覆盖索引就是查询需要的所有列都包含在同一个二级索引里查询引擎在索引树上就能拿到全部数据连回表都省了。注意MyISAM和InnoDB在这点上差别很大。MyISAM的索引和数据是分开的索引叶子存的是数据行的物理地址无论主键索引还是普通索引都不存放整行数据所以MyISAM没有聚簇索引的概念。你现在新建表默认都是InnoDB但遇到老库的时候要能区分这两个引擎。2. 索引类型选型该建哪些索引才能稳准狠2.1 主键索引、唯一索引、普通索引怎么选很多新手一上来就给所有经常查询的列都建索引结果索引建了一大堆写入慢、磁盘占用高查询也没快多少。选类型之前先搞清楚它们各自的约束。主键索引是聚簇索引一个表只能有一个通常用自增id或者业务上唯一且稳定的字段。它天然不允许null约束最为严格。唯一索引允许null值但同一列除null外不能出现重复值它在约束数据完整性之外还能帮优化器更准确地估算行数查询该列时选择性很高。普通索引不约束重复只负责加速查询建得太多就是负担。实践里我的选型逻辑很明确先找出查询的where条件里出现频率最高的列再看看这些列是否需要唯一性约束如果能接受重复值就建普通索引需要防重复就建唯一索引。比如用户手机号在业务上绝对唯一那就应该建唯一索引而不是普通索引既能保证数据质量又顺带做了查询加速。如果只是商品分类ID这种会出现大量重复值的字段建普通索引就够了。2.2 联合索引和最左前缀原则单列索引解决的是单列查询但实际业务查询条件经常是多个字段组合的比如按“城市 状态 创建时间”筛选订单。这种情况你有两种选择建三个单列索引或建一个三列联合索引。绝大多数场景下应该选后者。联合索引的底层依然是B树只是索引键变成了多个列的有序组合先按第一个列排序第一列相同再按第二列排序以此类推。这带来一个非常重要的规则查询必须从最左侧的列开始匹配跳过最左列直接用第二列索引就用不上。这就是最左前缀原则。建联合索引前先想清楚查询模式。比如订单表上高频查询是“按城市和状态筛列表”同时还有个低频场景是“只按状态统计”那么建(city, status)这个联合索引时“只按status”的查询虽然不能直接用整个索引但如果把status放在前面就能覆盖这两种情况。实际上联合索引(b, a)能同时服务“a单独查”和“a和b联合查”反之则不能。所以列顺序安排是联合索引设计的核心要反复权衡高频查询的匹配条件放在最左侧。2.3 回表、覆盖索引和索引下推联合索引还有一个隐藏收益通过包含更多查询需要用到的列来形成覆盖索引省掉回表。比如订单表最常见的查询是select id, city, status from orders where city 北京这时如果建的是(city, status)联合索引id本来就是聚簇索引键的一部分整个查询所需字段都能在二级索引树里找到零回表查询效率是最优的。MySQL 5.6以后还有个优化叫索引下推英文Index Condition Pushdown简称ICP。它允许在索引遍历过程中直接对索引包含的字段做where过滤只有满足条件才回表取完整数据。举个具体例子联合索引(city, status)查询条件where city北京 and status1如果没有ICPMySQL会先在索引里找到所有城市为北京的记录然后一条条回表再在完整行上过滤status。有了ICPstatus这个判断在遍历索引时就完成了回表次数大幅减少。判断一条SQL是否用上了覆盖索引和下推最好的工具是执行计划。下面马上讲怎么建索引、怎么看执行计划这些概念会串起来。3. 实操索引的创建方法与执行计划解读3.1 建索引的标准姿势和参数细节先看最基本的建索引语法我以一张常见的订单表为例CREATE TABLE orders ( id bigint NOT NULL AUTO_INCREMENT, order_no varchar(64) NOT NULL, user_id bigint NOT NULL, city varchar(32) NOT NULL, status tinyint NOT NULL, amount decimal(10,2) NOT NULL, created_at datetime NOT NULL, PRIMARY KEY (id), UNIQUE KEY uk_order_no (order_no), KEY idx_city_status (city, status) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;这张表里已经有了三种典型索引主键索引约束ID唯一索引约束订单号并加速订单号查询联合索引加速“城市状态”的组合筛选。这样的设计满足了大部分查询场景。如果表已经存在单独加索引用ALTER TABLE或者CREATE INDEXALTER TABLE orders ADD INDEX idx_user_created (user_id, created_at); CREATE INDEX idx_amount ON orders(amount);生产环境加索引要特别注意锁表问题。MySQL 5.6之前加索引默认会锁住整张表大表加索引期间写入直接堵塞。5.6之后可以用在线DDL语法上多了ALGORITHM和LOCK选项比如ALTER TABLE orders ADD INDEX idx_user_created (user_id, created_at), ALGORITHMINPLACE, LOCKNONE;在官方支持的前提下INPLACE算法加LOCKNONE允许DDL期间继续读写但并发的写操作也有自身开销并且大表上的索引创建过程依然可能很长。建议任何线上加索引都要放在业务低峰期并且先在测试库执行一遍记录耗时和影响。3.2 EXPLAIN执行计划一条SQL慢不慢答案全在这建完索引到底有没有生效SQL走的是哪个索引、扫描了多少行、有没有回表EXPLAIN能直接回答。我最常用的查看方式是EXPLAIN加完整SQLEXPLAIN SELECT id, city, status FROM orders WHERE city 北京 AND status 1;执行结果里的几个关键列要重点看type访问类型。从好到差依次是system、const、eq_ref、ref、range、index、ALL。看到ALL就要警醒这是全表扫描。看到index说明索引全扫虽然用了索引但效率未必高。ref和range是比较健康的范围查询。key实际使用的索引名。如果是NULL说明没走任何索引。key_len索引使用的字节数。这个值能看出联合索引到底用到了几个列数值越大说明用到的列越多。rows预估扫描的行数。这个数字越小越好。Extra额外信息。出现Using index说明是覆盖索引出现Using filesort说明排序没走索引需要优化Using temporary更是要避免的临时表操作。我实际操作中对一条SQL的体检顺序是先看type是不是ALL再看key是不是NULL然后看rows预估量最后看Extra里有没有Using filesort和Using temporary。这四个地方都没问题了这条SQL基本就健康了。3.3 索引长度key_len的计算方法能看懂key_len你才能真正判断联合索引的利用程度。key_len的计算公式取决于列类型和字符集。以utf8mb4为例每个字符占4个字节。varchar类型还要额外加2个字节记录长度允许NULL的列加1个字节。假设字段是这样定义的city varchar(32) NOT NULL在utf8mb4下单列索引的key_len 32*4 2 130。如果联合索引是(city, status)status tinyint占1个字节NOT NULL不加字节那么key_len 130 1 131。执行计划里如果key_len显示130说明只用了city这一列显示131说明city和status都用上了。这是排查联合索引生效情况的绝招。很多面试官问“联合索引到底用了几个列”其实就是在考key_len的计算。3.4 索引字段长度别贪多前缀索引怎么用如果一个字段很长比如存文章正文、URL地址这种Text类型或超长Varchar给整个字段建索引会非常臃肿B树的每个节点能存放的索引键数量急剧减少树变高查询效率反而下降。这时候可以只取字段的前若干字符建立索引叫前缀索引。ALTER TABLE articles ADD INDEX idx_title_prefix (title(20));前缀索引的缺点是可能遇到重复值选择性降低。所谓选择性可以理解为“这一列里不同值的比例”。比例越高索引区分度越好。选前缀长度时我一般先用SQL统计对比看不同前缀长度下的distinct数量变化找一个既能控制长度又能保持区分度的值。注意前缀索引无法用于覆盖索引因为索引里只存了前缀而不是完整值。查询需要完整字段时只能回表。4. 索引失效的场景与排查经验索引不是建了就万事大吉。我见过太多开发把索引建好了但一条SQL因为写法问题硬是让索引废掉性能图标直接拉满。这部分的坑我在生产环境里基本都踩过一遍逐个说。4.1 函数运算和隐式类型转换在索引列上做函数操作索引必然失效。典型的例子SELECT * FROM orders WHERE DATE(created_at) 2025-01-01;created_at列上就算有索引DATE函数把它包裹之后MySQL无法直接使用索引排序好的值进行查找只能全表扫描。正确的写法是范围查询SELECT * FROM orders WHERE created_at 2025-01-01 00:00:00 AND created_at 2025-01-02 00:00:00;隐式类型转换是更隐蔽的问题。比如user_id是varchar类型但查询时传入了数字SELECT * FROM users WHERE user_id 123456;MySQL会把varchar的user_id转成数字再比较索引列上发生了类型转换索引失效。这类问题在排查慢查询时经常发现SQL看表面没问题执行计划却是ALL。解决方式就是保持类型一致参数类型和字段类型相同。4.2 like模糊匹配和not in的坑最经典的坑是like。以通配符开头的模糊查询无法使用索引这基本是共识了SELECT * FROM users WHERE name LIKE %张%; -- 索引失效 SELECT * FROM users WHERE name LIKE 张%; -- 前缀匹配索引可用这里有一个延伸技巧如果确实需要做包含匹配并且数据量不大可以维持原样如果数据量大到无法接受全表扫描常规方案就是改用全文索引或引入专用搜索组件。不要把精力花在想办法让%张%走索引上方向就错了。not in和not exists是另一个常见坑。优化器通常认为全表扫描比用索引做反向匹配更划算所以大概率不走索引。改为left join加is null或者用exists改写往往效果更好。当然现在的MySQL版本优化器有时候会聪明地转换成anti join但不要依赖这个实际跑一下EXPLAIN最靠谱。4.3 联合索引列顺序写错联合索引最怕WHERE条件里没有最左列。比如索引是(city, status)但查询是SELECT * FROM orders WHERE status 1 AND amount 100;status不在联合索引的最左列而且amount也不在索引中这个查询大概率全表扫描。这种情况要么把status当最左列重新设计联合索引要么为status单建索引。最左前缀原则是铁的纪律设计时就要考虑所有可能出现的高频查询组合。4.4 order by、group by和distinct的隐性陷阱排序和分组同样可以使用索引但条件很苛刻排序顺序必须和联合索引列顺序一致且排序方向要和索引定义一致。以(city, status)为例下面这几种写法就可能触发Using filesortSELECT * FROM orders WHERE city 北京 ORDER BY amount; -- amount不在索引里 SELECT * FROM orders ORDER BY status, city; -- 列顺序和索引不一致Using filesort意味着MySQL要把查询结果放进一块内存或磁盘空间自行排序数据量大时耗时极高。优化方法要么调整索引列顺序以匹配排序需求要么把排序列加进索引。但这里要平衡新索引能不能覆盖其他查询不能让索引无限膨胀。group by和distinct本质上是先排序再分组走不上索引就会产生临时表。Extra里出现Using temporary时先检查group by和order by的列是否构成联合索引的前缀。5. 性能调优实战从定位慢查询到安全上线5.1 用慢查询日志圈定问题SQL与其盲目猜慢在哪不如让MySQL自己告诉你。打开慢查询日志把执行时间超过阈值的SQL记录下来SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1; SET GLOBAL log_queries_not_using_indexes ON;执行时间超过1秒且没走索引的SQL都会被记录下来日志文件默认在数据目录下的hostname-slow.log。拿到日志后分析哪些SQL出现频率最高把top N拿出来EXPLAIN。我处理生产经验是80%的性能问题集中在20%的慢SQL上优化这部分就能解决大多数隐患。5.2 大表加索引的完整操作方案大表加索引不能上来就执行ALTER TABLE风险太大。我的标准操作流程是这样在测试环境导出同样的表结构和数据模拟执行加索引记录耗时。检查线上磁盘空间。InnoDB加索引会重建表空间开销大概是原表大小的一倍多磁盘不够直接失败。选择业务低峰期执行先用SHOW PROCESSLIST确认当前没有长时间运行的事务。用在线DDL语法执行同时监控主从延迟。如果是从库也配置了一致的索引主库加索引引起的重放操作会让从库延迟变大。加完后跑一遍核心查询的EXPLAIN确认走了新索引。有一次给一张两千万行的订单表加联合索引在测试环境执行了40分钟磁盘占用翻了1.5倍。临时决定把操作推迟到凌晨执行并提前清理了30%的磁盘空间才顺利跑完。所以大表DDL前必须把空间和时长都测算清楚。5.3 冗余索引的排查与清理索引不是越多越好多出来的索引除了浪费磁盘还会拖慢写入。每次insert和update都要同步维护所有索引树。清理冗余索引是性能调优里投入产出比很高的一件事。最常见的是重复索引同列建了普通索引又建了唯一索引比如idx_user_id和uk_user_id同时存在。另一种是联合索引的左前缀重复已有(city, status)索引又单独建一个city索引后者就是冗余。因为(city, status)已经能覆盖单独查city的场景。排查冗余索引用information_schemaSELECT table_name, index_name, GROUP_CONCAT(column_name ORDER BY seq_in_index) AS cols FROM information_schema.statistics WHERE table_schema your_db GROUP BY table_name, index_name;拿到所有索引后比对列组合删除完全冗余的索引。删除索引用DROP INDEX这在InnoDB里也是在线操作但同样要低峰期执行。5.4 一个真实的优化案例说个我实际处理的例子。业务反馈订单列表页越翻越慢最严重的接口耗时到了4.8秒。定位后发现慢SQL长这样SELECT id, order_no, user_id, amount, status FROM orders WHERE city 上海 AND status IN (1, 2, 3) ORDER BY created_at DESC LIMIT 20;表结构里原有的索引是city单列索引和created_at单列索引。执行计划显示SQL先走city索引找到约20万行上海订单然后回表过滤status最后把所有匹配行进行filesort排序再取前20条。问题就出在回表和filesort上。我的优化方案是把索引改成三列联合索引ALTER TABLE orders ADD INDEX idx_city_status_created (city, status, created_at);改成这个索引后city和status在索引树中就能完成过滤created_at天然有序排序也不需要filesort。这条SQL的执行时间从4.8秒降到30毫秒左右效果立竿见影。这个案例特别能说明联合索引的设计价值——把查询、过滤、排序三方面需求一次性打包进索引里。6. 面试高频问题和常见问题排查速查6.1 高频面试题怎么答索引这块是MySQL面试的必考点我把几个高频问题的答题思路整理成表每个问题背后都有一个考点面试题核心考点建议回答框架为什么InnoDB用B树不用哈希表索引结构选型哈希支持等值但无法范围查询B树有序且树高固定磁盘IO次数少聚簇索引和二级索引的区别索引存储结构聚簇索引叶子存整行二级索引叶子存索引值和主键需要回表联合索引最左前缀具体指什么联合索引原理索引按列顺序逐层排列查询必须包含最左列且匹配顺序一致索引失效场景有哪些索引使用的边界函数运算、隐式转换、like前导通配、最左列缺失、排序不一致等什么情况下要回表怎么避免覆盖索引二级索引无法覆盖查询字段时就回表用联合索引包含查询字段可避免面试官如果让你现场分析一个SQL为什么慢一定要养成先EXPLAIN的习惯。从type、key、rows、Extra入手快速定位是全表扫描、回表太多、还是filesort这比背概念有说服力得多。6.2 日常问题排查速查表把自己遇到的常见问题整理成速查表排查问题的时候看一眼效率很高症状可能原因排查方向查询越来越慢数据量没涨多少索引失效或统计信息失真EXPLAIN看key列和rows回表次数是否异常写入很慢磁盘IO高索引太多每个索引都要维护用information_schema查冗余索引并清理SQL走了索引还是慢回表次数太多或rows估算高检查是否覆盖索引考虑联合索引优化order by字段导致filesort排序字段不在索引里或顺序不一致调整联合索引包含排序字段某列选择性很高但没建索引低基数误判实际过滤效果差算distinct比例高选择性列必须建索引主从延迟在加索引后飙升大表DDL引起从库回放开销低峰期执行评估并行复制参数我在实际项目里还有个习惯定期把线上数据库的慢查询日志和分析脚本跑一遍把执行计划里出现ALL和Using filesort的SQL统一收集下来每周花半小时评审一次。这种制度化的巡检比临时救火效果好得多很多问题在变成线上事故之前就能被拦下来。6.3 关于索引统计信息容易忽略的细节还有一个容易忽略但实际影响很大的点MySQL优化器选索引时依赖的是索引的统计信息而不是实时数据。当你表数据量发生大幅变化比如大批量删数据后又灌入大量新数据统计信息可能过期优化器会选错索引。解决办法很简单执行ANALYZE TABLE手动更新统计信息ANALYZE TABLE orders;这点在面试里问的人不多但实际生产中真的会因为统计信息不准导致一条慢SQL突然出现。排查时候如果发现EXPLAIN显示优化器选的索引明显不对比如明明有更好的索引偏不走可以先ANALYZE TABLE再看效果。最后再分享一点我的个人体会处理索引问题这些年最深的感悟是索引设计没有银弹每个方案都是在读取速度、写入成本、磁盘占用之间做权衡。不要迷信“索引越多越好”也不要因为怕麻烦就只建主键索引。最好的做法是先把高频查询梳理清楚理解业务真正的访问模式再针对性地设计联合索引让每个索引都有明确的“服务对象”。另外一个小技巧每次上线新的索引之后过几天回头看一眼慢查询日志和索引的使用情况确认真实效果必要时候用SHOW INDEX FROM table查看索引基数。新的索引长期没被使用说明它没有命中核心查询该考虑优化或删掉这套“设计—上线—复盘”的循环比什么高级理论都管用。