附sp_dbdata的存储过程脚本
DELIMITER $$
DROP PROCEDURE IF EXISTS `mysql`.`sp_dbdata`$$
create procedure sp_dbdata (IN db_name varchar(100),IN f_limit int)
BEGIN if db_name <> 'ALL' THEN
set @sqltext=concat('SELECT concat(table_schema,\'.\',table_name),concat(round(table_rows/1000000,2),\'M\') rows,concat(round(data_length/(1024*1024*1024),2),\'G\') DATA,concat(round(index_length/(1024*1024*1024)\,2),\'G\') idx,
concat(round((data_length+index_length)/(1024*1024*1024),2),\'G\') total_size,round(index_length/data_length,2) id
xfrac FROM information_schema.TABLES ','where table_schema ="',db_name,'" ORDER BY data_length+index_length DESC L
IMIT ',f_limit);
prepare s1 from @sqltext;
execute s1;
deallocate prepare s1;
else
set @sqltext=concat('SELECT concat(table_schema,\'.\',table_name),concat(round(table_rows/1000000,2),\'M\') rows,concat(round(data_length/(1024*1024*1024),2),\'G\') DATA,concat(round(index_length/(1024*1024*1024),2),\'G\') idx,
concat(round((data_length+index_length)/(1024*1024*1024),2),\'G\') total_size,round(index_length/data_length,2) idxfrac FROM information_schema.TABLES ORDER BY data_length+index_length DESC LIMIT ',f_limit);
prepare s2 from @sqltext;
execute s2;
deallocate prepare s2;
end if;
END$$
DELIMITER ;
DROP PROCEDURE IF EXISTS `mysql`.`sp_dbdata`$$
create procedure sp_dbdata (IN db_name varchar(100),IN f_limit int)
BEGIN if db_name <> 'ALL' THEN
set @sqltext=concat('SELECT concat(table_schema,\'.\',table_name),concat(round(table_rows/1000000,2),\'M\') rows,concat(round(data_length/(1024*1024*1024),2),\'G\') DATA,concat(round(index_length/(1024*1024*1024)\,2),\'G\') idx,
concat(round((data_length+index_length)/(1024*1024*1024),2),\'G\') total_size,round(index_length/data_length,2) id
xfrac FROM information_schema.TABLES ','where table_schema ="',db_name,'" ORDER BY data_length+index_length DESC L
IMIT ',f_limit);
prepare s1 from @sqltext;
execute s1;
deallocate prepare s1;
else
set @sqltext=concat('SELECT concat(table_schema,\'.\',table_name),concat(round(table_rows/1000000,2),\'M\') rows,concat(round(data_length/(1024*1024*1024),2),\'G\') DATA,concat(round(index_length/(1024*1024*1024),2),\'G\') idx,
concat(round((data_length+index_length)/(1024*1024*1024),2),\'G\') total_size,round(index_length/data_length,2) idxfrac FROM information_schema.TABLES ORDER BY data_length+index_length DESC LIMIT ',f_limit);
prepare s2 from @sqltext;
execute s2;
deallocate prepare s2;
end if;
END$$
DELIMITER ;
附某一个我目前平台上跑的投票数据库实例的数据片段：
Call mysql.sp_dbdata(‘ALL’,20);
######TABLESTATUS包括表rows和对应数据大小，索引大小，已经索引和数据比例
concat(table_schema,'.',table_name) rows DATA idx total_size idxfrac
tech_survey_2.vote_32803 6.69M 0.15G 0.06G 0.21G 0.43
tech_survey_2.vote_33115 3.19M 0.07G 0.03G 0.10G 0.43
auto_survey_2.vote_28346 3.06M 0.07G 0.03G 0.10G 0.43
finance_survey_2.vote_30796 2.11M 0.05G 0.02G 0.07G 0.43
ent_survey_3.vote_27834 2.02M 0.05G 0.02G 0.06G 0.43
ent_survey_3.userinfo_27834 1.01M 0.03G 0.03G 0.06G 1.12
finance_survey.vote_27014 1.72M 0.04G 0.02G 0.06G 0.43
eladies_survey.vote_29246_old 1.57M 0.04G 0.02G 0.05G 0.43
tech_survey_2.vote_28901 1.51M 0.03G 0.01G 0.05G 0.43
ent_survey_3.vote_28063 1.51M 0.03G 0.01G 0.05G 0.43
finance_survey_2.vote_33024 1.49M 0.03G 0.01G 0.05G 0.43
auto_survey_2.userinfo_28346 0.77M 0.02G 0.02G 0.04G 1.08
news_survey_2.vote_31174 1.34M 0.03G 0.01G 0.04G 0.43
sports_survey.userinfo_26112 0.69M 0.02G 0.02G 0.04G 1.23
ent_survey.vote_30619 1.27M 0.03G 0.01G 0.04G 0.43
finance_survey.userinfo_27014 0.66M 0.02G 0.02G 0.04G 1.23
