Adakah bijak untuk mencipta jadual yang berbeza untuk data yang sama untuk setiap tarikh?

Question

Saya mempunyai jadual MYSQL InnoDBtable dengan lajur berikut (nama jadual dan lajur ditukar):

• Tarikh (PK, tarikh)
• var_a (PK, FK, INT)
• var_b (PK, FK, INT)
• rel_ab(perpuluhan)

di mana rel_ab 是描述给定日期 2 个变量 var_a 和 var_b 之间关系的列。 （var_a 和 var_b merujuk kepada jadual yang berbeza)

Data dimuat naik dalam kelompok setiap hari, berjumlah kira-kira 7 juta baris setiap hari. Masalahnya ialah, selepas hanya beberapa minggu, ia mula mengambil masa berjam-jam untuk memuat naik setiap kumpulan harian baharu. Jelas sekali kami perlu menambah baik reka bentuk meja kami. Berikut ialah beberapa butiran tambahan tentang borang kami.

• Kami menggunakan COMPRESSION="zlib".
• Selain kunci utama komposit, kami juga mencipta indeks yang diperlukan untuk kunci asing pada lajur var_a 和 var_b.
• Apabila kami mengekstrak data daripada jadual ini, sentiasa gunakan untuk tarikh tertentu 的查询 SELECT * FROM table WHERE date = . Pemilihan hanya mengambil masa beberapa minit.
• Kami (hampir pasti) tidak akan mempunyai sebab untuk memadamkan entri daripada jadual yang dirujuk oleh var_a 和 var_b .
• Data dimuat naik oleh fungsi panda df.to_sql('temp', con, if_exists='replace', index=False, method='multi'), di mana kami memasukkan abaikan temp< /code> kepada df.to_sql('temp', con, if_exists='replace', index=False, method='multi') 上传，我们在其中插入忽略 < code>temp 到 table，然后删除 temp, kemudian padamkan temp.

Jadi saya bercadang untuk melakukan sekurang-kurangnya satu daripada perkara berikut:

• Alih keluar kekangan kunci asing pada lajur var_a 和 var_b dan bergantung pada proses muat naik data untuk melakukan semuanya dengan betul. Ini kerana kedua-dua indeks sebenarnya tidak meningkatkan kelajuan pertanyaan dalam kes penggunaan kami.
• Bahagikan jadual kepada jadual yang berbeza untuk setiap tarikh. Sebagai contoh, saya mempunyai lajur yang dipanggil table_230501 的表，其中包含 var_a、var_b、rel_ab. Ini kerana kami hanya memilih satu tarikh pada satu masa.

Saya tahu bahawa penyelesaian pertama mungkin mengancam integriti data dan penyelesaian kedua akan mengacaukan seni bina kami. Dalam pengalaman terhad saya, saya tidak pernah mendengar tentang pilihan kedua sama ada, dan tidak dapat mencari sebarang contoh reka bentuk ini dalam talian. Adakah mana-mana pilihan ini penyelesaian yang wajar? Kedua-duanya akan meningkatkan kelajuan muat naik dan mengurangkan penggunaan cakera, tetapi kedua-duanya mempunyai kelemahannya. Jika tidak, apakah cara lain untuk meningkatkan kelajuan muat naik?

EDIT: Saya SHOW CREATE TABLE sepatutnya kelihatan seperti

CREATE TABLE table (
  date date NOT NULL,
  var_a int NOT NULL,
  var_b int NOT NULL,
  rel_ab decimal(19,16) NOT NULL,
  PRIMARY KEY (date,`var_a`,`var_b`),
  KEY a_idx (var_a),
  KEY b_idx (var_b),
  CONSTRAINT a FOREIGN KEY (var_a) REFERENCES other_table_a (var_a) ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT b FOREIGN KEY (var_b) REFERENCES other_table_b (var_b) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMPRESSION="zlib"

Answer 1

Terdapat beberapa penyelesaian berpotensi yang boleh membantu anda meningkatkan kelajuan muat naik jadual MySQL anda:

Alih keluar indeks pada var_a dan var_b: Memandangkan anda tidak menggunakan indeks ini untuk mempercepatkan pertanyaan, mengalih keluarnya boleh membantu mempercepatkan proses muat naik. Walau bagaimanapun, jika anda menggunakan kekangan kunci asing, biasanya disyorkan untuk menyimpan indeks pada lajur yang dimiliki oleh kunci asing.

Pecah jadual mengikut tarikh: Pembahagian membantu meningkatkan prestasi pertanyaan kerana ia membenarkan pangkalan data mengimbas hanya partition yang berkaitan untuk pertanyaan tertentu. Walau bagaimanapun, ia juga menjadikan penyelenggaraan dan sandaran lebih kompleks, yang mungkin tidak diperlukan jika pertanyaan anda sudah menunjukkan prestasi yang baik.

Gunakan kaedah sisipan pukal: Daripada memasukkan baris individu menggunakan df.to_sql, anda boleh cuba menggunakan kaedah sisipan pukal seperti LOAD DATA INFILE atau API sisipan pukal MySQL. Ini lebih pantas daripada memasukkan secara individu, terutamanya jika anda boleh memuat naik data secara pukal dan bukannya satu baris pada satu masa.

Gunakan algoritma pemampatan yang berbeza: Anda sedang menggunakan pemampatan zlib, tetapi terdapat algoritma pemampatan lain yang mungkin lebih pantas atau lebih cekap untuk data anda. Anda boleh cuba mencuba pilihan pemampatan yang berbeza untuk melihat sama ada pilihan tersebut meningkatkan kelajuan muat naik.

Tingkatkan sumber pelayan: Jika anda mempunyai belanjawan dan sumber, menaik taraf perkakasan pelayan atau menambah bilangan pelayan boleh membantu meningkatkan kelajuan muat naik. Ini mungkin bukan pilihan yang berdaya maju untuk semua orang, tetapi patut dipertimbangkan jika anda telah kehabisan pilihan anda yang lain.

Mengenai pilihan cadangan anda, mengalih keluar kekangan kunci asing boleh menyebabkan isu integriti data, jadi saya tidak mengesyorkan pendekatan ini. Jika pertanyaan anda sudah mengalami masalah prestasi, pembahagian mengikut tarikh mungkin merupakan penyelesaian yang baik, tetapi jika pertanyaan anda sudah berjalan dengan cepat, ia mungkin tidak diperlukan.

Answer 2

Untuk mempercepatkan muat naik, padamkannya. Serius, jika satu-satunya perkara yang anda lakukan ialah mendapatkan apa yang ada dalam fail untuk tarikh tertentu, mengapa meletakkan data ke dalam jadual? (Ulasan anda menunjukkan bahawa satu fail sebenarnya adalah beberapa fail. Mungkin idea yang baik untuk menggabungkannya dahulu.)

Jika anda benar-benar memerlukan data dalam jadual, mari kita bincangkan perkara ini...

• Sebelum menentukan indeks, kita mesti melihat semua pertanyaan utama.
• Tertib lajur dalam PK adalah penting untuk memuatkan dan membuat pertanyaan.
• Pembahagian mungkin membantu dengan memuatkan, tetapi tidak mungkin membantu dengan pertanyaan. Pengecualian: Adakah anda memadamkan data "lama"?
• Sila berikan显示创建表; mungkin terdapat beberapa kehalusan yang hilang daripada apa yang anda sediakan.
• Bagaimana pemuatan dilakukan? 加载数据 besar? Semoga tidak memasukkan satu baris pada satu masa. Saya tidak tahu bagaimana panda berfungsi. (Anda juga tidak tahu bagaimana 99 pakej lain yang "memudahkan" akses MySQL berfungsi.) Sila fahami apa yang ia lakukan di sebalik tabir. Anda mungkin perlu memintas Panda untuk mendapatkan prestasi yang lebih baik. Pemuatan pukal sekurang-kurangnya 10 kali lebih cepat daripada pemuatan baris demi baris.
• Saya tidak nampak keperluan untuk meja sementara semasa memuatkan. Mungkin. Jika anda mengalih keluar FK (seperti yang anda cadangkan), anda boleh melaksanakan pertanyaan untuk mengesahkan kewujudan var_a dan var_b dalam jadual lain. Itulah "FK analog".
• Jika boleh, susun data masuk berdasarkan PK. (Ini mungkin menjadi punca pertumbuhan ekonomi yang perlahan.)
• Adakah terdapat sebarang kunci tambahan? Mereka menjejaskan kelajuan pemuatan.
• Saya rasa FK anda membayangkan indeks pada jadual lain.
• Adakah anda menambah baris baharu pada jadual lain?
• "rel_ab (PERPULUHAN)" - berapa tempat perpuluhan? Apakah kenyataan yang tepat? Jika ia adalah sejenis ukuran, adakah anda telah mempertimbangkan FLOAT?
• Kini terdapat banyak baris dalam jadual lain. Iaitu, adakah anda benar-benar memerlukan INT 4-bait untuk merujuknya? Tukar kepada 3 Bait MEDIUMINT [UNSIGNED] Jimat sekurang-kurangnya 7MB sehari.
• Bagaimana anda mengendalikan 7 juta baris dalam PILIHAN itu?
• Tiada mampatan. InnoDB sangat tidak cekap. Hanya satu daripada 4 lajur yang boleh dimampatkan. Mampatan memerlukan ruang_kolam_penampan tambahan. Mampatan menggunakan banyak CPU. Untuk InnoDB, pengecutan 2x adalah tipikal.

Berbilang jadual "sama" selalu tidak bijak. Meja sentiasa lebih baik. Walau bagaimanapun, seperti yang dicadangkan di atas, jadual sifar masih lebih baik.