특정 MySQL 테이블이 차지하는 디스크 공간을 어떻게 확인할 수 있습니까?

특정 MySQL 테이블이 차지하는 디스크 공간을 어떻게 확인할 수 있습니까?

---

특정 MySQL 테이블이 차지하는 디스크 공간을 결정하는 빠른 방법이 있습니까? 테이블은 MyISAM 또는 Innodb 일 수 있습니다.

---

테이블의 mydb.mytable경우 다음을 위해 이것을 실행하십시오.

바이트

SELECT (data_length+index_length) tablesize
FROM information_schema.tables
WHERE table_schema='mydb' and table_name='mytable';

킬로바이트

SELECT (data_length+index_length)/power(1024,1) tablesize_kb
FROM information_schema.tables
WHERE table_schema='mydb' and table_name='mytable';

메가 바이트

SELECT (data_length+index_length)/power(1024,2) tablesize_mb
FROM information_schema.tables
WHERE table_schema='mydb' and table_name='mytable';

기가 바이트

SELECT (data_length+index_length)/power(1024,3) tablesize_gb
FROM information_schema.tables
WHERE table_schema='mydb' and table_name='mytable';

일반적인

다음은 최대 단위 표시가 TB (TeraBytes) 인 일반 쿼리입니다.

SELECT 
    CONCAT(FORMAT(DAT/POWER(1024,pw1),2),' ',SUBSTR(units,pw1*2+1,2)) DATSIZE,
    CONCAT(FORMAT(NDX/POWER(1024,pw2),2),' ',SUBSTR(units,pw2*2+1,2)) NDXSIZE,
    CONCAT(FORMAT(TBL/POWER(1024,pw3),2),' ',SUBSTR(units,pw3*2+1,2)) TBLSIZE
FROM
(
    SELECT DAT,NDX,TBL,IF(px>4,4,px) pw1,IF(py>4,4,py) pw2,IF(pz>4,4,pz) pw3
    FROM 
    (
        SELECT data_length DAT,index_length NDX,data_length+index_length TBL,
        FLOOR(LOG(IF(data_length=0,1,data_length))/LOG(1024)) px,
        FLOOR(LOG(IF(index_length=0,1,index_length))/LOG(1024)) py,
        FLOOR(LOG(IF(data_length+index_length=0,1,data_length+index_length))/LOG(1024)) pz
        FROM information_schema.tables
        WHERE table_schema='mydb'
        AND table_name='mytable'
    ) AA
) A,(SELECT 'B KBMBGBTB' units) B;

시도 해봐 !!!

---

상위 20 개의 가장 큰 테이블을 MB 단위로 가져 오는 빠른 SQL

SELECT table_schema, table_name,
  ROUND((data_length+index_length)/POWER(1024,2),2) AS tablesize_mb
FROM information_schema.tables
ORDER BY tablesize_mb DESC LIMIT 20;

누군가에게 도움이 되길 바랍니다!

---

이것은 InnoDB 테이블에는 정확하지 않습니다. 디스크의 크기는 실제로 쿼리를 통해보고 된 크기보다 큽니다.

자세한 내용은 Percona의이 링크를 참조하십시오.

http://www.mysqlperformanceblog.com/2008/12/16/how-much-space-does-empty-innodb-table-take/

---

기본적으로 mysql이 설치된 Linux에서 :

[you@yourbox]$ ls -lha /var/lib/mysql/<databasename>

NIXCRAFT의 mysql db 위치 에 기반

---

Based on the RolandMySQLDBA's answer I think we can use the above to get the size of each schema in a table:

SELECT table_schema, SUM((data_length+index_length)/power(1024,1)) tablesize_kb 
    FROM information_schema.tables GROUP BY table_schema;

Really liked it!

---

You could perhaps look at the size of the files...

Each table is stored in a couple of separate files inside a folder that is named whatever you called your database. These folders are stored within the mysql data directory.

From there you can do a 'du -sh .*' to get the size of the table on disk.

---

Taken from How do I check how much disk space my database is using?

You can check MySQL table size either by looking at phpMyAdmin in your control panel by clicking on the database name in the left frame and reading the size for the tables in there in the right frame.

The below query will as well help to get the same information in bytes

select SUM(data_length) + SUM(index_length) as total_size 
from information_schema.tables 
where table_schema = 'db_name' 
and table_name='table_name';

---

I would just use 'mysqldiskusage' tool as follow

$ mysqldiskusage --server=user:password@localhost mydbname
# Source on localhost: ... connected.

# Database totals:
+------------+----------------+
| db_name    |         total  |
+------------+----------------+
| mydbaname  | 5,403,033,600  |
+------------+----------------+

Total database disk usage = 5,403,033,600 bytes or 5.03 GB

참고URL : https://stackoverflow.com/questions/6474591/how-can-you-determine-how-much-disk-space-a-particular-mysql-table-is-taking-up