Ik ben op zoek naar enkele “inferentieregels” (vergelijkbaar met het instellen van bedieningsregels of logische regels) die ik kan gebruiken om een SQL-query in complexiteit of omvang te verminderen.
Bestaat er zoiets? Papieren, gereedschap? Zijn er overeenkomsten die u zelf hebt gevonden? Het lijkt op de een of andere manier op query-optimalisatie, maar niet in termen van prestaties.
Om het anders te zeggen: met een (complexe) query met JOIN’s, SUBSELECT’s, UNION’s is het mogelijk (of niet) om deze te reduceren tot een eenvoudiger, equivalent SQL-statement, dat hetzelfde resultaat oplevert, door enkele transformatieregels te gebruiken ?
Dus ik ben op zoek naar equivalente transformaties van SQL-instructies, zoals het feit dat de meeste SUBSELECT’s kunnen worden herschreven als een JOIN.
Antwoord 1, autoriteit 100%
Om het anders te zeggen: met een (complexe) query met JOIN’s, SUBSELECT’s, UNION’s is het mogelijk (of niet) om deze te reduceren tot een eenvoudiger, equivalent SQL-statement, dat hetzelfde resultaat oplevert, door enkele transformatieregels te gebruiken ?
Dat is precies wat optimizers doen voor de kost (niet dat ik zeg dat ze dit altijd goed doen).
Aangezien SQLeen op een set gebaseerde taal is, zijn er meestal meer dan één manier om de ene query om te zetten in een andere.
Vind deze zoekopdracht leuk:
SELECT *
FROM mytable
WHERE col1 > @value1 OR col2 < @value2
kan hierin worden omgezet:
SELECT *
FROM mytable
WHERE col1 > @value1
UNION
SELECT *
FROM mytable
WHERE col2 < @value2
of dit:
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
, die er lelijker uitzien maar betere uitvoeringsplannen kunnen opleveren.
Een van de meest voorkomende dingen die u kunt doen, is deze zoekopdracht vervangen:
SELECT *
FROM mytable
WHERE col IN
(
SELECT othercol
FROM othertable
)
met deze:
SELECT *
FROM mytable mo
WHERE EXISTS
(
SELECT NULL
FROM othertable o
WHERE o.othercol = mo.col
)
In sommige RDBMS‘s (zoals PostgreSQL), DISTINCTen GROUP BYgebruiken de verschillende uitvoeringsplannen , dus soms is het beter om de een door de ander te vervangen:
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
In PostgreSQLsorteert DISTINCTen GROUP BYhashes.
MySQLmist FULL OUTER JOIN, dus het kan als volgt worden herschreven:
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
, maar lees dit artikel in mijn blog over hoe je dit efficiënter kunt doen in MySQL:
Deze hiërarchische zoekopdracht in 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
kan naar dit worden getransformeerd:
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
, waarbij de laatste beter presteert.
Zie dit artikel in mijn blog voor de details van het uitvoeringsplan:
Om alle bereiken te vinden die het opgegeven bereik overlappen, kunt u de volgende zoekopdracht gebruiken:
SELECT *
FROM ranges
WHERE end_date >= @start
AND start_date <= @end
, maar in SQL Serverlevert deze complexere zoekopdracht sneller dezelfde resultaten op:
SELECT *
FROM ranges
WHERE (start_date > @start AND start_date <= @end)
OR (@start BETWEEN start_date AND end_date)
, en geloof het of niet, ik heb hier ook een artikel over op mijn blog:
SQL Servermist ook een efficiënte manier om cumulatieve aggregaties uit te voeren, dus deze vraag:
SELECT mi.id, SUM(mo.value) AS running_sum
FROM mytable mi
JOIN mytable mo
ON mo.id <= mi.id
GROUP BY
mi.id
kan efficiënter worden herschreven met behulp van, Heer help me, cursors (u hebt me goed gehoord: cursors, more efficientlyen SQL Serverin één zin).
Zie dit artikel in mijn blog over hoe je dit moet doen:
- Tijden afvlakken: SQL Server
Er wordt vaak aan een bepaald soort zoekopdracht voldaan in financiële toepassingen die zoeken naar de effectieve koers voor een valuta, zoals deze in 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
)
Deze query kan zwaar worden herschreven om een gelijkheidsvoorwaarde te gebruiken die een HASH JOINtoestaat in plaats van 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
Ondanks dat het enorm omvangrijk is, is de laatste zoekopdracht 6keer sneller.
Het belangrijkste idee hier is om <=te vervangen door =, waarvoor een kalendertabel in het geheugen moet worden gemaakt. om JOINmet.
Antwoord 2, autoriteit 14%
Hier zijn er een paar van het werken met Oracle 8 & 9 (natuurlijk, soms kan het tegenovergestelde de zoekopdracht eenvoudiger of sneller maken):
Haakjes kunnen worden verwijderd als ze niet worden gebruikt om de prioriteit van de operator te overschrijven. Een eenvoudig voorbeeld is wanneer alle booleaanse operatoren in uw where-clausule hetzelfde zijn: where ((a or b) or c)is gelijk aan where a or b or c.
Een subquery kan vaak (zo niet altijd) worden samengevoegd met de hoofdqueryom deze te vereenvoudigen. In mijn ervaring verbetert dit de prestaties vaak aanzienlijk:
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
)
;
is gelijk aan
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
;
Het gebruik van ANSI-joinsscheidt veel “code monkey”-logica van de echt interessante delen van de where-clausule: de vorige query is gelijk aan
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
;
Als u wilt controleren op het bestaan van een rij, gebruik dan niet count(*), maar gebruik in plaats daarvan rownum = 1of plaats de query in een where exists-clausule om slechts één rij op te halen in plaats van alle.
Antwoord 3, autoriteit 10%
- Ik veronderstel dat het voor de hand liggende is om te zoeken naar cursors die kunnen worden vervangen door een op SQL ‘Set’ gebaseerde bewerking.
- De volgende op mijn lijst is zoeken naar gecorreleerde subquery’s die kunnen worden herschreven als een niet-gecorreleerde query
- Breek in lange opgeslagen procedures afzonderlijke SQL-instructies op in hun eigen opgeslagen procedures. Op die manier krijgen ze hun eigen queryplan in de cache.
- Zoek naar transacties waarvan het bereik kan worden ingekort. Ik vind regelmatig afschriften binnen een transactie die veilig buiten kunnen zijn.
- Subselecties kunnen vaak worden herschreven als ongecompliceerde joins (moderne optimizers zijn goed in het herkennen van eenvoudige joins)
Zoals @Quassnoi al zei, doet de Optimizer vaak goed werk. Een manier om dit te helpen is ervoor te zorgen dat indexen en statistieken up-to-date zijn en dat er geschikte indexen bestaan voor uw querywerklast.
Antwoord 4, autoriteit 8%
Ik vind het leuk om alle soorten subselect te vervangen door een join-query.
Deze is duidelijk:
SELECT *
FROM mytable mo
WHERE EXISTS
(
SELECT *
FROM othertable o
WHERE o.othercol = mo.col
)
door
SELECT mo.*
FROM mytable mo inner join othertable o on o.othercol = mo.col
En deze wordt onderschat :
SELECT *
FROM mytable mo
WHERE NOT EXISTS
(
SELECT *
FROM othertable o
WHERE o.othercol = mo.col
)
door
SELECT mo.*
FROM mytable mo left outer join othertable o on o.othercol = mo.col
WHERE o.othercol is null
Het zou de DBMS kunnen helpen om het goede uitvoeringsplan te kiezen in een groot verzoek.
Antwoord 5, autoriteit 8%
Ik wil dat iedereen in een team een reeks normen volgt om code leesbaar, onderhoudbaar, begrijpelijk, wasbaar, enz. te maken. 🙂
- iedereen gebruikt dezelfde alias
- geen cursors. geen lussen
- waarom zelfs maar denken aan IN als je kunt BESTAAN
- INDENT
- Consistentie in codeerstijl
er zijn nog meer dingen hier Wat zijn enkele van uw meest bruikbare databasestandaarden?
Antwoord 6, autoriteit 6%
Gezien de aard van SQL, moet u zich absoluut bewust zijn van de prestatie-implicaties van refactoring. Refactoring van SQL-applicatiesis een goede bron voor refactoring met een sterke nadruk op prestaties ( zie hoofdstuk 5).
Antwoord 7, autoriteit 5%
Hoewel vereenvoudiging misschien niet gelijk staat aan optimalisatie, kan vereenvoudiging belangrijk zijn bij het schrijven van leesbare SQL-code, wat op zijn beurt van cruciaal belang is om uw SQL-code te kunnen controleren op conceptuele correctheid (niet syntactische correctheid, die uw ontwikkelomgeving voor u zou moeten controleren) . Het lijkt mij dat we in een ideale wereld de meest eenvoudige, leesbare SQL-code zouden schrijven en dat de optimizer die SQL-code zou herschrijven zodat deze in welke vorm dan ook (misschien meer uitgebreid) het snelst zou werken.
Ik heb gemerkt dat het erg handig is om SQL-statements te beschouwen als gebaseerd op setlogica, vooral als ik where-clausules moet combineren of een complexe ontkenning van een where-clausule moet bedenken. Ik gebruik in dit geval de wetten van de booleaanse algebra.
De belangrijkste voor het vereenvoudigen van een waar-clausule zijn waarschijnlijk de wetten van DeMorgan (merk op dat “·” “AND” is en “+” “OF” is):
- NIET (x · y) = NIET x + NIET y
- NIET (x + y) = NIET x · NIET y
Dit vertaalt zich in SQL naar:
NOT (expr1 AND expr2) -> NOT expr1 OR NOT expr2
NOT (expr1 OR expr2) -> NOT expr1 AND NOT expr2
Deze wetten kunnen erg handig zijn bij het vereenvoudigen van waar-clausules met veel geneste ANDen ORdelen.
Het is ook handig om te onthouden dat de instructie field1 IN (value1, value2, ...)gelijk is aan field1 = value1 OR field1 = value2 OR .... Hiermee kunt u de IN ()op twee manieren negeren:
NOT field1 IN (value1, value2) -- for longer lists
NOT field1 = value1 AND NOT field1 = value2 -- for shorter lists
Een subquery kan ook op deze manier worden bedacht. Dit negeerde bijvoorbeeld de where-clausule:
NOT (table1.field1 = value1 AND EXISTS (SELECT * FROM table2 WHERE table1.field1 = table2.field2))
kan worden herschreven als:
NOT table1.field1 = value1 OR NOT EXISTS (SELECT * FROM table2 WHERE table1.field1 = table2.field2))
Deze wetten vertellen u niet hoe u een SQL-query met behulp van een subquery kunt omzetten in een met behulp van een join, maar booleaanse logica kan u helpen inzicht te krijgen in de typen joins en wat uw query zou moeten opleveren. Met tabellen Aen Bis een INNER JOINbijvoorbeeld als A AND B, een LEFT OUTER JOINis als (A AND NOT B) OR (A AND B)wat vereenvoudigt tot A OR (A AND B), en een FULL OUTER JOINis A OR (A AND B) OR Bwat vereenvoudigt tot A OR B.
Antwoord 8
Mijn aanpak is om relationele theorie in het algemeen en relationele algebra in het bijzonder te leren. Leer vervolgens de constructies te herkennen die in SQL worden gebruikt om operatoren uit de relationele algebra (bijv. universele kwantificering, ook wel deling) en calculus (bijv. existentiële kwantificering) te implementeren. De gotcha is dat SQL functies heeft die niet in het relationele model worden gevonden, b.v. nulls, die hoe dan ook het beste kunnen worden weggewerkt. Aanbevolen literatuur: SQL en relationele theorie : Nauwkeurige SQL-code schrijven op CJ-datum.
In deze geest ben ik er niet van overtuigd dat “het feit dat de meeste SUBSELECT’s kunnen worden herschreven als een JOIN” een vereenvoudiging is.
Neem bijvoorbeeld deze vraag:
SELECT c
FROM T1
WHERE c NOT IN ( SELECT c FROM T2 );
Herschrijven met JOIN
SELECT DISTINCT T1.c
FROM T1 NATURAL LEFT OUTER JOIN T2
WHERE T2.c IS NULL;
De join is uitgebreider!
Als alternatief, herken het construct een antijoin implementeert op de projectie van cb.v. pseudo-algrbra
T1 { c } antijoin T2 { c }
Vereenvoudiging met behulp van relationele operatoren:
SELECT c FROM T1 EXCEPT SELECT c FROM T2;