MySQL的存储引擎

MySQL的存储引擎

1. InnoDB

   • 支持事务
   • 行锁
   • 支持外键
   • 多版本并发控制，4种隔离级别
   • 聚集的方式，每个表都有主键按顺序存放

2. MyISAM

   • 不支持事务、表锁
   • 支持全文检索
   • 储存表引擎是由MYD和MYI组成，MYD存放数据文件，MYI存放索引文件

3. NDB

   • 集群存储引擎
   • 数据全部存放在内存之中

4. Memory

   • 数据存放在内存之中
   • 建立临时表来存放临时表查询的中间结果集
   • 索引使用的哈希索引
   • 并发性差

5. Archive

   • 只支持SELECT和INSERT
   • 数据归档数据

6. Federated

   • 远程mysql的数据表

7. Maria

   • 支持缓存
   • 支持索引文件
   • 应用行锁设计

8. 查看MySQL支持的引擎

   mysql> show engines\G;
   *************************** 1. row ***************************
         Engine: ARCHIVE
        Support: YES
        Comment: Archive storage engine
   Transactions: NO
             XA: NO
     Savepoints: NO
   *************************** 2. row ***************************
         Engine: BLACKHOLE
        Support: YES
        Comment: /dev/null storage engine (anything you write to it disappears)
   Transactions: NO
             XA: NO
     Savepoints: NO
   *************************** 3. row ***************************
         Engine: MRG_MYISAM
        Support: YES
        Comment: Collection of identical MyISAM tables
   Transactions: NO
             XA: NO
     Savepoints: NO
   *************************** 4. row ***************************
         Engine: FEDERATED
        Support: NO
        Comment: Federated MySQL storage engine
   Transactions: NULL
             XA: NULL
     Savepoints: NULL
   *************************** 5. row ***************************
         Engine: MyISAM
        Support: YES
        Comment: MyISAM storage engine
   Transactions: NO
             XA: NO
     Savepoints: NO
   *************************** 6. row ***************************
         Engine: PERFORMANCE_SCHEMA
        Support: YES
        Comment: Performance Schema
   Transactions: NO
             XA: NO
     Savepoints: NO
   *************************** 7. row ***************************
         Engine: InnoDB
        Support: DEFAULT
        Comment: Supports transactions, row-level locking, and foreign keys
   Transactions: YES
             XA: YES
     Savepoints: YES
   *************************** 8. row ***************************
         Engine: MEMORY
        Support: YES
        Comment: Hash based, stored in memory, useful for temporary tables
   Transactions: NO
             XA: NO
     Savepoints: NO
   *************************** 9. row ***************************
         Engine: CSV
        Support: YES
        Comment: CSV storage engine
   Transactions: NO
             XA: NO
     Savepoints: NO
   9 rows in set (0.00 sec)
   
   ERROR:
   No query specified