Méthodes de requête en streaming et de requête de curseur dans MySQL (partage de résumé)

Cet article vous apporte des connaissances pertinentes sur mysql Il présente principalement les méthodes de requête en streaming et de requête par curseur dans MySQL. Il a une bonne valeur de référence et j'espère qu'il sera utile à tout le monde.

Apprentissage recommandé : Tutoriel vidéo MySQL

1. Scénario commercial

Maintenant, le système d'entreprise doit lire 5 millions de lignes de données de la base de données MySQL pour le traitement

• Migrer des données
• Exporter des données
• Batch traitement des données

Deuxièmement, énumérez les trois méthodes de traitement

• Requête régulière : lire 5 millions de données dans la mémoire JVM à la fois, ou lire des pages
• Requête en streaming : lire un élément à la fois et le charger dans la mémoire JVM pour le traitement métier
• Requête de curseur : comme le streaming, le nombre de données lues à la fois est contrôlé via le paramètre fetchSize

2.1 Requête régulière

Par défaut, l'ensemble complet des résultats de récupération sera stocké en mémoire . Dans la plupart des cas, il s’agit de la manière la plus efficace de fonctionner et la plus facile à mettre en œuvre.

En supposant qu'une seule table ait un volume de données de 5 millions, personne ne la chargera dans la mémoire en même temps et la pagination est généralement utilisée.

Ici, la démo de test sert uniquement à surveiller la JVM, donc la pagination n'est pas utilisée et les données sont chargées dans la mémoire en même temps

@Test
public void generalQuery() throws Exception {
    // 1核2G：查询一百条记录：47ms
    // 1核2G：查询一千条记录：2050 ms
    // 1核2G：查询一万条记录：26589 ms
    // 1核2G：查询五万条记录：135966 ms
    String sql = "select * from wh_b_inventory limit 10000";
    ps = conn.prepareStatement(sql);
    ResultSet rs = ps.executeQuery(sql);
    int count = 0;
    while (rs.next()) {
        count++;
    }
    System.out.println(count);
}

Surveillance JVM

Nous ajusterons la mémoire à -Xms70m -Xmx70m

tout le processus de requête, l'utilisation de la mémoire du tas augmente progressivement et conduit finalement à un MOO :

java.lang.OutOfMemoryError : limite de surcharge du GC dépassée

1 Déclenchement fréquent du GC

2.

2.2 Requête en streaming

Une chose à noter à propos des requêtes en streaming : toutes les lignes du jeu de résultats doivent être lues (ou fermées) avant que toute autre requête puisse être émise sur la connexion, sinon une exception sera levée et la requête monopolisera la connexion.

À en juger par les résultats des tests, la requête en streaming n'a pas amélioré la vitesse de requête

@Test
public void streamQuery() throws Exception {
    // 1核2G：查询一百条记录：138ms
    // 1核2G：查询一千条记录：2304 ms
    // 1核2G：查询一万条记录：26536 ms
    // 1核2G：查询五万条记录：135931 ms
    String sql = "select * from wh_b_inventory limit 50000";
    statement = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
    statement.setFetchSize(Integer.MIN_VALUE);
    ResultSet rs = statement.executeQuery(sql);
    int count = 0;
    while (rs.next()) {
        count++;
    }
    System.out.println(count);
}

Surveillance JVM

Nous avons réduit la mémoire du tas -Xms70m -Xmx70m

Nous avons constaté que même si la mémoire du tas n'était que de 70 m, elle restait ne s'est pas produit MOO

2.3 Requête du curseur

Remarque :

1. Besoin de fusionner les paramètres dans les informations de connexion à la base de données

useCursorFetch=true

2. Deuxièmement, définissez le nombre de données lues par Statement à chaque fois. , comme en lire 1 000 à la fois

À en juger par les résultats des tests, la requête du curseur a réduit la vitesse de requête dans une certaine mesure

@Test
public void cursorQuery() throws Exception {
    Class.forName("com.mysql.jdbc.Driver");
    // 注意这里需要拼接参数，否则就是普通查询
    conn = DriverManager.getConnection("jdbc:mysql://101.34.50.82:3306/mysql-demo?useCursorFetch=true", "root", "123456");
    start = System.currentTimeMillis();
 
     // 1核2G：查询一百条记录：52 ms
     // 1核2G：查询一千条记录：1095 ms
    // 1核2G：查询一万条记录：17432 ms
    // 1核2G：查询五万条记录：90244 ms
    String sql = "select * from wh_b_inventory limit 50000";
    ((JDBC4Connection) conn).setUseCursorFetch(true);
    statement = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
    statement.setFetchSize(1000);
    ResultSet rs = statement.executeQuery(sql);
    int count = 0;
    while (rs.next()) {
        count++;
    }
    System.out.println(count);
}

Surveillance JVM

Nous avons réduit la mémoire du tas -Xms70m -Xmx70m

Nous avons trouvé que dans un cas monothread, les requêtes de curseur Comme les requêtes de streaming, le MOO peut être très bien évité et les requêtes de curseur peuvent optimiser la vitesse des requêtes.

3. RowData

ResultSet.next() La logique est d'implémenter la classe ResultSetImpl pour obtenir la ligne de données suivante de RowData à chaque fois. RowData est une interface et le diagramme de relation d'implémentation est le suivant

3.1 RowDataStatic

Par défaut, ResultSet utilisera l'instance RowDataStatic lors de la génération de l'objet RowDataStatic, tous les enregistrements de ResultSet seront lus dans la mémoire, puis via. next() Lire à partir de la mémoire un par un

3.2 RowDataDynamic

Lors de l'utilisation du traitement en streaming, ResultSet utilise l'objet RowDataDynamic, et cet objet lancera IO pour lire une seule ligne de données à chaque fois que next() est appelé

3.3 RowDataCursor

RowDataCursor L'appel est un traitement par lots, puis une mise en cache interne est effectuée. Le processus est le suivant :

Tout d'abord, il vérifiera s'il y a des données dans son tampon interne qui n'ont pas été renvoyées. S'il y en a, il le fera. revenez à la ligne suivante.

• Si toutes les lectures sont terminées, déclenchez-en une nouvelle sur le serveur MySQL. Demandez de lire le résultat de la quantité fetchSize
• et mettez le résultat de retour en mémoire tampon dans le tampon interne, puis renvoyez la première ligne de données
.

En résumé :

Le RowDataStatic par défaut lit toutes les données dans la mémoire du client, il s'agit également de notre JVM ;

RowDataDynamic lit une donnée par appel IO

RowDataCursor lit les lignes fetchSize à la fois, puis lance un appel de demande une fois la consommation terminée.

4.Principe de communication JDBC

L'interaction entre JDBC et le serveur MySQL s'effectue via Socket Correspondant à la programmation réseau, MySQL peut être considéré comme un SocketServer, donc un lien de requête complet doit être :

.

Client JDBC -> Client Socket -> MySQL -> Récupérer les données renvoyées -> MySQL Kernel Socket Buffer -> Client Socket Buffer -> Une requête ordinaire chargera toutes les données interrogées dans la JVM, puis les traitera.

Si la quantité de données de requête est trop importante, elle continuera à subir GC, puis il y aura un débordement de mémoire

4.2 Requête de streaming streamQuery

Lorsque le serveur est prêt à revenir des premières données, il se chargera les données dans le tampon, et les données passeront par la liaison TCP, dans le tampon du noyau de la machine client, la méthode inputStream.read() de JDBC sera réveillée pour lire les données. La seule différence est que lorsque la lecture du flux est activée, elle est activée. lit uniquement à partir du noyau à chaque fois. Prendre des données d'une taille de package ne renvoie qu'une seule ligne de données. Si un package ne peut pas assembler une ligne de données, un autre package sera lu.

4.3 CursorQuery Cursor Query

Lorsque le curseur est activé, lorsque le serveur renvoie des données, il renverra des données en fonction de la taille de fetchSize, et lorsque le client recevra des données, il lira toutes les données du tampon à chaque fois. si les données contiennent 100 millions de données, si FetchSize est défini sur 1 000, 100 000 communications aller-retour seront effectuées

Puisque MySQL ne sait pas quand le client a fini de consommer les données, sa propre table correspondante peut avoir une opération écrite DML ; à ce stade, MySQL doit créer un espace temporaire pour stocker les données qui doivent être supprimées.

Ainsi, lorsque vous activez useCursorFetch pour lire une grande table, vous verrez plusieurs phénomènes sur MySQL :

1 L'IOPS monte en flèche

2. L'espace disque monte en flèche

3. Une fois que le client JDBC a lancé SQL, le temps est long. en attente des données de réponse SQL. Pendant ce temps, le serveur prépare les données
• 4. Une fois la préparation des données terminée et la transmission des données commence, la réponse du réseau commence à augmenter et les IOPS passent de « lecture et écriture » à "lire".
• IOPS (Entrée/Sortie par seconde) : Le nombre de lectures et d'écritures sur disque par seconde
• 5. Le processeur et la mémoire augmenteront dans une certaine proportion
• 5. Scénarios de concurrence
• Appels simultanés : Jmete 10 threads simultanément dans 1 seconde Le rapport sur les performances de la mémoire de l'appel de

requête de flux est le suivant

Les appels simultanés sont également très bons pour l'utilisation de la mémoire, et il n'y a pas d'augmentation superposée

Le rapport sur les performances de la mémoire de la requête de curseur est le suivant

6. Résumé

1. La requête de curseur et la requête de streaming peuvent éviter le MOO dans un seul thread

2. La requête de curseur est plus rapide que la requête de streaming en termes de vitesse de requête, et la requête de streaming ne peut pas raccourcir le temps de requête par rapport. avec une requête ordinaire ;

3. Dans des scénarios simultanés, la tendance de la mémoire du segment de requête en streaming est plus stable et il n'y a pas d'augmentation additive.

Apprentissage recommandé :

Tutoriel vidéo 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!