為什麼阿里巴巴不建議MySQL使用Text型別？

有情懷，有乾貨，微信搜尋【三太子敖丙】關注這個不一樣的程式設計師。

本文 GitHub https://github.com/JavaFamily 已收錄，有一線大廠面試完整考點、資料以及我的系列文章。

前言

眾所周知，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。

行記錄格式：

具有以下特點：

• 儲存變長列的前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檢測不通過報錯。

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

max_allowed_packet: This value by default is small, to catch large (possibly incorrect) packets. Must be increased if using large TEXT columns or long strings. As big as largest BLOB.

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

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分離，保證業務系統的穩定性。

好啦以上就是本期的全部內容了，我是敖丙，你知道的越多，你不知道的越多，我們下期見！

絮叨

敖丙把自己的面試文章整理成了一本電子書，共 1630頁！

乾貨滿滿，字字精髓。目錄如下，還有我複習時總結的面試題以及簡歷模板，現在免費送給大家。

連結:https://pan.baidu.com/s/1ZQEKJBgtYle3v-1LimcSwg 密碼:wjk6

我是敖丙，你知道的越多，你不知道的越多，感謝各位人才的：點贊、收藏和評論，我們下期見！

文章持續更新，可以微信搜一搜「 三太子敖丙 」第一時間閱讀，回覆【資料】有我準備的一線大廠面試資料和簡歷模板，本文 GitHub https://github.com/JavaFamily 已經收錄，有大廠面試完整考點，歡迎Star。