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         MyISAMMySQL默认的存储引擎,它主要用于大多数的Web、数据仓库和其它应用中。可以通过数据库配置文件中的storage_engine选项来改变默认的存储引擎。
l         InnoDB:主要用于事务处理应用,并且支持事务的ACID特性和外键。
l         BDB:支持COMMITROLLBACK和其它事务特性。
在创建表或修改表的时候,都可以指定需要使用的存储引擎:

 

CREATE TABLE engineTest (id INT) ENGINE = MyISAM;

 

ALTER TABLE engineTest ENGINE = ARCHIVE;
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