MySQL笔记(3)---文件

1.前言

第二章简单记录了一下InnoDB存储引擎的一个基本内容，介绍了保证高效插入的Insert Buffer，change Buffer和确保数据安全的write ahead log以及double write机制，还介绍了查询和保存的内存策略，LRU列表、Free列表以及Flush列表，与此同时相关的线程操作，Purge Thread（回收undo页）、Page Cleaner Thread（刷新脏页），这两个操作在早期版本是在主线程中执行的。内容有些凌乱，主要是对物理结构没有进行说明，本章记录一下MySQL的文件。

MySQL的文件主要有以下几种：

参数文件：启动参数文件

日志文件：错误日志、二进制日志、慢查询日志、查询日志

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

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

MySQL表结构文件

存储引擎文件：本章介绍InnoDB相关内容

2.参数文件

mysql启动时会读取一个配置文件，按照一定的顺序在指定的位置进行读取，用户只需要通过命令mysql --help | grep my.cnf来找即可。

oracle在找不到配置的时候，会出错无法装载，mysql会使用默认值进行初始化。

SHOW VARIABLES命令可以查看参数。

参数分为两类：静态参数和动态参数。动态参数意味着在运行过程中可以进行修改，静态就是运行过程中不能修改。通过SET命令来修改动态参数。

3.日志文件

3.1 错误日志

错误日志对MySQL的启动、运行、关闭过程进行了记录。通过SHOW VARIABLES like "log_error"查看文件位置

通过错误日志可以排错，或者优化数据库参数。

3.2 慢查询日志

慢查询日志可以帮助定位可能存在问题的SQL语句，从SQL层进行优化。

可以在MySQL启动时设置一个阈值，运行超过该值的SQL语句记录到慢查询日志文件中，long_query_time来设置，默认值10，10秒，需要超过这个值，等于不行。

默认情况，MySQL不启动慢查询，需要手动设置参数为on

SHOW VARIABLES LIKE 'long_query_time'

SHOW VARIABLES LIKE 'log_slow_queries'

此外，还有一个参数log_queries_not_using_indexes参数，设置为on，会将没有使用索引的语句加入慢查询日志。

MySQL 5.6.5开始新增参数log_throttle_queries_not_using_indexes，用来表示每分钟允许记录到slow log的且未使用索引的SQL语句次数。默认为0，表示没有限制。

mysqldumpslow命令可以用于分析慢查询日志，mysqldumpslow xx-slow.log

MySQL5.1开始，将该日志放入了一张表mysql.slow_log中，方便查询且更直观。参数log_output决定了输出格式，默认是FILE。SHOW VARIABLES LIKE ‘log_output'，可以改成TABLE。SET GLOBAL log_output='TABLE'。

slow_log表如果使用CSV引擎，在大数据的情况下性能不好，可以切换成MyISAM。开启慢查询过程中是不能切换的，可以先暂停。

SET GLOBAL slow_query_log=off

ALTER TABLE mysql.slow_log ENGINE=MyISAM

InnoDB加强了对SQL语句的捕获方式，因为早期数据少，查询不会很慢。slow_log中加入了逻辑读取和物理读取的统计，物理读取指的是磁盘IO，逻辑读取包含所有读取，缓冲池与磁盘等。long_query_io可以将超过指定数量的逻辑IO次数的SQL记录到slow_log中，默认值100。

兼容原MySQL数据库运行的方式，还添加了参数slow_query_type，用来表示启用slow log的方式。可选值为：

0表示不将SQL语句记录到slow log

1表示根据运行时间将SQL记录到slow log

2表示根据逻辑IO次数将SQL语句记录到slow log

3表示根据运行时间及逻辑IO次数将SQL语句记录到slowlog

3.3 查询日志

查询日志记录了所有MySQL数据库请求的信息，包括执行错误的。默认文件名为：主机名.log。

可以将其放入general_log中，操作基本和慢查询日志差不多。

3.4 二进制日志

二进制日志记录了对MySQL数据库执行的更改操作，不包括查询。即使Update操作没有修改数据库本身，这个也会写入二进制日志。

SHOW BINLOG EVENT可以查询记录。

二级制日志主要有以下几种作用：

恢复：某些数据的恢复需要二进制日志，如在一个数据库全备文件恢复后，可以通过二进制日志进行point-in-time恢复

复制：原理与恢复类似，通过复制和执行二进制日志文件，使一台从节点与一台主节点进行实时同步

审计：用户可以通过二进制日志中的信息来进行审计，判断是否有对数据库进行注入的攻击。

通过配置参数可以启动二进制日志文件log-bin[=name] 如果不指定name，则默认名为主机名。这个功能需要配置开启，开启会损失一点性能，大概1%，但是可以用于复制和恢复，可以接受。日志文件在配置的datadir下面，show variables like "datadir"查找。以下配置与二进制日志相关：

max_binlog_size：单个文件的最大大小，超过会产生一个新的二进制文件，后缀+1。默认1G

binlog_cache_size：未提交的事务的二级制日志会先写到缓存，提交后才写入文件，这个参数决定缓存的大小。基于会话的，一个会话分配一个，所以不能过大，也不能太小。可以通过SHOW GLOBAL STATUS命令查看binlog_cache_use、binlog_cache_disk_use的状态，判断当前的设置是否合适，前者记录使用缓存的次数，后者是使用临时文件写二进制日志的次数。

sync_binlog：不是每次写都更新一次二进制文件，这个参数可以控制每写缓存多少次同步到磁盘。为1表示采取同步写磁盘的方式来写二级制日志，不使用缓冲写。默认是0。同步写会产生一个问题，就是事务未提交之前，操作记录被写入磁盘，这个时候宕机，下次启动没有提交操作，记录会被回滚，但是二进制日志记录了该事务，所以不能被回滚。可以设置innodb_support_xa为1解决这个问题。

binlog-do-db：写入哪些库的日志

binlog-ignore-db: 忽略写入哪些库的日志

log-slave-update：如果是从节点，它不会写入主节点发送的二级制日志到自己的文件中，设置这个值可以写入。

binlog_format：该参数很重要，影响了二进制日志的格式。5.1版本前没有这个参数。可以设置的值有：

STATEMENT：记录的是日志的逻辑SQL语句

ROW：记录表的行更改情况，可以将事务隔离级别设置成READ COMMITED，获得更好的并发性

MIXED：混合格式，默认使用STATEMENT，但是在以下情况使用ROW格式：

1.存储引擎为NDB，DML操作都会以ROW格式记录

2.UUID、USER、CURRENT_USER、FOUND_ROWS、ROW_COUNT等不确定的函数

3.使用了INSERT DELAY语句

4.使用了用户定义函数UDF

5.使用了临时表

此外这种格式还有存储引擎的限制，Blockhole不支持ROW格式，NDB不支持STATEMENT格式。

该参数是动态参数。另外ROW格式对磁盘空间要求有所增加，复制采取的是传输二进制日志方式，所以网络开销也会增加。

使用mysqlbinlog命令来查看二进制日志文件内容。

3.5 套接字文件

UNIX系统下本地连接MySQL可以采取UNIX域套接字方式，这个由参数socket控制，SHOW VARIABLES LIKE ’socket'

3.6 pid文件

MySQL启动时，会将自己的进程ID写入一个文件中——该文件即为pid文件。SHOW VARIABLES LIKE ‘pid_file'查看位置。

3.7 表结构定义文件

MySQL每个表都有对应的文件，不论表是什么存储引擎，都有一个frm后缀的文件，这个文件记录了该表的表结构定义。

frm还用来存放视图的定义，如果用户创建了一个v_a视图，就会产生一个v_a.frm文件。视图的文件是文本，可以直接使用cat命令查看。

3.8 InnoDB存储引擎文件

每个存储引擎有自己独有的文件。

3.8.1表空间

将存储的数据按照表空间进行存放。默认情况有一个10MB初始大小的ibdata1文件，这个就是默认的表空间文件。

通过参数innodb_data_file_path对其设置，将多个文件组成一个表空间。如果两个文件在不同的磁盘上组成表空间，磁盘负载可能被平均，因此可以提高数据库性能。

所有的表数据都会放入表空间，如果设置innodb_file_per_table可以为每个表产生一个独立的表空间，表名.ibd。单独的表空间只保存数据、索引和插入缓冲的bitmap等信息，其他的还是保存在默认表空间中。

3.8.2 重做日志文件

默认情况，有两个文件ib_logfile0和ib_logfile1，这个是重做日志文件。可以在宕机的情况，通过重做日志恢复到之前的状态。

Innodb至少需要一个重做组，里面至少两个文件，为了提供可靠性，可以设置多个镜像日志组，将不同的文件放在不同磁盘上，提供重做日志的高可用性。

日志组中，每个重做日志大小一致，循环写入的方式运行。先写重做日志1，到达文件的最后时，会切换到重做日志2，2被写满时，会再切换到1。

以下参数与重做日志相关：

innodb_log_file_size:每个重做日志的大小，1.2版本前不能超过4GB

innodb_log_files_in_group:日志文件组中，重做文件的数量，默认2

innodb_mirrored_log_groups:指定日志镜像文件组的数量，默认1

innodb_log_group_home_dir:指定日志文件组所在路径

重做日志对数据库影响很大。设置太大，恢复耗时很久，设置太小，一个事务的日志可能需要多次切换冲重做日志文件，并且会导致频繁的发送async checkpoint，性能抖动。

重做日志和二进制日志有啥区别呢？定义重做日志只记录有关该引擎本身的事务日志。其次，记录的内容不同，无论用户将二进制日志文件格式设置成什么，其记录的是页的物理情况，而不是逻辑日志。二级制文件只在事务提交前，只写写一次。重做日志在事务进行过程中，可能不断产生。

1.2版本定义了51种重做日志的类型，但有基本的重做格式：

redo_log_type：表示重做日志的类型，1字节

space: 表空间ID，采取压缩方式可能小于4字节

page_no: 表示页的偏移量，同样使用压缩的方式

redo_log_body:表示每个重做日志的数据部分，恢复时需要调用相应的函数进行。

第二章说过，重做日志不是直接写，而是先写入缓存，再按一定的条件顺序写入日志文件。写入磁盘是按512个字节，1个扇区写入的，保证写入必定成功。所以重做过程不需要double write。

innodb_flush_log_at_trx_commit的有效值为0,1,2。0表示提交事务时，不刷新日志到磁盘，等主线程执行。1表示将执行的commit时将重做日志缓冲同步到磁盘。fysnc。2表示重做日志异步写到磁盘，不保证完成。所以保证事务的时候，这个值必须设置成1。