MySQL系列(7): 存储引擎介绍
MySQL 默认支持多种存储引擎,提供了灵活的存储,以支持不同场景的应用。
MySQL 8.0 版本,默认使用 InnoDB
存储引擎,即建表语句(CREATE TABLE
) 不带 ENGINE=子句会创建 InnoDB 表。
关于 MySQL 存储引擎,可以单独写成一个系列文章,后续有时间列入计划中。The InnoDB Storage Engine - 官网,lternative Storage Engines - 替代引擎
查看数据库提供支持的存储引擎命令:
1 | mysql> SHOW ENGINES; |
查看存储引擎
- MySQL 5.5 之前默认的存储引擎是
MyISAM
,之后改为InnoDB
。1
2
3
4-- 查看数据库支持的存储引擎
SHOW ENGINES;
-- 查看当前默认的存储引擎
SHOW VARIABLES LIKE '%storage_engine%'; - 在建表时,可在尾部指定存储引擎和字符编码,
1
2
3
4
5
6
7-- 建表时指定存储引擎
CREATE TABLE userinfo (
id INT(11) NOT NULL AUTO_INCREMENT,
NAME VARCHAR(255) DEFAULT NULL,
PASSWORD VARCHAR(255) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=INNODB DEFAULT CHARSET=utf8 - 通过查看表创建的信息,在集息尾部有显示存储引擎和字符编码。
1
2
3
4-- 查看数据库表状态
SHOW TABLE STATUS FROM DB_NAME WHERE NAME='tb_name';
-- 查看建表信息
show create table userinfo; - 修改表存储引擎:
InnoDB,MyISAM
1
2-- 修改表的存储引擎
ALTER TABLE tb_name ENGINE=MYISAM;
MySQL 存储引擎及特性
MySQL 在设计时就考虑了不同的应用场景,相应的提供了多种存储引擎。 下表汇总了 MySQL 提供的存储引擎支持特性的对比。
特点 | MyISAM | Memory | InnoDB | Archive | NDB |
---|---|---|---|---|---|
B-tree 索引 | yes | yes | yes | no | no |
备份/时间点恢复 | yes | yes | yes | yes | yes |
集群数据库 | no | no | no | no | yes |
集群索引 | no | no | yes | yes | no |
数据压缩 | yes(note 2) | no | yes | 支持 | 支持 |
HASH索引 | no | N/A | yes | no | yes |
数据加密 | yes(note 3) | yes(note 3) | yes(note 4) | yes(note 3) | yes(note 3) |
外键 | no | no | yes | no | yes(note 5) |
全文搜索索引 | yes | no | yes(note 6) | no | no |
地理空间数据类型 | yes | no | yes | yes | yes |
地理空间数据索引 | yes | no | yes(note 7) | no | no |
Hash 索引 | no | yes | no(note 8) | no | yes |
索引缓存 | yes | N/A | yes | no | yes |
锁粒度 | Table | Table | Row | Row | Row |
MVCC (多版本并发控制) |
no | no | yes | no | no |
复制 | yes | Limited | yes | yes | yes |
存储限制 | 265TB | RAM | 64TB | None | 384EB |
T-tree 索引 | no | no | no | no | yes |
事务 | no | no | yes | no | yes |
更新数据字 典统计信息 |
yes | yes | yes | yes | yes |
备注:
- note 3 由服务器内部的加密函数实现。
- note 4 由服务器内部的加密函数实现;MySQL 5.7 版本和更新版本支持,支持数据静态表空间加密。
- note 5 MySQL 集群 NDB 7.3 版本和更新版本支持。
- note 6 MySQL 5.6 或更新版本,InnoDB 支持全文索引。
- note 7 MySQL 5.7 或更新版本, InnoDB 支持地理空间数据类型的索引。
- note 8 InnoDB 在内部利用哈希索引来实现其自适应哈希索引功能。
InnoDB/MyISAM
区别
MySQL最常用的2种存储引擎InnoDB
和MyISAM
在提供的功能上还是有些区别。
- InnoDB 支持事务安全;MyISAM不支持事务。
- InnoDB 自增列必须是索引。若是组合索引,必须是组合索引第一列;MyISAM 表的自增列可以是组合索引的其它列。
- InnoDB 支持外键约束,MyISAM 不支持,一个包含外键的 InnoDB 表转 MyISAM 表会失败。
- InnoDB 前缀索引的前缀长度可以达到
1000
字节长;MyISAM
前缀索引的前缀长度最长是767
字节长。 - InnoDB 支持 空间列 类型 索引,MyISAM 不支持。
- InnoDB 是聚集索引,数据文件是和索引绑在一起的,必须要有主键,通过主键索引效率很高,跨平台可直接拷贝;MyISAM 是非聚集索引,数据文件是分离的,索引保存的是数据文件的指针,主键索引和辅助索引是独立的,跨平台很难直接拷贝。
- InnoDB 不保存表的具体行数,执行
select count(*) from table
时需要全表扫描;MyISAM用一个变量保存了整个表的行数,执行上述语句时只需要读出该变量即可,速度很快。如果COUNT...WHERE
后面带条件,则两则执行是一样的。
选择
- 读操作多,可选用 MyISAM。
- 写操作多,用 InnoDB。
MySQL系列(7): 存储引擎介绍