B 树索引如何在 mysql 中工作 [英] how B-tree indexing works in mysql

问题描述

当我为 mysql 中的表创建索引时，我看到 index_type 是 BTREE 类型.现在虽然我了解 btree(s)，但我不太明白它如何存储索引以及数据库如何基于此搜索记录.

When I create an index for a table in mysql, I see that the index_type is type BTREE. Now although I understand about btree(s), I do not quiet understand how it stores the index and how the database searches the records based on this.

我的意思是，btree 非常适合数据库执行读取和写入大数据块，当我们为 Primary key 的列类型创建索引时，我的理解是，它创建了一棵树并根据根的值类型拆分根的值.

I mean, btree is excellent for databases to perform read and writes large blocks of data, when we create an index for column type of Primary key, what I understand is, it creates a tree and splitting the values for the root based on the value type of the root.

现在，它是只存储树下的主键 ID 还是与该主键关联的整个数据?

Now, does it store only the the primary key ID under the trees or the whole data associated with that primary key?

找到想要的主ID后，数据库如何提取记录?

After finding the wanted primary ID, how does the database extract the record?

推荐答案

数据库将索引值存储为 B-Tree 键，记录指针存储为 B-Tree 值.

The database stores the value indexed as a B-Tree key, and the record pointer as a B-Tree value.

每当您搜索包含某个索引列的某个值的记录时，引擎都会在 B-Tree 中定位包含该值的键，检索指向该记录的指针并获取该记录.

Whenever you search for a record holding a certain value of an indexed column, the engine locates the key holding this value in the B-Tree, retrieves the pointer to the record and fetches the record.

究竟什么是记录指针"，取决于存储引擎.

What exactly is a "record pointer", depends on the storage engine.

• 在MyISAM中，记录指针是MYI文件中记录的偏移量.

• In MyISAM, the record pointer is an offset to the record in the MYI file.

在InnoDB中，记录指针是PRIMARY KEY的值.

In InnoDB, the record pointer is the value of the PRIMARY KEY.

在InnoDB中，表本身是一个B-Tree，带有一个PRIMARY KEY作为B-Tree> 键.这就是所谓的聚集索引"或索引组织表".在这种情况下，所有其他字段都存储为 B-Tree 值.

In InnoDB, the table itself is a B-Tree with a PRIMARY KEY as a B-Tree key. This is what called a "clustered index" or "index-organized table". In this case, all other fields are stored as a B-Tree value.

在MyISAM中，记录的存储没有任何特殊的顺序.这称为堆存储".

In MyISAM, the records are stored without any special order. This is called "heap storage".

这篇关于B 树索引如何在 mysql 中工作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！