MySQL 8.4 Query Optimization 2.2 | The Primary Key IS the Table: InnoDB Clustered Index

In InnoDB, rows are not stored beside an index. They live inside the primary key index itself, the clustered index. In this episode we see what that means for the orders table: leaf pages that hold complete rows, sorted and chained in primary key order.
Then: what your primary key choice controls, increasing keys vs. random UUIDs, and what InnoDB does when a table has no primary key, including MySQL 8.4's generated invisible primary key.
⏱️ Chapters
0:00 The primary key is the table
0:26 The clustered index: leaf pages hold the full rows
1:28 Benefit 1: a lookup by primary key is direct
1:54 Benefit 2: a range on the primary key is cheap
2:18 Benefit 3: the key becomes the physical order (order_items)
2:49 Inserts with an increasing key
3:09 Random UUIDs and page splits
3:43 No primary key? How InnoDB picks the clustered index
4:30 The catch with a hidden row ID
4:47 MySQL 8.4: sql_generate_invisible_primary_key
5:31 Recap
6:02 Next: secondary indexes and the primary key bookmark
📌 What you'll learn
• An InnoDB table is its clustered index: the leaf pages hold every column of every row, sorted by primary key and linked to the previous and next page
• A lookup by primary key takes only a handful of page reads, even with 10 million orders
• A range like order_id BETWEEN 1000 AND 2000 reads forward through neighboring pages: 1,000 orders ≈ 5–6 pages
• With PRIMARY KEY (order_id, line_no), all lines of one order sit together, usually on a single page
• An increasing key appends at the end and keeps pages nearly full. A random UUID lands in the middle, splits full pages, costs extra writes and leaves pages half empty
• Without a primary key, InnoDB uses the first UNIQUE index whose columns are all NOT NULL, or else a hidden 6-byte row ID (index name: GEN_CLUST_INDEX)
• A hidden row ID can't be seen or used by your queries, and with row-based replication a replica may scan the whole table for every changed row
• MySQL 8.4: with sql_generate_invisible_primary_key = ON, a table created without a primary key gets an invisible my_row_id BIGINT UNSIGNED AUTO_INCREMENT primary key. The setting is OFF by default
🧪 Lab database
All demos use ddt_lab, introduced in Episode 1.5. Every table in ddt_lab has a primary key.
[Paste the link to the ddt_lab scripts here]
📚 This course: MySQL 8.4 Query Optimization
Module 2 · Episode 2.2
◀ Previous: 2.1 How InnoDB Stores a Table: Tablespace, Page, Row
▶ Next: 2.3 Secondary Indexes and the Primary Key Bookmark
▶ 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 裡,資料列不是放在索引旁邊,而是直接存在主鍵索引裡,這個索引叫做聚簇索引(clustered index)。這一集用 orders 表來看這代表什麼:葉頁存放完整的資料列,依主鍵排序,頁和頁之間前後串成一條鏈。
接著看主鍵的選擇決定了哪些事、遞增鍵和隨機 UUID 的差別,以及沒有主鍵時 InnoDB 會怎麼做,包括 MySQL 8.4 自動產生的隱藏主鍵。
⏱️ 章節
0:00 主鍵就是這張表
0:26 聚簇索引:葉頁存放完整的資料列
1:28 好處一:用主鍵查一筆,直接找到
1:54 好處二:主鍵範圍查詢很便宜
2:18 好處三:主鍵決定實際的排列順序(order_items)
2:49 遞增鍵的寫入
3:09 隨機 UUID 與頁分裂
3:43 沒有主鍵時,InnoDB 怎麼挑聚簇索引
4:30 隱藏 row ID 的問題
4:47 MySQL 8.4:sql_generate_invisible_primary_key
5:31 重點回顧
6:02 下一集:次要索引與主鍵書籤
📌 這一集會學到
・InnoDB 的表就是它的聚簇索引:葉頁存放每一列的所有欄位,依主鍵排序,並和前後的頁串在一起
・用主鍵查一筆只要讀幾頁,就算有一千萬筆訂單也一樣
・像 order_id BETWEEN 1000 AND 2000 這樣的範圍,只要沿著相鄰的頁往後讀:1,000 筆訂單 ≈ 5~6 頁
・PRIMARY KEY (order_id, line_no) 讓同一張訂單的所有明細放在一起,通常在同一頁
・遞增的鍵都加在最後面,頁幾乎都是滿的;隨機的 UUID 會插進中間,讓已經滿的頁分裂,多出額外的寫入,頁也會變得半空
・沒有主鍵時,InnoDB 會用第一個欄位全是 NOT NULL 的唯一索引;再沒有,就用隱藏的 6 bytes row ID(索引名稱:GEN_CLUST_INDEX)
・隱藏的 row ID 看不到,查詢也用不到;在 row-based 複寫下,replica 甚至可能每改一列就要掃描整張表
・MySQL 8.4:打開 sql_generate_invisible_primary_key 之後,沒有主鍵的新表會自動加上看不見的 my_row_id 主鍵(BIGINT UNSIGNED AUTO_INCREMENT)。這個設定預設是關閉的
🧪 實驗資料庫
所有示範都使用第 1.5 集介紹的 ddt_lab,裡面每張表都有主鍵。
[在這裡貼上 ddt_lab 腳本的連結]
📚 本課程:MySQL 8.4 查詢優化
Module 2・第 2.2 集
◀ 上一集:2.1 InnoDB 怎麼儲存一張表:表空間、頁、資料列
▶ 下一集:2.3 次要索引與主鍵書籤
▶ 系列: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
