MySQL 8.0 OCP 英文题库解析（四）

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

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

本期公布试题26~30

试题26:

The data in this instance transient;no backup or replication will be required.It is currently under performing.The database size is static and including indexes is 19G Total system memory is 32G After profiling the system,you highlight these MySQL status and global variables:
Com_rollback 85408355 Com_commit 1234342 
Innodb_buffer_pool_pages_free 163840 
mysqld Buffer_pool_size=20G
Innodb_flush_log_at_trx_commit=2
Disable-log-bin
The OS metrics indicate that disk is a bottleneck.Other variables retain their default values.Which two changes will provide the most benefit to the instance? F)innodb_log_file_size=1G [正确] 
C)innodb_flush_log_at_trx_commit=1 [错误] 
E)max_connections=10000 [错误] 
D)innodb_doublewrite=0 [正确] 
B)buffer_pool_size=24G [错误] 
A)sync_binlog=0 [错误]

解析

题目描述了一个 临时性、无备份、无复制需求的 MySQL 实例，当前性能较差，并给出以下关键信息：数据库大小：19GB（含索引），数据量稳定（不再增长）。系统内存：32GB，但当前 innodb_buffer_pool_size 仅配置为 20GB。
事务回滚率极高：Com_rollback（85,408,355）远高于 Com_commit（1,234,342），表明事务冲突或逻辑错误频繁。当前配置：
innodb_flush_log_at_trx_commit=2（牺牲持久性换取性能，事务提交时日志不立即刷盘）。
disable-log-bin（未启用二进制日志，无需考虑复制或恢复）。
innodb_buffer_pool_pages_free=163840（空闲缓冲池页数，计算得约 2.5GB 未使用，说明 buffer_pool_size 未充分利用）。The OS metrics indicate that disk is a bottleneck.
磁盘成为瓶颈（OS 指标显示 I/O 压力大）。哪两项更改将为实例提供最大的好处？F)innodb_log_file_size=1G [正确] 
innoDB日志文件（ib_logfile0/1）默认较小，频繁切换会引发大量磁盘I/O。
增大到1G可显著减少日志切换频率，缓解磁盘压力。C)innodb_flush_log_at_trx_commit=1 [错误] 
innodb_flush_log_at_trx_commit=1
设置为1会要求事务提交时日志立即刷盘，加剧磁盘I/O，与优化目标相反。E)max_connections=10000 [错误] 
max_connections=10000
连接数与当前磁盘瓶颈无关，且过高会导致资源浪费。D)innodb_doublewrite=0 [正确] 
双写缓冲（Doublewrite）是InnoDB防止数据页损坏的机制，但会额外写入磁盘。
临时实例可关闭（题目声明无需备份/持久性），提升写入性能。B)buffer_pool_size=24G [错误] 
当前20GB缓冲池已足够缓存19GB数据，增大后OS和其他进程可能内存不足。A)sync_binlog=0 [错误]
二进制日志已禁用（disable-log-bin），此参数无意义。

试题27:

Choose two.User `fwuser`@`localhost` is registered with the MySQL Enterprise Firewall and has been 
granted privileges for the SAKILA database.Examine these commands that you executed and the 
results:mysql> SELECT MODE FROM INFORMATION_SCHEMA.MYSQL_FIREWALL_USERSWHERE 
USERHOST = 'fwuser@localhost' ;（见下图）
You then execute this command:mysql> CALL 
mysql.sp_set_firewall_mode('fwuser@localhost' , 'RESET') ; Which two are true? G)The fwuser@localhost account mode is set to OFF. [正确] 
D)The mysql.firewall_users table is truncated. [错误] 
C)The whitelist of the fwuser@localhost account is truncated. [正确] 
A)The fwuser@localhost account is removed from the mysql.user table. [错误] 
B)The information_schema.MYSQL_FIREWALL_WHITELIST table is truncated. [错误] 
F)The fwuser@localhost account mode is set to DETECTING. [错误] 
E)The firewall resets all options to default values. [错误] 

sp_set_firewall_mode('user', 'RESET')	
1. 用户模式设为 OFF
2. 清空该用户白名单
不涉及的操作	
用户删除、全局配置变更、其他用户数据G)The fwuser@localhost account mode is set to OFF. [正确] 
执行 CALL mysql.sp_set_firewall_mode('fwuser@localhost', 'RESET') 后，防火墙模式会从当前状态（如图中的 DETECTING）重置为 OFF。
OFF 模式：防火墙对该用户完全禁用，不检测或拦截任何SQL语句。D)The mysql.firewall_users table is truncated. [错误] 
mysql.firewall_users 表被清空
错误原因：RESET 仅清空白名单表（firewall_whitelist），不会操作 firewall_users 表。C)The whitelist of the fwuser@localhost account is truncated. [正确] 
RESET 操作会清空该用户的白名单规则（即删除 mysql.firewall_whitelist 表中相关记录）。
注意：仅影响当前用户的白名单，其他用户或全局配置不受影响。A)The fwuser@localhost account is removed from the mysql.user table. [错误] 
fwuser@localhost 账户从 mysql.user 表移除
错误原因：防火墙操作不影响用户账户本身，仅管理防火墙规则。B)The information_schema.MYSQL_FIREWALL_WHITELIST table is truncated. [错误] 
information_schema.MYSQL_FIREWALL_WHITELIST 表被清空
错误原因：RESET 操作的是底层 mysql.firewall_whitelist 表，而非信息模式视图。F)The fwuser@localhost account mode is set to DETECTING. [错误] 
账户模式被设置为 DETECTING
错误原因：RESET 会将模式设为 OFF，而非 DETECTING（检测模式需手动启用）。E)The firewall resets all options to default values. [错误]
防火墙所有选项重置为默认值
错误原因：RESET 仅针对当前用户的白名单和模式，不影响全局防火墙配置。

试题28:

Choose two.Examine this statement and output:
mysql> SHOW GRANTS FOR jsmith; 
Grants for jsmith@%GRANT USAGE ON *.* TO 'jsmith@'%'
GRANT UPDATE (Name) ON 'world.country' TO 'jsmith'@'%'; 
2 rows in set (0.00 sec)
Which two SQL statements can jsmith execute? D)UPDATE world.country SET Name='all'; [正确] 
B)UPDATE world.country SET Name='one' LIMIT 1; [正确] 
C)UPDATE world.country SET Name=' first ' ORDER BY Name LIMIT 1; [错误] 
A)UPDATE world.country SET Name=CONCAT ('New ' ,Name) ; [错误] 
E)UPDATE world.country SET Name=' new' WHERE Name='old'; [错误]

解析

本题考查表的update、select权限

用户 jsmith 被授予对 world.country 表 Name 列的更新权限（GRANT UPDATE (Name) ON world.country）。
因此 正确选项D、BC) UPDATE world.country SET Name='first' ORDER BY Name LIMIT 1;
错误原因：虽然语法有效，但 ORDER BY 需要 SELECT 权限（用户未被授予），执行会报权限错误。A) UPDATE world.country SET Name=CONCAT('New ', Name);
错误原因：CONCAT 函数需读取原 Name 值，隐式需要 SELECT 权限（用户仅有 UPDATE 权限）。E) UPDATE world.country SET Name='new' WHERE Name='old';
错误原因：WHERE 条件需检查 Name 列值，隐式需要 SELECT 权限。

试题29:

Choose twoThere are five MySQL instances configured with a working group replication. Examine 
the output of the group members:mysql> SELECT MEMBER_ID, MEMBER_STATE 
FROMperformance_schema.replication_group_members;（见下图）Which two statements are true 
about network partitioning in the cluster?

C)The cluster will shut down to preserve data consistency. [错误] 
D)There could be both a 2 node and 3 node group replication still running, so shutting down group 
replication and diagnosing the issue is recommended. [正确] 
A)The group replication will buffer the transactions on the online nodes until the unreachable nodes 
return online. [错误] 
E)The cluster has built-in high availability and updates group_replication_ip_whitelist to remove the 
unreachable nodes. [错误] 
B)A manual intervention to force group members to be only the working two instances is required. 
[正确]

解析

这是一个有5个实例的MGR高可用集群

C)The cluster will shut down to preserve data consistency. [错误] 
集群将自动关闭以保持数据一致性
错误原因：Group Replication 不会自动关闭，分裂的子组可能继续运行（需手动干预）。D)There could be both a 2 node and 3 node group replication still running, so shutting down group replication and diagnosing the issue is recommended. [正确]
可能存在一个 2 节点和一个 3 节点的 Group Replication 仍在运行，建议关闭 Group Replication 并诊断问题2 个节点为 ONLINE（在线），3 个节点为 UNREACHABLE（不可达）。
若网络分裂为两部分（如 2 节点和 3 节点各自独立），可能形成“脑裂”（Split-Brain），双方均认为自己是多数派（quorum）。A)The group replication will buffer the transactions on the online nodes until the unreachable nodes return online. [错误] 
Group Replication 会在在线节点上缓冲事务，直到不可达节点恢复
错误原因：事务不会缓冲，若节点失去 quorum（多数派），写入会直接失败E)The cluster has built-in high availability and updates group_replication_ip_whitelist to remove the unreachable nodes. [错误] 
集群内置高可用性，并自动更新 group_replication_ip_whitelist 移除不可达节点错误原因：
group_replication_ip_whitelist 仅控制允许连接的 IP，不会动态修改。B)A manual intervention to force group members to be only the working two instances is required. [正确]
需要手动干预，强制将集群成员设置为仅剩的两个工作实例。

试题30:

Choose two.Which two statements are true about InnoDB data-at-rest encryption? 
A)It supports all indexes transparently. [正确] 
B)It decrypts data for use in memory: [正确] 
D)It does not support the transportable tablespaces feature. [错误] 
E)It enforces encryption from disk to memory and over network transmission. [错误] 
C)It supports only non-blob datatypes. [错误]

解析

A)It supports all indexes transparently. [正确] InnoDB 静态加密会自动加密表空间文件（.ibd）和重做日志（redo log），包括所有索引数据（如 B+树索引、全文索引等）。透明性：应用无需修改查询逻辑，索引操作（如 WHERE 条件、ORDER BY）仍正常执行。B)It decrypts data for use in memory: [正确] 
数据在内存中使用时会解密加密仅针对磁盘存储（静态数据），数据加载到内存前会自动解密，确保查询性能不受影响。安全性：内存中为明文，但依赖操作系统的内存保护机制（如 Linux 的 mlock）。D)It does not support the transportable tablespaces feature. [错误] 
不支持可传输表空间（Transportable Tablespaces）
错误原因：InnoDB 加密支持可传输表空间，但需在导出/导入时处理加密元数据（如密钥）。E)It enforces encryption from disk to memory and over network transmission. [错误] 
强制加密从磁盘到内存及网络传输
错误原因：
静态加密仅针对磁盘数据，内存和网络传输不加密（需额外配置 TLS 或应用程序加密）。C)It supports only non-blob datatypes. [错误]
仅支持非 BLOB 数据类型
错误原因：InnoDB 加密支持所有数据类型，包括 BLOB、TEXT 等大对象。