Bagaimana untuk menyelesaikan kebuntuan yang disebabkan oleh gabungan indeks pengoptimuman MySQL

Latar Belakang

Kebuntuan berlaku dalam persekitaran pengeluaran Dengan menyemak log kebuntuan, saya melihat kebuntuan itu disebabkan oleh dua kenyataan kemas kini yang sama (hanya nilai dalam keadaan yang berbeza),

adalah seperti berikut:

UPDATE test_table SET `status` = 1 WHERE `trans_id` = 'xxx1' AND `status` = 0;
UPDATE test_table SET `status` = 1 WHERE `trans_id` = 'xxx2' AND `status` = 0;

Sukar untuk difahami pada mulanya Selepas banyak siasatan dan kajian, saya menganalisis prinsip-prinsip khusus pembentukan kebuntuan suka berkongsi dengan semua orang dengan harapan dapat membantu orang yang menghadapi masalah yang sama.

Disebabkan MySQL banyak ilmu, banyak kata nama tidak akan terlalu banyak diperkenalkan di sini Kawan-kawan yang berminat boleh buat susulan dengan kajian mendalam khas.

Log kebuntuan

*** (1) TRANSACTION:
TRANSACTION 791913819, ACTIVE 0 sec starting index read, thread declared inside InnoDB 4999
mysql tables in use 3, locked 3
LOCK WAIT 4 lock struct(s), heap size 1184, 3 row lock(s)
MySQL thread id 462005230, OS thread handle 0x7f55d5da3700, query id 2621313306 x.x.x.x test_user Searching rows for update
UPDATE test_table SET `status` = 1 WHERE `trans_id` = 'xxx1' AND `status` = 0;
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 110 page no 39167 n bits 1056 index `idx_status` of table `test`.`test_table` trx id 791913819 lock_mode X waiting
Record lock, heap no 495 PHYSICAL RECORD: n_fields 2; compact format; info bits 0

*** (2) TRANSACTION:
TRANSACTION 791913818, ACTIVE 0 sec starting index read, thread declared inside InnoDB 4999
mysql tables in use 3, locked 3
5 lock struct(s), heap size 1184, 4 row lock(s)
MySQL thread id 462005231, OS thread handle 0x7f55cee63700, query id 2621313305 x.x.x.x test_user Searching rows for update
UPDATE test_table SET `status` = 1 WHERE `trans_id` = 'xxx2' AND `status` = 0;
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 110 page no 39167 n bits 1056 index `idx_status` of table `test`.`test_table` trx id 791913818 lock_mode X
Record lock, heap no 495 PHYSICAL RECORD: n_fields 2; compact format; info bits 0

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 110 page no 41569 n bits 88 index `PRIMARY` of table `test`.`test_table` trx id 791913818 lock_mode X locks rec but not gap waiting
Record lock, heap no 14 PHYSICAL RECORD: n_fields 30; compact format; info bits 0

*** WE ROLL BACK TRANSACTION (1)

Analisis ringkas log kebuntuan di atas:

• 1. Baris 1 hingga 9), baris 6 ialah pernyataan SQL yang dilaksanakan oleh transaksi (1), baris 7 dan 8 bermakna transaksi (1) sedang menunggu kunci X pada indeks idx_status

• 2. Dalam blok kandungan kedua (baris 11 hingga baris 19), baris 16 ialah pernyataan SQL yang dilaksanakan oleh transaksi (2), dan baris 17 dan 18 bermakna transaksi (2) memegang indeks idx_status Kunci X dihidupkan ;

• bermaksud: transaksi (2) sedang menunggu untuk memperoleh kunci X pada indeks UTAMA. (tetapi bukan gap bermaksud bukan gap lock)

• 4. Ayat terakhir bermaksud MySQL melancarkan transaksi (1).

Struktur jadual

CREATE TABLE `test_table` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`trans_id` varchar(21) NOT NULL,
`status` int(11) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `uniq_trans_id` (`trans_id`) USING BTREE,
KEY `idx_status` (`status`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8

Seperti yang dapat dilihat daripada struktur jadual, terdapat indeks unik trans_id pada lajur uniq_trans_id , dan terdapat normal indeks pada lajur status idx_status, lajur id ialah indeks kunci utama PRIMARY .

Terdapat dua jenis indeks dalam enjin InnoDB:

• Indeks berkelompok: Letakkan storan data dan indeks bersama , nod daun struktur indeks menyimpan data baris.

• Indeks tambahan: Nod daun indeks tambahan menyimpan nilai kunci utama, iaitu nilai kunci indeks berkelompok.

Indeks kunci utama PRIMARY ialah indeks berkelompok dan data baris akan disimpan dalam nod daun. Indeks uniq_trans_id dan idx_status ialah indeks tambahan dan nod daun menyimpan nilai kunci utama, iaitu nilai lajur id.

Apabila kami mencari data baris melalui indeks tambahan, kami mula-mula mencari id kunci utama melalui indeks tambahan, kemudian melakukan carian kedua melalui indeks kunci primer (juga dipanggil kembali ke jadual), dan akhirnya cari data baris.

Pelan pelaksanaan

Dengan melihat pelan pelaksanaan, anda boleh mendapati bahawa pernyataan kemas kini menggunakan penggabungan indeks, iaitu, pernyataan ini menggunakan kedua-dua uniq_trans_id indeks , dan indeks idx_status digunakan Using intersect(uniq_trans_id,idx_status) bermaksud untuk mendapatkan persimpangan melalui dua indeks.

Mengapa index_merge digunakan?

Sebelum MySQL 5.0, jadual hanya boleh menggunakan satu indeks pada satu masa dan tidak boleh menggunakan berbilang indeks pada masa yang sama untuk imbasan bersyarat. Tetapi bermula dari 5.1, teknologi pengoptimuman index merge telah diperkenalkan dan berbilang indeks boleh digunakan untuk melakukan imbasan bersyarat pada jadual yang sama.

Sebagai contoh, pernyataan dalam pelan pelaksanaan:

UPDATE test_table SET `status` = 1 WHERE `trans_id` = '38' AND `status` = 0 ;

MySQL akan menggunakan indeks trans_id = ‘38’ untuk mencari nilai id yang disimpan dalam nod daun berdasarkan Keadaan uniq_trans_id; pada masa yang sama Berdasarkan keadaan status = 0, indeks idx_status akan digunakan untuk mencari nilai id yang disimpan dalam nod daun, kemudian kedua-dua nilai id yang ditemui akan bersilang, dan akhirnya id persimpangan akan dikembalikan ke jadual, iaitu, nod daun akan ditemui melalui data Baris indeks UTAMA yang disimpan dalam .

Ramai orang mungkin mempunyai soalan di sini uniq_trans_id sudah menjadi indeks unik Hanya satu keping data boleh didapati paling banyak melalui indeks ini. Kemudian mengapa pengoptimum MySQL menggunakan dua indeks untuk mendapatkan persimpangan. , dan kemudian kembali ke jadual untuk membuat pertanyaan. Bukankah ini menambahkan proses carian indeks idx_status lagi? Mari analisa proses pelaksanaan kedua-dua situasi ini.

Yang pertama hanya menggunakan indeks uniq_trans_id:

• Menurut syarat pertanyaan trans_id = ‘38’, gunakan indeks uniq_trans_id untuk mencari data yang disimpan dalam nilai id nod daun;

• Gunakan indeks PRIMER untuk mencari data baris yang disimpan dalam nod daun melalui nilai id yang ditemui;

  dan kemudian lulus
Syarat menapis data baris yang ditemui.

• status = 0

  Jenis kedua menggunakan penggabungan indeks
:

Using intersect(uniq_trans_id,idx_status)

Mengikut syarat pertanyaan

, gunakan indeks
• untuk cari nod daun Nilai id yang disimpan dalam nod daun;

• Silangkan nilai id yang terdapat dalam 1/2, dan kemudian gunakan indeks UTAMA untuk mencari data baris yang disimpan dalam nod daun

Dalam kedua-dua kes di atas, Perbezaan utama ialah kaedah pertama adalah untuk mencari data melalui indeks terlebih dahulu, dan kemudian menggunakan syarat pertanyaan lain untuk menapis kaedah kedua adalah untuk mendapatkan persilangan nilai id pertama yang ditemui melalui dua indeks Jika masih terdapat nilai id selepas persimpangan, , kemudian kembali ke jadual untuk mendapatkan semula data.

Apabila pengoptimum percaya bahawa kos pelaksanaan kes kedua adalah lebih kecil daripada kes pertama, penggabungan indeks akan berlaku. (Terdapat sedikit data dalam jadual aliran persekitaran pengeluaran status = 0, yang merupakan salah satu sebab mengapa pengoptimum mempertimbangkan kes kedua).

Mengapa ia buntu selepas menggunakan index_merge

Di atas secara ringkas menggambarkan proses penguncian dua transaksi kemas kini, seperti yang dapat dilihat dari rajah Ternyata terdapat bahagian yang bertindih dan bersilang pada kedua-dua indeks idx_status dan PRIMER (indeks berkelompok), yang mewujudkan keadaan untuk kebuntuan.

Sebagai contoh, kebuntuan akan berlaku apabila pemasaan berikut ditemui:

Transaksi 1 menunggu transaksi 2 untuk melepaskan kunci, Transaksi 2 menunggu transaksi 1 melepaskan kunci, sekali gus menyebabkan kebuntuan.

Selepas MySQL mengesan kebuntuan, ia akan melancarkan semula transaksi secara automatik dengan kos yang lebih rendah Contohnya, dalam rajah masa di atas, jika transaksi 1 memegang lebih sedikit kunci daripada transaksi 2, maka MySQL akan melancarkan transaksi 1. .

Penyelesaian

1 Dari peringkat kod

• Dalam keadaan pertanyaan di mana, hanya luluskan trans_id selepas menanyakan data, dalam kod Tentukan sama ada status adalah 0 pada tahap

• menggunakan force index(uniq_trans_id) untuk memaksa pernyataan pertanyaan menggunakan indeks uniq_trans_id

• di mana terus selepas syarat pertanyaan Gunakan medan id untuk mengemas kini melalui kunci utama.

2 Dari peringkat MySQL

• padamkan indeks

  atau buat indeks bersama yang mengandungi dua lajur ini; 🎜 >idx_status

  Matikan
pengoptimuman pengoptimuman MySQL.

• index merge

Atas ialah kandungan terperinci Bagaimana untuk menyelesaikan kebuntuan yang disebabkan oleh gabungan indeks pengoptimuman MySQL. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!