web 项目之中慢查询定位查询sql语句

web 项目之中慢查询的使用(flask为例):
    1,如果只是看msql的慢查询的日志在的项目之中很难定位问题
    2,可以借用 SQLALCHEMY_RECORD_QUERIES 与 DATABASE_QUERY_TIMEOUT 将慢查询以及相关的上下文信息记录到日志中
    3,代码逻辑:
        1),启用查询记录功能
        2),app.logger 增加一个记录日志的处理器
        3),每次请求后,超过阈值的存储到日志中去

# coding=utf-8
import logging
from logging.handlers import RotatingFileHandler

from flask import Flask, request, jsonify
from flask_sqlalchemy import get_debug_queries

from flask_sqlalchemy import SQLAlchemy

db = SQLAlchemy()
from users import User

app = Flask(__name__)
app.config.from_object('config')
# 配置查询超时时间
app.config['DATABASE_QUERY_TIMEOUT'] = 0.0001
# 保存查询记录
app.config['SQLALCHEMY_RECORD_QUERIES'] = True
db.init_app(app)

# 增加一个日志记录
formatter = logging.Formatter(
    "[%(asctime)s] {%(pathname)s:%(lineno)d} %(levelname)s - %(message)s")
handler = RotatingFileHandler('slow_query.log', maxBytes=10000, backupCount=10)
handler.setLevel(logging.WARN)
handler.setFormatter(formatter)
app.logger.addHandler(handler)

with app.app_context():
    db.drop_all()
    db.create_all()


@app.route('/users', methods=['POST'])
def users():
    username = request.form.get('name')

    user = User(username)
    print 'User ID: {}'.format(user.id)
    db.session.add(user)
    db.session.commit()
    return jsonify({'id': user.id})

# 请求钩子
@app.after_request
def after_request(response):
    for query in get_debug_queries():
        # 如果超出查询时间,那么记录到日志之中!
        if query.duration >= app.config['DATABASE_QUERY_TIMEOUT']:
            app.logger.warn(
                ('\nContext:{}\nSLOW QUERY: {}\nParameters: {}\n'
                 'Duration: {}\n').format(query.context, query.statement,
                                          query.parameters, query.duration))
    return response

if __name__ == '__main__':
    app.run(host='0.0.0.0', port=9000)