mysql - python mysql Ver 8.0.22-0ubuntu0.20.04.3 for Linux on x86_64 ((Ubuntu)) 中的 mysql 性能问题

Question

我正在制作一个每秒至少需要 500 行条目的数据库，如下所示

[1,amit,"55555","2020-12-21 12:12:12"]

当我尝试使用队列和线程在 pyhton 中将其插入 mysql 时，它每秒仅插入 150 行

我的代码如下

import mysql.connector
from queue import Queue
from threading import Thread




def do_stuff(q,mydb,mycursor):
      while True:
#             mycursor = mydb.cursor()
            a=  q.get()
            sql = "INSERT INTO list (id, ticker, price, tmp) VALUES (%s, %s, %s, %s)"
            val = (a[0], a[1],float(a[3]),a[2])
            mycursor.execute(sql, val)

            mydb.commit()
            q.task_done()

q = Queue(maxsize=0)
num_threads = 10 

for i in range(num_threads):
        mydb = mysql.connector.connect(host="localhost",user="root",password="password",database="list1",auth_plugin='mysql_native_password')
        mycursor = mydb.cursor()
        worker = Thread(target=do_stuff, args=(q,mydb,mycursor)) 
        worker.start()
        
        
def strt():
    for i in range(100000):
        a = [i,"name","154.55","2020-12-21 12:12:12"]
        q.put(a)

如果有人建议提高性能或任何其他方法可以更快地编写

请告诉我谢谢

标签： mysqlmysql-python