Ergebnis 1 bis 9 von 9

Thema: Mysql: Effizienz von Strukturen

  1. #1

    Mysql: Effizienz von Strukturen

    Hi,
    ich bin in der Planung für ein Projekt, dort möchte ich die Datenbankstruktur so klein wie möglich halten, deshalb denke ich über folgendes Konstrukt nach

    Code:
    // tablename: entries
    id | name | namespace | content 
    
    // tablename: entries_attributes
    id | enty_id | name | namespace | content
    In "entries" soll jeglicher Hauptinhalt gespeichert werden, bezugnehmend darauf gibt es für den "entries"-table noch einen "entries_attributes"-table. In dem werden zusätzliche Informationen abgelegt.


    Ein kleines Beispiel:
    Pages haben im "entries"-table einen namespace "app.page". In der Spalte "name" wird der Name der Seite und in "content" der Hauptinhalt gespeichert.
    Wenn ich jetzt zb. eine SEO url der Page hinzufügen will, mache ich das über "entries_attributes". Dort wäre der "namespace" "app.page.seourl", in der Spalte "name" stünde "url" und in der spalte content wäre die eigentliche URL. Die Spalte "name" würde bei "entries_attributes" also für die Identifikation dienen (Namen der Spalten sind nicht final ).

    Messages haben im "entries"-table einen namespace "foobar.message".
    Snippets haben im "entries"-table einen namespace "foobar.snippets".

    Ist es, auch bei großen Datenmengen, effizient so zu arbeiten oder wäre ein eigener "entry"-table sinnvoller?
    Wie sehr bremst das "WHERE namespace = ?".
    Der "enties_attributes"-table kann je Eintrag sehr viele Unterpunkte beinhalten, der Abruf geschieht entweder über die "WHERE entry_id = ?" oder über "WHERE entry_id = ? AND namespace = foobar.page.seourl", wenn ich nur bestimmte attribute hole.

  2. #2
    Bot #0384479 Avatar von BurnerR
    Registriert seit
    Jul 2013
    Beiträge
    3.580

    Re: Mysql: Effizienz von Strukturen

    Nur für den Fall, dass es dir nicht klar ist: Was du da in Betracht ziehst nennt sich EAV.

    imHo:
    namespace column solltest du vermutlich indexing.

    Performance-Test könntest du doch vermutlich relativ einfach bauen oder? Fände ich auch spannend.
    Ansonsten findet sich zum EAV mit mysql vllt was nettes. Grad nur was zu postresql gefunden, weil es da mit JSONB eine interessante Alternative gibt..
    Aber willst du wirklich, wirklich EAV einsetzen? ;-).

    Vielleicht ist dieser Beitrag auch interessant für dich?
    https://stackoverflow.com/a/19368048/1638910

  3. #3
    Mitglied

    (Threadstarter)


    Registriert seit
    Jul 2013
    Beiträge
    278

    Re: Mysql: Effizienz von Strukturen

    Ich möchte es für bestimmte Dinge einsetzten, nicht gänzlich. Danke für den Link ich versuche das gerade in einem Testcase nachzubilden.

  4. #4
    Mitglied

    (Threadstarter)


    Registriert seit
    Jul 2013
    Beiträge
    278

    Re: Mysql: Effizienz von Strukturen

    Da es ein private Projekt ist, komme ich leider seltener dazu. Habe mal ein Konstrukt gebastelt um den Unterschied der Geschwindigkeit zu visualisieren.

    Als content kommt jeweils ein text mit 593 Zeichen zum Einsatz (Lorem Ipsum). In der Datenbank befinden sich 100.000 Einträge. Das holen der Einträge habe ich auf 10.000 beschränkt.

    Tabellen

    Normaler Weg:
    Code (PHP):
    1.  
    2. Data_Reg: id | content
    3.  
    Attribut Weg:
    Code (PHP):
    1.  
    2. Data_Attr: id | name
    3. Data_Attributes: id | ref_id | content
    4.  

    Queries

    Normaler Weg:
    Code (PHP):
    1.  
    2. SELECT * FROM Data_reg LIMIT 10000
    3.  
    Attribut Weg:
    Code (PHP):
    1.  
    2. "SELECT Data_Attr.id, DA.id, DA.content FROM attrway LEFT JOIN Data_Attributes DA ON DA.ref_id = Data_Attr.id LIMIT 10000"
    3.  

    Zeiten

    Normaler Weg: 0.05389404296875 Sekunden
    Attribut Weg: 24.86952996254 Sekunden



    MySQL: Ver 15.1 Distrib 10.2.10-MariaDB, for osx10.13 (x86_64)
    PHP: PHP 7.1.14
    Zum Abfragen wurde PHP´s PDO genutzt.

    PHP, MySQL und der Client waren in meinem Test der gleiche PC.
    Für diesen Beitrag bedankt sich Cybergreek

  5. #5
    Bot #0384479 Avatar von BurnerR
    Registriert seit
    Jul 2013
    Beiträge
    3.580

    Re: Mysql: Effizienz von Strukturen

    Kannst du die Unterschiede in den Tabellen noch etwas ausführen? Das leuchtet mir spontan nicht ein.
    Würde glaube ich zwischendurch just for fun was in postgresql nachbauen, wenn du die Testdaten zur Verfügung stellst.

  6. #6

    Re: Mysql: Effizienz von Strukturen

    Mal zwei Punkte in den Raum geworfen, die mir spontan auffallen, wo ich Fallstricke sehe, bezogen auf den zweiten Weg:

    Ist das nicht logisch dass das zweite Konstrukt mehr Zeit in Anspruch nimmt?, Es sind doch mehr "Aufgaben"/Operationen für die DB - den Query zu erzeugen, den JOIN durchzuführen, die Attribute, von davor "zusammengeführten" (?) Daten des JOIN? (RAM?) zu behandeln, daraus zu filtern bzw. zu selektieren. Und dann kommt die Rückgabe.

    KISS Prinzip? - Keep it simple stupid? Gilt das nicht auch für eine DB?
    Und was ist üblicher und somit auch optimierter als "Use-Case" ? Vermutlich der einfache Query, ohne "Bedingungen".

    Auf der anderen Seite müßte man jetzt aber auch bedenken, du brauchst für den zweiten Query viel länger, aber wäre die Zeit der Datenverarbeitung um an die Inhalte zu kommen, auch langsamer oder gewinnst du da Zeit gegenüber Weg 1? - Wäre vielleicht schon der nächste Schritt, aber auch interessant.

    Aber auch RAM Usage würde mich interessieren, bzw. wie ein "JOIN" virtuell arbeitet. Aber vielleicht ist die Frage auch etwas fehl am Platz.
    Geändert von theSplit (06.07.18 um 01:38 Uhr)
    Gruß theSplit
    @ I might be sober. The good things... the bad things... all I ever know is here! @ The past; yesterday, the present; here and now; the future; to be shaped. @
    +++ Pi-Thread +++ IT-Talents Code Competitions +++ NGB-Statistik Opt-Out/Anonymisierung +++ KISS Ebook Starter [Linux] +++ Browser Add-on: Flag cookies +++ Bandcamp +++ Github +++ Touch +++

  7. #7
    Bot #0384479 Avatar von BurnerR
    Registriert seit
    Jul 2013
    Beiträge
    3.580

    Re: Mysql: Effizienz von Strukturen

    KISS Prinzip? - Keep it simple stupid? Gilt das nicht auch für eine DB?
    Und was ist üblicher und somit auch optimierter als "Use-Case" ? Vermutlich der einfache Query, ohne "Bedingungen".
    Darauf gibt es halt zuweilen nicht die eine Antwort, da du nur tradeoffs hast und am Ende qualitativ abwägen musst, was in deinem speziellen Fall der bessere Kompromiss ist.
    Typisches Beispiel sind z.B. Schreib- vs. Lesevorgänge. Manchmal hast du einen Ansatz, wo nur Lesevorgänge schnell sind und einen anderen, wo nur Schreibvorgänge schnell sind. Du musst dann abwägen, was in dem vorliegenden Anwendungsfall wichtiger ist.

    An sich imHo verwendet man EAV nur wenn einem wirklich nichts besseres einfällt, weil man damit viele Vorteile eines relationalen DBMS aushebelt.

  8. #8

    Re: Mysql: Effizienz von Strukturen

    Die Struktur ist so lahm, weil MySQL da standardmäßig keinen Index anlegt. Ich hab mal dein Beispiel nachgebaut (und mit zufälligen Texten befüllt, MariaDB 10.1):
    Code (MySQL):
    1.  
    2. -- Versuch 1: Struktur wie oben, 100000 zufällige Einträge
    3. CREATE TABLE data_attr (
    4.     name varchar(255) NOT NULL
    5. );
    6. CREATE TABLE data_attributes (
    7.     ref_id int NOT NULL,
    8.     content TEXT NOT NULL
    9. );
    10. -- => 17.571578025818 sec
    11. -- => 16.847698926926
    12.  
    13. -- Versuch 2: FOREIGN KEY constraints an die Tabelle
    14. DROP TABLE TABLE data_attributes;
    15. CREATE TABLE data_attributes (
    16.     ref_id int NOT NULL REFERENCES data_attr ON DELETE CASCADE,
    17.     content TEXT NOT NULL
    18. );
    19. -- => 16.15616106987
    20.  
    21. -- Versuch 3: Index manuell anlegen
    22. CREATE INDEX data_attributes_ref_id_idx ON data_attributes (ref_id);
    23. -- => 0.028398990631104
    24. -- => 0.029699087142944
    0.03 sec sieht doch gar nicht so schlecht aus, oder?

    Zum Nachvollziehen:

    Spoiler: 

    Code (PHP):
    1. <?php
    2. ini_set('display_errors', true);
    3.  
    4. $pdo = new PDO('mysql:host=localhost;dbname=performance_tests', 'username', 'password');
    5.  
    6. if (isset($_GET['insert'])) {
    7.     echo "Insert...\n";
    8.     $t = microtime(true);
    9.     $pdo->query('DELETE FROM data_attributes;');
    10.     $pdo->query('DELETE FROM data_attr;');
    11.  
    12.     $statement1 = $pdo->prepare("INSERT INTO data_attr (id, name) VALUES (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?)");
    13.     $statement2 = $pdo->prepare("INSERT INTO data_attributes (ref_id, content) VALUES (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?)");
    14.  
    15.     for ($i = 1; $i < 100000; $i += 50) {
    16.         $v1 = [];
    17.         $v2 = [];
    18.         for ($j = $i; $j < $i+50; $j++) {
    19.             $v1[] = $j;
    20.             $v1[] = bin2hex(random_bytes(8));
    21.             $v2[] = $j;
    22.             $v2[] = base64_encode(random_bytes(444));
    23.         }
    24.         $statement1->execute($v1);
    25.         $statement2->execute($v2);
    26.  
    27.         if (($i+19) % 10000 == 0) {
    28.             var_dump($i+49);
    29.             flush();
    30.         }
    31.     }
    32.  
    33.     var_dump(microtime(true) - $t);
    34. }
    35.  
    36.  
    37. var_dump($pdo->query("SELECT count(*) FROM data_attr")->fetch());
    38. var_dump($pdo->query("SELECT count(*) FROM data_attributes")->fetch());
    39.  
    40. // ANFRAGE
    41. $t = microtime(true);
    42. $sql = "SELECT data_attr.id, DA.id, DA.content FROM data_attr LEFT JOIN data_attributes DA ON DA.ref_id = data_attr.id LIMIT 10000";
    43. $result = $pdo->query($sql);
    44. var_dump(microtime(true) - $t);
    45.  



    Fun fact: Das selbe mit Postgresql 10. Postgres scheint selbst ohne jegliche Indices einen effizienten Algorithmus dafür zu haben. Was meine Meinung wieder bestätigt, dass der einzige Vorteil von MySQL seine Verbreitung ist...
    Code (PostgreSQL):
    1. CREATE TABLE data_attr (
    2.     id serial PRIMARY KEY,
    3.     name varchar(255) NOT NULL
    4. );
    5. CREATE TABLE data_attributes (
    6.     id serial PRIMARY KEY,
    7.     ref_id int NOT NULL,
    8.     content TEXT NOT NULL
    9. );
    10. -- => 0.042082071304321
    11. -- => 0.048994064331055
    12.  
    13. DROP TABLE data_attributes;
    14. CREATE TABLE data_attributes (
    15.     id serial PRIMARY KEY,
    16.     ref_id int NOT NULL REFERENCES data_attr ON DELETE CASCADE,
    17.     content TEXT NOT NULL
    18. );
    19. -- => 0.058371067047119
    20. -- => 0.057842969894409
    21.  
    22. CREATE INDEX data_attributes_ref_id_idx ON data_attributes (ref_id);
    23. -- => 0.018332004547119
    24. -- => 0.017482042312622
    Für diesen Beitrag bedanken sich theSplit, Cybergreek, BurnerR
    Geändert von Rakorium-M (06.07.18 um 11:53 Uhr)

  9. #9
    Zeitreisender

    Administrator

    Avatar von drfuture
    Registriert seit
    Jul 2013
    Ort
    in der Zukunft
    Beiträge
    5.292
    ngb:news Artikel
    16

    Re: Mysql: Effizienz von Strukturen

    @TS Es dürfte schlicht auch stark darauf ankommen was du eigentlich mit den Daten machen möchtest.
    Ein "Neuesten Beitrag anzeigen", "Anzahl der Beiträge", "Anzahl zeit" .. usw. ist denke ich bei deinem Vorschlag langsamer.
    Alles was zählen, Sortieren, Berechnungen usw. beinhaltet und so auf einer größeren Datenbasis stattfinden muss.

    Reine selects dürften relativ gleich schnell sein bei beiden Verfahren.
    Du baust dir so aber im Zweifel vielleicht doppelte Inhalte oder rekursionen in die eine Tabelle. Auch das kommt auf den Inhalt und die Struktur der Daten an.
    |_|D *`~{ Ich kenne deine Zukunft }~´* |_|D

Berechtigungen

  • Neue Themen erstellen: Nein
  • Themen beantworten: Nein
  • Anhänge hochladen: Nein
  • Beiträge bearbeiten: Nein
  •