MySQL 面试宝典 - 文件篇

  一.请简述 MySQL 配置文件的加载顺序？

MySQL 读取配置文件的顺序

读取顺序：/etc/mysql/my.cnf>/etc/my.cnf>~/.my.cnf 

命令验证：

方法 1：

方法 2：     

二.MySQL 启动时如果找不到参数文件，会报错还是启动？

MySQL 数据库参数文件的作用和 Oracle 数据库的参数文件极其类似，不同的是，Oracle 实例在启动时若找不到参数文件，是不能进行装载（mount）操作。

MySQL 稍微有所不同，MySQL 实例可以不需要参数文件，这时所有的参数值取决于编译 MySQL 时指定的默认值和源代码中指定参数的默认值。

如果 MySQL 实例在默认的数据库目录下找不到 mysql 架构，则启动同样会失败。

三.如何查看 MySQL 参数？

可以把数据库参数看成一个键/值（key/value）对。

可以通过命令 SHOW VARIABLES 查看数据库中的所有参数，也可以通过 LIKE 来过滤参数名。

从 MySQL 5.1 版本开始，还可以通过 information_schema 架构下的 GLOBAL_VARIABLES 视图来进行查找。

通过配置文件查看参数

cat /etc/my.cnf|grep -i "VARIABLE_NAME"

四.如何修改 MySQL 参数?

会话级别修改：

set session innodb_lock_wait_timeout=50;

对当前会话立即生效，退出后，参数失效，不影响后续的会话

全局级别修改：

set global innodb_lock_wait_timeout=50;

当前会话不生效，对后续连接进来的会话生效

修改配置文件

五：MySQL 有哪些类型表空间，简述各自作用？

MySQL 有五种表空间: 

系统表空间(也叫共享表空间) 、独立表空间 、临时表空间 、undo 表空间 、通用表空间。

1.系统表空间

主要用来存放 undo 信息、insert buffer 索引页、double write buffer 等数据。

系统表空间系统表空间（system tablespace）是在初始化 mysql 实例时生成的，读取 my.cnf 中的 innodb_data_file_path 参数，初始对应大小的文件。

mysql 默认的系统表空间文件大小是 12M，只有一个文件(ibdata1)，它默认是保存在 mysql 实例的 datadir 变量的目录下。

#### 在 mysql 实例中查看共享表空间的大小

#### 在 mysql 的 datadir 变量所指定的目录下查看系统表空间文件

当系统表空间不够用时（也就是 ibdata1 文件），会自动扩展（autoextend），默认每次自动扩展 64M。

2.独立表空间

从 mysql 5.6.6 版本开始，独立表空间（file-per-table tablespaces）默认是开启的（也就是 innodb_file_per_table 参数不设置时，默认等于 1），在开启的情况下，创建一个 innodb 引擎的表，那么表有自己独立的一些数据文件。

这些数据文件在操作系统上的文件体现如下所示：

表名.frm   # 表的表结构文件(里面存放的是表的创建语句)

表名.ibd   # 表的数据文件(当有数据往表中插入时,数据就保存之个文件中的)

独立表空间的好处:

01:表数据分开存放(不把所有鸡蛋放在 1 个蓝子里面);损坏 1 个文件不至于影响所有表

02:容易维护,查询速度快（IO 分散）

03:使用 MySQL Enterprise Backup 快速备份或还原在每表文件表空间中创建的表，不会中断其他 InnoDB 表的使用

缺点：

对 fsync 系统调用来说不友好，如果使用一个表空间文件的话单次系统调用可以完成数据的落盘，但是如果你将表空间文件拆分成多个。

原来的一次 fsync 可能会就变成针对涉及到的所有表空间文件分别执行一次 fsync，增加 fsync 的次数。

独立表空间文件中仅存放该表对应数据、索引、insert buffer bitmap。

其余的诸如：undo 信息、insert buffer 索引页、double write buffer 等信息依然放在默认表空间，也就是共享表空间中。

当 innodb_file_per_table 参数为 0 时，表示使用系统表空间，当为 1 时，表示使用独立表空间。

innodb_file_per_table 选项只对新建的表起作用，对于已经分配了表空间的表不起作用。

如果想把已经分配到系统表空间中的表转移到独立表空间，可以使用下面语句:

ALTER TABLE 表名 TABLESPACE [=] innodb_file_per_tables;

如果要将已经存储在独立表空间的表转移到系统表空间:

ALTER TABLE 表名 TABLESPACE [=] innodb_system;

其中中括号里的=可有可无。

与 InnoDB 不同，MyISAM 并没有什么表空间一说，表的数据和索引都存放在对应的数据库子目录下。

假如 cjc 表使用的是 MyISAM 存储引擎，那么他所在数据库对应的目录下会为 cjc 表创建下面 3 个文件：

1.cjc.frm 表结构。2.cjc.MYD 表数据。3.cjc.MYI 表索引。

3.临时表空间

临时表空间用于存放用户创建的临时表和磁盘内部临时表。

参数 innodb_temp_data_file_path 定义了临时表空间的一些名称、大小、规格属性

MySQL 5.7 对于 InnoDB 存储引擎的临时表空间做了优化。

在 MySQL 5.7 之前，INNODB 引擎的临时表都保存在 ibdata 里面，而 ibdata 的贪婪式磁盘占用导致临时表的创建与删除对其他正常表产生非常大的性能影响。

在 MySQL5.7 中，对于临时表做了下面两个重要方面的优化：

(1)MySQL 5.7 把临时表的数据以及回滚信息(仅限于未压缩表)从共享表空间里面剥离出来，形成自己单独的表空间，参数为 innodb_temp_data_file_path。

(2)MySQL 5.7 把临时表的相关检索信息保存在系统信息表中：information_schema.innodb_temp_table_info. 

而 MySQL 5.7 之前的版本想要查看临时表的系统信息是没有太好的办法。

select * frominformation_schema.innodb_temp_table_info;

需要注意的一点就是：

虽然 INNODB 临时表有自己的表空间，但是目前还不能自己定义临时表空间文件的保存路径，只能是继承 innodb_data_home_dir。

此时如果想要拿其他的磁盘，比如内存盘来充当临时表空间的保存地址，只能用老办法，做软链。

MySQL 临时表类型

1.外部临时表，通过 create temporary table 语法创建的临时表，可以指定存储引擎为 memory，innodb, myisam 等等，这类表在会话结束后，会被自动清理。

如果临时表与非临时表同时存在，那么非临时表不可见。show tables 命令不显示临时表信息。

可通过 informationschema.INNODBTEMPTABLEINFO 系统表可以查看外部临时表的相关信息

2.内部临时表，通常在执行复杂 SQL，比如 group by, order by, distinct, union 等，执行计划中如果包含 Using temporary.

还有 undo 回滚的时候，但空间不足的时候，MySQL 内部将使用自动生成的临时表，以辅助完成工作。

外部临时表、内部临时表

参数

tmp_table_size

内部临时表在内存中的的最大值，与 max_heap_table_size 参数共同决定，取二者的最小值。如果临时表超过该值，就会从内存转移到磁盘上；

max_heap_table_size

用户创建的内存表的最大值，也用于和 tmp_table_size 一起，限制内部临时表在内存中的大小；

innodb_tmpdir

innodb_temp_data_file_path

innodb 引擎下 temp 文件属性。建议限制 innodbtempdatafilepath = ibtmp1:1G:autoextend:max:30G；

default_tmp_storage_engine

外部临时表(create temporary table 创建的表)默认的存储引擎；

internal_tmp_disk_storage_engine

磁盘上的内部临时表存储引擎，可选值为 myisam 或者 innodb。

使用 innodb 表在某些场景下，比如临时表列太多，或者行大小超过限制，可能会出现“ Row size too large or Too many columns”的错误，这时应该将临时表的 innodb 引擎改回 myisam。

slave_load_tmpdir

tmpdir

表示磁盘上临时表所在的目录。

临时表目录，当临时表大小超过一定阈值，就会从内存转移到磁盘上；

max_tmp_tables

状态信息

Created_tmp_disk_tables

执行 SQL 语句时，MySQL 在磁盘上创建的内部临时表数量，如果这个值很大，可能原因是分配给临时表的最大内存值较小，或者 SQL 中有大量排序、分组、去重等操作，SQL 需要优化；

Created_tmp_files

创建的临时表数量；

Created_tmp_tables

执行 SQL 语句时，MySQL 创建的内部临时表数量；

Slave_open_temp_tables

statement 或则 mix 模式下才会看到有使用；

通过复制，当前 slave 创建了多少临时表

information_schema.innodb_temp_table_info

4.undo 表空间

MySQL5.5 时代的 undo log

在 MySQL5.5 以及之前，InnoDB 的 undo log 也是存放在 ibdata1 里面的。一旦出现大事务，这个大事务所使用的 undo log 占用的空间就会一直在 ibdata1 里面存在，即使这个事务已经关闭。

答案是没有直接的办法，只能全库导出 sql 文件，然后重新初始化 mysql 实例，再全库导入。

MySQL 5.6 时代的 undo log

MySQL 5.6 增加了参数 innodb_undo_directory、innodb_undo_logs 和 innodb_undo_tablespaces 这 3 个参数，可以把 undo log 从 ibdata1 移出来单独存放。

innodb_undo_directory，

指定单独存放 undo 表空间的目录，默认为.(即 datadir)，可以设置相对路径或者绝对路径。

该参数实例初始化之后虽然不可直接改动，但是可以通过先停库，修改配置文件，然后移动 undo 表空间文件的方式去修改该参数；

innodb_undo_tablespaces，

指定单独存放的 undo 表空间个数，例如如果设置为 3，则 undo 表空间为 undo001、undo002、undo003，每个文件初始大小默认为 10M。

该参数我们推荐设置为大于等于 3，原因下文将解释。该参数实例初始化之后不可改动；

innodb_undo_logs，

指定回滚段的个数(早期版本该参数名字是 innodb_rollback_segments)，默认 128 个。每个回滚段可同时支持 1024 个在线事务。

这些回滚段会平均分布到各个 undo 表空间中。

该变量可以动态调整，但是物理上的回滚段不会减少，只是会控制用到的回滚段的个数。

实际使用方面，在初始化实例之前，我们只需要设置 innodb_undo_tablespaces 参数(建议大于等于 3)即可将 undo log 设置到单独的 undo 表空间中。

MySQL 5.7 时代的 undo log

MySQL 5.7 引入了新的参数，innodb_undo_log_truncate，开启后可在线收缩拆分出来的 undo 表空间。

在满足以下 2 个条件下，undo 表空间文件可在线收缩：

innodb_undo_tablespaces>=2。因为 truncate undo 表空间时，该文件处于 inactive 状态，如果只有 1 个 undo 表空间，那么整个系统在此过程中将处于不可用状态。

为了尽可能降低 truncate 对系统的影响，建议将该参数最少设置为 3；

innodb_undo_logs>=35(默认 128)。

因为在 MySQL 5.7 中，第一个 undo log 永远在系统表空间中，另外 32 个 undo log 分配给了临时表空间，即 ibtmp1，至少还有 2 个 undo log 才能保证 2 个 undo 表空间中每个里面至少有 1 个 undo log；

满足以上 2 个条件后，把 innodb_undo_log_truncate 设置为 ON 即可开启 undo 表空间的自动 truncate，这还跟如下 2 个参数有关：

(1)innodb_max_undo_log_size，undo 表空间文件超过此值即标记为可收缩，默认 1G，可在线修改；

(2)innodb_purge_rseg_truncate_frequency,指定 purge 操作被唤起多少次之后才释放 rollback segments。当 undo 表空间里面的 rollback segments 被释放时，undo 表空间才会被 truncate。由此可见，该参数越小，undo 表空间被尝试 truncate 的频率越高。

MySQL 8.0 收缩 UNDO

1、添加新的 undo 文件 undo003。mysql8.0 中默认 innodb_undo_tablespace 为 2 个，不足 2 个时，不允许设置为 inactive，且默认创建的 undo 受保护，不允许删除。

2、将膨胀的 undo 临时设置为 inactive，以及 innodb_undo_log_truncate=on，自动 truncate 释放膨胀的 undo 空间。

3、重新将释放空间之后的 undo 设置为 active，可重新上线使用。

具体操作如下：

查看 undo 大小

添加新的 undo 表空间 undo003。系统默认是 2 个 undo，大小设置 4G

注意：创建添加新的 undo 必须以.ibu 结尾，否则触发如下错误提示

5.通用表空间

通用表空间(General Tablespaces)

通用表空间为通过 create tablespace 语法创建的共享表空间。

通用表空间可以创建于 mysql 数据目录外的其他表空间，其可以容纳多张表，且其支持所有的行格式。

通过 create table tab_name ... tablespace [=] tablespace_name 或 alter table tab_name tablespace [=] tablespace_name 语法将其添加与通用表空间内。

六：请简述 MySQL redo log 和 binlog 区别？ 

redo log 和 binlog 的区别：

日志归属：

binlog 由 Server 层实现，所有引擎都可以使用。

redo log 是 innodb 引擎特有的日志。

日志类型：

binlog 是逻辑日志，记录原始 SQL 或数据变更前后内容。

redo 是物理日志，记录在哪些页上进行了哪些修改。

写入方式：

binlog 是追加写，写满一个文件后创建新文件继续写。

redo log 是循环写，全部写满后覆盖从头写。

适用场景：

binlog 适用于主从恢复和误删除恢复。

redo log 适用于崩溃恢复。

虽然在更新 BufferPool 后，也写入了 binlog 中，但 binlog 并不具备 crash-safe 的能力。

因为崩溃可能发生在写 binlog 后，刷脏前。在主从同步的情况下，从节点会拿到多出来的一条 binlog。

所以 server 层的 binlog 是不支持崩溃恢复的，只是支持误删数据恢复。InnoDB 考虑到这一点，自己实现了 redo log。

因为最开始 MySQL 里并没有 InnoDB 引擎。MySQL 自带的引擎是 MyISAM，但是 MyISAM 没有 crash-safe 的能力，binlog 日志只能用于归档。

而 InnoDB 是另一个公司以插件形式引入 MySQL 的，既然只依靠 binlog 是没有 crash-safe 能力的，所以 InnoDB 使用另外一套日志系统——也就是 redo log 来实现 crash-safe 能力。

源码附件已经打包好上传到百度云了，大家自行下载即可～

链接: https://pan.baidu.com/s/14G-bpVthImHD4eosZUNSFA?pwd=yu27提取码: yu27

百度云链接不稳定，随时可能会失效，大家抓紧保存哈。

如果百度云链接失效了的话，请留言告诉我，我看到后会及时更新～

开源地址

码云地址：http://github.crmeb.net/u/defu

Github 地址：http://github.crmeb.net/u/defu

本文转载自：http://blog.itpub.net/29785807/viewspace-2886375/