存储引擎就是存储数据,建立索引,更新查询数据等等技术的实现方式 。存储引擎是基于表的,而不是基于库的。所以存储引擎也可被称为表类型。
MySQL5.0支持的存储引擎包含 : InnoDB 、MyISAM 、BDB、MEMORY、MERGE、EXAMPLE、NDB Cluster、ARCHIVE、CSV、BLACKHOLE、FEDERATED等,其中InnoDB和BDB提供事务安全表,其他存储引擎是非事务安全表。
可以通过show engines
show engines;
+--------------------+---------+-------------------------------------------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+-------------------------------------------------------------------------------------------------+--------------+------+------------+
| CSV | YES | Stores tables as CSV files | NO | NO | NO |
| MRG_MyISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| Aria | YES | Crash-safe tables with MyISAM heritage. Used for internal temporary tables and privilege tables | NO | NO | NO |
| MyISAM | YES | Non-transactional engine with good performance and small data footprint | NO | NO | NO |
| SEQUENCE | YES | Generated tables filled with sequential values | YES | NO | YES |
| InnoDB | DEFAULT | Supports transactions, row-level locking, foreign keys and encryption for tables | YES | YES | YES |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
+--------------------+---------+-------------------------------------------------------------------------------------------------+--------------+------+------------+
8 rows in set (0.000 sec)
ySQL5.5之前的默认存储引擎是MyISAM,5.5之后就改为了InnoDB。
各种存储引擎特性
下面重点介绍几种常用的存储引擎, 并对比各个存储引擎之间的区别, 如下表所示 :
特点 | InnoDB | MyISAM | MEMORY | MERGE | NDB |
---|---|---|---|---|---|
存储限制 | 64TB | 有 | 有 | 没有 | 有 |
事务安全 | 支持 | ||||
锁机制 | 行锁(适合高并发) | 表锁 | 表锁 | 表锁 | 行锁 |
B树索引 | 支持 | 支持 | 支持 | 支持 | 支持 |
哈希索引 | 支持 | ||||
全文索引 | 支持(5.6版本之后) | 支持 | |||
集群索引 | 支持 | ||||
数据索引 | 支持 | 支持 | 支持 | ||
索引缓存 | 支持 | 支持 | 支持 | 支持 | 支持 |
数据可压缩 | 支持 | ||||
空间使用 | 高 | 低 | N/A | 低 | 低 |
内存使用 | 高 | 低 | 中等 | 低 | 高 |
批量插入速度 | 低 | 高 | 高 | 高 | 高 |
支持外键 | 支持 |
存储引擎是作用于表级别的,表创建时,可以指定不同的存储引擎,但我们一般建议统一存储引擎为InnoDB。
-- 查看指定库中表的引擎
SHOW TABLE STATUS FROM world;
-- 查看所有表的引擎
SELECT table_schema,table_name,ENGINE FROM information_schema.tables;
InnoDB
InnoDB存储引擎是Mysql的默认存储引擎。InnoDB存储引擎提供了具有提交、回滚、崩溃恢复能力的事务安全。但是对比MyISAM的存储引擎,InnoDB写的处理效率差一些,并且会占用更多的磁盘空间以保留数据和索引。
InnoDB存储引擎不同于其他存储引擎的特点 :
页是InnoDB存储引擎管理数据库的最小磁盘单位。
页类型为B-Tree node的页,存放的即是表中行的实际数据了。
InnoDB中的页大小为16KB,且不可以更改
InnoDB可以将一条记录中的某些数据存储在真正的数据页面之外,即作为行溢出数据。MySQL的varchar数据类型可以存放65535个字节,但
实际只能存储65532个
。同时InnoDB是B+树结构的,因此
每个页中至少应该有两个行记录
,否则失去了B+树的意义,变成了链表,所以一行记录
最大长度的阈值是8098
,如果大于这个值就会将其存到溢出行中。
由于MyISAM并不常用,我也不打算去深究其底层的一些原理和实现。我们在这里简单的对比一下这两个存储引擎的区别就好。我们分点来一点点描述。
- 事务 InnoDB支持事务、回滚、事务安全和奔溃恢复。而MyISAM不支持,但查询的速度要比InnoDB更快
- 主键 InnoDB规定,如果没有设置主键,就自动的生成一个6字节的主键,而MyISAM允许没有任何索引和主键的存在,索引就是行的地址
- 外键 InnoDB支持外键,而MyISAM不支持
- 表锁 InnoDB支持行锁和表锁,而MyISAM只支持表锁
- 全文索引 InnoDB不支持全文索引,但是可以用插件来实现相应的功能,而MyISAM是本身就支持全本索引
- 行数 InnoDB获取行数时,需要扫全表。而MyISAM保存了当前表的总行数,直接读取即可。
所以,简单总结一下,MyISAM只适用于查询大于更新的场景,如果你的系统查询的情况占绝大多数(例如报表系统)就可以使用MyISAM来存储,除此之外,都建议使用InnoDB。