MySQL optimise la vitesse INSERT ralentie à cause des index

MySQL Docs dire :

La taille de la table ralentit l'insertion d'index par log N, en supposant que B-arbre d'index.

Est-ce à dire que pour l'insertion de chaque nouvelle ligne, la vitesse d'insertion sera ralentie par un facteur log N où N, je suppose est le nombre de lignes? même si j'insère toutes les lignes en une seule requête? c'est à dire :

INSERT INTO mytable VALUES (1,1,1), (2,2,2),  (3,3,3), .... ,(n,n,n)

Où n est ~de 70 000

J'ai actuellement ~1,47 million de lignes dans une table avec la structure suivante :

CREATE TABLE mytable (
   `id` INT,
   `value` MEDIUMINT(5),
   `date` DATE,
   PRIMARY_KEY(`id`,`date`)
) ENGINE = MyISAM

Lorsque je l'insère dans le ci-dessus mentionné de la mode dans une transaction, la durée de validation de prise est d'environ 275 secondes. Comment puis-je optimiser ce, depuis que de nouvelles données seront ajoutées tous les jours et l'heure d'insertion vais juste continuer à ralentir.

Aussi, est-il quelque chose en dehors de tout les requêtes qui pourraient aider? peut-être que certains paramètres de configuration?

Possible De La Méthode 1 - Retrait D'Indices

J'ai lu que la suppression des indices juste avant d'insertion peut aider à insérer la vitesse. Et après les inserts, j'ai ajouter de l'indice de nouveau. Mais ici, le seul indice est la clé primaire, et en le déposant n'aidera pas beaucoup à mon avis. Aussi, tandis que la clé primaire est chuté , toutes les requêtes de sélection sera rédhibitoire lent.

je ne connais pas d'autres méthodes possibles.

Edit : Voici quelques tests sur l'insertion ~de 60 000 lignes dans la table avec ~1.47 mil lignes:

À l'aide de la plaine de la requête décrite ci-dessus : 146 secondes

De l'utilisation de MySQL LOAD DATA infile : 145 secondes

De l'utilisation de MySQL LOAD DATA infile et le partage des fichiers csv comme suggéré par David Jashi dans sa réponse: 136 secondes pour 60 fichiers avec 1000 lignes chacune, 136 secondes pour 6 fichiers avec 10 000 lignes de chaque

La suppression et re-ajout de la clé primaire : retrait de la clé a eu 11 secondes, 0,8 secondes pour l'insertion de données, MAIS 153 secondes pour ré-ajout de la clé primaire, totalement prenant ~165 secondes

source d'informationauteur Peeyush Kushwaha

  1. 18

    Si vous voulez rapide des inserts, la première chose que vous avez besoin est le bon matériel. Qui suppose une quantité suffisante de RAM, un SSD au lieu des disques mécaniques et plutôt puissant PROCESSEUR.

    Puisque vous utiliser MyISAM, ce que vous voulez est pour l'optimiser depuis de configuration par défaut est conçu pour ralentir et de vieilles machines.

    Voici une excellente lecture sur la configuration de MyISAM

    Après cela, vous devez savoir une chose - et c'est comment les bases de données de faire leur travail à l'interne, combien de disques durs et de travail. Je vais simplifier le mécanisme dans la description suivante:

    Une transaction est MySQL en attente pour le disque dur, pour confirmer ce qu'il a écrit les données. C'est pourquoi les opérations sont lentes sur les disques mécaniques, ils peuvent faire 200-400 d'entrée-sortie par seconde. Traduit, cela signifie que vous pouvez obtenir 200ish insertion de requêtes par seconde à l'aide de MyISAM sur une transmission mécanique. Naturellement, c'est l'explication simplifiéejuste pour décrire ce qui se passe, ce n'est pas l'ensemble du mécanisme derrière transaction.

    Depuis une requête, en particulier celui correspondant à la taille de votre table, est relativement faible en termes d'octets, vous êtes effectivement perdre du précieux IOPS sur une seule requête.

    Si vous enchaînez plusieurs requêtes (100 ou 200 ou plus, il n'y a pas de nombre exact, vous devez le tester) en une seule transaction et puis engagez - le, vous serez instantanément obtenir plus d'écritures par seconde.

    Percona les gars sont la réalisation de 15k insère un deuxième sur un matériel relativement pas cher. Même 5k insère un deuxième n'est pas mal. La table tels que le vôtre est petit, j'ai fait des tests sur une table similaire (3 colonnes plus) et j'ai réussi à obtenir 1 milliard d'enregistrements sans problèmes notables, à l'aide de 16 go de ram de la machine avec un 240 GO SSD (1 lecteur, pas de RAID, utilisé à des fins de test).

    TL;DR: - suivez le lien ci-dessus, configurez votre serveur, obtenir un SSD, l'envelopper de plusieurs insertions dans 1 opérations et des bénéfices. Et ne pas tourner à l'indexation puis, il ne s'applique pas toujours, car à un certain moment vous permettra de passer de traitement des e /s et le temps de les construire.

  2. 4

    Chute de l'indice va vous aider de toute façon. Pensez aussi à utiliser des LOAD DATA. Vous pouvez trouver certains de comparaison et de repères ici

    Aussi, lors de la construction de la CLÉ PRIMAIRE, utiliser des champs, qui viennent en premier dans votre tableau, de manière séquentielle, c'est à dire changer de place de deuxième et troisième champs dans la structure.

  3. 3

    Si vous faites une insertion en bloc d'un million de lignes, puis la suppression de l'index, faire de l'insertion et de la reconstruction de l'index sera probablement plus rapide. Toutefois, si votre problème est que seule ligne inserts de prendre trop de temps, alors vous avez d'autres problèmes (comme pas assez de mémoire) et l'abandon de l'indice ne va pas aider beaucoup.

  4. 1

    De construction/reconstruction de l'index est ce que vous êtes en essayant d'accélérer. Si vous devez disposer de ce tableau/la structure de la clé, le plus rapide du matériel et/ou de modifier la configuration du serveur pour accélérer la construction de l'index est probablement la réponse - assurez-vous que votre serveur et les paramètres sont tels qu'il peut être accompli dans la mémoire.

    Sinon, pensez à faire de compromis avec la structure qui permettrait d'améliorer l'insertion des vitesses. Sinon, penser à des façons que vous pouvez heureux de vivre avec un 3 minutes à insérer.