首页 > 代码库 > MySQL存储引擎

MySQL存储引擎

为了防止无良网站的爬虫抓取文章,特此标识,转载请注明文章出处。LaplaceDemon/SJQ。

http://www.cnblogs.com/shijiaqi1066/p/3857808.html

 

 

 

存储引擎概述

存储引擎就是存储数据,建立索引,更新查询数据等等技术的实现方式。

存储引擎是基于表的,而不是基于库的。所以存储引擎也可被称为表类型。

Oracle,SqlServer等数据库只有一种存储引擎。MySQL提供了插件式的存储引擎架构。所以MySQL存在多种存储引擎,可以根据需要使用相应引擎,或者编写存储引擎。

 

查询MySQL系统中支持的存储引擎

使用MySQL客户端命令,显示当前MySQL系统所支持的数据库引擎。

命令:show engines;

wps_clip_image-9130

 

查看当前默认使用的存储引擎

命令:show variables like ‘table_type‘;

wps_clip_image-19088

 

 

InnoDB

从MySQL5.5.8,InnoDB成为MySQL默认的存储引擎。MySQL从3.23.34a开始支持InnoDB。

InnoDB支持事务,主要面向OLTP数据库应用。支持行锁,支持外键,并支持类似于Oracle的行锁定读,即默认读取操作不会产生锁。

 

InnoDB存储引擎中支持自动增长列AUTO_INCREMENT。自动增长列的值不能为空,且值必须唯一,且必须为主键。在执行插入操作时,若不指定自动增长列的值,或自动增长列的值为0或NULL,则插入的值为自动增长后的值。

若插入任意值,该值在该列中没有出现过,则可以直接插入。

InnoDB存储引擎中,创建的表的结构存储于.frm文件中。数据和索引存储在innodb_data_home和innodb_data_path表空间中。

 

对于表的数据存储,InnoDB存储引擎采用了聚集的方式,每张表的存储都是按主键顺序进行存放。若没有显示地在表定义时指定主键,InnoDB会为每一行生成一个6字节的ROWID,并以此作为主键。

InnoDB支持外键。外键所在的表为子表,外键依赖的表为父表。父表中被主表外键支持的字段必须为主键。当删除、更新浮标的某条信息时,子表也必须有相应的改变。

 

InnoDB通过多版本并发控制(MVCC)来获得高并发性,并实现了SQL标准的4种隔离机制,默认为REPEATABLE级别。同时使用一种被称为next-key locking的策略来避免幻读。InnoDB还提供了插入缓冲、二次写、自适应哈希索引、预读等高性能和高可用的功能。

 

 

MyISAM

MySQL5.5.8之前MyISAM是MySQL默认的存储引擎。

MyISAM不支持事务,不支持外键,支持全文索引,处理速度快。主要面向OLAP数据库应用。

MyISAM存储引擎的表存储成3个文件,文件名与表名相同,扩展名分别为:frm,MYD,MYI。

  • frm文件:存储表的结构。
  • myd文件:存储数据。
  • myi文件:存储存储索引。

MySQL 5.0版本之前,MyISAM默认支持的表大小为4GB,若需要支持大于4GB的MyISAM表时,则需要指定MAX_ROWS和AVG_ROW_LENGTH属性。从MySQL5.0开始,MyISAM默认支持256T的单表数据。

MyISAM的缓冲池只缓存索引文件,而不缓存数据文件。数据文件的缓存交给操作系统完成。这与大多数使用LRU算法缓存数据的数据库都不同。在MySQL5.1.23之前,无论32位系统或64位系统,缓存最大只能设置为4GB,在之后的版本中,64位的系统可以支持大于4GB的索引缓冲区。

 

 

MEMORY

MEMORY存储引擎(之前被称为HEAP存储引擎)将表中的数据存储在内存中。表结构以文件存储于磁盘。文件名与表名相同,后缀名为frm。服务器需要有足够的内存来维持MEMORY存储引擎的表的使用。如果不需要使用了,可以释放这些内容,或删除不需要的表。

MEMORY存储引擎默认使用哈希索引,可以按需求指定索引类型。

只支持表锁,并发性能较差。不支持VARCHAR、BLOB和TEXT的列类型。

MEMORY存储引擎通常很少用到。由于基于内存,所以响应速度非常快。但若内存出现异常就会影响到数据的完整性。若重启机器或者关机,或当mysqld守护进程崩溃时,所有的MEMORY数据都会丢失。

MEMORY表的大小主要取决于两个参数,分别是max_rows和max_heap_table_size:

  • max_rows可以在创建表时指定;
  • max_heap_table_size的大小默认为16MB,可以按需要进行扩大。

若数据超过了设置的值,则MySQL数据库会把其转换到MyISAM存储引擎表,存放在磁盘中。这导致访问性能的降低。

当数据有如下情况时,可以考虑使用MEMORY表:

  • 临时的数据:目标数据只是临时需要,在其生命周期中必须立即可用。
  • 相对无关的数据:存储在MEMORY表中的数据如果突然丢失,不会对应用服务产生实质的负面影响,而且不会对数据完整性有长期影响。

如果使用MySQL4.1及以之前版本,MEMORY的搜索比MyISAM表的搜索效果要低,因为MEMORY表只支持哈希索引,这需要使用整个键进行搜索。但是,4.1之后的版本同时支持散列索引和B树索引。B树索可以使用部分查询和通配查询,也可以使用 < 、> 和 >= 等操作符。

 

 

NDB

NDB存储引擎是一个集群存储以前那个,类似于Oracle的RAC集群。

NDB的特点是主键查找速度极快,并通过添加NDB数据存储节点可以线性的提交数据库性能,是高可用,高性能的集群系统。

NDB存储引擎有一个问题,即其查询连接操作是在数据库层完成的,而不是存储引擎完成的。这意味着,复杂的连接操作需要巨大的网络开销,因此返回擦洗速度很慢。

 

 

Archive

Archive存储引擎只支持INSERT和SELECT操作,从MySQL5.1开始支持索引。

Archive存储引擎使用zlib算法将数据和进行压缩后存储,压缩比可达1:10。Archive存储引擎非常适合存储归档数据。Archive使用行锁啊来实现高并发的插入操作,但不支持事务。

 

 

Federated

Federated存储引擎不存放数据,只是指向一台远程MySQL数据库服务器上的表。

 

 

Maria

Maria存储引擎是新开发的引擎,用于取代原有的MyISAM。支持缓存数据和索引文件,应用行锁设计,提供MVCC功能,支持事务和非事务安全的选项,以及更好的BLOB字符类型的处理性能。

 

 

其他存储引擎

MySQL还有很多其他的存储引擎,包括Merge,CVS,Sphinx,Infobright,它们各自有各自的使用场合。

 

创建表时指定存储引擎

创建一个InnoDB的数据表,基本格式:

CREATE TABLE 表名(

    ......

) ENGINE = INNODB;

 

例:创建country表,指定存储引擎为InnoDB。

CREATE TABLE country(    country_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,    country VARCHAR(50) NOT NULL,    last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,    PRIMARY KEY (country_id)) ENGINE = InnoDB DEFAULT CHARSET=gbk;

 

修改表的存储引擎

修改表的存储引擎为InnoDB,基本格式:

ALERT TABLE 表名 ENGINE = INNODB;

使用ALERT TABLE修改表的存储引擎可能导致数据库中的数据丢失,所以在修改前,需要备份数据。

 

 

 

 

为了防止无良网站的爬虫抓取文章,特此标识,转载请注明文章出处。LaplaceDemon/SJQ。

http://www.cnblogs.com/shijiaqi1066/p/3857808.html