如何在mysql中分析表空间使用情况

对，就是分析MySQL表空间。

先查表空间大小： sql SELECT table_name, ROUND((data_length+index_length)/1 02 4 /1 02 4 ,2 ) AS '总大小(MB)', ROUND(data_length/1 02 4 /1 02 4 ,2 ) AS '数据大小(MB)', ROUND(index_length/1 02 4 /1 02 4 ,2 ) AS '索引大小(MB)', table_rows AS '行数' FROM information_schema.TABLES WHERE table_schema = 'your_database_name' ORDER BY (data_length+index_length) DESC;
看哪些表太大，索引比例过高。

再查存储引擎分布： sql SELECT engine, ROUND(SUM(data_length+index_length)/1 02 4 /1 02 4 ,2 ) AS '总大小(MB)', COUNT() AS '表数量' FROM information_schema.TABLES WHERE table_schema = 'your_database_name' AND engine IS NOT NULL GROUP BY engine;
MyISAM想迁移InnoDB？
深度碎片检查： sql ANALYZE TABLE your_table_name; -
更新统计信息 SHOW TABLE STATUS LIKE 'your_table_name' G; -
碎片判断
表太大，用OPTIMIZE TABLE重建。

InnoDB分析： sql ls-lh /var/lib/mysql/your_database_name/.ibd; -
.ibd文件大小 SELECT file_name, total_extentsextent_size/1 02 4 /1 02 4 AS '总大小(MB)', used_extentsextent_size/1 02 4 /1 02 4 AS '已用(MB)' FROM information_schema.INNODB_SYS_DATAFILES;
共享表空间膨胀，考虑重建数据库。

定期监控： sql CREATE TABLE db_space_monitor ( check_time DATETIME, table_name VARCHAR(2 00), total_size_mb DECIMAL(1 0,2 ), data_size_mb DECIMAL(1 0,2 ), index_size_mb DECIMAL(1 0,2 ), PRIMARY KEY (check_time, table_name) );
-
定期采集脚本 INSERT INTO db_space_monitor SELECT NOW(), table_name, ROUND((data_length+index_length)/1 02 4 /1 02 4 ,2 ), ROUND(data_length/1 02 4 /1 02 4 ,2 ), ROUND(index_length/1 02 4 /1 02 4 ,2 ) FROM information_schema.TABLES WHERE table_schema = 'your_database_name';
有问题？优化索引，碎片处理，或升级存储引擎。

mysql表的data free过大

哎，你问我 MySQL 表 datafree 过大咋办... 我当时也懵，后来才反应过来，这事儿吧，原因挺多。

就说 2 02 2 年吧，我在上海这边搞项目，碰见过好几次。
一瞅，嚯，某个表 datafree 怪大的，比如有个 orders 表，突然就膨胀了，动不动就百分之四五十。

第一啊，就是表结构改了。
比如你删了个列，特别大的列，像 customer_photo 这种，几 MB 呢。
你直接 ALTER TABLE 删了，MySQL 老实说，它不一定会马上给你把那块地还回来。
我当时就见过，删了列，datafree 根本没少。
这时候你咋办？你只能用 OPTIMIZE TABLE orders; 这个命令。
它会重新整理一下，把没用的空间收回来。
这个操作可能要一会儿，看你表有多大。

第二啊，就是 insert delete 太频繁了。
特别是个子小的表，一天到晚增删数据。
比如有个 log 表，记录用户操作啥的，一天几万条。
你删几条，再加几条，时间长了，表里就全是坑坑洼洼的，datafree 就上去了。
这种情况，除了 OPTIMIZE TABLE，你还得定期 ANALYZE TABLE orders;。
这个命令主要是更新统计信息，让 MySQL 知道现在表啥情况，查询的时候能选个好点的路。
我一般隔几天或者一周跑一次。

第三，索引的事儿。
索引要是老变，老增删改，它也会膨胀。
比如 users 表，经常查 status 列，但这个 status 又老变。
你每次变，索引都得跟着变，可能就分裂了。
我有个表，索引都大了好几倍，datafree 也跟着涨。
这时候咋办？你看看那个索引还用不用，不用了，直接 DROP INDEX index_name ON users; 删了。
如果索引就是得用，但碎片太严重了，你就得重建。
可以先用 CREATE INDEX new_idx ON users(status); 建个新索引，然后 DROP INDEX index_name ON users; 删掉旧的。

第四啊，统计信息不准。
这个我理解得不太深，但好像也是原因。
统计信息不准，MySQL 就选不到最好的执行计划，读数据写数据都别扭，空间利用自然就差了。
解决方法嘛，还是 ANALYZE TABLE orders;。
有时候可能得 REPAIR TABLE orders;，这个命令好像也能修统计信息，还能修复轻微的数据损坏。

总之啊，排查 datafree 过大，你就从这四个方面看看。
先看表结构有没有变，再看增删改是不是太频繁，再看索引行不行，最后看看统计信息。
找到原因，用对命令，一般问题就解决了。
数据库空间嘛，还是得好好管理。