MySQL存储引擎MyISAM和InnoDB

目录

1.1 MySQL存储引擎

1.1.1 什么是存储引擎

1.1.2 MySQL5.7 支持的引擎

1.1.3 如何选择MySQL引擎

1.1.4可以根据以下的原则来选择 MySQL 存储引擎

 1.1.5 MyISAM和InnoDB的区别

1.MyISAM 存储引擎

2.InnoDB 存储引擎

1.1.6 关于 MyISAM 与InnoDB 选择使用

1.1.7.修改默认的存储引擎

(1)通过 alter table 修改。

(2)通过修改 my.cnf，指定默认存储引擎并重启服务。​编辑

(3)通过 create table 创建表时指定存储引擎。

(4)通过 Mysql convert table format 转化存储引擎​编辑

---

1.1 MySQL存储引擎

1.1.1 什么是存储引擎

        数据库存储引擎是数据库底层软件组件，数据库管理系统使用数据引擎进行创建、查询、更新和删除数据操作。不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能，使用不同的存储引擎还可以获得特定的功能。

        现在许多数据库管理系统都支持多种不同的存储引擎。MySQL 的核心就是存储引擎。

1.1.2 MySQL5.7 支持的引擎

        MySQL 支持多种类型的数据库引擎，可分别根据各个引擎的功能和特性为不同的数据库处理任务提供各自不同的适应性和灵活性。在 MySQL 中，可以利用 SHOW ENGINES 语句来显示可用的数据库引擎和默认引擎。

        MySQL 提供了多个不同的存储引擎，包括处理事务安全表的引擎和处理非事务安全表的引擎。在 MySQL 中，不需要在整个服务器中使用同一种存储引擎，针对具体的要求，可以对每一个表使用不同的存储引擎。

        MySQL 5.7 支持的存储引擎有 InnoDB、MyISAM、Memory、Merge、Archive、Federated、CSV、BLACKHOLE 等。可以使用SHOW ENGINES语句查看系统所支持的引擎类型

1.1.3 如何选择MySQL引擎

        

功能	MylSAM	MEMORY	InnoDB	Archive
存储限制	256TB	RAM	64TB	None
支持事务	No	No	Yes	No
支持全文索引	Yes	No	No	No
支持树索引	Yes	Yes	Yes	No
支持哈希索引	No	Yes	No	No
支持数据缓存	No	N/A	Yes	No
支持外键	No	No	Yes	No

提示：

• InnoDB 事务型数据库的首选引擎，支持事务安全表（ACID），支持行锁定和外键。MySQL 5.5.5 之后，InnoDB 作为默认存储引擎。

• MyISAM 是基于 ISAM 的存储引擎，并对其进行扩展，是在 Web、数据仓储和其他应用环境下最常使用的存储引擎之一。MyISAM 拥有较高的插入、查询速度，但不支持事务。

• MEMORY 存储引擎将表中的数据存储到内存中，为查询和引用其他数据提供快速访问。

1.1.4可以根据以下的原则来选择 MySQL 存储引擎

• 如果要提供提交、回滚和恢复的事务安全（ACID 兼容）能力，并要求实现并发控制，InnoDB 是一个很好的选择。
• 如果数据表主要用来插入和查询记录，则 MyISAM 引擎提供较高的处理效率。
• 如果只是临时存放数据，数据量不大，并且不需要较高的数据安全性，可以选择将数据保存在内存的 MEMORY 引擎中，MySQL 中使用该引擎作为临时表，存放查询的中间结果。
• 如果只有 INSERT 和 SELECT 操作，可以选择Archive 引擎，Archive 存储引擎支持高并发的插入操作，但是本身并不是事务安全的。Archive 存储引擎非常适合存储归档数据，如记录日志信息可以使用 Archive 引擎。

        在 MySQL 客户端中，使用以下命令可以査看 MySQL 支持的引擎

mysql>show engines;

 1.1.5 MyISAM和InnoDB的区别

1.MyISAM 存储引擎

        MyISAM 存储引擎不支持事务，也不支持外键，特点是访问速度快，对事务完整性没有要求，以 SELECT、INSERT 为主的应用基本都可以使用这个引擎来创建表。

        每个 MyISAM 表在磁盘上存储成3个文件，其中文件名和表名都相同，但是扩展名分
别为:

• frm(存储表定义)
• MYD(MYData，存储数据)
• MYI(MYIndex，存储索引)

        数据文件和索引文件可以放置在不同的目录，平均分配10，获取更快的速度。要指定数据文件和索引文件的路径，需要在创建表的时候通过 DATA DIRECTORY 和 INDEXDIRECTORY 语句指定，文件路径需要使用绝对路径。

        每个 MyISAM 表都有一个标志，服务器或 myisamchk 程序在检查 MyISAM 数据表时会对这个标志进行设置。MyISAM 表还有一个标志用来表明该数据表在上次使用后是不是被正常的关闭了。如果服务器意外宕机或崩溃，这个标志可以用来判断数据表是否需要检查和修复。如果想让这种检查自动进行，可以在启动服务器时使用--myisam-recover 实现。这会让服务器在每次打开一个MVISAM数据表时自动检查数据表的标志并进行必要的修复处理。MyISAM 类型的表可能会损坏，可以使用 CHECK TABLE 语句来检査 MVISAM 表的健康,并用 REPAIR TABLE 语句修复一个损坏的 MyISAM 表。

        MyISAM 表还支持3种不同的存储格式:

• 静态(固定长度)表
• 动态表
• 压缩表

        其中静态表是默认的存储格式。静态表中的字段都是非可变字段,这样每个记录都是固定长度的，这种存储方式的优点是存储非常迅速，容易缓存，出现故障容易恢复;缺点是占用的空间通常比动态表多。静态表在数据存储时会根据列定义的宽度定义补足空格,但是在访问的时候并不会得到这些空格，这些空格在返回给应用之前已经去掉。同时需要注意:在某些情况下可能需要返回字段后的空格，而使用这种格式时后面的空格会被自动处理掉。

        动态表包含可变字段，记录不是固定长度的，这样存储的优点是占用空间较少，但是频繁的更新、删除记录会产生碎片，需要定期执行 OPTIMIZE TABLE 语句或 myisamchk -r命令来改善性能，并且出现故障的时候恢复相对比较困难。

        压缩表由 myisamchk 工具创建,占据非常小的空间,因为每条记录都是被单独压缩的,所以只有非常小的访问开支。

2.InnoDB 存储引擎

        InnoDB 是一个健壮的事务型存储引擎，这种存储引擎已经被很多互联网公司使用，为用户操作非常大的数据存储提供了一个强大的解决方案。MySQL 从 5.5.5 版本开始，默认的存储引擎为InnoDB。InnoDB 存储引擎还引入了行级锁定和外键约束，在以下场景中使用 InnoDB 存储引擎是最理想的选择:

• 更新密集的表:InnoDB存储引擎特别适合处理多重并发的更新请求
• 事务:InnoDB 存储引擎是支持事务的标准 MySQL存储引擎。
• 自动灾难恢复:与其它存储引擎不同，InnoDB表能够自动从灾难中恢复。
• 外键约束:MySQL 支持外键的存储引擎只有InnoDB。
• 支持自动增加列 AUTO INCREMENT 属性。

        一般来说，如果需要事务支持，并且有较高的并发读取频率，InnoDB 是不错的选择。
而 MyISAM 和 InnoDB 两种存储引擎的区别主要表现在以下几个方面。

• InnoDB 支持事务，MyISAM 不支持，这一点是非常重要的。事务是一种高级的处理方式，如对一些表中的列进行增删改的过程中只要哪个出错还可以回滚还原,而 MyISAM就不可以。
• MyISAM 适合查询、插入为主的应用，InnoDB 适合频繁修改以及涉及到安全性较高的
  应用。
• InnoDB 支持外键，MyISAM 不支持。
• 从 MySQL5.5.5 以后，InnoDB 是默认引擎
• MySQL 从 5.6 版本开始 |nnoDB 引擎才支持 FULLTEXT 类型的索引。
• InnoDB 中不保存表的行数，如 select count(*)from table 时，InnoDB 需要扫描一遍整个表来计算有多少行，但是 MyISAM 只要简单的读出保存好的行数即可。需要注意的是，当 count(*)语句包含 where 条件时 MyISAM 也需要扫描整个表。
• 对于自增长的字段，InnoDB中必须包含只有该字段的索引，但是在 MyISAM 表中可以和其他字段一起建立组合索引。
• 清空整个表时，InnoDB 是一行一行的删除，效率非常慢。MyISAM 则会重建表。
• InnoDB 支持行锁(某些情况下还是锁整表，如 update table set a=1 where user like
  '%lee%')。

1.1.6 关于 MyISAM 与InnoDB 选择使用

        MyISAM 和 InnoDB 是 MySQL数据库提供的两种存储引擎。两者的优劣可谓是各有千秋。InnoDB 会支持一些关系数据库的高级功能，如事务功能和行级锁，MVISAM不支持MyISAM 的性能更优，占用的存储空间少。所以，选择何种存储引警，视具体应用而定。

        (1)如果应用程序一定要使用事务，毫无疑问要选择InnoDB 引擎。但要注意，InnoDB的行级锁是有条件的。在 where 条件没有使用主键时,照样会锁全表。比如 DELETEFROMmytable 这样的删除语句。
        (2)如果应用程序对查询性能要求较高，就要使用MVISAM 了。MVISAM 索引和数据是分开的,而且其索引是压缩的,可以更好地利用内存。所以它的查询性能明显优于 InnoDB.压缩后的索引也能节约一些磁盘空间。

1.1.7.修改默认的存储引擎

(1)通过 alter table 修改。

(2)通过修改 my.cnf，指定默认存储引擎并重启服务。

(3)通过 create table 创建表时指定存储引擎。

(4)通过 Mysql convert table format 转化存储引擎