你有没有遇到过这种情况同样的SQL数据量小的时候秒回数据量一大就慢到让你怀疑机器是不是坏了。我有段时间频繁被这类问题困扰后来排查下来十次里有八次是索引没用上或者压根没建索引。SQL索引这个东西说简单就是把原来的顺序翻书变成查目录但到底怎么建、建在哪些字段上、为什么明明建了索引执行计划还是不走里面的细节非常多。这篇内容我把几年实战里关于索引的积累整理了一遍适合刚接触数据库索引、想搞懂慢SQL优化、以及在MySQL和SQL Server之间来回切换的开发者。看懂之后你至少能解决大部分“查询慢”的问题。1. 先把索引的底裤掀开它为什么能让查询变快1.1 索引的本质从顺序翻书到查目录在索引出现之前数据库想找一行记录只能挨个读数据页专业点叫全表扫描。数据量小无所谓数据上了百万这块开销就恐怖了。索引本质是一种额外的数据结构把数据的“某个字段值”和“物理位置”之间的映射关系提前组织好。你可以把它理解成书的目录没目录时一页页翻有目录后直接翻到对应章节。不过数据库的目录底层通常不是普通数组而是一棵B树。B树有两个特点值得说一是所有真实数据只存在于叶子节点而且叶子节点通过双向链表串起来二是非叶子节点只存键值和指针单个节点能放下大量键树的高度通常很矮。一个三层的B树能支撑千万级数据量的查询再加上每层一次磁盘IO性能自然比全表扫描好得多。这也是为什么索引的底层绝大多数选B树而不是二叉搜索树后者树太高磁盘IO次数多查询不稳定。这里有个常见的误解索引不是“给查询开挂”而是改变数据查找路径。全表扫描的复杂度是O(n)B树查询大概是O(log n)。数据量越大两者差距越明显。很多开发者在本地用几千条数据测试觉得索引没用等上了生产几十万几百万行才发现没索引根本跑不动原因就在这里。1.2 不同索引结构怎么选索引结构不只是B树。实际使用中你可能听到这几个词B树索引、哈希索引、倒排索引。不同数据库对同一种结构的实现也有一些差异比如MySQL InnoDB针对频繁访问的B树节点会自动生成自适应哈希索引用来加速等值查询SQL Server除了传统的B树索引之外还有列存储索引专门为OLAP场景设计的列式存储。我自己在做技术方案时会先问清楚业务查询类型在线交易类查询优先B树精确等值匹配可以考虑哈希全文检索则要单独规划倒排索引。至于索引结构在底层怎么生成作为开发运维人员其实不用太操心核心是要理解每种结构能做什么、不能做什么。B树索引最通用支持等值查询、范围查询、排序和前缀匹配。MySQL InnoDB、SQL Server的聚集索引和二级索引都用它。哈希索引只支持等值匹配不支持范围查询。内存表或自适应哈希索引会用。好处是查找单个键极快O(1)级别。倒排索引主要供全文检索用比如搜索某个词在哪些文档里出现核心是“词项到文档列表”的映射。SQL Server的全文索引、MySQL FULLTEXT都是这类。选择哪种索引取决于查询模式。如果业务经常查“某个员工年龄在25到30之间”哈希索引直接出局只能B树。这里需要特别提醒一点InnoDB的普通索引默认就是B树但你不需要手动指定结构。建索引时只指定字段和类型数据库会按内核决定。1.3 聚簇索引、二级索引和回表MySQL与SQL Server的差异这是最容易绕晕的地方。InnoDB里表数据本身就是按主键组织成B树的这个B树的叶子节点存的是整行数据叫聚簇索引clustered index。除了它之外的其他索引叶子节点只保存索引列和主键值这类叫二级索引或非聚簇索引。当通过二级索引查数据时先找出主键再用主键到聚簇索引里查一次完整行这就是回表。回表本身是额外开销但如果二级索引覆盖了查询需要的所有列就不必回表称为覆盖索引。SQL Server的说法略有不同你可以为一张表显式创建“聚集索引”和若干“非聚集索引”。聚集索引决定数据的物理存储顺序默认如果建了主键主键就是聚集索引也可以设置成非聚集。非聚集索引叶子节点存的是聚集索引键或行定位符。整体思想跟InnoDB的聚簇/二级索引几乎一致只是术语和细节不一样。让我用个生活化的类比聚簇索引像整本字典本身按拼音排好查到一个字直接翻到那一页二级索引像“偏旁部首检字表”你先查偏旁找到页码然后再翻到正文那一页。如果不查整个字只查页首信息那就能省掉后一步。理解了这个后面讲覆盖索引和索引失效就顺理成章了。另一个容易忽略的概念是主键索引。InnoDB如果没有显式主键会选第一个非空唯一索引要是也没有则生成隐藏主键。主键索引不仅是约束更是聚簇索引的入口。所以主键设计不能只看业务同样要考虑数据写入的局部性。自增整数通常最好字符串主键要注意长度和随机性。2. 索引怎么建才不踩坑字段选择与创建原则2.1 创建索引的语法和基本规范不管是MySQL还是SQL Server创建索引的SQL语法非常接近。核心模板是-- MySQL / SQL Server CREATE INDEX idx_user_name ON sys_user (name); -- 唯一索引 CREATE UNIQUE INDEX idx_user_email ON sys_user (email); -- 联合索引复合索引 CREATE INDEX idx_user_status_create_time ON sys_user (status, create_time);在SQL Server里还可以额外指定填充因子、包含列、过滤索引等CREATE NONCLUSTERED INDEX idx_order_status_date ON dbo.t_order (status, create_time) INCLUDE (order_amount);这里的INCLUDE列不会参与索引排序但它会被保存在索引叶子节点里目的是实现覆盖索引减少回表。MySQL的InnoDB中要实现类似效果只能把所有需要的列都放进联合索引里显得笨重一些。这也是两个数据库在细节体验上的差异。CREATE INDEX在MySQL和SQL Server里都是在线DDL吗这里要注意MySQL 8.0默认支持INPLACE方式但如果你没指定ALGORITHM某些场景还是会退化成COPY需要锁表SQL Server用ONLINE ON选项可以在重建索引时减少阻塞但也不是完全没有锁。所以生产环境对大表做DDL我习惯先查一下兼容性和版本支持再决定要不要在低峰期操作。另外索引命名也建议统一比如idx_表名_字段名一旦线上索引多起来规范命名会省很多事。建索引前我建议先统计一下常见查询的WHERE、ORDER BY、GROUP BY字段。最怕看到的是同一条SQL换个字段组合就新建一个索引结果索引数量爆炸写性能下降。原则是能用联合索引覆盖多条SQL的就别拆成多个单列索引。2.2 最左前缀原则联合索引为什么如此挑食联合索引复合索引是实战中最重要的优化手段也是坑最多的。假设你建了索引(a, b, c)实际上数据库会按照a、b、c的顺序把键拼起来排序。所以查询能用到索引的前提是过滤条件里必须有a或者同时有a和b才可能用上这个索引。比如WHERE a 1可用WHERE a 1 AND b 2可用WHERE a 1 AND b 2 AND c 3可用WHERE b 2一般不可用除非你的SQL优化器能根据统计做跳跃扫描MySQL 8.0的“Skip Scan”在特定条件下能用但别依赖WHERE c 3不可用这就是最左前缀原则。很多人以为只要联合索引里含了这个字段就能用其实顺序决定了谁在左边谁优先。所以你设计联合索引时要把区分度高、查询最频繁的字段放前面同时兼顾范围查询字段的取舍范围查询后面的字段会被截断用过range之后后面的字段无法继续使用索引排序。举个例子业务里最常用的查询是“查某状态下的订单再按创建时间排序”那索引(status, create_time)通常比(create_time, status)更合适因为等值条件status放前面可以直接命中索引并顺手利用索引的有序性避免文件排序。如果反过来先按create_time范围查再过滤status联合索引的优势就少了一大半。2.3 什么字段值得建索引什么字段别碰很多新手的习惯是查询慢就给所有出现在WHERE后面的字段都建索引。这是典型的自作聪明。索引是要占空间、要维护的每次写数据都要同步更新索引索引太多会让INSERT/UPDATE变慢磁盘压力也大。我判断一个字段是否值得建索引主要看三点。一是区分度。性别字段只有男、女两种值建了索引最多把数据分成两堆过滤效果极差优化器很可能放弃索引直接全表扫描。区分度可以用COUNT(DISTINCT column) / COUNT(*)粗略评估越接近1越好。二是查询频率。如果一个字段一个月都用不上一次别建。三是数据量。几百几千行的表就算全表扫描也很快建索引的意义不大反而增加维护成本。还有一个容易忽视的问题重复索引。比如有联合索引(status, create_time)又单独建一个status索引。后者就是冗余的因为联合索引的最左前缀已经覆盖status的等值查询。这种冗余索引在MySQL和SQL Server里都不会报错但会拖慢写入占用磁盘。排查时可以直接查数据库的信息视图比如MySQL的statistics表或SQL Server的sys.indexes结合SQL分析找出重复定义。还有一个看起来不大但很实际的问题字符集。两张表关联字段一个utf8一个utf8mb4或者大小写排序规则不一样JOIN时可能产生隐式转换。这种问题在SQL Server里同样存在比如中文排序规则Chinese_PRC_CI_AS和Latin1_General_CI_AS不同都会影响索引使用。建表时统一字符集、统一排序规则看似基础其实能避免很多后期麻烦。3. 索引失效的十大典型场景含排查清单3.1 这些写法会让索引白建这是我咨询中出现频率最高的问题明明索引建了EXPLAIN一看还是全表扫描。下面这几种典型写法每一行都在劝退优化器。第一对索引列使用函数或表达式。例如WHERE YEAR(create_time) 2025即使create_time上有索引函数包裹后索引也会失效。正确写法是用范围查询WHERE create_time 2025-01-01 AND create_time 2026-01-01。第二隐式类型转换。常见于字符串字段和数字比较。varchar类型的手机号字段写成WHERE phone 13800138000优化器可能把字段隐式转成数字索引失效。写SQL时类型要严格匹配用引号包裹。第三前模糊匹配。WHERE name LIKE %麦田%不限定位数B树无法从“%”开始查找只能全表扫。如果确实需要需要考虑全文索引、倒排索引或者业务上改造成前缀查询。第四OR连接非索引字段。WHERE id 1 OR name 张伟如果只有id有索引name没有优化器可能放弃id索引改全表。解决办法是给name也加上索引或者改写为UNION ALL。第五对索引列进行运算。WHERE a 1 10这种也会失效应改成WHERE a 9。第六NOT IN、NOT EXISTS、!、在某些情况下会导致优化器放弃索引。注意是“某些情况”不是绝对和数据分布有关但默认要谨慎。第七隐式字符编码不一致导致类型转换JOIN时常出问题。两张表关联字段的排序规则不同也可能引起索引失效。这类问题很隐蔽可以用EXPLAIN观察结果或在表设计时统一字符集和排序规则。3.2 索引失效自查速查表下面这张表我从实际操作里整理出来排查慢SQL时对照着看效率非常高。场景典型写法为什么失效建议改写函数包裹索引列WHERE YEAR(ctime)2025无法对函数结果用B树搜索范围查询隐式类型转换WHERE phone138...字段被转类型索引列参与计算字符串加引号前模糊WHERE name LIKE %xxB树只能从左前缀匹配全文索引或业务调整OR非索引列WHERE id1 OR namexx优化器难以合并多路径补索引/UNION索引列运算WHERE a110索引列上表达式导致无序移到右侧连接时字符集不一致JOIN ON a.uuid b.uuid隐式转换统一字符集排序规则注意优化器是否选择索引还受统计信息影响。即使是同样一条SQL如果表数据分布变化但统计信息没更新也可能走出奇怪计划。所以自查时除了看语法还要确认统计信息是否最新。3.3 实操排查步骤EXPLAIN是照妖镜遇到慢SQL我的标准动作是这样。第一步开启慢查询日志或者拿业务里反馈的SQL先定位是哪条慢。第二步把SQL单独拎出来在前面加EXPLAINMySQL或看执行计划SQL Server的CtrlL。重点看几个字段type、key、rows、Extra。type里如果出现ALL说明全表扫描出现index说明扫了整棵二级索引树也不一定快。rows是优化器估计的扫描行数如果比实际返回行数高出几个数量级说明选择性和索引有问题。Extra里出现Using filesort、Using temporary信号也很明确排序用了临时文件需要检查ORDER BY是否和索引顺序一致。顺便说一句SQL Server看执行计划时如果看到“索引扫描(Index Scan)”且输出行数很大基本等同于全表扫。而“索引查找(Index Seek)”才是我们希望看到的高效路径。我常用的习惯是先看树形执行计划中最耗CPU和IO的节点再决定优化方向而不是闷头加索引。这里还要提醒一点EXPLAIN的type不是非黑即白。像range、ref、eq_ref都是可以接受的const是理想状态。如果看到type是ref说明走了非唯一索引等值查询性能通常不错看到range说明走了索引范围扫描看到ALL和index就要警惕了。掌握这些细节排查速度能快不少。4. 慢SQL优化从6秒到30毫秒的完整案例4.1 拿到慢SQL之后的第一步先看数据分布和执行计划很多朋友把慢SQL拿到手第一反应就是“加索引”。我理解这种心情但更靠谱的顺序是先分析SQL的逻辑再分析表的数据分布最后才动手设计索引。如果查询本身就要全量聚合几百万行做统计那即便加了索引也不一定能根治得考虑汇总表、物化视图或缓存。我这里复盘一个印象很深的优化案例。一张订单流水表数据量约800万行业务方反馈按“状态创建时间”查订单列表很卡大概要6秒。SQL大概是SELECT order_id, order_amount, create_time FROM t_order WHERE status 3 AND create_time 2025-06-01 AND create_time 2025-06-30 ORDER BY create_time DESC LIMIT 20;执行计划显示走了ALL全表扫描。两个过滤字段分别有各自单列索引但优化器没选因为status单列索引区分度不够create_time单列索引又要大量回表。这就暴露了索引设计没跟上查询模式。4.2 改一个联合索引效果立竿见影我当时给的建议是建联合索引ALTER TABLE t_order ADD INDEX idx_status_create_time (status, create_time);为什么这个顺序因为status是等值条件放在索引左侧create_time是范围条件放在右侧。执行查询时优化器可以在B树里先按status定位到一段区域再按create_time范围继续扫描而且因为索引里已经带上create_time排序ORDER BY create_time DESC理论上也能利用索引顺序避免filesort。改完之后查询时间从前面的6秒降到了60毫秒左右效果非常明显。这里有个细节要补充虽然联合索引能覆盖这个查询的过滤和排序但查询的列里有order_amount它不在索引里所以依然需要回表。如果这个列表查询高频且需要返回的字段固定可以进一步把order_amount也放进索引组成覆盖索引。不过覆盖索引会增加索引宽度要权衡大小和更新成本。在我的经验里20行列表查询回表20次完全可接受没必要盲目覆盖。4.3 延伸优化覆盖索引和MRR都是回表的朋友回表是二级索引查询中的常见开销。MySQL 5.6以后推出了MRRMulti-Range Read目的是把二级索引查出来的主键先排序再批量回表减少随机IO。优化器在某些情况下会自动选择MRR但也不总是生效。想主动控制可以设置optimizer_switch相关参数不过在大多数业务中更简单的做法是让查询走覆盖索引。覆盖索引就是索引里包含查询需要的所有列。比如上面的查询如果改成订单列表只需要order_id和create_time都不用额外回表。但现实中SELECT后面的字段经常是*这时覆盖索引往往不现实采用“先主键再回表”的方式更常见。另外一个优化思路是延迟关联。经典写法是SELECT t1.order_id, t1.order_amount FROM t_order t1 INNER JOIN ( SELECT id FROM t_order WHERE status 3 AND create_time 2025-06-01 ORDER BY create_time DESC LIMIT 20 ) t2 ON t1.id t2.id这个SQL先让子查询用联合索引快速定位20个主键再把这些主键回表取完整行。相比直接全字段排序扫描和回表范围大大缩小。我第一次在线上用这套写法实测对深分页场景非常有效。5. 索引维护与常见问题实录5.1 索引不是建完就完事碎片、统计信息和冗余索引数据库的索引在反复增删改之后会产生碎片。以SQL Server为例碎片率高了以后扫描索引页时IO次数增加查询反而变慢。常见做法是定期重建或重组索引例如ALTER INDEX idx_order_status_date ON dbo.t_order REBUILD;MySQL InnoDB没有直接REBUILD的简单命令通常用ALTER TABLE ... ENGINE InnoDB来重建表或者用OPTIMIZE TABLE整理碎片。不过要注意生产环境大表执行这操作会锁表很长一段时间凌晨低峰期操作都比较悬。更稳的方案是借助在线DDL或第三方工具像MySQL的pt-online-schema-changeSQL Server则可以用ONLINE ON选项。除了碎片统计信息也必须关注。优化器选索引不是靠猜而是靠统计信息估算行数和区分度。所以当数据量发生大幅变化时要主动更新统计信息。MySQL里执行ANALYZE TABLE t_order; SQL Server里执行UPDATE STATISTICS dbo.t_order。很多“我索引没问题但优化器就是不走”的怪事更新完统计信息就好了。5.2 我踩过的几个索引坑分享给你最后说几个实战里踩过的坑。第一个坑UUID当主键。InnoDB的聚簇索引按主键顺序组织UUID随机性太强插入时经常要移动数据页导致页分裂和写放大性能下降得很厉害。如果非要用UUID可以考虑改成有序UUID或者用自增主键业务唯一键两个字段各司其职。第二个坑为每一列都无脑建索引而不是考虑联合索引。我之前维护过一张表用户表有23个索引数据量不到50万查询还是慢。后来检查发现一半索引从来没被用上纯属心理安慰。冗余索引不仅占空间还拖慢写入。删掉没用的索引后写入性能明显回升。第三个坑在排序场景中忽略联合索引的方向。MySQL 8.0之前索引默认升序如果查询是ORDER BY create_time DESC可能没法完全利用索引顺序。设计索引时就要考虑排序方向SQL Server也类似可以在索引定义里指定ASC/DESC。还有个经验不要指望所有SQL都能走索引。比如报表类的聚合查询如果过滤条件很少数据量又特别大就算有索引也无法避免大范围扫描。这时候与其硬调索引不如做数仓、汇总表、物化视图把压力前置转移。索引是工具不是银弹理解这一点后优化思路会开阔很多。