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

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

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

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

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

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

#InnoDB共享表空間初始化大小，默認(rèn)是 10MB，也非?？覺，改成 1GB，并且自動擴展
innodb_data_file_path = ibdata1:1G:autoextend

#如果不了解本選項，建議設(shè)置為1，能較好保護(hù)數(shù)據(jù)可靠性，對性能有一定影響，但可控
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的獨立表空間模式，便于管理
innodb_file_per_table = 1

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

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

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

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

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

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

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

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

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

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

3. 開始使用InnoDB引擎
修改完配置文件，即可啟動MySQL。啟動完畢后，在MySQL的datadir目錄下，若產(chǎn)生以下幾個文件，則表示應(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    |

接下來創(chuàng)建一個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;

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

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

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

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

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

實際上，InnoDB的表空間文件是可以修改的，使用以下語句就可以修改：

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

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

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

　　以下即為不同平臺下，單獨表空間文件最大限度。

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用戶請注意： FAT和VFAT (FAT32)不適合MySQL的生產(chǎn)使用。應(yīng)使用NTFS。

共享表空間與獨占表空間可以通過參數(shù)innodb_file_per_table來轉(zhuǎn)換，若為1，則開啟獨占表空間，否則，開啟共享表存儲。

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

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

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

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

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

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

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

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

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

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

最新評論