MySQL数据库状态及语句性能分析

mysql数据库是一种开源的关系型数据库，其使用较为广泛。我们的sql语句在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 ;