Comment faire pour désactiver l'index en innodb

Je suis en train d'accélérer l'instruction bulk insert dans une table InnoDB par la désactivation temporaire de ses index:

ALTER TABLE mytable DISABLE KEYS;

Mais il donne une avertissement:

+-------+------+-------------------------------------------------------------+
| Level | Code | Message                                                     |
+-------+------+-------------------------------------------------------------+
| Note  | 1031 | Table storage engine for 'mytable' doesn't have this option |
+-------+------+-------------------------------------------------------------+
1 row in set (0.00 sec)

Comment peut-on désactiver l'index?

Quelles sont les alternatives pour éviter d'utiliser l'index pour faire des insertions?

Comment pouvons-nous accélérer le processus?

  • Quelle quantité de données que vous avez (en termes de lignes et de gigaoctets)? Et comment voulez-vous charger?
  • environ 5 millions de lignes et d'environ 1,2 GO. Merci.
  • Connexes: stackoverflow.com/q/654594/632951
InformationsquelleAutor fanchyna | 2012-03-01
  1. 30

    Avez-vous essayé le suivant?

        SET autocommit=0; 
    SET unique_checks=0; 
    SET foreign_key_checks=0;

    À partir de la base de Références https://dev.mysql.com/doc/refman/5.5/en/optimizing-innodb-bulk-data-loading.html

    Voir La Section "En Vrac Chargement Des Données Des Conseils"

    • J'ai essayé de le faire. Mais la question est que la façon de savoir si l'indice est en mesure ou désactivé?
    • Je sais que le "EXPLIQUER" déclaration peut aider à comprendre le "SELECT" déclaration, mais ce que sur le "LOAD DATA LOCAL INFILE" déclaration? Comment puis-je savoir si l'indice est en mesure lors de l'exécution de cette commande? Merci.
    • Activer/désactiver les touches est un paramètre global, non pas par séance. Utiliser les show keys in my_table pour voir si les indices sont activés ou désactivés.
    InformationsquelleAutor lurkerbelow
  2. 28

    Il y a une très bonne raison pourquoi vous ne pouvez pas exécuter DISABLE KEYS sur une table InnoDB; InnoDB n'est pas conçu pour l'utiliser, et MyISAM est.

    En fait, voici ce qui se passe quand vous rechargez un mysqldump:

    Vous verrez un CREATE TABLE pour un MyISAM tableau suivant par un verrou en écriture.

    Avant que toutes les insertions sont exécutés, un appel à ALTER TABLE ... DISABLE KEYS est fait.

    Ce que ce n'est éteindre le second index dans la table MyISAM.

    Puis, insertions sont faites. Tout cela est fait, la CLÉ PRIMAIRE et de tous UNIQUE de CLÉS dans la table MyISAM sont désactivés. Avant la UNLOCK TABLEs, un appel ALTER TABLE ... ENABLE KEYS est fait dans le but de reconstruire tous les index de façon linéaire.

    À mon humble avis, cette opération n'a pas été codé dans le Moteur de Stockage InnoDB parce que de toutes les clés dans un index non unique venu avec la clé primaire de l'entrée de gen_clust_index (aka Index Cluster). Ce serait une opération très coûteuse car la création d'un index non unique nécessiterait O(n log n) temps de fonctionnement pour récupérer chaque clé unique pour l'attacher à un non-clé unique.

    À la lumière de cela, l'affichage d'un avertissement au sujet d'essayer de DISABLE KEYS/ENABLE KEYS sur une table InnoDB est beaucoup plus facile que le codage des exceptions à la commande mysqldump pour toutes les affaires impliquant des moteurs de stockage MyISAM.

    • Merci pour votre explication, mais la question demeure: comment puis-je éviter d'utiliser des index lors du téléchargement de données afin de réduire le montant de l'importation de temps.Merci.
    • Ce que @RolandoMySQLDBA signifie que vous n'aurez pas de réduire sensiblement le temps de chargement de la désactivation des indices sur une base de données INNODB. Cluster indices sont basées sur les arbres (= log chronométré) structures de données dans leur propre droit; vos choix sont la construction de la totalité de l'index à nlogn, ou la course n logn inserts. Vous pourriez économiser un peu par-insérer les coûts, mais dans la pratique, je parie que c'est très peu; il y a séquentielle insérer des optimisations déjà en place.
    • Je fais régulièrement de construire de très grandes innodb tables avec beaucoup d'index (des centaines de giga-octets / 10+ différents indices). Il y a une énorme réduction du temps de création, quand je INSERT dans des tableaux sans index. De toute évidence, "énorme" dépend fortement de la taille définitive de votre table, le nombre et le type d'index, les types d'éléments de données, etc. J'ai l'habitude de créer la table avec rien, mais la clé primaire (pas nécessaire si vous êtes juste de chargement de données qui a déjà un id de clé primaire), puis créer les index à la fin. Vous pouvez ne pas remarquer une différence à 5MM de lignes et de 1,5 GO, mais comme vos tables grandissent, vous.
    InformationsquelleAutor RolandoMySQLDBA
  3. 8

    à réduire les coûts pour le re-calcul de l'index, vous devez insérer les données à l'aide de DATA INFILE ou de l'utilisation de Mysql Multi Insertions de lignes, comme

    INSERT INTO nom_table (a,b,c) VALEURS(1,2,3),(4,5,6),(7,8,9);

    -> si l'insertion de plusieurs lignes avec une seule instruction.

    Combien de lignes on peut insérer avec un énoncé dépend de la max_allowed_packet mysql réglage.

    • Êtes-vous sûr de cela? Alors, êtes-vous nous dire pour insérer un million de lignes à l'aide d'un unique insert?
    • Je ne vous dis à insérer "toutes les données" avec une seule instruction, mais d'insérer plusieurs lignes à la fois. Dans mes applications, j'ai utilisé de 500 à 1000 lignes par insérer sans problèmes. Il dépend principalement du nombre de lignes et de la quantité de données que vous introduisez par ligne.
    • Alors quel est le bon numéro? Décidez-vous que par essais et erreurs?
    • Son juste comme tout autre paramètre. plus le nombre de lignes que vous insérez avec une instruction plus vite il obtient. avoir trop de lignes feront l'exécution de php, un message d'erreur concernant une trop grande déclaration. Vous avez besoin de violon et de surveiller ce qui fonctionne le mieux pour votre application.
    • Vous dites que nous devrions définir la limite de l'illimité et de l'exécuter ensuite le tout dans un commande?
    • voir mon commentaire précédent. Je expecitly n'ai pas dit de faire des choses en une seule instruction. <le sarcasme>si votre serveur dispose d'une quantité illimitée de mémoire vous pouvez utiliser un nombre illimité de énorme/long de la/des déclarations gros</sarcasme>
    • C'est pas illimitée
    • c'est ce que je dis. Je vais mettre à jour la réponse de toute façon, comme le lien que vous avez posté est utile.
    • En changeant de personne INSERTs à plusieurs lignes avec 1 déclaration réduit le temps de téléchargement de 10 minutes à 10 secondes.

    InformationsquelleAutor staabm
  4. 4

    Un peu en retard mais... quoi que... oublier ici toutes les réponses, ne pas désactiver l'index, il n'y a aucun moyen, il suffit de déposer leur ALTER TABLE nom_table DROP INDEX whatever, bulk insert les données, puis de l'instruction ALTER TABLE nom_table ADD INDEX whatever (whatever); la quantité de temps de recréer l'index est 1% de la masse de l'insérer avec l'index sur elle, comme 400000 rangs, a pris 10 minutes, avec des index et comme les 2 secondes sans eux..., acclamations...

    • bon point! qui a jamais déclassé ce post. je voudrais savoir pourquoi.
    • Quel est le temps nécessaire pour recréer l'index après l'insertion? Je suppose que les indices sont là pour une raison, donc, de les déposer, en insérant les enregistrements, puis la création de l'arrière de la même index, tout prend du temps. En prenant en considération le temps nécessaire pour déposer et créer l'index, est-il encore plus vite?
    InformationsquelleAutor KciNicK