数据库存储引擎innodb与myisam

	innodb	myisam
其它区别	事物-索引-外键-主键-锁-内存利用率-有崩溃恢复能力	插入快-压缩查询-行数-全文索引，易产生碎片
索引区别	存储文件：frm(表定义文件)、ibd(数据文件) B+树的叶子节点： (1)主键索引的叶子节点=数据文件 (2)辅助索引的叶子节点=主键值	存储文件：frm(表定义文件)、MYI(索引文件)、MYD(数据文件) B+树的叶子节点： (1)主键索引的叶子节点=数据文件的地址指针 (2)辅助索引的叶子节点=数据文件的地址指针

抉择：需要 事务-读写-恢复 的时候，使用innodb

innodb引擎的4大特性：预读，二次写，插入缓冲，自适应哈希索引

 

一、innodb与myisam的区别与取舍、innodb引擎的4大特性

如下是两者的索引图：

两者的相同点：两者都是mysql的常用引擎；两者的索引都是B+树

两者的区别：
事务：InnoDB支持,MyISAM不支持
外键：InnoDB支持,MyISAM不支持
索引类型：InnoDB是聚簇索引(叶子节点存数据)，MyISAM是非聚簇索引(叶子节点存指针)
插入速度：MyISAM批量插入速度快
查询行数：MyISAM的查询性能会比InnoDB强，InnoDB查询表行数要全表扫描，MyISAM存在变量中直接读取
内存空间使用率：InnoDB比MyISAM高
全文索引：MyISAM支持。Innodb不支持,5.7以后才支持
压缩查询：MyISAM表格可以被压缩后进行查询操作
锁级别：InnoDB支持表级锁+(默认)行级锁，而MyISAM支持表级锁
主键：InnoDB必须有，MyISAM可没有
存储文件：Innodb存储文件有frm、ibd，而Myisam是frm、MYD、MYI

PS1：InnoDB的行锁是实现在索引上的，而不是锁在物理行记录上。潜台词是，如果访问没有命中索引，也无法使用行锁，将要退化为表锁。
举个例子：t_user(uid, uname, age, sex) innodb; uid PrimaryKey，无其他索引
update t_user set age=10 where uid=1;              命中索引，行锁。
update t_user set age=10 where uid != 1;           未命中索引，表锁。
update t_user set age=10 where name='shenjian';    无索引，表锁。

PS2：Innodb存储文件有frm、ibd，而Myisam是frm、MYD、MYI
Innodb：frm是表定义文件，ibd是数据文件；Myisam：frm是表定义文件，myd是数据文件，myi是索引文件
InnoDB的B+树主键索引的叶子节点就是数据文件，辅助索引的叶子节点是主键的值；而MyISAM的B+树主键索引和辅助索引的叶子节点都是数据文件的地址指针。

PS3：InnoDB为什么推荐使用自增ID作为主键，而不是uuid？
答：自增ID可以保证每次插入时B+索引是从右边扩展的，可以避免B+树和频繁合并和分裂（对比使用UUID）。如果使用字符串主键和随机主键，会使得数据随机插入，效率比较差。

PS4：innodb引擎的4大特性
插入缓冲(insert buffer),二次写(double write),自适应哈希索引(ahi),预读(read ahead)

两者如何取舍？
1. 是否要支持事务，如果要请选择innodb，如果不需要可以考虑MyISAM；
2. 如果表中绝大多数都只是读查询，可以考虑MyISAM，如果既有读也有写，请使用InnoDB。
3. 系统奔溃后，MyISAM恢复起来更困难，能否接受；
4. MySQL5.5版本开始Innodb已经成为Mysql的默认引擎(之前是MyISAM)，说明其优势是有目共睹的，如果你不知道用什么，那就用InnoDB，至少不会差。

二、表空间碎片

1、碎片产生的原因
（1）表的存储会出现碎片化，每当删除了一行内容，该段空间就会变为空白、被留空，而在一段时间内的大量删除操作，会使这种留空的空间变得比存储列表内容所使用的空间更大；
（2）当执行插入操作时，MySQL会尝试使用空白空间，但如果某个空白空间一直没有被大小合适的数据占用，仍然无法将其彻底占用，就形成了碎片；
（3）当MySQL对数据进行扫描时，它扫描的对象实际是列表的容量需求上限，也就是数据被写入的区域中处于峰值位置的部分；
例如一个表有1万行，每行10字节，会占用10万字节存储空间，执行删除操作，只留一行，实际内容只剩下10字节，但MySQL在读取时，仍看做是10万字节的表进行处理，所以，碎片越多，就会越来越影响查询性能。

2、查看表碎片大小
（1）查看某个表的碎片大小
mysql> show table status like '表名';
结果中’Data_free’列的值就是碎片大小
（2）列出所有已经产生碎片的表
mysql> select table_schema, table_name, data_free, `engine`
from information_schema.tables
where table_schema not in ('information_schema', 'mysql') and data_free>0;

3、清除表碎片
（1）MyISAM表：mysql> optimize table 表名
（2）InnoDB表：mysql> alter table 表名 engine=InnoDB
OPTIMIZE 可以整理数据文件,并重排索引。OPTIMIZE 操作会暂时锁住表,而且数据量越大,耗费的时间也越长
建议做个脚本，定期在访问低谷时间执行，例如每周三凌晨，检查DATA_FREE字段，大于自己认为的警戒值的话，就清理一次。

4、myisam和innodb在使用空间碎片的区别
myisam
1 x
2 x
3 x
删除了三条记录
插入新的三条(会在删除的空间上,从最后开始添加)
1 x 6
2 x 5
3 x 4

innodb
1 x
2 x
3 x
插入新的三条
4
5
6

三、MySQL删除数据什么情况下会释放空间？

1、drop table table_name 立刻释放磁盘空间，不管是Innodb和MyISAM

2、truncate table table_name 立刻释放磁盘空间 ，不管是Innodb和MyISAM 。truncate table其实有点类似于drop table 然后create
只不过这个create table 的过程做了优化，比如表结构文件之前已经有了等等，就不需要重新再搞一把。所以速度上应该是接近drop table的速度。

3、对于delete from table_name:删除表的全部数据，InnoDB不会释放磁盘空间,MyISAM会立刻释放磁盘空间(应该是做了特别处理，也比较合理)

4、对于delete from table_name where xxx带条件的删除，不管是innodb还是MyISAM都不会释放磁盘空间。

5、delete操作以后，使用optimize table table_name会立刻释放磁盘空间。不管是innodb还是myisam。所以要想达到清理数据的目的，请delete以后执行optimize table操作。

6、delete from表以后虽然未释放磁盘空间，但是下次插入数据的时候，仍然可以使用这部分空间。 
做一个实验，产生500W行的一个数据表，删除250W行，然后发现磁盘表文件占用磁盘大小无变化，重新插入250W以后。几乎还是没变化。证明了猜想。(ps:这个是抄过来的，我感觉这里实验的是myisam，因为这里说到插入250W还是没变化)

转载：

InnoDB和MyISAM的区别

MySQL清除表空间碎片

MySQL删除数据什么情况下会释放空间