MySQL 8.4 Query Optimization 1.4 | What Changed in 8.4 for Query Optimization

Name: MySQL 8.4 Query Optimization 1.4 | What Changed in 8.4 for Query Optimization
Uploaded: 2026-11-08T01:21:06.725Z
Duration: 4 min 12 s
Channel: Doctor Dragon Tech 龍博士

Doctor Dragon Tech 龍博士

1 follower

6 Video Views·Nov 8, 2026 #MySQL #MySQL84 #QueryOptimization

Upgrading from MySQL 8.0 to 8.4 doesn't change a single line of your SQL — but it can change how that SQL runs. Here are the 8.4 changes that matter for query optimization, and why.

In this episode, we sort the changes into three groups: what the optimizer knows, what you can see in a plan, and how the InnoDB engine behaves underneath. For each one, we explain why it affects query performance and where in this course we test it hands-on.

⏱️ Chapters
0:00 Same SQL, different behavior
0:22 Three groups of changes
0:34 Histograms with AUTO UPDATE
1:24 EXPLAIN INTO & JSON format version 2
2:05 Changed InnoDB defaults
3:07 Faster or slower: capture plans before you upgrade
3:37 What did NOT change
4:02 Next: course roadmap & lab database

📌 What you'll learn
• Why an 8.0 histogram quietly goes stale, and how AUTO UPDATE in 8.4 keeps it refreshed with table statistics
• EXPLAIN INTO: save a JSON plan to a user variable so you can compare it later
• EXPLAIN FORMAT=JSON version 2, and how it now lines up with the tree format
• Four changed InnoDB defaults and what they do to your queries:
– innodb_adaptive_hash_index: now OFF
– innodb_change_buffering: now none
– innodb_io_capacity: 200 → 10,000, and innodb_flush_method = O_DIRECT on Linux
– TempTable: memory now sized from the server's RAM
• Why the same query can get faster OR slower after an upgrade, and the simple before/after check that protects you

✅ Upgrade checklist from this episode
1. Before upgrading, capture the plans and timings of your key queries
2. Upgrade
3. Run the same queries again and compare

🧱 What stays the same in 8.0 and 8.4
B+Tree row storage, secondary indexes pointing back to the primary key, and how to read an execution plan. Most of this course works the same way on both versions, and we'll point it out whenever it does.

📚 This course: MySQL 8.4 Query Optimization
Module 1 · Episode 1.4
◀ Previous: 1.3 MySQL 8.4 LTS Explained
▶ Next: 1.5 Course Roadmap & the Lab Database

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

──────────
【中文說明】
從 MySQL 8.0 升級到 8.4，你的 SQL 一行都不用改，但執行方式可能會不一樣。這一集整理 8.4 和查詢優化有關的改變，並說明為什麼會有影響。

我們把這些改變分成三類：優化器知道什麼、在執行計畫裡看得到什麼，以及底層 InnoDB 引擎怎麼運作。每一項都會說明它為什麼會影響查詢效能，以及在課程的哪個部分實際測試。

📌 這一集會學到
・為什麼 8.0 的 histogram 會在不知不覺中過時，以及 8.4 的 AUTO UPDATE 怎麼讓它跟著統計資料一起更新
・EXPLAIN INTO：把 JSON 格式的執行計畫存進使用者變數，方便之後比對
・EXPLAIN FORMAT=JSON 第 2 版，以及它現在怎麼和 tree 格式對應
・4 個改了預設值的 InnoDB 參數，以及對查詢的影響：
– innodb_adaptive_hash_index：改成 OFF
– innodb_change_buffering：改成 none
– innodb_io_capacity：200 → 10,000；Linux 上 innodb_flush_method 改成 O_DIRECT
– TempTable：記憶體上限改成依伺服器的 RAM 決定
・為什麼同一個查詢升級後可能變快、也可能變慢，以及保護自己的升級前後比對方法

✅ 這一集的升級檢查清單
1. 升級前，先記下關鍵查詢的執行計畫和執行時間
2. 升級
3. 再跑一次同樣的查詢，前後比對

🧱 8.0 和 8.4 沒有改變的部分
B+Tree 的資料列儲存方式、二級索引指回主鍵，以及看執行計畫的方法。這門課大部分的內容在兩個版本都一樣，遇到相同的地方我們都會特別標出來。

📚 本課程：MySQL 8.4 查詢優化
Module 1・第 1.4 集
◀ 上一集：1.3 MySQL 8.4 LTS 完整解說
▶ 下一集：1.5 課程路線圖與實驗資料庫

▶ 系列：MySQL 8.4 索引與查詢優化，新的單元會陸續加進播放清單。
👍 覺得有幫助的話，歡迎按讚、訂閱，也可以在留言告訴我你想看哪個主題。

──────────
© 2026 Doctor Dragon Tech (龍博士). All rights reserved.
All animations, diagrams, narration and the channel logo in this video are original works. Please do not re-upload or reuse them without permission. Sharing the link is always welcome.

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 #QueryOptimization

Upgrading from 8.0 to 8.4 does not change a single line of your SQL, but it can change how that SQL runs. In this episode, we look at the changes that matter for query optimization, why each one matters, and where in the course we will test it. Think of them in three groups. What the optimizer knows, what you can see in a plan, and how the engine underneath behaves. what the optimizer knows a histogram tells the optimizer how values are spread across a column so it can estimate how many rows a condition will match in 8.0 a histogram is a snapshot as the data changes it quietly goes stale and the optimizer keeps planning with old numbers in 8.4 you can create a histogram with the auto update option then It is refreshed whenever InnoDB recalculates the table's statistics. This is new in 8.4. We test it in Module 10, where we watch a plan change as the data moves. Second, what you can see Explain Into saves a JSON plan into a user variable, so you can store it and compare it later. And version 2 of the JSON format follows the same structure as the tree output, so the two finally line up. Both came in the innovation releases, so they are new, compared with 8.0. Explain Analyze and the tree format were already in 8.0, and they stay at the heart of this course. We test all of this, in modules, 4 and 5. Third, how the engine behaves. 8.4, changed several InnoDB defaults, and 4 of them matter for us. The adaptive hash index is now off. So some repeated lookups lose a shortcut they used to have. Change buffering is now none. So changes to secondary indexes are no longer buffered. InnoDB updates the index page right away. That makes index write cost more visible. And we first meet it in episode 2.7. The I/O capacity went from 200 to 10,000. And the flush method is now O_DIRECT on Linux. And the TempTable engine now sizes its memory from the server's RAM. We test the first three in Module 11 and TempTable in Module 8. Here is the important part. The same query on the same data can behave differently after the upgrade, without any change in your code. Sometimes it is faster and sometimes it is slower. So before you upgrade. Capture the plans and timings of your key queries. After the upgrade, run them again and compare. And what did not change? The foundations. How InnoDB stores rows in B+ trees. How a secondary index points back to the primary key. And how to read an execution plan. Most of this course works the same way on 8.0 and 8.4. And we will say so whenever it does. In the next episode. We walk through the course roadmap and meet the lab database we will use in every demo.