Règles générales pour simplifier les déclarations SQL
je suis à la recherche de quelques" règles d'inférence " (semblables à des règles d'opération ou des règles de logique) que je peux utiliser pour réduire une requête SQL dans la complexité ou la taille. Existe-t-il quelque chose comme ça? Tous les papiers, tous les outils? Des équivalences que vous avez trouvées vous-même? C'est en quelque sorte similaire à l'optimisation des requêtes, mais pas en termes de performance.
pour l'exprimer autrement: avoir une requête (complexe) avec jointures, sous-sélections, UNIONs est-il possible (ou non) de la réduire à une déclaration SQL équivalente plus simple, qui produit le même résultat, en utilisant certaines règles de transformation?
donc, je suis à la recherche de transformations équivalentes de déclarations SQL comme le fait que la plupart des sous-sélects peuvent être réécrits comme une jointure.
8 réponses
pour l'exprimer différemment: avoir une requête (complexe) avec jointures, sous-sélections, UNIONs est-il possible (ou non) de la réduire à une déclaration SQL plus simple et équivalente, qui produit le même résultat, en utilisant certaines règles de transformation?
C'est exactement ce que font les optimiseurs pour gagner leur vie (ce n'est pas que je dis qu'ils le font toujours bien).
depuis SQL est un langage basé sur des ensembles, Il ya généralement plus qu'un moyen de transformer une requête à l'autre.
Comme cette requête:
SELECT *
FROM mytable
WHERE col1 > @value1 OR col2 < @value2
peut être transformé en ceci:
SELECT *
FROM mytable
WHERE col1 > @value1
UNION
SELECT *
FROM mytable
WHERE col2 < @value2
ou ceci:
SELECT mo.*
FROM (
SELECT id
FROM mytable
WHERE col1 > @value1
UNION
SELECT id
FROM mytable
WHERE col2 < @value2
) mi
JOIN mytable mo
ON mo.id = mi.id
, qui semblent plus moches mais peuvent donner de meilleurs plans d'exécution.
une des choses les plus communes à faire est de remplacer cette requête:
SELECT *
FROM mytable
WHERE col IN
(
SELECT othercol
FROM othertable
)
avec celui-ci:
SELECT *
FROM mytable mo
WHERE EXISTS
(
SELECT NULL
FROM othertable o
WHERE o.othercol = mo.col
)
dans certains RDBMS 's (comme PostgreSQL ), DISTINCT et GROUP BY utiliser les différents plans d'exécution, il est donc parfois préférable de remplacer l'un par l'autre:
SELECT mo.grouper,
(
SELECT SUM(col)
FROM mytable mi
WHERE mi.grouper = mo.grouper
)
FROM (
SELECT DISTINCT grouper
FROM mytable
) mo
vs.
SELECT mo.grouper, SUM(col)
FROM mytable
GROUP BY
mo.grouper
Dans PostgreSQL , DISTINCT trie et GROUP BY les tables de hachage.
MySQL manque FULL OUTER JOIN , il peut donc être réécrit comme suit:
SELECT t1.col1, t2.col2
FROM table1 t1
LEFT OUTER JOIN
table2 t2
ON t1.id = t2.id
vs.
SELECT t1.col1, t2.col2
FROM table1 t1
LEFT JOIN
table2 t2
ON t1.id = t2.id
UNION ALL
SELECT NULL, t2.col2
FROM table1 t1
RIGHT JOIN
table2 t2
ON t1.id = t2.id
WHERE t1.id IS NULL
, mais voir cet article dans mon blog sur la façon de le faire plus efficacement dans MySQL :
Cette requête hiérarchique dans Oracle :
SELECT DISTINCT(animal_id) AS animal_id
FROM animal
START WITH
animal_id = :id
CONNECT BY
PRIOR animal_id IN (father, mother)
ORDER BY
animal_id
peut être transformé en ceci:
SELECT DISTINCT(animal_id) AS animal_id
FROM (
SELECT 0 AS gender, animal_id, father AS parent
FROM animal
UNION ALL
SELECT 1, animal_id, mother
FROM animal
)
START WITH
animal_id = :id
CONNECT BY
parent = PRIOR animal_id
ORDER BY
animal_id
, ce dernier étant plus performant.
voir cet article sur mon blog pour les détails du plan d'exécution:
pour trouver toutes les plages qui chevauchent la plage donnée, vous pouvez utiliser la requête suivante:
SELECT *
FROM ranges
WHERE end_date >= @start
AND start_date <= @end
, mais dans SQL Server cette requête plus complexe donne les mêmes résultats plus rapidement:
SELECT *
FROM ranges
WHERE (start_date > @start AND start_date <= @end)
OR (@start BETWEEN start_date AND end_date)
, et croyez-le ou pas, j'ai un article dans mon blog sur ce trop:
SQL Server manque également d'un moyen efficace de faire des agrégats cumulatifs, de sorte que cette question:
SELECT mi.id, SUM(mo.value) AS running_sum
FROM mytable mi
JOIN mytable mo
ON mo.id <= mi.id
GROUP BY
mi.id
peut être réécrit plus efficacement en utilisant, Seigneur aide-moi, curseurs (vous m'avez bien entendu: cursors , more efficiently et SQL Server en une phrase).
voir cet article dans mon blog sur la façon de le faire:
il existe un certain type de requête couramment rencontré dans les applications financières qui recherche le taux effectif d'une monnaie, comme celle-ci dans Oracle :
SELECT TO_CHAR(SUM(xac_amount * rte_rate), 'FM999G999G999G999G999G999D999999')
FROM t_transaction x
JOIN t_rate r
ON (rte_currency, rte_date) IN
(
SELECT xac_currency, MAX(rte_date)
FROM t_rate
WHERE rte_currency = xac_currency
AND rte_date <= xac_date
)
cette requête peut être fortement réécrite pour utiliser une condition d'égalité qui permet un HASH JOIN au lieu de NESTED LOOPS :
WITH v_rate AS
(
SELECT cur_id AS eff_currency, dte_date AS eff_date, rte_rate AS eff_rate
FROM (
SELECT cur_id, dte_date,
(
SELECT MAX(rte_date)
FROM t_rate ri
WHERE rte_currency = cur_id
AND rte_date <= dte_date
) AS rte_effdate
FROM (
SELECT (
SELECT MAX(rte_date)
FROM t_rate
) - level + 1 AS dte_date
FROM dual
CONNECT BY
level <=
(
SELECT MAX(rte_date) - MIN(rte_date)
FROM t_rate
)
) v_date,
(
SELECT 1 AS cur_id
FROM dual
UNION ALL
SELECT 2 AS cur_id
FROM dual
) v_currency
) v_eff
LEFT JOIN
t_rate
ON rte_currency = cur_id
AND rte_date = rte_effdate
)
SELECT TO_CHAR(SUM(xac_amount * eff_rate), 'FM999G999G999G999G999G999D999999')
FROM (
SELECT xac_currency, TRUNC(xac_date) AS xac_date, SUM(xac_amount) AS xac_amount, COUNT(*) AS cnt
FROM t_transaction x
GROUP BY
xac_currency, TRUNC(xac_date)
)
JOIN v_rate
ON eff_currency = xac_currency
AND eff_date = xac_date
en dépit d'être volumineux comme un enfer, la dernière requête est 6 fois plus rapide.
l'idée principale ici est de remplacer <= par = , ce qui nécessite la construction d'une table de calendrier en mémoire. à JOIN avec.
voici quelques exemples de travail avec Oracle 8 & 9 (bien sûr, parfois faire le contraire pourrait rendre la requête plus simple ou plus rapide):
les parenthèses peuvent être supprimées si elles ne sont pas utilisées pour outrepasser la priorité de l'opérateur. Un exemple simple est quand tous les opérateurs booléens dans votre clause where sont les mêmes: where ((a or b) or c) est équivalent à where a or b or c .
une sous-requête peut souvent (si pas toujours) être fusionnée avec la requête principale pour simplifier. D'après mon expérience, cela améliore souvent considérablement les performances:
select foo.a,
bar.a
from foomatic foo,
bartastic bar
where foo.id = bar.id and
bar.id = (
select ban.id
from bantabulous ban
where ban.bandana = 42
)
;
est équivalent à
select foo.a,
bar.a
from foomatic foo,
bartastic bar,
bantabulous ban
where foo.id = bar.id and
bar.id = ban.id and
ban.bandana = 42
;
en utilisant ANSI joins sépare beaucoup de logique de "singe de code" des parties vraiment intéressantes de la clause où: la requête précédente est équivalente à
select foo.a,
bar.a
from foomatic foo
join bartastic bar on bar.id = foo.id
join bantabulous ban on ban.id = bar.id
where ban.bandana = 42
;
si vous voulez vérifier l'existence d'une rangée, n'utilisez pas count(*) , utilisez plutôt rownum = 1 ou mettez la requête dans une clause where exists pour aller chercher une seule rangée au lieu de toutes.
- je suppose que l'évidence est de chercher des curseurs qui peuvent être remplacés par une opération basée sur SQL 'Set'.
- ensuite sur ma liste, est de chercher toutes les sous-requêtes corrélées qui peuvent être réécrites comme une requête non-corrélée
- dans les procédures stockées de longue durée, décomposer des instructions SQL séparées dans leurs propres procédures stockées. De cette façon, ils obtiendront leur propre plan de requête en cache.
- Rechercher des transactions qui peuvent avoir leur portée réduite. Je trouve régulièrement à l'intérieur d'une transaction des déclarations qui peuvent être en toute sécurité à l'extérieur.
- les sous-sélections peuvent souvent être réécrites comme des jointures simples (les optimiseurs modernes sont bons pour repérer les simples)
comme @Quassnoi l'a mentionné, L'Optimiseur fait souvent du bon travail. Une façon de l'aider est de s'assurer que les index et les statistiques sont à jour, et que des index appropriés existent pour votre charge de travail d'interrogation.
j'aime remplacer toute sorte de sous-select par join query.
celui-ci est évident:
SELECT *
FROM mytable mo
WHERE EXISTS
(
SELECT *
FROM othertable o
WHERE o.othercol = mo.col
)
par
SELECT mo.*
FROM mytable mo inner join othertable o on o.othercol = mo.col
et celui-ci est sous-estimé :
SELECT *
FROM mytable mo
WHERE NOT EXISTS
(
SELECT *
FROM othertable o
WHERE o.othercol = mo.col
)
par
SELECT mo.*
FROM mytable mo left outer join othertable o on o.othercol = mo.col
WHERE o.othercol is null
qu'Elle pourrait aider les SGBD pour choisir le bon plan d'exécution dans une grande demande.
j'aime tout le monde sur une équipe de suivre un ensemble de normes afin de rendre le code lisible et maintenable, compréhensible, machine, etc.. :)
- tout le monde utilise le même alias
- pas de curseurs. pas de boucles
- pourquoi pensez vous quand vous ne pouvez EXISTE
- tiret
- cohérence du style de codage
il y a d'autres choses ici Quelles sont certaines de vos normes de base de données les plus utiles?
étant donné la nature de SQL, vous devez absolument être conscient des implications de performance de tout remaniement. Refactoring des Applications SQL est une bonne ressource sur le refactoring avec un lourd accent sur la performance (voir le Chapitre 5).
bien que la simplification ne soit peut-être pas synonyme d'optimisation, la simplification peut être importante dans l'écriture du code SQL lisible, ce qui est à son tour critique pour être en mesure de vérifier votre code SQL pour l'exactitude conceptuelle (pas l'exactitude syntaxique, que votre environnement de développement devrait vérifier pour vous). Il me semble que dans un monde idéal, nous écririons le code SQL le plus simple et lisible, puis l'optimiseur réécrirait ce code SQL sous n'importe quelle forme (peut-être plus verbeux) exécuter le plus rapide.
j'ai trouvé qu'il est très utile de penser aux énoncés SQL comme basés sur une logique établie, surtout si j'ai besoin de combiner des clauses où ou de comprendre une négation complexe d'une clause où. J'utilise les lois de l'algèbre booléenne dans ce cas.
les plus importantes pour simplifier une clause où sont probablement les lois de DeMorgan (notez que · * " Est " et " et " + "est" ou"):
- PAS (x · y) = x + PAS y
- PAS (x + y) = x · de ne PAS y
cela se traduit en SQL par:
NOT (expr1 AND expr2) -> NOT expr1 OR NOT expr2
NOT (expr1 OR expr2) -> NOT expr1 AND NOT expr2
ces lois peuvent être très utiles pour simplifier où les clauses avec beaucoup de "emboîtés AND et OR parties.
il est également utile de se rappeler que la mention field1 IN (value1, value2, ...) est équivalente à field1 = value1 OR field1 = value2 OR ... . Cela vous permet de nier le IN () une de deux façons:
NOT field1 IN (value1, value2) -- for longer lists
NOT field1 = value1 AND NOT field1 = value2 -- for shorter lists
Une sous-requête peut être pensé de cette façon aussi. Par exemple, ceci a annulé où clause:
NOT (table1.field1 = value1 AND EXISTS (SELECT * FROM table2 WHERE table1.field1 = table2.field2))
peut être réécrit comme:
NOT table1.field1 = value1 OR NOT EXISTS (SELECT * FROM table2 WHERE table1.field1 = table2.field2))
ces lois ne vous disent pas comment transformer une requête SQL en utilisant un sous-jeu en utilisant une jointure, mais la logique booléenne peut vous aider à comprendre les types de jointure et ce que votre requête devrait être de retour. Par exemple, avec les tableaux A et B , un INNER JOIN est comme A AND B , un LEFT OUTER JOIN est comme (A AND NOT B) OR (A AND B) qui simplifie à A OR (A AND B) , et un FULL OUTER JOIN est A OR (A AND B) OR B qui simplifie à A OR B .
Mon approche est d'apprendre la théorie relationnelle en général et de l'algèbre relationnelle en particulier. Apprenez ensuite à repérer les constructions utilisées dans SQL pour mettre en œuvre les opérateurs à partir de l'algèbre relationnelle (par exemple, Universal quantification A. K. A. division) et le calcul (par exemple quantification existentielle). Le gotcha est que SQL a des caractéristiques qui ne se trouvent pas dans le modèle relationnel par exemple nulls, qui sont probablement mieux remaniés loin de toute façon. Lecture recommandée: SQL et la Théorie Relationnelle: Comment Ecrire le code SQL exact par C. J. Date .
dans cet esprit, Je ne suis pas convaincu que" le fait que la plupart des sous-sélections puissent être réécrites en jointure " représente une simplification.
prenez cette requête par exemple:
SELECT c
FROM T1
WHERE c NOT IN ( SELECT c FROM T2 );
réécrire en utilisant la jointure
SELECT DISTINCT T1.c
FROM T1 NATURAL LEFT OUTER JOIN T2
WHERE T2.c IS NULL;
le jointure est plus verbeux!
alternativement, reconnaître la construction est la mise en œuvre d'un antijoin sur la projection de c p.ex. pseudo algrbra
T1 { c } antijoin T2 { c }
Simplification à l'aide d'opérateurs relationnels:
SELECT c FROM T1 EXCEPT SELECT c FROM T2;