mysqldump mit insert ... on duplicate
<p>ich möchte daten von einer datenbank in eine andere zusammenführen. also erstelle ich einen dump mit <code>mysqldump</code> und importiere ihn dann in eine andere datenbank (mit derselben tabellenstruktur). ich habe in diesem fall keine probleme (wie doppelte einträge oder etwas anderes).</p><p>aber ich führe zu testzwecken einige zusammenführungen durch und werde sie später endgültig zusammenführen. daher möchte ich die zusammenführung einige male ausführen (daten können geändert werden). beachten sie, dass meine zeilen in meinen tabellen niemals gelöscht werden, sondern nur eingefügt oder aktualisiert werden können.</p><p>kann ich mysqldump mit der option on duplicate erstellen? oder kann ich einen dump zusammenführen, der neue daten einfügt und geänderte daten aktualisiert?</p><p>klar, ich kann <code>on duplicate</code> im speicherauszug manuell, aber ich möchte den zusammenführungsprozess automatisieren.</p>
<p>es gibt optionen, die ihnen dabei helfen:</p><pre><code> --insert-ignore insert rows with insert ignore. --replace use replace into instead of insert into. -t, --no-create-info don't write table creation info. </code></pre><p>denken sie an dieses paradigma</p><ul> <li>mysqldump alles von db1 in dump1</li> <li>laden sie dump1 in db3</li> <li>mysqldump alles aus db2 mit --replace (oder --insert-ignore) und --no-create-info in dump2</li> <li>laden sie dump2 in db3</li> </ul>
<p>bitte führen sie diese abfrage aus:</p><pre><code>select data\_bb / power(1024,1) data\_kb, data\_bb / power(1024,2) data\_mb, data\_bb / power(1024,3) data\_gb from (select sum(data\_length) data\_bb from information\_schema.tables where table\_schema not in ('information\_schema','performance\_schema','mysql')) a; </code></pre><p>dies gibt ihnen eine baseball-figur. die kolumne <code>index\_length</code> wird nicht verwendet, da mysqldump keine indizes, sondern nur daten ausgibt. um sicher zu gehen, sollten sie es immer sofort gzip:</p><pre><code>mysqldump --all-databases --routines --triggers | gzip &gt; mysqldata.sql.gz </code></pre><p>versuche es !!!</p> <p>im allgemeinen ist der schnellste weg, eine tabelle zu duplizieren, einfach:</p><pre><code>create table table2 as select * from table1; </code></pre><p>parallele inserts sind möglicherweise schneller, jedoch nur mit einem sehr schnellen festplattensubsystem (wenn daten auf vielen laufwerken verschachtelt sind). andernfalls ist dies langsamer.</p><p>sobald sie mit dem ändern von <code>table2</code> fertig sind, kann es den neuen namen annehmen mit:</p><pre><code>begin; drop table table1; alter table table2 rename to table1; commit; </code></pre><p>der befehl <code>drop table</code> benötigt eine exklusive sperre, die sich auf gleichzeitige leser auf eine weise auswirkt, die sie vielleicht vorhersehen möchten:</p><ul> <li><code>drop</code> wartet, bis alle ausstehenden lesevorgänge anderer transaktionen in der tabelle abgeschlossen sind.</li> <li>jede neue transaktion, die in der zwischenzeit versucht, diese tabelle zu lesen, wird gewartet und schlägt dann fehl, da das ursprüngliche <code>table1</code> nicht mehr vorhanden ist. der fehler würde so aussehen: "beziehung mit oid <em>oid</em> konnte nicht geöffnet werden")</li> </ul><p>um das zweite problem zu vermeiden, können sie <code>table1</code> in <code>old\_table1</code> umbenennen, anstatt es zu löschen, und es dann erst später außerhalb der transaktion löschen, wenn diese leser damit fertig sind. die obige sequenz würde also werden:</p><pre><code>begin; alter table table1 rename to old\_table1; alter table table2 rename to table1; commit; ... drop table old\_table1; </code></pre> <p>sie laufen <code>rake db:create</code> einmal und nur einmal, und sie führen es zuerst aus. dann führen sie <code>rake db:migrate</code> jedes mal, wenn sie eine migration hinzufügen/ändern. entweder haben sie diese migration bereits ausgeführt oder sie verweisen auf eine datenbank, die bereits vorhanden ist und eine tabelle mit dem namen <code>users</code> enthält. ich vermute, sie haben die migration bereits einmal durchgeführt. in diesem fall können sie wahrscheinlich loslegen. wenn sie die datenbank entpacken und von vorne beginnen möchten, tun sie <code>rake db:drop db:create db:migrate</code>.</p> <p>angenommen, sie verwenden ms sql server (was sie nicht sind, finden sie im <strong> update unten </strong>):</p><blockquote> <p>eine tabelle kann nur eine zeitstempelspalte haben. der wert in der timestamp-spalte wird jedes mal aktualisiert, wenn eine zeile mit einer timestamp-spalte eingefügt oder aktualisiert wird. diese eigenschaft macht eine timestamp-spalte zu einem schlechten kandidaten für schlüssel, insbesondere für primärschlüssel. bei jeder aktualisierung der zeile wird der zeitstempelwert und damit der schlüsselwert geändert. wenn sich die spalte in einem primärschlüssel befindet, ist der alte schlüsselwert nicht mehr gültig und fremdschlüssel, die auf den alten wert verweisen, sind nicht mehr gültig. wenn in einem dynamischen cursor auf die tabelle verwiesen wird, ändern alle aktualisierungen die position der zeilen im cursor. befindet sich die spalte in einem indexschlüssel, generieren alle aktualisierungen der datenzeile auch aktualisierungen des index.</p> </blockquote><p>informationen zu <a href="http://msdn.microsoft.com/en-us/library/aa260631%28v=sql.80%29.aspx"> msdn </a> </p><p>wenn sie datums-/uhrzeitinformationen für eine zeile speichern müssen und diese datums-/uhrzeitänderung nicht haben, verwenden sie datetime. andernfalls verwenden sie timestamp.</p><p><strong> beachten sie auch: </strong> ms sql server-zeitstempelfelder sind weder datum noch uhrzeit. sie sind binäre darstellungen der relativen reihenfolge, in der die daten geändert wurden.</p><h2>aktualisieren</h2><p>wie sie aktualisiert haben, um mysql zu sagen:</p><blockquote> <p>timestamp-werte werden zum speichern von der aktuellen zeitzone in utc konvertiert und zum abrufen von utc zurück in die aktuelle zeitzone konvertiert. (dies tritt nur für den timestamp-datentyp auf, nicht für andere typen wie datetime.)</p> </blockquote><p>zitat aus <a href="http://dev.mysql.com/doc/refman/5.0/en/timestamp.html">mysql-referenz</a> </p><p>insbesondere:</p><blockquote> <p>wenn sie einen timestamp-wert speichern, dann die zeitzone ändern und den wert abrufen, unterscheidet sich der abgerufene wert von dem von ihnen gespeicherten wert.</p> </blockquote><p>wenn sie also eine anwendung in mehreren zeitzonen verwenden und das datum und die uhrzeit benötigen, um die einstellungen der einzelnen benutzer widerzuspiegeln, verwenden sie timestamp. wenn sie unabhängig von der zeitzone konsistenz benötigen, verwenden sie datetime</p> Ich sehe nicht, wie Sie schneller als mit SqlBulkCopy ausführen können. Auf unserem Projekt können wir 250K Zeilen in ungefähr 3 Minuten importieren, also scheint Ihre Rate ungefähr richtig zu sein. 

 Ich denke nicht, dass es parallel dazu helfen würde, selbst wenn es technisch möglich wäre. Wir führen nur jeweils einen Import aus, ansonsten beginnt SQL Azure damit, unsere Anforderungen zu terminieren. 

 In der Tat ist es manchmal nicht möglich, eine große Group-by-Abfrage gleichzeitig mit dem Import auszuführen. SQL Azure führt eine Menge Arbeit aus, um die Servicequalität zu gewährleisten. Dazu gehört auch das Zeitlimit für Anforderungen, die zu lange dauern, zu viele Ressourcen usw 

 Wenn Sie also mehrere große Masseneinsätze zur selben Zeit machen, werden Sie *wahrscheinlich* eine Auszeit haben. <p>dies ist eine <em> sehr </em> gute frage. oft können personen in einer organisation in der doku-historie herumschnüffeln, um zu sehen, was der autor über frühere revisionen gedacht hat.</p><p>eine möglichkeit, dies zu umgehen (nicht elegant), besteht darin, eine kopie des dokuments mit <strong>datei</strong> -&gt; <strong>kopie erstellen ...</strong> zu erstellen</p><p>diese kopie enthält den aktuellsten inhalt und startet den verlauf neu. dann können sie die ursprüngliche löschen, die neue umbenennen usw.</p> <p>ja, mysql kann (unter bestimmten bedingungen) einen index für die spalten in order by verwenden. mysql kann jedoch keinen index für <em> gemischt</em> asc, desc-reihenfolge nach (<code>select * from foo order by bar asc, pants desc</code>). wenn sie ihre anfrage und die anweisung create table teilen, können wir ihre frage genauer beantworten.</p><p>hinweise zum optimieren der order by-klauseln: <a href="http://dev.mysql.com/doc/refman/5.1/en/order-by-optimization.html">http://dev.mysql.com/doc/refman/5.1/en/order-by-optimization.html</a> </p><p><strong> bearbeiten 21.01.2012 08:53 </strong></p><p>es gab fragen zur quelle meiner aussage zur verwendung eines index mit gemischtem asc/desc im order by. aus der dokumentation <a href="http://dev.mysql.com/doc/refman/5.5/en/order-by-optimization.html">order by optimization</a> :</p><blockquote> <p>in einigen fällen kann mysql keine indizes verwenden, um order by aufzulösen, obwohl es weiterhin indizes verwendet, um die zeilen zu finden, die mit der where-klausel übereinstimmen. diese fälle umfassen folgendes:</p> <p>...</p> <p>sie mischen asc und desc:</p> <p>select * from t1 order by key\_part1 desc, key\_part2 asc;</p> <p>...</p> </blockquote><p>außerdem lautet das desc-schlüsselwort <a href="http://dev.mysql.com/doc/refman/5.5/en/create-index.html">bedeutungslos</a> in create index</p><blockquote> <p>eine index\_col\_name-spezifikation kann mit asc oder desc enden. diese schlüsselwörter sind für zukünftige erweiterungen zur angabe des speichers für aufsteigende oder absteigende indexwerte zulässig. derzeit werden sie analysiert, aber ignoriert. indexwerte werden immer in aufsteigender reihenfolge gespeichert.</p> </blockquote> <p>es ist eine einschränkung des dbms - soweit ich weiß in allen. und das nicht nur beim hinzufügen einer spalte, sondern auch beim neuanordnen von spalten. wenn wir eine <code>unique</code> -einschränkung für <code>(a1, a2)</code> haben, können wir keine <code>foreign key</code> hinzufügen, die <code>references (a2, a1)</code>, es sei denn, es gibt eine eindeutige einschränkung für diese <code>(a2, a1)</code> das ist im wesentlichen redundant.</p><p>es wäre nicht besonders schwierig, dies als feature hinzuzufügen:</p><blockquote> <p>wenn es eine <code>unique</code> -einschränkung für <code>(a)</code> gibt, ist auch jede <code>(a, b, c, ..., z)</code> oder <code>(b,c, ...a, ...z)</code> kombination garantiert <code>unique</code>.</p> </blockquote><p>oder die verallgemeinerung:</p><blockquote> <p>wenn für <code>(a1, a2, ..., an)</code> eine <code>unique</code> -einschränkung besteht, ist auch jede <code>(a1, a2, ..., an, b1, b2, ..., bm)</code> - kombination oder neuanordnung <code>unique</code> garantiert.</p> </blockquote><p>es scheint, dass es nicht gefragt wurde oder nicht als hoch genug eingestuft wurde, um implementiert zu werden.</p><p>sie können jederzeit - im jeweiligen kanal - eine anfrage für die zu implementierende funktion stellen. oder implementieren sie es sogar selbst, wenn das dbms open source ist, wie postgres.</p> <p>es gibt keine einfache antwort auf ihre frage, aber hier sind einige dinge, über die sie nachdenken sollten.</p><p>erstens ist die skalierung nicht das einzige, worüber man sich sorgen machen muss. was sie mit ihren daten machen, ist. wenn sie 500 tabellen mit 30 tb daten) haben und einfach oltp mit sehr wenig berichterstellung) arbeiten, werden sie wahrscheinlich nicht zu viele probleme haben es gibt 32-tb-datenbanken auf postgresql. gleichzeitig wird sich die leistung jedoch etwas verschlechtern, da die festplatte auf alles übertragen werden muss. wenn sie über 50 tb daten verfügen, aber häufig einen treffer von etwa 100 gb haben, können sie dies auch erstellen sie einen server mit genügend ram, um diesen teil der datenbank im speicher zu halten, und sie sind golden.</p><p>wenn sie jedoch versuchen, den modus (häufigster wert) aus 1 tb daten zu entfernen, spielt es keine rolle, welches system sie verwenden. dies wird <em> schmerzhaft sein </em> mit oder ohne scherben. (bearbeiten: sharding kann dieses problem <em> tatsächlich verschlimmern. </em>)</p><p>die hauptprobleme, auf die sie mit riesigen datenbanken unter mysql und postgresql stoßen werden, sind die tatsache, dass keine der beiden parallelen zwischen abfragen unterstützt. mit anderen worten, eine abfrage wird von einem einzelnen thread als einzelner block ausgeführt und kann nicht in teile zerlegt und separat ausgeführt werden. dies ist meistens ein problem, wenn große analytische abfragen über große datenmengen ausgeführt werden. hier kommen postgres-xc und green plum zur rettung, da sie speicher von ausführung trennen und dies auf koordinatorebene tun können. beachten sie, dass postgres-xc und green plum im wesentlichen intern sharding verwenden, die koordinatoren jedoch die gesamte konsistenz global erzwingen.</p><p>mit intraquery-parallelität können sie die abfrage aufteilen, verschiedene prozessoren/festplatten-e/a-kanäle teile davon ausführen lassen und teile der ergebnismenge zurückmelden, die zusammengestellt und an die anwendung zurückgegeben werden sollen. auch dies ist normalerweise am hilfreichsten bei analytischen und nicht bei transaktionsverarbeitungslasten.</p><p>die zweite sache ist, dass einige systeme wie vertica oder greenplum informationsspalten zusammen speichern. dies erschwert die verwendung des systems aus der perspektive von oltp] und verringert die leistung dort, erhöht jedoch die leistung für große analytische workloads drastisch. dies ist also ein workload-spezifischer kompromiss.</p><p>die antwort ist also, dass sie <em>, sobald sie über 1-2 tb in der größe) </em> stehen, möglicherweise mit einer zahl konfrontiert werden dies ist wiederum spezifisch für datenbanken, größe der arbeitssätze usw. an diesem punkt müssen sie sich jedoch wirklich für schneeflockensysteme entscheiden, dh solche, die einzigartig und auf ihre arbeitslast zugeschnitten sind.</p><p>dies bedeutet natürlich, dass die grenzwerte im allgemeinen nicht quantifizierbar sind.</p><p><strong>bearbeiten</strong>: ich habe jetzt mit einer 9-tb-datenbank gearbeitet, die eine mischung aus entscheidungsunterstützung und transaktionsverarbeitungs-workloads in postgresql verarbeitet. die größte herausforderung besteht darin, dass sie bei fragen, die große teile des datensatzes betreffen, eine weile auf die antwort warten müssen.</p><p>bei sorgfältiger beachtung der grundlagen (einschließlich indizes, autovakuum, funktionsweise auf niedrigem niveau usw.) und ausreichender rechenressourcen sind diese jedoch vollständig verwaltbar (und ich schätze, dass sie bis weit in den 30-tb-bereich in pg verwaltbar sind).</p><p><strong>edit2</strong>: sobald sie 100 tb erreicht haben, hängt es von ihrem datensatz ab, was funktioniert. ich arbeite gerade an einem, der nicht in diesen bereich skaliert, da er zuerst das limit von 32 tb pro tabelle in postgresql erreicht.</p> <p>verwenden sie <code>addfield()</code>. hier ist ein beispiel:</p><pre><code>$query = db\_select('super\_long\_table', 'slt'); $query-&gt;addfield('slt', 'mys\_super\_long\_field', 'mslf'); </code></pre><p>an <code>addfield()</code> übergebene argumente sind:</p><ul> <li>tabellenalias</li> <li>feld - ihr langer feldname wird hier sein</li> <li>alias ​​- ihr kurzfeld-alias ​​wird hier sein</li> </ul><p>lesen sie mehr darüber <a href="https://api.drupal.org/api/drupal/includes!database!select.inc/function/selectqueryinterface%3a%3aaddfield/7">hier</a> .</p>