一、存储引擎分类
Mysql默认支持多种存储引擎，用户可以通过选择不同的数据引擎，提供灵活的存储，提高应用的效率。Mysql支持的存储引擎包括MyISAM、InnoDB、BDB、MEMORY、MERGE、EXAMPLE、NDB Cluster、ARCHIVE、CSV、BLACKHOLE、FEDERATED 等，其中InnoDB 和BDB 提供事务安全表，其他存储引擎都是非事务安全表。
（1）输入命令 SHOW ENGINES 查看当前数据库支持的存储引擎及特点：可以看到当前数据库默认的引擎是InnoDB
# 表格显示形式
show engines g;
# 列表显示形式
show engines G;
mysql> show engines G;
*************************** 1. row ***************************
Engine: MEMORY
Support: YES
Comment: Hash based, stored in memory, useful for temporary tables
Transactions: NO
XA: NO
Savepoints: NO
*************************** 6. row ***************************
Engine: MyISAM
Support: YES
Comment: MyISAM storage engine
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
（2）输入命令 SHOW VARIABLES LIKE '%storage_engine' 查看数据库默认使用的哪个引擎
mysql> SHOW VARIABLES LIKE '%storage_engine';
+----------------------------------+-----------+
| Variable_name | Value |
+----------------------------------+-----------+
| default_storage_engine | InnoDB |
| default_tmp_storage_engine | InnoDB |
| internal_tmp_disk_storage_engine | InnoDB |
| internal_tmp_mem_storage_engine | TempTable |
+----------------------------------+-----------+
4 rows in set, 1 warning (0.00 sec)
（3）在创建表的时候，可以使用命令：CREATE TABLE table_name(...)ENGINE = InnoDB/MyISAM DEFAULT CHARSET=gbk即可指定数据库引擎
CREATE TABLE tbl_student (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
name VARCHAR(50) NOT NULL,
PRIMARY KEY (id)
)ENGINE=InnoDB DEFAULT CHARSET=gbk;
（4）可以使用 SHOW CREATE TABLE table_name 语句查看表的SQL创建语句，即可以看到该表使用的引擎
（5）执行命令 ALTER TABLE tablename engine = InnoDB 改变表的引擎
二、存储引擎的特点
2.1、MyISAM引擎
2.1.1 特点：
（1）不支持事务，不支持外键，不支持行级锁，操作时需要锁定整张表，不过会保存表的行数，SELECT/INSERT为主的应用可以使用该引擎。
（2）每个MyISAM 在磁盘上存储成3 个文件，其文件名都和表名相同，扩展名分别是：
.frm:存储表定义（表结构等信息）
.MYD(MYData):存储数据
.MYI(MYIndex):存储索引
（3）数据文件和索引文件可以放置在不同的目录，平均分布IO，获得更快的速度。指定索引文件和数据文件的路径，需要在创建表的时候通过DATA DIRECTORY 和INDEX DIRECTORY语句指定，文件路径需要是绝对路径，并且具有访问权限。
（4）MyISAM类型的表可能会损坏，可以用CHECK TABLE语句来检查MyISAM表健康，并用REPAIR TABLE语句修复一个损坏的MyISAM表
（5）只有MyISAM支持FULLTEXT全文索引
使用场景：查询较多的场景
2.1.2 存储：
MyISAM的表支持3种不同的存储格式，分别为：静态表（默认的）、动态表、压缩表。
（1）静态表（固定长度）：表中字段都是非变长字段，每个记录都是固定长度，当表不包含可变长度列（VARCHAR,BLOB,TEXT）使用。
① 优点：存储迅速，容易缓存，出现故障容易恢复
② 缺点：占用的空间通常比动态表多，这是因为静态表中数据存储的时候会根据列宽度补足空格，但是访问的时候是不可见的（真实数据存在的空格也会被去掉）
# 创建表，使用MyISAM引擎
create table tbl_test(name char(10))engine=myisam;
# 插入测试语句
insert into tbl_test values('abc'),(' abc'),('abc ');
# 查询结果
select name,length(name) from tbl_test;
+-------+--------------+
| name | length(name) |
+-------+--------------+
| abc | 3 |
| abc | 5 |
| abc | 3 |
+-------+--------------+
（2）动态表：表中包含变长字段，记录是不固定长度的。当表包含可变长度列（VARCHAR，BLOB，TEXT，Dynamic）时使用。
① 优点：占用空间相对较少
② 缺点：频繁更新/删除记录会产生碎片，需要定期执行OPTIMIZE TABLE tablename或myisamchk -r命令改善性能；出现故障恢复相对比较困难。
（3）压缩表：是由myisampack工具创建的，占用非常小的磁盘空间，因为每个记录都会被单独压缩，所以只有非常小的方位开支。
2.2、InnoDB引擎
2.2.1 特点：
（1）具有提交、回滚和崩溃恢复能力的事务安全。写的处理效率差一点并且会占用更多的磁盘空间保留数据和索引。
（2）提供了对数据库ACID（原子性Atomicity、一致性Consistency、隔离性Isolation、持久性Durability）事务的支持，实现了SQL标准的四种隔离级别。
（3）设计目标就是处理大容量的数据库系统，MySQL运行时InnoDB会在内存中建立缓冲池，用于缓冲数据和索引。
（4）执行“select count(*) from table”语句时需要扫描全表，只是因为InnoDB不会像MyISAM那样不保存具体的行数，所以需要扫描整个表才能计算多少行。
（5）由于是行锁，粒度更小，所以写操作不会锁定全表，在并发较高时，使用InnoDB会提升效率。即存在大量UPDATE/INSERT操作时，效率较高。
（6）InnoDB清空数据量大的表时，是非常缓慢，这是因为InnoDB必须处理表中的每一行，根据InnoDB的事务设计原则，首先需要把“删除动作”写入“事务日志”，然后写入实际的表。
所以，清空大表的时候，最好直接drop table然后重建。即InnoDB一行一行删除，不会重建表。
使用场景：
（1）经常UPDETE/INSERT的表，使用处理多重并发的更新请求
（2）支持事务，必选InnoDB
（3）可以从灾难中恢复（日志+事务回滚）
（4）外键约束、列属性AUTO_INCREMENT支持
不同于其他引擎的特点介绍：
（1）自动增长列：
1) 自动增长的列可以手工插入，但是如果插入的为null或者0，则实际的是自增后的值。
2) 使用ALTER TABLE tableName AUTO_INCREMENT = initial_value强制设置列增长的初始值，默认为1。但是该initial_value值是保存在内存中的，如果使用该值之前数据库重启启动的话，这个强制的默认值会丢失，就会需要重新设置。
3) 执行select LAST_INSERT_ID()可以查看当前线程最后插入记录使用的值，但是一次插入好多条记录的话，那么返回的是第一条的自增值。即insert tablename values(1),(2),(3)则返回的是value(1)的自增值。
# 创建表
create table tbl_test (
id int not null auto_increment ,
name varchar(10) not null,
primary key(id)
) engine=innodb;
# 插入一条数据
insert into tbl_test(name) values('a');
# 查看自增值
mysql> select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
| 1 |
+------------------+
# 批量插入多条数据
insert into tbl_test(name) values('b'),('c'),('d');
# 查看自增值
mysql> select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
| 2 |
+------------------+
4) InnoDB自增列必须是索引（主键是一种聚集索引），如果是组合索引，也必须是组合索引的第一列。而MyISAM可以是组合索引的其他列（按照组合索引的前面几列进行排序后递增的）,即index(col1,col2)中自增列为col1
（2）外键约束：
1) MySQL唯一支持外键的只有InnoDB，创建外键的时候，要求父表必须有对应的索引，子表在创建外键的时候也会自动创建对应的索引。
2) 在创建索引的时候，可以指定在删除、更新父表的时候，对子表进行相应的操作，如：RESTRICT、CASCADE、SET NULL、NO ACTION(与RESTRICT相同)
RESTRICT：限制子表有关联记录的情况下父表不能更新。
CASCADE：父表在更新或删除的时候，更新/删除子表对应记录。
SET NULL：父表在更新或者删除的时候，子表对应字段SET NULL
比如：子表中外键指定时候有ON DELETE RESTRICT ON UPDATE CASCADE，表明在父表删除的时候，子表有对应记录不允许被删除，主表在更新记录的时候，子表如果有对应记录则子表对应更新
CONSTRAINT fk_student_course FOREIGN KEY(student_id) REFERENCES student(student_id) ON DELETE RESTRICT ON UPDATE CASCADE;
3) 当某个表被其他表创建了参考外键，那么该表对应的索引或者主键禁止被删除。如果操作LOAD DATA与ALTER TABLE需要暂时关闭外键检查可以加快处理速度，执行SET FOREIGN_KEY_CHECKS=0关闭，之后执行SET FOREIGN_KEY_CHECKS=1恢复原状。通过命令可以查看index与keys
2.2.2 存储：
InnoDB 存储表和索引有以下两种方式：
1、使用共享表空间存储
这种方式表的表结构保存在.frm 文件中，数据和索引保存在innodb_data_home_dir 和innodb_data_file_path 定义的表空间中，可以是多个文件。
2、使用多表空间存储
这种方式表的表结构仍然保存在.frm 文件中，但是每个表的数据和索引单独保存在.ibd 中。如果是个分区表，则每个分区对应单独的.ibd文件，文件名是“表名+分区名”，可以在创建分区的时候指定每个分区的数据文件的位置，以此来将表的IO 均匀分布在多个磁盘上。
要使用多表空间的存储方式，需要设置参数innodb_file_per_table，并重新启动服务后才可以生效，对于新建的表按照多表空间的方式创建，已有的表仍然使用共享表空间存储。如果将已有的多表空间方式修改回共享表空间的方式，则新建表会在共享表空间中创建，但已有的多表空间的表仍然保存原来的访问方式。所以多表空间的参数生效后，只对新建的表生效。多表空间的数据文件没有大小限制，不需要设置初始大小，也不需要设置文件的最大限制、扩展大小等参数。
对于使用多表空间特性的表，可以比较方便地进行单表备份和恢复操作，但是直接复制.ibd 文件是不行的，因为没有共享表空间的数据字典信息，直接复制的.ibd 文件和.frm 文件恢复时是不能被正确识别的，但可以通过以下命令：
ALTER TABLE tbl_name DISCARD TABLESPACE;
ALTER TABLE tbl_name IMPORT TABLESPACE;
将备份恢复到数据库中，但是这样的单表备份，只能恢复到表原来在的数据库中，而不能恢复到其他的数据库中。如果要将单表恢复到目标数据库，则需要通过mysqldump 和mysqlimport 来实现。
3、Memory引擎
MEMORY 存储引擎使用存在内存中的内容来创建表。每个MEMORY 表只实际对应一个磁盘文件，格式是.frm。MEMORY 类型的表访问非常得快，因为它的数据是放在内存中的，并且默认使用HASH 索引，但是一旦服务关闭，表中的数据就会丢失掉
给MEMORY 表创建索引的时候，可以指定使用HASH 索引还是BTREE 索引
create index id_hash_index USING HASH on tbl_test (id) ;
SHOW INDEX FROM tbl_test G
create index id_btree_index USING BTREE on tbl_test (id) ;
SHOW INDEX FROM tbl_test G
在启动MySQL 服务的时候使用--init-file 选项，把INSERT INTO ... SELECT 或LOAD DATA INFILE 这样的语句放入这个文件中，就可以在服务启动时从持久稳固的数据源装载表。
服务器需要足够内存来维持所有在同一时间使用的MEMORY 表，当不再需要MEMORY表的内容之时，要释放被MEMORY 表使用的内存，应该执行DELETE FROM 或TRUNCATE TABLE，或者整个地删除表（使用DROP TABLE 操作）。
每个MEMORY 表中可以放置的数据量的大小，受到max_heap_table_size 系统变量的约束，这个系统变量的初始值是16MB，可以按照需要加大。此外，在定义MEMORY 表的时候，可以通过MAX_ROWS 子句指定表的最大行数。
MEMORY 类型的存储引擎主要用在那些内容变化不频繁的代码表，或者作为统计操作的中间结果表，便于高效地对中间结果进行分析并得到最终的统计结果。对MEMORY 存储引擎的表进行更新操作要谨慎，因为数据并没有实际写入到磁盘中，所以一定要对下次重新启动服务后如何获得这些修改后的数据有所考虑。
不支持Blob和TEXT类型的字段，一般很少用
三、存储引擎比较
1、InnoDB支持事务，MyISAM不支持，对于InnoDB每一条SQL语言都默认封装成事务，自动提交。这样会影响速度，最好是把多条SQL语句放在begin transaction;和commit之间组成一个事务。
2、InnoDB支持外键，而MyISAM不支持，对一个包含外键的InnoDB表转为MYISAM会失败
3、InnoDB是聚簇索引，数据文件是和索引绑定在一起的，必须要有主键，通过主键索引效率会很高。但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据。
因此主键不应该过大，因为主键太大，其他索引也都会很大。而MYISAM是非聚簇索引，数据文件是分离的，索引保存的是数据文件的指针。主键索引和辅助索引是独立的。
4、InnoDB不保存表的具体行数，执行select count(*)from table时，需要全表扫描。而MyISAM用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快。
5、InnoDB不支持全文索引，而MyISAM支持全文索引，查询效率上MyISAM更高。
| 对比项 | MyISAM | InnoDB |
|---|---|---|
| 主外键 | 不支持 | 支持 |
| 事务 | 不支持 | 支持 |
| 行表锁 | 表锁，即使操作一条记录也会锁住整个表，不适合高并发的操作 | 行锁，操作时只锁某一行，不对其他行有影响，适合高并发操作 |
| 缓存 | 只缓存索引，不缓存真实数据 | 不仅缓存索引还要缓存真实数据，对内存要求较高，而且内存大小对性能有决定性的影响 |
| 表空间 | 小 | 大 |
| 关注点 | 性能 | 事务 |
四、如何选择？
1、是否要支持事务。如果要选择InnoDB，否则选择MyISAM
2、如果表中绝大多数都只是读查询，可以考虑MyISAM，如果读写也挺频繁，使用InnoDB
3、系统崩溃后，MyISAM恢复起来更困难，是否可以接受