类似的参数,SQL查询耗时相差100倍
澳门博彩导航网址
澳门博彩导航网址
当前位置 : 澳门博彩导航网址 > 澳门博彩导航网址大全

类似的参数,SQL查询耗时相差100倍

使用Python制作一个每日归档的crontab job。

#!/usr/bin/env python
#encoding: utf-8
from dmconfig import DmConf
#from dmdb import Dmdb
import redis
import MySQLdb
import dawnutils


import time
from datetime import datetime, timedelta, date

conf = DmConf.loadConf

db = MySQLdb.connecthost=conf["DbHost"],user=conf[DbAccount],passwd=conf[DbPassword],\
        db=conf[DbName],charset=conf[DbCharset]
cache = redis.Redishost=conf[RedisHost], port=conf[RedisPort], 
        db=conf[Redisdbid], password=conf[RedisPassword]

#cursor = db.cursor

def try_reconnectconn:
    try:
        conn.ping
    except:
        conn = MySQLdb.connecthost=conf["DbHost"],user=conf[DbAccount],passwd=conf[DbPassword],\
            db=conf[DbName],charset=conf[DbCharset]


def zip_taskdevice, start, stop:
    #cursor = db.cursor
    format = "%Y%m%d%H%M%S"
    begin = time.strftime"%Y-%m-%d %H:%M:%S",time.strptimestart,format
    end = time.strftime"%Y-%m-%d %H:%M:%S",time.strptimestop,format
    print "%s:%s,%s"%device, begin, end
    sql = "SELECT * from `period` WHERE `snrCode` = \"%s\" AND `time` > \"%s\" AND `time` < \"%s\" ORDER BY `recId` DESC"%device, begin, end
    print sql
    cursor = db.cursor

    try_reconnectdb
    t1 = time.time
    try:
        cursor.executesql
        results = cursor.fetchall
    except MySQLdb.Error,e:
        print "Error %s"%e

    print "SQL takes %f seconds"%time.time-t1

    print "len of reconds, %d"%lenresults

    #for row in results:
        #print row


def dispatcherdevSet, start, stop:
    print "size of set: %d"%lendevSet
    print devSet
    for dev in devSet:
        zip_taskdev, start, stop

def archive_task_queue:
    today = datetime.now
    oneday = timedeltadays=1
    yesterday = today - oneday
    format = "%Y%m%d%H%M%S"
    begin = time.strftimeformat, yesterday.timetuple[:8] + 120000
    end = time.strftimeformat, today.timetuple[:8] + 120000

    sql = "SELECT * from `logbook` WHERE `login` > \"%s\" AND `login` < \"%s\" AND `logout` > \"%s\" AND `logout` < \"%s\""%begin, end, begin, end
    print sql
    
    cursor = db.cursor
    reclist = []
    try:
        cursor.executesql
        results = cursor.fetchall

        for row in results:
            #print row
            reclist.appendrow[1]
    except MySQLdb.Error,e:
        print "Error %s"%e


    if lenreclist:
        dispatchersetreclist, begin, end

    db.close

if __name__ == __main__:
    archive_task_queue

运行结果比较古怪。

SELECT * from `logbook` WHERE `login` > "20160720120000" AND `login` < "20160721120000" AND `logout` > "20160720120000" AND `logout` < "20160721120000"
size of set: 4
set[uB1H700001, uB1H700002, uA1E500018, uA2H300001]
B1H700001:2016-07-20 12:00:00,2016-07-21 12:00:00
SELECT * from `period` WHERE `snrCode` = "B1H700001" AND `time` > "2016-07-20 12:00:00" AND `time` < "2016-07-21 12:00:00" ORDER BY `recId` DESC
SQL takes 0.018648 seconds
len of reconds, 597
B1H700002:2016-07-20 12:00:00,2016-07-21 12:00:00
SELECT * from `period` WHERE `snrCode` = "B1H700002" AND `time` > "2016-07-20 12:00:00" AND `time` < "2016-07-21 12:00:00" ORDER BY `recId` DESC
SQL takes 0.566339 seconds
len of reconds, 4642
A1E500018:2016-07-20 12:00:00,2016-07-21 12:00:00
SELECT * from `period` WHERE `snrCode` = "A1E500018" AND `time` > "2016-07-20 12:00:00" AND `time` < "2016-07-21 12:00:00" ORDER BY `recId` DESC
SQL takes 0.236042 seconds
len of reconds, 0
A2H300001:2016-07-20 12:00:00,2016-07-21 12:00:00
SELECT * from `period` WHERE `snrCode` = "A2H300001" AND `time` > "2016-07-20 12:00:00" AND `time` < "2016-07-21 12:00:00" ORDER BY `recId` DESC
SQL takes 65.640211 seconds
len of reconds, 5772

B1H70002的查询4642组数据,A2H300001为5772组数据,为何SQL耗时分别为0.56和65秒?差100倍?

一次查询结果并不能完全断定该语句的问题,你可以回顾下是否在改次查询中有其他的资源使用数据库或者网路资源。

ps:可以将sql改为参数传递,方便sql执行时候优化器的处理,加速执行效率。

广告位

澳门博彩导航网址