Frage MyISAM versus InnoDB [geschlossen]


Ich arbeite an einem Projekt, das viele Datenbank-Schreibvorgänge beinhaltet, würde ich sagen (70% Einsätze und 30% liest). Dieses Verhältnis würde auch Aktualisierungen umfassen, die ich für einen Lese- und einen Schreibvorgang halte. Die Lesevorgänge können schmutzig sein (z. B. benötige ich zum Zeitpunkt des Lesens keine 100% genaue Information).
Die Aufgabe besteht darin, mehr als 1 Million Datenbanktransaktionen pro Stunde durchzuführen.

Ich habe im Internet eine Menge über die Unterschiede zwischen MyISAM und InnoDB gelesen, und MyISAM scheint für mich die naheliegendste Wahl für die bestimmte Datenbank / Tabellen zu sein, die ich für diese Aufgabe verwenden werde. Von dem, was ich zu lesen scheint, ist InnoDB gut, wenn Transaktionen benötigt werden, da das Sperren auf Zeilenebene unterstützt wird.

Hat jemand Erfahrung mit dieser Art von Ladung (oder höher)? Ist MyISAM der richtige Weg?


807
2017-08-21 14:50


Ursprung


Antworten:


Ich habe kurz besprochen Diese Frage in einer Tabelle, damit Sie schließen können, ob Sie mit gehen InnoDB oder MyISAM.

Hier finden Sie eine kleine Übersicht darüber, welche db-Speicher-Engine in welcher Situation verwendet werden sollte:

                                                 MyISAM InnoDB
-------------------------------------------------- --------------
Erforderliche Volltextsuche Ja 5.6.4
-------------------------------------------------- --------------
Erfordern Transaktionen Ja
-------------------------------------------------- --------------
Häufige Auswahlabfragen Ja
-------------------------------------------------- --------------
Häufiges Einfügen, Aktualisieren, Löschen Ja
-------------------------------------------------- --------------
Zeilensperre (Mehrfachbearbeitung auf Einzeltisch) Ja
-------------------------------------------------- --------------
Relationales Basisdesign Ja

Zusammenfassen:

Häufiges Lesen, fast keine Schrift => MyISAM
Volltextsuche in MySQL <= 5.5 => MyISAM

Unter allen anderen Umständen InnoDB ist normalerweise der beste Weg zu gehen.


496
2017-07-22 22:01



Ich bin kein Datenbankexperte und spreche nicht aus Erfahrung. Jedoch:

MyISAM-Tabellen verwenden Sperren auf Tabellenebene. Basierend auf Ihren Traffic-Schätzungen haben Sie fast 200 Schreibvorgänge pro Sekunde. Mit MyISAM, nur eine davon könnte zu jeder Zeit in Bearbeitung sein. Sie müssen sicherstellen, dass Ihre Hardware mit dieser Transaktion Schritt halten kann, um eine Überschreitung zu vermeiden, d. H. Eine einzelne Abfrage kann nicht länger als 5 ms dauern.

Das deutet darauf hin, dass Sie eine Speicher-Engine benötigen würden, die Sperren auf Zeilenebene unterstützt, d. H. InnoDB.

Auf der anderen Seite sollte es ziemlich trivial sein, ein paar einfache Skripte zu schreiben, um die Last mit jeder Speicher-Engine zu simulieren, und dann die Ergebnisse zu vergleichen.


263
2017-08-22 16:03



Die Leute reden oft von Performance, Lese-Schreib-Lesefunktionen, Fremdschlüsseln usw. Aber es gibt meiner Meinung nach noch ein weiteres Must-Have-Feature für eine Speicher-Engine: atomare Updates.

Versuche dies:

  1. Geben Sie für Ihre MyISAM-Tabelle ein UPDATE aus, das 5 Sekunden dauert.
  2. Während das UPDATE läuft, sagen wir 2,5 Sekunden, drücken Sie Strg-C, um es zu unterbrechen.
  3. Beobachten Sie die Auswirkungen auf den Tisch. Wie viele Zeilen wurden aktualisiert? Wie viele wurden nicht aktualisiert? Ist die Tabelle sogar lesbar oder wurde sie beschädigt, wenn Sie Strg-C drücken?
  4. Versuchen Sie das gleiche Experiment mit UPDATE für eine InnoDB-Tabelle und unterbrechen Sie die laufende Abfrage.
  5. Beachten Sie die InnoDB-Tabelle. Null Zeilen wurden aktualisiert. InnoDB hat Ihnen zugesichert, atomare Updates zu haben, und wenn das vollständige Update nicht festgeschrieben werden konnte, wird die gesamte Änderung rückgängig gemacht. Außerdem ist die Tabelle nicht beschädigt. Dies funktioniert auch, wenn Sie verwenden killall -9 mysqld um einen Absturz zu simulieren.

Leistung ist natürlich wünschenswert, aber keine Daten verlieren sollte das übertrumpfen.


172
2017-07-17 17:47



Ich habe mit MySQL an einem hochvolumigen System gearbeitet und sowohl MyISAM als auch InnoDB ausprobiert.

Ich habe festgestellt, dass die Sperrung auf Tabellenebene in MyISAM zu ernsthaften Leistungsproblemen für unsere Arbeitslast geführt hat, die ähnlich wie Ihre aussieht. Leider stellte ich auch fest, dass die Leistung unter InnoDB auch schlechter war als ich mir erhofft hatte.

Am Ende habe ich das Konfliktproblem gelöst, indem ich die Daten so fragmentiert habe, dass die Einfügungen in eine "heiße" Tabelle gingen und die heiße Tabelle niemals ausgewählt wurde.

Dies erlaubte auch das Löschen (die Daten waren zeitabhängig und wir behielten nur den Wert von X Tagen) bei "veralteten" Tabellen auf, die wiederum nicht von ausgewählten Abfragen berührt wurden. InnoDB scheint bei Bulk-Löschvorgängen eine schlechte Leistung zu haben. Wenn Sie also Daten bereinigen möchten, sollten Sie sie so strukturieren, dass die alten Daten in einer veralteten Tabelle liegen, die einfach gelöscht werden kann, anstatt Löschungen auszuführen.

Natürlich weiß ich nicht, was Ihre Bewerbung ist, aber hoffentlich gibt Ihnen das einen Einblick in einige der Probleme mit MyISAM und InnoDB.


135
2017-09-16 21:57



Bei einer Belastung mit mehr Schreib- und Lesevorgängen profitieren Sie von InnoDB. Da InnoDB eine Zeilensperrung anstelle einer Tabellensperrung bietet, wird Ihre SELECTs kann gleichzeitig sein, nicht nur miteinander, sondern auch mit vielen INSERTs. Wenn Sie jedoch keine SQL-Transaktionen verwenden möchten, setzen Sie das InnoDB-Commit auf 2 (innodb_flush_log_at_trx_commit). Dadurch erhalten Sie eine Menge roher Leistung zurück, die Sie sonst verlieren würden, wenn Sie Tabellen von MyISAM nach InnoDB verschieben.

Ziehen Sie auch eine Replikation in Betracht. Dadurch erhalten Sie eine Lesenskalierung und da Sie angegeben haben, dass Ihre Lesevorgänge nicht auf dem neuesten Stand sein müssen, können Sie die Replikation ein wenig nachlassen. Seien Sie nur sicher, dass es unter etwas anderem als dem schwersten Verkehr aufholen kann, oder es wird immer dahinter zurückbleiben und niemals aufholen. Wenn du diesen Weg aber gehst, ich stark Ich empfehle Ihnen, das Lesen von den Slaves und die Replikationsverzögerung auf den Datenbankhandler zu isolieren. Es ist so viel einfacher, wenn der Anwendungscode dies nicht weiß.

Berücksichtigen Sie schließlich die unterschiedlichen Tabellenlasten. Sie haben nicht das gleiche Lese- / Schreibverhältnis für alle Tabellen. Einige kleinere Tabellen mit fast 100% Lesevorgängen könnten es sich leisten, MyISAM zu behalten. Ebenso, wenn Sie einige Tabellen haben, die fast 100% schreiben, können Sie davon profitieren INSERT DELAYED, aber das wird nur in MyISAM unterstützt DELAYED Klausel wird für eine InnoDB-Tabelle ignoriert).

Aber Benchmark um sicher zu sein.


61
2018-01-05 23:39



Ein bisschen spät zum Spiel ... aber hier ist eine ziemlich umfassende Post schrieb ich vor ein paar Monatenmit den wichtigsten Unterschieden zwischen MYISAM und InnoDB. Schnapp dir eine Tasse Tee (und vielleicht einen Keks) und genieße.


Der Hauptunterschied zwischen MyISAM und InnoDB liegt in referenzieller Integrität und Transaktionen. Es gibt auch andere Unterschiede wie Sperren, Rollbacks und Volltextsuchen.

Referentielle Integrität

Die referenzielle Integrität stellt sicher, dass Beziehungen zwischen Tabellen konsistent bleiben. Genauer gesagt bedeutet dies, dass wenn eine Tabelle (zB Listings) einen Fremdschlüssel (zB Produkt ID) auf eine andere Tabelle (zB Produkte) verweist, wenn Änderungen oder Löschungen in der Zeigetabelle auftreten, diese Änderungen kaskadiert werden Tabelle. Wenn in unserem Beispiel ein Produkt umbenannt wird, werden auch die Fremdschlüssel der Verknüpfungstabelle aktualisiert. Wenn ein Produkt aus der Tabelle "Produkte" gelöscht wird, werden auch alle Einträge, die auf den gelöschten Eintrag verweisen, gelöscht. Darüber hinaus muss bei jeder neuen Auflistung dieser Fremdschlüssel auf einen gültigen, vorhandenen Eintrag verweisen.

InnoDB ist ein relationales DBMS (RDBMS) und hat somit referentielle Integrität, während MyISAM dies nicht tut.

Transaktionen und Atomkraft

Daten in einer Tabelle werden mithilfe von DML-Anweisungen (Data Manipulation Language) wie SELECT, INSERT, UPDATE und DELETE verwaltet. Eine Transaktionsgruppe fasst zwei oder mehr DML-Anweisungen zu einer einzelnen Arbeitseinheit zusammen, sodass entweder die gesamte Einheit angewendet wird oder keine.

MyISAM unterstützt keine Transaktionen, während InnoDB dies tut.

Wenn eine Operation während der Verwendung einer MyISAM-Tabelle unterbrochen wird, wird die Operation sofort abgebrochen und die betroffenen Zeilen (oder sogar Daten in jeder Zeile) bleiben betroffen, auch wenn die Operation nicht abgeschlossen wurde.

Wenn eine Operation während der Verwendung einer InnoDB-Tabelle unterbrochen wird, weil Transaktionen verwendet werden, die Atomizität aufweisen, wird jede Transaktion, die nicht abgeschlossen wurde, nicht wirksam, da kein Commit durchgeführt wird.

Tabellenverriegelung gegen Zeilenverriegelung

Wenn eine Abfrage für eine MyISAM-Tabelle ausgeführt wird, wird die gesamte Tabelle gesperrt, in der sie abgefragt wird. Dies bedeutet, dass nachfolgende Abfragen nur ausgeführt werden, nachdem die aktuelle abgeschlossen ist. Wenn Sie eine große Tabelle lesen und / oder häufige Lese- und Schreibvorgänge ausführen, kann dies zu einem großen Rückstand an Abfragen führen.

Wenn eine Abfrage für eine InnoDB-Tabelle ausgeführt wird, sind nur die betroffenen Zeilen gesperrt. Der Rest der Tabelle bleibt für CRUD-Vorgänge verfügbar. Das bedeutet, dass Abfragen gleichzeitig in derselben Tabelle ausgeführt werden können, sofern sie nicht dieselbe Zeile verwenden.

Diese Funktion in InnoDB wird als Nebenläufigkeit bezeichnet. So groß wie die Parallelität ist, gibt es einen großen Nachteil, der für einen ausgewählten Bereich von Tabellen gilt, da es einen Overhead beim Umschalten zwischen den Kernel-Threads gibt, und Sie sollten die Kernel-Threads begrenzen, um zu verhindern, dass der Server zum Stillstand kommt .

Transaktionen und Rollbacks

Wenn Sie eine Operation in MyISAM ausführen, werden die Änderungen festgelegt. In InnoDB können diese Änderungen rückgängig gemacht werden. Die am häufigsten verwendeten Befehle zum Steuern von Transaktionen sind COMMIT, ROLLBACK und SAVEPOINT. 1. COMMIT - Sie können mehrere DML-Operationen schreiben, aber die Änderungen werden nur gespeichert, wenn ein COMMIT ausgeführt wird. 2. ROLLBACK - Sie können alle Operationen verwerfen, die noch nicht festgeschrieben wurden. 3. SAVEPOINT - setzt einen Punkt in der Liste von Operationen, auf die eine ROLLBACK-Operation zurückgesetzt werden kann

Zuverlässigkeit

MyISAM bietet keine Datenintegrität - Hardwarefehler, unsauberes Herunterfahren und abgebrochene Vorgänge können dazu führen, dass die Daten beschädigt werden. Dies würde eine vollständige Reparatur oder Neuerstellung der Indizes und Tabellen erfordern.

InnoDB hingegen verwendet ein Transaktionsprotokoll, einen Doppelschreibpuffer und automatische Prüfsummen und Validierung, um Korruption zu verhindern. Bevor InnoDB Änderungen vornimmt, zeichnet es die Daten vor den Transaktionen in einer Systemtabellenbereichsdatei namens ibdata1 auf. Wenn es einen Absturz gibt, würde InnoDB durch die Wiedergabe dieser Protokolle automatisch wiederherstellen.

FULLTEXT Indizierung

InnoDB unterstützt die FULLTEXT-Indizierung erst ab MySQL Version 5.6.4. Zum Zeitpunkt des Schreibens dieses Posts liegt die MySQL-Version vieler Shared Hosting-Provider immer noch unterhalb von 5.6.4, was bedeutet, dass die FULLTEXT-Indizierung für InnoDB-Tabellen nicht unterstützt wird.

Dies ist jedoch kein Grund, MyISAM zu verwenden. Es empfiehlt sich, zu einem Hosting-Provider zu wechseln, der aktuelle MySQL-Versionen unterstützt. Nicht, dass eine MyISAM-Tabelle, die die FULLTEXT-Indizierung verwendet, nicht in eine InnoDB-Tabelle konvertiert werden kann.

Fazit

Schlussfolgernd sollte InnoDB Ihre Standardspeicher-Engine der Wahl sein. Wählen Sie MyISAM oder andere Datentypen, wenn sie einem bestimmten Bedarf entsprechen.


61
2018-01-21 15:32



Um die große Auswahl an Antworten zu ergänzen, die die mechanischen Unterschiede zwischen den beiden Motoren abdecken, präsentiere ich eine empirische Geschwindigkeitsvergleichsstudie.

Im Hinblick auf die reine Geschwindigkeit ist es nicht immer so, dass MyISAM schneller ist als InnoDB, aber meiner Erfahrung nach ist es für PURE READ Arbeitsumgebungen um einen Faktor von etwa 2,0-2,5 mal schneller. Natürlich ist dies nicht für alle Umgebungen geeignet - wie andere geschrieben haben, fehlt MyISAM Dinge wie Transaktionen und Fremdschlüssel.

Ich habe unten ein paar Benchmarks gemacht - ich habe Python für das Schleifen und die Timit-Bibliothek für Timing-Vergleiche verwendet. Für Interesse habe ich auch die Gedächtnismaschine eingeschlossen, diese gibt die beste Leistung auf der ganzen Linie, obwohl sie nur für kleinere Tabellen passend ist (Sie stoßen fortwährend an The table 'tbl' is full wenn Sie das MySQL-Speicherlimit überschreiten. Die vier Arten von Auswahl, die ich betrachte sind:

  1. Vanille SELECTs
  2. zählt
  3. bedingte SELECTs
  4. indizierte und nicht indizierte Unterauswahl

Zuerst habe ich drei Tabellen mit dem folgenden SQL erstellt

CREATE TABLE
    data_interrogation.test_table_myisam
    (
        index_col BIGINT NOT NULL AUTO_INCREMENT,
        value1 DOUBLE,
        value2 DOUBLE,
        value3 DOUBLE,
        value4 DOUBLE,
        PRIMARY KEY (index_col)
    )
    ENGINE=MyISAM DEFAULT CHARSET=utf8

mit 'MyISAM' ersetzt 'InnoDB' und 'Speicher' in der zweiten und dritten Tabelle.

1) Vanille wählt

Abfrage: SELECT * FROM tbl WHERE index_col = xx

Ergebnis: zeichnen

Comparison of vanilla selects by different database engines

Die Geschwindigkeit von diesen ist im Großen und Ganzen die gleiche und wie erwartet ist linear in der Anzahl der Spalten ausgewählt werden. InnoDB scheint leicht schneller als MyISAM, aber das ist wirklich marginal.

Code:

import timeit
import MySQLdb
import MySQLdb.cursors
import random
from random import randint

db = MySQLdb.connect(host="...", user="...", passwd="...", db="...", cursorclass=MySQLdb.cursors.DictCursor)
cur = db.cursor()

lengthOfTable = 100000

# Fill up the tables with random data
for x in xrange(lengthOfTable):
    rand1 = random.random()
    rand2 = random.random()
    rand3 = random.random()
    rand4 = random.random()

    insertString = "INSERT INTO test_table_innodb (value1,value2,value3,value4) VALUES (" + str(rand1) + "," + str(rand2) + "," + str(rand3) + "," + str(rand4) + ")"
    insertString2 = "INSERT INTO test_table_myisam (value1,value2,value3,value4) VALUES (" + str(rand1) + "," + str(rand2) + "," + str(rand3) + "," + str(rand4) + ")"
    insertString3 = "INSERT INTO test_table_memory (value1,value2,value3,value4) VALUES (" + str(rand1) + "," + str(rand2) + "," + str(rand3) + "," + str(rand4) + ")"

    cur.execute(insertString)
    cur.execute(insertString2)
    cur.execute(insertString3)

db.commit()

# Define a function to pull a certain number of records from these tables
def selectRandomRecords(testTable,numberOfRecords):

    for x in xrange(numberOfRecords):
        rand1 = randint(0,lengthOfTable)

        selectString = "SELECT * FROM " + testTable + " WHERE index_col = " + str(rand1)
        cur.execute(selectString)

setupString = "from __main__ import selectRandomRecords"

# Test time taken using timeit
myisam_times = []
innodb_times = []
memory_times = []

for theLength in [3,10,30,100,300,1000,3000,10000]:

    innodb_times.append( timeit.timeit('selectRandomRecords("test_table_innodb",' + str(theLength) + ')', number=100, setup=setupString) )
    myisam_times.append( timeit.timeit('selectRandomRecords("test_table_myisam",' + str(theLength) + ')', number=100, setup=setupString) )
    memory_times.append( timeit.timeit('selectRandomRecords("test_table_memory",' + str(theLength) + ')', number=100, setup=setupString) )

2) zählt

Abfrage: SELECT count(*) FROM tbl

Ergebnis: MyISAM gewinnt

Comparison of counts by different database engines

Dieser zeigt einen großen Unterschied zwischen MyISAM und InnoDB - MyISAM (und Speicher) verfolgt die Anzahl der Datensätze in der Tabelle, also ist diese Transaktion schnell und O (1). Die Zeit, die InnoDB benötigt, um zu zählen, steigt superlinear mit der Tabellengröße in dem Bereich, den ich untersucht habe. Ich vermute, dass viele der Beschleunigungen von MyISAM-Abfragen, die in der Praxis beobachtet werden, auf ähnliche Effekte zurückzuführen sind.

Code:

myisam_times = []
innodb_times = []
memory_times = []

# Define a function to count the records
def countRecords(testTable):

    selectString = "SELECT count(*) FROM " + testTable
    cur.execute(selectString)

setupString = "from __main__ import countRecords"

# Truncate the tables and re-fill with a set amount of data
for theLength in [3,10,30,100,300,1000,3000,10000,30000,100000]:

    truncateString = "TRUNCATE test_table_innodb"
    truncateString2 = "TRUNCATE test_table_myisam"
    truncateString3 = "TRUNCATE test_table_memory"

    cur.execute(truncateString)
    cur.execute(truncateString2)
    cur.execute(truncateString3)

    for x in xrange(theLength):
        rand1 = random.random()
        rand2 = random.random()
        rand3 = random.random()
        rand4 = random.random()

        insertString = "INSERT INTO test_table_innodb (value1,value2,value3,value4) VALUES (" + str(rand1) + "," + str(rand2) + "," + str(rand3) + "," + str(rand4) + ")"
        insertString2 = "INSERT INTO test_table_myisam (value1,value2,value3,value4) VALUES (" + str(rand1) + "," + str(rand2) + "," + str(rand3) + "," + str(rand4) + ")"
        insertString3 = "INSERT INTO test_table_memory (value1,value2,value3,value4) VALUES (" + str(rand1) + "," + str(rand2) + "," + str(rand3) + "," + str(rand4) + ")"

        cur.execute(insertString)
        cur.execute(insertString2)
        cur.execute(insertString3)

    db.commit()

    # Count and time the query
    innodb_times.append( timeit.timeit('countRecords("test_table_innodb")', number=100, setup=setupString) )
    myisam_times.append( timeit.timeit('countRecords("test_table_myisam")', number=100, setup=setupString) )
    memory_times.append( timeit.timeit('countRecords("test_table_memory")', number=100, setup=setupString) )

3) Bedingte Auswahl

Abfrage: SELECT * FROM tbl WHERE value1<0.5 AND value2<0.5 AND value3<0.5 AND value4<0.5

Ergebnis: MyISAM gewinnt

Comparison of conditional selects by different database engines

Hier führen MyISAM und Speicher ungefähr dasselbe aus und schlagen InnoDB bei größeren Tabellen um etwa 50%. Dies ist die Art von Abfrage, für die die Vorteile von MyISAM maximiert scheinen.

Code:

myisam_times = []
innodb_times = []
memory_times = []

# Define a function to perform conditional selects
def conditionalSelect(testTable):
    selectString = "SELECT * FROM " + testTable + " WHERE value1 < 0.5 AND value2 < 0.5 AND value3 < 0.5 AND value4 < 0.5"
    cur.execute(selectString)

setupString = "from __main__ import conditionalSelect"

# Truncate the tables and re-fill with a set amount of data
for theLength in [3,10,30,100,300,1000,3000,10000,30000,100000]:

    truncateString = "TRUNCATE test_table_innodb"
    truncateString2 = "TRUNCATE test_table_myisam"
    truncateString3 = "TRUNCATE test_table_memory"

    cur.execute(truncateString)
    cur.execute(truncateString2)
    cur.execute(truncateString3)

    for x in xrange(theLength):
        rand1 = random.random()
        rand2 = random.random()
        rand3 = random.random()
        rand4 = random.random()

        insertString = "INSERT INTO test_table_innodb (value1,value2,value3,value4) VALUES (" + str(rand1) + "," + str(rand2) + "," + str(rand3) + "," + str(rand4) + ")"
        insertString2 = "INSERT INTO test_table_myisam (value1,value2,value3,value4) VALUES (" + str(rand1) + "," + str(rand2) + "," + str(rand3) + "," + str(rand4) + ")"
        insertString3 = "INSERT INTO test_table_memory (value1,value2,value3,value4) VALUES (" + str(rand1) + "," + str(rand2) + "," + str(rand3) + "," + str(rand4) + ")"

        cur.execute(insertString)
        cur.execute(insertString2)
        cur.execute(insertString3)

    db.commit()

    # Count and time the query
    innodb_times.append( timeit.timeit('conditionalSelect("test_table_innodb")', number=100, setup=setupString) )
    myisam_times.append( timeit.timeit('conditionalSelect("test_table_myisam")', number=100, setup=setupString) )
    memory_times.append( timeit.timeit('conditionalSelect("test_table_memory")', number=100, setup=setupString) )

4) Sub-wählt

Ergebnis: InnoDB gewinnt

Für diese Abfrage habe ich einen zusätzlichen Satz von Tabellen für die Unterauswahl erstellt. Jeder besteht aus zwei Spalten mit BIGINTs, einem mit einem Primärschlüsselindex und einem ohne Index. Aufgrund der großen Tabellengröße habe ich die Speicher-Engine nicht getestet. Der SQL-Tabellenerstellungsbefehl war

CREATE TABLE
    subselect_myisam
    (
        index_col bigint NOT NULL,
        non_index_col bigint,
        PRIMARY KEY (index_col)
    )
    ENGINE=MyISAM DEFAULT CHARSET=utf8;

In der zweiten Tabelle wird noch einmal 'MyISAM' durch 'InnoDB' ersetzt.

In dieser Abfrage lasse ich die Größe der Auswahltabelle bei 1000000 und verändere stattdessen die Größe der unterselektierten Spalten.

Comparison of sub-selects by different database engines

Hier gewinnt die InnoDB leicht. Nachdem wir eine vernünftige Tabelle erreicht haben, skalieren beide Engines linear mit der Größe der Unterauswahl. Der Index beschleunigt den MyISAM-Befehl, hat aber interessanterweise nur geringe Auswirkungen auf die InnoDB-Geschwindigkeit. subSelect.png

Code:

myisam_times = []
innodb_times = []
myisam_times_2 = []
innodb_times_2 = []

def subSelectRecordsIndexed(testTable,testSubSelect):
    selectString = "SELECT * FROM " + testTable + " WHERE index_col in ( SELECT index_col FROM " + testSubSelect + " )"
    cur.execute(selectString)

setupString = "from __main__ import subSelectRecordsIndexed"

def subSelectRecordsNotIndexed(testTable,testSubSelect):
    selectString = "SELECT * FROM " + testTable + " WHERE index_col in ( SELECT non_index_col FROM " + testSubSelect + " )"
    cur.execute(selectString)

setupString2 = "from __main__ import subSelectRecordsNotIndexed"

# Truncate the old tables, and re-fill with 1000000 records
truncateString = "TRUNCATE test_table_innodb"
truncateString2 = "TRUNCATE test_table_myisam"

cur.execute(truncateString)
cur.execute(truncateString2)

lengthOfTable = 1000000

# Fill up the tables with random data
for x in xrange(lengthOfTable):
    rand1 = random.random()
    rand2 = random.random()
    rand3 = random.random()
    rand4 = random.random()

    insertString = "INSERT INTO test_table_innodb (value1,value2,value3,value4) VALUES (" + str(rand1) + "," + str(rand2) + "," + str(rand3) + "," + str(rand4) + ")"
    insertString2 = "INSERT INTO test_table_myisam (value1,value2,value3,value4) VALUES (" + str(rand1) + "," + str(rand2) + "," + str(rand3) + "," + str(rand4) + ")"

    cur.execute(insertString)
    cur.execute(insertString2)

for theLength in [3,10,30,100,300,1000,3000,10000,30000,100000]:

    truncateString = "TRUNCATE subselect_innodb"
    truncateString2 = "TRUNCATE subselect_myisam"

    cur.execute(truncateString)
    cur.execute(truncateString2)

    # For each length, empty the table and re-fill it with random data
    rand_sample = sorted(random.sample(xrange(lengthOfTable), theLength))
    rand_sample_2 = random.sample(xrange(lengthOfTable), theLength)

    for (the_value_1,the_value_2) in zip(rand_sample,rand_sample_2):
        insertString = "INSERT INTO subselect_innodb (index_col,non_index_col) VALUES (" + str(the_value_1) + "," + str(the_value_2) + ")"
        insertString2 = "INSERT INTO subselect_myisam (index_col,non_index_col) VALUES (" + str(the_value_1) + "," + str(the_value_2) + ")"

        cur.execute(insertString)
        cur.execute(insertString2)

    db.commit()

    # Finally, time the queries
    innodb_times.append( timeit.timeit('subSelectRecordsIndexed("test_table_innodb","subselect_innodb")', number=100, setup=setupString) )
    myisam_times.append( timeit.timeit('subSelectRecordsIndexed("test_table_myisam","subselect_myisam")', number=100, setup=setupString) )

    innodb_times_2.append( timeit.timeit('subSelectRecordsNotIndexed("test_table_innodb","subselect_innodb")', number=100, setup=setupString2) )
    myisam_times_2.append( timeit.timeit('subSelectRecordsNotIndexed("test_table_myisam","subselect_myisam")', number=100, setup=setupString2) )

Ich denke, die Botschaft von all dem ist, dass, wenn Sie es sind Ja wirklich Wenn Sie sich Gedanken über die Geschwindigkeit machen, müssen Sie die Abfragen, die Sie durchführen, vergleichen und keine Annahmen darüber treffen, welche Engine besser geeignet ist.


51
2018-06-11 09:15



Etwas off-topic, aber für die Dokumentation und Vollständigkeit, möchte ich Folgendes hinzufügen.

Im Allgemeinen führt die Verwendung von InnoDB zu einer viel weniger komplexen Anwendung, wahrscheinlich auch fehlerfreier. Da Sie die gesamte referenzielle Integrität (Fremdschlüsseleinschränkungen) in das Datenmodell einfügen können, benötigen Sie nicht annähernd so viel Anwendungscode, wie Sie mit MyISAM benötigen.

Jedes Mal, wenn Sie einen Datensatz einfügen, löschen oder ersetzen, müssen Sie die Beziehungen überprüfen und pflegen. Z.B. Wenn Sie einen Eltern löschen, sollten auch alle Kinder gelöscht werden. Wenn Sie beispielsweise in einem einfachen Blogging-System einen Blogposting-Datensatz löschen, müssen Sie die Kommentarsätze usw. löschen. In InnoDB wird dies automatisch von der Datenbank-Engine durchgeführt (wenn Sie die Einschränkungen im Modell angegeben haben) ) und erfordert keinen Anwendungscode. In MyISAM muss dies in die Anwendung kodiert werden, was bei Webservern sehr schwierig ist. Web-Server sind von Natur aus sehr parallel / parallel und da diese Aktionen atomar sein sollten und MyISAM keine realen Transaktionen unterstützt, ist die Verwendung von MyISAM für Web-Server riskant / fehleranfällig.

Auch in den meisten Fällen wird InnoDB viel besser funktionieren, und zwar aus mehreren Gründen, zum einen weil sie im Gegensatz zu Sperren auf Tabellenebene Sperren auf Datensatzebene verwenden können. Nicht nur in Situationen, in denen Schreibvorgänge häufiger sind als in Lesevorgängen, auch in Situationen mit komplexen Joins in großen Datasets. Wir haben eine 3-fache Leistungssteigerung festgestellt, indem wir InnoDB-Tabellen über MyISAM-Tabellen für sehr große Joins verwenden (die mehrere Minuten dauern).

Ich würde sagen, dass InnoDB (unter Verwendung eines 3NF-Datenmodells mit referenzieller Integrität) im Allgemeinen die Standardauswahl bei der Verwendung von MySQL sein sollte. MyISAM sollte nur in ganz bestimmten Fällen verwendet werden. Es wird wahrscheinlich weniger ausführen, was zu einer größeren und fehlerhafteren Anwendung führt.

Nachdem ich das gesagt habe. Datamodelling ist eine Kunst, die selten unter Webdesignern / -programmierern zu finden ist. Nichts für ungut, aber es erklärt, dass MyISAM so oft benutzt wird.


32
2017-08-26 12:18



InnoDB bietet:

ACID transactions
row-level locking
foreign key constraints
automatic crash recovery
table compression (read/write)
spatial data types (no spatial indexes)

In InnoDB können alle Daten in einer Zeile außer TEXT und BLOB maximal 8.000 Bytes belegen. Für InnoDB ist keine Volltextindizierung verfügbar. In InnoDB wird COUNT (*) s (wenn WHERE, GROUP BY oder JOIN nicht verwendet wird) langsamer ausgeführt als in MyISAM, da die Zeilenanzahl nicht intern gespeichert wird. InnoDB speichert sowohl Daten als auch Indizes in einer Datei. InnoDB verwendet einen Pufferpool zum Zwischenspeichern von Daten und Indizes.

MyISAM bietet:

fast COUNT(*)s (when WHERE, GROUP BY, or JOIN is not used)
full text indexing
smaller disk footprint
very high table compression (read only)
spatial data types and indexes (R-tree)

MyISAM hat Sperren auf Tabellenebene, aber keine Sperren auf Zeilenebene. Keine Transaktionen Keine automatische Wiederherstellung nach einem Systemabsturz, bietet jedoch Reparaturfunktionalität. Keine Fremdschlüsseleinschränkungen MyISAM-Tabellen sind im Vergleich zu InnoDB-Tabellen im Allgemeinen kompakter in der Größe auf der Festplatte. MyISAM-Tabellen können durch Komprimieren mit myisampack bei Bedarf weiter stark verkleinert werden, werden aber schreibgeschützt. MyISAM speichert Indizes in einer Datei und Daten in einer anderen. MyISAM verwendet Schlüsselpuffer zum Zwischenspeichern von Indizes und überlässt die Daten-Caching-Verwaltung dem Betriebssystem.

Insgesamt würde ich InnoDB für die meisten Zwecke und MyISAM nur für spezielle Zwecke empfehlen. InnoDB ist jetzt die Standard-Engine in neuen MySQL-Versionen.


29
2018-05-28 07:03



Wenn Sie MyISAM verwenden, werden Sie nicht tun irgendein Transaktionen pro Stunde, es sei denn, Sie betrachten jede DML-Anweisung als eine Transaktion (die im Falle eines Absturzes in keinem Fall dauerhaft oder atomar ist).

Daher denke ich, dass Sie InnoDB verwenden müssen.

300 Transaktionen pro Sekunde klingt ziemlich viel. Wenn Sie diese Transaktionen unbedingt dauerhaft bei Stromausfall benötigen, stellen Sie sicher, dass Ihr E / A-Subsystem diese vielen Schreibvorgänge pro Sekunde problemlos bewältigen kann. Sie benötigen mindestens einen RAID-Controller mit batteriegepuffertem Cache.

Wenn Sie einen kleinen Haltbarkeits-Treffer erzielen können, können Sie InnoDB verwenden, indem innodb_flush_log_at_trx_commit auf 0 oder 2 gesetzt wird (Details siehe Dokumente). Sie können die Leistung verbessern.

Es gibt eine Reihe von Patches, die die Gleichzeitigkeit von Google und anderen erhöhen können. Diese können von Interesse sein, wenn Sie immer noch nicht genug Leistung ohne sie erhalten können.


24
2017-09-16 21:34