MySQL中不使用Text類型的原因是什么

MySQL中不使用Text類型的原因是什么，相信很多沒有經驗的人對此束手無策，為此本文總結了問題出現的原因和解決方法，通過這篇文章希望你能解決這個問題。

眾所周知，MySQL廣泛應用于互聯網的OLTP(聯機事務處理過程)業務系統中，在大廠開發規范中，經常會看到一條"不建議使用text大字段類型”。

下面就從text類型的存儲結構，引發的問題解釋下為什么不建議使用text類型，以及Text改造的建議方法。

背景

寫log表導致DML慢

問題描述

某歪有一個業務系統，使用RDS for MySQL  5.7的高可用版本，配置long_query_time=1s，添加慢查詢告警，我第一反應就是某歪又亂點了。

我通過監控看CPU，  QPS，TPS等指標不是很高，最近剛好雙十一全站都在做營銷活動，用戶量稍微有所增加。某歪反饋有些原本不慢的接口變的很慢，影響了正常的業務，需要做一下troubleshooting。

問題分析

我從慢查詢告警，可以看到有一些insert和update語句比較慢，同時告警時段的監控，發現IOPS很高，達到了70MB/s左右，由于RDS的CloundDBA功能不可用，又沒有audit  log功能，troubleshooting比較困難，硬著頭皮只能分析binlog了。

配置了max_binlog_size =512MB，在IOPS高的時段里，看下binlog的生成情況。

需要分析為什么binlog寫這么快，最有可能原因就是insert into  request_log表上有text類型，request_log表結構如下(demo)

CREATE TABLE request_log (`  `id bigint(20) NOT NULL AUTO_INCREMENT,`  `log text,`      `created_at datetime NOT NULL,`  `status tinyint(4) NOT NULL,`  `method varchar(10) DEFAULT NULL,`  `url varchar(50) DEFAULT NULL,`  `update_at datetime DEFAULT NULL,`  `running_time tinyint(4) DEFAULT '0',`  `user_id bigint(20) DEFAULT NULL,`  `type varchar(50) DEFAULT NULL,`  `PRIMARY KEY (id)` `) ENGINE=InnoDB AUTO_INCREMENT=4229611 DEFAULT CHARSET=utf8`

分析binlog：

$ mysqlbinlog --no-defaults -v -v --base64-output=DECODE-ROWS mysql-bin.000539|egrep "insert into request_log"

滿屏幕都是看不清的內容，翻了半天沒翻完。

基本上已經確定是寫入request_log的log字段引起的，導致binlog_cache頻繁的flush，以及binlog過度切換，導致IOPS過高，影響了其他正常的DML操作。

問題解決

跟開發同學溝通后，計劃在下一個版本修復這個問題，不再將request信息寫入表中，寫入到本地日志文件，通過filebeat抽取到es進行查詢，如果只是為了查看日志也可以接入grayLog等日志工具，沒必要寫入數據庫。

文章最后我還會介紹幾個MySQL 我踩過Text相關的坑，這介紹坑之前我先介紹下MySQLText類型。

MySQL中的Text

Text類型

text是一個能夠存儲大量的數據的大對象，有四種類型：TINYTEXT, TEXT,  MEDIUMTEXT,LONGTEXT，不同類型存儲的值范圍不同，如下所示

其中L表是text類型中存儲的實際長度的字節數。可以計算出TEXT類型最大存儲長度2**16-1 = 65535 Bytes。

InnoDB數據頁

Innodb數據頁由以下7個部分組成：

說明：File Trailer只有一個FiL_Page_end_lsn部分，占用8字節，前4字節代表該頁的checksum值，最后4字節和File  Header中的FIL_PAGE_LSN，一個頁是否發生了Corrupt，是通過File  Trailer部分進行檢測，而該部分的檢測會有一定的開銷，用戶可以通過參數innodb_checksums開啟或關閉這個頁完整性的檢測。

從MySQL 5.6開始默認的表存儲引擎是InnoDB，它是面向ROW存儲的，每個page(default page size =  16KB)，存儲的行記錄也是有規定的，最多允許存儲16K/2 - 200 = 7992行。

InnoDB的行格式

Innodb支持四種行格式：

由于Dynamic是Compact變異而來，結構大同而已，現在默認都是Dynamic格式;COMPRESSED主要是對表和索引數據進行壓縮，一般適用于使用率低的歸檔，備份類的需求，主要介紹下REDUNDANT和COMPACT行格式。

Redundant行格式

這種格式為了兼容舊版本MySQL。

行記錄格式：

字段長度偏移列表 記錄頭信息，占48字節 列1數據 列2數據 ……. Text列指針數據

具有以下特點：

• 存儲變長列的前768 Bytes在索引記錄中，剩余的存儲在overflow page中，對于固定長度且超過768  Bytes會被當做變長字段存儲在off-page中。

• 索引頁中的每條記錄包含一個6 Bytes的頭部，用于鏈接記錄用于行鎖。

• 聚簇索引的記錄包含用戶定義的所有列。另外還有一個6字節的事務ID(DB_TRX_ID)和一個7字節長度的回滾段指針(Roll pointer)列。

• 如果創建表沒有顯示指定主鍵，每個聚簇索引行還包括一個6字節的行ID(row ID)字段。

• 每個二級索引記錄包含了所有定義的主鍵索引列。

• 一條記錄包含一個指針來指向這條記錄的每個列，如果一條記錄的列的總長度小于128字節，這個指針占用1個字節，否則2個字節。這個指針數組稱為記錄目錄(record  directory)。指針指向的區域是這條記錄的數據部分。

• 固定長度的字符字段比如CHAR(10)通過固定長度的格式存儲，尾部填充空格。

• 固定長度字段長度大于或者等于768字節將被編碼成變長的字段，存儲在off-page中。

• 一個SQL的NULL值存儲一個字節或者兩個字節在記錄目錄(record  dirictoty)。對于變長字段null值在數據區域占0個字節。對于固定長度的字段，依然存儲固定長度在數據部分，為null值保留固定長度空間允許列從null值更新為非空值而不會引起索引的分裂。

• 對varchar類型，Redundant行記錄格式同樣不占用任何存儲空間，而CHAR類型的NULL值需要占用空間。

其中變長類型是通過長度 + 數據的方式存儲，不同類型長度是從1到4個字節(L+1 到 L + 4)，對于TEXT類型的值需要L  Bytes存儲value，同時需要2個字節存儲value的長度。同時Innodb最大行長度規定為65535  Bytes，對于Text類型，只保存9到12字節的指針，數據單獨存在overflow page中。

Compact行格式

這種行格式比redundant格式減少了存儲空間作為代價，但是會增加某些操作的CPU開銷。如果系統workload是受緩存命中率和磁盤速度限制，compact行格式可能更快。如果你的工作負載受CPU速度限制，compact行格式可能更慢，Compact  行格式被所有file format所支持。

行記錄格式：

Compact首部是一個非NULL變長字段長度的列表，并且是按列的順序逆序放置的，若列的長度小于255字節，用1字節表示;若大于255個字節，用2字節表示。變長字段最大不可以超過2字節，這是因為MySQL數據庫中varchar類型最大長度限制為65535，變長字段之后的第二個部分是NULL標志位，表示該行數據是否有NULL值。有則用1表示，該部分所占的字節應該為1字節。

所以在創建表的時候，盡量使用NOT NULL DEFAULT ''，如果表中列存儲大量的NULL值，一方面占用空間，另一個方面影響索引列的穩定性。

具有以下特點：

• 索引的每條記錄包含一個5個字節的頭部，頭部前面可以有一個可變長度的頭部。這個頭部用來將相關連的記錄鏈接在一起，也用于行鎖。

• 記錄頭部的變長部分包含了一個表示null 值的位向量(bit vector)。如果索引中可以為null的字段數量為N，這個位向量包含 N/8  向上取整的字節數。比例如果有9-16個字段可以為NULL值，這個位向量使用兩個字節。為NULL的列不占用空間，只占用這個位向量中的位。頭部的變長部分還包含了變長字段的長度。每個長度占用一個或者2個字節，這取決了字段的最大長度。如果所有列都可以為null  并且制定了固定長度，記錄頭部就沒有變長部分。

• 對每個不為NULL的變長字段，記錄頭包含了一個字節或者兩個字節的字段長度。只有當字段存儲在外部的溢出區域或者字段最大長度超過255字節并且實際長度超過127個字節的時候會使用2個字節的記錄頭部。對應外部存儲的字段，兩個字節的長度指明內部存儲部分的長度加上指向外部存儲部分的20個字節的指針。內部部分是768字節，因此這個長度值為  768+20， 20個字節的指針存儲了這個字段的真實長度。

• NULL不占該部分任何空間，即NULL除了占用NULL標志位，實際存儲不占任何空間。

• 記錄頭部跟著非空字段的數據部分。

• 聚簇索引的記錄包含了所以用戶定于的字段。另外還有一個6字節的事務ID列和一個7字節的回滾段指針。

• 如果沒有定于主鍵索引，則聚簇索引還包括一個6字節的Row ID列。

• 每個輔助索引記錄包含為群集索引鍵定義的不在輔助索引中的所有主鍵列。如果任何一個主鍵列是可變長度的，那么每個輔助索引的記錄頭都有一個可變長度的部分來記錄它們的長度，即使輔助索引是在固定長度的列上定義的。

• 固定長度的字符字段比如CHAR(10)通過固定長度的格式存儲，尾部填充空格。

• 對于變長的字符集，比如uft8mb3和utf8mb4， InnoDB試圖用N字節來存儲  CHAR(N)。如果CHAR(N)列的值的長度超過N字節，列后面的空格減少到最小值。CHAR(N)列值的最大長度是最大字符編碼數 x  N。比如utf8mb4字符集的最長編碼為4，則列的最長字節數是 4*N。

Text類型引發的問題

插入text字段導致報錯

創建測試表

[root@barret] [test]>create table user(id bigint not null primary key auto_increment,    -> name varchar(20) not null default '' comment '姓名',    -> age tinyint not null default 0 comment 'age',    -> gender char(1) not null default 'M' comment '性別',   -> info text not null comment '用戶信息',   -> create_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '創建時間',   -> update_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改時間'   -> ); Query OK, 0 rows affected (0.04 sec)

插入測試數據

root@barret] [test]>insert into user(name,age,gender,info) values('moon', 34, 'M', repeat('a',1024*1024*3)); ERROR 1406 (22001): Data too long for column 'info' at row 1 [root@barret] [test]>insert into user(name,age,gender,info) values('sky', 35, 'M', repeat('b',1024*1024*5)); ERROR 1301 (HY000): Result of repeat() was larger than max_allowed_packet (4194304) - truncated

錯誤分析

[root@barret] [test]>select @@max_allowed_packet; +----------------------+ | @@max_allowed_packet | +----------------------+ |       4194304 | +----------------------+ 1 row in set (0.00 sec)

max_allowed_packet控制communication  buffer最大尺寸，當發送的數據包大小超過該值就會報錯，我們都知道，MySQL包括Server層和存儲引擎，它們之間遵循2PC協議，Server層主要處理用戶的請求：連接請求—>SQL語法分析—>語義檢查—>生成執行計劃—>執行計劃—>fetch  data;存儲引擎層主要存儲數據，提供數據讀寫接口。

max_allowed_packet=4M，當第一條insert repeat('a',1024*1024*3)，數據包Server執行SQL發送數據包到InnoDB層的時候，檢查數據包大小沒有超過限制4M，在InnoDB寫數據時，發現超過了Text的限制導致報錯。第二條insert的數據包大小超過限制4M，Server檢測不通過報錯。

max_allowed_packet=4M，當第一條insert  repeat('a',1024*1024*3)，數據包Server執行SQL發送數據包到InnoDB層的時候，檢查數據包大小沒有超過限制4M，在InnoDB寫數據時，發現超過了Text的限制導致報錯。第二條insert的數據包大小超過限制4M，Server檢測不通過報錯。

引用AWS RDS參數組中該參數的描述

增加該參數的大小可以緩解報錯，但是不能徹底的解決問題。

RDS實例被鎖定

背景描述

公司每個月都會做一些營銷活動，有個服務apush活動推送，單獨部署在高可用版的RDS for MySQL 5.7，配置是4C8G  150G磁盤，數據庫里也就4張表，晚上22：00下班走的時候，rds實例數據使用了50G空間，第二天早晨9：30在地鐵上收到釘釘告警短信，提示push服務rds實例由于disk  is full被locked with —read-only，開發也反饋，應用日志報了一堆MySQL error。

問題分析

通過DMS登錄到數據庫，看一下那個表最大，發現有張表push_log占用了100G+，看了下表結構，里面有兩個text字段。

request text default '' comment '請求信息', response text default '' comment '響應信息' mysql>show  table status like 'push_log'；

發現Avg_row_length基本都在150KB左右，Rows = 78w，表的大小約為780000*150KB/1024/1024 =  111.5G。

通過主鍵update也很慢

insert into user(name,age,gender,info) values('thooo', 35, 'M', repeat('c',65535); insert into user(name,age,gender,info) values('thooo11', 35, 'M', repeat('d',65535); insert into user(name,age,gender,info) select name,age,gender,info from user; Query OK, 6144 rows affected (5.62 sec) Records: 6144  Duplicates: 0  Warnings: 0                                         [root@barret] [test]>select count(*) from user; +----------+ | count(*) | +----------+ |    24576 | +----------+ 1 row in set (0.05 sec)

做update操作并跟蹤。

mysql> set profiling = 1; Query OK, 0 rows affected, 1 warning (0.00 sec)  mysql> update user set info = repeat('f',65535) where id = 11; Query OK, 1 row affected (0.28 sec) Rows matched: 1  Changed: 1  Warnings: 0  mysql> show profiles; +----------+------------+--------------------------------------------------------+ | Query_ID | Duration   | Query                                                  | +----------+------------+--------------------------------------------------------+ |        1 | 0.27874125 | update user set info = repeat('f',65535) where id = 11 | +----------+------------+--------------------------------------------------------+ 1 row in set, 1 warning (0.00 sec)  mysql> show profile cpu,block io for query 1;   +----------------------+----------+----------+------------+--------------+---------------+ | Status               | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out | +----------------------+----------+----------+------------+--------------+---------------+ | starting             | 0.000124 | 0.000088 |   0.000035 |            0 |             0 | | checking permissions | 0.000021 | 0.000014 |   0.000006 |            0 |             0 | | Opening tables       | 0.000038 | 0.000026 |   0.000011 |            0 |             0 | | init                 | 0.000067 | 0.000049 |   0.000020 |            0 |             0 | | System lock          | 0.000076 | 0.000054 |   0.000021 |            0 |             0 | | updating             | 0.244906 | 0.000000 |   0.015382 |            0 |         16392 | | end                  | 0.000036 | 0.000000 |   0.000034 |            0 |             0 | | query end            | 0.033040 | 0.000000 |   0.000393 |            0 |           136 | | closing tables       | 0.000046 | 0.000000 |   0.000043 |            0 |             0 | | freeing items        | 0.000298 | 0.000000 |   0.000053 |            0 |             0 | | cleaning up          | 0.000092 | 0.000000 |   0.000092 |            0 |             0 | +----------------------+----------+----------+------------+--------------+---------------+ 11 rows in set, 1 warning (0.00 sec)

可以看到主要耗時在updating這一步，IO輸出次數16392次，在并發的表上通過id做update，也會變得很慢。

group_concat也會導致查詢報錯

在業務開發當中，經常有類似這樣的需求，需要根據每個省份可以定點醫保單位名稱，通常實現如下：

select group_concat(dru_name) from t_drugstore group by province;

其中內置group_concat返回一個聚合的string，最大長度由參數group_concat_max_len(Maximum allowed  result length in bytes for the  GROUP_CONCAT())決定，默認是1024，一般都太短了，開發要求改長一點，例如1024000。

當group_concat返回的結果集的大小超過max_allowed_packet限制的時候，程序會報錯，這一點要額外注意。

MySQL內置的log表

MySQL中的日志表mysql.general_log和mysql.slow_log，如果開啟審計audit功能，同時log_output=TABLE，就會有mysql.audit_log表，結構跟mysql.general_log大同小異。

分別看一下他們的表結構

CREATE TABLE `general_log` (   `event_time` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),   `user_host` mediumtext NOT NULL,   `thread_id` bigint(21) unsigned NOT NULL,   `server_id` int(10) unsigned NOT NULL,   `command_type` varchar(64) NOT NULL,   `argument` mediumblob NOT NULL ) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='General log'

CREATE TABLE `slow_log` (   `start_time` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),   `user_host` mediumtext NOT NULL,   `query_time` time(6) NOT NULL,   `lock_time` time(6) NOT NULL,   `rows_sent` int(11) NOT NULL,   `rows_examined` int(11) NOT NULL,   `db` varchar(512) NOT NULL,   `last_insert_id` int(11) NOT NULL,   `insert_id` int(11) NOT NULL,   `server_id` int(10) unsigned NOT NULL,   `sql_text` mediumblob NOT NULL,   `thread_id` bigint(21) unsigned NOT NULL ) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='Slow log'

mysql.general_log記錄的是經過MySQL Server處理的所有的SQL，包括后端和用戶的，insert比較頻繁，同時argument  mediumblob NOT NULL，對MySQL  Server性能有影響的，一般我們在dev環境為了跟蹤排查問題，可以開啟general_log，Production環境禁止開啟general_log，可以開啟audit_log，它是在general_log的基礎上做了一些filter，比如我只需要業務賬號發起的所有的SQL，這個很有用的，很多時候需要分析某一段時間內哪個SQL的QPS，TPS比較高。

mysql.slow_log記錄的是執行超過long_query_time的所有SQL，如果遵循MySQL開發規范，slow  query不會太多，但是開啟了log_queries_not_using_indexes=ON就會有好多full table  scan的SQL被記錄，這時slow_log表會很大，對于RDS來說，一般只保留一天的數據，在頻繁insert into  slow_log的時候，做truncate table slow_log去清理slow_log會導致MDL，影響MySQL穩定性。

建議將log_output=FILE，開啟slow_log， audit_log，這樣就會將slow_log，audit_log寫入文件，通過Go  API處理這些文件將數據寫入分布式列式數據庫clickhouse中做統計分析。

Text改造建議

使用es存儲

在MySQL中，一般log表會存儲text類型保存request或response類的數據，用于接口調用失敗時去手動排查問題，使用頻繁的很低。可以考慮寫入本地log  file，通過filebeat抽取到es中，按天索引，根據數據保留策略進行清理。

使用對象存儲

有些業務場景表用到TEXT，BLOB類型，存儲的一些圖片信息，比如商品的圖片，更新頻率比較低，可以考慮使用對象存儲，例如阿里云的OSS，AWS的S3都可以，能夠方便且高效的實現這類需求。

總結

由于MySQL是單進程多線程模型，一個SQL語句無法利用多個cpu  core去執行，這也就決定了MySQL比較適合OLTP(特點：大量用戶訪問、邏輯讀，索引掃描，返回少量數據，SQL簡單)業務系統，同時要針對MySQL去制定一些建模規范和開發規范，盡量避免使用Text類型，它不但消耗大量的網絡和IO帶寬，同時在該表上的DML操作都會變得很慢。

另外建議將復雜的統計分析類的SQL，建議遷移到實時數倉OLAP中，例如目前使用比較多的clickhouse，里云的ADB，AWS的Redshift都可以，做到OLTP和OLAP類業務SQL分離，保證業務系統的穩定性。

看完上述內容，你們掌握MySQL中不使用Text類型的原因是什么的方法了嗎？如果還想學到更多技能或想了解更多相關內容，歡迎關注億速云行業資訊頻道，感謝各位的閱讀！