mysql 数据库性能查看以及一些SQL

 

主要方法： 

show status , show profile ,检查慢查询日志 

 

# 检测执行的sql，具体那个位置比较慢 

SELECT * FROM phone_sts

 

#数据库版本

SELECT  VERSION();

 

#查询sql对资源的占用情况 

SHOW profiles ; 

#查询当前sql执行过程中的时间消耗 

SHOW profile ;

 

 SHOW profile  block io,cpu FOR  QUERY 31969

 

 

 

 

 

 

 #检查profiling是否开启

SHOW VARIABLES LIKE '%pro%';  

#开启

SET profiling=1;  

 

 

SHOW profiles;  

 

 

#c查询各个表占用大小

SELECT   

  table_schema AS 'Db Name',  

  ROUND( SUM( data_length + index_length ) / 1024 / 1024, 3 ) AS 'Db Size (MB)',  

  ROUND( SUM( data_free ) / 1024 / 1024, 3 ) AS 'Free Space (MB)'  

FROM information_schema.tables  

GROUP BY table_schema ;  

 

 

 

 

SHOW DATABASES;    

USE information_schema;   

 

SHOW TABLES;  

 

 # 表超过1000行的

 SELECT CONCAT(table_schema,'.',table_name) AS table_name,table_rows   

 FROM information_schema.tables 

 WHERE table_rows > 1000  

 ORDER BY table_rows DESC;   

 

# 最大的10个表

 SELECT CONCAT(table_schema,'.',table_name) table_name,   

 CONCAT(ROUND(data_length/(1024*1024),2),'M') data_length   

 FROM information_schema.TABLES   

 ORDER BY data_length DESC LIMIT 10; 

 

 

# 查询一个sql语句的时间消耗在哪里

SET @query_id=1 ; 

SELECT state,SUM(duration)  AS total_R,

ROUND(

   100*SUM(duration)/

   (SELECT  SUM(DURATION)

   FROM information_schema.PROFILING

    WHERE query_id=@query_id

 ),2) AS calls , 

 SUM(duration )/COUNT(*) AS "R/Call"

 FROM information_schema.PROFILING

WHERE query_id=@query_id

GROUP BY state 

ORDER BY total_R DESC ; 

 

 

 

 

# 查询一个sql语句的时间消耗在哪里

SET @query_id=31969 ; 

SELECT state,SUM(duration)  AS total_R,

ROUND(

   100*SUM(duration)/

   (SELECT  SUM(DURATION)

   FROM information_schema.PROFILING

 ),2) AS calls , 

 SUM(duration )/COUNT(*) AS "R/Call"

 FROM information_schema.PROFILING

 

GROUP BY state 

ORDER BY total_R DESC ;