MySQL效能最佳化TIPS

MySQL效能最佳化TIPS

作者：葉金榮（Email:），來源：http://imysql.cn，轉載請註明譯者和出處，並且不能用於商業用途，違者必究。

一. 啟動參數最佳化
修改 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 修複索引才行.
• 採用複製機制來分攤讀資料的負載,把寫資料只放在主伺服器上,把讀平均分攤到各個從伺服器上,能大大提高系統負載.