MySQL存储引擎 INNODB和MYISAM

存储引擎概述

什么是存储引擎

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

MySQL5.7支持的存储引擎

InnoDB、MyISAM、Memory、Merge、Archive、Federated、CSV、BLACKHOLE等。使用show engines语句可以查看系统支持的引擎类型

功能	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的默认存储引擎  

MyISAM能够为数据表的插入和查询记录提供较高的处理效率

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

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

操作存储引擎

查看mysql支持的存储引擎

show engines；

MyISAM 存储引擎

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

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

（1）frm(存储表定义)

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

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

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

（1）静态(固定长度)表

（2）动态表

（3）压缩表

InnoDB存储引擎

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

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

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

（3）自动灾难恢复：与其它存储引擎不同，InnoDB 表能够自动从灾难中恢复。

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

（5）支持自动增加列 AUTO_INCREMENT 属性。

Innodb的数据文件：

ibd：数据表的数据文件

frm：数据表的元数据

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

修改默认的存储引擎

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

创建表，并查看默认用的存储引擎
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

通过 alter table 修改

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

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

[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

临时修改默认存储引擎 

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.插入一千万数据，来比较两个存储引擎的存储效率

创建两个存储过程

mysql> create procedure insertm()
begin
set @i=1;
while @i<=10000000
do
insert into tm(name) values(concat("wy",@i));
set @i=@i+1;
end while;
end
$


mysql> create procedure inserti()
begin
set @i=1;
while @i<=10000000
do
insert into ti(name) values(concat("wy",@i));
set @i=@i+1;
end while;
end
$

mysql> delimiter ;

利用存储过程向两个表添加数据

插入（一千万条）MyIsam存储引擎的表中的时间如下：

mysql> call insertm;

Query OK, 0 rows affected (1 min 49.74 sec)


插入（一千万条）InnoDB存储引擎的表中的时间如下：

 mysql> call inserti;

Query OK, 0 rows affected (13 min 32.96 sec)


根据结果得出：MyIsam存储引擎在写入方面有优势

3.查询数据总数目

InnoDB的SQL语句的分析：
mysql> desc select count(*) from ti\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: ti
   partitions: NULL
         type: index
possible_keys: NULL
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 9732352
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.00 sec)

下面是MyIsam（他的数据存储在其他的表中所以这里是没有影响行数的）的SQL语句的分析：
mysql> desc select count(*) from tm\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: NULL
   partitions: NULL
         type: NULL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
     filtered: NULL
        Extra: Select tables optimized away
1 row in set, 1 warning (0.00 sec)

4.查询某一范围的数据

没有索引的列

mysql> select * from tm where name>"zhangsan100" and name<"zhangsan10000000";
+---------+-----------------+
| id      | name            |
+---------+-----------------+
|    1000 | zhangsan1000    |
|   10000 | zhangsan10000   |
|  100000 | zhangsan100000  |
| 1000000 | zhangsan1000000 |
+---------+-----------------+
4 rows in set (1.69 sec)



mysql> select * from ti where name>"zhangsan100" and name<"zhangsan10000000";
+---------+-----------------+
| id      | name            |
+---------+-----------------+
|    1000 | zhangsan1000    |
|   10000 | zhangsan10000   |
|  100000 | zhangsan100000  |
| 1000000 | zhangsan1000000 |
+---------+-----------------+
4 rows in set (2.57 sec)


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


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

无索引查询MyIsam有优势

有索引的列

mysql> select * from tm where id>10 and id<999999;

999988 rows in set (1.96 sec)


mysql> select * from ti where id>10 and id<999999;

999988 rows in set (0.52 sec)

有索引查询InnoDB有优势