The data structure used in a relational database system

Question

What data structure is used in the DBMS to store the actual data that we enter, the name of the first name, etc. I know that trees B and B + are effective for indexing, etc., but I have not received a cpnvincing answer for this, please excuse my ignorance.

Answer 1

Usually it depends on the storage mechanism, and raw data will be stored for different classifications of database data, of which there are at least three common divisions:

• Indexes (keys indicate rows containing this value)
• String data (non-blob data, some info here )
• BLOB data (usually it’s voluminous data, consider it as extended data that you usually don’t do, but retrieve it in bulk after searching for the string with other keys)

Most of the information that I will include here will be based on knowledge of MySQL.

Indices

For example, MySQL has several parameters , including:

• Innodb
• Myisam
• Memory

From what I can tell, InnoDB and MyISAM use B-tree indexes , while the memory storage mechanism allows you to specify b-tree or hash indexes.

The documents even contain a page comparing how everyone uses it .

Other RDBMSs will use their own specifications, but I would suggest that a B-tree is common.

Usually the index will look like a small table; moreover, the index value is the primary key, and the value is a list of primary keys for the rows that contain this value.

Row data

This answer is again complicated and depends on the storage mechanism. If you are interested in implementation details, I would read them in storage systems.

Strings are stored in a data format that can be quickly viewed by some primary key. The speed is ensured by the fact that strings usually have a fixed limit of relatively small (I think, 2 ¹⁶ ) bytes, after which further data will be transferred to extended data pages.

The primary key is always indexed, and other values ​​may optionally be indexed. If this is not so, then the only way to store them may be to “scan the table” - literally, sorting through all the data, comparing the column values ​​with the value you are looking for.

Blob data

Think of blob data, such as a large file system without special indexing properties other than being able to find the primary key for your row. They also lose the benefits of the fixed space allocated for each row, which is a compromise for storing large arbitrary amounts of data.