MySQL存储引擎MyISAM和InnoDB

1.1MySQL存储引擎

1.1.1概述

1、什么是存储引擎

MySQL中的数据用各种不同的技术存储在文件（或内存）中。这些技术中的每一种都使用不同的存储机制、索引技巧、锁定水平并且提供广泛的、不同的功能和能力；通过选择不同的技术，能够获得额外的速度或者功能，从而改善应用的整体性能；

这些不同技术以及配套的相关功能在Mysql中被称为存储引擎（也称作表类型）

补充：

关系数据库表：用于存储和组织信息的数据结构，可以将表理解为由行和列组成的表格，类似于excel电子表格的形式；有的表简单、有的表复杂、有的表不用来存储任何长期数据、有的表读取时非常快，但是插入数据时却很差。

在实际开发过程中中，可能需要各种各样的表，不同的表意味着存储不同类型的数据，数据的处理上也会存在着差异；那么，对于mysql来说，它提供了很多类型的存储引擎（或者说不同的表类型），根据对数据处理的需求，可以选择不同的存储引擎，从而最大限度的利用MySQL强大的功能；

2、存储引擎的类型

Innodb、MyISAM、memory、archive、

查看存储引擎：show engines;

默认存储引擎

 （1）MyISAM存储引擎

》MyISAM存储引擎不支持事务，不支持外键，支持全文索引、B树索引

》特点：访问速度快，对事务完整性没有要求，以select、insert为主的应用基本都可以使用这个引擎来创建表；

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

frm:存储的是表的结构、定义（比如表有多少列、每个列的名字）

MYD：D是数据，表中每一行的记录放到这里

MYI：I是索引，这个文件存放的是索引

 》存储空间：256T

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

每个MyISAM表都有一个标志，服务器或myisam程序在检查MyISAM数据表时会对这个标志进行设置。

MyISAM表还有一个标志来表明该数据表在上次使用后是不是被正常关闭了；如果服务器意外宕机或崩溃，这个标志可以用来判断数据表是否需要检查和修复；如果想让这种检查自动进行，可以在启动服务器时使用--myisam-recover实现。这会让服务器在每次打开一个MyISAM数据表时自动检查数据表的标志并进行必要的修复处理；

MyISAM类型的表可能会损坏，可以使用check table语句来检查MyISAM表的健康，并用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属性

》锁定一行：select * from biao where name='zhangsan' for update

（3）总结

一般来说，如果需要事务支持，并且有较高的并发读取频率，Innodb是不错的选择；

》Innodb：支持事务，MyISAM不支持；事物是一种高级的处理方式，如对一些表中的列进行增删改的过程中只要哪个出错还可以回滚还原，而MyISAM就不可以；

》MyISAM适合查询、插入为主的应用，Innodb适合频繁的修改以及涉及到安全性较高的应用；

》Innodb支持外键，MyISAM不支持；

》从MySQL5.5.5开始，Innodb是默认引擎；

》MySQL从5.6版本开始Innodb引擎才支持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%';）

innodb:     64T              支持事务          不支持全文索引       b树索引    数据缓存     支持外键

myisam:   256T            不支持事务       支持全文索引          b树索引

memory:  与内存有关   不支持事务      不支持全文索引       b树索引     hash索引  临时表

archive： none             不支持事务      不支持全文索引      insert ,select

3、MyISAM和Innodb的选择使用

Innodb会支持一些关系数据库的高级功能，如事物和行级锁，MyISAM不支持；MyISAM的性能更优，占用的存储空间少；

（1）如果应用程序一定要使用事物，选择Innodb引擎；Innodb的行级锁是有条件的，在where条件没有使用主键时，照样会锁全表；如delete from mytable这样的删除语句

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

1.1.2操作

1、登录mysql，创建库、表，查看新创建的库

2、再新创建一个表

注释：在auth库中，再创建一个表，会多两个文件；一个是frm,一个是ibd;而opt是共用的，不会随着新创建的表，而增加，它是该库的一些配置信息，比如说：编码，查询规则等这些信息所存放的地方；frm：数据表的元meta数据（表的结构相关信息，关于表的属性信息）；ibd：核心数据；myisam有专门放置索引文件的文件，而innodb是没有的，索引和数据是在一起的；

3、设置存储引擎

(1)登录

（2）显示auth库中users这个表的信息

注释：同一个库不同的表，可以设置不同的存储引擎；存储引擎是针对表的，可以针对不同的表设置不同的存储引擎；

（3）进入auth库中，改变存储引擎（方式1）

注释：不同的存储引擎，所生成的表的文件的后缀名是不一样的；

           如果设置的有事务相关的技术，改完存储引擎就不能用了，因为innodb支持事务，myisam不支持；

（4）创建表的时候，设置存储引擎（方式2）

注释：大小写不影响

（5）临时设置存储引擎

注释：set设置，只在当前会话内生效；一旦断开，再次连接，就回到配置文件指定的；

（6）比较myisam和innodb处理数据的速度

谁写入快、谁读取快；谁带索引读取快、谁带索引写入快；

读：有索引的和没索引的

》创建库、表

》写测试脚本

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
$

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
$

更改结束符

创建存储过程

向两个表添加10000000行记录（利用存储过程）

更改结束符号

呼叫存储过程

写入：myisam更快；

读取速度

注释：无索引--myisam更快

》有索引

注释：带索引是innodb更快