在使用云数据库时，如aws rds、阿里云rds等等，没有提供系统级别的权限，要想获知数据库和表的大小就只好通过查询语句来了。
查看MySQL数据库大小
|
1
|
SELECT table_schema “Database Name”, sum( data_length + index_length ) / 1024 / 1024 “Database Size in MB” FROM information_schema.TABLES GROUP BY table_schema;
|
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
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表大小
|
1
2
3
4
|
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;
|
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
|
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的表大小
|
1
2
3
4
5
6
7
8
9
|
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;
|
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
|
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)
|
文章转载来自：ttlsa.com
