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

19 Video Views·Nov 9, 2026  #MySQL #MySQL84 #InnoDB

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