MySQL 8.4 Query Optimization 2.3 | Secondary Indexes and the Primary Key Bookmark

14 Video Views·Nov 11, 2026  #MySQL #MySQL84 #InnoDB

Most queries don't search by primary key. To find one customer by email, InnoDB needs a second index, and that index doesn't store the row. It stores a bookmark: the primary key. In this episode we follow one lookup through both trees, and measure it: 7 pages instead of 6,817.

Then: why InnoDB uses the primary key as the bookmark instead of a disk address, why every secondary index pays for a wide primary key, how UUID_TO_BIN shrinks a UUID key, and when a covering index skips the second search entirely.

⏱️ Chapters
0:00 From the clustered index to secondary indexes
0:23 Finding one customer by email without an index
0:55 uk_email: a separate tree, sorted by email
1:10 What a secondary index stores: the primary key bookmark
1:37 One lookup, two searches
2:08 Measured: 7 pages vs. 6,817
2:26 Why a primary key, not a disk address
3:01 Non-unique indexes too: idx_phone
3:31 The cost of a wide primary key: CHAR(36) UUIDs
4:21 UUID_TO_BIN with the swap flag
5:02 Covering index: no trip back to the table
5:37 Many matches, many trips back
6:08 Recap
6:42 Next: inside the B+Tree

📌 What you'll learn
• A secondary index is a separate B+Tree with its own pages, sorted by its own columns
• Its leaf entries hold only the indexed columns plus the primary key, the bookmark that points back to the full row
• A lookup by email is two searches: the uk_email tree finds customer_id 52817, then the clustered index finds the row
• On the test server, the whole lookup read 7 pages. A full scan of the customers table reads 6,817
• Rows move when pages split, but the primary key doesn't, so the bookmark stays valid
• The primary key is copied into every secondary index: a CHAR(36) UUID key costs about 108 MB for 1 million rows × 3 secondary indexes, just for bookmarks
• UUID_TO_BIN(UUID(), 1) stores 16 bytes instead of 36 and puts the time part first, so new keys arrive mostly in order. For random version-4 UUIDs the swap flag doesn't help the order, but you still save 20 bytes per key
• A covering index answers from the secondary index alone, e.g. SELECT customer_id … WHERE email = …
• When a query matches thousands of rows through a secondary index, each match needs its own trip back to the clustered index. That's one reason the optimizer sometimes chooses a table scan (measured in Episode 2.5)

🧪 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.3
◀ Previous: 2.2 The Primary Key IS the Table: InnoDB Clustered Index
▶ Next: 2.4 B+Tree Anatomy: Root, Internal and Leaf Pages

▶ 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.

──────────
【中文說明】
大部分的查詢都不是用主鍵來找。要用 email 找一位客戶,InnoDB 需要另一個索引,而這個索引裡放的不是整列資料,而是一個書籤:主鍵。這一集跟著一次查詢走過兩棵樹,並實際量測:只讀 7 頁,全表掃描則要 6,817 頁。

接著看 InnoDB 為什麼用主鍵當書籤、而不是用硬碟位置;主鍵太寬為什麼會讓每個次要索引都變大;UUID_TO_BIN 怎麼把 UUID 主鍵變小;以及覆蓋索引什麼時候可以完全省掉第二次搜尋。

⏱️ 章節
0:00 從聚簇索引到次要索引
0:23 沒有索引時,怎麼用 email 找一位客戶
0:55 uk_email:另一棵依 email 排序的樹
1:10 次要索引裡存什麼:主鍵書籤
1:37 一次查詢,兩次搜尋
2:08 實測:7 頁 vs. 6,817 頁
2:26 為什麼用主鍵,而不是硬碟位置
3:01 非唯一索引也一樣:idx_phone
3:31 主鍵太寬的代價:CHAR(36) 的 UUID
4:21 UUID_TO_BIN 與 swap 參數
5:02 覆蓋索引:不用回到表裡
5:37 符合的列很多,回表次數也很多
6:08 重點回顧
6:42 下一集:B+Tree 的內部結構

📌 這一集會學到
・次要索引是另一棵 B+Tree,有自己的頁,依自己的欄位排序
・它的葉頁項目只存索引欄位和主鍵;主鍵就是書籤,指回完整的資料列
・用 email 查一筆要搜尋兩次:先在 uk_email 樹找到 customer_id 52817,再到聚簇索引找到整列資料
・在測試伺服器上,整次查詢只讀了 7 頁;全表掃描 customers 要讀 6,817 頁
・頁分裂時資料列會移動,主鍵卻不會變,所以書籤一直有效
・主鍵會複製到每個次要索引裡:用 CHAR(36) 的 UUID 當主鍵,100 萬列 × 3 個次要索引,光是書籤就要約 108 MB
・UUID_TO_BIN(UUID(), 1) 只要 16 bytes,不是 36 bytes,而且把時間的部分放到最前面,新的鍵大致會依序進來;如果是隨機的第 4 版 UUID,swap 參數沒辦法讓它變得有順序,但每個鍵一樣可以省 20 bytes
・覆蓋索引只用次要索引就能回答查詢,例如 SELECT customer_id … WHERE email = …
・如果一個查詢透過次要索引找到幾千列,每一列都要各自回到聚簇索引一次。這也是優化器有時寧可全表掃描的原因之一(第 2.5 集會實測)

🧪 實驗資料庫
所有示範都使用第 1.5 集介紹的 ddt_lab。
[在這裡貼上 ddt_lab 腳本的連結]

📚 本課程:MySQL 8.4 查詢優化
Module 2・第 2.3 集
◀ 上一集:2.2 主鍵就是這張表:InnoDB 聚簇索引
▶ 下一集:2.4 B+Tree 結構:根頁、中間頁與葉頁

▶ 系列: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