mysql 覆盖索引

参考自 cnblogs.com/chenpingzhao/p/4776981.html

概念：索引包含查询需要的所有字段，则称为覆盖索引，不需要再回表。

判断标准：explain，如果 extra列=using index，则会覆盖索引

表结构

CREATE TABLE `inventory` (
  `inventory_id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
  `film_id` smallint(5) unsigned NOT NULL,
  `store_id` tinyint(3) unsigned NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`inventory_id`),
  KEY `idx_fk_film_id` (`film_id`),
  KEY `idx_store_id_film_id` (`store_id`,`film_id`),
  CONSTRAINT `fk_inventory_film` FOREIGN KEY (`film_id`) REFERENCES `film` (`film_id`) ON UPDATE CASCADE,
  CONSTRAINT `fk_inventory_store` FOREIGN KEY (`store_id`) REFERENCES `store` (`store_id`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=4582 DEFAULT CHARSET=utf8 |

　explain

mysql>  EXPLAIN SELECT store_id, film_id FROM sakila.inventory\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: inventory
         type: index
possible_keys: NULL
          key: idx_store_id_film_id
      key_len: 3
          ref: NULL
         rows: 4581
        Extra: Using index
1 row in set (0.03 sec)

　　

-- 5.7.33-log

select version()

字段	值	含义说明
id	1	查询的唯一标识（此处只有一个查询，无子查询 / 联表）
select_type	SIMPLE	简单查询（非子查询、非联表、非派生表）
table	t	本次查询涉及的表名是 t
partitions	（空）	表未做分区，无分区匹配信息
type	ref	访问类型为 ref（非主键 / 唯一索引的等值匹配，属于高效级别，仅次于 const/eq_ref）
possible_keys	idx_task_id	优化器认为可能用到的索引：idx_task_id
key	idx_task_id	实际使用的索引：idx_task_id（与 possible_keys 一致，索引选择合理）
key_len	147	使用的索引长度为 147 字节（需结合字段类型判断：如 VARCHAR(48) + 字符集 utf8mb4 时，48*3 + 3 = 147，匹配） 总 key_len = 字符字节数 + 长度标识字节 + NULL 标记字节 = 36×4 + 2 + 1 = 144 + 2 + 1 = 147 字符集：不同字符集的单个字符字节占用不同（如 utf8=3 字节 / 字符，utf8mb4=4 字节 / 字符）；
ref	const	索引匹配的是常量值（如 WHERE task_id = '固定值'）
rows	1	优化器预估需要扫描的行数（仅 1 行，效率极高）
filtered	100.0	经过条件过滤后剩余的行数比例（100% 表示无额外过滤，条件直接命中索引）
Extra	Using index	覆盖索引（Covering Index）：查询所需字段全部包含在索引中，无需回表查询主键 / 其他字段

 

 6196行增加where 后

 

三、剩余问题：为何子查询仍有「临时表 + 文件排序」？

核心原因：子查询的GROUP BY data_md5,tax_id + MAX(create_time)逻辑，当前仅用了idx_qsxx_tax_id单字段索引，无法覆盖「分组 + 求最大值」的需求，MySQL 仍需创建临时表对data_md5+create_time分组排序。

举个通俗例子：

• 现有索引仅能帮你快速找到 “tax_id=1800813957930561536” 的所有行（1 行），但要对这 1 行按data_md5分组、求create_time最大值，MySQL 仍需临时表存储数据并排序（即使只有 1 行，逻辑上仍会触发）。

四、最终优化：创建「分组 + 排序」覆盖索引（根治临时表 / 文件排序）

只需新增一个联合覆盖索引，让 MySQL 直接从索引中完成GROUP BY + MAX计算，无需临时表和排序：

sql

 

 

-- 索引字段顺序：等值过滤字段（tax_id）→ 分组字段（data_md5）→ 聚合字段（create_time）
ALTER TABLE t_tax_special_qsxx 
ADD INDEX idx_tax_md5_create (tax_id, data_md5, create_time);

 

优化后预期效果：

• 子查询（DERIVED）的Extra会从 Using where; Using temporary; Using filesort → Using where; Using index；
• 彻底消除临时表和文件排序，子查询执行效率再提升 50%+；
• 整个 UPDATE 语句从 “毫秒级” 进一步优化为 “微秒级”（几乎无耗时）。