.frm file contains nothing more than the schema. It is looked at when opening the file, then info is kept in RAM.
ibdata1 or the
.ibd file is the "tablespace" for the data. It contains meta info about the data, indexes, etc. In particular, it points to the root nodes for the indexes of the table. (Note: The data is stored based on the
PRIMARY KEY, so the "data" acts very much like an "index".)
From the "root" node of a B+Tree, you can drill down to an individual row.
From an individual row, it is possible (when doing a range/index/table scan) to easily find the next/previous row in the B+Tree.
The BTrees are organized in 16KB "blocks", pointed to by a tuple that contains (approximately): tablespace number and block number. Since a tablespace is a file, the block number is easily translated into a byte offset in that file. The OS has an extra layer of looking up such a logical address to find a physical address.
Within the Block, there may be, say, 100 "records" that are either data rows or index rows or links down the BTree to other blocks ("nodes"). (The "100" is a Rule of Thumb; in reality, it could be as few as 1, to as many as several thousand.)
To find a particular row (other than the "next" or "previous") within a block, some form of search is done. (I don't know whether this is a linear scan or a binary search.)
SELECT name from mytable where id>1
- Parse the command.
- "Open" the table -- either cached (cf
table_open_cache) or use
.frm (or Data Dictionary in MySQL 8.0)
- Figure out how to perform the query
- Find index on
id. Let's say it is
- Locate root of B+Tree for the PK
- Drill down until locating the last (or, since a PK is unique, the only) row with
id=1, then start at the 'next' record.
- Scan forward, fetching
name from each row until you hit the end of the table.