一. 啟動參數優化
修改 my.cnf (或者my.ini)，加入/修改以下幾行 #設定緩存的連接數,節省連接時的開銷
back_log = 64 #禁用文件系統外部鎖
external-locking = 0 #禁用BDB,如果你確實不需要的話,innodb也是如此
skip-bdb #索引緩沖,如果是專用的數據庫服務器,可以設置高達服務器內存的一半,如果不是專用的, key_buffer = 512M #緩存數據表數量,如果內存較大,可以設置稍微高一點,否則還是設置低一點
#設置這個參數可以參見系統狀態中的 open_tables(表示當前打開的數據表總數)
#和 opened_tables(表示所有打開的數據表總數)
table_cache = 128 #禁用dns解析,如果你的授權信息中采用dns授權方式了,就不能啟用該選項
skip-name-resolve #記錄慢查詢和沒有使用索引的查詢,便於幫助分析問題所在
long_query_time = 1
log-slow-querIEs = /usr/local/MySQL/data/slow.log
log-querIEs-not-using-indexes 其他參數諸如 sort_buffer_size,net_buffer_length,read_buffer_size,read_rnd_buffer_size,myisam_sort_buffer_size,
thread_cache_size,query_cache_size,max_binlog_cache_size 等請查詢MySQL手冊,然後做出合適的調整. 二. 其他小TIPS * 針對Innodb表,盡量不執行 SELECT COUNT(*) 語句,因為Innodb表沒有類似MyISAM那樣的內部計數器來記錄表記錄總量,執行這個操作將會全表掃描,速度很慢.
* 盡量使用MyISAM表,除非必須使用其他類型,因為MyISAM類型的總體讀寫效率是相當高的,缺點是表級鎖,而不是行/頁級鎖.
* 善用 EXPLAIN來幫助你分析查詢優化情況
* 如果需要對一個較大的且並發讀寫較多的數據表做 GROUP BY 等統計操作,建議使用摘要表來存儲統計信息,定期更新統計表,這可能獲得很大的性能改善.
* 查詢時如果有 ORDER BY分句的話,注意讓它的字段順序和索引字段順序對應,這樣能加快排序速度索引 `idx_`(col1, col2, col3),那麼查詢 SELECT .... FROM ... WHERE col1=1 AND col2=2; 使用索引,而查詢 ... WHERE col2=2 AND col3=3; 或 ... WHERE col1=1 AND col3=3; 則不使用索引.
* WHERE 中的條件如果有恆量類型的(如 `fIEld` = 1),就盡量放在前面,這樣能更快的執行過濾.
* 2 個表連接時,連接字段的類型最好一致(包括字段長度),這樣的話索引速度快多了.
* 大部分情況下,字符類型的字段索引值需要一部分,例如 CREATE INDEX char_idx ON tbl1 ( name(10) );
* 盡量使用最合適的數據類型,能使用 ENUM 就不使用 TINYINT ,能使用 SMALLINT 就不使用 MEDIUMINT.這樣能節省存儲空間,增加數據存儲量,提高搜索速度.不要擔心這樣會對省級產生很大的影響,因為加入從 TINYINT 類型改變為 INT 的話,並不會改變原來的數據.
* 如果知道某個表總是頻繁使用的話,可以把它放到 hot_cache 中,用以下方法: SET GLOBAL hot_cache.key_buffer_size=128*1024;
CACHE INDEX `xxx` IN hot_cache; * 把拖沓復雜,速度慢的的查詢分解成多個簡潔明了的查詢,這樣盡管查詢次數多了,但是總體速度和效率卻可能反而更高了,而且也減少了鎖表的可能.
* 執行查詢時,盡量不使用外部函數,因為這樣的話就無法使用可能存在的索引,並且無論如何都會極大地降低效率.如: ... WHERE `create_time` > UNIX_TIMESTAMP(NOW()); 這樣的查詢.可以在程序中把當前的時間取得,然後直接執行構造好了的SQL語句.
* 在索引字段上使用 LIKE 查詢時,左邊不要使用 '%' 修飾符,這樣就可以利用索引,否則無法使用索引.如 ... `name` LIKE 'yejr%';.
* 如果有可能,多使用存儲過程,這大概能獲得 22% 的性能提高. * 定期的在MyISAM表上執行 OPTIMIZE TABLE,這能整理隨便,提高索引效率.
* 如果你主要按 col1，col2，...順序檢索記錄,請在對表大量更改後執行 ALTER TABLE ... ORDER BY col1, col2, ... 語句,這可以獲得更好的性能.
* 對於頻繁更改的MyISAM表,應盡量避免更新所有變長字段(VARCHAR、BLOB和TEXT).
* 對於記錄總數超過500萬的單表,就應該趕緊考慮分表了.分表策略有多種,比如按ID號段,或者按時間切分,等等.
* 創建數據表時盡量指定字段不能為NULL,並且有默認值.
* 使用 LOAD DATA,而不是使用大批量的 INSERT 語句來導入數據.
* 使數據表名和字段名盡可能的短,例如在 user 表中使用字段名 name,而不是 user_name.
* 用 DELAY_KEY_WRITE = 1 選項讓MyISAM更快地更新索引,因為在表關閉之前它們不刷新到硬盤上.缺點是如果服務器如果突然被殺掉了,重啟之後就必須運行 myisamchk 修復索引才行.
* 采用復制機制來分攤讀數據的負載,把寫數據只放在主服務器上,把讀平均分攤到各個從服務器上,能大大提高系統負載. 數據庫性能優化涉及到系統硬件和軟件的方方面面，本文討論的主要是編譯和配置優化、服務器參數調整、如何選用合適的表類型，以及如何用數據庫內建的命令輔助 分析和優化性能，特別是如何用EXPLAIN輔助優化查詢的性能。原文出處：http://www.devshed.com/Server_Side/MySQL/Optimize/ 在apache, PHP, MySQL的體系架構中，MySQL對於性能的影響最大，也是關鍵的核心部分。對於Discuz!論壇程序也是如此，MySQL的設置是否合理優化，直接影響到論壇的速度和承載量！同時，MySQL也是優化難度最大的一個部分，不但需要理解一些MySQL專業知識，同時還需要長時間的觀察統計並且根據經驗進行判斷，然後設置合理的參數。下面我們了解一下MySQL優化的一些基礎，MySQL的優化我分為兩個部分，一是服務器物理硬件的優化；二是MySQL自身(my.cnf)的優化。 (1) 服務器硬件對MySQL性能的影響
a) 磁盤尋道能力（磁盤I/O）,以目前高轉速SCSI硬盤(7200轉/秒)為例，這種硬盤理論上每秒尋道7200次，這是物理特性決定的，沒有辦法改變。 MySQL每秒鐘都在進行大量、復雜的查詢操作，對磁盤的讀寫量可想而知。所以，通常認為磁盤I/O是制約MySQL性能的最大因素之一，對於日均訪問量在100萬PV以上的Discuz!論壇，由於磁盤I/O的制約，MySQL的性能會非常低下！解決這一制約因素可以考慮以下幾種解決方案：
使用 RAID-0+1磁盤陣列，注意不要嘗試使用RAID-5，MySQL在RAID-5磁盤陣列上的效率不會像你期待的那樣快；拋棄傳統的硬盤，使用速度更快的閃存式存儲設備。經過Discuz!公司技術工程的測試，使用閃存式存儲設備可比傳統硬盤速度高出6－10倍左右。
b) CPU 對於MySQL應用，推薦使用S.M.P.架構的多路對稱CPU，例如：可以使用兩顆Intel Xeon 3.6GHz的CPU。
c) 物理內存對於一台使用MySQL的Database Server來說，服務器內存建議不要小於2GB，推薦使用4GB以上的物理內存。 (2) MySQL自身因素當解決了上述服務器硬件制約因素後，讓我們看看MySQL自身的優化是如何操作的。對MySQL自身的優化主要是對其配置文件my.cnf中的各項參數進行優化調整。下面我們介紹一些對性能影響較大的參數。
由於my.cnf文件的優化設置是與服務器硬件配置息息相關的，因而我們指定一個假想的服務器硬件環境：
CPU: 2顆Intel Xeon 2.4GHz
內存: 4GB DDR
硬盤: SCSI 73GB 許多新手往往把重新編譯源代碼看成是一種無可避免的災禍，其實編譯源代碼還能對程序的最終性能起到顯著的影響。編譯過程可以用不同流水線上裝配同樣型號的汽車比擬：第一條流水線由素質較低的工人操作，裝配程序未能盡善盡美，零件裝配誤差較大；第二條流水線由高素質的技術工人操作，汽車裝配程序合理，且利用最好的工具保證產品的高質量。雖然兩條流水線上裝配出來的汽車外觀一模一樣，但兩種汽車的性能表現卻可能大不相同。對於編譯器來說情況也完全相似，有些編譯器裝配出來的程序要比其他編譯器的更好。編譯時考慮所有可用的選項也是極其重要的。很可能某些編譯器的默認選項值不能符合要求，或者，為了滿足應用的特定需求，我們需要指定一些特殊的編譯選項。正如MySQL文檔所指出的，只要采用了更好的編譯器或者使用更合理的編譯選項，應用性能的提高程度可以達到10-30%。既然如此，編譯時具體應該注意哪些問題才能讓MySQL數據庫運行得更快呢？ ▲ 使用pgcc編譯器
如果系統使用的是奔騰處理器，那麼pgcc（Pentium GCC）正是為這些系統下運行的程序提供的專用編譯器。pgcc是gcc編譯器（http://www.gnu.org/software/gcc/）的奔騰優化版，用pgcc編譯MySQL代碼可以讓整體性能提高10%以上！關於pgcc的更多信息，請參見http: //www.goof.com/pcg/。當然，如果系統使用的不是奔騰處理器，采用這種方法提高MySQL的運行速度就不合適了，因為正如其名字所示， pgcc是專門為奔騰系統提供的。 ▲ 把MySQLd編譯成靜態模式
以不帶共享庫的形式編譯mysqld同樣可以提高性能。在配置行加入下面這個選項可以將MySQLd編譯成靜態模式：
% >./configure -with-MySQLd-ldflags=-all-static [--其他配置選項] ▲ 配置示例
下面的配置命令經常用於提高MySQL的性能：
% >CFLAGS="-O6 -mpentiumpro -fomit-frame-pointer" CXX=gcc CXXFLAGS="-O6
-mpentiumpro -fomit-frame-pointer -felide-constructors -fno-exceptions -fno-rtti"
./configure --prefix=/usr/local --enable-assembler --with-MySQLd-ldflags=-all-static
--disable-shared 詳細解釋每個gcc選項的作用已經超出了本文的范圍，請訪問gcc的說明文檔了解這些信息（http://gcc.gnu.org/）。注意不要拘泥於這個例子，請在命令行執行man gcc仔細了解每一個gcc選項的含義。正確的編譯方法固然重要，但它只是提高MySQL服務器性能工作的一部分。MySQL服務器的許多參數會影響服務器的性能表現，而且我們可以把這些參數保存到配置文件，使得每次MySQL服務器啟動時這些參數都自動發揮作用。這個配置文件就是my.cnf。 MySQL 服務器提供了my.cnf文件的幾個示例，它們可以在/usr/local/mysql/share/mysql/目錄下找到，名字分別為my -small.cnf、my-medium.cnf、my-large.cnf以及my-huge.cnf。文件名字中關於規模的說明描述了該配置文件適用的系統類型。例如，如果運行MySQL服務器的系統內存不多，而且MySQL只是偶爾使用，那麼使用my-small.cnf配置文件最為理想，這個配置文件告訴mysqld daemon使用最少的系統資源。反之，如果MySQL服務器用於支持一個大規模的在線商場，系統擁有2G的內存，那麼使用MySQL-huge.cnf 最為合適。要使用上述示例配置文件，我們應該先復制一個最適合要求的配置文件，並把它命名為my.cnf。這個復制得到的配置文件可以按照如下三種方式使用：
全局：把這個my.cnf文件復制到服務器的/etc目錄，此時文件中所定義的參數將全局有效，即對該服務器上運行的所有MySQL數據庫服務器都有效。
局部：把這個my.cnf文件復制到[MYSQL-INSTALL-DIR]/var/將使該文件只對指定的服務器有效，其中[MYSQL-INSTALL-DIR]表示安裝MySQL的目錄。
用戶：最後，我們還可以把該文件的作用范圍局限到指定的用戶，這只需把my.cnf文件復制到用戶的根目錄即可。那麼，如何設置my.cnf文件中的參數呢？或者進一步說，哪些參數是我們可以設置的呢？所有這些參數都對MySQL服務器有著全局性的影響，但同時每一個參數都和MySQL的特定部分關系較為密切。例如，max_connections參數屬於MySQLd一類。那麼，如何才能得知這一點呢？這只需執行如下命令：
% >/usr/local/mysql/libexec/mysqld --help 該命令將顯示出和mysqld有關的各種選項和參數。要尋找這些參數非常方便，因為這些參數都在“Possible variables for option --set-variable (-O) are”這行內容的後面。找到這些參數之後，我們就可以在my.cnf文件中按照如下方式設置所有這些參數： set-variable = max_connections=100 這行代碼的效果是：同時連接MySQL服務器的最大連接數量限制為100。不要忘了在my.cnf文件[mysqld]小節加上一個set-variable指令，具體請參見配置文件中的示例。數據庫的常規優化 MySQL 本身的配置文件 my.cnf（或 my.ini）中的相關參數，對整個數據庫系統來說尤為重要。針對不同的服務器內存容量，MySQL 程序包中提供了 my-small.cnf、my-medium.cnf、my-large.cnf、my-huge.cnf 四個分別適用於服務器內存不低於 64M、256M、512M、1G 情況下的參數設置，您可以根據自身機器的實際情況，數據庫應用所占比重，在上述四個文件中提供的參數基礎上對配置文件進行修改。Unix 類系統用戶，建議將配置文件命名為 my.cnf 放置於 /etc 中；Windows 系統用戶直接在 Winmysqladmin.exe 中對 my.ini 進行修改即可。 除了以上默認的配置文件提供的參數以外，通常情況您還需要在 [MySQLd] 後修改或增加以下的參數以適應大部分 web 應用程序的需要：
* 最大連接數為 600，以滿足一般應用對連接數的需要：增加 max_connections = 600。根據我們的經驗，500～1000 是較為合適的數值，沒必要將其設置為超過 1000，那只會造成對資源的浪費。
* 不使用 innodb 和 bdb：增加兩行內容，分別是 skip-innodb 和 skip-bdb。Discuz! 和大部分 web 應用程序不需要使用此兩項功能，因此將其關閉以節約內存和磁盤空間，提高效率。
* 連接超時時間 5，避免空閒進程過多的內存占用：增加 wait_timeout = 5。通過減少超時時間，使得使用 pconnect（長期連接）的用戶在利用其不需反復驗證用戶名和密碼的同時，避免打開過多的空閒進程，減少內存消耗。
* 禁止端口連接：增加 skip-networking。如果使用 Unix 類操作系統，數據庫和 httpd 在同一台服務器，且不需要遠程讀取數據庫，可增設此項參數，關閉默認的 3306 端口，有效提禁止外部網絡未經授權的訪問，避免端口被用以進行 DDOS 攻擊。Windows 系統不需要（不能）增加這個參數。
其他參數的配置在此不詳述，如果您對服務器及 MySQL 數據庫有相當的了解，可以通過數據庫的日常運行情況，有針對性的進行修改。數據表優化（Optimize Table）當數據庫中刪除了大量的數據後，可能會發現數據文件尺寸並沒有減小。這是因為刪除操作後在數據文件中留下碎片所致。Discuz! 在系統數設置界面提供了數據表優化的功能，可以去除刪除操作後留下的數據文件碎片，減小文件尺寸，加快未來的讀寫操作。您只要在做完批量刪除，或定期（如每一兩個月）進行一次數據表優化操作即可。
