MySQL 8.0 OCP 英文题库解析（十三）

Oracle 为庆祝 MySQL 30 周年，截止到 2025.07.31 之前。所有人均可以免费考取原价245美元的MySQL OCP 认证。

从今天开始，将英文题库免费公布出来，并进行解析，帮助大家在一个月之内轻松通过OCP认证。

本期公布试题111~120

试题111:

SELECT * FROM performance_schema.table_io_waits_summary_by_table 
WHERE COUNT_STAR > 0 \GOBJECT_TYPE: TABLE  
OBJECT_SCHEMA: test  
OBJECT_NAME: demo_test  
COUNT_STAR: 61567093  
SUM_TIMER_WAIT: 59009007572922  
MIN_TIMER_WAIT: 395922  
AVG_TIMER_WAIT: 958095  
MAX_TIMER_WAIT: 558852005358  COUNT_READ: 38665056  
SUM_TIMER_READ: 20598719962188  
MIN_TIMER_READ: 395922  
AVG_TIMER_READ: 532728  
MAX_TIMER_READ: 558852005358  COUNT_WRITE: 22902028  
SUM_TIMER_WRITE: 38410287610743  
MIN_TIMER_WRITE: 1130688  
AVG_TIMER_WRITE: 1677006  
MAX_TIMER_WRITE: 17205682920  COUNT_DELETE: 22902028  
SUM_TIMER_DELETE: 38410287610743  
MIN_TIMER_DELETE: 1130688  
AVG_TIMER_DELETE: 1677006  
MAX_TIMER_DELETE: 17205682920D)Average read times are approximately three times faster than writes. [正确] 
C)22902028 rows were deleted. These columns aggregate all delete operations. [正确] 
E)The longest I/O wait was for writes. [错误] 
B)The I/O average time is 532728. These columns aggregate all fetch operations [错误] 
A)I/O distribution is approximately 50/50 read/write. [错误]

解析

D	Average read times are approximately three times faster than writes.	读操作的平均耗时大约是写的 3 倍快	
✅ 正确	AVG_TIMER_READ (532728) ≈ 1/3 * AVG_TIMER_WRITE (1677006)C	22902028 rows were deleted. These columns aggregate all delete operations.	
共删除了 22902028 行，这些列统计了所有删除操作	
✅ 正确	COUNT_DELETE=22902028，与 COUNT_WRITE 相同，说明所有写操作都是删除E	The longest I/O wait was for writes.	
最长的 I/O 等待是写操作	
❌ 错误	MAX_TIMER_READ (558852005358) > MAX_TIMER_WRITE (17205682920)，读操作的最大等待时间更长B	The I/O average time is 532728. These columns aggregate all fetch operations.	
I/O 平均时间是 532728，这些列统计了所有 fetch 操作	
❌ 错误	AVG_TIMER_READ=532728 仅适用于读操作，不是所有 I/O 操作的平均值A	I/O distribution is approximately 50/50 read/write.	
I/O 分布大约是 50/50 读写	
❌ 错误	COUNT_READ (38665056) ≈ 1.7 * COUNT_WRITE (22902028)，读操作更多

试题112:

Choose two.Which two statements are true about using backups of the binary log? 
C) Multiple binary logs can be used to restore data. [正确] 
D) They allow for point-in-time recovery of the data. [正确] 
E) Multiple binary logs can be applied in parallel for faster data restoration. [错误] 
A) Binary logs are relatively small, and therefore, excellent for long-term storage and disaster 
recovery. [错误] 
B) Binary logs can always be used to unapply unwanted schema changes. [错误]   

解析

关于二进制日志（binary log）备份的使用，哪两项是正确的？
选项	英文原文	中文翻译	是否正确	解析
C	Multiple binary logs can be used to restore data.	
可以使用多个二进制日志来恢复数据	
✅ 正确	MySQL 支持按顺序应用多个 binlog 文件进行数据恢复（如 mysqlbinlog binlog.00001 binlog.00002 | mysql -u root -p）。D	They allow for point-in-time recovery of the data.	
它们允许进行时间点恢复（PITR）	
✅ 正确	通过 mysqlbinlog --start-datetime 和 --stop-datetime 可恢复到指定时间点。E	Multiple binary logs can be applied in parallel for faster data restoration.	
可以并行应用多个二进制日志以加快数据恢复	
❌ 错误	MySQL binlog 必须按顺序应用，不能并行执行（否则会导致数据不一致）。A	Binary logs are relatively small, and therefore, excellent for long-term storage and disaster recovery.	
二进制日志相对较小，因此非常适合长期存储和灾难恢复	
❌ 错误	binlog 会持续增长（尤其在高负载环境下），长期存储需配合日志轮替（expire_logs_days）。B	Binary logs can always be used to unapply unwanted schema changes.	
二进制日志始终可用于回退不需要的 schema 变更	
❌ 错误	binlog 记录的是操作（如 DML/DDL），直接回退需手动分析逆向操作（如 DROP TABLE 无法直接回退，需从备份恢复）。

试题113:

Examine this command, which executes successfully on InnoDB 
Cluster:dba.dropMetadataSchema()Which two statements are true? C)Connections driven by MySQL Router are not affected by the command.（元数据已经没有） [错误] 
D)The mysql_innodb_cluster_metadata schema is dropped from all reachable members of the cluster. [正确] 
A)The command drops the mysql_innodb_cluster_metadata schema and re-creates it.(不重建) [错误] 
B)The mysql_innodb_cluster_metadata schema is dropped from the instance where the connection was established. [错误] 
F)Group Replication is still operational, but InnoDB Cluster must be reimported under MySQL Shell. 
[正确] 
E)Group Replication will be dissolved and all metadata purged.（需要执行 dissolve） [错误]

解析

在 InnoDB Cluster 上成功执行以下命令后，哪两项是正确的？
dba.dropMetadataSchema()D	The mysql_innodb_cluster_metadata schema is dropped from all reachable members of the cluster.	mysql_innodb_cluster_metadata 
元数据 schema 会从集群所有可达成员中删除	
✅ 正确	该命令会同步删除所有节点上的元数据 schema（需节点可连接）。F	Group Replication is still operational, but InnoDB Cluster must be reimported under MySQL Shell.	
Group Replication 仍保持运行，但需在 MySQL Shell 中重新导入 InnoDB Cluster	
✅ 正确	元数据删除后，集群管理功能失效，需通过 dba.createCluster() 或 dba.rebootClusterFromCompleteOutage() 重建。C	Connections driven by MySQL Router are not affected by the command.	
MySQL Router 驱动的连接不受此命令影响	
❌ 错误	元数据删除后，Router 无法获取集群拓扑，现有连接可能中断，新连接会失败。A	The command drops the mysql_innodb_cluster_metadata schema and re-creates it.	
命令会删除并重新创建 mysql_innodb_cluster_metadata schema	
❌ 错误	仅删除，不会自动重建（需手动操作）。B	The mysql_innodb_cluster_metadata schema is dropped from the instance where the connection was established.	
仅在当前连接的实例上删除元数据 schema	
❌ 错误	会同步删除所有可达节点的元数据，非单节点操作。E	Group Replication will be dissolved and all metadata purged.	
Group Replication 会被解散且所有元数据清除	
❌ 错误	GR 组仍存在，仅元数据被删除（需显式调用 cluster.dissolve() 解散集群）。

试题114:

Choose two.You are backing up raw InnoDB files by using mysqlbackup.Which two groups of files 
will be backed up during a full backup? 
E) *.sdi files [错误] 
A) *.ibd files [正确] 
C) *.CSM files [错误] 
B) ibbackup files [错误] 
D) ib_logfile* files [正确]

解析

使用 mysqlbackup 工具备份 InnoDB 原始文件时，哪两组文件会在全量备份中被包含？
A	*.ibd files	
InnoDB 表空间文件（存储表数据和索引）	
✅ 正确	全量备份必须包含所有 *.ibd 文件（核心数据文件）。D	ib_logfile* files	
InnoDB 重做日志文件（事务日志）	
✅ 正确	备份时默认包含活跃的 ib_logfile0 和 ib_logfile1（用于崩溃恢复）。E	*.sdi files	
序列化字典信息文件（MySQL 8.0+ 表元数据）	
❌ 错误	mysqlbackup 不备份 *.sdi 文件（这些文件会在恢复时自动重建）。C	*.CSM files	
列式存储元数据文件（ColumnStore 插件相关）	
❌ 错误	与 InnoDB 无关，属于 MariaDB ColumnStore 插件文件。B	ibbackup files	
mysqlbackup 工具生成的临时文件	
❌ 错误	这些是备份过程中生成的临时文件，非备份对象。

试题115:

Choose two.Which two are characteristics of snapshot-based backups? 
C) Snapshot-based backups greatly reduce time during which the database and applications are 
unavailable. [正确] 
B) There is no need for InnoDB tables to perform its own recovery when restoring from the 
snapshot backup. [错误] 
D) A separate physical copy must be made before releasing the snapshot backup. [正确] 
E) Snapshot backups can be used only in virtual machines. [错误] 
A) The frozen file system can be cloned to another virtual machine immediately into active service. 
[错误]  

解析

关于基于快照的备份（snapshot-based backups），哪两项是正确的？C	Snapshot-based backups greatly reduce time during which the database and applications are unavailable.	
快照备份大幅减少数据库和应用不可用的时间	
✅ 正确	快照备份是瞬时完成的，几乎不阻塞数据库操作（如 LVM 快照、存储阵列快照）。D	A separate physical copy must be made before releasing the snapshot backup.	
在释放快照备份前，必须生成一个独立的物理副本	
✅ 正确	快照本身依赖存储系统的写时复制机制，需在释放前复制数据到独立备份（如 lvconvert --merge 前需先导出数据）。B	There is no need for InnoDB tables to perform its own recovery when restoring from the snapshot backup.	
从快照备份恢复时，InnoDB 表无需执行自身的恢复过程	
❌ 错误	快照可能捕获到不完整事务，恢复后 InnoDB 仍需通过重做日志（redo log）进行崩溃恢复。E	Snapshot backups can be used only in virtual machines.	
快照备份仅适用于虚拟机环境	
❌ 错误	物理服务器同样支持（如 LVM、ZFS、存储硬件快照）。A	The frozen file system can be cloned to another virtual machine immediately into active service.	
冻结的文件系统可立即克隆到另一台虚拟机并投入生产	
❌ 错误	快照数据需一致性处理（如应用日志或锁表），直接克隆可能导致数据损坏。

试题116:

Choose three.Examine these statements, which execute successfully:
TRUNCATE test; BEGIN;INSERT 
INTO test (id, name) VALUES(1, \Hello\) ; 
ROLLBACK;
SELECT id FROM test;
Which three storage 
engines would return a nonempty recordset for the test table when executing the statements? 
A)MEMORY [正确] 
E)MyISAM [正确] 
F)InnoDB [错误] 
B)BLACKHOLE [错误] 
D)NDB [错误] 
C)ARCHIVE [正确]

解析

以下 SQL 语句执行成功后，哪三种存储引擎的 test 表在执行 SELECT id FROM test; 时会返回非空结果集？TRUNCATE test;          -- 清空表
BEGIN;                  -- 开启事务
INSERT INTO test (id, name) VALUES(1, 'Hello');  -- 插入数据
ROLLBACK;               -- 回滚事务
SELECT id FROM test;    -- 查询数据选项	存储引擎	是否返回数据	原因
A	MEMORY	✅ 返回数据	
TRUNCATE 后数据被清空，但 ROLLBACK 对 MEMORY 引擎无效（不支持事务），插入的数据仍然存在。E	MyISAM	✅ 返回数据	
MyISAM 不支持事务，ROLLBACK 无效，插入的数据不会被回滚。C	ARCHIVE	✅ 返回数据	
ARCHIVE 引擎虽然支持事务，但 TRUNCATE 是 DDL 语句，自动提交，ROLLBACK 无法撤销 INSERT。F	InnoDB	❌ 不返回数据	
InnoDB 支持事务，ROLLBACK 会撤销 INSERT，表保持空状态。B	BLACKHOLE	❌ 不返回数据	
BLACKHOLE 引擎不存储任何数据，插入后立即丢弃。D	NDB	❌ 不返回数据	
NDB（MySQL Cluster）支持事务，ROLLBACK 会撤销 INSERT。

试题117:

Choose two.Which two statements are true about the mysql_config_editor program? 
F)It manages the configuration of the MySQL Firewall feature. [错误] 
C)It will use client options by default unless you provide --login-path. [正确] 
D)It can be used to create and edit SSL certificates and log locations. [错误] 
A)It provides an interface to change my.cnf files. [错误] 
E)It manages the configuration of user privileges for accessing the server. [错误] 
G)It manages the configuration of client programs. [正确] 
B)It can move datadir to a new location. [错误]

解析

关于 mysql_config_editor 程序，哪两项是正确的？C	It will use client options by default unless you provide --login-path.	
默认使用客户端选项，除非显式指定 --login-path	
✅ 正确	
mysql_config_editor 存储的登录路径（login-path）可覆盖默认客户端配置（如 ~/.my.cnf）。G	It manages the configuration of client programs.	
管理客户端程序的配置	
✅ 正确	
该工具用于安全存储客户端连接凭据（如用户名、密码、主机等），供 mysql、mysqldump 等客户端使用。F	It manages the configuration of the MySQL Firewall feature.	
管理 MySQL 防火墙功能的配置	
❌ 错误	
防火墙功能由 MYSQL_FIREWALL 插件管理，与 mysql_config_editor 无关。D	It can be used to create and edit SSL certificates and log locations.	
可用于创建和编辑 SSL 证书及日志位置	
❌ 错误	
SSL 证书和日志配置需手动修改 my.cnf 或使用专用工具（如 openssl）。A	It provides an interface to change my.cnf files.	
提供修改 my.cnf 文件的接口	
❌ 错误	该工具仅管理客户端登录凭据，不直接修改 my.cnf。E	It manages the configuration of user privileges for accessing the server.	
管理用户访问服务器的权限配置	
❌ 错误	
用户权限由 GRANT/REVOKE 管理，与 mysql_config_editor 无关。B	It can move datadir to a new location.	
可迁移数据目录（datadir）到新位置	
❌ 错误	数据目录迁移需手动操作或修改 my.cnf，该工具不涉及。

试题118:

Choose two.Which two statements are true about the binary log encryption feature? 
C)It can be set at run time. [正确] 
D)It can be activated per session. [错误] 
A)It requires a keyring plugin. [正确] 
B)When enabled it encrypts existing binary logs. [错误] 
E)It encrypts any connecting slaves connection thread. [错误]

解析

关于二进制日志加密功能（binary log encryption），哪两项是正确的？
C	It can be set at run time.	
可以在运行时动态启用	
✅ 正确	
通过 SET GLOBAL binlog_encryption = ON 可在线开启加密，无需重启 MySQL。A	It requires a keyring plugin.	
需要密钥环插件（keyring plugin）支持	
✅ 正确	
加密依赖 keyring_file 或 keyring_vault 等插件管理密钥。D	It can be activated per session.	
可以按会话（session）激活	
❌ 错误	
二进制日志加密是全局性设置，无法针对单个会话启用或禁用。B	When enabled it encrypts existing binary logs.	
启用后会加密已有的二进制日志	
❌ 错误	
仅加密新生成的 binlog 文件，已有文件不受影响。E	It encrypts any connecting slaves connection thread.	
会加密所有从库连接线程	
❌ 错误	
加密仅针对 binlog 文件内容，不涉及主从复制通信（需单独配置 SSL 加密连接）。

试题119:

Choose two.Examine this MySQL client command to connect to a remote database:mysql -h 
remote.example.org -u root -p --protocol=TCP --ssl-mode=Which two --ssl-mode values will 
ensure that an X.509-compliant certificate will be used to establish the SSL/TLS connection to 
MySQL? 
B) REQUIRED [错误] 
C) VERIFY_IDENTITY. [正确] 
E) VERIFY_CA [正确] 
A) DISABLED [错误] 
D) PREFERED [错误] 

解析

以下 MySQL 客户端命令用于连接远程数据库，其中 --ssl-mode 参数的哪两个值可以确保使用符合 X.509 标准的证书建立 SSL/TLS 连接？
mysql -h remote.example.org -u root -p --protocol=TCP --ssl-mode=?C	VERIFY_IDENTITY	
✅ 强制验证	
验证服务器证书的 CA 及主机名（需证书的 CN/SAN 匹配 remote.example.org）。E	VERIFY_CA	
✅ 强制验证	
仅验证服务器证书的 CA，不检查主机名。B	REQUIRED	
❌ 不强制验证	
启用 SSL，但不验证证书合法性（可能被中间人攻击）。A	DISABLED	
❌ 不启用 SSL	
完全禁用加密，无证书验证。D	PREFERRED（正确拼写应为 PREFERRED）	
❌ 不强制验证	
优先使用 SSL，但不验证证书（选项拼写错误，实际应为 PREFERRED）。

试题120:

Choose two.Examine this query and its output:（见下图）Which two statements are true? 

A)User bob had a significantly higher ratio of SELECT + INSERT statements to QUIT than both app 
and root users.app [错误] 
D)The root user had the largest number of modified rows for a SELECT statement. [错误] 
C)The app user had the highest total number of rows read from storage engines. [正确] 
E)The root user had the largest single wait time. [正确] 
B)User bob had the largest total time waiting for locks. [错误]

解析

user	        用户名	        执行SQL语句的MySQL用户账号。
statement	语句类型	        SQL语句的类型（如SELECT、INSERT、QUIT等）。
total	        总执行次数	该用户执行此类语句的总次数。
total_latency	总延迟时间	该用户执行此类语句的总耗时（如5.91 h表示5.91小时）。
max_latency	单次最大延迟	该用户执行此类语句的单次最长耗时（如1.05 s表示1.05秒）。
lock_latency	总锁等待时间	该用户执行此类语句时等待锁的总耗时（如23.19 m表示23.19分钟）。
rows_sent	返回给客户端的行数	该用户执行此类语句时返回给客户端的行数（仅适用于查询语句）。
rows_examined	扫描的行数	该用户执行此类语句时从存储引擎扫描的行数（反映查询效率）。
rows_affected	影响的行数	该用户执行此类语句时修改的行数（适用于INSERT/UPDATE/DELETE等）。
full_scans	全表扫描次数	该用户执行此类语句时触发全表扫描的次数（高值可能需优化索引）。

C	The app user had the highest total number of rows read from storage engines.	
app 用户从存储引擎读取的总行数最多	
✅ 正确	题目数据表明 app 用户的 rows_examined	显著高于其他用户。E	The root user had the largest single wait time.	root 
用户有最大的单次等待时间	
✅ 正确	题目数据中 root 的 max_latency	最高（如某个查询长时间阻塞）。A	User bob had a significantly higher ratio of SELECT + INSERT statements to QUIT than both app and root users.	
bob 用户的 (SELECT + INSERT) 与 QUIT 语句比例显著高于 app 和 root	
❌ 错误	D	The root user had the largest number of modified rows for a SELECT statement.	
root 用户在 SELECT 语句中修改的行数最多	
❌ 错误	SELECT 语句不修改数据，逻辑矛盾（可能是混淆了 SELECT ... FOR UPDATE）。B	User bob had the largest total time waiting for locks.	
bob 用户的总锁等待时间最长	
❌ 错误	题目数据中 bob 的 lock_latency	并非最高。