MySQL發(fā)展到今天，InnoDB引擎已經(jīng)作為絕對(duì)的主力，除了像大數(shù)據(jù)量分析等比較特殊領(lǐng)域需求外，它適用于眾多場(chǎng)景。然而，仍有不少開(kāi)發(fā)者還在“執(zhí)迷不悟”的使用MyISAM引擎，覺(jué)得對(duì)InnoDB無(wú)法把握好，還是MyISAM簡(jiǎn)單省事，還能支持快速COUNT(*)。本文是由于最近幾天幫忙處理discuz論壇有感而發(fā)，希望能對(duì)廣大開(kāi)發(fā)者有幫助。

1. 快速認(rèn)識(shí)InnoDB
InnoDB是MySQL下使用最廣泛的引擎，它是基于MySQL的高可擴(kuò)展性和高性能存儲(chǔ)引擎，從5.5版本開(kāi)始，它已經(jīng)成為了默認(rèn)引擎。
InnODB引擎支持眾多特性：

a) 支持ACID，簡(jiǎn)單地說(shuō)就是支持事務(wù)完整性、一致性；
b) 支持行鎖，以及類(lèi)似ORACLE的一致性讀，多用戶(hù)并發(fā)；
c) 獨(dú)有的聚集索引主鍵設(shè)計(jì)方式，可大幅提升并發(fā)讀寫(xiě)性能；
d) 支持外鍵；
e) 支持崩潰數(shù)據(jù)自修復(fù)；
InnoDB有這么多特性，比MyISAM來(lái)的優(yōu)秀多了，還猶豫什么，果斷的切換到InnoDB引擎吧 :)

2. 修改InnoDB配置選項(xiàng)
可以選擇官方版本，或者Percona的分支，如果不知道在哪下載，就google吧。
安裝完MySQL后，需要適當(dāng)修改下my.cnf配置文件，針對(duì)InnoDB相關(guān)的選項(xiàng)做一些調(diào)整，才能較好的運(yùn)行InnoDB。
相關(guān)的選項(xiàng)有：

#InnoDB存儲(chǔ)數(shù)據(jù)字典、內(nèi)部數(shù)據(jù)結(jié)構(gòu)的緩沖池，16MB 已經(jīng)足夠大了。
innodb_additional_mem_pool_size = 16M

#InnoDB用于緩存數(shù)據(jù)、索引、鎖、插入緩沖、數(shù)據(jù)字典等
#如果是專(zhuān)用的DB服務(wù)器，且以InnoDB引擎為主的場(chǎng)景，通?？稍O(shè)置物理內(nèi)存的50%
#如果是非專(zhuān)用DB服務(wù)器，可以先嘗試設(shè)置成內(nèi)存的1/4，如果有問(wèn)題再調(diào)整
#默認(rèn)值是8M，非常坑X(jué)，這也是導(dǎo)致很多人覺(jué)得InnoDB不如MyISAM好用的緣故
innodb_buffer_pool_size = 4G

#InnoDB共享表空間初始化大小，默認(rèn)是 10MB，也非常坑X(jué)，改成 1GB，并且自動(dòng)擴(kuò)展
innodb_data_file_path = ibdata1:1G:autoextend

#如果不了解本選項(xiàng)，建議設(shè)置為1，能較好保護(hù)數(shù)據(jù)可靠性，對(duì)性能有一定影響，但可控
innodb_flush_log_at_trx_commit = 1

#InnoDB的log buffer，通常設(shè)置為 64MB 就足夠了
innodb_log_buffer_size = 64M

#InnoDB redo log大小，通常設(shè)置256MB 就足夠了
innodb_log_file_size = 256M

#InnoDB redo log文件組，通常設(shè)置為 2 就足夠了
innodb_log_files_in_group = 2

#啟用InnoDB的獨(dú)立表空間模式，便于管理
innodb_file_per_table = 1

#啟用InnoDB的status file，便于管理員查看以及監(jiān)控等
innodb_status_file = 1

#設(shè)置事務(wù)隔離級(jí)別為 READ-COMMITED，提高事務(wù)效率，通常都滿(mǎn)足事務(wù)一致性要求
transaction_isolation = READ-COMMITTED 
在這里，其他配置選項(xiàng)也需要注意：

#設(shè)置最大并發(fā)連接數(shù)，如果前端程序是PHP，可適當(dāng)加大，但不可過(guò)大
#如果前端程序采用連接池，可適當(dāng)調(diào)小，避免連接數(shù)過(guò)大
max_connections = 60

#最大連接錯(cuò)誤次數(shù)，可適當(dāng)加大，防止頻繁連接錯(cuò)誤后，前端host被mysql拒絕掉
max_connect_errors = 100000

#設(shè)置慢查詢(xún)閥值，建議設(shè)置最小的 1 秒
long_query_time = 1

#設(shè)置臨時(shí)表最大值，這是每次連接都會(huì)分配，不宜設(shè)置過(guò)大 max_heap_table_size 和 tmp_table_size 要設(shè)置一樣大
max_heap_table_size = 96M
tmp_table_size = 96M

#每個(gè)連接都會(huì)分配的一些排序、連接等緩沖，一般設(shè)置為 2MB 就足夠了
sort_buffer_size = 2M
join_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 2M

#建議關(guān)閉query cache，有些時(shí)候?qū)π阅芊炊且环N損害
query_cache_size = 0

#如果是以InnoDB引擎為主的DB，專(zhuān)用于MyISAM引擎的 key_buffer_size 可以設(shè)置較小，8MB 已足夠
#如果是以MyISAM引擎為主，可設(shè)置較大，但不能超過(guò)4G
#在這里，強(qiáng)烈建議不使用MyISAM引擎，默認(rèn)都是用InnoDB引擎
key_buffer_size = 8M

#設(shè)置連接超時(shí)閥值，如果前端程序采用短連接，建議縮短這2個(gè)值
#如果前端程序采用長(zhǎng)連接，可直接注釋掉這兩個(gè)選項(xiàng)，是用默認(rèn)配置(8小時(shí))
interactive_timeout = 120
wait_timeout = 120

3. 開(kāi)始使用InnoDB引擎
修改完配置文件，即可啟動(dòng)MySQL。啟動(dòng)完畢后，在MySQL的datadir目錄下，若產(chǎn)生以下幾個(gè)文件，則表示應(yīng)該可以使用InnoDB引擎了。

-rw-rw---- 1 mysql mysql 1.0G Sep 21 17:25 ibdata1
-rw-rw---- 1 mysql mysql 256M Sep 21 17:25 ib_logfile0
-rw-rw---- 1 mysql mysql 256M Sep 21 10:50 ib_logfile1

登錄MySQL后，執(zhí)行命令，確認(rèn)已啟用InnoDB引擎：

(root:imysql.cn:Thu Oct 15 09:16:22 2009)[mysql]> show engines;
+------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine   | Support | Comment                            | Transactions | XA  | Savepoints |
+------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB   | YES   | Supports transactions, row-level locking, and foreign keys   | YES     | YES | YES    |

接下來(lái)創(chuàng)建一個(gè)InnoDB表：

(root:imysql.cn:Thu Oct 15 09:16:22 2009)[mysql]> 
CREATE TABLE my_innodb_talbe(
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
name VARCHAR(20) NOT NULL DEFAULT '',
passwd VARCHAR(32) NOT NULL DEFAULT '',
PRIMARY KEY(id),
UNIQUE KEY `idx_name`(name)
) ENGINE = InnoDB;

有幾個(gè)和MySQL(尤其是InnoDB引擎)數(shù)據(jù)表設(shè)計(jì)相關(guān)的建議，希望開(kāi)發(fā)者朋友能遵循：

a) 所有InnoDB數(shù)據(jù)表都創(chuàng)建一個(gè)和業(yè)務(wù)無(wú)關(guān)的自增數(shù)字型作為主鍵，對(duì)保證性能很有幫助；
b) 杜絕使用text/blob，確實(shí)需要使用的，盡可能拆分出去成一個(gè)獨(dú)立的表；
c) 時(shí)間戳建議使用 TIMESTAMP 類(lèi)型存儲(chǔ)；
d) IPV4 地址建議用 INT UNSIGNED 類(lèi)型存儲(chǔ)；
e) 性別等非是即非的邏輯，建議采用 TINYINT 存儲(chǔ)，而不是 CHAR(1)；
f) 存儲(chǔ)較長(zhǎng)文本內(nèi)容時(shí)，建議采用JSON/BSON格式存儲(chǔ)；

4.了解InnoDB的存儲(chǔ)結(jié)構(gòu)
從物理意義上來(lái)講，InnoDB表由共享表空間、日志文件組（redo文件組）、表結(jié)構(gòu)定義文件組成。若將innodb_file_per_table設(shè)置為on，則系統(tǒng)將為每一個(gè)表單獨(dú)的生成一個(gè)table_name.ibd的文件，在此文件中，存儲(chǔ)與該表相關(guān)的數(shù)據(jù)、索引、表的內(nèi)部數(shù)據(jù)字典信息。表結(jié)構(gòu)文件則以.frm結(jié)尾，這與存儲(chǔ)引擎無(wú)關(guān)。

　　以下為InnoDB的表空間結(jié)構(gòu)圖：

在InnoDB存儲(chǔ)引擎中，默認(rèn)表空間文件是ibdata1，初始化為10M，且可以擴(kuò)展，如下圖所示：

實(shí)際上，InnoDB的表空間文件是可以修改的，使用以下語(yǔ)句就可以修改：

Innodb_data_file_path=ibdata1:370M；ibdata2:50M:autoextend

　　使用共享表空間存儲(chǔ)方式時(shí)，Innodb的所有數(shù)據(jù)保存在一個(gè)單獨(dú)的表空間里面，而這個(gè)表空間可以由很多個(gè)文件組成，一個(gè)表可以跨多個(gè)文件存在，所以其大小限制不再是文件大小的限制，而是其自身的限制。從Innodb的官方文檔中可以看到，其表空間的最大限制為64TB，也就是說(shuō)，Innodb的單表限制基本上也在64TB左右了，當(dāng)然這個(gè)大小是包括這個(gè)表的所有索引等其他相關(guān)數(shù)據(jù)。

　　而在使用單獨(dú)表空間存儲(chǔ)方式時(shí)，每個(gè)表的數(shù)據(jù)以一個(gè)單獨(dú)的文件來(lái)存放，這個(gè)時(shí)候的單表限制，又變成文件系統(tǒng)的大小限制了。

　　以下即為不同平臺(tái)下，單獨(dú)表空間文件最大限度。

Operating System  File-size Limit
Win32 w/ FAT/FAT32  2GB/4GB
Win32 w/ NTFS          2TB (possibly larger)
Linux 2.4+          (using ext3 file system) 4TB
Solaris 9/10          16TB
MacOS X w/ HFS+         2TB
NetWare w/NSS file system  8TB

以下是MySQL文檔中的內(nèi)容：
Windows用戶(hù)請(qǐng)注意： FAT和VFAT (FAT32)不適合MySQL的生產(chǎn)使用。應(yīng)使用NTFS。

共享表空間與獨(dú)占表空間可以通過(guò)參數(shù)innodb_file_per_table來(lái)轉(zhuǎn)換，若為1，則開(kāi)啟獨(dú)占表空間，否則，開(kāi)啟共享表存儲(chǔ)。

在服務(wù)器資源有限,單表數(shù)據(jù)不是特別多的情況下, 獨(dú)立表空間明顯比共享方式效率更高 . 但是MySQL 默認(rèn)是共享表空間 。

具體的共享表空間和獨(dú)立表空間優(yōu)缺點(diǎn)如下：

共享表空間：
優(yōu)點(diǎn)：
可以放表空間分成多個(gè)文件存放到各個(gè)磁盤(pán)上（表空間文件大小不受表大小的限制，如一個(gè)表可以分布在不同步的文件上）。數(shù)據(jù)和文件放在一起方便管理。
缺點(diǎn)：
所有的數(shù)據(jù)和索引存放到一個(gè)文件中以為著將有一個(gè)很常大的文件，雖然可以把一個(gè)大文件分成多個(gè)小文件，但是多個(gè)表及索引在表空間中混合存儲(chǔ)，這樣對(duì)于一個(gè)表做了大量刪除操作后表空間中將會(huì)有大量的空隙，特別是對(duì)于統(tǒng)計(jì)分析，日值系統(tǒng)這類(lèi)應(yīng)用最不適合用共享表空間。

獨(dú)立表空間：在配置文件（my.cnf）中設(shè)置： innodb_file_per_table

優(yōu)點(diǎn)：
1．  每個(gè)表都有自已獨(dú)立的表空間。
2．  每個(gè)表的數(shù)據(jù)和索引都會(huì)存在自已的表空間中。
3．  可以實(shí)現(xiàn)單表在不同的數(shù)據(jù)庫(kù)中移動(dòng)。
4．  空間可以回收（除drop table操作處，表空不能自已回收）
a)         Drop table操作自動(dòng)回收表空間，如果對(duì)于統(tǒng)計(jì)分析或是日值表，刪除大量數(shù)據(jù)后可以通過(guò):alter table TableName engine=innodb;回縮不用的空間。
b)         對(duì)于使innodb-plugin的Innodb使用truncate table也會(huì)使空間收縮。
c)         對(duì)于使用獨(dú)立表空間的表，不管怎么刪除，表空間的碎片不會(huì)太嚴(yán)重的影響性能，而且還有機(jī)會(huì)處理。
缺點(diǎn)：
單表增加過(guò)大，如超過(guò)100個(gè)G。

對(duì)于啟用了innodb_file_per_table 的參數(shù)選項(xiàng)之后，在每個(gè)表對(duì)應(yīng)的.idb文件內(nèi)只是存放了數(shù)據(jù)、索引和插入緩沖，而撤銷(xiāo)（undo）信息，系統(tǒng)事務(wù)信息，二次寫(xiě)緩沖等還是存放在了原來(lái)的共享表空間內(nèi)。

數(shù)據(jù)段即B+樹(shù)的葉節(jié)點(diǎn)，索引段即為B+樹(shù)的非索引節(jié)點(diǎn)。

InnoDB存儲(chǔ)引擎的管理是由引擎本身完成的，表空間是由分散的頁(yè)和段組成。

區(qū)由64個(gè)連續(xù)的頁(yè)組成，每個(gè)頁(yè)大小為16K，即每個(gè)區(qū)大小為1MB，創(chuàng)建新表時(shí)，先有32頁(yè)大小的碎片頁(yè)存放數(shù)據(jù)，使用完后才是區(qū)的申請(qǐng)，（InnoDB最多每次申請(qǐng)4個(gè)區(qū)，保證數(shù)據(jù)的順序性能）

頁(yè)類(lèi)型有：數(shù)據(jù)頁(yè)、Undo頁(yè)、系統(tǒng)頁(yè)、事務(wù)數(shù)據(jù)頁(yè)、插入緩沖位圖頁(yè)、以及插入緩沖空閑列表頁(yè)。

最新評(píng)論