柚子快報(bào)激活碼778899分享：Mysql大單表JSON優(yōu)化

http://yzkb.51969.com/

優(yōu)化方案

MySQL 8.0.32 中，有幾種方法可以優(yōu)化存儲(chǔ) JSON 字符串的數(shù)據(jù)表。以下是一些建議，可以幫助您減少存儲(chǔ)空間：

使用壓縮: MySQL 8.0 支持表級(jí)壓縮，可以通過(guò)修改表來(lái)啟用壓縮。

ALTER TABLE your_table ROW_FORMAT=COMPRESSED;

優(yōu)化 JSON 存儲(chǔ): JSON 數(shù)據(jù)通常會(huì)有一些冗余，您可以考慮對(duì) JSON 數(shù)據(jù)進(jìn)行預(yù)處理，刪除不必要的字段或縮短字段名。分區(qū)表: 分區(qū)表可以有助于更好地管理大表的數(shù)據(jù)，可以通過(guò) RANGE、LIST、HASH 或 KEY 分區(qū)。

ALTER TABLE your_table PARTITION BY RANGE (TO_DAYS(creation_date)) (

PARTITION p0 VALUES LESS THAN (TO_DAYS('2023-01-01')),

PARTITION p1 VALUES LESS THAN (TO_DAYS('2024-01-01')),

PARTITION p2 VALUES LESS THAN (TO_DAYS('2025-01-01')),

PARTITION p3 VALUES LESS THAN MAXVALUE

);

使用適當(dāng)?shù)臄?shù)據(jù)類型: 如果可能，盡量避免使用 JSON 存儲(chǔ)大數(shù)據(jù)量，可以使用結(jié)構(gòu)化的表來(lái)存儲(chǔ)數(shù)據(jù)，這樣更容易進(jìn)行優(yōu)化和壓縮。外部存儲(chǔ): 如果 JSON 數(shù)據(jù)非常大，考慮將其存儲(chǔ)在外部文件系統(tǒng)或?qū)ο蟠鎯?chǔ)中，如 Amazon S3，然后在表中只存儲(chǔ)文件路徑或索引。刪除重復(fù)數(shù)據(jù): 檢查是否有重復(fù)的數(shù)據(jù)，刪除重復(fù)數(shù)據(jù)可以節(jié)省大量存儲(chǔ)空間。數(shù)據(jù)庫(kù)清理: 定期清理和維護(hù)數(shù)據(jù)庫(kù)，如刪除無(wú)用的索引、歸檔舊數(shù)據(jù)等。使用 InnoDB 壓縮: InnoDB 支持透明的數(shù)據(jù)壓縮，可以啟用表或索引級(jí)的壓縮。

ALTER TABLE your_table ENGINE=InnoDB, ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;

調(diào)整 InnoDB 配置: 適當(dāng)?shù)卣{(diào)整 InnoDB 的配置參數(shù)，如 innodb_file_format, innodb_file_per_table 等，以獲得更好的性能和存儲(chǔ)效率。手動(dòng)壓縮 JSON 數(shù)據(jù): 在插入數(shù)據(jù)之前，可以使用壓縮算法（如 gzip）手動(dòng)壓縮 JSON 字符串，然后在查詢時(shí)解壓縮。

通過(guò)這些方法，您可以優(yōu)化存儲(chǔ) JSON 字符串的數(shù)據(jù)表，從而減少存儲(chǔ)空間。具體采用哪種方法可以根據(jù)您的實(shí)際情況和需求進(jìn)行選擇和組合。

使用 InnoDB 壓縮

InnoDB 引擎支持表和索引的壓縮，可以通過(guò) ROW_FORMAT=COMPRESSED 來(lái)啟用壓縮。壓縮可以顯著減少存儲(chǔ)空間，同時(shí)還能提高某些查詢的性能（尤其是讀取更多數(shù)據(jù)時(shí)）。

啟用壓縮

要啟用壓縮，可以在創(chuàng)建表時(shí)指定 ROW_FORMAT=COMPRESSED 和 KEY_BLOCK_SIZE。KEY_BLOCK_SIZE 指定壓縮塊的大小，通常可以設(shè)置為 1, 2, 4, 8, 或 16 KB。

CREATE TABLE your_table (

id INT PRIMARY KEY,

data JSON

) ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;

對(duì)于已有表，您可以通過(guò) ALTER TABLE 命令啟用壓縮：

ALTER TABLE your_table ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;

KEY_BLOCK_SIZE 是 InnoDB 表和索引壓縮時(shí)使用的一個(gè)參數(shù)，它指定壓縮塊的大小。該參數(shù)在啟用 InnoDB 表壓縮時(shí)非常重要，因?yàn)樗苯佑绊懙綌?shù)據(jù)的壓縮率和性能。

含義和使用

KEY_BLOCK_SIZE 參數(shù)：定義壓縮塊的大小，以千字節(jié)（KB）為單位。有效的值通常為 1, 2, 4, 8, 或 16 KB。壓縮塊的大小：指定的塊大小決定了數(shù)據(jù)在磁盤上的存儲(chǔ)方式。較小的塊大小通常會(huì)有更高的壓縮率，但可能會(huì)對(duì)性能產(chǎn)生負(fù)面影響，因?yàn)楦嗟膲K需要被管理和訪問(wèn)。較大的塊大小通常會(huì)有較好的性能，但壓縮率可能會(huì)較低。

選擇合適的塊大小

選擇 KEY_BLOCK_SIZE 時(shí)，可以考慮以下因素：

數(shù)據(jù)類型和大?。喝绻臄?shù)據(jù)比較小且重復(fù)性高，較小的塊大小可能會(huì)提供更高的壓縮率。對(duì)于較大的數(shù)據(jù)，較大的塊大小可能會(huì)更合適。性能需求：如果性能是關(guān)鍵考慮因素，較大的塊大小通常會(huì)更好，因?yàn)樗鼫p少了壓縮和解壓縮的開(kāi)銷。存儲(chǔ)空間：如果存儲(chǔ)空間有限且需要最大化壓縮率，較小的塊大小可能會(huì)更好。

示例配置的含義

使用 KEY_BLOCK_SIZE=8：

塊大小為 8 KB：指定每個(gè)壓縮塊的大小為 8 KB。壓縮效率和性能的平衡：8 KB 的塊大小通常在壓縮效率和性能之間提供一個(gè)良好的平衡。它通常適用于大多數(shù)應(yīng)用程序，但具體效果仍然需要根據(jù)實(shí)際數(shù)據(jù)和查詢模式進(jìn)行測(cè)試和調(diào)整。

總之，KEY_BLOCK_SIZE 是一個(gè)關(guān)鍵參數(shù)，用于調(diào)整 InnoDB 壓縮表的壓縮塊大小，從而影響表的存儲(chǔ)效率和性能。選擇合適的塊大小需要根據(jù)具體應(yīng)用場(chǎng)景和數(shù)據(jù)特性進(jìn)行權(quán)衡和測(cè)試。

調(diào)整 InnoDB 配置

適當(dāng)調(diào)整 InnoDB 配置參數(shù)可以提高性能和存儲(chǔ)效率。以下是一些重要的 InnoDB 配置參數(shù)及其含義：

innodb_file_format

這個(gè)參數(shù)指定 InnoDB 的文件格式。MySQL 8.0 默認(rèn)使用 Barracuda 文件格式，支持表壓縮和動(dòng)態(tài)行格式。

SET GLOBAL innodb_file_format = Barracuda;

在 MySQL 8.0 中，innodb_file_format 變量已被廢棄（deprecated），并且默認(rèn)的文件格式已經(jīng)固定為 Barracuda，因此執(zhí)行 SHOW VARIABLES LIKE ‘innodb_file_format’; 返回為空是預(yù)期行為。 在 MySQL 8.0 中，不再需要手動(dòng)設(shè)置 innodb_file_format，因?yàn)?Barracuda 文件格式是默認(rèn)的且唯一支持的格式。這也是為什么即使您嘗試查詢這個(gè)變量，返回的結(jié)果會(huì)是空的。 如果您想確認(rèn)當(dāng)前的表使用的是 Barracuda 文件格式，您可以通過(guò)以下命令查看表的行格式：

SHOW TABLE STATUS LIKE 'your_table_name';

在輸出中，Row_format 列會(huì)顯示 Compressed 或 Dynamic，這表示使用的是 Barracuda 文件格式。

innodb_file_per_table

這個(gè)參數(shù)決定 InnoDB 是否為每個(gè)表使用單獨(dú)的表空間文件。啟用這個(gè)選項(xiàng)后，每個(gè)表的數(shù)據(jù)和索引將存儲(chǔ)在獨(dú)立的 .ibd 文件中。這可以更容易管理表的壓縮和存儲(chǔ)。

SET GLOBAL innodb_file_per_table = ON;

innodb_page_size

這個(gè)參數(shù)指定 InnoDB 頁(yè)的大小，默認(rèn)是 16KB。較小的頁(yè)面大小可能有助于壓縮率，但會(huì)增加開(kāi)銷。一般情況下，保持默認(rèn)設(shè)置即可。

SET GLOBAL innodb_page_size = 16384; -- 16KB

innodb_log_file_size

這個(gè)參數(shù)指定 InnoDB 日志文件的大小。較大的日志文件可以減少寫入的頻率，改善性能，但也會(huì)增加恢復(fù)時(shí)間。

SET GLOBAL innodb_log_file_size = 512M; -- 512MB

innodb_buffer_pool_size

這個(gè)參數(shù)指定 InnoDB 緩沖池的大小。緩沖池用于緩存數(shù)據(jù)和索引，提高讀取性能。根據(jù)系統(tǒng)內(nèi)存大小進(jìn)行調(diào)整，通常設(shè)置為系統(tǒng)內(nèi)存的 70-80%。

SET GLOBAL innodb_buffer_pool_size = 8G; -- 8GB

配置示例

在 MySQL 配置文件 (my.cnf 或 my.ini) 中進(jìn)行這些設(shè)置：

[mysqld]

innodb_file_format = Barracuda

innodb_file_per_table = 1

innodb_page_size = 16384

innodb_log_file_size = 512M

innodb_buffer_pool_size = 8G

應(yīng)用這些配置

修改配置文件后，您需要重啟 MySQL 服務(wù)以使更改生效。

sudo systemctl restart mysql

通過(guò)以上調(diào)整和配置，您可以有效地減少存儲(chǔ)空間，并在某些情況下提高性能。確保在更改配置前備份數(shù)據(jù)，并逐步測(cè)試這些調(diào)整對(duì)系統(tǒng)的影響。

案例

版本：8.0.32 text類型字段，底層為JSON字符串 全量數(shù)據(jù): 35G -> 開(kāi)啟壓縮后: 26G 壓縮比=0.74，壓縮率=0.26

2010-01-01數(shù)據(jù): 18G -> 開(kāi)啟壓縮后: 8.9G 壓縮比=0.49，壓縮率=0.51

讀寫

未開(kāi)啟壓縮

Seconds % Task name

3441.68033 98% write task 0079.842993 02% read task

開(kāi)啟壓縮后

Seconds % Task name

3442.119693 85% write task 0627.699276 15% read task 開(kāi)啟壓縮+關(guān)閉binlog，性能沒(méi)有太大的變化，cpu負(fù)載整體有降低

Seconds % Task name

3442.033828 85% write task 0624.982888 15% read task

CPU

4核心設(shè)備 未開(kāi)啟壓縮：load avg 4.x 開(kāi)啟壓縮后：load avg 7.x cpu負(fù)載在優(yōu)化webClient線程池后（8個(gè)IO-worker），穩(wěn)定在3.x-4.x之間。并且任務(wù)完成時(shí)間沒(méi)有波動(dòng)，還是在3442.412387S左右。生產(chǎn)消息的隊(duì)列也沒(méi)有了滿隊(duì)告警日志"buffer full"。 也就是說(shuō)cpu負(fù)載加大主要是因?yàn)閣ebClient請(qǐng)求并行度過(guò)高導(dǎo)致的

小結(jié)

開(kāi)啟壓縮后，CPU負(fù)載顯著增高，寫入性能穩(wěn)定，讀取性能顯著降低，約增加了7倍。

多次實(shí)驗(yàn)驗(yàn)證在數(shù)據(jù)量達(dá)到350W+之后寫入性能也會(huì)下降，導(dǎo)致應(yīng)用程序操作并發(fā)受限，cpu使用率飆升，mybatis線程出現(xiàn)鎖競(jìng)爭(zhēng)，劣化嚴(yán)重導(dǎo)致應(yīng)用程序宕機(jī)，同樣配置環(huán)境，取消壓縮表之后表現(xiàn)良好。 非壓縮表在并發(fā)提升后，數(shù)據(jù)量達(dá)到300W左右時(shí)也會(huì)出現(xiàn)同樣的劣化效果 其根本原因在于壓縮數(shù)據(jù)的cpu成本

"web-client-consumer-7" #64 daemon prio=5 os_prio=31 cpu=108005.30ms elapsed=2343.21s tid=0x00007f8b55ba7600 nid=0xc203 waiting for monitor entry [0x000070000b6fa000]

java.lang.Thread.State: BLOCKED (on object monitor)

at org.apache.ibatis.ognl.OgnlRuntime.invokeMethod(OgnlRuntime.java:1151)

- waiting to lock <0x0000000703f319d8> (a java.lang.reflect.Method)

at org.apache.ibatis.ognl.OgnlRuntime.callAppropriateMethod(OgnlRuntime.java:1958)

"web-client-consumer-1" #50 daemon prio=5 os_prio=31 cpu=109368.51ms elapsed=2344.58s tid=0x00007f8b5fe31000 nid=0xb907 waiting for monitor entry [0x000070000aee2000]

java.lang.Thread.State: BLOCKED (on object monitor)

at org.apache.ibatis.ognl.OgnlRuntime.invokeMethod(OgnlRuntime.java:1151)

- locked <0x0000000703f319d8> (a java.lang.reflect.Method)

at org.apache.ibatis.ognl.OgnlRuntime.callAppropriateMethod(OgnlRuntime.java:1958)

數(shù)據(jù)存在本地環(huán)境的客觀因素，mysql與服務(wù)共用一臺(tái)設(shè)備等等，以及mysql配置合理性問(wèn)題，案例僅供參考，具體數(shù)據(jù)建議參考相關(guān)官方文檔

TEXT 類型 vs JSON 類型

TEXT 類型

TEXT 類型用于存儲(chǔ)長(zhǎng)文本數(shù)據(jù)，包括 JSON 字符串。它適用于大多數(shù)文本存儲(chǔ)需求，但對(duì) JSON 數(shù)據(jù)的處理功能有限。 優(yōu)點(diǎn)：

兼容性高：TEXT 類型在不同版本和工具中具有廣泛的支持。無(wú)額外開(kāi)銷：沒(méi)有 JSON 數(shù)據(jù)類型的內(nèi)部處理開(kāi)銷。

缺點(diǎn)：

缺乏內(nèi)置功能：TEXT 類型沒(méi)有 JSON 數(shù)據(jù)類型的內(nèi)置函數(shù)和操作符，例如 JSON_EXTRACT、JSON_SET 等。性能問(wèn)題：大 JSON 數(shù)據(jù)的查詢和操作可能會(huì)影響性能。

示例定義：

ALTER TABLE your_table ADD COLUMN json_text TEXT;

JSON 類型

JSON 類型是 MySQL 5.7+ 中的專用數(shù)據(jù)類型，用于存儲(chǔ) JSON 數(shù)據(jù)。它提供了豐富的功能來(lái)操作 JSON 數(shù)據(jù)。 優(yōu)點(diǎn)：

內(nèi)置函數(shù)：支持各種 JSON 函數(shù)，如 JSON_EXTRACT、JSON_SET、JSON_ARRAYAGG 等。數(shù)據(jù)驗(yàn)證：MySQL 會(huì)驗(yàn)證 JSON 格式是否合法。索引支持：可以對(duì) JSON 字段創(chuàng)建虛擬列，并在虛擬列上創(chuàng)建索引，提高查詢性能。

缺點(diǎn)：

性能開(kāi)銷：存儲(chǔ) JSON 數(shù)據(jù)時(shí)可能會(huì)有額外的開(kāi)銷。兼容性問(wèn)題：某些舊版工具和應(yīng)用可能不完全支持 JSON 數(shù)據(jù)類型。

示例定義：

ALTER TABLE your_table ADD COLUMN json_data JSON;

選擇建議

對(duì)于包含大 JSON 數(shù)據(jù)的字段，推薦使用 JSON 類型。以下是詳細(xì)的理由和操作步驟：

推薦使用 JSON 類型

推薦理由：

數(shù)據(jù)驗(yàn)證：JSON 類型自動(dòng)驗(yàn)證數(shù)據(jù)格式，確保數(shù)據(jù)符合 JSON 標(biāo)準(zhǔn)。功能豐富：JSON 類型提供了豐富的 JSON 操作函數(shù)，適合需要對(duì) JSON 數(shù)據(jù)進(jìn)行操作和查詢的場(chǎng)景。未來(lái)兼容性：JSON 類型在未來(lái)版本中可能會(huì)得到更多支持和優(yōu)化。

使用 TEXT 類型的情況

如果您的 JSON 數(shù)據(jù)是一次性寫入且不需要經(jīng)常查詢或操作，可以繼續(xù)使用 TEXT 類型 在這種情況下，您只需確保 JSON 數(shù)據(jù)在寫入時(shí)是有效的，并且查詢和操作的復(fù)雜度較低。

總結(jié)

類型優(yōu)點(diǎn)缺點(diǎn)TEXT兼容性高；無(wú)額外的 JSON 數(shù)據(jù)類型開(kāi)銷沒(méi)有 JSON 數(shù)據(jù)類型的內(nèi)置功能；查詢性能較差JSON內(nèi)置函數(shù)和操作符；數(shù)據(jù)驗(yàn)證；可以創(chuàng)建索引存儲(chǔ) JSON 數(shù)據(jù)時(shí)有額外開(kāi)銷；兼容性問(wèn)題

對(duì)于大 JSON 數(shù)據(jù)，推薦使用 JSON 類型，以利用 MySQL 的內(nèi)置功能和優(yōu)化。對(duì)于簡(jiǎn)單的文本存儲(chǔ)，TEXT 類型也可以滿足需求，但可能需要額外的處理步驟來(lái)管理 JSON 數(shù)據(jù)。

柚子快報(bào)激活碼778899分享：Mysql大單表JSON優(yōu)化

http://yzkb.51969.com/

好文推薦