1. 先搞懂索引到底干了一件什么事聊MySQL索引之前我想先抛一个场景。前阵子帮朋友排查一个线上慢查询某张业务表八百万行数据一条SELECT * FROM order_info WHERE user_id 128901跑了两秒多。加了一个普通索引之后直接降到二十毫秒。前后差了上百倍但代码一行没改就是加了一行create index的语句。这就是索引的价值也是为什么我觉得每个写SQL的人都应该把它彻底吃透。很多人对索引的理解停留在“加了索引查询就快了”但没想过它到底为什么快、快在哪个环节、什么情况下加了反而拖后腿。我在实际项目里见过不少开发同学遇到慢查询就盲目加索引结果索引建了一大堆数据写入变慢了磁盘空间也撑不住最后还得一个个删。所以这篇文章我想把索引的创建和删除讲透包括背后的数据结构原理、具体SQL语法、设计时的取舍标准以及生产环境里那些坑。1.1 为什么MySQL要专门搞一套索引结构在没有索引的情况下MySQL执行一条查询就是全表扫描从第一行数据开始一行一行比对直到找到目标记录。你可以把它想象成一本没有目录的字典想找一个“张”字只能从第一页翻到最后一页运气好可能几页就找到了运气不好整本翻完。索引本质上是给存储引擎另外维护的一套“目录结构”让数据查找不用再从头扫到尾。MySQL默认的存储引擎InnoDB用的是B树。B树这个结构有两个核心特性一是所有数据都存储在叶子节点并且叶子节点之间通过指针串联成双向链表二是非叶子节点只存索引键值和子节点指针每一层节点数量有限所以树的高度很矮。一般两三百万行的表B树高度也就三四层查找一次只需要走三四次磁盘I/O。全表扫描要读多少页几万甚至几十万个数据页高下立判。索引这么好用为什么不全表每一列都加索引因为索引是要额外存储、额外维护的。每插入一条记录、删除一条记录、更新某个带索引的字段InnoDB都要同步维护所有相关索引的B树。写入性能的损耗以及索引表空间占用的磁盘都是成本。这也是为什么索引创建和删除都不是可以随手为之的小事。1.2 主键索引和二级索引到底有什么区别InnoDB里数据表本身其实就是一个索引结构叫作聚簇索引。每张表默认以主键作为聚簇索引的键值叶子节点上存的是整行完整数据。如果你建表时没指定主键InnoDB会自己选一个非空唯一索引作为主键如果也没有就隐式生成一个内部主键。总之InnoDB表一定有一个聚簇索引。除了聚簇索引之外的索引统称为二级索引也有人叫辅助索引。二级索引的叶子节点只存两样东西索引键值和对应主键值。查询时如果二级索引覆盖不了全部所需字段就需要拿着主键值回头去聚簇索引里把整行数据捞出来这个过程叫回表。举个例子表结构里有主键id、字段user_id和user_name。如果我在user_id上建了一个索引执行SELECT * FROM t WHERE user_id 10086MySQL会先在二级索引的B树里定位到键值为10086的叶子节点拿到主键id然后拿着这个id去聚簇索引里读取完整行。一次查询至少走两次B树查找。但如果我只需要user_id这一个字段写成SELECT user_id FROM t WHERE user_id 10086二级索引的叶子节点里就有这个值完全不用回表这叫做覆盖索引优化。这个细节极其重要因为很多慢SQL的问题不一定是没有索引而是建了索引但select了过多不需要的列导致本来可以覆盖查询的场景硬生生多出大量回表I/O。我在后面讲创建索引时会专门提到怎么利用覆盖索引来优化SQL。2. 创建索引的4种正确姿势与设计红线创建索引听起来很简单不就是一行SQL嘛。但我在项目里review过不少人的操作真不是每个人都能把那一行SQL写对的。建错的索引、冗余的索引、选择度极低的索引带来的问题甚至比没有索引还难看。2.1 建表时直接定义索引的语法解析在建表语句里可以把索引直接写在字段定义后面也可以在表定义的末尾统一声明。两种写法看起来差不多但可读性和维护性差别挺大CREATE TABLE user_info ( id BIGINT NOT NULL AUTO_INCREMENT, user_id VARCHAR(32) NOT NULL, user_name VARCHAR(64), status TINYINT DEFAULT 1, PRIMARY KEY (id), UNIQUE KEY uk_user_id (user_id), KEY idx_user_name (user_name) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;上面这段SQL里PRIMARY KEY (id)建的是主键聚簇索引UNIQUE KEY uk_user_id (user_id)建的是唯一二级索引既约束了user_id不能重复也能加速查询KEY idx_user_name (user_name)建的是最普通的二级索引。我建议项目里统一规范索引命名主键叫PRIMARY唯一索引叫uk_开头字段名普通索引叫idx_开头加字段名。别小看这个习惯后面你们DBA巡检慢查询、或者出线上故障临时排查索引时一眼就能看出这个索引是哪个字段上的、用来干什么的省太多时间了。2.2 表已经建好了加索引用CREATE INDEX还是ALTER TABLE大多数时候表早就上线了不可能为了加索引重建表。这时候有两种写法CREATE INDEX和ALTER TABLE ... ADD INDEX。-- 方式一 CREATE INDEX idx_user_name ON user_info(user_name); -- 方式二 ALTER TABLE user_info ADD INDEX idx_user_name(user_name);这两条SQL本质上做的事情几乎一样底层都是添加一个二级索引执行过程也都会触发表的重建或在线DDL。区别在于ALTER TABLE的能力更完整它除了能加普通索引还能加主键、加唯一约束、删除主键等而CREATE INDEX只能创建普通或唯一索引。如果只是单纯加索引我个人更喜欢用CREATE INDEX语义更清晰但如果你要同时加多个索引、或者调整约束就统一用ALTER TABLE一条语句搞定多种变更。顺便说一句MySQL 8.0里支持了不可见索引和函数索引。不可见索引的意思是索引保留在表上但优化器查询时完全忽略它相当于一个开关。函数索引比如ALTER TABLE t ADD INDEX idx_year(created_at, YEAR(created_at))MySQL 8.0可以直接在虚拟列上建索引本质上还是普通索引但对那些经常把时间字段套函数的查询帮助很大。这些虽然名字听着花哨底层的索引结构并没有变。2.3 创建索引前必看字段选择度决定瓶颈这是最重要的部分。我在项目里见过有人给一个只有0和1两个值的status字段建索引还建得理直气壮。结果呢数据分布差不多各占一半优化器一计算发现全表扫描和走索引回表的成本差不多干脆就不走索引等于白建。索引值区分度也就是选择度是决定索引有没有用的关键指标。选择度的计算方式很简单COUNT(DISTINCT column) / COUNT(*)这个值越接近1说明重复值越少索引过滤效果越好越接近0说明大部分行都是同一个值索引几乎没意义。我建议建索引前先跑一条SQL验证一下SELECT COUNT(DISTINCT user_id) / COUNT(*) AS selectivity FROM user_info;如果算出来的选择度低于0.1这个字段除非用来满足覆盖索引需求否则基本不用考虑建索引。高选择度字段才是索引的主战场比如订单号、手机号、邮箱这种几乎每条记录都不同的列。还有一类特殊情况字段值很长比如URL、文章正文建索引时整字段存进去会导致索引树非常大、每一层能存的数据量变小树变高B树优势就会被削弱。这种场景我一般建议建前缀索引只取前N个字符ALTER TABLE article ADD INDEX idx_url_prefix(url(20));注意前缀索引有个硬伤它没法用于覆盖索引因为索引树里存的不是完整的列值。2.4 联合索引的最左前缀法则别再傻傻建N个单列索引很多人遇到多条件查询习惯性地每个条件字段各建一个单列索引比如WHERE user_id ? AND status ? AND create_time ?给三个字段各建一个索引。这种做法在MySQL里大部分时候只有一个索引会被真正用到剩下两个就是浪费。正确的设计是建联合索引比如(user_id, status, create_time)。联合索引的B树先按第一个字段排序第一个字段相同再按第二个字段排序以此类推。所以它能直接用于最左前缀索引里最左边的连续字段集合都可以用来加速查询。具体来说(user_id)、(user_id, status)、(user_id, status, create_time)这三种查询条件都能命中这个联合索引前段但(status, create_time)这种不包含最左列的条件就用不上这个联合索引。建联合索引时还有个实用经验把等值查询的字段放前面范围查询字段放后面。因为范围查询一旦出现后续字段就没法继续利用索引做精确定位了。比如WHERE user_id ? AND create_time ?就应该建(user_id, create_time)而不是(create_time, user_id)。这个细节做错了整个索引的过滤性能会大打折扣。我还见过一种冗余索引的场景表上已经有一个(user_id, status)联合索引又单独建了个user_id单列索引。其实单列的user_id索引完全可以被联合索引替代是完全的冗余。冗余索引不仅多占磁盘、拖慢写入还会让优化器偶尔犯迷糊。清理冗余索引本来就是DBA日常巡检的重点工作之一。3. 删除索引语法容易代价评估难删除索引看起来就是一行SQL的事但真正做起来特别是大表环境下要考虑的远比语法多得多。3.1 删除索引的标准语法与执行细节MySQL里删除索引同样有两种写法对应前面创建时的两种方式-- 方式一 DROP INDEX idx_user_name ON user_info; -- 方式二 ALTER TABLE user_info DROP INDEX idx_user_name;这两条SQL效果完全一致。DROP INDEX不能用来删主键索引需要ALTER TABLE ... DROP PRIMARY KEY。不过生产环境里多数的删除操作其实发生在三类场景一是清理冗余索引二是某个SQL改了查询条件旧索引不再被任何查询命中三是当前索引结构设计不合理需要换成新的联合索引。删除索引之前我习惯先查一下这个索引是否还有人用。方法也很简单——开启MySQL的性能字典或者慢查询日志观察一周或者用sys.schema_unused_indexes视图直接看系统统计结果SELECT * FROM sys.schema_unused_indexes WHERE object_schema yourdb;这个视图是MySQL 5.7和8.0自带的能识别出过去一段时间内从未被使用的索引。我建议不要看了这个结果就匆匆删除先确认对应的表上有没有比较低频但关键的业务查询比如每月跑一次的报表SQL也会用到这个索引一周观察窗口可能根本体现不出来。3.2 Online DDL与锁表问题大表删索引前必须知道很多同学以为MySQL执行DROP INDEX是瞬间完成的。如果你在几十万行的表上操作确实体感上是瞬间但如果是几千万行的大表就没这么简单了。InnoDB从5.6开始支持了Online DDL但不同索引操作的在线程度不一样。官方文档里索引创建是ALGORITHMINPLACE允许DML并发索引删除同样支持INPLACE。真正值得注意的不是锁不锁表而是操作期间产生的日志量和资源消耗。大表重建索引或删除索引会导致大批量数据页变更redo log写入量飙升同时主从同步也可能因为DDL产生延迟。我自己踩过一次坑凌晨对一张四千多万行的日志表做索引整理删掉一个废弃索引再加一个新索引结果主库redo log所在磁盘空间被撑满数据库直接只读了。当时整个核心链路全部挂掉最后是清理binlog加重启实例才恢复非常惊吓。我的经验是线上大表任何索引结构变更都别直接在主库上执行。要么用 pt-online-schema-change 这类工具做在线无锁变更要么至少先在从库上跑一遍确认耗时和空间消耗都在预期范围内再在低峰期执行。对于超大表我个人的建议是优先考虑新建一张新表在低峰期切换把索引结构调整放到新表创建时一次性完成比在线上表里反复增减索引稳妥得多。3.3 删除索引之后的验证执行计划里藏着答案删除索引很容易难的是删完之后的验证。很多人删完索引就跑结果等到下一次运营报表跑出来才发现一条核心SQL从原来的几十毫秒变成了几十秒这时候再补齐索引又要折腾一轮而且还要等创建索引期间的重建资源消耗。我每次删除索引后一定会把相关SQL的执行计划拉出来重新看一遍。举一个我在项目里实际遇到过的例子。有一张订单表原来有个idx_order_no (order_no)单列索引。后来因为业务需要扩大了查询维度我在(order_no, order_status)上建了联合索引。理论上单列索引是冗余的可以删掉。但删除之后我拿EXPLAIN SELECT * FROM order_info WHERE order_no 20240115001验证发现确实走的是联合索引key显示的是联合索引名type是ref完全没问题。但如果当时查的是SELECT order_no FROM ...覆盖索引的叶子节点没有完整数据就会多出回表步骤这也要提前评估。删除索引后的验证流程我总结成三步先用EXPLAIN确认关键SQL走的是期望索引type不是ALL再通过SHOW PROFILE或者直接压测对比耗时最后观察一段时间线上监控确认没有慢查询召回。三步都过了这条索引才算是真正删除干净了。4. 生产环境里最容易踩的索引坑含死锁案例分析创建和删除索引本身不难难的是索引在真实并发环境下的行为。我打算专门用一节来聊生产环境里那些让人头疼的索引相关坑这些内容很多是常规文档不会写清楚、但实战中经常撞见的。4.1 二级索引更新时的加锁顺序为什么会触发死锁先看一个很有意思的问题。MySQL通过二级索引更新数据时加锁顺序是先锁二级索引项再回表锁聚簇索引里的主键记录。这个顺序是固定的看起来也合理但高并发下偏偏容易形成交叉等待。我举个例子。假设表里有一个联合索引(group_id, order_id)两条不同的业务记录一条在group_id1组一条在group_id2组。事务A要更新1组的一条订单它的加锁顺序是锁二级索引(group_id1)项然后锁对应的主键记录。事务B要更新2组的一条订单它加锁顺序相同先锁二级索引(group_id2)项再锁对应的主键记录。两个事务各干各的看起来井水不犯河水锁资源不重叠怎么会死锁真正容易死锁的场景是两个事务同时锁了对方即将要锁的二级索引项。比如事务A先锁了对二级索引项1的回表主键记录又准备去锁二级索引项2对应的主键事务B则先锁了二级索引项2对应的主键又准备去锁二级索引项1对应的主键。这个时间窗口极小它存在的前提是更新条件涉及多个二级索引项或者一个事务里有多条更新语句形成了锁获取顺序的不一致。MySQL死锁日志里看到类似“waiting for this lock to be granted”两条互相等待的记录大概率就是这种交叉。我实际遇到过一个真实的死锁案例。场景是一张订单表二级索引建在(shop_id, order_status)上有一个批量结转业务会同时更新某几家店铺的状态。业务代码先查出一批shop_id对应的订单按shop_id排序后逐条UPDATE。但两个并发事务查到的shop_id集合顺序不一样事务A先更新shop_id1的订单事务B先更新shop_id2的订单两条UPDATE操作都走二级索引加锁而后各自动态扩展锁范围最终死锁导致业务侧报错重试重试又加重锁争用。解决办法也不复杂优先考虑业务操作按主键排序后执行让所有事务的加锁顺序统一或者干脆改成对同一类shop_id的订单先加锁再批量更新。核心思路就是让并发事务争取锁资源的顺序尽量一致交叉等待自然就不存在了。这也是索引设计之外的另一个重要认知索引影响的不只是查询速度还有锁的粒度和顺序。4.2 索引表空间与碎片为什么删数据后表空间不变成空白索引表空间这个热搜词值得展开聊一下。InnoDB表的数据和索引统一存储在表空间文件里无论是ibd文件还是系统表空间。删除大量数据之后你有没有发现ibd文件大小几乎没变这是因为InnoDB回收的最小单位是数据页页内数据删空后页本身在索引树上的空间会保留复用而不是直接归还给操作系统。这种情况在频繁insert和delete的日志表上特别明显。我处理过一个案例某报表明细表每天写入几十万条三个月清理一次历史数据DELETE删了上千万行但表空间文件一直涨到40GB没降下来。后续一插入数据直接落回那些被删除后留下的空闲页里文件大小不变性能也说得过去但大量碎片导致索引扫描的随机I/O增多页面密度低实际可用空间被严重浪费。索引碎片整理的标准做法有两个ALTER TABLE table_name ENGINEInnoDB或者OPTIMIZE TABLE table_name。这两个操作都会重建整张表包括所有二级索引整理后碎片会消除表空间会明显收缩。注意对大表来说重建意味着原表整个被复制一遍需要至少两倍表空间大小的空闲磁盘而且执行期间有大量磁盘I/O和写入务必在低峰期操作。顺带一提我可以在information_schema里查看索引的存储统计信息SELECT TABLE_NAME, INDEX_NAME, STAT_VALUE FROM information_schema.innodb_index_stats WHERE TABLE_NAME order_info;这个视图能看到索引的统计值比如叶子节点数量和大小通过对比多张表可以快速定位有没有异常膨胀的索引。我在做容量规划时会定期拉这个视图做趋势分析。4.3 索引失效的六种常见情况一条条对号入座索引建了不代表查询就一定走索引。以下六种场景放在任何版本的MySQL里都成立值得挨个排查一遍。第一种是在索引列上做函数运算。WHERE DATE(create_time) 2024-01-01即使create_time上有索引也别指望走索引因为优化器无法对函数处理后的结果做范围匹配。解决办法是改成WHERE create_time 2024-01-01 00:00:00 AND create_time 2024-01-02 00:00:00这也解释了为什么MySQL 8.0的函数索引和虚拟列在实际业务里非常实用。第二种是隐式类型转换。索引列是varchar类型查询条件却传了数字MySQL会把列上每个值转成数字再比较索引照样失效。比如WHERE phone 13800138000phone列是varchar这个查询就是典型的索引杀手。统一下发参数类型的强类型校验或者改造SQL加引号都是常见的修复方式。第三种是前导模糊查询。LIKE %keyword%没有前缀B树只能顺序扫描。如果业务确实需要模糊搜索可以评估改用全文索引或者把关键字额外存一张倒排表。第四种是OR两侧条件不都走索引。WHERE user_id 10086 OR status 1如果status上有索引、user_id上没有那基本就是全表扫描。第五种是联合索引不满足最左前缀。这个前面讲过不再重复。第六种是优化器判断走索引代价更高。最常见的就是必要条件过滤比例太低比如索引选择度极低优化器宁可全表扫描。这类问题是数据分布问题单独建索引解决不了需要重新设计SQL语义或者考虑别的手段。我在实际项目中排查慢SQL时基本按照这个顺序往下捋每次都很快定位。强烈建议把这六种情况抄下来贴在工位上写SQL之前扫一眼能帮你少走非常多弯路。4.4 索引命中的确认手段EXPLAIN输出怎么看才不踩坑最后再分享一个非常实用但是经常被人忽视的工具EXPLAIN。有些同学会看EXPLAIN的select type和table但只看这两个远远不够。判断索引是否真正命中重点看四列key、type、rows、Extra。key列显示的是优化器最终选择使用的索引名如果为NULL说明没有走索引。type列的优化等级从高到低大概是 system const eq_ref ref range index ALL至少要达到ref或range以上才算有效索引访问。rows列显示预估扫描行数这个值越接近实际命中的行数越好如果rows显示百万级别但type是ALL基本确定是全表扫描。Extra列里最值得关注的两个词一个是Using index代表覆盖索引即查询的所有字段都能从二级索引里拿到不用回表这是最优状态另一个是Using filesort代表排序没走索引可能需要额外的临时文件排序。我之前优化过一个列表页排序慢的问题就是让排序字段进了联合索引Extra里不再出现Using filesort从几百毫秒降到几十毫秒。另外说一句MySQL 8.0的EXPLAIN还支持EXPLAIN ANALYZE它能真实执行语句并输出每一步的耗时和扫描行数对于复杂调优场景非常直观。我建议排查慢SQL时先用EXPLAIN做静态分析再需要细粒度定位时用EXPLAIN ANALYZE跑一遍两个工具配合着用。5. 我的几条实战经验与建议索引管理这块儿做到最后拼的不是某个具体的语法而是整体设计纪律。我对团队的要求很简单建索引必须有SQL验证过程要么用EXPLAIN对比前后执行计划要么用性能监控数据说话删索引必须走审批并且要查清楚这个索引在所有SQL里是否真的无人使用不能只看系统视图还要跟业务开发确认低频报表的依赖命名规范从头立住永远不要出现随手敲的index1index2这种名字否则半年后连你自己都不确定它是干嘛的。还有一个小技巧是我自己一直在用的给每个索引写上备注。MySQL虽然不能在索引上直接注释但可以在字段上用COMMENT字段记录用途或者在团队的数据库维护文档里把每个索引的使用场景、创建日期、负责人、关联SQL全部记录下来。这个文档看着很土很啰嗦但在排障时是真的救命。另外想说一句索引设计是一个动态过程。业务初期数据量小单索引随便建没问题数据量涨到千万级联合索引和覆盖索引的设计就要重新审视过亿后可能连索引本身都要考虑拆分到单独的磁盘文件甚至引入归档表来减少热数据规模。不要指望一次设计就能吃一辈子定期做索引健康检查什么季度做一次全量巡检这就是我经历过线上事故后养成的肌肉记忆。mysql创建索引和删除索引这两件事写出来也就几十行SQL但背后牵扯的数据结构、锁机制、成本评估、业务理解每一层都有学问。希望这篇文章能帮你把这些点串起来下次再面对慢查询和索引调优不只是会敲那两行命令而是真正知道自己在干什么。
