mysql单条sql的性能_单条sql性能分析与优化

性能分析

1. explain 查看sql执行计划，得出索引使用情况等信息

2. show profiling 查看sql所有执行步骤以及用时，使用步骤如下

1)开启性能剖析

mysql> set profiling = 1;

2)执行你要查询的sql

3)查看性能剖析接收到的sql以及耗时

mysql> show profiles;

4)查看sql执行的详细步骤耗时(这里query后的id是步骤3中的QUERY_ID)

mysql> show prifile for query 1;

5)这样可能可阅读性不强，不容易观察出耗时最多的步骤以及执行次数等

mysql> set @query_id = 1;

mysql>

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 pct_r, COUNT(*) AS calls, SUM(duration) / COUNT(*) AS ffp

FROM information_schema.profiling

WHERE QUERY_ID = @query_id

GROUP BY state

ORDER BY total_r DESC;

这样就可以清晰看到sql语句耗时在哪些步骤

性能优化