MySQL存储引擎

一、存储引擎概述

1.什么是存储引擎

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

2.MySQL5.7支持的存储引擎

        MySQL 支持多种类型的数据库引擎，可分别根据各个引擎的功能和特性为不同的数据库处理任务提供各自不同的适应性和灵活性。在 MySQL 中，可以利用 SHOW ENGINES 语句来显示可用的数据库引擎和默认引擎。
        MySQL 提供了多个不同的存储引擎，包括处理事务安全表的引擎和处理非事务安全表的引擎。在 MySQL 中，不需要在整个服务器中使用同一种存储引擎，针对具体的要求，可以对每一个表使用不同的存储引擎。
        MySQL 5.7支持的存储引擎有 InnoDB、MyISAM、Memory、Merge、Archive、Federated、CSV、BLACKHOLE 等。可以使用SHOW ENGINES语句查看系统所支持的引擎类型。

二、操作存储引擎

 1.MySQL默认存储引擎

        InnoDB 是系统的默认引擎，支持可靠的事务处理。使用下面的语句可以修改数据库临时的默认存储引擎。

SET default_storage_engine=< 存储引擎名 >

 2.MyISAM存储引擎

        MyISAM 存储引擎不支持事务，也不支持外键，特点是访问速度快，对事务完整性没有要求，以 SELECT、INSERT 为主的应用基本都可以使用这个引擎来创建表。
        每个 MyISAM 表在磁盘上存储成 3 个文件，其中文件名和表名都相同，但是扩展名分别为：         frm(存储表定义)
        MYD(MYData，存储数据)
        MYI(MYIndex，存储索引)
        MyISAM 表还支持 3 种不同的存储格式：
        静态(固定长度)表
        动态表
        压缩表

3.InnoDB存储引擎

        InnoDB 是一个健壮的事务型存储引擎，这种存储引擎已经被很多互联网公司使用，为用户操作非常大的数据存储提供了一个强大的解决方案。MySQL 从 5.5.5 版本开始，默认的存储引擎为 InnoDB。InnoDB 存储引擎还引入了行级锁定和外键约束，在以下场景中使用 InnoDB 存储引擎是最理想的选择：
        更新密集的表：InnoDB 存储引擎特别适合处理多重并发的更新请求。
        事务：InnoDB 存储引擎是支持事务的标准 MySQL 存储引擎。
        自动灾难恢复：与其它存储引擎不同，InnoDB 表能够自动从灾难中恢复。
        外键约束：MySQL 支持外键的存储引擎只有 InnoDB。
        支持自动增加列 AUTO_INCREMENT 属性。
        Innodb的数据文件：
        ibd：数据表的数据文件。
        frm：数据表的元数据。
        opt：存储的是mysql的一些配置信息，如编码、排序的信息等。

4.关于 MyISAM 与 InnoDB 选择使用     

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

5.修改默认的存储引擎

(1)通过alter table修改

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

(2)通过配置文件修改

[root@localhost ~]# vim /etc/my.cnf
[mysqld]
#添加下面语句
default-storage-engine=MyISAM

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

mysql> use auth
mysgl>create table t3 (id int(10),name char(20)) engine=INNODB;

 三、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;

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

(1)设置sql语句结束符

mysql> delimiter $

(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 ;

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

插入（一千万条）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.查询某一范围数据

(1)没有索引的列

select * from tm where name>"wy100" and name <"wy10000000";
...
4 rows in set (0.68 sec)
select * from ti where name>"wy100" and name <"wy10000000";
...
4 rows in set (2.71 sec)

无索引查询MyIsam有优势

(2)有索引的列

select * from tm where id>10 and id<999999;
...
9999988 rows in set (1.88 sec)
select * from ti where id>10 and id<999999;
...
9999988 rows in set (0.65 sec)

有索引查询INNODB有优势