情况
- 使用Python 3.7.2
- 我已经阅读了服务器上有5m行的MariaDB表的流行。
- 我有一个带有7K整数的本地文本文件,每行1个。
- 整数表示表的IDX。
- 表的IDX列是主要键。(所以我想它会自动索引?)
问题
我需要选择文本文件中IDX的所有行。
我的努力
版本1
进行7k查询,文本文件中的每行之一。这每秒大约有130个查询,总成本约为1分钟。
import pymysql
connection = pymysql.connect(....)
with connection.cursor() as cursor:
query = (
"SELECT *"
" FROM TABLE1"
" WHERE IDX = %(idx)s;"
)
all_selected = {}
with open("idx_list.txt", "r") as f:
for idx in f:
idx = idx.strip()
if idx:
idx = int(idx)
parameters = {"idx": idx}
cursor.execute(query, parameters)
result = cursor.fetchall()[0]
all_selected[idx] = result
版本2
选择整个桌子,迭代光标和樱桃挑出行。.fetchall_unbuffered()
上的前循环覆盖每秒30-40k行,整个脚本的成本约为3分钟。
import pymysql
connection = pymysql.connect(....)
with connection.cursor() as cursor:
query = "SELECT * FROM TABLE1"
set_of_idx = set()
with open("idx_list.txt", "r") as f:
for line in f:
if line.strip():
line = int(line.strip())
set_of_idx.add(line)
all_selected = {}
cursor.execute(query)
for row in cursor.fetchall_unbuffered():
if row[0] in set_of_idx:
all_selected[row[0]] = row[1:]
预期行为
我需要更快地选择,因为文本文件中的IDX数量将来会大于10K-100K。
我咨询了其他答案,包括此答案,但由于我只阅读Prepilege,因此无法使用它,因此不可能创建另一个表格。
那么如何更快地进行选择?
临时表实现看起来像:
connection = pymysql.connect(....,local_infile=True)
with connection.cursor() as cursor:
cursor.execute("CREATE TEMPORARY TABLE R (IDX INT PRIMARY KEY)")
cursor.execute("LOAD DATA LOCAL INFILE 'idx_list.txt' INTO R")
cursor.execute("SELECT TABLE1.* FROM TABLE1 JOIN R USING ( IDX )")
..
cursor.execute("DROP TEMPORARY TABLE R")
感谢@danblack的提示(或超过提示),我能够通过以下查询实现所需的结果。
query = (
"SELECT *"
" FROM TABLE1"
" INNER JOIN R"
" ON R.IDX = TABLE1.IDX;"
)
cursor.execute(query)
Danblack的SELECT
语句对我不起作用,引起了错误:
pymysql.err.programmingerror:(1064,"您在SQL语法中有一个错误;检查与您的MariadB服务器版本相对应的手册,以便在第1行中使用'iDx'的正确语法")
这可能是因为Mariadb的加入语法,所以我咨询了有关加入桌子的Mariadb文档。
现在,它在0.9秒内选择了7k行。
仅出于完整的目的而留在这里作为答案,也是为了将来的读者。