提高存取MySQL效率小技巧

資料庫的存取效率，永遠是網站存取速度很重要的瓶頸，所以能在資料庫存取時提高效率，可以讓網站的存取速度增加很多，在這裡紀錄一下一些常用的小技巧

• 資料類型
• 越小越好
• 越簡單越好
• 盡量不使用NULL
• 程式
• 使用指定索引
• 少用JOIN，多用幾次SELECT撈取大量資料
• 索引
• 自定義Hash Index做字串完整比對

資料類型

越小越好

要使用能夠用來正確儲存資料的最小類型的資料型態，要確保不會低估要儲存的資料大小，因為當資料可以確定用比較少的資料去儲存的時候，所使用的空間也會更少，所需要的索引空間也會越少，取得資料所需要的計算時間也會越短。

在可以用CHAR(20)去儲存資料時，就不要用CHAR(200)
在可以用VARCHAR(20)去儲存資料時，就不要用VARCHAR(200)
在可以用TINYINT去儲存資料時，就不要用INT
在可以用TEXT去儲存資料時，就不要用LONGTEXT
在可以用BLOB去儲存資料時，就不要用LONGBLOG

越簡單越好

整數（INT）> 固定字串（CHAR）> 變動字串（VARCHAR）> 文字（TEXT）

越簡單的資料類型，資料庫所需要用來建立索引的效率越好，因為資料類型越複雜代表資料的排列組合越多，所以需要更大的索引及計算去取得資料

在可以用整數（INT）去儲存資料時，就不要用固定字串（CHAR）
在可以用固定字串（CHAR）去儲存資料時，就不要用變動字串（VARCHAR）
在可以用變動字串（VARCHAR）去儲存資料時，就不要用文字（TEXT）

盡量不使用NULL

若非必要儲存NULL的資料，否則要盡可能的把資料欄位設定為NOT NULL，資料庫很難最佳化有NULL資料欄位的查詢，可以NULL的資料欄位需要更多的儲存空間，資料庫還需要對其進行特殊處理，而當有NULL資料欄位使用索引（INDEX）的時候，每一條的索引紀錄必需要額外紀錄資料，導致查詢時索引的效率降低。

若真的要儲存NULL，在不影響原有的資料的情況下，可以考慮用0、特殊值...等等之類的值去代替，可以用來區別是否為NULL

程式

指定使用索引

SELECT id
FROM data USE INDEX(type)
WHERE type=12345 AND level > 3
ORDER BY id

在下SQL語法的時候，有時某些語法使用某些索引執行效率會比較好，可是有時候MySQL沒辦法替我們選擇一個最適合的索引，導致執行的效率很慢（slow query），所以我們必須使用USE INDEX去指定執行效率好的索引，以提升效率。

少用JOIN，多用幾次SELECT撈取大量資料

SELECT user.id , user.name , post.id , post.content
FROM user , post
WHERE user.id = post.user_id ......

我們在撈取使用者的文章資訊的的時候，我們可能會用JOIN去撈取我們要的資料，這樣很直覺，只是當使用者資料有10000筆，而文章有99999筆，像這種有大量資料時候的話，使用JOIN對於資料存取真的是惡夢，因為JOIN過後表示會有10000x99999=999990000筆資料，然後再從這麼大量的資料中去撈取WHERE判斷式中指定的資料，在資料庫伺服器記憶體不夠的時候鐵定會炸掉。

解決方式是我們可以分批撈取使用者的資料，以及文章的資料

SELECT user.id , user.name
FROM user

SELECT id , content , user_id
FROM post

在使用者資料資料撈取出來之後，使用迴圈將使用者存成陣列，但是「陣列的索引」是使用可以識別的「使用者編號（user.id）」當作索引值。

$user['user.id'] = array()

在撈取文章資料後，必須將撈取的文章指定給該文章的作者（使用者），而我們撈取的使用有撈取「使用者編號（user.id）」，所以可以透過這個資料，將文章存到使用者資料下的文章陣列。

$user[$post['user_id']]['post'] = $post_array

透過分次撈取，本來的10000x99999=999990000筆資料，就會變成10000+99999=109999筆資料，記憶體消耗降低極大下，也可以達到同樣的功能

後記

JOIN 在資料庫幾乎是必學的語法，讓資料表的正規化，避免資料冗余，在查詢的時候也可以透過 JOIN 組合出想要的資料，所以 JOIN 對於資料庫正規化有它的用途，但資料庫的發展的初期中，還沒有像現在有用到巨量的資料（Google、Facebook、Twitter ... etc），所以在流量&資料量小的時候，資料庫在 JOIN 時的查詢回應速度通常差沒幾毫秒 （0.01~0.05毫秒），所以都在可接受的範圍，但現在大型網站一天都好幾千萬的存取量，假如每天有 1000 萬的存取次數，而每個地方都慢了 0.05 毫秒，總共就會慢了 10,000,000 x 0.05 = 500,000 毫秒 = 500 秒，在我們要求每次回應時間最差都要在 1 ~ 2 秒內回應，若是像 Google、Facebook 這樣的大型服務，要求可能都要在 0.1 秒以內，這樣慢的時間可能會讓使用者等到不耐煩，若 $$ 多多加機器僅能簡單應付巨量級的查詢，沒辦法解決 JOIN 所造成的查詢瓶頸，所以在巨量級資料的情況下，幾乎很少會使用 JOIN 的方式去查詢所有的資料出來，天下武功，無堅不摧，唯快不破。

索引

自定義Hash Index做字串完整比對

我們知道在對字串（CHAR或VARCHAR）去做查找的時候效率會遠比對整數（INT）查找還慢，因CRC32對字串做校驗後會回傳整數的校驗碼，我們在資料表增加一個整數型態欄位，儲存要比對字串的校驗碼。

crc32

建立str 的 32 位迴圈冗余校驗碼多項式。這通常用於檢查傳輸的資料是否完整。
由於 PHP 的整數是帶符號的，許多 crc32 校驗碼將返回負整數，因此你需要使用 sprintf() 或 printf() 的「%u」格式符來取得表示無符號 crc32 校驗碼的字串。

在原本實作email登入時會對email欄位做索引，所以會先去查找email字串欄位的資料，之後再去比對密碼是否正確，但若資料過多字串比對的效率會降低很多

SELECT id,name,email
FROM user
WHERE email = "kejyun@gmail.com"
AND password = "xxx"

我們加入了emailcrc的欄位去儲存對email字串的校驗碼，再查找email字串欄位的資料前，先透過crc整數校驗碼快速過濾掉不可能的資料，之後再從少數的資料中做email字串欄位字串比對，如果資料量很大，這樣的效率會提升很多。

SELECT id,name,email
FROM user
WHERE emailcrc = CRC32("kejyun@gmail.com")
AND email="kejyun@gmail.com"
AND password="xxx"

這邊要注意的是沒辦法只使用crc校驗碼去當作唯一的條件，不同的字串可能會出現相同的校驗碼，所以最後還是要對你要比對的字串做比對，避免查詢發生錯誤。

email	emailcrc
kejyun1@gmail.com	1234567890
kejyun2@gmail.com	1234567890

參考資料

• High Scaling Websites Structure Learning Notes 大型網站架構學習筆記
• Database Optimization
• Index Hint Syntax
• MySQL Performance Blog
• High Performance MySQL(Books)
• High Performance MySQL, 2nd Edition原文書免費下載
• [MySQL]執行順序與效率概論
• PHP手冊 - crc32