MySQL存储引擎采用了可插拔的结构,即用户可以根据自己的需要来选择不同的存储引擎。
下表是MySQL不同的存储引擎的不同的特性:
Feature
|
MyISAM
|
BDB
|
Memory
|
InnoDB
|
Archive
|
NDB
|
Storage limits
|
256TB
|
No
|
Yes
|
64TB
|
No
|
384EB
[4]
|
Transactions
|
No
|
Yes
|
No
|
Yes
|
No
|
Yes
|
Locking granularity
|
Table
|
Page
|
Table
|
Row
|
Row
|
Row
|
MVCC (snapshot read)
|
No
|
No
|
No
|
Yes
|
Yes
|
No
|
Geospatial support
|
Yes
|
Yes
[1]
|
No
|
Yes
[1]
|
Yes
[1]
|
Yes
[1]
|
B-tree indexes
|
Yes
|
Yes
|
Yes
|
Yes
|
No
|
Yes
|
Hash indexes
|
No
|
No
|
Yes
|
No
|
No
|
Yes
|
Full-text search indexes
|
Yes
|
No
|
No
|
No
|
No
|
No
|
Clustered indexes
|
No
|
Yes
|
No
|
Yes
|
No
|
No
|
Data caches
|
No
|
Yes
|
N/A
|
Yes
|
No
|
Yes
|
Index caches
|
Yes
|
Yes
|
N/A
|
Yes
|
No
|
Yes
|
Compressed data
|
Yes
|
No
|
No
|
No
|
Yes
|
No
|
Encrypted data
[2]
|
Yes
|
Yes
|
Yes
|
Yes
|
Yes
|
Yes
|
Cluster database support
|
No
|
No
|
No
|
No
|
No
|
Yes
|
Replication support
[3]
|
Yes
|
Yes
|
Yes
|
Yes
|
Yes
|
Yes
|
Foreign key support
|
No
|
No
|
No
|
Yes
|
No
|
No
|
Backup / point-in-time recovery
[3]
|
Yes
|
Yes
|
Yes
|
Yes
|
Yes
|
Yes
|
Query cache support
|
Yes
|
Yes
|
Yes
|
Yes
|
Yes
|
Yes
|
Update statistics for data dictionary
|
Yes
|
Yes
|
Yes
|
Yes
|
Yes
|
Yes
|
下面列举几个常用的存储引擎:
l
MyISAM:MySQL默认的存储引擎,它主要用于大多数的Web、数据仓库和其它应用中。可以通过数据库配置文件中的storage_engine选项来改变默认的存储引擎。
l
InnoDB:主要用于事务处理应用,并且支持事务的ACID特性和外键。
l
BDB:支持COMMIT,ROLLBACK和其它事务特性。
在创建表或修改表的时候,都可以指定需要使用的存储引擎:
InnoDBBDBInnoDB
InnoDBMVCCCOMMIT, ROLLBACKsvepoints
BDBCOMMITROLLBACK
MySQLInnoDB
InnoDBInnoDBInnoDBMyISAM
InnoDBrowid
InnoDB
l
S
l
X
T1t
l
T2tST1T2tS
l
T2tX
T1tXt
InnoDB
l
ISTTS
l
IXTTX
l
SIS
l
XIX
1,0
|
X
|
IX
|
S
|
IS
|
X
|
0
|
0
|
0
|
0
|
IX
|
0
|
1
|
0
|
1
|
S
|
0
|
0
|
1
|
1
|
IS
|
0
|
1
|
1
|
1
|
InnoDBREPEATABLE READInnoDBSQL
InnoDBSELECT
REPEATABLE READ
DROP TABLE ALTER TABLEDROP TABLEMYSQLALTER TABLEMYSQL
Next-Key Locking
InnoDBnext-key lockingSX
InnoDBgapRSXR
InnoDB
InnoDB6-byte7-byte(roll pointer)undoundo
InnoDBundo
undoInnoDB
undo
本文转自holy2009 51CTO博客,原文链接:http://blog.51cto.com/holy2010/370158