MySQL: Paging optimieren

Eine Interviewfrage: Wie führt man Paging durch, wenn die MySQL-Tabelle eine große Datenmenge enthält? . . . Damals wusste ich nur, dass Tabellen geteilt werden können, wenn die Datenmenge groß ist, aber ich wusste nicht, was ich tun sollte, ohne Tabellen zu teilen. . . . Ach, der den Agenten gebeten hat, nur ein paar Daten und ein einfaches Limit und einen Offset zu haben, um sie vollständig zu halten (Gesichtsbedeckung). . .

Viele Anwendungen neigen dazu, nur die neuesten oder beliebtesten Datensätze anzuzeigen. Damit jedoch weiterhin auf alte Datensätze zugegriffen werden kann, ist eine Seitennavigationsleiste erforderlich. Die Frage, wie man Paging über MySQL besser implementieren kann, war jedoch schon immer ein Problem. Obwohl es keine Standardlösung gibt, hilft das Verständnis der zugrunde liegenden Datenbank mehr oder weniger dabei, paginierte Abfragen zu optimieren.

Werfen wir einen Blick auf eine häufig verwendete Abfrage mit schlechter Leistung.

SELECT *
FROM city
ORDER BY id DESC
LIMIT 0, 15

Diese Abfrage dauert 0,00 Sekunden. Was ist also an dieser Abfrage falsch? Tatsächlich gibt es mit dieser Abfrageanweisung und diesen Parametern kein Problem, da sie den Primärschlüssel der folgenden Tabelle verwenden und nur 15 Datensätze lesen.

CREATE TABLE city (
  id int(10) unsigned NOT NULL AUTO_INCREMENT,
  city varchar(128) NOT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB;

Das eigentliche Problem besteht darin, dass der Offset (Paging-Offset) sehr groß ist, wie im Folgenden:

SELECT *
FROM city
ORDER BY id DESC
LIMIT 100000, 15;

Die obige Abfrage dauert 0,22 Sekunden, wenn 2 Millionen Datensatzzeilen vorhanden sind. Wenn Sie den SQL-Ausführungsplan über EXPLAIN anzeigen, können Sie feststellen, dass die SQL 100015 Zeilen abgerufen hat, am Ende jedoch nur 15 Zeilen benötigt wurden. Große Paging-Offsets erhöhen den Datenverbrauch und MySQL lädt viele Daten in den Speicher, die letztendlich nicht verwendet werden. Auch wenn wir davon ausgehen, dass die meisten Website-Benutzer nur auf die ersten Datenseiten zugreifen, kann eine kleine Anzahl von Anfragen mit großen Seitenversätzen Schaden für das gesamte System verursachen. Facebook ist sich dessen bewusst, aber anstatt die Datenbank zu optimieren, um mehr Anfragen pro Sekunde zu bearbeiten, konzentriert sich Facebook darauf, die Varianz der Antwortzeiten auf Anfragen zu reduzieren.

Für Paging-Anfragen gibt es noch eine weitere sehr wichtige Information, nämlich die Gesamtzahl der Datensätze. Mit der folgenden Abfrage können wir die Gesamtzahl der Datensätze leicht ermitteln.

SELECT COUNT(*)
FROM city;

Allerdings dauert das obige SQL 9,28 Sekunden, wenn InnoDB als Speicher-Engine verwendet wird. Eine falsche Optimierung besteht darin, SQL_CALC_FOUND_ROWS zu verwenden, um die Anzahl der Datensätze, die die Bedingungen erfüllen, im Voraus während der Paging-Abfrage vorzubereiten und dann einfach eine Auswahl von FOUND_ROWS() auszuführen. In den meisten Fällen bedeuten kürzere Abfrageanweisungen jedoch keine verbesserte Leistung. Leider wird diese Paging-Abfragemethode in vielen Mainstream-Frameworks verwendet. Schauen wir uns die Abfrageleistung dieser Anweisung an.

SELECT SQL_CALC_FOUND_ROWS *
FROM city
ORDER BY id DESC
LIMIT 100000, 15;

Diese Anweisung dauert 20,02 Sekunden, doppelt so lange wie die vorherige. Es stellt sich heraus, dass die Verwendung von SQL_CALC_FOUND_ROWS für Paging eine sehr schlechte Idee ist.

Werfen wir einen Blick auf die Optimierungsmöglichkeiten. Der Artikel ist in zwei Teile unterteilt. Im ersten Teil geht es darum, wie man die Gesamtzahl der Datensätze erhält, und im zweiten Teil geht es darum, die tatsächlichen Datensätze zu erhalten.

Berechnen Sie effizient die Anzahl der Zeilen

Wenn die verwendete Engine MyISAM ist, können Sie COUNT(*) direkt ausführen, um die Anzahl der Zeilen abzurufen. Ebenso wird in einer Heap-Tabelle die Zeilennummer auch in den Metainformationen der Tabelle gespeichert. Wenn die Engine jedoch InnoDB ist, wird die Situation komplizierter, da InnoDB nicht die spezifische Anzahl von Zeilen in der Tabelle speichert.

Wir können die Anzahl der Zeilen zwischenspeichern und sie dann regelmäßig über einen Daemon-Prozess aktualisieren. Wenn einige Benutzervorgänge dazu führen, dass der Cache ungültig wird, führen Sie die folgende Anweisung aus:

SELECT COUNT(*)
FROM city
USE INDEX(PRIMARY);

Holen Sie sich Datensätze

Geben Sie nun den wichtigsten Teil dieses Artikels ein und erhalten Sie die Datensätze, die in der Paginierung angezeigt werden sollen. Wie oben erwähnt, beeinträchtigen große Offsets die Leistung, daher müssen wir die Abfrageanweisung neu schreiben. Zur Demonstration erstellen wir eine neue Tabelle „News“, sortieren diese nach Aktualität (die neueste Veröffentlichung steht ganz oben) und implementieren ein leistungsstarkes Paging. Der Einfachheit halber gehen wir davon aus, dass die ID der neuesten Pressemitteilung auch die größte ist.

CREATE TABLE news(
   id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
   title VARCHAR(128) NOT NULL
) ENGINE=InnoDB;

Ein effizienterer Weg basiert auf der letzten vom Benutzer angezeigten News-ID. Die Anweisung zur Abfrage der nächsten Seite lautet wie folgt: Sie müssen die letzte auf der aktuellen Seite angezeigte ID übergeben.

SELECT *
FROM news WHERE id < $last_id
ORDER BY id DESC
LIMIT $perpage

Die Anweisung zum Abfragen der vorherigen Seite ist ähnlich, außer dass die erste ID der aktuellen Seite übergeben werden muss und die Reihenfolge umgekehrt werden muss.

SELECT *
FROM news WHERE id > $last_id
ORDER BY id ASC
LIMIT $perpage

Die obige Abfragemethode eignet sich für einfaches Paging, d. h. es wird keine spezifische Seitennavigation angezeigt, sondern nur „Vorherige Seite“ und „Nächste Seite“. In der Fußzeile eines Blogs werden beispielsweise „Vorherige Seite“ und „Nächste Seite“ angezeigt " Taste. Wenn es jedoch immer noch schwierig ist, eine echte Seitennavigation zu erreichen, schauen wir uns einen anderen Weg an.

SELECT id
FROM (
   SELECT id, ((@cnt:= @cnt + 1) + $perpage - 1) % $perpage cnt
   FROM news 
   JOIN (SELECT @cnt:= 0)T
   WHERE id < $last_id
   ORDER BY id DESC
   LIMIT $perpage * $buttons
)C
WHERE cnt = 0;

Mit der obigen Anweisung kann für jede Paging-Schaltfläche eine dem Offset entsprechende ID berechnet werden. Dieser Ansatz hat noch einen weiteren Vorteil. Angenommen, ein neuer Artikel wird auf der Website veröffentlicht, dann wird die Position aller Artikel um eine Position nach hinten verschoben. Wenn der Benutzer also beim Veröffentlichen eines Artikels die Seite wechselt, sieht er einen Artikel zweimal. Wenn die Offset-ID jeder Schaltfläche festgelegt ist, wird dieses Problem gelöst. Mark Callaghan hat einen ähnlichen Blog veröffentlicht, der kombinierte Indizes und zwei Positionsvariablen verwendet, aber die Grundidee ist dieselbe.

　　如果表中的记录很少被删除、修改，还可以将记录对应的页码存储到表中，并在该列上创建合适的索引。采用这种方式，当新增一个记录的时候，需要执行下面的查询重新生成对应的页号。

SET p:= 0;
UPDATE news SET page=CEIL((p:= p + 1) / $perpage) ORDER BY id DESC;

　　当然，也可以新增一个专用于分页的表，可以用个后台程序来维护。

UPDATE pagination T
JOIN (
   SELECT id, CEIL((p:= p + 1) / $perpage) page
   FROM news
   ORDER BY id
)C
ON C.id = T.id
SET T.page = C.page;

　　现在想获取任意一页的元素就很简单了：

SELECT *
FROM news A
JOIN pagination B ON A.id=B.ID
WHERE page=$offset;

　　还有另外一种与上种方法比较相似的方法来做分页，这种方式比较试用于数据集相对小，并且没有可用的索引的情况下—比如处理搜索结果时。在一个普通的服务器上执行下面的查询，当有2M条记录时，要耗费2sec左右。这种方式比较简单，创建一个用来存储所有Id的临时表即可(这也是最耗费性能的地方)。

CREATE TEMPORARY TABLE _tmp (KEY SORT(random))
SELECT id, FLOOR(RAND() * 0x8000000) random
FROM city;

ALTER TABLE _tmp ADD OFFSET INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, DROP INDEX SORT, ORDER BY random;

　　接下来就可以向下面一样执行分页查询了。

SELECT *
FROM _tmp
WHERE OFFSET >= $offset
ORDER BY OFFSET
LIMIT $perpage;

　　简单来说，对于分页的优化就是。。。避免数据量大时扫描过多的记录。

　　博客比较长，所以翻译的有些粗糙。。。，之后会在好好检查一遍的。在自己做测试时，有些查询时间与作者有点不一致，不过作者这篇博客是写于2011年的，so~不要在意具体数据，领会精神吧~~

Das obige ist der detaillierte Inhalt vonMySQL: Paging optimieren. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!