網站整體優化其一：資料庫優化

仲介交易 HTTP://www.aliyun.com/zixun/aggregation/6858.html">SEO診斷 淘寶客 雲主機 技術大廳

目前web2.0的程式，很大瓶頸是資料庫的吞度量。 不過，如何才能確定系統的瓶頸是資料庫呢，因為只有確定資料庫是整個系統的瓶頸，我們才有必要去優化他，畢竟，還有這麼多需求等待我們去做。

如何確定資料庫是瓶頸?

1 如果程式設計良好，有一個資料庫操作邏輯層，可以從這個層的統計資料看到每個請求花費的時間，如果平均時間已經不能讓你容忍的話，資料庫已經是瓶頸了。

2 在資料庫的伺服器上使用top命令，看看mysql伺服器佔用資源的情況，看看機子的平均負載。

如果伺服器的平均負載已經很高，mysql佔用了塊100%的cpu資源，說明mysql伺服器很忙了。

3 在資料庫伺服器上使用iostat命令，看看磁片IO，如果block住的操作比較多的話，說明資料庫操作還是過於頻繁了，磁片都回應不急了。

4 建議打開mysql的慢查詢日誌，這樣grep select看一下日誌中的慢查詢的數量，如果數量較多，說明慢查詢的數量很多，需要進行調整了。

5 如果有一天資料庫無法插入了，需要檢查一下資料庫表是不是過大了。 32位的作業系統上一個表最大的容量是2^32這麼大。 不過還是建議增加一個資料庫操作的邏輯層，在資料庫操作的前後記錄下操作的時間，進行統計上報，利用監控程式來報警相關618.html">負責人，這樣可以及早的知道資料庫是瓶頸，提前做出優化。

知道資料庫是瓶頸了，如何來進行優化呢?

1 我們第一個想到是看看資料庫的容量是不是太大了，如果資料庫表太大的話，索引檔也會比較大，每次的更新操作就會更加的費時。 需要考慮進行分庫和分表了。

分庫分表按照一定的規則來對資料庫中的記錄進行分區來存儲，一方面可以做到一定的負載均衡，將請求平分下來，每個區段去獨自承受;另一方面，分庫分表可以使我們存儲和操作更多的資料。

不過分庫分表需要多之前基於單庫的程式進行修改，存在一定的風險，因此，在程式設計之初就應該考慮到分庫分表的需要，最好是將資料庫操作層獨立出來，便於擴展和更改。

2 如果資料庫表不是很大，但是查詢慢的話，我們需要檢查一下我們的sql查詢語句，利用mysql的explain語句看看是不是使用了索引，如果沒有使用索引，那我們需要在相應的欄位上建上索引，反復的使用explain， 尋找到個一個合適的索引。

在建索引時需要考慮：

1)資料庫的索引要做到越少越好。

因為每次更新都需要更新索引，索引過多就會降低寫入的速度。

2)最窄的欄位放在鍵的左邊。

這樣提高了索引中每一個點的基數，帶來更好的索引讀寫性能。

3)儘量避免file sort排序、臨時表和資料表掃描。

對於大表，全資料表掃描會導致大量的磁片IO的操作，會導致操作非常的緩慢。

4)對於大表，儘量不要將索引建在字串類型的列上，字串的匹配是很費時的，需要付出很高的性能代價，如果一定有必要，建議對字串列進行hash後取一個整形的值來進行索引。

3 如果更新操作有點慢，而讀操作的回應要求不需要很及時的話，可以考慮利用mysql的主從熱備來分擔讀寫的壓力。

畢竟對資料庫的操作，寫少讀多。 因此，我們將對資料庫的寫操作放到mysql的主伺服器上，利用mysql的熱備，我們在備份的資料庫伺服器上進行讀操作，由於可以有多個熱備mysql，於是可以將讀操作分佈在多個熱備上面，從而將讀操作均衡開來，提高讀操作的性能。

4 緩存的使用

緩存是一切幕後程式的根本，因為80%的請求是對應20%的資料，我們只需要少量的記憶體將20%的資料緩存起來，就可以大大的滿足我們系統需求，何樂而不為呢。

1)mysql設置中儘量增加key cache，thread cache、查詢的cache。

2)在應用程式層增加一個memcached這樣的通用cache。

3)對於少量資料，但是操作頻繁的表使用mysql提供的記憶體heap表，可以獲得極高的寫入和讀取速度。

5 資料庫的設計上進行優化

對於傳統的資料庫設計我們講究建模範式，避免資料的冗余從而導致髒資料。 然而在我們實際的應用中需要根據情況來使用第三范式的一些規則，對於一些頻繁需要在多個地方出現的資料，如同一個論壇這種使用者和主題以及回復等有關聯的應用中，如果我們將使用者同主題和回復分開來存儲， 每次查詢一下一篇文章或者一個回復的情況都需要對使用者表和主題表或者回復表進行聯查，如果資料量小的話，這樣聯查的性能還是可以接受的，如果表大一點，上了3、4十萬以上的資料，聯查的速度就會比較慢了。

該范式化的地方需要進行范式化，但是還是需要根據情況來設計我們的表，從而達到性能和良好設計的折中。

其它的話：

1 對於資料庫的操作建議分層處理，至少分為兩層，一層是資料庫操作的邏輯層，一層是資料庫的cache層。

從一開始就考慮如此，可以很方便在未來對資料庫進行劃分部署、分庫分表擴展。

2 增加mysql的監控，監控mysql的慢查詢日誌，監控mysql的請求情況。

3 根據自己的需要來選擇mysql的儲存引擎。

myisam有較高的讀寫速度，但是由於表鎖定，不能同時進行快速的讀和寫。

innodb支援事務，提供了行級的鎖，但是為了使用事務，表空間會比較大，而且不支援全文索引。

heap將表放到記憶體中，適合與表小而需要頻繁操作的情況，如使用者資訊，其讀寫很快，但是不是持久的，需要自己來寫工具讓其持久。

4 mysql伺服器的一些狀態檢測的命令。

show slave status：可以看到主從同步的情況。

show [full] processlist：可以看到mysql伺服器的請求情況，如果發現lock情況很多，需要注意了。

show status：可以看到mysql伺服器的各種請求情況。

我的小站 HTTP://www.qin3.com