什么是存储引擎?
存储引擎是指表的类型,数据库的存储类型决定了表在计算机中的存储方式, 用户可以根据不同的存储方式,是否进行事务处理等来选择合适的存储引擎。
查看数据库支持的存储引擎:
mysql>SHOW ENGINES;+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support |Comment| Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| FEDERATED | NO |Federated MySQL storage engine| NULL | NULL | NULL |
| MRG_MYISAM | YES | Collection ofidentical 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 ENGINES\G;*************************** 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: Collectionofidentical 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 toit 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, storedin memory, useful for temporarytables
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:DEFAULTComment: Supports transactions, row-level locking, and foreignkeys
Transactions: YES
XA: YES
Savepoints: YES*************************** 9. row ***************************Engine: PERFORMANCE_SCHEMA
Support: YES
Comment: PerformanceSchemaTransactions: NO
XA: NO
Savepoints: NO9 rows in set (0.00sec)
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. 断电数据就没了, 不能建立太大的表
同一个数据库中可以使用多种存储引擎。