表名含有大写字母的所有表:
select table_name
from information_schema.tables
where table_schema='databasename' and binary table_name REGEXP '[A-Z]';
有json格式的表:
select TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME,DATA_TYPE from information_schema.columns where DATA_TYPE like 'json' and TABLE_SCHEMA not in ('sys','mysql','information_schema','performance_schema');
表大小:
select TABLE_SCHEMA,TABLE_TYPE,ENGINE,TABLE_NAME,ROUND((DATA_LENGTH+INDEX_LENGTH+DATA_FREE)/1024/1024/1024) TOTAL_GB
from information_schema.tables
where TABLE_SCHEMA='databasename'
order by TOTAL_GB desc limit 10;
表数量:
select TABLE_SCHEMA,count(TABLE_NAME) from tables where TABLE_SCHEMA not in ('mysql','information_schema','performation_schema','sys') group by TABLE_SCHEMA;
分区数量:
select TABLE_SCHEMA,TABLE_NAME,count(PARTITION_NAME) from PARTITIONS where TABLE_SCHEMA not in ('mysql','information_schema','performation_schema','sys') group by TABLE_SCHEMA,TABLE_NAME;
查询大于1G无索引表:
SELECT TABLE_SCHEMA, TABLE_NAME, total_gb
FROM (select T.TABLE_SCHEMA,T.TABLE_NAME,round(((data_length + index_length + data_free) / 1024 / 1024 / 1024),2) AS total_gb from TABLES T where T.TABLE_TYPE = 'BASE TABLE' AND T.TABLE_SCHEMA NOT IN ('SYS', 'MYSQL', 'INFORMATION_SCHEMA', 'PERFORMANCE_SCHEMA')) T
WHERE total_gb > 1
order by total_gb desc;