[G_PRO] Databanken
  • Welkom
  • Inleiding
    • Benodigdheden
    • Nuttige extra's
    • Mee helpen?
  • Semester 1: databanken intro
    • Installatie van alle software
    • Databanken
      • Inleiding
      • Voorbeeld
      • Wat is een relationele databank
      • Basisstructuren van een relationele databank
      • Diagramnotatie
    • (My)SQL
      • DDL
        • Datatypes
          • Enum
        • CREATE
        • ALTER
        • DROP
        • Sleutels voor identificatie
        • Primaire sleutel toevoegen/verwijderen
        • Primaire sleutel in een nieuwe tabel
        • Vreemde sleutels
        • Relaties-voorstellen
        • Labo
      • DML
        • INSERT
        • UPDATE
        • DELETE
        • LIKE
        • Logische operatoren
        • Vergelijkingen
        • SELECT sorteren
        • Functies
        • Labo
      • SELECT
        • SELECT met clausules
        • SELECT met WHERE
      • Groeperen en samenvatten
        • Aggregaatfuncties
        • SELECT met GROUP BY
        • SELECT met HAVING
        • IN
        • BETWEEN
        • SELECT DISTINCT
        • Labo
      • JOINS
        • JOINs bij simpele relaties
        • JOINs via tussenliggende tabel
        • UPDATE van JOIN
      • apTunes project
  • PRO (GEEN LEERSTOF EN/OF IN OPBOUW)
    • PRO
      • Waarom een databank?
      • Een databank ontwerpen
      • ERM
        • Basisbegrippen
        • Constraints (voorwaarden, beperkingen)
        • Keys (Sleutels)
        • Discussiepunten bij ontwerp in ERM
        • Aanvullende begrippen
        • Herleiden van ERD's tot tabellen
        • Voorbeeld
        • Bibliografie
        • Labo oefeningen
      • NoSQL
      • Blockchain
  • SEMESTER 2: DATABANKEN
    • Join
      • INNER JOIN
      • LEFT JOIN
      • COALESCE
      • RIGHT JOIN
      • LEFT EXCLUDING JOIN
      • RIGHT EXCLUDING JOIN
      • OUTER JOIN
      • OUTER EXCLUDING JOIN
      • Samenvattende flowchart
      • Labo oefeningen
    • Views
      • CREATE
      • ALTER
      • DROP
      • UPDATE
      • RENAME
      • BESLUIT
      • Labo oefeningen
    • Subqueries
      • Een onafhankelijke subquery
      • Subqueries met tijdelijke opslag
      • Labo oefeningen
    • Indexeren
      • mogelijkheden
      • CREATE
      • DROP
      • UNIQUE
      • trage queries opvolgen
    • ERD
      • Referentiële beperkingen
      • Overzicht
      • Labo oefeningen
    • Stored programs
      • Stored procedures
      • CREATE
      • DROP
      • DELIMITER
      • Variabelen
      • Parameters
      • Gerelateerde data invoegen
      • IF -THEN-ELSE-ELSEIF
      • LOOP
      • WHILE
      • REPEAT
      • SIGNAL
      • Error handling
      • RESIGNAL
      • Stored functies
      • Triggers
      • Cursors
      • Transacties en rollbacks
      • Object access control
      • Oefeningen basisgebruik stored procedures
      • Oefeningen control flow
      • Oefeningen stored functions
      • Oefeningen triggers
      • Oefeningen error handling
      • Oefeningen transacties en access control
Powered by GitBook
On this page
  • Stap 1: bijhouden
  • Stap 2a: uitpluizen zonder MySQL Workbench
  • Stap 2b: uitpluizen met MySQL Workbench
  • Stap 3: algoritme om te optimaliseren voor een query

Was this helpful?

Export as PDF
  1. SEMESTER 2: DATABANKEN
  2. Indexeren

trage queries opvolgen

PreviousUNIQUENextERD

Last updated 2 years ago

Was this helpful?

Stap 1: bijhouden

Met de juiste instellingen kan je MySQL queries laten onthouden die langer duren dan een zekere drempelwaarde. Als het gaat om queries die regelmatig terugkomen, is het de moeite waard indexen aan te maken die deze queries kunnen versnellen.

Je ziet in MySQL Workbench hoe lang het duurt om een query uit te voeren:

Je kan de trage queries ook opslaan door in het bestand my.cnf in je MySQL-installatie de variabele slow_query_log op On te zetten, long_query_time op het aantal seconden dat "te veel" is in te stellen (bv. 0.2 voor 200 milliseconden) en slow_query_log_file in te vullen met de locatie van het gewenste logbestand. Alle queries die je vanaf dan uitvoert zullen in het logbestand worden bijgehouden.

Stap 2a: uitpluizen zonder MySQL Workbench

Je voert de queries in kwestie opnieuw uit, maar je laat ze voorafgaan door het sleutelwoordje EXPLAIN. Je krijgt dan één rij per doorzochte tabel in een uitgebreide versie van volgend formaat:

table

key

rows

naam van een doorzochte tabel

eventueel gebruikte index

aantal rijen doorzocht

naam van een andere doorzochte tabel

eventueel gebruikte index

aantal rijen doorzocht

Als er voor een index NULL staat, betekent dat dat alle rijen van de eerste tot de laatste zijn beschouwd, wat typisch erg inefficiënt is (zeker als het aantal rijen groot is). Door een index toe te voegen aan een kolom met een gezochte waarde (bv. een kolom vermeld in een WHERE), kan je de NULL wegwerken en het aantal doorzochte rijen drastisch verlagen.

Stap 2b: uitpluizen met MySQL Workbench

Volgende werkwijze is alleen mogelijk als je script één statement bevat. Laat in dit geval USE <naam-van-je-databank>; achterwege, want dat is op zich al een statement.

MySQL Workbench heeft ook een grafische weergave van je uitvoeringsplan. Deze kan je bekijken na het uitvoeren van je statement via onderstaande knop:

Als je op een element van het diagram gaat staan met je muis, krijg je ook een technische uitleg over waarom dat onderdeel van je query waarschijnlijk niet efficiënt is. Let op! Het betreft hier nog steeds vuistregels. Een "full table scan" is bijvoorbeeld geen ramp als je tabel erg klein is. Dat wordt ook vermeld in de kleine lettertjes.

Stap 3: algoritme om te optimaliseren voor een query

Als je een bepaalde SELECT-query wil optimaliseren, kan je onderstaand stappenplan volgen:

  1. Als er een WHERE voorkomt in je SELECT, voorzie dan één index op alle kolommen samen die via AND verbonden zijn en die rechtstreeks vergeleken worden met een constante waarde.

  2. Voeg aan je index ook de eerste van de volgende mogelijkheden toe die je hieronder krijgt.

    a. Een kolom die niet rechtstreeks wordt vergeleken met een waarde, maar wel in een bereik moet liggen (via BETWEEN, >, <, LIKE waarbij het eerste karakter geen wildcard is)

    b. Alle kolommen die in een GROUP BY worden gebruikt, in de volgorde waarin ze in de GROUP BY worden vermeld

    c. Alle kolommen die in een ORDER BY worden gebruikt, in de volgorde waarin ze in de ORDER BY worden vermeld, maar enkel als er geen mix van ASC en DESC voorkomt in de ORDER BY

Als er staat dat een kolom rechtstreeks vergeleken wordt met een waarde, betekent dat bijvoorbeeld dat er staat WHERE MYCOLUMN = .... Wat niet zou gaan is WHERE RIGHT(MYCOLUMN,3) = ... omdat we daar de kolom eerst verder verwerken en dan pas vergelijken. Dan heeft een index op de kolom geen zin.

Als je op deze knop klikt, krijg je een diagram waarin wordt uitgelegd welke stappen nodig zijn om je query uit te voeren. Een volledig overzicht van de betekenis van de elementen in zo'n diagram vind je . Je hoeft niet elk onderdeel te kennen, maar bekijk de pagina kort. Belangrijk is dat je rode boxes met daarin "full table scan" zo veel mogelijk vermijdt, zeker als het gaat om tabellen met veel data in.

Bovenstaand stappenplan is afkomstig uit . Dit is een zeer nauwe verwant van MySQL. Als je de pagina bekijkt, zie je dat het laatste woord over indexen nog niet gezegd is. Voor deze cursus volstaat het algoritme, maar als je ooit in je carrière geconfronteerd wordt met performantieproblemen in een database, raadpleeg dan deze uitleg voor je nieuwe (virtuele) hardware aankoopt!

hier
de documentatie van MariaDB
De eerste timing drukt uit hoe lang het duurt om de query uit te voeren. De tweede drukt uit hoe lang het duurt om de rsultaten op te halen en wordt niet verbeterd door een index.