Présentation du test de performances de prétraitement (instruction préparée) de la base de données MySQL-tutoriel mysql-php.cn

Recommandations d'apprentissage gratuites : tutoriel vidéo mysql

1. Que fait le prétraitement

Lorsque nous soumettons une instruction de base de données, l'instruction atteint le service de base de données et le service de base de données doit analyser cette instruction SQL, par exemple Vérification de la syntaxe, les conditions de requête sont d'abord optimisées puis exécutées. Pour le prétraitement, en termes simples, l'interaction initiale entre le client et le service de base de données est divisée en deux temps. Tout d’abord, soumettez l’instruction de base de données et laissez le service de base de données analyser d’abord l’instruction. Deuxièmement, soumettez les paramètres, appelez l'instruction et exécutez-la. De cette façon, pour les instructions qui sont exécutées plusieurs fois de manière répétée, l'instruction de base de données peut être soumise et analysée une fois, puis les instructions qui viennent d'être analysées sont appelées et exécutées en continu. Cela permet d'économiser le temps d'analyser plusieurs fois la même instruction. Afin d’atteindre l’objectif d’améliorer l’efficacité.

Les déclarations de prétraitement prennent en charge les espaces réservés (espaces réservés) et les paramètres sont soumis par des espaces réservés de liaison. Un point très important est que seules les valeurs peuvent être liées à des espaces réservés, et non à certains mots-clés de l'instruction SQL. Par exemple, instruction : "select * from student which student.id = ?". Si l'espace réservé (?) est "1 ou 1=1", alors "1 ou 1=1" sera considéré comme une valeur, c'est-à-dire entouré de symboles ``. Enfin, cette déclaration illégale sera Quelque chose s'est mal passé. Obtenant ainsi la vulnérabilité de l’injection SQL (injection SQL).

Les trois étapes principales du mécanisme de prétraitement :

1. Prétraiter l'instruction

2. Exécuter l'instruction

3. Détruire l'instruction de prétraitement.

2. Introduction à la table `performance_schema`.`prepared_statements_instances`

Exécutez le script sql : affichez la variable globale comme "%prepare%". Vous pouvez voir une valeur appelée 'performance_schema_max_prepared_statement_instances'的系统变量。其值为0表示不启用预处理语句性能数据记录表`performance_schema`.`prepared_statements_instances` ; -1 indique le traitement dynamique du nombre d'enregistrements ; d'autres valeurs entières positives indiquent le nombre maximum de performance_schema_max_prepared_statement_instances enregistrements.

Tableau `performance_schema`. Qu'est-ce que `prepared_statements_instances` ? Il est utilisé pour enregistrer certaines informations de base et données de performance des instructions préparées. Par exemple, l'ID de l'instruction préparée, le nom de l'instruction préparée, le contenu spécifique de l'instruction préparée, le nombre de fois que l'instruction préparée est exécutée, le temps pris pour chaque exécution, l'ID de thread auquel chaque instruction préparée la déclaration appartient, etc. Lorsque nous créons une instruction préparée, une donnée sera insérée dans ce tableau. Les instructions préparées sont basées sur des connexions. Si la connexion est déconnectée, les instructions préparées sont automatiquement supprimées. Mais la table `performance_schema`.`prepared_statements_instances` est globale et n'a rien à voir avec la connexion à la base de données. Avec ces données, nous pouvons savoir : 1. Si l'instruction exécutée dans le code est réellement prétraitée, 2. En comprenant l'exécution de l'instruction prétraitée, nous pouvons déterminer si une instruction doit être prétraitée dans l'entreprise.

3. Description de la fonction de préparation de qt

En fonction des besoins de mon propre projet, le code client de ce test utilise Qt. Une fonction clé est enregistrée ici : la fonction préparer de la classe QSqlQuery. Appeler la fonction préparer consiste à soumettre une commande à la base de données pour créer une instruction préparée. Cela signifie que lors de l'appel, il y aura une interaction avec le service de base de données. Il convient de noter que lorsque le même objet de classe QSqlQuery appelle préparer pour la deuxième fois, l'instruction préparée créée par le premier appel à préparer sera supprimée, puis une instruction préparée sera créée, même si les deux instructions préparées sont exactement les mêmes. même. Lors de l'appel de la fonction exec de QSqlQuery, les instructions préparées précédemment créées par QSqlQuery seront également supprimées. Par conséquent, à la fin de la requête, la connexion est fermée ou la requête exécute d'autres instructions, ce qui n'entraîne aucun enregistrement des instructions préparées associées dans la table `performance_schema`.`prepared_statements_instances`, et on pense à tort que la création du La déclaration préparée a échoué. En fait, l'approche de Qt nous évite également de supprimer manuellement les instructions préparées.

4. Conjecture expérimentale

La différence entre une instruction exécutée régulièrement et une instruction exécutée après prétraitement est que dans le cas d'exécutions multiples, l'instruction prétraitée n'a besoin que d'Analyse l'instruction SQL une fois, puis passez plus de temps à transmettre les paramètres et les paramètres de liaison. Les instructions préparées utilisent le protocole de transfert binaire lors du renvoi des résultats, tandis que les instructions ordinaires utilisent le protocole de transfert au format texte. Nous faisons donc la conjecture suivante et la vérifions.

1. Si une instruction simple est exécutée, il n'y a pas beaucoup de différence de performances entre l'exécution ordinaire et l'exécution avec prétraitement. Les instructions préparées ne montrent leurs avantages que lorsque des instructions complexes sont exécutées de manière répétée.

2. Lorsque l'ensemble de résultats de la requête contient une grande quantité de données, les instructions préparées présenteront des avantages en termes de performances.

5. Enregistrement de données expérimentales

序号	是否预处理	语句	是否远程数据库	返回数据量	每次实验语句执行总次数	三次实验平均总耗时/单位毫秒
1	是	select * from task where task.taskId in (?)	是	1000	1000	69822
2	否	select * from task where task.taskId in (arr)	是	1000	1000	66778
3	是	select * from task where task.taskId = ?	是	1	1000	1260
4	否	select * from task where task.taskId = id	是	1	1000	951
5	是	select * from task a LEFT JOIN task_file b ON a.taskId = b.task_id where a.taskName like '%s%' and b.file_id > 100000 and b.file_id < 200000 and a.taskId = ？";	是	2	1000	2130
6	否	select * from task a LEFT JOIN task_file b ON a.taskId = b.task_id where a.taskName like '%s%' and b.file_id > 100000 and b.file_id < 200000 and a.taskId = 32327";	是	2	1000	1480
7	是	select * from task where task.taskId in (?)	否	1000	1000	57051
8	否	select * from task where task.taskId in (arr)	否	1000	1000	56235
9	是	select * from task where task.taskId = ?	否	1	1000	217
10	否	select * from task where task.taskId = id	否	1	1000	204
11	是	select * from task a LEFT JOIN task_file b ON a.taskId = b.task_id where a.taskName like '%s%' and b.file_id > 100000 and b.file_id < 200000 and a.taskId = ？";	否	2	1000	366
12	否	select * from task a LEFT JOIN task_file b ON a.taskId = b.task_id where a.taskName like '%s%' and b.file_id > 100000 and b.file_id < 200000 and a.taskId = 32327";	否	2	1000	380

6. Conclusion

Les résultats des données expérimentales sont un peu différents de ce à quoi je m'attendais, mais après une inspection répétée du code de test et du processus de test, il a été confirmé qu'il devrait y avoir aucun problème avec le test lui-même. En respectant les données expérimentales, nous tirons les conclusions suivantes :

1. En comparant les expériences 5 et 6, et en comparant les expériences 11 et 12, nous pouvons conclure que la conjecture 1 est fausse. La conclusion devrait être : Il n'y a pas de différence de performances significative entre le prétraitement MySQL et les requêtes régulières dans les instructions simples et les instructions complexes .

2. En comparant l'expérience 1 et l'expérience 2, et en comparant l'expérience 7 et l'expérience 8, nous pouvons conclure que la conjecture 2 est fausse. La conclusion devrait être : Il n'y a pas d'écart de performances significatif dans la transmission de données entre les résultats du prétraitement MySQL et les requêtes régulières.

3. De plus, comparez les données expérimentales de la base de données distante et de la base de données locale. On peut conclure : La base de données MySQL apportera une amélioration significative des performances des opérations de données au niveau local.