拓冰网站定制 
MySQL的索引调优是性能优化里回报最高的动作之一而单列索引是整个索引体系最基础、也最容易出效果的一块。很多慢查询原因就一条where里的字段没有索引或者有索引但没被用上。我做后端开发和数据库运维这些年每次定位线上SQL第一步永远是看表结构第二步就是explain单列索引建不建、建得对不对基本决定了这条SQL是毫秒级还是秒级。这篇文章写给两类人一类是刚接触MySQL、想知道索引是什么的初学者另一类是已经会写CRUD、但总在调优上前怕狼后怕虎的开发。我会把单列索引的原理、创建方式、实战调优和一些踩坑经验一次讲清不整那些百度百科式的废话全部按实际能用的标准来。这篇里我不碰联合索引、覆盖索引、索引下推这些进阶玩法集中火力把单列索引搞清楚。地基建牢了后面再聊复合索引才顺。1. 单列索引的核心原理与设计思路1.1 为什么全表扫描会被淘汰索引解决的核心痛点先想一个再普通不过的场景user表里有几百万数据你要按email找一个用户。没有索引时MySQL只能从第一行开始一页一页把全部数据读出来逐行比对email字段。这个操作叫全表扫描数据量一大磁盘IO直线上升SQL自然慢得让人抓狂。索引解决的问题本质上和书的目录一样。翻一本五百页的书找某个名词你不会从第一页开始逐页翻而是看目录、对页码、直接翻到对应页。单列索引就是把某一列的数据抽出来排好序存成一种额外的数据结构查询时先在这个“目录”里找到位置再去取数据。你建的每一个单列索引都是在给MySQL制作一份这样的目录。那为什么MySQL不默认给所有列都建索引因为目录本身要占空间而且你每往书里加一页内容目录也得跟着改。索引是典型的“空间换时间”并且写操作时要付出维护代价。所以索引调优的本质不是把所有列都建一遍索引而是找到真正会拖慢查询的那些字段。1.2 B树是如何组织单列索引的InnoDB存储引擎里单列索引默认使用B树数据结构。为什么不选哈希表哈希表做等值查询确实极快比如 where id 5但做范围查询where age 20和排序时哈希就无能为力了因为数据没有顺序。B树则是个折中的好方案叶子节点按列值从小到大有序排列且相邻叶子节点通过指针连在一起既能做等值匹配也能在O(logN)的开销下完成范围扫描和排序。这里要提一个关键点单列索引分为主键索引和普通索引两者叶子节点存的东西不一样。主键索引也叫聚簇索引叶子节点直接存整行数据所以通过主键查询不需要额外回表。而我们在其他列上建的普通索引叶子节点里存的是主键值查询时先找到对应主键再回表去取完整的行数据。这就意味着普通索引的查询通常至少访问两棵B树先查索引树再查主键树。我见过不少初学者以为索引越多越快其实当你理解了普通索引要回表就会明白每一步都要成本。数据量越大回表次数越多随机IO越多查询自然变慢。这也是为什么很多调优经验里会有“避免select *”“尽量让查询列在索引里”这类建议。单列索引虽然简单但回表这个机制必须时刻记得。1.3 为什么不是所有列都建索引接上文索引有空间成本和DML维护成本。一张表如果有10个单列索引每次insert、update、deleteMySQL都要同步维护这10棵B树写性能被成倍拖低。我记得有个生产环境的业务表为了让所有可能的where条件都能走索引一口气建了8个单列索引结果数据导入速度直接慢了三倍。判断一个字段值不值得建索引业内最常用的量化指标是区分度。区分度可以用基数cardinality来近似理解也就是这个列里不同值的个数。比如性别列只有“男/女”两个不同值基数就是2即便建了索引MySQL也可能觉得全表扫描更划算因为通过索引找到大量主键后再回表开销反而不如直接顺序扫描。区分度高的列比如email、订单号、手机号就非常适合建单列索引。你在SHOW INDEX FROM user;的结果里能看到一个Cardinality字段它能大致反映这个索引的区分度。数值越大说明重复值越少索引越有价值。注意这个值是采样估算的不是精确值但足够用来做初步判断。2. 单列索引的创建语法与核心参数2.1 三种创建索引的方式该怎么选创建单列索引有几种常见路子不是必须死记但要知道每一种的适用场景。第一种是建表时直接定义。这种方式适合新表设计阶段把索引需求和字段一起确定好避免上线后再做大表DDLCREATE TABLE user_orders ( id INT PRIMARY KEY AUTO_INCREMENT, order_no VARCHAR(64) NOT NULL, user_id INT NOT NULL, status TINYINT NOT NULL DEFAULT 1, created_at DATETIME NOT NULL, KEY idx_order_no (order_no) );第二种是给现有表加索引日常运维里用得最多。生产环境经常是“业务已经上线慢查询突然出现”这时候用ALTER TABLE给字段补索引ALTER TABLE user_orders ADD INDEX idx_user_id (user_id);第三种是CREATE INDEX写法效果和ALTER TABLE一样只是习惯不同CREATE INDEX idx_status ON user_orders (status);选哪种我的经验是新表用第一种字段确定、命名规范一次到位存量表或临时分析用后两种。要注意的是MySQL 8.0前给大表加索引可能触发在线DDL的锁表问题业务高峰期千万别直接跑。MySQL 8.0虽然支持ALGORITHMINPLACE、LOCKNONE来减轻锁影响但长事务下依然可能阻塞稳妥做法是低谷期执行或者用gh-ost这类工具。2.2 字段类型、前缀索引与命名规范索引不是随便哪列都能建得高效字段选择有讲究。数值类型INT/BIGINT在B树里比较大小非常快字符串类型则逐字符比较所以能用数值存的尽量用数值。对于超长的字符串比如VARCHAR(255)的url、description完整建索引会占很大空间MySQL也允许只取前N个字符建立前缀索引CREATE INDEX idx_url_prefix ON page_urls (url(80));前缀索引能显著缩小索引体积但有两个坑一是它无法用于覆盖索引优化二是对order by、group by的支持也打了折扣。所以前缀长度不是拍脑袋定的得看这列前面的字符区分度够不够。我一般用这条SQL来试SELECT COUNT(DISTINCT LEFT(url, 80)) / COUNT(*) AS selectivity FROM page_urls;选择性接近1.0说明前缀区分度足够高这个长度就比较合理。如果80个字符前面还高度重复就得继续加长或者换另一种字段设计思路。索引命名也值得养成习惯。我推荐统一用idx_表名_字段名例如user表的email索引就叫idx_user_email。这样在批量管理索引时看一眼名字就知道是哪个表哪个列不至于排查问题时对着抽象名字一头雾水。MySQL对索引长度的限制也要注意默认情况下InnoDB的最大键长度是3072字节使用utf8mb4字符集时换算成字符数要除以4超过就得用前缀索引。2.3 查看和删掉一个索引其实也重要调优不是只会加索引就行知道怎么查看、怎么清理冗余索引同样重要。查看命令很简单SHOW INDEX FROM user_orders;结果里重点看几列Key_name是索引名Seq_in_index是索引内的顺序Column_name是列名Cardinality是区分度估算值。如果发现有两个明显重复的索引比如idx_email和idx_user_email_dup同时存在就该保留更有用的那个删掉另一个。删除索引的语法要有印象线上清冗余索引时经常用ALTER TABLE user_orders DROP INDEX idx_status;我遇到过一次线上问题开发同学为了测试随手建了四五个几乎重复的索引业务高峰时MySQL写库性能明显下滑一查索引树比数据还多删除冗余索引后立竿见影。所以每个索引都应该有存在理由找不到理由的索引就是潜在的风险。3. 单列索引调优实战场景、失效与Explain分析3.1 这些高频场景建议优先建单列索引先明确一个原则单列索引最核心的收益是让where条件、order by排序、join关联这三类操作避免扫描全表。第一where条件里的过滤列。比如登录场景经常查email和password那条SQL是select * from user where email ?email就是高区分度列建索引效果极好。第二order by排序列。比如查询订单列表按created_at倒序如果没有索引MySQL需要把所有满足条件的行先收集起来再做一次文件排序filesort有索引时数据已经有序直接扫索引叶子就行效率完全不是一个级别。第三join的关联列。两表关联时驱动表的关联列最好有索引被驱动表一定要有索引否则join效率会非常难看。这里要多说一句不是单独出现这类场景就一定建如果过滤条件前面加了个函数或者select出来的列全是这个索引列本身都是不同玩法。但绝大多数线上慢查询80%都能靠一个合理的单列索引解决。给个直观对比。假设user表30万行没索引时执行SELECT * FROM user WHERE email test001example.com;慢查询日志里往往显示扫描行数接近30万耗时甚至到几百毫秒。一旦建了idx_email再执行同样的SQL扫描行数会变成1行左右耗时通常降到几毫秒以内。我第一次感受到索引威力就是在这样的场景下从此对全表扫描有了本能排斥。3.2 索引失效的常见雷区与改写技巧建了索引不代表SQL一定走索引下面这几种雷区我基本都踩过挨个说。第一对索引列使用函数。比如where YEAR(created_at) 2024created_at上虽然有索引但MySQL必须对每一行执行YEAR函数才能比较索引就废了。改写原则是“把函数挪到条件右边”写成where created_at 2024-01-01 and created_at 2025-01-01这样既能走索引语义也完全一致。第二隐式类型转换。如果phone列是varchar类型查询时却传了数字where phone 13800138000MySQL会先把列值转成数字再比较索引照样失效。解决办法是应用端保证参数类型和字段类型一致SQL里也尽量用引号把字符串包起来。第三like前置通配符。where name like %abc在最前面用了%B树无法基于这样无序的起点定位索引必然失效。如果业务确实需要模糊搜索更合理的方向是考虑全文索引或者用专门的搜索引擎组件。第四OR条件串联。where a 1 or b 2如果a有索引而b没有MySQL可能选择全表扫描因为要同时找到两个分支的结果做合并。改写思路是把OR拆成两个查询用UNION ALL或者在b上也建立索引让优化器有的选。第五优化器认为全表扫描更快。即便索引存在如果这个字段总体数据量巨大且选择性低优化器估算后可能放弃索引。这种情况不用强行干预更值得反思的是表设计或业务查询模式。这些失效问题最直观的验证手段就是explain。例行体检非常必要改完SQL、加完索引都跑一遍explain看key字段是不是真的用了索引别想当然。3.3 用EXPLAIN验证索引是否真的生效在调优里explain是效率最高的诊断工具。完整的explain输出字段很多做单列索引调优我重点看这四个。第一个是type它描述访问类型。从好到差常见排序是const、ref、range、index、ALL。const表示按主键或唯一索引等值查询ref是普通索引等值查询range是范围扫描index会遍历整棵索引树ALL就是全表扫描。性能调优的最低目标是把ALL改成range以上级别。第二个是key表示实际用到的索引名。如果这一列是NULL说明这条SQL没走索引需要回看上一部分的失效原因。第三个是rows预估扫描的行数。这个值越小越好它虽然不是精确值但能直观反映查询成本。第四个是Extra里面有很多关键提示。看见Using filesort意味着排序没有走索引看见Using temporary意味着查询用了临时表这两个都是需要重点关注优化的信号。举个例子我专门造了一个30万行的订单表来演示EXPLAIN SELECT * FROM user_orders WHERE user_id 10086\G在没有id_idx前type是ALLrows接近30万Extra里有时还能看到Using where。执行ALTER TABLE user_orders ADD INDEX idx_user_id(user_id);后再跑EXPLAIN SELECT * FROM user_orders WHERE user_id 10086\Gtype变成refkey显示idx_user_idrows变成几十行效果一目了然。这种前后对比是调优过程里最有成就感的瞬间。记住一个习惯每写一条重要SQL都顺手explain一下长期下来对索引的理解会扎实很多。4. 常见问题与避坑实录4.1 索引不是越多越好维护成本比你想的高在线下答疑时经常看到新手给一个表建十几个索引想法很简单“每个查询都能走索引不是更好吗”但真实情况是每次insert、update、deleteInnoDB都要同步维护所有二级索引索引越多写放大越明显。对高频写入的业务表来说这可能是比慢查询更严重的性能杀手。有个特别典型的案例一个用户行为表每天有上百万条增量写入开发为了各个维度的统计查询建了一堆单列索引结果数据导入任务从10分钟膨胀到45分钟。定位下来索引维护占据了绝大部分额外开销。后来我们把明显冗余、重复的索引清掉导入时间回到15分钟几个核心查询依然有索引可用。我的参考标准是单张表的核心单列索引尽量控制在4到5个以内除非业务场景特殊否则超过这个量级就要想想是不是该上复合索引了。另外多看看慢查询日志去确认每个索引是否真的被使用过长期用不到的索引果断删掉。MySQL 8.0有performance_schema的table_io_waits_summary_by_index_usage视图可以查索引使用情况这个技巧对排查冗余索引很有用。4.2 低选择性字段到底该不该建索引性别、状态这种只有几个固定值的列部分场景下建索引的争议一直存在。我的观点是如果过滤结果真的会筛选掉大量数据可以建否则不必建。因为索引的优势在于缩小范围像性别列如果男女各占一半索引树扫出来一半主键再逐行回表成本一点不比全表扫描低。真的需要优化这类查询时另一个思路是把低选择性条件和高选择性条件组合起来用复合索引不过这属于后面单列索引之外的进阶内容。单列索引阶段你要记住的是区分度是决策的核心指标。同样的列如果业务上90%数据是某一类剩下10%是另一类那针对那10%的查询建索引的效果才明显。你可以在业务代码里观察实际查询条件也可以直接用SQL看区分度SELECT COUNT(DISTINCT status) / COUNT(*) FROM user_orders;这个值接近1说明每一行的status都不同值得建索引接近0说明重复度极高大概率建了也白建。4.3 排序与索引的配合避免filesortorder by是除了where之外最值得关心索引的场景。明明只占几万行的表排序却占了慢查询的大头多半是filesort在拖后腿。B树叶子本身有序只要order by的列正好是索引列且排序方向和索引顺序一致默认ASC如果都是DESC也没问题MySQL就能直接按索引顺序取数完全省掉排序动作。反例是order by后的列被包了函数比如order by DATE_FORMAT(created_at, %Y-%m-%d)索引无法提供有序性filesort必然出现。还有一种情况是查询同时带了where和order by比如where user_id10086 order by created_at如果只对user_id建了单列索引MySQL会先从索引里定位user_id的行再对created_at做额外排序。这时候单列索引就不够用了更优解是建复合索引(user_id, created_at)。作为单列索引笔记我只提醒一点当一条SQL同时涉及过滤和排序时不要急着认定单列索引能优化到底很可能复合索引才是答案。先把单列索引的原理吃透后面接触复合索引时你会觉得自己在降维打击。4.4 DML场景下索引带来的额外开销与批量导入技巧索引在select查询上风光无限但在数据写入上却是实打实的负担。每一行插入都要为主键树和所有二级索引树更新节点update或delete同样如此。这就是为什么在数据初始化、大批量导入的场景有经验的DBA会建议先删索引、导数据、再重建索引。我做一个百万级数据迁移任务时用的就是这套流程。先把目标表的非主键索引全部drop掉用load data或批量insert灌入数据最后再一次性重建索引。实测下来重建索引的时间通常比带着索引逐条写入要短得多整体导入耗时能缩短一半以上。如果留着索引硬灌每插入一批数据B树就要做多次节点分裂和页写入代价高昂。对于小表的日常增删改索引开销可以忽略不需要这么极端的操作。所以判断是否采用“先删索引再导入”的策略时核心看数据量和写入频率。这些经验说穿了不值钱但能省下大量线上事故排查时间比背几条命令实用得多。调优做到最后比工具更重要的是思路。你先把单列索引的数据结构、建索引的选择标准和失效机制想明白遇到任何新表、新慢查询都能迅速判断该不该加索引。我现在的例行工作流非常简单抓慢查询SQL、用explain看执行计划、检查字段区分度、加索引后再explain对比。这套流程不花哨但几乎每次都能命中问题。也希望你把单列索引这块地基打扎实后面再用联合索引、覆盖索引去玩更复杂的优化场景时才能手到擒来。 MySQL单列索引调优实战：原理、失效与EXPLAIN分析
MySQL的索引调优是性能优化里回报最高的动作之一而单列索引是整个索引体系最基础、也最容易出效果的一块。很多慢查询原因就一条where里的字段没有索引或者有索引但没被用上。我做后端开发和数据库运维这些年每次定位线上SQL第一步永远是看表结构第二步就是explain单列索引建不建、建得对不对基本决定了这条SQL是毫秒级还是秒级。这篇文章写给两类人一类是刚接触MySQL、想知道索引是什么的初学者另一类是已经会写CRUD、但总在调优上前怕狼后怕虎的开发。我会把单列索引的原理、创建方式、实战调优和一些踩坑经验一次讲清不整那些百度百科式的废话全部按实际能用的标准来。这篇里我不碰联合索引、覆盖索引、索引下推这些进阶玩法集中火力把单列索引搞清楚。地基建牢了后面再聊复合索引才顺。1. 单列索引的核心原理与设计思路1.1 为什么全表扫描会被淘汰索引解决的核心痛点先想一个再普通不过的场景user表里有几百万数据你要按email找一个用户。没有索引时MySQL只能从第一行开始一页一页把全部数据读出来逐行比对email字段。这个操作叫全表扫描数据量一大磁盘IO直线上升SQL自然慢得让人抓狂。索引解决的问题本质上和书的目录一样。翻一本五百页的书找某个名词你不会从第一页开始逐页翻而是看目录、对页码、直接翻到对应页。单列索引就是把某一列的数据抽出来排好序存成一种额外的数据结构查询时先在这个“目录”里找到位置再去取数据。你建的每一个单列索引都是在给MySQL制作一份这样的目录。那为什么MySQL不默认给所有列都建索引因为目录本身要占空间而且你每往书里加一页内容目录也得跟着改。索引是典型的“空间换时间”并且写操作时要付出维护代价。所以索引调优的本质不是把所有列都建一遍索引而是找到真正会拖慢查询的那些字段。1.2 B树是如何组织单列索引的InnoDB存储引擎里单列索引默认使用B树数据结构。为什么不选哈希表哈希表做等值查询确实极快比如 where id 5但做范围查询where age 20和排序时哈希就无能为力了因为数据没有顺序。B树则是个折中的好方案叶子节点按列值从小到大有序排列且相邻叶子节点通过指针连在一起既能做等值匹配也能在O(logN)的开销下完成范围扫描和排序。这里要提一个关键点单列索引分为主键索引和普通索引两者叶子节点存的东西不一样。主键索引也叫聚簇索引叶子节点直接存整行数据所以通过主键查询不需要额外回表。而我们在其他列上建的普通索引叶子节点里存的是主键值查询时先找到对应主键再回表去取完整的行数据。这就意味着普通索引的查询通常至少访问两棵B树先查索引树再查主键树。我见过不少初学者以为索引越多越快其实当你理解了普通索引要回表就会明白每一步都要成本。数据量越大回表次数越多随机IO越多查询自然变慢。这也是为什么很多调优经验里会有“避免select *”“尽量让查询列在索引里”这类建议。单列索引虽然简单但回表这个机制必须时刻记得。1.3 为什么不是所有列都建索引接上文索引有空间成本和DML维护成本。一张表如果有10个单列索引每次insert、update、deleteMySQL都要同步维护这10棵B树写性能被成倍拖低。我记得有个生产环境的业务表为了让所有可能的where条件都能走索引一口气建了8个单列索引结果数据导入速度直接慢了三倍。判断一个字段值不值得建索引业内最常用的量化指标是区分度。区分度可以用基数cardinality来近似理解也就是这个列里不同值的个数。比如性别列只有“男/女”两个不同值基数就是2即便建了索引MySQL也可能觉得全表扫描更划算因为通过索引找到大量主键后再回表开销反而不如直接顺序扫描。区分度高的列比如email、订单号、手机号就非常适合建单列索引。你在SHOW INDEX FROM user;的结果里能看到一个Cardinality字段它能大致反映这个索引的区分度。数值越大说明重复值越少索引越有价值。注意这个值是采样估算的不是精确值但足够用来做初步判断。2. 单列索引的创建语法与核心参数2.1 三种创建索引的方式该怎么选创建单列索引有几种常见路子不是必须死记但要知道每一种的适用场景。第一种是建表时直接定义。这种方式适合新表设计阶段把索引需求和字段一起确定好避免上线后再做大表DDLCREATE TABLE user_orders ( id INT PRIMARY KEY AUTO_INCREMENT, order_no VARCHAR(64) NOT NULL, user_id INT NOT NULL, status TINYINT NOT NULL DEFAULT 1, created_at DATETIME NOT NULL, KEY idx_order_no (order_no) );第二种是给现有表加索引日常运维里用得最多。生产环境经常是“业务已经上线慢查询突然出现”这时候用ALTER TABLE给字段补索引ALTER TABLE user_orders ADD INDEX idx_user_id (user_id);第三种是CREATE INDEX写法效果和ALTER TABLE一样只是习惯不同CREATE INDEX idx_status ON user_orders (status);选哪种我的经验是新表用第一种字段确定、命名规范一次到位存量表或临时分析用后两种。要注意的是MySQL 8.0前给大表加索引可能触发在线DDL的锁表问题业务高峰期千万别直接跑。MySQL 8.0虽然支持ALGORITHMINPLACE、LOCKNONE来减轻锁影响但长事务下依然可能阻塞稳妥做法是低谷期执行或者用gh-ost这类工具。2.2 字段类型、前缀索引与命名规范索引不是随便哪列都能建得高效字段选择有讲究。数值类型INT/BIGINT在B树里比较大小非常快字符串类型则逐字符比较所以能用数值存的尽量用数值。对于超长的字符串比如VARCHAR(255)的url、description完整建索引会占很大空间MySQL也允许只取前N个字符建立前缀索引CREATE INDEX idx_url_prefix ON page_urls (url(80));前缀索引能显著缩小索引体积但有两个坑一是它无法用于覆盖索引优化二是对order by、group by的支持也打了折扣。所以前缀长度不是拍脑袋定的得看这列前面的字符区分度够不够。我一般用这条SQL来试SELECT COUNT(DISTINCT LEFT(url, 80)) / COUNT(*) AS selectivity FROM page_urls;选择性接近1.0说明前缀区分度足够高这个长度就比较合理。如果80个字符前面还高度重复就得继续加长或者换另一种字段设计思路。索引命名也值得养成习惯。我推荐统一用idx_表名_字段名例如user表的email索引就叫idx_user_email。这样在批量管理索引时看一眼名字就知道是哪个表哪个列不至于排查问题时对着抽象名字一头雾水。MySQL对索引长度的限制也要注意默认情况下InnoDB的最大键长度是3072字节使用utf8mb4字符集时换算成字符数要除以4超过就得用前缀索引。2.3 查看和删掉一个索引其实也重要调优不是只会加索引就行知道怎么查看、怎么清理冗余索引同样重要。查看命令很简单SHOW INDEX FROM user_orders;结果里重点看几列Key_name是索引名Seq_in_index是索引内的顺序Column_name是列名Cardinality是区分度估算值。如果发现有两个明显重复的索引比如idx_email和idx_user_email_dup同时存在就该保留更有用的那个删掉另一个。删除索引的语法要有印象线上清冗余索引时经常用ALTER TABLE user_orders DROP INDEX idx_status;我遇到过一次线上问题开发同学为了测试随手建了四五个几乎重复的索引业务高峰时MySQL写库性能明显下滑一查索引树比数据还多删除冗余索引后立竿见影。所以每个索引都应该有存在理由找不到理由的索引就是潜在的风险。3. 单列索引调优实战场景、失效与Explain分析3.1 这些高频场景建议优先建单列索引先明确一个原则单列索引最核心的收益是让where条件、order by排序、join关联这三类操作避免扫描全表。第一where条件里的过滤列。比如登录场景经常查email和password那条SQL是select * from user where email ?email就是高区分度列建索引效果极好。第二order by排序列。比如查询订单列表按created_at倒序如果没有索引MySQL需要把所有满足条件的行先收集起来再做一次文件排序filesort有索引时数据已经有序直接扫索引叶子就行效率完全不是一个级别。第三join的关联列。两表关联时驱动表的关联列最好有索引被驱动表一定要有索引否则join效率会非常难看。这里要多说一句不是单独出现这类场景就一定建如果过滤条件前面加了个函数或者select出来的列全是这个索引列本身都是不同玩法。但绝大多数线上慢查询80%都能靠一个合理的单列索引解决。给个直观对比。假设user表30万行没索引时执行SELECT * FROM user WHERE email test001example.com;慢查询日志里往往显示扫描行数接近30万耗时甚至到几百毫秒。一旦建了idx_email再执行同样的SQL扫描行数会变成1行左右耗时通常降到几毫秒以内。我第一次感受到索引威力就是在这样的场景下从此对全表扫描有了本能排斥。3.2 索引失效的常见雷区与改写技巧建了索引不代表SQL一定走索引下面这几种雷区我基本都踩过挨个说。第一对索引列使用函数。比如where YEAR(created_at) 2024created_at上虽然有索引但MySQL必须对每一行执行YEAR函数才能比较索引就废了。改写原则是“把函数挪到条件右边”写成where created_at 2024-01-01 and created_at 2025-01-01这样既能走索引语义也完全一致。第二隐式类型转换。如果phone列是varchar类型查询时却传了数字where phone 13800138000MySQL会先把列值转成数字再比较索引照样失效。解决办法是应用端保证参数类型和字段类型一致SQL里也尽量用引号把字符串包起来。第三like前置通配符。where name like %abc在最前面用了%B树无法基于这样无序的起点定位索引必然失效。如果业务确实需要模糊搜索更合理的方向是考虑全文索引或者用专门的搜索引擎组件。第四OR条件串联。where a 1 or b 2如果a有索引而b没有MySQL可能选择全表扫描因为要同时找到两个分支的结果做合并。改写思路是把OR拆成两个查询用UNION ALL或者在b上也建立索引让优化器有的选。第五优化器认为全表扫描更快。即便索引存在如果这个字段总体数据量巨大且选择性低优化器估算后可能放弃索引。这种情况不用强行干预更值得反思的是表设计或业务查询模式。这些失效问题最直观的验证手段就是explain。例行体检非常必要改完SQL、加完索引都跑一遍explain看key字段是不是真的用了索引别想当然。3.3 用EXPLAIN验证索引是否真的生效在调优里explain是效率最高的诊断工具。完整的explain输出字段很多做单列索引调优我重点看这四个。第一个是type它描述访问类型。从好到差常见排序是const、ref、range、index、ALL。const表示按主键或唯一索引等值查询ref是普通索引等值查询range是范围扫描index会遍历整棵索引树ALL就是全表扫描。性能调优的最低目标是把ALL改成range以上级别。第二个是key表示实际用到的索引名。如果这一列是NULL说明这条SQL没走索引需要回看上一部分的失效原因。第三个是rows预估扫描的行数。这个值越小越好它虽然不是精确值但能直观反映查询成本。第四个是Extra里面有很多关键提示。看见Using filesort意味着排序没有走索引看见Using temporary意味着查询用了临时表这两个都是需要重点关注优化的信号。举个例子我专门造了一个30万行的订单表来演示EXPLAIN SELECT * FROM user_orders WHERE user_id 10086\G在没有id_idx前type是ALLrows接近30万Extra里有时还能看到Using where。执行ALTER TABLE user_orders ADD INDEX idx_user_id(user_id);后再跑EXPLAIN SELECT * FROM user_orders WHERE user_id 10086\Gtype变成refkey显示idx_user_idrows变成几十行效果一目了然。这种前后对比是调优过程里最有成就感的瞬间。记住一个习惯每写一条重要SQL都顺手explain一下长期下来对索引的理解会扎实很多。4. 常见问题与避坑实录4.1 索引不是越多越好维护成本比你想的高在线下答疑时经常看到新手给一个表建十几个索引想法很简单“每个查询都能走索引不是更好吗”但真实情况是每次insert、update、deleteInnoDB都要同步维护所有二级索引索引越多写放大越明显。对高频写入的业务表来说这可能是比慢查询更严重的性能杀手。有个特别典型的案例一个用户行为表每天有上百万条增量写入开发为了各个维度的统计查询建了一堆单列索引结果数据导入任务从10分钟膨胀到45分钟。定位下来索引维护占据了绝大部分额外开销。后来我们把明显冗余、重复的索引清掉导入时间回到15分钟几个核心查询依然有索引可用。我的参考标准是单张表的核心单列索引尽量控制在4到5个以内除非业务场景特殊否则超过这个量级就要想想是不是该上复合索引了。另外多看看慢查询日志去确认每个索引是否真的被使用过长期用不到的索引果断删掉。MySQL 8.0有performance_schema的table_io_waits_summary_by_index_usage视图可以查索引使用情况这个技巧对排查冗余索引很有用。4.2 低选择性字段到底该不该建索引性别、状态这种只有几个固定值的列部分场景下建索引的争议一直存在。我的观点是如果过滤结果真的会筛选掉大量数据可以建否则不必建。因为索引的优势在于缩小范围像性别列如果男女各占一半索引树扫出来一半主键再逐行回表成本一点不比全表扫描低。真的需要优化这类查询时另一个思路是把低选择性条件和高选择性条件组合起来用复合索引不过这属于后面单列索引之外的进阶内容。单列索引阶段你要记住的是区分度是决策的核心指标。同样的列如果业务上90%数据是某一类剩下10%是另一类那针对那10%的查询建索引的效果才明显。你可以在业务代码里观察实际查询条件也可以直接用SQL看区分度SELECT COUNT(DISTINCT status) / COUNT(*) FROM user_orders;这个值接近1说明每一行的status都不同值得建索引接近0说明重复度极高大概率建了也白建。4.3 排序与索引的配合避免filesortorder by是除了where之外最值得关心索引的场景。明明只占几万行的表排序却占了慢查询的大头多半是filesort在拖后腿。B树叶子本身有序只要order by的列正好是索引列且排序方向和索引顺序一致默认ASC如果都是DESC也没问题MySQL就能直接按索引顺序取数完全省掉排序动作。反例是order by后的列被包了函数比如order by DATE_FORMAT(created_at, %Y-%m-%d)索引无法提供有序性filesort必然出现。还有一种情况是查询同时带了where和order by比如where user_id10086 order by created_at如果只对user_id建了单列索引MySQL会先从索引里定位user_id的行再对created_at做额外排序。这时候单列索引就不够用了更优解是建复合索引(user_id, created_at)。作为单列索引笔记我只提醒一点当一条SQL同时涉及过滤和排序时不要急着认定单列索引能优化到底很可能复合索引才是答案。先把单列索引的原理吃透后面接触复合索引时你会觉得自己在降维打击。4.4 DML场景下索引带来的额外开销与批量导入技巧索引在select查询上风光无限但在数据写入上却是实打实的负担。每一行插入都要为主键树和所有二级索引树更新节点update或delete同样如此。这就是为什么在数据初始化、大批量导入的场景有经验的DBA会建议先删索引、导数据、再重建索引。我做一个百万级数据迁移任务时用的就是这套流程。先把目标表的非主键索引全部drop掉用load data或批量insert灌入数据最后再一次性重建索引。实测下来重建索引的时间通常比带着索引逐条写入要短得多整体导入耗时能缩短一半以上。如果留着索引硬灌每插入一批数据B树就要做多次节点分裂和页写入代价高昂。对于小表的日常增删改索引开销可以忽略不需要这么极端的操作。所以判断是否采用“先删索引再导入”的策略时核心看数据量和写入频率。这些经验说穿了不值钱但能省下大量线上事故排查时间比背几条命令实用得多。调优做到最后比工具更重要的是思路。你先把单列索引的数据结构、建索引的选择标准和失效机制想明白遇到任何新表、新慢查询都能迅速判断该不该加索引。我现在的例行工作流非常简单抓慢查询SQL、用explain看执行计划、检查字段区分度、加索引后再explain对比。这套流程不花哨但几乎每次都能命中问题。也希望你把单列索引这块地基打扎实后面再用联合索引、覆盖索引去玩更复杂的优化场景时才能手到擒来。