각 날짜마다 동일한 데이터에 대해 서로 다른 테이블을 만드는 것이 현명한가요?

Question

다음 열이 포함된 MYSQL InnoDB 테이블table이 있습니다(테이블 및 열 이름이 변경됨):

• 날짜(PK, 날짜)
• var_a(PK, FK, INT)
• var_b(PK, FK, INT)
• rel_ab(십진수)

여기서 rel_ab 是描述给定日期 2 个变量 var_a 和 var_b 之间关系的列。 （var_a 和 var_b는 다른 테이블을 의미함)

데이터는 매일 일괄 업로드되며, 하루 총 약 700만 행이 업로드됩니다. 문제는 불과 몇 주 후에 각각의 새로운 일일 배치를 업로드하는 데 몇 시간이 걸리기 시작했다는 것입니다. 분명히 우리는 테이블 디자인을 개선해야 합니다. 다음은 양식에 대한 몇 가지 추가 세부정보입니다.

• 우리는 COMPRESSION="zlib"를 사용합니다.
• 복합 기본 키 외에도 var_a 和 var_b 열의 외래 키에 필요한 인덱스도 생성합니다.
• 이 테이블에서 데이터를 추출할 때는 항상 특정 날짜에 사용하세요 的查询 SELECT * FROM table WHERE date = . 선택에는 몇 분 밖에 걸리지 않습니다.
• (거의 확실하게) var_a 和 var_b 에서 참조하는 테이블에서 항목을 삭제할 이유가 없습니다.
• 데이터는 pandas 함수 df.to_sql('temp', con, if_exists='replace', index=False, method='multi')에 의해 업로드됩니다. 여기에 무시 를 삽입합니다. temp< /code>를 df.to_sql('temp', con, if_exists='replace', index=False, method='multi') 上传，我们在其中插入忽略 < code>temp 到 table，然后删除 temp로 변경한 다음 temp를 삭제하세요.

그래서 저는 다음 중 적어도 하나를 할 계획입니다:

• 열의 외래 키 제약 조건을 제거하고 var_a 和 var_b 데이터 업로드 프로세스를 활용하여 모든 작업을 올바르게 수행하세요. 이는 우리 사용 사례에서는 어떤 인덱스도 실제로 쿼리 속도를 향상시키지 않기 때문입니다.
• 테이블을 날짜별로 여러 테이블로 나눕니다. 예를 들어 table_230501 的表，其中包含 var_a、var_b、rel_ab라는 열이 있습니다. 이는 한 번에 하나의 날짜만 선택하기 때문입니다.

첫 번째 솔루션은 데이터 무결성을 위협할 수 있고 두 번째 솔루션은 우리 아키텍처를 엉망으로 만들 수 있다는 것을 알고 있습니다. 제한된 경험으로 인해 두 번째 옵션에 대해서도 들어본 적이 없으며 온라인에서 이 디자인의 예를 찾을 수 없습니다. 이러한 옵션 중 합리적인 솔루션이 있습니까? 둘 다 업로드 속도를 높이고 디스크 사용량을 줄이지만 둘 다 단점이 있습니다. 그렇지 않으면 업로드 속도를 높이는 다른 방법이 있습니까?

편집: 내 SHOW CREATE TABLE는

처럼 보여야 합니다. 으으으으

Answer 1

MySQL 테이블의 업로드 속도를 향상시키는 데 도움이 될 수 있는 몇 가지 잠재적인 솔루션이 있습니다.

var_a 및 var_b에서 인덱스 제거: 쿼리 속도를 높이기 위해 이러한 인덱스를 사용하지 않으므로 인덱스를 제거하면 업로드 프로세스 속도를 높이는 데 도움이 될 수 있습니다. 그러나 외래 키 제약 조건을 사용하는 경우 일반적으로 외래 키에 속한 열에 대한 인덱스를 유지하는 것이 좋습니다.

날짜별로 테이블 분할: 분할을 사용하면 데이터베이스가 특정 쿼리에 대해 관련 파티션만 검색할 수 있으므로 쿼리 성능이 향상됩니다. 그러나 유지 관리 및 백업이 더욱 복잡해지기 때문에 쿼리가 이미 잘 수행되고 있는 경우에는 필요하지 않을 수도 있습니다.

대량 삽입 방법 사용: df.to_sql을 사용하여 개별 행을 삽입하는 대신 LOAD DATA INFILE 또는 MySQL 대량 삽입 API와 같은 대량 삽입 방법을 사용해 볼 수 있습니다. 특히 한 번에 한 행이 아닌 대량으로 데이터를 업로드할 수 있는 경우 개별적으로 삽입하는 것보다 빠릅니다.

다른 압축 알고리즘 사용: 현재 zlib 압축을 사용하고 있지만 데이터에 더 빠르고 효율적일 수 있는 다른 압축 알고리즘이 있습니다. 다양한 압축 옵션을 시도하여 업로드 속도가 향상되는지 확인할 수 있습니다.

서버 리소스 늘리기: 예산과 리소스가 있는 경우 서버 하드웨어를 업그레이드하거나 서버 수를 늘리면 업로드 속도를 높이는 데 도움이 될 수 있습니다. 이는 모든 사람에게 실행 가능한 옵션이 아닐 수 있지만 다른 옵션을 모두 사용했다면 고려해 볼 가치가 있습니다.

제안한 옵션과 관련하여 외래 키 제약 조건을 제거하면 데이터 무결성 문제가 발생할 수 있으므로 이 접근 방식을 권장하지 않습니다. 쿼리에 이미 성능 문제가 있는 경우 날짜별로 분할하는 것이 좋은 솔루션일 수 있지만 쿼리가 이미 빠르게 실행되고 있는 경우에는 필요하지 않을 수 있습니다.

Answer 2

업로드 속도를 높이려면 삭제하세요. 진지하게, 당신이 하고 있는 유일한 일이 특정 날짜의 파일에 있는 내용을 정확히 얻는 것이라면 왜 데이터를 테이블에 입력합니까? (귀하의 의견에서는 하나의 파일이 실제로는 여러 개의 파일이라는 점을 지적하고 있습니다. 먼저 파일을 결합하는 것이 좋을 수도 있습니다.)

테이블의 데이터가 꼭 필요한 경우 이에 대해 논의해 보세요...

• 색인을 결정하기 전에 모든 주요 쿼리를 반드시 살펴보아야 합니다.
• PK의 열 순서는 로드와 쿼리 모두에 중요합니다.
• 파티셔닝은 로딩에는 도움이 될 수 있지만 쿼리에는 도움이 되지 않을 것입니다. 예외: "오래된" 데이터를 삭제합니까?
• 제공해 주세요显示创建表 제공한 내용에 미묘한 부분이 누락되었을 수 있습니다.
• 로딩은 어떻게 이루어지나요? 엄청난 加载数据? 한 번에 한 행씩 삽입하지 않기를 바랍니다. 팬더가 어떻게 작동하는지 모르겠습니다. (또한 MySQL 액세스를 "단순화"하는 다른 99개 패키지가 어떻게 작동하는지 알지 못합니다.) 이 패키지가 뒤에서 무엇을 하는지 이해하십시오. 더 나은 성능을 얻으려면 Pandas를 우회해야 할 수도 있습니다. 대량 로드는 행별 로드보다 최소 10배 빠릅니다.
• 로드하는 동안 임시 테이블의 필요성을 본 적이 없습니다. 아마도. 제안한 대로 FK를 제거하면 쿼리를 실행하여 다른 테이블에 var_a 및 var_b가 있는지 확인할 수 있습니다. 그것이 바로 "아날로그 FK"입니다.
• 가능하다면 들어오는 데이터를 PK를 기준으로 정렬하세요. (이것이 어쩌면 경제 성장 둔화의 원인일 수도 있습니다.)
• 보조키도 있나요? 로딩 속도에 영향을 미칩니다.
• FK가 다른 테이블의 인덱스를 암시하는 것 같습니다.
• 다른 테이블에 새 행을 추가하시나요?
• "rel_ab (DECIMAL)" - 소수점 이하 몇 자리입니까? 정확한 진술은 무엇입니까? 일종의 측정이라면 FLOAT를 고려해 보셨나요?
• 이제 다른 테이블에는 많은 행이 있습니다. 즉, 이를 참조하려면 실제로 4바이트 INT가 필요합니까? 3바이트로 전환 MEDIUMINT [UNSIGNED] 하루에 최소 7MB를 절약하세요.
• SELECT에서 700만 행을 어떻게 처리하나요?
• 압축이 없습니다. InnoDB는 매우 비효율적입니다. 4개의 열 중 하나만 압축할 수 있습니다. 압축에는 추가 buffer_pool_space가 필요합니다. 압축은 CPU를 많이 사용합니다. InnoDB의 경우 2배 축소가 일반적입니다.

여러 개의 "동일한" 테이블은 항상 현명하지 않습니다. 테이블은 항상 더 좋습니다. 그러나 위에서 제안한 것처럼 0 테이블이 여전히 더 좋습니다.