MySQL schema design for large tables

把大字段拆出來

主要是指那些 text 類型的欄位
例如 Article 的 content 內文欄位
可以拆出來變成 ArticleContent 表
因為在大部分的 query 裡你其實都不會需要 content 欄位
通常只有顯示單一 article 的時候才需要

例如 Profile 表
phone_number, address, occupation 之類的很少被用來 query 的欄位
其實也可以拆出來到另外一個表
ExtendedProfile

大表的水平拆分:置頂訊息

假設你有一個存放論壇文章的表 Post
文章分成一般用戶的「普通文章」和管理者發布的「置頂文章」
你可能會在 Post 表新增一個 boolean 欄位
區分是不是置頂文章

但是置頂文章和普通文章的數量可能差很多
而且置頂文章的 query 次數也會多很多(每一頁都要顯示)
所以另一個可行的辦法是幫置頂文章獨立開一個 table

這種方式就是所謂的「水平拆分」

不是所有的 COUNT 都需要實時

很多 count 可以透過 crontab 定時跑
然後存進某個欄位裡
前端顯示時直接去讀那個欄位的值即可

把同一個 counter 的資料存成多筆 record

這樣可以減少同時有多個 request 修改了同一個 row 所引起的 lock 的問題
Deadlock found when trying to get lock; try restarting transaction

在你的 counter table 新增一個 slot 欄位(槽)
每次寫入的時候隨機選擇(透過 slot 欄位)某一個 record 寫入
要讀取的時候就把這幾筆 SUM 起來

song_id count slot
100 2 1
100 5 2
100 3 3
100 8 4
101 1 1
101 19 2
101 3 3
101 7 4

UPDATE song_counter SET count = count + 1 WHERE song_id = 100 AND slot = 1;
UPDATE song_counter SET count = count + 1 WHERE song_id = 100 AND slot = 2;
UPDATE song_counter SET count = count + 1 WHERE song_id = 100 AND slot = 3;

缺點就是 record 會變超多
可以用 crontab 定期整理數據
把 counter 資料寫到 slot 1
然後刪掉其他的 slot

ref:
High Performance MySQL, Chapter 4.4