隱藏列

8.0.23 新增隱藏列特性。什么是隱藏列？它基本上是一個(gè)表的常規(guī)列，具有自己的名稱和數(shù)據(jù)類型。

它像任何其他常規(guī)列一樣處理和更新，唯一的區(qū)別是對應(yīng)用程序不可見。

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

這個(gè)定義看起來很奇怪，但如果提供一個(gè)這個(gè)特性的真實(shí)使用案例，一切都應(yīng)該更清晰。

假設(shè)您的應(yīng)用程序代碼中有SELECT *查詢。作為經(jīng)驗(yàn)豐富的數(shù)據(jù)庫開發(fā)人員，您應(yīng)該知道這種查詢不應(yīng)存在于任何生產(chǎn)代碼中。

典型的問題是，當(dāng)您需要更改表架構(gòu)，添加或刪除列，或者更糟的是在其他列中間添加新列時(shí)。

抓取到你應(yīng)用程序變量中的字段位置可能會(huì)完全打破應(yīng)用程序或觸發(fā)意外的錯(cuò)誤行為。

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

在這種情況下，如果您需要避免更改應(yīng)用程序代碼以匹配新表架構(gòu)，可以將新列添加為隱藏列，它不會(huì)返回給客戶端，因?yàn)槟牟樵儧]有明確搜索它。

所以，您的應(yīng)用程序不會(huì)失敗或出現(xiàn)奇怪的行為。

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

您需要在列定義中使用INVISIBLE關(guān)鍵字。

當(dāng)您需要將列設(shè)置為可見時(shí)，需要使用VISIBLE關(guān)鍵字。

讓我們看一個(gè)例子。 我們創(chuàng)建一個(gè)表并插入一些行：

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 |
+----+---------------------------+------------------------------+

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

為了避免我們的應(yīng)用程序因SELECT *和新添加的中間列等情況失效，我們必須將title列創(chuàng)建為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;

現(xiàn)在看看表架構(gòu)：

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

你可以看到，該列被正確地標(biāo)記了INVISIBLE關(guān)鍵字。 再試一次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改變后查詢不會(huì)失敗。

如果你想看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 將列設(shè)置為可見：

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

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

關(guān)于隱形性的元數(shù)據(jù)在information_schema中可用，INVISIBLE/VISIBLE關(guān)鍵字在 binlog 中保留，以便正確復(fù)制所有更改。

生成的隱藏主鍵

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

沒有主鍵的情況下創(chuàng)建 InnoDB 表，往往不是一個(gè)好的選擇。

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

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

該功能對于強(qiáng)制缺乏經(jīng)驗(yàn)的用戶的 InnoDB 表都具有顯式主鍵很有用，即使是隱藏的。

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

默認(rèn)情況下，此功能被禁用，因此 MySQL 將繼續(xù)像過去一樣運(yùn)行。

要啟用 GIPK，您必須設(shè)置以下動(dòng)態(tài)系統(tǒng)變量（它具有全局和會(huì)話作用域）：

mysql> SET [PERSIST] sql_generate_invisible_primary_key=ON; 

現(xiàn)在在不指定顯式主鍵的情況下創(chuàng)建一個(gè)表：

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的隱藏主鍵已經(jīng)自動(dòng)創(chuàng)建。

注意：

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

GIPK 的數(shù)據(jù)類型始終為使用 AUTO_INCREMENT 的 BIGINT UNSIGNED。

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

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)

很顯然。如果您執(zhí)行SELECT *，主鍵不會(huì)被返回：

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

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

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 的第一個(gè)版本中就引入了。

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

不過，當(dāng)索引不可見時(shí)，在針對表執(zhí)行任何 DML 語句（INSERT、UPDATE、DELETE、REPLACE）時(shí)，它仍會(huì)得到更新。

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

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

隱藏索引可以測試在不考慮它的情況下查詢的執(zhí)行計(jì)劃。最大的優(yōu)點(diǎn)是您不需要?jiǎng)h除索引。請記住，索引刪除幾乎是瞬間完成的，但重建索引則不然。

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

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

注意：

主鍵（PRIMARY Key）不能隱藏

UNIQUE 索引可以隱藏，但仍會(huì)執(zhí)行唯一性檢查

有關(guān)索引不可見性的信息在information_schema中可用

索引不可見性會(huì)被正確復(fù)制

總結(jié)

從我的角度來看，你不應(yīng)該使用隱藏列，因?yàn)樽罴褜?shí)踐是不應(yīng)在任何應(yīng)用中部署SELECT *查詢。不過，在某些緊急情況下，此功能可能非常有用，可以飛快地解決問題。

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

不過，它可以幫助一個(gè)創(chuàng)建時(shí)沒有主鍵的表擁有一個(gè)適當(dāng)?shù)闹麈I，這個(gè)主鍵可以方便地被使用和變得可見。

關(guān)于隱藏索引，這是一個(gè)非常簡單的功能，在測試時(shí)非常有用，特別是在可能使用多個(gè)索引，和不確定優(yōu)化器是否選擇了最佳執(zhí)行計(jì)劃的情況中。

審核編輯：劉清