mysql常用语句

查看各个数据库占用大小

select TABLE_SCHEMA, concat(truncate(sum(data_length)/1024/1024,2),' MB') as data_size,
concat(truncate(sum(index_length)/1024/1024,2),'MB') as index_size
from information_schema.tables
group by TABLE_SCHEMA
order by data_length desc;

查看单个数据库各个表的大小

SELECT table_name, data_length + index_length AS len, table_rows,
 
 CONCAT(ROUND((data_length + index_length)/1024/1024,2),'MB') AS datas
 
FROM information_schema.tables
 
WHERE table_schema = '库名'
 
ORDER BY len DESC;

 

#统计表内字段值出现的次数
SELECT
    字段名, COUNT(字段名)
FROM
    表名  where 条件
GROUP BY
   字段名
HAVING
(COUNT(字段名) > 出现多少次)  ORDER BY COUNT(字段名) DESC

 

mysql设置信息

show variables like '%connect%';

 

连接池信息

 show global status like 'Max_used_connections';