<p>Saya baru-baru ini melihat peningkatan ketara dalam perbezaan masa yang diperlukan untuk melengkapkan pernyataan INSERT yang mudah. Walaupun kenyataan ini mengambil masa kira-kira 11 milisaat secara purata, kadangkala ia boleh mengambil masa 10-30 saat, malah saya perasan ia mengambil masa lebih daripada 5 minit untuk dilaksanakan. </p> <p>Versi MySQL ialah <code>8.0.24</code>, dijalankan pada Windows Server 2016. Setahu saya, sumber pelayan tidak pernah terlebih beban. Pelayan mempunyai overhed CPU yang mencukupi dan 32GB RAM diperuntukkan kepadanya. </p> <p>Ini ialah jadual yang saya gunakan: </p> <pre class="brush:php;toolbar:false;">BUAT JADUAL `saved_segment` ( `recording_id` bigint unsigned NOT NULL, `index` bigint unsigned NOT NULL, `start_filetime` bigint unsigned NOT NULL, `end_filetime` bigint unsigned NOT NULL, `offset_and_size` bigint unsigned NOT NULL DEFAULT '18446744073709551615', `id_storage` tinyint tidak ditandatangani BUKAN NULL, KUNCI UTAMA (`id_rakaman`,`indeks`) ) ENJIN=CHARSET LALAI InnoDB=utf8mb4 COLLATE=utf8mb4_0900_ai_ci</pre> <p>Jadual ini tidak mempunyai indeks lain atau kunci asing, dan tidak digunakan sebagai rujukan kepada kunci asing dalam mana-mana jadual lain. Keseluruhan saiz meja adalah kira-kira 20GB dan bilangan baris adalah kira-kira 281M, yang saya rasa tidak terlalu besar. </p> <p>Jadual digunakan hampir keseluruhannya dalam mod baca sahaja, dengan sehingga 1000 bacaan sesaat. Semua bacaan ini berlaku dalam pertanyaan SELECT yang mudah dan bukannya transaksi yang rumit, dan mereka menggunakan indeks kunci utama dengan cekap. Terdapat sangat sedikit, jika ada, penulisan serentak pada jadual ini. Ini dilakukan dengan sengaja untuk mencuba dan mengetahui sama ada ia akan membantu dengan sisipan perlahan, tetapi tidak. Sehingga itu, sentiasa ada sehingga 10 sisipan serentak sedang dijalankan. Kenyataan KEMASKINI atau PADAM tidak akan dilaksanakan pada jadual ini. </p> <p>Pertanyaan yang saya ada masalah semuanya dibina dengan cara ini. Mereka tidak pernah muncul dalam transaksi.Walaupun sisipan berdasarkan kunci primer berkelompok pastinya bukan tambahan sahaja, pertanyaan akan hampir sentiasa memasukkan antara 1 dan 20 baris bersebelahan ke dalam jadual: </p> <pre class="brush:php;toolbar:false;">MASUKKAN ABAIKAN KE dalam saved_segment (id_rakaman, `indeks`, masa mula_fail, masa_fail_akhir, mengimbangi_dan_saiz, id_storan) VALUES (19173, 631609, 133121662986640000, 133121663016640000, 20562291758298876, 10), (19173, 631610, 133121663016640000, 133121663046640000, 20574308942546216, 10), (19173, 631611, 133121663046640000, 133121663076640000, 20585348350688128, 10), (19173, 631612, 133121663076640000, 133121663106640000, 20596854568114720, 10), (19173, 631613, 133121663106640000, 133121663136640000, 20609723363860884, 10), (19173, 631614, 133121663136640000, 133121663166640000, 20622106425668780, 10), (19173, 631615, 133121663166640000, 133121663196640000, 20634653501528448, 10), (19173, 631616, 133121663196640000, 133121663226640000, 20646967172721148, 10), (19173, 631617, 133121663226640000, 133121663256640000, 20657773176227488, 10), (19173, 631618, 133121663256640000, 133121663286640000, 20668825200822108, 10)</pra> <p>Ini ialah output pernyataan EXPLAIN untuk pertanyaan di atas: </p> <table class="s-table"> <kepala> <tr> <th>id</th> <th>Pilih jenis</th> <th>Jadual</th> <th>Partition</th> <th>Jenis</th> <th>Kekunci yang mungkin</th> <th>key</th> <th>key_len</th> <th>Rujukan</th> <th>OK</th> <th>Disaring</th> <th>Tambahan</th> </tr> </kepala> <tbody> <tr> <td>1</td> <td>Sisipkan</td> <td>Segmen yang disimpan</td> <td>Kosong</td> <td>Semua</td> <td>Kosong</td> <td>Kosong</td> <td>Kosong</td> <td>Kosong</td> <td>Kosong</td> <td>Kosong</td> <td>Kosong</td> </tr> </tbody> </table> <p>Isu ini agak baharu dan tidak ketara apabila jadual adalah dua kali lebih kecil. </p> <p>Saya cuba mengurangkan bilangan sisipan serentak dalam jadual daripada kira-kira 10 kepada 1. Saya juga mengalih keluar kekunci asing (<code>recording_id</code>) pada beberapa lajur untuk mempercepatkan lagi sisipan. <kod>Menganalisis jadual</kod> dan analisis skema tidak menghasilkan sebarang maklumat yang boleh diambil tindakan.</p> <p>Satu penyelesaian yang saya fikirkan ialah mengalih keluar kunci primer berkelompok dan menambah kunci utama auto-kenaikan dan indeks biasa pada lajur <kod>(id_rakaman, indeks)</kod> Pada pendapat saya ini akan membantu menjadikan sisipan "tambahan sahaja".Saya terbuka kepada mana-mana dan semua cadangan, terima kasih terlebih dahulu! </p> <p>Edit: Saya akan menangani beberapa perkara dan soalan yang dibangkitkan dalam ulasan dan jawapan: </p> <ul> <li><kod>autokomit</kod> ditetapkan kepada <kod>HIDUP</kod></li> Nilai <li><code>innodb_buffer_pool_size</code> ialah <code>21474836480</code>, dan nilai <code>innodb_buffer_pool<.code>4< 28< ;/kod> </ li> <li>Satu ulasan membangkitkan kebimbangan tentang perbalahan antara kunci baca yang digunakan untuk membaca dan kunci eksklusif yang digunakan untuk menulis. Jadual digunakan agak seperti cache, saya tidak perlu membaca untuk sentiasa mencerminkan keadaan terkini jadual jika ia bermakna peningkatan dalam prestasi. Walau bagaimanapun, jadual harus kekal tahan lama walaupun sekiranya berlaku ranap pelayan dan kegagalan perkakasan. Bolehkah ini dicapai dengan tahap pengasingan transaksi yang lebih santai? </li> <li>Seni bina pasti boleh dioptimumkan; <kod>recording_id</kod> start_filetime</code> ; Saya takut perubahan ini hanya akan menangguhkan masalah untuk seketika sehingga saiz jadual meningkat untuk mengimbangi ruang yang disimpan. </li> <li>Sisipan ke dalam jadual sentiasa berterusan PILIHAN yang dilakukan pada jadual kelihatan seperti ini: </li> </ul> <pre class="brush:php;toolbar:false;">SELECT TRUE DARI saved_segment WHERE recording_id = ? DAN `index` = ?</pre> <pre class="brush:php;toolbar:false;">SELECT indeks, start_filetime, end_filetime, offset_and_size, storage_id DARI saved_segment DI MANA recording_id = ? start_filetime >= ? start_filetime <= ? PESANAN MENGIKUT `index` ASC</pre> <p>Jenis pertanyaan kedua pastinya boleh diperbaiki dengan indeks, tetapi saya bimbang ini akan merendahkan lagi prestasi INSERT. </p> <p>Satu lagi perkara yang saya terlupa untuk nyatakan ialah jadual yang hampir serupa dengan ini wujud. Ia bertanya dan memasukkan sama, tetapi boleh menyebabkan kebuluran IO selanjutnya. </p> <p>Edit 2: <kod>TUNJUKKAN STATUS JADUAL</kod> hasil jadual <kod>segmen_simpan</kod>, dan jadual yang hampir sama <kod>tangkapan_simpan</kod> Terdapat indeks tambahan pada lajur null</code></p> <table class="s-table"> <kepala> <tr> <th>Nama</th> <th>Enjin</th> <th>Versi</th> <th>Format baris</th> <th>OK</th> <th>Purata panjang baris</th> <th>Panjang data</th> <th>Panjang data maksimum</th> <th>Index_length</th> <th>Tiada data</th> <th>Autokenaikan</th> <th>Masa penciptaan</th> <th>Dikemas kini</th> <th>Semak masa</th> <th>Organisasi</th> <th>Checksum</th> <th>Pilihan penciptaan</th> <th>Ulasan</th> </tr> </kepala> <tbody> <tr> <td>Tangkapan skrin disimpan</td> <td>InnoDB</td> <td>10</td> <td>Berita</td> <td>483430208</td> <td>61</td> <td>29780606976</td> <td>0</td> <td>21380464640</td> <td>6291456</td> <td>Kosong</td> <td>“21-10-2021 01:03:21”</td> <td>“2022-11-07 16:51:45”</td> <td>Kosong</td> <td>utf8mb4_0900_ai_ci</td> <td>Kosong</td> <td></td> <td></td> </tr> <tr> <td>Segmen yang disimpan</td> <td>InnoDB</td> <td>10</td> <td>Berita</td> <td>281861164</td> <td>73</td> <td>20802699264</td> <td>0</td> <td>0</td> <td>4194304</td> <td>Kosong</td> <td>“2022-11-02 09:03:05”</td> <td>“2022-11-07 16:51:22”</td> <td>Kosong</td> <td>utf8mb4_0900_ai_ci</td> <td>Kosong</td> <td></td> <td></td> </tr> </tbody> </table></p>

(recording_id, index)

SELECT  TRUE
FROM    saved_segment
WHERE   recording_id = ? AND `index` = ?

... EXISTS ( SELECT 1
        FROM    saved_segment
        WHERE   recording_id = ? AND `index` = ? ) ...

PRIMARY KEY(recording_id, index)

INDEX(recording_id, start_filetime)

PRIMARY KEY(recording_id, start_filetime, index).
INDEX(recording_id, index)