Das Einfügen in eine große MySQL-Tabelle ohne automatisch inkrementierten Primärschlüssel ist sehr langsam

Question

Mir ist kürzlich ein deutlicher Anstieg des Zeitunterschieds aufgefallen, der zum Abschließen einer einfachen INSERT-Anweisung erforderlich ist. Während diese Anweisungen im Durchschnitt etwa 11 Millisekunden dauern, können sie manchmal auch 10 bis 30 Sekunden dauern, und ich habe sogar bemerkt, dass die Ausführung mehr als 5 Minuten dauert.

MySQL-Version ist 8.0.24, läuft auf Windows Server 2016. Soweit ich weiß, waren die Ressourcen des Servers nie überlastet. Dem Server steht ausreichend CPU-Overhead zur Verfügung und ihm sind 32 GB RAM zugewiesen.

Dies ist die Tabelle, die ich verwende:

CREATE TABLE `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',
  „storage_id“ tinyint unsigned NOT NULL,
  PRIMÄRSCHLÜSSEL („recording_id“, „index“)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

Diese Tabelle hat keine anderen Indizes oder Fremdschlüssel und wird in keiner anderen Tabelle als Referenz auf Fremdschlüssel verwendet. Die gesamte Tabellengröße beträgt etwa 20 GB und die Anzahl der Zeilen beträgt etwa 281 MB, was meiner Meinung nach nicht zu groß ist.

Die Tabelle wird fast ausschließlich im schreibgeschützten Modus verwendet, mit bis zu 1000 Lesevorgängen pro Sekunde. Alle diese Lesevorgänge erfolgen in einfachen SELECT-Abfragen und nicht in komplexen Transaktionen und nutzen den Primärschlüsselindex effizient. Es gibt, wenn überhaupt, nur sehr wenige gleichzeitige Schreibvorgänge in dieser Tabelle. Dies geschah mit Absicht, um herauszufinden, ob es beim langsamen Einführen helfen würde, aber das ist nicht der Fall. Bis dahin sind immer bis zu 10 gleichzeitige Einfügungen im Gange. UPDATE- oder DELETE-Anweisungen werden niemals für diese Tabelle ausgeführt.

Die Abfragen, mit denen ich Probleme habe, sind alle auf diese Weise aufgebaut. Sie erscheinen nie in der Transaktion.Während Einfügungen, die auf dem geclusterten Primärschlüssel basieren, definitiv nicht nur durch Anhängen möglich sind, fügt die Abfrage fast immer zwischen 1 und 20 benachbarte Zeilen in die Tabelle ein:

INSERT IGNORE INTO saved_segment
(recording_id, `index`, start_filetime, end_filetime, offset_and_size, storage_id) WERTE
(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)

Dies ist die Ausgabe der EXPLAIN-Anweisung für die obige Abfrage:

id	Typ auswählen	Tabelle	Partition	Typ	Mögliche Schlüssel	key	key_len	Referenz	OK	Gefiltert	Extra
1	Einfügen	Gespeicherte Segmente	Leer	Alle	Leer	Leer	Leer	Leer	Leer	Leer	Leer

Diese Probleme sind relativ neu und fielen nicht auf, als der Tisch etwa doppelt so klein war.

Ich habe versucht, die Anzahl gleichzeitiger Einfügungen in der Tabelle von etwa 10 auf 1 zu reduzieren. Ich habe auch die Fremdschlüssel (recording_id) für einige Spalten entfernt, um die Einfügungen noch schneller zu machen. Die Analyse von Tabellen und die Schemaanalyse ergaben keine verwertbaren Informationen.

Eine Lösung, die ich mir überlegt habe, bestand darin, den gruppierten Primärschlüssel zu entfernen und einen automatisch inkrementierenden Primärschlüssel und einen regulären Index für die Spalte (recording_id, index) hinzuzufügen. Meiner Meinung nach würde dies dazu beitragen, die Einfügung „nur anhängen“ zu machen.Ich bin für alle Vorschläge offen, vielen Dank im Voraus!

Bearbeiten: Ich werde auf einige der in den Kommentaren und Antworten aufgeworfenen Punkte und Fragen eingehen:

• autocommit ist auf EIN
gesetzt. Der Wert von
• innodb_buffer_pool_size ist 21474836480 und der Wert von innodb_buffer_pool_chunk_size ist 134217728 ;
• Ein Kommentar äußerte Bedenken hinsichtlich eines Konflikts zwischen der Lesesperre für Lesevorgänge und der exklusiven Sperre für Schreibvorgänge. Die Tabelle wird in gewisser Weise wie ein Cache verwendet. Ich muss nicht lesen, um immer den neuesten Status der Tabelle anzuzeigen, wenn dies eine Leistungssteigerung bedeutet. Allerdings soll die Tabelle auch bei Serverabstürzen und Hardwareausfällen langlebig bleiben. Könnte dies mit einer lockereren Transaktionsisolationsstufe erreicht werden?
• Die Architektur kann definitiv optimiert werden; recording_id kann eine 4-Byte-Ganzzahl sein, end_filetime start_filetime ; kann auch kleiner sein. Ich befürchte, dass diese Änderungen das Problem nur eine Weile hinauszögern werden, bis die Größe der Tabelle vergrößert wird, um den eingesparten Platz auszugleichen.
• Einfügungen in die Tabelle sind immer fortlaufend Ein für die Tabelle ausgeführtes SELECT sieht folgendermaßen aus:

SELECT TRUE
VON gespeichertes_segment
WHERE Recording_id = ? AND `index` = ?

SELECT index, start_filetime, end_filetime, offset_and_size, storage_id
VON gespeichertes_segment
WO Recording_id = ?
        start_filetime >= ? UND
        start_filetime <= ?
ORDER BY `index` ASC

Der zweite Abfragetyp könnte sicherlich durch einen Index verbessert werden, aber ich befürchte, dass dies die INSERT-Leistung weiter beeinträchtigen würde.

Eine weitere Sache, die ich vergessen habe zu erwähnen, ist, dass es eine Tabelle gibt, die dieser sehr ähnlich ist. Es werden genau die gleichen Abfragen und Einfügungen durchgeführt, es kann jedoch zu einem weiteren E/A-Ausfall kommen.

Bearbeiten 2: SHOW TABLE STATUS die Ergebnisse der Tabelle saved_segment und einer sehr ähnlichen Tabelle saved_screenshot (die in bigint nicht signiert ist). Es gibt einen zusätzlichen Index für die Spalte null

Name	Motor	Version	Zeilenformat	OK	Durchschnittliche Zeilenlänge	Datenlänge	Maximale Datenlänge	Indexlänge	Keine Daten	Auto-Inkrement	Erstellungszeit	Aktualisiert	Zeit prüfen	Organisation	Prüfsumme	Erstellungsoptionen	Kommentare
Gespeicherter Screenshot	InnoDB	10	Neuigkeiten	483430208	61	29780606976	0	21380464640	6291456	Leer	“2021-10-21 01:03:21”	“2022-11-07 16:51:45”	Leer	utf8mb4_0900_ai_ci	Leer
Gespeicherte Segmente	InnoDB	10	Neuigkeiten	281861164	73	20802699264	0	0	4194304	Leer	“2022-11-02 09:03:05”	“2022-11-07 16:51:22”	Leer	utf8mb4_0900_ai_ci	Leer

Answer 1

我会带着这个答案冒险。

假设

• innodb_buffer_pool_size 的值略小于 20MB，并且
• 每秒 1K 个选择随机到达表的各个部分，然后

系统最近变得受 I/O 限制，因为下一个 Select 所需的“下一个”块越来越经常不缓存在 buffer_pool 中。

简单的解决方案是获取更多 RAM 并提高该可调参数的设置。但表格只会增长到您购买的下一个限制。

相反，这里有一些部分解决方案。

• 如果数字不太大，前两列可能是 INT UNSIGNED（4 个字节而不是 8），甚至可能是 MEDIUMINT UNSIGNED（3 个字节） ）。注意 ALTER TABLE 会长时间锁定表。
• 这些开始和结束时间看起来像带有小数秒的时间戳，且始终为“.000”。 DATETIME 和 TIMESTAMP 占用 5 个字节（而不是 8 个字节）。
• 您的示例显示经过时间为 0。如果 (end-start) 通常非常小，那么存储经过时间而不是结束时间会进一步缩小数据。 （但是使用结束时间会让事情变得混乱）。
• 您提供的示例数据看起来“连续”。这与自动增量的效率差不多。这是常态吗？如果不是，INSERT 可能是 I/O 抖动的一部分。
• 您建议添加人工智能以及二级索引，这使得插入的工作量增加了一倍；所以我不推荐它。

更多

是的，情况就是这样。

将其作为 INDEX，或者更好的是，作为 PRIMARY KEY 的开头，可以为您的两个查询提供最佳帮助：

(recording_id, index)

回复：

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

如果它用于控制其他一些 SQL，请考虑将其添加到其他 SQL 中：

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

该查询（无论哪种形式）都需要您已有的内容

PRIMARY KEY(recording_id, index)

您的其他查询需求

INDEX(recording_id, start_filetime)

所以，添加索引，或...

更好...这种组合对于两者来说都更好SELECT：

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

有了这个组合，

• 单行存在性检查将“使用索引”执行，因为它是“覆盖”。
• 另一个查询将找到在 PK 上聚集在一起的所有相关行。
• （PK 有这 3 列，因为它需要是唯一的。它们按这个顺序排列有利于您的第二个查询。而且它是 PK，而不仅仅是一个 INDEX，因此它不需要在索引的 BTree 之间反弹和数据的 BTree。）
• “集群”可以通过减少此类查询所需的磁盘块数量来提高性能。这会减少 buffer_pool 中的“颠簸”，从而减少增加 RAM 的需要。
• 我的索引建议大部分与我的数据类型建议正交。