MySQL 存储引擎

MySQL 存储引擎

1.MyISAM存储引擎

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

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

frm(存储表定义)

MYD(MYData,存储数据) 

MYI(MYIndex, 存储索引)

每个MyISAM表都有一个标志，服务器或myisamchk程序在检查 MyISAM数据表时  会对这个标志进行设置。MyISAM 表还有一个标志用来表明该数据表在上次使用后是不是被  正常的关闭了。

MyISAM 表还支持3 种不同的存储格式：

静态(固定长度)表

静态表是默认的存储格式。静态表中的字段都是非可变字段，这样每个记录都是固 定长度的，这种存储方式的优点是存储非常迅速，容易缓存，出现故障容易恢复；缺点是占 用的空间通常比动态表多。静态表在数据存储时会根据列定义的宽度定义补足空格，但是在 访问的时候并不会得到这些空格，这些空格在返回给应用之前已经去掉。同时需要注意：在 某些情况下可能需要返回字段后的空格，而使用这种格式时后面的空格会被自动处理掉。

动 态 表 

动态表包含可变字段，记录不是固定长度的，这样存储的优点是占用空间较少，但是频繁的更新、删除记录会产生碎片，需要定期执行OPTIMIZE TABLE语句或myisamchk -r 命令来改善性能，并且出现故障的时候恢复相对比较困难。

压 缩 表

压缩表由myisamchk 工具创建，占据非常小的空间，因为每条记录都是被单独压缩的，所以只有非常小的访问开支。

2.InnoDB   存储引擎

InnoDB是一个健壮的事务型存储引擎，这种存储引擎已经被很多互联网公司使用，为 用户操作非常大的数据存储提供了一个强大的解决方案。

在以下场景中使 用 InnoDB 存储引擎是最理想的选择：

更新密集的表： InnoDB 存储引擎特别适合处理多重并发的更新请求。

事 务 ：InnoDB存储引擎是支持事务的标准MySQL存储引擎。

自动灾难恢复：与其它存储引擎不同，InnoDB表能够自动从灾难中恢复。

外 键 约 束 ：MySQL支持外键的存储引擎只有InnoDB。 >支持自动增加列AUTO_INCREMENT属性。

MyISAM和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%';)。

3. 关 于MyISAM与 InnoDB 选择使用

MyISAM和 InnoDB是 MySQL数据库提供的两种存储引擎。两者的优劣可谓是各有千 秋 。InnoDB会支持一些关系数据库的高级功能，如事务功能和行级锁，MyISAM不支持。 MyISAM的性能更优，占用的存储空间少。所以，选择何种存储引擎，视具体应用而定。

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

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

有人说MyISAM 只能用于小型应用，其实这只是一种偏见。如果数据量比较大，这是 需要通过升级架构来解决，比如分表分库，而不是单纯地依赖存储引擎。

4.修改默认的存储引擎

(1)通过 alter table 修改。

MySQL>alter table user_info engine=MyISAM;

(2)通过修改my.cnf, 指定默认存储引擎并重启服务。

[root@Mysql    ]#vim  my.cnf

default-storage-engine=InnoDB

( 3 ) 通 过create  table 创建表时指定存储引擎。

MySQL>create table engineTest(id int)engine=MyISAM;

(4) 通 过Mysql_convert_table_format    转化存储引擎。

[root@Mysql /#yum -y install perl-DBI perl-DBD-MySQL

[root@Mysql]#/usr/local/mysql/bin/mysql_convert_table_format --user=root -password='123456'--sock=/tmp/mysql.sock auth