mysql 各种文件详解

文件

分析构成MySQL数据库和InnoDB存储引擎的各种类型文件。

参数文件：告诉MySQL实例启动时在哪里可以找到数据库文件，并且指定某些初始化参数，这些参数定义了某种内存结构的大小等设置，还会介绍各种参数的类型。

日志文件：用来记录MySQL实例对某种条件做出相应时写入的文件，如错误日志文件、二进制日志文件、慢查询日志文件、查询日志文件等。

socket文件：当用UNIX域套接字方式进行连接时需要的文件。

pid文件：MySQL实例的进程ID文件。

MySQL表结构文件：用来存放MySQL表结构定义文件。

存储引擎文件：因为MySQL表存储引擎的关系，每个存储引擎都会有自己的文件来保存各种数据。这些存储引擎真正存储了记录和索引等数据。

参数文件

什么是参数

可以把数据库参数看成一个键/值(key/value)对。我们将这个参数设置为1G, 即innodb_buffer_pool_size=1G。这里的"键"是innodb_buffer_pool_size，“值”是1G,这就是键值对。

可以使用SHOW VARIABLES查看数据库中的所有参数，也可以通过LIKE来过滤参数名。

 show variables like 'innodb_buffer%'\G;

参数类型

动态参数意味着可以在MySQL实例运行中进行更改，静态参数说明在整个生命周期内部都不得进行更改，就好像是只读的。

可以通过set命令对动态参数进行更改。

  • 动态参数

image-20200220081835608

可以看到read_buffer_size从262KB调整到8KB，而用户登录看到的全局的read_buffer_size的值依然是262KB，也就是仅在本回话中才是这个数值。

image-20200220082250856

这次将read_buffer_size全局值更改为80KB，而当前会话的read_buffer_size的值依然还是8KB, 对变量的全局值进行了修改，在这次的实例生命周期内都有效，但MySQL本身不会对参数文件进行修改。在下次启动MySQL启动时，还是会读取参数文件。

若用户想在数据库实例下一次启动时该参数还是保留为当前的修改的值，那么用户必须去修改参数文件。

  • 静态参数

若对静态参数进行修改，会得到错误。

日志文件

错误日志（error log）

错误日志文件是对MySQL的启动、运行、关闭过程进行了记录。

MySQL DBA在遇到问题时应该首先查看该文件以便定位问题。该文件不仅记录了所有的错误信息，也记录一些警告信息或正确的信息。

image-20200220095449598

image-20200220100510681

image-20200220095912525

慢查询日志(show query log)

慢查询日志用来帮助DBA定位可能存在问题的SQL语句，从而进行SQL语句层面的优化，

  • long_query_time:

在MySQL启动时设置一个阈值，将运行时间操作该阈值的所有SQL语句记录到慢查询日志文件中。DBA每天或每过一段时间对其进行检查，确认是否有SQL语句需要进行优化。该阈值通过参数long_query_time进行设置。默认10秒。只有大于这个值才会被记录。

image-20200220104558756

另外一个和慢日志有关的参数log_queries_not_using_indexes.如果运行的SQL语句没有使用索引，则MySQL数据库同样会将这条SQL语句记录到慢查询日志文件中。

Screenshot 2020-02-20 at 10.49.34

  • 慢查询表slow_log:

image-20200220105311437

image-20200220110020170

image-20200220110104662

  • long_query_io:

image-20200220103223442

查询日志（log）

查询日志可以使用general_log表，使用方法和slow_log相同

二进制日志(binlog)

二进制文件记录了对MySQL数据库执行的更改操作，但是不包括SELECT和SHOW这类操作。因为这类操作对数据本身没有修改，然而，若操作本身并没有导致数据库发生变化，那么该操作也会写入二进制。

Screenshot 2020-02-20 at 11.28.13

Screenshot 2020-02-20 at 11.28.23

二进制查询的作用：

image-20200220113301714

Socket文件

image-20200220113508709

Pid文件

Pid文件是指当MySQL实例启动时，会将自己的进程ID写入PID文件中。

表结构定义文件

image-20200220114050661

InnoDB存储引擎文件

本节将介绍与InnoDB存储引擎密切的相关文件。

1.表空间文件

InnoDB采用将存储的数据按表空间（tablespace）进行存放和设计，在默认配置下会有一个10MB大小的文件，名为ibdata1文件，该文件就是默认的表空间文件(tablespace file)，用户可以通过参数innodb_data_file_path进行设置。

用户可以通过多个文件组成一个表空间，同时制定文件的属性：

innodb_data_file_path=/db/ibdata1:2000M;/dr2/db/ibdata2:2000M:autoextend

image-20200220190410934

image-20200220185941772

表Profile、t1和t2都是基于InnoDB存储的表，由于设置参数inondb_file_per_table=on, 因此产生了单独的.idb独立表空间文件。单独的表空间文件仅仅存储该表的数据、索引和插入缓冲BITMAP等信息。其余的信息还存放在默认的表空间中。

image-20200220184600370

2.重做日志文件

重做日志文件（redo log file）:

重做日志文件对InnoDB存储引擎至关重要，它们记录了对于InnoDB存储引擎的事务日志。

在默认情况下，在InnoDB存储引擎的数据目录下会有两个名为ib_logfile0和id_logfile1的文件。在MySQL官方手册中将其称为Innodb存储引擎的日志文件，更准确的定义是重做日志文件（redo log file）。

当实例或介质失败，重做日志文件就能派上用场。如，数据库主机掉电介质失败，InnoDB存储引擎会使用重做日志恢复到掉电前的水平，以此来保证数据的准确性。

image-20200220203718033

每个InnoDB存储引擎至少有1个重做日志文件组(group),每个文件组至少有2个重做日志文件，如默认的id_logfile0和ib_logfile1。为了得到更高的可靠性，用户可以设置多个的镜像日志组（mirrored log groups)，将不同的文件组放在不同的磁盘上，以此提高重做日志的高可用性。在日志组中每个重做日志文件的大小一致，并以循环写入的方式运行。InnoDB存储引擎先写重做日志文件1，当达到文件的最后时，会切换至重做日志文件2，再当重做日志文件2也被写满时，会再切换到重做日志文件1。

  • innodb_log_file_size

  • innodb_log_file_in_group

  • innodb_mirrored_log_groups

  • innodb_log_group_home_dir

image-20200220205821787

image-20200220205748487

总结

image-20200220190945800

表

索引组织表

在InnoDB存储引擎中，表都是根据主键顺序组织存放的，这种存储方式的表称为索引组织表(index organized table) 。在InnoDB存储引擎表中，每张表都有个主键（Primary Key），如果在创建表时没有显式地定义主键，则InnoDB存储引擎会按照如下方式选择或创建主键盘：

选择或者创建主键的方式：

  1. 首先判断表中是否有唯一的非空索引（unique not null）, 如果有，则该列即为主键。
  2. 如果不符合上述条件，InnoDB存储引擎自动创建一个6字节大小的指针。

当表中有多个非空唯一索引时，InnoDB存储引擎将选择建表时第一个非空索引作为主键。

注意：主键的选择根据的是定义索引的顺序，而不是建表时的顺序。

image-20200221102814738

_rowid可是显示表的主键，因此通过上述查询可以找到表z的主键。此外，虽然c d 列都是非空唯一索引，都可以作为主键的候选，但是在定义的过程中，由于d列首先定义唯一索引，故InnoDB存储引擎作为主键。

InnoDB逻辑存储结构

image-20200221105125401

表空间

表空间是Innodb存储结构的最高层，所有的数据都存放在表空间中。如果用户启用了参数innodb_file_per_table,则每张表内数据可以单独放到表空间内。

如果启用了innodb_file_per_table的参数，需要注意的是每张表的表空间内存放的只是数据、索引和插入缓存Bitmap页，其他类的数据，如回滚（undo）信息，插入缓冲索引页，系统事务信息，二次写缓冲（double write buffer）等还是存放在原来的共享表空间ibdata1内。

这也说明了，即使启用了参数innodb_file_per_table之后，共享表空间还是会不断增大。

Screenshot 2020-02-21 at 10.54.27

另外要注意的是，即使对事务进行rollback， ibdata1这个表也并不会缩减至原来大小。

段

image-20200221132315427

区

区的大小为1MB, 区是由连续页组成的，为了保证区中页的连续性，InnoDB一次会从磁盘中申请4~5个区。默认情况下存储引擎页的大小是16KB，即一个区中有连续64个页。

碎片页：（fragment page）

用户在启用了参数innodb_file_per_table后，创建的表默认大小是96KB。区中是64个连续的页。每个段开始，先用32页大小的碎片页来存放数据，在使用完这些页之后才是64个连续页的申请，一次来节省空间。

页

image-20200221134954990

行

image-20200221135057492

Innodb行记录格式

Innodb存储引擎以行的形势存储。这意味着页中保存着表中一行行的数据。

查看当前表使用的行格式：

image-20200223183242617

Compact行记录格式

image-20200223183706473

image-20200223183733073

create table mytest(
t1 varchar(10),
t2 varchar(10),
t3 char(10),
t4 varchar(10)
)engine=innodb row_format=compact;

Redundant行记录格式

image-20200223184556750

create table mytest2 engine=Innodb row_format=redundant as select * from mytest;

行溢出数据

InnonDB存储引擎可以将一条记录中的某些数据存储在真正的数据页面之外。

VARCHAR(N)中的N指的是字符长度。而文档中说明VARCHAR类型最大支持65535，单位是字节。

需要注意的是，MySQL官方手册中定义的 65535长度是指所有VARCHAR列的长度总和，如果列的长度总和超出这个长度，依然无法创建。

image-20200223190422835

Compressed 和 Dynamic行记录格式

image-20200223190712764

CHAR的行结构存储

从MySQL4.1版本开始，CHAR(N)中N指的是字符的个数！而不是之前版本中的字节长度。

在不同的字符集下，CHAR类型列内部存储的可能不是定长的数据。gbk下中文2个字符，utf-8下每个中文3个字节。

创建一个表，指定表中字段类型为2字符

CREATE TABLE j(a char(2))charset=utf8 engine=innodb;

insert into j select 'ab';
insert into j select '我们';
insert into j select 'a';

image-20200223192654968

InnoDB存储引擎内部对CHAR类型在多字节字符集类型的存储。CHAR类型被明确视为了变长字符类型，对于未能占满长度的字符还是填充0x20。

Innodb数据页结构

页是Innodb存储管理数据引擎的最小单位。

数据页由以下7部分组成：

image-20200223212248371

File Header用来记录页的一些头信息，由8部分组成，共占38字节。

Page Header接着File Header部分是Page Header, 该部分用来记录数据页的状态信息，由14部分组成，共占用56个字节。

Infimum 和 Supremum Record

image-20200223214134033

User Record 和 Free Space

User Record 实际存储行记录的内容

InnoDB存储引擎表总是B+索引组织的

Free Space很明显指的就是空闲空间，同样也是个链表数据结构。在一条记录被删除后，该空间会被加入到空闲链表中

image-20200223215059418

File Trailer

image-20200223215251551

Named File Format机制

InnoDB存储引擎通过Named File Format机制解决不同版本下页结构兼容性问题。

约束

数据完整性

关系型数据库和文件系统不同，关系数据库能保证存储数据的完整性，不需要应用程序的控制，而文件系统一般需要在程序端进行控制。当前几乎所有的关系型数据库都提供了约束机制，该机制保证了数据的完整性。

数据库的完整性有如下三种：

实体完整性：

保证表中有一个主键盘。在InnoDB存储引擎表中，用户可以通过定义Primary Key或Unique Key约束来保证实体的完整性。用户还可以通过编辑一个触发器来保证实体完整性。

域完整性：

保证每列的值满足特定的条件。在InnoDB存储引擎表中，域完整性可以通过以下几个途径来保证：

  • 选择合适的数据类型确保一个数据值满足特定条件
  • 外键（Foreign Key）约束
  • 编写触发器
  • 还可以考虑用DEFAULT约束作为强制域完整性的一个方面

参照完整性：

参照完整性保证两个表之间的关系。InnoDB支持外键，因此允许用户定义外键以强制参照完整性，也可以通过编写触发器以强制执行。

对于InnoDB存储引擎本身，提供了以下几种约束：

Primary Key

Unique Key

Foreign Key

Default Key

Not NULL

约束的创建和查找

约束创建时可采用如下两种方式：

  • 表建立时就进行约束定义
  • 利用ALTER TABLE命令进行创建约束

important:

  • Foreign key约束并不仅仅可以与另一表中的Primary key相连接，还可以定义为应用另一表的unique约束。Foreign key约束可以包含空值，但是如果任何组合Foreign key约束的列包含空值，则将跳过组成foreign key约束的所有值的验证。若要确保验证了组合foreign key约束的所有值，请将所有参与列定义为NOT NULL。

  • unique的数据项可以为空，外键的值也可以为空，外键也可以关联unique的数据

创建u表和p表，p表存在外键，引用主键u

create table u(id int, name varchar(20), id_card char(18), primary key(id),unique key(name));

create table p(id int,u_id int,primary key(id),foreign key(u_id) references u(id));

在u表数据为空的情况下，向p表中插入数据，可以发现报错。

image-20200224114919624

在u表数据存在1，向p表中插入数据，外键盘数据也为1，可以发现成功！

image-20200224115045588

那么如果保证在主键不存在的情况下，向外键表中插入值呢？

mysql中有一个参数FOREIGN_KEY_CHECKS

利用这个参数,设置

set FOREIGN_KEY_CHECKS=0

此时可以执行p表添加操作

添加完成后，将FOREIGN_KEY_CHECKS改为1即可

set FOREIGN_KEY_CHECKS=1

对错误数据的约束

MySQL数据库允许非法的或不正确的数据插入或更新，又或者可以在数据库内部将其转化为一个合法的值，如向NOT NULL的字段插入一个NULL值，Mysql会将其更改为0再插入。

image-20200225165829721

set sql_mode='STRICT_TRANS_TABLES'

ENUM和SET约束

image-20200225195540713

create table a(id int,sex enum('male','female'));
insert into a_enum select 1,'female';
show variables like 'sql_mode'\G;

约束和索引的区别

image-20200225220303393

触发器与约束

触发器的作用是在执行INSERT DELETE 和 UPDATE命令之前或之后自动调用SQL命令或存储过程。

创建触发器的命令是CREATE TRIGGER，只有具备Super权限的MySQL数据库用户才可以自行这条命令：

CREATE
[DEFINER={user|CURRENT_USER}]
TRIGGER trigger_name BEFORE|AFTER INSERT|UPDATE|DELETE
ON tb1_name FOR EACH ROW trigger_stmt

最多可以为一个表建立6个触发器。当前MySQL只支持FOR EACH STATEMENT 的触发方式。

DELIMITER $$

CREATE TABLE usercash_err_log BEFORE UPDATE ON usercash(
	FOR EACH ROW
	BEGIN 
	IF new.cash-oil.cash > 0 THEN
	INSERT INTO usercash_err_log
	SELECT old.userid, old.cash, new.cash,USER(),NOW();
	SET new.cash = old.cash;
	END IF;
	END;
	$$
)

DELIMITER $$

上述例子首相穿件一张usercash_err_log来记录错误数值更新的日志，然后创建了进行约束操作的触发器tgr_usercash_update，其类型为BEFORE。 触发器首先判断新、旧值之间的差值，在正常情况下消费总是减的，新值应该总是小于原来的值，因此大于原值的数据被判断为非法的输入，将cash值设定为原来的值，并将非法的数据更新插入表usercash_err_log.

外键约束

外键用来保证参照完整性，MySQL存储引擎完整支持外键约束。

外键定义如下：

image-20200308163223788

用户可以在执行CREATE TABLE时就添加外键，也可以在表创建后通过ALTER TABLE命令来添加。

CREATE TABLE parent(
id INT NOT NULL,
PRIMARY KEY(id)
)ENGINE = INNODB;

CREATE TABLE child(
id INT, parent_id INT,
FOREIGN KEY(parent_id) REFERENCES parent(id)
)ENGINE=INNODB;

对子表所做的操作，可定义的子表操作有：

CASCADE

SET NULL

NO ACTION

RESTRICT

image-20200308164618669

对于参照完整性约束，外键能起到一个非常好的作用。但是对于数据的导入时，外键导致在外键约束的检查上花费大量时间。因为MySQL数据库的外键是即时检查的，所以对导入的每一行都会进行外键检查。

但是用户可以关闭外键检查：

SET foreign_key_checks = 0;
SET foreign_key_checks = 1;

视图

在 MySQL数据库中，视图(View)是一个命名的虚表，它由一个SQL查询来定义，可以当做表使用。

与持久表(permanent table)不同的是，视图中的数据没有实际的物理存储。

视图的作用

创建视图

image-20200308184854826

修改视图

image-20200308184924971

create view v_t as select * from t where id<10;
insert into v_t select 20;
select * from v_t;

上面的例子中，创建了一个id<10的视图v_t。但之后向视图里插入了id为20的值，插入没有报错。但是用户查询还是没能查到数据。接着更改视图定义WITH CHECK OPTION选项。可以发现错误。

alter view v_t as select * from t where id<10 with check option;
insert into v_t select 20;
show tables\G;

物化视图

物化视图可以用于预先计算并保存多表的链接(JOIN)或聚集(GROUP BY)等耗时较多的SQL操作。

物化视图再SQL Server数据库中，称这种视图为索引视图。

image-20200308220014056

分区表

对于OLAP应用，分区可以提高查询性能。而对于OLTP分布，并不一定有用。

通过以下命令来查看当前数据库是否启用了分区功能：

SHOW VARIABLES LIKE '%partition%'\G;
SHOW PLUGINS\G;

当前MySQL数据库支持以下几种类型的分区：

Screenshot 2020-03-08 at 23.00.57

参考如下：

mysql表分区详解

总结

image-20200221104126970

 
分类: mysql
posted @ 2020-04-21 16:44  Mr-Lee-long  阅读(5028)  评论(0)  编辑  收藏  举报