认识 MySQL物理文件
发布时间：2023-10-08 点击：123
mysql教程栏目介绍mysql物理文件。
1.数据库的数据存储文件
mysql 数据库会在data目录下面建立一个以数据库为名的文件夹，用来存储数据库中的表文件数据。不同 的数据库引擎，每个表的扩展名也不一样 ，例如： innodb 用“ .myd ”作为扩展名， innodb 用 “.ibd” ， archive 用 “.arc” ，csv 用 “.csv“。
1. ".frm"文件
8.0之前无论是那种存储引擎，创建表之后就一定会生成一个以表明命名的'.frm'文件。frm文件主要存放与表相关的数据信息，主要包括表结构的定义信息。当数据库崩溃时，用户可以通过frm文件来恢复数据表结构。
2. ".myd"文件
“.myd”文件是innodb存储引擎专用，存放innodb表的数据。每一个innodb表都会有一个“.myd”文件与 之对应，同样存放于所属数据库的文件夹 下， 和“.frm”文件在一起。
3. ".myi"文件
“.myi”文件也是专属于innodb存储引擎的，主要存放innodb表的索引相关信息。对于innodb存储来说， 可以被cache 的内容主要就是来源 于“.myi”文件中。 每一个innodb表对应一个“.myi”文件，存放于位置 和“.frm”以及“.myd”一样。
4. ".ibd"文件与".ibdata"文件
这两种文件都是存放 innodb 数据的文件，之所以有两种文件来存放innodb的数据（包括索引），是因为 innodb 的数据存储方式能够通过配置来决定是使用共享表空间存放存储数据，还是独享表空间存放存储数据。独享表空间存储方式使用“.ibd”文件来存放数据，且每个表一个“.ibd”文件 ，文件存放在和innodb数据相同的位置。如果选用共享存储表空间来存放数据，则会使用 ibdata 文件来存放，所有表共同使用一个 （或者多个，可自行配置）ibdata文件。
ibdata文件可以通过 innodb_data_home_dir（数据存放目录）和 innodb_data_file_path (配置每个文件的名称) 两个参数配置组成 innodb_data_file_path 中可以一次配置多个ibdata文件 #innodb_data_file_path = ibdata1:2000m;ibdata2:10m:autoextend 配置方式共享表空间以及独占表空间都是针对数据的存储方式而言的。
共享表空间: 某一个数据库的所有的表数据，索引文件全部放在一个文件中。
独占表空间: 每一个表都将会生成以独立的文件方式来进行存储，每一个表都有一个.frm表描述文件，还有 一个.ibd文件。其中这个文件包括了 单独一个表的数据 内容以及索引内容。
4.1 两者对比
共享表空间：
优点： 可以放表空间分成多个文件存放到各个磁盘上。数据和文件放在一起方便管理。
缺点： 所有的数据和索引存放到一个文件中，多个表及索引在表空间中混合存储，这样对于一个表做了大量删除操作后表空间中将会有大量的空隙，特别是对于统计分析，日志系统这类应用最不适合用共享表空间。
独立表空间：
优点:
每个表都有自已独立的表空间。
每个表的数据和索引都会存在自已的表空间中。
可以实现单表在不同的数据库中移动。
空间可以回收
a) drop table 操作自动回收表空间，如果对于统计分析或是日值表，删除大量数据后可以通过: alter table tablename engine=innodb ；回收不用的空间。
b) 对于使用独立表空间的表，不管怎么删除，表空间的碎片不会太严重的影响性能，而且还有机会处理。 缺点：单表增加过大，如超过100 g。 相比较之下，使用独占表空间的效率以及性能会更高一点 共享表空间和独立表空间之间的转换。
show variables like "innodb_file_per_table"; on代表独立表空间管理，off代表共享表空间管理；修改数据库的表空间管理方式 修改innodb_file_per_table的参数值即可，但是修改不能影响之前已经使用过的共享表空间和独立表空间；innodb_file_per_table=1 为使用独占表空间innodb_file_per_table=0 为使用共享表空间复制代码2. 日志
日志文件：查询日志、慢查询日志、错误日志、事务日志、binlog日志、错误日志、中继日志
2.1 查询日志
查询日志在mysql中被称之为 general log（通用日志），不要被"查询日志"的名字误导，错误的以为查询日志只会记录select语句，其实不然，查询日志记录了数据库执行的命令，不管这些语句是否正确，都会被记录，我想这也是 general log 之所以"通用"的原因吧，由于数据库操作命令有可能非常多而且执行比较频繁，所以当开启了查询日志以后，数据库可能需要不停的写入查询日志，这样会增大服务器的io压力，增加很多系统开销，所以默认情况下，mysql的查询日志是没有开启的，但是开启查询日志也有助于我们分析哪些语句执行密集，执行密集的select语句对应的数据是否能够被缓存，查询日志也可以帮助我们分析问题，所以，我们可以根据实际情况决定是否开启查询日志，如果需要可以手动开启。如果开启了查询日志，那么我们可以通过如下3种方式存储查询日志。
方式1：将查询日志存放于指定的日志文件中。
方式2：将查询日志存放于 mysql.general_log 表中。
方式3：将查询日志同时存放于指定的日志文件与mysql库的general_log表中。
查看查询日志是否开启
show variables like 'general_log';复制代码
show variables where variable_name like "%general_log%" or variable_name="log_output";复制代码
general_log:表示查询日志是否开启，on表示开启，off表示未开启，默认为off
log_output:表示当查询日志开启以后，以哪种方式存放，log_output可以设置为4种值，"file"、"table"、"file,table"、"none"。
# 设置查询日志的输出方式set global log_output=[none|file|table|file,table];# 设置general log的日志文件路径set global general_log_file='/tmp/general.log';# 开启general logset global general_log=on;# 关闭general logset global general_log=off;复制代码2.2 慢日志
所谓的慢查询就是通过设置来记录超过一定时间的sql语句！
开启mysql的慢查询日志功能
# 查看是否开启 未使用索引的sql记录日志查询show variables like 'log_queries_not_using_indexes';# 开启 未使用索引的sql记录日志查询set global log_queries_not_using_indexs=on/off;# 查看超过多长时间的查询记入慢查询日志中show variables like 'long_query_time';# 设置记录时长，0为全部记录，设置之后需重新启动set global long_query_time=10# 查看是否开启 mysql慢查询日志功能show variables like 'slow_qurey_log'# 开启、关闭慢日志set global slow_qurey_log=on/off;# 查看日志记录位置show variables like 'slow_query_log_file';#日志存储方式show variables like "log_output";复制代码flie方式
select sleep(10) 执行完成查看日志
# time: 2020-10-26t05:12:09.564006z# user@host: root[root] @ localhost [] id: 12# query_time: 10.000272 lock_time: 0.000000 rows_sent: 1 rows_examined: 1set timestamp=1603689119;select sleep(10);复制代码table方式
日志分析工具 mysqldumpslow
查看 mysqldumpslow 的帮助信息：
-s order
测试一个config server 服务器挂机后，集群是否能读写数据
腾讯云服务器怎么区分大小
云计算转型需要持续智能吗？
品牌营销：“流量为王”才是关键？
网站更改后的问题-虚拟主机/数据库问题
帮忙看一下这个服务器控制面板怎么打不开
现在怎么还搞个短信验证这么地麻烦
如何通过大白菜U盘启动盘来进行电脑磁盘碎片整理
1.数据库的数据存储文件
mysql 数据库会在data目录下面建立一个以数据库为名的文件夹，用来存储数据库中的表文件数据。不同 的数据库引擎，每个表的扩展名也不一样 ，例如： innodb 用“ .myd ”作为扩展名， innodb 用 “.ibd” ， archive 用 “.arc” ，csv 用 “.csv“。
1. ".frm"文件
8.0之前无论是那种存储引擎，创建表之后就一定会生成一个以表明命名的'.frm'文件。frm文件主要存放与表相关的数据信息，主要包括表结构的定义信息。当数据库崩溃时，用户可以通过frm文件来恢复数据表结构。
2. ".myd"文件
“.myd”文件是innodb存储引擎专用，存放innodb表的数据。每一个innodb表都会有一个“.myd”文件与 之对应，同样存放于所属数据库的文件夹 下， 和“.frm”文件在一起。
3. ".myi"文件
“.myi”文件也是专属于innodb存储引擎的，主要存放innodb表的索引相关信息。对于innodb存储来说， 可以被cache 的内容主要就是来源 于“.myi”文件中。 每一个innodb表对应一个“.myi”文件，存放于位置 和“.frm”以及“.myd”一样。
4. ".ibd"文件与".ibdata"文件
这两种文件都是存放 innodb 数据的文件，之所以有两种文件来存放innodb的数据（包括索引），是因为 innodb 的数据存储方式能够通过配置来决定是使用共享表空间存放存储数据，还是独享表空间存放存储数据。独享表空间存储方式使用“.ibd”文件来存放数据，且每个表一个“.ibd”文件 ，文件存放在和innodb数据相同的位置。如果选用共享存储表空间来存放数据，则会使用 ibdata 文件来存放，所有表共同使用一个 （或者多个，可自行配置）ibdata文件。
ibdata文件可以通过 innodb_data_home_dir（数据存放目录）和 innodb_data_file_path (配置每个文件的名称) 两个参数配置组成 innodb_data_file_path 中可以一次配置多个ibdata文件 #innodb_data_file_path = ibdata1:2000m;ibdata2:10m:autoextend 配置方式共享表空间以及独占表空间都是针对数据的存储方式而言的。
共享表空间: 某一个数据库的所有的表数据，索引文件全部放在一个文件中。
独占表空间: 每一个表都将会生成以独立的文件方式来进行存储，每一个表都有一个.frm表描述文件，还有 一个.ibd文件。其中这个文件包括了 单独一个表的数据 内容以及索引内容。
4.1 两者对比
共享表空间：
优点： 可以放表空间分成多个文件存放到各个磁盘上。数据和文件放在一起方便管理。
缺点： 所有的数据和索引存放到一个文件中，多个表及索引在表空间中混合存储，这样对于一个表做了大量删除操作后表空间中将会有大量的空隙，特别是对于统计分析，日志系统这类应用最不适合用共享表空间。
独立表空间：
优点:
每个表都有自已独立的表空间。
每个表的数据和索引都会存在自已的表空间中。
可以实现单表在不同的数据库中移动。
空间可以回收
a) drop table 操作自动回收表空间，如果对于统计分析或是日值表，删除大量数据后可以通过: alter table tablename engine=innodb ；回收不用的空间。
b) 对于使用独立表空间的表，不管怎么删除，表空间的碎片不会太严重的影响性能，而且还有机会处理。 缺点：单表增加过大，如超过100 g。 相比较之下，使用独占表空间的效率以及性能会更高一点 共享表空间和独立表空间之间的转换。
show variables like "innodb_file_per_table"; on代表独立表空间管理，off代表共享表空间管理；修改数据库的表空间管理方式 修改innodb_file_per_table的参数值即可，但是修改不能影响之前已经使用过的共享表空间和独立表空间；innodb_file_per_table=1 为使用独占表空间innodb_file_per_table=0 为使用共享表空间复制代码2. 日志
日志文件：查询日志、慢查询日志、错误日志、事务日志、binlog日志、错误日志、中继日志
2.1 查询日志
查询日志在mysql中被称之为 general log（通用日志），不要被"查询日志"的名字误导，错误的以为查询日志只会记录select语句，其实不然，查询日志记录了数据库执行的命令，不管这些语句是否正确，都会被记录，我想这也是 general log 之所以"通用"的原因吧，由于数据库操作命令有可能非常多而且执行比较频繁，所以当开启了查询日志以后，数据库可能需要不停的写入查询日志，这样会增大服务器的io压力，增加很多系统开销，所以默认情况下，mysql的查询日志是没有开启的，但是开启查询日志也有助于我们分析哪些语句执行密集，执行密集的select语句对应的数据是否能够被缓存，查询日志也可以帮助我们分析问题，所以，我们可以根据实际情况决定是否开启查询日志，如果需要可以手动开启。如果开启了查询日志，那么我们可以通过如下3种方式存储查询日志。
方式1：将查询日志存放于指定的日志文件中。
方式2：将查询日志存放于 mysql.general_log 表中。
方式3：将查询日志同时存放于指定的日志文件与mysql库的general_log表中。
查看查询日志是否开启
show variables like 'general_log';复制代码
show variables where variable_name like "%general_log%" or variable_name="log_output";复制代码
general_log:表示查询日志是否开启，on表示开启，off表示未开启，默认为off
log_output:表示当查询日志开启以后，以哪种方式存放，log_output可以设置为4种值，"file"、"table"、"file,table"、"none"。
# 设置查询日志的输出方式set global log_output=[none|file|table|file,table];# 设置general log的日志文件路径set global general_log_file='/tmp/general.log';# 开启general logset global general_log=on;# 关闭general logset global general_log=off;复制代码2.2 慢日志
所谓的慢查询就是通过设置来记录超过一定时间的sql语句！
开启mysql的慢查询日志功能
# 查看是否开启 未使用索引的sql记录日志查询show variables like 'log_queries_not_using_indexes';# 开启 未使用索引的sql记录日志查询set global log_queries_not_using_indexs=on/off;# 查看超过多长时间的查询记入慢查询日志中show variables like 'long_query_time';# 设置记录时长，0为全部记录，设置之后需重新启动set global long_query_time=10# 查看是否开启 mysql慢查询日志功能show variables like 'slow_qurey_log'# 开启、关闭慢日志set global slow_qurey_log=on/off;# 查看日志记录位置show variables like 'slow_query_log_file';#日志存储方式show variables like "log_output";复制代码flie方式
select sleep(10) 执行完成查看日志
# time: 2020-10-26t05:12:09.564006z# user@host: root[root] @ localhost [] id: 12# query_time: 10.000272 lock_time: 0.000000 rows_sent: 1 rows_examined: 1set timestamp=1603689119;select sleep(10);复制代码table方式
日志分析工具 mysqldumpslow
查看 mysqldumpslow 的帮助信息：
-s order
测试一个config server 服务器挂机后，集群是否能读写数据
腾讯云服务器怎么区分大小
云计算转型需要持续智能吗？
品牌营销：“流量为王”才是关键？
网站更改后的问题-虚拟主机/数据库问题
帮忙看一下这个服务器控制面板怎么打不开
现在怎么还搞个短信验证这么地麻烦
如何通过大白菜U盘启动盘来进行电脑磁盘碎片整理
上一篇：麻烦你帮着挂载未使用的磁盘谢谢