Как найти самые большие базы данных и самые большие таблицы MySQL

Иногда на хостинге или на веб-сервере может закончится место из-за того, что в таблицах MySQL слишком много данных. Иногда эти данные можно безболезненно удалить, если там хранятся логи посещений или статистика.

Если баз данных и таблиц много, то найти самую большую из них может быть непростой задачей.

Описанные в этой заметке способы подходят как для phpMyAdmin, так и при подключении к MySQL в консоли.

Вы можете найти самую большую базу данных, самую большую таблицу в определённой базе данных, либо самую большую таблицу среди всех баз данных — в данной заметке описаны все эти варианты.

Как найти самые большие базы данных MySQL

Вывод баз данных с сортировкой по их общему объёму:

SELECT DBName,CONCAT(LPAD(FORMAT(SDSize/POWER(1024,pw),3),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "DataSize",
CONCAT(LPAD(FORMAT(SXSize/POWER(1024,pw),3),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "IndexSize",
CONCAT(LPAD(FORMAT(STSize/POWER(1024,pw),3),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Total Size"
FROM (SELECT IFNULL(DB,'All Databases') DBName,SUM(DSize) SDSize,
SUM(XSize) SXSize,SUM(TSize) STSize
FROM (SELECT table_schema DB,data_length DSize,index_length XSize, 
data_length+index_length TSize FROM information_schema.tables WHERE
table_schema NOT IN ('mysql','information_schema','performance_schema')) AAA
GROUP BY DB WITH ROLLUP) AA,(SELECT 2 pw) BB ORDER BY (SDSize+SXSize);

Показ общего объёма занимаемой памяти по механизмам (движкам) хранения:

SELECT IFNULL(B.engine,'Total') "Storage Engine",
CONCAT(LPAD(REPLACE(FORMAT(B.DSize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Data Size",
CONCAT(LPAD(REPLACE(FORMAT(B.ISize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Index Size",
CONCAT(LPAD(REPLACE(FORMAT(B.TSize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Table Size" FROM
(SELECT engine,SUM(data_length) DSize,SUM(index_length) ISize,
SUM(data_length+index_length) TSize FROM information_schema.tables
WHERE table_schema NOT IN ('mysql','information_schema','performance_schema')
AND engine IS NOT NULL GROUP BY engine WITH ROLLUP) B,(SELECT 2 pw) A ORDER BY TSize;

Показ количество занимаемого места на диске и механизма хранения:

SELECT IF(ISNULL(B.table_schema)+ISNULL(B.engine)=2,
"Storage for All Databases",IF(ISNULL(B.table_schema)+ISNULL(B.engine)=1,
CONCAT("Storage for ",B.table_schema),
CONCAT(B.engine," Tables for ",B.table_schema))) Statistic,
CONCAT(LPAD(REPLACE(FORMAT(B.DSize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "DataSize",
CONCAT(LPAD(REPLACE(FORMAT(B.ISize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "IndexSize",
CONCAT(LPAD(REPLACE(FORMAT(B.TSize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Table Size" FROM
(SELECT table_schema,engine,SUM(data_length) DSize,
SUM(index_length) ISize,SUM(data_length+index_length) TSize
FROM information_schema.tables WHERE table_schema NOT IN
('mysql','information_schema','performance_schema') AND
engine IS NOT NULL GROUP BY table_schema,engine WITH ROLLUP) B,
(SELECT 2 pw) A ORDER BY TSize;

Обратите внимание на то, что во всех запросах вы видите подзапрос, который выглядит следующим образом: (SELECT 2 pw)

• (SELECT 0 pw) представляет размеры базы данных в байтах
• (SELECT 1 pw) представить размеры базы данных в килобайтах
• (SELECT 2 pw) представить размеры базы данных в мегабайтах
• (SELECT 3 pw) представить размеры базы данных в гигабайтах
• (SELECT 4 pw) представить размеры базы данных в терабайтах
• (SELECT 5 pw) представьте размеры базы данных в петабайтах

Ещё один вариант команды, он должен дать вам размер данных и индексов ваших баз данных (смотрит только MyISAM или InnoDB):

SELECT table_schema 'database', 
 concat( round( sum( data_length + index_length ) / ( 1024 *1024 ) , 2 ) , 'M' ) size 
FROM information_schema.TABLES
WHERE ENGINE=('MyISAM' || 'InnoDB' ) 
GROUP BY table_schema 
ORDER BY size ASC;

Обратите внимание, что это не обязательно размер на диске, особенно при использовании InnoDB. Ваши файлы ibdata (и отдельные файлы *.idb при использовании параметра innodb_file_per_table) постоянно растут, но не сжимаются автоматически.

Чтобы получить размер файла на диске, если вы используете Linux:

du -s /ПУТЬ/ДО//ДИРЕКТОРИИ/mysql/data | sort -n

Как найти самые большие таблицы MySQL

Вы можете использовать этот запрос, чтобы показать размер таблицы (хотя сначала вам нужно заменить переменные $DB_NAME — имя базы данных: и $TABLE_NAME — имя таблицы):

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;