程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> 關於MYSQL數據庫 >> MySQL的InnoDB引擎入門學習教程

MySQL的InnoDB引擎入門學習教程

編輯:關於MYSQL數據庫

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

1. 快速認識InnoDB
InnoDB是MySQL下使用最廣泛的引擎,它是基於MySQL的高可擴展性和高性能存儲引擎,從5.5版本開始,它已經成為了默認引擎。
InnODB引擎支持眾多特性:

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

2. 修改InnoDB配置選項
可以選擇官方版本,或者Percona的分支,如果不知道在哪下載,就google吧。
安裝完MySQL後,需要適當修改下my.cnf配置文件,針對InnoDB相關的選項做一些調整,才能較好的運行InnoDB。
相關的選項有:

#InnoDB存儲數據字典、內部數據結構的緩沖池,16MB 已經足夠大了。
innodb_additional_mem_pool_size = 16M

#InnoDB用於緩存數據、索引、鎖、插入緩沖、數據字典等
#如果是專用的DB服務器,且以InnoDB引擎為主的場景,通常可設置物理內存的50%
#如果是非專用DB服務器,可以先嘗試設置成內存的1/4,如果有問題再調整
#默認值是8M,非常坑X,這也是導致很多人覺得InnoDB不如MyISAM好用的緣故
innodb_buffer_pool_size = 4G

#InnoDB共享表空間初始化大小,默認是 10MB,也非常坑X,改成 1GB,並且自動擴展
innodb_data_file_path = ibdata1:1G:autoextend

#如果不了解本選項,建議設置為1,能較好保護數據可靠性,對性能有一定影響,但可控
innodb_flush_log_at_trx_commit = 1

#InnoDB的log buffer,通常設置為 64MB 就足夠了
innodb_log_buffer_size = 64M

#InnoDB redo log大小,通常設置256MB 就足夠了
innodb_log_file_size = 256M

#InnoDB redo log文件組,通常設置為 2 就足夠了
innodb_log_files_in_group = 2

#啟用InnoDB的獨立表空間模式,便於管理
innodb_file_per_table = 1

#啟用InnoDB的status file,便於管理員查看以及監控等
innodb_status_file = 1

#設置事務隔離級別為 READ-COMMITED,提高事務效率,通常都滿足事務一致性要求
transaction_isolation = READ-COMMITTED 
在這裡,其他配置選項也需要注意:

#設置最大並發連接數,如果前端程序是PHP,可適當加大,但不可過大
#如果前端程序采用連接池,可適當調小,避免連接數過大
max_connections = 60

#最大連接錯誤次數,可適當加大,防止頻繁連接錯誤後,前端host被mysql拒絕掉
max_connect_errors = 100000

#設置慢查詢閥值,建議設置最小的 1 秒
long_query_time = 1

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

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

#建議關閉query cache,有些時候對性能反而是一種損害
query_cache_size = 0

#如果是以InnoDB引擎為主的DB,專用於MyISAM引擎的 key_buffer_size 可以設置較小,8MB 已足夠
#如果是以MyISAM引擎為主,可設置較大,但不能超過4G
#在這裡,強烈建議不使用MyISAM引擎,默認都是用InnoDB引擎
key_buffer_size = 8M

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

3. 開始使用InnoDB引擎
修改完配置文件,即可啟動MySQL。啟動完畢後,在MySQL的datadir目錄下,若產生以下幾個文件,則表示應該可以使用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後,執行命令,確認已啟用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    |

接下來創建一個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引擎)數據表設計相關的建議,希望開發者朋友能遵循:

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

4.了解InnoDB的存儲結構
從物理意義上來講,InnoDB表由共享表空間、日志文件組(redo文件組)、表結構定義文件組成。若將innodb_file_per_table設置為on,則系統將為每一個表單獨的生成一個table_name.ibd的文件,在此文件中,存儲與該表相關的數據、索引、表的內部數據字典信息。表結構文件則以.frm結尾,這與存儲引擎無關。

  以下為InnoDB的表空間結構圖:

20151118120502290.jpg (421×275)

在InnoDB存儲引擎中,默認表空間文件是ibdata1,初始化為10M,且可以擴展,如下圖所示:

20151118120527840.png (422×154)

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

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

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

  而在使用單獨表空間存儲方式時,每個表的數據以一個單獨的文件來存放,這個時候的單表限制,又變成文件系統的大小限制了。

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

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文檔中的內容:
Windows用戶請注意: FAT和VFAT (FAT32)不適合MySQL的生產使用。應使用NTFS。

共享表空間與獨占表空間可以通過參數innodb_file_per_table來轉換,若為1,則開啟獨占表空間,否則,開啟共享表存儲。

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

具體的共享表空間和獨立表空間優缺點如下:

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

獨立表空間:在配置文件(my.cnf)中設置: innodb_file_per_table

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

對於啟用了innodb_file_per_table 的參數選項之後,在每個表對應的.idb文件內只是存放了數據、索引和插入緩沖,而撤銷(undo)信息,系統事務信息,二次寫緩沖等還是存放在了原來的共享表空間內。

數據段即B+樹的葉節點,索引段即為B+樹的非索引節點。

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

區由64個連續的頁組成,每個頁大小為16K,即每個區大小為1MB,創建新表時,先有32頁大小的碎片頁存放數據,使用完後才是區的申請,(InnoDB最多每次申請4個區,保證數據的順序性能)

頁類型有:數據頁、Undo頁、系統頁、事務數據頁、插入緩沖位圖頁、以及插入緩沖空閒列表頁。

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved