一、查询某个数据库里所有表的大小和行数
mysql> select table_name, (data_length+index_length)/1024/1024 as total_mb,table_rows from information_schema.tables where table_schema='zabbix' order by total_mb desc; +----------------------------+---------------+------------+ | table_name | total_mb | table_rows | +----------------------------+---------------+------------+ | history_uint | 5399.29687500 | 57731617 | | history | 4807.39062500 | 45944386 | | trends_uint | 474.92187500 | 7184507 | | history_str | 240.35937500 | 2404622 | | trends | 215.87500000 | 2946368 | | items | 6.21875000 | 10033 | | events | 4.93750000 | 8340 | | alerts | 4.67187500 | 5030 | | items_applications | 2.35937500 | 11510 | | triggers | 1.89062500 | 4359 | | auditlog | 1.85937500 | 6802 | | images | 1.53125000 | 138 | | functions | 0.67187500 | 4746 | | item_discovery | 0.57812500 | 3921 | | graphs_items | 0.51562500 | 3773 | | graphs | 0.50000000 | 1561 | | auditlog_details | 0.42187500 | 2620 | | event_recovery | 0.32812500 | 2938 | | item_preproc | 0.29687500 | 3668 | | applications | 0.29687500 | 2345 | | application_template | 0.28125000 | 1989 | | sessions | 0.23437500 | 1064 | | hosts | 0.17187500 | 167 | | profiles | 0.15625000 | 564 | | sysmaps_elements | 0.15625000 | 95 | | mappings | 0.12500000 | 820 | | widget_field | 0.10937500 | 170 | | graph_discovery | 0.10937500 | 887 | | problem | 0.09375000 | 110 | | httptest | 0.07812500 | 41 | | item_condition | 0.07812500 | 446 | | trigger_depends | 0.07812500 | 401 | | sysmaps | 0.07812500 | 13 | | acknowledges | 0.06250000 | 207 | | escalations | 0.06250000 | 8 | | group_prototype | 0.06250000 | 7 | | event_suppress | 0.06250000 | 0 | | trigger_discovery | 0.06250000 | 648 | | sysmaps_links | 0.06250000 | 0 | | scripts | 0.06250000 | 3 | | hostmacro | 0.06250000 | 297 | | screens | 0.04687500 | 12 | | icon_mapping | 0.04687500 | 0 | | host_discovery | 0.04687500 | 2 | | opcommand_hst | 0.04687500 | 0 | | sysmap_user | 0.04687500 | 0 | | correlation | 0.04687500 | 0 | | screen_usrgrp | 0.04687500 | 0 | | icon_map | 0.04687500 | 0 | | opcommand_grp | 0.04687500 | 0 | | users_groups | 0.04687500 | 5 | | slideshow_user | 0.04687500 | 0 | | autoreg_host | 0.04687500 | 0 | | screen_user | 0.04687500 | 0 | | httptestitem | 0.04687500 | 123 | | opgroup | 0.04687500 | 0 | | slides | 0.04687500 | 0 | | rights | 0.04687500 | 4 | | dservices | 0.04687500 | 30 | | task | 0.04687500 | 0 | | media | 0.04687500 | 9 | | drules | 0.04687500 | 8 | | sysmap_element_trigger | 0.04687500 | 0 | | httpstepitem | 0.04687500 | 123 | | tag_filter | 0.04687500 | 0 | | services_links | 0.04687500 | 0 | | slideshows | 0.04687500 | 0 | | item_application_prototype | 0.04687500 | 23 | | hosts_templates | 0.04687500 | 263 | | maintenances_windows | 0.04687500 | 0 | | slideshow_usrgrp | 0.04687500 | 0 | | application_prototype | 0.04687500 | 23 | | hosts_groups | 0.04687500 | 211 | | sysmaps_link_triggers | 0.04687500 | 0 | | maintenances_hosts | 0.04687500 | 0 | | application_discovery | 0.04687500 | 8 | | interface | 0.04687500 | 82 | | service_alarms | 0.04687500 | 0 | | optemplate | 0.04687500 | 0 | | maintenances_groups | 0.04687500 | 0 | | dashboard_usrgrp | 0.04687500 | 0 | | opmessage_usr | 0.04687500 | 22 | | maintenances | 0.04687500 | 0 | | dashboard_user | 0.04687500 | 0 | | actions | 0.04687500 | 16 | | sysmap_usrgrp | 0.04687500 | 0 | | config | 0.04687500 | 0 | | opmessage_grp | 0.04687500 | 4 | | globalmacro | 0.03125000 | 0 | | usrgrp | 0.03125000 | 8 | | conditions | 0.03125000 | 30 | | opmessage | 0.03125000 | 30 | | corr_operation | 0.03125000 | 0 | | sysmap_url | 0.03125000 | 0 | | expressions | 0.03125000 | 10 | | proxy_history | 0.03125000 | 0 | | opcommand | 0.03125000 | 0 | | history_text | 0.03125000 | 22 | | sysmap_shape | 0.03125000 | 0 | | users | 0.03125000 | 3 | | httpstep | 0.03125000 | 41 | | httptest_field | 0.03125000 | 0 | | media_type | 0.03125000 | 11 | | proxy_dhistory | 0.03125000 | 0 | | operations | 0.03125000 | 32 | | sysmap_element_url | 0.03125000 | 0 | | hstgrp | 0.03125000 | 27 | | event_tag | 0.03125000 | 0 | | regexps | 0.03125000 | 5 | | history_log | 0.03125000 | 0 | | proxy_autoreg_host | 0.03125000 | 0 | | group_discovery | 0.03125000 | 0 | | opconditions | 0.03125000 | 0 | | services_times | 0.03125000 | 0 | | trigger_tag | 0.03125000 | 0 | | dhosts | 0.03125000 | 31 | | httpstep_field | 0.03125000 | 26 | | dchecks | 0.03125000 | 8 | | problem_tag | 0.03125000 | 0 | | services | 0.03125000 | 0 | | interface_discovery | 0.03125000 | 0 | | graph_theme | 0.03125000 | 4 | | corr_condition_group | 0.03125000 | 0 | | widget | 0.03125000 | 23 | | corr_condition | 0.03125000 | 0 | | screens_items | 0.03125000 | 51 | | valuemaps | 0.03125000 | 75 | | maintenance_tag | 0.03125000 | 0 | | dashboard | 0.03125000 | 3 | | task_remote_command_result | 0.01562500 | 0 | | task_remote_command | 0.01562500 | 0 | | opinventory | 0.01562500 | 0 | | task_close_problem | 0.01562500 | 0 | | corr_condition_tagvalue | 0.01562500 | 0 | | task_check_now | 0.01562500 | 0 | | corr_condition_tagpair | 0.01562500 | 0 | | task_acknowledge | 0.01562500 | 0 | | corr_condition_tag | 0.01562500 | 0 | | housekeeper | 0.01562500 | 0 | | dbversion | 0.01562500 | 0 | | ids | 0.01562500 | 52 | | globalvars | 0.01562500 | 0 | | timeperiods | 0.01562500 | 0 | | host_inventory | 0.01562500 | 20 | +----------------------------+---------------+------------+ 144 rows in set (0.02 sec)
二、查询所有数据库和索引的大小
mysql> select table_schema, concat(truncate(sum(data_length)/1024/1024,2),' mb') as data_size,concat(truncate(sum(index_length)/1024/1024,2),'mb') as index_size from information_schema.tables group by table_schema order by data_size desc; +--------------------+------------+------------+ | table_schema | data_size | index_size | +--------------------+------------+------------+ | zabbix | 8326.06 mb | 3031.85mb | | jumpserver | 26.57 mb | 8.21mb | | mysql | 2.49 mb | 0.21mb | | information_schema | 0.15 mb | 0.00mb | | sys | 0.01 mb | 0.00mb | | performance_schema | 0.00 mb | 0.00mb | +--------------------+------------+------------+ 6 rows in set (0.40 sec)
