mysql数据库如何存储数据结构_MySQL数据库----存储引擎

什么是存储引擎？

存储引擎说白了就是如何存储数据、如何为存储的数据建立索引和如何更新、查询数据等技术的实现方

法。因为在关系数据库中数据的存储是以表的形式存储的，所以存储引擎也可以称为表类型(即存储和操作此表的类型)

SQL 解析器、SQL 优化器、缓冲池、存储引擎等组件在每个数据库中都存在,但不是每 个数据库都有这么多存储引擎。MySQL 的插件式存储引擎可以让存储引擎层的开发人员设 计他们希望的存储层,例如,有的应用需要满足事务的要求,有的应用则不需要对事务有这 么强的要求 ;有的希望数据能持久存储,有的只希望放在内存中,临时并快速地提供对数据 的查询。

一种类型表明的是一种存储方式

存储引擎：就是表的类型，不同的类型就会对应不同的处理机制去处理他

事务：就是要么同时成功，要么同时不成功

MySQL数据库的体系架构如下图所示：

从上图中可以看出，MySQL主要分为以下几个组件：

连接池组件

管理服务和工具组件

SQL接口组件

分析器组件

优化器组件

缓冲组件

插件式存储引擎

物理文件

一、存储引擎

存储引擎 : 其实就是指定 表 如何存储数据，如何为存储的数据 建立索引以及 如何更新，查询数据等技术实现的方法。因为在关系数据库中数据的存储是以表的形式存储的，所以存储引擎也可以称为表类型(即存储和操作此表的类型)

了解:在Oracle 和SQL Server等数据库中只有一种存储引擎，所有数据存储管理机制都是一样的。而MySql数据库提供了多种存储引擎。用户可以根据不同的需求为数据表选择不同的存储引擎，用户也可以根据自己的需要编写自己的存储引擎

下表显示了各种存储引擎的特性：

MySQL存储引擎比较

特性MyISAMInnoDBMemoryArchiveNDB

存储限制

没有

64TB

有

没有

没有

事务

支持

支持

锁粒度

表

行

表

行

页

B树索引

支持

支持

支持

支持

哈希索引

支持

支持

全文索引

支持

集群索引

支持

数据缓存

支持

支持

索引缓存

支持

支持

支持

数据压缩

支持

支持

批量插入

高

相对低

高

非常高

高

内存消耗

低

高

中

低

低

外键支持

支持

复制支持

支持

支持

支持

支持

支持

查询缓存

支持

支持

支持

支持

支持

备份恢复

支持

支持

支持

支持

支持

集群支持

支持

其中最常见的两种存储引擎是MyISAM 和 InnoDB

MyISAM存储引擎

1、MyISAM 是MySQL (mysql 5.5版本以前) 原来的默认存储引擎.

1、MyISAM   这种存储引擎不支持事务，不支持行级锁，只支持并发插入的表锁。

2、MyISAM   类型的表支持三种不同的存储结构：静态型、动态型、压缩型。

(1)静态型：就是定义的表列的大小是固定(即不含有：xblob、xtext、varchar等长度可变的数据类型)，这样mysql就会自动使用静态myisam格式。

使用静态格式的表的性能比较高，因为在维护和访问的时候以预定格式存储数据时需要的开销很低。但是这高性能是用空间换来的，因为在定义的时候是固定的，所以不管列中的值有多大，都会以最大值为准，占据了整个空间。

(2)动态型：如果列(即使只有一列)定义为动态的(xblob, xtext, varchar等数据类型)，这时myisam就自动使用动态型，虽然动态型的表占用了比静态型表较少的空间，但带来了性能的降低，因为如果某个字段的内容发生改变则其位置很可能需要移动，这样就会导致碎片的产生。随着数据变化的增多，碎片就会增加，数据访问性能就会相应的降低。

对于因为碎片的原因而降低数据访问性，有两种解决办法：

@1、尽可能使用静态数据类型

@2、经常使用optimize   table语句，他会整理表的碎片，恢复由于表的更新和删除导致的空间丢失。

(如果存储引擎不支持 optimize  table    则可以转储并重新加载数据，这样也可以减少碎片)

(3)压缩型：如果在这个数据库中创建的是在整个生命周期内只读的表，则这种情况就是用myisam的压缩型表来减少空间的占用。

3、MyISAM也是使用B+tree索引但是和Innodb的在具体实现上有些不同。

InnoDB存储引擎

(1)MySQL默认存储引擎(MySQL 5.5 版本后).

(2)innodb 支持事务，回滚以及系统崩溃修复能力和多版本迸发控制的事务的安全。

(3)innodb 支持自增长列(auto_increment),自增长列的值不能为空，(一个表只允许存在一个自增,并且要求自增列必须为索引)

(4)innodb 支持外键(foreign key) ,外键所在的表称为子表,而所依赖的表称为父表。

(5)innodb存储引擎支持行级锁。

(6)innodb存储引擎索引使用的是B+Tree

补充2点：

1.大容量的数据集时趋向于选择Innodb。因为它支持事务处理和故障的恢复。Innodb可以利用数据日志来进行数据的恢复。主键的查询在Innodb也是比较快的。

2.大批量的插入语句时(这里是INSERT语句)在MyIASM引擎中执行的比较的快，但是UPDATE语句在Innodb下执行的会比较的快，尤其是在并发量大的时候。

2.两种引擎所使用的索引的数据结构是什么？

答案:都是B+树!

MyIASM引擎，B+树的数据结构中存储的内容实际上是实际数据的地址值。也就是说它的索引和实际数据是分开的，只不过使用索引指向了实际数据。这种索引的模式被称为非聚集索引。

Innodb引擎的索引的数据结构也是B+树，只不过数据结构中存储的都是实际的数据，这种索引有被称为聚集索引。

Memory  存储引擎

Memory存储引擎(之前称为Heap)将表中数据存放在内存中，如果数据库重启或崩溃，数据丢失，因此它非常适合存储临时数据。

Archive存储引擎

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

NDB存储引擎

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

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

二、测试存储引擎

创建三个表，分别使用innodb，myisam，memory 存储引擎，进行插入数据测试

重点[面试题]:

innodb与MyIASM存储引擎的区别:

1.innodb 是mysql5.5版本以后的默认存储引擎, 而MyISAM是5.5版本以前的默认存储引擎.

2.innodb 支持事物,而MyISAM不支持事物

3.innodb 支持行级锁.而MyIASM 它支持的是并发的表级锁.

4.innodb 支持外键, 而MyIASM 不支持外键

5.innodb与MyIASM存储引擎都采用B+TREE存储数据, 但是innodb的索引与数据存储在一个文件中,这种方式我们称之为聚合索引.

而MyIASM则会单独创建一个索引文件,也就是说,数据与索引是分离开的

6.在效率方面MyISAM比innodb高,但是在性能方面innodb要好一点.

使用存储引擎

方法1：建表时指定

存储引擎

1.create table t1(id int) engine = innodb 会出现2个文件

2.create table t2(id int) engine = myisam 会出现3个文件(速度比上面的快)，但是我们还是用innodb存储

3.create table t3(id int) engine = memory 只有表结构没有表数据,是创建到内存中的

4.create table t4(id int) engine = blackhole 黑洞，吃数据(数据存进去就没有了，存进去就没有了)

方法2：在配置文件中指定默认的存储引擎

/etc/my.cnf

[mysqld]

default-storage-engine=INNODB

innodb_file_per_table=1