前言

• 關於這堂分享
• 為什麼需要了解資料庫內部結構
• 為什麼B-Tree和B+Tree被廣泛用來實現索引

關於這堂分享

• 千萬不要以為聽了這堂分享就懂所有觀念
  1. 時間有限，很多東西點到為止
  2. 處在一知半解的情況下是很危險的
• 希望大家能無私的分享自己的知識
  1. 分享是雙向，別人會提出不同看法
  2. 講者的知識大多數也是許多人分享的
  3. 當你停止分享，某部分來說你也停止成長
  4. 做簡報很累，但是每次都有不同的收穫

Ｑ：為什麼需要了解資料庫內部結構？

為什麼需要了解資料庫內部結構

• 避免設計出不正確的table schema
  • 直接影響應用層的開發
  • 引起的效能問題通常是沒救的
• 面對C10K環境，資料庫的內部結構會大幅影響效能
  • Database Server 無法輕易加機器擴容
  • 愈高流量愈體現底層資料庫結構設計的好壞
  • 內部結構設計的好壞也決定未來你Debug的時間
• 多數公司並沒有專職DBA
  • ​即便有大多數都離業務較遠
  • 很多時候系統問題不是光靠DBA就能排除
    (你一定聽過DBA吶喊又Dead Lock...)
    (但是他一定不會幫你改code)

Ｑ：為什麼B-Tree和B+Tree被廣泛用來實現索引？

為什麼B-Tree和B+Tree被廣泛用來實現索引

1. 查詢效率高、保證有序
2. 為什麼不用BST，要用B-Tree
   • 面對大數據量時無法將全部索引加載到內存
   • 內存比較換取硬碟IO
   • B-Tree大幅降低樹高，進而減少硬碟IO

無法將全部索引加載到內存

• 假設索引:數據的佔用比約1:15，以一台記憶體16G且硬碟1TB的機器來看，至少需要67G
• 因此我們不可能將所有索引加載至內存，能做的只是逐一加載每一個數據頁(節點)
• 因此減少硬碟 IO 次數很重要

內存比較換取硬碟IO

以讀取 1MB 的連續數據：

• 內存耗時大約為 250us，換算成人類時間是 7.5天，相當於藍色Kevin每次出國最小時數
• 磁盤需要 20ms，換算成人類時間是 20個月，時間足夠你生一個孩子

B-Tree,B+Tree大幅降低樹高

假設我們今天要找 5

BST

B-Tree

(為方便講解，以B-Tree說明)

B-Tree,B+Tree大幅降低樹高

假設我們今天要找 5

BST

B-Tree

(為方便講解，以B-Tree說明)

1次IO

1次IO

B-Tree,B+Tree大幅降低樹高

假設我們今天要找 5

BST

B-Tree

(為方便講解，以B-Tree說明)

1次IO

2次IO

B-Tree,B+Tree大幅降低樹高

假設我們今天要找 5

BST

B-Tree

(為方便講解，以B-Tree說明)

2次IO

3次IO

B-Tree,B+Tree大幅降低樹高

假設我們今天要找 5

BST

B-Tree

(為方便講解，以B-Tree說明)

3次IO

4次IO

B+Tree 原理

• Binary Search & BST
• B-Tree簡介
• B+Tree比起B-Tree多了什麼
• IOT vs Heap Table

Binary Search & BST

Binary Search

Binary Search

n = 1 + 2 + 4 + ... + 2^(h-1) + 2^h = 2^(h+1) - 1
h = O(log(n))

BST

假設我們要查找7這個節點....

B-Tree 簡介

B-Tree

超級比一比：你能發現幾個不同？

B-Tree

假設我們今天要找 5

B-Tree：查找

假設我們今天要找 5

1次IO+1次內存定位

B-Tree：查找

假設我們今天要找 5

2次IO+1次內存定位

B-Tree：查找

假設我們今天要找 5

3次IO+1次內存定位

B-Tree：插入

B-Tree：刪除

B+Tree比起B-Tree多了什麼？

B-Tree還有什麼不足

• 解決：
  • B-Tree減少硬碟IO次數
  • B-Tree大幅降低樹高
• 未解決：
  • 未定位到數據行
  • 無法處理範圍查詢 

未定位到數據行

1. 直接把key對應的數據行直接儲存在節點中
2. 數據行單獨儲存，節點儲存對應數據行的位置
3. 修改Key與子樹的判斷邏輯，使子樹>=上一個Key且<下一個key，使所有訪問都落在leaf node中，leaf node中直接儲存數據行或數據行位置

未定位到數據行

1. 直接把key對應的數據行直接儲存在節點中
   > 造成過多 IO​
2. 數據行單獨儲存，節點儲存對應數據行的位置
3. 修改Key與子樹的判斷邏輯，使子樹>=上一個Key且<下一個key，使所有訪問都落在leaf node中，leaf node中直接儲存數據行或數據行位置

無法處理範圍查詢

1. 不改動：查詢時先查到左界，再查到右界，之後再透過DFS或BFS遍歷中間的所有節點
2. 依照「未定位數據行第三個方案」，將leaf node都加上指針連結

Index-Organized Table(IOT)

vs

Heap Table

什麼是Index-Organized Table

• 簡單的說 Table 本身就是顆B+Tree
• 就是剛剛說的leaf node儲存資料的情況

IOT的優點

• 資料是有序的，所以range scan on PK 很快
• 省下sort by PK 的時間
• （邏輯上）只需要一次IO便能拿到全部資料

IOT的缺點

• 資料都存放在leaf node上，因此能放row的數量有限
  • 經常會發生 leaf node 的 splitting/merging
  • 引發rows要物理上移動位置
• 如果PK是有序的，read & write 容易集中在少量的leaf-node，進而引發Contention問題
  • Contention容易導致blocking
  • 交易頻繁集中右側，造成page splitting/merging

IOT的缺點

• secondary index 只能儲存那個 record 的 PK
  • 使用 natural key 作 PK 的 table ，其 secondary index 會很肥大
  • 使用 secondary index 去找資料時，需要額外的 disk READ 在 IOT 上

什麼是Heap Table

• data「隨意」找一個 data page 存放。
• 就是剛剛說的leaf node儲存資料位置的情況

Heap Table 優點

• 因為 index leaf node 只存放 PK + pointer，因此
  • 可以存放更多的rows，減少 leaf node splitting/merging
  • 不會令row data需要移動物理位置
• 因為資料沒有指定位置存放，因此
  • 即使PK是用上auto-increment，不容易發生 data page contention
  • 寫入資料不容易發生blocking
• RDBMS 有 Free Space Management 機制，使得近期被寫進資料的 data page 會優先被重用

Heap Table 缺點

• Range Scan on Pk 一般需要整個 table 都作一次 scanning ，極吃IO
  • 一般商業系統( OLTP )極少使用 PK 作 Range Scan ，問題不大
  • 專門用作數據分析/產生報表的 OLAP ， Range Scan
    on PK 倒是常常發生
• （邏輯上）需要2次IO便能拿到全部資料
• 一但發生 index page contention ，影響範圍更大

IOT & Heap Table 小結 

• Cache hit rate是應該被重視的，但不應被過分追求
• Contention需要更快的 CPU single thread 效能，這方面的成長越來越慢。
• IO throughput 可以用  RAID 10 來解決 (SSD 的 Random IO throughput 也越來越好)

MySQL的索引實現與設計

• MySQL索引實現
• Secondary Index
• Database Normalization
• Database Bad Smells

MySQL索引實現

• MyISAM 實現

• InnoDB 實現

MyISAM 實現

1. 以Heap Table實現
2. index leaf node 只存放 PK + pointer

Heap Table 優點

• 因為 index leaf node 只存放 PK + pointer，因此
  • 可以存放更多的rows，減少 leaf node splitting/merging
  • 不會令row data需要移動物理位置
• 因為資料沒有指定位置存放，因此
  • 即使PK是用上auto-increment，不容易發生 data page contention
  • 寫入資料不容易發生blocking
• RDBMS 有 Free Space Management 機制，使得近期被寫進資料的 data page 會優先被重用

Heap Table 缺點

• Range Scan on Pk 一般需要整個 table 都作一次 scanning ，極吃IO
  • 一般商業系統( OLTP )極少使用 PK 作 Range Scan ，問題不大
  • 專門用作數據分析/產生報表的 OLAP ， Range Scan
    on PK 倒是常常發生
• （邏輯上）需要2次IO便能拿到全部資料
• 一但發生 index page contention ，影響範圍更大

InnoDB 實現

1. 以 Index of Table 實現
2. index leaf node 存放 data

IOT的優點

• 資料是有序的，所以range scan on PK 很快
• 省下sort by PK 的時間
• （邏輯上）只需要一次IO便能拿到全部資料

IOT的缺點

• 資料都存放在leaf node上，因此能放row的數量有限
  • 經常會發生 leaf node 的 splitting/merging
  • 引發rows要物理上移動位置
• 如果PK是有序的，read & write 容易集中在少量的leaf-node，進而引發Contention問題
  • Contention容易導致blocking
  • 交易頻繁集中右側，造成page splitting/merging

IOT的缺點

• secondary index 只能儲存那個 record 的 PK
  • 使用 natural key 作 PK 的 table ，其 secondary index 會很肥大
  • 使用 secondary index 去找資料時，需要額外的 disk READ 在 IOT 上

Secondary Index

• 簡介 Secondary Index
• 如何判斷增加 Index 是必須的
• Index & Schema

簡介 Secondary Index

• Primary Key 以外的 Index
• 大多數人說：查詢不夠快，就加上index的那個index
• index 是犧牲 WRITE 效能去提升 READ 的效能

• 每增加一個 index 時~

  • 有額外要改動的 index data page

  • 有額外可能性因為遇上 index page lock 而需要等待/發生 deadlock

如何判斷增加 Index 是必須的

• 增加unique index 不作為 primary index的原因是什麼？
• 增加該 index 有助於你的 TX 嗎？
• 該 index 的 cardinality 夠高嗎？
• 資料的存續期間多久？
• 資料是否一定要夠即時？
• 當前的 Table Schema 合理嗎？

Index & Schema

• 股市有句話：好的老師帶你上天堂 不好的老師帶你住套房
• 同理：好的 Schema 讓你不煩惱 不好的 Schema 讓你 On-Call 到飽
• 好的 Schema等同於：
  Database Normalization + 對的 PK

Index & Schema

以手遊來說，目前大多支持帳號、FB、Google、Game Center 等登入方式...

假設你要設計Table的話，你會採取哪個方式？

1. 一張 user table 包含所有資訊
2. user, user_account_login, user_fb_login, user_google_login....

​

Index & Schema

1. 一張 user table 包含所有資訊
2. user, user_account_login, user_fb_login, user_google_login....

​法 2 相較 1 有以下優點：

• 容易變更，更能適應業務變化
• 避免空間浪費
  • 讀取會員會加快
  • 新會員寫入也會變快

Database Normalization

• What is Data Normalization?
• Why is it important?
• 第一正規形式(1NF)
• 第二正規形式(2NF)
• 第三正規形式(3NF)

What is Data Normalization?

資料庫正規化就是指把關聯式資料庫的欄位與表單做規劃，讓資料重覆性與相依性能夠降到最低。當然這個"資料重覆性與相依性能夠降到最低"情況下，還必須讓資料庫可以正常運作。

• 重複的資料會浪費磁碟空間，並產生維護方面的問題
• 不一致的相依性會讓資料出錯誤。

Why is it important?

• 避免重複或多餘資料
• 避免資料不一致性
• 讓資料庫更易於維護
• 資料庫更容易依照業務調整

第一正規形式(1NF)

• 刪除各個資料表中的重複群組。
• 使用主索引鍵識別每一組關聯的資料。

重點：去除重複資料！

第一正規形式(1NF)

重複群通常會出現在會計帳上，每一筆記錄可能有不定個數的值。舉例來說：

第一正規形式(1NF)

想要消除重複群的話，只要把每筆記錄都轉化為單一記錄即可：

第一正規形式(1NF)

如果是因為缺乏唯一的識別碼而違反 1NF，只需要加入一個唯一識別碼即可：

第二正規形式(2NF)

• 它符合第一正規化
• 資料表中所有欄位的資料都必須和該資料表的主鍵（or複合主鍵）有完全依賴關係
  重點：在去除部分功能相依的問題

第二正規形式(2NF)

供應商的名稱和住址就只和供應商 ID 有關（部分依賴），這不符合第二正規化的原則：

第二正規形式(2NF)

第三正規形式(3NF)

• 符合 2NF
• 要求所有非主鍵屬性都只和主鍵有相關性，也就是說非主鍵屬性之間應該是獨立無關的
                   
                    重點：去除遞移相依的問題

第三正規形式(3NF)

小計依賴於非主鍵欄位「單價」和「數量」，不符合第三正規化：

第三正規形式(3NF)

Database Bad Smells

• Multi-purpose column
• Multi-purpose table
• Redundant data
• Tables with many columns
• Tables with many rows
• "Smart" columns
• Fear of change

Multi-purpose column

判別方法：系統需要知道這個 column /其他 column 的 value ，才知道具體怎去使用這個 column 的內容

Multi-purpose column

WHY NOT DO THIS?

• 需要額外的邏輯取出資料
• 資料增刪修改容易出錯

Multi-purpose table

判別方法：

• 某一 table 內，大量 column 的 value 是 null

• 這個 table 內有一個叫「 type 」或是「 kind 」的 column ， application 需要先知道這個 column 的值才 能決定這個 Record 怎麼使用

Multi-purpose table

WHY NOT DO THIS?

• 永遠只能寫出非最佳化的語句
• type 出錯後的引發的資料不一致問題
• 後續想優化，往往會不敢動
  • 多個application使用
  • 資料量十分龐大

Redundant data

判別方法：
發現當修改一筆資料時，許多資料要跟著變動

Redundant data

當有天中港路變成台灣大道......

台灣大道

Redundant data

比較好的作法可能是

台灣大道

Tables with many columns

判別：當一個表的欄位過多的時候

Database Normalization

Tables with many rows

判別：

1. 查詢中到索引且表結構有做 Normalization，但還是很慢
2. 對這張表做的任何操作耗時較久

解決：水平或垂直分表

• 水平分表例如把訂單拆成訂單跟詳情
• 垂直分表例如把資料照月份拆表

目的：改善效能

"Smart" columns

判別：

• 看到 column value 為 xml, json, array 
• 某個欄位得值前半部代表A意思，後半部代表B意思
  ex. JSSSC1912051041000000
   

缺點：

• smart column 內部資料不能再建立 index
• filtering / joining 時，Query 難於編寫且效能低下

Fear of change

判別：

• 太多應用在該表上
• 該表數據量太大
• 該表十分噁心

建議：
提早規劃優化流程，避免造成無法挽回的傷害

小結

• 重視你的表結構
• 謹慎評估索引增減與效益
• 步步為營的優化
  • 先學會走，再來說跑
  • 高報酬必定有高風險
    ex.分散式系統帶來的維運、資料一致性問題

索引使用策略與優化

• 最左前綴原理與相關優化
• 覆蓋索引
• 索引提示
• 優化器選擇不使用索引的情況
• ICP 優化
• InnoDB主鍵選擇與插入優化

最左前綴原理與相關優化

最左前綴原理與相關優化

最左前綴原理與相關優化

最左前綴原理與相關優化

由前面的例子就會發現索引有 leftmost prefixes 的特性，因此考量使用場景，下面的索引只有約一半有用

覆蓋索引

覆蓋索引即從索引中就可以拿到所有資訊

覆蓋索引

猜猜看哪一種查詢會對應到哪一個結果？

覆蓋索引

索引提示

• 索引提示不一定會用索引
• 強制索引提示在不匹配情況下無用

優化器選擇不使用索引的情況

• 因為我判斷你很智障
  • 根本沒用到那個Index
• 因為我判斷我比較聰明
  • 判斷不用可以減少IO次數
  • 判斷資料量大到乾脆主鍵直接查

ICP 優化

ICP 優化，取出索引同時會判斷是否可進行 where 條件過濾

InnoDB主鍵選擇與插入優化

原則：盡可能小、有序

InnoDB主鍵選擇與插入優化

版本：PK非遞增且佔用空間大    操作：寫入資料

InnoDB主鍵選擇與插入優化

版本：PK遞增且佔用空間小    操作：寫入資料

InnoDB主鍵選擇與插入優化

版本：PK非遞增且佔用空間大    操作：查詢資料

InnoDB主鍵選擇與插入優化

版本：PK遞增且佔用空間小    操作：查詢資料

Bonus Time

• MySQL Join時怎麼利用索引啊？
• MySQL Range Search怎麼實現？

MySQL Join時怎麼利用索引啊？

MySQL Range Search怎麼實現？

MySQL Range Search怎麼實現？

PS: 搜尋範圍包含資料數佔整體資料筆數的權重很高時，execution plan optimizer會選擇全表查詢