Mysql：覆盖索引

一、什么是覆盖索引覆盖索引不是一种特殊的索引类型而是一种查询状态如果某个索引包含了一条 SQL 查询所需要的全部字段那么 MySQL 只读取这个索引就能完成查询不需要再读取完整的数据行。这个索引对该查询来说就是覆盖索引。“查询需要的字段”不只是SELECT后面的字段还可能包括WHERE过滤字段JOIN ... ON关联字段ORDER BY排序字段GROUP BY分组字段HAVING条件字段最终需要返回的字段MySQL 官方将这种只读取索引树、不额外读取完整数据行的方式称为 index-only scan传统格式的EXPLAIN通常会在Extra中显示Using index。二、为什么覆盖索引能提高性要理解它需要先知道 InnoDB 的两类索引。1. 聚簇索引InnoDB 的主键索引是聚簇索引它的叶子节点保存的是完整数据行主键 id | v 完整数据行例如id1001 user_id20 status1 amount99.00 remark新用户订单通过主键查询时找到主键索引的叶子节点就得到了完整数据。2. 二级索引除聚簇索引以外的普通索引、唯一索引一般称为二级索引。假设有索引KEY idx_user_status (user_id, status)它的叶子节点大致保存user_id status 主键idInnoDB 的二级索引会自动包含主键列。因此即使创建索引时没有显式写id二级索引中仍然可以取得主键值。三、什么是“回表”创建订单表CREATE TABLE orders ( id BIGINT PRIMARY KEY, user_id BIGINT NOT NULL, status TINYINT NOT NULL, amount DECIMAL(10, 2) NOT NULL, created_at DATETIME NOT NULL, remark VARCHAR(500), KEY idx_user_status (user_id, status) ) ENGINE InnoDB;执行SELECT amount FROM orders WHERE user_id 100 AND status 1;索引idx_user_status只有user_id status id但查询还需要amount二级索引中没有这个字段所以执行过程大致是1. 查找 idx_user_status 2. 找到符合条件的记录 3. 从二级索引中取得主键 id 4. 使用 id 查询聚簇索引 5. 从完整数据行中取得 amount第 4 步就是通常所说的回表二级索引 - 主键值 - 聚簇索引 - 完整数据行如果符合条件的记录有 10 万条就可能发生大量主键索引查找。四、怎样变成覆盖索引将索引改为CREATE INDEX idx_user_status_amount ON orders(user_id, status, amount);再次执行SELECT amount FROM orders WHERE user_id 100 AND status 1;这个索引中包含user_id status amount id查询需要的三个字段过滤需要user_id过滤需要status返回需要amount全部可以从索引中得到因此不需要回表。执行过程变为1. 查找 idx_user_status_amount 2. 在索引叶子节点中取得 amount 3. 直接返回结果此时idx_user_status_amount对这条 SQL 来说就是覆盖索引。五、主键可以被自动覆盖由于 InnoDB 二级索引自动携带主键下面的查询也是覆盖索引SELECT id, amount FROM orders WHERE user_id 100 AND status 1;使用的索引仍然是(user_id, status, amount)虽然定义中没有写id但物理上二级索引包含主键因此查询不需要回表。对于联合主键InnoDB 会将主键的各个组成列加入二级索引。一般不需要这样定义(user_id, status, amount, id)因为id是主键时通常已经自动包含在二级索引中。六、同一个索引是否覆盖取决于 SQL索引KEY idx_user_status_amount (user_id, status, amount)查询一覆盖SELECT amount FROM orders WHERE user_id 100 AND status 1;需要的字段都在索引中。查询二依然覆盖SELECT id, amount FROM orders WHERE user_id 100 AND status 1;id是主键二级索引自动包含它。查询三不覆盖SELECT amount, remark FROM orders WHERE user_id 100 AND status 1;remark不在索引中需要根据主键回表读取。查询四通常不覆盖SELECT * FROM orders WHERE user_id 100 AND status 1;SELECT *需要所有字段普通二级索引通常不包含所有列因此需要回表。所以准确的说法不是idx_user_status_amount是覆盖索引。而是idx_user_status_amount覆盖了某条具体查询。七、覆盖索引与最左前缀原则是两回事假设有联合索引KEY idx_abc (a, b, c)它的排序结构可以理解为先按 a 排序 a 相同时按 b 排序 a、b 都相同时按 c 排序MySQL 可以直接利用的连续左前缀包括(a) (a, b) (a, b, c)但通常不能直接利用(b) (c) (b, c)来进行高效的 B-tree 定位。考虑SELECT b, c FROM test WHERE b 10;查询需要的b、c都在idx_abc中因此它可能是一个覆盖索引扫描。但是查询没有使用最左侧的aMySQL 可能无法通过索引快速定位只能扫描大量甚至整个索引。因此覆盖索引 ! 一定能高效查找 使用了索引 ! 一定是覆盖索引判断性能至少要看两个问题索引能否高效定位目标范围索引能否覆盖查询避免回表理想情况是两者同时满足。八、如何通过 EXPLAIN 判断执行EXPLAIN SELECT amount FROM orders WHERE user_id 100 AND status 1;重点关注key: idx_user_status_amount Extra: Using indexUsing index通常表示查询需要的信息可以直接从索引树取得无须额外读取完整数据行。不过要继续看typetyperef Using index typerange Using index一般说明既利用索引定位又避免了回表。如果是typeindex Using index可能表示扫描了整个索引。虽然没有回表但扫描量仍可能很大。MySQL 的树形执行计划也可能直接显示Covering index scan on orders using idx_user_status_amount可以使用EXPLAIN FORMATTREE SELECT amount FROM orders WHERE user_id 100 AND status 1;九、Using index和Using index condition的区别这两个非常容易混淆。Using index表示使用了覆盖索引只读取索引通常不读取完整数据行Using index condition表示使用了索引条件下推也就是 ICP先在二级索引中判断部分条件 符合条件后再读取完整数据行它可以减少回表次数但通常并没有彻底消除回表。官方文档明确区分了Using index和Using index condition。例如SELECT * FROM users WHERE city 上海 AND name LIKE 张%;存在索引(city, name)由于查询使用SELECT *其他字段不在索引中所以仍然需要完整数据行。MySQL 可以先在索引中判断city和name过滤掉不符合的记录再回表读取剩余记录。十、前缀索引通常不能完整覆盖字段例如CREATE INDEX idx_name ON users(name(10));这个索引只保存name的前 10 个字符。下面的查询通常不能仅通过该索引返回完整的nameSELECT name FROM users WHERE name 一个超过十个字符的完整姓名;因为索引中没有完整字段值MySQL可能需要读取完整数据行来确认和返回结果。MySQL 的前缀索引只保存指定长度的字符串前缀。十一、覆盖索引的优点1. 减少 BTree 查找次数不覆盖查询二级索引 查询聚簇索引覆盖只查询二级索引2. 减少随机 I/O大量回表可能访问不同的数据页。覆盖索引只扫描较紧凑的索引页通常更利于缓存和顺序读取。3. 索引通常比完整数据行小一页中能容纳更多索引记录因此读取相同数量的记录时可能需要访问更少的数据页。官方文档也指出索引树通常小于完整表数据因此覆盖索引扫描一般比全表扫描更快。(dev.mysql.com)例如SELECT id, created_at FROM orders WHERE user_id 100 ORDER BY created_at DESC LIMIT 1000;如果索引可以同时完成过滤、排序和字段覆盖就能明显减少读取完整数据行的成本。十二、覆盖索引的缺点不能为了覆盖查询就把所有字段都加入索引。例如(user_id, status, created_at, amount, remark, address, description)索引过宽会带来占用更多磁盘空间占用更多 Buffer Pool降低单个索引页能存放的记录数增加 BTree 层级的可能性降低插入和更新速度更新索引字段时需要维护索引增加优化器选择索引的成本