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

MySQL存储引擎

2024-07-13 04:22:15 230人阅读

为了防止无良网站的爬虫抓取文章，特此标识，转载请注明文章出处。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存储引擎有一个问题，即其查询连接操作是在数据库层完成的，而不是存储引擎完成的。这意味着，复杂的连接操作需要巨大的网络开销，因此返回擦洗速度很慢。

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修改表的存储引擎可能导致数据库中的数据丢失，所以在修改前，需要备份数据。