什么是存储引擎?
存储引擎是指表的类型,数据库的存储类型决定了表在计算机中的存储方式, 用户可以根据不同的存储方式,是否进行事务处理等来选择合适的存储引擎。
查看数据库支持的存储引擎:
mysql> SHOW ENGINES; +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ | Engine | Support | Comment | Transactions | XA | Savepoints | +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ | FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL | | MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO | | MyISAM | YES | MyISAM storage engine | NO | NO | NO | | BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO | | CSV | YES | CSV storage engine | NO | NO | NO | | MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO | | ARCHIVE | YES | Archive storage engine | NO | NO | NO | | InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES | | PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO | +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ 9 rows in set (0.03 sec)
这样看起来很乱,可以加上G, 让它显示的好看一些:
mysql> SHOW ENGINESG; *************************** 1. row *************************** Engine: FEDERATED #存储引擎的名称 Support: NO #NO代表不支持 Comment: Federated MySQL storage engine #表示对存储引擎的特点 Transactions: NULL #是否支持事务处理,YES代表支持 XA: NULL #是否是分布式处理规范,YES代表支持 Savepoints: NULL #是否支持保存点,YES代表支持 *************************** 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.00 sec) ERROR: No query specified
查看mysql支持的存储引擎:
mysql> SHOW VARIABLES LIKE 'have%'; +----------------------+----------+ | Variable_name | Value | +----------------------+----------+ | have_compress | YES | | have_crypt | NO | | have_csv | YES | | have_dynamic_loading | YES | | have_geometry | YES | | have_innodb | YES | | have_ndbcluster | NO | | have_openssl | DISABLED | | have_partitioning | YES | | have_profiling | YES | | have_query_cache | YES | | have_rtree_keys | YES | | have_ssl | DISABLED | | have_symlink | YES | +----------------------+----------+ 14 rows in set (0.00 sec)
查看默认使用的存储引擎:
mysql> SHOW VARIABLES LIKE 'storage_engine'; +----------------+--------+ | Variable_name | Value | +----------------+--------+ | storage_engine | InnoDB | +----------------+--------+ 1 row in set (0.00 sec)
mysql常用存储引擎及特点:
InnoDB存储引擎:
1. 提供了事务,包括回滚,修复,多版本并发控制的事务安全
2. 支持外键
3. 表结构存储在.frm文件中
4. 数据和索引存储在InnoDB_datahome_dir和InnoDB_datafile_path定义的表空间中
5. 读写效率低,占用的数据空间比较大
mysam存储引擎:
1. 表存储成三个文件,文件名与表名相同 .frm 表结构 .myd 数据 .myi 索引
2. 三种方式:静态型,动态型, 压缩型
3. 占用磁盘空间小,处理速度快,不支持事务,没有事务安全性及并发性处理
memory存储引擎:
1. 使用内存中的数据创建的表,所有数据也都存在内存中
2. 每个表实际对应一个磁盘文件,文件名为.frm, 只存储表结构,数据在内存中
3. 提高表的处理效率,速度快
4. 需要的内存空间大,如果不使用了,需要删除以释放内存
5. 断电数据就没了, 不能建立太大的表
同一个数据库中可以使用多种存储引擎。