Bagaimana untuk menyelesaikan masalah yang dihadapi oleh peningkatan auto kunci utama MySQL

1. Mengapa tidak menggunakan UUID

Jadi, jika kita menggunakan rentetan UUID sebagai kunci utama, ia akan menyebabkan setiap kali data dimasukkan, kita perlu mencari kedudukannya sendiri dalam B+Tree , selepas mencarinya, adalah mungkin untuk mengalihkan nod berikutnya (sama seperti memasukkan rekod ke dalam tatasusunan, mengalihkan nod berikutnya mungkin melibatkan pemisahan halaman, dan kecekapan sisipan akan dikurangkan).

Sebaliknya, dalam indeks bukan berkelompok, nod daun menyimpan nilai kunci primer Jika kunci utama ialah rentetan UUID yang panjang, ia akan menduduki ruang storan yang lebih besar (berbanding dengan int dan In perkataan lain), maka bilangan nilai kunci utama yang boleh disimpan oleh nod daun yang sama akan dikurangkan, yang boleh menyebabkan pokok menjadi lebih tinggi, yang bermaksud bahawa bilangan IO semasa pertanyaan meningkat dan kecekapan pertanyaan berkurangan.

Berdasarkan analisis di atas, kami cuba untuk tidak menggunakan UUID sebagai kunci utama dalam MySQL Tanpa UUID, sesetengah rakan mungkin berfikir, bolehkah saya menggunakan kunci utama untuk meningkat secara automatik?

Peningkatan automatik kunci utama jelas boleh menyelesaikan dua masalah yang dihadapi apabila menggunakan UUID sebagai kunci utama. Kunci utama adalah auto-increment Anda hanya perlu menambahkannya ke hujung pokok setiap kali, ia tidak akan melibatkan masalah pembahagian halaman ruang penyimpanan yang diduduki agak kecil Bagi yang tidak berkelompok Kesan pengindeksan juga akan menjadi lebih kecil.

Jadi, adakah penambahan auto kunci utama merupakan penyelesaian terbaik? Adakah terdapat sebarang isu yang perlu diberi perhatian apabila kunci utama dinaikkan secara automatik?

2. Masalah kenaikan auto kunci utama

Kandungan berikut mempunyai premis yang sama, iaitu jadual kami mempunyai kenaikan automatik kunci utama.

Secara umumnya, tiada masalah dengan penambahan automatik kunci utama. Walau bagaimanapun, jika anda berada dalam persekitaran konkurensi tinggi, akan ada masalah.

Pertama sekali, perkara paling mudah untuk difikirkan ialah masalah titik panas ekor yang berlaku semasa pemasukan serentak tinggi, setiap orang perlu menanyakan nilai ini dan kemudian mengira nilai kunci utama mereka sendiri terikat kunci utama adalah Ia akan menjadi data panas, dan persaingan kunci akan berlaku di sini semasa sisipan serentak.

Untuk menyelesaikan masalah ini, kita perlu memilih mana yang sesuai untuk kita innodb_autoinc_lock_mode.

2.1 Tiga bentuk sisipan data

Pertama sekali, apabila kita memasukkan data ke dalam jadual data, biasanya terdapat tiga bentuk yang berbeza, seperti berikut:

• insert into user(name) values('javaboy') atau replace into user(name) values('javaboy'), sisipan jenis ini tanpa subkueri bersarang dan boleh menentukan bilangan baris tertentu untuk dimasukkan dipanggil simple insert, tetapi perlu diingat bahawa INSERT ... ON DUPLICATE KEY UPDATE tidak bukan simple insert.

• load data atau insert into user select ... from ...., ini adalah sisipan kelompok, dipanggil bulk insert Satu ciri sisipan kelompok ini ialah bilangan keping data yang hendak disisipkan tidak diketahui permulaannya.

• insert into user(id,name) values(null,'javaboy'),(null,'江南一点雨'), ini juga adalah sisipan kelompok, tetapi ia berbeza daripada yang kedua Ia mengandungi beberapa nilai yang dijana secara automatik (kunci utama dalam kes ini dinaikkan secara automatik) ), dan boleh menentukan jumlah baris yang disisipkan, ini dipanggil mixed insert, yang juga sejenis INSERT ... ON DUPLICATE KEY UPDATE untuk mixed insert yang disebut dalam titik pertama di atas.

Sisipan data dibahagikan kepada tiga kategori ini, terutamanya kerana apabila kunci utama ditambah, skema pemprosesan kunci adalah berbeza.

2.2 innodb_autoinc_lock_mode

Kami boleh mengawal cara kunci MySQL diproses apabila kunci utama ditambah secara automatik dengan mengawal nilai pembolehubah innodb_autoinc_lock_mode.

Pembolehubah innodb_autoinc_lock_mode mempunyai tiga nilai berbeza:

• 0: Ini mewakili tradisional dalam mod ini, tiga nilai berbeza yang kami nyatakan di atas Apabila memasukkan SQL, penyelesaian untuk kunci kenaikan automatik adalah sama Pada permulaan pernyataan SQL yang dimasukkan, kunci AUTO-INC peringkat jadual diperolehi, dan kemudian kunci dilepaskan selepas pelaksanaan SQL yang dimasukkan itu selesai ialah ia boleh memastikan bahawa kunci utama auto-increment berterusan semasa pemasukan kelompok.

• 1: Ini bermakna berturut-turut Dalam mod ini, beberapa pengoptimuman telah dibuat kepada simple insert (yang boleh menentukan bilangan baris yang disisipkan, sepadan dengan dua situasi 1 dan 3. di atas), kerana simple insert mudah untuk mengira bilangan baris untuk dimasukkan, beberapa nilai berturut-turut boleh dijana pada satu masa dan digunakan dalam penyata SQL sisipan yang sepadan Dengan cara ini, kunci AUTO-INC boleh dilepaskan pendahuluan, yang boleh mengurangkan menunggu kunci dan meningkatkan kecekapan pemasukan.

• 2: Ini bermakna bersilang Dalam kes ini, tiada kunci AUTO-INC Kami memprosesnya satu persatu adalah bertambah, ia tidak soalan Berterusan.

Seperti yang anda boleh lihat dari pengenalan di atas, sebenarnya, jenis ketiga, iaitu, apabila nilai innodb_autoinc_lock_mode ialah 2, kecekapan concurrency adalah yang paling kuat, begitu juga dengan menetapkan innodb_autoinc_lock_mode =2?

Ia bergantung kepada keadaan.

Brother Song sebelum ini telah menulis artikel untuk memperkenalkan kepada rakan-rakannya tiga format fail log binlog MySQL:

• baris: Apa yang direkodkan dalam binlog adalah khusus nilai. Daripada SQL asal, mari kita ambil contoh mudah Katakan medan dalam jadual ialah UUID, dan SQL yang dilaksanakan oleh pengguna ialah insert into user(username,uuid) values('javaboy',uuid()), maka SQL akhirnya direkodkan dalam binlog ialah insert into user(username,uuid) values('javaboy',‘0212cfa0-de06-11ed-a026-0242ac110004’).

• pernyataan: Apa yang direkodkan dalam binlog ialah SQL asal dengan mengambil satu baris sebagai contoh, apa yang akhirnya direkodkan dalam binlog ialah insert into user(username,uuid) values('javaboy',uuid()).

• bercampur: Dalam mod ini, MySQL akan menentukan format log berdasarkan pernyataan SQL tertentu, iaitu, pilih antara pernyataan dan baris.

Untuk ketiga-tiga mod berbeza ini, adalah jelas bahawa semasa replikasi induk-hamba, mod pernyataan boleh menyebabkan ketidakkonsistenan data induk-hamba, jadi kini format binlog lalai MySQL ialah row .

Kembali kepada soalan kami:

• Jika format binlog adalah baris, maka kita boleh menetapkan nilai innodb_autoinc_lock_mode kepada 2, untuk memastikan keselarasan data yang terbaik takat Keupayaan untuk memasukkan tidak menyebabkan masalah ketidakkonsistenan data tuan-hamba.

• Jika format binlog ialah pernyataan, lebih baik kita tetapkan nilai innodb_autoinc_lock_mode kepada 1, yang meningkatkan keupayaan sisipan serentak simple insert Untuk sisipan kelompok, adalah lebih baik untuk mendapatkan AUTO-INC terlebih dahulu Kunci dilepaskan selepas pemasukan berjaya Ini juga boleh mengelakkan ketidakkonsistenan data tuan-hamba dan memastikan keselamatan replikasi data.

• Dua mata di atas adalah terutamanya untuk enjin storan InnoDB Jika ia adalah enjin storan MyISAM, kunci AUTO-INC diperoleh dahulu, dan kemudian dilepaskan selepas pemasukan selesai. yang bersamaan dengan pasangan nilai pembolehubah innodb_autoinc_lock_mode MyISAM tidak berfungsi.

2.3 Amalan

Seterusnya, mari gunakan SQL mudah untuk menunjukkan kepada rakan-rakan kita bagaimana nilai berbeza dari innodb_autoinc_lock_mode sepadan dengan hasil yang berbeza.

Kami boleh menggunakan pertanyaan SQL berikut untuk melihat tetapan semasa innodb_autoinc_lock_mode:

Seperti yang anda lihat, nilai lalai semasa versi 8.0. 32 yang saya gunakan ialah 2.

Saya akan menukarnya kepada 0 dahulu Kaedah pengubahsuaian adalah dengan menambah baris /etc/my.cnf dalam fail innodb_autoinc_lock_mode=0:

Selepas perubahan. selesai, mulakan semula paparan , seperti berikut:

Seperti yang anda lihat, ia telah diubah sekarang.

Sekarang andaikan saya mempunyai jadual berikut:

CREATE TABLE `user` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=100 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Kenaikan ini bermula dari 100. Sekarang andaikan saya mempunyai sisipan SQL berikut:

insert into user(id,username) values(1,'javaboy'),(null,'江南一点雨'),(3,'www.javaboy.org'),(null,'lisi');

Selepas pemasukan selesai, kita Mari lihat hasil pertanyaan:

Menurut pengenalan kami sebelum ini, situasi ini sepatutnya boleh dijelaskan, jadi saya tidak akan menerangkan butiran di sini.

Seterusnya, saya menukar nilai innodb_autoinc_lock_mode kepada 1, seperti berikut:

Masih SQL yang sama di atas, mari laksanakannya semula. Selepas pelaksanaan selesai, hasilnya adalah sama seperti di atas.

Tetapi! ! ! **Selepas SQL di atas dilaksanakan, jika kami ingin memasukkan data sekali lagi, dan ID yang baru dimasukkan tidak menyatakan nilai, kami mendapati bahawa nilai ID yang dijana secara automatik ialah 104. **Ini kerana kami menetapkan innodb_autoinc_lock_mode=1 Pada masa ini, apabila melaksanakan simple insert sisipan, sistem melihat bahawa saya ingin memasukkan 4 rekod dan terus mengeluarkan 4 ID untuk saya terlebih dahulu, iaitu 100 dan 101. , 102 dan 103. Akibatnya, SQL sebenarnya hanya menggunakan dua ID, dan dua yang selebihnya tidak berguna, tetapi sisipan seterusnya masih akan bermula dari 104.

Atas ialah kandungan terperinci Bagaimana untuk menyelesaikan masalah yang dihadapi oleh peningkatan auto kunci utama MySQL. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!