Ketahui lebih lanjut tentang auto-increment kunci utama dalam MySQL

Artikel ini akan membawa anda memahami kunci utama kenaikan automatik dalam MySQL, memperkenalkan mekanisme pengubahsuaian nilai kenaikan automatik, pemasaan pengubahsuaian nilai kenaikan automatik, kaedah pengoptimuman kunci kenaikan automatik, dll. Rakan yang memerlukan boleh belajar tentang ia~

1. Di manakah nilai peningkatan diri?

Enjin yang berbeza mempunyai strategi yang berbeza untuk menyimpan nilai tambah automatik

1 Nilai tambah automatik enjin MyISAM disimpan dalam fail data

2.Enjin InnoDB Nilai tambah sendiri disimpan dalam memori dalam MySQL 5.7 dan versi terdahulu dan tidak dikekalkan. Selepas setiap permulaan semula, apabila anda membuka jadual buat kali pertama, anda akan menemui nilai kenaikan automatik maksimum maks(id), dan kemudian gunakan saiz langkah maks(id) sebagai nilai kenaikan automatik semasa jadual

select max(ai_col) from table_name for update;

Dalam MySQL versi 8.0, perubahan dalam nilai kenaikan diri direkodkan dalam log buat semula Apabila dimulakan semula, bergantung pada log buat semula untuk memulihkan nilai sebelum mulakan semula

. 2. Mekanisme pengubahsuaian nilai kenaikan sendiri

Jika id medan ditakrifkan sebagai AUTO_INCREMENT, apabila memasukkan baris data, tingkah laku kenaikan automatik adalah seperti berikut:

1 . Jika medan id dinyatakan sebagai nilai 0, batal atau tidak ditentukan semasa memasukkan data, maka Isikan nilai AUTO_INCREMENT semasa jadual ini ke dalam medan kenaikan automatik

2 Jika medan id menentukan nilai tertentu apabila memasukkan data, terus gunakan nilai yang dinyatakan dalam pernyataan

Katakan, masa tertentu Nilai yang akan dimasukkan ialah X, dan nilai kenaikan automatik semasa ialah Y

1 nilai kenaikan diri semasa kepada nilai kenaikan diri baharu

Algoritma penjanaan nilai kenaikan diri baharu ialah: bermula daripada auto_increment_offset (nilai awal), mengambil auto_increment_increment (saiz langkah) sebagai saiz langkah , dan meneruskan superpose sehingga ia ditemui Nilai pertama yang lebih besar daripada Tambah medan kunci utama dan c ialah indeks unik Pernyataan penciptaan jadual adalah seperti berikut:

Andaikan sudah ada rekod. (1,1,1) dalam jadual t, dan kemudian laksanakan arahan data sisipan :

Proses pelaksanaan adalah seperti berikut:

1 antara muka untuk menulis baris, dan nilai baris ini yang diluluskan ialah (0,1,1)

2.InnoDB mencari nilai yang tidak menyatakan id kenaikan automatik dan memperoleh auto- semasa nilai kenaikan 2 jadual t

CREATE TABLE `t` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `c` int(11) DEFAULT NULL,
  `d` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `c` (`c`)) ENGINE=InnoDB;

3. Tukar nilai baris masuk kepada (2,1,1)

insert into t values(null, 1, 1);

4 >

5. Teruskan memasukkan data Memandangkan sudah ada rekod c=1, ralat kunci pendua (konflik kunci unik) dilaporkan, penyataan Carta aliran pelaksanaan yang sepadan dengan mengembalikan

adalah seperti. berikut:

Selepas itu, apabila memasukkan baris data baharu, ID auto-increment yang diperolehi ialah 3. Terdapat ketaksinambungan dalam kunci utama yang dinaikkan secara automatik

Konflik kunci unik dan penarikan balik transaksi akan membawa kepada ketaksinambungan dalam id kunci utama yang ditambah secara automatik

4. Auto-increment Optimization of lock increase

Auto-increment id lock bukanlah kunci transaksi, tetapi dikeluarkan serta-merta selepas setiap permohonan selesai untuk membolehkan transaksi lain digunakan semula

Tetapi dalam MySQL5 dalam versi 0, skop penguncian yang meningkat sendiri adalah tahap pernyataan. Dalam erti kata lain, jika penyataan digunakan untuk kunci auto-increment jadual, kunci akan dikeluarkan selepas penyataan itu dilaksanakan MySQL versi 5.1.22 memperkenalkan strategi baharu, parameter baharu innodb_autoinc_lock_mode, nilai lalai ialah 1

1. Parameter ini ditetapkan kepada 0, yang bermaksud strategi versi MySQL5.0 sebelum ini diguna pakai, iaitu, kunci dikeluarkan hanya selepas pernyataan 2. Parameter ini ditetapkan kepada 1

Dalam penyata sisipan biasa, kunci peningkatan sendiri dilepaskan serta-merta selepas permohonan

Untuk pernyataan seperti sisipan...pilih sisipan itu data dalam kelompok, kunci yang meningkat sendiri masih perlu menunggu sehingga penyata selesai sebelum ia dikeluarkan

3 Parameter ini ditetapkan kepada 2. Semua tindakan untuk memohon kunci utama yang ditambah secara automatik akan melepaskan kunci selepas aplikasi

Untuk ketekalan data, tetapan lalai ialah 1

• Jika sessionB melepaskan kunci kenaikan automatik serta-merta selepas memohon kenaikan automatik, maka perkara berikut situasi mungkin berlaku:
sessionB mula-mula memasukkan dua baris data (1,1,1), (2,2,2)

sessionA yang digunakan untuk id kenaikan automatik dan mendapat id=3 Selepas memasukkan (3,5,5)

, sessionB terus melaksanakan dan memasukkan dua rekod ( 4,3,3), (5,4,4)

Apabila binlog_format=statement, kedua-dua sesi melaksanakan arahan data sisipan pada masa yang sama, jadi binlog menghadap log kemas kini jadual t2 Terdapat hanya dua situasi: sama ada rekod sesiA dahulu, atau rekod sesiB dahulu. Tidak kira yang mana satu, binlog ini dilaksanakan daripada pangkalan data hamba, atau digunakan untuk memulihkan kejadian sementara Dalam pangkalan data siap sedia dan contoh sementara, pernyataan sessionB dilaksanakan dan ID dalam hasil yang dijana adalah berterusan. Pada masa ini, ketidakkonsistenan data berlaku dalam perpustakaan ini

Idea untuk menyelesaikan masalah ini:
1) Biarkan kumpulan perpustakaan asal memasukkan pernyataan data untuk menjana nilai id berterusan. Oleh itu, kunci peningkatan diri tidak dilepaskan sehingga pernyataan dilaksanakan, hanya untuk mencapai tujuan ini

• 2）在binlog里面把插入数据的操作都如实记录进来，到备库执行的时候，不再依赖于自增主键去生成。也就是把innodb_autoinc_lock_mode设置为2，同时binlog_format设置为row

  如果有批量插入数据（insert … select、replace … select和load data）的场景时，从并发插入数据性能的角度考虑，建议把innodb_autoinc_lock_mode设置为2，同时binlog_format设置为row，这样做既能并发性，又不会出现数据一致性的问题

  对于批量插入数据的语句，MySQL有一个批量申请自增id的策略：

  1.语句执行过程中，第一次申请自增id，会分配1个

  2.1个用完以后，这个语句第二次申请自增id，会分配2个

  3.2个用完以后，还是这个语句，第三次申请自增id，会分配4个

  4.依次类推，同一个语句去申请自增id，每次申请到的自增id个数都是上一次的两倍

  insert into t values(null, 1,1);
  insert into t values(null, 2,2);
  insert into t values(null, 3,3);
  insert into t values(null, 4,4);
  create table t2 like t;
  insert into t2(c,d) select c,d from t;
  insert into t2 values(null, 5,5);

  insert … select，实际上往表t2中插入了4行数据。但是，这四行数据是分三次申请的自增id，第一次申请到了id=1，第二次被分配了id=2和id=3，第三次被分配到id=4到id=7

  由于这条语句实际上只用上了4个id，所以id=5到id=7就被浪费掉了。之后，再执行insert into t2 values(null, 5,5)，实际上插入了的数据就是(8,5,5)

  这是主键id出现自增id不连续的第三种原因

  五、自增主键用完了

  自增主键字段在达到定义类型上限后，再插入一行记录，则会报主键冲突的错误

  以无符号整型（4个字节，上限就是$2^{32}-1$）为例，通过下面这个语句序列验证一下：

  CREATE TABLE t ( id INT UNSIGNED auto_increment PRIMARY KEY ) auto_increment = 4294967295;
  INSERT INTO t VALUES(NULL);
  INSERT INTO t VALUES(NULL);

  第一个insert语句插入数据成功后，这个表的AUTO_INCREMENT没有改变（还是4294967295），就导致了第二个insert语句又拿到相同的自增id值，再试图执行插入语句，报主键冲突错误

  相关学习推荐：mysql教程(视频)

  Atas ialah kandungan terperinci Ketahui lebih lanjut tentang auto-increment kunci utama dalam MySQL. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!