Memasukkan ke dalam jadual MySQL yang besar tanpa kunci utama auto-increment adalah sangat perlahan

Question

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.

Versi MySQL ialah 8.0.24, dijalankan pada Windows Server 2016. Setahu saya, sumber pelayan tidak pernah terlebih beban. Pelayan mempunyai overhed CPU yang mencukupi dan 32GB RAM diperuntukkan kepadanya.

Ini ialah jadual yang saya gunakan:

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

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.

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.

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:

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` = ?

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

Jenis pertanyaan kedua pastinya boleh diperbaiki dengan indeks, tetapi saya bimbang ini akan merendahkan lagi prestasi INSERT.

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.

Edit 2: TUNJUKKAN STATUS JADUAL hasil jadual segmen_simpan, dan jadual yang hampir sama tangkapan_simpan Terdapat indeks tambahan pada lajur null

Nama	Enjin	Versi	Format baris	OK	Purata panjang baris	Panjang data	Panjang data maksimum	Index_length	Tiada data	Autokenaikan	Masa penciptaan	Dikemas kini	Semak masa	Organisasi	Checksum	Pilihan penciptaan	Ulasan
Tangkapan skrin disimpan	InnoDB	10	Berita	483430208	61	29780606976	0	21380464640	6291456	Kosong	“21-10-2021 01:03:21”	“2022-11-07 16:51:45”	Kosong	utf8mb4_0900_ai_ci	Kosong
Segmen yang disimpan	InnoDB	10	Berita	281861164	73	20802699264	0	0	4194304	Kosong	“2022-11-02 09:03:05”	“2022-11-07 16:51:22”	Kosong	utf8mb4_0900_ai_ci	Kosong

Answer 1

Saya akan keluar sendiri dengan jawapan ini.

Hipotesis

Nilai
• innodb_buffer_pool_size kurang sedikit daripada 20MB, dan
• 1K pilihan sesaat tiba di bahagian rawak jadual, kemudian

Sistem telah menjadi terikat I/O sejak kebelakangan ini, kerana blok "seterusnya" yang diperlukan untuk Pilih seterusnya semakin kerap tidak dicache dalam buffer_pool.

Penyelesaian mudah adalah untuk mendapatkan lebih banyak RAM dan meningkatkan tetapan boleh melaras ini. Tetapi jadual hanya akan berkembang ke had seterusnya yang anda beli.

Sebaliknya, berikut adalah beberapa penyelesaian separa.

• Jika nombor tidak terlalu besar, dua lajur pertama mungkin INT UNSIGNED（4 个字节而不是 8），甚至可能是 MEDIUMINT UNSIGNED（3 个字节） ）。注意 ALTER TABLE mengunci meja untuk masa yang lama.
• Masa mula dan tamat ini kelihatan seperti cap masa dengan pecahan saat dan sentiasa ".000". DATETIME 和 TIMESTAMP Mengambil 5 bait (bukannya 8 bait).
• Contoh anda menunjukkan masa berlalu sebanyak 0. Jika (permulaan akhir) biasanya sangat kecil, menyimpan masa berlalu dan bukannya masa tamat akan mengecilkan lagi data. (Tetapi menggunakan masa akhir boleh membuat keadaan mengelirukan).
• Data contoh yang anda berikan kelihatan "berterusan". Ini adalah sama cekapnya dengan kenaikan automatik. Adakah ini norma? Jika tidak, INSERT mungkin sebahagian daripada I/O thrashing.
• Anda mencadangkan menambah kecerdasan buatan serta indeks sekunder, yang menggandakan kerja memasukkan, jadi saya tidak mengesyorkannya.

Lagi

Ya, begitulah keadaannya.

Menggunakannya sebagai permulaan INDEX，或者更好的是，作为 PRIMARY KEY akan memberi anda bantuan terbaik dengan kedua-dua pertanyaan anda:

(recording_id, index)

Balas:

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

Jika ia digunakan untuk mengawal beberapa SQL lain, pertimbangkan untuk menambahkannya pada SQL yang lain:

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

Pertanyaan ini (dalam mana-mana bentuk) memerlukan kandungan yang anda sudah ada

PRIMARY KEY(recording_id, index)

Perlu pertanyaan anda yang lain

INDEX(recording_id, start_filetime)

Jadi, tambah indeks, atau ...

Lebih baik... Gabungan ini lebih baik untuk kedua-duanya : SELECT

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

Dengan gabungan ini,

Semakan kewujudan baris tunggal akan dilakukan "menggunakan indeks" kerana ia "dilindungi".
Pertanyaan lain akan mendapati semua baris yang berkaitan dikumpulkan bersama pada PK.
(PK mempunyai 3 lajur ini kerana ia perlu unik. Mempunyainya dalam susunan ini adalah baik untuk pertanyaan kedua anda. Juga ia adalah PK, bukan hanya INDEX, jadi ia tidak perlu berada di antara BTree dalam indeks Btree antara lantunan dan data)
"Pengelompokan"
• boleh meningkatkan prestasi dengan mengurangkan bilangan blok cakera yang diperlukan untuk pertanyaan sedemikian. Ini mengurangkan "belasah" dalam buffer_pool, dengan itu mengurangkan keperluan untuk meningkatkan RAM.
Cadangan pengindeksan saya kebanyakannya ortogon dengan cadangan jenis data saya.