MySQL技术内幕：InnoDB存储引擎（第3章文件）

MySQL技术内幕：InnoDB存储引擎（第3章文件）

第3章文件

参数文件（my.cnf）

mysql --help | grep my.cnf
在当前实例生命周期有效，重启后还是读配置文件

• 静态

  只能改配置文件，并重启

• 动态

  在当前实例生命周期有效，重启后还是读配置文件

  • session（当前会话有效）
  • global（全局有效）

日志文件

• 错误日志（log_error）（error.log）

  show VARIABLES like ‘%log_error%’;

• 慢查询日志（slow_query_log）

  • 参数

    slow_query_log：是否开启
    slow_query_log_file：慢查询文件
    long_query_time：阈值时间
    log_queries_not_using_indexes：是否记录没有使用索引的查询，开启会写入到慢日志中
    log_throttle_queries_not_using_indexes：每分钟允许记录到slow log的并且未使用首页的sql语句次数，默认0，表示没有限制

  • log_output（指定输出格式）

    • FILE（默认）

      mysqldumpslow命令查看分析慢查询：

      1. 命令：mysqldumpslow 慢查询文件
      2. 查看前10的慢查询： mysqldumpslow -s al -n 10 慢查询文件

    • TABLE（mysql.slow_log）

      set global slow_query_log=off; – 关闭慢查询
      set global log_output=‘TABLE’; – 修改为TABLE
      alter table mysql.slow_log engine=MyISAM; – 修改engine，提高效率
      set global slow_query_log=on; – 开启慢查询
      – 测试
      select SLEEP(10);
      – 查看慢日志
      SELECT * from mysql.slow_log;

      补充：
      show CREATE TABLE mysql.slow_log; --查看表结构

• 查询日志（general_log）

  show VARIABLES like ‘%general%’;
  general_log：是否开启
  general_log_file：文件位置

• 二进制日志（binary log，binlog）

  记录对数据库进行更改的操作，不包括select和show，这些操作不会修改数据。
  查看命令：
  show VARIABLES like ‘%bin%’;
  log_bin：是否开启二进制日志
  log_bin_basename：文件地址
  log_bin_index：文件索引
  – 查看当前binlog状态
  show MASTER status;
  – 查看对应binlog详情
  show BINLOG EVENTS in ‘binlog.000018’;
  – 查看数据库文件位置
  show VARIABLES like ‘%datadir%’;

  • 作用

    • 恢复（recovery）

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

    • 复制（replication）

      通过复制和执行二进制日志进行实时同步。

    • 审计（audit）

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

  • 参数

    • max_binlog_size（单个最大文件）

      二进制文件最大值，超过则生成新文件，默认1G

    • binlog_cache_size（会话级别，未提交事务二进制日志缓存，默认32K）

      会话级别，所有未提交的二进制日志会被记录到该缓存中，等该事务提交时直接将缓冲中的二进制日志写入到二进制日志文件中，默认大小为32K。
      当一个事务记录大于该设置时，会写入到临时文件中

      • Binlog_cache_disk_use（写临时文件次数）

        记录使用了临时文件写二进制日志次数

      • Binlog_cache_use（写入缓存次数）

        记录使用了缓冲写二进制日志的次数

    • sync_binlog（默认1）

      表示采用同步写磁盘方式来写二进制日志，不使用操作系统缓冲。默认1，建议为1，保证高可用性

    • log_slave_updates（slave角色是否写二进制日志）

      如果是slave角色，需要开启该参数，才会将二进制日志写到自己的二进制日志文件中。

    • binlog_format

      • STATEMENT

        二进制日志记录的是逻辑SQL语句，对UUID(),USER()等函数在SLAVE会产生不确定值。

      • ROW

        记录表的行更改情况，不再是简单的记录SQL语句。

      • MIXED

        默认使用STATEMENT格式，在UUID(),USER()等函数时使用ROW格式。

  • 查看说明（mysqlbinlog）（效果图）

    查看工具： STATEMENT可以直接查看到SQL；ROW需要加上 -vv参数才能看到内容
    mysqlbinlog -vv --start-position=203 test.00004
    

套接字文件（socket）

套接字文件：可用于本地通信
查看命令：
show VARIABLES like ‘%socket%’;

pid文件（进程号文件）

pid文件：
将自己的进行ID写入到该文件中
查看命令:
show VARIABLES like ‘%pid_file%’;

表结构定义文件（frm）

frm文件定义表结构

InnoDB存储引擎文件

• 表空间（tablespace）

  • 共享表空间（ibdata1）

    show variables like ‘innodb_data_%’;
    享表空间是将所有的表的数据和索引保存在ibdata1中，将存储数据按表空间（table space）进行存放，默认12M，名为ibdata1
    可以由多个文件组成表空间：
    innodb_data_file_path=/var/lib/mysql/ibdata1:2000M;/var/lib/mysql/ibdata2:2000M:12M:autoextend

  • 独立表空间（innodb_file_per_table，表名.ibd）

    启用独立表空间参数：
    innodb_file_per_table=ON
    show VARIABLES like ‘%innodb_file_per_table%’;
    为每张表数据使用单独文件存放，命名规则：表名.ibd

• 重做日志文件（redo log）

  默认：ib_logfile0和ib_logfile1两个文件，非常重要，记录了事务日志。当实例会或介质失败时，可通过重做日志恢复数据到断电前的时刻，保证数据完整性。

  一般情况下我们可以按照每1GB的Redo log的恢复时间大约在5分钟左右来估算。

  • 日志文件组（group）

    至少有1个重做日志文件组（group），每个组至少有2个重做日志文件，默认ib_logfile0，ib_logfile1。

  • 镜像日志组（mirrored log groups）

    用户可以设置多个镜像日志组（mirrored log groups），不同组放到不同磁盘上，提供重做日志可用性。

  • 写入方式

  • 

    在日志组中每个重做日志大小一致，并以循环写入方式运行。1写满，则切换到2写，2写满，切换到1写。

  • 重做日志格式（图）

    redo_log_type（1，重做日志类型），space（可以压缩，可能小于4，表空间id），page_no（页偏移量，可以压缩），redo_log_body（重做日志数据）

    • 

  • 参数

    innodb_log_file_size：重做日志大小
    innodb_log_files_in_group：指定重做日志组中重做日志数量，默认2
    innodb_log_group_home_dir：指定重做日志文件所在路径，默认 ./ 表示数据库的数据目录下。

    innodb_flush_log_at_trx_commit控制事务提交时处理重做日志方式，0，1，2。默认为1，保证事务的完整性。
    0：事务提交时不会触发写入，等待主线程每秒刷新
    1.commit时将重做日志同步刷新到磁盘（fsync）
    2.commit时将重做日志写到文件系统缓存中，不能完全保证commit时写入重做日志。

  • 和二进制日志区别：

    • 记录对象不同

      二进制日志记录所有与MySQL数据库相关日志记录，包括Innodb、MyISAM等引擎日志
      重做日志只记录有关InnoDB引擎本身的事务日志

    • 记录内容不同

      二进制日志记录的都是关于一个事务具体操作内容，即该日志为逻辑日志
      记录关于每个页（page）的更改物理情况。

    • 写入时间不同

      二进制日志仅在事务提交前进行提交，即只写入磁盘一次（不论事务有多大）
      事务进行过程中，不断有重做日志被写入到重做日志文件中

  • 写入过程（按扇区为单位进行写入，1扇区=512字节，1页=16K=32扇区）

    重做日志先写入重做日志缓冲区（redo log buffer），然后按照一定条件顺序写入日志文件。写入磁盘时按512字节（16K-页，相当于32个扇区），即一个扇区大小写入。
    因为扇区是写入的最小单位，因此可以保证写入必定是成功的，因此不需要有doublewrite。
    补充：硬盘的最小存储单位是扇区，并且块中的扇区是连续的。
    综上mysql页默认16K大小，那么脏页刷新时会涉及到物理磁盘32个扇区。刷新脏页过程中如果遇到断电，大概率存在扇区只写了一部分的情况，这时候就需要了解mysql一个高级特性double write。

  • 执行重做日志条件

    1.master thread每秒会将重做日志缓冲写入到磁盘（不论事务是否已经提交）
    2.提交事务时根据innodb_flush_log_at_trx_commit值来决定写入方式

  • 设置注意点

    重做日志不能太大，很大可能会导致恢复时间很长；
    不能设置太小，太小可能会导致一个事务日志需要多次切换重做日志文件，频繁发生async checkpoint，导致性能抖动。
    重做日志有一个capacity变量，代表最后检查点不能超过这个阈值，超过则必须将缓冲池中脏页部分刷新到磁盘，导致用户线程阻塞。