【请关注】各类数据库优化，抓大重点整改，快速优化空间mysql，Oracle，Neo4j等

各类数据库优化，抓大重点整改，快速优化，首先分析各数据库查询全部表的空间大小及记录条数的语句：

 

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);