MySQL存储引擎

一：概述

1.什么是存储引擎

数据库存储引擎是数据库底层软件组件，数据库管理系统使用数据引擎进行创建、查询、更新和删除数据操作。不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能，使用不同的存储引擎还可以获得特定的功能。
现在许多数据库管理系统都支持多种不同的存储引擎。MySQL 的核心就是存储引擎

在MySQL 客户端中， 使用以下命令可以查看MySQL 支持的引擎。

mysql>show engines;

2.各种MySQL存储引擎的特点

功能	MyISAM	MEMORY	InnoDB	Archive
存储限制	256TB	RAM	64TB	none
支持事务	No	No	Yes	No
支持全文索引	Yes	No	No	No
支持树索引	Yes	Yes	Yes	No
支持哈希索引	No	Yes	No	No
支持数据缓存	No	N/A	Yes	No
支持外键	No	No	Yes	No

  可以根据以下的原则来选择 MySQL 存储引擎

如果要提供提交、回滚和恢复的事务安全（ACID 兼容）能力，并要求实现并发控制，InnoDB 是一个很好的选择。
如果数据表主要用来插入和查询记录，则 MyISAM 引擎提供较高的处理效率。
如果只是临时存放数据，数据量不大，并且不需要较高的数据安全性，可以选择将数据保存在内存的 MEMORY 引擎中，MySQL 中使用该引擎作为临时表，存放查询的中间结果。
如果只有 INSERT 和 SELECT 操作，可以选择Archive 引擎，Archive 存储引擎支持高并发的插入操作，但是本身并不是事务安全的。Archive 存储引擎非常适合存储归档数据，如记录日志信息可以使用 Archive 引擎。
InnoDB 是系统的默认引擎，支持可靠的事务处理。
使用下面的语句可以修改数据库临时的默认存储引擎

SET default_storage_engine=< 存储引擎名 >

3.MyISAM存储引擎 

MylSAM 存储引擎不支持事务， 也不支待外键， 特点是访问速度快， 对事务完整性没有要求， 以SELECT 、INSERT 为主的应用基本都可以使用这个引擎来创建表。
每个MylSAM 表在磁盘上存储成3 个文件， 其中文件名和表名都相同， 但是扩展名分别为：

• frm（存储表定义）
• MYD(MYData , 存储数据）
• MYl(MYlndex, 存储索引）

数据文件和索引文件可以放置在不同的目录， 平均分配10 , 获取更快的速度。要指定数据文件和索引文件的路径， 需要在创建表的时候通过DATA DIRECTORY 和INDEX DIRECTORY 语句指定，文件路径需要使用绝对路径。

每个MylSAM 表都有一个标志， 服务器或myisamchk 程序在检查MylSAM 数据表时会对这个标志进行设置。MylSAM 表还有一个标志用来表明该数据表在上次使用后是不是被正常的关闭了。如果服务器意外右机或崩溃， 这个标志可以用来判断数据表是否需要检查和修复。如果想让这种检查自动进行，可以在启动服务器时使用－－myisam-recover 实现。这会让服务器在每次打开一个MylSAM 数据表时自动检查数据表的标志并进行必要的修复处理。My lSAM 类型的表可能会损坏， 可以使用CHECK TABLE 语句来检查MylSAM 表的健康，并用REPAIR TABLE 语句修复一个损坏的My lSAM 表。
MylSAM 表还支持3 种不同的存储格式：

• 静态（ 固定长度）表
• 动态表
• 压缩表

静态表是默认的存储格式。静态表中的字段都是非可变字段， 这样每个记录都是固定长度的， 这种存储方式的优点是存储非常迅速， 容易缓存， 出现故障容易恢复； 缺点是占用的空间通常比动态表多。静态表在数据存储时会根据列定义的宽度定义补足空格， 但是在访问的时候并不会得到这些空格， 这些空格在返回给应用之前已经去掉。同时需要注意： 在某些情况下可能需要返回字段后的空格， 而使用这种格式时后面的空格会被自动处理掉。
动态表包含可变字段， 记录不是固定长度的， 这样存储的优点是占用空间较少， 但是频繁的更新、删除记录会产生碎片， 需要定期执行OPTIMIZE TABLE 语句或myisamchk -r命令来改善性能， 并且出现故障的时候恢复相对比较困难。
压缩表由myisamchk 工具创建， 占据非常小的空间， 因为每条记录都是被单独压缩的，所以只有非常小的访问开支。

4.InnoDB存储引擎

lnnoDB 是一个健壮的事务型存储引擎， 这种存储引擎已经被很多互联网公司使用， 为用户操作非常大的数据存储提供了一个强大的解决方案。MyS Q L 从5.5.5 版本开始， 默认的存储引擎为lnnoDB 。lnnoDB 存储引擎还引入了行级锁定和外键约束， 在以下场景中使用lnnoDB 存储引擎是最理想的选择：

•  更新密集的表： lnnoDB 存储引擎特别适合处理多重并发的更新请求。
•  事务： lnnoDB 存储引擎是支持事务的标准MySQ L 存储引擎。
•  自动灾难恢复与其它存储引擎不同， lnnoDB 表能够自动从灾难中恢复。
•  外键约束： MySQL 支持外键的存储引擎只有lnnoDB 。
•  支持自动增加列AUTO_INCREMENT 属性。

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

二：操作存储引擎

修改默认的存储引擎

( 1 ) 通过alter table 修改。

mysql> alter table t1 engine=MyISAM;
mysql> show table status from auth where name='t1'\G
( 2 ) 通过修改my.cnf, 指定默认存储引擎并重启服务。

[root@localhost ~]# vim /etc/my.cnf
[mysqld]
#添加下面语句
default-storage-engine=MyISAM
( 3 ) 通过create table 创建表时指定存储引擎。

mysql> use auth
mysql> create table t3 (id int(10),name char(20)) engine=INNODB;
mysql> show table status from auth where name='t3'\G
( 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