Gibt es einen REALEN performancesunterschied zwischen INT- und VARCHAR-Primärschlüsseln?

Gibt es einen messbaren performancesunterschied zwischen der Verwendung von INT vs. VARCHAR als Primärschlüssel in MySQL? Ich möchte VARCHAR als Primärschlüssel für Referenzlisten verwenden (denke US-Bundesstaaten, Ländercodes), und ein Mitarbeiter wird sich nicht an INT AUTO_INCREMENT als Primärschlüssel für alle Tabellen rühren.

Mein Argument, wie hier ausgeführt , ist, dass der performancesunterschied zwischen INT und VARCHAR vernachlässigbar ist, da jeder INT-Fremdschlüsselverweis einen JOIN benötigt, um den Verweis sinnvoll zu machen, ein VARCHAR-Schlüssel wird die Information direkt darstellen.

Hat also jemand Erfahrung mit diesem speziellen Anwendungsfall und den damit verbundenen performancesbedenken?

Sie machen einen guten Punkt, dass Sie einige verbundene Abfragen vermeiden können, indem Sie einen natürlichen Schlüssel anstelle eines Ersatzschlüssels verwenden . Nur Sie können beurteilen, ob der Nutzen für Ihre Anwendung von Bedeutung ist.

Das heißt, Sie können die Abfragen in Ihrer Anwendung, die am wichtigsten sind, schnell messen, da sie mit großen Datenmengen arbeiten oder sehr häufig ausgeführt werden. Wenn diese Abfragen vom Eliminieren eines Joins profitieren und nicht unter Verwendung eines Varchar-Primärschlüssels leiden, dann tun Sie es.

Verwenden Sie keine Strategie für alle Tabellen in Ihrer database. Es ist wahrscheinlich, dass in einigen Fällen ein natürlicher Schlüssel besser ist, aber in anderen Fällen ist ein Ersatzschlüssel besser.

Andere Leute machen einen guten Punkt, dass es in der Praxis selten ist, dass ein natürlicher Schlüssel sich nie ändert oder Duplikate hat, so dass sich Ersatzschlüssel normalerweise lohnen.

Es geht nicht um performance. Es geht darum, was einen guten Primärschlüssel ausmacht. Einmalig und unveränderlich im Laufe der Zeit. Sie denken vielleicht, dass sich eine Entität wie ein Ländercode im Laufe der Zeit nie ändert und ein guter Kandidat für einen Primärschlüssel wäre. Aber bittere Erfahrung ist das selten.

INT AUTO_INCREMENT erfüllt die Bedingung “einmalig und unveränderbar über Zeit”. Daher die Präferenz.

Hängt von der Länge ab. Wenn das varchar 20 Zeichen hat und das int 4 ist, dann wird Ihr Index, wenn Sie ein int verwenden, FÜNF mal so viele Knoten pro Seite des Indexraums auf der Festplatte haben … Das bedeutet, dass das Tracing erfolgt Der Index benötigt ein Fünftel so viele physische und / oder logische Lesevorgänge.

Wenn die performance bei der Gelegenheit ein Problem darstellt, sollten Sie immer einen ganzzahligen, nicht sinnvollen Schlüssel (Ersatzzeichen) für Ihre Tabellen und für Fremdschlüssel verwenden, die auf die Zeilen in diesen Tabellen verweisen.

Zur Gewährleistung der Datenkonsistenz sollte jede Tabelle, auf die sie sich auswirkt, auch einen aussagekräftigen nicht numerischen alternativen Schlüssel (oder eindeutigen Index) enthalten, um sicherzustellen, dass doppelte Zeilen nicht eingefügt werden können (basierend auf sinnvollen Tabellenattributen).

Für die spezifische Verwendung, über die Sie sprechen (wie Zustandsübersichten), spielt es keine Rolle, weil die Größe der Tabelle so klein ist. Im Allgemeinen hat die Performance keine Auswirkungen auf Tabellen mit weniger als ein paar tausend Zeilen. ..

Absolut nicht.

Ich habe mehrere … mehrere … Performance-Checks zwischen INT, VARCHAR und CHAR gemacht.

10 Millionen Datensatztabellen mit einem PRIMÄREN SCHLÜSSEL (einzigartig und geclustert) hatten die exakt gleiche Geschwindigkeit und performance (und Teilbaumkosten), egal welche der drei, die ich verwendete.

Nichtsdestoweniger … benutze was immer das Beste für deine Anwendung ist. Mach dir keine Sorgen über die performance.

Für kurze Codes gibt es wahrscheinlich keinen Unterschied. Dies gilt umso mehr, als die Tabelle mit diesen Codes wahrscheinlich sehr klein ist (höchstens ein paar tausend Zeilen) und sich nicht häufig ändert (wann haben wir das letzte Mal einen neuen US-Staat hinzugefügt).

Bei größeren Tabellen mit größeren Abweichungen zwischen den Schlüsseln kann dies gefährlich sein. Denken Sie zum Beispiel daran, die E-Mail-Adresse / den Benutzernamen aus einer Benutzertabelle zu verwenden. Was passiert, wenn Sie einige Millionen Benutzer haben und einige dieser Benutzer lange Namen oder E-Mail-Adressen haben? Jetzt, wann immer Sie diese Tabelle mit diesem Schlüssel verbinden müssen, wird es viel teurer.

Ich war ein bisschen genervt von dem Mangel an Benchmarks für diese online, also habe ich selbst einen Test gemacht.

Beachten Sie jedoch, dass ich es nicht regelmäßig mache. Überprüfen Sie daher meine Einstellungen und Schritte auf Faktoren, die die Ergebnisse unbeabsichtigt beeinflusst haben könnten, und schreiben Sie Ihre Bedenken in Kommentaren.

Das Setup war wie folgt:

  • Intel® Core ™ i7-7500U CPU mit 2,70 GHz × 4
  • 15,6 GiB RAM, von denen ich rund 8 GB sicherstellte, waren während des Tests frei.
  • 148,6 GB SSD-Laufwerk, mit viel freiem Speicherplatz.
  • Ubuntu 16.04 64-Bit
  • MySQL Ver 14.14 Distrib 5.7.20, für Linux (x86_64)

Die Tische:

create table jan_int (data1 varchar(255), data2 int(10), myindex tinyint(4)) ENGINE=InnoDB; create table jan_int_index (data1 varchar(255), data2 int(10), myindex tinyint(4), INDEX (myindex)) ENGINE=InnoDB; create table jan_char (data1 varchar(255), data2 int(10), myindex char(6)) ENGINE=InnoDB; create table jan_char_index (data1 varchar(255), data2 int(10), myindex char(6), INDEX (myindex)) ENGINE=InnoDB; create table jan_varchar (data1 varchar(255), data2 int(10), myindex varchar(63)) ENGINE=InnoDB; create table jan_varchar_index (data1 varchar(255), data2 int(10), myindex varchar(63), INDEX (myindex)) ENGINE=InnoDB; 

Dann füllte ich 10 Millionen Zeilen in jeder Tabelle mit einem PHP-Skript, dessen Essenz wie folgt ist:

 $pdo = get_pdo(); $keys = [ 'alabam', 'massac', 'newyor', 'newham', 'delawa', 'califo', 'nevada', 'texas_', 'florid', 'ohio__' ]; for ($k = 0; $k < 10; $k++) { for ($j = 0; $j < 1000; $j++) { $val = ''; for ($i = 0; $i < 1000; $i++) { $val .= '("' . generate_random_string() . '", ' . rand (0, 10000) . ', "' . ($keys[rand(0, 9)]) . '"),'; } $val = rtrim($val, ','); $pdo->query('INSERT INTO jan_char VALUES ' . $val); } echo "\n" . ($k + 1) . ' millon(s) rows inserted.'; } 

Für int Tabellen wurde das Bit ($keys[rand(0, 9)]) nur durch rand(0, 9) , und für varchar Tabellen verwendete ich vollständige US-Zustandsnamen, ohne sie auf 6 Zeichen zu varchar oder zu erweitern. generate_random_string() generiert eine zufällige Zeichenfolge mit 10 Zeichen.

Dann lief ich in MySQL:

  • SET SESSION query_cache_type=0;
  • Für jan_int Tabelle jan_int :
    • SELECT count(*) FROM jan_int WHERE myindex = 5;
    • SELECT BENCHMARK(1000000000, (SELECT count(*) FROM jan_int WHERE myindex = 5));
  • Für andere Tabellen, wie oben, mit myindex = 'califo' für char Tabellen und myindex = 'california' für varchar Tabellen.

Zeiten der BENCHMARK Abfrage für jede Tabelle:

  • jan_int: 21.30 sek
  • jan_int_index: 18,79 sek
  • jan_char: 21.70 sek
  • jan_char_index: 18,85 sek
  • jan_varchar: 21.76 sek
  • jan_varchar_index: 18,86 sek

In Bezug auf die Tabellen- und show table status from janperformancetest; ist hier die Ausgabe des show table status from janperformancetest; (mit ein paar Spalten nicht gezeigt):

 |-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------| | Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Collation | |-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------| | jan_int | InnoDB | 10 | Dynamic | 9739094 | 43 | 422510592 | 0 | 0 | 4194304 | NULL | utf8mb4_unicode_520_ci | | jan_int_index | InnoDB | 10 | Dynamic | 9740329 | 43 | 420413440 | 0 | 132857856 | 7340032 | NULL | utf8mb4_unicode_520_ci | | jan_char | InnoDB | 10 | Dynamic | 9726613 | 51 | 500170752 | 0 | 0 | 5242880 | NULL | utf8mb4_unicode_520_ci | | jan_char_index | InnoDB | 10 | Dynamic | 9719059 | 52 | 513802240 | 0 | 202342400 | 5242880 | NULL | utf8mb4_unicode_520_ci | | jan_varchar | InnoDB | 10 | Dynamic | 9722049 | 53 | 521142272 | 0 | 0 | 7340032 | NULL | utf8mb4_unicode_520_ci | | jan_varchar_index | InnoDB | 10 | Dynamic | 9738381 | 49 | 486539264 | 0 | 202375168 | 7340032 | NULL | utf8mb4_unicode_520_ci | |-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------| 

Meine Schlussfolgerung ist, dass es für diesen speziellen Anwendungsfall keinen performancesunterschied gibt.

Was den Primärschlüssel anbelangt, sollte unabhängig davon, was eine Zeile eindeutig macht, als Primärschlüssel festgelegt werden.

Für eine Referenz als Fremdschlüssel ist die Verwendung einer automatisch inkrementierenden Ganzzahl als Ersatz eine gute Idee aus zwei Hauptgründen.
– Zunächst fällt normalerweise weniger Overhead an.
– Zweitens, wenn Sie die Tabelle aktualisieren müssen, die das eindeutige varchar enthält, dann muss das Update auf alle untergeordneten Tabellen herunterkaskadiert werden und alle von ihnen sowie die Indizes aktualisieren, während es mit dem int-Ersatz nur das aktualisieren muss Master-Tabelle und ihre Indizes.

Das Drawaback zur Verwendung des Ersatzes besteht darin, dass Sie möglicherweise die Bedeutung des Ersatzes ändern können:

 ex. id value 1 A 2 B 3 C Update 3 to D id value 1 A 2 B 3 D Update 2 to C id value 1 A 2 C 3 D Update 3 to B id value 1 A 2 C 3 B 

Alles hängt davon ab, worüber Sie sich in Ihrer Struktur wirklich sorgen müssen und was am meisten bedeutet.

Bei HauteLook haben wir viele unserer Tabellen auf natürliche Schlüssel umgestellt. Wir haben eine reale performancessteigerung erfahren. Wie Sie bereits erwähnt haben, verwenden viele unserer Abfragen jetzt weniger Joins, wodurch die Abfragen leistungsfähiger werden. Wir werden sogar einen zusammengesetzten Primärschlüssel verwenden, wenn es sinnvoll ist. Abgesehen davon ist es einfacher, mit einigen Tabellen zu arbeiten, wenn sie einen Ersatzschlüssel haben.

Wenn Sie Benutzer Schnittstellen zu Ihrer database schreiben lassen, kann ein Ersatzschlüssel hilfreich sein. Der Dritte kann sich darauf verlassen, dass sich der Ersatzschlüssel nur in sehr seltenen Fällen ändert.

Häufige Fälle, in denen ein Ersatz AUTO_INCREMENT schmerzt:

Ein allgemeines Schemamuster ist ein Viele-zu-Viele-Mapping :

 CREATE TABLE map ( id ... AUTO_INCREMENT, foo_id ..., bar_id ..., PRIMARY KEY(id), UNIQUE(foo_id, bar_id), INDEX(bar_id) ); 

Die Ausführung dieses Musters ist viel besser, besonders wenn InnoDB verwendet wird:

 CREATE TABLE map ( # No surrogate foo_id ..., bar_id ..., PRIMARY KEY(foo_id, bar_id), INDEX (bar_id, foo_id) ); 

Warum?

  • InnoDB-Sekundärschlüssel benötigen eine zusätzliche Suche. indem man das Paar in den PK bewegt, wird das für eine Richtung vermieden.
  • Der sekundäre Index ist “deckend” und benötigt daher keine zusätzliche Suche.
  • Diese Tabelle ist kleiner, weil id und ein Index wegfallen.

Ein anderer Fall ( Land ):

 country_id INT ... -- versus country_code CHAR(2) CHARACTER SET ascii 

Allzu oft normalisiert der Anfänger country_code in ein 4-Byte- INT anstatt eine “natürliche” 2-Byte-, fast unveränderliche 2-Byte-Zeichenkette zu verwenden. Schneller, kleiner, weniger JOINs, lesbarer.

Ich stand vor demselben Dilemma. Ich habe ein DW (Constellation-Schema) mit 3 Faktentabellen, Straßenunfällen, Fahrzeugen bei Unfällen und Unfälle bei Unfällen erstellt. Die Daten umfassen alle Unfälle, die zwischen 1979 und 2012 in Großbritannien registriert wurden, sowie 60 Dimensionstabellen. Insgesamt etwa 20 Millionen Datensätze.

Fakten Tabellenbeziehungen:

 +----------+ +---------+ | Accident |>--------< | Vehicle | +-----v----+ 1 * +----v----+ 1| |1 | +----------+ | +---<| Casualty |>---+ * +----------+ * 

RDMS: MySQL 5.6

Nativ ist der Accident-Index ein varchar (Zahlen und Buchstaben) mit 15 Ziffern. Ich habe versucht, keine Ersatzschlüssel zu haben, wenn sich die Unfallindizes nie ändern würden. In einem i7-Computer (8 coree) wurde die DW zu langsam, um nach 12 Millionen Belastungsdaten abhängig von den Dimensionen abzufragen. Nach vielen Nacharbeiten und Hinzufügen von Bigint-Ersatzschlüsseln erhielt ich eine durchschnittliche performancessteigerung von 20%. Aber zu geringen performanceszuwachs, aber gültiger Versuch. Ich arbeite in MySQL Tuning und Clustering.

Ich bin mir nicht sicher über die Auswirkungen auf die Performance, aber es scheint zumindest während der Entwicklung ein möglicher Kompromiss zu sein, sowohl den automatisch inkrementierten Ganzzahl- “Ersatz” -Schlüssel als auch den beabsichtigten, einzigartigen “natürlichen” Schlüssel einzuschließen. Dies gibt Ihnen die Möglichkeit, die performance sowie andere mögliche Probleme, einschließlich der Änderbarkeit von natürlichen Schlüsseln, zu bewerten.

Die Frage bezieht sich auf MySQL, daher gibt es einen signifikanten Unterschied. Wenn es um Oracle ging (das Zahlen als String speichert – ja, ich konnte es zuerst nicht glauben), dann nicht viel Unterschied.

Die Speicherung in der Tabelle ist nicht das Problem, aber das Aktualisieren und Verweisen auf den Index ist. Abfragen, bei denen nach einem Datensatz auf der Basis seines Primärschlüssels gesucht wird, sind häufig – Sie möchten, dass sie so schnell wie möglich auftreten, weil sie so oft vorkommen.

Die Sache ist eine CPU beschäftigt sich mit 4 Byte und 8 Byte Ganzzahlen natürlich in Silizium . Es ist wirklich schnell für den Vergleich von zwei ganzen Zahlen – es passiert in ein oder zwei Taktzyklen.

Sehen Sie sich nun einen String an – er besteht aus vielen Zeichen (mehr als ein Byte pro Zeichen in diesen Tagen). Der Vergleich zweier Zeichenfolgen für die Priorität kann nicht in einem oder zwei Zyklen durchgeführt werden. Stattdessen müssen die Zeichen der Zeichen wiederholt werden, bis ein Unterschied gefunden wird. Ich bin mir sicher, dass es Tricks gibt, um es in einigen databaseen schneller zu machen, aber das ist hier irrelevant, weil ein int-Vergleich natürlich und blitzschnell in Silizium von der CPU durchgeführt wird.

Meine allgemeine Regel – jeder Primärschlüssel sollte ein autoinkrementierender INT sein, besonders in OO-Apps mit einem ORM (Hibernate, Datanucleus, was auch immer), wo es viele Beziehungen zwischen Objekten gibt – sie werden normalerweise immer als einfache FK implementiert und die Fähigkeit für die DB, um diese schnell zu beheben, ist wichtig für die Reaktionsfähigkeit Ihrer App.

Wie üblich gibt es keine pauschalen Antworten. ‘Es kommt darauf an!’ und ich bin nicht witzig. Mein Verständnis der ursprünglichen Frage war für Schlüssel auf kleinen Tabellen – wie Country (ganzzahlige ID oder char / varchar code) ein Fremdschlüssel zu einer potenziell riesigen Tabelle wie Adresse / Kontakt-Tabelle.

Es gibt zwei Szenarien, wenn Sie Daten aus der DB zurückholen möchten. Die erste ist eine Suchabfrage, bei der alle Kontakte mit Landes- und Ländercodes oder Namen aufgelistet werden sollen (die IDs helfen nicht und benötigen daher eine Suche). Der andere ist ein Get-Szenario für den Primärschlüssel, das einen einzelnen Kontaktdatensatz zeigt, in dem der Name des Bundesstaates, Landes angezeigt werden muss.

Für letzteres ist es wahrscheinlich egal, worauf der FK basiert, da wir Tabellen für einen einzelnen Datensatz oder einige Datensätze und Schlüssellesevorgänge zusammenführen. Das vorherige (Such- oder Listen-) Szenario kann von unserer Wahl beeinflusst werden. Da es erforderlich ist, das Land zu zeigen (zumindest ein erkennbarer Code und vielleicht sogar die Suche selbst einen Ländercode enthält), kann potentiell nicht durch einen Ersatzschlüssel mit einer anderen Tabelle verbunden werden (ich bin hier nur vorsichtig, weil ich nicht wirklich getestet habe) dies, aber scheint sehr wahrscheinlich) Verbesserung der performance; Ungeachtet dessen, dass es sicherlich bei der Suche hilft.

Da Codes klein sind – normalerweise nicht mehr als 3 Zeichen für Land und Staat, kann es in Ordnung sein, in diesem Szenario die natürlichen Schlüssel als Fremdschlüssel zu verwenden.

Das andere Szenario, in dem Schlüssel von längeren Varchar-Werten und möglicherweise größeren Tabellen abhängig sind; der Ersatzschlüssel hat wahrscheinlich den Vorteil.

Gestatten Sie mir zu sagen, dass es einen Unterschied gibt, der den performancesumfang berücksichtigt (Out of the Box-Definition):

1- Die Verwendung von Surrogat int ist schneller in der Anwendung, da Sie ToUpper (), ToLower (), ToUpperInvarient () oder ToLowerInvarient () in Ihrem Code oder in Ihrer Abfrage nicht benötigen und diese 4 functionen unterschiedliche performancesbenchmarks haben. Siehe Microsoft-performancesregeln zu diesem Thema. (Durchführung der Anwendung)

2- Die Verwendung von Surrogat int garantiert, dass der Schlüssel im Laufe der Zeit nicht geändert wird. Auch Ländercodes können sich ändern, siehe Wikipedia, wie sich ISO-Codes im Laufe der Zeit veränderten. Das würde viel Zeit brauchen, um den Primärschlüssel für Unterbäume zu ändern. (performance der Datenpflege)

3- Es scheint Probleme mit ORM-Lösungen, wie NHibernate, wenn PK / FK nicht Int ist. (Entwicklerleistung)