[MySQL] Query met (te) veel joins optimaliseren

[egonolieux]

Ik ben een applicatie aan het bouwen voor het beheren van ruilkaartcollecties. Bij het weergeven van kaarten kunnen gebruikers filters toepassen om de resultaten te verfijnen.

Het probleem is echter dat deze filtercriteria telkens een nieuwe join toevoegen aan de query, waardoor ik met +- 25 joins zit. Bij grotere resultaten (5000) is dit nefast voor de performance en duurt het uitvoeren van de query tot één seconde, wat veel te traag is.

Aangezien de WHERE clausule dynamisch opgebouwd wordt en weinig relevantie heeft, heb ik ze weggelaten binnen dit voorbeeld (alsook paginering):

code:

SELECT 
    card_variant.id AS card_id, 
    card.name AS card_name, 
    GROUP_CONCAT(
        DISTINCT card_type.name ORDER BY card_type.name ASC SEPARATOR \', \'
    ) AS card_types, 
    GROUP_CONCAT(
        DISTINCT card_variant_type.name ORDER BY card_variant_type.name ASC SEPARATOR \', \'
    ) AS card_variant_types, 
    expansion.name AS expansion_name, 
    expansion.symbol_file_path AS expansion_symbol_file_path, 
    card.number, 
    card_rarity.name AS rarity_name, 
    language_has_card_rarity.symbol_file_path AS rarity_symbol_file_path, 
    card_variant.image_file_path 
FROM card 
INNER JOIN card_variant ON card.id = card_variant.card_id 
INNER JOIN card_variant_has_card_variant_type ON card_variant.id = card_variant_has_card_variant_type.card_variant_id 
INNER JOIN card_variant_type ON card_variant_has_card_variant_type.card_variant_type_id = card_variant_type.id 
INNER JOIN expansion ON card.expansion_id = expansion.id 
INNER JOIN expansion_series ON expansion.expansion_series_id = expansion_series.id 
INNER JOIN language ON expansion_series.language_id = language.id 
LEFT JOIN card_has_card_type ON card.id = card_has_card_type.card_id 
LEFT JOIN card_type ON card_has_card_type.card_type_id = card_type.id 
LEFT JOIN card_has_energy_type on card.id = card_has_energy_type.card_id 
LEFT JOIN energy_type ON card_has_energy_type.energy_type_id = energy_type.id 
LEFT JOIN pokemon_stage ON card.pokemon_stage_id = pokemon_stage.id 
LEFT JOIN card_effect ON card.id = card_effect.card_id 
LEFT JOIN card_attack ON card.id = card_attack.card_id 
LEFT JOIN card_attack_has_energy_type ON card_attack.id = card_attack_has_energy_type.card_attack_id 
LEFT JOIN energy_type AS attack_energy_type ON card_attack_has_energy_type.energy_type_id = attack_energy_type.id 
LEFT JOIN card_has_weakness ON card.id = card_has_weakness.card_id 
LEFT JOIN energy_type AS weakness ON card_has_weakness.energy_type_id = weakness.id 
LEFT JOIN card_has_resistance ON card.id = card_has_resistance.card_id 
LEFT JOIN energy_type AS resistance ON card_has_resistance.energy_type_id = resistance.id 
INNER JOIN card_rarity ON card.card_rarity_id = card_rarity.id 
INNER JOIN language_has_card_rarity ON card_rarity.id = language_has_card_rarity.card_rarity_id 
LEFT JOIN card_has_card_illustrator ON card.id = card_has_card_illustrator.card_id 
LEFT JOIN card_illustrator ON card_has_card_illustrator.card_illustrator_id = card_illustrator.id 
GROUP BY card_variant.id;

Hoe kan ik de uitvoertijd verbeteren zonder mijn database schema overhoop te halen?

[sig69]

Wat zegt een explain?

[ Voor 33% gewijzigd door sig69 op 04-05-2016 19:57 ]

[RobIII]

sig69 schreef op woensdag 04 mei 2016 @ 19:12:
Wat zegt een explain?

^ Dat dus. Volgende steekwoord: indexes!
Overigens: je group by klopt voor geen meter (Hoe werkt dat GROUP BY nu eigenlijk?) en een group_concat is 99/100 keer een rode vlag; heb je die écht nodig?

[ Voor 41% gewijzigd door RobIII op 04-05-2016 21:00 ]

[sig69]

Mijn originele post bevatte ook: "MySql zo te zien", maar dat bleek achteraf in de titel te staan. Dat zag ik ook aan de group by inderdaad.

[Erhnam]

Ik zie op het eerste gezicht niet hoe het veel sneller kan. Er zitten niet bijvoorbeel veel where statements in. Vaak zie je dat men eerst alles op elkaar bouwt en als laatste een where uitvoert. Wellicht met indexen? Zeker op alle types, hier zou je clustered indexen op kunnen zetten. Heb je verder alle data uit alle tabellen nodig?

[ThomasG]

Het gebruik van indices heeft hier een sleutel rol in, maar het lukraak aanmaken van een index gaat niet zonder meer helpen. Ik weet niet hoe het inmiddels voor staat, maar MySQL had er altijd een handje van een index te negeren wanneer de query niet in de volgorde van de indices werd opgebouwd. Dus wellicht heb je al een index, maar wordt deze niet gebruikt.

[sig69]

De where statements worden dynamisch opgebouwd. Dat is waarschijnlijk (een deel van) het probleem: je kan moeilijk voor elke mogelijke combinatie optimaliseren. Je kan wel een explain doen voor een bepaalde query, maar als een gebruiker op andere velden zoekt wordt de uiteindelijke query ook anders. Misschien moet je de oplossing gaan zoeken in iets als facetted search, elastic search, of hoe heet dat spul ook alweer (ik ken de term maar verder geen ervaring mee).

[ Voor 51% gewijzigd door sig69 op 04-05-2016 22:11 ]

[egonolieux]

Output van explain: https://gist.github.com/egonolieux/b19864c127d7a25c644b767e1eb660aa

Ik ben momenteel zelf nog aan het uitzoeken wat al de waarden specifiek betekenen, maar geef gerust feedback als je iets ziet.

RobIII schreef op woensdag 04 mei 2016 @ 20:56:
[...]

Overigens: je group by klopt voor geen meter (Hoe werkt dat GROUP BY nu eigenlijk?) en een group_concat is 99/100 keer een rode vlag; heb je die écht nodig?

Wat is er mis aan mijn GROUP BY? Alle kolommen die ik niet aggregeer zijn hetzelfde voor elke card_variant.id dus hoef ik ze niet te vermelden (althans in MySQL).

Waarom is GROUP_CONCAT 99/100 keer een rode vlag? Wat ik hier wil bereiken is meerdere waarden voor dezelfde kolom in een string gescheiden door een komma samenvatten.

sig69 schreef op woensdag 04 mei 2016 @ 22:07:
De where statements worden dynamisch opgebouwd. Dat is waarschijnlijk (een deel van) het probleem: je kan moeilijk voor elke mogelijke combinatie optimaliseren. Je kan wel een explain doen voor een bepaalde query, maar als een gebruiker op andere velden zoekt wordt de uiteindelijke query ook anders. Misschien moet je de oplossing gaan zoeken in iets als facetted search, elastic search, of hoe heet dat spul ook alweer (ik ken de term maar verder geen ervaring mee).

Het probleem zit hem niet bepaald in het filteren zelf (tenzij de gebruiker honderden filter opties aanvinkt). Het uitvoeren van de query met of zonder (een normaal aantal) filteropties geeft vrijwel geen significant verschil.

[Gomez12]

Persoonlijk zou ik toch eens goed naar je db-ontwerp kijken want volgens mij heb je het ietwat te ver genormaliseerd...

Maar ok, dat wens je niet.
Dan zou ik zeggen :
- Explain nakijken
- Analyse tables draaien (zo heet dat toch in mysql om je statistieken bij te werken)
- Een aantal dingen in sub-query's stoppen zodat ze eerder afgestopt kunnen worden. Bijv is de hele join van regel 39 nog wel van toepassing als regel 38 al een null geeft?
- Maar sowieso group by fixen, nu geef je de optimizer maar halve info om mee te werken, geef hem alle info.

[DaCoTa]

Als je je database schema intact wilt laten, zul je je where clauses op id's moeten doen. Ik ga er even vanuit dat alle 'attribuut' tabellen maar een beperkt aantal rijen bevatten. Je kunt dus óf die allemaal cachen, want dat lijkt me statische data en in code alle id's optrommelen, of alleen van de where clauses eerste de id's resolven en die direct gebruiken, in plaats van joins.

[GlowMouse]

Het lijkt erop dat je WHERE-clauses toevoegt op tabellen die je joint. Dit is niet goed te optimaliseren. Het beste kun je een zoekserver zoals Sphinx inzetten.

ThomasG schreef op woensdag 04 mei 2016 @ 22:06:
MySQL had er altijd een handje van een index te negeren wanneer de query niet in de volgorde van de indices werd opgebouwd. Dus wellicht heb je al een index, maar wordt deze niet gebruikt.

Dit is onzin.

[Xudonax]

Mag ik een andere oplossingsrichting voorstellen?

Ga eens kijken naar zoekmachines zoals bijvoorbeeld Solr, ElasticSearch of zelfs "naked" Lucene. De eerste twee vereisen een Java applicatieserver (maar Jetty is meegeleverd AFAIK) en zijn bloedsnel (queries zijn vrijwel altijd in <100ms beantwoord). "Naked" Lucene is een stuk lastiger om mee te werken, maar vereist geen extra service of server.

Wel ga je je datamodel moeten heroverwegen en volledig moeten platslaan. Aan de andere kant, een index op Solr kan behoorlijk compact zijn, ik heb praktijkvoorbeelden waar een PostgreSQL database van ~6GB volledig in een geoptimaliseerde Solr index van nog geen 600MB paste. Okay, je hebt een hoop waardes die dan enkel geïndexeerd zijn in je zoekmachine (dus je kunt ze er niet meer uit halen om bijv. aan de gebruiker te tonen), maar dat hadden we ook niet nodig Als je zorgt dat je enkel die waardes "stored" die je nodig hebt voor je zoekresultatenpagina en de rest enkel indexeert dan hoeft het niet eens zo heel veel extra ruimte te kosten.

Een nadeel is wel dat je regelmatig de database en de zoekindex moet synchroniseren, maar als je een CMS of een ORM gebruikt kun je waarschijnlijk inhaken op een "update" of "create" event waarbij je dat specifieke item in je zoekindex ook meteen update. Let echter wel op dat dit enkel goed gaat bij Solr/ElasticSearch, als je zelf tegen Lucene aanpraat moet je opletten dat je niet een te gefragmenteerde index krijgt want dan word het geheel weer trager.

[ThomasG]

GlowMouse schreef op woensdag 04 mei 2016 @ 23:46:
[...]

Dit is onzin.

Dat is zeker geen onzin. Als jij een multi-column index hebt op (a, b, c) en jouw where query bevat alleen a en c, dan is het effectief enkel een index op a. Als de query vervolgens eerst c heeft staan, werkt de index in sommige gevallen in zijn geheel niet. Vooral met complexe join queries wil een index nog wel eens genegeerd worden.

[GlowMouse]

ThomasG schreef op donderdag 05 mei 2016 @ 00:02:
[...]
Dat is zeker geen onzin. Als jij een multi-column index hebt op (a, b, c) en jouw where query bevat alleen a en c, dan is het effectief enkel een index op a. Als de query vervolgens eerst c heeft staan, werkt de index in sommige gevallen in zijn geheel niet. Vooral met complexe join queries wil een index nog wel eens genegeerd worden.

Dit is logisch bij een BTREE index, maar je had het over de opbouw van de query die in dezelfde volgorde moet zijn als de index (dus in jouw geval WHERE a=1 AND b=2 ipv WHERE b=2 AND a=1).

[ZaZ]

Als het je eigen database is effe

code:

SET GLOBAL sql_mode=CONCAT(@@sql_mode, ',ONLY_FULL_GROUP_BY');

eenmaal uitvoeren

Als het toe is gevoegd werkt vanaf de volgende sessie de group by tenminste weer een beetje normaal

Met

code:

SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

Maak je het weer ongedaan.

Ik gebruik eigenlijk nooit MySQL dus kan het even niet testen. Misschien dat iemand anders nog effe bij kan springen

[ Voor 54% gewijzigd door ZaZ op 05-05-2016 00:15 ]

[sig69]

egonolieux schreef op woensdag 04 mei 2016 @ 22:17:
Output van explain: https://gist.github.com/egonolieux/b19864c127d7a25c644b767e1eb660aa

Ik ben momenteel zelf nog aan het uitzoeken wat al de waarden specifiek betekenen, maar geef gerust feedback als je iets ziet.

Dit is geen explain van een trage query lijkt mij. Deze is van de query in de TS? Hebben we niks aan.

[...]


Wat is er mis aan mijn GROUP BY? Alle kolommen die ik niet aggregeer zijn hetzelfde voor elke card_variant.id dus hoef ik ze niet te vermelden (althans in MySQL).

In MySql wel ja...

Waarom is GROUP_CONCAT 99/100 keer een rode vlag? Wat ik hier wil bereiken is meerdere waarden voor dezelfde kolom in een string gescheiden door een komma samenvatten.


[...]


Het probleem zit hem niet bepaald in het filteren zelf (tenzij de gebruiker honderden filter opties aanvinkt). Het uitvoeren van de query met of zonder (een normaal aantal) filteropties geeft vrijwel geen significant verschil.

Als dat allemaal niet uitmaakt, zou ik naar de hardware gaan kijken

[storeman]

Ik zou gaan kijken naar de-normalisatie met bijvoorbeeld een materialized view. Misschien kun je de data op een logische manier terugbrengen naar 1, 2, 3 of 4 materialized views. Dat scheelt natuurlijk al flink wat joins. Een paar indexes erop en je moet toch behoorlijk snel kunnen zoeken.

Join je altijd op dezelfde tabellen? Of doe je dit ook dynamisch adhv de gevraagde filters? Je zou namelijk ook een right-join kunnen doen. Dan gooi je al in een eerder stadium rijen weg, dit kan een behoorlijk verschil maken in performance. Had gisteren nog een query die hierdoor een factor 8 sneller werd (0.08 naar 0.01ms). Uiteindelijk niet bruikbaar omdat er te veel rijen werden weggegooid (had met de count ook 0 waardes nodig voor elk weeknummer), maar misschien is dit bij jou wel in te passen.

[CurlyMo]

Wat ik niet goed snap is dat je allerlei tabellen joined die vervolgens niet terugkomen in de te selecteren kolommen en niet nodig zijn om voor bepaalde tabellen te koppelen. Naar mijn idee kunnen de volgende tabellen dus uit je query:
- expansion_series
- language
- card_has_energy_type
- energy_type
- pokemon_stage
- card_effect
- card_attack
- card_attack_has_energy_type
- energy_type AS attack_energy_type
- card_has_weakness
- energy_type AS weakness
- card_has_resistance
- energy_type AS resistance
- card_has_card_illustrator
- card_illustrator

Dat scheelt alweer 16 joins. Vervolgens kan het in bepaalde gevallen veel sneller zijn om resultaten via een subquery op te halen dan via een left-join. Zorg wel voor juiste indexes.

Dan blijft er dit over:

SQL:

SELECT 
    card_variant.id AS card_id, 
    card.name AS card_name, 
    GROUP_CONCAT(
        (SELECT DISTINCT card_type.name FROM card_type INNER JOIN card_has_card_type ON card_has_card_type.card_type_id = card_type.id WHERE card.id = card_has_card_type.card_id ORDER BY card_type.name ASC) SEPARATOR \', \'
    ) AS card_types, 
    GROUP_CONCAT(
        DISTINCT card_variant_type.name ORDER BY card_variant_type.name ASC SEPARATOR \', \'
    ) AS card_variant_types, 
    expansion.name AS expansion_name, 
    expansion.symbol_file_path AS expansion_symbol_file_path, 
    card.number, 
    card_rarity.name AS rarity_name, 
    language_has_card_rarity.symbol_file_path AS rarity_symbol_file_path, 
    card_variant.image_file_path 
FROM card 
INNER JOIN card_variant ON card.id = card_variant.card_id 
INNER JOIN card_variant_has_card_variant_type ON card_variant.id = card_variant_has_card_variant_type.card_variant_id 
INNER JOIN card_variant_type ON card_variant_has_card_variant_type.card_variant_type_id = card_variant_type.id 
INNER JOIN expansion ON card.expansion_id = expansion.id 
INNER JOIN card_rarity ON card.card_rarity_id = card_rarity.id 
INNER JOIN language_has_card_rarity ON card_rarity.id = language_has_card_rarity.card_rarity_id 
GROUP BY card_variant.id;

Ik ben niet erg bekend met group_concat dus het kan zijn dat ik daar een foutje in heb gemaakt, maar het idee is duidelijk

---

Als je toch aanvullende selectiecriteria nodig hebt zou je een EXISTS kunnen toevoegen. Die wordt in MySQL met een ander execution plan uitgevoerd dan een LEFT JOIN / NOT IN. Dat kan ook wel eens beter zijn voor je performance (of slechter).

code:

... AS b
WHERE EXISTS (SELECT 1 FROM foo AS a WHERE a.id = b.id)
...

Leesvoer:
https://explainextended.c...-left-join-is-null-mysql/

[ Voor 8% gewijzigd door CurlyMo op 05-05-2016 21:45 ]

[Rannasha]

CurlyMo schreef op donderdag 05 mei 2016 @ 15:21:
Wat ik niet goed snap is dat je allerlei tabellen joined die vervolgens niet terugkomen in de te selecteren kolommen en niet nodig zijn om voor bepaalde tabellen te koppelen. Naar mijn idee kunnen de volgende tabellen dus uit je query:
- expansion_series
- language
- card_has_energy_type
- energy_type
- pokemon_stage
- card_effect
- card_attack
- card_attack_has_energy_type
- energy_type AS attack_energy_type
- card_has_weakness
- energy_type AS weakness
- card_has_resistance
- energy_type AS resistance
- card_has_card_illustrator
- card_illustrator

Dat scheelt alweer 16 joins.

De TS gaf aan dat hij de WHERE clause weg liet omdat deze geen relevante info zou bevatten. Ik ga er van uit dat in de WHERE clause wel gebruik gemaakt wordt van de tabellen die gejoined worden.

[CurlyMo]

Rannasha schreef op vrijdag 06 mei 2016 @ 11:46:
[...]


De TS gaf aan dat hij de WHERE clause weg liet omdat deze geen relevante info zou bevatten. Ik ga er van uit dat in de WHERE clause wel gebruik gemaakt wordt van de tabellen die gejoined worden.

Vandaar dat ik alternatieven aangeef in mijn tweede deel van mijn post. Nu gooit TS alles in een grote bulk aan joins, terwijl in een EXISTS of IN efficiënter kan zijn.

[egonolieux]

Gomez12 schreef op woensdag 04 mei 2016 @ 23:18:
Persoonlijk zou ik toch eens goed naar je db-ontwerp kijken want volgens mij heb je het ietwat te ver genormaliseerd...

Maar ok, dat wens je niet.
Dan zou ik zeggen :
- Explain nakijken
- Analyse tables draaien (zo heet dat toch in mysql om je statistieken bij te werken)
- Een aantal dingen in sub-query's stoppen zodat ze eerder afgestopt kunnen worden. Bijv is de hele join van regel 39 nog wel van toepassing als regel 38 al een null geeft?
- Maar sowieso group by fixen, nu geef je de optimizer maar halve info om mee te werken, geef hem alle info.

De meeste tabellen zijn eigenlijk maar simpele lookup tables die een tiental rijen bevatten.

Wat GROUP BY in MySQL betreft heb ik wat tests uitgevoerd met en zonder de "nodige kolommen": de kolommen weglaten is tot 10% sneller. Mijn bevinding wordt ook door anderen bevestigd: http://stackoverflow.com/questions/5986127/do-all-columns-in-a-select-list-have-to-appear-in-a-group-by-clause. Akkoord, het is niet "volgens de SQL" standaarden, maar MySQL laat het toe en het blijkt sneller te zijn. Ik ga de performance niet verder verlagen om aan bepaalde "officiele standaarden" te voldoen.

storeman schreef op donderdag 05 mei 2016 @ 11:14:
Ik zou gaan kijken naar de-normalisatie met bijvoorbeeld een materialized view. Misschien kun je de data op een logische manier terugbrengen naar 1, 2, 3 of 4 materialized views. Dat scheelt natuurlijk al flink wat joins. Een paar indexes erop en je moet toch behoorlijk snel kunnen zoeken.

Join je altijd op dezelfde tabellen? Of doe je dit ook dynamisch adhv de gevraagde filters? Je zou namelijk ook een right-join kunnen doen. Dan gooi je al in een eerder stadium rijen weg, dit kan een behoorlijk verschil maken in performance. Had gisteren nog een query die hierdoor een factor 8 sneller werd (0.08 naar 0.01ms). Uiteindelijk niet bruikbaar omdat er te veel rijen werden weggegooid (had met de count ook 0 waardes nodig voor elk weeknummer), maar misschien is dit bij jou wel in te passen.

Zoals ik eerder reeds vermeld heb is denormaliseren niet echt aan de orde. De database is niet applicatiespecifiek. Waar mogelijk heb ik reeds het aantal tabellen beperkt door nullable kolommen te gebruiken. Materialized views worden niet ondersteund in MySQL als ik me niet vergis, tenzij op een omslachtige omweg.

Rannasha schreef op vrijdag 06 mei 2016 @ 11:46:
[...]

De TS gaf aan dat hij de WHERE clause weg liet omdat deze geen relevante info zou bevatten. Ik ga er van uit dat in de WHERE clause wel gebruik gemaakt wordt van de tabellen die gejoined worden.

Correct. De kolommen in de SELECT worden gebruikt om de resultaten re visualiseren, maar er wordt ook gefilterd op meerdere kolommen die niet getoond worden.

Aangezien er reeds indexes gebruikt worden en de snelheid niet naar mijn wensen is, zal ik eens experimenteren met eerdere suggesties als alternatief voor joins. Ik moet toegeven dat mijn SQL kennis relatief basis is, dus ik ga dit eerst even nader bestuderen.

Met Apache Solr en dergelijke zoekmachines heb ik geen ervaring, maar ik ga dit zeker eens bekijken.

[RobIII]

Als het kleine lookup tabellen zijn moeten ze makkelijk in het geheugen passen en al helemaal geen probleem zijn. Laat anders http://mysqltuner.com/ eens los op je DB?

[CurlyMo]

En wat als je dus die ene selectie in een subquery stopt?
En wat als je die andere left joins in een exists stopt?

[Montaner]

Views gebruiken? Of een genormaliseerde tabel?

[Gomez12]

trix0r schreef op maandag 09 mei 2016 @ 11:13:
Views gebruiken? Of een genormaliseerde tabel?

Hoe wil je dit nog meer normaliseren?

[Montaner]

Gomez12 schreef op maandag 09 mei 2016 @ 11:25:
[...]

Hoe wil je dit nog meer normaliseren?

"Typo" ... gedenormaliseerde tabel maken. Aangezien het hier volgens mij om vrij statische data gaat (hoevaak komen er enkele nieuwe kaarten bij?) kan je best eens in de zoveel tijd alles verladen naar een andere tabel/db.

[Verwijderd]

JOINS vormen nagenoeg nooit echte performance problemen als je al de goede indexes op je tabellen hebt zitten.
Je kunt dit controleren door EXPLAIN te gebruiken.

Een goede truc om te checken wat het probleem van je query veroorzaakt is om 1 voor 1 alle joins weg te halen (en bijbehorende select velden) en dan te checken of er 1 is die de query erg langzaam maakt.
De snelheid van je query kun je checken door hem te runnen in een client, bv phpmyadmin of SequelPro als je een mac hebt.

Het zelfde kun je door voor de items in je SELECT. Haal ze 1 voor 1 weg en kijk wat het doet met de snelheid van je query.

Success!

[sig69]

Een goede truc om te checken of het zin heeft om te reageren is kijken naar de datum van de laatste reactie 😃