2012年12月25日 星期二

提高存取MySQL效率小技巧

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







資料類型

越小越好

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

在可以用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 = "[email protected]"
AND password = "xxx"
我們加入了emailcrc的欄位去儲存對email字串的校驗碼,再查找email字串欄位的資料前,先透過crc整數校驗碼快速過濾掉不可能的資料,之後再從少數的資料中做email字串欄位字串比對,如果資料量很大,這樣的效率會提升很多。

SELECT id,name,email
FROM user
WHERE emailcrc = CRC32("[email protected]")
AND email="[email protected]"
AND password="xxx"
這邊要注意的是沒辦法只使用crc校驗碼去當作唯一的條件,不同的字串可能會出現相同的校驗碼,所以最後還是要對你要比對的字串做比對,避免查詢發生錯誤。

email emailcrc
[email protected] 1234567890
[email protected] 1234567890



參考資料

沒有留言:

張貼留言

ADS