CakePHP: Optimalisatie van ACL-tabellen

Inleiding tot acl-optimalisatie in cakephp

Deze pagina behandelt een methode om de prestaties van queries naar ACL (Access Control List) tabellen in CakePHP aanzienlijk te verbeteren. De optimalisatie is relatief eenvoudig en omvat het aanbrengen van indexen en foreign keys in de database. De code is oorspronkelijk gevonden op een CakePHP discussiegroep en kan een merkbaar verschil maken in de responstijd van applicaties die zwaar leunen op ACL-functionaliteit. Het is belangrijk om te begrijpen dat deze optimalisatie specifiek gericht is op de database queries en niet op de CakePHP code zelf.

De tabelstructuur: acos

De `acos`-tabel (Access Control Objects) definieert de objecten die toegankelijk zijn. De tabelstructuur omvat de volgende velden: `id` (primary key), `parent_id`, `model`, `foreign_key`, `alias`, `lft`, en `rght`. De `lft` en `rght` velden worden gebruikt voor het implementeren van een boomstructuur voor de ACL. Het correct indexeren van deze velden is cruciaal voor efficiënte queries. We gebruiken **InnoDB** als storage engine.

Indexen voor de acos-tabel

Om de queryprestaties te verbeteren, worden de volgende indexen toegevoegd aan de `acos`-tabel: `idx_acos_lft_rght` (op `lft` en `rght`), `idx_acos_alias` (op `alias`), en `idx_acos_model_foreign_key` (op `model` en `foreign_key`). Deze indexen maken het mogelijk om sneller specifieke objecten te vinden op basis van deze criteria. De indexen op `lft` en `rght` zijn bijzonder belangrijk voor het navigeren door de boomstructuur. Denk aan de lengte van het `model` veld (255) bij het creëren van de index.

De tabelstructuur: aros

De `aros`-tabel (Access Control Records) definieert de gebruikers of rollen die toegang hebben tot de objecten. Net als de `acos`-tabel bevat deze de velden: `id` (primary key), `parent_id`, `model`, `foreign_key`, `alias`, `lft`, en `rght`. De structuur is vergelijkbaar met `acos` en vereist vergelijkbare optimalisaties. Hierbij wordt ook **InnoDB** gebruikt als storage engine.

Indexen voor de aros-tabel

Voor de `aros`-tabel worden de volgende indexen toegevoegd: `idx_aros_lft_rght` (op `lft` en `rght`), `idx_aros_alias` (op `alias`), en `idx_aros_model_foreign_key` (op `model` en `foreign_key`). Deze indexen, vergelijkbaar met die voor `acos`, helpen bij het snel vinden van specifieke records. Het is belangrijk om de indexen consistent te houden tussen de `aros` en `acos` tabellen voor optimale prestaties.

De tabelstructuur: aros_acos en foreign keys

De `aros_acos`-tabel is een relatietabel die de relatie tussen `aros` en `acos` definieert, en de permissies (`_create`, `_read`, `_update`, `_delete`) opslaat. Deze tabel bevat `aro_id` en `aco_id` als foreign keys die verwijzen naar de `aros` en `acos` tabellen respectievelijk. Een **unique index** (`idx_aros_acos_aro_id_aco_id`) wordt toegevoegd om te voorkomen dat er dubbele relaties ontstaan. Foreign keys zorgen voor data-integriteit en verbeteren de queryprestaties in veel gevallen.

Sql code voor optimalisatie

/* ACL Tables */CREATE TABLE acos ( id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, parent_id INT DEFAULT NULL, model VARCHAR(255) DEFAULT '', foreign_key INT UNSIGNED DEFAULT NULL, alias VARCHAR(255) DEFAULT '', lft INT DEFAULT NULL, rght INT DEFAULT NULL) ENGINE = INNODB;-- table name is quoted because it is a reserved wordCREATE INDEX idx_acos_lft_rght ON `acos`(lft,rght);CREATE INDEX idx_acos_alias ON `acos`(alias);CREATE INDEX idx_acos_model_foreign_key ON `acos`(model(255),foreign_key);CREATE TABLE aros ( id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, parent_id INT DEFAULT NULL, model VARCHAR(255) DEFAULT '', foreign_key INT UNSIGNED DEFAULT NULL, alias VARCHAR(255) DEFAULT '', lft INT DEFAULT NULL, rght INT DEFAULT NULL) ENGINE = INNODB;-- table name is quoted because it is a reserved wordCREATE INDEX idx_aros_lft_rght ON `aros`(lft,rght);CREATE INDEX idx_aros_alias ON `aros`(alias);CREATE INDEX idx_aros_model_foreign_key ON `aros`(model(255),foreign_key);CREATE TABLE aros_acos ( id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, aro_id INT UNSIGNED NOT NULL, aco_id INT UNSIGNED NOT NULL, _create CHAR(2) NOT NULL DEFAULT 0, _read CHAR(2) NOT NULL DEFAULT 0, _update CHAR(2) NOT NULL DEFAULT 0, _delete CHAR(2) NOT NULL DEFAULT 0) ENGINE = INNODB;-- table names are quoted because they are reserved wordsCREATE UNIQUE INDEX idx_aros_acos_aro_id_aco_id ON `aros_acos`(aro_id, aco_id);ALTER TABLE aros_acos ADD CONSTRAINT FOREIGN KEY (aro_id) REFERENCES `aros`(id);ALTER TABLE aros_acos ADD CONSTRAINT FOREIGN KEY (aco_id) REFERENCES `acos`(id);