首页 > MySQL > mysql 数据库性能查看
2016
02-29

mysql 数据库性能查看

主要方法:

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 ;


留下一个回复

你的email不会被公开。

此站点使用Akismet来减少垃圾评论。了解我们如何处理您的评论数据