需要记录慢查询，在配置文件中加入慢查询的相关配置。

/etc/my.cnf


general_log=off

log_output=table

slow_query_log=1

long_query_time=0.3


  • general_log=off      会记录所有的查询，建议关闭（set global general_log = off）

  • log_output=table     将log记录到表中

  • slow_query_log=1     开启慢查询记录

  • long_query_time=0.3  大于0.3秒的查询会被记录


配置好以后，我的做法是使用python每周读取数据库中的记录，邮件发出来以便分析。


sql如下：

SELECT
    max(user_host),
    min(start_time),
    max(start_time),
    count(*),
    sum(
        CASE
        WHEN TIME_TO_SEC(query_time) = 0 THEN
            0.3
        ELSE
            TIME_TO_SEC(query_time)
        END
    ),
    max(
        CASE
        WHEN TIME_TO_SEC(query_time) = 0 THEN
            0.3
        ELSE
            TIME_TO_SEC(query_time)
        END
    ),
    min(
        CASE
        WHEN TIME_TO_SEC(query_time) = 0 THEN
            0.3
        ELSE
            TIME_TO_SEC(query_time)
        END
    ),
    round(
        avg(
            CASE
            WHEN TIME_TO_SEC(query_time) = 0 THEN
                0.3
            ELSE
                TIME_TO_SEC(query_time)
            END
        ),
        2
    ),
    sql_text
FROM
    slow_log
WHERE
    sql_text NOT LIKE '%SQL_NO_CACHE%'
GROUP BY
    LEFT (sql_text, 40)
ORDER BY
    count(*) DESC
LIMIT 20;


部分python代码如下：

for i in range(len(conn)):
            log=p.get_table(conn[i],sql)
            query_log+='_'*120+'\r\n'
            query_log+="Slow_Log_%s:%s____%s\r\n\r\n" % (conn[i]["host"],conn[i]["port"],days)
            for n in range(len(log)): 
                query_log+="%s  %s  %s  %s  %s " % (str(log[n][3]),str(log[n][4]),str(log[n][5]),str(log[n][6]),str(log[n][7]))
                query_log+="user_host :%s    start_time :%s   end_time :%s\r\n" % (str(log[n][0]),str(log[n][1]),str(log[n][2]))
                query_log+=' '*40+squeeze(str(log[n][8]))+'\r\n\r\n'
                
        mail.send_mail(mail_list,mail_title,query_log)


得到的邮件部分：

Slow_Log_x.x.x.x:3306____2016-05-26

 

1889  704.4  33.0  0.3  0.37 user_host :x[x] @  [x]    start_time :2016-05-25 09:30:27   end_time :2016-05-26 09:16:05

                                        select max(online_user),round(sum(online_user)/count(id)) from online where datetime >= '2016-05-25' and server_id = 27


这样就可以看到每周比较慢的查询，然后进行优化分析。