Datenbank-SQL-Optimierung ist ein allt?gliches Problem, wenn es um Paging-Abfragen mit Millionen von Datenmengen geht. Welche guten Optimierungsvorschl?ge gibt es? Einige h?ufig verwendete Methoden sind unten als Referenz und zum Lernen aufgeführt!

Methode 1: Direkte Verwendung der von der Datenbank bereitgestellten SQL-Anweisung

• Anweisungsstil: In MySQL sind die folgenden Methoden verfügbar: SELECT * FROM table name LIMIT M,N
• Adaptive Szenarien: Geeignet für Situation mit kleinen Datenmengen (Tupel-Hunderte/Tausende von Ebenen)
• Grund/Nachteil: Der vollst?ndige Tabellenscan ist sehr langsam und einige Datenbank-Ergebniss?tze geben instabile Ergebnisse zurück (z. B. wird einmal 1, 2, 3 und ein anderes Mal zurückgegeben). gibt 2, 1,3 zurück. Die Grenze besteht darin, N Ausgaben von der M-Position der Ergebnismenge zu nehmen und den Rest zu verwerfen.
  

Methode 2: Erstellen Sie einen Prim?rschlüssel oder einen eindeutigen Index und verwenden Sie den Index ( unter der Annahme von 10 Elementen pro Seite)

• Anweisungsstil: In MySQL k?nnen die folgenden Methoden verwendet werden: SELECT * FROM table name WHERE id_pk > (pageNum*10) LIMIT M
• Adaptive Szenarien: Geeignet für Situationen mit gro?en Datenmengen (Zehntausende von Tupeln)
• Grund: Das Scannen des Index erfolgt sehr schnell. Da die Datenabfrage nicht nach pk_id sortiert ist, gibt es F?lle, in denen Daten fehlen 3
  

Methode 3: Basierend auf der Indexsortierung

• Anweisungsstil: In MySQL kann die folgende Methode verwendet werden: SELECT * FROM table name WHERE id_pk > (pageNum*10) ORDER BY id_pk ASC LIMIT M
• Adaptive Szenarien: Geeignet für Situationen mit gro?en Datenmengen (Element Die Anzahl der Gruppen betr?gt Zehntausende). Es ist am besten, dass das Spaltenobjekt nach ORDER BY der Prim?rschlüssel oder eindeutig ist, damit das ORDER BY Der Vorgang kann mithilfe des Index eliminiert werden, aber die Ergebnismenge ist stabil (zur Bedeutung von Stabilit?t siehe Methode 1). ist gef?lscht, echtes DESC wird in Zukunft durchgeführt, ich freue mich auf ...). Gibt die Anzahl der Tupel pro Seite an
• 
  Anweisungsstil: In MySQL kann die folgende Methode verwendet werden: PREPARE stmt_name FROM SELECT * FROM table name WHERE id_pk > (?*?) ORDER BY id_pk ASC LIMIT M

Anpassen an Szenarien: gro?es Datenvolumen

Grund: Der Index-Scan erfolgt sehr schnell. Die Prepare-Anweisung ist schneller als die allgemeine Abfrageanweisung.

Methode 5: Wenn Sie MySQL zur Unterstützung von ORDER-Operationen verwenden, k?nnen Sie Indizes verwenden, um einige Tupel schnell zu finden und vollst?ndige Tabellenscans zu vermeiden.

• Zum Beispiel: Lesen Sie Tupel in den Zeilen 1000 bis 1019 (pk ist der Prim?rschlüssel). /einzigartiger Schlüssel). Variable)
• Beispiel für die Verwendung einer Unterabfrage:
  

  SELECT * FROM your_table WHERE pk>=1000 ORDER BY pk ASC LIMIT 0,20

Beispiel für die Verwendung einer Verbindung:

SELECT * FROM your_table WHERE id <=
(SELECT id FROM your_table ORDER BY id desc LIMIT ($page-1)*$pagesize ORDER BY id desc
LIMIT $pagesize

MySQL verwendet Limit-Paging für gro?e Datenmengen. Mit zunehmender Seitenzahl wird die Abfrageeffizienz geringer. Testexperiment

1. Verwenden Sie direkt ?Limit Start, Count Paging“-Anweisungen, was auch die in meinem Programm verwendete Methode ist:

SELECT * FROM your_table AS t1
JOIN (SELECT id FROM your_table ORDER BY id desc LIMIT ($page-1)*$pagesize AS t2
WHERE t1.id <= t2.id ORDER BY t1.id desc LIMIT $pagesize;

Wenn die Startseite klein ist, gibt es kein Leistungsproblem mit dem Schauen wir uns die Ausführungszeit von Paging ab 10, 100, 1000, 10000 an (nehmen Sie 20 Elemente pro Seite).

Wie folgt:

select * from product limit start, count

Wir haben gesehen, dass mit zunehmendem Startdatensatz auch die Zeit zunimmt. Dies zeigt, dass das Paging-Anweisungslimit eng mit der Startseitenzahl zusammenh?ngt, daher ?ndern wir den Startdatensatz in Schauen Sie sich das an 40 W (also ungef?hr der durchschnittliche Rekord)

select * from product limit 10, 20 0.016秒
select * from product limit 100, 20 0.016秒
select * from product limit 1000, 20 0.047秒
select * from product limit 10000, 20 0.094秒

Sehen Sie sich die Zeit an, die wir ben?tigt haben, um die letzte Seite mit Datens?tzen zu erhalten

select * from product limit 400000, 20 3.229秒

Für eine Seite mit der gr??ten Seitenzahl wie dieser ist diese Zeit natürlich unertr?glich.

Daraus k?nnen wir auch zwei Dinge schlie?en: Die Abfragezeit der Limit-Anweisung ist proportional zur Position des Startdatensatzes.

Die Limit-Anweisung von MySQL ist sehr praktisch, eignet sich jedoch nicht für die direkte Verwendung auf Tabellen mit viele Rekorde.

2. Leistungsoptimierungsmethode für das Limit-Paging-Problem

Verwenden Sie den Covering-Index der Tabelle, um Paging-Abfragen zu beschleunigen

Wir alle wissen, dass, wenn die Anweisung, die die Indexabfrage verwendet, nur diese Indexspalte (Covering-Index) enth?lt ), dann wird dieser Fall schnell abgefragt.

Da es einen Optimierungsalgorithmus für die Indexsuche gibt und sich die Daten im Abfrageindex befinden, ist es nicht erforderlich, die relevante Datenadresse zu finden, was viel Zeit spart. Darüber hinaus gibt es in MySQL auch einen zugeh?rigen Index-Cache. Es ist besser, den Cache zu verwenden, wenn die Parallelit?t hoch ist.

In unserem Beispiel wissen wir, dass das ID-Feld der Prim?rschlüssel ist und daher natürlich den Standard-Prim?rschlüsselindex enth?lt. Sehen wir uns nun an, wie die Abfrage mithilfe des Covering-Index funktioniert.

Dieses Mal fragen wir die Daten der letzten Seite (unter Verwendung eines abdeckenden Indexes, der nur die ID-Spalte umfasst) wie folgt ab:

select * from product limit 866613, 20 37.44秒

1. Im Vergleich zu den 37,44 Sekunden für die Abfrage aller Spalten wird die Geschwindigkeit um etwa das Hundertfache erh?ht

那么如果我們也要查詢所有列，有兩種方法，一種是id>=的形式，另一種就是利用join，看下實際情況：

SELECT * FROM product WHERE ID > =(select id from product limit 866613, 1) limit 20

查詢時間為0.2秒！

另一種寫法

SELECT * FROM product a JOIN (select id from product limit 866613, 20) b ON a.ID = b.id

查詢時間也很短！

3. 復合索引優(yōu)化方法

MySql 性能到底能有多高？MySql 這個數(shù)據(jù)庫絕對是適合dba級的高手去玩的，一般做一點1萬篇新聞的小型系統(tǒng)怎么寫都可以，用xx框架可以實現(xiàn)快速開發(fā)。可是數(shù)據(jù)量到了10萬，百萬至千萬，他的性能還能那么高嗎？一點小小的失誤，可能造成整個系統(tǒng)的改寫，甚至更本系統(tǒng)無法正常運行！好了，不那么多廢話了。

用事實說話，看例子：

數(shù)據(jù)表 collect ( id, title ,info ,vtype) 就這4個字段，其中 title 用定長，info 用text, id 是逐漸，vtype是tinyint，vtype是索引。這是一個基本的新聞系統(tǒng)的簡單模型?，F(xiàn)在往里面填充數(shù)據(jù)，填充10萬篇新聞。最后collect 為 10萬條記錄，數(shù)據(jù)庫表占用硬1.6G。

OK ,看下面這條sql語句：

select id,title from collect limit 1000,10;

很快；基本上0.01秒就OK，再看下面的

select id,title from collect limit 90000,10;

從9萬條開始分頁，結果？

8-9秒完成，my god 哪出問題了？其實要優(yōu)化這條數(shù)據(jù)，網(wǎng)上找得到答案?？聪旅嬉粭l語句:

select id from collect order by id limit 90000,10;

很快，0.04秒就OK。為什么？因為用了id主鍵做索引當然快。網(wǎng)上的改法是：

select id,title from collect where id>=(select id from collect order by id limit 90000,1) limit 10;

這就是用了id做索引的結果?？墒菃栴}復雜那么一點點，就完了?？聪旅娴恼Z句

select id from collect where vtype=1 order by id limit 90000,10;

很慢，用了8-9秒！

到了這里我相信很多人會和我一樣，有崩潰感覺！vtype 做了索引了啊？怎么會慢呢？vtype做了索引是不錯，你直接

select id from collect where vtype=1 limit 1000,10;

是很快的，基本上0.05秒，可是提高90倍，從9萬開始，那就是0.05*90=4.5秒的速度了。和測試結果8-9秒到了一個數(shù)量級。

從這里開始有人提出了分表的思路，這個和dis #cuz 論壇是一樣的思路。思路如下：

建一個索引表：t (id,title,vtype) 并設置成定長，然后做分頁，分頁出結果再到 collect 里面去找info 。是否可行呢？實驗下就知道了。

10萬條記錄到 t(id,title,vtype) 里，數(shù)據(jù)表大小20M左右。用

select id from collect where vtype=1 limit 1000,10;

很快了?；旧?.1-0.2秒可以跑完。為什么會這樣呢？我猜想是因為collect 數(shù)據(jù)太多，所以分頁要跑很長的路。limit 完全和數(shù)據(jù)表的大小有關的。其實這樣做還是全表掃描，只是因為數(shù)據(jù)量小，只有10萬才快。OK， 來個瘋狂的實驗，加到100萬條，測試性能。加了10倍的數(shù)據(jù)，馬上t表就到了200多M，而且是定長。還是剛才的查詢語句，時間是0.1-0.2秒完成！分表性能沒問題？

錯！因為我們的limit還是9萬，所以快。給個大的，90萬開始

select id from t where vtype=1 order by id limit 900000,10;

看看結果，時間是1-2秒！why ?

分表了時間還是這么長，非常之郁悶！有人說定長會提高limit的性能，開始我也以為，因為一條記錄的長度是固定的，mysql 應該可以算出90萬的位置才對?。靠墒俏覀兏吖懒薽ysql 的智能，他不是商務數(shù)據(jù)庫，事實證明定長和非定長對limit影響不大？怪不得有人說discuz到了100萬條記錄就會很慢，我相信這是真的，這個和數(shù)據(jù)庫設計有關！

難道MySQL 無法突破100萬的限制嗎？？？到了100萬的分頁就真的到了極限？

答案是：NO 為什么突破不了100萬是因為不會設計mysql造成的。下面介紹非分表法，來個瘋狂的測試！一張表搞定100萬記錄，并且10G 數(shù)據(jù)庫，如何快速分頁！

好了，我們的測試又回到 collect表，開始測試結論是：

30萬數(shù)據(jù)，用分表法可行，超過30萬他的速度會慢道你無法忍受！當然如果用分表+我這種方法，那是絕對完美的。但是用了我這種方法后，不用分表也可以完美解決！

答案就是：復合索引！有一次設計mysql索引的時候，無意中發(fā)現(xiàn)索引名字可以任取，可以選擇幾個字段進來，這有什么用呢？

開始的

select id from collect order by id limit 90000,10;

這么快就是因為走了索引，可是如果加了where 就不走索引了。抱著試試看的想法加了 search(vtype,id) 這樣的索引。

然后測試

select id from collect where vtype=1 limit 90000,10;

非?？欤?.04秒完成！

再測試:

select id ,title from collect where vtype=1 limit 90000,10;

非常遺憾，8-9秒，沒走search索引！

再測試：search(id,vtype)，還是select id 這個語句，也非常遺憾，0.5秒。

綜上：如果對于有where 條件，又想走索引用limit的，必須設計一個索引，將where 放第一位，limit用到的主鍵放第2位，而且只能select 主鍵！

完美解決了分頁問題了?？梢钥焖俜祷豬d就有希望優(yōu)化limit ， 按這樣的邏輯，百萬級的limit 應該在0.0x秒就可以分完?？磥韒ysql 語句的優(yōu)化和索引時非常重要的！

Empfohlen: ?MySQL-Tutorial“

Das obige ist der detaillierte Inhalt vonMySQL-Paging-Abfragemethode für Millionen von Daten und ihre Optimierungsvorschl?ge. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!