mysql数据库表大小查询

查询数据库中所有表的数据大小与索引大小

select 
TABLE_NAME,
concat(truncate(data_length/1024/1024,2),' MB') as data_size,
concat(truncate(index_length/1024/1024,2),' MB') as index_size 
from information_schema.tables 
where TABLE_SCHEMA = '[TABLE_SCHEMA ]'
order by data_size

## truncate() 函数可以截取 concat() 函数可以拼接

查看数据库中的状态
show table status from [TABLE_SCHEMA ]

创建分区表
用于做分区表的列必须是主键，或包含于主键中

DROP TABLE IF EXISTS `invoice_data`;
CREATE TABLE `invoice_data` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `inv_code_no` varchar(22) NOT NULL,
  `fp_content` text,
  `insert_time` datetime NOT NULL,
  PRIMARY KEY (`id`,`insert_time`),
  UNIQUE KEY `index_dm_hm` (`inv_code_no`,`insert_time`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1704085 DEFAULT CHARSET=utf8
/*!50100 PARTITION BY RANGE (to_days(`insert_time`))
(PARTITION p20200624 VALUES LESS THAN (737965) ENGINE = InnoDB,
 PARTITION p20200625 VALUES LESS THAN (737966) ENGINE = InnoDB,
 PARTITION p20200626 VALUES LESS THAN (737967) ENGINE = InnoDB,
 PARTITION p20200627 VALUES LESS THAN (737968) ENGINE = InnoDB,
 PARTITION p20200628 VALUES LESS THAN (737969) ENGINE = InnoDB,
 PARTITION p20200629 VALUES LESS THAN (737970) ENGINE = InnoDB,
 PARTITION p20200630 VALUES LESS THAN (737971) ENGINE = InnoDB,
 PARTITION P20200701 VALUES LESS THAN (737972) ENGINE = InnoDB) */;