MySQL ストアドプロシージャの長所と短所の分析

MySQL バージョン 5.0 ではストアド プロシージャのサポートが開始されました。ストアド プロシージャは、外部アプリケーションから呼び出せるようにデータベースに保存される複雑なプログラムです。ストアド プロシージャは、特定の機能を実行する一連の SQL ステートメントです。これはコンパイルされ、作成され、データベースに保存されます。ユーザーは、ストアド プロシージャの名前を指定し、パラメーター (オプション) を指定して、これを呼び出して実行できます。

ストアド プロシージャを使用すると、SQL ステートメントの再利用率を効果的に向上でき、関連する SQL ステートメントのグループをストアド プロシージャにまとめることができるため、アプリケーションの複数のクエリによって発生する MySQL サーバーとの接続遅延やネットワーク リソースの占有を回避できます。以下は、ID を渡して指定された ID を持つ学生を削除し、同時に拡張テーブル内の学生情報を削除するストアド プロシージャの例です。この方法では、アプリケーションが 2 つの SQL 操作を実行する必要なく、関連データを処理できます。

存在する場合は削除プロシージャ delete_student_by_id;

区切り文字 $$

CREATE PROCEDURE delete_student_by_id(IN p_id INT)
始める
	t_studentsから削除
  ここで、id = p_id;
      
  t_students_info から削除
  ここで、student_id = p_id;
終わり
$$
    
区切り文字 ;

一般に、ストアド プロシージャには次のような利点があります。

• これはデータベース層で直接実行されるため、ネットワーク帯域幅の使用量とクエリ タスク実行の待ち時間が削減されます。
• コードの再利用性と保守性を向上させ、ビジネス ルールを集約し、一貫性を強化し、セキュリティを向上させます。
• セキュリティ上の利点と優れた権限制御方法をもたらします。典型的な例は、銀行での送金のストアド プロシージャです。ストアド プロシージャは 1 つのトランザクションで転送を完了し、後で確認できるように完全な操作ログを記録します。関連するテーブルに対する昇格された権限を必要とせずに、ストアド プロシージャを通じてアクセスを実行できます。
• サーバーはストアド プロシージャの実行をキャッシュするため、繰り返し実行の負荷を軽減できます。
• ストアド プロシージャはサーバー側に保存されるため、サービス オーダーの展開、バックアップ、保守の面でメンテナンスが容易になります。
• アプリケーション開発者とデータベース開発者の作業を分離できるため、データベースの専門家がストアド プロシージャを記述でき、一部のアプリケーション開発者の SQL 記述スキルが低いという問題を回避できます。
  

もちろん、常に長所と短所があり、ストアド プロシージャにもいくつかの欠点があります。

• MySQL には優れた開発およびデバッグ ツールがないため、ストアド プロシージャのデバッグは比較的困難です。
• SQL 言語自体はアプリケーション プログラミング言語ほど効率的ではなく、比較的基本的なものです。そのため、複雑な業務を処理することが困難になります。
• ストアド プロシージャにより、アプリケーションの展開が複雑になる場合もあります。アプリケーション コードとデータベース テーブルを展開する必要があるだけでなく、ストアド プロシージャも展開する必要があります。
• 各接続の各ストアド プロシージャの実行プラン キャッシュは独立しています。多数の接続が同じストアド プロシージャを呼び出す場合、キャッシュが繰り返されるとリソースが無駄になります。
• ストアド プロシージャは実行をデータベース サーバーに転送するため、アプリケーション サーバーの容量を拡張するよりもデータベース サーバーの容量を拡張することが難しくなり、コストも高くなります。
• ストアド プロシージャによって占有されるリソースは制御が難しく、バグが発生するとサーバーがクラッシュする可能性があります。
• ストアド プロシージャのコードは解釈が困難です。ストアド プロシージャが CALL XYZ('A') の形式で単純に呼び出される場合、スロー クエリ ログを分析することは困難です。これには、ストアド プロシージャのコードを見つけて、内部のステートメントを確認する必要があるためです。
• ステートメント レベルの binlog またはレプリケーションの場合、ストアド プロシージャを使用すると、潜在的な問題を排除するために厳密にチェックしない限り、ストアド プロシージャを使用できなくなる落とし穴が多数発生する可能性があります。
  

したがって、通常、上記の欠陥を回避するには、ストアド プロシージャを小さく簡潔に保つ必要があります。もちろん、ストアド プロシージャは、特にストアド プロシージャ内でループを使用して複数の小さなクエリを完了する場合など、一部の操作ではより高速に実行されます。クエリが十分に小さい場合、SQL ステートメントの解析とネットワーク通信が、ワークロードが高くなりすぎる大きな要因になります。今回は、ストアド プロシージャの利点について説明します。次のストアド プロシージャ コードを例に挙げます。

insert_many_rows が存在する場合はプロシージャを削除します。

デリミタ //

CREATE PROCEDURE insert_many_rows(IN loops INT)
始める
	v1 INT を宣言します。
  SET v1=ループ;
  v1 > 0 の場合
  	test_tableに値を挿入(NULL, 0,
                                 「ああああああああああああ」、
                                 'ああああああああああ');
    v1=v1-1 を設定します。
  終了しながら;
終わり
//

デリミタ ;
	

同じ機能をアプリケーションで比較すると、ストアド プロシージャを使用した場合の方が 2 倍以上パフォーマンスが向上し、MySQL プロキシを使用した場合と比較すると 3 倍パフォーマンスが向上することがわかります。

結論: ストアド プロシージャは現在はあまり使用されていませんが、一部の安定したビジネスでは、データベース サーバー間のネットワーク要求が多すぎる場合や、大量のネットワーク帯域幅が占有されている場合は、ストアド プロシージャを使用してパフォーマンスを最適化し、応答速度を向上させることを検討できます。ただし、トラブルシューティングに多くの時間を費やすことになる予期しないエラーを回避するために、ストアド プロシージャを繰り返し検証する必要があります。

上記は、MySQL ストアド プロシージャの長所と短所の詳細な分析です。MySQL ストアド プロシージャの長所と短所の詳細については、123WORDPRESS.COM の他の関連記事に注目してください。