Queries optimaliseren, veel developers zien het als een straf. Het kan nochtans eenvoudig zijn, maar zoals bij alles moet je eerst begrijpen wat er gebeurt vooraleer je kan gaan ingrijpen.

Om te begrijpen hoe MySQL een query behandelt kan je het EXPLAIN-statement gebruiken. Met behulp van dit statement kom je heel wat te weten.

Een voorbeeld:

EXPLAIN SELECT d.id, d.profile_id, ...
FROM designs AS d
INNER JOIN templates AS t ON d.template_id = t.id
INNER JOIN profiles AS p ON d.profile_id = p.id 
WHERE d.active = 'Y' AND d.offline = 'N' AND d.template_id > 0 AND (d.date_expire IS NULL OR d.date_expire > NOW());

Als resultaat krijg je iets zoals hieronder:

id	select_ type	table	type	possible_ keys	key	key_ len	ref	rows	Extra
1	simple	t	range	primary	primary	4	null	4	Using where; Using index
1	simple	d	all	null	null	null	null	585	Using where
1	simple	p	eq_ref	primary, idx_id_active	primary	4	db.d. profile_id	1	Using index

In deze tabel zijn er verschillende kolommen met elk hun eigen betekenis, hieronder bespreken we de relevante kolommen. Meer informatie over alle kolommen kan je terugvinden op dev.mysql.com.

select_type

Het gebruikte SELECT-type. Mogelijke waardes zijn:

• SIMPLE

  Zoals het woord als zegt: een eenvoudige SELECT.

• PRIMARY

  Omringende SELECT-query.

• UNION

  Tweede of laatste SELECT in een query die gebruik maakt van UNION.

• DEPENDANT UNION

  Tweede of laatste SELECT in een query die gebruikt maakt van UNION en afhankelijk is van de omringende query.

• UNION RESULT

  Resultaat van de UNION.

• SUBQUERY

  Eerste SELECT in de subquery.

• DEPENDANT SUBQUERY

  Eerste SELECT in de subquery en afhankelijk van de omringende query.

• DERIVED

  Afgeleide tabel (maw: er staat een subquery in de FROM-clause).

• UNCACHEABLE SUBQUERY

  Subquery waarvan iedere rij moet worden geherevalueerd.

• UNCACHEABLE UNION

  Tweede of laatste SELECT in een query die gebruikt maakt van een UNION in de subquery.

table

De gebruikte tabel.

type

Het type van JOIN, er zijn verschillende waardes mogelijk.

De verschillende types worden hieronder aflopend gesorteerd. De volgorde van de MySQL manual wordt gerespecteerd, en deze omschrijft de volgorde als 'gesorteerd van het beste type naar het slechtste'. In de praktijk is deze volgorde niet van 'snel naar traag', gezien dit alles afhangt van wat je exact verwacht van je query.

• system

  Indien de tabel maar 1 rij bevat.

• const

  Je vergelijkt met een constante en is daarom snel.

  SELECT * FROM table WHERE id = 1;

• eq_ref

  Er is maar 1 rij uit deze tabel die past bij (de combinatie van) de vorige rijen. Hier gaat het dus om een PRIMARY KEY of UNIQUE index in de tabel die wordt gejoined.

  SELECT t1.*, t2.* FROM table1 AS t1 INNER JOIN table2 AS t2 ON t2.id = t1.fk;

• ref

  Bij (de combinatie van) de vorige rijen kunnen meerdere rijen van deze kolom matchen omdat de kolom waarop de tabellen gejoind worden geen unieke waarden bevat.

  SELECT * FROM table1 AS t1 INNER JOIN table2 AS t2 ON t2.not_unique = t1.fk;

• fulltext

  In tegenstelling tot bovenstaande query-types wordt hier niet gezocht op een exacte waarde in je dataset, maar wordt een match gedaan op de volledige inhoud van je rij om te bekijken of de waarde voorkomt en hoe vaak deze voorkomt.

  Hierbij worden intern ook enkele speciale handelingen uitgevoerd: zo worden bijvoorbeeld zoekwoorden die in meer dan 50% van de rijen voorkomen of taal-specifieke stopwoorden (zoals some, then, …) niet meegerekend.

  Met 'IN NATURAL LANGUAGE MODE' zal het nodig zijn een FULLTEXT index aan te maken op de columns die je wenst te doorzoeken.
  Bij 'IN BOOLEAN MODE' is dit niet nodig (opzoeken zal hierdoor wel trager gaan), en kunnen ook speciale operators meegegeven worden om heel specifiek te gaan zoeken. Deze zal uiteraard om deze reden ook nog trager zijn dan de NATURAL LANGUAGE MODE omdat dit een veel complexere operatie kan worden.

  SELECT * FROM table WHERE MATCH(column) AGAINST('search');

• ref_or_null

  Idem als 'ref', maar hier wordt ook gecontroleerd op null-waarden.

  SELECT * FROM table1 AS t1 LEFT OUTER JOIN table2 AS t2 ON t2.not_unique = t1.fk;

• index_merge

  Dit type geldt voor queries waarbij het resultaat de uitkomst is van meerdere verschillende selecties (typisch bij OR). Intern worden deze verschillende selecties apart uitgevoerd en de resultaten hiervan worden gecombineerd weergegeven als omvattende resultset die voldoet aan de volledige expressie.

  SELECT * FROM table WHERE column1 = X OR column2 = Y;

• unique_subquery

  De kolommen die gematcht worden zijn het resultaat van een subquery die unieke kolommen geeft.

  SELECT * FROM table1 WHERE column IN (SELECT id FROM table2);

• index_subquery

  Dit type is gelijk aan unique_subquery, behalve dat in plaats van een subquery met unieke waarden, deze werkt met niet-unieke kolommen.

  SELECT * FROM table1 WHERE column IN (SELECT not_unique FROM table2);

• range

  Enkel rijen binnen een bepaalde range van een index moeten worden onderzocht, de gebruikte index kan je terugvinden in de key-kolom.

  SELECT * FROM table WHERE id BETWEEN 10 AND 20;
  SELECT * FROM table WHERE id IN (10, 11, 12);

• index

  Idem als ALL (zie onder), maar enkel de index moet onderzocht worden.

  SELECT * FROM table WHERE id = 1;

• ALL

  De volledige tabel moet onderzocht worden. Indien dit voorvalt los je dit best op.

possible_keys

Een oplijsting van de indexen die MySQL kan gebruiken. Dit wil niet zeggen dat er één zal gebruikt worden.

Indien deze kolom leeg is, dan zijn er geen bruikbare indexen gevonden. Het kan dan nuttig zijn om op basis van de WHERE-clause een toepasselijke index te maken.

key

Hier kan je zien welke key (index) gebruikt is.

ref

Hier kan je zien welke kolom vergeleken werd met de gebruikte index.

rows

Het (geschatte) aantal rijen dat MySQL zal onderzoeken.

Als je gebruik maakt van JOINS dan moet je de resultaten in deze kolom met elkaar vermeningvuldigen, zo bekom je het totale aantal rijen. Het spreekt vanzelf dat een lager totaal een snellere query oplevert.

extra

In deze kolom kan je informatie vinden over hoe MySQL de query oplost. Mogelijke waardes zijn:
(enkel relevante waardes zijn vermeld, meer op: http://dev.mysql.com/doc/refman/5.1/en/using-explain.html)

• Distinct

  MySQL zoekt naar unieke waardes.

• Impossible WHERE noticed after reading const tables

  Indien de WHERE-clause altijd false oplevert.

• No tables

  Indien de query geen FROM-clause heeft.

• Using filesort

  Om de rijen correct te sorteren moet MySQL de gevonden resultaten opnieuw overlopen.

• Using index

  Er is een enkel een index gebruikt om de rijen te selecteren.

• Using temporary

  Om de query op te lossen moet een tijdelijke tabel gemaakt worden. Dit valt meestal voor indien je GROUP BY of ORDER BY gebruikt.

• Using where

  Om de query op te lossen is de WHERE-clause gebruikt. Indien dit niet voorkomt is er ofwel iets mis met je query of onderzoek je bewust alle rijen van de tabel.
  Let wel: dit kan een vertraging aanduiden als er veel data is.

Zoals je kan afleiden uit bovenstaande lijst vermijd je best 'Using filesort' en 'Using temporary', dit zijn de twee gevallen die een query traag maken.

Wat is een index?

Om een query te optimaliseren moeten we natuurlijk begrijpen wat een index is.

Zie een index als een inhoudsopgave van een boek. Door een inhoudsopgave kan je snel naar een bepaald onderwerp in het boek gaan zonder alle pagina's te moeten lezen.

MySQL gebruikt de index in een aantal situaties:

• zoeken van rijen
• elimineren van niet-relevante rijen, indien er meerdere mogelijke indexen zijn zal MySQL deze nemen met de minste rijen
• om verschillende tabellen te JOINEN
• minimum- en maximumwaardes ophalen
• sorteren van resultaten

Zonder indexen moet MySQL de volledige tabel gaan overlopen om resultaten te vinden, met een index hoeft MySQL enkel het relevante stuk te doorlopen.

Je kan nu denken dat je op iedere kolom en op elke mogelijke combinatie een index kan leggen. Dit is echter geen goed idee - en ook niet mogelijk - want een index wordt bij iedere INSERT, UPDATE, DELETE herberekend, waardoor deze statements dan weer trager worden. Hoe meer indexen hoe trager het geheel, hou dit dus ook in het achterhoofd bij het optimaliseren van een query.

Bij het maken van een index kan een type meegeven. Iedere type heeft zijn eigen eigenschappen.

• PRIMARY KEY

  De combinatie van velden is uniek doorheen de tabel, per tabel kan er maximaal 1 PRIMARY KEY zijn.

• UNIQUE

  De combinatie van velden is uniek doorheen de tabel.

• INDEX

  In tegenstelling tot een UNIQUE index hoeft de combinatie van velden niet uniek te zijn.

• FULLTEXT

  Een speciaal type van index dat gebruikt word voor full-text-searches. (enkel beschikbaar voor MyISAM-tabellen)

Je kan een index aanmaken met volgende MySQL-statement:

ALTER TABLE table1 ADD [type] (column, column);

Hoe kies je nu een juiste index?

Vooraleer je indexen begint aan te brengen moet je nadenken of het wel nut heeft. Is het een tabel met weinig data, dan kan het zijn dat de query sneller is als alle rijen worden overlopen. Is het een query die weinig gebruikt wordt? Dan kan je je beter concentreren op queries die veel worden uitgevoerd (bijvoorbeeld bij iedere klik).

Een klein rekenvoorbeeld: je hebt een query die 1 maal per dag word uitgevoerd en 10 seconden duurt, door middel van indexes kan je die terugbrengen naar 2 seconden, dan heb je 8seconden gewonnen.

Stel nu dat er een query is die 1000 keer per dag wordt uitgevoerd en die 1 seconden duurt en je kan optimaliseren tot 200ms dan win je 1000 x 800ms en dus in totaal 8000 seconden.

Opmerking: Dit is een theoretisch voorbeeld.

Een goede index zorgt ervoor dat MySQL de WHERE-clause zo snel mogelijk kan oplossen. Als je bijvoorbeeld e.active = 'Y' AND e.hidden = 'N' in je query hebt staan dan is het slim om een index op active en hidden te leggen.

Je kan eigenlijk volgende leidraad gebruiken: eerst de kolommen in de WHERE-clause, dan de kolommen in de SELECT-clause.

Opmerking: Dit is een leidraad, geen vaststaand feit.

Als we een aantal indexen leggen en onze eerste query terug uitvoeren komen we op volgend resultaat:

id	select_ type	table	type	possible_ keys	key	key_ len	ref	rows	Extra
1	simple	t	range	primary	primary	4	null	4	Using where; Using index
1	simple	d	ref	idx_designs_products	idx_designs_products	4	db.t.id	37	Using where
1	simple	p	eq_ref	primary, idx_id_active	primary	4	db.d. profile_id	1	Using index

Andere voorbeelden om queries te optimaliseren

STRAIGHT_JOIN

Bij het uitvoeren van een query met JOINS over verschillende tabellen, maakt MySQL zelf een query plan. Dit bepaalt in welke volgorde de tabellen worden geprocessed en gejoined.

Dit zal more often than not de meest effeciënte manier van joinen zijn, echter niet altijd. Het kan natuurlijk altijd voorkomen dat juist die ene vaak gebruikte query doorheen een grote dataset door MySQL net niet goed genoeg geïnterpreteerd wordt, met een groot prestatieverlies als gevolg. Dit kan je oplossen met STRAIGHT_JOIN.

Door middel van SRAIGHT JOIN kan je zelf bepalen in welke volgorde je tabellen zullen worden gejoined, of eerder: MySQL zal je tabellen van links naar rechts joinen in plaats van zelf de volgorde te beslissen. Met dan zorgvuldig zelf de tabellen in de juiste volgorde neer te poten, kan je sommige queries efficiënter maken.

SELECT STRAIGHT_JOIN *
FROM table1 AS t1
INNER JOIN table2 AS t2 ON t1.col = t2.col;

Werken met DATETIME

In veel CMS-en is er de mogelijkheid om een publicatie-datum van een artikel aan te geven. Dit is een potientieel risico voor trage queries.

Omdat een tijdstip iedere seconde anders is, kan de query maximaal 1 seconde gecached worden.

SELECT e.*, m.*
FROM blog_entries AS e
INNER JOIN meta AS m ON e.meta_id = m.id
WHERE e.active = 'Y' AND e.hidden = 'N' AND e.date_publish < NOW()
ORDER BY e.date_publish DESC;

Het is overbodig om de gebruiker tot op de seconde te laten specifieren wanneer een een artikel moet getoond worden. Je kan er dus beter voor zorgen dat bij het opslaan de seconden op 00 staan. En je query herschrijven als volgt (uiteraard met het correcte tijdstip):

SELECT e.*, m.*
FROM blog_entries AS e
INNER JOIN meta AS m ON e.meta_id = m.id
WHERE e.active = 'Y' AND e.hidden = 'N' AND e.date_publish < "2009-11-03 13:37:00"
ORDER BY e.date_publish DESC;

Hierdoor kan je query maximaal 1 minuut gecached worden. Je zou natuurlijke en stap verder kunnen gaan en enkel kwartieren kunnen toelaten.

ORDER BY RAND()

Als we onze server-admins mogen geloven dan sterft er iedere keer je ORDER BY RAND() gebruikt een klein schattig konijntje. ORDER BY RAND() is een heel intensieve operatie bij grote tabellen.

Je kan dit beter oplossen in je code, bijvoorbeeld met de PHP-functie shuffle.

Heb je echter een slechts een kleine result-set nodig die random moet gegenereerd worden uit een grote hoeveelheid data, dan biedt shuffle natuurlijk ook geen goed resultaat, omdat je dan die grote hoeveelheid data (even) in het geheugen zal moeten dulden, terwijl je slechts een kleine hoeveelheid data hiervan nodig hebt. Hoe kan je dit beter?

// get the maximum ID
$max = 'SELECT MAX(id) FROM table;';
// do n queries, where n is the number of rows to retrieve
// this should be more effective then one ORDER BY RAND()-query
for ($i = 0; $ < [totaal-aantal-benodigde-random-rijen]; $i++) {
	get random row
	$results[$i] = 'SELECT  * FROM table LIMIT '. rand(0, $max) .', 1;';
}

Waarom is dit nu efficienter dan ORDER BY RAND()? Wel, voor iedere rij in de tabel moet bij ORDER BY RAND() een willekeurig getal worden gegenereerd, hierna moet er op al deze willekeurige getallen ook nog gesorteerd worden.

Disclaimer: zie deze blogpost niet als de heilige graal om je website met tienduizende bezoekers te gaan optimaliseren. Voor zo'n situaties kan je beroep doen op DB-admins, deze kunnen op basis van je applicatie en logs gaan kijken wat er specifiek moet geoptimaliseerd worden.

Bronnen: MySQL, Openminds, PHPhulp, #netlash, Learning MySQL, Web Database Application with PHP & MySQL.