（mysql）存储引擎MyISAM和InnoDB

存储引擎概述以及MySQL5.7支持的存储引擎

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

//可以使用以下语句显示可用的数据库引擎和默认引擎 mysql> show engines;

• MySQL 5.7 支持的存储引擎有 InnoDB(64T)、MyISAM(256T)、Memory(内存越大存储越多)、Merge、Archive(none)、Federated、CSV、BLACKHOLE
• 不同引擎的优缺点

功能	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 作为默认存储引擎
  • select * from biao where user_name='zhangsan' for update
• MyISAM 是基于 ISAM 的存储引擎，并对其进行扩展，是在 Web、数据仓储和其他应用环境下最常使用的存储引擎之一。MyISAM 拥有较高的插入、查询速度，但不支持事务
• MEMORY 存储引擎将表中的数据存储到内存中，为查询和引用其他数据提供快速访问

一、操作存储引擎

1：MyISAM 存储引擎

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

• 每个 MyISAM 表在磁盘上存储成 3 个文件，其中文件名和表名都相同
  • frm(存储表定义,表的结构信息)
  • MYD(MYData，存储数据)
  • MYI(MYIndex，存储索引)
• MyISAM 表还支持 3 种不同的存储格式
  • 静态(固定长度)表
  • 动态表
  • 压缩表

2：InnoDB 存储引擎

        InnoDB 是一个健壮的事务型存储引擎，这种存储引擎已经被很多互联网公司使用，为 用户操作非常大的数据存储提供了一个强大的解决方案；InnoDB 存储引擎还引入了行级锁定和外键约束

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

• Innodb的数据文件
  • ibd：数据表的数据文件
  • frm：数据表的元数据
  • opt：存储的是mysql的一些配置信息，如编码、排序的信息

MyISAM 与 InnoDB 选择

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

• 如果应用程序对查询性能要求较高，就要使用 MyISAM 了。MyISAM 索引和数据 是分开的，而且其索引是压缩的，可以更好地利用内存

3：修改默认的存储引擎

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

 //通过my.inf配置文件修改
[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
//通过配置文件修改的方法，对以前的老表不会有影响，只会影响以后新创建的表，并且在创建表时没有指定存储引擎的情况

//通过 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  

//通过 Mysql_convert_table_format 转化存储引擎。         
[root@Mysql /]# yum -y install perl-DBl perl-DBD-MySQL
[root@Mysql /]# /usr/local/mysql/bin/mysql_convert_table_format--user=root-password='123456'--sock=/tmp/mysql.sock auth  

二、MyISam和InnoDB实例比较

        1：修改默认的存储引擎

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）创建两个存储过程
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 ;
//备注：
//delimiter ;语句是设置sql语句的结束符为“;”

（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)

        3：查询某一范围的数据

（1）没有索引的列
mysql> select * from tm where name>"wy100" and name <"wy10000000";
mysql> select * from ti where name>"wy100" and name <"wy10000000";
mysql> select * from tm where name="wy9999999";
mysql> select * from ti where name="wy9999999";
（2）有索引的列
//对于使用MyIsam存储引擎的表：
select * from tm where id>10 and id<999999;
//对于使用了InnoDB存储引擎的表：
select * from ti where id>10 and id<999999;