Mijn projecten bevatten regelmatig tabellen met tien miljoen of meer records. Denk aan logbestanden, transactiegegevens of analysedata die maandenlang wordt verzameld. Op een gegeven moment worden queries zo traag dat indexen alleen niet meer voldoende zijn. Dan grijp ik naar MySQL partitionering, een techniek die grote tabellen opdeelt in kleinere, beheersbare stukken die apart kunnen worden doorzocht.
Wanneer partitionering zinvol wordt
Partitionering heeft vooral zin bij tabellen waar queries zich concentreren op specifieke datumbereiken of categorieën. In mijn ervaring zijn dit meestal logbestanden die per dag of maand worden opgevraagd, ordergegevens die per kwartaal worden geanalyseerd, of gebruikersactiviteit die per regio wordt bekeken. MySQL kan dan irrelevante partities volledig overslaan tijdens het zoeken, wat de querytijd drastisch vermindert.
Het beslissingsproces begint altijd bij het analyseren van querypatronen. Als ik merk dat 90% van mijn queries zich richt op data van de afgelopen drie maanden, dan is dat een sterke indicator. Ook tabellen die regelmatig oude data laten verwijderen zijn goede kandidaten, omdat je hele partities kunt droppen in plaats van DELETE statements uit te voeren.
Range partitioning werk ik het vaakst mee. Hierbij verdeel je data op basis van waardes in een kolom, meestal een datum. Een voorbeeld van een tabel die ik partitioneer op maandbasis:
CREATE TABLE user_activities (
id BIGINT AUTO_INCREMENT,
user_id INT NOT NULL,
activity_type VARCHAR(50),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
data JSON,
PRIMARY KEY (id, created_at)
)
PARTITION BY RANGE (YEAR(created_at) * 100 + MONTH(created_at)) (
PARTITION p202401 VALUES LESS THAN (202402),
PARTITION p202402 VALUES LESS THAN (202403),
PARTITION p202403 VALUES LESS THAN (202404),
PARTITION p202404 VALUES LESS THAN (202405),
PARTITION p_future VALUES LESS THAN MAXVALUE
);
Let op dat de partitiekolom onderdeel moet zijn van de primary key. Dit is een MySQL-vereiste waar ik aanvankelijk tegenaan liep. De formule YEAR(created_at) * 100 + MONTH(created_at) converteert datums naar integers zoals 202401 voor januari 2024, wat makkelijk te begrijpen partities oplevert.
Hash partitioning voor gelijkmatige verdeling
Hash partitioning past ik toe wanneer data geen natuurlijke range heeft maar wel gelijkmatig verdeeld moet worden. Dit zie ik vaak bij user-gerelateerde tabellen waar ik wil voorkomen dat alle data van populaire gebruikers in één partitie terechtkomt.
CREATE TABLE user_profiles (
user_id INT NOT NULL,
username VARCHAR(255),
email VARCHAR(255),
profile_data JSON,
updated_at TIMESTAMP,
PRIMARY KEY (user_id)
)
PARTITION BY HASH(user_id)
PARTITIONS 8;
MySQL distribueert records automatisch over acht partities door een hash-functie toe te passen op user_id. Het aantal partities kies ik meestal als macht van twee, omdat dit de hash-functie helpt om data gelijkmatig te verdelen. Te veel partities kunnen overhead veroorzaken, te weinig bieden onvoldoende voordeel.
Bij hash partitioning moet ik wel oppassen met het aantal partities. Queries die niet filteren op de partitiekolom moeten alle partities scannen. Daarom gebruik ik deze methode vooral voor tabellen waar queries bijna altijd een specifieke user_id bevatten.
Automatisch partitieonderhoud implementeren
Nieuwe partities aanmaken en oude verwijderen doe ik niet handmatig. In plaats daarvan schrijf ik scripts die dit automatiseren, omdat handmatig onderhoud foutgevoelig is en vergeten wordt. Voor range partitioning op datum maak ik maandelijks nieuwe partities aan:
-- Stored procedure voor automatisch partitie-onderhoud
DELIMITER $$
CREATE PROCEDURE AddMonthlyPartition(
IN table_name VARCHAR(255),
IN months_ahead INT
)
BEGIN
DECLARE partition_name VARCHAR(255);
DECLARE partition_value INT;
DECLARE sql_stmt TEXT;
SET partition_value = (YEAR(DATE_ADD(NOW(), INTERVAL months_ahead MONTH)) * 100 +
MONTH(DATE_ADD(NOW(), INTERVAL months_ahead MONTH)));
SET partition_name = CONCAT('p', partition_value);
SET sql_stmt = CONCAT(
'ALTER TABLE ', table_name,
' ADD PARTITION (PARTITION ', partition_name,
' VALUES LESS THAN (', partition_value + 1, '))'
);
SET @sql = sql_stmt;
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END$$
DELIMITER ;
Deze stored procedure roep ik aan via een cron job die elke maand draait. Daarnaast maak ik een vergelijkbare procedure voor het droppen van oude partities. Oude data verwijderen wordt dan zo simpel als ALTER TABLE user_activities DROP PARTITION p202201, wat veel sneller is dan DELETE statements.
Het monitoren van partitie-prestaties doe ik via information_schema tabellen. Query's zoals SELECT * FROM information_schema.PARTITIONS WHERE table_name = 'user_activities' tonen me hoeveel rows elke partitie bevat en of de verdeling nog kloopt.
Indexes en performance-overwegingen
Partitionering beïnvloedt indexstrategieën aanzienlijk. Indexes die ik op een gepartitioneerde tabel zet, worden per partitie aangemaakt. Dit betekent dat een index op een tabel met acht partities eigenlijk acht afzonderlijke indexes wordt. Voor performance is dit meestal voordelig, omdat kleinere indexes sneller zijn om te doorzoeken.
-- Index toevoegen aan gepartitioneerde tabel
ALTER TABLE user_activities
ADD INDEX idx_user_activity (user_id, activity_type);
Deze index wordt automatisch op elke partitie toegepast. Queries die zowel filteren op de partitiekolom als op geïndexeerde kolommen, profiteren dubbel: ze hoeven maar enkele partities te scannen en kunnen binnen die partities de index gebruiken.
Cross-partition queries vermijd ik waar mogelijk. Een query als SELECT COUNT(*) FROM user_activities WHERE activity_type = 'login' zonder datum-filter moet alle partities scannen. In plaats daarvan probeer ik altijd een filter op created_at toe te voegen: SELECT COUNT(*) FROM user_activities WHERE activity_type = 'login' AND created_at >= '2024-01-01'.
Unique constraints op gepartitioneerde tabellen kunnen lastig zijn. MySQL vereist dat alle unique indexes de partitiekolom bevatten. Dit betekent dat een unique constraint op alleen email niet mogelijk is als je partitioneert op created_at. Soms moet ik mijn datamodel aanpassen om dit op te lossen.
Partitionering heeft mijn database-prestaties enorm verbeterd, vooral voor rapportages over grote datasets. Queries die vroeger minuten duurden, draaien nu in seconden omdat MySQL alleen relevante partities hoeft te doorzoeken. Het vraagt wel discipline in het onderhoud en zorgvuldige planning van de partitiestructuur, maar die investering betaalt zich meerdere keren terug in snellere applicaties en tevreden gebruikers.