Fundamentals of Database Engineering - How to improve slow DB queries
最近趁著稍微有空 把之前一直很想上的一堂Udemy課程上完了
Fundamentals of Database Engineering
https://www.udemy.com/course/database-engines-crash-course/
講師從基本的ACID講到 index底層原理、sharding partitioning等工作常常碰到東西的原理
以前可能都只是會使用而已，這堂課是從底層開始講述各種tradeoff
干貨還不少，推薦給大家
其中有個專題是Working with Billon-Row Table
裡面講到了 如果你DB reads很慢 你該怎麼辦 拿來分享給大家
1. 加Index
這應該是很多人都會回答的第一個方式，對的確是這樣沒錯，
通常read 慢 常常關鍵在你沒加索引 (常常WHERE, ORDER, GROUP的記得要加)或是 索引失效
例如你加了composite index (a, b, c)
但是查詢是where b=2 and c=3 這樣就會失效 畢竟索引底層原理就是排序過 所以B和C是全局無序的 他只相對A有序
或者用了like %xx 之類的也會造成索引失效
note1: 可以使用Explain or Analyze去分析查詢 explain不會真的去跑 但是它會讓你知道執行計劃是什麼 例如fullscan就真的蠻糟糕的...
note2: 某些情況下 可以達到"Index Only Scan" 例如你想要查的資料本身就只有該欄位 (select name from people, 然後name本身就是index) 這樣速度會更快
2. Partition 分區
Partion的方式有兩種，分別為vertical(VP)及horizontal partition(HP)， 我們看資料的方式通常是使用表格的，也就是說有欄有列， VP就是根據欄來做分拆，而HP是對列進行分拆 會比較常見
你會選一個partitioning key 去切 這個會有關於業務邏輯 你要確保每次查詢盡量不要 跨partition (效能不佳)
例如: Dates(訂單分月), List(郵遞區號), Hash(Consistent hash)
Partition過的index會小很多 也更好fit進去memory
例如，可以按照訂單的日期將資料分成每個月一個分區。這樣做的好處是，當進行特定月份的查詢時，系統可以僅搜索該月份的分區，而不需要搜索整個資料表
如果有訂單只保留兩年的需求 Partition在刪表的速度也是很快的
這是DB內建的機制 你的query還是下 select … from CUSTOMERS_TABLE
實際上他會創很多表 可能是CUSTOMERS_200K, CUSTOMERS_400K
而DB engine會找到哪張表 並只query那個表
note1: 很多人會把HP和sharding搞混 這兩個不完全相等
HP資料是可以放在同一個server的 (其實只是將多張表 放到同一個DB裡面) 也可以放在不同server 那這時候就是類似Sharding (主要是增加處理量) 水平擴展
note2: VP通常是把不常用的東西拉出來
- 例如你有個欄位有存blob, 但是你平常query根本用不到 就拉出來 或是有些column 根本就不需要 非常少用到 除了減少每次query需要讀的資料外 也可以減少被lock的機會
3. Cache
這邊就不多說 主要是使用者查詢是否可以接受stale的data 或者是說少更改的資源 在更改的同時去invalidate cache
4. Replication
也就是俗話說的 讀寫分離 一個master帶多個slave, master負責寫入 查詢都到slave 業務邏輯多讀取得很適合
不過要特別注意到 一致性vs效能的trade off
這篇不錯 可以看看
https://mark-lin.com/posts/20190925/
=> ASYNC複製的話 效能是最好的 (master先ack後 再透過binlog傳給slave 當然極短時間內可能發生還沒同步的情況 -replica lag)
=> Sync mode的話 效能差 但是就是強一致性
基本上來說 兩者都有使用場景
例如standby的server通常都是放在另外一個AZ 而且會要求sync mode
而一般read-only的server就不一定了
5. Sharding
把不同row放到不同server
例如你可以找一個field當作shard key(可以使用consistent hashing)
優點: Scalability 拆表放到不同server後 連index也更小了
缺點: 程式複雜度極高 而且會喪失Transaction的性質 (因為Transaction只發生在一個單筆的DB中) Rollbacks就很麻煩 multiple shard 幾乎沒有任何的acid特性 沒有transcation, 沒有rollback, resharding也會是蠻惡夢的事情
所以其實真的需要shard的機會不高 不必要一開始就over engineer
6. Database Configuration/Maintenance
這比較偏資料庫管理者(DBA)的範疇
像是記憶體分配 query_cache_size/max_connections 等等參數 或是記得要實現connection pool (避免每次請求都產生新的connections
另外真的資料庫不夠用時 也可以考慮垂直升級DB的hardware (像是CPU memory等硬參數)
在維護方面 有些資料庫可以定期清理碎片化的資料 (vacuum)
或者是reindexing (可能因為刪除了過多資料後 導致資料index偏一邊 效率不好) 或者是statistics updates (這會讓資料庫的query plan更佳準確)
7. Concurrency and Locking
這段又更複雜了 包含了隔離級別 樂觀鎖 悲觀鎖 exclusive lock, shared lock, Row-level locking/ table lock/deadlock等等
基本上就是在解決consistency 與performance中要做出選擇
改天再寫一篇跟大家分享!
8. (附錄) SQL vs NoSQL 選型比較
這邊我就先鎖定NOSQL的範疇在document store (e.g. MongoDB)
- Schemas
這應該是最重要的一個評估條件
如果資料常常變動 且不是結構化的資料 例如user_settings這種看起來未來就會被塞很多new field的table… 那麼會建議就用nosql
反之schema也有enforce 資料integrity的好處
=> schema不固定的話 可以選nosql - Normalize & De-normalize
SQL會要求資料正規化 也就是你會把共同欄位提取出來 因此有很多好處 例如節省硬碟空間 保持資料一致性 寫入很快. denormalize就是有著所有相反的缺點 聽起來很糟吧 不過唯一的優點就是他讀取快 （SQL 需要很多random IO access)
https://engineering.fb.com/2012/01/05/web/building-timeline-scaling-up-to-hold-your-life-story/ FB在用戶的timeline中就用了大量的denormalize的方法降低資料庫負擔
=> 有foreign key 這種想法+需要正規化的話 sql是不錯的選擇
3. Scaling (performance)
Scaling也是蠻大的課題 SQL通常難以實現橫向擴展 沒有說sql就不能sharding, 但是SQL rely在relation
假設有三個表
post/user/comment
你在SQL裡面的設計 post 會去join user info 或comment info
你一sharding, 你post裡面的東西要去哪裡找這個shard, 事情變成更複雜
但是NOSQL本身就self-donormalized, 他是一個完整的object
這樣變成你只要找出shard key 就好
=> SQL通常vertical scaling比較容易 頂多加個read replicas, 一旦sharding了 就難以確保acid性質
4. Consistency
在強烈要求consistency的資料庫下 即使mongoDB在近年來新增了不少transaction的 support 但是其生態性和範圍都還比不上SQL
