Database Optimaliseren

Hela, kan ik wat dingen doen om mijn database te versnellen?

Of: Help, mijn query is automatisch afgebroken!

Als uw database query er te lang over doet, loopt u het risico dat uw site vastloopt omdat de andere queries hierdoor opgehouden worden. Verder kan de database server door te zware/slecht ontworpen queries overbelast raken, waardoor ook andere sites op ons cluster trager of onbereikbaar worden. Daarom worden zulke queries door ons afgebroken.

Mogelijke oorzaken van een trage query:

• Geen indices
• Te veel joins
• Te veel rijen

En in mindere mate:

• Te grote rijen
• BLOBS in plaats van TEXT
• TEXT in plaats van VARCHAR
• queries naar informatie_schema zijn traag (zie onder)

U kunt opvragen welke queries de server aan het draaien is met het SQL commando show processlist. U kunt dit ook opvragen in PHPMyadmin door bovenaan op server te klikken en dan te klikken op het tabblad processes. Vaak zegt de status (de een na laatste kolom) u meer.

Sending data -> Er is teveel data, waarschijnlijk worden er vele gigabytes heen en weer gepompt. Dit duidt over het algemeen op een JOIN met te weinig (foute) condities.

Copying to temp table -> Er zijn teveel joins. De-normaliseer naar minder tabellen.

Meer informatie over de query is te verkrijgen met de explain opdracht van MySQL.

Een voorbeeld

Stel dat we de volgende tabel hebben om de responsetijd van een aantal websites te monitoren :

mysql> describe resultaten;

Field	Type	Null	Key	Default	Extra
datum	datetime	NO	MUL	0000-00-00 00:00:00
site	varchar(255)	NO	MUL
http_code	char(3)	YES	NULL
responsetijd	float	YES	NULL
online	int(11)	YES	NULL

Als we nu alle resultaten willen hebben met een responsetijd groter dan 4 seconden, kunnen we de volgende query uitvoeren:

select * from resultaten where responsetijd > 4;

Deze query doet er twee seconden over.

Om de tien resultaten met de langste responsetijd te vinden, kunnen we het volgende proberen:

select * from resultaten order by responsetijd desc limit  10;

Deze doet er zelfs 3.5 seconden over.

We gaan de eerste query onderzoeken met de explain opdracht :

mysql> explain select   * from resultaten where responsetijd > 4;

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	resultaten	ALL	NULL	NULL	NULL	NULL	1461450	Using where

Er is geen index op 'responsetijd'. We kunnen de query versnellen door een index toe te voegen:

mysql> alter table resultaten add index responsetijd_index (responsetijd);
Query OK, 1461450 rows affected (52.49 sec)
Records: 1461450  Duplicates: 0  Warnings: 0

De eerste query doet er nu nog maar 0.3 seconden over, de tweede doet er nu 0 seconden over. Dit omdat hij nu een index gebruikt:

mysql> explain select   * from resultaten where responsetijd > 4;

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	resultaten	range	responsetijd_index	responsetijd_index	5	NULL	26673	Using where

Denormalisatie

In het algemeen is het aan te raden een database ontwerp te normaliseren. Dit omdat anders gegevens meerdere keren in de database komen te staan en tegenstrijdig kunnen worden. Normalisatie zal in het algemeen leiden tot meer, maar kleinere tabellen en tot meer JOINS in uw queries.

De bovenstaande tabel zouden we bijvoorbeeld kunnen normaliseren door het attribuut 'site' in een aparte tabel sites (site_id, naam) op te slaan. In de tabel resultaten zouden we dan de kolom site kunnen vervangen door een kolom site_id. Voordelen hiervan zouden zijn:

• minder opslagruimte nodig
• als de site naam verandert, hoeft er maar een rij aangepast te worden.

Nadeel hiervan is dat bij iedere query waarbij de naam van de site van belang is, een JOIN moet doen met de tabel sites. Queries met te veel JOINS kunnen erg langzaam worden, en in dat geval is het aan te raden weer te denormaliseren.

De huidige waardering is: 87/100 (5 stemmen)

 You need to enable JavaScript to vote