MySQL物理文件
参数文件
当MySQL实例启动时,数据库会读取参数文件,查找参数文件的顺序为/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf
,相同的参数会以最后读到的参数文件为准。当参数文件不存在时,NySQL所有的参数值就取决于编译MySQL时指定的值和源代码指定参数的默认值。参数由key-value类型的键值对组成,我们可以通过show variables或information_schema.GLOBAL_VARIABLES视图来查看。
参数类型分为动态参数和静态参数。动态参数即意味着我们可以在MySQL实例运行过程中修改,静态参数即无法在实例运行中修改。我们可以通过SET命令对动态参数进行修改
SET |
global和session用于声明参数修改是基于全局的还是当前会话的,有的动态参数只能基于会话修改,例如autocommit。当然也可以使用SET @@global | @@session的形式来修改,效果是一样的。需要注意的是参数动态修改后,在重启时修改会失效恢复原始值,因此在动态修改参数后,我们应该持久化到参数文件中。
日志文件
日志文件记录了MySQL实例运行过程中产生的各种活动日志,常见的日志文件有:错误日志、二进制日志、慢查询日志、general log
错误日志
错误日志为你教案对MySQL启动、运行、关闭过程进行了记录,其中涵盖了错误信息、告警信息等,方便我们定位MySQL的日常问题。可以通过show variables like ‘log_error’来定位该文件
mysql> show variables like 'log_error'; |
慢查询日志
通过设置一个阈值,执行时间超过该阈值的SQL都将记录到慢查询日志,通过分析慢查询日志,可以帮忙定位数据库性能问题,找出问题SQL,并对其进行优化。将参数slow_query_log设置为ON表示开启慢查询日志,long_query_time参数用于设置慢查询阈值。我们可以通过show variables like ‘slow_query_log_file’定位慢查询日志文件,慢查询日志也可以存放在数据表中,由参数log_output控制。数据表为CSV引擎,查询性能并不高。
此外,我们可以设置参数log_queries_not_using_indexes=on来将未使用索引的SQL记录到慢查询中,即使它并没有超过慢查询阈值,未使用索引的语句往往带有一定的风险,随着数据量的不断增长,数据表扫描的代价会越来越多,成为性能瓶颈。未避免这类SQL重复被记录到慢查询日志中,导致文件大小快速增长,我们应该同时设置log_throttle_queries_not_using_indexes参数,限制每分钟记录到慢查询日志中未使用索引的SQL次数。
慢查询日志的内容随着时间不断增长,手动分析该日志就比较麻烦了,MySQL提供了mysqldumpslow工具用来分析慢查询日志,当然也可以使用第三方开源的pt-query工具
[root@t-luhx02-v-szzb ~]# mysqldumpslow --help |
查询时间最长的10条SQL
mysqldumpslow -s t -t 10 -a mysqlslow.log |
如果慢查询日志文件比较大时,分析消耗较多资源,我们可以通过sed命令按时间范围把需要的从慢查询日志文件中拷贝到新的文件中,再对新文件进行分析
sed -n '/# Time: 200507 21:00:02/,/# Time: 200508 1:30:05/'p mysqlslow.log >slow_20200508.log |
二进制日志
二进制日志(binary log)记录了所有MySQL的数据修改操作,SELECT或者SHOW之类的操作并不会记录,我们可以通过show binlog events命令查看二进制日志中的内容
mysql> show binlog events in 'mysql-bin.000007'\G |
binlog是追加写入的,写到一定大小会写入下一个文件中,并不覆盖之前的文件。由于binlog记录了所有逻辑操作,且文件不会覆盖,因此我们可以通过binlog将数据库恢复到binlog范围内的任意时间点,同时也是MySQL实现复制的基础。
二进制日志由参数log-bin控制,我们可以指定其文件路径及文件名格式,生成的index文件为二进制的索引文件,用来存放过往的二进制日志序号。二进制日志记录的信息和行为同时收到下列参数的影响:
- max_binlog_size:指定了单个二进制日志文件的最大值,如果超过该值则生成新的二进制日志文件,序号+1并记录到index文件中
- binlog_cache_size:系统给binlog分配了一块内存,由参数binlog_cache_size控制单个线程binlog cache的大小,如果超过限制则放到临时文件中,事务提交后则写入binlog并清空binlog cache。通过show global status查看binlog_cache_use和binlog_cache_disk_use的状态,可以判断binlog_cache_size是否设置合理。
- sync_binlog:binlog写入时是先写到文件系统的page cache,并没有持久化到磁盘,等待fsync将数据持久化到磁盘上。这个机制是由参数sync_binlog控制的,为0时表示每次提交事务只写到page cache,不做fsync;为1时每次提交事务都会执行fsync;大于1时则表示每次提交事务写入到page cache,等累计N个事务再fsync,设置为大于1的值可能会在主机异常重启后丢失N个事务的binlog日志
- log-slave-update:如果当前实例在复制中是slave角色,想要将master获取并执行的二进制日志写入自身的二进制日志文件中,需要开启log-slave-update,特别是在级联复制的场景下
- binlog_format:binlog_format影响了记录二进制日志的格式,其有三个可选值:ROW、STATEMENT、MIXED。STATEMENT格式记录的是逻辑SQL语句;ROW格式记录表的行更改情况,导致二进制日志大小变大,RC隔离级别下必须设置为ROW;MIXED格式下,默认采用STATEMENT格式记录二进制日志,在部分情况下会使用ROW格式。
如果想要查询二进制日志文件的内容,我们可以利用mysqlbinlog工具来解析
[root@t-luhx02-v-szzb ~]# mysqlbinlog -vv --start-position=338 /service/mysql/db1/binlog/mysql-bin.000007 |
general log
general log会将MySQL执行的所有SQL记录下来,开启后可能会产生大量日志,需谨慎使用,默认不开启。如果要开启可以将参数general_log设置ON,通过show variables general_log_file可以查看general log的文件路径。与慢查询日志相同,general log也受参数log_output影响
socket文件
在UNIX系统下本地连接MySQL可以采用UNIX域套接字方式,这种方式需要一个socket文件,该文件由参数socket控制,默认在temp目录下。当我们利用本地mysql客户端连接数据库时,可以通过-S选项指定socket文件的方式连接。
pid文件
MySQL实例启动时,会将自己的进程ID写入到PID文件中,文件由参数pid_file控制,默认位于数据库目录下
表结构定义文件
MySQL的数据存储是根据表进行的,每个表都存在对应的文件,无论任何存储引擎,都有一个frm后缀名的文件,这个文件记录了对应表的表结构定义,frm还会用来存放视图的定义。
表空间文件
系统表空间
bdata1.ibd,主要用于保存double writer buffer,changer buffer,数据字典,数据表。我们可以通过innodb_data_file_path参数指定一个或多个数据文件
innodb_data_file_path = /data1/ibdata1:12M;/data2/ibdata2:12M:autoextend |
系统表空间最大的问题在于当存放在系统表空间的数据表删除后,占用的空间并不会释放,容易造成空间浪费
单表表空间
单表表空间即每张表对应一个表空间,其数据文件名为表名.ibd,要想使用单表表空间需要开启参数innodb_filer_per_table,也可以在创建表时指定单表表空间。
create table t1 (id int primary key) tablespace innodb_file_per_table; |
相对于系统表空间,单表表空间的优点如下:
- 可以为不同的表指定不同的磁盘目录
- 可以结合可传输表空间快速移植单表
- 可以使用表压缩等特性
- 更好的对表进行重建收缩空间
- 表删除后空间释放
使用单表表空间后,打开表所需的文件描述符也随之增大,增加了内存的消耗
通用表空间
通用表空间从MySQL5.7开始引入到InnoDB中,通用表空间和系统表空间一样,也是共享表空间,每个表空间可以包含多张表。通用表空间可以像单表表空间类似,将数据文件定义在其它目录下,并占用更少的文件描述符,但无法像单表表空间那样通过可传输表空间进行单表迁移。
mysql> create tablespace tb1 add datafile '/service/data/tb1.ibd' engine innodb; |
三种表空间可以根据需求互相来回切换,但其执行性能代价较大,需注意时机
# 单表表空间 |
要想删除通用表空间需要先确保表空间为空,如果其中存在对象则不允许删除,需要先将对象迁移或修改为其它表空间,我们可以通过下列语句查看表空间中的对象
select regexp_replace(a.name,'/.+','') dbname,regexp_replace(a.name,'.+/','') tablename from innodb_tables a, innodb_tablespaces b where a.space = b.space and b.name= 'tb1'; |