《MYSQL教程MySQL的InnoDB引擎入門學(xué)習(xí)教程》要點(diǎn)：
本文介紹了MYSQL教程MySQL的InnoDB引擎入門學(xué)習(xí)教程，希望對您有用。如果有疑問，可以聯(lián)系我們。

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

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

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

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

MYSQL應(yīng)用
#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引擎為主的場景,通常可設(shè)置物理內(nèi)存的50%
#如果是非專用DB服務(wù)器,可以先嘗試設(shè)置成內(nèi)存的1/4,如果有問題再調(diào)整
#默認(rèn)值是8M,非常坑X,這也是導(dǎo)致很多人覺得InnoDB不如MyISAM好用的緣故
innodb_buffer_pool_size = 4G
#InnoDB共享表空間初始化大小,默認(rèn)是 10MB,也非常坑X,改成 1GB,并且自動擴(kuò)展
innodb_data_file_path = ibdata1:1G:autoextend
#如果不了解本選項(xiàng),建議設(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的獨(dú)立表空間模式,便于管理
innodb_file_per_table = 1
#啟用InnoDB的status file,便于管理員查看以及監(jiān)控等
innodb_status_file = 1
#設(shè)置事務(wù)隔離級別為 READ-COMMITED,提高事務(wù)效率,通常都滿足事務(wù)一致性要求
transaction_isolation = READ-COMMITTED 
在這里,其他配置選項(xiàng)也需要注意：
#設(shè)置最大并發(fā)連接數(shù),如果前端程序是PHP,可適當(dāng)加大,但不可過大
#如果前端程序采用連接池,可適當(dāng)調(diào)小,避免連接數(shù)過大
max_connections = 60
#最大連接錯(cuò)誤次數(shù),可適當(dāng)加大,防止頻繁連接錯(cuò)誤后,前端host被mysql拒絕掉
max_connect_errors = 100000
#設(shè)置慢查詢閥值,建議設(shè)置最小的 1 秒
long_query_time = 1
#設(shè)置臨時(shí)表最大值,這是每次連接都會分配,不宜設(shè)置過大 max_heap_table_size 和 tmp_table_size 要設(shè)置一樣大
max_heap_table_size = 96M
tmp_table_size = 96M
#每個(gè)連接都會分配的一些排序、連接等緩沖,一般設(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,專用于MyISAM引擎的 key_buffer_size 可以設(shè)置較小,8MB 已足夠
#如果是以MyISAM引擎為主,可設(shè)置較大,但不能超過4G
#在這里,強(qiáng)烈建議不使用MyISAM引擎,默認(rèn)都是用InnoDB引擎
key_buffer_size = 8M
#設(shè)置連接超時(shí)閥值,如果前端程序采用短連接,建議縮短這2個(gè)值
#如果前端程序采用長連接,可直接注釋掉這兩個(gè)選項(xiàng),是用默認(rèn)配置(8小時(shí))
interactive_timeout = 120
wait_timeout = 120

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

MYSQL應(yīng)用
-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應(yīng)用登錄MySQL后,執(zhí)行命令,確認(rèn)已啟用InnoDB引擎：

MYSQL應(yīng)用
(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    |

MYSQL應(yīng)用接下來創(chuàng)建一個(gè)InnoDB表：

MYSQL應(yīng)用
(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應(yīng)用有幾個(gè)和MySQL(尤其是InnoDB引擎)數(shù)據(jù)表設(shè)計(jì)相關(guān)的建議,希望開發(fā)者朋友能遵循：

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

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

MYSQL應(yīng)用　　以下為InnoDB的表空間結(jié)構(gòu)圖：

MYSQL應(yīng)用

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

MYSQL應(yīng)用

MYSQL應(yīng)用實(shí)際上,InnoDB的表空間文件是可以修改的,使用以下語句就可以修改：

MYSQL應(yīng)用Innodb_data_file_path=ibdata1:370M；ibdata2:50M:autoextend

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

MYSQL應(yīng)用　　而在使用單獨(dú)表空間存儲方式時(shí),每個(gè)表的數(shù)據(jù)以一個(gè)單獨(dú)的文件來存放,這個(gè)時(shí)候的單表限制,又變成文件系統(tǒng)的大小限制了.

MYSQL應(yīng)用　　以下即為不同平臺下,單獨(dú)表空間文件最大限度.

MYSQL應(yī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應(yīng)用以下是MySQL文檔中的內(nèi)容：
Windows用戶請注意： FAT和VFAT (FAT32)不適合MySQL的生產(chǎn)使用.應(yīng)使用NTFS.

MYSQL應(yīng)用共享表空間與獨(dú)占表空間可以通過參數(shù)innodb_file_per_table來轉(zhuǎn)換,若為1,則開啟獨(dú)占表空間,否則,開啟共享表存儲.

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

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

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

MYSQL應(yīng)用獨(dú)立表空間：在配置文件(my.cnf)中設(shè)置： innodb_file_per_table

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

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

MYSQL應(yīng)用數(shù)據(jù)段即B+樹的葉節(jié)點(diǎn),索引段即為B+樹的非索引節(jié)點(diǎn).

MYSQL應(yīng)用InnoDB存儲引擎的管理是由引擎本身完成的,表空間是由分散的頁和段組成.

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

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

轉(zhuǎn)載請注明本頁網(wǎng)址：
http://www.fzlkiss.com/jiaocheng/4735.html