Mysql常用命令

开发调优
,
1 
SELECT table_schema AS "数据库名称",
       ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS "数据库大小(MB)"
FROM information_schema.tables
GROUP BY table_schema
ORDER BY SUM(data_length + index_length) DESC;

image
image664×392 10 KB

2 
SELECT 
  table_name AS '表名',
  ROUND(((data_length + index_length) / 1024 / 1024), 2) AS '总大小(MB)',
  ROUND((data_length / 1024 / 1024), 2) AS '数据大小(MB)',
  ROUND((index_length / 1024 / 1024), 2) AS '索引大小(MB)',
  table_rows AS '大概行数'
FROM information_schema.tables 
WHERE table_schema = 'your_database_name'  -- 替换为你的数据库名
ORDER BY (data_length + index_length) DESC; -- 按表占用空间从大到小排序

用于看某个库中所有表的占用空间大小

3 
OPTIMIZE TABLE your_table;

压缩空间

4

如果在表执行压缩空间失败时可以尝试类似下面的。

ALTER TABLE applog ENGINE=InnoDB, ALGORITHM=INPLACE, LOCK=NONE;