MySQL中MyISAM与InnoDB区别及选择

MyISAM：

设计目标是快速读取
不支持事务，对一个包含外键的InnoDB表转为MYISAM会失败；回滚将造成不完全回滚，不具有原子性
不支持外键
不支持外键
支持全文搜索
只支持表级锁

不支持故障恢复(也可以理解为和数据库事务相关的)
用一个变量保存了整个表的行数,不带where时，直接返回保存的行数 , 如果带where条件的行数也需要扫描整个表
MyISAM 表被存放在三个文件 。frm 文件存放表格定义。数据文件是MYD (MYData) 。 索引文件是MYI (MYIndex)引伸
跨平台很难直接拷贝
MyISAM中可以使AUTO_INCREMENT类型字段建立联合索引
表格可以被压缩

DELETE FROM table时，InnoDB不会重新建立表，而是一行一行的 删除，效率非常慢。MyISAM则会重建表。

 

MyISAM引擎适用场景

1.不需要事务的操作；
2.插入、更新少，读取频繁；
3.频繁的统计计算。
4.读取频繁,更新少(可能和事务有关 不用像innodb一样生成readview 考虑事务的隔离级别)；

 

InnoDB：

设计目标是处理大数量数据时提供高性能的服务，它在运行时会在内存中建立缓冲池，用于缓冲数据和索引。
支持事务处理、ACID事务特性
不加锁读取
支持外键
支持行锁

可以利用事务日志进行数据恢复
不支持FULLTEXT类型的索引
不保存表的具体行数，需要扫描表来计算有多少行
InnoDB 把数据和索引存放在表空间里面(如果使用共享表空间，那么所有表的数据文件和索引文件都保存在一个表空间里，一个表空间可以有多个文件，通过innodb_data_file_path和innodb_data_home_dir参数设置共享表空间的位置和名字，一般共享表空间的名字叫ibdata1-n。
如果使用多表空间，那么每个表都有一个表空间文件用于存储每个表的数据和索引，文件名以表名开头，以.ibd为扩展名)
跨平台可直接拷贝使用
InnoDB中必须包含AUTO_INCREMENT类型字段的索引
表格很难被压缩   

Innodb引擎适用场景

1.需要事务的操作；
2.更新数据需要使用行级锁；
3.大数据量读写,可以提高多用户并发操作的性能；
4.大型互联网应用。

 

选择：
因为MyISAM相对简单所以在效率上要优于InnoDB.如果系统读多，写少。对原子性要求低。那么MyISAM最好的选择。且MyISAM恢复速度快。可直接用备份覆盖恢复。如果你的应用程序对查询性能要求较高，就要使用MyISAM了。MyISAM索引和数据是分开的，而且其索引是压缩的，可以更好地利用内存。所以它的查询性能明显优于INNODB。压缩后的索引也能节约一些磁盘空间。MyISAM拥有全文索引的功能，这可以极大地优化LIKE查询的效率。
如果系统读少，写多的时候，尤其是并发写入高的时候。InnoDB就是首选了。

 

myisam生成主键的时候 会有一个变量记录主键的最大值及时重启了也不会受影响,innodb中没有一个变量记录最大值 因为mysql服务重启后会从记录里面找到最大值+1当成主键的下一个值

 

MySQL之MyISAM存储引擎压缩表

一、介绍

MySQL的MyISAM存储引擎支持压缩表空间，压缩后的表空间会减少，但是压缩后的表是只读的，不能插入和更新数据，如果需要更新，则需要解压后更新，再压缩 。

二、案例

1.创建数据库，创建表并指定存储引擎为MyISAM，字符集为utf8

1 2 3 4 5

create database myisamdb; use myisamdb; create table myisam (id int unsigned auto_increment primary key, name varchar(20) not null )engine=myisam charset=utf8;

2.将数据插入到表myisam表中

1 2	insert into myisam(name) values('联想tinkpad'),('Dell外星人'), ('雷神玄武'),('mac pro');

3.使用蠕虫复制将myisam表空间迅速膨胀

1 2 3

mysql> insert into myisam select null,name from myisam; Query OK, 262144 rows affected (1.00 sec) Records: 262144  Duplicates: 0  Warnings: 0

4.查看压缩前的myisam表空间大小

1 2 3 4

[root@ping myisamdb]# ls -lh myisam* -rw-rw---- 1 mysql mysql 8.4K 2月  12 20:00 myisam.frm -rw-rw---- 1 mysql mysql  12M 2月  12 20:06 myisam.MYD -rw-rw---- 1 mysql mysql 5.2M 2月  12 20:06 myisam.MYI

5.使用myisampack命令将myisam表空间进行压缩 

1 2 3 4 5 6

[root@ping myisamdb]# myisampack  myisam Compressing myisam.MYD: (524288 records) - Calculating statistics - Compressing file 47.89%     Remember to run myisamchk -rq on compressed table

6.查看压缩后的myisam表空间大小

1 2 3 4

[root@ping myisamdb]# ls -lh myisam* -rw-rw---- 1 mysql mysql 8.4K 2月  12 20:00 myisam.frm  #表结构文件 -rw-rw---- 1 mysql mysql 6.0M 2月  12 20:06 myisam.MYD  #表空间文件 -rw-rw---- 1 mysql mysql 1.0K 2月  12 20:07 myisam.MYI  #表索引文件

　　

7.由于压缩后表空间会改变，导致索引找不到记录对应的索引位置，所以要恢复索引

1 2 3 4 5

[root@ping myisamdb]# myisamchk -rq myisam - check record delete-chain - recovering (with sort) MyISAM-table 'myisam' Data records: 524288 - Fixing index 1

8.查看恢复的索引文件大小

1 2 3 4

[root@ping myisamdb]# ls -lh myisam* -rw-rw---- 1 mysql mysql 8.4K 2月  12 20:00 myisam.frm -rw-rw---- 1 mysql mysql 6.0M 2月  12 20:06 myisam.MYD -rw-rw---- 1 mysql mysql 5.1M 2月  12 20:08 myisam.MYI

转载至:   https://www.cnblogs.com/pingzhe/p/8449348.html