JSON欄位類型在目前的版本中本身沒有索引,那麼在生產上是非常可怕的,JSON欄位的增、刪、改、查效率可想而知,基本沒法用,也許是基於此,MySQL5. 7中提供了Generated欄位類型,網路上有叫生成欄位或是計算列的。這裡先來了解一下什麼是Generated Column。
1、Generated Column介紹
Generated Column是MySQL 5.7.6引進的新特性,所謂Cenerated Column,就是資料庫中這一列由其他欄位計算而得。引用官方參考手冊中的例子予以說明:
CREATE TABLE triangle ( sidea DOUBLE, sideb DOUBLE, sidec DOUBLE AS (SQRT(sidea * sidea + sideb * sideb)) ); INSERT INTO triangle (sidea, sideb) VALUES(1,1),(3,4),(6,8); mysql> SELECT * FROM triangle; +-------+-------+--------------------+ | sidea | sideb | sidec | +-------+-------+--------------------+ | 1 | 1 | 1.4142135623730951 | | 3 | 4 | 5 | | 6 | 8 | 10 | +-------+-------+--------------------+
Generated Column有兩種,即Virtual Generated Column和Stored Generated Column,前者只將Generated Column保存在資料字典中(表的元資料),並不會將這一列資料持久化到磁碟上;後者會將Generated Column持久化到磁碟上,而不是每次讀取的時候計算所得。很明顯,後者存放了可以透過已有數據計算而得到的數據,需要更多的磁碟空間,與Virtual Column相比並沒有優勢,因此,MySQL 5.7中,不指定Generated Column的類型,預設是Virtual Column。雖然一般情況下都應該使用Virtal Generated Column,但是,目前使用Virtual Generated Column還有很多限制:不能用作主鍵、不能作為主鍵、不能創建全文索引和空間索引等,但是在後續的版本中可能支持,所以如果使用Generated Column欄位做索引的話,還是使用Stored Generated Column吧,在使用Generated Column做索引上,JSON欄位索引的解決方案,官方也是推薦使用Stored Generated Column。使用Stored Generated Column建表語句如下,只是在加了個單字:
CREATE TABLE triangle ( sidea DOUBLE, sideb DOUBLE, sidec DOUBLE AS (SQRT(sidea * sidea + sideb * sideb) STORED) );
2、Generated Column注意事項
Generated Column是不能進行寫操作的,它是自動產生的;在創建的時候要考慮這個列的計算公式是否合理,不合理的話創建的時候不會報錯,使用時插入值就會報錯;Generated Column依賴的列在刪除的時候會提示報錯,必須先刪除Generated Column才能再刪除它所依賴的列;Generated Column定義不合法,如我們將generated column定義為 "x列+y列",很明顯,x列或y列都是數值型,如果我們將x列或y列定義(或修改)為字符型,則預期會報錯,但實際上我們可以正常創建,但是在插入的時候是會報錯的。
3、利用Generated Column為JSON字段添加索引
正常情況下,JSON字段的相關查詢是掃描全表的,因為JSON字段本身不能創建索引的,我們利用Generated Column特性,對JSON中相關key作為Generated Column來做生成列,然後對Generated Column做索引:
ALTER TABLE json_test ADD COLUMN age INT AS (JSON_EXTRACT(user_info,'$.age')) STORED, ADD KEY idx_age (age);
前後對比圖如下:
查詢可以很明顯的看出,使用Generated Columnn% JSON欄位中的值使用索引。
結束語
MySQL5.7中Generated Column和JSON Column的出現,使一些場景替代MongoDB等NoSQL提供了可能,雖然整體上還沒有MongoDB等做的那麼強大,但相信以後使用這兩種類型的場景會越來越多, 同時對DBA的挑戰也越來越大,希望密集使用JSON類型業務使用獨立的MySQL實例來運行,以免JSON成為大字段(存儲在JSON文檔的大小JSON 列被限制為值 max_allowed_packet的系統變數)時會對其他業務帶來影響。
以上就是MySQL 5.7新特性| Json Column和Generated Column(下)的內容,更多相關內容請關注PHP中文網(www.php.cn)!