[MySQL] Performanceproblemen join tussen grote tabellen

[JeRa]

Ik heb twee redelijk grote tabellen:

Tabel A (honderdduizenden rows):

id	UNSIGNED MEDIUMINT	PRIMARY
b_id	UNSIGNED INT	INDEX, FK -> b.id

Tabel B (miljoenen rows):

id	UNSIGNED INT	INDEX
value	UNSIGNED INT	INDEX

Eigenlijk heb ik nog veel meer tabellen, maar dit is de essentie van het probleem. Ik ben bezig met een zoekmachine die ik een uniforme query wil laten genereren die MySQL aan de hand van de indices en hun cardinaliteit zal gaan moeten optimaliseren. Probleem: MySQL faalt af en toe in het optimaliseren waardoor ik genoodzaakt wordt om heel lelijke queries te schrijven om het toch nog snel te krijgen. Vanzelfsprekend laat ik regelmatig de tabellen analyzeren om de verspreiding van de indices bij te werken.

Eerste voorbeeld waarbij het foutgaat:

SELECT * FROM a INNER JOIN b ON a.b_id = b.id ORDER BY b.value ASC LIMIT 0, 30;

De snelle manier: sorteer tabel b aan de hand van de index op value, en zoek vervolgens de juiste records terug uit tabel a en elimineer alle rows uit b die niet terug te vinden zijn totdat je 30 records hebt.

MySQL bedacht echter dit: maak een temporary table met alle records uit tabel a gejoined met b, sorteer deze vervolgens en pak de eerste 30 records. De enige manier waarop ik MySQL kan forceren dit gedrag te vermijden is dit:

SELECT STRAIGHT_JOIN * FROM b INNER JOIN a ON b.id = a.b_id ORDER BY b.value ASC LIMIT 0, 30;

Tweede voorbeeld waarbij het foutgaat:

SELECT * FROM a
INNER JOIN b b1 ON a.b_id = b1.id
INNER JOIN b b2 ON a.b_id = b2.id
WHERE b1.value = constante1 AND b2.value = constante2

De snelle manier: pak alle records uit tabel b waar value gelijk is aan constante1 en vervolgens aan constante2. Dit gaat snel vanwege de index. Laat deze gegevens intersecten op basis van b.id en neem alle waardes voor b.id waar ze in allebei de recordsets voorkomen. Join vervolgens tabel a op deze waardes door middel van a.b_id, wat ook snel gaat vanwege de index.

MySQL doet echter: 47 seconden over deze join? Terwijl ik zelf, met bovenstaande methode, véél sneller resultaat heb.

Waarom doe je het dan niet op de 'snelle manieren' zoals je boven beschreven hebt?

Dit zijn maar twee voorbeeldjes waar in werkelijkheid een legio van dit soort problemen opduikt. Ik wil op de één of andere manier MySQL duidelijk maken hoe het moet, zonder voor elk specifiek probleem een aparte query te schrijven. Maar hoe?

[Gomez12]

JeRa schreef op dinsdag 22 augustus 2006 @ 21:43:
...
Dit zijn maar twee voorbeeldjes waar in werkelijkheid een legio van dit soort problemen opduikt. Ik wil op de één of andere manier MySQL duidelijk maken hoe het moet, zonder voor elk specifiek probleem een aparte query te schrijven. Maar hoe?

Simepl gezegd : Jij wilt voor een generieke oplossing creeeren die net zo snel is als jouw specifieke oplossing per query ( oftewel jouw oplossingen zijn al uniek per probleem, een generieke oplossing is altijd trager )

2e simpele opmerking waarschijnlijk... Maar als ik jouw query's zie ( die uitgaan van a als hoofdtabel en b erbij joinen ) en jouw snelle oplossingen ( begin bij b en zoek a erbij ) zou ik zeggen gooi je hoofd-query om zodat b hoofdtabel wordt en a erbij gejoind wordt ( twijfelachtig hoeveel resultaat dit zal hebben, maar ja )

En ik denk inderdaad niet dat je eraan dat je eraan ontkomt om gewoon per situatie 1 nieuwe query te bouwen, want je situaties zijn nogal verschillend ( btw. waarom zijn de querys nu ook al verschillend??? ). Je eigen oplossingen zijn ook verschillend dus moeten je querys ook verschillend zijn.

Als je algemene oplossingen wil hebben moet je eens kijken naar stored querys, je bouwt er hier een paar van, je roept deze vrij algemeen op, en je bent alweer een heel stuk verder.

Maar gewoon heel simpel de slowquery log van mysql aanzetten, dan per langzame query gaan bekijken of het zinnig is om deze te herschrijven. Heb je veel dezelfde querys in code staan verplaats deze dan naar stored querys zodat je maar 1x je stored query hoeft bij te werken ipv 10x je code.

[JeRa]

Gomez12 schreef op dinsdag 22 augustus 2006 @ 22:36:
[...]

Simepl gezegd : Jij wilt voor een generieke oplossing creeeren die net zo snel is als jouw specifieke oplossing per query ( oftewel jouw oplossingen zijn al uniek per probleem, een generieke oplossing is altijd trager )

Het ligt ietsjes anders...ik ben van mening dat mijn specifieke oplossingen niet zó specifiek zijn dat de optimizer van MySQL er niet achter had kunnen komen. Mijn 'ideale' visie van een DBMS is: geef 'm een generiek probleem en laat 't 'm op z'n eigen manier oplossen zodat hij het snelst op de oplossing komt. De in de OP beschreven 'snelle' oplossingen waren methodes die ik na 2 seconden denken bedacht had, waardoor ik twijfels kreeg over mijn opzet. Echter zie ik niet waarom MySQL het niet zelf had kunnen bedenken.

2e simpele opmerking waarschijnlijk... Maar als ik jouw query's zie ( die uitgaan van a als hoofdtabel en b erbij joinen ) en jouw snelle oplossingen ( begin bij b en zoek a erbij ) zou ik zeggen gooi je hoofd-query om zodat b hoofdtabel wordt en a erbij gejoind wordt ( twijfelachtig hoeveel resultaat dit zal hebben, maar ja )

Ik kan tabel b wel hoofdtabel maken, maar zonder een STRAIGHT_JOIN zal MySQL ze gewoon weer omdraaien en maakt hij tabel a weer hoofdtabel. Echter kan ik die STRAIGHT_JOIN niet gebruiken in alle situaties, en soms zijn ook compleet andere oplossingen vereist.

En ik denk inderdaad niet dat je eraan dat je eraan ontkomt om gewoon per situatie 1 nieuwe query te bouwen, want je situaties zijn nogal verschillend ( btw. waarom zijn de querys nu ook al verschillend??? ). Je eigen oplossingen zijn ook verschillend dus moeten je querys ook verschillend zijn.

Die queries worden stap voor stap opgebouwd, op een zo logisch mogelijke manier. Tabel b is ondergeschikt aan tabel b in de hiërarchie en wordt dus ook altijd ná tabel a beschreven in de query. De JOINs en de WHERE-clausule worden dynamsch samengesteld door een stukje PHP.

Als je algemene oplossingen wil hebben moet je eens kijken naar stored querys, je bouwt er hier een paar van, je roept deze vrij algemeen op, en je bent alweer een heel stuk verder.

Dat is misschien nog een goeie oplossing, hoewel het voor mij nog steeds ver van wenselijk is. Ik vrees zelf dat ik voor bepaalde bewerkingen niet met alléén stored queries toekom en ook PHP erin moet gaan betrekken.

[Gomez12]

JeRa schreef op dinsdag 22 augustus 2006 @ 23:07:
[...]

Het ligt ietsjes anders...ik ben van mening dat mijn specifieke oplossingen niet zó specifiek zijn dat de optimizer van MySQL er niet achter had kunnen komen. Mijn 'ideale' visie van een DBMS is: geef 'm een generiek probleem en laat 't 'm op z'n eigen manier oplossen zodat hij het snelst op de oplossing komt. De in de OP beschreven 'snelle' oplossingen waren methodes die ik na 2 seconden denken bedacht had, waardoor ik twijfels kreeg over mijn opzet. Echter zie ik niet waarom MySQL het niet zelf had kunnen bedenken.

Misschien omdat het in 90% van de gevallen andersom sneller is, of omdat jouw herschrijvingen niet in alle situaties het goede antwoord geven ( in jouw geval wel, maar in alle gevallen??? ) ... Daarom biedt mysql ook mogelijkheden om om de optimizer heen te komen ( omdat deze nog wel eens fout kan gaan in specifieke situaties )

[...]

Die queries worden stap voor stap opgebouwd, op een zo logisch mogelijke manier. Tabel b is ondergeschikt aan tabel b in de hiërarchie en wordt dus ook altijd ná tabel a beschreven in de query. De JOINs en de WHERE-clausule worden dynamsch samengesteld door een stukje PHP.

Dus oftewel simpele conclusie de logische manier die je php produceert is niet goed. Want de manier die php creeert vind mysql totaal niet logisch ( / kan er niets mee ). Ga dan niet kijken naar mysql, maar ga de oplossing zoeken in de opbouw van je queries in php. Ik ging ervanuit dat er een aantal standaard querys waren waar php een aantal velden invulde, dan zou je naar mysql kunnen kijken. Maar als php zelf een complete "logische" query opbouwt dan moet je toch echt bij php zijn als mysql de query niet logisch vind...
Tip : Probeer je hele query in php op te bouwen, niet alleen je joins en wheres want waarschijnlijk komt hier je probleem door, je probeert een standaard query te veel uit te breiden met joins en where s waardoor mysql niet meer weet wat hij ermee aan moet. Het is 1x veel meer werk maar als je het goed opbouwt kan je er later veel plezier aan beleven.

[...]

Dat is misschien nog een goeie oplossing, hoewel het voor mij nog steeds ver van wenselijk is. Ik vrees zelf dat ik voor bepaalde bewerkingen niet met alléén stored queries toekom en ook PHP erin moet gaan betrekken.

Opmerking : Als je stored querys en php tegelijk nodig hebt ben je verkeerd bezig. De query optimizer van mysql kan hier niet meer mee overweg. Of je schrijft je stored querys generiek en filtert het laatste gedelte in php of je moet zelf een complete query-builder opbouwen ( aanrader, maar veel , veel werk ). Begin niet aan stored querys met php code erin, dan doe je met mazzel half werk.

[Voutloos]

Wat zegt EXPLAIN dan van je 2e voorbeeld?

[JeRa]

Gomez12 schreef op dinsdag 22 augustus 2006 @ 23:35:
Dus oftewel simpele conclusie de logische manier die je php produceert is niet goed. Want de manier die php creeert vind mysql totaal niet logisch ( / kan er niets mee ). Ga dan niet kijken naar mysql, maar ga de oplossing zoeken in de opbouw van je queries in php. Ik ging ervanuit dat er een aantal standaard querys waren waar php een aantal velden invulde, dan zou je naar mysql kunnen kijken. Maar als php zelf een complete "logische" query opbouwt dan moet je toch echt bij php zijn als mysql de query niet logisch vind...

Probleem is een beetje dat geen enkele vorm van query door MySQL als logisch wordt gezien, want hoe ik het ook wend of keer; MySQL zal altijd zèlf de (foutieve) volgorde van joinen bepalen

Tip : Probeer je hele query in php op te bouwen, niet alleen je joins en wheres want waarschijnlijk komt hier je probleem door, je probeert een standaard query te veel uit te breiden met joins en where s waardoor mysql niet meer weet wat hij ermee aan moet. Het is 1x veel meer werk maar als je het goed opbouwt kan je er later veel plezier aan beleven.

Ik denk dat het hier op neer gaat komen inderdaad

Voutloos schreef op woensdag 23 augustus 2006 @ 00:09:
Wat zegt EXPLAIN dan van je 2e voorbeeld?

EXPLAIN geeft dan niet aan dat ie geen gebruik kan maken van indices of dat ie temporary tables gaat gebruiken. Echter, als ik die query omschrijf naar een subquery:

SELECT *
FROM b b1
INNER JOIN (SELECT * FROM b WHERE value = constante2) b2
ON b1.id = b2.id
LEFT JOIN a
ON b1.id = a.b_id
WHERE b1.value = constante1

Dan geeft de EXPLAIN ongeveer hetzelfde aan, terwijl deze query een factor 5 sneller kan worden uitgevoerd.

[ Voor 19% gewijzigd door JeRa op 23-08-2006 00:21 ]

[Infinitive]

Misschien is het een overwegen waard om om te schakelen naar een andere DBMS, zoals PostgreSQL? Misschien dat deze beter in staat is om de queries te analyseren en optimaliseren. Waarschijnlijk dat er veel aan MySQL veranderd is de afgelopen jaren, maar een aantal jaar geleden was wat features betreft PostgreSQL toch duidelijk de meerdere over MySQL.

Maar bedenk wel dat het analyseren van een query door de DBMS ook zwaar is en bijvoorbeeld alleen het probleem van het vinden van een geschikte join-volgorde al computationeel zwaar is, zodat DBMSen bepaalde vuistregels en benaderingen gebruiken.