RDBMS 資料庫案例設計 (一) - Schema 設計技巧

PHP 也有 Day 番外篇，關於 Schema 的設計技巧 …

ref. 教材參考 由 Ant 提供

Schema 欄位設計怎麼開？

• 時間欄位

  • timestamp (only support until 2038)
  • datetime
  • integer

• 通常有國際標準的就直接 Follow, e.g. ISO, RFC, Schema.org

  • 電話號碼標準
  • 國際區碼 (Country Code)
  • 各國幣值

怎麼對欄位打 Index

情境一：用戶登入頁

SELECT * FROM users WHERE ( `email`={email} OR `username`={username} ) AND `password`={password} AND `password_expired_at` > NOW() AND `status` = {active};

where 後面主要有 email OR username AND password AND password_expired_at 接著

1. 對 where 後面分別建立 index

• index: [email], [username]
• explain: index merge idx_email, idx_username

2. 多打 password index

• index: [email], [username], [password]
• explain: idx_email
  • 發現原先打的 username 跟 password 都用不到了，只用 email 就可以

—- 清除所有 index —-

3. 複合鍵的 Index 順序差別為何?

3.1 username + email

• index: [username, email]
• explain: 沒吃到複合鍵的 index
  • 所以 OR 的條件應該要分別建立 index

3.2 email + username

• index: [email, username]
• explain: index sort_union(idx_email_username, idx_username_email)

情境二：用戶認證頁

SELECT id, email, status FROM users WHERE `email` = {email} AND `token` = {token} AND `token_expired_at` > NOW() AND `status` = {unverified}

• index: [email]
• explain: 直接吃掉 email index.

覆蓋索引 Covering Index. (最快速的 index)

撈資料的流程，通常是從 index 查找看是否滿足。

如果所要欄位在 index 裡面沒有，就要額外到 data 區查找來回傳。

但是如果要撈出來的資料在 index 裡面就涵蓋了，就不需要去 data 區撈資料了。這種取得方式稱為Covering Index 也可以說是最快速的 Index.

資料底層儲存

• innodb_space
  • 檢查 datatype 設計是否得宜，可以利用 innodb_space 工具指令，來把數據結構 dump 出來
  • 查看儲存的 record 位置以及順序等等
• page_illustrate
  • 畫出實際資料在 page 中的儲存大小與位置

案例分析 char vs varchar

利用 innodb_space, page_illustrate 來分析 char, varchar 之間的更新變換

char 變動資料案例

由於 char 的資料結構原本就是固定長度

• 不管同一筆資料在修改後長度是否 「超出」 或者 「小於」 原先長度
  • record 儲存位置不變
    • 長度 「超過」原先大小會被截斷
    • 長度 「小於」原先大小會留下空間
  • page 中的位置會產生碎片，因為原先就預留 char 的大小位置

varchar 變動資料案例

在發生更新資料長度不同的時候

• 與初始資料 「一樣長」，

  • record 儲存位置不變
  • page 儲存結構圖沒變，因為空間還是夠塞

• 比初始資料 「短」，

  • record 儲存位置不變
  • page 儲存結構圖沒變，因為空間還是夠塞

• 比初始資料 「長」，

  • record 儲存位置改變，因為原先位置放不下
  • page 儲存結構圖舊的位置產生碎片，新的資料存放到大小允許的空間

資料碎片

• 通常碎片發生在 update 跟 delete 的時候

  • 下一筆 insert 長度 「相同」 前一個碎片空間，也不會放在碎片空間
  • 下一筆 insert 長度 「小於」 前一個碎片空間，就會放進去了

• 副作用

  • 搜尋變慢
  • 佔硬碟空間

• 解決碎片浪費空間的方法

  • mysql optimze table
  • 需要時間，還會 Lock Table !!!!

延伸問題

• 當你要 update 從 null 改成有值時，會留下一個很大的碎片。
• 讀的時候無法預測，速度會變慢
• 對於 SQL 也不用下 is not NULL
• 對於底層影響很大
• 程式不用處理 null 的情境*(自行補充)*

當然如果有業務需求也可以使用

以上為聽 Ant 口語跟 Demo 操作時的個人筆記，建議大家都可以試著自己操作分析加深印象

ref. Hackmd

延伸補充 (via 同事 jnlin)

1. timestamp 還是用 integer 存會比較好，因為有時區的 bug : https://bugs.mysql.com/bug.php?id=38455
2. 複合鍵的 index 只有在後面的 index 可以用 RANGE QUERY (ex 大於, 小於) ref
3. varchar & text 都會有 fragment 的問題，有些時候建議另外開一張表
4. 如果 EXPLAIN 沒用 index，可以用 USE INDEX 來 hint sql parse engine 來用