mysql版本查询sql

发布时间: 2023-11-21 10:41 阅读: 文章来源:1MUMB88PS

1、检查连接库信息等information_schema.PROCESSLIST

select SUBSTRING_INDEX(host,‘:‘,1) as ip , count(*) from information_schema.processlist group by ip;

2.慢sql情况查询

可以使用以下三种方式查询,第一种是了解MySQL进程大概情况;第二种是按照影响时间倒序的,可以查询到目前最慢的一条sql;第三种是防止sql 的info消息过长而无法显示完整。

-- usual

show processlist;

-- extend

select * FROM information_schema.`PROCESSLIST` WHERE info IS NOT NULL ORDER BY TIME DESC;

-- full info

show full processlist;

补充一下,若出现大量慢sql,在不影响业务的前提下,可以将一些select先kill掉,然后来缓冲一下MySQL的性能问题

-- kill id

select concat(‘kill ‘,id,‘ ;‘) FROM information_schema.`PROCESSLIST` WHERE info like ‘select%‘ and time > 3 ORDER BY TIME DESC;

5.0.37及以上,可以使用profile来分析近期sql执行时间;

查看是否开启,如果为off,则执行下一条语句开启

show variables like "%pro%";

set profiling = 1;

show profiles;

show profile cpu, block io, memory,swaps,context switches,source for query 6;可以查看出一条SQL语句执行的各种资源消耗情况,比如CPU,IO等

show profile all for query 6 查看第6条语句的所有的执行信息。

3.连接数的查询

可以使用以下sql查询到当前实例下所有库的连接数(由于该sql是根据同一个host来判断的,所以可能存在一个ip,不同端口有多个连接)

select db,COUNT(1) FROM information_schema.`PROCESSLIST` GROUP BY db ORDER BY count(1) DESC;

-- 若想查询到完整的host

select db,host FROM information_schema.`PROCESSLIST`

-- where db = dbname;

-- 若想查询有哪些host连接到当前实例,而不考虑host端口

select SUBSTRING_INDEX(HOST,‘:‘,1) ip,COUNT(1) FROM information_schema.`PROCESSLIST`

where db = dbname GROUP BY ip ORDER BY 2 DESC;

-- 计算各个库总容量

select round(sum(DATA_LENGTH+INDEX_LENGTH)/1024/1024,2) as total_db_mb,TABLE_SCHEMA from information_schema.tables group by TABLE_SCHEMA

--各个表

select table_schema,table_name,table_rows,ROUND((data_length+index_length)/1024/1024,2) AS total_mb FROM information_schema.tables order by total_mb desc

--

select

*

FROM

(select

table_name,

concat(ROUND(SUM(data_length / 1024 / 1024), 2), ‘MB‘) AS data_length_MB,

ROUND(SUM(data_length / 1024 / 1024), 2) data_length,

concat(ROUND(SUM(index_length / 1024 / 1024), 2), ‘MB‘) AS index_length_MB

FROM

information_schema.tables

WHERE

table_schema = ‘party_build‘

GROUP BY table_name) t

ORDER BY data_length DESC

•••展开全文