Q: SPFILE 和 PFILE 有什么区别？如何在两者之间转换？

- **PFILE（init .ora）**：文本文件，可直接编辑，修改后需重启生效。 - **SPFILE（spfile .ora）**：二进制文件，不可直接编辑，支持 `ALTER SYSTEM SET ... SCOPE=SPFILE/MEMORY/BOTH;` 动态修改。 Oracle 启动时按优先级查找：spfile .ora → spfile.ora → init .ora。 转换命令： ```sql CREATE PFILE='/tmp/initORCL.ora' FROM SPFILE; -- SPFILE 导出为 PFILE CREATE SPFILE FROM PFILE='/tmp/initORCL.ora'; -- PFILE 转为 SPFILE ``` 最佳实践：生产环境使用 SPFILE，同时定期备份为 PFILE 以便紧急情况下手工编辑启动。

Question 1

Oracle 实例（Instance）和数据库（Database）有什么区别？

Accepted Answer

Oracle 数据库由物理文件（数据文件、控制文件、重做日志文件等）组成，而实例是一组内存结构（SGA）和后台进程的集合。
一个实例在任何时刻只能挂载并打开一个数据库，但在 RAC 环境中，多个实例可以同时打开同一个数据库。
实例是临时的，随着启动而创建、关闭而消失；数据库是持久的，存储在磁盘上。
可以用 `SELECT instance_name FROM v$instance;` 和 `SELECT name FROM v$database;` 分别查看实例名和数据库名。

Question 2

请详细描述 SGA 的主要组件及其作用。

Accepted Answer

SGA（System Global Area）是所有服务器进程和后台进程共享的内存区域，主要包括：
- **Buffer Cache**：缓存从磁盘读取的数据块，减少物理 I/O。
- **Shared Pool**：包含 Library Cache（缓存 SQL 执行计划）和 Data Dictionary Cache（缓存数据字典信息）。
- **Redo Log Buffer**：暂存事务产生的重做记录，由 LGWR 进程定期写入在线重做日志文件。
- **Large Pool**：用于 RMAN 备份、共享服务器模式的 UGA 及并行查询消息缓冲。
- **Java Pool**：为 JVM 提供内存，用于 Java 存储过程。
- **Streams Pool**：用于 Oracle Streams / GoldenGate 的消息缓冲。
可以通过 `SHOW SGA;` 或 `SELECT * FROM v$sgainfo;` 查看各组件大小。

Question 3

PGA 是什么？它与 SGA 有什么不同？

Accepted Answer

PGA（Program Global Area）是每个服务器进程私有的内存区域，不与其他进程共享。
主要包含排序区（Sort Area）、哈希区（Hash Area）和会话信息。当执行 ORDER BY、GROUP BY 或 Hash Join 时，数据在 PGA 中处理。
如果 PGA 中内存不足，排序操作会溢出到临时表空间（磁盘），导致性能下降。
通过 `PGA_AGGREGATE_TARGET` 参数控制所有 PGA 的总大小，Oracle 自动管理各进程的分配。
查看当前 PGA 使用：`SELECT * FROM v$pgastat;`

Question 4

Oracle 有哪些关键的后台进程？各自的作用是什么？

Accepted Answer

Oracle 核心后台进程包括：
- **PMON**（Process Monitor）：清理失败的用户进程，释放锁和回滚未提交事务。
- **SMON**（System Monitor）：实例恢复（前滚已提交、回滚未提交），合并空闲空间，清理临时段。
- **DBWn**（Database Writer）：将 Buffer Cache 中的脏块写入数据文件。
- **LGWR**（Log Writer）：将 Redo Log Buffer 写入在线重做日志，是事务提交的关键。
- **CKPT**（Checkpoint）：触发 DBWn 写脏块，更新控制文件和数据文件头的 SCN 信息。
- **ARCn**（Archiver）：在归档模式下将已满的在线重做日志归档。
使用 `SELECT pname, description FROM v$bgprocess WHERE paddr <> '00';` 可查看活跃的后台进程。

Question 5

什么是表空间（Tablespace）？Oracle 有哪些默认表空间？

Accepted Answer

表空间是 Oracle 逻辑存储的最大单位，一个表空间包含一个或多个数据文件。所有段（表、索引等）都存储在表空间中。
Oracle 默认创建的表空间包括：
- **SYSTEM**：存储数据字典和系统对象。
- **SYSAUX**：SYSTEM 的辅助表空间，存放 AWR、ADDM 等组件数据。
- **UNDOTBS1**：存储 Undo 数据。
- **TEMP**：用于排序、哈希等临时操作。
- **USERS**：默认的用户数据表空间。
创建表空间示例：`CREATE TABLESPACE app_data DATAFILE '/u01/app/oracle/oradata/app_data01.dbf' SIZE 1G AUTOEXTEND ON NEXT 256M MAXSIZE 10G;`

Question 6

DELETE、TRUNCATE 和 DROP 的区别是什么？

Accepted Answer

- **DELETE** 是 DML 操作，逐行删除数据并记录 Undo，可以回滚，可以带 WHERE 条件。不释放高水位线（HWM）以下的空间。
- **TRUNCATE** 是 DDL 操作，快速移除表中所有数据并重置 HWM，自动提交不可回滚，不触发行级触发器。`TRUNCATE TABLE emp;` 效率远高于 `DELETE FROM emp;`
- **DROP** 是 DDL 操作，删除表的定义和所有数据。12c 起默认放入回收站，可用 `FLASHBACK TABLE emp TO BEFORE DROP;` 恢复。
面试关键点：DELETE 产生大量 Undo 和 Redo，TRUNCATE 几乎不产生；TRUNCATE 不能在有外键引用时直接执行。

Question 7

Oracle 中有哪些类型的索引？B-Tree 索引和 Bitmap 索引的适用场景分别是什么？

Accepted Answer

Oracle 支持多种索引类型：B-Tree（默认）、Bitmap、Function-Based、Reverse Key、Index-Organized Table (IOT) 等。
- **B-Tree 索引**：适合高基数（high cardinality）列，如主键和唯一键。OLTP 场景首选，支持范围扫描和等值查询。
- **Bitmap 索引**：适合低基数列（如性别、状态），在 OLAP/数据仓库场景中高效。多个 Bitmap 索引可通过 AND/OR 位运算快速组合。
- Bitmap 索引在 DML 频繁的 OLTP 环境中会导致严重的锁争用，因为一个 Bitmap 段可能覆盖多行。
创建示例：`CREATE BITMAP INDEX idx_status ON orders(status);`

Question 8

如何创建用户并授予权限？ROLE 和直接授权有什么区别？

Accepted Answer

创建用户并授权的基本步骤：
```sql
CREATE USER app_user IDENTIFIED BY "SecureP@ss123"
  DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp QUOTA 1G ON users;
GRANT CONNECT, RESOURCE TO app_user;
GRANT SELECT ON hr.employees TO app_user;
```
- **直接授权**：权限直接授予用户，立即生效，权限存在于当前会话中。
- **ROLE 授权**：权限授予角色，再将角色授予用户。便于批量管理，但 PL/SQL 定义者权限的存储过程中不识别通过 ROLE 获得的对象权限，需要直接授权。
查看用户权限：`SELECT * FROM dba_sys_privs WHERE grantee = 'APP_USER';`
查看角色中的权限：`SELECT * FROM role_sys_privs WHERE role = 'CONNECT';`

Question 9

Oracle 数据库的启动（Startup）过程分为哪几个阶段？每个阶段做了什么？

Accepted Answer

Oracle 启动分为三个阶段：
1. **NOMOUNT**：读取参数文件（spfile/pfile），分配 SGA，启动后台进程。此时实例已创建但尚未关联数据库。用于创建数据库或重建控制文件。
2. **MOUNT**：打开控制文件，获取数据文件和重做日志文件的位置信息。此时可以执行恢复操作、修改归档模式、重命名数据文件。
3. **OPEN**：打开数据文件和重做日志文件，SMON 执行实例恢复（如果需要），数据库对用户可用。
命令：`STARTUP NOMOUNT;` → `ALTER DATABASE MOUNT;` → `ALTER DATABASE OPEN;` 或直接 `STARTUP;`
受限模式打开：`STARTUP RESTRICT;`（仅允许有 RESTRICTED SESSION 权限的用户连接）。

Question 10

SPFILE 和 PFILE 有什么区别？如何在两者之间转换？

Accepted Answer

- **PFILE（init.ora）**：文本文件，可直接编辑，修改后需重启生效。 - **SPFILE（spfile.ora）**：二进制文件，不可直接编辑，支持 `ALTER SYSTEM SET ... SCOPE=SPFILE/MEMORY/BOTH;` 动态修改。 Oracle 启动时按优先级查找：spfile.ora → spfile.ora → init.ora。转换命令： ```sql CREATE PFILE='/tmp/initORCL.ora' FROM SPFILE; -- SPFILE 导出为 PFILE CREATE SPFILE FROM PFILE='/tmp/initORCL.ora'; -- PFILE 转为 SPFILE ``` 最佳实践：生产环境使用 SPFILE，同时定期备份为 PFILE 以便紧急情况下手工编辑启动。

Question 11

Oracle Net 监听器（Listener）的作用是什么？如何配置和管理？

Accepted Answer

监听器是运行在数据库服务器上的网络服务，负责接收客户端连接请求并将其转交给对应的数据库实例。
关键配置文件：
- **listener.ora**：服务端监听器配置，定义监听地址和端口。
- **tnsnames.ora**：客户端连接描述符，定义如何连接到数据库。
- **sqlnet.ora**：网络层参数配置（超时、加密等）。
管理命令：
```bash
lsnrctl start        # 启动监听
lsnrctl stop         # 停止监听
lsnrctl status       # 查看状态
lsnrctl services     # 查看注册的服务
```
动态服务注册：PMON 自动将实例信息注册到监听器（`LOCAL_LISTENER` 参数控制），推荐使用动态注册而非静态配置。

Question 12

如何安全地关闭 Oracle 数据库？SHUTDOWN 的几种模式有什么区别？

Accepted Answer

Oracle 提供四种关闭模式：
- **SHUTDOWN NORMAL**：等待所有用户主动断开连接，不接受新连接。可能等待很长时间。
- **SHUTDOWN TRANSACTIONAL**：等待所有活跃事务完成后断开会话，不接受新事务。比 NORMAL 更实用。
- **SHUTDOWN IMMEDIATE**：回滚所有未提交事务，立即断开所有会话。**生产环境最常用**，保证数据一致性。
- **SHUTDOWN ABORT**：立即终止实例，不做回滚和检查点。相当于断电，下次启动需要实例恢复。仅在其他模式无法关闭时使用。
最佳实践：先发公告通知用户，使用 `ALTER SYSTEM KILL SESSION` 清理长事务，然后执行 `SHUTDOWN IMMEDIATE;`。

Question 13

RESTORE 和 RECOVER 的区别是什么？

Accepted Answer

- **RESTORE**：从备份中将物理文件（数据文件、控制文件等）复制回磁盘。这一步只是"还原文件"，数据库此时不一致。
- **RECOVER**：应用归档日志和在线重做日志进行前滚（Roll Forward），将数据恢复到指定的时间点或最新状态，使数据库达到一致性。
完整恢复流程：
```
RMAN> STARTUP MOUNT;
RMAN> RESTORE DATABASE;   -- 从备份还原数据文件
RMAN> RECOVER DATABASE;   -- 应用日志前滚
RMAN> ALTER DATABASE OPEN; -- 打开数据库
```
如果是部分恢复（单个数据文件），可以在数据库 OPEN 状态下进行：
`RMAN> RESTORE DATAFILE 5; RECOVER DATAFILE 5; ALTER DATABASE DATAFILE 5 ONLINE;`

Question 14

Oracle 数据库有哪些主要的升级方法？

Accepted Answer

Oracle 提供多种升级方法，适用于不同场景：
1. **DBUA（Database Upgrade Assistant）**：图形化工具，引导式完成升级，适合单实例和简单环境。
2. **手动升级（Manual Upgrade）**：通过运行 `catctl.pl` 脚本手动执行数据字典升级，灵活性最高。
3. **AutoUpgrade**：Oracle 19c+ 推荐工具，自动完成预检查、升级、后续处理全流程。命令：`java -jar autoupgrade.jar -config config.cfg -mode deploy`
4. **可传输表空间（Transportable Tablespace）**：跨平台、跨版本迁移数据，适合大数据量场景。
5. **Data Pump Export/Import**：逻辑导出导入，适合小型数据库或需要重组数据的场景。
6. **GoldenGate**：零停机迁移升级，适合 7×24 业务环境。
选择方法时需考虑数据库大小、停机窗口、跨平台需求和复杂度。

Question 15

什么是 Oracle 用户配置文件（Profile）？它如何实施密码策略？

Accepted Answer

Oracle **Profile** 是一组资源限制和密码策略的命名集合，分配给用户以控制资源使用和密码行为。
密码相关参数：
- **PASSWORD_LIFE_TIME**：密码有效期（天数），过期后需修改密码。
- **PASSWORD_REUSE_TIME / PASSWORD_REUSE_MAX**：密码重用限制。
- **PASSWORD_LOCK_TIME**：账户锁定时长（登录失败后）。
- **FAILED_LOGIN_ATTEMPTS**：允许连续失败登录的次数，超过则锁定账户。
- **PASSWORD_VERIFY_FUNCTION**：密码复杂度验证函数（如 `ORA12C_VERIFY_FUNCTION`）。
```sql
CREATE PROFILE secure_profile LIMIT
  PASSWORD_LIFE_TIME 90
  FAILED_LOGIN_ATTEMPTS 5
  PASSWORD_LOCK_TIME 1/24
  PASSWORD_VERIFY_FUNCTION ORA12C_VERIFY_FUNCTION;
ALTER U

Question 16

什么是 Oracle 多租户架构？CDB 和 PDB 的概念是什么？

Accepted Answer

Oracle **多租户架构**（12c 引入）允许一个容器数据库（CDB）中承载多个可插拔数据库（PDB），实现数据库整合。
核心概念：
- **CDB（Container Database）**：容器数据库，包含 Root 容器（CDB$ROOT）、种子容器（PDB$SEED）和用户 PDB。
- **CDB$ROOT**：存储 Oracle 系统元数据，所有 PDB 共享。
- **PDB$SEED**：只读模板，用于快速创建新 PDB。
- **PDB（Pluggable Database）**：可插拔数据库，每个 PDB 拥有独立的用户、表空间、数据，对应用透明。
```sql
-- 查看 CDB 中的所有 PDB
SELECT con_id, name, open_mode FROM v$pdbs;
-- 切换到指定 PDB
ALTER SESSION SET CONTAINER = pdb1;
-- 查看当前容器
SHOW CON_NAME;
```
**优势**：资源共享（共用内存和后台进程）、简化管理（统一补丁和备份）、快速部署（秒级克隆 PDB）、更好的资源隔离。

Question 17

Data Guard 有哪些保护模式？最大性能、最大可用性和最大保护之间有什么区别？

Accepted Answer

Data Guard 提供三种保护模式，平衡数据保护级别和性能：
**最大保护（Maximum Protection）**：
- 主库必须等待至少一个备库确认 redo 已写入磁盘后才提交事务。
- **零数据丢失**，但如果备库不可用，主库将自动关闭。
- 使用 `SYNC` 传输模式 + `AFFIRM`。
**最大可用性（Maximum Availability）**：
- 正常情况下与最大保护相同（同步传输）。
- 如果备库不可用，自动降级为最大性能模式，主库不会停机。
- **零数据丢失**（备库正常时），兼顾可用性。
**最大性能（Maximum Performance）**：
- 默认模式，主库异步传输 redo，不等待备库确认。
- 对主库性能影响最小，但可能丢失少量未传输的 redo。
- 使用 `ASYNC` 传输模式。
```sql
-- 查看当前保护模式
SELECT protection_mode, protection_level FROM v$database;
-- 切换保护模式
ALTER DATABASE SET STANDBY DATABASE T

Question 18

Oracle 中 TRUNCATE、DELETE 和 DROP 有什么区别？

Accepted Answer

三者都可以移除数据，但机制和影响完全不同：
**DELETE**（DML 操作）：
- 逐行删除数据，可带 `WHERE` 条件，可以回滚（`ROLLBACK`）。
- 产生大量 redo 和 undo，触发触发器（triggers），不释放高水位线以下的空间。
- `DELETE FROM employees WHERE department_id = 10;`
**TRUNCATE**（DDL 操作）：
- 删除表中所有数据，**不可回滚**（隐式提交），不触发行级触发器。
- 重置高水位线（HWM），立即释放空间（除非使用 `REUSE STORAGE`），速度极快。
- `TRUNCATE TABLE employees;` 或 `TRUNCATE TABLE employees REUSE STORAGE;`
**DROP**（DDL 操作）：
- 删除整个表结构和数据，表进入回收站（可用 `FLASHBACK TABLE` 恢复）。
- `DROP TABLE employees;` — 可恢复；`DROP TABLE employees PURGE;` — 彻底删除。
|

Question 19

请描述InnoDB存储引擎的整体架构，包括内存结构和磁盘结构的主要组件。

Accepted Answer

InnoDB架构分为**内存结构**和**磁盘结构**两大部分。

**内存结构**包括：**Buffer Pool**（缓冲池）用于缓存数据页和索引页，是InnoDB最重要的内存区域；**Change Buffer**（变更缓冲）用于缓存对非唯一二级索引的修改；**Adaptive Hash Index**（自适应哈希索引）由InnoDB自动为热点页构建；**Log Buffer**（日志缓冲）用于暂存redo log写入磁盘前的数据。

**磁盘结构**包括：**系统表空间**（ibdata1）存储数据字典、Undo Log等；**独立表空间**（.ibd文件）存储每张表的数据和索引；**Redo Log**（ib_logfile0/1）用于崩溃恢复；**Undo Tablespaces** 在8.0中独立管理回滚段；**Doublewrite Buffer**防止部分写入导致的页损坏。

可通过 `SHOW ENGINE INNODB STATUS\G` 查看运行时各组件的状态信息。Buffer Pool大小由 `innodb_buffer_pool_size` 控制，通常设置为

Question 20

InnoDB表空间有哪些类型？`innodb_file_per_table`参数的作用是什么？

Accepted Answer

InnoDB支持以下**表空间类型**：

**系统表空间**（System Tablespace）：默认文件ibdata1，存储数据字典、Doublewrite Buffer、Change Buffer以及Undo Log（5.7默认）。由 `innodb_data_file_path` 配置，如 `innodb_data_file_path=ibdata1:1G:autoextend`。

**独立表空间**（File-Per-Table Tablespace）：每张表对应一个 `.ibd` 文件，存储该表的数据和索引。通过 `innodb_file_per_table=ON`（8.0默认开启）启用。优点是可单独备份、回收空间（`ALTER TABLE t ENGINE=InnoDB` 或 `OPTIMIZE TABLE t`）。

**通用表空间**（General Tablespace）：用户自定义的共享表空间，可存放多张表。通过 `CREATE TABLESPACE ts1 ADD DATAFILE 'ts1.ibd' ENGINE=InnoDB` 创建，建表时指定 `CRE

Question 21

MySQL主从复制的基本原理是什么？涉及哪些关键线程？

Accepted Answer

MySQL主从复制基于**Binlog**实现，采用异步复制模型，涉及三个关键线程：

**主库（Master）**端：**Binlog Dump Thread**（也称IO线程），当从库连接主库请求复制时创建。该线程负责读取主库的Binlog事件并发送给从库。每个从库连接对应一个Dump线程，可通过 `SHOW PROCESSLIST` 查看。

**从库（Slave）**端有两个线程：**IO Thread** 负责连接主库并接收Binlog事件，写入本地的**Relay Log**（中继日志）；**SQL Thread** 负责读取Relay Log并在从库上重放（replay）SQL事件，完成数据同步。

复制流程：主库执行事务 → 写入Binlog → Dump Thread发送事件 → 从库IO Thread接收并写Relay Log → SQL Thread重放Relay Log。可通过 `SHOW SLAVE STATUS\G`（8.0.22+用 `SHOW REPLICA STATUS\G`）查看复制状态，关键字段包括 `Seconds_Behind_Master`、

Question 22

Binlog的三种格式（STATEMENT、ROW、MIXED）分别有什么特点？生产环境推荐使用哪种？

Accepted Answer

**STATEMENT格式**：记录原始SQL语句。优点是日志量小；缺点是某些函数（如 `UUID()`、`NOW()`、`RAND()`）和非确定性查询可能导致主从数据不一致。

**ROW格式**：记录每行数据的变更前后值（Before Image / After Image）。优点是精确可靠，不存在主从不一致问题；缺点是对于批量操作（如 `UPDATE t SET status=1 WHERE id>1000`）日志量极大。可通过 `binlog_row_image=MINIMAL` 仅记录变更列来减少日志量。

**MIXED格式**：默认使用STATEMENT，当检测到不安全的SQL时自动切换为ROW。折中方案，但在某些边界情况下仍可能不安全。

**生产环境推荐使用ROW格式**，原因：
1. 数据一致性最有保障，是MGR和GTID的推荐配置。
2. 配合 `binlog_row_image=FULL` 可用于数据恢复和审计。
3. 支持 **binlog2sql** 等工具进行闪回操作（从ROW日志生成反向SQL）。

设置：`SET GLOBAL binlog_form

Question 23

如何使用EXPLAIN分析MySQL查询的执行计划？关键字段有哪些含义？

Accepted Answer

**EXPLAIN**是分析查询性能的核心工具。在SELECT/INSERT/UPDATE/DELETE前加 `EXPLAIN` 即可获取执行计划。MySQL 8.0还支持 `EXPLAIN ANALYZE` 获取实际执行统计。

**关键字段含义**：
- **type**（访问类型）：从优到劣排列为 `system > const > eq_ref > ref > range > index > ALL`。ALL表示全表扫描，应优先优化。
- **key**：实际使用的索引名称，NULL表示未使用索引。
- **rows**：预估需要扫描的行数，值越小越好。
- **Extra**：重要附加信息。`Using index`（覆盖索引）最优；`Using filesort`（额外排序）和 `Using temporary`（临时表）需要优化；`Using where` 表示存储引擎返回后还需Server层过滤。
- **filtered**：经过WHERE条件过滤后剩余的行百分比。
- **ref**：与索引比较的列或常量。

**EXPLAIN FORMAT=JSON** 提供更详

Question 24

什么是覆盖索引（Covering Index）？如何利用覆盖索引优化查询性能？

Accepted Answer

**覆盖索引（Covering Index）**是指一个索引包含了查询所需的所有列，使得查询可以完全通过索引获取数据，而**无需回表**访问聚簇索引中的完整行。在EXPLAIN输出中，Extra列显示 `Using index` 即表示使用了覆盖索引。

**示例**：假设表 `orders` 有索引 `idx_user_status(user_id, status)`：
```sql
-- 覆盖索引：查询列都在索引中，无需回表
SELECT user_id, status FROM orders WHERE user_id = 100;
-- 非覆盖索引：需要回表获取order_amount
SELECT user_id, status, order_amount FROM orders WHERE user_id = 100;
```

**优化策略**：
1. 对高频查询，创建包含SELECT列和WHERE列的**联合索引**，使其成为覆盖索引。
2. 避免 `SELECT *`，只查询必要的列，增加覆盖索引命中概率。
3. 对于COUNT查询，二级索引天然是覆盖索引：`SELE

Question 25

如何识别和优化MySQL慢查询？请描述完整的慢查询分析流程。

Accepted Answer

慢查询优化是DBA的核心日常工作，完整流程如下：

**第一步：开启慢查询日志**
```sql
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;  -- 超过1秒记录
SET GLOBAL log_queries_not_using_indexes = ON;  -- 记录未使用索引的查询
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';
```

**第二步：分析慢查询日志**
使用 **pt-query-digest** 聚合分析：`pt-query-digest /var/log/mysql/slow.log > slow_report.txt`。报告按总耗时排序，识别TOP N最慢的查询模式（Query Fingerprint）。

**第三步：逐个优化**
1. `EXPLAIN ANALYZE` 查看执行计划和实际耗时。
2. 检查是否缺少索引（type=ALL全表扫描）。
3. 检查是否索引失效（函数操作索引列、隐式类型转换

Question 26

什么是前缀索引？什么时候应该使用前缀索引？如何选择合适的前缀长度？

Accepted Answer

**前缀索引（Prefix Index）**是对字符串列的前N个字符建立索引，而非整个列值。适用于长字符串列（如VARCHAR(255)、TEXT），可显著减少索引大小和维护开销。

**创建语法**：`ALTER TABLE t ADD INDEX idx_email(email(10))`，对email列的前10个字符建立索引。

**选择前缀长度的方法**：通过计算不同前缀长度的**区分度（Selectivity）**来确定：
```sql
-- 完整列的区分度（基准值）
SELECT COUNT(DISTINCT email) / COUNT(*) AS full_selectivity FROM users;
-- 不同前缀长度的区分度
SELECT
  COUNT(DISTINCT LEFT(email, 5)) / COUNT(*) AS sel_5,
  COUNT(DISTINCT LEFT(email, 7)) / COUNT(*) AS sel_7,
  COUNT(DISTINCT LEFT(email, 10)) / COUNT(*) AS sel_10,

Question 27

mysqldump和mysqlpump有什么区别？各自的优缺点是什么？

Accepted Answer

**mysqldump**和**mysqlpump**都是MySQL官方的逻辑备份工具，但设计理念不同：

**mysqldump**：经典的单线程逻辑备份工具。按表逐个导出SQL语句（CREATE TABLE + INSERT）。优点是稳定可靠、兼容性好、可通过管道直接导入。缺点是**单线程**，大数据量时速度慢。常用参数：
```bash
mysqldump -u root -p --single-transaction --master-data=2 --routines --triggers --events --databases db1 db2 > backup.sql
```
`--single-transaction` 利用MVCC获取一致性快照（仅InnoDB），不锁表。`--master-data=2` 在备份中记录Binlog位点信息（用于搭建从库）。

**mysqlpump**：MySQL 5.7引入的增强版，支持**多线程并行导出**。可按数据库或表级别并行，使用 `--default-parallelism=N` 设置线程数。额外支持进度显示、用户和权限导

Question 28

逻辑备份和物理备份有什么区别？分别适用于什么场景？

Accepted Answer

**逻辑备份**（mysqldump, mysqlpump, mydumper）导出的是SQL语句或CSV格式的数据，与存储引擎无关：
- **优点**：跨平台/跨版本兼容性好；可选择性备份（单表、单库）；备份文件可读可编辑；适合小数据量和数据迁移。
- **缺点**：速度慢（需要逐行读取并生成SQL）；恢复慢（需要逐条执行SQL重建索引）；备份期间可能影响性能。
- **适用场景**：数据量 < 50GB的库；跨版本迁移（如5.7→8.0）；部分表/库的备份恢复。

**物理备份**（XtraBackup, MySQL Enterprise Backup, 文件系统快照）直接拷贝数据文件：
- **优点**：速度快（接近磁盘I/O速度）；恢复快（拷贝文件即可）；对线上影响小。
- **缺点**：通常只能恢复到同版本/同平台；不能选择性恢复单表（8.0 XtraBackup支持部分表导出）；备份文件不可读。
- **适用场景**：数据量 > 50GB的大库；日常定期备份；需要快速恢复的场景。

**生产最佳实践**：**物理全量备份（每天/每周）+ Binlog持续归档（PITR）+

Question 29

MySQL的用户权限系统是如何工作的？GRANT权限检查的顺序是什么？

Accepted Answer

MySQL使用**多层级权限系统**，权限信息存储在mysql系统数据库的权限表中。权限检查按照从**全局到局部**的顺序进行：

**权限层级**（从高到低）：
1. **全局权限**（`mysql.user`表）：适用于所有数据库，如 `GRANT ALL ON *.* TO 'user'@'host'`。
2. **数据库权限**（`mysql.db`表）：适用于指定数据库，如 `GRANT SELECT ON mydb.* TO 'user'@'host'`。
3. **表权限**（`mysql.tables_priv`表）：适用于指定表，如 `GRANT SELECT, INSERT ON mydb.t1 TO 'user'@'host'`。
4. **列权限**（`mysql.columns_priv`表）：适用于指定列，如 `GRANT SELECT(id, name) ON mydb.t1 TO 'user'@'host'`。
5. **存储过程/函数权限**（`mysql.procs_priv`表）。

**检查顺序**：MySQL采用**OR逻辑**逐级检查。先检查

Question 30

如何为MySQL配置SSL/TLS加密连接？如何验证连接是否使用了加密？

Accepted Answer

MySQL支持使用**SSL/TLS**加密客户端与服务器之间的通信，防止数据在传输过程中被窃听或篡改。

**服务器端配置**（my.cnf）：
```ini
[mysqld]
ssl-ca=/etc/mysql/ssl/ca.pem
ssl-cert=/etc/mysql/ssl/server-cert.pem
ssl-key=/etc/mysql/ssl/server-key.pem
require_secure_transport=ON  # 强制所有连接使用加密
tls_version=TLSv1.2,TLSv1.3  # 指定支持的TLS版本
```

MySQL 8.0会自动生成SSL证书（存放在数据目录），也可使用 `mysql_ssl_rsa_setup` 工具手动生成。

**强制特定用户使用SSL**：
```sql
ALTER USER 'secure_user'@'%' REQUIRE SSL;
-- 或更严格：要求X509证书
ALTER USER 'secure_user'@'%' REQUIRE X509;
-- 或指定特定证书属性
ALTER USER

数据库面试题库

选择一道题，开始练习