수천 개의 SELECT 쿼리 속도 향상

수천 개의 SELECT 쿼리 속도 향상

상황.

• Python 3.7.2 사용
• 서버에서 5M 행이 있는 MariaDB 테이블을 읽은 적이 있습니다.
• 한 줄에 하나씩 7K 정수로 된 로컬 텍스트 파일이 있습니다.
• 정수는 테이블의 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

전체 테이블을 선택하고 커서 및 체리픽 행을 반복합니다.for-loop over.fetchall_unbuffered()는 초당 30~40,000 행을 커버하며 스크립트 전체를 완료하는 데 약 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에서 10K까지 증가할 것이기 때문에 더 빨리 선택해야 합니다.

이것과 같은 다른 답변을 참고했지만, 저는 읽기 권한만 가지고 있기 때문에 다른 테이블을 만들 수 없습니다.

그럼 어떻게 하면 더 빨리 선택을 할 수 있을까요?

임시 테이블 구현은 다음과 같습니다.

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)

댄블랙스SELECT다음 오류가 발생하여 문이 작동하지 않았습니다.

pymysql.err.Programming Error: (1064, "SQL 구문에 오류가 있습니다. 사용하시는 MariaDB 서버 버전에 대응하는 매뉴얼에서 1행의 'IDX' 근처에서 사용할 올바른 구문을 확인하십시오.)

이는 MariaDB의 join 구문 때문일 수 있으므로 MariaDB의 join table 문서를 참조했습니다.

이제 7K 행을 0.9초 만에 선택할 수 있습니다.

완성도를 위해서, 그리고 미래의 독자들을 위해서, 해답으로서 이곳을 떠나는 것.

언급URL : https://stackoverflow.com/questions/54900574/making-thousands-of-select-queries-faster