Ankündigung

Einklappen
Keine Ankündigung bisher.

[MySQL] MyISAM zu InnoDB mit Spaltengruppe als Primärschlüssel + AI ?

Einklappen

Neue Werbung 2019

Einklappen
X
  • Filter
  • Zeit
  • Anzeigen
Alles löschen
neue Beiträge

  • [MySQL] MyISAM zu InnoDB mit Spaltengruppe als Primärschlüssel + AI ?

    Hallo,

    wie mir gestern zugetragen wurde, ist dies wohl inzwischen auch mit MySQL InnoDB möglich.
    Ich habe in ein par Tabellen (aus verschiedenen gründen) mit Spaltengruppen als Primärschlüssel + AI und ich müsste dies auch beibehalten.
    Nach dem ich jetzt schon ne Weile rum google wie ich MyISAM zu InnoDB konvertiert bekomme, konnte ich zu meinem Thema nichts finden, i.d.R. eben nur einspaltige Uniques (id).

    Code:
    CREATE TABLE `com_conv_msg` (
        `cid` BIGINT(15) NOT NULL COMMENT 'conversation id',
        `id` BIGINT(12) NOT NULL AUTO_INCREMENT COMMENT 'MSG-ID on this conversation',
        `uid` BIGINT(12) NOT NULL,
        `txt` TEXT NOT NULL,
        PRIMARY KEY (`cid`,`id`)
    ) ENGINE=MyISAM;
    Diese Tabelle mal als kleines Beispiel.
    Und ich konnte da so gar nichts finden was sich auf InnoDB bezieht, ich mein im schlimmsten Fall könnte ich alle Tabellen neu anlegen wenn sich nichts zum Thema konvertieren findet aber auch dann müsste ich wissen wie die Syntax bei InnoDB aussieht, denn einfach die Engine ändern bringt nix...

    Code:
    #1075 - Falsche Tabellendefinition. Es darf nur eine AUTO_INCREMENT-Spalte geben, und diese muss als Schlüssel definiert werden
    Ich denke MyISAM ist schon kacke, wie ich lese sperrt es im Bedarfsfall gleich die ganze Tabelle und dafür habe ich es sicher zu oft verwendet als das es später reibungslos läuft.
    Wenn also eine Umstellung wirklich möglich ist, wäre ich euch wirklich sehr Dankbar wenn ihr mir helfen könnt.

    MfG: Paykoman

  • #2
    Warum Du unbedingt einen PK über mehrere Spalten brauchst, wenn die eine eh UNIQUE und NOT NULL ist (impliziet durch AI und NOT NULL) mag dahin gestellt sein, die Syntax wäre aber zumindest in PG simpel:

    Code:
    test=# create table com_conv_mag(cid bigint, id bigserial, uid bigint, txt text, primary key(cid,id));
    CREATE TABLE
    test=*# \d com_conv_mag
                                Table "public.com_conv_mag"
     Column |  Type  | Collation | Nullable |                 Default                  
    --------+--------+-----------+----------+------------------------------------------
     cid    | bigint |           | not null |
     id     | bigint |           | not null | nextval('com_conv_mag_id_seq'::regclass)
     uid    | bigint |           |          |
     txt    | text   |           |          |
    Indexes:
        "com_conv_mag_pkey" PRIMARY KEY, btree (cid, id)
    
    test=*#
    PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

    Kommentar


    • #3
      Naja, weil eben alle spalten erst den UNIQUE darstellen und ich trotzdem die spalten einzeln für Aufgaben verwenden kann.
      Ich fand das bisher immer überaus praktisch und bin halt kein Fan von ID Spalten die irgendwann 12 stellig oder mehr sind (im Falle von Messenger-Nachrichten, Medien (Uploads), Kommentare usw. wohl schnell erreicht)...

      Aber wie gesagt das die Spalten weiterhin genutzt werden und gleichzeitig ein User/Inhalt-basierender counter entsteht ist eben praktisch.
      Im bereits genannten Beispiel sind es ja nur 2 Spalten, Konversations-ID und die Nachrichten-ID, da die Nachrichten-ID AI ist habe ich gleichzeitig eine Durchnummerierung der Nachrichten (zu jeder Unterhaltung) beginnend bei 1.

      Ich weiß halt nicht wie ich es noch erklären soll, dachte das es evtl. auch bei anderen recht beliebt ist, aber vllt. hilft noch ein Beispiel...


      Tabelle: invoices = year | id (AI)
      Tabelle: invoices_pos = iid (concat(i.year, i.id) | pos (AI)

      Jedes Jahr fängt die ID wieder bei 1 mit der Jahreszahl als Präfix an (fortlaufend & unique).
      Zugehörige Rechnungspositionen automatisch durchnummeriert 2019-1 | 1 \n 2019-1 | 2 usw...
      Eine AI unique ID in beiden Tabellen anstelle des Mehrspaltigen AI ist zudem eine höhere Belastung, selbige Daten wie eben müssten zusätzlich zur ID eh gespeichert werden, zudem wird die ID immer größer.

      Ja... naja...

      Mal sehen vllt. hat ja einer noch etwas auf Lager das ich in MySQL InnoDB nutzen kann.

      Kommentar


      • #4
        Die unique Spalte zuerst
        PHP-Code:
        CREATE TABLE `com_conv_msg` (
        `
        cidBIGINT15 NOT NULL COMMENT 'conversation id',
        `
        idBIGINT12 NOT NULL AUTO_INCREMENT COMMENT 'MSG-ID on this conversation',
        `
        uidBIGINT12 NOT NULL ,
        `
        txtTEXT NOT NULL ,
        PRIMARY KEY ( `id` , `cid` ) 
        ENGINE InnoDB 

        Kommentar


        • #5
          Zitat von Paykoman Beitrag anzeigen
          Im bereits genannten Beispiel sind es ja nur 2 Spalten, Konversations-ID und die Nachrichten-ID, da die Nachrichten-ID AI ist habe ich gleichzeitig eine Durchnummerierung der Nachrichten (zu jeder Unterhaltung) beginnend bei 1.
          Ich will Dich schon mal ganz vorsichtig darauf vorbereiten, daß das so nix wird. Die AI zählt nicht nach dem Merkmal einer anderen Spalte neu hoch, und sie zählt auch nicht ohne Lücken hoch. Nach einem ROLLBACK hast Du eine Lücke. Aber, hey, das sage ich Dir jetzt alles noch nicht, das merkst Du alles noch selber ...

          Andreas
          PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

          Kommentar


          • #6
            Stimmt unter InnoDB tut sie das nicht (in MyISAM schon), natoll
            Hätte ich es mal eher getestet, bekomme noch ne Kriese xD
            Womit wir eigentlich wieder bei der Kernfrage sind das InnoDB es immer noch nicht so kann wie MyISAM

            Kommentar


            • #7
              die sinnvollere Lösung wäre, die Durchnummerierung / Zählung bei der Abfrage zu machen. Dazu gibt es Window-Funktionen - die MySQL aber erst seit kurzem und nicht vollständig kann.
              PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

              Kommentar


              • #8
                Hmmm, ob das Sinnvoll ist... Der Vorteil in MyISAM mit der Durchzählung der Nachrichten zu jeder Konversation habe ich ja nicht nur für die Unique genutzt, die entsprechende MSG id habe ich auch im Frontend verwendet.

                Jetzt müsste ich da eine stetig anwachsende ID kreuz und quer schicken (Websocket etc.) zusätzlich zur selbigen Konversations-ID (sag ja in diesem Punkt war es schon echt sehr bequem die Daten mit MyISAM zu verwalten).
                Da eben jede Konversation einen eigenen Zähler hatte, konnte ich die jeweilige nachricht-ID für weitere Zwecke verwenden. z.B. Synchronisation mit IDB (ID der neuen nachricht entspricht der Nachfolger ID der letzten nachricht usw.).

                Kann man sich da nicht irgend wie Abhilfe schaffen? Ähnlich wie in PG blos in MySQL statt AI "nextval('com_conv_mag_id_seq'::regclass)" als default?

                ::EDIT::
                Alternativ wäre die einzige Lösung in der com_conv Tabelle einen Zähler hinzuzufügen der nach dem hinzufügen einer Nachricht um 1 angehoben wird aber dann muss ich zusehen wie ich den Aktuellen Zähler in das INSERT bekomme...

                Nun muss ich mir noch überlegen wie ich es bei den anderen Tabellen mache.
                Finde InnoDB da echt umständlich =(

                Kommentar


                • #9
                  Das ist das was wir hier immer rauf und runter beten, erst Gedanken machen, dann programmieren. Die Probleme die man sich durch eine falsche Herangehensweise schafft werden immer grösser.

                  MYISAM ist auch bei Mysql seit der Version 5.5 als Standardengine durch InnoDB abgelöst worden. Nun darfst du raten warum?

                  Kommentar


                  • #10
                    Ja stimmt, problematisch ist das man am Anfang nicht alles überschauen kann und ich zu mindestens denke es wird so schon gut gehen, weil es zu dem Zeitpunkt einfach logisch, am einfachsten und sinnvoll erscheint.
                    Also für den Messenger würde ich es jetzt tatsächlich so angehen:

                    Code:
                    SELECT @id:=mc FROM com_conv WHERE id = 1 LIMIT 1;
                    UPDATE com_conv SET mc = @id + 1 WHERE id = 1;
                    INSERT INTO com_conv_msg (`cid`, `id`, `uid`, `txt`)values(1, @id, 1, 'test');
                    Unter InnoDB sollten dann ja nur die entsprechenden Zeilen gesperrt werden und andere aktive Unterhaltungen nicht beeinträchtigen.
                    An den Abfragen kann eigentlich auch nicht viel schief laufen, ich habe wie gesagt mir heute morgen mal TRANSACTION angesehen weiß nur noch nicht ob es für diesen Query nicht ein wenig zuviel des guten ist...

                    Ja und ich muss mir noch ansehen wie das ganze mit php PDO funktioniert, denke damit könnte man das dann ja auch absichern mit COMMIT und ROLEBACK (aber wie gesagt was sollte da schon schief gehen?).

                    vs. MyISAM ist dies echt extrem Umständlich, 3 Querys wo MyISAM ein einfaches INSERT ausreicht (wüsste aber nicht wie ich sonst selbigen Effekt bekomme).

                    ::EDIT::
                    Wäre diese Variante effizienter (wahrscheinlich nicht hmm? Bleibt ja bei 3 Querys, aber Überschneidungen mit den ID´s dürften reduziert werden) ?

                    Code:
                    INSERT INTO com_conv_msg (`cid`, `id`, `uid`, `txt`)values(1, (SELECT mc FROM com_conv WHERE id = 1), 1, 'test');
                    UPDATE com_conv SET mc = mc + 1 WHERE id = 1
                    Problematisch ist das ein lastInsertId() nicht funktioniert

                    Kommentar


                    • #11
                      Also hier geht's aber munter durcheinander.
                      "Problematisch ist das ein lastInsetID().."
                      Das ist nicht problematisch, es gibt herrliche SQL Funktionen, die alles bieten, was man braucht.
                      "vielleicht noch mit Commit und Rollback", das muss man nicht dranbauen, es ist immer integriert!
                      Rollback gibt es im Fehlerfalle sogar geschenkt. Man kann höchstens von außen noch mit Clienttransaktionen rumdocktern, wenn man es dem Server schwer machen will.

                      Ich habe sicher einiges schräges Zeug übersehen.

                      Einfach mal ein Prinzip, das durch den ganzen Thread hindurch missachtet wird:
                      Primär-Schlüsselfelder müssen nichts, außer eindeutig sein, alles andere dran basteln zu wollen, ist gegen die Regeln.
                      Ein Schlüsselfeld, das sichtbar ist
                      Ein Schlüsselfeld, das aufsteigende ist
                      Ein Schlüsselfeld, das lückenlos ist
                      Alle Arten von Fachinformationen, die in ID reincodiert werden oder ID, die sich aus Fachinformationen zusammensetzen

                      Früher hat vielleicht mal der Mercedeswerkstattmann an der Fahrgestellnummer erkannt, wann und auf welcher Linie die Karre gebaut wurde (oder Schlimmeres), aber das war, als Nummer und Buchstaben noch per Stempelwerk oder Schlagstift auf Papier oder Metall gebracht wurden.

                      Heute verwendet man eigenständig, unabhängige Felder für Fachinformationen. Noch kurz warum?
                      Man kann technische Funktion, Nutzdaten und Business Logik jederzeit(!) unabhängig von einander ändern. Das ist Gold wert.

                      "Praktisch" ist das durcheinander nur solange, wie man noch nicht vor der Aufgabe stand, das ein oder andere in einem solchen "gewachsenen" System zu ändern.

                      Kommentar


                      • #12
                        Da es atm alles nicht so recht zum Ziel führt, kam mir gerade in den Sinn MyISAM ggf. nur noch als Wrapper zu nutzen um die fortlaufende ID zu generieren ohne das es zu Konflikten kommt...

                        Alle anderen Tabellen können dann einheitlich InnoDB sein auf die dann eben SELECT, UPDATE, JOINS und co ausgeführt werden, somit käme auf die MyISAM Tabelle lediglich INSERT und gelegentlich DELETE.

                        Die Tabellen in denen Datensätze mit mehrspaltigen AI eingefügt werden müssen, müsste dann natürlich immer ein INSERT in die jeweilige MyISAM Tabelle vorangestellt sein und mit lastinsertId() hat php dann eine konfliktfreie ID die dann beim insert in die InnoDB genutzt werden kann. Über weitere Spalten als die, die nötig sind zur ID-Generierung würden die Tabellen natürlich nicht haben.

                        Denke der zusätzliche Speicherverbrauch würde sich in Grenzen halten, Performance sollte auch nicht wirklich belastet werden (wird ja nicht wirklich weiter verwendet).

                        Beispiel:
                        PHP-Code:
                        $msgID $db->insert('com_conv_msg_w', ['cid' => $convID]); // gibt dann die AI id für Nachrichten je Konversation
                        $msgData = [
                            
                        'cid' => $convID,
                            
                        'id' => intval($msgID),
                            
                        'uid' => intval($_SESSION['member']['id']),
                            
                        'txt' => $form->retText('msg'),
                            
                        'url' => '',
                            
                        'att' => '',
                            
                        'atxt' => '',
                            
                        'date' => date::op('now''db''system')
                        ];
                        $db->insert('com_conv_msg'$msgData); 
                        Wäre jedenfalls umständlicher als alles was wir bisher hatten...

                        Kommentar


                        • #13
                          Zitat von Paykoman Beitrag anzeigen
                          Wäre jedenfalls umständlicher als alles was wir bisher hatten...
                          Ja. Viel Erfolg!
                          PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

                          Kommentar


                          • #14
                            Er liebt halt umständliche Lösungen. Wozu einfach, wenns auch kompliziert geht? Ich werde solche Leute nie verstehen.

                            Schon allein beim Anblick der kryptischen Spalten- und Tabellennamen dreht es mir alle Zehennägel hoch. Datenbankdesign aus der Hölle.

                            Kommentar


                            • #15
                              @Perry: An den Daten der Mehrspaltigen ID's ändert sich doch nichts, eine Konversation hat immer die selbe ID selbiges gilt für die anderen Bereiche...
                              Es ist eben wie du schon sagst die unique setzt sich aus verschiedenen Daten zusammen nur das ich es halt nicht als 1 Feld (char) verwende, Vor- & Nachteile habe ich ja bereits genannt.

                              Und ein Durcheinander gibt es in meinen Daten in diesem Sinn nicht... Durcheinander ist hier nur das Thema hier und weil InnoDB bisher kein äquivalent zu dieser MyISAM Funktion gibt =(


                              ::EDIT::
                              Nun ja die letzten 2 Kommentare... Es gab ja keine Lösung!

                              Kommentar

                              Lädt...
                              X