MyISAM和InnoDB引擎優(yōu)化分析
更新時間：2012年11月22日 17:16:39 作者：
這幾天在學(xué)習(xí)mysql數(shù)據(jù)庫的優(yōu)化并在自己的服務(wù)器上進行設(shè)置，喻名堂主要學(xué)習(xí)了MyISAM和InnoDB兩種引擎的優(yōu)化方法，需要了解跟多的朋友可以參考下
這幾天喻名堂在學(xué)習(xí)mysql數(shù)據(jù)庫的優(yōu)化并在自己的服務(wù)器上進行設(shè)置，喻名堂主要學(xué)習(xí)了MyISAM和InnoDB兩種引擎的優(yōu)化方法，它們各有優(yōu)缺點，一般在實際應(yīng)用中將兩種引擎結(jié)合起來使用效果會更好。喻名堂測試的硬件配置以及軟件環(huán)境如下：
服務(wù)器型號：IBM S226
CPU：至強四核
內(nèi)存：4G
硬盤：兩個80G做RAID1
系統(tǒng)：windows server 2003 SP1 32位企業(yè)版
Mysql版本：5.5
根據(jù)自己服務(wù)器的實際情況，優(yōu)化過和參數(shù)如下：
一、公共選項
skip-external-locking //避免MySQL的外部鎖定，減少出錯幾率增強穩(wěn)定性。
skip-name-resolve //禁止MySQL對外部連接進行DNS解析，使用這一選項可以消除MySQL進行DNS解析的時間。但需要注意，如果開啟該選項，則所有遠程億恩科技主機連接授權(quán)都要使用IP地址方式，否則MySQL將無法正常處理連接請求！
max_connections = 1024 //指定MySQL允許的最大連接進程數(shù)。如果在訪問論壇時經(jīng)常出現(xiàn)Too Many Connections的錯誤提示，則需要增大該參數(shù)值。
query_cache_size = 16M //默認為0，就是不啟用。指定MySQL查詢緩沖區(qū)的大小?？梢酝ㄟ^在MySQL控制臺執(zhí)行以下命令觀察：
# > SHOW VARIABLES LIKE '%query_cache%';
# > SHOW STATUS LIKE 'Qcache%';
# 如果Qcache_lowmem_prunes的值非常大，則表明經(jīng)常出現(xiàn)緩沖不夠的情況；
如果Qcache_hits的值非常大，則表明查詢緩沖使用非常頻繁，如果該值較小反而會影響效率，那么可以考慮不用查詢緩沖；Qcache_free_blocks，如果該值非常大，則表明緩沖區(qū)中碎片很多。
sort_buffer_size = 6M //每個線程的排序緩存大小，該選項對排序order by、group by起作用。注意：該參數(shù)對應(yīng)的分配內(nèi)存是每連接獨占！如果有100個連接，那么實際分配的總共排序緩沖區(qū)大小為100 × 6 ＝ 600MB。所以，對于內(nèi)存在4GB左右的服務(wù)器推薦設(shè)置為6-8M。
record_buffer=16M //每個進行一個順序掃描的線程為其掃描的每張表分配這個大小的一個緩沖區(qū)，可以設(shè)置為2M以上
table_cache = 512 //為所有線程打開表的數(shù)量。增加該值能增加mysqld要求的文件描述符的數(shù)量。Mysql對每個唯一打開的表需要2個文件描述符。
二、MyISAM選項
key_buffer_size = 256M //key_buffer_size指定用于索引的緩沖區(qū)大小，增加它可得到更好的索引處理性能。對于內(nèi)存在4GB左右的服務(wù)器該參數(shù)可設(shè)置為256M或384M。
注意：該參數(shù)值設(shè)置的過大反而會是服務(wù)器整體效率降低！
read_buffer_size = 4M //讀查詢操作所能使用的緩沖區(qū)大小。和sort_buffer_size一樣，該參數(shù)對應(yīng)的分配內(nèi)存也是每連接獨享！
myisam_sort_buffer_size = 64M //默認為16M。設(shè)置、恢復(fù)、修改表的時候使用的緩沖大小，值不要設(shè)的太大。
join_buffer_size = 8M //聯(lián)合查詢操作所能使用的緩沖區(qū)大小，和sort_buffer_size一樣，該參數(shù)對應(yīng)的分配內(nèi)存也是每連接獨享！
三、InnoDB選項
innodb_buffer_pool_size＝1G //一個Innodb最重要的參數(shù)，這個參數(shù)和MyISAM的key_buffer_size有相似之處，但也是有差別的。這個參數(shù)主要緩存innodb表的索引，數(shù)據(jù)，插入數(shù)據(jù)時的緩沖，設(shè)置得越大，存取表里面數(shù)據(jù)時所需要的磁盤I/O越少，一般是內(nèi)存的一半，不超過2G，否則系統(tǒng)會崩潰。為Innodb加速優(yōu)化首要參數(shù)。該參數(shù)分配內(nèi)存的原則：這個參數(shù)默認分配只有8M，可以說是非常小的一個值。如果是一個專用ＤＢ服務(wù)器，那么他可以占到內(nèi)存的70%-80%。這個參數(shù)不能動態(tài)更改，所以分配需多考慮。分配過大，會使Swap占用過多，致使Mysql的查詢特慢。如果你的數(shù)據(jù)比較小，那么可分配是你的數(shù)據(jù)大?。保埃プ笥易鰹檫@個參數(shù)的值。
例如：數(shù)據(jù)大小為50Ｍ,那么給這個值分配innodb_buffer_pool_size＝64Ｍ
innodb_additional_mem_pool_size=16M //用來存放Innodb的內(nèi)部目錄這個值不用分配太大，系統(tǒng)可以自動調(diào)。不用設(shè)置太高。通常比較大數(shù)據(jù)設(shè)置16Ｍ夠用了，如果表比較多，可以適當(dāng)?shù)脑龃?。如果這個值自動增加，會在error log有中顯示的。
innodb_log_file_size=256M //在日志組中每個日志文件的大小，一般是innodb_buffer_pool_size的25%，官方推薦是innodb_buffer_pool_size的40-50%。一般控制在幾個ＬＯＧ文件相加大小在２Ｇ以內(nèi)為佳。具體情況還需要看你的事務(wù)大小，數(shù)據(jù)大小為依據(jù)。說明：這個值分配的大小和數(shù)據(jù)庫的寫入速度，事務(wù)大小，異常重啟后的恢復(fù)有很大的關(guān)系。
innodb_log_files_in_group=2 //指定你有幾個日志組。分配原則：一般我們可以用２－３個日值組。默認為兩個。
innodb_log_buffer_size＝3M //事務(wù)在內(nèi)存中的緩沖。分配原則：控制在2-8M.這個值不用太多的。他里面的內(nèi)存一般一秒鐘寫到磁盤一次。具體寫入方式和你的事務(wù)提交方式有關(guān)。在oracle等數(shù)據(jù)庫了解這個，一般最大指定為３Ｍ比較合適。
innodb_flush_logs_at_trx_commit＝0 //控制事務(wù)的提交方式分配原則：這個參數(shù)只有３個值，０，１，２請確認一下自已能接受的級別。默認為１，主庫請不要更改了。性能更高的可以設(shè)置為０或是２，但會丟失一秒鐘的事務(wù)。說明：這個參數(shù)的設(shè)置對Ｉｎｎｏｄｂ的性能有很大的影響，所以在這里給多說明一下。當(dāng)這個值為1時：innodb 的事務(wù)LOG在每次提交后寫入日值文件，并對日值做刷新到磁盤。這個可以做到不丟任何一個事務(wù)。當(dāng)這個值為2時：在每個提交，日志緩沖被寫到文件，但不對日志文件做到磁盤操作的刷新,在對日志文件的刷新在值為2的情況也每秒發(fā)生一次。但需要注意的是，由于進程調(diào)用方面的問題，并不能保證每秒１００％的發(fā)生。從而在性能上是最快的。但操作系統(tǒng)崩潰或掉電才會刪除最后一秒的事務(wù)。當(dāng)這個值為0時：日志緩沖每秒一次地被寫到日志文件，并且對日志文件做到磁盤操作的刷新，但是在一個事務(wù)提交不做任何操作。mysqld進程的崩潰會刪除崩潰前最后一秒的事務(wù)。
從以上分析，當(dāng)這個值不為１時，可以取得較好的性能，但遇到異常會有損失，所以需要根據(jù)自已的情況去衡量。
服務(wù)器型號：IBM S226
CPU：至強四核
內(nèi)存：4G
硬盤：兩個80G做RAID1
系統(tǒng)：windows server 2003 SP1 32位企業(yè)版
Mysql版本：5.5
根據(jù)自己服務(wù)器的實際情況，優(yōu)化過和參數(shù)如下：
一、公共選項
skip-external-locking //避免MySQL的外部鎖定，減少出錯幾率增強穩(wěn)定性。
skip-name-resolve //禁止MySQL對外部連接進行DNS解析，使用這一選項可以消除MySQL進行DNS解析的時間。但需要注意，如果開啟該選項，則所有遠程億恩科技主機連接授權(quán)都要使用IP地址方式，否則MySQL將無法正常處理連接請求！
max_connections = 1024 //指定MySQL允許的最大連接進程數(shù)。如果在訪問論壇時經(jīng)常出現(xiàn)Too Many Connections的錯誤提示，則需要增大該參數(shù)值。
query_cache_size = 16M //默認為0，就是不啟用。指定MySQL查詢緩沖區(qū)的大小?？梢酝ㄟ^在MySQL控制臺執(zhí)行以下命令觀察：
# > SHOW VARIABLES LIKE '%query_cache%';
# > SHOW STATUS LIKE 'Qcache%';
# 如果Qcache_lowmem_prunes的值非常大，則表明經(jīng)常出現(xiàn)緩沖不夠的情況；
如果Qcache_hits的值非常大，則表明查詢緩沖使用非常頻繁，如果該值較小反而會影響效率，那么可以考慮不用查詢緩沖；Qcache_free_blocks，如果該值非常大，則表明緩沖區(qū)中碎片很多。
sort_buffer_size = 6M //每個線程的排序緩存大小，該選項對排序order by、group by起作用。注意：該參數(shù)對應(yīng)的分配內(nèi)存是每連接獨占！如果有100個連接，那么實際分配的總共排序緩沖區(qū)大小為100 × 6 ＝ 600MB。所以，對于內(nèi)存在4GB左右的服務(wù)器推薦設(shè)置為6-8M。
record_buffer=16M //每個進行一個順序掃描的線程為其掃描的每張表分配這個大小的一個緩沖區(qū)，可以設(shè)置為2M以上
table_cache = 512 //為所有線程打開表的數(shù)量。增加該值能增加mysqld要求的文件描述符的數(shù)量。Mysql對每個唯一打開的表需要2個文件描述符。
二、MyISAM選項
key_buffer_size = 256M //key_buffer_size指定用于索引的緩沖區(qū)大小，增加它可得到更好的索引處理性能。對于內(nèi)存在4GB左右的服務(wù)器該參數(shù)可設(shè)置為256M或384M。
注意：該參數(shù)值設(shè)置的過大反而會是服務(wù)器整體效率降低！
read_buffer_size = 4M //讀查詢操作所能使用的緩沖區(qū)大小。和sort_buffer_size一樣，該參數(shù)對應(yīng)的分配內(nèi)存也是每連接獨享！
myisam_sort_buffer_size = 64M //默認為16M。設(shè)置、恢復(fù)、修改表的時候使用的緩沖大小，值不要設(shè)的太大。
join_buffer_size = 8M //聯(lián)合查詢操作所能使用的緩沖區(qū)大小，和sort_buffer_size一樣，該參數(shù)對應(yīng)的分配內(nèi)存也是每連接獨享！
三、InnoDB選項
innodb_buffer_pool_size＝1G //一個Innodb最重要的參數(shù)，這個參數(shù)和MyISAM的key_buffer_size有相似之處，但也是有差別的。這個參數(shù)主要緩存innodb表的索引，數(shù)據(jù)，插入數(shù)據(jù)時的緩沖，設(shè)置得越大，存取表里面數(shù)據(jù)時所需要的磁盤I/O越少，一般是內(nèi)存的一半，不超過2G，否則系統(tǒng)會崩潰。為Innodb加速優(yōu)化首要參數(shù)。該參數(shù)分配內(nèi)存的原則：這個參數(shù)默認分配只有8M，可以說是非常小的一個值。如果是一個專用ＤＢ服務(wù)器，那么他可以占到內(nèi)存的70%-80%。這個參數(shù)不能動態(tài)更改，所以分配需多考慮。分配過大，會使Swap占用過多，致使Mysql的查詢特慢。如果你的數(shù)據(jù)比較小，那么可分配是你的數(shù)據(jù)大?。保埃プ笥易鰹檫@個參數(shù)的值。
例如：數(shù)據(jù)大小為50Ｍ,那么給這個值分配innodb_buffer_pool_size＝64Ｍ
innodb_additional_mem_pool_size=16M //用來存放Innodb的內(nèi)部目錄這個值不用分配太大，系統(tǒng)可以自動調(diào)。不用設(shè)置太高。通常比較大數(shù)據(jù)設(shè)置16Ｍ夠用了，如果表比較多，可以適當(dāng)?shù)脑龃?。如果這個值自動增加，會在error log有中顯示的。
innodb_log_file_size=256M //在日志組中每個日志文件的大小，一般是innodb_buffer_pool_size的25%，官方推薦是innodb_buffer_pool_size的40-50%。一般控制在幾個ＬＯＧ文件相加大小在２Ｇ以內(nèi)為佳。具體情況還需要看你的事務(wù)大小，數(shù)據(jù)大小為依據(jù)。說明：這個值分配的大小和數(shù)據(jù)庫的寫入速度，事務(wù)大小，異常重啟后的恢復(fù)有很大的關(guān)系。
innodb_log_files_in_group=2 //指定你有幾個日志組。分配原則：一般我們可以用２－３個日值組。默認為兩個。
innodb_log_buffer_size＝3M //事務(wù)在內(nèi)存中的緩沖。分配原則：控制在2-8M.這個值不用太多的。他里面的內(nèi)存一般一秒鐘寫到磁盤一次。具體寫入方式和你的事務(wù)提交方式有關(guān)。在oracle等數(shù)據(jù)庫了解這個，一般最大指定為３Ｍ比較合適。
innodb_flush_logs_at_trx_commit＝0 //控制事務(wù)的提交方式分配原則：這個參數(shù)只有３個值，０，１，２請確認一下自已能接受的級別。默認為１，主庫請不要更改了。性能更高的可以設(shè)置為０或是２，但會丟失一秒鐘的事務(wù)。說明：這個參數(shù)的設(shè)置對Ｉｎｎｏｄｂ的性能有很大的影響，所以在這里給多說明一下。當(dāng)這個值為1時：innodb 的事務(wù)LOG在每次提交后寫入日值文件，并對日值做刷新到磁盤。這個可以做到不丟任何一個事務(wù)。當(dāng)這個值為2時：在每個提交，日志緩沖被寫到文件，但不對日志文件做到磁盤操作的刷新,在對日志文件的刷新在值為2的情況也每秒發(fā)生一次。但需要注意的是，由于進程調(diào)用方面的問題，并不能保證每秒１００％的發(fā)生。從而在性能上是最快的。但操作系統(tǒng)崩潰或掉電才會刪除最后一秒的事務(wù)。當(dāng)這個值為0時：日志緩沖每秒一次地被寫到日志文件，并且對日志文件做到磁盤操作的刷新，但是在一個事務(wù)提交不做任何操作。mysqld進程的崩潰會刪除崩潰前最后一秒的事務(wù)。
從以上分析，當(dāng)這個值不為１時，可以取得較好的性能，但遇到異常會有損失，所以需要根據(jù)自已的情況去衡量。
相關(guān)文章
Windows 下noinstall方式安裝 mysql 5.7.5 m15 winx64(推薦)
這篇文章主要介紹了Windows 下noinstall方式安裝 mysql-5.7.5-m15-winx64的相關(guān)資料,非常不錯，具有參考借鑒價值，感興趣的朋友一起看看吧2016-09-09
在日常DBA運維過程中，對表結(jié)構(gòu)進行變更算是個普遍的需求了。如果操作的對象是個熱表、大表，難免心里一怵，這些DDL操作是否可以直接執(zhí)行，哪些會影響線上讀寫，哪些會影響主從，甚至導(dǎo)致服務(wù)器壓力驟升，本文做了梳理，希望對大家有所幫助。2021-05-05
這篇文章主要介紹了mysql?8.0.30?降級到?8.0.27,本文分步驟給大家介紹的非常詳細，對大家的學(xué)習(xí)或工作具有一定的參考借鑒價值，需要的朋友可以參考下2022-09-09
這篇文章主要介紹了mysql常用備份命令和shell備份腳本，幫助大家更好的理解和使用MySQL數(shù)據(jù)庫，感興趣的朋友可以了解下2020-09-09
MySQL for update鎖表還是鎖行校驗(過程詳解)
在MySQL中,使用for update子句可以對查詢結(jié)果集進行行級鎖定,以便在事務(wù)中對這些行進行更新或者防止其他事務(wù)對這些行進行修改,這篇文章主要介紹了MySQL for update鎖表還是鎖行校驗,需要的朋友可以參考下2024-02-02
Mysql存儲引擎MyISAM的常見問題（表損壞、無法訪問、磁盤空間不足）
這篇文章主要介紹了Mysql存儲引擎MyISAM的常見問題，針對表損壞、無法訪問、磁盤空間不足等問題進行解決，感興趣的小伙伴們可以參考一下2016-05-05
MySQL GTID（全局事務(wù)標(biāo)識符）與binlog（二進制日志）是搭建主從復(fù)制的兩種機制,GTID為每個事務(wù)分配唯一標(biāo)識,確保復(fù)制無數(shù)據(jù)沖突或丟失,便于復(fù)制配置和管理；binlog記錄所有數(shù)據(jù)庫更改,用于數(shù)據(jù)恢復(fù)和復(fù)制,GTID基于事務(wù)標(biāo)識2024-10-10
這篇文章主要介紹了詳解MySQL拼接函數(shù)CONCAT的使用心得,文中通過示例代碼介紹的非常詳細，對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2020-11-11
本文主要介紹了MySQL運算符!=和＜＞及=和＜=＞的使用區(qū)別，文中通過示例代碼介紹的非常詳細，對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2023-05-05
文章介紹了如何使用Sharding-JDBC對訂單表進行水平分表,并通過配置分片規(guī)則、數(shù)據(jù)操作和測試驗證了分表的實現(xiàn),同時,文章還討論了Sharding-JDBC的多種集成方式和4種分片策略的實現(xiàn)方法,包括標(biāo)準(zhǔn)分片策略、復(fù)合分片策略、行表達式分片策略和Hint分片策略2025-02-02