Explain

を使用したMySQLクエリの理解データベース管理者またはデータエンジニアとしての新しい仕事に就いていて、これらの非常識なクエリが何を意味し、何をすべきかを理解しようとするのに迷ってしまいました。 なぜ5つの結合があり、なぜORDER BYがサブクエリ内で使用されているのですか？ 覚えておいて、あなたは理由のために雇われた-最も可能性の高い、その理由はまた、最後の十年にわたって作成され、編集された多くの複雑なクエリ

EXPLAINキーワードは、さまざまなSQLデータベース全体で使用され、SQLデータベースがクエリを実行する方法に関 MySQLでは、EXPLAINSELECTINSERTDELETEREPLACEREPLACESELECTINSERTINSERTDELETE。UPDATE。 単純なクエリの場合、次のようになります:

EXPLAIN SELECT * FROM foo WHERE foo.bar = 'infrastructure as a service' OR foo.bar = 'iaas';

通常の結果出力の代わりに、MySQLはステートメントを実行するときにどのプロセスがどの順序で行われるかを説明することによって、ステー注:EXPLAINSELECT権限がない可p>

EXPLAIN遅いクエリをすばやく解決するのに最適なツールです。 それは確かにあなたを助けることができますが、それは構造的思考の必要性と所定の位置にデータモデルの良い概要を奪うことはありません。 多くの場合、最も簡単な修正と迅速なアドバイスは、パフォーマンスの問題がある多くのクエリで使用されている場合、問題の特定のテーブルの列にイン しかし、あまりにも多くのインデックスを使用しないことは逆効果である可能性があるため注意してくださ インデックスとテーブルを読み取ることは、テーブルにかなりの量の行があり、必要なデータポイントが少数である場合にのみ意味があります。 テーブルから巨大な結果セットを取得し、さまざまな列を頻繁に照会する場合、すべての列のインデックスは意味をなさず、パフォーマンスを妨げます。 Indexとno indexの実際の計算の詳細については、公式のMySQLドキュメントのパフォーマンスの推定を参照してください。可能で適用可能な限り避けたいのは、クエリ内の並べ替えと計算です。

可能な限り避けたいのは、クエリ内の並べ替えと計算です。

クエリ内で計算を避けることができないと思われる場合は、はい、できます。 結果セットを別の場所に書き込み、クエリの外部のデータポイントを計算すると、データベースへの負担が少なくなり、アプリケーションにとって全体的に SQLですぐに結果が生成されるのではなく、アプリケーション内で計算する理由を文書化してください。 そうでなければ、次のデータベース管理者または開発者が来て、”ああ、私の前任者はあなたがSQLでそれを行うことができることを知らなかった！”データベースが死ぬという必然的な問題をまだ抱えていない開発者チームの中には、日付や同様のデータポイント間の数の違いに対してクエリ内計算を使

SQLクエリの一般的な経験則は次のとおりです。

正確にして、必要な結果のみを生成します。もう少し複雑なクエリをチェックしてみましょう…

SELECT site_options.domain, sites_users.user, site_taxes.monthly_statement_fee, site.name, AVG(price) AS average_product_price FROM sites_orders_products, site_taxes, site, sites_users, site_options WHERE site_options.site_id = site.id AND sites_users.id = site.user_id AND site_taxes.site_id = site.id AND sites_orders_products.site_id = site.id GROUP BY site.id ORDER BY site.date_modified desc LIMIT 5;+-----------------------------+-----------------------------+-----------------------+------------------------------------------+-----------------------+| domain | user | monthly_statement_fee | name | average_product_price |+-----------------------------+-----------------------------+-----------------------+------------------------------------------+-----------------------+| www.xxxxxxxxxxxxxxxxxxx.com | [email protected] | 0.50 | xxxxxxxxxxxxxxxxxxxxx | 3.254781 || www.xxxxxxxxxxx.com | [email protected] | 0.50 | xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx | 9.471022 || | [email protected] | 0.00 | xxxxxxxxxxxxxxxxx | 8.646297 || | [email protected] | 0.00 | xxxxxxxxxxxxxxx | 9.042460 || | [email protected] | 0.00 | xxxxxxxxxxxxxxxxxx | 6.679182 |+-----------------------------+-----------------------------+-----------------------+------------------------------------------+-----------------------+5 rows in set (0.00 sec)

…とそのEXPLAIN出力。p>

+------+-------------+---------------------------------+--------+-----------------+---------------+---------+---------------------------------+------+-----------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+------+-------------+---------------------------------+--------+-----------------+---------------+---------+---------------------------------+------+-----------+| 1 | SIMPLE | sites | index | PRIMARY,user_id | PRIMARY | 4 | NULL | 858 | Using temporary; Using filesort || 1 | SIMPLE | sites_options | ref | site_id | site_id | 4 | service.sites.id | 1 | || 1 | SIMPLE | sites_taxes | ref | site_id | site_id | 4 | service.sites.id | 1 | || 1 | SIMPLE | sites_users | eq_ref | PRIMARY | PRIMARY | 4 | service.sites.user_id | 1 | || 1 | SIMPLE | sites_orders_products | ref | site_id | site_id | 4 | service.sites.id | 4153 | |//+------+-------------+---------------------------------+--------+-----------------+---------------+---------+---------------------------------+------+-----------+5 rows in set (0.00 sec)

EXPLAIN出力の列には、太字で問題を特定するために特別な注意が必要な列があります:

• id(クエリid)
• select_type(ステートメントのタイプ)
• table(テーブル参照)
• type(結合タイプ)
• possible_keys(どのキーが使用されていたか)
• key(使用されたキー)
• key_len(使用されたキーの長さ)
• ref(インデックスと比較された列)
• rows(検索された行の量)
• extra(追加情報)

検索された行の量が高いほど、パフォーマンスを最大化するためには、インデックスとクエリの精度に関する最適化レベ 追加の列には、該当する場合にクエリを改善するために集中できる可能性のあるアクションが表示されます。

Show Warnings;

EXPLAINで使用したクエリが正しく解析されない場合は、MySQLクエリエディタにSHOW WARNINGS;EXPLAINEXPLAIN SELECT * FROM foo WHERE foo.bar = 'infrastructure as a service' OR foo.bar = 'iaas';foo。 MySQLの出力は次のようになります。

ERROR 1146 (42S02): Table 'db.foo' doesn't exist

SHOW WARNINGS;と入力すると、出力は次のようになります。

+-------+------+-------------------------------------+| Level | Code | Message |+-------+------+-------------------------------------+| Error | 1146 | Table 'db.foo' doesn't exist |+-------+------+-------------------------------------+1 row in set (0.00 sec)

意図的な構文エラーでこれを試してみましょう。

EXPLAIN SELECT * FROM foo WHERE name = ///;

これにより、次の警告が生成されます:

> SHOW WARNINGS;+-------+------+---------------------------------------------------------------------+| Level | Code | Message |+-------+------+---------------------------------------------------------------------+| Error | 1064 | You have an error in your SQL syntax; (...) near '///' at line 1 |+-------+------+---------------------------------------------------------------------+

この警告出力はかなり簡単で、すぐに結果出力としてMySQLによって示されますが、解析されないより複雑なクエリの場合は、解析可能なクエリフラグメントで何が起こるかを見ることができます。 SHOW WARNINGS;には、次のような有用な情報を提供できる特別なマーカーが含まれています。

• <index_lookup>(query fragment):クエリが適切に解析された場合、インデックスルックアップが発生します
• <if>(condition, expr1, expr2): クエリのこの特定の部分でif条件が発生しています
• <primary_index_lookup>(query fragment):主キーを介してインデックス検索が発生しています
• <temporary table>:内部テーブルがここに作成され、一時的な結果を保存します

これらの特別なマーカーの詳細については、”Extended Explain Output Format”を参照してください。mysqlの公式ドキュメント。

長期的な修正

悪いデータベースのパフォーマンスの根本的な原因を修正するには、いくつかの方法があります。 最初に見るべき点は、データモデルです。 データはどのように構造化され、適切なデータベースを使用していますか？ 多くの製品では、SQLデータベースは問題ありません。 覚えておくべき重要なことの一つは、残念ながら多くの企業では発生しません通常の運用データベースからアクセスログを常に分離することです。 ほとんどの場合、会社は小規模になり、大きくなり、本質的には同じデータベースを使用しています。 これにより、特に会社が大きくなるにつれて、全体的なパフォーマンスが大幅に低下します。 したがって、適合し、持続可能なデータモデルを作成することは非常に重要です。

データモデル

データモデルを選択すると、正しい形式のデータベースも明らかになる可能性が最も高いです。 あなたの製品が非常に基本的でない限り、あなたはおそらくいくつかのユースケースのためのいくつかのデータベースを持っています-あなたはアクセスログのためのほぼリアルタイムの数字を表示する必要がある場合は、通常のトランザクションがSQLデータベースを介して発生する可能性がありますが、あなたは最も可能性の高い高パフォーマンスのデータウェアハウスを望んでいるでしょう、そしてあなたは両方のデータベースの関連するデータポイントをレコメンダーエンジンにも蓄積するグラフデータベースを持っているかもしれません。

ここでの設計が悪いと、データベースに向かってボトルネックが発生し、ソフトウェア側からもデータベースが出力できるものの両方からすべてが遅く コンテナが製品に適しているかどうか、モノリスが物事を処理するためのより良い方法であるかどうか、他の場所に広がる他の機能を対象とした複数のマイクロサービスを備えたコアモノリスを持つかどうか、データへのアクセス、収集、処理、および保存方法を選択する必要があります。

Hardware

一般的な構造と同じくらい重要なのは、ハードウェアがデータベースのパフォーマンスの重要なコンポーネントであることです。 Exoscaleでは、トランザクションとストレージのボリューム、および希望する応答時間に応じて使用できるさまざまなインスタンスオプションを提供します。

アプリケーションのピーク期間を決定し、可能であれば遅い管理クエリをいつ省略するかを知ることが重要です。 データベーストランザクションと分析のタイミングを設計する際には、ディスクI/Oとネットワーク統計も考慮する必要があります。

概要

結論として、ここでは要約長期的なパフォーマンスのための主なポイントは次のとおりです:

• あなたの会社のニーズに合った持続可能なデータモデルを作成します
• データベースの適切な形式を選択します
• あなたの製品に合ったソフ>パフォーマンスと帯域幅に応じて、アプリケーションとデータベースのニーズに最適なインスタンスを選択します