Question MyISAM versus InnoDB [fermé]


Je travaille sur un projet qui implique de nombreuses écritures de base de données, je dirais (70% d'inserts et 30% de lectures). Ce ratio comprendrait également des mises à jour que je considère comme étant lues et écrites. Les lectures peuvent être sales (par exemple, je n'ai pas besoin d'informations précises à 100% au moment de la lecture).
La tâche en question fera plus d'un million de transactions de base de données par heure.

J'ai lu beaucoup de choses sur le web à propos des différences entre MyISAM et InnoDB, et MyISAM me semble être le choix évident pour la base de données / les tables que j'utiliserai pour cette tâche. D'après ce que j'ai l'impression de lire, InnoDB est bon si des transactions sont nécessaires puisque le verrouillage au niveau de la ligne est supporté.

Quelqu'un at-il une expérience avec ce type de charge (ou plus)? Est-ce que MyISAM est le chemin à parcourir?


807
2017-08-21 14:50


origine


Réponses:


J'ai brièvement discuté cette question dans un tableau de sorte que vous pouvez conclure si aller avec InnoDB ou MyISAM.

Voici un petit aperçu du moteur de stockage db que vous devez utiliser dans quelle situation:

                                                 MyISAM InnoDB
-------------------------------------------------- --------------
Recherche de texte intégral requise Oui 5.6.4
-------------------------------------------------- --------------
Exiger des transactions Oui
-------------------------------------------------- --------------
Fréquentes sélectionner les requêtes Oui
-------------------------------------------------- --------------
Insertion fréquente, mise à jour, suppression Oui
-------------------------------------------------- --------------
Verrouillage de ligne (multi-traitement sur une seule table) Oui
-------------------------------------------------- --------------
Conception de base relationnelle Oui

Résumer:

Lecture fréquente, presque pas d'écriture => MyISAM
Recherche en texte intégral dans MySQL <= 5.5 => MyISAM

Dans toutes les autres circonstances, InnoDB est généralement la meilleure façon de faire.


496
2017-07-22 22:01



Je ne suis pas expert en bases de données, et je ne parle pas d'expérience. Toutefois:

Les tables MyISAM utilisent un verrouillage au niveau de la table. Selon vos estimations de trafic, vous avez près de 200 écritures par seconde. Avec MyISAM, un seul d'entre eux pourrait être en cours à tout moment. Vous devez vous assurer que votre matériel peut suivre ces transactions pour éviter le dépassement, c'est-à-dire qu'une seule requête ne peut pas prendre plus de 5 ms.

Cela me suggère que vous auriez besoin d'un moteur de stockage qui prend en charge le verrouillage au niveau des lignes, c'est-à-dire InnoDB.

D'autre part, il devrait être assez trivial d'écrire quelques scripts simples pour simuler la charge avec chaque moteur de stockage, puis comparer les résultats.


263
2017-08-22 16:03



Les gens parlent souvent des performances, des lectures et des écritures, des clés étrangères, etc. Mais à mon avis, il existe une autre fonctionnalité incontournable pour un moteur de stockage: mises à jour atomiques.

Essaye ça:

  1. Émettez un UPDATE contre votre table MyISAM qui prend 5 secondes.
  2. Pendant que la mise à jour est en cours, par exemple 2,5 secondes, appuyez sur Ctrl-C pour l'interrompre.
  3. Observez les effets sur la table. Combien de lignes ont été mises à jour? Combien n'ont pas été mis à jour? La table est-elle même lisible ou est-elle corrompue lorsque vous appuyez sur Ctrl-C?
  4. Essayez la même expérience avec UPDATE sur une table InnoDB, en interrompant la requête en cours.
  5. Observez la table InnoDB. Zéro les lignes ont été mises à jour. InnoDB vous a assuré avoir des mises à jour atomiques, et si la mise à jour complète n'a pas pu être validée, elle annule tout le changement. En outre, la table n'est pas corrompue. Cela fonctionne même si vous utilisez killall -9 mysqld simuler un crash

La performance est souhaitable bien sûr, mais ne pas perdre de données devrait l'emporter.


172
2017-07-17 17:47



J'ai travaillé sur un système à haut volume utilisant MySQL et j'ai essayé MyISAM et InnoDB.

J'ai trouvé que le verrouillage au niveau de la table dans MyISAM causait de sérieux problèmes de performance pour notre charge de travail qui ressemble à la vôtre. Malheureusement, j'ai également constaté que la performance sous InnoDB était également pire que ce que j'espérais.

À la fin, j'ai résolu le problème de contention en fragmentant les données de telle sorte que les insertions sont entrées dans une table "chaude" et que les sélections n'ont jamais interrogé la table active.

Cela a également permis des suppressions (les données étaient sensibles au temps et nous n'avons retenu que X jours) sur des tables "périmées" qui n'étaient pas encore touchées par des requêtes select. InnoDB semble avoir des performances médiocres sur les suppressions groupées. Si vous envisagez de purger des données, vous pouvez les structurer de telle manière que les anciennes données soient dans une table obsolète qui peut simplement être supprimée au lieu de supprimer des suppressions.

Bien sûr, je n'ai aucune idée de ce qu'est votre application mais j'espère que cela vous donne un aperçu de certains problèmes avec MyISAM et InnoDB.


135
2017-09-16 21:57



Pour un chargement avec plus d'écritures et de lectures, vous bénéficierez d'InnoDB. Parce que InnoDB fournit le verrouillage de ligne plutôt que le verrouillage de table, votre SELECTs peuvent être simultanés, pas seulement entre eux, mais aussi avec de nombreux INSERTs. Cependant, sauf si vous avez l'intention d'utiliser des transactions SQL, définissez le vidage de validation InnoDB sur 2 (innodb_flush_log_at_trx_commit). Cela vous renvoie beaucoup de performances brutes que vous auriez perdues lors du déplacement de tables de MyISAM vers InnoDB.

En outre, envisagez d'ajouter une réplication. Cela vous donne un peu de mise à l'échelle et puisque vous avez déclaré que vos lectures ne doivent pas être à jour, vous pouvez laisser la réplication prendre un peu de retard. Assurez-vous juste qu'il peut rattraper n'importe quoi sauf le trafic le plus lourd ou il sera toujours derrière et ne rattrapera jamais. Si vous allez de cette façon, cependant, je fortement Nous vous recommandons d'isoler la lecture des esclaves et la gestion des délais de réplication dans votre gestionnaire de base de données. C'est tellement plus simple si le code de l'application ne le sait pas.

Enfin, soyez conscient des différentes charges de table. Vous n'aurez pas le même rapport lecture / écriture sur toutes les tables. Certaines tables plus petites avec près de 100% de lectures pourraient permettre de rester MyISAM. De même, si vous avez des tables proches de 100%, vous pouvez bénéficier de INSERT DELAYED, mais cela est uniquement pris en charge dans MyISAM (le DELAYED clause est ignorée pour une table InnoDB).

Mais benchmark pour être sûr.


61
2018-01-05 23:39



Un peu en retard au jeu ... mais voici un assez complet après j'ai écrit il y a quelques mois, détaillant les différences majeures entre MYISAM et InnoDB. Prenez une tasse de thé (et peut-être un biscuit), et profitez-en.


La principale différence entre MyISAM et InnoDB réside dans l'intégrité référentielle et les transactions. Il existe également d'autres différences telles que le verrouillage, les annulations et les recherches en texte intégral.

Intégrité référentielle

L'intégrité référentielle garantit que les relations entre les tables restent cohérentes. Plus précisément, cela signifie qu'une table (par exemple Listings) a une clé étrangère (par exemple Product ID) pointant vers une table différente (par exemple Products), lorsque des mises à jour ou des suppressions se produisent dans la table pointée, ces changements sont en cascade table. Dans notre exemple, si un produit est renommé, les clés étrangères de la table de liaison seront également mises à jour; Si un produit est supprimé de la table "Produits", les listes qui pointent vers l'entrée supprimée seront également supprimées. En outre, toute nouvelle liste doit avoir cette clé étrangère pointant vers une entrée existante valide.

InnoDB est un SGBD relationnel (SGBDR) et a donc une intégrité référentielle, contrairement à MyISAM.

Transactions et atomicité

Les données d'une table sont gérées à l'aide des instructions DML (Data Manipulation Language), telles que SELECT, INSERT, UPDATE et DELETE. Une transaction regroupe deux déclarations DML ou plus en une seule unité de travail, de sorte que l'unité entière est appliquée, ou rien de tout cela.

MyISAM ne supporte pas les transactions, contrairement à InnoDB.

Si une opération est interrompue lors de l'utilisation d'une table MyISAM, l'opération est annulée immédiatement et les lignes (ou même les données de chaque ligne) affectées restent affectées, même si l'opération n'est pas terminée.

Si une opération est interrompue lors de l'utilisation d'une table InnoDB, parce qu'elle utilise des transactions, ce qui a de l'atomicité, toute transaction qui ne s'est pas achevée ne prendra pas effet, puisqu'aucune validation n'est faite.

Verrouillage de table contre verrouillage de rangée

Lorsqu'une requête est exécutée sur une table MyISAM, la table entière dans laquelle elle interroge sera verrouillée. Cela signifie que les requêtes suivantes ne seront exécutées qu'après la fin de la requête en cours. Si vous lisez une grande table et / ou si vous effectuez de fréquentes opérations de lecture et d'écriture, cela peut entraîner un énorme retard dans les requêtes.

Lorsqu'une requête s'exécute sur une table InnoDB, seules les lignes concernées sont verrouillées, le reste de la table reste disponible pour les opérations CRUD. Cela signifie que les requêtes peuvent s'exécuter simultanément sur la même table, à condition qu'elles n'utilisent pas la même ligne.

Cette fonctionnalité dans InnoDB est connue sous le nom de concurrence. Aussi grande que la concurrence est, il y a un inconvénient majeur qui s'applique à une sélection de tables, en ce qu'il y a un overhead dans la commutation entre les threads du noyau, et vous devez définir une limite sur les threads du noyau pour empêcher le serveur de s'arrêter .

Transactions et Rollbacks

Lorsque vous exécutez une opération dans MyISAM, les modifications sont définies. dans InnoDB, ces changements peuvent être annulés. Les commandes les plus courantes utilisées pour contrôler les transactions sont COMMIT, ROLLBACK et SAVEPOINT. 1. COMMIT - vous pouvez écrire plusieurs opérations DML, mais les changements ne seront sauvegardés que quand un COMMIT est fait 2. ROLLBACK - vous pouvez annuler toutes les opérations qui n'ont pas encore été validées 3. SAVEPOINT - définit un point dans la liste des opérations auxquelles une opération ROLLBACK peut être annulée

Fiabilité

MyISAM n'offre aucune intégrité des données - Des pannes matérielles, des fermetures mal nettoyées et des opérations annulées peuvent corrompre les données. Cela nécessiterait une réparation complète ou des reconstructions des index et des tables.

InnoDB, d'un autre côté, utilise un journal transactionnel, un tampon d'écriture double et une vérification et une vérification automatique pour éviter toute corruption. Avant de procéder à des modifications, InnoDB enregistre les données avant les transactions dans un fichier d'espace table système appelé ibdata1. En cas d'accident, InnoDB se réenregistre automatiquement via la relecture de ces journaux.

Indexation FULLTEXT

InnoDB ne supporte pas l'indexation FULLTEXT jusqu'à MySQL version 5.6.4. Au moment de l'écriture de ce post, la version MySQL de nombreux hébergeurs partagés est toujours inférieure à 5.6.4, ce qui signifie que l'indexation FULLTEXT n'est pas supportée pour les tables InnoDB.

Cependant, ce n'est pas une raison valable pour utiliser MyISAM. Il est préférable de passer à un fournisseur d'hébergement qui prend en charge les versions mises à jour de MySQL. Ce n'est pas qu'une table MyISAM qui utilise l'indexation FULLTEXT ne peut pas être convertie en une table InnoDB.

Conclusion

En conclusion, InnoDB devrait être votre moteur de stockage par défaut. Choisissez MyISAM ou d'autres types de données lorsqu'ils répondent à un besoin spécifique.


61
2018-01-21 15:32



Pour ajouter à la large sélection de réponses couvrant ici les différences mécaniques entre les deux moteurs, je présente une étude de comparaison de vitesse empirique.

En termes de vitesse pure, ce n'est pas toujours le cas MyISAM est plus rapide que InnoDB mais dans mon expérience, il a tendance à être plus rapide pour les environnements de travail PURE READ par un facteur d'environ 2,0-2,5 fois. Il est clair que ceci n'est pas approprié pour tous les environnements - comme d'autres l'ont écrit, MyISAM manque de choses telles que les transactions et les clés étrangères.

J'ai fait un peu de benchmarking ci-dessous - j'ai utilisé python pour la boucle et la bibliothèque timeit pour les comparaisons de temps. Pour l'intérêt j'ai également inclus le moteur de mémoire, ceci donne la meilleure exécution à travers le conseil bien qu'il soit seulement approprié aux tables plus petites (vous rencontrerez continuellement The table 'tbl' is full lorsque vous dépassez la limite de la mémoire MySQL). Les quatre types de sélection que je regarde sont:

  1. Vanilla SELECTs
  2. compte
  3. sélections conditionnelles
  4. sous-sélection indexée et non-indexée

Tout d'abord, j'ai créé trois tables en utilisant le SQL suivant

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

avec "MyISAM" substitué à "InnoDB" et "mémoire" dans les deuxième et troisième tables.

1) Sélection de vanille

Question: SELECT * FROM tbl WHERE index_col = xx

Résultat: dessiner

Comparison of vanilla selects by different database engines

La vitesse de ceux-ci est globalement la même, et comme prévu est linéaire dans le nombre de colonnes à sélectionner. InnoDB semble légèrement plus rapide que MyISAM mais c'est vraiment 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) Compte

Question: SELECT count(*) FROM tbl

Résultat: MyISAM gagne

Comparison of counts by different database engines

Celui-ci montre une grande différence entre MyISAM et InnoDB - MyISAM (et la mémoire) garde une trace du nombre d'enregistrements dans la table, donc cette transaction est rapide et O (1). La quantité de temps nécessaire à InnoDB pour compter augmente de façon super-linéaire avec la taille de la table dans la plage que j'ai étudiée. Je soupçonne que beaucoup des accélérations des requêtes MyISAM observées dans la pratique sont dues à des effets similaires.

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) Sélections conditionnelles

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

Résultat: MyISAM gagne

Comparison of conditional selects by different database engines

Ici, MyISAM et la mémoire fonctionnent à peu près de la même façon, et battent InnoDB d'environ 50% pour les tables plus grandes. C'est le genre de requête pour laquelle les avantages de MyISAM semblent être maximisés.

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) Sous-sélection

Résultat: InnoDB gagne

Pour cette requête, j'ai créé un ensemble supplémentaire de tables pour le sous-select. Chacun est simplement deux colonnes de BIGINT, une avec un index de clé primaire et une sans index. En raison de la grande taille de la table, je n'ai pas testé le moteur de la mémoire. La commande de création de table SQL était

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

Là encore, 'MyISAM' est remplacé par 'InnoDB' dans la deuxième table.

Dans cette requête, je laisse la taille de la table de sélection à 1000000 et à la place, je modifie la taille des colonnes sous-sélectionnées.

Comparison of sub-selects by different database engines

Ici, l'InnoDB gagne facilement. Une fois que nous avons atteint une table de taille raisonnable, les deux moteurs s'alignent linéairement avec la taille du sous-select. L'index accélère la commande MyISAM mais, de façon intéressante, a peu d'effet sur la vitesse InnoDB. 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) )

Je pense que le message à retenir de tout cela est que si vous êtes vraiment préoccupé par la vitesse, vous devez comparer les requêtes que vous faites plutôt que de faire des suppositions sur quel moteur sera le plus approprié.


51
2018-06-11 09:15



Un peu hors sujet, mais à des fins de documentation et d'exhaustivité, je voudrais ajouter ce qui suit.

En général, l'utilisation d'InnoDB résultera en une application beaucoup moins complexe, probablement aussi sans bug. Étant donné que vous pouvez placer toute l'intégrité référentielle (contraintes de clé étrangère) dans le modèle de données, vous n'avez pas besoin d'autant de code d'application que vous le souhaitez avec MyISAM.

Chaque fois que vous insérez, supprimez ou remplacez un enregistrement, vous devrez vérifier et maintenir les relations. Par exemple. Si vous supprimez un parent, tous les enfants doivent également être supprimés. Par exemple, même dans un simple système de blog, si vous supprimez un enregistrement de blog, vous devrez supprimer les enregistrements de commentaire, les goûts, etc. Dans InnoDB, ceci est fait automatiquement par le moteur de base de données (si vous spécifiez les contraintes dans le modèle ) et ne nécessite aucun code d'application. Dans MyISAM, ceci devra être codé dans l'application, ce qui est très difficile sur les serveurs web. Les serveurs Web sont par nature très concurrents / parallèles et comme ces actions doivent être atomiques et que MyISAM ne prend en charge aucune transaction réelle, l'utilisation de MyISAM for Web-servers est risquée / sujette aux erreurs.

En outre, dans la plupart des cas, InnoDB fonctionnera beaucoup mieux, pour un grand nombre de raisons, l'une étant de pouvoir utiliser le verrouillage au niveau de l'enregistrement plutôt que le verrouillage au niveau de la table. Non seulement dans une situation où les écritures sont plus fréquentes que les lectures, même dans les situations avec des jointures complexes sur de grands ensembles de données. Nous avons remarqué une augmentation des performances de 3 fois en utilisant simplement les tables InnoDB sur les tables MyISAM pour les très grandes jointures (en prenant plusieurs minutes).

Je dirais qu'en général InnoDB (en utilisant un datamodel 3NF complet avec l'intégrité référentielle) devrait être le choix par défaut lors de l'utilisation de MySQL. MyISAM ne doit être utilisé que dans des cas très spécifiques. Il fonctionnera probablement moins, entraînera une application plus importante et plus buggée.

Cela étant dit. Datamodelling est un art rarement trouvé parmi les webdesigners / -programmers. Sans vouloir offenser, mais cela explique MyISAM tellement utilisé.


32
2017-08-26 12:18



InnoDB offre:

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

Dans InnoDB, toutes les données d'une ligne à l'exception de TEXT et BLOB peuvent occuper au maximum 8 000 octets. Aucune indexation en texte intégral n'est disponible pour InnoDB. Dans InnoDB, COUNT (*) s (lorsque WHERE, GROUP BY ou JOIN n'est pas utilisé) s'exécute plus lentement que dans MyISAM car le nombre de lignes n'est pas stocké en interne. InnoDB stocke les données et les index dans un fichier. InnoDB utilise un pool de mémoire tampon pour mettre en cache les données et les index.

MyISAM offre:

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 possède un verrouillage au niveau de la table, mais pas de verrouillage au niveau de la ligne. Aucune transaction Pas de récupération automatique après un crash, mais il offre une fonctionnalité de table de réparation. Aucune contrainte de clé étrangère. Les tables MyISAM sont généralement plus compactes sur disque que les tables InnoDB. Les tables MyISAM peuvent être encore plus réduites en taille en compressant avec myisampack si nécessaire, mais deviennent en lecture seule. MyISAM stocke les index dans un fichier et les données dans un autre. MyISAM utilise des tampons de clé pour la mise en cache des index et laisse la gestion de la mise en cache des données au système d'exploitation.

Globalement, je recommanderais InnoDB pour la plupart des utilisations et MyISAM pour des utilisations spécialisées seulement. InnoDB est maintenant le moteur par défaut dans les nouvelles versions de MySQL.


29
2018-05-28 07:03



Si vous utilisez MyISAM, vous ne le ferez pas tout transactions par heure, sauf si vous considérez chaque déclaration DML comme une transaction (qui, dans tous les cas, ne sera pas durable ou atomique en cas de plantage).

Par conséquent, je pense que vous devez utiliser InnoDB.

300 transactions par seconde sonne comme beaucoup. Si vous avez absolument besoin que ces transactions soient durables en cas de coupure de courant, assurez-vous que votre sous-système d'E / S peut gérer facilement ces nombreuses écritures par seconde. Vous aurez besoin d'au moins un contrôleur RAID avec un cache sauvegardé par batterie.

Si vous pouvez prendre un petit coup de durabilité, vous pouvez utiliser InnoDB avec innodb_flush_log_at_trx_commit mis à 0 ou 2 (voir docs pour plus de détails), vous pouvez améliorer les performances.

Il existe un certain nombre de correctifs qui peuvent augmenter la simultanéité de Google et des autres. Ces derniers peuvent être intéressants si vous ne pouvez toujours pas obtenir suffisamment de performances sans eux.


24
2017-09-16 21:34



Notez s'il vous plaît que mon éducation formelle et mon expérience sont avec Oracle, alors que mon travail avec MySQL a été entièrement personnel et sur mon propre temps, donc si je dis des choses qui sont vraies pour Oracle mais ne sont pas vraies pour MySQL, je m'excuse. Alors que les deux systèmes partagent beaucoup, la théorie relationnelle / algèbre est la même, et les bases de données relationnelles sont toujours des bases de données relationnelles, il y a encore beaucoup de différences !!

J'aime particulièrement (ainsi que le verrouillage au niveau des lignes) qu'InnoDB soit basé sur les transactions, ce qui signifie que vous pouvez mettre à jour / insérer / créer / modifier / supprimer / etc plusieurs fois pour une "opération" de votre application web. Le problème qui se pose est que si seulement certains de ces changements / opérations finissent par être commis, mais d'autres pas, vous aurez la plupart du temps (en fonction de la conception spécifique de la base de données) se retrouver avec une base de données avec des données / structure conflictuelles.

Remarque: Avec Oracle, les instructions create / alter / drop sont appelées "DDL" (Data Definition) et déclenchent implicitement une validation. Les instructions d'insertion / mise à jour / suppression, appelées "DML" (Data Manipulation), sont ne pas commis automatiquement, mais seulement quand un DDL, commit, ou exit / quitter est exécuté (ou si vous définissez votre session à "auto-commit", ou si votre client commet automatiquement). Il est impératif d'être conscient de cela lorsque vous travaillez avec Oracle, mais je ne suis pas sûr de savoir comment MySQL gère les deux types d'instructions. Pour cette raison, je tiens à préciser que je ne suis pas sûr de cela quand il s'agit de MySQL; seulement avec Oracle.

Un exemple de quand les moteurs basés sur les transactions excellent:

Disons que je ou vous êtes sur une page Web pour vous inscrire à un événement gratuit, et l'un des principaux objectifs du système est de permettre seulement jusqu'à 100 personnes de s'inscrire, puisque c'est la limite de l'allocation des places pour l'événement. Une fois que 100 inscriptions sont atteintes, le système désactive d'autres inscriptions, au moins jusqu'à ce que d'autres annulent.

Dans ce cas, il peut y avoir une table pour les invités (nom, téléphone, email, etc.), et une deuxième table qui suit le nombre d'invités qui se sont inscrits. Nous avons donc deux opérations pour une "transaction". Supposons maintenant qu'après l'ajout des informations d'invité à la table GUESTS, il y a une perte de connexion ou une erreur ayant le même impact. La table GUESTS a été mise à jour (insérée dans), mais la connexion a été perdue avant que les "sièges disponibles" puissent être mis à jour.

Nous avons maintenant ajouté un invité à la table des invités, mais le nombre de places disponibles est maintenant incorrect (par exemple, la valeur est 85 alors qu'il est en réalité 84).

Bien sûr Il existe plusieurs façons de gérer cela, comme le suivi des places disponibles avec "100 moins nombre de lignes dans la table des invités", ou un code qui vérifie que l'information est cohérente, etc .... Mais avec un moteur de base de données basé sur les transactions comme InnoDB, soit TOUT des opérations sont engagées, ou AUCUN d'entre eux sont. Cela peut être utile dans de nombreux cas, mais comme je l'ai dit, ce n'est pas la seule façon d'être en sécurité, non (un bon moyen, cependant, géré par la base de données, pas le programmeur / scénariste).

Tout ce qui est «basé sur les transactions» signifie essentiellement dans ce contexte, à moins que je ne manque quelque chose - que la transaction soit réussie comme elle le devrait, ou rien est changé, car ne faire que des changements partiels pourrait rendre un mineur à un désordre SEVERE de la base de données, peut-être même le corrompre ...

Mais je le répète encore une fois, ce n'est pas la seule façon d'éviter de faire des dégâts. Mais c'est l'une des méthodes que le moteur gère lui-même, vous laissant au code / script avec seulement besoin de s'inquiéter de "la transaction a été réussie ou non, et que dois-je faire (comme réessayer)", au lieu de écrire du code pour le vérifier "manuellement" depuis l'extérieur de la base de données, et faire beaucoup plus de travail pour de tels événements.

Enfin, une note sur le verrouillage de table vs le verrouillage de ligne:

AVERTISSEMENT:  Je peux me tromper dans tout ce qui suit en ce qui concerne MySQL, et les situations hypothétiques / exemples sont des choses à examiner, mais je peux me tromper sur ce que exactement est possible de provoquer une corruption avec MySQL. Les exemples sont cependant très réels en programmation générale, même si MySQL dispose de plus de mécanismes pour éviter de telles choses ...

Quoi qu'il en soit, je suis assez confiant en accord avec ceux qui ont fait valoir que le nombre de connexions est autorisé à la fois Est-ce que ne pas travailler autour d'une table verrouillée. En fait, plusieurs connexions sont tout le point de verrouillage d'une table !!  Ainsi, les autres processus / utilisateurs / applications ne sont pas en mesure de corrompre la base de données en apportant des modifications en même temps.

Comment deux ou plusieurs connexions travaillant sur la même ligne pourraient-elles faire un JOUR VRAIMENT MAUVAIS pour vous? Supposons qu'il existe deux processus qui veulent / doivent mettre à jour la même valeur dans la même ligne, disons parce que la ligne est un enregistrement d'un tour en bus, et que chacun des deux processus veut simultanément mettre à jour les "cavaliers" ou "available_seats" champ comme "la valeur actuelle plus 1."

Faisons cela hypothétiquement, étape par étape:

  1. Processus on lit la valeur actuelle, disons qu'elle est vide, donc '0' jusqu'à présent.
  2. Le processus deux lit également la valeur actuelle, qui est toujours 0.
  3. Traiter un écrit (courant + 1) qui est 1.
  4. Traiter deux devrait écrire 2, mais comme il a lu la valeur actuelle avant processus on écrit la nouvelle valeur, il écrit aussi 1 à la table.

je suis pas sure que deux connexions pourraient s'entremêler comme ça, les deux lisant avant que le premier n'écrive ... Mais sinon, alors je verrais toujours un problème avec:

  1. Processus lit la valeur actuelle, qui est 0.
  2. Traiter un écrit (courant + 1), ce qui est 1.
  3. Le processus deux lit la valeur actuelle maintenant. Mais alors que le processus DID a écrit (mise à jour), il n'a pas validé les données, donc seul ce même processus peut lire la nouvelle valeur qu'il a mise à jour, alors que tous les autres voient l'ancienne valeur jusqu'à ce qu'il y ait commit.

En outre, au moins avec les bases de données Oracle, il existe des niveaux d'isolement, que je ne perdrai pas de temps à essayer de paraphraser. Voici un bon article sur ce sujet, et chaque niveau d'isolement a ses avantages et ses inconvénients, ce qui irait de pair avec l'importance des moteurs basés sur les transactions dans une base de données ...

Enfin, il est possible que des mesures de protection différentes soient en place dans MyISAM, au lieu des clés étrangères et de l'interaction basée sur les transactions. Eh bien, pour un, il y a le fait que toute une table est verrouillée, ce qui rend moins probable que les transactions / FK sont nécessaire.

Et hélas, si vous êtes conscient de ces problèmes de concurrence, oui vous pouvez jouer moins sûr et juste écrire vos applications, configurer vos systèmes afin que ces erreurs ne sont pas possibles (votre code est alors responsable, plutôt que la base de données elle-même). Cependant, à mon avis, je dirais qu'il est toujours préférable d'utiliser autant de sauvegardes que possible, en programmant de façon défensive, et en étant toujours conscient que l'erreur humaine est impossible à éviter complètement. Cela arrive à tout le monde, et tous ceux qui disent qu'ils sont immunisés doivent mentir, ou n'ont pas fait plus qu'écrire une application / script "Hello World". ;-)

J'espère que QUELQUES d'entre elles sont utiles à quelqu'un, et même plus, j'espère que je ne suis pas seulement devenu coupable d'hypothèses et que je suis un humain par erreur !! Toutes mes excuses si c'est le cas, mais il est bon de réfléchir aux exemples, de faire des recherches sur le risque, etc., même s'ils ne sont pas potentiels dans ce contexte précis.

N'hésitez pas à me corriger, éditez cette "réponse", votez même le bas. Juste s'il vous plaît essayez d'améliorer, plutôt que de corriger une mauvaise hypothèse de la mienne avec une autre. ;-)

C'est ma première réponse, alors s'il vous plaît pardonnez la longueur en raison de tous les avertissements, etc ... Je ne veux pas avoir l'air arrogant quand je ne suis pas absolument certain!


12
2018-04-21 01:54