MySQL中的覆盖索引

MySQL 索引 面试 大约 1684 字

索引覆盖

Extra字段显示Using index,索引处取得的数据即是要求的数据,则不会再回数据文件再查询,直接返回了。

explain select id from account where id = 1;

输出:

mysql> explain select id from account where id = 1;
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table   | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra       |
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | account | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | Using index |
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

回表查询

取得索引上保存的数据,再回数据文件中查询所要求的的全部的属性值。

explain select * from account where id = 1;

输出:

mysql> explain select * from account where id = 1;
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table   | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | account | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

原理

B+树在叶子节点保存节点索引和节点数据,这里的节点数据保存的只是创建索引时指定的字段,其他字段在数据文件中。

阅读 66 · 发布于 2021-03-09

————        END        ————

扫描下方二维码关注公众号和小程序↓↓↓

扫描二维码关注我
昵称:
随便看看 换一批