Comment résoudre le problème de pagination profonde MySQL

Cet article vous apporte des connaissances pertinentes sur mysql. Il présente principalement la solution élégante au problème de pagination profonde mysql. Cet article explique comment optimiser le problème de pagination profonde lorsque la table mysql contient une grande quantité de données et joint le. pseudo-code d'un cas récent d'optimisation de problèmes SQL lents, j'espère qu'il sera utile à tout le monde.

Apprentissage recommandé : Tutoriel vidéo mysql

Dans le processus quotidien de développement de la demande, je pense que tout le monde sera familier avec limit, mais lors de l'utilisation de limit, lorsque le décalage (offset) est très grand, vous trouverez une requête efficacité De plus en plus lentement. Lorsque la limite est de 2 000 au début, l'interrogation des données requises peut prendre 200 ms. Cependant, lorsque la limite est de 4 000 avec un décalage de 100 000, vous constaterez que son efficacité de requête nécessite déjà environ 1S. de pire en pire.

Résumé

Cet article expliquera comment optimiser le problème de pagination profonde lorsque la table mysql contient une grande quantité de données, et joindra le pseudocode d'un cas récent d'optimisation du problème SQL lent.

1. Limiter la description du problème de pagination profonde

Jetons d'abord un coup d'œil à la structure de la table (donnez simplement un exemple, la structure de la table est incomplète et les champs inutiles ne seront pas affichés)

CREATE TABLE `p2p_detail_record` (
  `id` varchar(32) COLLATE utf8mb4_bin NOT NULL DEFAULT '' COMMENT '主键',
  `batch_num` int NOT NULL DEFAULT '0' COMMENT '上报数量',
  `uptime` bigint NOT NULL DEFAULT '0' COMMENT '上报时间',
  `uuid` varchar(64) COLLATE utf8mb4_bin NOT NULL DEFAULT '' COMMENT '会议id',
  `start_time_stamp` bigint NOT NULL DEFAULT '0' COMMENT '开始时间',
  `answer_time_stamp` bigint NOT NULL DEFAULT '0' COMMENT '应答时间',
  `end_time_stamp` bigint NOT NULL DEFAULT '0' COMMENT '结束时间',
  `duration` int NOT NULL DEFAULT '0' COMMENT '持续时间',
  PRIMARY KEY (`id`),
  KEY `idx_uuid` (`uuid`),
  KEY `idx_start_time_stamp` (`start_time_stamp`) //索引,
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='p2p通话记录详情表';

Supposons le SQL de pagination profonde que nous voulons la requête ressemble à ceci

select * 
from p2p_detail_record ppdr 
where ppdr .start_time_stamp >1656666798000 
limit 0,2000

L'efficacité de la requête est de 94 ms, est-ce rapide ? Donc si nous limitons 100 000 ou 2 000, l'efficacité des requêtes est de 1,5S, ce qui est déjà très lent. Et s'il y en avait plus ?

2. Analyse des causes de la lenteur de sql

Jetons un œil au plan d'exécution de ce sql

Il a également atteint l'index, alors pourquoi est-il toujours lent ? Passons d'abord en revue les points de connaissances pertinents de MySQL.

Index clusterisé et index non cluster

Index clusterisé : Les nœuds feuilles stockent la ligne entière de données.

Index non clusterisé : Le nœud feuille stocke la valeur de clé primaire correspondant à la ligne entière de données.

Le processus d'utilisation d'une requête d'index non clusterisé

• Trouvez le nœud feuille correspondant via l'arborescence d'index non clusterisé et obtenez la valeur de la clé primaire.
• Ensuite, récupérez la valeur de la clé primaire et revenez à l'arbre d'index clusterisé pour trouver la ligne entière de données correspondante. (L'ensemble du processus est appelé retour de table)

Retour à la question de savoir pourquoi ce SQL est lent, les raisons sont les suivantes

1 L'instruction limit analysera d'abord les lignes offset+n, puis supprimera les lignes. lignes de décalage précédentes. Après avoir renvoyé n lignes de données. En d'autres termes, limit 100000,10 analysera 100010 lignes, tandis que limit 0,10 analysera uniquement 10 lignes. Ici, nous devons revenir à la table 100010 fois, et beaucoup de temps est consacré au retour de la table. limit 100000,10，就会扫描100010行，而limit 0,10，只扫描10行。这里需要回表100010次，大量的时间都在回表这个上面。

方案核心思路： 能不能事先知道要从哪个主键ID开始，减少回表的次数

常见解决方案

通过子查询优化

select * 
from p2p_detail_record ppdr 
where id >= (select id from p2p_detail_record ppdr2 where ppdr2 .start_time_stamp >1656666798000 limit 100000,1) 
limit 2000

相同的查询结果，也是10W条开始的第2000条，查询效率为200ms，是不是快了不少。

标签记录法

标签记录法： 其实标记一下上次查询到哪一条了，下次再来查的时候，从该条开始往下扫描。类似书签的作用

select * from p2p_detail_record ppdr
where ppdr.id > 'bb9d67ee6eac4cab9909bad7c98f54d4'
order by id 
limit 2000

备注：bb9d67ee6eac4cab9909bad7c98f54d4是上次查询结果的最后一条ID

使用标签记录法，性能都会不错的，因为命中了idIdée de base de la solution :

Peut-on savoir à l'avance de quel ID de clé primaire partir, afin de réduire le nombre de retours de table ?

• Solutions courantes
• Optimiser via des sous-requêtes

  CREATE TABLE `p2p_detail_record` (
    `id` varchar(32) COLLATE utf8mb4_bin NOT NULL DEFAULT '' COMMENT '主键',
    `batch_num` int NOT NULL DEFAULT '0' COMMENT '上报数量',
    `uptime` bigint NOT NULL DEFAULT '0' COMMENT '上报时间',
    `uuid` varchar(64) COLLATE utf8mb4_bin NOT NULL DEFAULT '' COMMENT '会议id',
    `start_time_stamp` bigint NOT NULL DEFAULT '0' COMMENT '开始时间',
    `answer_time_stamp` bigint NOT NULL DEFAULT '0' COMMENT '应答时间',
    `end_time_stamp` bigint NOT NULL DEFAULT '0' COMMENT '结束时间',
    `duration` int NOT NULL DEFAULT '0' COMMENT '持续时间',
    PRIMARY KEY (`id`),
    KEY `idx_uuid` (`uuid`),
    KEY `idx_start_time_stamp` (`start_time_stamp`) //索引,
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='p2p通话记录详情表';

  La même requête le résultat est également le 2000e à partir d'un article de 100 000 articles, l'efficacité des requêtes est de 200 ms, ce qui est beaucoup plus rapide.

Méthode d'enregistrement des balises

• Méthode d'enregistrement des balises : En fait, marquez l'élément que vous avez vérifié la dernière fois. Lorsque vous vérifierez à nouveau la prochaine fois, lancez la numérisation à partir de cet élément.
Similaire à la fonction des signets

//最小ID 
String  lastId = null; 
//一页的条数 
Integer pageSize = 2000; 
List<P2pRecordVo> list ;
do{   
   list = listP2pRecordByPage(lastId,pageSize);    //标签记录法，记录上次查询过的Id 
   lastId = list.get(list.size()-1).getId();       //获取上一次查询数据最后的ID，用于记录
   //对数据的操作逻辑
   XXXXX();
 }while(isNotEmpty(list));
   
<select id ="listP2pRecordByPage">  
   select * 
   from p2p_detail_record ppdr where 1=1
   <if test = "lastId != null">
   and ppdr.id > #{lastId}
   </if>
   order by id asc
   limit #{pageSize}
</select>

En utilisant la méthode d'enregistrement des balises, les performances seront bonnes, car l'index id est atteint. Mais cette méthode présente plusieurs inconvénients

.

1. Vous ne pouvez interroger que sur des pages consécutives, pas sur plusieurs pages. 2. Un champ similaire à auto-incrémentation continue est nécessaire (orber par identifiant peut être utilisé).

• Comparaison des solutions
Utilisation de la méthode

d'optimisation des sous-requêtes

Avantages : Une requête multipage est possible et vous pouvez vérifier les données sur la page de votre choix.

🎜Inconvénients : 🎜 Pas aussi efficace que la 🎜méthode d'enregistrement par tag🎜. 🎜Raison :🎜 Par exemple, après avoir vérifié 100 000 éléments de données, vous devez également d'abord interroger le 1 000e élément de données correspondant à l'index non clusterisé, puis obtenir l'ID à partir du 100 000e élément pour la requête. 🎜🎜🎜Utilisation de la 🎜méthode d'enregistrement des tags🎜🎜🎜🎜🎜Avantages : 🎜 L'efficacité des requêtes est très stable et très rapide. 🎜🎜🎜Inconvénients :🎜🎜

• 不跨页查询，
• 需要一种类似连续自增的字段

关于第二点的说明： 该点一般都好解决，可使用任意不重复的字段进行排序即可。若使用可能重复的字段进行排序的字段，由于mysql对于相同值的字段排序是无序，导致如果正好在分页时，上下页中可能存在相同的数据。

实战案例

需求： 需要查询查询某一时间段的数据量，假设有几十万的数据量需要查询出来，进行某些操作。

需求分析 1、分批查询（分页查询），设计深分页问题，导致效率较慢。

CREATE TABLE `p2p_detail_record` (
  `id` varchar(32) COLLATE utf8mb4_bin NOT NULL DEFAULT '' COMMENT '主键',
  `batch_num` int NOT NULL DEFAULT '0' COMMENT '上报数量',
  `uptime` bigint NOT NULL DEFAULT '0' COMMENT '上报时间',
  `uuid` varchar(64) COLLATE utf8mb4_bin NOT NULL DEFAULT '' COMMENT '会议id',
  `start_time_stamp` bigint NOT NULL DEFAULT '0' COMMENT '开始时间',
  `answer_time_stamp` bigint NOT NULL DEFAULT '0' COMMENT '应答时间',
  `end_time_stamp` bigint NOT NULL DEFAULT '0' COMMENT '结束时间',
  `duration` int NOT NULL DEFAULT '0' COMMENT '持续时间',
  PRIMARY KEY (`id`),
  KEY `idx_uuid` (`uuid`),
  KEY `idx_start_time_stamp` (`start_time_stamp`) //索引,
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='p2p通话记录详情表';

伪代码实现：

//最小ID 
String  lastId = null; 
//一页的条数 
Integer pageSize = 2000; 
List<P2pRecordVo> list ;
do{   
   list = listP2pRecordByPage(lastId,pageSize);    //标签记录法，记录上次查询过的Id 
   lastId = list.get(list.size()-1).getId();       //获取上一次查询数据最后的ID，用于记录
   //对数据的操作逻辑
   XXXXX();
 }while(isNotEmpty(list));
   
<select id ="listP2pRecordByPage">  
   select * 
   from p2p_detail_record ppdr where 1=1
   <if test = "lastId != null">
   and ppdr.id > #{lastId}
   </if>
   order by id asc
   limit #{pageSize}
</select>

这里有个小优化点: 可能有的人会先对所有数据排序一遍，拿到最小ID，但是这样对所有数据排序，然后去min（id),耗时也蛮长的，其实第一次查询，可不带lastId进行查询，查询结果也是一样。速度更快。

总结

1、当业务需要从表中查出大数据量时，而又项目架构没上ES时，可考虑使用标签记录法的方式，对查询效率进行优化。

2、从需求上也应该尽可能避免，在大数据量的情况下，分页查询最后一页的功能。或者限制成只能一页一页往后划的场景。

推荐学习：mysql视频教程

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!