MySQL 데이터베이스의 테이블 크기를 얻는 방법은 무엇입니까?

MySQL 데이터베이스의 테이블 크기를 얻는 방법은 무엇입니까?

---

이 쿼리를 실행하여 MySQL 데이터베이스에있는 모든 테이블의 크기를 가져올 수 있습니다.

show table status from myDatabaseName;

결과를 이해하는 데 도움이 필요합니다. 크기가 가장 큰 테이블을 찾고 있습니다.

어떤 열을 봐야합니까?

---

이 쿼리를 사용하여 테이블의 크기를 표시 할 수 있습니다 (먼저 변수를 대체해야 함).

SELECT 
    table_name AS `Table`, 
    round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB` 
FROM information_schema.TABLES 
WHERE table_schema = "$DB_NAME"
    AND table_name = "$TABLE_NAME";

또는 다음 쿼리를 통해 모든 데이터베이스의 모든 테이블 크기를 나열합니다.

SELECT 
     table_schema as `Database`, 
     table_name AS `Table`, 
     round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB` 
FROM information_schema.TABLES 
ORDER BY (data_length + index_length) DESC;

---

SELECT TABLE_NAME AS "Table Name", 
table_rows AS "Quant of Rows", ROUND( (
data_length + index_length
) /1024, 2 ) AS "Total Size Kb"
FROM information_schema.TABLES
WHERE information_schema.TABLES.table_schema = 'YOUR SCHEMA NAME/DATABASE NAME HERE'
LIMIT 0 , 30

" information_schema "-> SCHEMATA 테이블-> " SCHEMA_NAME "열 에서 스키마 이름을 가져올 수 있습니다.

---

추가 다음과 같이 mysql 데이터베이스의 크기를 얻을 수 있습니다 .

SELECT table_schema "DB Name", 
Round(Sum(data_length + index_length) / 1024 / 1024, 1) "DB Size in MB" 
FROM   information_schema.tables 
GROUP  BY table_schema; 

결과

DB Name              |      DB Size in MB

mydatabase_wrdp             39.1
information_schema          0.0

여기에서 추가 세부 정보를 얻을 수 있습니다 .

---

SELECT 
    table_name AS "Table",  
    round(((data_length + index_length) / 1024 / 1024), 2) as size   
FROM information_schema.TABLES  
WHERE table_schema = "YOUR_DATABASE_NAME"  
ORDER BY size DESC; 

이렇게하면 크기가 정렬됩니다 (DB 크기 (MB)).

---

쿼리에서 현재 선택한 데이터베이스를 사용하려는 경우. 이 쿼리를 복사하여 붙여 넣으십시오. (수정 필요 없음)

SELECT table_name ,
  round(((data_length + index_length) / 1024 / 1024), 2) as SIZE_MB
FROM information_schema.TABLES
WHERE table_schema = DATABASE() ORDER BY SIZE_MB DESC;

---

Workbench를 사용하여 많은 정보를 쉽게 얻을 수있는 방법이 있습니다.

• 스키마 이름을 마우스 오른쪽 단추로 클릭하고 "Schema inspector"를 클릭하십시오.

• 결과 창에는 여러 탭이 있습니다. 첫 번째 탭 "정보"에는 대략적인 데이터베이스 크기 (MB)가 표시됩니다.

• 두 번째 탭인 "테이블"에는 각 테이블의 데이터 길이 및 기타 세부 정보가 표시됩니다.

---

데이터베이스 이름이 "news_alert" 라고 가정합니다 . 그런 다음이 쿼리는 데이터베이스에있는 모든 테이블의 크기를 표시합니다.

모든 테이블의 크기 :

SELECT
  TABLE_NAME AS `Table`,
  ROUND(((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024),2) AS `Size (MB)`
FROM
  information_schema.TABLES
WHERE
  TABLE_SCHEMA = "news_alert"
ORDER BY
  (DATA_LENGTH + INDEX_LENGTH)
DESC;

산출:

    +---------+-----------+
    | Table   | Size (MB) |
    +---------+-----------+
    | news    |      0.08 |
    | keyword |      0.02 |
    +---------+-----------+
    2 rows in set (0.00 sec)

특정 테이블의 경우 :

SELECT
  TABLE_NAME AS `Table`,
  ROUND(((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024),2) AS `Size (MB)`
FROM
  information_schema.TABLES
WHERE
    TABLE_SCHEMA = "news_alert"
  AND
    TABLE_NAME = "news"
ORDER BY
  (DATA_LENGTH + INDEX_LENGTH)
DESC;

산출:

+-------+-----------+
| Table | Size (MB) |
+-------+-----------+
| news  |      0.08 |
+-------+-----------+
1 row in set (0.00 sec)

---

다음 셸 명령을 시도합니다 ( DB_NAME데이터베이스 이름으로 대체 ).

mysql -uroot <<<"SELECT table_name AS 'Tables', round(((data_length + index_length) / 1024 / 1024), 2) 'Size in MB' FROM information_schema.TABLES WHERE table_schema = \"DB_NAME\" ORDER BY (data_length + index_length) DESC;" | head

Drupal / drush 솔루션의 경우 사용중인 가장 큰 테이블을 표시하는 다음 예제 스크립트를 확인하십시오.

#!/bin/sh
DB_NAME=$(drush status --fields=db-name --field-labels=0 | tr -d '\r\n ')
drush sqlq "SELECT table_name AS 'Tables', round(((data_length + index_length) / 1024 / 1024), 2) 'Size in MB' FROM information_schema.TABLES WHERE table_schema = \"${DB_NAME}\" ORDER BY (data_length + index_length) DESC;" | head -n20

---

If you are using phpmyadmin then just go to the table structure

e.g.

Space usage
Data    1.5 MiB
Index   0   B
Total   1.5 Mi

---

Heres another way of working this out from using the bash command line.

for i in mysql -NB -e 'show databases'; do echo $i; mysql -e "SELECT table_name AS 'Tables', round(((data_length+index_length)/1024/1024),2) 'Size in MB' FROM information_schema.TABLES WHERE table_schema =\"$i\" ORDER BY (data_length + index_length) DESC" ; done

---

Adapted from ChapMic's answer to suite my particular need.

Only specify your database name, then sort all the tables in descending order - from LARGEST to SMALLEST table inside selected database. Needs only 1 variable to be replaced = your database name.

SELECT 
table_name AS `Table`, 
round(((data_length + index_length) / 1024 / 1024), 2) AS `size`
FROM information_schema.TABLES 
WHERE table_schema = "YOUR_DATABASE_NAME_HERE"
ORDER BY size DESC;

---

Another way of showing the number of rows and space occupied and ordering by it.

SELECT
     table_schema as `Database`,
     table_name AS `Table`,
     table_rows AS "Quant of Rows",
     round(((data_length + index_length) / 1024 / 1024/ 1024), 2) `Size in GB`
FROM information_schema.TABLES
WHERE table_schema = 'yourDatabaseName'
ORDER BY (data_length + index_length) DESC;  

The only string you have to substitute in this query is "yourDatabaseName".

---

If you have ssh access, you might want to simply try du -hc /var/lib/mysql (or different datadir, as set in your my.cnf) as well.

---

Calculate the total size of the database at the end:

(SELECT 
  table_name AS `Table`, 
  round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB` 
  FROM information_schema.TABLES 
  WHERE table_schema = "$DB_NAME"
)
UNION ALL
(SELECT 
  'TOTAL:',
  SUM(round(((data_length + index_length) / 1024 / 1024), 2) )
  FROM information_schema.TABLES 
  WHERE table_schema = "$DB_NAME"
)

---

SELECT TABLE_NAME AS table_name, 
table_rows AS QuantofRows, 
ROUND((data_length + index_length) /1024, 2 ) AS total_size_kb 
FROM information_schema.TABLES
WHERE information_schema.TABLES.table_schema = 'db'
ORDER BY (data_length + index_length) DESC; 

all 2 above is tested on mysql

---

I find the existing answers don't actually give the size of tables on the disk, which is more helpful. This query gives more accurate disk estimate compared to table size based on data_length & index. I had to use this for an AWS RDS instance where you cannot physically examine the disk and check file sizes.

select NAME as TABLENAME,FILE_SIZE/(1024*1024*1024) as ACTUAL_FILE_SIZE_GB
, round(((data_length + index_length) / 1024 / 1024/1024), 2) as REPORTED_TABLE_SIZE_GB 
from INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES s
join INFORMATION_SCHEMA.TABLES t 
on NAME = Concat(table_schema,'/',table_name)
order by FILE_SIZE desc

---

this should be tested in mysql, not postgresql

SELECT table_schema, # "DB Name", 
Round(Sum(data_length + index_length) / 1024 / 1024, 1) # "DB Size in MB" 
FROM   information_schema.tables 
GROUP  BY table_schema; 

참고URL : https://stackoverflow.com/questions/9620198/how-to-get-the-sizes-of-the-tables-of-a-mysql-database