在进行mysql数据库巡检的时候,发送一个数据文件特别大,有快60g了,如下:
[root@db_m1_slave_1 business_db]# ll -h MOVIE_INTERFACE_LOG.ibd
-rw-rw----. 1 mysql mysql 59G Jan 16 16:40 MOVIE_INTERFACE_LOG.ibd
[root@db_m1_slave_1 business_db]#
查看数据量,才不到200W记录,那是怎么会占据60g的容量呢?
mysql> select count(1) from MOVIE_INTERFACE_LOG;
+----------+
| count(1) |
+----------+
| 1769717 |
+----------+
1 row in set (7.20 sec)
mysql>
进去查看这个表,可以看到有varchar(1000),text字段以及longtext字段
mysql> show create table MOVIE_INTERFACE_LOG;
+---------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+---------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| MOVIE_INTERFACE_LOG | CREATE TABLE `MOVIE_INTERFACE_LOG` (
`ID` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '自增主键',
`TRADE_ID` varchar(200) DEFAULT NULL COMMENT '接口ID',
`REQUEST` text COMMENT '请求报文',
`RESPONSE` longtext COMMENT '返回报文',
`MEMO` varchar(1000) DEFAULT NULL COMMENT '报文描述信息',
`CREATE_DATE` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建日期',
`UPDATE_DATE` datetime DEFAULT NULL COMMENT '修改日期',
`CREATE_USER` varchar(20) DEFAULT NULL COMMENT '创建人',
`UPDATE_USER` varchar(20) DEFAULT NULL COMMENT '修改人',
PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=1769690 DEFAULT CHARSET=utf8 COMMENT='接口请求报文记录' |
+---------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql>
表字段清晰不多,而且没有多余的索引,那么导致磁盘空间占据毕竟大的因素就是longtext/text大字段。去查看官方网站的注解 http://dev.mysql.com/doc/refman/5.7/en/string-type-overview.html,查看longtext和text字段所占据的磁盘空间大小。
LONGTEXT [CHARACTER SET charset_name] [COLLATE collation_name]
A TEXT column with a maximum length of 4,294,967,295 or 4GB (232 − 1) characters. The effective maximum length is less if the value contains multibyte characters. The effective maximum length of LONGTEXT columns also depends on the configured maximum packet size in the client/server protocol and available memory. Each LONGTEXT value is stored using a 4-byte length prefix that indicates the number of bytes in the value.
TEXT[(M)] [CHARACTER SET charset_name] [COLLATE collation_name]
A TEXT column with a maximum length of 65,535 (216 − 1) characters. The effective maximum length is less if the value contains multibyte characters. Each TEXT value is stored using a 2-byte length prefix that indicates the number of bytes in the value.
An optional length M can be given for this type. If this is done, MySQL creates the column as the smallest TEXT type large enough to hold values M characters long.
【分析】text字段占据的为65535个byte,还可以,不到1M,就算所有的记录满负荷了,200W占据60g的可能比较小,二longtext单个记录最大可以占据到4g得容量,所以可以看得出来,不到200W的MOVIE_INTERFACE_LOG表占据了60g容量,也就可以理解了。