Comment puis-je ajouter des indices de tables MySQL?

J'ai une très grande table MySQL avec environ 150 000 lignes de données. Actuellement, lorsque j'essaie de l'exécuter

SELECT * FROM table WHERE id = '1';

le code fonctionne très bien tant que le champ ID est l'index primaire.
Cependant, récemment, pour un développement dans le projet, je recherche la base de données par un autre champ. Par exemple

SELECT * FROM table WHERE product_id = '1';

Ce champ n'a pas été préalablement indexés, cependant, je l'ai ajouté comme un indice, mais lorsque j'essaie d'exécuter la requête ci-dessus, le résultat est très lent. Un EXPLIQUER requête révèle qu'il n'existe pas d'indice pour le product_id champ lorsque je l'ai déjà ajouté un et comme un résultat de la requête prend n'importe où de 20 à 30 minutes pour retourner une seule ligne.

Mon plein EXPLIQUER les résultats sont les suivants:

| id | select_type | table | type | possible_keys        | key  | key_len | ref  | rows      | Extra       |
+----+-------------+-------+------+----------------------+------+---------+------+------+------------------+
|  1 | SIMPLE      | table | ALL  | NULL                 | NULL | NULL    | NULL |    157211 | Using where |
+----+-------------+-------+------+----------------------+------+---------+------+------+------------------+

Il pourrait être utile de noter que je viens de prendre un coup d'oeil et le champ ID est stocké en tant que INT alors que le PRODUCT_ID champ est stocké comme VARCHAR. Cela pourrait-il être la source du problème?

  • Pouvez-vous publier EXPLAIN résultats? Êtes-vous certain, c'est qu'il y a n index? Ou est l'indice de là, mais MySQL est le choix de ne pas l'utiliser?
  • Une grande table serait de 150 000 000 dossiers. Une très grande table a 15,000,000,000 enregistrements. Une table de taille moyenne est de 150 000. Pour référence future.
  • Sachez que " OU " peut faire MySql de ne pas utiliser les index. J'ai eu une requête avec 3 OU de l'. Chaque mached un index, et a couru dans les 15ms, Tous ensemble, pris entre 25sec et le délai d'expiration. J'ai donc fait 3 requêtes et de l'UNION ed ensemble, il a également pris 15ms sur 500.000 lignes.
InformationsquelleAutor Michael | 2010-06-09
  1. 562
    ALTER TABLE `table` ADD INDEX `product_id` (`product_id`)

    Ne jamais comparer integer à strings dans MySQL. Si id est int, supprimer les guillemets.

    • Je l'ai déjà ajouté à l'index de l'aide de l'exacte SQL, mais il semble qu'il n'a pas été "appliquée" aux données et de les EXPLIQUER de la requête montre qu'il n'existe pas d'indice pour le domaine.
    • Soin de vérifier les index avec un SHOW CREATE TABLE, ou avez-vous déjà fait cela?
    • Utilisation SHOW INDEXES FROM YOURTABLE dev.mysql.com/doc/refman/5.0/en/show-index.html pour vérifier si les indices ont été ajoutés
    • Aujourd'hui, j'ai eu le problème exact @Michael décrit, et la solution était de "ne Jamais comparer entier à cordes dans mysql." Je vous remercie.
    • J'ai ajouté mon index et utilisé votre commande SHOW INDEX à PARTIR de YOURTABLE. Il montre, mais je ne pense pas que c'est utilisé alors que j'ai une requête à ma table. Dois-je changer la requête select lors de l'utilisation d'un indice ?
    • juste poser une autre question (vous pouvez me ping avec ma poignée dans ses commentaires) avec: 1. la requête elle-même 2. son expliquez-3. le show create table 4. quelques statistiques sur la quantité de données dans la table, la façon dont le cardinal est la sélection
    • J'ai résolu mon problème merci pour vos commentaires si. Le problème était calme cool si : j'ai généré 100k valeurs de java à ma base de données et mon index était sur une colonne définie comme DATETIME. Le truc, c'est que toutes les dates où le même! Quand je mets le hasard des dates tout fonctionnait bien.
    • comparer entier à cordes en mysql Pourquoi pas? N'est-ce pas automatiquement convertir des chaînes en nombres dans ce cas?
    • Personnellement, j'ai vu des cas où il coulé le mauvais opérande qui a causé l'analyse complète. Pour ne pas dire qu'il n'a pas de sens.
    • Eh bien, type de règles de conversion dit qui ne devrait pas arriver. Mais je ne voudrais pas en témoigner. Comme pour "ne fait aucun sens", bien sûr, si vous écrivez l'instruction de vous-même. Mais de sens que si elle est générée. Pourquoi s'embêter à s'adapter au type d'une variable passée, si on pouvait juste les convertir en string et laissez mysql le manipuler...
    • eh bien, je l'ai fait observer que vraiment il y a longtemps, vous ne savez pas quelle version a été que. Même si vous produisez automatiquement, il est logique de mettre en œuvre un générateur de requêtes qui respecte les types.
    • la raison principale pourquoi un index n'est pas utilisé, est de cardinalité et de MySQL penser un full table scan serait plus rapide que l'utilisation de l'indice, ou un autre indice pourrait être mieux adapté. Assurez-vous que votre cardinalités sont régulièrement mis à jour (ANALYZE TABLE en cas de besoin), ce qui peut augmenter leurs pages web pour obtenir plus fiable (au détriment de plus le calcul de celles-ci). Si tout cela échoue, et vous êtes vraiment bien sûr, et intimement connaissez vos données, le USE / FORCE INDEX sont à votre disposition, mais cela signifie que vous serez obligé de vérifier si cela fonctionne mieux tous les X quantité de temps à partir de là.
    • J'ai répondu ci-dessus vous n'avez probablement pas le voir. En fait la raison pour laquelle il n'était pas utilisé (ou il pourrait avoir été utilisé idk), c'est que tout mon champ de date a la même valeur. Je l'ai fait entrer en programmant 100k valeurs et je ne pense pas à ce sujet.
    • ouais, juste une réponse quand j'ai vu quelqu'un a dit sur un commentaire de plus de 5 ans 🙂 Mais: le débogage en général: si vous avez coché la cardinalité, vous auriez vu votre index est en effet estimée à seulement quelques valeurs différentes, ce qui en fait effectivement désagréable à utiliser comme index, ce qui est logique compte tenu de la façon dont vous l'avez rempli. Donc, la réponse a été plus comment déboguer en général, plutôt que de ce cas 😉
    • Ouais j'ai l'habitude de vérifier si l'utilisateur a une certaine réputation de point avant de poster sur un vieux commentaire pour voir si elles sont susceptibles de répondre. De toute façon je vais devoir vérifier le caractère charnel chose que je n'ai aucune idée de ce qui est maintenant. Merci pour l'astuce.

    InformationsquelleAutor zerkms
  2. 142
    ALTER TABLE TABLE_NAME ADD INDEX (COLUMN_NAME);
    • Dans MySQL, si vous utilisez ALTER TABLE tbl ADD INDEX (col) au lieu de ALTER TABLE tbl ADD INDEX col (col), puis à l'aide ALTER TABLE tbl ADD INDEX (col) plus d'une fois continuez d'ajouter des indices nommé col_2,col_3,... à chaque fois. Tandis que l'utilisation de ALTER TABLE tbl ADD INDEX col (col) 2ème fois, donnera ERROR 1061 (42000): Duplicate key name 'col'.
    InformationsquelleAutor pabloferraz
  3. 72

    Vous pouvez utiliser cette syntaxe pour ajouter un index et de contrôler le type d'index (dièse ou un ARBRE).

    create index your_index_name on your_table_name(your_column_name) using HASH;
or
create index your_index_name on your_table_name(your_column_name) using BTREE;

    Vous pouvez en apprendre davantage sur les différences entre l'ARBRE et le HACHAGE des indices ici:
    http://dev.mysql.com/doc/refman/5.5/en/index-btree-hash.html

    • Hachage converti à arbre lorsque je vois à l'aide de montrer index.
    • Quelle sera la valeur par défaut de Hachage et BTree, si je ne précisez pas?
    • BTree est par défaut
    • parce que InnoDB et MyIsam ne prennent pas en charge de HACHAGE, autant que je sache, seulement de la Mémoire et NDB moteurs de stockage de soutien il
    InformationsquelleAutor Hieu Vo
  4. 53

    Il est intéressant de noter que plusieurs indices permettent d'améliorer considérablement les performances de votre requête. Ainsi, dans l'exemple ci-dessus, nous supposons ProductID est le seul champ de recherche, mais ont été à la requête de dire ProductID = 1 ET Catégorie = 7 alors un multiple de la colonne de l'index de l'aide. Ceci est réalisé avec les éléments suivants:

    ALTER TABLE `table` ADD INDEX `index_name` (`col1`,`col2`)

    En outre, l'indice devrait correspondre à l'ordre des champs de la requête. Dans mon exemple, l'indice devrait être (ProductID,de la Catégorie) et non pas l'inverse.

    • Nice, nommer explicitement l'index permet de renversement.
    • Pouvez-vous en citer la source de the index should match the order of the query fields ?
    • Malheureusement, il a été un collègue ...
    InformationsquelleAutor Antony
  5. 45

    Index de deux types: lorsque vous définissez une clé primaire, MySQL va le prendre comme indice par défaut.

    Explication

    De clé primaire que l'indice de

    Considérer que vous avez un tbl_student table et que vous souhaitez student_id comme clé primaire:

    ALTER TABLE `tbl_student` ADD PRIMARY KEY (`student_id`)

    Déclaration ci-dessus ajoute une clé primaire, ce qui signifie que les valeurs indexées doit être unique et ne peut pas être NULL.

    Spécifier le nom de l'index

    ALTER TABLE `tbl_student` ADD INDEX student_index (`student_id`)

    Déclaration ci-dessus va créer un index avec student_index nom.

    Create unique index

    ALTER TABLE `tbl_student` ADD UNIQUE student_unique_index (`student_id`)

    Ici, student_unique_index est le nom de l'index attribué à student_id et crée un index pour quelles valeurs doit être unique (ici, nul ne peut être acceptée).

    Fulltext option

    ALTER TABLE `tbl_student` ADD FULLTEXT student_fulltext_index (`student_id`)

    Déclaration ci-dessus va créer l'index de texte intégral au nom de student_fulltext_index, pour laquelle vous avez besoin de Mysql MyISAM Moteur.

    Comment faire pour supprimer les index ?

    DROP INDEX `student_index` ON `tbl_student`

    Comment vérifier les index disponibles?

    SHOW INDEX FROM `tbl_student`
    InformationsquelleAutor Jazzzzzz
  6. 16

    Vous dites que vous avez un indice, l'expliquer, dit le contraire. Toutefois, si vous avez vraiment, c'est la façon de continuer:

    Si vous avez un index sur la colonne, et MySQL décide de ne pas l'utiliser, il peut, par car:

    1. Il y a un autre indice dans la requête MySQL juge la plus appropriée à utiliser, et il peut utiliser qu'un seul. La solution est généralement un index sur plusieurs colonnes si leur méthode normale de récupération est, en valeur, de plus d'une colonne.
    2. MySQL décide il y a de nombreuses lignes correspondantes, et pense un tablescan est probablement plus rapide. Si ce n'est pas le cas, parfois, un ANALYZE TABLE aide.
    3. Dans des requêtes plus complexes, il décide de ne pas l'utiliser basée sur les extrêmement intelligente de la pensée vaudou dans la requête-plan que pour une raison tout simplement pas adaptée à vos besoins actuels.

    Dans le cas de (2) ou (3), vous pourriez coaxial MySQL en utilisant l'index par indicateur d'index sytax, mais si vous le faites, assurez-vous de faire quelques tests pour déterminer si elle améliore réellement les performances de l'utilisation de l'index que vous allusion elle.

    InformationsquelleAutor Wrikken