☰

Cómo analizar el espacio utilizado por tablas, índices.

tag-icon • tag-icon mysql
Cómo analizar el espacio utilizado por tablas, índices.

Contamos con una base de datos que ha crecido orgánicamente durante el año pasado. ¿Cómo podemos analizar rápidamente el espacio utilizado por cada mesa? También nos gustaría considerar los índices.

Respuesta1

Cuando uso tablas innodb en mis bases de datos, me gusta usar elinnodb_file_per_tableconfiguración. Esto me permite tener una idea rápida de lo que está pasando con ls como el caos.sugirió.

Esta afirmación puede darle una idea bastante clara de cuánto espacio está utilizando.

use information_schema;
SELECT `TABLE_SCHEMA`, `TABLE_NAME`, `TABLE_ROWS`, `DATA_LENGTH`, 
       `INDEX_LENGTH`, `DATA_FREE` FROM `TABLES`

Respuesta2

¿Tengo algunas preguntas locas para que las utilices? Los escribí hace dos años y todavía los uso hoy. ¡Pruébalos!

Aquí hay una consulta para resumir todos los datos de todos los motores de almacenamiento.

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') AND
engine IS NOT NULL GROUP BY engine WITH ROLLUP) B,
(SELECT 3 pw) A ORDER BY TSize;

Aquí hay una consulta para resumir todos los datos de todas las bases de datos.

SELECT DBName,CONCAT(LPAD(FORMAT(SDSize/POWER(1024,pw),3),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Data Size",CONCAT(LPAD(FORMAT(SXSize/
POWER(1024,pw),3),17,' '),' ',SUBSTR(' KMGTP',pw+1,1),'B') "Index Size",
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')) AAA GROUP BY DB WITH ROLLUP) AA,
(SELECT 3 pw) BB ORDER BY (SDSize+SXSize);

Aquí hay una consulta para resumir todos los datos de todas las bases de datos agrupadas por motor de almacenamiento.

SELECT Statistic,DataSize "Data Size",IndexSize "Index Size",TableSize "Table Size"
FROM (SELECT IF(ISNULL(table_schema)=1,10,0) schema_score,
IF(ISNULL(engine)=1,10,0) engine_score,IF(ISNULL(table_schema)=1,
'ZZZZZZZZZZZZZZZZ',table_schema) schemaname,
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') TableSize
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') AND
engine IS NOT NULL GROUP BY table_schema,engine WITH ROLLUP) B,
(SELECT 3 pw) A) AA ORDER BY schemaname,schema_score,engine_score;

ADVERTENCIA: Notará que al final de todas estas consultas hay un SELECT en línea que se ve así:(SELECT 3 pw)

El número 3 hace que el informe salga en GigaBytes.
De hecho aquí hay una lista de los números y las unidades del informe que utiliza:

  • (SELECT 0 pw)Informes en bytes
  • (SELECT 1 pw)Informes en kilobytes
  • (SELECT 2 pw)Informes en Megabytes
  • (SELECT 3 pw)Informes en Gigabytes
  • (SELECT 4 pw)Informes en Terabytes
  • (SELECT 5 pw)Informes en Petabytes (nunca he usado esta configuración, pero está ahí en caso de que algún día alcances ese número)

Disfrutar !!!

Respuesta3

MySQL tiene algunas funciones integradas

use <datbase name>;
show table status;

Respuesta4

Intente utilizar una herramienta como esta para ver su instancia de MySQL en un formato fácil de leer:http://www.aquafold.com/index-mysql.html

información relacionada