Je passe du temps à optimiser notre base de données actuelle.
Je m'intéresse plus particulièrement aux indices.
Il y a quelques questions :
Que vont accélérer les index ?
Extraction de données -- instructions SELECT.
Quel sera le ralentissement des indices ?
Manipulation des données -- instructions INSERT, UPDATE, DELETE.
Quand est-ce une bonne idée d'ajouter un index ?
Si vous souhaitez obtenir de meilleures performances en matière d'extraction de données.
Quand est-ce une mauvaise idée d'ajouter un index ?
Sur les tables qui seront soumises à de lourdes manipulations de données - insertion, mise à jour...
Avantages et inconvénients des index multiples par rapport aux index multi-colonnes ?
Les requêtes doivent tenir compte de l'ordre des colonnes lorsqu'elles traitent un index couvrant (un index sur plus d'une colonne), de gauche à droite dans la définition des colonnes d'index. L'ordre des colonnes dans la requête n'a pas d'importance, seulement celui des colonnes 1, 2 et 3 - une requête doit avoir une référence à la colonne 1 avant que l'index puisse être utilisé. S'il n'y a qu'une référence à la colonne 2 ou 3, l'indice de recouvrement pour 1/2/3 ne pourra pas être utilisé.
Dans MySQL, un seul index peut être utilisé par SELECT/déclaration dans la requête (les sous-requêtes/etc sont considérées comme une déclaration séparée). Et il y a une limite à la quantité d'espace par table que MySQL autorise. De plus, l'exécution d'une fonction sur une colonne indexée rend l'index inutile - IE :
WHERE DATE(datetime_column) = ...
Je ne suis pas d'accord avec certaines des réponses à cette question.
Y a-t-il une chose telle que trop d'index ?
Bien sûr. Ne créez pas d'index qui ne sont utilisés par aucune de vos requêtes. Ne créez pas d'index redondants. Utilisez des outils comme pt-duplicate-key-checker y pt-index-usage pour vous aider à découvrir les index dont vous n'avez pas besoin.
Que vont accélérer les index ?
D'autres réponses ont indiqué que les opérations INSERT/UPDATE/DELETE sont plus lentes si le nombre d'index est élevé. C'est vrai, mais considérez que de nombreuses utilisations de UPDATE et DELETE ont également des clauses WHERE et que dans MySQL, UPDATE et DELETE supportent également les JOINs. Les index peuvent être plus avantageux pour ces requêtes que la surcharge liée à la mise à jour des index.
De plus, InnoDB verrouille les lignes affectées par un UPDATE ou un DELETE. C'est ce qu'on appelle le verrouillage au niveau des lignes, mais il s'agit en fait d'un verrouillage au niveau de l'index. S'il n'y a pas d'index pour restreindre la recherche, InnoDB doit verrouiller beaucoup plus de lignes que la ligne spécifique que vous modifiez. Il peut même verrouiller tous les lignes du tableau. Ces verrous bloquent les modifications apportées par d'autres clients, même si elles ne sont pas logiquement en conflit.
Quand est-ce une bonne idée d'ajouter un index ?
Si vous savez que vous devez exécuter une requête qui bénéficierait d'un index dans l'un des cas ci-dessus.
Quand est-ce une mauvaise idée d'ajouter un index ?
Si l'index est un préfixe gauche d'un autre index existant, ou si l'index n'aide aucune des requêtes que vous devez exécuter.
Avantages et inconvénients des index multiples par rapport aux index multi-colonnes ?
Dans certains cas, MySQL peut effectuer optimisation de l'indexation et de la fusion et d'unir ou de croiser les résultats des recherches d'index indépendantes. Mais il est plus performant de définir un seul index, de sorte que la fusion des index n'est pas nécessaire.
Pour l'un de mes clients consultant, j'ai défini un index multi-colonnes sur une table many-to-many où il n'y avait pas d'index, et j'ai amélioré leur requête de jointure par un facteur de 94 millions !
La conception des bons indices est un processus complexe, basé sur les requêtes que vous devez optimiser . Vous ne devez pas établir de règles générales telles que "tout indexer" ou "ne rien indexer pour éviter de ralentir les mises à jour".
Voir aussi ma présentation Comment concevoir des indices, vraiment .
Y a-t-il une chose telle que trop d'index ?
Les index doivent être déterminés en fonction du problème à résoudre : les tables, les requêtes que votre application va exécuter, etc.
Que vont accélérer les index ?
SELECTs.
Quel sera le ralentissement des indices ?
Les INSERTS seront plus lents, car vous devez mettre à jour l'index.
Quand est-ce une bonne idée d'ajouter un index ?
Lorsque votre application a besoin d'une autre clause WHERE.
Quand est-ce une mauvaise idée d'ajouter un index ?
Lorsque vous n'en avez pas besoin pour effectuer des requêtes ou appliquer des contraintes d'unicité.
Avantages et inconvénients des index multiples par rapport aux index multi-colonnes ?
Je ne comprends pas la question. Si vous avez une contrainte d'unicité qui inclut plusieurs colonnes, il faut absolument la modéliser comme telle.
Est-il une telle chose comme trop d'index?
Oui. Ne pas aller à la recherche de créer des index, de les créer si nécessaire.
Quelles seront les indices de vitesse?
Requêtes sur l'index de la table/vue.
Quelles seront les indices de ralentir?
Une instruction INSERT à l'encontre de l'indexation de la table sera ralentie, car chaque nouvel enregistrement devra être indexé.
Quand est-ce une bonne idée d'ajouter un index?
Lorsqu'une requête n'est pas en cours d'exécution à une vitesse acceptable. Vous pouvez filtrer sur les dossiers qui ne font pas partie du cluster PK, dans ce cas, vous devez ajouter des index basés sur les filtres vous êtes à la recherche sur (si la performance jugera bon de le faire).
Quand est-ce une mauvaise idée d'ajouter un index?
Lorsque vous le faites pour l'amour d'elle - je.e la sur-optimisation.
Pro et Con de plusieurs index vs multi-index de colonne?
Dépend des requêtes que vous voulez améliorer.
Y a-t-il une chose telle que trop d'index ?
Oui, comme toute chose, trop d'index ralentira la manipulation des données.
Quand est-ce une bonne idée d'ajouter un index ?
Une bonne idée d'ajouter un index est lorsque vos requêtes sont trop lentes (c'est-à-dire que vous avez trop de jointures dans vos requêtes). Vous ne devriez utiliser cette optimisation qu'après avoir construit un modèle solide, afin d'ajuster les performances.
Prograide est une communauté de développeurs qui cherche à élargir la connaissance de la programmation au-delà de l'anglais.
Pour cela nous avons les plus grands doutes résolus en français et vous pouvez aussi poser vos propres questions ou résoudre celles des autres.
