mysql 默认引擎innodb 初探(三)

写在前

本篇博客承接上一篇 mysql 默认引擎innodb 初探(二)继续对mysql数据库 innodb存储引擎进行探索

innodb 文件

mysql数据库和innodb存储引擎表的各种类型文件:

  • 参数文件
  • 日志文件(错误日志文件,二进制日志文件,慢查询日志文件,查询日志文件)
  • socket文件(Unix套接字连接,避免走tcp协议,web服务器和mysql服务器在同一机器上时可用于提高通信提高效率)
  • pid文件(保存mysql实例进程ID)
  • mysql表结构文件
  • innodb存储引擎文件

参数文件

查看mysql配置文件

mysql --help | grep my.cnf

查当前mysql实例配置项

show variables like "innodb%"\G

配置参数:

  • 动态参数(dynamic)【mysql实例运行中可以更改】
  • 静态参数(static)【实例生命周期内不得进行更改 eg : datadir 】

动态设置参数格式

set [@@global. | @@session.]system_var_name = expr

eg :
   set read_buffer_size = 1024000;
   set @@session.read_buffer_size = 2048000;
   set @@global.read_buffer_size  = 4096000;

静态参数是不能动态修改的,强制修改会报错;
有些动态参数只能在会话中(session)修改,eg : autocommit ;
有些动态参数修改后整个实例都会生效, eg : binlog_cache_size ;
有些动态参数即可用在会话中修改,也可以在整个生命期内修改, eg : read_buffer_size ;

日志文件

日志文件记录了mysql数据库的各种活动,通过分析日志可用快速精确查找问题并优化;

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

错误日志记录了mysql的启动,运行,关闭过程,通过该文件可以快速定位问题;

查看mysql实例错误文件路径

show variables like "log_error"\G

这里写图片描述

这里写图片描述

慢查询日志

慢查询日志允许你可以设置一个阀值,运行时间超过该阀值的所有sql语句都会记录在慢查询日志文件中;
可以很好的帮助优化数据库,;

show  variables like "slow_query_log"\G   # 查看是否开启慢查询日志
set   slow_query_log = ON|OFF;  # 开启|关闭慢查询日志

show variables like "log_output"\G   # 查看慢查询日志记录到文件还是表中 
set log_output=TABLE|FILE;  # 设置慢查询日志输出到table or files中 

show variables like "slow_query_log_file"\G  # 查看慢查询日志文件路径

show  variables like "long_query_time"\G  # 查看慢查询阀值
set long_query_time=10;  # 设置慢查询阀值为10s

show variables like "log_queries_not_using_indexes"\G # 查看是否开启,没有使用索引也记录到慢查询日志中
set log_queries_not_using_indexes=ON|OFF;  # 开启or关闭

show variables like "log_throttle_queries_not_using_indexes"\G # 每分钟 允许【因为没有使用索引】而记录到慢查询日志中的sql语句数
# log_throttle_queries_not_using_indexes = 0; 表示不限制数量,可能会频繁记录,要小心

  • 使用mysqldumpslow工具分析慢查询日志文件 (当设置log_output=FILE时)

这里写图片描述

  • 查看慢查询日志表(当设置log_output=TABLE时)

这里写图片描述

slow_log表默认是 CSV存储引擎,对查询效率不是很高,可以设置为MySIAM;不过个人建议设置成Archive存储引擎;

查询日志

查询日志记录所有mysql请求(insert,update,delete,select),无论是否正确;
默认记录到到文件中,打开log_output=TABLE后记录到mysql.general_log表中;


show variables like "general_log"\G   # 查看是否开启查询日志
set @@global.general_log = ON|OFF;   # 开启or关闭查询日志

show variables like "general_log_file"\G  # 查看查询日志文件路径

show variables like "log_output"\G  # 查看查询日志输出到文件还是表中

一般建议关闭,默认也是关闭查询日志;

二进制日志

二进制日志记录mysql数据库执行更改的所有操作(不包括select,show 等查询操作)

  • 恢复(recovery) eg : 进行point-in-time恢复
  • 复制(replication) eg : master-slave 复制
  • 审计(audit) eg : 分析二进制日志文件,查看是否有注入攻击等

配置文件中设置 log-bin [=binlog_file_name] 开启二进制日志;
如果不指定binlog_file_name默认为主机名;
二进制日志文件放在 datadir数据目录下

show variables like "datadir"\G 

这里写图片描述

这里写图片描述

mysql-bin.index文件为二进制的索引文件,存放二进制日志序号

这里写图片描述

二进制日志相关配置参数:

  • max_binlog_size

    指定单个二进制文件最大值,超过该大小,将产生新的二进制文件,后缀+1,并记录到.index文件中

  • binlog_cache_size

    当执行事务时,所有未提交的二进制日志会记录到一个缓存中,
    等事务提交时,直接将缓存中的二进制日志写入到二进制日志文件中,
    binlog_cache_size 设定缓存大小,默认为32k;

    binlog_cache_size是基于会话(session)的,
    当一个线程开始一个事务时,mysql就会自动分配一个大小为binlog_cache_size的缓存,
    因此binlog_cache_size不能太大【好像nginx的client_header_buffer_size也是这样的】

  • sync_binlog

    默认二进制日志不是每次写都会同步到磁盘,当数据库发生宕机时,可能会有部分数据没有刷盘;
    sync_binlog设置没写缓冲多少次就同步到磁盘,默认sync_binlog=0

    使用innodb存储引擎进行复制时,为了获得最大高可用性,建议开启

  • binlog-do-db

  • binlog-ignore-db
  • log-slave-update

    binlog-do-db | binlog-ignore-db 指定那些库或者忽略那些库写二进制日志

    log-slave-update 指定哪些要进行主从同步

  • binlog_format

    • STATEMENT 二进制日志记录逻辑sql语句,
    • ROW 记录表的行更改情况,会占用更多存储空间,主从复制时,会增加网络开销;但是有更好的 可靠性
    • MIXED 默认采用STATEMENT,特殊采用ROW

    可以使用mysqlbinlog工具分析二进制文件

    这里写图片描述

套接字文件

unix本地可以 使用套接字连接mysql
可以参考ngigx调用php-cgi的方式

这里写图片描述

pid文件

保存mysql进程ID

这里写图片描述

mysql表结构定义文件

无论使用何种存储引擎,mysql都会为每张表建立一个后缀为frm的文件,文件中定义了表结构。

innodb存储引擎文件

以上介绍的文件都是mysql数据库本身文件,和存储引擎无关;
InnoDB存储引擎拥有自己的文件:

  • 表空间文件
  • 重做日志文件
表空间文件
show variables like "innodb_file_per_table"\G
set innodb_file_per_table = ON|OFF;  # 开启or关闭独立表空间

show variables like "innodb_data_file_path"\G   # 查看共享表空间文件

开启innodb_file_per_table独立表空间后,独立表空间只存储该表的数据、索引、插入缓冲bitmap等信息;其他信息依然存放在共享表空间中,如插入缓冲数据等;

这里写图片描述

重做日志文件

Inoodb存储引擎的数据目录下有两个名为ib_logfile0 和 ib_logfile1的文件;

每个存储引擎至少有1个重做日志组(group)
每个组至少有2个重做日志文件,默认为ib_logfile0 , ib_logfile1,两文件大小一致,以循环写入的方式运行;

这里写图片描述

这里写图片描述

重做日志文件相关配置参数 :

  • innodb_log_file_size 指定日志文件大小
  • innodb_log_files_in_group 指定每个文件组下重做日志文件数量
  • innodb_mirrored_log_groups 指定日志镜像文件组的数量,默认为 1,没有镜像
  • innodb_log_group_home_dir 日志文件组所在路径

tips : 重做日志文件过大,在恢复数据时可能会耗费很长时间;过小,会频繁切换重做日志,导致频繁async checkpoint;

二进制日志文件与重做日志文件对比:

  • 二进制日志文件记录mysql数据库相关日志,包括所有存储引擎 ;重做日志文件记录Innodb存储 引擎事务日志
  • 二进制日志文件更具binlog_format不同,分别记录逻辑sql语句(STATEMENT),具体更新内容(ROW),根据场景混合使用(MIXED);重做日志文件记录每个页的物理更改;
  • 二进制日志不断写入二进制日志缓冲中,事务提交时刷盘一次;重做日志,在事务过程中不断写入到重做日志文件中

checkpoint技术一个触发条件就是事务提交,使用innodb_flush_log_at_trx_commit参数可以控制事务提交时要不要强制将重做日志写盘;

innodb_flush_log_at_trx_commit:

  • 0 : 事务提交时,不强制将重做日志写入重做日志文件中
  • 1 :事务提交时,将重做日志缓冲同步到磁盘,并fsync(同步文件系统缓存)
  • 2:事务提交时,将重做日志异步刷回磁盘(写到文件系统缓存中)

后记

后续将介绍

  • 索引与算法 B+
  • 事务
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值