1. 项目概述在日常数据库运维工作中了解数据库中各业务库及表的大小分布是DBA和开发人员的基础需求。通过分析库表大小我们可以及时发现异常增长的表合理规划存储资源优化备份策略评估分库分表必要性MySQL提供了多种SQL命令可以精确获取这些信息本文将详细介绍5种实用方法及其适用场景。2. 核心SQL命令解析2.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;执行结果示例----------------------------- | 数据库 | 大小(MB)| ----------------------------- | ecommerce_db | 2456.33 | | user_center | 1234.56 | | mysql | 45.67 | | information_schema | 0.00 | -----------------------------参数说明data_length数据部分占用空间index_length索引部分占用空间/1024/1024将字节转换为MBROUND(...,2)保留两位小数2.2 查看指定数据库中各表大小SELECT table_name AS 表名, ROUND(data_length/1024/1024, 2) AS 数据大小(MB), ROUND(index_length/1024/1024, 2) AS 索引大小(MB), ROUND((data_length 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;实际案例分析电商数据库发现订单历史表异常增长------------------------------------------------------------------------ | 表名 | 数据大小(MB) | 索引大小(MB) | 总大小(MB) | 行数 | ------------------------------------------------------------------------ | order_history | 1560.22 | 320.11 | 1880.33 | 5800000 | | products | 420.45 | 150.67 | 571.12 | 12000 | | users | 85.33 | 62.45 | 147.78 | 350000 | ------------------------------------------------------------------------2.3 查看表空间物理文件大小对于InnoDB引擎可通过文件系统直接查看SELECT file_name, ROUND(bytes/1024/1024,2) AS size_mb FROM information_schema.files WHERE file_name LIKE %ibd ORDER BY bytes DESC LIMIT 10;2.4 使用存储过程批量检查创建自动化检查存储过程DELIMITER // CREATE PROCEDURE analyze_db_sizes() BEGIN DECLARE done INT DEFAULT FALSE; DECLARE db_name VARCHAR(64); DECLARE db_cursor CURSOR FOR SELECT schema_name FROM information_schema.schemata; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done TRUE; CREATE TEMPORARY TABLE IF NOT EXISTS temp_db_sizes ( db_name VARCHAR(64), size_mb DECIMAL(10,2) ); OPEN db_cursor; read_loop: LOOP FETCH db_cursor INTO db_name; IF done THEN LEAVE read_loop; END IF; SET sql CONCAT(INSERT INTO temp_db_sizes SELECT , db_name, , ROUND(SUM(data_length index_length)/1024/1024,2) FROM information_schema.tables WHERE table_schema , db_name, ); PREPARE stmt FROM sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; END LOOP; CLOSE db_cursor; SELECT * FROM temp_db_sizes ORDER BY size_mb DESC; DROP TEMPORARY TABLE temp_db_sizes; END // DELIMITER ; -- 执行存储过程 CALL analyze_db_sizes();2.5 监控表大小变化趋势创建历史记录表定期快照CREATE TABLE IF NOT EXISTS table_size_history ( id INT AUTO_INCREMENT PRIMARY KEY, check_date DATETIME DEFAULT CURRENT_TIMESTAMP, table_schema VARCHAR(64), table_name VARCHAR(64), data_mb DECIMAL(10,2), index_mb DECIMAL(10,2), total_mb DECIMAL(10,2), rows_count BIGINT ); -- 定期执行此语句记录快照 INSERT INTO table_size_history (table_schema, table_name, data_mb, index_mb, total_mb, rows_count) SELECT table_schema, table_name, ROUND(data_length/1024/1024, 2), ROUND(index_length/1024/1024, 2), ROUND((data_length index_length)/1024/1024, 2), table_rows FROM information_schema.tables WHERE table_schema NOT IN (information_schema, performance_schema, mysql, sys);3. 高级应用技巧3.1 识别碎片化严重的表SELECT table_schema, table_name, ROUND(data_free/1024/1024,2) AS free_space_mb, ROUND((data_free/(data_lengthindex_length))*100,2) AS frag_ratio FROM information_schema.tables WHERE data_free 100*1024*1024 -- 大于100MB的碎片 AND (data_length index_length) 500*1024*1024 -- 表总大小大于500MB ORDER BY frag_ratio DESC LIMIT 10;优化建议碎片率30%的表建议执行OPTIMIZE TABLE业务低峰期操作大表建议使用pt-online-schema-change工具3.2 预测表增长趋势SELECT h1.table_schema, h1.table_name, h1.total_mb AS current_size, h2.total_mb AS prev_size, h1.check_date, h2.check_date, ROUND((h1.total_mb - h2.total_mb)/DATEDIFF(h1.check_date, h2.check_date),2) AS growth_mb_per_day FROM table_size_history h1 JOIN table_size_history h2 ON h1.table_schema h2.table_schema AND h1.table_name h2.table_name AND h1.id (SELECT MAX(id) FROM table_size_history) AND h2.id (SELECT MAX(id)-1 FROM table_size_history) WHERE h1.total_mb h2.total_mb ORDER BY growth_mb_per_day DESC;3.3 自动化监控脚本#!/bin/bash # 数据库连接配置 DB_HOSTlocalhost DB_USERmonitor_user DB_PASSsecure_password DB_PORT3306 # 执行SQL并输出CSV报告 mysql -h$DB_HOST -u$DB_USER -p$DB_PASS -P$DB_PORT -N -e SELECT CONCAT(table_schema,.,table_name) AS object, ROUND(data_length/1024/1024,2) AS data_mb, ROUND(index_length/1024/1024,2) AS index_mb, ROUND((data_lengthindex_length)/1024/1024,2) AS total_mb, table_rows FROM information_schema.tables WHERE table_schema NOT IN (information_schema,performance_schema,mysql,sys) ORDER BY (data_length index_length) DESC; /var/log/mysql_size_report_$(date %Y%m%d).csv # 发送邮件通知 mail -s MySQL Storage Report - $(date) dba-teamcompany.com /var/log/mysql_size_report_$(date %Y%m%d).csv4. 常见问题解决方案4.1 查询结果不准确现象table_rows字段值与实际行数差异大大小统计与磁盘占用不一致原因与解决InnoDB的行数是估值执行ANALYZE TABLE更新统计信息ANALYZE TABLE your_table_name;包含未统计的BLOB/TEXT大字段需单独计算SELECT SUM(LENGTH(blob_column))/1024/1024 AS blob_size_mb FROM your_table_name;4.2 权限问题错误示例ERROR 1142 (42000): SELECT command denied to user app_user% for table tables解决方案创建专用监控账号CREATE USER db_monitor% IDENTIFIED BY complex_password; GRANT SELECT ON information_schema.* TO db_monitor%; FLUSH PRIVILEGES;4.3 大数据库查询超时优化方案分批查询SELECT ... FROM information_schema.tables WHERE table_schema IN (db1,db2) LIMIT 10;使用MySQL Shell并行查询util.parallelTables(information_schema.tables, SELECT * FROM information_schema.tables WHERE table_schema?, [db1,db2]);5. 性能优化建议定期维护计划每周执行一次ANALYZE TABLE更新统计信息每月对碎片率20%的表执行优化存储引擎选择-- 检查表的存储引擎分布 SELECT engine, COUNT(*) AS table_count, ROUND(SUM(data_length index_length)/1024/1024,2) AS total_mb FROM information_schema.tables WHERE table_schema NOT IN (information_schema,mysql,performance_schema,sys) GROUP BY engine;分区表特殊处理-- 查看分区表各分区大小 SELECT partition_name, ROUND(data_length/1024/1024,2) AS data_mb, ROUND(index_length/1024/1024,2) AS index_mb FROM information_schema.partitions WHERE table_schema your_db AND table_name your_partitioned_table;监控指标阈值建议指标警告阈值严重阈值应对措施单表大小50GB100GB考虑分表日增长量5GB10GB检查数据归档策略索引占比60%80%索引优化碎片率30%50%立即优化