So optimieren Sie MySQL für zig Millionen schnelles Paging

Die Verarbeitung der MySQL-Datenbankoptimierung ermöglicht zig Millionen schnelle Paging-Analysen. Werfen wir einen Blick darauf.
Datentabellensammlung (id, title, info, vtype) hat diese 4 Felder, von denen der Titel eine feste Länge hat, die info ein Text ist, die id graduell ist, der vtype ein Tinyint ist und der vtype ein Index ist. Dies ist ein einfaches Modell eines grundlegenden Nachrichtensystems. Füllen Sie es nun mit Daten und mit 100.000 Nachrichten.
Die endgültige Sammlung umfasst 100.000 Datensätze und die Datenbanktabelle belegt 1,6 GB Festplatte. OK, schauen Sie sich die folgende SQL-Anweisung an:
wählen Sie ID,Titel aus Sammellimit 1000,10 aus. Das Paging beginnt bei 90.000 Artikeln, das Ergebnis?
In 8-9 Sekunden fertig, was ist los mit meinem Gott? ? ? ? Wenn Sie diese Daten optimieren möchten, finden Sie die Antwort sogar online. Schauen Sie sich die folgende Anweisung an:
Wählen Sie die ID aus der Sammelbestellung nach ID-Limit 90000,10 aus. Es ist sehr schnell, es ist in 0,04 Sekunden in Ordnung. Warum? Denn die Verwendung des id-Primärschlüssels zur Indizierung ist natürlich schneller. Die Online-Änderung lautet:
select id,title from Collect where id>=(select id from Collect order by id limit 90000,1) limit 10;
Dies ist das Ergebnis der Verwendung von id für die Indizierung. Aber wenn das Problem nur ein bisschen kompliziert ist, ist es vorbei. Schauen Sie sich die folgende Anweisung an:
select id from Collect where vtype=1 order by id limit 90000,10; Es ist sehr langsam, es dauerte 8-9 Sekunden!
Ich glaube, an diesem Punkt wird es vielen Menschen genauso gehen wie mir, sie fühlen sich zusammengebrochen! Ist vtype indiziert? Warum ist es so langsam? Es ist gut, den vtype indiziert zu haben, wobei vtype=1 auf 1000,10 begrenzt ist, was im Grunde genommen 0,05 Sekunden beträgt, aber ab 90.000 um das 90-fache erhöht werden kann. 90=4,5 Sekunden Geschwindigkeit. Und das Testergebnis liegt bei 8-9 Sekunden um eine Größenordnung. Von hier aus begann jemand, die Idee von Untertabellen vorzuschlagen, die dieselbe Idee wie das Discuz-Forum ist. Die Idee ist wie folgt:
Erstellen Sie eine Indextabelle: t (id, title, vtype) und legen Sie sie auf eine feste Länge fest, führen Sie dann Paging durch, paginieren Sie die Ergebnisse aus und gehen Sie dann zum Sammeln, um Informationen zu finden. Ist es möglich? Sie werden es durch Experimentieren herausfinden.
100.000 Datensätze werden in t(id,title,vtype) gespeichert und die Größe der Datentabelle beträgt etwa 20 MB. Verwenden Sie
select id from t where vtype=1 order by id limit 90000,10; Grundsätzlich kann es in 0,1–0,2 Sekunden ausgeführt werden. Warum passiert das? Ich schätze, das liegt daran, dass zu viele Daten erfasst werden und daher das Paging sehr lange dauert. Die Grenze hängt vollständig von der Größe der Datentabelle ab. Tatsächlich handelt es sich immer noch um einen vollständigen Tabellenscan, nur weil die Datenmenge gering ist und nur bei 100.000 Daten schneller ist. OK, machen wir ein verrücktes Experiment, addieren es zu 1 Million und testen die Leistung.
Nach dem Hinzufügen des Zehnfachen der Daten erreichte die t-Tabelle sofort mehr als 200 MB und hatte eine feste Länge. Die Abfrageanweisung dauert derzeit 0,1 bis 0,2 Sekunden! Gibt es ein Problem mit der Leistung der Untertabelle? falsch! Da unser Limit immer noch bei 90.000 liegt, geht es schnell. Probieren Sie es aus, beginnen Sie bei 900.000
Wählen Sie die ID aus t, wobei vtype=1 nach ID-Limit 900.000,10 sortiert ist. Schauen Sie sich das Ergebnis an, die Zeit beträgt 1-2 Sekunden!
Warum? Selbst nach der Aufteilung des Stundenplans ist die Zeit immer noch so lang, was sehr frustrierend ist! Einige Leute sagen, dass eine feste Länge die Leistung von Limit verbessert. Zuerst dachte ich, dass MySQL die Position 900.000 berechnen sollte, weil die Länge eines Datensatzes festgelegt ist, oder? Aber wir haben die Intelligenz von MySQL überschätzt. Es hat sich gezeigt, dass feste Länge und nicht feste Länge kaum Einfluss auf das Limit haben. Kein Wunder, dass manche Leute sagen, dass Discuz sehr langsam sein wird, wenn es 1 Million Datensätze erreicht. Das hängt mit dem Datenbankdesign zusammen.
Kann MySQL die 1-Millionen-Grenze nicht durchbrechen? ? ? Ist es wirklich die Grenze, wenn 1 Million Seiten erreicht sind? ? ?
Die Antwort lautet: NEIN!!!! Der Grund, warum es 1 Million nicht überschreiten kann, liegt darin, dass es nicht weiß, wie man MySQL entwirft. Lassen Sie uns die Nicht-Tabellen-Methode vorstellen und einen verrückten Test durchführen! Eine Tabelle kann 1 Million Datensätze und eine 10G-Datenbank verarbeiten. So können Sie schnell paginieren!
Okay, unser Test ist zur Sammeltabelle zurückgekehrt. Die Schlussfolgerung lautet: 300.000 Daten, die Verwendung der Split-Table-Methode ist machbar. Wenn sie 300.000 überschreitet, wird die Geschwindigkeit unerträglich sein. Wenn Sie die Methode „sub-table + me“ verwenden, ist dies natürlich absolut perfekt. Aber nachdem ich meine Methode verwendet habe, kann es perfekt gelöst werden, ohne die Tabelle zu teilen!
Die Antwort lautet: zusammengesetzter Index! Beim Entwerfen eines MySQL-Index habe ich versehentlich festgestellt, dass der Indexname beliebig gewählt werden kann und Sie mehrere Felder zur Eingabe auswählen können. Die anfängliche Auswahl-ID aus der Sammelreihenfolge nach ID-Limit 90000,10 ist so schnell, weil der Index verwendet wird, aber wenn Sie wo hinzufügen, wird der Index nicht verwendet. Ich habe einen Index wie search(vtype,id) hinzugefügt, mit der Idee, es auszuprobieren. Testen Sie dann
select id from Collect where vtype=1 limit 90000,10; In 0,04 Sekunden erledigt!
Erneut testen: ID,Titel aus Sammlung auswählen, wobei vtype=1 limitiert ist 90000,10; Sehr leid, 8-9 Sekunden, kein Suchindex!
Erneut testen: Suche (id, vtype) oder id-Anweisung auswählen, was ebenfalls sehr bedauerlich ist, 0,5 Sekunden.
Um es zusammenzufassen: Wenn es eine Where-Bedingung gibt und Sie Limit für die Indizierung verwenden möchten, müssen Sie einen Index entwerfen, zuerst Where und dann den von Limit verwendeten Primärschlüssel eingeben, und Sie können nur den Primärschlüssel auswählen!
Das Paging-Problem wurde perfekt gelöst. Wenn Sie die ID schnell zurückgeben können, können Sie hoffen, das Limit zu optimieren. Nach dieser Logik sollte ein Limit auf Millionenebene durch 0,0x Sekunden geteilt werden. Es scheint, dass die Optimierung und Indizierung von MySQL-Anweisungen sehr wichtig ist!
Okay, zurück zur ursprünglichen Frage: Wie können wir die oben genannten Forschungsergebnisse erfolgreich und schnell auf die Entwicklung anwenden? Wenn ich zusammengesetzte Abfragen verwende, ist mein leichtgewichtiges Framework nutzlos. Ich muss die Paging-Zeichenfolge selbst schreiben. Hier ist ein weiteres Beispiel, und die Idee kommt heraus:
select * from Collect where id in (9000,12,50,7000); Es kann in 0 Sekunden überprüft werden!
Mein Gott, der Index von MySQL ist auch für die In-Anweisung wirksam! Es scheint, dass die Aussage im Internet, dass „in nicht indiziert werden kann“, falsch ist!
Mit dieser Schlussfolgerung kann es leicht auf leichte Frameworks angewendet werden:
Der Code lautet wie folgt:
$db=dblink();
$db->pagesize=20;
$sql="select id from Collect where vtype=$vtype";
$db->execute($sql);
$strpage=$db->strpage(); //Paging Die Zeichenfolge ist zur einfachen Ausgabe in einer temporären Variablen gespeichert
while($rs=$db->fetch_array()){
$strid.=$rs['id'].',';
}
$strid=substr($strid,0,strlen($strid)-1); //Konstruieren Sie die ID-Zeichenfolge
$db->pagesize=0; //Sehr wichtig, melden Sie sich nicht von der Klasse ab Löschen Sie in diesem Fall das Paging, sodass Sie die Datenbankverbindung nur einmal verwenden und nicht erneut öffnen müssen
$db->execute("select id,title,url,sTime,gTime, vtype,tag from Collect where id in ($strid)");
fetch_array()): ?>

 < ;?php echo $rs['id'];?>
 
 
 
 
 < ;a href="http://www.php1.cn/">  
< /tr>


echo $strpage;
Durch einfache Transformation Die Idee ist eigentlich ganz einfach: 1) Finden Sie durch Optimieren des Index die ID und buchstabieren Sie sie in eine Zeichenfolge wie „123,90000,12000“. 2) Die zweite Abfrage findet die Ergebnisse.
Ein kleiner Index + eine kleine Änderung ermöglichen es MySQL, Millionen oder sogar Dutzende Millionen effizienter Paging zu unterstützen!
Durch die Beispiele hier habe ich über etwas nachgedacht: Für große Systeme darf PHP keine Frameworks verwenden, insbesondere Frameworks, die nicht einmal SQL-Anweisungen sehen können! Denn mein Leichtbaugerüst wäre zunächst fast zusammengebrochen! Es eignet sich nur für die schnelle Entwicklung kleiner Anwendungen und großer Websites. Die Datenschicht, einschließlich der Logikschicht, kann das Framework nicht verwenden. Wenn Programmierer die Kontrolle über SQL-Anweisungen verlieren, steigt das Risiko des Projekts exponentiell! Insbesondere bei der Verwendung von MySQL muss MySQL einen professionellen DBA benötigen, um seine beste Leistung zu erzielen. Der durch einen Index verursachte Leistungsunterschied kann tausende Male betragen!
PS: Nach tatsächlichen Tests liegt die Grenze bei 1 Million Daten, 1,6 Millionen Daten, 15G-Tabelle, 190M-Index, selbst wenn der Index verwendet wird, bei 0,49 Sekunden. Daher ist es am besten, andere beim Paging nicht die Daten nach 100.000 Daten sehen zu lassen, da es sonst sehr langsam wird! Sogar mit einem Index. Nach einer solchen Optimierung hat MySQL die Grenze von Millionen Seiten erreicht! Aber ein solches Ergebnis ist schon sehr gut, wenn Sie SQLServer verwenden, wird es definitiv hängen bleiben! Die 1,6 Millionen Daten mit der ID in (str) sind sehr schnell, im Grunde immer noch 0 Sekunden. Wenn ja, sollte MySQL in der Lage sein, problemlos zig Millionen Daten zu verarbeiten.