做数据库性能优化这几年被问到最多的一个问题就是明明建了索引为什么查询还是慢这个问题几乎贯穿了所有MySQL使用者的成长路径。索引这个东西表面上看就是一条CREATE INDEX语句的事但真正要在生产环境里打造一套高效、稳定、不拖累写入的索引体系需要考虑的远比大多数人想象的多。这篇文章我想从索引的底层原理讲起结合实际的慢SQL案例和排查经验把MySQL索引优化这件事拆开揉碎。内容主要面向已经会写基本SQL、但希望进一步搞懂索引设计逻辑的开发同学和DBA新人。文章里不会堆砌晦涩的公式而是用我这些年踩过的坑、做过对比实验总结出来的经验来说话。读完你至少能搞明白三件事索引为什么能提速、什么场景下索引会失效、以及遇到慢SQL时应该按什么思路去分析和优化。1. 索引到底在解决什么问题索引的本质就是给数据表建立一套快速定位数据的“目录结构”。但很多人在建索引的时候根本没想明白这个“目录结构”在MySQL里到底长什么样。1.1 全表扫描的代价MySQL为什么需要索引先看一个最基础的问题没有索引时MySQL是怎么查数据的假设你有张用户表里面有500万行记录现在要执行一条SELECT * FROM user WHERE phone 13800138000。MySQL Server层会把这条语句交给InnoDB存储引擎InnoDB的做法是从表对应的第一个数据页开始一页一页地读每读一页就把里面的记录逐行拿出来比对phone字段。这个过程叫全表扫描。InnoDB的数据页默认大小是16KB假设每行记录平均占用200字节一页大约能放80行。500万行数据就是62500个数据页乘以16KB差不多要扫描1GB左右的数据。即便磁盘是SSD顺序读完这1GB数据也要几百毫秒到一秒以上。一旦并发上来数据库的IO立刻被打满。索引解决的就是这个问题。有了索引MySQL不需要扫描所有数据页而是先在一棵更小的树结构里找到目标记录所在的页号再去对应的页里读取记录。这个查找过程能把百万级甚至千万级数据的查询耗时从秒级降到毫秒级。1.2 B树为什么能扛住千万级数据MySQL的InnoDB引擎默认使用B树作为索引结构。为什么不是二叉树、不是哈希表、也不是B树二叉树的问题在于高度不可控。数据量一大树的高度就会变高最坏情况下退化成链表跟全表扫描没区别。而且二叉树每个节点只保存一个键值存储利用率很低。哈希表的问题在于它是无序的。等值查询确实快O(1)的复杂度但一旦遇到范围查询、排序、模糊匹配哈希索引就完全无能为力了。B树相比B树的优势在于B树把所有数据都存放在叶子节点并且叶子节点之间通过指针串联成一个有序链表。这意味着不管是等值查找还是范围查询都能高效完成。同时由于非叶子节点只存索引键和指针一个16KB的页能存下非常多的键值树的高度被压得很低。我做一个粗略计算假设索引键是8字节的bigint页指针6字节一个非叶子节点大约能存16KB / 14B ≈ 1170个条目。三层B树根节点层中间层叶子层能存多少数据1170 × 1170 × 每叶子页可容纳的记录数按100行算≈ 1.37亿行。也就是说千万级数据量的表三层B树足够覆盖查询时只需要3次磁盘IO就能定位到目标数据页。这就是B树能扛住海量数据的根本原因。1.3 索引不是免费的写入代价和空间代价搞懂了索引能带来什么更要清楚索引要付出什么代价。每次执行INSERT、UPDATE、DELETE时InnoDB不仅要维护数据本身还要同步维护这张表上的所有二级索引。想象一下一份合同数据既要更新底稿又要按编号、按日期、按客户名称各抄一份目录任何一份目录都要跟着改。表上的索引越多写入时的额外开销就越大。空间代价同样不能忽视。一个表的二级索引需要额外的磁盘空间存储索引字段越多、长度越长占用的空间越大。我在实际生产里见过一张不到2000万行的流水表因为前人不加节制地建了11个索引索引占用的磁盘空间比表数据本身还大每次批量导入数据耗时翻倍。所以索引优化从来不是“越多越好”而是在查询性能和写入开销之间找到平衡点。2. 索引类型选型主键索引、二级索引与覆盖索引创建索引之前先搞清楚索引有哪些类型各自负责什么场景。很多优化方案选错根源在于对索引类型的理解停留在“能用就行”的层面。2.1 主键索引怎么设计才好用主键索引也叫聚簇索引它有两个重要特征数据行物理存储在主键索引的叶子节点上表数据按照主键顺序排列。换句话说InnoDB表其实就是一棵以主键为排序键的B树。主键设计有个容易被忽视的点主键值应该尽量单调递增。为什么因为数据是按主键顺序存放的。如果你的主键是随机生成的UUID新插入的行可能落在已有数据中间InnoDB需要移动后面的数据来腾位置或者触发页分裂产生大量碎片和随机IO。我在压测环境里做过对比同样写入200万行数据自增bigint主键比随机UUID主键的导入速度快了接近40%表碎片率也明显更低。实际工作中我推荐三个选择优先用BIGINT UNSIGNED AUTO_INCREMENT这种方案简单且顺序写入效率最高如果有分布式场景可以选择BIGINT类型的雪花ID仍然保证趋势递增只有在数据完全不允许暴露自增值、且对写入性能不敏感的场景才考虑UUID而且最好用UUID_TO_BIN这类函数把字符串转成二进制存储。另外提一句强烈不建议用业务字段做主键。我见过有人用身份证号做主键先不说隐私合规问题光是一个字段长度远超bigint这一点就会让每个二级索引的叶子节点存“更大的主键值”导致整个表的索引变大、性能变差。2.2 二级索引与最左前缀原则二级索引也叫非聚簇索引是我们在日常优化中建得最多的索引。它的叶子节点不存完整数据行只存索引键和主键值。所以通过二级索引查数据通常需要两步先查到主键值再回到主键索引里去取整行数据这个过程叫回表。在二级索引的设计里最重要的规则是最左前缀原则。联合索引(a, b, c)实际生效的是以a为首的所有前缀组合(a)、(a, b)、(a, b, c)。如果你写出WHERE b 1 AND c 2这样的条件因为跳过了最左边的a这个联合索引就起不到作用。但是有一点要注意最左前缀不是“SQL里必须按索引顺序写where条件”MySQL优化器会自动调整条件顺序。真正决定能不能用上索引的是查询条件里有没有包含索引的第一个字段。我曾经跟同事解释这个规则时用过一个比方联合索引就像一本按“拼音首字母-城市-街道”编排的通讯录你可以只查拼音首字母或者拼音首字母加城市但你不能跳过首字母直接按街道去查。2.3 覆盖索引这个“免费午餐”回表虽然比全表扫描快得多但每回表一次就是一次随机IO。能不能避免能用覆盖索引。覆盖索引的概念很简单你查询的所有字段都包含在同一个二级索引里。这样InnoDB在二级索引的B树上就能拿到全部需要的数据不需要再回主键索引取行。执行计划里Extra字段如果显示Using index就说明命中了覆盖索引。举一个我优化过的真实例子。订单表order_info有30个字段业务上频繁执行这条SQLSELECT order_id, order_status, create_time FROM order_info WHERE buyer_id 10086 ORDER BY create_time DESC LIMIT 20;原表只有一个buyer_id单列索引每次查询通过buyer_id定位到一个买家所有的订单然后回表取完整数据行再用create_time排序取前20条。买家订单多的时候可能回表上千次慢的时候两秒多。优化方案很简单把索引改成(buyer_id, create_time, order_id, order_status)。改完之后查询所需的三个字段全都在索引里排序也能直接利用索引顺序连filesort都省了。优化后同样场景耗时从2秒多降到30毫秒以内其他字段一律不需要就是典型的覆盖索引红利。3. 索引失效场景排查为什么建了索引还是慢这是整个索引优化里最有价值的部分。生产环境里最让人抓狂的不是没有索引而是索引明明存在执行计划里却看不到或者走了索引但比全表扫描还慢。3.1 高频失效场景速查我在处理慢SQL工单时总结过一份高频索引失效清单几乎每次都能命中其中一条第一是条件字段上做了函数运算。比如WHERE DATE(create_time) 2024-01-01这种写法MySQL没法直接利用create_time上的索引因为每个索引键都要套一层DATE()之后才能跟常量比较。正确做法是改成WHERE create_time 2024-01-01 00:00:00 AND create_time 2024-01-02 00:00:00。第二是隐式类型转换。最常见的是字符串字段被赋了数值。表里phone字段是varchar你写WHERE phone 13800138000MySQL会把phone全列转成数值再比较索引直接失效。反过来如果字段是数值类型而传入的是字符串MySQL能把常量转成数值反而没事。这里有个非常隐蔽的坑传入值类型不匹配时EXPLAIN可能显示的type还是ref但实际扫描行数会异常增加需要结合rows字段一起判断。第三是LIKE前置百分号。WHERE name LIKE %小明%这种写法因为百分号在最前面无法利用B树的有序性只能全索引扫描。把百分号挪到后面变成小明%就能走索引。但注意即使改成后缀匹配对于超长字符串字段扫描成本依然不低必要的时候可以考虑全文索引或搜索引擎。第四是OR条件导致索引失效。WHERE a 1 OR b 2如果a和b不是同一个索引MySQL可能选择全表扫描因为它要对两个条件分别查索引再合并结果成本反而更高。解决办法是用UNION拆分或者建联合索引。5.0以上的新版本优化器有些场景能自动走index merge但别依赖这个行为。第五是NOT IN、!、IS NOT NULL这类否定条件。这类运算的优化空间很有限优化器经常判断不走索引更划算。真要处理海量数据的排除型查询建议改写成LEFT JOIN ... WHERE ... IS NULL的格式或者直接调整业务逻辑。3.2 用EXPLAIN看清执行计划排查索引失效问题第一件事永远是打开EXPLAIN。我见过很多开发同学不看执行计划凭直觉加索引、删索引折腾半天问题还在。用EXPLAIN时我重点看的字段有这几个type列是最直观的从好到差依次是system、const、eq_ref、ref、range、index、ALL。前四种属于高效访问range表示范围扫描也还行index是全索引扫描一般不算理想ALL就是全表扫描最差。很多时候只要type从ALL变成ref或range一条SQL的性能就能有质的飞跃。key列表示实际用到的索引。注意有些SQL虽然建了索引但key列是NULL说明这个索引压根没被用上要回到查询条件里找原因。rows列是优化器估算的需要扫描的行数这个值跟实际值相差越大越说明统计信息可能有问题或者SQL写法存在隐式转换等坑。Extra列信息量极大。看到Using filesort意味着排序没法利用索引顺序数据量大时要重点优化看到Using temporary说明用了临时表常见的GROUP BY、DISTINCT场景容易触发看到Using index是好消息覆盖索引命中看到Using where表示存储引擎返回数据后Server层又做了过滤需要注意过滤条件是否因为用了函数而失去索引能力。3.3 优化器放弃索引的几个隐藏原因还有一种情况比索引失效更隐蔽优化器其实评估过索引但主动放弃了。数据量很小的表就经常这样总共几千行数据全表扫描一次只要0.1毫秒走索引反而要查索引树、回表多好几次随机IO优化器当然选全表扫描。这个现象不是故障不用管它。优化器依赖的统计信息过期也会导致判断错误。InnoDB维护的统计信息是采样估算的当表数据发生大规模变更比如删掉一半数据后统计信息可能跟实际严重偏差导致优化器选错执行计划。解决办法是执行ANALYZE TABLE重新收集统计信息。另外一个反直觉的情况是隐式排序。有时候你的查询条件能走索引但需要按另一列排序优化器算下来使用索引加排序比直接扫描更快它就会放弃已有索引。处理这类问题时建议直接查看EXPLAIN ANALYZEMySQL 8.0的真实执行耗时而不是只看估算值能省去很多猜测。4. 慢SQL优化实战全流程理论铺垫了这么多核心还是要落到实战上。这一节我用一个完整的优化案例带你走一遍从发现问题、分析定位到最终优化的全流程。4.1 开启慢查询日志找到目标SQL优化慢SQL的第一步是“找到它”。云数据库基本都默认开启了慢查询日志自建MySQL需要手动确认。# 临时开启重启后失效 SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1; SET GLOBAL log_queries_not_using_indexes ON; # 查看配置 SHOW VARIABLES LIKE slow_query_log%; SHOW VARIABLES LIKE long_query_time;long_query_time表示超过多少秒的SQL会被记录我建议开发环境设成0.5生产环境先设1观察一段时间后再决定是否收紧。log_queries_not_using_indexes可以把没有使用索引的SQL也记进来非常适合做索引失效的普查。慢查询日志会记录每条SQL的执行时间、锁等待时间、扫描行数和返回行数。扫描行数特别多但返回行数特别少的SQL是优化性价比最高的目标这类SQL直接把数据库大部分IO都吃掉了。4.2 一个典型慢SQL的优化全记录有一年我接手了一个电商系统的性能优化线上频繁报警DBA排查后给我扔过来一条慢SQLSELECT o.order_id, o.order_amount, u.user_name, u.user_phone FROM order_info o JOIN user_info u ON o.buyer_id u.user_id WHERE o.order_status 0 AND o.create_time 2024-03-01 00:00:00 AND o.create_time 2024-04-01 00:00:00 ORDER BY o.create_time DESC LIMIT 100;order_info表当时有800万行order_status有大量值为0的记录order_info上的索引情况是主键order_id二级索引idx_buyer_id还有一个是idx_create_time。user_info上的主键是user_id。第一次EXPLAIN的结果显示order_info表的type是ALLrows估算120万Extra里还有Using filesort。虽然idx_create_time存在但查询里同时过滤order_status 0这个过滤条件不在索引里所以走idx_create_time仍然要对大量记录回表再判断order_status优化器直接放弃了这个索引。我的调整思路分两步。第一步把过滤和排序整合进一个联合索引。创建(order_status, create_time)联合索引。为什么选这个顺序order_status是等值条件create_time是范围条件。最左前缀原则下等值字段放前面能让B树先按order_status锁定一小部分数据再按create_time有序读取。第二步尝试消除回表。查询需要返回order_amount这个字段但索引里没有所以要回表。如果查询频率非常高可以考虑把order_amount也纳入索引做成覆盖索引也就是(order_status, create_time, order_amount)。这里其实做了取舍order_amount属于高频查询字段加上之后查询就不需要回表了但索引体积会变大一些写入性能略有牺牲。经过压测这个牺牲在接受范围内。优化后的执行计划type从ALL变成了rangerows估算从120万降到1万左右Extra里的Using filesort彻底消失因为create_time的顺序直接由索引保证。线上实际耗时从3.2秒降到120毫秒效果立竿见影。4.3 大表DDL变更的落地方案在800万行的表上直接执行CREATE INDEX会有锁表风险。MySQL 8.0之前的版本ADD INDEX虽然支持INPLACE算法但整个过程仍然会占用大量IO和CPU资源在高并发业务下容易造成主从延迟飙升。我的建议是使用pt-online-schema-change这类在线DDL工具。它的原理是先创建一张结构相同的新表然后通过触发器把增量数据同步到新表最后在原表和新表之间做一次原子切换。整个过程中原表依然可读可写业务几乎无感知。还有个细节是加索引时机的选择尽量避开业务高峰期放在凌晨低峰期执行。就算工具再成熟几百G的大表做一次结构变更短则十几分钟长则数小时任何意外都可能发生。给生产环境做变更之前先在测试环境用同样容量的数据演练一遍记录实际耗时这样线上执行时才心里有底。5. 索引维护与冗余治理很多人只关注怎么建索引却忽略了索引的日常维护。一个表上线时间越长索引数量往往会越来越失控。我接手过的最夸张的一个表上光是(a, b)和(a, b, c)这两个联合索引就同时存在冗余得毫无意义。5.1 冗余索引的成本到底有多大冗余索引的危害不像慢SQL那么显眼但它会持续不断地磨损数据库性能。每次写入都要维护所有索引冗余索引相当于让写入操作多做无用工。同时占用的磁盘空间会推高备份恢复的时间和成本。排查冗余索引没有特别高深的方法核心就是看索引的“左前缀”关系。索引(a, b)和(a, b, c)同时存在时后者其实覆盖了前者的绝大部分场景前者大概率可以删掉。还有一种情况是(a)单列索引和(a, b)联合索引并存除非有非常多只查a字段的独立场景否则单列索引也是冗余的。我自己常用的查询语句是依据information_schema表的统计信息来筛候选索引SELECT TABLE_NAME, INDEX_NAME, GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX) AS index_columns FROM information_schema.STATISTICS WHERE TABLE_SCHEMA your_db GROUP BY TABLE_NAME, INDEX_NAME ORDER BY TABLE_NAME, INDEX_NAME;拿到所有索引清单后人工过一遍特别关注那些列名重复的索引然后结合业务方确认是否可以删除。5.2 用performance_schema监控索引真实使用情况判断一个索引到底有没有用不能靠猜要看真实数据。MySQL 5.7及以上版本可以通过sys.schema_unused_indexes视图查看哪些索引从未被使用过。SELECT * FROM sys.schema_unused_indexes WHERE object_schema your_db;这个视图读取的是performance_schema里的表IO统计信息如果索引从未参与过任何查询这里就会列出来。对这类索引我建议先在测试环境观察一两个业务周期确认没有报表或后台任务依赖它再考虑删除。不过要注意schema_unused_indexes只能反映服务启动后到当前时间的使用情况。如果服务刚重启不久数据是不完整的。最好跑一两个星期后再查看。删除索引的SQL很简单ALTER TABLE order_info DROP INDEX idx_buyer_id;但删索引之前一定要做两件事第一确认没有长尾的定时任务、数据同步脚本引用这个索引第二先在从库上执行删除观察一段时间没有异常再操作主库。我在文章里反复强调这些操作规范因为生产环境里一次不经意的错误索引操作代价是业务停摆远不是省那几分钟能补偿的。5.3 索引维护的日常巡检建议索引维护不该是出问题才处理应该纳入日常巡检。我给自己定了一个简单的巡检周期每周看一次慢查询日志统计TOP10慢SQL涉及的表每两周跑一次schema_unused_indexes视图核对冗余索引每个月抽查几张大表的索引大小和表碎片率。表碎片的问题也要提一句。频繁的插入和删除会让B树的叶子节点产生大量空洞表现为表实际占用的空间远超理论值全表扫描性能明显下降。处理碎片的方法是执行OPTIMIZE TABLE但注意这个操作会锁表同样建议在低峰期执行。也可以通过对表重建的方式来整理碎片效果类似。6. 常见问题与排查技巧实录索引优化过程中踩过的坑不少我把最有代表性的问题整理成一个速查表方便大家在实际工作中对照使用。问题现象可能原因排查方向解决办法建了索引但EXPLAIN显示没走条件字段使用函数查看where条件有无包裹函数改写为范围条件查询变慢但写入也变慢索引过多过冗余查sys.schema_unused_indexes清理冗余索引varchar字段查询慢隐式类型转换检查传入参数类型传入字符串常量或修改字段类型ORDER BY排序慢排序字段不在索引中查看Extra含Using filesort联合索引包含排序字段分页越翻越慢OFFSET过大分析LIMIT执行计划使用延迟关联或游标分页统计信息不准导致选错索引大表频繁增删查看EXPLAIN rows估算是否离谱执行ANALYZE TABLE还有一个调优技巧值得单独说就是延迟关联处理深分页。当需要分页到很深的页时比如LIMIT 100000, 20MySQL必须先扫描前100000行再丢弃成本很高。延迟关联的核心思路是先用一个覆盖索引查出20个主键ID再回到原表取完整行SELECT o.* FROM order_info o JOIN ( SELECT order_id FROM order_info WHERE create_time 2024-03-01 00:00:00 ORDER BY create_time LIMIT 100000, 20 ) t ON o.order_id t.order_id;这个写法里内层子查询只访问覆盖索引数据量小不需要回表速度非常快。外层再按主键关联取行由于主键聚簇查找本身效率很高整体性能比直接深分页好得多。排查索引问题时还有一点容易被忽视索引选择跟数据分布强相关。同样的SQL在数据量100万的时候走索引很快数据量涨到2000万之后可能优化器反而选全表扫描因为这时候索引的选择性变差了。遇到这种“以前很快现在突然慢了”的问题不要急着删索引先分析数据分布的变化。写到最后分享一个我坚持了很久的习惯每次上线新的索引或修改SQL前都会保存一份优化前后的EXPLAIN结果和执行耗时记录按日期归档。几个月后再回看这些记录能清晰地看到索引体系的演进过程也能很快定位是哪次变更导致了性能回退。索引优化从来没有一劳永逸的银弹它更像是数据库健康管理的一部分需要持续观察、持续调整。希望大家读完这篇文章后再遇到“明明建了索引为什么还慢”的问题时能有一套清晰的排查思路而不是一头扎进SQL里瞎试。