一、MySQL存储引擎

1.INNODB

2.MyISAM

3.momery

4.与存储引擎有关的sql命令

1.show engines \G：查看数据库支持哪些存储引擎

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

2.alter table student engine=myisam; 修改表的存储引擎

mysql> alter table student engine=myisam; Query OK, 7 rows affected (1.28 sec) Records: 7 Duplicates: 0 Warnings: 0 mysql> show create table student; +---------+-----------------------------------+ | Table | Create Table | +---------+------------------------------------+ | student | CREATE TABLE `student` ( `sid` bigint(20) DEFAULT NULL, `sname` varchar(255) DEFAULT NULL, `sex` char(1) DEFAULT NULL, `sage` int(11) DEFAULT NULL, KEY `inx_all` (`sid`,`sage`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 | 

二、MyLSAM和InnoDB存储引擎区别

锁机制:表示数据库在并发请求访问的时候，多个事务在操作时，并发操作的粒度B-树索引和哈希索引:主要是加速SQL的查询速度
外键:子表的字段依赖父表的主键，设置两张表的依赖关系
事务︰多个SQL语句，保证他们共同执行的原子操作，要么成功，要么失败，不能只成功一部分，失败需要考虑回滚事务
索引缓存和数据缓存︰和MYSQL server的查询缓存相关，在没有对数据和索引做修改之前，重复查询可以不用进行磁盘l/O，读取上一次内存中查询的缓存就可以了。

三、MyLSAM和InnoDB存储引擎使用的锁

MylSAM使用的表级锁：表级锁对当前操作的整张表进行加锁，是一个力度较大的锁，实现比较简单。资源消耗较小，加锁快，会出现死锁。触发锁的冲突的概率大，并发度最大。
INNODB采用的是行级锁：行级锁只对当前操作的数据行进行加锁，是一种力度比较小的锁，行级锁能大大降低数据库操作的冲突，并发度是比较高的，加锁的开销是比较大，加锁比较慢，会出现死锁。