What is the relation between Tablespaces, .frm & .ibd files and database pages in MySQL? I looked in the documentation but there was not a clear answer regarding their relation, can someone give an real life example of their relation in MySQL?

Database pages are smallest read write unit in databases and is usually 8KB, but:


What is the relation between MySQL pages and .frm &amp; .ibd files? 
Is .frm files consists of many pages coming together? 
If so then does the .frm files have any type of info on the pages that are inside them? 
Like the location of each page and stuff like that? 
Where can i read more about this?
And what about tablespaces? 
Is every .frm file a tablespace? 


I read their documentation for hours but it got me even more confused

(I'm talking about the InnoDB storage engine BTW)
What is the relation between MySQL pages and .frm &amp; .ibd files? Is .frm files consists of many pages coming together?
  


.frm used to contain metadata about tables- that means, the structure (think of the output of SHOW CREATE TABLE- columns and data types) in its own format. Now a days (Since MySQL 8.0) that data is stored on InnoDB tables on the mysql database to allow transactional schema changes. .ibd contained mostly data (not that innodb also had its own internal data dictionary, supporting MySQL one, but different), organized in namespaces>extents>pages, and inside pages, records "rows".

There is other stuff (structures) related to InnoDB for it to work, but the main take away is that there are 2 different planes, the SQL layer, which takes care of connection handling, query parsing and optimization for execution, and the Engine layer (InnoDB, MyISAM, MyRocks, ...) which decides how data is actually stored. Both layers communicate with common API called Handler API, which contains very basic row-level actions like "Search the first record in Primary Key order" or "get the following record".

.frms were an SQL-level functionality (so found for every engine), .ibds were an InnoDB-only structure. Because InnoDB also had its own internal dictionary, aside from the MySQL one, if you deleted a .frm improperly (from the filesystem), InnoDB got confused as the table was still there from its point of view.


  If so then does the .frm files have any type of info on the pages that are inside them? Like the location of each page and stuff like that?


No, the MySQL data dictionary, be it on .frm or in its new form only contains basic SQL-level information (what is the table definition, which columns are there, which types, etc.). InnoDB information is stored on InnoDB related files- ibdata1, .ibds, transactiona logs. When InnoDB engine receives, through the Handler API the need to read a particular row, it does that independently of the SQL later- data may be on cache (buffer pool, or it may be on disk only, or it may be on disk but not available because it is hidden for the given session). To find a particular page, it implements a series of memory and disk structures (headers on files, tree-like structures, lookup tables, hashes that will let InnoDB access the right page). This is very little space to give an proper overview of InnoDB internals, but think of it like a very complex set of pointers and references that allow accessing quickly data while maintaining consistency to disk and access control.


  Where can i read more about this?


I already shared with you the InnoDB: A journey to the core which describes more in detail how it works. Aside from that, probably the best way is the source code itself- MySQL source code is available and you can check the details of how InnoDB implements its own data structures. This is not simple- and almost impossible to summarize here. For a very simplistic approach, each table creates a B+tree structure organized in 16K pages ordered by the primary key (there is always a primary key, even if one has not been explicitly created). Other indexes are implemented as separate tree structures that record the value of the PK of the row they index. If you just want a simplified approach about the logical view of this, without the technical details, you can read my slides on Query Optimization for MySQL/InnoDB.


  And what about tablespaces? Is every .frm file a tablespace?


No, as we said, .frm is a MySQL table structure. However, InnoDB usually creates a different tablespace per table (there are exceptions, like partitioned tables), and each tablespace normally is on a separate file (.ibd). However, this is not necessarily true, there could be several tables on the tablespace0 (it's complicated :-)).