MySQL 中的覆盖索引
MySQL 索引 面试 About 1,684 words索引覆盖
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+
树在叶子节点保存节点索引和节点数据,这里的节点数据保存的只是创建索引时指定的字段,其他字段在数据文件中。
Views: 2,328 · Posted: 2021-03-09
————        END        ————
Give me a Star, Thanks:)
https://github.com/fendoudebb/LiteNote扫描下方二维码关注公众号和小程序↓↓↓
Loading...