MySQL虛擬列

JaxYoun發表於2024-03-19
MySql

當我們談論資料庫最佳化時，經常會遇到各種技術和策略。其中，MySQL的虛擬列（也被稱為生成列或儲存列）是一個引人注目的特性。它不僅可以幫助開發者提高查詢效率，還能為資料表提供額外的計算功能，而無需真正改變表的結構。在這篇文章中，我們將深入探討MySQL虛擬列的發展、原理以及應用。

一、虛擬列的發展

在早期的MySQL版本中，開發者通常需要為經常需要計算的欄位建立額外的物理列，並在資料插入或更新時手動計算這些列的值。這種方法雖然可行，但它增加了資料冗餘和應用程式的複雜性。

為了解決這個問題，MySQL 5.7版本引入了虛擬列（也稱為生成列）的概念。虛擬列允許開發者在表中定義一個基於其他列的計算公式，而不需要實際儲存這些計算的結果。當查詢虛擬列時，MySQL會根據公式動態計算其值。

在後續的版本中，MySQL進一步增強了虛擬列的功能，允許開發者選擇是否將虛擬列的結果實際儲存在磁碟上（即儲存列），以提高查詢效能。

二、虛擬列的原理

虛擬列的工作原理相對簡單。當你在表中定義一個虛擬列時，你需要為其提供一個表示式，該表示式基於表中的其他列。每當查詢虛擬列時，MySQL都會根據該表示式動態計算其值。

虛擬列的一個重要特點是它們不佔用實際的磁碟空間（除非你選擇將它們定義為儲存列）。這意味著你可以在不增加儲存開銷的情況下為表新增額外的計算功能。MySQL 5.7 並且支援兩種型別的生成列：

2.1 虛擬生成列（Virtual Generated Column）

虛擬生成列的值是在查詢時動態計算的，不會佔用額外的磁碟空間來儲存這些值。
它們的值是根據列定義中的表示式計算得出的，該表示式可以引用同一表中的其他列。
由於值是動態計算的，因此每次查詢虛擬生成列時，MySQL 都會根據相應的表示式重新計算其值。
虛擬生成列可以用於 SELECT 查詢的 WHERE 子句、ORDER BY 子句和 GROUP BY 子句等，以提供基於計算的查詢條件或排序。

2.2 儲存生成列（Stored Generated Column）

儲存生成列的值是在資料插入或更新時計算的，並且計算結果會實際儲存在磁碟上。
與虛擬生成列不同，儲存生成列佔用了額外的磁碟空間來儲存它們的值。
由於值是預先計算並儲存的，因此在查詢儲存生成列時，MySQL 可以直接讀取儲存的值，而不需要重新計算。
儲存生成列可以用於建立索引，以進一步提高查詢效能。索引可以基於儲存生成列的值進行快速查詢和排序。

三、虛擬列的用法

當你定義一個虛擬列時，你需要使用GENERATED ALWAYS AS語句來指定該列的值是如何從其他列計算得出的。基本的語法如下：

column_name data_type
 [GENERATED ALWAYS] AS (expression) [VIRTUAL | STORED]
  • column_name：虛擬列的名稱。
  • data_type：虛擬列的資料型別，它必須與你所使用的表示式返回的資料型別相容。
  • GENERATED ALWAYS：指示該列的值總是由給定的表示式生成。- ALWAYS關鍵字是可選的，因為預設情況下生成列就是ALWAYS生成的。
  • AS (expression)：指定如何計算虛擬列值的表示式。這個表示式可以引用表中的其他列。
  • VIRTUAL 或 STORED：指定生成列的型別。VIRTUAL表示該列的值在查詢時動態計算，而STORED表示該列的值在資料插入或更新時計算並儲存。如果你省略這部分，MySQL 5.7及更早版本會預設使用VIRTUAL，而在MySQL 8.0及更高版本中，你需要明確指定VIRTUAL或STORED。

我們建立一個表，其中包含一個JSON列和一個基於JSON列中某個值的虛擬列。然後，我們為這個虛擬列建立索引以提高查詢效能。
首先，我們建立一個包含JSON列和虛擬列的表：

CREATE TABLE users (  
  id INT AUTO_INCREMENT PRIMARY KEY,  
  profile JSON,  
  full_name VARCHAR(255) GENERATED ALWAYS AS (  
    CONCAT(  
      JSON_UNQUOTE(JSON_EXTRACT(profile, '$.first_name')), ' ',  
      JSON_UNQUOTE(JSON_EXTRACT(profile, '$.last_name'))  
    )  
  ) VIRTUAL  
);

users 表有一個 profile 列，它的資料型別是 JSON。此外，我們還有一個 full_name 虛擬列，它是透過拼接 profile 列中 first_name 和 last_name 鍵對應的值生成的。我們使用 JSON_EXTRACT 函式從 profile 列中提取值，並使用 JSON_UNQUOTE 函式將提取出的JSON字串轉換為普通字串。
接下來，我們為 full_name 虛擬列建立一個索引：

CREATE INDEX idx_full_name ON users(full_name);

-- 插入資料  
INSERT INTO users (profile) VALUES  
('{"first_name": "John", "last_name": "Doe"}'),  
('{"first_name": "Jane", "last_name": "Smith"}');  

-- 查詢資料  
SELECT * FROM users WHERE full_name = 'John Doe';

由於我們已經為 full_name 列建立了索引，因此上述查詢應該會更快，尤其是當表中有大量資料時。

請注意：由於 full_name 是一個虛擬列，你不能直接更新它的值。如果你需要改變 full_name 的值，你必須更新 profile 列中相應的 first_name 或 last_name 值。

四、虛擬列的使用條件和限制

4.1 使用條件

  • 確定性：生成列的表示式必須是確定性的。這意味著給定相同的輸入，表示式必須總是產生相同的結果。例如，使用NOW()函式的表示式就不是確定性的，因為它返回當前的日期和時間。

  • 引用其他列：生成列的表示式可以引用表中的其他列，但這些列必須定義在生成列之前。

  • 資料型別相容性：生成列的資料型別必須與表示式的結果相容。例如，如果你將兩個整數相加，生成列的資料型別應該是整數型別。

  • 索引限制：只有STORED生成列可以被索引。VIRTUAL生成列在MySQL 5.7及更早版本中不能被索引，但在MySQL 8.0及更高版本中，這個限制已經被放寬，允許對VIRTUAL生成列建立索引。

4.2 限制條件

  • 效能考慮：對於VIRTUAL生成列，每次查詢時都需要計算表示式，這可能會影響效能，尤其是當表示式複雜或資料量大時。對於STORED生成列，雖然計算只在資料插入或更新時發生，但它們佔用了額外的儲存空間。

  • 儲存引擎限制：並非所有的MySQL儲存引擎都支援生成列。例如，在MySQL 5.7中，只有InnoDB、MyISAM和MEMORY儲存引擎支援生成列。

  • 表示式限制：生成列的表示式有一些限制。例如，它們不能引用其他表中的列，不能包含子查詢，不能引用非確定性的函式（如RAND()或NOW()），除非這些函式被用作常量值。

  • 修改限制：一旦建立了生成列，就不能直接修改它的值。因為它是根據其他列的值自動生成的。如果你需要改變生成列的值，你必須修改它所依賴的列的值。

  • 複製和二進位制日誌：如果你的MySQL伺服器配置了複製或使用了二進位制日誌，那麼對生成列的更改（透過修改它所依賴的列）也會被記錄並複製到其他伺服器上。

  • 備份和恢復：在備份和恢復資料庫時，需要確保備份工具能夠正確處理生成列。一些較舊的備份工具可能不支援生成列。

  • 升級考慮：如果你的資料庫是從較舊的MySQL版本升級而來的，需要確保升級過程正確處理了生成列。在升級之前，最好先在測試環境中驗證生成列的行為和效能。

五、虛擬列的應用場景

虛擬列在許多場景中都非常有用。以下是一些常見的應用示例：

  • 複雜計算與表示式：當查詢中需要頻繁進行復雜計算，而這些計算又是基於表中其他欄位的時候，虛擬列特別有用。透過將這些計算定義為虛擬列，MySQL可以預先或在查詢時計算這些值，從而避免在每次查詢時都重複相同的計算。

  • 用作索引：虛擬列可以被索引，這對於提高查詢效能非常關鍵。特別是在處理大型資料集時，如果查詢條件涉及到計算密集型操作，將這些操作的結果儲存為虛擬列併為其建立索引可以大大加快查詢速度。

  • 資料轉換與格式化：如果查詢中經常需要將資料從一種格式轉換為另一種格式（例如日期時間格式、貨幣格式等），將這些轉換定義為虛擬列可以減少每次查詢時的轉換開銷。

  • 統一查詢邏輯：在複雜的應用程式中，可能會有多個查詢需要執行相同的計算或轉換。透過使用虛擬列，可以將這些計算或轉換的邏輯封裝在表結構中，使得查詢更加簡潔且易於維護。

  • 避免使用檢視：在某些情況下，開發者可能會使用檢視來封裝複雜的查詢邏輯。然而，檢視在某些情況下可能不如虛擬列高效。虛擬列允許資料庫在物理表層面進行最佳化，而檢視則可能需要在每次查詢時動態生成結果集。

  • 減少IO操作：當使用儲存虛擬列時（即結果實際儲存在磁碟上），由於資料已經預先計算並儲存，因此可以減少查詢時的IO操作，從而提高查詢效能。

  • 與JSON等非標準欄位的互動：對於儲存了JSON或其他非標準格式資料的欄位，直接在這些欄位上進行查詢可能會非常低效。透過將JSON欄位中的值提取為虛擬列，併為其建立索引，可以顯著提高對這些資料的查詢效率。

總結

MySQL的虛擬列是一個強大而靈活的特性，它允許開發者在表中定義基於其他列的計算結果，而無需實際儲存這些計算的值。透過使用虛擬列，你可以避免資料冗餘，簡化查詢，最佳化索引，執行資料驗證以及自動轉換資料格式。

隨著MySQL的不斷髮展，我們可以期待虛擬列在未來版本中繼續得到增強和最佳化，為開發者提供更多便利和功能。在設計和最佳化資料庫時，不要忘記考慮使用虛擬列來提高效能和簡化應用程式邏輯。

原文連結：https://blog.csdn.net/qq_26664043/article/details/136047875

相關文章