文章目录
  1. 1. MyISAM
  2. 2. InnoDB
    1. 2.1. auto increment
    2. 2.2. 外键约束
    3. 2.3. 存储方式
  3. 3. MEMORY(Heap)
  4. 4. MERGE
  5. 5. Archive
  6. 6. BDB
  7. 7. NDB
  8. 8. Federated
  9. 9. Maria
  10. 10. 常见引擎对比
  11. 11. 如何选择
  12. 12. 总结

当初学习Mysql的时候,知道Mysql中有MyISAM和InnoDB两种存储引擎,还对插入大批量数据做了对比,今天就趁机整理学习一下Mysql的存储引擎。

主要介绍一下MyISAM、InnoDB、MEMORY、MERGE、Archive等。

MyISAM

MyISAM是MySQL的默认存储引擎。MyISAM不支持事务、行级锁、也不支持外键,但其访问速度快,对事务完整性没有要求,或者以SELECT、INSERT为主的应用基本都可以使用这个引擎来创建表。

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

  • .frm(存储表定义)
  • MYD(MYData,存储数据)
  • MYI(MYIndex,存储索引)

要注意的是MyISAM不缓存数据文件,只缓存索引文件

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

每个MyISAM表都有一个标志,服务器或myisamchk程序在检查MyISAM数据表时会对这个标志进行设置。MyISAM表还有一个标志用来表明该数据表在上次使用后是不是被正常的关闭了。如果服务器以为当机或崩溃,这个标志可以用来判断数据表是否需要检查和修复。如果想让这种检查自动进行,可以在启动服务器时使用–myisam-recover现象。这会让服务器在每次打开一个MyISAM数据表是自动检查数据表的标志并进行必要的修复处理。MyISAM类型的表可能会损坏,可以使用CHECK TABLE语句来检查MyISAM表的健康,并用REPAIR TABLE语句修复一个损坏到MyISAM表。

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

  • 静态(固定长度)表

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

  • 动态表

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

  • 压缩表

    压缩表由myisamchk工具创建,占据非常小的空间,因为每条记录都是被单独压缩的,所以只有非常小的访问开支。 但是这种表压缩之后不能再被修改。另外,因为是压缩数据,所以这种表在读取的时候要先实行解压缩。

InnoDB

事务型数据库首选引擎,支持ACID事务,支持行级锁。InnoDB是为处理巨大数据量时的最大性能设计。InnoDB存储引擎完全与MySQL服务器整合,InnoDB存储引擎为主内存中缓存数据和索引而维持它自己的缓冲池。InnoDB存储它的表&索引在一个表空间中,表空间可以包含数个文件。这与MyISAM表不同,比如在MyISAM表中每个表被存在分离文件中。InnoDB表可以是任何尺寸,即使在文件尺寸被限制为2GB的操作系统中。InnoDB默认地被包含在MySQL二进制分发中。

InnoDB给MySQL提供了具有事务(transaction)、回滚(rollback)和崩溃修复能力(crash recovery capabilitie)、多版本并发控制(mulit-sersioned concurrcency contorl)的事务安全(transaction-safe(compliant)型表。InnoDB提供了行级锁(locking on row level),提供与Oracle类似的不加锁读取(non-locking read in SELECTs)。InnoDB锁定在行级并且也在SELECT语句提供一个Oracle风格一致的非锁定读。这些特色增加了多用户部署和性能。没有在InnoDB中扩大锁定的需要,因为在InnoDB中行级锁适合非常小的空间。InnoDB也支持FOREIGN KEY强制。在SQL查询中,你可以自由地将InnoDB类型的表与其它MySQL的表的类型混合起来,甚至在同一个查询中也可以混合。这些特性均提高了多用户并发操作的性能表现。

auto increment

InnoDB表的自动增长列可以手工插入,但是插入的如果是空或0,则实际插入到则是自动增长后到值。可以通过”ALTER TABLE…AUTO_INCREMENT=n;”语句强制设置自动增长值的起始值,默认为1,但是该强制到默认值是保存在内存中,数据库重启后该值将会丢失。可以使用LAST_INSERT_ID()查询当前线程最后插入记录使用的值。如果一次插入多条记录,那么返回的是第一条记录使用的自动增长值。

对于InnoDB表,自动增长列必须是索引。如果是组合索引,也必须是组合索引的第一列,但是对于MyISAM表,自动增长列可以是组合索引的其他列,这样插入记录后,自动增长列是按照组合索引到前面几列排序后递增的。

外键约束

MySQL支持外键的存储引擎只有InnoDB,在创建外键的时候,父表必须有对应的索引,子表在创建外键的时候也会自动创建对应的索引。

在创建索引的时候,可以指定在删除、更新父表时,对子表进行的相应操作,包括restrict、cascade、set null和no action。其中restrict和no action相同,是指限制在子表有关联的情况下,父表不能更新;casecade表示父表在更新或删除时,更新或者删除子表对应的记录;set null 则表示父表在更新或者删除的时候,子表对应的字段被set null。

当某个表被其它表创建了外键参照,那么该表对应的索引或主键被禁止删除。

可以使用set foreign_key_checks=0;临时关闭外键约束,set foreign_key_checks=1;打开约束。

存储方式

InnoDB存储表和索引有以下两种方式。

使用共享表空间存储,这种方式创建的表结构保存在.frm文件中,数据和索引保存在innodb_data_home_dir和innodb_data_file_path定义的表空间中,可以是多个文件

使用多表空间存储,这种方式创建的表结构依然存储在.frm文件中,但是每个表的数据和索引单独保存在.ibd中。

innodb_file_per_table 这个参数控制是使用共享表空间还是多表空间存储。当为ON时使用多表空间存储,为OFF时使用共享表空间存储。需要重新启动数据库后才可以生效,并且只对新建的表生效,原表仍然使用更改前的存储方式。

多表空间对数据文件没有大小限制,不需要设置初始大小。优点是方便维护,不至于使单个文件过大。但是直接复制.ibd文件是不行的,因为没有共享表空间的数据字典信息,直接复制.ibd 文件和 .frm文件恢复时是不能被目标库使用。如果要将进行备份和恢复操作,需要使用mysqldump 和 mysqlimport工具。

即使在多表空间的存储方式下,共享表空间仍然是必须的,InnoDB把内部数据词典和重做日志放在这个文件中。

MEMORY(Heap)

MEMORY存储引擎使用在内存中的内容来创建表。每个MEMORY表只实际对应一个磁盘文件,格式是.frm。MEMORY类型的访问非常快,因为她的数据时存放在内存中的,并且默认使用HASH索引,但是一旦服务器关闭,表中的数据就会丢失。给MEMORY表创建索引的时候,默认使用HASH索引。

默认情况下,memory数据表使用散列索引,利用这种索引进行“相等比较”非常快,但是对“范围比较”的速度就慢多了。因此,散列索引值适合使用在”=”和”<=>”的操作符中,不适合使用在”<”或”>”操作符中,也同样不适合用在order by字句里。如果确实要使用”<”或”>”或betwen操作符,可以使用btree索引来加快速度。

存储在MEMORY数据表里的数据行使用的是长度不变的格式,因此加快处理速度,这意味着不能使用BLOB和TEXT这样的长度可变的数据类型。VARCHAR是一种长度可变的类型,但因为它在MySQL内部当作长度固定不变的CHAR类型,所以可以使用。

create table tab_memory engine=memory 
select id,name,age,addr from man order by id;

使用USING HASH/BTREE来指定特定到索引。

create index mem_hash using hash on tab_memory(city_id);

在启动MySQL服务的时候使用–init-file选项,把insert into…select或load data infile 这样的语句放入到这个文件中,就可以在服务启动时从持久稳固的数据源中装载表。

服务器需要足够的内存来维持所在的在同一时间使用的MEMORY表,当不再使用MEMORY表时,要释放MEMORY表所占用的内存,应该执行DELETE FROM或truncate table或者删除整个表。

每个MEMORY表中放置的数据量的大小,受到max_heap_table_size系统变量的约束,这个系统变量的初始值是16M,同时在创建MEMORY表时可以使用MAX_ROWS子句来指定表中的最大行数。

主要用于那些内容变化不频繁的代码表,或者作为统计操作的中间结果表,便于高效地堆中间结果进行分析并得到最终的统计结果。对MEMORY存储引擎的表进行更新操作要谨慎,因为数据并没有实际写入到磁盘中,所以一定要对下次重新启动服务后如何获得这些修改后的数据有所考虑。

MERGE

merge存储引擎是一组MyISAM表的组合,这些MyISAM表结构必须完全相同,MERGE表中并没有数据,对MERGE类型的表可以进行查询、更新、删除的操作,这些操作实际上是对内部的MyISAM表进行操作。对于对MERGE表进行的插入操作,是根据INSERT_METHOD子句定义的插入的表,可以有3个不同的值,first和last值使得插入操作被相应的作用在第一个或最后一个表上,不定义这个子句或者为NO,表示不能对这个MERGE表进行插入操作。可以对MERGE表进行drop操作,这个操作只是删除MERGE表的定义,对内部的表没有任何影响。MERGE在磁盘上保留2个以MERGE表名开头文件:.frm文件存储表的定义;.MRG文件包含组合表的信息,包括MERGE表由哪些表组成,插入数据时的依据。可以通过修改.MRG文件来修改MERGE表,但是修改后要通过flush table刷新。

create table man_all(id int,name varchar(20))engine=merge union=(man1,man2) insert_methos=last;

优点在于可以突破对单个MyISAM表大小的限制,通过将不同的表分布在多个磁盘上,可以有效的改善MERGE表的访问效率。

Archive

Archive非常适合存储归档数据,如日志信息。它只支持INSERT和SELECT操作,其设计的主要目的是提供高速的插入和压缩功能。

BDB

是MySQL第一款事务安全的存储引擎。在Berkeley DB database library的基础上建立,同样是事务安全的,但BDB的普及率显然不及InnoDB,因为大多数在MySQL中寻找支持事务的存储引擎的同时也在找支持MVCC或是行级锁定存储引擎,而BDB只支持Page-level Lock。

NDB

NDB存储引擎是一个集群存储引擎,类似于Oracle的RAC,但它是Share Nothing的架构,因此能提供更高级别的高可用性和可扩展性。NDB的特点是数据全部放在内存中,因此通过主键查找非常快。

关于NDB,有一个问题需要注意,它的连接(join)操作是在MySQL数据库层完成,不是在存储引擎层完成,这意味着,复杂的join操作需要巨大的网络开销,查询速度会很慢。

Federated

Federated存储引擎不存放数据,它至少指向一台远程MySQL数据库服务器上的表,非常类似于Oracle的透明网关。

Maria

Maria存储引擎是新开发的引擎,其设计目标是用来取代原有的MyISAM存储引擎,从而成为MySQL默认的存储引擎。

常见引擎对比

如何选择

MyISAM:如果应用是以读和插入操作为主,并且对事务完整性、并发性要求不高,选择MyISAM不错

InnoDB:如果应用对事务的完整性有比较高的要求,在并发条件下要求数据的一致性,有大量的增删改查操作,支持外键使用InnoDB比较合适。对于类似计费或者财务系统等对数据准确性要求比较高的系统,适合选择InnoDB。

MEMORY:将所有数据保存在RAM中,对数据读取提供了极快的访问速度。 MEMORY是对表的大小有限制,数据不能持久化。MEMORY表通常用于更新不太频繁的小表

总结

虽然MySQL有非常多的存储引擎,但它们之间不存在优劣关系,而是根据不同的应用选择适合自己业务的存储引擎。

文章目录
  1. 1. MyISAM
  2. 2. InnoDB
    1. 2.1. auto increment
    2. 2.2. 外键约束
    3. 2.3. 存储方式
  3. 3. MEMORY(Heap)
  4. 4. MERGE
  5. 5. Archive
  6. 6. BDB
  7. 7. NDB
  8. 8. Federated
  9. 9. Maria
  10. 10. 常见引擎对比
  11. 11. 如何选择
  12. 12. 总结