georf.de

PostgreSQL vs. MySQL und DB-Views bei Feuerwehrsport-Statistik.de

Posted on by georf

Auf meiner Webseite Feuerwehrsport-Statistik.de rechne ich mit relativ vielen Daten Statistiken aus. Dabei werden unter anderem auch Joins über große Tabellen durchgeführt. Bisher läuft das über PHP mit MySQL. Es gibt keine Abstraktionsschicht und so wird jede SQL-Query einzelnd geschrieben.

  $teams[$key]['members'] = $db->getFirstRow("
    SELECT COUNT(*) AS `count`
    FROM (
      SELECT `person_id`
      FROM (
        SELECT `p`.`person_id`
        FROM `group_scores` `s`
        INNER JOIN `person_participations` `p` ON `p`.`score_id` = `s`.`id`
        WHERE `s`.`team_id` = '".$team['id']."'
      UNION
        SELECT `person_id`
        FROM `scores`
        WHERE `team_id` = '".$team['id']."'
      ) `i`
      GROUP BY `person_id`
    ) `c`", 'count');

Um die immer weiter wachsenden Anforderungen zu erfüllen, probiere ich gerade das Projekt zu Rails zu konvertieren. Dabei sollen die Abstraktionen von ActiveRecord genutzt werden, um möglichst wenig eigenen SQL-Code zu schreiben. Aber schon bei obigen Beispiel stößt ActiveRecord an seine Grenzen.

Datenbank-Views in Rails

Kurze Erklärung zum Verständnis: Mitglied einer Mannschaft kann man über zwei verschiedene Arten werden. Einmal ist man für eine Mannschaft in einer Gruppendisziplin gestartet (person_participations) oder man ist in einer Einzeldisziplin für diese Mannschaft gestartet (scores). In ActiveRecord kann man beide Beziehungen mittels has_many abbilden.

class Team < ActiveRecord::Base
  has_many :group_scores
  has_many :scores
  has_many :person_participations, through: :group_scores
  has_many :group_people, through: :person_participations, class_name: 'Person', 
    foreign_key: 'person_id', source: :person
  has_many :people, through: :scores
end

Um jetzt alle Mitglieder einer Mannschaft zu bestimmen, muss man einen UNION über scores und person_participations bilden.

def members
  Person.where("id IN (
      #{person_participations.select(:person_id).to_sql} 
      UNION 
      #{scores.select(:person_id).to_sql}
      GROUP BY person_id
    ")
  )
end

Diese Methode liefert zu einer bestimmten Mannschaft alle Mitglieder. Will man aber die Mannschaften nach Mitgliederanzahl sortieren, muss man für alle Mannschaften (zur Zeit 2110) diese Methode und somit eine aufwendige SQL-Query absetzen. Nützliche Rails-Funktionen wie joins und includes funktionieren nicht.

Die Lösung liefert die Datenbank: Views. Views sind Abbilder auf die Daten. Sie können nur gelesen werden. ActiveRecord unterstützt diese nicht direkt, was sich vor allem dadurch widerspiegelt, dass sie nicht in der schema.rb auftauchen. Ich habe das nach einer Anleitung implementiert und schon funktionieren die Anfragen.

PostgreSQL ist 14-mal schneller als MySQL

Nachdem ich für eine Übersichtsseite auch die Anzahl der Wettkampfteilnahmen pro Mannschaft brauchte, dauerte die Anfrage trotz zweier Views mit MySQL 2110 ms. In diesem Fall sind team_members und team_competitions Views, die mit mehreren JOIN- und UNION-Operationen arbeiten.

-- Team Load (2110.8ms)
SELECT 
    teams . *,
    COUNT(person_id) AS members_count,
    COUNT(competition_id) AS competitions_count
FROM
    `teams`
        INNER JOIN
    `team_members` ON `team_members`.`team_id` = `teams`.`id`
        INNER JOIN
    `people` ON `people`.`id` = `team_members`.`person_id`
        INNER JOIN
    `team_competitions` ON `team_competitions`.`team_id` = `teams`.`id`
        INNER JOIN
    `competitions` ON `competitions`.`id` = `team_competitions`.`competition_id`
GROUP BY teams.id

Einem Kommentar auf Stackoverflow zur Folge lohnt es sich PostgreSQL als Alternative zu MySQL auszuprobieren. Der Kommentar behauptet, dass MySQL mit komplexen Joins manchmal Probleme macht. Dank Rails ist ein Umstieg auf eine anderes RDBMS kein Problem. Die gleiche Abfrage dauert nun nurnoch 143 ms.

Auf verschieden Vergleichsseite von RDBMS ist auch immer wieder zu lesen, dass für jeden Anwendungsfall die Auswahl der Datenbank einzelnd erfolgen soll, denn die Performance hängt stark von den verwendeten Operatoren ab. Für die neue Implementierung der Statistikseite wird also PostgreSQL zum Einsatz kommen.

Schreibe einen Kommentar