查找数据库大小和表大小

在使用云数据库时，如aws rds、阿里云rds等等，没有提供系统级别的权限，要想获知数据库和表的大小就只好通过查询语句来了。

查看MySQL数据库大小

SELECT table_schema “Database Name”, sum( data_length + index_length ) / 1024 / 1024 “Database Size in MB” FROM information_schema.TABLES GROUP BY table_schema;

mysql> SELECT table_schema “Database Name”, sum( data_length + index_length ) / 1024 / 1024 “Database Size in MB” FROM information_schema.TABLES GROUP BY table_schema;
+——————–+———————+
| Database Name      | Database Size in MB |
+——————–+———————+
| dev_xian7_cn       |          0.06250000 |
| fuwu_ttlsa_com     |          0.80861568 |
| information_schema |          0.00781250 |
| main               |          0.12500000 |
| mysql              |          0.63574028 |
| my_xian7_cn        |          5.06250000 |
| www_ttlsa_com      |         81.73545456 |
| www_ttmark_com     |         31.87838650 |
| wx                 |          0.00613022 |
| zhifu_xian7_cn     |          1.95312500 |
+——————–+———————+
10 rows in set (1.04 sec)

查看MySQL表大小

SELECT table_name AS “Tables”,round(((data_length + index_length) / 1024 / 1024), 2) “Size in MB”
FROM information_schema.TABLES
WHERE table_schema = “www_ttlsa_com”
ORDER BY (data_length + index_length) DESC;

mysql> SELECT table_name AS “Tables”,round(((data_length + index_length) / 1024 / 1024), 2) “Size in MB”
    -> FROM information_schema.TABLES
    -> WHERE table_schema = “www_ttlsa_com”
    -> ORDER BY (data_length + index_length) DESC;
+———————————-+————+
| Tables                           | Size in MB |
+———————————-+————+
| ttlsa_postmeta                   |      29.74 |
| ttlsa_posts                      |      25.04 |
| ttlsa_redirection_404            |      15.30 |
| ttlsa_sph_stats                  |       2.53 |
| ttlsa_options                    |       2.53 |
| ttlsa_comments                   |       1.44 |
| ttlsa_commentmeta                |       1.17 |
| ttlsa_redirection_logs           |       1.11 |
| ttlsa_usermeta                   |       0.75 |
| ttlsa_cst_files                  |       0.70 |
| ttlsa_term_relationships         |       0.38 |
| ttlsa_redirection_items          |       0.30 |
| ttlsa_terms                      |       0.28 |
| ttlsa_term_taxonomy              |       0.19 |
| ttlsa_post_views_realtime        |       0.07 |
| ttlsa_wp_rp_tags                 |       0.06 |
| ttlsa_users                      |       0.04 |
| ttlsa_ez_adsense_options         |       0.04 |
| ttlsa_post_views_history         |       0.03 |
| ttlsa_links                      |       0.01 |
| ttlsa_pollsip                    |       0.00 |
| ttlsa_redirection_groups         |       0.00 |
| ttlsa_redirection_modules        |       0.00 |
| ttlsa_termmeta                   |       0.00 |
| ttlsa_woocommerce_order_itemmeta |       0.00 |
| ttlsa_post_views_summary         |       0.00 |
| ttlsa_mobilepress                |       0.00 |
| ttlsa_pollsa                     |       0.00 |
| ttlsa_ahm_download_stats         |       0.00 |
| ttlsa_pollsq                     |       0.00 |
| ahm_files                        |       0.00 |
| ttlsa_sph_counter                |       0.00 |
| ttlsa_wpo_campaign_category      |       0.00 |
| ttlsa_gravatars                  |       0.00 |
| ttlsa_woocommerce_order_items    |       0.00 |
| ttlsa_nggv_votes                 |       0.00 |
| ttlsa_wpo_campaign_word          |       0.00 |
| ttlsa_seo_title_tag_tag          |       0.00 |
| ttlsa_nggv_settings              |       0.00 |
| ttlsa_wpo_campaign_post          |       0.00 |
| ttlsa_wpo_campaign_feed          |       0.00 |
+———————————-+————+
41 rows in set (0.00 sec)

找出前10的表大小

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 10;

mysql> 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 10;
+—————————————+——-+——-+——-+————+———+
| CONCAT(table_schema, ‘.’, table_name) | rows  | DATA  | idx   | total_size | idxfrac |
+—————————————+——-+——-+——-+————+———+
| www_ttlsa_com.ttlsa_postmeta          | 0.01M | 0.02G | 0.01G | 0.03G      |    0.65 |
| www_ttlsa_com.ttlsa_posts             | 0.00M | 0.01G | 0.02G | 0.02G      |    1.66 |
| www_ttmark_com.tm_posts               | 0.01M | 0.01G | 0.01G | 0.02G      |    0.57 |
| www_ttlsa_com.ttlsa_redirection_404   | 0.01M | 0.01G | 0.01G | 0.01G      |    0.83 |
| www_ttmark_com.tm_postmeta            | 0.10M | 0.01G | 0.00G | 0.01G      |    0.47 |
| my_xian7_cn.tbl_pp_user               | 0.01M | 0.00G | 0.00G | 0.00G      |    0.00 |
| www_ttlsa_com.ttlsa_sph_stats         | 0.03M | 0.00G | 0.00G | 0.00G      |    0.95 |
| www_ttlsa_com.ttlsa_options           | 0.00M | 0.00G | 0.00G | 0.00G      |    0.02 |
| zhifu_xian7_cn.tbl_pay_trade          | 0.00M | 0.00G | 0.00G | 0.00G      |    0.13 |
| my_xian7_cn.t_user                    | 0.00M | 0.00G | 0.00G | 0.00G      |    0.00 |
+—————————————+——-+——-+——-+————+———+
10 rows in set (0.20 sec)