一、MySQL 数据库引擎简介

1.1 ISAM 和 MyISAM

ISAM 设计之时就考虑到数据库被查询的次数要远大于更新的次数。因此,ISAM 执行读取操作的速度很快,而且不占用大量的内存和存储资源。ISAM 的两个主要不足之处在于,它不支持事务处理,也不能够容错。如果你的硬盘崩溃了,那么数据文件就无法恢复了。
注意:使用 ISAM 注意点:必须经常备份所有实时数据。

MyISAM 是 ISAM 的 扩展,增加了索引和字段管理的大量功能,还提供了两个工具(用来修复数据库文件的 MyISAMCHK 工具和用来恢复浪费空间的 MyISAMPACK 工具)。支持了表级锁,但是比较浪费空间。
注意:MyISAM 格式的一个重要缺陷就是不能在表损坏后恢复数据。

MyISAM 引擎使用注意:必须经常使用 Optimize Table 命令清理空间;必须经常备份所 有实时数据。工具有用来修复数据库文件的 MyISAMCHK 工具和用来恢复浪费空间的 MyISAMPACK 工具。不支持事务。数据越多,写操作效率越低。因为要维护数据和索引信息。

(索引列越多,相对效率月底。)

如果使用该数据库引擎,会生成三个文件:

  • .frm:表结构信息

  • .MYD:数据文件

  • .MYI:表的索引信息

1.2 InnoDB

InnoDB 比 ISAM 和 MyISAM 引擎慢一些,但是 InnoDB 包括了对事务处理和外键的支持。InnoDB 处理巨大数据量时性能较高,支持行锁,支持 InnoDB 类型的表与其它 MySQL 的表的类型混合起来,甚至在同一个查询中也可以混合。

MySQL 官方对 InnoDB 是这样解释的:InnoDB 给 MySQL 提供了具有提交、回滚和崩溃恢复能力的事务安全(ACID 兼容)存储引擎。InnoDB 锁定在行级并且也在 SELECT 语句提供一个 Oracle 风格一致的非锁定读,这些特色增加了多用户部署的性能。

InnoDB 存储引擎被完全与 MySQL 服务器整合,InnoDB 存储引擎为在主内存中缓存数据和索引而维持它自己的缓冲池。InnoDB 存储它的表&索引在一个表空间中,表空间可以包含数个文件(或原始磁盘分区)。这与 MyISAM 表不同,比如在 MyISAM 表中每个表被存在分离的文件中。InnoDB 表可以是任何尺寸,即使在文件尺寸被限制为 2GB 的操作系统上。在 MySQL5.7 版本中,InnoDB 存储引擎管理的数据文件为两个:分别是 frm,idb 文件。

InnoDB 特点:

  • 支持事务

  • 数据多版本读取(InnoDB+MyISAM+ISAM)

  • 锁定机制的改进

  • 实现外键

1.3 innodb 与 myisam 区别

  1. InnoDB 支持事务,MyISAM 不支持,对于 InnoDB 每一条 SQL 语言都默认封装成事务, 自动提交,这样会影响速度,所以最好把多条 SQL 语言放在 begin transaction 和 commit 之 间,组成一个事务;

  2. InnoDB 支持外键,而 MyISAM 不支持。对一个包含外键的 InnoDB 表转为 MYISAM 会失败;

  3. InnoDB 是聚集索引,数据文件是和索引绑在一起的,必须要有主键,通过主键索引效率很高。但是辅助索引需要两次查询,先查询到主键,然后再通过主键查询到数据。因此, 主键不应该过大,因为主键太大,其他索引也都会很大。而 MyISAM 是非聚集索引,数据文件是分离的,索引保存的是数据文件的指针。主键索引和辅助索引是独立的。

  4. InnoDB 不保存表的具体行数,执行 select count(*) from table 时需要全表扫描。而 MyISAM 用一个变量保存了整个表的行数,执行上述语句时只需要读出该变量即可,速度很快;

  5. Innodb 不支持全文索引,而 MyISAM 支持全文索引,查询效率上 MyISAM 要高;

1.4 如何选择 innodb 与 myisam

  1. 是否要支持事务,如果要请选择 innodb,如果不需要可以考虑 MyISAM

  2. 如果表中绝大多数都只是读查询,可以考虑 MyISAM,如果既有读写也挺频繁,请使用 InnoDB。

  3. 系统崩溃后,MyISAM 恢复起来更困难,能否接受;

  4. MySQL5.5 版本开始 Innodb 已经成为 MySQL 的默认引擎(之前是 MyISAM),说明其优势是有目共睹的,如果你不知道用什么,那就用 InnoDB,至少不会差。

二、存储引擎管理

1.1 查看数据库支持的存储引擎

show engines;

1.2 查看数据库当前使用的存储引擎

就是默认引擎是什么。

show variables like '%storage_engine%';

也可以在 MySQL 配置文件中查看。 windows - my.ini。 Linux - my.cnf

1.3 查看数据库表所用的存储引擎

show create table table_name;

1.4 创建表指定存储引擎

create table table_name (column_name column_type) engine = engine_name;

1.5 修改表的存储引擎

alter table table_name engine=engine_name;

1.6 修改默认的存储引擎

在 MySQL 配置文件中修改下述内容:

default-storage-engine=INNODB

MySQL 配置文件:

windows 系统:5.7 版本 my.ini 文件在数据目录中 C:\ProgramData\MySQL\MySQL Server 5.7

Linux 系统:/etc/my.cnf


标题:(11)MySQL数据库引擎简介、引擎管理——MySQL学习笔记
作者:AlgerFan
地址:https://www.algerfan.cn/articles/2019/08/12/1565571467966.html
版权声明:本文为博主原创文章,转载请附上博文链接!

添加新评论