MySQL存储引擎MyISAM和InnoDB

精选原创

当前无昵称 2024-06-28 11:43:44 博主文章分类：数据库管理与高可用 ©著作权

文章标签 存储引擎 mysql MySQL 文章分类 云服务云计算 私藏项目实操分享

©著作权归作者所有：来自51CTO博客作者当前无昵称的原创作品，请联系作者获取转载授权，否则将追究法律责任

一、存储引擎概述

1：什么是存储引擎

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

2：支持的存储引擎

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

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

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

3：如何选择MySQL存储引擎

功能	MyISAM	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 存储引擎将表中的数据存储到内存中，为查询和引用其他数据提供快速访问。

4:根据以下原则来选择MySQL存储引擎

如果要提供提交、回滚和恢复的事务安全（ACID 兼容）能力，并要求实现并发控制，InnoDB 是一个很好的选择。

如果数据表主要用来插入和查询记录，则MySQL引擎提供较高的处理效率。

如果只是临时存放数据，数据量不大，并且不需要较高的数据安全性，可以选择将数据保存在内存的 MEMORY 引擎中，MySQL 中使用该引擎作为临时表，存放查询的中间结果。

如果只有 INSERT 和 SELECT 操作，可以选择Archive 引擎，Archive 存储引擎支持高并发的插入操作，但是本身并不是事务安全的。Archive 存储引擎非常适合存储归档数据，如记录日志信息可以使用 Archive 引擎。

5：MySQL默认存储引擎

InnoDB 是系统的默认引擎，支持可靠的事务处理。

使用下面的语句可以修改数据库临时的默认存储引擎：

SET default_storage_engine=< 存储引擎名 >

也可以在创建表的时候指定存储引擎，还可以使用alter语句进行修改：

alter table 表名 engine=存储引擎；

二、操作存储引擎

1：查看MySQL支持的存储引擎

可以使用show engines命令进行查询：

MySQL存储引擎MyISAM和InnoDB_MySQL

2、MyISAM存储引擎

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

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

（1）frm(存储表定义)

（2）MYD(MYData，存储数据)

（3）MYI(MYIndex，存储索引)

3：InnoDB存储引擎

InnoDB 是一个健壮的事务型存储引擎，这种存储引擎已经被很多互联网公司使用，为用户操作非常大的数据存储提供了一个强大的解决方案。从5.5版本开始，默认存储引擎为InnoDB。

在以下场景中使用InnoDB存储引擎是最理想的选择：

更新密集的表：InnoDB存储引擎特别适合处理多重并发的更新请求。

事务：InnoDB存储引擎是支持事务的标准MySQL存储引擎。

自动灾难恢复：InnoDB表能够自动从灾难中恢复。

外键约束：MySQL支持外键的存储引擎只有InnoDB。

支持自动增加列 AUTO_INCREMENT属性。

InnoDB的数据文件：

MySQL存储引擎MyISAM和InnoDB_mysql_02

ibd：数据表的数据文件

frm：数据表的元数据

opt：存储的是MySQL的一些配置信息，如编码、排序的信息等。

4：关于MyISAM 与InnoDB 选择使用

（1）如果应用程序一定要使用事务，毫无疑问要选择InnoDB 引擎。但要注意，InnoDB 的行级锁是有条件的。在where 条件没有使用主键时，照样会锁全表。比如DELETE FROM mytable 这样的删除语句。

（2）如果应用程序对查询性能要求较高，就要使用MyISAM 了。MyISAM 索引和数据是分开的，而且其索引是压缩的，可以更好地利用内存。所以它的查询性能明显优于InnoDB。压缩后的索引也能节约一些磁盘空间

5：修改默认的存储引擎

（1）创建表，并查看默认用的存储引擎

mysql> create database auth;
mysql> use auth  
mysql> CREATE TABLE t1 (user_name CHAR(16), user_passwd CHAR(48));
mysql> show table status from auth where name='t1'\G

查看表的默认引擎语法格式如下：

show table status from 库名 where='表名'

（2）通过alter table 修改

mysql> alter table t1 engine=MyISAM;
mysql> show table status from auth where name='t1'\G

（3）通过配置文件修改

[root@localhost ~]# vim /etc/my.cnf
[mysqld]
#添加下面语句
default-storage-engine=MyISAM
[root@localhost ~]# systemctl restart mysqld
[root@localhost ~]# mysql -uroot -ppwd123
mysql> use auth
mysql> CREATE TABLE t2 (user_name CHAR(16), user_passwd CHAR(48));
mysql> show table status from auth where name='t2'\G

通过配置文件修改的方法，对以前的老表不会有影响，只会影响以后新创建的表，并且在创建表时没有指定存储引擎的情况。

（4）通过create table 创建表时指定存储引擎

mysql> use auth
mysql> create table t3 (id int(10),name char(20)) engine=INNODB;
mysql> show table status from auth where name='t3'\G

（5）临时修改默认的存储引擎

SET default_storage_engine=< 存储引擎名 >

三：MyISam和InnoDB实例比较

1：创建两张表分别以MyIsam和InnoDB作为存储引擎。

create database test;
use test;
create table tm(id int(20) primary key auto_increment,name char(30)) engine=myisam;
create table ti(id int(20) primary key auto_increment,name char(30)) engine=innodb;
mysql> show create table tm\G
mysql> show create table ti\G

两张表内容是一致的但是存储引擎不一样，下面我们从插入数据开始进行测试比较。

2：插入一千万数据，来比较两个存储引擎的存储效率

这里当然不能手动的插入，创建一个存储过程插入一千万的数据。

（1）设置sql语句结束符

mysql> delimiter $

delimiter $语句是设置sql语句的结束符为“$”

（2）创建两个存储过程

存储引擎为MyISAM:

Mysql> create procedure insertm()
begin
set @i=1;
while @i<=10000000
do
insert into tm(name) values(concat("zhangsan",@i));
set @i=@i+1;
end while;
end
$
mysql> delimiter ; mysql> call inserti;
Query OK, 0 rows affected (13 min 32.96 sec)
插入（一千万条）MyIsam存储引擎的表中的时间如下：
mysql> call insertm;
Query OK, 0 rows affected (1 min 49.74 sec)

存储引擎为InnoDB:

mysql> create procedure inserti()
begin
set @i=1;
while @i<=10000000
do
insert into ti(name) values(concat("zhangsan",@i));
set @i=@i+1;
end while;
end
$
mysql> delimiter ;设置sql语句的结束符为“；”
插入（一千万条）InnoDB存储引擎的表中的时间如下：
 mysql> call inserti;
Query OK, 0 rows affected (13 min 32.96 sec)

比较结果：MyIsam存储引擎在写入方面有优势

（3）查询某一范围的数据

MyISAM存储引擎：

mysql> select * from tm where name>"zhangsan100" and name <"zhangsan10000000";
+---------+-----------+
| id      | name      |
+---------+-----------+
|    1000 | wy1000    |
|   10000 | wy10000   |
|  100000 | wy100000  |
| 1000000 | wy1000000 |
+---------+-----------+
4 rows in set (0.68 sec)

InnoDB存储引擎：

mysql> select * from ti where name>"zhangsan100" and name <"zhangsan10000000";
+---------+-----------+
| id      | name      |
+---------+-----------+
|    1000 | wy1000    |
|   10000 | wy10000   |
|  100000 | wy100000  |
| 1000000 | wy1000000 |
+---------+-----------+
4 rows in set (2.71 sec)

MyISAM存储引擎：

mysql> select * from tm where name="wy9999999";
+---------+-----------+
| id      | name      |
+---------+-----------+
| 9999999 | wy9999999 |
+---------+-----------+
1 row in set (0.42 sec)

InnoDB存储引擎：

mysql> select * from ti where name="wy9999999";
+---------+-----------+
| id      | name      |
+---------+-----------+
| 9999999 | wy9999999 |
+---------+-----------+
1 row in set (2.38 sec)

无索引查询MyIsam有优势

（4）有索引的列

对于使用MyIsam存储引擎的表：
select * from tm where id>10 and id<999999;
执行时间：
9999988 rows in set (1.88sec)
对于使用了InnoDB存储引擎的表：
select * from ti where id>10 and id<999999;
执行时间：
9999988 rows in set (0.65 sec)

有索引查询INNODB有优势