mysql 默认引擎innodb 初探（三）

最新推荐文章于 2024-10-11 23:57:22 发布

flybird1971

最新推荐文章于 2024-10-11 23:57:22 发布

阅读量8.4k

点赞数

分类专栏：数据库文章标签： mysql 数据库 innodb

本文链接：https://blog.csdn.net/lx1607/article/details/51544625

版权

数据库专栏收录该内容

4 篇文章 0 订阅

订阅专栏

写在前

本篇博客承接上一篇 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参数可以控制事务提交时要不要强制将重做日志写盘;