MySQL 8.4 Query Optimization 2.4 | B+Tree Basics: Full Table Scan vs. Index Lookup

13 Video Views·Nov 11, 2026

very InnoDB table is a B+Tree, and so is every secondary index. In this episode we first look at the shape of that tree: leaf pages that hold the rows, and small internal pages that only point the way down. Then we put it to the test on a 51-million-row table: the same query takes 6.61 seconds without an index on time, and 0.00 seconds with one.

We read the evidence in EXPLAIN (ALL vs. range) and EXPLAIN ANALYZE (51.9 million rows read vs. 5), and finish with what to watch out for when you measure.

⏱️ Chapters
0:00 Every InnoDB table is a B+Tree
0:07 Leaf pages, internal pages and the root
0:42 A secondary index is a second tree
1:02 Live demo: two tables, log and logIX
1:52 The query: WHERE time is later than '2026-08-01' LIMIT 5
2:31 EXPLAIN on log: type ALL, a full table scan
3:06 The same query on logIX: type range
4:00 EXPLAIN ANALYZE: 51.9 million rows vs. 5
4:34 Measuring fairly: buffer pool, repeat runs, estimates
5:11 The lesson: index design starts with the query

📌 What you'll learn
• InnoDB stores everything in 16 KB pages. The rows live in the leaf pages, sorted by primary key and linked to their neighbours
• Internal pages hold no rows, only a key and a page number per entry, so one page can point to hundreds of pages below it
• That keeps the tree shallow: even in a 51-million-row table, any row is only 3–4 page reads from the root
• A secondary index such as IX_time is another B+Tree of the same shape, sorted by time instead of by id
• Without an index on time, MySQL took 6.61 s for SELECT … WHERE time is later than '2026-08-01' LIMIT 5. LIMIT 5 does not make a query cheap if MySQL has to scan to find the rows
• EXPLAIN on log shows type ALL, key NULL and an estimate of about 51 million rows. On logIX it shows type range using IX_time
• EXPLAIN ANALYZE: the table scan really read about 51.9 million rows (7.59 s); the index range scan read only the 5 rows it needed, in about a quarter of a millisecond
• EXPLAIN ANALYZE actually executes the query, so be careful running it on a table this large
• A single run is not a benchmark: the first scan warms the buffer pool, so repeat the tests and record cache state, concurrency and server load
• EXPLAIN shows estimated rows only; actual rows and time come from EXPLAIN ANALYZE

🧪 Lab database
The demo uses two tables with the same structure and data: log (no index on time) and logIX (index IX_time on time).
[Paste the link to the lab scripts here]

📚 This course: MySQL 8.4 Query Optimization
Module 2 · Episode 2.4
◀ Previous: 2.3 Secondary Indexes and the Primary Key Bookmark
▶ Next: 3.1 When an Index Makes a Query Slower

▶ 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 表都是一棵 B+Tree,每一個次要索引也是。這一集先看這棵樹的樣子:葉頁放資料列,上面幾層的中間頁很小,只負責指路。接著在一張 5,100 萬列的表上實際測試:同一個查詢,time 欄位沒有索引時要 6.61 秒,有索引時只要 0.00 秒。

我們會從 EXPLAIN(ALL 對 range)和 EXPLAIN ANALYZE(讀了 5,190 萬列對 5 列)找出證據,最後說明量測時要注意哪些事。

⏱️ 章節
0:00 每張 InnoDB 表都是一棵 B+Tree
0:07 葉頁、中間頁與根頁
0:42 次要索引是另一棵樹
1:02 實際示範:log 和 logIX 兩張表
1:52 查詢:time 晚於 '2026-08-01' LIMIT 5
2:31 log 的 EXPLAIN:type ALL,全表掃描
3:06 同一個查詢換成 logIX:type range
4:00 EXPLAIN ANALYZE:5,190 萬列 vs. 5 列
4:34 公平量測:buffer pool、重複測試、估計值
5:11 重點:索引設計從查詢模式開始

📌 這一集會學到
・InnoDB 把所有東西存在 16 KB 的頁裡;資料列放在葉頁,照主鍵排序,相鄰的葉頁前後串在一起
・中間頁不放資料列,每一筆只記一個鍵和一個頁號,所以一頁可以指向下一層的幾百頁
・所以樹很矮:就算是 5,100 萬列的表,任何一列從根頁往下只要讀 3 到 4 頁
・次要索引(例如 IX_time)是另一棵形狀一樣的 B+Tree,只是照 time 排序,不是照 id
・time 沒有索引時,查詢 time 晚於 2026-08-01、LIMIT 5 花了 6.61 秒。如果 MySQL 得掃描才能找到符合的列,LIMIT 5 也不會讓查詢變便宜
・log 的 EXPLAIN 顯示 type ALL、key 是 NULL,估計要看約 5,100 萬列;logIX 則顯示 type range,使用 IX_time
・EXPLAIN ANALYZE:全表掃描實際讀了約 5,190 萬列(7.59 秒);索引範圍掃描只讀了需要的 5 列,大約 0.25 毫秒
・EXPLAIN ANALYZE 會真的執行查詢,在這麼大的表上要小心使用
・只跑一次不算基準測試:第一次全表掃描會把頁載入 buffer pool,所以要重複測試,並記錄快取狀態、同時執行的連線數和伺服器負載
・EXPLAIN 只給估計的列數;實際列數和時間要看 EXPLAIN ANALYZE

🧪 實驗資料
示範使用兩張結構和資料都相同的表:log(time 沒有索引)和 logIX(time 上有索引 IX_time)。
[在這裡貼上實驗腳本的連結]

📚 本課程:MySQL 8.4 查詢優化
Module 2・第 2.4 集
◀ 上一集:2.3 次要索引與主鍵書籤
▶ 下一集:3.1 索引反而使查詢變慢

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

本影片內容僅供學習參