1. 先从底层原理说起索引到底在解决什么问题做后端开发这些年我几乎每周都能碰到一次索引相关的线上问题。前阵子帮一位朋友排查慢接口订单表接近两千万行where条件就两个字段user_id和status。原始SQL跑了23秒接口直接超时用户投诉刷屏。我加上一个(user_id, status)的复合索引同样的查询直接降到40毫秒。这500倍的差距就是索引最直接的价值。有人觉得索引就是“给表加个查询加速器”这么理解不全面。索引本质上是一种额外的数据结构InnoDB里默认用B树。B树的特点是多叉、有序、叶子节点存储数据行指针——InnoDB的主键索引叶子节点直接存整行数据二级索引叶子节点存主键值。查询路径从根节点一路走到叶子时间复杂度是log n级别而全表扫描是O(n)级别。数据量小的时候两者没区别一旦数据量到百万、千万log n的查找路径只有几次磁盘I/O全表扫描则要读几万个数据页差距是按数量级拉的。很多人学索引喜欢死磕B树和红黑树的区别我不建议零基础的人一上来就钻这个。你只需要先记住三件关键事索引是排好序的数据结构索引能减少扫描的数据量索引在InnoDB里是聚簇的主键索引直接存行数据二级索引存主键值。至于选择什么字段建索引、用单列还是复合索引、什么情况下索引会失效这些才是日常写SQL时真正要关心的东西。提示索引不是越多越好。每张表的 insert、update、delete 都要同步维护索引结构索引太多会让写入变慢、磁盘占用变高。判断一个索引该不该建核心看它能不能被你实际的高频查询用到。2. 索引设计先看区分度再看查询场景2.1 区分度低就别硬建给一个字段建索引之前先问自己一个最基础的问题这个字段能筛掉多少数据专业说法叫区分度Cardinality。区分度高才值得建索引。比如user_id、phone、order_no这种字段每个值基本唯一建索引后能立刻把扫描范围缩小到一两条记录。反之像status这种字段一张表里90%的数据都是0、5%是1、5%是2单独给它建索引回表查出来的可能占了大半数据优化器估算之后大概率直接放弃索引走全表扫描。我见过一个很典型的例子有同事给性别字段建了索引查询条件是 where genderM and create_time between ...结果索引完全没走。原因很简单gender的区分度太低优化器觉得还不如直接扫全表。后来把索引改成(create_time, gender)效果立刻不一样了——先用时间范围把二十多万行缩到几千行再用性别过滤整体扫描量大幅下降。建索引前最好用SQL确认区分度SELECT COUNT(DISTINCT column_name) AS cardinality, COUNT(*) AS total FROM table_name;如果cardinality / total 小于10%甚至低于5%这个字段单独建索引基本没什么用更适合放在复合索引的靠后位置做过滤条件。2.2 优先覆盖这几类场景不是所有查询都需要索引。我的经验是优先考虑以下几类线上接口里的高频查询。每天被执行几十万次的SQL它的where条件字段必须重点设计索引。排序和分页。order by涉及的字段能走索引可以避免filesort深分页limit 1000000, 20没有索引加持绝对是灾难。高频join的关联字段。两表关联时连接字段上有索引能显著减少驱动表每行去被驱动表探测的开销。反过来低频报表查询、临时跑数脚本、数据量低于几万行的配置表建不建索引影响不大反而增加写入负担。索引不是免费的每次数据变更都要同步维护索引结构写多读少的表尤其要想清楚。2.3 主键索引与二级索引的取舍InnoDB表默认按主键聚簇主键索引的叶子节点就是完整数据行。所以主键选择直接影响整张表的物理存储布局。主键最好用自增整型或有序的业务ID。为什么B树按主键顺序排列自增ID插入时永远追加到末尾页分裂少、写入高效。如果用UUID或随机字符串做主键数据插入会随机落在树的中间位置频繁触发页分裂和页重排写入性能下降明显索引碎片也会变多。二级索引的叶子节点存的是主键值因此二级索引查询通常要两步骤先在二级索引B树找到主键值再回主键索引查完整数据行专业说法叫“回表”。如果查询所需列恰好都在二级索引里就能做到覆盖索引直接返回结果连回表都省了。这一点在后面会详细讲是优化效率非常高的手段。3. 复合索引怎么建where条件a and b的完整拆解3.1 最左前缀原则必须刻在脑子里“mysql where条件a and b应该怎么建索引”这个问题在网上搜索量非常高。要回答它绕不开最左前缀原则。复合索引(a, b)在B树里的排序逻辑是先按a排序a相同再按b排序。所以它能高效服务的查询条件是where a ?、where a ? and b ?。但where b ?这种条件因为跳过了最左列a索引没法直接定位优化器只能把复合索引整体扫一遍效果远不如预期。单纯查b字段需要单独给b建索引。建复合索引的本质就是把“能覆盖多个查询模式”的字段排好顺序。通用经验是等值条件的字段放前面范围条件between、、放后面区分度高的放前面区分度低的放后面。这样设计既符合B树的排序特性也能最大限度缩小扫描范围。举个订单表的例子CREATE TABLE orders ( id BIGINT PRIMARY KEY AUTO_INCREMENT, user_id BIGINT NOT NULL, status TINYINT NOT NULL, create_time DATETIME NOT NULL, amount DECIMAL(10,2), KEY idx_user_status (user_id, status), KEY idx_create_time (create_time) ) ENGINEInnoDB;典型查询一SELECT * FROM orders WHERE user_id 123 AND status 1;这条查询走idx_user_status先按user_id等值定位到该用户的所有订单再在索引内部按status过滤。索引遍历的行数极少。典型查询二SELECT * FROM orders WHERE user_id 123 ORDER BY create_time DESC LIMIT 20;这条查询如果只有idx_user_status走索引过滤user_id之后还需要对create_time做filesort排序。如果业务上高频出现“按用户查最近订单”的场景更合理的方案是建(user_id, create_time)复合索引这样排序直接走索引的有序性省掉filesort。查询模式决定索引结构这是复合索引设计的核心思路。3.2 or条件是最容易翻车的写法where a and b适合建复合索引但where a or b的情况完全不同。MySQL里or条件想走索引要求or两侧都是索引列。如果一侧是普通字段没有索引整个查询直接放弃索引走全表扫描。这是新手最容易踩的坑之一。-- 假设只有idx_user_id没有idx_phone SELECT * FROM users WHERE user_id 123 OR phone 13800138000;MySQL评估一下一半条件有索引另一半要全表扫那还不如整个全表扫。解决办法有两个要么给phone也建索引两边都覆盖要么用union all拆开SELECT * FROM users WHERE user_id 123 UNION ALL SELECT * FROM users WHERE phone 13800138000 AND user_id 123;不过在真实业务里我更倾向于先反问一句这个or逻辑能不能改成in很多时候where a in (1,2,3)比where a1 or a2 or a3的可优化性强得多。3.3 覆盖索引查询列全部收进索引里复合索引还有一个特别容易被忽略的组合收益覆盖索引。覆盖索引的意思是二级索引的叶子节点中已经包含了你需要的所有列可以直接从索引返回结果连回主表查数据行的操作都免了。执行计划里Extra字段会显示Using index。拿上面那张订单表举例-- 给表再加一个复合索引(user_id, status, amount) SELECT user_id, status, amount FROM orders WHERE user_id 123 AND status 1;这个查询的所有列都在复合索引(user_id, status, amount)里扫描索引就能拿到全部结果不需要回表。日常优化报表类查询时这是最划算的手段——不改业务逻辑只需要把select字段尽可能收窄到索引列范围内。注意覆盖索引对查询性能提升非常大但也不能为了覆盖所有查询把一堆无关列塞进索引。索引列越多索引体积越大写入维护成本越高。只针对高频查询做覆盖设计低频场景别去迁就。4. 索引失效的坑这些场景我全踩过4.1 函数操作与隐式转换把索引列放在函数里是索引失效的最高频原因。WHERE DATE(create_time) 2024-01-01;这条SQL看着没问题但索引列被DATE()包装之后B树的有序信息全废了优化器只能全表扫。正确写法是范围条件WHERE create_time 2024-01-01 00:00:00 AND create_time 2024-01-02 00:00:00;两者效果等价后者能走索引。隐式转换也是同一个道理。字段是varchar类型查询时写 where phone 13800138000 不写引号MySQL要先把phone列转成数字再比较索引失效反过来字段是数字写 where id 123一样的问题。写SQL时类型匹配必须严格对齐。4.2 前缀模糊与负向查询like abc%能走索引like %abc和like %abc%不能走。原因在于B树只能按前缀匹配有序结构后缀或中缀模糊匹配无法利用有序性。业务上真需要中缀模糊搜索更合适的是全文索引或搜索引擎而不是硬靠普通索引扛。not in、not like、这些负向查询普遍难走索引。即使走了优化器也经常因为估算成本太高而放弃。能改成正向范围的尽量改成正向范围。4.3 字符集与排序规则不一致这个坑特别隐蔽。两张表join的字段一个用utf8mb4一个用latin1或者两边collation不同MySQL为了比较数据需要做隐式转换索引直接失效。这种问题一般要在建表时就统一规范全库的字符集和排序规则保持一致。真遇到已经存在的表修改表结构后记得重新分析执行计划确认索引重新生效。4.4 索引列参与运算where a 1 10这种写法看着很聪明实际等于给索引列套了一层表达式索引用不上。应该改成 where a 9。做了这么多年开发我发现很多人写SQL喜欢把计算放在查询条件里最后全表扫描了还不知道为什么。4.5 统计信息过期还有一种情况索引明明建了执行计划却显示不用索引。这时候不一定是SQL写错很可能是优化器的统计信息过期了。大批量导入数据之后经常发生。解决办法很简单ANALYZE TABLE your_table;重新统计之后执行计划往往就恢复正常了。这个操作很轻量排查时可以先做这一步。5. 用EXPLAIN和慢查询日志定位索引问题5.1 EXPLAIN怎么读才高效排查索引问题EXPLAIN是最基本的手段。执行EXPLAIN SELECT ...之后重点看几个关键列type字段从好到差依次是 system const eq_ref ref range index ALL。ALL就是全表扫描出现它就要警惕。Extra字段出现Using filesort和Using temporary通常意味着排序和临时表没有用到索引是明确优化信号。key列显示实际使用的索引名rows列是优化器估算扫描的行数估算结果偏差太大往往是统计信息过期。我一般看执行计划时会特别关注这些标记组合Extra标记含义优化方向Using index覆盖索引已经很好无需处理Using index condition索引下推生效检查复合索引列顺序是否最优Using where索引过滤后回表过滤考虑增加索引列覆盖where条件Using filesort结果排序用了文件排序调整索引列顺序匹配order byUsing temporary用了临时表优化group by / distinct相关查询定位到问题后一次只优化一个点改完用实际查询耗时验证不要只看执行计划。5.2 慢查询日志怎么用开启慢查询日志是排查线上索引问题的第一步。通常把long_query_time设为1秒超过1秒的SQL全部记录下来再逐条用EXPLAIN分析。我自己习惯先把慢SQL按执行次数和总耗时排序优先处理高频慢查询。线上治理顺序是高频且慢的最优先低频但极慢的其次。等所有慢SQL都处理完再把long_query_time调回5秒避免日志量过大。5.3 一个实际排查案例朋友公司的商品表某个后台管理列表页每次打开都要十几秒。捞出来SQL一看SELECT * FROM products WHERE category_id 10 AND status 1 ORDER BY updated_at DESC LIMIT 20;表里只有一个category_id单列索引。执行计划Extra显示Using filesortrows估算二十万。我的调整方案是把索引改成(category_id, status, updated_at)。这样where等值条件直接过滤到几千行order by updated_at也能顺着索引顺序读取filesort直接消失。改完查询降到30毫秒。这是个非常经典的复合索引匹配查询模式的例子。6. 索引维护建了索引不等于一劳永逸6.1 冗余索引必须清理常见问题是冗余索引。一个表里同时存在idx_user_id和idx_user_id_status前者就是冗余的——因为后者的最左前缀已经覆盖了前者。冗余索引白占磁盘空间还拖慢写入一旦发现要果断删掉。查冗余索引可以用information_schemaSELECT table_name, index_name, GROUP_CONCAT(column_name ORDER BY seq_in_index) AS cols FROM information_schema.statistics GROUP BY table_name, index_name HAVING COUNT(*) 1;拿到索引列组合后再人工对比哪些复合索引的前缀重复。比如(A, B)和(A, B, C)不是完全冗余但(A, B)和(A, B, C)前缀相同的部分如果后者已经覆盖了前者的查询需求前者就可以删。我碰到过最难忘的一个案例某天凌晨报表任务突然从20分钟变成2小时排查下来是前一天有同事给大表加了两个冗余索引写入压力没变化后台批量更新时索引维护成本暴涨。删掉冗余索引后任务恢复20分钟。索引这种东西不是越多越稳而是越精准越稳。6.2 长文本字段用前缀索引长文本字段直接建全字段索引索引体积相当大占内存和磁盘都很夸张。合理做法是加前缀索引ALTER TABLE article ADD INDEX idx_content (content(20));前缀长度选多少是个取舍问题。一般用下面这种方式试探SELECT COUNT(DISTINCT LEFT(content, 10)) / COUNT(*) AS p10, COUNT(DISTINCT LEFT(content, 20)) / COUNT(*) AS p20, COUNT(DISTINCT LEFT(content, 30)) / COUNT(*) AS p30 FROM article;哪个长度的区分度稳定在90%以上就取那个长度。前缀索引虽然不能覆盖idx字段本身用于排序但等值查询和范围过滤的效果足够好。6.3 索引下推要结合复合索引理解MySQL 5.6引入了索引下推Index Condition Pushdown。之前二级索引只能定位到大致范围然后回表对整行数据做where过滤。有了下推之后在索引遍历过程中就提前过滤掉不满足条件的二级索引列减少回表次数。这也是我在前面反复强调复合索引列顺序的原因——下推的收益很大程度依赖复合索引设计是否合理。条件都过滤完再回表和先回表再过滤性能差距在数据量大时非常显著。7. 个人实操过程中的几条心得最后分享几条我这些年攒下来的经验不一定适合所有场景但至少能帮你少走弯路。第一优先保证线上高QPS查询能走索引再考虑慢报表。成本收益比完全不同。一个每天调用几十万次的接口优化掉20毫秒一天的收益是巨大的一个月跑一次的报表优化掉10分钟一共几秒钟的日均收益。排序很清楚。第二建索引前一定先看区分度。区分度不够的字段强行建索引是在给自己找麻烦。一个字段如果只能筛掉一半数据这个索引对性能的贡献微乎其微反而拖累写入。第三复合索引字段顺序按“等值在前、范围在后、区分度在前”来排别拍脑袋。顺序排错了就算建了复合索引也可能一个查询都用不上。第四定期做索引审查。删除长期不用的冗余索引同时更新统计信息。我一般每季度会跑一次索引清单结合慢查询日志里的实际使用情况清理一批低效索引。第五写多读少的表索引越精简越好。日志表、流水表这类高频写入的表每次写入都在为每个索引付出代价。索引数量每多一个写入性能就降一档。第六SQL写的合规比事后优化更重要。索引设计得再好一条 like %xx% 的查询也能让你前功尽弃。写SQL时先想想这个条件能不能走索引养成习惯比任何技巧都管用。索引设计不是考试题没有一个万能答案。每张表的查询模式不一样业务瓶颈不一样适合的索引方案也不一样。我的建议是带着EXPLAIN跑一遍自己的慢SQL把每个查询的类型、扫描行数、Extra标记都捋一遍再动手设计索引。索引上线后定期复盘执行计划及时清理无用索引。这套循环比背一百条理论都管用。