马上注册，结交更多好友，享用更多功能，让你轻松玩转社区。
您需要 登录 才可以下载或查看，没有账号？立即注册
x
各类数据库优化，抓大重点整改，快速优化，起首分析各数据库查询全部表的空间巨细及记录条数的语句：
MySQL
-- 检察所有表的空间巨细
SELECT
TABLE_SCHEMA AS `数据库名`,
TABLE_NAME AS `表名`,
ENGINE AS `存储引擎`,
CONCAT(ROUND(DATA_LENGTH/1024/1024, 2), ' MB') AS `数据巨细`,
CONCAT(ROUND(INDEX_LENGTH/1024/1024, 2), ' MB') AS `索引巨细`,
CONCAT(ROUND((DATA_LENGTH+INDEX_LENGTH)/1024/1024, 2), ' MB') AS `总巨细`
FROM
INFORMATION_SCHEMA.TABLES;
-- 检察所有表的记录条数
SELECT TABLE_NAME, (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = t.TABLE_NAME) AS `记录数`
FROM INFORMATION_SCHEMA.TABLES t;
Oracle
-- 检察所有表的空间巨细（需有相应权限）
SELECT
OWNER AS `模式名`,
TABLE_NAME AS `表名`,
BYTES/1024/1024 AS `巨细(MB)`
FROM
DBA_TABLES;
-- 检察所有表的记录条数（需有相应权限）
SELECT OWNER, TABLE_NAME, (SELECT COUNT(*) FROM ALL_TABLES WHERE TABLE_NAME = t.TABLE_NAME AND OWNER = t.OWNER) AS `记录数`
FROM ALL_TABLES t;
GaussDB
-- 检察所有表的空间巨细
SELECT
relname AS `表名`,
pg_size_pretty(pg_relation_size(relid)) AS `巨细`
FROM
pg_stat_user_tables;
-- 检察所有表的记录条数
SELECT relname, (SELECT COUNT(*) FROM pg_stat_user_tables WHERE relname = t.relname) AS `记录数`
FROM pg_stat_user_tables t;
SQL Server
-- 检察所有表的空间占用（包括数据和索引）
SELECT
OBJECT_NAME(i.object_id) AS `表名`,
SUM(a.used_pages)*8/1024 AS `巨细(MB)`
FROM
sys.indexes i
JOIN
sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id
JOIN
sys.allocation_units a ON p.partition_id = a.container_id
GROUP BY
i.object_id;
-- 检察所有表的记录条数
SELECT OBJECT_NAME(object_id) AS `表名`, (SELECT COUNT(*) FROM sys.tables WHERE OBJECT_NAME(object_id) = t.name) AS `记录数`
FROM sys.tables t;
MongoDB
// 连接MongoDB Shell后实行
// 检察所有集合（表）的巨细
db.getCollectionNames().forEach(function(collectionName) {
printjson(db[collectionName].stats());
});
// 检察所有集合的记录条数
db.getCollectionNames().forEach(function(collectionName) {
print(collectionName + ': ' + db[collectionName].count());
});
Neo4j
// 检察所有节点标签及其对应的节点数
MATCH (n)
RETURN labels(n)[0] AS `标签名`, count(n) AS `节点数`
GROUP BY labels(n)[0];
// 检察所有关系类型及其对应的关系数
MATCH ()-[r]-()
RETURN type(r) AS `关系类型`, count(r) AS `关系数`
GROUP BY type(r);
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。
|
