mysql一些常用查询语句

版本:8.0

查询正在执行表或者使用者的执行信息

select t1.* from (select * from information_schema.`PROCESSLIST` ) t1 
WHERE `INFO` like "%sp_aqc_org_patent_m%" 
ORDER BY `TIME` DESC;

select t1.* from (select * from information_schema.`PROCESSLIST` ) t1 
WHERE `HOST` like "%.43:%" 
ORDER BY `TIME` DESC;

查询使用空间

#查询mysql所有库的占用磁盘大小
SELECT 
TABLE_SCHEMA as '数据库',
concat(data_size,' GB') as '数据容量(GB)',
concat(index_size,' GB') as '索引容量(GB)',
concat(data_sum,' GB') as '合计'
FROM (
	SELECT 
	*,
	truncate(data_size + index_size, 2) as 'data_sum'
	from 
	(
		select 
		TABLE_SCHEMA,
		truncate(sum(data_length)/1024/1024/1024,2) as 'data_size',
		truncate(sum(index_length)/1024/1024/1024,2) as 'index_size'
		from information_schema.tables
		group by TABLE_SCHEMA
	) t1
	ORDER BY data_sum desc
) t2;


#查询mysql指定库下表的占用磁盘空间大小
SELECT 
data_schema as '库名',
data_table as '表名',
concat(data_size,' GB') as '数据容量(GB)',
concat(index_size,' GB') as '索引容量(GB)',
concat(data_sum,' GB') as '合计(GB)' 
FROM (
	SELECT 
	 data_schema,
	 data_table,
	 data_size,
	 index_size,
	 round(data_size + index_size, 2) as 'data_sum'
	FROM (
		SELECT 
				table_schema as `data_schema`, 
				table_name AS `data_table`, 
				round(((data_length) / 1024 / 1024 / 1024), 2) as `data_size`, 
				round(((index_length) / 1024 / 1024 / 1024), 2) as `index_size`
		FROM information_schema.tables 
		WHERE table_schema = 'qhdata_support_credit_baidu_m'
	) t1
	ORDER BY data_sum DESC
) t2;

;
#查询库表对应的信息总条数
select
table_schema as '数据库',
sum(table_rows) as '记录数',
sum(truncate(data_length/1024/1024/1024, 2)) as '数据容量(GB)',
sum(truncate(index_length/1024/1024/1024, 2)) as '索引容量(GB)'
from information_schema.tables
group by table_schema
order by sum(data_length) desc, sum(index_length) desc;