Comment puis-je obtenir MySQL pour utiliser un INDEX pour la requête de vue?
Je travaille sur un projet web avec une base de données MySql sur Java EE. Nous avions besoin d'une vue pour résumer les données de 3 tables avec plus de 3M lignes dans l'ensemble. Chaque table a été créée avec index. Mais je n'ai pas trouvé un moyen de profiter des index dans la récupération conditionnelle de l'instruction SELECT à partir de la vue que nous avons créée avec [group by].
J'ai des suggestions de personnes que utiliser des vues 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 certains tests que j'ai pris, les index peuvent être utilisés dans l'instruction view select. Peut-être que j'ai créé ces vues dans le mauvais sens.
, je vais utiliser un exemple pour décrire mon problème.
Nous avons une table qui enregistre les données pour les scores élevés dans les jeux NBA, avec 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);
Ensuite, je crée une vue pour voir le score le plus élevé que Kobe Bryant a obtenu 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 déclaration 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'explique dans toad pour mysql, j'ai découvert que mysql a scan toutes les lignes pour former la vue, puis trouver des données avec condition, sans utiliser index sur [happened_in].
explain select * from v_kobe_highscores where happened_in = 2006;
La vue que nous utilisons dans notre projet est construite parmi des tables avec des millions de lignes. L'analyse de toutes les lignes de la table dans chaque récupération de données de vue 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. Je pense que @spencer7593 a le bon point. L'optimiseur MySQL ne "pousse" pas ce prédicat dans la requête view.
3 réponses
Comment obtenez-vous MySQL pour utiliser un index pour une requête de vue? La réponse courte, fournir un index que MySQL peut utiliser.
Dans ce cas, l'indice optimal est probablement un indice "couvrant":
... ON highscores (player, happened_in, score)
Il est probable que MySQL utilisera cet index, et L'explication montrera: "Using index" en raison du WHERE player = 24 (un prédicat d'égalité sur la colonne principale de l'index. Le GROUP BY happened_id (la deuxième colonne de l'index), peut permettre à MySQL d'optimiser cela en utilisant l'index pour éviter une opération de tri. Inclure la colonne score dans l'index permettra à la requête de satisfaire entièrement à partir de l'index, sans avoir à visiter (rechercher) les pages de données référencées par l'index.
C'est la réponse rapide. La réponse la plus longue est que MySQL est très peu susceptible d'utiliser un index avec une colonne en tête de happened_id pour la requête view.
Pourquoi la vue provoque un problème de performance
L'un des problèmes que vous avez avec la vue MySQL est que MySQL ne "pousse" pas 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 exécute la "requête de vue"interne. Cette requête pour la vue est exécutée séparément, avant la requête externe. Le resultset de l'exécution de cette requête est "matérialisé"; c'est-à-dire que les résultats sont stockés en tant que table MyISAM intermédiaire. (MySQL l'appelle une "table dérivée", et ce nom qu'ils utilisent a du sens, quand vous comprenez le opérations effectuées par MysQL.)
L'essentiel est que L'index 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 la création de la "table dérivée" intermédiaire, la requête externe est exécutée, en utilisant cette" table dérivée " comme rowsource. C'est lorsque cette requête externe s'exécute que le prédicat happened_in = 2006 est évalué.
Notez que toutes les lignes de la requête view sont stockées, ce qui (dans votre cas) est un ligne pour chaque valeur de happened_in, pas seulement celle sur laquelle vous spécifiez un prédicat d'égalité dans la requête externe.
La façon dont les requêtes de vue sont traitées peut être "inattendue" par certains, et c'est l'une des raisons pour lesquelles l'utilisation de "vues" dans MySQL peut entraîner des problèmes de performances, par rapport à la façon dont les requêtes de vue sont traitées par d'autres bases de données relationnelles.
amélioration des performances de la requête de vue avec un index de couverture approprié
Compte tenu de votre définition de vue et votre requête, sur le meilleur que vous allez obtenir serait une méthode d'accès" en utilisant l'index " pour la requête de vue. Pour obtenir cela, vous auriez besoin d'un index de couverture, par exemple
... ON highscores (player, happened_in, score).
C'est probablement L'index le plus bénéfique (en termes de performances) pour votre définition de vue existante et votre requête existante. La colonne player est la colonne principale car vous avez un prédicat d'égalité sur cette colonne dans la requête view. La colonne happened_in est suivante, car vous avez une opération GROUP BY sur cette colonne, et MySQL va pouvoir utiliser cet index pour optimiser le groupe par opération. Nous incluons également la colonne score, car c'est la seule autre colonne référencée dans votre requête. Cela fait de L'index un index "couvrant", car MySQL peut satisfaire cette requête directement à partir des pages d'index, sans avoir besoin de visiter les pages de la table sous-jacente. Et c'est aussi bon que nous allons sortir de ce plan de requête: "utiliser l'index" sans "utiliser filesort".
Comparer les performances autonome requête sans table dérivée
Vous pouvez comparer le plan d'exécution de votre requête à la vue par rapport à une requête autonome équivalente:
SELECT player
, MAX(score) AS highest_score
, happened_in
FROM highscores
WHERE player = 24
AND happened_in = 2006
GROUP
BY player
, happened_in
La requête autonome peut également utiliser un index de couverture, par exemple
... ON highscores (player, happened_in, score)
Mais sans avoir besoin de matérialiser une table MyISAM intermédiaire.
Je ne suis pas sûr que l'un des précédents apporte une réponse directe à la question que vous posiez.
Q: Comment puis-je obtenir MySQL à utiliser un INDEX pour la requête de vue?
R: définissez un INDEX approprié que la requête view peut utiliser.
La réponse courte est de fournir un "index couvrant" (index inclut toutes les colonnes référencées dans la requête view). Les colonnes en tête de cet index doivent être les colonnes référencées avec des prédicats d'égalité (dans votre cas, la colonne player serait une colonne en tête car vous avez un prédicat player = 24 dans la requête. En outre, les colonnes référencées dans le GROUP BY devraient être en tête des colonnes dans l'index, ce qui permet à MySQL d'optimiser l'opération GROUP BY, en utilisant l'index plutôt que d'utiliser une opération de tri.
Le point clé ici est que la requête view est fondamentalement une requête autonome; les résultats de cette requête sont stockés dans une table "dérivée" Intermédiaire (une table MyISAM qui est créée lorsqu'une requête contre la vue est exécutée.
Utiliser des vues dans MySQL n'est pas nécessairement une "mauvaise idée" , mais je mettrais fortement en garde Ceux qui choisissent pour utiliser des vues dans MySQL pour être conscient de la façon dont MySQL traite les requêtes qui font référence à ces vues. Et la façon dont MySQL traite les requêtes de vue diffère (de manière significative) de la façon dont les requêtes de vue sont traitées par d'autres bases de données (par exemple Oracle, SQL Server).
Créer l'indexcomposite avec les colonnes player + happened_in (dans cet ordre particulier) est le meilleur que vous pouvez faire dans ce cas.
PS: ne testez pas le comportement de l'optimiseur mysql sur une si petite quantité de lignes, car il est susceptible de préférer fullscan aux index. Si vous voulez voir ce qui se passera dans la vie réelle-remplissez-le avec la quantité de données de la vie réelle.
Cela ne répond pas directement à la question, mais c'est une solution de contournement directement liée pour les autres qui rencontrent ce problème. Cela permet d'obtenir les mêmes avantages que l'utilisation d'une vue, tout en minimisant les inconvénients.
Je configure une fonction PHP à laquelle je peux envoyer des paramètres, des choses à pousser à l'intérieur pour maximiser l'utilisation de l'index, plutôt que de les utiliser dans une clause join ou where en dehors d'une vue. Dans la fonction, vous pouvez formuler la syntaxe SQL pour une table dérivée et renvoyer cette syntaxe. Ensuite, dans le programme appelant, vous pouvez faire quelque chose comme ceci:
$table = tablesyntax(parameters);
select field1, field2 from {$table} as x... + other SQL
Ainsi, vous obtenez les avantages d'encapsulation de la vue, la possibilité de l'appeler comme si c'était une vue, mais pas les limitations d'index.