MYSQL引擎

存储引擎，即表类型（table_type）

 

说明：

    我们在创建表的时候，可以选择不同的存储引擎，选择合适的存储引擎往往能够有效的提高数据库的性能和数据的访问效率，而且数据库的每个表也可以各自使用适合的引擎来满足不同的性能和实际需求。

 

 

常用的存储引擎相关命令

 

• 查看指定表使用的存储引擎

        SHOW CREATE TABLE 表名

        示例代码：

       

 

        或者

        

        SHOW TABLE STATUS LIKE 'sc'\G;

        示例代码：

        

 

• 查看当前数据库支持的存储引擎

        SHOW ENGINES \G;

        示例代码：

        

 

• 给表指定存储引擎

        CREATE TABLE 表名(

        字段名 类型,

        engine=引擎名

        );

        示例代码：

        

 

• 或者你想在建表后更改表的引擎

         ALTER TABLE 表名 ENGINE=引擎名;

         示例代码：

         

 

 

------------------------------------------------------------------------------------------------------------------------

 

常用存储引擎优缺点及应用场景

 

一、MyISAM

它是MySQL5.5之前的默认存储引擎

 

优势：访问速度快

 

适用场景：

    对事务的完整性没有要求，或以select、insert为主的应用基本都可以使用MyISAM。在web、数据仓库中应用广泛

 

特点：

• 不支持事务，外键
• 每个MyISAM在磁盘上存储为3个文件，文件名和表名相同，扩展名分别是

        .frm   -------存储表定义

        .MYD -------MYData，存储数据

        .MYI   -------MYIndex，存储引擎

• 数据文件和索引文件可以放在不同的目录，平均分布IO，加快访问速度，在创建表的时候通过data directiory 和 index directory 来指定存储引擎
• myisam表支持三种不同的存储格式
• 静态表(fixed) ---默认的存储格式

         静态表的字段都是非变长字段，每个记录都是固定长度的，当表不包含变量长度列（VARCHAR,BLOB,TEXT）时，使用这个格式。

• 优点：

        存储速度快，出现故障容易恢复

• 缺点：

        占用空间比动态表大，静态表在进行数据存储时会按照事先定义好的列宽度不足空格，但是在访问的时候会去掉这些空格。

• 动态表（dynamic）

         包含变长字段，例如varchar, text, blob，如果一个MyISAM表包含任何可变长字段，或者该表创建时用row_format=dynamic指定，则该表使用动态格式存储

• 优点

         占用空间小

• 缺点

        频繁的更新和删除操作会产生碎片，需要定期用optimize table 语句或myisamchk - r 命令来改善性能，并且在出现故障后较难恢复。

• 压缩表

         由myisampack工具创建，占据非常小的磁盘空间，因为每个记录都是被单独压缩的。

 

--------------------------------------------------------------------------------------------

 

二、InnoDB

    MySQL5.5之后的默认存储引擎

 

应用场景：如果应用对事务的完整性有较高的要求，在并发条件下要求数据的一致性，数据操作中包含读、插入、删除、更新，那InnoDB是最好的选择。在计费系统、财务系统等对数据的准确性要求较高的系统中被广泛应用。

 

优点：提供了具有提交、回滚、奔溃恢复能力的事务安全，支持外键。

缺点：相比较MyISAM，写的效率差一点，并且会占用更多的磁盘空间来存储数据和索引。

 

特点：

1、自动增长列

    InnoDB表的自动增长列必须是索引，如果是组合索引，也必须是组合索引的第一列

    MyISAM表的自动增长列可以是组合索引的其他列

    可以通过alter table 表名 auto_increment=n来强制设置自动增长，但是该值是保存在内存中的，所以在数据库重启后会失效，需要重新指定。

 

2、外键约束

    MySQL的存储引擎中只有InnoDB支持外键约束

    注意：当某个表被其他表创建了外键参照，那么该表对应的索引和主键禁止被删除

    当导入多个表的数据时，如果要忽略表之前导入顺序，或者当执行load data和alter table 操作，为了提高处理速度的时候，可以暂时关闭外键约束命令，命令是：

    SET FOREIGN_KEY_CHECKS=0;

执行完毕之后，再使其为1，开启外键。

 

3、存储方式

    InniDB存储数据和索引有共享空间表存储和独占空间存储两种方式。通过参数innodb_file_pre_table控制，0表示共享空间，也是默认的，1表示独占空间

    两种方式的表结构（描述）都保存在.frm文件中。

 

-----------------------------------------------------------------------------------------------------------------------

        

三、MEMORY

 

MEMORY存储引擎用保存在内存中的数据来创建表，每个memory表对应一个磁盘文件。格式是.frm

 

特点：

    由于他的数据是存放在内存中的，并且默认使用HASH索引，所以他的访问速度特别快，同时也造成了他的缺点，就是数据库服务一旦关闭，数据就会丢失，另外对表的大小有限制。

    每个memory表中可以存储数据量的大小，受到max_heap_table_size变量的约束，他的初始值是16MB，可以在定义Memory表的时候通过max_rows指定表的最大行数

 

适用场景：内容变化不频繁的代码表，作为统计操作的中间结果表，便于利用它速度快的优势高效的对中间结果进行分析。

 

-------------------------------------------------------------------------------------------------------------------------

 

四、MERGE

 

Merge表是一组MyISAM表的组合，这些myisam表的结构必须完全相同，MERGE表本身并没有数据，对它的操作实际上是对内部MYISAM表的操作

MERGE表在磁盘上保留了两个文件， .mrg文件存储组合表的信息。

 

应用场景：用于将一系列MyISAM表以逻辑方式组合在一起，并作为一个对象引用它们

 

优点：突破对单个MyISAM表的大小限制，通过将不同的表分布在多个磁盘上，提高访问效率

 

例如：

insert_method=first/last/no 表示对MERGE表插入操作时，作用对象是谁。first表示作用于第一张myisam表，last作用于最后一张myisam表，no或者不指定表示不能对该MERGE表进行插入操作。

 

补充：

• 不同存储引擎对索引的支持