MySQL技术内幕3文件

3.1参数文件

3.1.1 什么是参数

可以通过SHOW VARIABLES查看数据库中的所有参数

从5.1版本开始，可以通过information_schema架构下的GLOBAL_VARIABLES视图

3.1.2 参数类型

MySQL数据库中的参数可以氛围两类：

  • 动态参数：整个实例生命周期内不得进行更改
  • 静态参数：可以在MySQL实例运行过程中进行更改

静态参数不可进行修改

可以通过set参数来对动态参数进行设置
SET
| [global | session] system_var_name=expr
| [@@global. | @@session. | @@] system_var_name=expr

global/session表明该参数的修改是基于当前会话还是整个实例的生命周期

对MySQL变量的全局值进行了修改，在这次的实例生命周期内都有效，但MySQL实例本身不会对参数文件中的该值进行修改。也就是说，下一次启动MySQL实例还是会读取参数文件，要保留当前文件，那么用户需要修改参数文件。

3.2 日志文件

MySQL中常见的日志：

  • 错误日志 error log
  • 二进制日志 binlog
  • 慢查询日志 slow query log
  • 查询日志 log

3.2.1 错误日志

SHOW VARIABLES LIKE 'log_error';定位错误日志文件的位置

3.2.2 慢查询日志

帮助定位可能存在问题的SQL语句，从而进行SQL语句层面的优化。阈值可以通过long_query_time来设置，默认为10秒

log_slow_queries: 默认情况下，不启动慢查询日志，需要手动设置参数‘log_slow_queries’
log_queries_not_using_indexes: 另一个重要参数log_queries_not_using_indexes控制是否记录没有使用索引的查询语句

5.6.5版本之后，新增了一个参数log_throttle_queries_not_using_indexes，表示每分钟允许记录到slow log的且未使用索引的SQL的语句次数

mysqldumpslow 
--verbose verbose
--debug debug
--help help

-v verbose
-d debug
-s ORDER 
    al:average lock time
    ar:average rows time
    at:average query time
    c: count
    l: lock time
    r: rows sent
    t: query time
-r reverse the sort order
-t NUM top n queries
-a 不将数字抽象成N，字符串抽象成‘S’
-n NUM 将数字抽象成至少N位的数字
-g PATTERN 正则表达式
-h HOSTNAME 数据库的主机
-i NAME 服务器实例名
-l 不从总时间减去lock时间

5.1版本开始会有一张慢查询表slow_log，可以通过SHOW CREATE TABLE mysql.slow_log;G查看
将参数log_out从‘FILE'可以设为‘TABLE’，然后就可以通过show_log表进行查询了

查看slow_log表的定义会发现该表使用的CSV引擎。用户可以把slow_log引擎转换到MyISAM，并在start_time列上缇娜家索引可以进一步提高查询的效率。如果已经启动了慢查询，将会提示错误。

InnoSQL中加强了SQL语句的捕获模式，增加了逻辑读取与物理读取
long_query_io，默认100，表示对于逻辑读取次数大于100的SQL语句记录到slow log,
slow_query_type表示启用SQL的方式:

  • 0表示不将SQL语句记录到slow log
  • 1表示根据SQL将SQL记录到slow log
  • 2根据逻辑IO次数将SQL记录到slow log
  • 3根据运行事件和逻辑IO次数将SQL语句记录到slow log

3.2.3 查询日志

查询日志记录了所有MySQL数据库请求的信息。默认文件名：主机名.log

5.1版开始，可以将查询日志放入mysql架构下的general_log表中，使用方法和slow_log表一样

3.2.4 二进制日志

二进制日志记录了所有对MySQL数据库执行更改的所有操作，但是不包括select和show这类操作。
SHOW BINLOG EVENTS IN 'mysqld.000008'G;

二进制日志的主要作用：

  • 恢复：某些数据的恢复需要二进制日志
  • 复制：通过复制和执行二进制日志是一个远程MySQL数据库和一台MySQL数据库进行实时同步
  • 审计：用户可以通过二进制日志中的信息来进行审计，判断是否有对数据库进行注入的攻击

通过配置参数log-bin[=name]可以启动二进制日志。如果不指定name，则默认二进制日志文件为主机名，后缀名为二进制日志的序列号，所在路径为数据库所在目录（datadir）。
bin_log.index为二进制的索引文件，用来存储过往产生的二进制文件
二进制日志文件在默认情况下并没有启动，需要手动指定参数来启动。

以下配置文件影响着二进制日志记录的信息和行为：

  • max_binlog_size：单个二进制日志文件的最大值
  • binlog_cache_size：决定缓冲二进制日志的大小，基于会话，可以通过SHOW GLOBAL STATUS查看binlog_cache_use（记录使用缓冲写二进制日志的次数），binlog_disk_use（记录了使用临时文件来写二进制日志的次数）来判断大小是否合适
  • sync_binlog：表示每写缓冲多少次就写到磁盘；默认为0，1表示同步写磁盘的方式来写二进制日志，不实用缓冲。通过参数innodb_support_xa=1来确保二进制日志和InnoDB存储文件的同步
  • binlog-do-db：表示需要写入哪些库的日志
  • binlog-ignore-db：表示需要忽略写入哪些库的日志
  • log-slave-update：如果当前数据库为slave，且需要从master取得并执行的二进制日志写入自己的二进制日志，则需要设置，如果需要master=>slave=>slave，则必须设置该参数
  • binlog_format：影响记录二进制日志的格式

binlog_format:

  1. STATEMENT日志的逻辑SQL语句
  2. ROW 记录表更改情况，可以将InnoDB的事务隔离级别设置为READ_COMMITED，能获得更好的并发性
  3. MIXED 格式下，默认使用STATEMENT格式，一些情况下会使用ROW
    1. 存储引擎为NDB，DML操作都用ROW格式
    2. UUID(),USER(),CURRENT_USER(),FOUND_ROWS(),ROW_COUNT()等不确定的函数
    3. 使用INSERT DELAY语句
    4. 使用了用户定义函数UDF
    5. 使用了临时表（temporary table）

查看二进制日志文件需要通过MySQL提供的工具mysqlbinlog

3.3 套接字文件

在UNIX下本地系统mysql可以用UNIX域套接字方式。套接字文件由参数socket控制。一般为/tmp/mysql.socket

3.4 pid文件

当MySQL实例启东时，会将自己的进程ID写入一个文件中即PID文件。该文件由pid_file控制，默认位于数据库目录下，文件名为主机名.pid

3.5 表结构定义文件

frm文件用来存放表结构和视图的定义

3.6 InnoDB存储引擎文件

3.6.1 表空间文件

在默认配置下会有一个初始大小为10MB名为ibddata1的默认表空间文件，用户可以通过innodb_data_file_path对其进行设置
用户可以多个文件组成一个表空间

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

设置innodb_data_file_path参数后，所有基于innodb存储引擎的表的数据都会记录到该共享表的空间中。若设置了参数innodb_file_per_table，则用户可以将每个基于innodb存储引擎的表产生一个独立表空间。独立表空间的命名规则：表名.ibd
单独的表仅存储该表的数据，索引和插入缓冲BITMAP等信息。

3.6.2 重做日志文件redo log file

在默认情况下，innoDB存储引擎的数据目录下会有两个名为ib_logfile0和ib_logfile1的文件。每个InnoDB存储引擎至少有1个重做日志文件组，每个文件组下至少两个重做日志文件ib_logfile0和ib_logfile1
下列参数影响者重做日志文件的属性：

  • innodb_log_file_size: 指定每个重做日志文件的大小
  • innodb_log_flles_in_group：指定日志文件组中重做日志文件的数量，默认为2.
  • innodb_mirrored_log_groups：指定日志镜像文件组的数量，默认为1
  • innodb_log_group_home_dir：指定了日志文件组的路径，默认为./，表示在MySQL的数据目录下

重做日志文件大小对存储引擎性能性能影响很大：

  1. 如果设置得太大，在恢复时可能需要很长时间
  2. 如果设置得太小，可能一个事务的日志需要多次切换重做日志文件，此外，还会导致频繁地发生async checkpoint，导致性能的抖动。

重做日志有个capacity变量，该表代表了最后的检查点不能超过这个阈值，否贼必须将缓冲池中的脏页中的部分脏数据写回，这会导致用户线程的阻塞

重做日志和二进制日志的区别

  1. 二进制日志会记录所有与MySQL有关的日志记录，包括InnoDB，MyISAM等其他存储引擎的日志。而InnoDB存储引擎的重做日志只记录相关引擎本身的事务日志
  2. 记录的内容不同。二进制日志记录事务的具体操作内容即该日志是逻辑日志。而InnoDB存储引擎的重做日志记录的是关于每个页的更改的物理情况
  3. 写入时间不同。二进制日志文件需要在事务提交前提交，即只写磁盘一次。而事务进行过程中不断写入重做日志文件中。

重做日志条目结构

  • redo_log_type：占用1字节，表示重做日志的类型
  • space： 表示表空间的ID，但采用压缩的方式，因此占用的空间可能小于4字节
  • page_no：表示页的偏移量，同样采用压缩的方式
  • redo_log_body：表示每个重做日志的数据部分，恢复时需要调用相应的函数进行解析

从日志往磁盘写入时，是按512字节，即一个扇区写入，不需要double write

另一个出发写磁盘的过程是由参数innodb_flush_log_at_trx_commit控制，表示在提交时，处理重做日志的方式:
0: 当提交事务时，不将事务的重做日志写入磁盘上的日志文件，而是等待主线程每秒的刷新
1：表示在执行commit是将重做日志缓冲同步写到磁盘，即伴有fsync的调用
2：表示将重做日志一步写到磁盘中，即写到文件系统的缓存中。

为了保持事务中ACID持久性必须将innodb_flush_log_at_trx_commit置为1，也就是每当有事务提交时，必须确保事务已经写入重做日志文件。当设置为2时，如果当MySQL宕机，但操作系统及服务器并没有发生宕机时，由于此时未写入磁盘的事务日志文件保存在文件系统缓存中，当恢复时同样保证数据不会丢失。

【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/zhouyu0-0/p/11804940.html