Comment puis-je obtenir MySQL à utiliser un INDEX pour afficher la requête?
Je suis en train de travailler sur un projet web avec base de données MySql sur Java EE. Nous avons besoin d'une vue de synthétiser les données à partir de 3 tables avec plus de 3M de lignes dans l'ensemble. Chaque tableau a été créé avec l'index. Mais je n'ai pas trouvé le moyen de profiter des avantages dans les indices dans la condition de l'instruction select de récupération de la vue que nous avons créé avec [groupe par].
J'ai obtenir des suggestions des personnes qui à l'aide de points de vue dans MySql n'est pas une bonne idée. Parce que vous ne pouvez pas créer d'index pour les vues dans mysql comme dans oracle. Mais dans les quelques test que j'ai pris, les indices peuvent être utilisées en vue de l'instruction select. Peut-être que j'ai créé ces vues dans un mauvais sens.
Je vais utiliser un exemple pour décrire mon problème.
Nous avons une table qui enregistre les données pour des scores élevés dans des matchs de la NBA, avec l'index sur la colonne [happend_in]
CREATE TABLE `highscores` (
`tbl_id` int(11) NOT NULL auto_increment,
`happened_in` int(4) default NULL,
`player` int(3) default NULL,
`score` int(3) default NULL,
PRIMARY KEY (`tbl_id`),
KEY `index_happened_in` (`happened_in`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
insérer des données(8 lignes)
INSERT INTO highscores(happened_in, player, score)
VALUES (2006, 24, 61),(2006, 24, 44),(2006, 24, 81),
(1998, 23, 51),(1997, 23, 46),(2006, 3, 55),(2007, 24, 34), (2008, 24, 37);
puis-je créer une vue pour voir le score le plus élevé que Kobe Bryant a obtenu au cours de chaque année
CREATE OR REPLACE VIEW v_kobe_highScores
AS
SELECT player, max(score) AS highest_score, happened_in
FROM highscores
WHERE player = 24
GROUP BY happened_in;
J'ai écrit une instruction conditionnelle pour voir le score le plus élevé que kobe obtenu dans 2006;
select * from v_kobe_highscores where happened_in = 2006;
Quand je l'ai expliquer dans toad for mysql, j'ai découvert que mysql dispose d'analyse toutes les lignes pour former la vue, puis de trouver des données de l'état, sans l'aide de l'index sur [happened_in].
explain select * from v_kobe_highscores where happened_in = 2006;
Le point de vue que nous utilisons dans notre projet s'est construit entre les tables avec des millions de lignes. La numérisation de toutes les lignes de la table dans chaque vue de la récupération des données est inacceptable. S'il vous plaît aider! Merci!
@zerkms Voici le résultat que j'ai testé sur la vie réelle. Je ne vois pas beaucoup de différences entre les deux. Je pense que @spencer7593 a le point de droit. L'optimiseur MySQL ne pas "pousser" que prédicat dans l'avis de requête.
Vous devez vous connecter pour publier un commentaire.
Comment obtenez-vous MySQL à utiliser un index pour un affichage requête? La réponse courte, fournir un indice que MySQL peut utiliser.
Dans ce cas, l'optimum de l'indice est susceptible d'une "couverture" de l'indice:
Il est probable que MySQL va utiliser cet index, et l'EXPLIQUER affichera:
"Using index"
en raison de laWHERE player = 24
(un prédicat d'égalité sur la première colonne de l'index. LeGROUP BY happened_id
(la deuxième colonne de l'index), peut permettre à MySQL pour optimiser ce à l'aide de l'index pour éviter une opération de tri. Y compris lascore
colonne dans l'index permettra à la requête satisfait entièrement à partir de l'index, sans avoir à visiter (recherche) les pages de données référencée par l'index.C'est de la réponse rapide. La plus longue réponse, c'est que MySQL est très rare d'utiliser un indice de colonne principale de
happened_id
pour l'avis de requête.Pourquoi la vue provoque un problème de performances
Une des questions que vous avez avec MySQL point de vue est que MySQL ne pas "pousser" le prédicat de la requête externe vers le bas dans l'avis de requête.
Votre requête externe spécifie
WHERE happened_in = 2006
. L'optimiseur MySQL ne considère pas le prédicat lorsqu'il s'exécute à l'intérieur "de la vue de requête". Cette requête pour le point de vue est exécuté séparément, avant de la requête externe. Le jeu de résultats de l'exécution de cette requête get "matérialisée"; c'est, les résultats sont stockés en tant qu'intermédiaire table MyISAM. (MySQL appelle une "table dérivée", et que le nom qu'ils utilisent un sens, quand vous comprenez les opérations que MysQL fait.)La ligne de fond est que l'indice que vous avez défini sur
happened_in
n'est pas utilisé par MySQL, quand il rusn la requête qui forme la définition de la vue.Après l'intermédiaire "table dérivée" est créé, ALORS que l'extérieur de la requête est exécutée, l'utilisation de la "table dérivée" en tant que contenu (rowsource). C'est à ce moment que l'extérieur de la requête s'exécute que le
happened_in = 2006
prédicat est évalué.Noter que toutes les lignes de la vue de requête sont stockées, ce qui (dans votre cas) est une ligne pour CHAQUE valeur de
happened_in
, pas seulement celle que vous spécifiez un prédicat d'égalité dans la requête externe.La façon à afficher les requêtes sont traitées peuvent être "inattendu" par certains, et c'est une des raisons que l'utilisation de "points de vue" dans MySQL peut entraîner des problèmes de performances, par rapport à la manière d'afficher les requêtes sont traitées par d'autres bases de données relationnelles.
l'Amélioration de la performance de l'avis de requête, avec un index de couverture
Donné votre définition de la vue et de votre requête, sur le meilleur, vous allez l'obtenir serait un "à l'Aide de l'indice de" méthode d'accès pour l'avis de requête. Pour l'obtenir, vous aurez besoin d'un index de couverture, par exemple
Qui est susceptible d'être le plus bénéfique (l'indice de la performance sage) de votre définition de la vue et de votre requête existante. Le
player
colonne est la colonne principale parce que vous avez un prédicat d'égalité sur la base de cette colonne dans la vue de requête. Lehappened_in
colonne est le suivant, parce que vous avez un GROUPE PAR opération sur la colonne, et MySQL va être en mesure d'utiliser cet indice pour optimiser le GROUPE PAR l'opération. Nous avons aussi inclus lesscore
colonne, parce que c'est la seule autre colonne référencée dans votre requête. Que fait l'indice d'une "couverture" de l'index, parce que MySQL peut satisfaire à cette requête directement à partir des pages de l'index, sans avoir besoin de visiter toutes les pages dans la table sous-jacente. Et c'est aussi bien que nous allons sortir de ce plan de requête: "à l'Aide de l'indice de" sans "Using filesort".Comparer les performances autonome requête sans dérivés de la table
Vous pouvez comparer le plan d'exécution de votre requête sur la vue par rapport à l'équivalent autonome requête:
Autonome requête peut également faire usage d'un index de couverture par exemple
mais sans avoir besoin de concrétiser un intermédiaire table MyISAM.
Je ne suis pas sûr que la précédente fournit une réponse directe à la question que vous posiez des questions.
Q: Comment puis-je obtenir MySQL à utiliser un INDEX pour afficher la requête?
A: Définir un INDEX adéquat que la vue requête peut utiliser.
La réponse courte est de fournir une "couvrant l'index" (indice inclut toutes les colonnes référencées dans la vue de requête). Les premières colonnes de l'indice doivent être les colonnes qui sont référencés avec des prédicats d'égalité (dans votre cas, la colonne
player
serait une colonne principale parce que vous avez unplayer = 24
prédicat dans la requête. Aussi, les colonnes référencées dans le GROUP BY doit être à la pointe des colonnes dans l'index, ce qui permet de MySQL pour optimiser laGROUP BY
opération, en faisant usage de l'index plutôt que de recourir à une opération de tri.Le point clé ici est que l'avis de requête est essentiellement autonome de la requête; les résultats de cette requête et stockés dans un lieu intermédiaire "dérivée" de la table (une table MyISAM qui est créé lorsqu'une requête contre la vue est exécuté.
À l'aide de points de vue dans MySQL n'est pas nécessairement une "mauvaise idée", mais je vous recommande de la prudence ceux qui choisissent d'utiliser des vues au sein de MySQL pour être au COURANT de comment MySQL processus de requêtes qui font référence à ces points de vue. Et la façon dont MySQL processus de requêtes d'affichage diffère (de façon significative) de la manière d'afficher les requêtes sont traitées par d'autres bases de données (par exemple, Oracle, SQL Server).
EXPLAIN
?happened_in = 2006
prédicat. J'explique le "où" sur la deuxième ligne comme se référant à laplayer = 24
prédicat de l'avis de requête, pas le prédicat de la requête externe. Notez que la première ligne dans le plan d'expliquer les montre à l'Aide de où", c'est en référence à lahappened_in = 2006
prédicat. (Ma compréhension est peut-être incomplète, je ne peux pas utiliser les vues dans MySQL.)happened_in
pourplayer = 24
. SI lehappened_in = 2006
prédicat avait été poussé dans l'avis de requête, alors il n'y aurait qu'une seule ligne dans la table dérivée, et nous attendons d'expliquer le rapport d'un nombre de lignes de 1 de il.MERGE
ne peut pas être utilisé si la vue contient des des constructions suivantes: fonctions d'Agrégation ( ...,MAX()
, (La réponse aurait pu être plus explicite quant à la raison pour laquelle MySQL n'est pas à repousser le prédicat dans l'OP vue)De la création de la composite index avec
player + happened_in
(dans cet ordre) des colonnes est le meilleur que vous pouvez faire dans ce cas.PS: ne pas tester mysql optimiseur de comportement sur un si petit nombre de lignes, parce qu'il est susceptible de préférer l'analyse complète sur les index. Si vous voulez voir ce qui va se passer dans la vraie vie - le remplir avec la vie réelle-même quantité de données.
ON (player_id, happened_in, score)
serait encore mieux de l'indice pour l'avis de requête; qui permettrait à l'GROUP BY
opération doit être optimisée, en évitant une opération de tri, ainsi permettant la requête pour être satisfait entièrement à partir de la page d'index, sans avoir besoin de visiter toutes les pages de données.Ce n'est pas directement répondre à la question, mais il est directement lié à la solution de contournement pour les autres cours d'exécution dans cette question. Cela permet d'obtenir les mêmes avantages de l'utilisation d'un point de vue, tout en minimisant les inconvénients.
- Je configurer une fonction PHP à qui je peux envoyer des paramètres, des choses à pousser vers l'intérieur afin de maximiser l'utilisation des index, plutôt que de les utiliser dans une jointure ou une clause where à l'extérieur d'un point de vue. Dans la fonction, vous pouvez formuler la syntaxe SQL pour une table dérivée, et de retour que de la syntaxe. Ensuite, dans le programme appelant, vous pouvez faire quelque chose comme ceci:
Ainsi, vous obtenez les avantages de l'encapsulation de la vue, la capacité de l'appeler comme si il est un point de vue, mais pas l'index limites.