在进行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字段所占据的磁盘空间大小。
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.