MySQL的InnoDB配置及優(yōu)化
MyISAM和InnoDB是在使用MySQL最常用的兩個(gè)表類型，各有優(yōu)缺點(diǎn)，視具體應(yīng)用而定?；镜牟顒e為：
MyISAM類型不支持事務(wù)處理等高級(jí)處理，而InnoDB類型支持。
MyISAM類型的表強(qiáng)調(diào)的是性能，其執(zhí)行數(shù)度比InnoDB類型更快，但是不提供事務(wù)支持，而InnoDB提供事務(wù)支持已經(jīng)外部鍵等高級(jí)數(shù)據(jù)庫功能。
MyIASM是IASM表的新版本，有如下擴(kuò)展：
1、二進(jìn)制層次的可移植性。
2、NULL列索引。
3、對(duì)變長(zhǎng)行比ISAM表有更少的碎片。
4、支持大文件。
5、更好的索引壓縮。
6、更好的鍵碼統(tǒng)計(jì)分布。
7、更好和更快的auto_increment處理。
InnoDB 是 MySQL 第一個(gè)提供外鍵約束的引擎，除了提供事務(wù)處理外，InnoDB 還支持行鎖，提供和 Oracle 一樣的一致性的不加鎖讀取，能增加并發(fā)讀的用戶數(shù)量并提高性能，不會(huì)增加鎖的數(shù)量。
InnoDB 的設(shè)計(jì)目標(biāo)是處理大容量數(shù)據(jù)時(shí)最大化性能，它的 CPU 利用率是其他所有基于磁盤的關(guān)系數(shù)據(jù)庫引擎中最有效率的。
InnoDB 是一套放在 MySQL 后臺(tái)的完整數(shù)據(jù)庫系統(tǒng)，InnoDB 有它自己的緩沖池，能緩沖數(shù)據(jù)和索引，InnoDB 還把數(shù)據(jù)和索引存放在表空間里面，可能包含好幾個(gè)文件，這和 MyISAM 表完全不同，在 MyISAM 中，表被存放在單獨(dú)的文件中，InnoDB 表的大小只受限于操作系統(tǒng)文件的大小，一般為 2GB。
以下是一些細(xì)節(jié)和具體實(shí)現(xiàn)的差別：
1、InnoDB不支持FULLTEXT類型的索引。
2、InnoDB 中不保存表的具體行數(shù)，也就是說，執(zhí)行select count(*) from table時(shí)，InnoDB要掃描一遍整個(gè)表來計(jì)算有多少行，但是MyISAM只要簡(jiǎn)單的讀出保存好的行數(shù)即可。注意的是，當(dāng)count(*)語句包含 where條件時(shí)，兩種表的操作是一樣的。
3、對(duì)于AUTO_INCREMENT類型的字段，InnoDB中必須包含只有該字段的索引，但是在MyISAM表中，可以和其他字段一起建立聯(lián)合索引。
4、DELETE FROM table時(shí)，InnoDB不會(huì)重新建立表，而是一行一行的刪除。
5、LOAD TABLE FROM MASTER操作對(duì)InnoDB是不起作用的，解決方法是首先把InnoDB表改成MyISAM表，導(dǎo)入數(shù)據(jù)后再改成InnoDB表，但是對(duì)于使用的額外的InnoDB特性(例如外鍵)的表不適用。
6、InnoDB表的行鎖也不是絕對(duì)的，如果在執(zhí)行一個(gè)SQL語句時(shí)MySQL不能確定要掃描的范圍，InnoDB表同樣會(huì)鎖全表，例如update table set num=1 where name like “%aaa%”
MyISAM和InnoDB優(yōu)化：
key_buffer_size - 這對(duì)MyISAM表來說非常重要。如果只是使用MyISAM表，可以把它設(shè)置為可用內(nèi)存的 30-40%。合理的值取決于索引大小、數(shù)據(jù)量以及負(fù)載 -- 記住，MyISAM表會(huì)使用操作系統(tǒng)的緩存來緩存數(shù)據(jù)，因此需要留出部分內(nèi)存給它們，很多情況下數(shù)據(jù)比索引大多了。盡管如此，需要總是檢查是否所有的 key_buffer 都被利用了 -- .MYI 文件只有 1GB，而 key_buffer 卻設(shè)置為 4GB 的情況是非常少的。這么做太浪費(fèi)了。如果你很少使用MyISAM表，那么也保留低于 16-32MB 的 key_buffer_size 以適應(yīng)給予磁盤的臨時(shí)表索引所需。
一、內(nèi)存利用方面：
1、innodb_buffer_pool_size
為Innodb加速優(yōu)化首要參數(shù)，這對(duì)Innodb表來說非常重要。Innodb相比MyISAM表對(duì)緩沖更為敏感。MyISAM可以在默認(rèn)的 key_buffer_size 設(shè)置下運(yùn)行的可以，然而Innodb在默認(rèn)的 innodb_buffer_pool_size 設(shè)置下卻跟蝸牛似的。由于Innodb把數(shù)據(jù)和索引都緩存起來，無需留給操作系統(tǒng)太多的內(nèi)存，因此如果只需要用Innodb的話則可以設(shè)置它高達(dá) 70-80% 的可用內(nèi)存。一些應(yīng)用于 key_buffer 的規(guī)則有 -- 如果你的數(shù)據(jù)量不大，并且不會(huì)暴增，那么無需把innodb_additional_pool_size - 這個(gè)選項(xiàng)對(duì)性能影響并不太多，至少在有差不多足夠內(nèi)存可分配的操作系統(tǒng)上是這樣。不過如果你仍然想設(shè)置為 20MB(或者更大)，因此就需要看一下Innodb其他需要分配的內(nèi)存有多少。
該參數(shù)分配內(nèi)存的原則：這個(gè)參數(shù)默認(rèn)分配只有8M，可以說是非常小的一個(gè)值。如果是一個(gè)專用DB服務(wù)器，那么他可以占到內(nèi)存的70%-80%。這個(gè)參數(shù)不能動(dòng)態(tài)更改，所以分配需多考慮。分配過大，會(huì)使Swap占用過多，致使Mysql的查詢特慢。如果你的數(shù)據(jù)比較小，那么可分配是你的數(shù)據(jù)大小+10%左右做為這個(gè)參數(shù)的值。例如：數(shù)據(jù)大小為50M,那么給這個(gè)值分配innodb_buffer_pool_size=64M。
設(shè)置方法：innodb_buffer_pool_size=4G
這個(gè)參數(shù)分配值的使用情況可以根據(jù)show innodb status\G;中的
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 4668764894;
去確認(rèn)使用情況。
2、innodb_additional_mem_pool
作用：用來存放Innodb的內(nèi)部目錄
這個(gè)值不用分配太大，系統(tǒng)可以自動(dòng)調(diào)。不用設(shè)置太高。通常比較大數(shù)據(jù)設(shè)置16M夠用了，如果表比較多，可以適當(dāng)?shù)脑龃?。如果這個(gè)值自動(dòng)增加，會(huì)在error log有中顯示的。
分配原則：
用show innodb status\G;去查看運(yùn)行中的DB是什么狀態(tài)(參考BUFFER POOL AND MEMORY段中)，然后可以調(diào)整到適當(dāng)?shù)闹怠?/div>
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 4668764894; in additional pool allocated 16777216
參考：in additional pool allocated 16777216
二、日誌控制方面：
1、innodb_log_file_size
在日志組中每個(gè)日志文件的大小.，在高寫入負(fù)載尤其是大數(shù)據(jù)集的情況下很重要。這個(gè)值越大則性能相對(duì)越高，但是要注意到可能會(huì)增加恢復(fù)時(shí)間。我經(jīng)常設(shè)置為 64-512MB，跟據(jù)服務(wù)器大小而異。你應(yīng)該設(shè)置日志文件總合大小到你緩沖池大小的25%~100%。
注意：停掉數(shù)據(jù)庫然后刪除數(shù)據(jù)庫目錄/var/lib/mysql下的ib_logfile0和ib_loggile1 或者執(zhí)行flush logs指令后才能設(shè)置innodb_log_file_size否則數(shù)據(jù)庫無法啟動(dòng)。
2、innodb_log_buffer_size
用來緩沖日志數(shù)據(jù)的緩沖區(qū)的大小，默認(rèn)的設(shè)置在中等強(qiáng)度寫入負(fù)載以及較短事務(wù)的情況下，服務(wù)器性能還可以。如果存在更新操作峰值或者負(fù)載較大，就應(yīng)該考慮加大它的值了。如果它的值設(shè)置太高了，可能會(huì)浪費(fèi)內(nèi)存 -- 它每秒都會(huì)刷新一次，因此無需設(shè)置超過1秒所需的內(nèi)存空間。通常 8-16MB 就足夠了。越小的系統(tǒng)它的值越小。
3、innodb_log_files_in_group
在日志組中的文件總數(shù)。
4、innodb_log_group_home_dir
默認(rèn)值為 innodb_log_group_home_dir = ./ 用來存放InnoDB日志文件的目錄路徑(如 ib_logfile0、ib_logfile1等)。在默認(rèn)的情況下，InnoDB驅(qū)動(dòng)程序?qū)⑹褂肕ySQL數(shù)據(jù)目錄作為自己保存日志文件的位置。
三、文件IO分配，空間占用方面
1、innodb_data_home_dir
默認(rèn)值為 innodb_data_home_dir = ，空值。InnoDB主目錄，所有與InnoDB數(shù)據(jù)表有關(guān)的目錄或文件路徑都相對(duì)于這個(gè)路徑。在默認(rèn)的情況下，這個(gè)主目錄就是MySQL的數(shù)據(jù)目錄，為空值時(shí)一般就是/var/lib/mysql目錄。
2、innodb_data_file_path
默認(rèn)值為 innodb_data_file_path = ibdata1:10M:autoextend 用來容納InnoDB數(shù)據(jù)表的表空間，這個(gè)值中ibdata1表示數(shù)據(jù)文件名，10M表示初始大小為10M，autoextend表示大小可擴(kuò)展。
3、innodb_file_per_table
為數(shù)據(jù)庫中的每個(gè)表使用一組數(shù)據(jù)文件，配置了innodb_file_per_table 參數(shù)后，每個(gè)創(chuàng)建的innodb表 都會(huì)生成2個(gè)數(shù)據(jù)文件，一個(gè)是擴(kuò)展名為frm保存表定義，一個(gè)是ibd擴(kuò)展名保存表數(shù)據(jù)。
四、各種緩存相關(guān)參數(shù)
1、table_cache
打開一個(gè)表的開銷可能很大。例如MyISAM把MYI文件頭標(biāo)志該表正在使用中。你肯定不希望這種操作太頻繁，所以通常要加大緩存數(shù)量，使得足以最大限度地緩存打開的表。它需要用到操作系統(tǒng)的資源以及內(nèi)存，對(duì)當(dāng)前的硬件配置來說當(dāng)然不是什么問題了。如果你有200多個(gè)表的話，那么設(shè)置為 1024 也許比較合適(每個(gè)線程都需要打開表)，如果連接數(shù)比較大那么就加大它的值。我曾經(jīng)見過設(shè)置為 100,000 的情況。
2、thread_cache
線程的創(chuàng)建和銷毀的開銷可能很大，因?yàn)槊總€(gè)線程的連接/斷開都需要。我通常至少設(shè)置為 16。如果應(yīng)用程序中有大量的跳躍并發(fā)連接并且 Threads_Created 的值也比較大，那么我就會(huì)加大它的值。它的目的是在通常的操作中無需創(chuàng)建新線程。
3、query_cache
如果你的應(yīng)用程序有大量讀，而且沒有應(yīng)用程序級(jí)別的緩存，那么這很有用。不要把它設(shè)置太大了，因?yàn)橄胍S護(hù)它也需要不少開銷，這會(huì)導(dǎo)致MySQL變慢。通常設(shè)置為 32-512Mb。設(shè)置完之后最好是跟蹤一段時(shí)間，查看是否運(yùn)行良好。在一定的負(fù)載壓力下，如果緩存命中率太低了，就啟用它。
4、sort_buffer_size
如果你只有一些簡(jiǎn)單的查詢，那么就無需增加它的值了，盡管你有 64GB 的內(nèi)存。搞不好也許會(huì)降低性能。
其它相關(guān)參數(shù)1、innodb_flush_logs_at_trx_commit
是否為Innodb比MyISAM慢1000倍而頭大?看來也許你忘了修改這個(gè)參數(shù)了。默認(rèn)值是 1，這意味著每次提交的更新事務(wù)(或者每個(gè)事務(wù)之外的語句)都會(huì)刷新到磁盤中，而這相當(dāng)耗費(fèi)資源，尤其是沒有電池備用緩存時(shí)。很多應(yīng)用程序，尤其是從 MyISAM轉(zhuǎn)變過來的那些，把它的值設(shè)置為 2 就可以了，也就是不把日志刷新到磁盤上，而只刷新到操作系統(tǒng)的緩存上。日志仍然會(huì)每秒刷新到磁盤中去，因此通常不會(huì)丟失每秒1-2次更新的消耗。如果設(shè)置為 0 就快很多了，不過也相對(duì)不安全了 -- MySQL服務(wù)器崩潰時(shí)就會(huì)丟失一些事務(wù)。設(shè)置為 2 只會(huì)丟失刷新到操作系統(tǒng)緩存的那部分事務(wù)。
2、innodb_lock_wait_timeout
MySQL可以自動(dòng)地監(jiān)測(cè)行鎖導(dǎo)致的死鎖并進(jìn)行相應(yīng)的處理，但是對(duì)于表鎖導(dǎo)致的死鎖不能自動(dòng)的監(jiān)測(cè)，所以該參數(shù)主要被用于在出現(xiàn)類似情況的時(shí)候等待指定的時(shí)間后回滾。系統(tǒng)默認(rèn)值是50秒，用戶可以根據(jù)應(yīng)用的需要進(jìn)行調(diào)整。
3、innodb_support_xa參數(shù)說明 innodb_support_xa參數(shù)設(shè)置是否支持分布式事務(wù)，默認(rèn)值是ON或者1，表示支持分布式事務(wù)。如果確認(rèn)應(yīng)用中不需要使用分布式事務(wù)，則可以關(guān)閉這個(gè)參數(shù)，減少磁盤刷新的次數(shù)并獲得更好的InnoDB性能。
4、table_locks_waited和table_locks_immediate
可以通過檢查table_locks_waited和table_locks_immediate狀態(tài)變量來分析系統(tǒng)上的表鎖定爭(zhēng)奪：
mysql> show status like 'Table%';
+----------------------------+----------+
| Variable_name | Value |
+----------------------------+----------+
| Table_locks_immediate | 105 |
| Table_locks_waited | 3 |
+----------------------------+----------+
2 rows in set (0.00 sec)
如果Table_locks_waited的值比較高，則說明存在著較嚴(yán)重的表級(jí)鎖爭(zhēng)用情況。這時(shí)，需要我們對(duì)應(yīng)用做進(jìn)一步的檢查，來確定問題所在。
本站僅提供存儲(chǔ)服務(wù)，所有內(nèi)容均由用戶發(fā)布，如發(fā)現(xiàn)有害或侵權(quán)內(nèi)容，請(qǐng)
點(diǎn)擊舉報(bào)。
