mysql 统计 表空间 占用情况

统计指定"DB Name"的表空间占用情况

SELECT table_schema, sum( data_length + index_length ) / 1024 / 1024 "DB Size in MB"
FROM information_schema.TABLES where table_schema = "DB Name" GROUP BY table_schema ;

分类显示所有表空间的占用情况

SELECT table_schema,
         SUM(data_length+index_length)/1024/1024 AS total_mb,
         SUM(data_length)/1024/1024 AS data_mb,
         SUM(index_length)/1024/1024 AS index_mb,
         COUNT(*) AS tables,
         CURDATE() AS today
FROM information_schema.tables
GROUP BY table_schema
ORDER BY 2 DESC;

可以写入如下语句对查询结果进行分类:

SELECT
sum(case when `EmailSource`='FM' then 1 else 0 end) as FM_Statistic,
sum(case when `EmailSource`='UOC' then 1 else 0 end) as FM_Statistic,
sum(case when `EmailSource`='OC' then 1 else 0 end) as FM_Statistic,
DATE_FORMAT(Date,'%Y-%m-%d') AS `DateTime`
FROM `user_performance`
WHERE Email !='' AND Email !='TOTAL'
AND (DATE_FORMAT(Date,"%Y-%m-%d") >= DATE_FORMAT('2011-02-05'),'%Y-%m-%d'))
AND (DATE_FORMAT(Date,"%Y-%m-%d") >= DATE_FORMAT('2011-03-07'),'%Y-%m-%d'))
GROUP BY `Date`;

搜索结果为: