[轉]MySQL的最佳化和架構

普通MySQL運行，資料量和訪問量不大的話，是足夠快的，但是當資料量和訪問量劇增的時候，那麼就會明顯發現MySQL很慢，甚至down掉，那麼就要考慮最佳化我們的MySQL了。

最佳化無非是從三個角度入手：
第一個是從硬體，增加硬體，增加伺服器。
第二個就是對我們的MySQL伺服器進行最佳化，增加緩衝大小，開多連接埠，讀寫分開。
第三個就是我們的應用最佳化，建立索引，最佳化SQL查詢語句，建立緩衝等等。

我就簡單的說說SQL查詢語句的最佳化。因為如果我們Web伺服器比資料庫伺服器多或者效能優良的話，我們完全可以把資料庫的壓力轉嫁到Web伺服器上，因為如果單台MySQL，或者 Master/Slave 架構的資料庫伺服器都負擔比較重，那麼就可以考慮把MySQL的運算放到Web伺服器上去進行。當然了，如果你Web伺服器比資料庫伺服器差，那就把壓力放在資料庫伺服器上吧，呵呵。 如果是把MySQL伺服器的壓力放在Web伺服器上，那麼很多運算就需要我們的程式去執行，比如Web程式中全部交給PHP指令碼去處理資料。單台MySQL伺服器，查詢、更新、插入、刪除都在一台伺服器上的話，訪問量一大，你會明顯發現鎖表現象，當對一個表進行更新刪除操作的時候，就會拒絕其他動作，這樣就會導致鎖表，解決這個問題最簡單直接的辦法就是拿兩台MySQL伺服器，一台負責查詢（select）操作，另外一台負責更改（update/delete/insert），然後進行同步，這樣能夠避免鎖表，如果伺服器更多，那麼就更好處理了，可以採用分散式資料庫架構和資料的散列儲存，驚天動地私服下面我們會簡單說一下。

一、SQL的最佳化和注意事項
現在我們假設我們只有一台MySQL伺服器，所有的select/update/insert/delete操作都是在這上面進行的，我們同時有三台Web伺服器，通過DNS輪巡來訪問，那麼我們如何進行我們應用程式和SQL的最佳化。

1. Where條件 在查詢中，Where條件也是一個比較重要的因素，盡量少並且是合理的where條件是很重要的，在寫每一個where條件的時候都要仔細考慮，盡量在多個條件的時候，把會提取盡量少資料量的條件放在前面，這樣就會減少後一個where條件的查詢時間。 有時候一些where條件會導致索引無效，當使用了Mysql函數的時候，索引將無效，比如：select * from tbl1 where left(name, 4) = ”hylr”，那麼這時候索引無效，還有就是使用LIKE進行搜尋匹配的時候，這樣的語句索引是無效的：select * from tbl1 where name like ”%xxx%”，但是這樣索引是有效：select * from tbl1 where name like ”xxx%”，所以謹慎的寫你的SQL是很重要的。

2. 關聯查詢和子查詢 資料庫一個很重要的特點是關聯查詢，LEFT JOIN 和全關聯，特別是多個表進行關聯，因為每個關聯表查詢的時候，進行掃描的時候都是一個笛卡爾乘積的數量級，掃描數量很大，如果確實是需要進行關聯操作，請給where或者on的條件進行索引。 關聯操作也是可能交給應用去操作的，看資料量的大小，如果資料量不是非常大，比如10萬條以下，那麼就可以交給程式去處理（totododo提出筆誤，特此修正），程式分別提取左右兩個表的資料，然後進行迴圈的掃描處理，返回結果，這個過程同樣非常耗費Web伺服器的資源，那麼就需要取決於你願意把壓力放在Web伺服器上或者資料庫伺服器上了。 子查詢是在mysql5中支援的功能，比如：select * from tbl1 where id in(select id from tbl1)，那樣效率是非常非常低，要盡量避免使用子查詢，要是我，絕對不用真封神私服，呵呵。

3.   一些耗費時間和資源的操作 SQL語句中一些浪費的操作，比如 DISTINCT、COUNT、GROUP BY、各種MySQL函數。這些操作都是比較耗資源的，我想應用最多的是count字句吧，如果使用count，盡量不要count（*），最好count一個欄位，比如count（id），或者count(1)，（據totododo測試效率其實是一樣的），同樣能夠起到統計的作用。如果不是十分必要，盡量不要使用distinct操作，就是提取唯一值，你完全可以把這個操作交給指令碼程式去執行提取唯一值，減少MySQL的負擔。group by 操作也是，確實需要分組的話，請謹慎的操作，如果是小批量的資料，可以考慮交給指令碼程式去做。 至於MySQL的函數，估計很多常用，比如有人喜歡把截取字串也交給MySQL去操作，或者時間轉換操作，使用比較多的函數像 SUBSTR(), CONCAT(), DATE_FORMAT(), TO_DAYS(), MAX(), MIN(), MD5() 等等，這些操作完全可以交給指令碼程式去做，減輕MySQL的負擔。

4. 合理的建立索引 索引的提升速度的一個非常重要的手段，索引在對一些經常進行select操作，並且值比較唯一的欄位是相當有效，比如主鍵的id欄位，唯一的名字name欄位等等。 但是索引對於唯一值比較少的欄位，比如性別gender欄位，寥寥無幾的類別欄位等，意義不大，因為性別是50%的幾率，索引幾乎沒有意義。對於update/delete/insert非常頻繁的表，建立索引要謹慎考慮，因為這些頻繁的操作同樣對於索引的維護工作量也是很大的，最後反而得不償失，這個需要自己仔細考慮。索引同樣不是越多越好，適當的索引會起到很關鍵的作用，不適當的索引，反而減低效率維護，增加維護機戰私服索引的負擔。

5. 監控sql執行效率 在select語句前面使用EXPLAIN字句能夠查看當前這個select字句的執行情況，包括使用了什麼操作、返回多少幾率、對索引的使用方式如何等等，能夠有效分析SQL語句的執行效率和合理程度。 另外使用MySQL中本身的慢查詢日誌：slow-log，同樣能夠記錄查詢中花費時間比較多的SQL語句，好對相應的語句進行最佳化和改寫。 另外在MySQL終端下，使用show processlist命令能夠有效查看當前MySQL在進行的線程，包括線程的狀態，是否鎖表等等，可以即時的查看SQL執行情況，同時對一些鎖表操作進行最佳化。

二、資料庫伺服器的架構和分布想法
對於伺服器的架構設計，這個其實是比較重要的，一個合理的設計，能夠讓應用更好的運行。當然，架構的設計，取決於你的應用和你硬體的實際情況。我就簡單的說說幾種不同的資料庫結構描述設計方式，權當是一個個人的想法，希望能夠有協助。
1. 單台伺服器開多進程和連接埠
單台MySQL伺服器，如果使用長連結等等都無法解決負載太大，串連太多的問題，不凡考慮採用一台MySQL上使用多個連接埠開啟多個MySQL守護進程的方法來緩解壓力。當然，前提是你的應用必須支援多連接埠，並且你的cpu和記憶體足夠運行多個守護進程。
優點 是能夠很好的緩解暫時伺服器的壓力，把不同的操作放在不同的連接埠，或者把不同的項目模組放在不同的連接埠去操作，良好的分擔單個守護進程的壓力。
缺點 是資料可能會產生紊亂，同時可能會導致很多未知的莫名風雲私服錯誤。呵呵
2. 使用Master/Slave的伺服器結構
Mysql本身具有同步功能，完全可以利用這個功能。構建 Master/Slave 的主從伺服器結構，最少只需要兩台MySQL伺服器，我們可以把 Master 伺服器使用者更新操作，包括 update/delete/insert，把Slave伺服器用於查詢操作，包括 select 操作，然後兩機進行同步。 優點 是合理的把更新和查詢的壓力分擔，並且能夠避免鎖表的問題。 缺點 是更新部即時，如果網路繁忙，可能會存在延遲的問題，並且任何一台伺服器down掉了都很麻煩。
3. 使用分布式的散列儲存
這種結構適合大資料量，並且負載比較大，然後伺服器比較充足的情況。分布式儲存結構，簡單的可以是多台伺服器，每台伺服器功能是類似的，但是儲存的資料不一樣，比如做一個使用者系統，那麼把使用者ID在1-10萬以內的儲存在A伺服器，使用者ID在10-20萬儲存在B伺服器，20-3-萬儲存在C伺服器，以此類推。如果每個使用者訪問的伺服器不足，可以構建組伺服器，就是每組使用者擁有多台伺服器，比如可以在某使用者組建立兩台MySQL伺服器，一台Master，一台Slave，同樣分離他們的更新和查詢操作，或者可以設計成雙向同步。同時，你的應用程式必須支援跨資料庫和跨伺服器的操作能力。 優點 是伺服器的負載合理的被平攤，每台伺服器都是負責一部分使用者，如果一台伺服器down掉了，不會影響其他使用者ID的使用者正常訪問。同時添加節點比較容易，如果又增加了10萬使用者，那麼又可以增加一個節點伺服器，升級很方便。 缺點 是任何一台資料庫伺服器down掉或者資料丟失，那麼這部分伺服器的使用者將很鬱悶，資料都沒了，當然，這個需要良好的備份機制
補充一： .資料庫的設計
盡量把資料庫設計的更小的占磁碟空間. 1).儘可能使用更小的整數類型.(mediumint就比int更合適). 2).儘可能的定義欄位為not null,除非這個欄位需要null.（這個規則只適合欄位為KEY的情形） 3).如果沒有用到變長欄位的話比如varchar,那就採用固定大小的紀錄格式比如char.（CHAR 總是比VARCHR快） 4).表的主索引應該儘可能的短.這樣的話每條紀錄都有名字標誌且更高效. 5).只建立確實需要的索引。索引有利於檢索記錄，但是不利於快速儲存記錄。如果總是要在表的組合欄位上做搜尋，那麼就在這些欄位上建立索引。索引的第一部分必須是最常使用的欄位.如果總是需要用到很多欄位，首先就應該多複製這些欄位，使索引更好的壓縮。 （這條只適合MYISAM引擎的表，對於INNODB則在儲存記錄的時候關係不大，因為INNODB是以事務為基礎的，如果想快速儲存記錄的話，特別是大批量的匯入記錄的時候） 6).所有資料都得在儲存到資料庫前進行處理。 7).所有欄位都得有預設值。 8).在某些情況下,把一個頻繁掃描的表分成兩個速度會快好多。在對動態格式表掃描以取得相關記錄時，它可能使用更小的靜態格式表的情況下更是如此。 (具體的表現為：MYISAM表的MERGE類型，以及MYISAM和INNODB通用的分區，詳情見手冊) 9).不會用到外鍵約束的地方盡量不要使用外鍵。
補充二：系統用途
1).及時的關閉對MYSQL的串連。 2).explain 複雜的SQL語句。（這樣能確定你的Select 語句怎麼最佳化最佳） 3).如果兩個關聯表要做比較話，做比較的欄位必須類型和長度都一致.（在資料龐大的時候建立INDEX） 4).LIMIT語句盡量要跟order by或者 distinct.這樣可以避免做一次full table scan. 5).如果想要清空表的所有紀錄,建議用truncate table tablename而不是delete from tablename. 不過有一個問題，truncate 不會在交易處理中復原。因為她要調用create table 真封神私服 語句。 （Truncate Table 語句先刪除表然後再重建，這個是屬於檔案層級的，所以自然快N多） 實測例子： song2為INNODB表。 mysql> select count(1) from song2; +———-+ | count(1) | +———-+ |   500000 | +———-+ 1 row in set (0.91 sec)
mysql> delete from song2; Query OK, 500000 rows affected (15.70 sec) mysql> truncate table song2; Query OK, 502238 rows affected (0.17 sec)
6).能使用STORE PROCEDURE 或者 USER FUNCTION的時候.（ROUTINE總是減少了伺服器端的開銷） 7).在一條insert語句中採用多重紀錄插入奇蹟世界私服格式.而且使用load data infile來匯入大量資料，這比單純的indert快好多.（在MYSQL中具體表現為：Insert INTO TABLEQ VALUES (),(),…();） （還有就是在MYISAM表中插入大量記錄的時候先禁用到KEYS後面再建立KEYS,具體表現語句： Alter TABLE TABLE1 DISABLE KEYS;Alter TABLE TABLE1 ENABLE KEYS; 而對於INNNODB 表在插入前先 set autocommit=0;完了後：set autocommit=1;這樣效率比較高。） 8).經常OPTIMIZE TABLE 來整理片段. 9).還有就是date 類型的資料如果頻繁要做比較的話盡量儲存在unsigned int 類型比較快。

From : http://hi.baidu.com/phper_yang/blog/item/ff69b94893c000fb83025c6f.html