MySQL高级特性之：`MySQL`的`Invisible Columns`：其在`DDL`操作和应用兼容性中的作用。 - 智猿学院-前后端，数据库，人工智能，云计算等领域前沿技术讲座

MySQL Invisible Columns：DDL操作与应用兼容性的利器

大家好，今天我们来深入探讨MySQL 8.0版本引入的一项重要特性——Invisible Columns（不可见列）。这项特性在数据库设计、数据迁移、版本升级以及应用兼容性方面都扮演着重要的角色。我们将从概念、语法、应用场景以及性能影响等多个角度进行详细分析，并辅以实例代码，帮助大家理解如何在实际项目中有效利用Invisible Columns。

1. Invisible Columns的概念与语法

Invisible Columns，顾名思义，是指默认情况下对SELECT * 查询不可见的列。这并不意味着这些列不存在或无法访问，它们仍然是表结构的一部分，可以像普通列一样进行插入、更新、删除和查询（显式指定列名）。关键在于，它们在默认的查询行为中被隐藏，这为数据库的改造和优化提供了极大的灵活性。

1.1 语法

创建Invisible Column的语法非常简单，只需在列定义中添加 INVISIBLE 关键字即可：

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(255),
    salary DECIMAL(10, 2),
    department_id INT,
    audit_ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP INVISIBLE, -- Invisible Column
    INDEX idx_department_id (department_id)
);

CREATE TABLE employees_v2 (
    id INT PRIMARY KEY,
    name VARCHAR(255),
    salary DECIMAL(10, 2),
    department_id INT,
    audit_ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP INVISIBLE,
    bonus DECIMAL(10,2) DEFAULT 0.00 INVISIBLE,  -- 新增的不可见列
    INDEX idx_department_id (department_id)
);

修改现有列为Invisible Column：

ALTER TABLE employees MODIFY COLUMN audit_ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP INVISIBLE;

修改Invisible Column为Visible Column：

ALTER TABLE employees MODIFY COLUMN audit_ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP VISIBLE;

1.2 查看Invisible Columns

可以通过以下方式查看表的列定义，从而确认哪些列是Invisible的：

DESCRIBE table_name; 或 DESC table_name;
SHOW CREATE TABLE table_name;
查询information_schema.COLUMNS 表。

DESCRIBE employees;

SHOW CREATE TABLE employees;

SELECT COLUMN_NAME, IS_VISIBLE
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'your_database_name' AND TABLE_NAME = 'employees';

2. Invisible Columns的应用场景

Invisible Columns在多种场景下都能发挥重要作用，主要包括：

2.1 数据库版本升级与数据迁移

在数据库版本升级或数据迁移过程中，可能需要向现有表添加新的列，但又不希望立即影响现有的应用程序。这时，可以将新列定义为Invisible，然后逐步修改应用程序以适应新的数据结构。

例如，从 employees 表升级到 employees_v2 表，新增了 bonus 列，为了平滑过渡，可以先将 bonus 列设为 Invisible。

2.2 数据安全与隐私保护

某些列可能包含敏感信息，例如审计信息、操作日志等。虽然这些信息对系统运维至关重要，但并不需要在所有查询中都暴露出来。可以将这些列设置为Invisible，仅在需要时显式查询。

例如，上述的 audit_ts 列就是一个很好的例子，它记录了数据的更新时间，用于审计追踪，但普通用户通常不需要知道这个信息。

2.3 简化SELECT * 查询

SELECT * 是一种常见的查询方式，但在某些情况下，表中的某些列并不需要被检索出来。将这些列设置为Invisible，可以减少不必要的数据传输和处理，提高查询效率。

2.4 逻辑删除标记

在某些应用中，我们不直接删除数据，而是通过添加一个 is_deleted 列来标记数据是否被删除。可以将 is_deleted 列设置为 Invisible，避免在常规查询中返回已标记为删除的数据，从而简化应用程序的逻辑。

2.5 应用程序兼容性

如果应用程序依赖于特定列的顺序，并且需要添加新列，则可以将新列设置为Invisible，以避免破坏应用程序的兼容性。

3. Invisible Columns与DDL操作

Invisible Columns可以与各种DDL操作结合使用，例如：

CREATE TABLE: 创建表时定义Invisible Columns。
ALTER TABLE: 修改现有列的可见性，添加新的Invisible Columns。
DROP COLUMN: 删除Invisible Columns（与其他列一样）。
CREATE INDEX: 可以为Invisible Columns创建索引。

3.1 创建索引

可以为Invisible Columns创建索引，这对于提高特定查询的性能非常有用。即使列是Invisible的，索引仍然可以被优化器使用。

CREATE INDEX idx_audit_ts ON employees (audit_ts); -- 为Invisible Column创建索引

3.2 使用Generated Columns构建复杂的Invisible Columns

Invisible Columns可以与Generated Columns结合使用，创建更加复杂的逻辑。例如，可以创建一个Generated Column，根据其他列的值计算出一个结果，并将该列设置为Invisible。

ALTER TABLE employees ADD COLUMN full_name VARCHAR(255) GENERATED ALWAYS AS (CONCAT(name, ' ', 'Employee')) INVISIBLE;

4. Invisible Columns与应用兼容性

Invisible Columns在保持应用兼容性方面具有显著优势。当需要向现有表添加新列时，将新列设置为Invisible可以避免破坏现有应用，因为 SELECT * 查询不会返回这些新列。

4.1 如何显式查询Invisible Columns

要查询Invisible Columns，必须显式指定列名：

SELECT id, name, salary, department_id, audit_ts FROM employees; -- 显式指定Invisible Column

4.2 设置Session级别的可见性

可以通过设置 optimizer_use_invisible_indexes 系统变量来控制是否使用Invisible索引。

SET optimizer_use_invisible_indexes = OFF; -- 默认值，不使用Invisible索引
SET optimizer_use_invisible_indexes = ON;  -- 使用Invisible索引

5. Invisible Columns的限制与注意事项

MySQL 8.0及以上版本: Invisible Columns是MySQL 8.0版本引入的特性，之前的版本不支持。
存储引擎: Invisible Columns特性适用于所有支持列可见性的存储引擎，例如InnoDB。
SELECT * 的影响: 需要注意 SELECT * 查询的行为，确保应用程序不会依赖于未显式指定的列。
数据导出与导入: 在数据导出和导入过程中，需要注意Invisible Columns的处理方式。使用 mysqldump 命令时需要加上 --column-statistics=0 选项，否则导出的 sql 文件会包含 column statistics，导入时可能会报错。

6. 示例代码

下面提供一个更完整的示例，展示Invisible Columns在版本升级和数据迁移中的应用。

6.1 初始表结构 (employees)

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(255),
    salary DECIMAL(10, 2),
    department_id INT
);

INSERT INTO employees (id, name, salary, department_id) VALUES
(1, 'Alice', 50000.00, 101),
(2, 'Bob', 60000.00, 102),
(3, 'Charlie', 70000.00, 101);

6.2 升级后的表结构 (employees_v2)，添加Invisible Columns

CREATE TABLE employees_v2 (
    id INT PRIMARY KEY,
    name VARCHAR(255),
    salary DECIMAL(10, 2),
    department_id INT,
    audit_ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP INVISIBLE,
    bonus DECIMAL(10,2) DEFAULT 0.00 INVISIBLE
);

INSERT INTO employees_v2 (id, name, salary, department_id)
SELECT id, name, salary, department_id FROM employees; -- 迁移数据

ALTER TABLE employees_v2 MODIFY COLUMN audit_ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP INVISIBLE;
ALTER TABLE employees_v2 MODIFY COLUMN bonus DECIMAL(10,2) DEFAULT 0.00 INVISIBLE;

6.3 查询示例

SELECT * FROM employees;  -- 返回 id, name, salary, department_id
SELECT * FROM employees_v2; -- 返回 id, name, salary, department_id

SELECT id, name, salary, department_id, audit_ts, bonus FROM employees_v2; -- 显式指定Invisible Columns
SELECT id, name, salary, department_id, audit_ts FROM employees_v2; -- 显式指定部分Invisible Columns

6.4 修改Invisible Column为Visible Column

ALTER TABLE employees_v2 MODIFY COLUMN audit_ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP VISIBLE;

SELECT * FROM employees_v2; -- 现在返回 id, name, salary, department_id, audit_ts

7. Invisible Columns的性能影响

Invisible Columns本身对性能的影响可以忽略不计。它们主要影响的是查询的执行计划和数据传输量。

查询优化器: 优化器会根据列的可见性来选择最佳的执行计划。如果查询没有显式指定Invisible Columns，优化器可能会选择更高效的执行计划，因为需要处理的数据量减少了。
数据传输: SELECT * 查询不会返回Invisible Columns，因此可以减少数据传输量，提高查询效率。
索引: 为Invisible Columns创建索引可以提高特定查询的性能，但需要根据实际情况进行评估，避免过度索引。

8. 与其他MySQL特性的交互

Invisible Columns可以与其他MySQL特性良好地配合使用，例如：

Generated Columns: 如前所述，可以创建Generated Columns，并将它们设置为Invisible。
JSON Columns: 可以将JSON Columns设置为Invisible，用于存储一些元数据或配置信息。
分区表: Invisible Columns可以用于分区表。
审计日志: 可以将审计日志相关的列设置为Invisible，只允许特定的用户或角色访问。

特性	与 Invisible Columns 的交互
Generated Columns	可以创建 Generated Columns，并将它们设置为 Invisible。例如： `ALTER TABLE employees ADD COLUMN full_name VARCHAR(255) GENERATED ALWAYS AS (CONCAT(name, ' ', 'Employee')) INVISIBLE;`
JSON Columns	可以将 JSON Columns 设置为 Invisible，用于存储一些元数据或配置信息。例如： `ALTER TABLE employees ADD COLUMN metadata JSON INVISIBLE;`
分区表	Invisible Columns 可以用于分区表。在创建分区表时，可以定义一些 Invisible Columns，用于存储分区相关的信息，例如分区时间戳。
审计日志	可以将审计日志相关的列设置为 Invisible，只允许特定的用户或角色访问。例如，可以创建一个 `audit_log` 表，其中包含 `user_id`、`timestamp`、`operation` 等列，并将这些列设置为 Invisible，只允许管理员或审计人员访问。

9. 总结： Invisible Columns是数据库设计和维护的有力工具

Invisible Columns 是 MySQL 提供的一项非常实用的特性，它允许我们在不破坏现有应用程序兼容性的前提下，灵活地修改表结构。通过合理利用 Invisible Columns，可以简化数据库升级和迁移过程，提高数据安全性，优化查询性能。掌握 Invisible Columns 的概念、语法和应用场景，对于数据库管理员和开发人员来说都是一项重要的技能。

MySQL Invisible Columns：DDL操作与应用兼容性的利器

发表回复 取消回复

发表回复取消回复