1. 从一次慢查询说起为什么索引访问方式决定了性能天花板那天下午监控系统突然报警一个核心业务接口的响应时间从平时的几十毫秒飙升到了十几秒。登录服务器打开慢查询日志一条看似平平无奇的SELECT语句赫然在列执行时间长达8秒。我第一反应是去看它的执行计划EXPLAIN结果在type列看到了那个最不想看到的词ALL。全表扫描。对于一个百万级别的用户表这无疑是性能灾难。但问题来了这个查询明明在user_id和status字段上都有单列索引为什么优化器不用呢这就是今天要深入探讨的核心MySQL的索引访问方式。const、ref、range、index、all还有index_merge这些出现在EXPLAIN输出type列中的关键字不仅仅是几个简单的标签它们直接描绘了MySQL从存储引擎获取数据的具体路径决定了查询是“飞”起来还是“爬”过去。理解这些访问方式你就能从执行计划的“结果”反推“原因”精准定位索引失效、SQL写法不佳等性能瓶颈从而进行有效的优化。这不仅仅是DBA的必修课也是每一位需要与数据库打交道的后端开发必须掌握的硬核技能。接下来我将结合大量实战案例带你逐一拆解这六种核心索引访问方式让你真正看懂执行计划并具备优化复杂查询的能力。2. 索引访问方式全解析从最优到最差在MySQL的EXPLAIN输出中type字段表示连接类型或访问类型它描述了MySQL决定如何查找表中的行。性能从最优到最差大致排序为systemconsteq_refreffulltextref_or_nullindex_mergeunique_subqueryindex_subqueryrangeindexALL。我们重点讨论其中最核心、最常见的六种。2.1 const基于主键或唯一索引的“常量”访问这是效率最高的访问方式没有之一。当MySQL能通过查询条件直接定位到唯一的一行时就会使用const。核心条件查询条件必须包含主键或唯一索引的所有列并且这些条件必须是等值比较。因为主键和唯一索引保证了数据的唯一性所以MySQL知道最多只会返回一行。实战案例 假设我们有一个用户表id是主键email字段上有唯一索引uniq_email。CREATE TABLE users ( id int NOT NULL AUTO_INCREMENT, name varchar(100) DEFAULT NULL, email varchar(100) DEFAULT NULL, PRIMARY KEY (id), UNIQUE KEY uniq_email (email) );执行以下查询EXPLAIN SELECT * FROM users WHERE id 1;你会看到type: const。同样EXPLAIN SELECT * FROM users WHERE email aliceexample.com;也会是const。为什么这么快对于InnoDB存储引擎表数据本身就是按照主键组织的一棵B树聚簇索引。通过主键等值查询相当于直接在聚簇索引的这棵树上进行了一次精确的二分查找时间复杂度是O(log N)。对于唯一索引过程类似先在唯一索引的B树上找到对应的主键值再通过这个主键值回表到聚簇索引中取出整行数据这个过程也叫“回表”。由于最多只有一行效率极高。注意system是const的一个特例当查询的表只有一行通常是系统表时会出现实践中极少遇到可以视同const。2.2 ref非唯一索引的等值匹配当查询条件使用了普通索引非唯一索引进行等值匹配时MySQL会使用ref访问方式。由于普通索引不强制唯一性可能会匹配到多行记录。核心条件使用普通索引的列进行等值比较。或者使用唯一索引/主键的部分列对于复合索引进行等值比较且这部分列是索引的最左前缀。实战案例 我们在users表的name字段上建立一个普通索引ALTER TABLE users ADD INDEX idx_name (name);执行查询EXPLAIN SELECT * FROM users WHERE name 张三;此时type列显示为ref。因为叫“张三”的用户可能不止一个。工作原理与性能优化器通过idx_name索引树快速找到所有name张三的索引记录。每一条索引记录都包含对应的主键值。然后MySQL需要根据这些主键值逐个回表到聚簇索引中取出完整的行数据。因此ref的性能取决于匹配到的行数。如果匹配行数很少高选择性性能接近const如果匹配行数很多低选择性则会产生大量的随机I/O回表操作性能下降明显。复合索引的最左前缀原则这是ref访问中一个极易出错的关键点。假设有一个复合索引idx_status_created (status, created_at)。WHERE status 1可以使用该索引访问类型为ref。WHERE status 1 AND created_at 2023-10-01也可以使用类型也是ref如果created_at也是等值。WHERE created_at 2023-10-01无法使用这个索引因为违反了最左前缀原则。status没出现索引失效会退化为全表扫描ALL。2.3 range利用索引进行范围扫描当查询条件使用索引列进行范围比较时MySQL会使用range访问方式。常见的范围操作符包括BETWEENIN()LIKE prefix%前缀匹配。核心条件索引列参与了范围查询。实战案例 继续使用users表假设我们在created_at字段上有索引idx_created (created_at)。EXPLAIN SELECT * FROM users WHERE created_at BETWEEN 2023-01-01 AND 2023-12-31; EXPLAIN SELECT * FROM users WHERE id 1000; -- 对主键的范围查询也是 range EXPLAIN SELECT * FROM users WHERE name LIKE 张%; -- 如果name有索引且是前缀匹配这些查询的type都会是range。工作原理与范围优化range访问会在索引树中进行“区间扫描”。优化器会定位到范围开始的第一个值然后沿着索引叶子节点的链表顺序扫描直到遇到不满足条件的第一个值为止。这是一个非常高效的操作因为它避免了扫描整个索引或全表。一个重要的边界IN()到底是ref还是range这是一个常见的困惑点。IN()列表在内部可能被优化器处理为多个等值条件的“或”运算。如果IN()列表中的值非常多优化器可能认为全表扫描成本更低。通常情况下对于索引列IN()会被视为range访问。例如WHERE id IN (1, 5, 10)执行计划type通常是range。你可以把它理解为多个等值条件id1 OR id5 OR id10的优化形式。范围查询对复合索引的影响这是设计索引时需要重点考虑的。对于复合索引idx_a_b_c (a, b, c)WHERE a 1 AND b 2索引只能用到a列。因为a列是范围查询其后的索引列b无法再以等值方式被高效使用索引的有序性在a列范围匹配后被打乱。WHERE a 1 AND b 2 AND c 3索引可以用到a, b两列。c列无法用于过滤因为b列是范围查询。 这个原则可以简化为范围查询列之后的索引列将失效。2.4 index全索引扫描index访问方式意味着MySQL决定扫描整棵索引树。这通常发生在两种场景查询所需的所有列都包含在某个索引中覆盖索引且没有更好的条件来限制索引扫描范围。查询需要按索引的顺序进行排序或分组而全索引扫描的成本低于全表扫描排序。虽然它扫描了索引的全部记录但因为它只遍历索引树通常比表数据文件小得多且顺序I/O所以速度通常比ALL全表扫描要快。实战案例 假设users表有索引idx_name (name)。EXPLAIN SELECT name, id FROM users ORDER BY name;这个查询只选取name和id。id是主键必然存在于idx_name这个二级索引的叶子节点中InnoDB的二级索引会存储主键值。因此这是一个“覆盖索引”查询。优化器发现与其扫描更大的聚簇索引全表不如顺序扫描更小的idx_name索引直接就能拿到name和id无需回表。此时type为index。另一个例子EXPLAIN SELECT COUNT(*) FROM users;如果users表有一个非空的二级索引比如idx_name优化器很可能会选择扫描这个更小的二级索引来计数而不是扫描主键索引或全表。此时type也是index。index与ALL的抉择当查询无法使用索引进行有效过滤时优化器会在index全索引扫描和ALL全表扫描之间做成本估算。如果存在一个较小的、能覆盖查询列的索引index可能胜出。否则就会是ALL。2.5 ALL全表扫描性能的“黑洞”ALL意味着MySQL将读取聚簇索引对于InnoDB或数据文件对于MyISAM中的每一行并检查是否满足WHERE子句的条件。这是最低效的访问方式必须极力避免尤其是在大表上。什么情况下会出现ALL查询条件没有使用索引这是最常见的原因。例如对没有索引的列进行条件过滤或者查询条件违反了索引的最左前缀原则。索引选择性太差优化器经过成本计算认为使用索引需要回表的成本高于直接全表扫描。例如一个“性别”字段上建有索引查询WHERE gender M可能匹配表中50%的数据这时使用索引反而更慢。查询需要表中绝大多数数据当WHERE条件过滤掉的行非常少时全表扫描的连续I/O可能比大量随机I/O索引扫描回表更高效。实战中的ALL陷阱 回到开头的案例查询条件是WHERE user_id 1001 AND status 0两个字段都有单列索引为什么是ALL 问题出在SQL的写法上。如果写成了SELECT * FROM orders WHERE user_id 0 1001 AND status 0;或者SELECT * FROM orders WHERE DATE(create_time) 2023-10-01; -- create_time有索引在字段上使用了函数或表达式会导致索引失效优化器无法使用索引进行查找只能退而求其次选择ALL。2.6 index_merge索引合并一种特殊的优化策略index_merge是MySQL提供的一种优化手段当一条SQL的WHERE条件中包含多个针对不同索引的范围或等值条件并且这些条件通过OR连接时优化器可能会尝试分别使用这些索引进行扫描然后将各自的结果集进行合并交集AND、并集OR或排序后取并集。常见的index_merge算法index_merge_intersection求交集。用于AND条件例如WHERE key1 1 AND key2 2且key1和key2都有独立的索引。index_merge_union求并集。用于OR条件例如WHERE key1 1 OR key2 2。index_merge_sort_union排序后求并集。用于OR连接的范围查询需要对获取到的主键ID进行排序去重。实战案例 表orders有idx_user_id (user_id)和idx_status (status)两个独立的单列索引。EXPLAIN SELECT * FROM orders WHERE user_id 1001 OR status 2;优化器可能会选择index_merge。它会分别通过idx_user_id索引找出所有user_id1001的主键集合A通过idx_status索引找出所有status2的主键集合B然后对A和B求并集最后根据合并后的主键集合回表查询数据。index_merge的利与弊利在某些无法建立理想复合索引的场景下index_merge提供了一种绕过全表扫描的可能。弊通常意味着索引设计可能存在问题。index_merge需要多次扫描索引、在内存中合并结果集其成本往往高于使用一个合适的复合索引。在上面的例子中如果有一个复合索引(user_id, status)或(status, user_id)查询效率会高得多。index_merge更像是优化器在现有次优索引下的“补救措施”而非首选方案。重要提示不要盲目追求执行计划中出现index_merge。看到它你应该首先反思能否通过调整索引设计如创建复合索引来获得更优的ref或range访问在MySQL 5.6及以前版本index_merge优化本身也存在一些bug和性能不稳定问题需要谨慎对待。3. 实战演练从执行计划到索引优化理解了理论我们通过一个复杂的实战案例将知识串联起来完成一次完整的性能诊断与优化。3.1 场景构建与问题SQL假设我们有一个电商订单表orders结构如下CREATE TABLE orders ( id bigint NOT NULL AUTO_INCREMENT, order_no varchar(32) NOT NULL COMMENT 订单号, user_id bigint NOT NULL COMMENT 用户ID, merchant_id int NOT NULL COMMENT 商家ID, amount decimal(10,2) NOT NULL COMMENT 订单金额, status tinyint NOT NULL COMMENT 状态1待支付2已支付3已发货4已完成5已取消, pay_time datetime DEFAULT NULL COMMENT 支付时间, create_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, update_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uk_order_no (order_no), KEY idx_user_id (user_id), KEY idx_merchant_id (merchant_id), KEY idx_status (status), KEY idx_create_time (create_time) ) ENGINEInnoDB;表中有约1000万条数据。现在有一个后台运营查询需求查询某个商家在某段时间内状态为“已完成”的订单并按支付时间倒序排列分页展示。最初的SQL可能这样写SELECT id, order_no, user_id, amount, pay_time FROM orders WHERE merchant_id 12345 AND status 4 AND create_time BETWEEN 2023-06-01 00:00:00 AND 2023-06-30 23:59:59 ORDER BY pay_time DESC LIMIT 0, 20;3.2 执行计划分析与问题诊断我们对这条SQL执行EXPLAIN或者更好的EXPLAIN FORMATJSON查看更详细信息EXPLAIN SELECT id, order_no, user_id, amount, pay_time FROM orders WHERE merchant_id 12345 AND status 4 AND create_time BETWEEN 2023-06-01 00:00:00 AND 2023-06-30 23:59:59 ORDER BY pay_time DESC LIMIT 0, 20;假设我们得到的简化版执行计划如下idselect_typetabletypepossible_keyskeykey_lenrowsExtra1SIMPLEordersindexidx_merchant_id,idx_status,idx_create_timeidx_create_time518642Using where; Using filesort解读这个“糟糕”的计划type: index优化器选择了idx_create_time索引进行全索引扫描。为什么因为ORDER BY pay_time DESC要求按pay_time排序但pay_time上没有索引。然而优化器错误地或者说在成本估算下选择了一个它可用的索引进行扫描而不是为了WHERE条件选择最优索引。key: idx_create_time证实了它正在扫描create_time的索引树。rows: 18642预估要扫描这么多索引记录。Extra: Using where; Using filesort这是两个严重的警告信号。Using where表示存储引擎返回的行需要在Server层再用WHERE子句的其他条件merchant_id 12345 AND status 4进行过滤。因为idx_create_time索引只包含了create_time和主键id无法判断merchant_id和status。Using filesort表示MySQL需要额外进行一次排序操作来满足ORDER BY pay_time DESC。filesort可能在内存或磁盘上进行当数据量大时非常耗时。性能瓶颈分析 这个执行计划效率极低。它先全扫描create_time索引约1.8万行然后对每一行回表再用WHERE条件过滤最后对过滤出的结果在内存或磁盘上进行排序。如果merchant_id12345且status4的订单在6月份只有几十个那么这个计划就做了大量无用功。3.3 优化方案设计与实施我们的目标是让查询能快速定位到满足WHERE条件的数据并避免或优化排序。方案一创建复合索引首选最根本的解决方法是创建一个能够覆盖WHERE条件和ORDER BY的复合索引。这里有两个思路针对WHERE过滤的索引(merchant_id, status, create_time)。这个索引可以高效地完成merchant_id等值过滤、status等值过滤、create_time范围过滤。但是它无法避免pay_time的filesort。尝试覆盖WHERE和ORDER BY的索引(merchant_id, status, pay_time)。这个索引可以高效完成merchant_id和status的过滤并且pay_time已经有序可以避免filesort。但是create_time的范围条件无法直接利用索引需要作为Using where条件在索引扫描后过滤。如何选择这取决于数据的分布。如果merchant_id12345 AND status4在6月份的数据量很小比如几十上百条那么方案2可能更好。因为它能利用pay_time索引避免排序虽然需要额外过滤create_time但过滤的数据集已经很小了。如果merchant_id12345 AND status4在6月份的数据量依然很大那么方案1可能更好它能最大程度减少需要扫描和处理的数据行。在实践中我们可以创建一个包含更多列的索引来尝试“覆盖查询”ALTER TABLE orders ADD INDEX idx_merchant_status_paytime_ctime (merchant_id, status, pay_time, create_time);这个索引的设计逻辑是前两列(merchant_id, status)用于等值过滤快速缩小范围。第三列pay_time用于满足ORDER BY避免filesort。注意由于pay_time前面有等值列所以ORDER BY merchant_id, status, pay_time是可以用到这个索引排序的而我们的ORDER BY pay_time在merchant_id和status是常量的情况下也可以利用索引的有序性这被称为“松散索引扫描”的一种特例但MySQL优化器在5.6/5.7后对这类场景优化得很好。第四列create_time被包含进来使得create_time的条件可以在索引中直接判断避免回表后过滤实现“索引条件下推”ICP。创建索引后再次EXPLAINidselect_typetabletypepossible_keyskeykey_lenrowsExtra1SIMPLEordersrangeidx_merchant_status_paytime_ctimeidx_merchant_status_paytime_ctime10150Using index condition优化效果type: range访问方式从全索引扫描提升到了范围扫描效率质的飞跃。key使用了我们新建的复合索引。rows: 150预估扫描行数从1.8万降到了150过滤精度极大提高。Extra: Using index condition表示使用了“索引条件下推”create_time的范围条件在存储引擎层就进行了过滤减少了回表的数据量。Using filesort消失了因为ORDER BY pay_time可以利用索引(..., pay_time, ...)的有序性无需额外排序。方案二使用FORCE INDEX引导优化器临时方案如果因为某些原因不能修改索引例如表太大加索引影响业务并且你确信某个现有索引更优可以尝试强制使用索引。但这只是权宜之计不推荐长期使用。SELECT id, order_no, user_id, amount, pay_time FROM orders FORCE INDEX (idx_merchant_id) WHERE merchant_id 12345 AND status 4 AND create_time BETWEEN 2023-06-01 00:00:00 AND 2023-06-30 23:59:59 ORDER BY pay_time DESC LIMIT 0, 20;这强制使用idx_merchant_id优化器可能会选择ref访问先过滤merchant_id然后再过滤其他条件。但排序问题filesort依然存在性能提升有限。3.4 延伸思考分页深度优化即使有了合适的索引当分页到很深的页码时例如LIMIT 100000, 20性能依然会骤降。因为MySQL需要先读取并丢弃前100000行。此时可以优化为SELECT o.id, o.order_no, o.user_id, o.amount, o.pay_time FROM orders o INNER JOIN ( SELECT id FROM orders WHERE merchant_id 12345 AND status 4 AND create_time BETWEEN 2023-06-01 00:00:00 AND 2023-06-30 23:59:59 ORDER BY pay_time DESC LIMIT 100000, 20 ) AS tmp ON o.id tmp.id ORDER BY o.pay_time DESC;子查询利用覆盖索引只查id和排序字段pay_time快速定位到需要的那20条数据的主键id然后通过JOIN回表取出所有字段。由于子查询的数据量小只有20个id回表成本很低。这是处理深度分页的经典优化手段。4. 避坑指南与高级技巧掌握了基本原理和常规优化后一些更深层次的“坑”和技巧能让你在复杂场景下游刃有余。4.1 索引失效的常见陷阱汇总除了违反最左前缀原则以下情况也会导致索引失效或性能低下在索引列上做计算、函数或类型转换-- 失效 WHERE YEAR(create_time) 2023; WHERE amount * 1.1 100; WHERE user_id 1001; -- user_id是int传入字符串可能触发隐式类型转换 -- 优化为 WHERE create_time 2023-01-01 AND create_time 2024-01-01; WHERE amount 100 / 1.1; WHERE user_id 1001;使用!或操作符大多数情况下!会导致索引失效进行全表扫描。因为索引树无法高效地定位“不等于某个值”的所有记录。使用IS NULL或IS NOT NULL单列索引可能失效取决于数据分布。如果列中NULL值很少IS NOT NULL可能走索引反之则可能全表扫描。复合索引中如果某一列可以为NULL且查询条件包含IS NULL索引可能只用到该列之前的部分。使用OR连接多个索引列条件如前所述可能触发index_merge但效率通常不如复合索引。如果OR连接的列没有索引则会导致全表扫描。LIKE以通配符开头LIKE %keyword或LIKE %keyword%无法使用索引因为索引的B树是按照前缀排序的。LIKE keyword%可以使用索引range访问。查询条件中使用IN子查询如果IN里面的子查询返回结果集很大优化器可能选择全表扫描。通常建议用JOIN改写。4.2 理解Using indexUsing whereUsing filesort等Extra信息EXPLAIN的Extra列提供了非常重要的额外信息Using index表示查询使用了“覆盖索引”所有需要的数据都在索引中取得无需回表。这是性能最好的情况之一。Using where表示Server层需要在存储引擎返回行之后再应用WHERE子句中的其他条件进行过滤。如果type是index或range但出现了Using where说明索引没能完全覆盖查询条件。Using index condition索引条件下推ICP。MySQL 5.6引入。存储引擎在读取索引时就会根据WHERE条件中索引包含的列进行过滤将过滤后的结果再返回给Server层。这减少了回表的数据量是Using where的优化版。Using filesort需要额外的排序步骤。如果排序数据量小于sort_buffer_size在内存中完成否则使用磁盘临时文件。必须尽力消除尤其是大数据量时。Using temporary需要使用临时表来处理查询常见于GROUP BY和DISTINCT操作且没有索引可以利用时。同样需要优化。4.3 索引选择性与统计信息优化器选择哪个索引依赖于它的“成本模型”。成本估算的基础是索引的选择性和表的统计信息。选择性指不重复的索引值基数与表总记录数的比值。选择性越高越接近1索引价值越大。例如user_id选择性通常很高gender选择性很低。统计信息SHOW TABLE STATUS LIKE orders;或查询information_schema.TABLES可以查看表的统计信息。ANALYZE TABLE orders;命令可以手动更新统计信息。如果统计信息过期优化器可能做出错误的选择。有时你会发现优化器没有选择你认为最优的索引。除了检查SQL写法还可以检查统计信息是否准确。在数据分布发生重大变化后手动更新统计信息是好的实践。4.4 联合索引的列顺序设计黄金法则设计复合索引时列的顺序至关重要。一个通用的经验法则是将选择性最高的列放在最前面但需要结合查询的具体条件。对于等值查询和范围查询混合的情况有一个更精确的原则等值条件列放在最前面。范围条件列放在后面。用于排序ORDER BY或分组GROUP BY的列放在范围条件列之后如果范围条件列之后还有等值列则放在等值列之后。用于覆盖查询的列放在最后。以前面的订单查询为例假设我们最常见的查询是WHERE merchant_id ? AND status ? ORDER BY pay_time DESC那么索引(merchant_id, status, pay_time)是最优的。如果还需要过滤create_time可以将其加在最后(merchant_id, status, pay_time, create_time)但要注意create_time作为范围条件时其后的列如果还有将无法用于索引查找。5. 系统化调优思路与工具使用最后将索引优化纳入日常的数据库开发和运维流程中。5.1 建立性能基线与监控不要等到线上出问题才去优化。应该对核心业务SQL建立性能基线记录其正常情况下的执行时间、扫描行数等。启用慢查询日志设置合理的long_query_time如1秒定期分析。使用性能模式Performance SchemaMySQL 5.7/8.0提供了更细粒度的性能监控可以追踪所有SQL的执行统计。配置监控告警对数据库的QPS、慢查询数量、CPU/I/O等关键指标设置告警。5.2 使用EXPLAIN ANALYZE进行实际执行分析MySQL 8.0.18引入了EXPLAIN ANALYZE它会实际执行查询并输出实际的执行时间、循环次数等详细信息比传统的EXPLAIN估算更准确。EXPLAIN ANALYZE SELECT * FROM orders WHERE merchant_id 12345 AND status 4;输出会包含每个步骤的实际执行成本是终极的优化验证工具。5.3 索引维护与生命周期管理索引不是建得越多越好。每个索引都会增加写操作INSERT/UPDATE/DELETE的成本因为需要维护额外的B树。需要定期审视冗余索引如idx_a (a)和idx_a_b (a, b)前者通常是冗余的因为复合索引可以用于单独查询a列。从未使用过的索引通过sys.schema_unused_indexes视图MySQL 5.7或慢查询日志分析找出长期不用的索引并删除。索引碎片整理对于写频繁的表索引会产生碎片影响性能。定期执行OPTIMIZE TABLE table_name;或ALTER TABLE table_name ENGINEInnoDB;可以重建表并整理碎片但这是重量级操作需要在业务低峰期进行。索引的优化是一场永无止境的旅程它没有银弹需要结合具体的业务场景、数据特征和查询模式不断地观察、分析、实验和调整。从看懂EXPLAIN中的type开始你已经掌握了打开数据库性能黑盒的第一把钥匙。记住最好的优化往往来自于对业务逻辑的深入理解以及“将计算推向数据”这一朴素而强大的原则——让索引尽可能地帮你完成过滤和排序减少不必要的数据移动和计算。