驚天動地私服與MySQL數(shù)據(jù)庫服務器的優(yōu)化

普通MySQL運行，數(shù)據(jù)量和訪問量不大的話，是足夠快的，但是當數(shù)據(jù)量和訪問量劇增的時候，那么就會明顯發(fā)現(xiàn)MySQL很慢，甚至down掉，那么就要考慮優(yōu)化我們的MySQL了。

優(yōu)化無非是從三個角度入手：
第一個是從硬件，增加硬件，增加服務器
第二個就是對我們的MySQL服務器進行優(yōu)化，增加緩存大小，開多端口，讀寫分開
第三個就是我們的應用優(yōu)化，建立索引，優(yōu)化SQL查詢語句，建立緩存等等

我就簡單的說說SQL查詢語句的優(yōu)化。因為如果我們Web服務器比數(shù)據(jù)庫服務器多或者性能優(yōu)良的話，我們完全可以把數(shù)據(jù)庫的壓力轉嫁到Web服務器上，因為如果單臺MySQL，或者 Master/Slave 架構的數(shù)據(jù)庫服務器都負擔比較重，那么就可以考慮把MySQL的運算放到Web服務器上去進行。當然了，如果你Web服務器比數(shù)據(jù)庫服務器差，那就把壓力放在數(shù)據(jù)庫服務器上吧。

如果是把MySQL服務器的壓力放在Web服務器上，那么很多運算就需要我們的程序去執(zhí)行，比如Web程序中全部交給PHP腳本去處理數(shù)據(jù)。單臺MySQL服務器，查詢、更新、插入、刪除都在一臺服務器上的話，訪問量一大，你會明顯發(fā)現(xiàn)鎖表現(xiàn)象，當對一個表進行更新刪除操作的時候，就會拒絕其他操作，這樣就會導致鎖表，解決這個問題最簡單直接的辦法就是拿兩臺MySQL服務器，一臺負責查詢（select）操作，另外一臺負責更改（update/delete/insert），然后進行同步，這樣能夠避免鎖表，如果服務器更多，那么就更好處理了，可以采用分布式數(shù)據(jù)庫架構和數(shù)據(jù)的散列存儲，驚天動地私服下面我們會簡單說一下。

一、SQL的優(yōu)化和注意事項
現(xiàn)在我們假設我們只有一臺MySQL服務器，所有的select/update/insert/delete操作都是在這上面進行的，我們同時有三臺Web服務器，通過DNS輪巡來訪問，那么我們?nèi)绾芜M行我們應用程序和SQL的優(yōu)化。
1. Where條件
在查詢中，WHERE條件也是一個比較重要的因素，盡量少并且是合理的where條件是很重要的，在寫每一個where條件的時候都要仔細考慮，盡量在多個條件的時候，把會提取盡量少數(shù)據(jù)量的條件放在前面，這樣就會減少后一個where條件的查詢時間。
有時候一些where條件會導致索引無效，當使用了Mysql函數(shù)的時候，索引將無效，比如：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. 關聯(lián)查詢和子查詢
數(shù)據(jù)庫一個很重要的特點是關聯(lián)查詢，LEFT JOIN 和全關聯(lián)，特別是多個表進行關聯(lián)，因為每個關聯(lián)表查詢的時候，進行掃描的時候都是一個笛卡爾乘積的數(shù)量級，掃描數(shù)量很大，如果確實是需要進行天龍八部私服關聯(lián)操作，請給where或者on的條件進行索引。
關聯(lián)操作也是可能交給應用去操作的，看數(shù)據(jù)量的大小，如果數(shù)據(jù)量不是非常大，比如10萬條以下，那么就可以交給程序去處理（totododo提出筆誤，特此修正），程序分別提取左右兩個表的數(shù)據(jù)，然后進行循環(huán)的掃描處理，返回結果，這個過程同樣非常耗費Web服務器的資源，那么就需要取決于你愿意把壓力放在Web服務器上或者數(shù)據(jù)庫服務器上了。
子查詢是在mysql5中支持的功能，比如：select * from tbl1 where id in(select id from tbl1)，那樣效率是非常非常低，要盡量避免使用子查詢，要是我，絕對不用真封神私服，呵呵。
3.   一些耗費時間和資源的操作
SQL語句中一些浪費的操作，比如 DISTINCT、COUNT、GROUP BY、各種MySQL函數(shù)。這些操作都是比較耗資源的，我想應用最多的是count字句吧，如果使用count，盡量不要count（*），最好count一個字段，比如count（id），或者count(1)，（據(jù)totododo測試效率其實是一樣的），同樣能夠起到統(tǒng)計的作用。如果不是十分必要，盡量不要使用distinct操作，就是提取唯一值，你完全可以把這個操作交給腳本程序去執(zhí)行提取唯一值，減少MySQL的負擔。group by 操作也是，確實需要分組的話，請謹慎的操作，如果是小批量的數(shù)據(jù)，可以考慮交給腳本程序去做。 #p#page_title#e#
至于MySQL的函數(shù)，估計很多常用，比如有人喜歡把截取字符串也交給MySQL去操作，或者時間轉換操作，使用比較多的函數(shù)像 SUBSTR(), CONCAT(), DATE_FORMAT(), TO_DAYS(), MAX(), MIN(), MD5() 等等，這些操作完全可以交給腳本程序去做，減輕MySQL的負擔。
4. 合理的建立索引
索引的提升速度的一個非常重要的手段，索引在對一些經(jīng)常進行select操作，并且值比較唯一的字段是相當有效的，比如主鍵的id字段，唯一的名字name字段等等。
但是索引對于唯一值比較少的字段，比如性別gender字段，寥寥無幾的類別字段等，意義不大，因為性別是50%的幾率，索引幾乎沒有意義。對于update/delete/insert非常頻繁的表，建立索引要慎重考慮，因為這些頻繁的操作同樣對于索引的維護工作量也是很大的，最后反而得不償失，這個需要自己仔細考慮。索引同樣不是越多越好，適當?shù)乃饕龝鸬胶荜P鍵的作用，不適當?shù)乃饕?，反而減低效率維護，增加維護機戰(zhàn)私服索引的負擔。
5. 監(jiān)控sql執(zhí)行效率
在select語句前面使用EXPLAIN字句能夠查看當前這個select字句的執(zhí)行情況，包括使用了什么操作、返回多少幾率、對索引的使用情況如何等等，能夠有效分析SQL語句的執(zhí)行效率和合理程度。
另外使用MySQL中本身的慢查詢?nèi)罩荆簊low-log，同樣能夠記錄查詢中花費時間比較多的SQL語句，好對相應的語句進行優(yōu)化和改寫。
另外在MySQL終端下，使用show processlist命令能夠有效的查看當前MySQL在進行的線程，包括線程的狀態(tài)，是否鎖表等等，可以實時的查看SQL執(zhí)行情況，同時對一些鎖表操作進行優(yōu)化。

二、數(shù)據(jù)庫服務器的架構和分布想法
對于服務器的架構設計，這個其實是比較重要的，一個合理的設計，能夠讓應用更好的運行。當然，架構的設計，取決于你的應用和你硬件的實際情況。我就簡單的說說幾種不同的數(shù)據(jù)庫架構設計方式，權當是一個個人的想法，希望能夠有幫助。
1. 單臺服務器開多進程和端口
單臺MySQL服務器，如果使用長鏈接等等都無法解決負載太大，連接太多的問題，不凡考慮采用一臺MySQL上使用多個端口開啟多個MySQL守護進程的方法來緩解壓力。當然，前提是你的應用必須支持多端口，并且你的cpu和內(nèi)存足夠運行多個守護進程。
優(yōu)點 是能夠很好的緩解暫時服務器的壓力，把不同的操作放在不同的端口，或者把不同的項目模塊放在不同的端口去操作，良好的分擔單個守護進程的壓力。
缺點 是數(shù)據(jù)可能會產(chǎn)生紊亂，同時可能會導致很多未知的莫名風云私服錯誤。呵呵
2. 使用Master/Slave的服務器結構
Mysql本身具有同步功能，完全可以利用這個功能。構建 Master/Slave 的主從服務器結構，最少只需要兩臺MySQL服務器，我們可以把 Master 服務器用戶更新操作，包括 update/delete/insert，把Slave服務器用于查詢操作，包括 select 操作，然后兩機進行同步。
優(yōu)點 是合理的把更新和查詢的壓力分擔，并且能夠避免鎖表的問題。
缺點 是更新部實時，如果網(wǎng)絡繁忙，可能會存在延遲的問題，并且任何一臺服務器down掉了都很麻煩。
3. 使用分布式的散列存儲
這種結構適合大數(shù)據(jù)量，并且負載比較大，然后服務器比較充足的情況。分布式存儲結構，簡單的可以是多臺服務器，每臺服務器功能是類似的，但是存儲的數(shù)據(jù)不一樣，比如做一個用戶系統(tǒng)，那么把用戶ID在1-10萬以內(nèi)的存儲在A服務器，用戶ID在10-20萬存儲在B服務器，20-3-萬存儲在C服務器，以此類推。如果每個用戶訪問的服務器不足，可以構建組服務器，就是每組用戶擁有多臺服務器，比如可以在某用戶組建立兩臺MySQL服務器，一臺Master，一臺Slave，同樣分離他們的更新和查詢操作，或者可以設計成雙向同步。同時，你的應用程序必須支持跨數(shù)據(jù)庫和跨服務器的操作能力。
優(yōu)點 是服務器的負載合理的被平攤，每臺服務器都是負責一部分用戶，如果一臺服務器down掉了，不會影響其他用戶ID的用戶正常訪問。同時添加節(jié)點比較容易，如果又增加了10萬用戶，那么又可以增加一個節(jié)點服務器，升級很方便。 #p#page_title#e#
缺點 是任何一臺數(shù)據(jù)庫服務器down掉或者數(shù)據(jù)丟失，那么這部分服務器的用戶將很郁悶，數(shù)據(jù)都沒了，當然，這個需要良好的備份機制。

 

現(xiàn)在大概列出如下望各位補充)
1.數(shù)據(jù)庫的設計
盡量把數(shù)據(jù)庫設計的更小的占磁盤空間.
1).盡可能使用更小的整數(shù)類型.(mediumint就比int更合適).
2).盡可能的定義字段為not null,除非這個字段需要null.（這個規(guī)則只適合字段為KEY的情形）
3).如果沒有用到變長字段的話比如varchar,那就采用固定大小的紀錄格式比如char.（CHAR 總是比VARCHR快）
4).表的主索引應該盡可能的短.這樣的話每條紀錄都有名字標志且更高效.
5).只創(chuàng)建確實需要的索引。索引有利于檢索記錄，但是不利于快速保存記錄。如果總是要在表的組合字段上做搜索，那么就在這些字段上創(chuàng)建索引。索引的第一部分必須是最常使用的字段.如果總是需要用到很多字段，首先就應該多復制這些字段，使索引更好的壓縮。
（這條只適合MYISAM引擎的表，對于INNODB則在保存記錄的時候關系不大，因為INNODB是以事務為基礎的，如果想快速保存記錄的話，特別是大批量的導入記錄的時候）
6).所有數(shù)據(jù)都得在保存到數(shù)據(jù)庫前進行處理。
7).所有字段都得有默認值。
8).在某些情況下,把一個頻繁掃描的表分成兩個速度會快好多。在對動態(tài)格式表掃描以取得相關記錄時，它可能使用更小的靜態(tài)格式表的情況下更是如此。
(具體的表現(xiàn)為：MYISAM表的MERGE類型，以及MYISAM和INNODB通用的分區(qū)，詳情見手冊)
9).不會用到外鍵約束的地方盡量不要使用外鍵。
2.系統(tǒng)的用途
1).及時的關閉對MYSQL的連接。
2).explain 復雜的SQL語句。（這樣能確定你的SELECT 語句怎么優(yōu)化最佳）
3).如果兩個關聯(lián)表要做比較話，做比較的字段必須類型和長度都一致.（在數(shù)據(jù)龐大的時候建立INDEX）
4).LIMIT語句盡量要跟order by或者 distinct.這樣可以避免做一次full table scan.
5).如果想要清空表的所有紀錄,建議用truncate table tablename而不是delete from tablename.
不過有一個問題，truncate 不會在事務處理中回滾。因為她要調(diào)用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來導入大量數(shù)據(jù)，這比單純的indert快好多.（在MYSQL中具體表現(xiàn)為：INSERT INTO TABLEQ VALUES (),(),...();）
（還有就是在MYISAM表中插入大量記錄的時候先禁用到KEYS后面再建立KEYS,具體表現(xiàn)語句：
ALTER TABLE TABLE1 DISABLE KEYS;ALTER TABLE TABLE1 ENABLE KEYS;
而對于INNNODB 表在插入前先 set autocommit=0;完了后：set autocommit=1;這樣效率比較高。）
8).經(jīng)常OPTIMIZE TABLE 來整理碎片.
9).還有就是date 類型的數(shù)據(jù)如果頻繁要做比較的話盡量保存在unsigned int 類型比較快。
3.系統(tǒng)的瓶頸
1).磁盤搜索.
并行搜索,把數(shù)據(jù)分開存放到多個磁盤中，這樣能加快搜索時間.
2).磁盤讀寫(IO)
可以從多個媒介中并行的讀取數(shù)據(jù)。
3).CPU周期
數(shù)據(jù)存放在主內(nèi)存中.這樣就得增加CPU的個數(shù)來處理這些數(shù)據(jù)。
4).內(nèi)存帶寬
當CPU要將更多的數(shù)據(jù)存放到CPU的緩存中來的話,內(nèi)存的帶寬就成了瓶頸