存在的問(wèn)題

在向MySQL數(shù)據(jù)庫(kù)表中插入或更新記錄時(shí)，有時(shí)會(huì)出現(xiàn)字段超長(zhǎng)的問(wèn)題，包括但不限于以下場(chǎng)景：

• 處理上游系統(tǒng)發(fā)送的交易信息，將多個(gè)字段拼成一個(gè)JSON或其他格式的字符串；在增加字段，或數(shù)據(jù)較長(zhǎng)時(shí)，寫(xiě)入或更新數(shù)據(jù)庫(kù)時(shí)可能超長(zhǎng)；
• 調(diào)用下游系統(tǒng)的服務(wù)，返回的部分字段（如錯(cuò)誤信息等）較長(zhǎng)時(shí)，導(dǎo)致更新數(shù)據(jù)庫(kù)記錄失敗。

問(wèn)題解決與優(yōu)化建議

1. JSON等格式的字段

有業(yè)務(wù)含義的重要字段，不建議通過(guò)JSON字符串格式保存在一個(gè)數(shù)據(jù)庫(kù)字段中。

假如需要將字段以JSON字符串格式保存在一個(gè)數(shù)據(jù)庫(kù)字段中，建議只保存相對(duì)不重要，且不需要作為唯一的查詢(xún)條件的字段，在進(jìn)行保存時(shí)也需要考慮字段超長(zhǎng)問(wèn)題，及新舊數(shù)據(jù)與新舊代碼相互之間的兼容問(wèn)題。

1.1. MySQL字符串字段長(zhǎng)度

半角英文字母、數(shù)字、符號(hào)等常見(jiàn)字符，1個(gè)字符占用1個(gè)字節(jié)；1個(gè)漢字字符占用3個(gè)字節(jié)。

在utf8字符集下，1個(gè)字符最多占用3個(gè)字節(jié)，不支持占用4個(gè)字節(jié)的字符。

在utf8mb4字符集下，1個(gè)字符最多占用4個(gè)字節(jié)，可以保存emoji表情等占用4個(gè)字節(jié)的字符。

MySQL字符串字段的最大長(zhǎng)度如下所示：

需要注意，CHAR、VARCHAR類(lèi)型字段的最大長(zhǎng)度的單位為字符數(shù)，能夠保存的漢字?jǐn)?shù)量等于最大支持字符數(shù)；

TEXT等類(lèi)型字段的最大長(zhǎng)度的單位為字節(jié)數(shù)，能夠保存的漢字?jǐn)?shù)量不超過(guò)最大支持字節(jié)數(shù)的1/3。

1.2. MySQL使用較長(zhǎng)的字符串類(lèi)型字段影響

MySQL、MariaDB較新版本支持JSON類(lèi)型字段，其他版本需要使用字符串類(lèi)型字段保存。

MySQL中長(zhǎng)度超過(guò)768字節(jié)的固定長(zhǎng)度字段被編碼為變長(zhǎng)字段，例如VARCHAR（超過(guò)768字節(jié)）、TEXT等，變長(zhǎng)字段被稱(chēng)為頁(yè)外列（off-page），不是保存在InnoDB的B+樹(shù)索引中，而是保存在溢出頁(yè)（overflow page）中。在溢出頁(yè)中，變長(zhǎng)字段的值以單鏈表形式存儲(chǔ)。

對(duì)于保存JSON形式的字符串類(lèi)型字段，由于需要保存較多內(nèi)容，很可能屬于變長(zhǎng)字段。

保存JSON形式的字符串類(lèi)型字段不適合在查詢(xún)時(shí)作為唯一的查詢(xún)條件，

原因如下：

• 保存JSON形式的字符串類(lèi)型字段不適合創(chuàng)建索引：一是JSON字符串中的字段順序不固定，通過(guò)like進(jìn)行最左匹配查詢(xún)，很難從保存JSON形式的字段中查詢(xún)到需要的數(shù)據(jù)；二是因?yàn)镮nnoDB索引支持的長(zhǎng)度有限（在MySQL InnoDB默認(rèn)配置下，索引支持的最大長(zhǎng)度為768字節(jié)）；
• 僅通過(guò)變長(zhǎng)字段進(jìn)行查詢(xún)時(shí)，無(wú)法通過(guò)B+樹(shù)結(jié)構(gòu)的索引進(jìn)行查詢(xún)，而是需要在單鏈表形式的溢出頁(yè)中逐條進(jìn)行查詢(xún)，查詢(xún)效率會(huì)非常低。

查詢(xún)變長(zhǎng)字段，與不查詢(xún)變長(zhǎng)字段相比，開(kāi)銷(xiāo)會(huì)更大，耗時(shí)會(huì)更長(zhǎng)。

因?yàn)椴樵?xún)變長(zhǎng)字段時(shí)，會(huì)增加從溢出頁(yè)中查詢(xún)數(shù)據(jù)的步驟；且需要返回的數(shù)據(jù)量可能較大，數(shù)據(jù)返回耗時(shí)會(huì)增加。

在查詢(xún)包含變長(zhǎng)字段的數(shù)據(jù)庫(kù)表時(shí)，假如不需要獲取變長(zhǎng)字段，則不應(yīng)該在SQL語(yǔ)句中指定查詢(xún)變長(zhǎng)字段。

2. 可以截?cái)嗟淖侄?/h3>

對(duì)于截?cái)嗪蟛挥绊懯褂玫淖侄?，在?xiě)入或更新數(shù)據(jù)庫(kù)時(shí)，可對(duì)存在超長(zhǎng)風(fēng)險(xiǎn)的字段按照數(shù)據(jù)庫(kù)字段長(zhǎng)度進(jìn)行截?cái)啵?/p>

JDK中的String.substring()方法，commons-lang3中的StringUtils.substring()、StringUtils.truncate()方法，參數(shù)中的數(shù)字單位都是字符數(shù)，不是字節(jié)數(shù)。

在Java中對(duì)字符串進(jìn)行截取時(shí)，建議使用StringUtils.truncate()方法。

MySQL中的CHAR、VARCHAR類(lèi)型的最大長(zhǎng)度，也是字符數(shù)，不是字節(jié)數(shù)。

因此在Java中對(duì)字符串根據(jù)MySQL的字符串類(lèi)型字段長(zhǎng)度進(jìn)行截取時(shí)，兩者的長(zhǎng)度是一致的。

例如MySQL中的字段為VARCHAR(200)，則可使用以下方式進(jìn)行截取，將截取結(jié)果寫(xiě)入數(shù)據(jù)庫(kù)。

StringUtils.truncate("xxx", 200);

總結(jié)

以上為個(gè)人經(jīng)驗(yàn)，希望能給大家一個(gè)參考，也希望大家多多支持腳本之家。

最新評(píng)論