我现在运行一个mysql docker容器映像在一个托管服务器上,最大内存为2个14gbram8000 iops 我经常选择和更新查询
我使用python和mysql连接器
我在开始时初始化一个连接 我最好在开始和结束时打开一个连接 只在出口处关闭
def initsql():
logger.log('initsql()')
global cnx
cnx = mysql.connector.connect(user='blabla', password='blabla',
host='blablahost',
database='blablabladb')
def closesql():
logger.log('closesql()')
global cnx
cnx.close()
现在有两个非常慢的函数:
def sqlSetModus(modus):
logger.log('sqlSetModus()')
t0 = time.time()
try:
internet_on() #Function check if there is a internet connection if not raise Exception()
global cnx
cursor = cnx.cursor()
global logintoken
currentdate = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
if modus == 'offline':
updateModus = '''UPDATE accounts SET inuse=0, token='none', was_online = '%s', modus = '%s' WHERE token = '%s' ''' % (currentdate, modus, logintoken)
else:
updateModus = '''UPDATE accounts SET was_online = '%s', modus = '%s' WHERE token = '%s' ''' % (currentdate, modus, logintoken)
cursor.execute(updateModus)
cnx.commit()
logger.success("sqlSetModus after {} Sekunden".format(time.time() - t0))
return True
except Exception:
logger.error("Error sqlSetModus after {} Sekunden".format(time.time() - t0))
raise Exception
finally:
cursor.close()
def sqlUpdatePoints(company):
logger.log('sqlUpdatePoints()')
t0 = time.time()
try:
internet_on()
global cnx
cursor = cnx.cursor()
global sessiontoken
currentdate = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
pointsvalue = 0.55
updateOnline = '''UPDATE accounts SET was_online = '%s', this_points = this_points + '%s' WHERE token = '%s' ''' % (currentdate, pointsvalue, logintoken)
cursor.execute(updateOnline)
updatePoints = '''UPDATE points SET points = points + '%s' WHERE company = 'companyXYZ' ''' % (pointvalue,)
cursor.execute(updatePoints)
updateAccount = '''INSERT INTO statistic (company, datum, website) VALUES ('companyXYZ', '%s', '%s') ON DUPLICATE KEY UPDATE counter=counter+1''' % (currentdate, company)
cursor.execute(updateAccount)
cnx.commit()
logger.success("sqlSetAfterSuccesplay after {} Sekunden".format(time.time() - t0))
return True
except Exception:
logger.error("Error sqlSetAfterSuccesplay after {} Sekunden".format(time.time() - t0))
raise Exception
finally:
cursor.close()
当然,我有更多的代码,也有更多的sql查询,比如简单的select,但它们确实工作得很快
这两个函数的开销总是超过10秒
sqlUpdatePoints时间:
[17:38:57.790] sqlUpdatePoints after 13.4862029552 Sekunden
[17:39:48.294] sqlUpdatePoints after 14.960242033 Sekunden
sqlSetModus时间:
[17:38:44.051] sqlSetModus after 14.4736759663 Sekunden
[17:39:31.702] sqlSetModus after 13.1622648239 Sekunden
[17:40:09.977] sqlSetModus after 16.7646770477 Sekunden
简单选择查询时间:
[17:39:53.213] sqlGetActiveAccount after 0.741001844406 Sekunden
[17:39:02.695] sqlGetActiveAccount after 0.737174034119 Sekunden
[17:38:12.800] sqlGetActiveAccount after 0.7693400383 Sekunden
您必须知道,我在同一时间运行了100个这样的代码实例,这意味着这是打开100个连接的时间 并且经常针对数据库运行sqlUpdatePoints+sqlSetModus和简单查询
表如下所示:
nr = int (autoincrement unique)
username = varchar
password = varchar
inuse = int
token = varchar
was_online = datetime
points = float
modus = enum
nr | username | password | inuse | token | was_online | this_points | modus
你没有说你已经有了哪些索引,所以我假设你没有:
对于
sqlSetModus()
函数,您需要索引:对于
sqlUpdatePoints()
函数,您需要索引:您正在为过滤器(
=
)使用相等,因此使用这些索引,您的搜索(更新)应该会非常迅速。你知道吗相关问题 更多 >
编程相关推荐