MySQL 存储引擎

1.MySQL提供的存储引擎

InnoDB
MyISAM
MEMORY
ARCHIVE
FEDERATED
EXAMPLE
BLACKHOLE
MERGE
NDBCLUSTER
CSV

除了以上一些存储引擎，MySQL还可以使用第三方存储引擎：

- MySQL当中插件式的存储引擎类型
- MySQL的2个分支
- perconaDB
- MariaDB

2.查看MySQL的存储引擎

查看当前MySQL支持的存储引擎

mysql> SHOW ENGINES;

查看存储引擎是InnoDB的表

mysql> SELECT TABLE_SCHEMA,TABLE_NAME,ENGINE FROM INFORMATION_SCHEMA.TABLES WHERE ENGINE='InnoDB';

查看存储引擎是MyISAM的表

mysql> SELECT TABLE_SCHEMA,TABLE_NAME,ENGINE FROM INFORMATION_SCHEMA.TABLES WHERE ENGINE='MyISAM';

3.InnoDB和MyISAM物理上的区别

InnoDB存储引擎物理文件

[root@db01 ~]# cd /usr/local/mysql/data/world/
[root@db01 world]# ll city.*
-rw-rw---- 1 mysql mysql   8710 Jun 15 21:37 city.frm   #表结构文件
-rw-rw---- 1 mysql mysql 835584 Jun 15 21:41 city.ibd   #表数据文件

MyISAM存储引擎物理文件

[root@db01 ~]# cd /usr/local/mysql/data/mysql
[root@db01 mysql]# ll user.*
-rw-rw---- 1 mysql mysql 10684 Jun  4 16:17 user.frm    #表结构文件
-rw-rw---- 1 mysql mysql  1164 Jun 10 20:18 user.MYD    #表数据文件
-rw-rw---- 1 mysql mysql  2048 Jun 11 22:39 user.MYI    #表索引文件

4.InnoDB存储引擎

从MySQL5.5版本之后，MySQL的默认内置存储引擎已经是InnoDB。

InnoDB存储引擎特点

• 事务安全（遵从 ACID）
• MVCC（Multi-Versioning Concurrency Control，多版本并发控制）
• InnoDB 行级别锁定
• Oracle 样式一致非锁定读取
• 表数据进行整理来优化基于主键的查询
• 支持外键引用完整性约束
• 大型数据卷上的最大性能
• 将对表的查询与不同存储引擎混合
• 出现故障后快速自动恢复
• 用于在内存中缓存数据和索引的缓冲区池

InnoDB核心特性

• MVCC
• 事务
• 行级锁
• 热备份
• 自动故障恢复 CSR（Crash Safe Recovery）

5.存储引擎相关命令

5.1 查看默认存储引擎

mysql> SELECT @@default_storage_engine;

mysql> SHOW VARIABLES LIKE "default_storage_engine";

5.2 查看指定表的存储引擎

mysql> SHOW CREATE TABLE city;

mysql> SELECT TABLE_SCHEMA,TABLE_NAME,ENGINE FROM information_schema.tables WHERE TABLE_NAME='city';

5.3 设置存储引擎

• 临时设置存储引擎

mysql> SET GLOBAL default_storage_engine=InnoDB;

• 永久设置存储引擎

在配置文件/etc/my.cnf里面设置，可以永久生效

[mysqld]
default_storage_engine=InnoDB

• 建表时指定存储引擎

mysql> CREATE TABLE tb01(id int) ENGINE=InnoDB;