Guide d'optimisation des performances de pagination MySQL

De nombreuses applications n'affichent souvent que les enregistrements les plus récents ou les plus populaires, mais afin de rendre accessibles les anciens enregistrements, une barre de navigation par pagination est nécessaire. Cependant, comment mieux implémenter la pagination via MySQL a toujours été un casse-tête. Bien qu’il n’existe pas de solution standard, comprendre les couches sous-jacentes d’une base de données peut aider à optimiser les requêtes paginées.

Jetons un coup d'œil à une requête couramment utilisée avec des performances médiocres.

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

Cette requête prend 0,00 seconde. Alors, quel est le problème avec cette requête ? En fait, cette instruction et ces paramètres de requête ne posent aucun problème, car ils utilisent la clé primaire du tableau ci-dessous et ne lisent que 15 enregistrements.

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

Le vrai problème est lorsque le décalage (décalage de pagination) est important, comme suit :

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

La requête ci-dessus prend 0,22 seconde lorsqu'il y a 2 millions de lignes d'enregistrements. En affichant le plan d'exécution SQL via EXPLAIN, vous pouvez constater que SQL a récupéré 100 015 lignes, mais que seulement 15 lignes ont été nécessaires à la fin. Les décalages de pagination importants augmentent les données utilisées et MySQL charge beaucoup de données en mémoire qui ne seront finalement pas utilisées. Même si nous supposons que la plupart des utilisateurs de sites Web n’accèdent qu’aux premières pages de données, un petit nombre de requêtes avec des décalages de page importants peuvent nuire à l’ensemble du système. Facebook en est conscient, mais au lieu d'optimiser la base de données afin de traiter plus de requêtes par seconde, Facebook se concentre sur la réduction de la variance des temps de réponse aux requêtes.

Pour les demandes de pagination, il existe une autre information également très importante, à savoir le nombre total d'enregistrements. Nous pouvons facilement obtenir le nombre total d’enregistrements grâce à la requête suivante.

SELECT COUNT(*)
FROM city;

Cependant, le SQL ci-dessus prend 9,28 secondes lors de l'utilisation d'InnoDB comme moteur de stockage. Une optimisation incorrecte consiste à utiliser SQL_CALC_FOUND_ROWS. SQL_CALC_FOUND_ROWS peut préparer le nombre d'enregistrements qui remplissent les conditions à l'avance lors de la requête de pagination, puis simplement exécuter une sélection FOUND_ROWS(); Mais dans la plupart des cas, des instructions de requête plus courtes ne signifient pas une amélioration des performances. Malheureusement, cette méthode de requête de pagination est utilisée dans de nombreux frameworks traditionnels. Jetons un coup d'œil aux performances de requête de cette instruction.

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

Cette déclaration prend 20,02 secondes, soit deux fois plus de temps que la précédente. Il s'avère que l'utilisation de SQL_CALC_FOUND_ROWS pour la pagination est une très mauvaise idée.

Voyons comment optimiser. L'article est divisé en deux parties. La première partie explique comment obtenir le nombre total d'enregistrements et la deuxième partie consiste à obtenir les enregistrements réels.

Calculer efficacement le nombre de lignes

Si le moteur utilisé est MyISAM, vous pouvez directement exécuter COUNT(*) pour obtenir le nombre de lignes. De même, dans une table tas, le numéro de ligne est également stocké dans les métainformations de la table. Mais si le moteur est InnoDB, la situation sera plus compliquée, car InnoDB ne sauvegarde pas le nombre spécifique de lignes dans le tableau.
Nous pouvons mettre en cache le nombre de lignes, puis le mettre à jour régulièrement via un processus démon ou lorsque certaines opérations de l'utilisateur rendent le cache invalide, exécuter l'instruction suivante :

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

Obtenir l'enregistrement

Entrez maintenant la partie la plus importante de cet article pour que les enregistrements soient affichés en pagination. Comme mentionné ci-dessus, des décalages importants affecteront les performances, nous devons donc réécrire l'instruction de requête. Pour démonstration, nous créons un nouveau tableau "actualités", le trions par actualité (la dernière version est en haut), et implémentons une pagination performante. Par souci de simplicité, nous supposons que l’ID du dernier communiqué de presse est également le plus grand.

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

Une manière plus efficace est basée sur le dernier identifiant d'actualité affiché par l'utilisateur. L'instruction pour interroger la page suivante est la suivante. Vous devez transmettre le dernier identifiant affiché sur la page actuelle.

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

L'instruction pour interroger la page précédente est similaire, sauf que le premier identifiant de la page actuelle doit être transmis, et dans l'ordre inverse.

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

La méthode de requête ci-dessus convient à une pagination simple, c'est-à-dire qu'aucune navigation de page spécifique n'est affichée, seules la « page précédente » et la « page suivante » sont affichées. Par exemple, le pied de page d'un blog. affiche les boutons « Page précédente » et « Page suivante ». Mais s’il est encore difficile de réaliser une véritable navigation dans les pages, regardons une autre manière.

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）！