優(yōu)化體系--詳解MySQL 5.7虛擬列，值得收藏

我們都知道where條件如果在字段上帶了函數(shù)就不會(huì)去走索引，不好優(yōu)化，無意間了解到mysql一個(gè)新特性--虛擬列，專門處理這塊問題的，下面一起來了解下吧~

創(chuàng)新互聯(lián)建站-專業(yè)網(wǎng)站定制、快速模板網(wǎng)站建設(shè)、高性價(jià)比南湖網(wǎng)站開發(fā)、企業(yè)建站全套包干低至880元,成熟完善的模板庫(kù),直接使用。一站式南湖網(wǎng)站制作公司更省心,省錢,快速模板網(wǎng)站建設(shè)找我們，業(yè)務(wù)覆蓋南湖地區(qū)。費(fèi)用合理售后完善，十余年實(shí)體公司更值得信賴。

在MySQL 5.7中，支持兩種Generated Column，即 Virtual Generated Column和Stored Generated Column ，前者只將Generated Column保存在數(shù)據(jù)字典中（表的元數(shù)據(jù)），并不會(huì)將這一列數(shù)據(jù)持久化到磁盤上；后者會(huì)將Generated Column持久化到磁盤上，而不是每次讀取的時(shí)候計(jì)算所得。很明顯，后者存放了可以通過已有數(shù)據(jù)計(jì)算而得的數(shù)據(jù)，需要更多的磁盤空間，與Virtual Column相比并沒有優(yōu)勢(shì)，因此，MySQL 5.7中，不指定Generated Column的類型，默認(rèn)是Virtual Column。

如果需要Stored Generated Golumn的話，可能在Virtual Generated Column上建立索引更加合適。綜上，一般情況下，都使用Virtual Generated Column，這也是MySQL默認(rèn)的方式

假設(shè)有一個(gè)表，其中包含一個(gè) date 類型的列 `SimpleDate` date

SimpleDate 是一個(gè)常用的查詢字段，并需要對(duì)其執(zhí)行日期函數(shù)，例如

此時(shí)的問題是 即使對(duì) SimpleDate 建立索引，這個(gè)查詢語句也無法使用，因?yàn)槿掌诤瘮?shù)阻止了索引。

為了提高查詢效率，通常要進(jìn)行額外的操作，例如新建一個(gè)字段 SimpleDate_dayofweek，存放 dayofweek(SimpleDate) 的計(jì)算結(jié)果，然后對(duì)這列創(chuàng)建索引，SimpleDate_dayofweek 的值需要程序?qū)懭?，例如使用觸發(fā)器，在 SimpleDate 有變動(dòng)時(shí)更新這樣查詢就可以改為

這么做的好處是提高了查詢性能，可以使用 SimpleDate_dayofweek 列的索引了，但又帶來了其他麻煩，例如

虛擬列 Generated Columns 就是用來解決這個(gè)問題的，可以增加一個(gè)可被索引的列，但實(shí)際上并不存在于數(shù)據(jù)表中，下面用一個(gè)實(shí)驗(yàn)來說明下：

需求：為了實(shí)現(xiàn)對(duì)json數(shù)據(jù)中部分?jǐn)?shù)據(jù)的索引查詢，考慮用MySQL5.7中的虛擬列功能

1、創(chuàng)建表

2、準(zhǔn)備數(shù)據(jù)

3、構(gòu)建姓名的虛擬列

4、構(gòu)建索引

5、測(cè)試是否用到索引

可以看出用了索引了

6、插入新數(shù)據(jù)

此時(shí)的表的結(jié)構(gòu)由于多出了user_name這一虛擬列，再插入別的數(shù)據(jù)要注意在表后指明插入列（不能給虛擬列插入數(shù)據(jù)）

做完發(fā)現(xiàn)這個(gè)實(shí)驗(yàn)好像不是那么好理解...應(yīng)該對(duì)比一下加不加虛擬列有沒走索引，可能會(huì)更容易讓大家理解的...后面會(huì)分享更多devops和DBA方面的內(nèi)容，感興趣的朋友可以關(guān)注一下~

mysql 自動(dòng)增長(zhǎng)虛擬列

對(duì)表table_xyz查詢結(jié)果排序：（查詢結(jié)果集中的字段名為field1）

SELECT (

SELECT count(*) as dd FROM table_xyz as a where a.field1b.field1) AS `序號(hào)`, b.field1

FROM table_xyz AS b;

MySQL數(shù)據(jù)庫(kù)必會(huì)技能，虛擬列的用法

對(duì)于想要將自動(dòng)生成的數(shù)據(jù)添加到表中的任何人來說， MySQL 虛擬列 是一個(gè)強(qiáng)大、易于使用和高級(jí)的功能。

INSERT 生成的列允許您在不使用and UPDATE 子句的情況下將自動(dòng)生成的數(shù)據(jù)存儲(chǔ)在表中。 這個(gè)有用的特性自 5.7 版 起就已成為 MySQL 的一部分，它代表了在生成數(shù)據(jù)時(shí)觸發(fā)器的另一種方法。此外，生成的列可以幫助您更輕松、更高效地查詢。

虛擬列 列類似于普通列，但您不能手動(dòng)更改其值。這是因?yàn)楸磉_(dá)式定義了如何根據(jù)從同一行的其他列中讀取的其他值來生成生成列的值。因此，生成的列在表的域內(nèi)工作，其定義不能涉及 JOIN 語句。

換句話說，您可以將生成的列視為一種視圖，但僅限于列。請(qǐng)注意，生成的列與 SQL 觸發(fā)器 不同，您只能在使用 CREATE TABLE or語句時(shí)定義它們，語法如下： ALTER TABLE

該 AS (generated_column_expression) 子句指定要添加或更新到表中的列是生成的列。定義 MySQL 將用于計(jì)算列值的 generation_expression 表達(dá)式，它不能引用另一個(gè)生成的列或除當(dāng)前表的列之外的任何內(nèi)容。另外，請(qǐng)注意生成表達(dá)式只能涉及不可變函數(shù)。例如，您不能在生成的列表達(dá)式定義中使用返回當(dāng)前日期的函數(shù)，因?yàn)樗且粋€(gè)可變函數(shù)。

您還可以在關(guān)鍵字前面 AS 加上 GENERATED ALWAYS 關(guān)鍵字以使生成的列的性質(zhì)更加明確，但這是可選的。然后，您可以指示生成列的類型是 VIRTUAL 還是 STORED 。您將在下面的章節(jié)中了解這兩種類型之間的區(qū)別。默認(rèn)情況下，如果沒有在查詢中明確指定，MySQL 會(huì)將生成的列標(biāo)記為 VIRTUAL .

現(xiàn)在讓我們看看生成的列語法在 CREATE TABLE 查詢中的作用：

在此示例中，該 full_name 列將自動(dòng)存儲(chǔ) first_name 和 last_name 列的連接。

如前所述，您可以將生成的列定義為 VIRTUAL 或 STORED。現(xiàn)在讓我們仔細(xì)看看這兩種類型。

MySQL 不存儲(chǔ)標(biāo)記為 VIRTUAL 的 虛擬列 。這意味著 MySQL 在需要時(shí)動(dòng)態(tài)評(píng)估其值。 BEFORE 這通常在觸發(fā)任何查詢后立即發(fā)生。換句話說，虛擬生成的列不占用存儲(chǔ)空間。

MySQL 存儲(chǔ)任何生成的標(biāo)記為 STORED 的列。這意味著每次插入或更新行時(shí)，MySQL 都會(huì)評(píng)估其值并將其存儲(chǔ)在磁盤上。換句話說，存儲(chǔ)列需要存儲(chǔ)空間，就好像它是普通列一樣。

現(xiàn)在讓我們進(jìn)一步了解虛擬列和存儲(chǔ)生成列的優(yōu)缺點(diǎn)。

優(yōu)點(diǎn)

缺點(diǎn)

優(yōu)點(diǎn)

缺點(diǎn)

采用生成的列有幾個(gè)原因，但以下三個(gè)是最重要的。

如您所見，您可以通過將四列與以下生成的列聚合來輕松生成此數(shù)據(jù)字段：

這將產(chǎn)生：

在這種情況下，生成的列使您能夠直接在數(shù)據(jù)庫(kù)級(jí)別標(biāo)準(zhǔn)化數(shù)據(jù)字段格式。此外，存儲(chǔ)生成的列避免了每次需要時(shí)都構(gòu)造此字段的不可避免的開銷。

通常，您使用網(wǎng)站 URL 中的資源 ID 或REST API來檢索您需要的數(shù)據(jù)。但是公開暴露您的 ID 可能會(huì)帶來安全問題。當(dāng)您發(fā)現(xiàn)自己使用自動(dòng)增量 ID 時(shí)尤其如此，這很容易預(yù)測(cè)并使抓取或機(jī)器人攻擊更容易。

為避免這種情況，您可以考慮通過使用自動(dòng)生成的、隨機(jī)的、更安全的公共 ID 來隱藏您的原始 ID。您可以通過對(duì)您的 ID 進(jìn)行散列處理，使用虛擬生成的列來實(shí)現(xiàn)這一點(diǎn)，如下所示：

請(qǐng)注意，為避免生成已知的哈希值，您可以將您的 ID 與特殊關(guān)鍵字連接起來。 在此處了解有關(guān) MySQL 加密和壓縮功能的更多信息。

過濾數(shù)據(jù)時(shí)，有些列比其他列更有用。此外，您通常必須更改存儲(chǔ)在列中的值的表示形式，以使過濾更簡(jiǎn)單或更直觀。您可以定義一個(gè)有用的生成列來存儲(chǔ)以所需格式執(zhí)行過濾所需的信息，而不是在每個(gè)過濾器查詢中執(zhí)行此操作。

例如，您可以定義一個(gè)生成的列，以便更輕松地找到籃球隊(duì)中的球員，如下所示：

這樣的列將產(chǎn)生：

如前所述，您只能在表中使用生成的列。此外，它們只能涉及不可變函數(shù)，并且MySQL 生成它們的值以響應(yīng) INSERT or UPDATE 查詢。另一方面，觸發(fā)器是 MySQL 自動(dòng)執(zhí)行的存儲(chǔ)程序，每當(dāng)與特定表關(guān)聯(lián)的 或 事件發(fā)生 INSERT 時(shí) UPDATE 。 DELETE 換句話說，觸發(fā)器可以涉及多個(gè)表和所有 MySQL 函數(shù)。與生成的列相比，這使它們成為更完整的解決方案。同時(shí)，MySQL 觸發(fā)器本質(zhì)上使用和定義更復(fù)雜，也比生成的列慢。