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

问题描述

当我在mysql中为表创建索引时，我看到index_type是 BTREE 类型。现在虽然我理解btree，我不安静了解如何存储索引和数据库如何基于此搜索记录。

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非常适合数据库执行读取和写入大块数据
当我们为主键创建列类型的索引时，我理解是，它创建树并根据根的值类型拆分根的值。

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-树 ，并将记录指针作为 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.

• code> MyISAM ，记录指针是 MYI 文件中的记录的偏移量。

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

在 InnoDB 中，记录指针是 PRIMARY KEY 的值。 / p>

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

在 InnoDB 中，表本身是 B-Tree 键作为 PRIMARY KEY 这就是所谓的聚簇索引或索引组织表。在这种情况下，所有其他字段存储为 B树值。

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屋！