Explication détaillée de l'exemple de code pour optimiser la pagination dans MySQL

Une question d'entretien, comment effectuer une pagination lorsqu'il y a une grande quantité de données dans la table MySQL. . . . À cette époque, je savais que les tableaux pouvaient être divisés uniquement lorsque la quantité de données était importante, mais je ne savais pas quoi faire sans diviser les tableaux. . . . Hélas, qui a demandé à l'agent de ne disposer que de quelques données et d'une simple limite et décalage pour les détenir complètement (couverture du visage). . .

De nombreuses applications ont tendance à afficher uniquement les enregistrements les plus récents ou les plus populaires, mais pour que les anciens enregistrements restent accessibles, 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 très 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 le 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 instruction prend 20,02 secondes, soit deux fois plus longtemps 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écutez l'instruction suivante :

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

Obtenir un enregistrement

Entrez maintenant dans la partie la plus importante de cet article et obtenez les enregistrements à afficher 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 permettant d'interroger la page précédente est similaire, sauf que le premier ID de la page actuelle doit être transmis et que l'ordre doit être inversé.

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 la « page précédente » et la « page suivante ». " bouton. 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;

Grâce à l'instruction ci-dessus, un identifiant correspondant au décalage peut être calculé pour chaque bouton de pagination. Il y a un autre avantage à cette approche. Supposons qu'un nouvel article soit publié sur le site Web, la position de tous les articles sera reculée d'une position, donc si l'utilisateur change de page lors de la publication d'un article, il verra l'article deux fois. Si l'ID de décalage de chaque bouton est corrigé, ce problème sera résolu. Mark Callaghan a publié un blog similaire, utilisant des index combinés et deux variables de position, mais l'idée de base est la même.

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

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;

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

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!