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。

posted @ 2018-07-06 08:56  dark_saber  阅读(231)  评论(0编辑  收藏  举报