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

MySQL中不使用Text類型的原因是什么，相信很多沒(méi)有經(jīng)驗(yàn)的人對(duì)此束手無(wú)策，為此本文總結(jié)了問(wèn)題出現(xiàn)的原因和解決方法，通過(guò)這篇文章希望你能解決這個(gè)問(wèn)題。

眾所周知，MySQL廣泛應(yīng)用于互聯(lián)網(wǎng)的OLTP(聯(lián)機(jī)事務(wù)處理過(guò)程)業(yè)務(wù)系統(tǒng)中，在大廠開(kāi)發(fā)規(guī)范中，經(jīng)常會(huì)看到一條"不建議使用text大字段類型”。

下面就從text類型的存儲(chǔ)結(jié)構(gòu)，引發(fā)的問(wèn)題解釋下為什么不建議使用text類型，以及Text改造的建議方法。

背景

寫(xiě)log表導(dǎo)致DML慢

問(wèn)題描述

某歪有一個(gè)業(yè)務(wù)系統(tǒng)，使用RDS for MySQL  5.7的高可用版本，配置long_query_time=1s，添加慢查詢告警，我第一反應(yīng)就是某歪又亂點(diǎn)了。

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

問(wèn)題分析

我從慢查詢告警，可以看到有一些insert和update語(yǔ)句比較慢，同時(shí)告警時(shí)段的監(jiān)控，發(fā)現(xiàn)IOPS很高，達(dá)到了70MB/s左右，由于RDS的CloundDBA功能不可用，又沒(méi)有audit  log功能，troubleshooting比較困難，硬著頭皮只能分析binlog了。

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

需要分析為什么binlog寫(xiě)這么快，最有可能原因就是insert into  request_log表上有text類型，request_log表結(jié)構(gòu)如下(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"

滿屏幕都是看不清的內(nèi)容，翻了半天沒(méi)翻完。

基本上已經(jīng)確定是寫(xiě)入request_log的log字段引起的，導(dǎo)致binlog_cache頻繁的flush，以及binlog過(guò)度切換，導(dǎo)致IOPS過(guò)高，影響了其他正常的DML操作。

問(wèn)題解決

跟開(kāi)發(fā)同學(xué)溝通后，計(jì)劃在下一個(gè)版本修復(fù)這個(gè)問(wèn)題，不再將request信息寫(xiě)入表中，寫(xiě)入到本地日志文件，通過(guò)filebeat抽取到es進(jìn)行查詢，如果只是為了查看日志也可以接入grayLog等日志工具，沒(méi)必要寫(xiě)入數(shù)據(jù)庫(kù)。

文章最后我還會(huì)介紹幾個(gè)MySQL 我踩過(guò)Text相關(guān)的坑，這介紹坑之前我先介紹下MySQLText類型。

MySQL中的Text

Text類型

text是一個(gè)能夠存儲(chǔ)大量的數(shù)據(jù)的大對(duì)象，有四種類型：TINYTEXT, TEXT,  MEDIUMTEXT,LONGTEXT，不同類型存儲(chǔ)的值范圍不同，如下所示

其中L表是text類型中存儲(chǔ)的實(shí)際長(zhǎng)度的字節(jié)數(shù)?？梢杂?jì)算出TEXT類型最大存儲(chǔ)長(zhǎng)度2**16-1 = 65535 Bytes。

InnoDB數(shù)據(jù)頁(yè)

Innodb數(shù)據(jù)頁(yè)由以下7個(gè)部分組成：

說(shuō)明：File Trailer只有一個(gè)FiL_Page_end_lsn部分，占用8字節(jié)，前4字節(jié)代表該頁(yè)的checksum值，最后4字節(jié)和File  Header中的FIL_PAGE_LSN，一個(gè)頁(yè)是否發(fā)生了Corrupt，是通過(guò)File  Trailer部分進(jìn)行檢測(cè)，而該部分的檢測(cè)會(huì)有一定的開(kāi)銷，用戶可以通過(guò)參數(shù)innodb_checksums開(kāi)啟或關(guān)閉這個(gè)頁(yè)完整性的檢測(cè)。

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

InnoDB的行格式

Innodb支持四種行格式：

由于Dynamic是Compact變異而來(lái)，結(jié)構(gòu)大同而已，現(xiàn)在默認(rèn)都是Dynamic格式;COMPRESSED主要是對(duì)表和索引數(shù)據(jù)進(jìn)行壓縮，一般適用于使用率低的歸檔，備份類的需求，主要介紹下REDUNDANT和COMPACT行格式。

Redundant行格式

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

行記錄格式：

字段長(zhǎng)度偏移列表 記錄頭信息，占48字節(jié) 列1數(shù)據(jù) 列2數(shù)據(jù) ……. Text列指針數(shù)據(jù)

具有以下特點(diǎn)：

• 存儲(chǔ)變長(zhǎng)列的前768 Bytes在索引記錄中，剩余的存儲(chǔ)在overflow page中，對(duì)于固定長(zhǎng)度且超過(guò)768  Bytes會(huì)被當(dāng)做變長(zhǎng)字段存儲(chǔ)在off-page中。

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

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

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

• 每個(gè)二級(jí)索引記錄包含了所有定義的主鍵索引列。

• 一條記錄包含一個(gè)指針來(lái)指向這條記錄的每個(gè)列，如果一條記錄的列的總長(zhǎng)度小于128字節(jié)，這個(gè)指針占用1個(gè)字節(jié)，否則2個(gè)字節(jié)。這個(gè)指針數(shù)組稱為記錄目錄(record  directory)。指針指向的區(qū)域是這條記錄的數(shù)據(jù)部分。

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

• 固定長(zhǎng)度字段長(zhǎng)度大于或者等于768字節(jié)將被編碼成變長(zhǎng)的字段，存儲(chǔ)在off-page中。

• 一個(gè)SQL的NULL值存儲(chǔ)一個(gè)字節(jié)或者兩個(gè)字節(jié)在記錄目錄(record  dirictoty)。對(duì)于變長(zhǎng)字段null值在數(shù)據(jù)區(qū)域占0個(gè)字節(jié)。對(duì)于固定長(zhǎng)度的字段，依然存儲(chǔ)固定長(zhǎng)度在數(shù)據(jù)部分，為null值保留固定長(zhǎng)度空間允許列從null值更新為非空值而不會(huì)引起索引的分裂。

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

其中變長(zhǎng)類型是通過(guò)長(zhǎng)度 + 數(shù)據(jù)的方式存儲(chǔ)，不同類型長(zhǎng)度是從1到4個(gè)字節(jié)(L+1 到 L + 4)，對(duì)于TEXT類型的值需要L  Bytes存儲(chǔ)value，同時(shí)需要2個(gè)字節(jié)存儲(chǔ)value的長(zhǎng)度。同時(shí)Innodb最大行長(zhǎng)度規(guī)定為65535  Bytes，對(duì)于Text類型，只保存9到12字節(jié)的指針，數(shù)據(jù)單獨(dú)存在overflow page中。

Compact行格式

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

行記錄格式：

Compact首部是一個(gè)非NULL變長(zhǎng)字段長(zhǎng)度的列表，并且是按列的順序逆序放置的，若列的長(zhǎng)度小于255字節(jié)，用1字節(jié)表示;若大于255個(gè)字節(jié)，用2字節(jié)表示。變長(zhǎng)字段最大不可以超過(guò)2字節(jié)，這是因?yàn)镸ySQL數(shù)據(jù)庫(kù)中varchar類型最大長(zhǎng)度限制為65535，變長(zhǎng)字段之后的第二個(gè)部分是NULL標(biāo)志位，表示該行數(shù)據(jù)是否有NULL值。有則用1表示，該部分所占的字節(jié)應(yīng)該為1字節(jié)。

所以在創(chuàng)建表的時(shí)候，盡量使用NOT NULL DEFAULT ''，如果表中列存儲(chǔ)大量的NULL值，一方面占用空間，另一個(gè)方面影響索引列的穩(wěn)定性。

具有以下特點(diǎn)：

• 索引的每條記錄包含一個(gè)5個(gè)字節(jié)的頭部，頭部前面可以有一個(gè)可變長(zhǎng)度的頭部。這個(gè)頭部用來(lái)將相關(guān)連的記錄鏈接在一起，也用于行鎖。

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

• 對(duì)每個(gè)不為NULL的變長(zhǎng)字段，記錄頭包含了一個(gè)字節(jié)或者兩個(gè)字節(jié)的字段長(zhǎng)度。只有當(dāng)字段存儲(chǔ)在外部的溢出區(qū)域或者字段最大長(zhǎng)度超過(guò)255字節(jié)并且實(shí)際長(zhǎng)度超過(guò)127個(gè)字節(jié)的時(shí)候會(huì)使用2個(gè)字節(jié)的記錄頭部。對(duì)應(yīng)外部存儲(chǔ)的字段，兩個(gè)字節(jié)的長(zhǎng)度指明內(nèi)部存儲(chǔ)部分的長(zhǎng)度加上指向外部存儲(chǔ)部分的20個(gè)字節(jié)的指針。內(nèi)部部分是768字節(jié)，因此這個(gè)長(zhǎng)度值為  768+20， 20個(gè)字節(jié)的指針存儲(chǔ)了這個(gè)字段的真實(shí)長(zhǎng)度。

• NULL不占該部分任何空間，即NULL除了占用NULL標(biāo)志位，實(shí)際存儲(chǔ)不占任何空間。

• 記錄頭部跟著非空字段的數(shù)據(jù)部分。

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

• 如果沒(méi)有定于主鍵索引，則聚簇索引還包括一個(gè)6字節(jié)的Row ID列。

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

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

• 對(duì)于變長(zhǎng)的字符集，比如uft8mb3和utf8mb4， InnoDB試圖用N字節(jié)來(lái)存儲(chǔ)  CHAR(N)。如果CHAR(N)列的值的長(zhǎng)度超過(guò)N字節(jié)，列后面的空格減少到最小值。CHAR(N)列值的最大長(zhǎng)度是最大字符編碼數(shù) x  N。比如utf8mb4字符集的最長(zhǎng)編碼為4，則列的最長(zhǎng)字節(jié)數(shù)是 4*N。

Text類型引發(fā)的問(wèn)題

插入text字段導(dǎo)致報(bào)錯(cuò)

創(chuàng)建測(cè)試表

[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 '創(chuàng)建時(shí)間',   -> update_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改時(shí)間'   -> ); Query OK, 0 rows affected (0.04 sec)

插入測(cè)試數(shù)據(jù)

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

錯(cuò)誤分析

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

max_allowed_packet控制communication  buffer最大尺寸，當(dāng)發(fā)送的數(shù)據(jù)包大小超過(guò)該值就會(huì)報(bào)錯(cuò)，我們都知道，MySQL包括Server層和存儲(chǔ)引擎，它們之間遵循2PC協(xié)議，Server層主要處理用戶的請(qǐng)求：連接請(qǐng)求—>SQL語(yǔ)法分析—>語(yǔ)義檢查—>生成執(zhí)行計(jì)劃—>執(zhí)行計(jì)劃—>fetch  data;存儲(chǔ)引擎層主要存儲(chǔ)數(shù)據(jù)，提供數(shù)據(jù)讀寫(xiě)接口。

max_allowed_packet=4M，當(dāng)?shù)谝粭linsert repeat('a',1024*1024*3)，數(shù)據(jù)包Server執(zhí)行SQL發(fā)送數(shù)據(jù)包到InnoDB層的時(shí)候，檢查數(shù)據(jù)包大小沒(méi)有超過(guò)限制4M，在InnoDB寫(xiě)數(shù)據(jù)時(shí)，發(fā)現(xiàn)超過(guò)了Text的限制導(dǎo)致報(bào)錯(cuò)。第二條insert的數(shù)據(jù)包大小超過(guò)限制4M，Server檢測(cè)不通過(guò)報(bào)錯(cuò)。

max_allowed_packet=4M，當(dāng)?shù)谝粭linsert  repeat('a',1024*1024*3)，數(shù)據(jù)包Server執(zhí)行SQL發(fā)送數(shù)據(jù)包到InnoDB層的時(shí)候，檢查數(shù)據(jù)包大小沒(méi)有超過(guò)限制4M，在InnoDB寫(xiě)數(shù)據(jù)時(shí)，發(fā)現(xiàn)超過(guò)了Text的限制導(dǎo)致報(bào)錯(cuò)。第二條insert的數(shù)據(jù)包大小超過(guò)限制4M，Server檢測(cè)不通過(guò)報(bào)錯(cuò)。

引用AWS RDS參數(shù)組中該參數(shù)的描述

增加該參數(shù)的大小可以緩解報(bào)錯(cuò)，但是不能徹底的解決問(wèn)題。

RDS實(shí)例被鎖定

背景描述

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

問(wèn)題分析

通過(guò)DMS登錄到數(shù)據(jù)庫(kù)，看一下那個(gè)表最大，發(fā)現(xiàn)有張表push_log占用了100G+，看了下表結(jié)構(gòu)，里面有兩個(gè)text字段。

request text default '' comment '請(qǐng)求信息', response text default '' comment '響應(yīng)信息' mysql>show  table status like 'push_log'；

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

通過(guò)主鍵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)

可以看到主要耗時(shí)在updating這一步，IO輸出次數(shù)16392次，在并發(fā)的表上通過(guò)id做update，也會(huì)變得很慢。

group_concat也會(huì)導(dǎo)致查詢報(bào)錯(cuò)

在業(yè)務(wù)開(kāi)發(fā)當(dāng)中，經(jīng)常有類似這樣的需求，需要根據(jù)每個(gè)省份可以定點(diǎn)醫(yī)保單位名稱，通常實(shí)現(xiàn)如下：

select group_concat(dru_name) from t_drugstore group by province;

其中內(nèi)置group_concat返回一個(gè)聚合的string，最大長(zhǎng)度由參數(shù)group_concat_max_len(Maximum allowed  result length in bytes for the  GROUP_CONCAT())決定，默認(rèn)是1024，一般都太短了，開(kāi)發(fā)要求改長(zhǎng)一點(diǎn)，例如1024000。

當(dāng)group_concat返回的結(jié)果集的大小超過(guò)max_allowed_packet限制的時(shí)候，程序會(huì)報(bào)錯(cuò)，這一點(diǎn)要額外注意。

MySQL內(nèi)置的log表

MySQL中的日志表mysql.general_log和mysql.slow_log，如果開(kāi)啟審計(jì)audit功能，同時(shí)log_output=TABLE，就會(huì)有mysql.audit_log表，結(jié)構(gòu)跟mysql.general_log大同小異。

分別看一下他們的表結(jié)構(gòu)

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記錄的是經(jīng)過(guò)MySQL Server處理的所有的SQL，包括后端和用戶的，insert比較頻繁，同時(shí)argument  mediumblob NOT NULL，對(duì)MySQL  Server性能有影響的，一般我們?cè)赿ev環(huán)境為了跟蹤排查問(wèn)題，可以開(kāi)啟general_log，Production環(huán)境禁止開(kāi)啟general_log，可以開(kāi)啟audit_log，它是在general_log的基礎(chǔ)上做了一些filter，比如我只需要業(yè)務(wù)賬號(hào)發(fā)起的所有的SQL，這個(gè)很有用的，很多時(shí)候需要分析某一段時(shí)間內(nèi)哪個(gè)SQL的QPS，TPS比較高。

mysql.slow_log記錄的是執(zhí)行超過(guò)long_query_time的所有SQL，如果遵循MySQL開(kāi)發(fā)規(guī)范，slow  query不會(huì)太多，但是開(kāi)啟了log_queries_not_using_indexes=ON就會(huì)有好多full table  scan的SQL被記錄，這時(shí)slow_log表會(huì)很大，對(duì)于RDS來(lái)說(shuō)，一般只保留一天的數(shù)據(jù)，在頻繁insert into  slow_log的時(shí)候，做truncate table slow_log去清理slow_log會(huì)導(dǎo)致MDL，影響MySQL穩(wěn)定性。

建議將log_output=FILE，開(kāi)啟slow_log， audit_log，這樣就會(huì)將slow_log，audit_log寫(xiě)入文件，通過(guò)Go  API處理這些文件將數(shù)據(jù)寫(xiě)入分布式列式數(shù)據(jù)庫(kù)clickhouse中做統(tǒng)計(jì)分析。

Text改造建議

使用es存儲(chǔ)

在MySQL中，一般log表會(huì)存儲(chǔ)text類型保存request或response類的數(shù)據(jù)，用于接口調(diào)用失敗時(shí)去手動(dòng)排查問(wèn)題，使用頻繁的很低?？梢钥紤]寫(xiě)入本地log  file，通過(guò)filebeat抽取到es中，按天索引，根據(jù)數(shù)據(jù)保留策略進(jìn)行清理。

使用對(duì)象存儲(chǔ)

有些業(yè)務(wù)場(chǎng)景表用到TEXT，BLOB類型，存儲(chǔ)的一些圖片信息，比如商品的圖片，更新頻率比較低，可以考慮使用對(duì)象存儲(chǔ)，例如阿里云的OSS，AWS的S3都可以，能夠方便且高效的實(shí)現(xiàn)這類需求。

總結(jié)

由于MySQL是單進(jìn)程多線程模型，一個(gè)SQL語(yǔ)句無(wú)法利用多個(gè)cpu  core去執(zhí)行，這也就決定了MySQL比較適合OLTP(特點(diǎn)：大量用戶訪問(wèn)、邏輯讀，索引掃描，返回少量數(shù)據(jù)，SQL簡(jiǎn)單)業(yè)務(wù)系統(tǒng)，同時(shí)要針對(duì)MySQL去制定一些建模規(guī)范和開(kāi)發(fā)規(guī)范，盡量避免使用Text類型，它不但消耗大量的網(wǎng)絡(luò)和IO帶寬，同時(shí)在該表上的DML操作都會(huì)變得很慢。

另外建議將復(fù)雜的統(tǒng)計(jì)分析類的SQL，建議遷移到實(shí)時(shí)數(shù)倉(cāng)OLAP中，例如目前使用比較多的clickhouse，里云的ADB，AWS的Redshift都可以，做到OLTP和OLAP類業(yè)務(wù)SQL分離，保證業(yè)務(wù)系統(tǒng)的穩(wěn)定性。

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