mysql日志类数据存储_基于MySQL的存储引擎与日志说明(全面讲解)

1.1 存储引擎的介绍

1.1.1 文件系统存储

文件系统：操作系统组织和存取数据的一种机制。文件系统是一种软件。

类型：ext2 3 4 ，xfs 数据。 不管使用什么文件系统，数据内容不会变化，不同的是，存储空间、大小、速度。

1.1.2 mysql数据库存储

MySQL引擎： 可以理解为，MySQL的“文件系统”，只不过功能更加强大。

MySQL引擎功能： 除了可以提供基本的存取功能，还有更多功能事务功能、锁定、备份和恢复、优化以及特殊功能。

1.1.3 MySQL存储引擎种类

MySQL 提供以下存储引擎：

InnoDB、MyISAM (最常用的两种)

MEMORY、ARCHIVE、FEDERATED、EXAMPLE

BLACKHOLE、MERGE、NDBCLUSTER、CSV

除此之外还可以使用第三方存储引擎。

1.1.4 innodb与myisam对比

InnoDb引擎

支持ACID的事务，支持事务的四种隔离级别；

支持行级锁及外键约束：因此可以支持写并发；

不存储总行数；

一个InnoDb引擎存储在一个文件空间(共享表空间，表大小不受操作系统控制，一个表可能分布在多个文件里)，也有可能为多个(设置为独立表空，表大小受操作系统文件大小限制，一般为2G)，受操作系统文件大小的限制；

主键索引采用聚集索引(索引的数据域存储数据文件本身)，辅索引的数据域存储主键的值；因此从辅索引查找数据，需要先通过辅索引找到主键值，再访问辅索引；最好使用自增主键，防止插入数据时，为维持B+树结构，文件的大调整。

Innodb的主索引结构如下：

MyISAM引擎

不支持事务，但是每次查询都是原子的；

支持表级锁，即每次操作是对整个表加锁；

存储表的总行数；

一个MYISAM表有三个文件：索引文件、表结构文件、数据文件；

采用菲聚集索引，索引文件的数据域存储指向数据文件的指针。辅索引与主索引基本一致，但是辅索引不用保证唯一性。

MYISAM的主索引结构如下：

两种索引数据查找过程如下：

1.2 innodb存储引擎

在MySQL5.5版本之后，默认的存储引擎，提供高可靠性和高性能。

1.2.1 Innodb引擎的优点

a) 事务安全(遵从ACID)

b) MVCC(Multi-Versioning Concurrency Control，多版本并发控制)

c) InnoDB行级锁

d) 支持外键引用完整性约束

e) 出现故障后快速自动恢复(crash safe recovery)

f) 用于在内存中缓存数据和索引的缓冲区池(buffer pool(data buffer page log buffer page) 、undo buffer page)

g) 大型数据卷上的最大性能

h) 将对表的查询与不同存储引擎混合

i) Oracle样式一致非锁定读取(共享锁)

j) 表数据进行整理来优化基于主键的查询(聚集索引)

1.2.2 Innodb功能总览

功能

支持

功能

支持

存储限制

64 TB

索引高速缓存

是

MVCC

是

数据高速缓存

是

B树索引

是

自适应散列索引

是

群集索引

是

复制

是

压缩数据

是

更新数据字典

是

加密数据[b]

是

地理空间数据类型

是

查询高速缓存

是

地理空间索引

否

事务

是

全文搜索索引

是

锁定粒度

行

群集数据库

否

外键

是

备份和恢复

是

文件格式管理

是

快速索引创建

是

多个缓冲区池

是

PERFORMANCE_SCHEMA

是

更改缓冲

是

自动故障恢复

是

1.2.3 查询存储引擎的方法

1、使用 SELECT 确认会话存储引擎：

SELECT @@default_storage_engine;

或

show variables like '%engine%';

2、使用 SHOW 确认每个表的存储引擎：

SHOW CREATE TABLE City\G

SHOW TABLE STATUS LIKE 'CountryLanguage'\G

3、使用 INFORMATION_SCHEMA 确认每个表的存储引擎：

SELECT TABLE_NAME, ENGINE FROM

INFORMATION_SCHEMA.TABLES

WHERE TABLE_NAME = 'City'

AND TABLE_SCHEMA = 'world_innodb'\G

4、从5.1版本，迁移到5.5版本以上版本

假如5.1版本数据库所有生产表都是myisam的。

使用mysqldump备份后，一点要替换备份的文件中的engine(引擎)字段，从myisam替换为innodb(可以使用sed命令)，否则迁移无任何意义。

数据库升级时，要注意其他配套设施的兼容性，注意代码能否兼容新特性。

1.2.4 设置存储引擎

1、在启动配置文件中设置服务器存储引擎：

[mysqld]

default-storage-engine=

2、使用 SET 命令为当前客户机会话设置：

SET @@storage_engine=;

3、在 CREATE TABLE 语句指定：

CREATE TABLE t (i INT) ENGINE = ;

1.3 InnoDB存储引擎的存储结构

1.3.1 InnoDB 系统表空间特性

默认情况下，InnoDB 元数据、撤消日志和缓冲区存储在系统“表空间”中。

这是单个逻辑存储区域，可以包含一个或多个文件。

每个文件可以是常规文件或原始分区。

最后的文件可以自动扩展。

1.3.2 表空间的定义

表空间：MySQL数据库存储的方式

表空间中包含数据文件

MySQl表空间和数据文件是1:1的关系

共享表空间除外，是可以1:N关系

1.3.3 表空间类型

1、共享表空间：ibdata1~ibdataN,一般是2-3个

2、独立表空间：存放在指定库目录下，例如data/world/目录下的city.ibd

表空间位置(datadir)：

data/目录下

1.3.4 系统表空间的存储内容

共享表空间(物理存储结构)

ibdata1~N 通常被叫做系统表空间，是数据初始化生成的

系统元数据，基表数据，除了表内容数据之外的数据。

tmp 表空间(一般很少关注)

undo日志 ：数据--回滚数据(回滚日志使用)

redo日志 ：ib_logfile0~N 存放系统的innodb表的一些重做日志。

说明：undo日志默认实在ibdata中的，在5.6以后是可以单独定义的。

tmp 表空间在5.7版本之后被移出了ibdata1，变为ibtmp1

在5.5版本之前，所有的应用数据也都默认存放到了ibdata中。

独立表空间(一个存储引擎的功能)

在5.6之后，默认的情况下会单表单独存储到独立表空间文件

除了系统表空间之外，InnoDB 还在数据库目录中创建另外的表空间，用于每个 InnoDB 表的 .ibd 文件。

InnoDB 创建的每个新表在数据库目录中设置一个 .ibd 文件来搭配表的.frm 文件。

可以使用 innodb_file_per_table 选项控制此设置，更改该设置仅会更改已创建的新表的默认值。。

1.3.5 设置共享表空间

查看当前的共享表空间设置

mysql> show variables like 'innodb_data_file_path';

+-----------------------+------------------------+

| Variable_name | Value |

+-------------