Mysql MyISAM InnoDB 優(yōu)化

huaihe0410

MYSQL 配置原則

配置合理的MySQL
服務(wù)器，盡量在應(yīng)用本身達(dá)到一個
MySQL最合理的使用針對MyISAM或InnoDB
不同引擎進(jìn)行不同定制性配置
針對不同的應(yīng)用情況進(jìn)行合理配置針對my.cnf
進(jìn)行配置，后面設(shè)置是針對內(nèi)存為2G的服務(wù)器進(jìn)行的合理設(shè)置


公共選項(xiàng)




MyISAM選項(xiàng)



InnoDB選項(xiàng)



MyISAM和InnoDB存儲引擎性能





MyISAM和InnoDB
存儲引擎性能差別并不是很大，針對InnoDB來說，影響性能的主要是
innodb_flush_log_at_trx_commit這個選項(xiàng)，如果設(shè)置為1
的話，那么每次插入數(shù)據(jù)的時候都會自動提交，導(dǎo)致性能急劇下降，應(yīng)該是跟刷新日志有
關(guān)系，設(shè)置為0效率能夠看到明顯提升，當(dāng)然，同樣你可以SQL
中提交“SET AUTOCOMMIT = 0”來設(shè)置達(dá)到好的性能。
同時也可以看出值得使用InnoDB來替代myISAM引擎來進(jìn)行開發(fā)，畢竟
InnoDB有多數(shù)據(jù)庫特性、更良好的數(shù)據(jù)存儲性能和查詢性能

MySQL技巧分享














------------------------------------------------------------

存儲引擎- - My I SAM, I n n oDB ，其他
MyISAM 相對更適合插入不多不頻繁，查詢較多的應(yīng)用環(huán)
境：不支持事務(wù)、只能是鎖全表、不支持外鍵、不支持
WAL(write ahead logging)
 InnoDB適合大并發(fā)寫入和查詢的環(huán)境：支持事務(wù)（ACID
兼容）、行鎖、外鍵、具備自己的內(nèi)存緩沖池、獨(dú)立的表
空間（不受大文件限制）
 Merge其實(shí)就是MRG_MyISAM，適用于將相同類型的多
種子表合并成一個大表，便于操作，但是無法完全利用到
索引的好處
 ARCHIVE存儲引擎被用來以非常小的覆蓋區(qū)存儲大量無
索引數(shù)據(jù)。 是只讀類型。

MyISAM 優(yōu)化
key_buffer_size 分配給MyISAM索引緩存的內(nèi)存總數(shù)
● query_cache_size 控制分配給查詢緩存的內(nèi)存總量
● long_query_time 設(shè)定慢查詢時間
● external-locking 禁止使用外部鎖，可預(yù)防死鎖
● back_log 臨時停止響應(yīng)新請求前在短時間內(nèi)可以堆起多少請求。如
果你需要在短時間內(nèi)允許大量連接，可以增加該數(shù)值
● table_cache 緩存數(shù)據(jù)表的數(shù)量，避免重復(fù)打開表的開銷
● thread_cache_size 緩存可重用線程數(shù)，見笑創(chuàng)建新線程的開銷
● sort/join/read buffer size 分配給每個線程中處理排序/掃描表連
接及索引的內(nèi)存
● skip-bdb 等，禁用不必要的引擎

InnoDB 優(yōu)化

● 如果數(shù)據(jù)庫CPU使用率小于70%，則MySQL的壓力可能在于磁盤
因素，可能有太多的事務(wù)和提交，或者緩沖池太小�？梢粤砭彌_池更
大一些，但不要設(shè)置緩沖池等于或超過物理內(nèi)存的80%
● 把多個修改(INSERT/UPDATE/DELETE)放在一個事務(wù)里。但是也要
注意有此產(chǎn)生的效率問題
● innodb_flush_log_at_trx_commit 設(shè)置為0(每秒刷新)，1(實(shí)時
刷新)，2(只寫日志文件，不刷新到磁盤)
● 使用較大日志文件及較大日志緩沖
● 往innoDB表導(dǎo)入數(shù)據(jù)時，先關(guān)閉autocommit模式，否則會實(shí)時
刷新數(shù)據(jù)到磁盤
● 更多的請查看：http://imysql.cn/?q=node/116
innodb_buffer_pool_size 控制分配給包括集群數(shù)據(jù)以及
次要索引頁的Innodb緩存的內(nèi)存總數(shù)，默認(rèn)16MB
● innodb_additional_mem_pool_size 控制分配給對
Innodb內(nèi)部數(shù)據(jù)字典進(jìn)行排序所需的緩沖，默認(rèn)1MB
● innodb_log_buffer_size 控制分配給對Innodb存儲提前
寫日志記錄所需的緩沖，默認(rèn)1MB
● innodb_log_files_in_group 在日志組里日志文件的數(shù)
目。InnoDB以循環(huán)方式寫進(jìn)文件。默認(rèn)是2（推薦）
● innodb_log_file_size 默認(rèn)是5MB。建議值從1MB到N
分之一緩沖池大小，其中N是組里日志文件的數(shù)目

其他優(yōu)化 - -
char 型 vs int 型
● 獨(dú)立索引 vs 聯(lián)合索引
● 記錄 slow queries
● 使用存儲過程、觸發(fā)器、視圖
● 定期執(zhí)行optimize / analyze table
● 針對Innodb表,盡量不執(zhí)行 SELECT COUNT(*) 語句
● 善用 EXPLAIN來幫助你分析查詢優(yōu)化情況
● 如果需要對一個較大的且并發(fā)讀寫較多的數(shù)據(jù)表做 GROUP BY 等統(tǒng)
計操作,建議使用摘要表來存儲統(tǒng)計信息,定期更新統(tǒng)計表
表連接時,連接字段的類型最好一致(包括字段長度),這樣的話索引
速度快多了
● 大部分情況下,字符類型的字段索引值需要一部分
● 盡量使用最合適的數(shù)據(jù)類型，不要浪費(fèi)空間和效率
● 執(zhí)行查詢時,盡量不使用外部函數(shù),因?yàn)檫@樣的話就無法使用可能存
在的索引
● 把拖沓復(fù)雜,速度慢的的查詢分解成多個簡潔明了的查詢
● 在索引字段上使用 LIKE 查詢時,左邊不要使用 '%' 修飾符,這樣就
可以利用索引,否則無法使用索引.如 ... `name` LIKE 'yejr%'
● 對于頻繁更改的MyISAM表,應(yīng)盡量避免更新所有變長字段
(VARCHAR、BLOB和TEXT)
● 分?jǐn)倝毫�，使用集�?復(fù)制
● 查詢時如果有 ORDER BY分句