Hey. Question by mysql . How does the mysql server store tables on the hard disk? I heard that it stores line by line, but it can also be stored in columns. How do you get something from the table? Also line by line or something different? I want to understand more deeply how it all works so that it becomes clear how the optimization goes.

  • To store on the pillars would be wildly inefficient. Each column is for example in its own disk block. Give the simplest query select * from table where id=1 . And here we would have to read as many blocks from the disk as we have columns ... Therefore, the record always lies entirely. How exactly - depends on the engine. And to understand the optimization, I would recommend first of all to study the principles of indexation. - Mike
  • if I only need, for example, a column, it is not efficiently stored in rows. because in order to get this column, you have to pull it out of each row. that is, you have to load the ALL line and pull the piece. extra memory consumed - Dimon

1 answer 1

Regardless of the storage engine , a frm structure is created, containing a description of the table structure. The structure description is in the internals documentation.

Then each storage engine can use its own approach to data storage. Mysql itself does not regulate it.

For example, memory or blackhole do not store anything on the disk at all. csv writes simple plain text data and recodes the string format from / to the internal mysql.

What mysql gives at the mercy of a specific storage engine can be found in the internals documentation in the Writing a Custom Storage Engine section. Here is described in more detail the work with plugin storages. That is, Mysql itself abstracts from everything related to the format of storing data on a disk and how to read this data. But by itself, the storage engine api involves working specifically with strings in the mysql internal binary representation. By the way, in the same place as examples cite implementations of other storage engine , for example, insert supplemented with code from myisam , update and delete - from csv

For myisam and innodb there are separate internals in the internals . Having the source of these storage engine in front of you at the time of reading seems to be necessarily described in some very superficial way. Start better with writing your storage engine .

PS: I cannot suggest anything further, I did not climb. But let it be even the direction of further research.