本章是mysql技术内幕这本书的读书笔记。可以点击超链接查看所有读书笔记。

1. 文件概览

MySQL数据库和InnoDB存储引擎表的各种类型文件如下:

文件名称 说明
参数文件 告诉MySQL:哪里找数据库文件、指定初始化参数
日志文件 记录日志,包括错误日志文件、二进制日志文件、慢查询日志文件、查询日志文件
socket文件 当用UNIX域套接字方式进行连接时需要的文件
pid文件 MySQL实例的进程ID文件
mysql表结构文件 存放mysql表结构定义文件
存储引擎文件 各个存储引擎都有自己的存储引擎文件。存储引擎文件真正存储了记录和索引数据

2. 参数文件

用户查看参数文件在哪的命令:

mysql --help|grep mysql.cnf

可以看到MYSQL按顺序会在多个目录下尝试寻找。第一个优先找的是/etc/my.cnf

mysql即使找不到参数文件,也可以通过编译MYSQL时指定的默认值和源代码中指定参数的默认值来启动实例。

采用show variables XXX可以来查看参数值。

2.1 参数类型

  1. 静态参数(read only variable):实例生命周期内不可以修改。
  2. 动态参数:实例声明周期内可以动态修改

在shell中使用

# 在会话或者全局级别修改变量名
set  global/session XXX

PS:有些动态参数只能在会话中修改,例如autocommit

查询全局或者会话的参数值可以使用以下命令:

select @@sesiion.read_buffer_size\G;
select @@global.read_buffer_size\G;

3 日志文件

3.1 错误日志

MYSQL出现问题,首先会考虑查看该日志用于定位问题。该日志除了记录所有错误信息,也记录一些警告信息或正确的信息。

错误日志主要是得到一些关于数据库优化的信息。

查看错误日志所在位置使用以下命令:

show variables like 'log_error'\G;

3.2 慢查询日志

慢查询日志可以帮助DBA定位可能存在问题的SQL语句, 从而进行SQL语句层面的优化。

使用例子:
MYSQL启动时设定一个阈值,将运行时间超过该值的所有SQL局域都记录到慢查询日志文件中。DBA每天或每过一段时间来对其进行检查,确认是否有SQL语句需要进行优化。该阈值可以通过参数long_query_time参数来设置,默认值为10,单位为秒。默认不开启慢查询日志。

# 查看是否开启慢查询日志
show variables like 'log_slow_queries'\G;
# 查看SQL超时阈值,默认不设置,开启需要设置long_quey_time=on
show variables like 'long_query_time'\G
# 将没有使用索引的SQL语句记录的慢查询日志,默认不开启,开启使用on
show variables like 'log_queries_not_using_indexes'\G
# 控制没有使用索引的SQL记录日志的量,防止日志膨胀过快。表示每分钟允许记录到慢日志中的SQL语句次数。默认为0表示没限制。
show variables like 'log_throttle_queries_not_using_indexes'\G 

使用 mysqldumpslow slow-log.log 可以帮助分析慢日志

# 得到执行时间最长的10条SQL语句
mysqldumpslow -s al -n 10 xx.

也可以把慢查询日志放入表中。表在mysql.slow_log

设置将慢查询日志放入表中

# 将参数log_output设置为table。是动态参数可以直接改
set global log_outpu='table';

此外慢查询日志还提供逻辑查询(所有通过IO或者内存的查询)和物理查询(需要IO)。通过比较逻辑查询和物理查询的比例可以优化SQL(避免物理查询)。这里不再展开。具体见书本P72

此外可以修改慢查询日志表的默认存储引擎从CSV到MYISAM来提升查询效率(先关掉慢查询再改表结构)

alter table mysql._slow_log engine=MyISAM

3.3 查询日志

默认为主机名.log。无论请求是否得到正确执行,都会记录日志。用法和慢查询日志比较接近。也可以写入mysql.general_logs。

3.4 二进制日志

记录数据库执行更高的所有操作。不包括SELECT和SHOW操作。

二进制日志作用:

  1. 恢复: 通过二进制日志进行point-in-time恢复
  2. 复制:实时数据同步
  3. 审计:对日志信息做审计,判断是否有数据的注入攻击

默认不开启,配置参数log-bin=name来开启

# 查看MYSQL文件存放信息
show variables like 'datadir';

根据官方文档可知,开启BINLOG,会使性能下降1%的样子。不过这个是完全值得的。

BINLOG涉及到的一些重要参数如下:

# binlog每个文件的最大大小(字节),命名按照序号递增
max_binlog_size
# binlog 缓存。未提交的事务的二进制日志会放入缓存在提交时FLUSH
# binlog cache 是基于会话的。合理分配很重要。根据binlog_cache_use(记录使用缓冲写二进制日志的次数)和binlog_cache_disk_size(记录了使用临时文件写二进制日志的次数)两个参数来协助设定合适的值。写临时文件次数少,说明够用。
binlog_cache_size
# 每写缓冲多少次,就FLUSH到磁盘。设为1则开启,默认为0。设为1有最高可用性(影响性能)。建议也开启innodb_support_xa来避免提交失败但是二进制已经被提交的情况。
sync_binlog
# 指定哪些库需要写入二进制文件
binlog-do-db
# 指定需要忽略那些库
binlog-ignore-db
# 表示slave是否要把变更写入自己的BINLOG。如果是配置master->slave->salve这种架构的时候需要设置
log-slave-update
# ROW STATEMENT MIXED三种(动态参数,建议会话级别修改)
binlog_format

通过mysqlbinlog来查看二进制日志:

# -vv参数可以显示具体的SQL
mysqlbinlog -vv --start-position=203 bin-log.00004

删除binlog

# mysql删除log日志
purge binary logs to 'mysql-bin.000037';

purge_relay_logs --user=root --password=XXX --host=127.0.0.1

4 套接字文件

如果在UNIX下采用UNIX域套接字方式连接MYSQL,那么就需要一个套接字文件。一般在/tmp/mysql.sock

```bash# 查看套接字文件路径
show variables like 'socket'\G;

#5 pid文件
实例启动时候会将自己的进程ID写入一个文件中,即pid文件。文件由pid_file参数控制,默认在数据库目录下,文件名为主机名.pid

```bash

show variables like 'pid_file'\G;

6 表结构定义文件

MYSQL采用插件式存储引擎,故MYSQ流数据的存储是根据表进行的,每个表都会有与之对应的文件。文件名后缀是frm,在datadir下

# 查看数据库目录在哪
show variables like 'datadir';

7 InnoDB存储文件

之前说的文件是MYSQL数据库本身的文件,现在提到的则是和InnoDB相关的文件了

7.1 表空间文件

表数据存放在表空间中。MYSQL存在默认的表空间,也可以自己设定一张表对应一个表空间。

# 默认表空间位置参数。可以设置大小和自动扩展例如:innodb_data_file_path = /db/ibdata1:2000M;/dr2/db/ibdata2:2000M:autoextend
show variables like 'innodb_data_file_path';
# 设置该参数为on,可以开启一个表对应一个表空间
show variables like 'innodb_file_per_table';

PS:就算开启了一张表对应一个表空间,但是存储该表的数据、索引和插入缓冲BITMAP等信息还是存放在默认表空间。

7.2 重做日志文件

在datadir下有两个名为ib_logfile0和ib_logfile1的文件,即重做日志文件。例如断电、介质失败问题时,就可以用重做日志来做恢复,来保证数据完整性。

可以设置多个镜像重做日志组放在不同磁盘保证高可用。一个日志组中的重做日志文件大小一致,并以循环写入的方式运行(存在覆盖情况)。重做日志设定的大小取决于你想恢复多久前的数据了。

相关参数如下:

#每个日志文件大小,最大512GB
innodb_log_file_size
# 每个重做日志文件组的日志数量,默认为2
innodb_log_files_in_group
# 设定有几个日志文件组,默认为1,即没有镜像日志文件组。如果磁盘本身做RAID了,也没必要再多设置几个日志文件组
innodb_mirrored_log_groups
# 日志文件组所在的路径
innodb_log_group_home_dir

使用show variables like 'innodb%log%'\G;可以查看重做日志文件相关的所有参数

重做日志条目结构:

写重做日志的过程


PS:

  1. 不需要double write:写重做日志不需要双写,因为写入是按照一个扇区来写的,不会写入失败。扇区是最小的写入单位。
  2. 触发时间:重做日志从缓存刷新到磁盘是有触发时机的。这个在讲master thread的时候提到过。还可以通过参数innodb_flush_log_at_trx_commit控制。设定为1能保证ACID中的D。具体使用方法见书本P90

7.3 重做日志文件使用经验

  1. 不能设置太大:恢复需要太久时间
  2. 不能设置太小:一个事务的日志需要多次切换重做日志文件,并且还会频繁发生async checkpoint造成性能抖动

例如性能抖动时会再错误日志中看到如下警告:

重做日志有个capacity,代表最后的检查点不能超过这个阈值。如果超过则必须将缓冲池中脏页列表中的部分脏数据页写回磁盘,造成用户线程阻塞

7.4 重做日志 VS 二进制日志

名称 职责范围 记录内容 写入时间
Innodb重做日志 InnoDB的存储引擎只记录有关存储引擎本身的事务日志 记录的是物理日志,即记录的是关于每个页的更改的物理情况 事务进行过程中
二进制日志 所有存储引擎的日志 记录逻辑日志,即关于一个事务的具体操作内容 仅在事务提交前进行提交