MySQL常用运维统计SQL

非DBA开发人员常用统计SQL

查看最大连接数

SHOW VARIABLES LIKE 'max_connections';

查看已使用连接数

show status like 'max%connections';

查看数据库连接IP列表及数量

SELECT SUBSTRING_INDEX(host,':',1) AS ip , count(*) AS cnt
FROM information_schema.processlist 
GROUP BY ip;

查看数据库连接IP列表及数量

SELECT SUBSTRING_INDEX(host,':',1) as ip , count(*) AS cnt
FROM information_schema.processlist 
GROUP BY ip;

查询自建的索引

SELECT 
TABLE_NAME, INDEX_NAME, 
GROUP_CONCAT(COLUMN_NAME) as INDEX_COLUMN
FROM information_schema.statistics
WHERE table_schema='数据库名称'
  AND INDEX_NAME <> 'PRIMARY'
GROUP BY TABLE_NAME, INDEX_NAME;

查看所有数据库各容量大小

SELECT
	table_schema AS '数据库',
	SUM(table_rows) AS '记录数',
	SUM(truncate(data_length/1024/1024, 2)) AS '数据容量(MB)',
	SUM(truncate(index_length/1024/1024, 2)) AS '索引容量(MB)'
	FROM information_schema.tables
GROUP BY table_schema
ORDER BY sum(data_length) DESC, sum(index_length) DESC;

查看所有数据库各表容量大小

SELECT
	table_schema AS '数据库',
	table_name AS '表名',
	table_rows AS '记录数',
	TRUNCATE(data_length/1024/1024, 2) AS '数据容量(MB)',
	TRUNCATE(index_length/1024/1024, 2) AS '索引容量(MB)'
FROM information_schema.tables
ORDER BY data_length DESC, index_length DESC;