MySQL 中的覆盖索引

索引覆盖

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+树在叶子节点保存节点索引和节点数据，这里的节点数据保存的只是创建索引时指定的字段，其他字段在数据文件中。