MySQL索引访问方式实战：从const到index merge的深度解析与优化

1. 项目概述一次彻底的MySQL索引访问方式实战搞数据库性能优化索引这块儿绕不过去。我们天天在EXPLAIN里看到type列写着const、ref、range这些词但你真的清楚它们背后意味着什么吗是走的主键扫描还是全表扫描是单列索引生效还是多个索引合并不同的访问方式性能可能差出几个数量级。这次我们不谈空洞的理论直接上手通过一系列精心设计的SQL实验把const、ref、range、index、all以及index merge这六种主要的索引访问方式彻底搞明白。目标是让你看完就能在自己的数据库上复现并且能一眼看穿EXPLAIN执行计划精准定位慢查询的根源。无论你是刚接触MySQL不久的开发者还是需要经常处理性能问题的DBA这篇实战总结都能给你带来直接的帮助。2. 实验环境搭建与数据准备2.1 构建测试表与数据理论说得再好不如动手跑一跑。我们首先需要一个足够“真实”的测试环境。这里我创建了一张user_orders表模拟一个常见的用户订单场景。字段设计上故意混合了各种数据类型和索引可能性。CREATE TABLE user_orders ( id bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 主键, user_id int(11) NOT NULL COMMENT 用户ID, order_sn varchar(32) NOT NULL COMMENT 订单号唯一, amount decimal(10,2) NOT NULL DEFAULT 0.00 COMMENT 订单金额, status tinyint(4) NOT NULL DEFAULT 0 COMMENT 状态0-待支付1-已支付2-已发货3-已完成4-已取消, product_category varchar(50) DEFAULT NULL COMMENT 商品类目, created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 创建时间, updated_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 更新时间, PRIMARY KEY (id), UNIQUE KEY uk_order_sn (order_sn), KEY idx_user_id (user_id), KEY idx_status (status), KEY idx_created_at (created_at), KEY idx_user_status (user_id, status), KEY idx_category_created (product_category, created_at) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT用户订单表;建完表光有结构不行还得有数据。我通过存储过程插入了约100万行数据确保数据分布有一定随机性同时让某些条件如特定user_id、status有足够的数据量供我们观察。order_sn是唯一的user_id和status则有大量重复值这是为了模拟真实业务中常见的索引选择性差异。注意在你自己测试时数据量不必强求百万级但至少要有几万行否则MySQL优化器可能因为数据量太小而选择全表扫描无法观察到预期的索引访问方式。另外记得在插入数据后再创建索引或者先删除索引再大批量插入最后再加回来这样效率更高。2.2 理解EXPLAIN输出关键字段我们的所有分析都基于EXPLAIN命令的输出。在深入每种访问方式前必须搞清楚几个核心字段type访问类型这是我们今天研究的核心。它描述了MySQL决定如何查找表中的行。从最优到最差大致是systemconsteq_refreffulltextref_or_nullindex_mergeunique_subqueryindex_subqueryrangeindexALL。keyMySQL实际决定使用的索引。如果为NULL则没有使用索引。key_len使用的索引的长度。可以用来判断索引是否被完全使用例如联合索引使用了前几列。rowsMySQL预估为了找到所需的行需要读取的行数。这是一个预估值但非常关键。Extra包含额外的信息例如Using index覆盖索引、Using where在存储引擎层检索行后服务器层再次过滤、Using filesort需要额外排序等。我们的实验将紧紧围绕type字段的变化并结合其他字段来解读MySQL优化器的选择。3. 六种核心索引访问方式深度解析3.1 const基于主键或唯一索引的常量查询这是效率最高的访问方式没有之一。当查询条件是对主键或唯一索引进行等值匹配时MySQL知道最多只会返回一条记录因此将其优化为一个常量。实验1主键等值查询EXPLAIN SELECT * FROM user_orders WHERE id 100;执行计划分析type:constkey:PRIMARYrows: 1 优化器直接通过主键B树定位到唯一记录开销极小。实验2唯一索引等值查询EXPLAIN SELECT * FROM user_orders WHERE order_sn SN1234567890;执行计划分析type:constkey:uk_order_snrows: 1 虽然order_sn不是主键但UNIQUE KEY保证了其唯一性因此访问方式也是const。实操心得const访问是理想状态。在设计查询时尽可能利用主键或唯一索引进行等值查找。在高并发点查场景如根据订单号查详情这种效率至关重要。另外即使表有上亿数据const查询的耗时也基本稳定在毫秒级。3.2 ref使用非唯一索引的等值匹配当通过普通索引非唯一索引进行等值匹配时MySQL使用ref访问。因为索引列值不唯一可能返回多条记录MySQL需要沿着索引叶子节点链表进行扫描。实验3普通单列索引等值查询EXPLAIN SELECT * FROM user_orders WHERE user_id 500;假设user_id500的用户有几十个订单。执行计划分析type:refkey:idx_user_idrows: 预估扫描的行数例如 45 优化器通过idx_user_id索引树找到user_id500的第一个条目然后顺序扫描叶子节点链表获取所有匹配的主键id最后回表查询完整数据。实验4最左前缀匹配EXPLAIN SELECT * FROM user_orders WHERE user_id 500 AND status 1;执行计划分析type:refkey:idx_user_status(联合索引)key_len: 5 (user_idint为4字节statustinyint为1字节)rows: 预估行数例如 10 这里使用了联合索引idx_user_status的最左列user_id进行等值匹配也属于ref访问。如果条件中是user_id和status都进行等值匹配则type可能仍然是ref如果返回行数较多也可能因为结果集很小而更优。注意事项ref的效率取决于索引的选择性。如果user_id有10万个不同值那么user_id500可能只匹配很少行效率很高。但如果status只有5个值0-4那么where status1虽然也能走idx_status索引但因为它匹配表中约20%的数据效率可能还不如全表扫描这时优化器可能会放弃索引。这就是索引选择性的概念不重复的索引值数量 / 表总记录数。比值越高索引选择性越好使用索引的效率就越高。3.3 range利用索引进行范围扫描当查询条件使用索引列进行范围比较BETWEENINLIKE prefix%时会使用range访问。它需要在索引树上定位到范围的起点然后顺序扫描到范围的终点。实验5基于时间的范围查询EXPLAIN SELECT * FROM user_orders WHERE created_at 2023-01-01 AND created_at 2023-02-01;执行计划分析type:rangekey:idx_created_atrows: 预估在时间范围内的订单数 优化器使用idx_created_at索引找到2023-01-01的位置然后向后扫描直到2023-02-01之前。实验6IN列表查询很多人以为IN是等值比较但实际上对于索引来说IN相当于多个等值条件优化器通常将其视为范围扫描。EXPLAIN SELECT * FROM user_orders WHERE user_id IN (100, 200, 300);执行计划分析type:rangekey:idx_user_idrows: 预估三个用户的总订单数 虽然IN列表里的每个值都是等值但组合在一起MySQL会从索引中查找多个点并将其归类为range访问。踩坑记录LIKE查询只有左前缀匹配LIKE abc%才能用到range访问。如果是LIKE %abc或LIKE %abc%索引就失效了因为无法利用索引的有序性。对于后缀匹配需求可以考虑额外创建反转字符串的索引或者使用全文索引。3.4 index全索引扫描index访问意味着扫描整个索引树。这通常发生在两种情况下查询所需的所有列都包含在索引中覆盖索引且没有合适的where条件来缩小范围但优化器认为扫描整个索引比扫描全表ALL成本更低。查询使用了索引但需要按索引的顺序进行排序而避免额外的排序操作。实验7覆盖索引扫描EXPLAIN SELECT user_id, status FROM user_orders;执行计划分析type:indexkey:idx_user_statusExtra:Using index这个查询只要求user_id和status它们恰好构成了idx_user_status联合索引的全部列。因此MySQL决定直接扫描整个idx_user_status索引树。因为索引大小通常远小于表数据大小且不需要回表所以这比ALL全表扫描要快。实验8利用索引避免排序EXPLAIN SELECT * FROM user_orders ORDER BY created_at LIMIT 10;执行计划分析type:indexkey:idx_created_atExtra:Using index虽然这里没有where条件但ORDER BY created_at要求按时间排序。如果走全表扫描需要对100万行数据进行排序Using filesort成本极高。而idx_created_at索引本身是按created_at有序的直接按顺序扫描索引的前10条然后回表取数据可能更快。优化器在这里选择了index访问来避免排序。核心要点index全索引扫描并不总是坏事。当它是“覆盖索引扫描”时效率可能非常高。判断标准是看Extra字段是否有Using index。有则是好事没有则意味着虽然扫描了索引但还需要回表如果索引本身很大性能可能并不理想。3.5 ALL全表扫描最糟糕的访问方式即从头到尾扫描聚簇索引对于InnoDB就是主键索引的所有叶子节点。当没有索引可用或者优化器计算后发现使用索引的成本比全表扫描还高时就会发生。实验9无索引条件查询EXPLAIN SELECT * FROM user_orders WHERE amount 100;执行计划分析type:ALLkey:NULLrows: 全表行数约100万 因为amount字段上没有索引优化器别无选择只能全表扫描。实验10索引选择性极差导致优化器放弃索引-- 假设status只有0,1,2,3,4五个值且分布均匀 EXPLAIN SELECT * FROM user_orders WHERE status 1;执行计划分析type:ALLkey:NULL(可能也可能使用idx_status但type为ref取决于优化器估算)rows: 全表行数 即使status上有索引idx_status但因为其选择性太差匹配约20%的数据回表的随机I/O成本可能远超顺序扫描聚簇索引的成本。优化器经过成本计算后可能会选择全表扫描。性能排查关键在慢查询日志中看到typeALL这就是一个强烈的优化信号。首先检查WHERE条件涉及的字段是否有索引如果没有考虑添加。如果有索引则可能是索引失效如对索引列做了函数运算WHERE DATE(created_at)...或者索引选择性太差需要重新评估索引设计或查询写法。3.6 index merge多索引合并扫描这是MySQL提供的一种优化手段当一条SQL的WHERE条件中包含多个针对不同单列索引的条件并且用OR或AND连接时MySQL有可能分别扫描这些索引然后将结果在内存中进行合并交集intersect或并集union。实验11OR条件的索引合并index_merge_unionEXPLAIN SELECT * FROM user_orders WHERE user_id 100 OR status 1;执行计划分析type:index_mergekey:idx_user_id,idx_statusExtra:Using union(idx_user_id,idx_status); Using where优化器分别使用idx_user_id索引查找user_id100的行和使用idx_status索引查找status1的行然后对两个结果集取并集union最后回表获取完整数据并过滤掉可能重复的行。实验12AND条件的索引合并index_merge_intersectEXPLAIN SELECT * FROM user_orders WHERE user_id 100 AND product_category Electronics;执行计划分析type:index_mergekey:idx_user_id,idx_category_createdExtra:Using intersect(idx_user_id,idx_category_created); Using where优化器分别使用idx_user_id和idx_category_created只用到最左列product_category索引获取各自满足条件的主键id集合然后对这两个主键集合取交集intersect最后用交集的主键回表查询。重要提醒index merge听起来很美好但它通常是次优选择的象征。它意味着没有合适的联合索引来同时满足这些条件。分别扫描多个索引、合并结果、再回表这一系列操作的成本往往高于使用一个高效的联合索引。在上面的实验12中最优解应该是创建一个(user_id, product_category)的联合索引这样type会变成更高效的ref直接定位到数据省去了合并步骤。因此看到index_merge时应该首先考虑是否能通过创建更合适的联合索引来优化。4. 联合索引与最左前缀原则实战理解了单索引的访问方式联合索引才是性能优化的重中之重。它的使用遵循最左前缀原则。实验13联合索引的全字段匹配EXPLAIN SELECT * FROM user_orders WHERE product_category Books AND created_at 2023-06-01;执行计划分析type:rangekey:idx_category_createdkey_len: 根据字段类型和字符集计算这里product_category是varchar(50)且为NULLutf8mb4下一个字符最多4字节实际使用的长度是50*4 2长度标识位再加上created_at的4字节。key_len值可以验证两列都被用到了。 因为条件中product_category是等值created_at是范围所以访问类型是range。索引被有效利用。实验14跳过最左列索引失效EXPLAIN SELECT * FROM user_orders WHERE created_at 2023-06-01;执行计划分析type:ALL或indexkey:NULL或idx_category_createdExtra: 可能为Using where由于WHERE条件没有包含联合索引idx_category_created的最左列product_category因此无法使用该索引进行高效查找。优化器可能选择全表扫描ALL或者如果它认为扫描整个索引index再过滤比全表扫描快也会选择后者但效率都不高。实验15仅使用最左列等值匹配EXPLAIN SELECT * FROM user_orders WHERE product_category Books;执行计划分析type:refkey:idx_category_createdkey_len: 仅包含product_category列的长度 这符合最左前缀原则索引有效访问类型为ref。设计经验设计联合索引时将选择性最高的列放在左边通常效果更好。但同时也要考虑查询频率。例如如果WHERE product_category ? AND created_at ?是最常见的查询那么(product_category, created_at)的顺序就是正确的。如果常见的查询是WHERE created_at ?那么单列索引或把created_at放在联合索引最左可能是更好的选择但这需要权衡。5. 执行计划深度诊断与优化案例光看懂type还不够需要结合整个EXPLAIN的输出进行综合诊断。案例一个看似用了索引的慢查询EXPLAIN SELECT * FROM user_orders WHERE user_id 10000 ORDER BY created_at DESC LIMIT 20;假设user_id 10000的数据有50万行。可能的执行计划type:range(使用了idx_user_id)key:idx_user_idExtra:Using index condition; Using filesort问题分析虽然用到了idx_user_id索引进行范围查找但排序字段created_at不在这个索引中也不符合最左前缀。因此MySQL需要先通过索引找到50万行数据的主键然后回表取出这50万行完整数据在内存或磁盘上进行排序Using filesort最后取前20条。filesort是性能杀手。优化方案1使用覆盖索引如果查询只需要部分列可以创建覆盖索引。-- 修改查询只选择需要的列并创建覆盖索引 ALTER TABLE user_orders ADD INDEX idx_user_created_cover (user_id, created_at, amount); EXPLAIN SELECT user_id, created_at, amount FROM user_orders WHERE user_id 10000 ORDER BY created_at DESC LIMIT 20;优化后Extra字段会出现Using index表示直接从索引中获取数据无需回表和filesort。优化方案2改变查询方式或索引设计如果必须SELECT *可以考虑创建一个以created_at为主的索引并利用索引的有序性。-- 创建一个适合排序和过滤的索引 ALTER TABLE user_orders ADD INDEX idx_created_user (created_at DESC, user_id); -- 改写查询利用索引的有序性进行“延迟关联” SELECT * FROM user_orders a INNER JOIN ( SELECT id FROM user_orders WHERE user_id 10000 ORDER BY created_at DESC LIMIT 20 ) b ON a.id b.id ORDER BY a.created_at DESC;子查询利用(created_at, user_id)索引快速找到满足条件的主键id因为created_at在前排序天然有效然后只回表20次获取完整数据效率大幅提升。6. 索引选择与优化器成本模型为什么有时候有索引优化器却不用这背后是MySQL的成本模型在决策。优化器会估算两种方式的成本索引扫描成本从索引中查找记录的成本 根据主键回表查询的成本。全表扫描成本顺序扫描聚簇索引所有页的成本。影响成本的关键因素包括innodb_stats_persistent是否持久化统计信息。innodb_stats_persistent_sample_pages统计信息采样的页数。索引的基数Cardinality通过SHOW INDEX FROM user_orders;查看表示索引列中不同值的数量估算值。这个值是通过采样估算的可能不准确实验16更新统计信息影响执行计划-- 强制优化器重新分析表更新统计信息 ANALYZE TABLE user_orders; -- 再次运行一个边界查询观察执行计划是否变化 EXPLAIN SELECT * FROM user_orders WHERE status 1;如果status1的数据分布不均匀旧的统计信息可能导致优化器做出错误判断。ANALYZE TABLE会更新统计信息可能使执行计划从ALL变为ref或者反之。运维建议对于数据更新频繁的大表定期比如在低峰期执行ANALYZE TABLE是必要的。也可以考虑使用FORCE INDEX或USE INDEX提示来在特定场景下强制优化器使用某个索引但这应是最后手段因为数据分布变化后强制索引可能适得其反。