討論MySQL 8.0中的幾個隱藏的特性

隱藏列

8.0.23 新增隱藏列特性。什么是隱藏列？它基本上是一個表的常規列，具有自己的名稱和數據類型。

它像任何其他常規列一樣處理和更新，唯一的區別是對應用程序不可見。

換句話說，只有在 SELECT 語句中明確搜索它時，才能訪問它；否則，它就像一個不存在的列。

這個定義看起來很奇怪，但如果提供一個這個特性的真實使用案例，一切都應該更清晰。

假設您的應用程序代碼中有SELECT *查詢。作為經驗豐富的數據庫開發人員，您應該知道這種查詢不應存在于任何生產代碼中。

典型的問題是，當您需要更改表架構，添加或刪除列，或者更糟的是在其他列中間添加新列時。

抓取到你應用程序變量中的字段位置可能會完全打破應用程序或觸發意外的錯誤行為。

這就是您需要避免在應用程序中使用SELECT *的原因。

在這種情況下，如果您需要避免更改應用程序代碼以匹配新表架構，可以將新列添加為隱藏列，它不會返回給客戶端，因為您的查詢沒有明確搜索它。

所以，您的應用程序不會失敗或出現奇怪的行為。

而這，就是隱藏列的用武之地。

您需要在列定義中使用INVISIBLE關鍵字。

當您需要將列設置為可見時，需要使用VISIBLE關鍵字。

讓我們看一個例子。 我們創建一個表并插入一些行：

mysql> CREATE TABLE articles (
  id INT UNSIGNED AUTO_INCREMENT,
  ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  article TEXT,
  PRIMARY KEY(id)
);
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO articles(article) VALUES
  ("This is first article"),
  ("This is second article"),
  ("This is third article");
Query OK, 3 rows affected (0.01 sec)  
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM articles;
+----+---------------------------+------------------------------+
| id | ts         | article        |
+----+---------------------------+------------------------------+
| 1 | 2023-07-28 1303 | This is first article |
| 2 | 2023-07-28 1303 | This is second article |
| 3 | 2023-07-28 1303 | This is third article |
+----+---------------------------+------------------------------+

有時，我們決定必須在ts列之后向表中添加一個新的字段title。

為了避免我們的應用程序因SELECT *和新添加的中間列等情況失效，我們必須將title列創建為INVISIBLE。

mysql> ALTER TABLE articles ADD COLUMN title VARCHAR(200) INVISIBLE AFTER ts;
Query OK, 0 rows affected (0.06 sec)  
Records: 0  Duplicates: 0  Warnings: 0

為新列提供一些值：

mysql> UPDATE articles SET title='Title 1' WHERE id=1;
UPDATE articles SET title='Title 2' WHERE id=2; 
UPDATE articles SET title='Title 3' WHERE id=3;

現在看看表架構：

CREATE TABLE `articles` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `ts` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `title` varchar(200) DEFAULT NULL /*!80023 INVISIBLE */,
  `article` text,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

你可以看到，該列被正確地標記了INVISIBLE關鍵字。 再試一次SELECT *：

mysql> SELECT * FROM articles;
+----+---------------------------+------------------------------+
| id | ts         | article        |
+----+---------------------------+------------------------------+
| 1 | 2023-07-28 1303 | This is first article |
| 2 | 2023-07-28 1303 | This is second article |
| 3 | 2023-07-28 1303 | This is third article |
+----+---------------------------+------------------------------+

你看，該列沒有返回。這允許schema改變后查詢不會失敗。

如果你想看title，你必須明確尋址該字段：

mysql> SELECT id, ts, title, article FROM articles;
+----+---------------------------+-----------+------------------------------+
| id | ts                  | title   | article                |
+----+---------------------------+-----------+------------------------------+
|  1 | 2023-07-28 1303 | Title 1 | This is first article  |
|  2 | 2023-07-28 13:15:03 | Title 2 | This is second article |
|  3 | 2023-07-28 1303 | Title 3 | This is third article  |
+----+---------------------------+-----------+------------------------------+

使用以下 DDL 將列設置為可見：

mysql> ALTER TABLE articles MODIFY COLUMN title varchar(200) VISIBLE;

記住，隱藏列像任何其他常規列一樣處理，所以您可以隨時讀取和更新它們。

關于隱形性的元數據在information_schema中可用，INVISIBLE/VISIBLE關鍵字在 binlog 中保留，以便正確復制所有更改。

生成的隱藏主鍵

這個特性在 MySQL 8.0.30 開始提供。生成的隱藏主鍵（GIPK）是一種特殊的隱藏列，僅適用于 InnoDB 表。

沒有主鍵的情況下創建 InnoDB 表，往往不是一個好的選擇。

我們強烈建議您的表中始終創建顯式主鍵。您可能還知道，如果您不提供主鍵，InnoDB 會創建一個隱藏的主鍵，但是 GIPK 提供的新特性使主鍵可以變得可用和最后可見。

相反，隱含創建的早期隱藏主鍵既不能成為可用的也不能成為可見的。

該功能對于強制缺乏經驗的用戶的 InnoDB 表都具有顯式主鍵很有用，即使是隱藏的。

讓我們看看它是如何工作的。

默認情況下，此功能被禁用，因此 MySQL 將繼續像過去一樣運行。

要啟用 GIPK，您必須設置以下動態系統變量（它具有全局和會話作用域）：

mysql> SET [PERSIST] sql_generate_invisible_primary_key=ON; 

現在在不指定顯式主鍵的情況下創建一個表：

mysql> CREATE TABLE customer(name VARCHAR(50));
Query OK, 0 rows affected (0.03 sec)

檢查模式：

mysql> SHOW CREATE TABLE customerG
*************************** 1. row ***************************
  Table: customer  
Create Table: CREATE TABLE `customer` (
  `my_row_id` bigint unsigned NOT NULL AUTO_INCREMENT /*!80023 INVISIBLE */,
  `name` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`my_row_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

名為my_row_id的隱藏主鍵已經自動創建。

注意：

GIPK 的名稱始終為my_row_id。您不能在表中有相同名稱的列。

GIPK 的數據類型始終為使用 AUTO_INCREMENT 的 BIGINT UNSIGNED。

有趣的是，您可以在查詢中使用主鍵并在明確尋址時看到它，就像描述的隱藏列一樣。

mysql> INSERT INTO customer VALUES('Tim'),('Rob'),('Bob');
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0

mysql> SELECT my_row_id, name FROM customer;
+--------------+-------+
| my_row_id | name |
+--------------+-------+
|         1 |  Tim |
|         2 |  Rob |
|         3 |  Bob |
+--------------+-------+
3 rows in set (0.00 sec)

mysql> SELECT my_row_id, name FROM customer WHERE my_row_id=2;
+--------------+-------+
| my_row_id | name |
+--------------+-------+
|         2 |  Rob |
+--------------+-------+
1 row in set (0.00 sec)

很顯然。如果您執行SELECT *，主鍵不會被返回：

mysql> SELECT * FROM customer WHERE my_row_id=2;
+-------+
| name |
+-------+
| Rob  |
+-------+

在某些時候，您最終可以決定使其可見，并在需要時更改名稱:

mysql> ALTER TABLE customer MODIFY `my_row_id` bigint unsigned not null auto_increment VISIBLE;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW CREATE TABLE customerG  
*************************** 1. row ***************************
  Table: customer
Create Table: CREATE TABLE `customer` (
  `my_row_id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`my_row_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

隱藏索引

為了完成隱形事物的概述，我們也來討論一下隱藏索引。這是最古老的隱形特性，在 MySQL 8.0 的第一個版本中就引入了。

您可以使索引對優化器不可見，以便測試如果該索引不存在，查詢的性能會如何。

不過，當索引不可見時，在針對表執行任何 DML 語句（INSERT、UPDATE、DELETE、REPLACE）時，它仍會得到更新。

您可以使用以下語句將索引設置為不可見和再次可見：

ALTER TABLE mytable ALTER INDEX my_idx INVISIBLE;
ALTER TABLE mytable ALTER INDEX my_idx VISIBLE;

隱藏索引可以測試在不考慮它的情況下查詢的執行計劃。最大的優點是您不需要刪除索引。請記住，索引刪除幾乎是瞬間完成的，但重建索引則不然。

根據表的大小，重建索引可能需要大量時間并過載服務器。另一種選擇是，您也可以使用IGNORE INDEX()索引提示，但在這種情況下，您可能會被迫在應用程序代碼中的許多查詢上添加索引提示。

將索引設置為不可見將允許您在很短的時間內開始測試查詢。并且您可以隨時輕松地將其設置回可見，而不會丟失任何更新。

注意：

主鍵（PRIMARY Key）不能隱藏

UNIQUE 索引可以隱藏，但仍會執行唯一性檢查

有關索引不可見性的信息在information_schema中可用

索引不可見性會被正確復制

總結

從我的角度來看，你不應該使用隱藏列，因為最佳實踐是不應在任何應用中部署SELECT *查詢。不過，在某些緊急情況下，此功能可能非常有用，可以飛快地解決問題。

但是之后要記住修復你的代碼并將隱藏列設置為可見會更好。 對 GIPK 來說，情況也差不多。只要記住為表提供顯式主鍵，就不需要此功能。

不過，它可以幫助一個創建時沒有主鍵的表擁有一個適當的主鍵，這個主鍵可以方便地被使用和變得可見。

關于隱藏索引，這是一個非常簡單的功能，在測試時非常有用，特別是在可能使用多個索引，和不確定優化器是否選擇了最佳執行計劃的情況中。

審核編輯：劉清