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 并非最高。