MySQL 8.4 Query Optimization 2.1 | How InnoDB Stores a Table: Tablespace, Page, Row

When you insert a row into an InnoDB table, where does it actually go? We follow one table, orders, from the .ibd file on disk down to a single row: tablespace → page → row.
Then we estimate how many orders fit in one 16 KB page, and check the estimate against the real 704 MB orders.ibd file on our MySQL 8.4 server.
⏱️ Chapters
0:00 Where does a row actually go?
0:19 Level 1: the tablespace (one table, one .ibd file)
0:53 Level 2: the 16 KB page
1:39 Level 3: inside a page
2:09 Hidden fields: transaction ID and roll pointer
2:36 How many rows fit in a page? Estimate vs. the real file
3:24 Large values and overflow pages
3:53 Why query cost is counted in pages
4:27 Recap
4:41 Next: the primary key is the table
📌 What you'll learn
• innodb_file_per_table is ON by default in MySQL 8.4, so each table gets its own .ibd file
• The 16 KB page is the unit InnoDB reads, caches in the buffer pool, and writes back. Even one small row means loading a whole page
• Inside a page: header, rows in primary key order, free space, page directory
• Every row carries hidden fields: a record header, a 6-byte transaction ID (DB_TRX_ID) and a 7-byte roll pointer (DB_ROLL_PTR) into the undo log
• Back-of-the-envelope math: 704 MB ÷ 16 KB ≈ 45,000 pages, a little over 200 orders per page
• In the DYNAMIC row format, long values such as JSON move to overflow pages, leaving a 20-byte pointer in the row
• Almost every optimization technique in this course is a way to touch fewer pages
🧪 Lab database
All demos use ddt_lab, introduced in Episode 1.5.
[Paste the link to the ddt_lab scripts here]
📚 This course: MySQL 8.4 Query Optimization
Module 2 · Episode 2.1
◀ Previous: 1.5 Course Roadmap & the Lab Database
▶ Next: 2.2 The Primary Key IS the Table
▶ Series: MySQL 8.4 Indexing & Query Optimization — new parts are added to the playlist as they are released.
👍 If this helped, like, subscribe, and tell me in the comments which topic you want next.
──────────
【中文說明】
在 InnoDB 表裡新增一筆資料,它實際上存到哪裡?這一集跟著 orders 這張表,從硬碟上的 .ibd 檔一路往下看到單一資料列:表空間 → 頁 → 資料列。
接著我們估算一個 16 KB 的頁能放多少筆訂單,再拿 MySQL 8.4 伺服器上真實的 orders.ibd(704 MB)來驗證。
⏱️ 章節
0:00 一筆資料到底存在哪裡?
0:19 第一層:表空間(一張表、一個 .ibd 檔)
0:53 第二層:16 KB 的頁
1:39 第三層:頁的內部結構
2:09 隱藏欄位:交易 ID 與 roll pointer
2:36 一頁能放幾列?估算與實際檔案對照
3:24 大型欄位與溢位頁
3:53 為什麼查詢成本要用「頁」來算
4:27 重點回顧
4:41 下一集:主鍵就是這張表
📌 這一集會學到
・MySQL 8.4 預設開啟 innodb_file_per_table,每張表都有自己的 .ibd 檔
・16 KB 的頁是 InnoDB 讀取、放進 buffer pool、寫回硬碟的單位;就算只要一小列,也得載入整頁
・頁的內部:頁標頭、依主鍵排序的資料列、空閒空間、page directory
・每一列都帶有隱藏欄位:record header、6 bytes 的交易 ID(DB_TRX_ID),以及指向 undo log 的 7 bytes roll pointer(DB_ROLL_PTR)
・簡單估算:704 MB ÷ 16 KB ≈ 45,000 頁,每頁大約 200 多筆訂單
・在 DYNAMIC 列格式下,JSON 這類很長的值會移到溢位頁,原本的列裡只留 20 bytes 的指標
・這門課幾乎所有的優化技巧,目的都是讓查詢碰到更少的頁
🧪 實驗資料庫
所有示範都使用第 1.5 集介紹的 ddt_lab。
[在這裡貼上 ddt_lab 腳本的連結]
📚 本課程:MySQL 8.4 查詢優化
Module 2・第 2.1 集
◀ 上一集:1.5 課程路線圖與實驗資料庫
▶ 下一集:2.2 主鍵就是這張表
▶ 系列:MySQL 8.4 索引與查詢優化,新的單元會陸續加進播放清單。
👍 覺得有幫助的話,歡迎按讚、訂閱,也可以在留言告訴我你想看哪個主題。
──────────
MySQL is a registered trademark of Oracle Corporation and/or its affiliates. This channel is not affiliated with or endorsed by Oracle.
Fonts:
• MOE Lishu Font (教育部隸書), Ministry of Education, R.O.C. (Taiwan), licensed under CC BY-ND 3.0 TW
• Inter (SIL Open Font License), DejaVu Sans Mono (DejaVu Fonts License)
This video is for educational purposes only. Please refer to the official MySQL documentation for production use.
© 2026 龍博士 Doctor Dragon Tech 版權所有
本影片的動畫、圖表、旁白與頻道標誌皆為原創製作。未經許可,請勿轉載、重製或用於商業用途;歡迎分享影片連結。
MySQL 是 Oracle Corporation 及/或其關係企業的註冊商標。本頻道與 Oracle 沒有任何關係,本影片也不是 Oracle 官方內容。
使用字體:
・教育部隸書,中華民國教育部,依「創用 CC 姓名標示-禁止改作 3.0 臺灣」授權使用
・Inter(SIL Open Font License)、DejaVu Sans Mono(DejaVu Fonts License)
本影片內容僅供學習參考,實際操作請以 MySQL 官方文件為準。
#MySQL #MySQL84 #InnoDB
Video Summary
AI GeneratedThis video explains the physical storage architecture of InnoDB tables in MySQL, detailing how data moves from the disk file down to individual rows. It emphasizes that the \"page\" is the fundamental unit of I/O, and the primary goal of database optimization is to minimize the number of pages read from disk to improve query performance.
