mysql的一些命令行操作指令

#查看所有数据库大小
SELECT table_schema AS 'Database',
    SUM(data_length + index_length) / 1024 / 1024 AS 'Total_size_MB'
FROM information_schema.tables
GROUP BY table_schema;
#查看所有数据库表空间大小
SELECT table_schema AS 'Database',
       table_name AS 'Table',
       round(((data_length + index_length) / 1024 / 1024), 2) AS 'Size_MB'
FROM information_schema.tables
ORDER BY data_length + index_length DESC;
#查看某个库所有表空间大小
SELECT table_name AS 'Table',
       round(((data_length + index_length) / 1024 / 1024), 2) AS 'Size_MB'
FROM information_schema.tables
WHERE table_schema = '填数据库名'
ORDER BY data_length + index_length DESC;
#查看数据库表空间碎片大小
SELECT
    table_schema AS `Database`,
    table_name AS `Table`,
    round(((data_length + index_length) / 1024 / 1024), 2) AS `Size (MB)`,
    round((data_free / 1024 / 1024), 2) AS `Free Space (MB)`
FROM
    information_schema.TABLES
WHERE
    table_schema='填数据库名';
#这条语句将重组表并释放未使用的空间，有助于提高表的性能并减少碎片
OPTIMIZE TABLE your_table_name;