innodb与MyISAM

1.innodb存储引擎介绍

独立表空间：每个表都会生成以独立的文件方式来存储，每个表都一个.frm的描述文件，还有一个.ibd文件。其中这个文件包括了单独一个表的数据及索引内容，默认情况下它的存储在mysql指定的目录下。

独立表空间优缺点

优点：

每个表都有自己独立的表空间；每个表的数据和索引都会存储在各个独立的表空间中；可以实现单表在不同的数据进行迁移；表空间可以回收（除了drop table操作，表空不能自己回收）；drop table 操作自动回收表空间，如果对统计分析或是日值表，删除大量数据后可以通过 ：alter table tablename engin=innodb进行回缩不用的空间；对于使用inodb-plugin的innodb使用truncate table会使用空间收缩。；对于使用独立表空间，不管怎么删除，表空间的碎片都不会太严重。

缺点：

单表增加过大，如超过100G。对于单表增长过大的问题，如果使用共享表空间可以把文件分开，但有同样有一个问题，如果访问的范围过大同样会访问多个文件，一样会比较慢。对于独立表空间也有一个解决办法是：使用分区表，也可以把那个大的表空间移动到别的空间上然后做一个连接。其实从性能上出发，当一个表超过100个G有可能响应也是较慢了，对于独立表空间还容易发现问题早做处理。

共享表空间：某一个数据库所有的表数据，索引文件全部都放在一个文件中，默认这个共享表空间的文件路径在data目录下，默认的文件名为 ibdata1,初始化为10M。

共享表空间优缺点

优点：可以将表空间分成多个文件存放在各个磁盘上（表空间文件大小不受表大小的限制，如一个表可以分布在不同的文件上），数据和文件放在一起方便管理。

缺点：所有的数据和索引存放到一个文件中，将来会是一个很大的文件，虽然可以把一个大文件分成多个小文件，但是多个表及索引在表空间中混合存储，这样对一个表做了大量删除操作后表空间将有大量的空隙，特别是对统计分析、日值系统这类应用最不适合用共享表空间。

如何开启独立表空间？

查看是否开启独产表空间：

mysql> show variables like '%per_table';

±----------------------±------+

| Variable_name | Value |

±----------------------±------+

| innodb_file_per_table | OFF |

±----------------------±------+

设置开启：

在my.cnf文件中[mysqld] 节点下添加innodb_file_per_table=1

或者通过命令：set global innodb_file_per_table=1;

注：

innodb_file_per_table值来进行修改即可，但是对于之前使用过的共享表空间则不会影响，除非手动的去进行修改或者是

innodb_file_per_table=1 为使用独占表空间

innodb_file_per_table=0 为使用共享表空间

修改独占空表空间的数据存储位置

innodb_data_home_dir = “C:\mysql\data”

innodb_log_group_home_dir = “C:\mysql\data”

innodb_data_file_path=ibdata1:10M:autoextend

innodb_file_per_table=1

参数说明:

这个设置配置一个可扩展大小的尺寸为10MB的单独文件，名为ibdata1。没有给出文件的位置，所以默认的是在MySQL的数据目录内。【对数据来进行初始化的设置】

innodb_data_home_dir 代表为数据库文件所存放的目录

innodb_log_group_home_dir 为日志存放目录

innodb_file_per_table 是否使用共享以及独占表空间来

以上的几个参数必须在一起加入。

对于参数一些注意的地方

InnoDB不创建目录，所以在启动服务器之前请确认”所配置的路径目录”的确存在。这对你配置的任何日志文件目录来说也是真实的。使用Unix或DOS的mkdir命令来创建任何必需的目录。

通过把innodb_data_home_dir的值原原本本地部署到数据文件名，并在需要的地方添加斜杠或反斜杠，InnoDB为每个数据文件形成目录路径。

如果innodb_data_home_dir选项根本没有在my.cnf中提到，默认值是“dot”目录 ./，这意思是MySQL数据目录。

（2）数据按照主键顺序存储

插入时做排序工作，效率低。

（3）特定功能

事务、外键约束 ： 都是为了维护数据的完整性。

并发性处理：

innodb擅长处理并发的。因为它使用了行级锁定，只该行锁了，其它行没有锁。

行级锁定：row-level locking，实现了行级锁定，在一定情况下，可以选择行级锁来提升并发性。也支持表级锁定，Innodb会自带锁，不需要我们自己设置。

多版本并发控制, MVCC，效果达到无阻塞读操作。

（4）总结：innodb擅长事务、数据的完整性及高并发处理，不擅长快速插入（插入前要排序，消耗时间）和检索。

2.MyISAM存储引擎介绍

MySQL<= 5.5 MySQL默认的存储引擎。

ISAM：Indexed Sequential Access Method(索引顺序存取方法)的缩写，是一种文件系统。

擅长与处理，高速读与写。

（1）存储方式

数据和索引分别存储于不同的文件中。

（2）数据的存储顺序为插入顺序（没有经过排序）

插入速度快，空间占用量小。

（3）功能

a.全文索引支持。（mysql>=5.6时innodb 也支持）

b.数据的压缩存储。.MYD文件的压缩存储。

压缩前，数据是25600KB：

进行压缩：使用工具 myisamPack完成压缩功能：该工具mysql自带

进入到需要压缩表的数据目录，执行压缩指令 myisampack 表名。配置环境变量。

压缩后：

注意，压缩后，需要重新修复索引：

查看结果，发现现在的数据变成12741KB了，比之前的更小了：

压缩优势：节省磁盘空间，减少磁盘IO开销。特点：压缩后的表变成了只读表，不可写。

如果需要更新数据，则需要先解压后更新。利用工具：myisamchk –unpack 表名 进行解压

解压后，变成了原来的25600KB

刷新表的状态：flush table myisam_2

c.并发性：

仅仅支持表级锁定，不支持高并发。

支持并发插入。写操作中的插入操作，不会阻塞读操作（其他操作）

（4）关于Innodb 和myisam的取舍：

Innodb ：数据完整性，并发性处理，擅长更新，删除。

myisam：高速查询及插入。擅长插入和查询。

具体举例：

那么对于微博项目来看，选择哪一个存储引擎呢？

a.微博主要是插入微博和查询微博列表，较为适合MyISAM；

b.微博在更新微博和删除微博，要少的多，较为适合MyISAM；

c.对数据完整性的需求并没有那么强烈，比如用户删除微博，关联的转播和评论并不要求都做相应的行为，较为适合MyISAM；

那么对于记账财务系统，选择哪一款存储引擎呢？

a.财务系统除了读取和插入，经常要进行数据的修改和删除，较为适合InnoDB；

b.在进行财务变更的时候，如果失败需要回滚必须用到事务，较为适合InnoDB；

c.每个用户的财务数据完整性和同步性非常重要，需要外键支持，否则财务将会混乱，较为适合InnoDB。