Maison > Article > base de données > Explication détaillée du principe de préparation dans MySQL
Explication détaillée du principe de préparation dans MySQL
- 黄舟original
- 2017-10-04 09:27:142877parcourir
Cet article présente principalement le contenu associé à MySQL Prepare, y compris la génération de Prepare, le processus d'exécution côté serveur, ainsi que le traitement par jdbc de Prepare et les tests associés. Les amis qui en ont besoin peuvent en savoir plus. J'espère que cela aide tout le monde.
Avantages de Prepare
La raison pour laquelle Prepare SQL est généré. Commençons par le processus d'exécution de SQL sur le serveur mysql. Le processus d'exécution de SQL comprend les étapes suivantes : analyse lexicale->analyse syntaxique->analyse sémantique->optimisation du plan d'exécution->exécution. Analyse lexicale -> Analyse grammaticale Ces deux étapes sont appelées analyse dure. L'analyse lexicale identifie chaque mot dans SQL et l'analyse syntaxique analyse si l'instruction SQL est conforme à la grammaire SQL et obtient un arbre syntaxique (Lex). Pour SQL avec des paramètres différents mais les mêmes, leur temps d'exécution est différent mais le temps d'analyse difficile est le même. À mesure que les données de requête changent pour le même SQL, le temps d'exécution de plusieurs requêtes peut être différent, mais le temps d'analyse matérielle reste inchangé. Plus le temps d'exécution SQL est court, plus le rapport entre le temps d'analyse matérielle SQL et le temps d'exécution total est élevé. Pour la plupart des applications SQL transactionnelles sur Taobao, les requêtes passeront par l'index et le temps d'exécution est relativement court. Par conséquent, Taobao utilise l'analyse dure de DB SQL pour représenter une grande partie.
L'émergence de Prepare vise à optimiser le problème de l'analyse difficile. Le processus d'exécution de Prepare côté serveur est le suivant
1) Prepare reçoit le SQL avec "?" du client, effectue une analyse approfondie pour obtenir l'arborescence syntaxique (stmt->Lex) , et le met en cache dans le cache de préparation où se trouve le thread. Ce cache est une HASH MAP. La clé est stmt->id. Ensuite, des informations telles que stmt->id sont renvoyées au client.
2) Execute reçoit des informations telles que le client stmt->id et les paramètres. Notez que le client n'a pas besoin d'envoyer du SQL ici. Le serveur recherche dans le cache de préparation le stmt analysé en dur en fonction du stmt->id et définit les paramètres, puis il peut continuer l'optimisation et l'exécution ultérieures.
Prepare peut gagner du temps d'analyse pendant la phase d'exécution. Si SQL n'est exécuté qu'une seule fois et est exécuté en mode préparation, alors l'exécution de SQL nécessite deux interactions avec le serveur (préparer et exécuter), tandis qu'en mode normal (non préparé), une seule interaction est requise. Utiliser Prepare de cette manière entraîne une surcharge réseau supplémentaire, qui peut contrebalancer les avantages. Examinons la situation dans laquelle le même SQL est exécuté plusieurs fois. Par exemple, s'il est exécuté 10 fois en mode préparation, une seule analyse matérielle est requise. À ce stade, la surcharge supplémentaire du réseau devient négligeable. Par conséquent, Prepare convient au SQL fréquemment exécuté.
Une autre fonction de Prepare est d'empêcher l'injection SQL, mais cela est réalisé en s'échappant dans jdbc côté client et n'a rien à voir avec le serveur.
Proportion de hard parsing
D'après les résultats obtenus via perf lors du stress test, les proportions de fonctions liées au hard parsing sont relativement élevées (MYSQLparse 4,93%, lex_one_token 1,79%, lex_start 1,12 %). Le total est proche de 8%. Par conséquent, l’utilisation de Prepare sur le serveur peut apporter davantage d’améliorations des performances.
jdbc et préparez
paramètres côté serveur jdbc :
useServerPrepStmts : la valeur par défaut est false. pour utiliser le commutateur de préparation du serveur
Paramètres du client jdbc :
cachePrepStmts : false par défaut s'il faut mettre en cache les objets PrepareStatement. Chaque connexion dispose d'un cache, qui est un cache LRU identifié de manière unique par SQL. Sous la même connexion, différents stmts n'ont pas besoin de recréer l'objet PrepareStatement.
prepStmtCacheSize : Le nombre d'objets prepareStatement dans le cache LRU. Généralement défini sur le numéro du SQL le plus couramment utilisé.
prepStmtCacheSqlLimit : La taille de l'objet PrepareStatement. Les dépassements de taille ne sont pas mis en cache.
Gestion de la préparation par Jdbc :
Gestion de la préparation par Jdbc lorsque useServerPrepStmts=true
1 ) Créer un PreparedStatement, envoyez la commande COM_PREPARE au serveur et transmettez le sql avec un point d'interrogation. Le serveur renvoie jdbc stmt->id et d'autres informations
2) Envoyez la commande COM_EXECUTE au serveur et transmettre les informations sur les paramètres.
Gestion de la préparation par Jdbc lorsque useServerPrepStmts=false
1) Créez un objet PreparedStatement et n'interagirez pas avec le serveur pour le moment.
2) Épissez le SQL complet en fonction des paramètres et de l'objet PreparedStatement, et envoyez la commande QUERY au serveur
Regardons le paramètre cachePrepStmts lorsqu'il est activé on et useServerPrepStmts est vrai ou faux. Les objets PreparedStatement sont mis en cache. C'est juste que useServerPrepStmts est vrai et que l'objet PreparedStatement mis en cache contient le stmt->id du serveur et d'autres informations. Autrement dit, si l'objet PreparedStatement est réutilisé, la surcharge de communication avec le serveur (commande COM_PREPARE) est éliminée. Et useServerPrepStmts=false signifie que l'activation de cachePrepStmts pour mettre en cache les objets PreparedStatement n'est qu'une simple information d'analyse SQL, donc activer cachePrepStmts à ce stade n'a pas beaucoup de sens.
Jetons un coup d'œil à un morceau de code Java
Connection con = null;
PreparedStatement ps = null;
String sql = "select * from user where id=?";
ps = con.prepareStatement(sql);
ps.setInt(1, 1);
ps.executeQuery();
ps.close();
ps = con.prepareStatement(sql);
ps.setInt(1, 3);
ps.executeQuery();
ps.close();Ce code prépare à exécuter la même instruction deux fois dans la même session, et il y a ps.close();
Lorsque useServerPrepStmts=false, le serveur analysera en dur deux fois Idem SQL.
Lorsque useServerPrepStmts=true, cachePrepStmts=false, le serveur analysera toujours en dur le même SQL deux fois.
Lorsque useServerPrepStmts=true, cachePrepStmts=true, le serveur n'analysera SQL qu'une seule fois.
S'il n'y a pas de ps.close(); entre deux préparations, alors cachePrepStmts=true et cachePrepStmts=false ne nécessitent qu'une seule analyse matérielle
Par conséquent, le client. Pour le même SQL, lors de l'allocation et de la libération fréquentes d'objets PreparedStatement, il est nécessaire d'activer le paramètre cachePrepStmts.
Test
1) Réalisation d'un test simple, testant principalement l'effet de la préparation et l'influence des paramètres useServerPrepStmts.
cnt = 5000;
// no prepare
String sql = "select biz_order_id,out_order_id,seller_nick,buyer_nick,seller_id,buyer_id,auction_id,auction_title,auction_price,buy_amount,biz_type,sub_biz_type,fail_reason,pay_status,logistics_status,out_trade_status,snap_path,gmt_create,status,ifnull(buyer_rate_status, 4) buyer_rate_status from tc_biz_order_0030 where " +
"parent_id = 594314511722841 or parent_id =547667559932641;";
begin = new Date();
System.out.println("begin:" + df.format(begin));
stmt = con.createStatement();
for (int i = 0; i < cnt; i++)
{
stmt.executeQuery(sql);
}
end = new Date();
System.out.println("end:" + df.format(end));
long temp = end.getTime() - begin.getTime();
System.out.println("no perpare interval:" + temp);
// test prepare
sql = "select biz_order_id,out_order_id,seller_nick,buyer_nick,seller_id,buyer_id,auction_id,auction_title,auction_price,buy_amount,biz_type,sub_biz_type,fail_reason,pay_status,logistics_status,out_trade_status,snap_path,gmt_create,status,ifnull(buyer_rate_status, 4) buyer_rate_status from tc_biz_order_0030 where " +
"parent_id = 594314511722841 or parent_id =?;";
ps = con.prepareStatement(sql);
BigInteger param = new BigInteger("547667559932641");
begin = new Date();
System.out.println("begin:" + df.format(begin));
for (int i = 0; i < cnt; i++)
{
ps.setObject(1, param);
ps.executeQuery();
}
end = new Date();
System.out.println("end:" + df.format(end));
temp = end.getTime() - begin.getTime();
System.out.println("prepare interval:" + temp);Les résultats de plusieurs tests d'échantillonnage sont les suivants
|
Rapport de temps de non-préparation et de préparation | ||||||
| useServerPrepStmts=true | 0,93 | ||||||
| useServerPrepStmts=false | 1.01 |
Conclusion :
Lorsque useServerPrepStmts=true, la préparation augmente de 7 % ;
Lorsque useServerPrepStmts=false, les performances de préparation et de non-préparation sont équivalentes.
Si la déclaration est simplifiée, sélectionnez * from tc_biz_order_0030 où parent_id =?. Ensuite, la conclusion du test est que lorsque useServerPrepStmts=true, la préparation ne s'améliore que de 2 % ; plus le SQL est simple, moins il faut de temps pour une analyse approfondie et moins la préparation s'améliore.
Remarque : ce test est effectué dans les conditions idéales d'une seule connexion et d'un seul SQL. Il y aura plusieurs connexions et plusieurs SQL en ligne, ainsi que la fréquence d'exécution de SQL et la complexité de celui-ci. le sql.Par conséquent, l'effet d'amélioration de la préparation varie en fonction de l'environnement spécifique.
2) Comparaison des performances supérieures avant et après la préparation
Ce qui suit n'est pas une préparation
6.46% mysqld mysqld [.] _Z10MYSQLparsePv 3.74% mysqld libc-2.12.so [.] __memcpy_ssse3 2.50% mysqld mysqld [.] my_hash_sort_utf8 2.15% mysqld mysqld [.] cmp_dtuple_rec_with_match 2.05% mysqld mysqld [.] _ZL13lex_one_tokenPvS_ 1.46% mysqld mysqld [.] buf_page_get_gen 1.34% mysqld mysqld [.] page_cur_search_with_match 1.31% mysqld mysqld [.] _ZL14build_templateP19row_prebuilt_structP3THDP5TABLEj 1.24% mysqld mysqld [.] rec_init_offsets 1.11% mysqld libjemalloc.so.1 [.] free 1.09% mysqld mysqld [.] rec_get_offsets_func 1.01% mysqld libjemalloc.so.1 [.] malloc 0.96% mysqld libc-2.12.so [.] __strlen_sse42 0.93% mysqld mysqld [.] _ZN4JOIN8optimizeEv 0.91% mysqld mysqld [.] _ZL15get_hash_symbolPKcjb 0.88% mysqld mysqld [.] row_search_for_mysql 0.86% mysqld [kernel.kallsyms] [k] tcp_recvmsg
Ce qui suit est préparé
3.46% mysqld libc-2.12.so [.] __memcpy_ssse3 2.32% mysqld mysqld [.] cmp_dtuple_rec_with_match 2.14% mysqld mysqld [.] _ZL14build_templateP19row_prebuilt_structP3THDP5TABLEj 1.96% mysqld mysqld [.] buf_page_get_gen 1.66% mysqld mysqld [.] page_cur_search_with_match 1.54% mysqld mysqld [.] row_search_for_mysql 1.44% mysqld mysqld [.] btr_cur_search_to_nth_level 1.41% mysqld libjemalloc.so.1 [.] free 1.35% mysqld mysqld [.] rec_init_offsets 1.32% mysqld [kernel.kallsyms] [k] kfree 1.14% mysqld libjemalloc.so.1 [.] malloc 1.08% mysqld [kernel.kallsyms] [k] fget_light 1.05% mysqld mysqld [.] rec_get_offsets_func 0.99% mysqld mysqld [.] _ZN8Protocol24send_result_set_metadataEP4ListI4ItemEj 0.90% mysqld mysqld [.] sync_array_print_long_waits 0.87% mysqld mysqld [.] page_rec_get_n_recs_before 0.81% mysqld mysqld [.] _ZN4JOIN8optimizeEv 0.81% mysqld libc-2.12.so [.] __strlen_sse42 0.78% mysqld mysqld [.] _ZL20make_join_statisticsP4JOINP10TABLE_LISTP4ItemP16st_dynamic_array 0.72% mysqld [kernel.kallsyms] [k] tcp_recvmsg 0.63% mysqld libpthread-2.12.so [.] __pthread_getspecific_internal 0.63% mysqld [kernel.kallsyms] [k] sk_run_filter 0.60% mysqld mysqld [.] _Z19find_field_in_tableP3THDP5TABLEPKcjbPj 0.60% mysqld mysqld [.] page_check_dir 0.57% mysqld mysqld [.] _Z16dispatch_command19enum_server_commandP3THDP
La comparaison montre que MYSQLparse lex_one_token a été optimisé lors de la préparation.
Réflexion
1 Le problème de l'ouverture de cachePrepStmts Comme mentionné précédemment, chaque connexion dispose d'un cache, qui est un LRU avec SQL comme paramètre. identifiant unique. Lorsqu'il existe de nombreuses sous-tables et des connexions volumineuses, cela peut entraîner des problèmes de mémoire pour le serveur d'applications. Le principe ici est qu'ibatis utilise prepare par défaut. Dans mybatis, l'étiquette StatementType peut spécifier si un certain SQL utilise Prepare.
statementType N'importe lequel de STATEMENT, PREPARED ou CALLABLE. Cela amène MyBatis à utiliser respectivement Statement, PreparedStatement ou CallableStatement. 🎜>
.
Résumé
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!