请问如何加快我这个程序的查询mysql速度

  • Z时代
  • 2024-02-20
  • 分类：IT

请问如何加快我这个程序的查询mysql速度

sql1 = "select company_code,staff_name from company_keyperson_test where staff_type='1008'or staff_type='1015'or staff_type='1014' or staff_type='1001' or staff_type='1013' or staff_type='1006'or staff_type='1007'  limit 0,1000"
# sql1 = 'select x from test1 '
cursor.execute(sql1)
data = cursor.fetchall()
listdata = []
listdata = list(data)
print('共有'+str(len(listdata))+'条数据待查询')
xr = []
count1 = 1
fromData = []
indata = ()
# for k in data:
#     xr.append(k[0])
#     # print("xr"+str(len(xr)))
for line in listdata:
    sql2 = "select stakes_ration,base_credit_code,shareholder_name from shareholders_infomation where base_credit_code='%s' and shareholder_name='%s'" % line
    cursor.execute(sql2)
    result111 = cursor.fetchall()
    if len(result111) != 0:
        indata = indata+result111
        print('已查找到'+str(count1)+'条数据')
        count1 = count1+1
inlist = []
inlist = list(indata)
print('共有'+str(len(inlist))+'条数据待导入')
count = 1
for line in inlist:
    sql3 = "update company_keyperson_test set staff_stake_num='%s' where company_code = '%s' and staff_name = '%s' and staff_type in('1001', '1008','1014','1007','1015','1013','1006')" % line
    cursor.execute(sql3)
    print('提交'+str(count)+'条数据')
    count = count+1
    db.commit()
cursor.close()

回答：

第一步:

mysql">select company_code,staff_name from company_keyperson_test where staff_type='1008'or staff_type='1015'or staff_type='1014' or staff_type='1001' or staff_type='1013' or staff_type='1006'or staff_type='1007'  limit 0,1000

修改成

select company_code,staff_name from company_keyperson_test where staff_type in ('1008','1015','1014','1001','1013','1006','1007')  limit 0,1000

第二步:
对查询出的结果 company_code,staff_name 进行去重,防止下面的for line in listdata循环进行无用的操作

回答：

表A关联表B, 使用临时表再更新表A, 一条SQL语句的事情

以上是 请问如何加快我这个程序的查询mysql速度 的全部内容， 来源链接： utcz.com/p/938022.html

回到顶部