Leitfaden zur Optimierung der MySQL-Paging-Leistung

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, kann das Verständnis der zugrunde liegenden Schichten einer Datenbank dabei helfen, 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) 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, aber am Ende 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 Information, die ebenfalls sehr wichtig ist, 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.

Sehen wir uns an, wie man optimiert. 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.

Zählen Sie effizient die Anzahl der Zeilen

Wenn die verwendete Engine MyISAM ist, können Sie COUNT(*) direkt ausführen, um die Anzahl der Zeilen zu erhalten. 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);

Get the record

Geben Sie nun den wichtigsten Teil dieses Artikels ein, um die Datensätze in Paginierung anzuzeigen. 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;

Eine effizientere Methode basiert auf der letzten vom Benutzer angezeigten Nachrichten-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 zwar in umgekehrter Reihenfolge.

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“. Beispielsweise die Fußzeile eines Blogs Zeigt die Schaltflächen „Vorherige Seite“ und „Nächste Seite“ an. 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;

通过上面的语句可以为每一个分页的按钮计算出一个offset对应的id。这种方法还有一个好处。假设，网站上正在发布一片新的文章，那么所有文章的位置都会往后移一位，所以如果用户在发布文章时换页，那么他会看见一篇文章两次。如果固定了每个按钮的offset Id，这个问题就迎刃而解了。Mark Callaghan发表过一篇类似的博客，利用了组合索引和两个位置变量，但是基本思想是一致的。

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

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;

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

以上就是MySQL分页性能优化指南的内容，更多相关内容请关注PHP中文网（m.sbmmt.com）！