MySQL数据库——配置文件、日志文件、套接字文件、pid文件、表结构文件、存储引擎文件

最新推荐文章于 2024-08-06 04:24:39 发布

讲不出再见

最新推荐文章于 2024-08-06 04:24:39 发布

阅读量826

点赞数

分类专栏： MySQL数据库文章标签： MySQL配置文件存储引擎文件错误日志慢查询日志二进制日志

本文链接：https://blog.csdn.net/dgh112233/article/details/106590641

版权

MySQL数据库专栏收录该内容

17 篇文章 3 订阅

订阅专栏

1、配置文件

配置文件里是MySQL数据库的全局参数，指定了MySQL启动时的各种配置，比如缓存池的大小，缓存池实例数量，日志文件大小（位置），等等。配置文件就是之前博客里讲的（linux下）my.cnf 或者（windows下）my.ini。

配置文件里的参数按照在运行期间是否可以被修改分为 静态参数 和 动态参数。静态参数是在数据库运行期间无法修改的，如果要修改静态参数，只能在配置文件里修改，然后重启数据库。动态参数是在数据库运行期间可以修改的。

按照参数的作用范围可以分为 会话参数 和 全局参数。比如用户A修改了某个参数，并且让该参数只在当前用户会话中起作用，假如有另一个用户B登录了数据库，那么B看到的该参数值仍然是旧值。有些参数只允许在会话中起作用，有些参数却只能是全局参数，而还有些参数既可以作为会话参数，又可以作为全局参数。

怎么查看这些参数和设置这些参数呢？

1、查看参数。

用 sql 指令，举个例子 SHOW GLOBAL innodb_buffer_pool_size; 或者 SELECT @@global.innodb_buffer_pool_size; 查看 innodb_buffer_pool_size 的全局值。 SHOW SESSION innodb_buffer_pool_size; 或者 SELECT @@session.innodb_buffer_pool_size; 查看 innodb_buffer_pool_size 的会话值。

另外，还可以直接看全局参数视图和用户会话参数视图，分别在 performance_schema 库里的 global_variables表和 user_variables_by_thread 表。

2、设置参数

用 sql 指令，举个例子 SET GLOBAL innodb_buffer_pool_size = 1024; 或者 SET @@global.innodb_buffer_pool_size = 1024;

2、日志文件

2.1、错误日志文件

虽然叫错误日志文件，但是并非所有的错误都会记录，另外，还会记录一些非错误信息，比如数据库的启动过程，关闭过程，数据库在运行中的关键错误，一些警告信息等。总之，记录的是需要我们注意且具有警示意义的信息。通过错误日志文件可以快速定位错误，查明原因。

查看错误日志文件，看看几个全局参数：

log_error：表示数据库的错误日志文件位置和名字，默认文件名为主机名，后缀为 .err。

log_error_verbosity：表示错误日志文件记录哪种信息，1 表示只记录错误， 2 表示记录错误和警告， 3 表示记录错误，警告和通知。默认为 3。

有些老版本里面，还有log_warnings 参数，这个和 log_error_verbosity 作用差不多，应该没有 log_error_verbosity 好，所以新版本已经删除了 log_warnings 参数。

2.2、慢查询日志文件

慢查询日志文件是对于优化数据库特别重要的日志文件，数据库有个全局参数 long_query_time ，是一个时间阈值，在数据库运行期间，只要某个sql语句执行的时间超过了（大于，等于都不行） long_query_time，就被认定为慢查询语句并被记录到慢查询日志中，因此，我们可以查看慢查询日志文件，分析那些执行时间久的sql语句，对它们进行优化，提升性能。

long_query_time 的单位是秒，但是小数点后面有6位，因此，可以精确到微秒。默认为10秒。这个时间是可以设置的。

slow_launch_time：如果建立线程的时间超过了2秒，也会记录到慢查询日志文件。

slow_query_log：是否开启慢查询日志，默认不开启。

slow_query_log_file：慢查询日志文件的名字和路径。默认名字为“主机名-slow.log”。

log_queries_not_using_indexes：是否开启，如果SQL语句没有用到索引，也会记录到慢查询日志。默认不开启。

log_throttle_queries_not_using_indexes：在 log_queries_not_using_indexes 开启的情况下，限制每分钟没有使用索引的SQL语句记录到慢查询日志的次数，0表示没有限制。因为在某些情况下，可能一分钟内没有用到索引的SQL语句会很多，导致慢查询日志文件急速增大，为了限制一下，才有了这个全局参数。

一般情况下，慢查询日志都是存储于日志文件中，但是时间一长，慢查询日志是非常多的，我们查看日志文件也就不方便了，MySQL数据库给了一个命令 mysqldumpslow ，专门用于处理慢查询日志和全文搜索慢查询日志。具体用法百度吧。

从MySQL 5.1 版本开始，可以运行将慢查询日志存储于一张表中，这张表为 mysql 库下的 slow_log 表。数据库默认是存于慢查询日志文件中，如何设置才能让慢查询日志存于表中呢？有一个全局参数 log_output，默认为 FILE，表示文件形式，如果设置为TABLE，就表示存于表中。放在表中的好处是，表的每个列代表日志的一个关键信息，我们可以使用 sql 语句更加方便，灵活地去读取和处理慢查询日志。

slow_log 慢查询日志表的存储引擎默认是 CSV， CSV引擎的特点就是以csv文件作为MySQL表，没有索引，不适合大数据表。因此，如果要用表格去存慢查询日志，就把slow_log 表的引擎换一个，能够支持索引，方便大数据量的处理就行。如果不熟悉 sql 指令，建议安装一个 Navicat，图形化操作数据库，并且也支持使用 sql 。如果用 SQL 去换一张表的存储引擎，举个例子：ALTER TABLE mysql.slow_log ENGINE = InnoDB。

另外，还有一点需要注意，在比较新的版本中，MySQL支持另一种判断慢查询语句的标准，即 long_query_io 全局参数，表示逻辑读取次数，不急，慢慢道来。在之前的版本的中，用SQL语句的执行时间来作为衡量标准，而且已经精确到微秒了，为什么还要搞一个 long_query_io 出来呢？原因在于引擎的运行不是稳定的，有时候业务量大，给人的感觉就忙一点（卡一点），有时候没什么事干，就空闲一点，所有在忙的时候，我们执行一条SQL的时间很可能要久一点，也许就因为久这一点，这条SQL就被认定为慢查询SQL了。因此，将SQL语句的执行时间作为评判标准，一般来讲没什么问题，只不过偶尔也会有误判。所以，在新版本中，才会出现了long_query_io 评判标准，推出两个概念，逻辑读取和物理读取，物理读取次数是指需要磁盘进行的IO次数，逻辑读取次数是指物理读取次数 + 缓存池读取次数， long_query_io 的使用就表示，不管你执行时间是多少，我只看你是不是比较费资源，费性能（因为你读取次数多呀），也许有条SQL在数据库空闲时执行的，虽然执行时间短一些，但是它却可能会消耗不少读取次数。

当然，新版本是兼容老版本的，你可以在新版本中指定慢查询评判标准是 long_query_time，也可以设置评判标准为 long_query_io，怎么设置呢？新版本中有一个全局参数 slow_query_type，决定了用哪种方式： 0 表示不开启慢查询日志，1 表示将时间作为标准， 2 表示将逻辑读取次数作为标准，3 表示将两个都作为评判标准，只要 SQL 超过了任意一个标准，都会被认为是慢查询。

2.3、查询日志

查询日志没有什么特殊的，但凡是用户对数据库的请求，查询日志统统记录下来（包括一些SQL执行出错的信息），在开发过程中，我也经常通过查看这个日志文件来判断有些SQL语句是否执行了，成功与否等等。下面两个全局参数：

general_log ：是否开启查询日志，默认不开启。

general_log_file：查询日志文件的名字和位置，默认为主机名.log。

查询日志文件的内容是非常多的，如何查看呢？有一个 tail 指令，常用的就是 tail -n 500 xxx.log，用于查看日志文件末尾最新的500行记录。

另外，查询日志也可以存储到表中，mysql 库下的 general_log 表，怎么设置呢？和慢查询日志的一样，同一个参数 log_out 设置为 TABLE 即可。

2.4、二进制日志

二进制日志文件用于存储用户对数据库的所有DML操作，即凡是用户对数据库数据进行了修改的，都要存储到二进制日志文件中，比如 update， delete等等，像 select，get，show 这些操作就不会记录进二进制日志文件。二进制日志文件里不是单单存储sql，还包含sql执行的起始时刻，执行的时间长度，哪个线程执行的，等等。

log_bin：表示是否开启二进制日志，默认不开启。

log_bin_basename：默认是主机名，表示二进制日志文件的基础名。因为二进制日志文件不止一个，所以会在基础名后面加自增数字，来达到区分的目的，比如“binlog.000001，binlog.000002等等”

log_bin_index：一个索引文件，后缀为“.index”，通过这个文件可以找到和区分所有的二进制日志文件。

因此，开启二进制日志后，会创建一个 basename.000001 日志文件和 basename.index 文件，如果日志文件满了，就继续创建一个新的日志文件 basename.000002 ，如此下去。

开启二进制日志会影响一点点数据库性能，官方测试，会使得数据库性能下降 1%。但是二进制日志会带来如下几个作用：

1、灾备恢复。之前的博客已经介绍了当缓冲池中的事务数据丢失时，如何保证事务的redo，用redo日志文件就可以保证。而此处的灾备恢复是指，当数据库磁盘里的数据丢失了一段时间的数据，你咋整，比如数据库为了做灾备工作，每隔一段时间都会把数据库里的所有数据进行一次备份，刚刚备份完，过了1个小时，磁盘毁坏了，里面的数据全没了，这就意味着这1个小时内的所有改动过的数据全没了，如果有二进制日志文件的话，我可以在备份的基础，按照顺序执行一遍二进制日志里的操作，就可以恢复了。

2、复制。这主要针对主从数据库集群的，主数据库的数据要尽量及时地给到从数据库，可以直接传输新的数据，也可以陪和着二进制日志文件进行数据同步。

3、审计。因为任何对数据库改动的操作都会被记录到二进制日志中，如果有攻击操作的话，就可以在日志中看到。

故，二进制文件最好和数据文件存在不同的磁盘上，避免因为磁盘的故障导致数据不能恢复。

3、套接字文件

在 Unix 或者 linux 系统里，一切皆是文件，套接字也是如此，索引在 Unix 系统下，本地连接 MySQL 可以采用 UNIX 域套接字方式，需要一个套接字文件，一般在 /tmp/mysql.sock。

4、PID文件

pid 代表进程号，MySQL启动时，即一个进程启动了，会将进程的ID写入一个文件，就是PID文件，MySQL的PID文件默认在如下位置。 pid_file 用于设置PID文件名和位置，默认名字为主机名.pid。

5、表结构定义文件

数据库表分为两部分内容，一部分是数据本身，另一部分是表的结构信息，MySQL有一种文件，后缀名为 .frm，一个frm文件用于存储一张表的结构或者一个视图的结构。

6、存储引擎文件

6.1、表空间文件

一个数据库里有很多表，一般来讲，我们的业务数据表都是基于同一种引擎的，但是由于性能优化需要，可能有些表需要使用不同的存储引擎，不同存储引擎的表放在不同的地方，基于 InnoDB 引擎的那些表主要存放于两个地方：1. 共享表空间文件， 2. 独立表空间文件。

独立表空间文件。innodb_file_per_table 参数用于是否启动这种存储方式，默认开启，将每一张表都单独存放一个独立的表空间文件里，文件名为表名.idb。存的都是表的数据、索引、插入缓存 BITMAP 信息。表其余的信息还是存放在共享表空间文件里。

共享表空间文件。这个文件是一定有的，如果 innodb_file_per_table 没有开启的话，那么所有基于InnoDB 引擎的表都将存储在共享表空间文件中，当然共享空间文件可以是多个，文件名以 ibdata 为基础名，后面数字自增即可，如下图，ibdata1 为文件名，12M为初始大小，autoextend 为自动增长文件大小。如果 innodb_file_per_table 开启了的话，那么共享表空间文件里存储的仅仅是那些基于 InnoDB 引擎的表的小部分信息，表的数据、索引、插入缓存 BITMAP 信息是存放在独立表空间文件里的。

6.2、redo日志文件

每一个存储引擎至少有一个redo日志组，一个redo日志组至少包含2个redo日志文件，默认是 ib_logfile0 和 ib_logfile1。可以用叫作镜像日志组的技术将不同的redo日志组存放于不同的磁盘上，提供可用性。

之前的博客已经讲过，redo日志的写入是循环覆盖地写，比如 ib_logfile0 写满了，就写ib_logfile1，等 ib_logfile0 写满了，在从ib_logfile0 开始覆盖写。

需要注意，redo日志不要太大，虽然可以支持最大为512G，但是总归不要设置这么大，因为一个文件太大的话，搜索和查询起来就比较慢。

6.3、表文件的结构

当创建一个数据库后，会看到有一个数据库名的文件夹生成，再创建一个表格后，会看到该文件夹下面生产了2个文件，一个文件是 frm文件，名字是表格名，另一个是 .idb 文件，名字也是表格名，

一个表文件分为若种段，我认为它是按功能分的，每一种段包含一种用途的数据，比如数据段，索引段，undo日志段，由于InnoDB引擎下每张表都有主键，且索引用的是B+树，所以数据段存的是B+树的叶子节点，索引段存的是B+树的非叶子节点。段是一个逻辑层面的概念，没有大小限制。下图是表文件的示意图，借用这篇文章的图 https://www.jianshu.com/p/11e0361e9989

segment 段， extent 区，page 页， row 行。

一个段包含若干区，区是代表磁盘上一块连续的存储空间，但是区与区之间不要求是连续的。一个区的大小固定是 1M，我想区的大小被设计为固定的且为1M，是因为：我觉得磁盘由于频繁的数据写入和删除，也会出现空间碎片化的情况，当然计算机会自动地整理这些碎片，但是整理也是需要花时间的，区的空间是需要找一块连续的磁盘空间的，如果允许用户定义区大小，万一定义大了，不是每次都能找到那么大的连续空间，如果找不到，岂不是要整理碎片空间去腾出连续空间，浪费时间不是；如果区定义小了，新问题出现了，整个数据库的数据太碎了，每个区也存不了几个数据，但是为了要找到这些区的位置，还需要维护大量的区信息，而且，一次性稍微读取多一点的数据时，就可能会读取多个区，区之间并不是连续的，所以磁盘IO时间增大。因此，我想既然官方给出了 1M 的大小，想必是比较合理的。

一个区包含多个页，每个页默认大小为 16 K，innodb_page_size 这个全局参数可以设置的。因此，默认情况下，1 个区包含 64 个页。页是连续的空间，至于页大小的设置与性能的关系，我觉得页的大小要合适，16K 是官方给出的默认值，并且我看过一篇性能比较文章，对比了 8K 和 16K 页的性能，结论是相差不多，8K 页对CPU造成的压力要比 16K 页的高0.5 ~ 1 倍，但是对于 select 语句，8K 页具有更大的吞吐量。相对来说，16K 是一个比较好的选择。页分为很多类型：数据页，undo页，系统页，事务数据页，插入缓存位图页，插入缓存空闲列表页，未压缩的二进制大对象页，压缩的二进制大对象页。

每个表空间文件都会有一个头页，这个页用于存储这个表空间里所有的段、区、页的信息，以方便管理。

一页包含很多行，由于一页的大小有限，因此一页存储的行数也有限。每一行的结构又是什么样呢？如下图，比较老的MySQL版本默认按照compact格式，变长字段长度列表：记录每个列字段的长度，如果列字段不超过255个字节，就用1个字节表示长度，如果超过了，就用2个字节表示，因此最大不能超过65535字节的字段存在，另外，长度列表的顺序和列的顺序相反。null标志位为1个字节，表示是否有哪一列的数据为null。记录头信息为5字节，每一个bit都是有意义的，具体信息如下面的表格（黑白图）。