mysql语句性能分析

1.开启慢查询

slow_query_log = 1   //开启

slow_query_log_file = mysql_slow_query.log  //日志文件位置

long_query_time = 1  //1s

通过开启慢查询日志，可以记录超过long_query_time定义时间的sql语句

2.show profiles

SHOW PROFILES;

SHOW PROFILE FOR QUERY 1; //指定查看某个查询详细信息

或者直接查询INFORMATION_SCHEMA表

SELECT STATE , SUM(DURATION) AS Total_R,

    ROUND(

        100 * SUM(DURATION) /

            (SELECT SUM(DURATION)

             FROM INFORMATION_SCHEMA.PROFILING

             WHERE QUERY_ID = 16

        ) , 2 ) AS Pct_R ,

    COUNT(*) AS Calls ,

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

FROM INFORMATION_SCHEMA.PROFILING

WHERE QUERY_ID = 16

GROUP BY STATE

ORDER BY Total_R DESC

3.explain

EXPLAIN SELECT * FROM orders WHERE SenderName like '%123%'

UNION ALL

SELECT * FROM orders WHERE SenderName like '%wang%'