参考链接
mysql在v5.1之前默认的存储引擎是【myisam】,在此之后的mysql的默认存储引擎是innodb
常用命令
- 查看mysql的 默认存储引擎
show variables like "%engine%"
+---------------------------------+---------------+
| Variable_name | Value |
+---------------------------------+---------------+
| default_storage_engine | InnoDB |
| default_tmp_storage_engine | InnoDB |
| disabled_storage_engines | |
| internal_tmp_mem_storage_engine | TempTable |
| secondary_engine_cost_threshold | 100000.000000 |
| use_secondary_engine | ON |
+---------------------------------+---------------+
- -- 查看mysql的 支持的 存储引擎
show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
- 全文索引(不同的引擎有不同的表现，min，和max)
show variables like "ft%";
+--------------------------+----------------+
| Variable_name | Value |
+--------------------------+----------------+
| ft_boolean_syntax | + -><()~*:""&| |
| ft_max_word_len | 84 |
| ft_min_word_len | 4 |
| ft_query_expansion_limit | 20 |
| ft_stopword_file | (built-in) |
+--------------------------+----------------+
区别
- 事务安全
myisam是非事务安全的，而innodb是支持事务安全的
- 锁的力度
myisam的锁的力度是表级别的，而innodb的锁的力度是 行级别
- 全文索引
myisam支持全文类型索引，而innodb（5.6之前）不支持全文索引。
- 外键
myisam不支持外键，innodb支持
- 记录总行数
myisam保存了记录的总行数，innodb需要遍历整张表，
- 删除（delete）
myisam则会重建表，innodb则一行一行删除（如果删除全部，则可以使用 truncate table xxxx）
- 缓存
myisam仅仅缓存索引，innodb不仅缓存索引还缓存数据。
- 数据恢复
myisam奔溃后无法快速恢复，innodb有一套完善的恢复机制（redolog + binlog）
