Erfahren Sie mehr über die automatische Inkrementierung von Primärschlüsseln in MySQL

In diesem Artikel lernen Sie den Primärschlüssel für die automatische Inkrementierung in MySQL kennen, stellen den Mechanismus zur Änderung des Werts für die automatische Inkrementierung, den Zeitpunkt der Änderung des Werts für die automatische Inkrementierung, die Methode zur Optimierung der Sperre für die automatische Inkrementierung usw. vor. Freunde in Not können mehr darüber erfahren ~

1. Wo wird der selbstgesteigerte Wert gespeichert?

Verschiedene Engines haben unterschiedliche Strategien zum Speichern selbstinkrementierter Werte

1 Der selbstinkrementierte Wert der MyISAM-Engine wird in der Datendatei gespeichert

2 Der selbstinkrementierte Wert der InnoDB-Engine, in MySQL5 .7 und frühere Versionen, der selbsterhöhende Wert wird im Speicher gespeichert, es gibt keine Persistenz. Nach jedem Neustart, wenn Sie die Tabelle zum ersten Mal öffnen, finden Sie den maximalen Wert für die automatische Inkrementierung max(id) und verwenden dann max(id) + Schrittgröße als aktuellen Wert für die automatische Inkrementierung der Tabelle

select max(ai_col) from table_name for update;

In MySQL Version 8.0 zeichnen Sie die Änderungen des Selbstinkrementwerts im Redo-Protokoll auf und verlassen sich beim Neustart darauf, dass das Redo-Protokoll den Wert vor dem Neustart wiederherstellt

2. Mechanismus zur Änderung des Selbstinkrementwerts

Wenn Die Feld-ID ist als AUTO_INCREMENT definiert. Beim Einfügen einer Datenzeile ist das Verhalten der automatischen Inkrementierung wie folgt:

1 Wenn das ID-Feld beim Einfügen von Daten als 0, Null oder nicht angegebener Wert angegeben ist, füllen Sie das aus Aktueller AUTO_INCREMENT-Wert dieser Tabelle in das Auto-Inkrementierungsfeld

2. Wenn das Feld beim Einfügen einen bestimmten Wert angibt, verwenden Sie einfach den in der Anweisung angegebenen Wert Der aktuelle Wert für die automatische Inkrementierung ist Y. 1. Bei Änderung

2. Wenn lang, fahren Sie mit der Überlagerung fort, bis der erste Wert größer als ist. Die Anweisungen zur Index- und Tabellenerstellung lauten wie folgt:

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;

Angenommen, es gibt bereits einen Datensatz ( 1,1,1) in Tabelle t. Führen Sie dann einen Befehl zum Einfügen von Daten aus:

rrree

Der Ausführungsprozess ist wie folgt:

1. Der Prozessor ruft die InnoDB-Engine-Schnittstelle auf, um den Wert dieser Zeile zu schreiben in ist (0,1,1)2. InnoDB findet einen Wert, der keine Auto-Inkrement-ID angibt, und erhält den aktuellen Auto-Inkrement-Wert 2 der Tabelle t

3. Ändern Sie den Wert der eingehenden Zeile zu (2,1,1)4. Ändern Sie den automatischen Inkrementwert der Tabelle auf 3

5. Da bereits ein Datensatz von c=1 vorhanden ist, ist ein doppelter Schlüsselfehler aufgetreten Konflikt) gemeldet wird, gibt die Anweisung

zurück und das entsprechende Ausführungsflussdiagramm sieht wie folgt aus:

Danach beträgt beim Einfügen einer neuen Datenzeile die erhaltene automatisch inkrementierte ID 3. Es gibt eine Situation, in der der sich selbst erhöhende Primärschlüssel diskontinuierlich ist. Ein eindeutiger Schlüsselkonflikt und ein Transaktions-Rollback führen dazu, dass die sich selbst erhöhende Primärschlüssel-ID diskontinuierlich ist. 4. Optimierung der selbst erhöhenden Sperre

Die selbsterhöhende ID-Sperre ist keine Transaktionssperre. Die Transaktionssperre wird sofort nach jeder Anwendung aufgehoben, damit andere Transaktionen erneut angewendet werden können.

Aber in der MySQL 5.0-Version ist der Umfang der selbsterhöhenden Sperre gleich Anweisungsebene. Mit anderen Worten: Wenn eine Anweisung eine Sperre für die automatische Tabelleninkrementierung anwendet, wird die Sperre erst freigegeben, wenn die Anweisung ausgeführt wird. MySQL Version 5.1.22 führt eine neue Strategie ein, einen neuen Parameter innodb_autoinc_lock_mode, der Standardwert ist 1

1. Dieser Parameter ist auf 0 gesetzt, was bedeutet, dass die Strategie der vorherigen MySQL 5.0-Version übernommen wird, d Anweisungen wird die Auto-Inkrement-Sperre sofort nach der Anwendung aufgehoben

Ähnlich wie bei insert...select-Anweisungen, die Daten stapelweise einfügen, muss die Auto-Inkrement-Sperre noch warten, bis die Anweisung abgeschlossen ist, bevor sie freigegeben wird

3 . Dieser Parameter ist auf 2 gesetzt und alle Aktionen, die für die automatische Inkrementierung von Primärschlüsseln gelten, werden nach der Anwendungssperre freigegeben. Aus Gründen der Datenkonsistenz ist die Standardeinstellung 1. Wenn SitzungB den Wert für die automatische Inkrementierung anwendet und freigibt Wenn die automatische Inkrementierungs-ID sofort gesperrt wird, kann die folgende Situation auftreten:

sessionB fügt zuerst zwei Datenzeilen (1,1,1), (2,2,2) ein.

sessionA hat die automatische Inkrementierungs-ID beantragt und erhalten id=3. Nach dem Einfügen von (3,5,5)

fuhr sessionB mit der Ausführung fort und fügte zwei Datensätze ein (4, 3,3), (5,4,4)

Wenn binlog_format=statement, die beiden Sitzungen Führen Sie gleichzeitig den Befehl zum Einfügen von Daten aus, sodass es für das Aktualisierungsprotokoll der Tabelle t2 im Binlog nur zwei Situationen gibt: Entweder zuerst aufzeichnen. Für SitzungA entweder zuerst SitzungB aufzeichnen. Unabhängig davon, um welches es sich handelt, wird dieses Binlog von der Slave-Datenbank ausgeführt oder zum Wiederherstellen einer temporären Instanz verwendet. In der Standby-Datenbank und der temporären Instanz wird die sessionB-Anweisung ausgeführt und die IDs in den generierten Ergebnissen sind fortlaufend. Zu diesem Zeitpunkt ist in dieser Bibliothek eine Dateninkonsistenz aufgetreten.

Die Ideen zur Lösung dieses Problems:

1) Lassen Sie die Batch-Insert-Datenanweisungen der Originalbibliothek kontinuierliche ID-Werte generieren. Daher wird die selbsterhöhende Sperre erst freigegeben, wenn die Anweisung ausgeführt wird, nur um diesen Zweck zu erreichen

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教程(视频)

Das obige ist der detaillierte Inhalt vonErfahren Sie mehr über die automatische Inkrementierung von Primärschlüsseln in MySQL. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!