MySQL笔记-InnoDB存储引擎数据文件和日志文件

作者:聂勇 欢迎转载,请保留作者信息并说明文章来源!

以前做电信项目主要用Oracle, Sybase等商业数据库,现在做互联网项目,主要使用开源数据库:MySQL。在使用的过程中有一些笔记,整理成几篇文章(所谓一个好脑袋不如一个烂笔头,记录下来方便后续查看 ^_^ )。

环境 | Enviroment

  • MySQL-5.1.*

数据文件与表空间 | Data File And Table Space

InnoDB的数据文件主要有两种:

  • *.frm存放表结构定义相关的元数据。
  • .idb或idbdata文件存放表中的数据和所有的索引数据。对于表中的数据,采用聚集(clustered)方式,每张表的存储都按主键的顺序存放,如果没有显式地在表定义时指定主键,InnoDB存储引擎会为每一行生成一个6字节的ROWID,并以此作为主键。

创建主键的规则如下:

  • 表中有非空的唯一索引,索引表为主键。
  • 无非空的唯一索引,自动创建一个6字节大小的主键。

InnoDB的逻辑结构类似Oracle,分表空间(table space)、段(segment)、区(extent)、块/页(block/page)。
InnoDB的逻辑结构

默认情况下,所有数据都存储在共享表空间中。如果设置了innodb_file_per_table,每个表一个*.idb文件,每张表一个单独的表空间(存放数据,索引,插入缓冲),但撤销信息,系统事务信息,二次写缓冲还是在共享表空间中。

区由64个连续的页组成,每页16KB。对于大的数据段,innodb引擎每次可以申请4个区(4MB)来保证数据的顺序和性能。

缓存池 | Buffer Pool

缓存池(Buffer pool)缓存的数据有:数据页、索引页、undo页、插入缓冲(insert buffer)、自适应哈希索引(adaptive hash index)、锁信息(lock info)、数据字典(data dirctionary)。用show engine innodb status命令看到的Buffer pool size为缓冲贴的数量,每个页16KB。

重做日志在日志缓冲区。

额外的内存池(additional pool)存放的数据:buffer pool中frame buffer和buffer control block对应的LRU、锁、等待待方面的信息。

日志文件 | Log File

1、查询日志(所有的SQL和操作指令都会记录)
1)配置文件对应的配置项:

  • general_log = 1|OFF (开关)
  • general_log_file = /home/nieyong/local/mysql-5156/log/query_general.log

2)如果将配置项log_output的值改成TABLE,则查询日志存储在表mysql.general_log

2、慢查询日志
1)配置文件对应的配置项:

  • slow_query_log = 1|OFF (开关)
  • long_query_time = 1 (单位:秒)
  • slow_query_log_file = /home/nieyong/local/mysql-5156/log/query_slow.log

2)如果将 log_queries_not_using_indexes 设置成1,如果运行的sql语句没有用到索引,MySQL也会将它写入慢查询日志。

3)参数log_output指定了慢查询(不仅是慢查询,所有日志输出都会受影响)输出的格式,默认为FILE,输出至slow_query_log_file变量值指定的文件;如果改为TABLE,慢查询将输出至mysql.slow_log表。

4)可用mysqldumpslow命令来查看和分析慢查询日志。例:

1
mysqldumpslow -s at -t 20 -a

3、错误日志
1)配置文件对应的配置项:

  • log-error = /home/nieyong/local/mysql-5156/log/mysqld.err

4、二进制日志
1)配置文件对应的配置项:

  • binlog_cache_size
  • max_binlog_cache_size
    可用命令 show global status like ‘binlog_cache%’; 查看,结果如下:
    1
    2
    3
    4
    5
    6
    +-----------------------+-------+
    | Variable_name | Value |
    +-----------------------+-------+
    | Binlog_cache_disk_use | 0 |
    | Binlog_cache_use | 88 |
    +-----------------------+-------+

Binlog_cache_disk_use记录了使用磁盘临时文件写二进制日志文件的次数;Binlog_cache_use记录了使用缓冲写二进制日志文件的次数。如果Binlog_cache_disk_use相比Binlog_cache_use非常小,说明binlog_cache_size设置合理。

  • sync_binlog 其值表示每写缓冲多少次就将数据同步至碰盘。
  • binlog_format 其值有: STATEMENT, ROW, MIXED
    • STATEMENT - 记录的是逻辑SQL语句。
    • ROW - 记录的是表的行列改情况,日志所需的容量相比STATEMENT要大很多。
    • MIXED - 默认采用STATEMENT记录日志,但在一些情况下会使用ROW格式。

2)查看二进制日志需用工具mysqlbinlog

5、重做日志(redo log) 记录的是关于每个页的更改的物理情况
1)配置文件对应的配置项:

  • innodb_log_file_size: 指定重做日志文件的大小(单位:byte)。大小的设置对MySQL有影响:太大在恢复时需要很长的时间;太小可能导致一个事务的日志需要多次切换重做日志文件。
  • innodb_log_files_in_group: 日志文件组中重做日志文件的数量
  • innodb_mirrored_log_groups: 日志镜像文件组的数量(默认为1,代表只有一个日志文件组,没有镜像)
  • innodb_log_group_home_dir:日志文件组所在路径

6、进程ID文件
1)配置文件对应的配置项:

  • pid-file = /home/nieyong/local/mysql-5156/tmp/mysql.pid

2)查看命令:

  • show variables like ‘pid_file’;

7、socket文件
1)配置文件对应的配置项:

  • socket = /home/nieyong/local/mysql-5156/data-test/tmp/mysql.sock

2)查看命令

  • show variables like ‘socket’;