New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

【Q381】什么是覆盖索引 #386

Open

shfshanyue opened this issue Aug 4, 2020 · 1 comment

Labels

db

Owner

shfshanyue commented Aug 4, 2020

No description provided.

shfshanyue added the db label

Owner Author

shfshanyue commented Aug 4, 2020

假设一个用户表 User(id primary, name unique, age)，此时 name 为唯一索引。

聚合索引: select * from User where id in (1, 2, 3, 4, 5)，由于 id 都在 B+ tree 的叶子节点，不需要去磁盘进一步 IO，称作聚合索引

假设有一个需求: 根据 name 查询该人对应的 age，在现有情况下是这样的：

根据 name 上的 B+ tree 快速找到该行对应的 id
根据 id 上的 B+ tree 快速找到该行对应的 age，此时需要通过主键 id 进一步 IO，称作回表

如果建立一个联合索引 (name, age)，直接在 name 上的 B+ tree 就可以找到它对应的 age，省了回表操作，称为覆盖索引

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment