Øg performance med multi-INSERTS

Jagten på den pæne kode kan nogen gange forringe din sides performance. Her viser jeg, hvordan multi-INSERTs til din MySQL-database mindsker loadtiden markant.

Af Jens Just Iversen

Når man indsætter flere rækker i en MySQL-database, kan man indsætte flere datasæt med én forespørgsel til databasen.

I en rå SQL-streng ser det således ud:

INSERT INTO tabel (kol1, kol2) VALUES (‘a1’,’a2’),(‘b1’,’b2’),(‘c1’,’c2’);

Det er måske ikke nyt for dig, men grunden til, at jeg alligevel skriver om det, er, at jagten på den pæne kode med generiske funktioner ofte får os til at sidde med en række enkeltstående MySQL-inserts, når dagen er omme.

Det kan for eksempel være en funktion til at tilføje et nyt produkt eller en funktion til at dublere data fra en tabel til en anden. Har vi brug for flere inserts i den forbindelse, kaldes funktionen tilsvarende antal gange. Nemt og pænt.

Det kan også være, at man ikke regner med, at systemet vil vokse, og før man ved af det, er der mange brugere på og datamængden øget.

Nu er problemet i mellemtiden, at de mange forespørgsler til databasen øger loadtiden. Herunder viser jeg, hvor meget.

A & B test

For at illustrere performanceforskelle vil a.php indsætte 250 rækker med testdata via enkeltstående INSERT-forespørgsler, mens b.php vil indsætte de samme 250 rækker via en multi-INSERT forespørgsel.

a.php:

<?php

include 'connect_to_mysql.php';

// Fyld array med testdata
$rows = array();
for ($i = 1; $i <= 250; $i++) {
    $rows[] = array('varenummer' => ($i*1234), 'pris' => ($i+10));
}

// Prepare INSERT-sætningen
$stmt = $conn->prepare('INSERT INTO produkter(varenummer, pris) VALUES (?,?)');

// Loop alle rækker og indsæt data samt execute dem
foreach ($rows as $row) {
    $stmt->bind_param('ii', $row['varenummer'], $row['pris']);
    $stmt->execute();
}

Ovenstående script danner 250 rækker med test-data, hvorefter INSERT-sætningen prepares en gang, mens hver enkelt række bliver indsat og sendt til databaseserveren enkeltvis.

b.php:

<?php

include 'connect_to_mysql.php';

// Fyld array med testdata
$rows = array();
for ($i = 1; $i <= 250; $i++) {
    $rows[] = array('varenummer' => ($i*1234), 'pris' => ($i+10));
}

// Lav variabel med strengen '(?,?),(?,?),(?,?),(?,?),[...]' osv.
$values = array_fill(0,sizeof($rows),'(?,?)');

// Prepare INSERT-sætningen med mange datasæt
$stmt = $conn->prepare("INSERT INTO produkter(varenummer, pris) VALUES ".trim(implode(',',$values),','));

$types = '';
$paramValues = array();
foreach ($rows as &$row) {
    $types .= 'ii';
    $paramValues[] = &$row['varenummer'];
    $paramValues[] = &$row['pris'];
}

$params = array_merge(array($types), $paramValues);
call_user_func_array(array($stmt, 'bind_param'), $params);

$stmt->execute();

b.php er mindre læsbar, men til forskel for a.php, hvor INSERT-sætningen prepares for kun et datasæt, bliver det her prepared for alle datasæt, så sætningen kommer til at se således ud:

INSERT INTO produkter(varenummer, pris) VALUES (?,?),(?,?),(?,?),(?,?),(?,?),(?,?),[...]

I stedet for at indsætte en række af gangen kaldes bind_param kun en gang, hvor alt dataen er repræsenteret:

$stmt->bind_param('iiiiiiiiiiiiii[…]', $a1,$a2,$b1,$b2,$c1,$c2,[…]);

Til sidst kaldes execute(), som sender forespørgslen til databaseserveren.

Test resultater

Kørsel	a.php	b.php
1	546 ms	25 ms
2	987 ms	36 ms
3	836 ms	30 ms
4	850 ms	45 ms
5	765 ms	46 ms
Gennemsnit	796,8 ms	36,4 ms

Hvert script blev kørt 5 gange, og som det fremgår af tabellen, blev det samme data indsat i databasen markant hurtigere med multi-INSERTS.

Gennemsnittet af de 5 kørsler af b.php på 36,4 ms er dermed godt 20 gange hurtigere.

Scriptene er kørt på samme maskine, som MySQL-serveren køres på. Man kan forestille sig, at routing til en ekstern server derfor vil koste endnu mere loadtid.

Selvom b.php er 20 gange hurtigere end a.php, vil forskellen være mindre betydelig ved færre rækker.

Et script er dog sjældent så lille som ovenstående 2 tests. Der skal også køres andre operationer, og så kan loadtiden få en vigtigere betydning.

Hvad er gevinsten?

Det er ikke tit, at en webapplikation skal indsætte 250 rækker af gangen. Det kan til gengæld være, at der skal indsættes mere data i flere kolonner. Og så er problematiken den samme.

En mindsket loadtid vil:

• forbedre brugeroplevelsen.
  En langsom computer eller internetforbindelse er død irriterende. Den oplevelse vil du gerne spare brugerne af din applikation for.

• mindske serveromkostninger
  Det kan godt være, at du ikke kan gå ud og skifte til en billigere webhost ved at mindske belastningen på dine INSERTs, men tænker du over det, når du laver arkitekturen til siden, vil du kunne blive på en billigere webhost i længere tid. Den dag din side får 100.000 besøgende dagligt, vil du også blive lykkelig for, at du tænkte på serverbelastningen.

Ulempen er, at koden oftest bliver mindre læsbar.

I b.php er det tydeligt, men bruger du en databasewrapper kan koden blive pæn.

I sidstnævnte skal insert()-funktionen fødes med et array over de forskellige rækker for at foretage multi-INSERT. I din foretrukne databasewrapper er det sikkert også nemt.

Selvom loadtiden mindskes, er det ikke altid en fordel at bruge multi-INSERTs.

Ved du, at antallet af samtidige INSERTs ikke senere skal øges, er der ingen grund til at bruge energi på at strukturere kode for multi-INSERTs.

Jeg har selv oplevet performanceproblemer med en funktion til at tilknytte et billede til et produkt. En ny leverandør betød mange flere billeder pr. produkt samt jævnligt nye varer og billeder, hvilket gjorde et cronjob tungt.

I PHP frameworket Laravel har jeg oplevet suboptimale implementeringer af database insert-funktioner.

Kun ved MySQL INSERTs?

Artiklen fokuserer på MySQL INSERT-forespørgsler, da det er forholdsvist nemt at gå til. Det er også muligt at lave multi-UPDATES, men det er lidt mere kringlet.

Hvis man kigger udenfor databaseverdenen, er det samme filosofi der ligger bag følgende, hvor der i høj grad lægges vægt på at mindske antallet af forbindelser.

• CSS sprites, hvor man lægger en sides billeder sammen i ét stort billede og kun henter den del af billedet ud, som man skal bruge (læs mere her: http://css-tricks.com/css-sprites/ ). Giver færre HTTP requests.

• Komprimering af store sider (CSS, JS, billeder og markup), for at undgå at brugerens browser skal lave flere round trips til serveren (læs mere her: https://developers.google.com/speed/docs/insights/PrioritizeVisibleContent ).

• Brug af CDNs, hvor man ved at lade brugeren hente ressourcer fra en ekstern server både lader dem kontakte den nærmeste server i CDN’et samt benytte en cached version på den næste side, hvor samme ressource bruges. (læs mere her, om Googles populære CDN for JavaScript biblioteker: https://developers.google.com/speed/libraries/ )

Jeg håber, artiklen har givet dig en bedre baggrund for at strukturere din kode, så den både performer godt nu og i fremtiden.