mysql - 测试不同sql语句的查询效率

Question

没有深入学习过数据库，只是了解一些mysql基础和增删改操作，我想问问有没有工具(方法也行)，当数据表中的数据几百万条时，可以让我通过使用不同的sql语句，来直观的查看不同sql语句的执行效率，谢谢大家了

Answer 1

4.1. EXPLAIN
MySQL では、EXPLAIN を使用して SQL 実行プランを表示できます。 使用法: EXPLAIN SELECT * FROM tb_item

4.2. 結果の説明
4.2.1. id
SELECT 識別子。これは SELECT クエリのシーケンス番号です。これは重要ではありません。
4.2.2. select_type
は SELECT 文の種類を示します。

には次の値があります。
1. SIMPLE
は、結合クエリとサブクエリを含まない単純なクエリを表します。
2. PRIMARY
はメインクエリ、つまり最も外側のクエリステートメントを表します。

3. UNION
は、接続クエリの 2 番目以降のクエリ文を表します。

4. DEPENDENT UNION
UNION の 2 番目以降の SELECT ステートメントは外部クエリに依存します。
5. UNION RESULT
接続クエリの結果。
6. SUBQUERY
サブクエリの最初の SELECT ステートメント。

7. DEPENDENT SUBQUERY
サブクエリの最初の SELECT ステートメントは外部クエリに依存します。
8. DERIVED
SELECT (FROM 句のサブクエリ)。
4.2.3. table
はクエリテーブルを表します。
4.2.4. type (重要)
はテーブルの接続タイプを示します。
次の接続タイプは、最良から最悪の順に並べられています:

1. system
テーブルには 1 つの行しかありません。これは const 型の特殊な列であり、通常は無視できます。
2. const
データ テーブルは、最大で 1 行のデータとのみ一致するため、PRIMARY KEY または UNIQUE インデックスのクエリによく使用されます。 const が最も最適化されていることが理解できます。

3. eq_ref
MySQL マニュアルには次のように書かれています: 「前のテーブルの行の組み合わせごとに、テーブルから 1 行を読み取ります。const 型を除いて、これがおそらく最適な結合タイプです。これは次の場合に使用されます。」インデックスのすべての部分が結合で使用され、インデックスは UNIQUE または PRIMARY KEY です。」 eq_ref は、= を使用してインデックス付き列を比較するために使用できます。

4. ref
クエリ条件インデックスが UNIQUE または PRIMARY KEY ではありません。 ref は、= または < または > 演算子を使用してインデックス付き列に使用できます。

5. ref_or_null
この結合タイプは ref に似ていますが、NULL 値を含む行を特別に検索するための MySQL が追加されています。この結合タイプの最適化は、サブクエリを解決する際によく使用されます。

上記の 5 つの状況はすべて、理想的なインデックス使用状況です。

6.index_merge
この結合タイプは、インデックス マージ最適化手法が使用されることを示します。この場合、key 列には使用されるインデックスのリストが含まれ、key_len には使用されるインデックスの最長のキー要素が含まれます。
7. unique_subquery
このタイプは、IN サブクエリの ref を次の形式で置き換えます: value IN (SELECT Primary_key FROM single_table WHERE some_expr)
unique_subquery は、サブクエリを完全に置き換えることができるインデックス検索関数です。より効率的です。
8.index_subquery
この接続タイプは unique_subquery に似ています。 IN サブクエリは置換できますが、次の形式のサブクエリの一意でないインデックスにのみ適しています: value IN (SELECT key_column FROM single_table WHERE some_expr)
9. range
は、次の形式を使用して、指定された範囲内の行のみを取得します。行を選択するための 1 つのインデックス。

10. インデックス
この結合タイプは、インデックス ツリーのみがスキャンされることを除いて、ALL と同じです。通常、インデックス ファイルはデータ ファイルよりも小さいため、これは通常 ALL よりも高速です。
11. ALL
前のテーブルの行の組み合わせごとに完全なテーブル スキャンを実行します。 (最悪のパフォーマンス)
4.2.5. possible_keys
は、MySQL がこのテーブル内の行を検索するために使用できるインデックスを示します。
列が NULL の場合、インデックスが使用されていないことを意味し、パフォーマンスを向上させるために列にインデックスを作成できます。
4.2.6. key
MySQL が実際に使用することを決定したキー (インデックス) を表示します。インデックスが選択されていない場合、キーは NULL になります。

インデックスの使用を強制することも、インデックスを無視することもできます:

4.2.7. key_len
MySQL が使用することを決定したキーの長さを表示します。キーが NULL の場合、長さは NULL になります。

注: key_len は、MySQL が使用する実際のインデックスの長さを決定します。

4.2.8. ref
テーブルから行を選択するためにキーで使用される列または定数を示します。
4.2.9. rows
クエリの実行時に MySQL が検査する必要があると考える行数を表示します。
4.2.10. 補足
この列には、MySQL がクエリを解決した方法の詳細が含まれています
• 特徴: MySQL は、最初に一致する行を見つけた後、現在の行の組み合わせに対するさらなる行の検索を停止します。
• 存在しません: MySQL はクエリに対して LEFT JOIN 最適化を実行でき、LEFT JOIN 標準に一致する行を見つけた後、テーブル内の前の行の組み合わせをさらにチェックしなくなります。
• 各レコードの範囲チェック (インデックス マップ: #): MySQL は使用できる適切なインデックスを見つけられませんでしたが、前のテーブルのカラム値が既知であれば、いくつかのインデックスが使用できる可能性があることを発見しました。
• filesort の使用: MySQL では、ソートされた順序で行を取得する方法を理解するために追加のパスが必要です。
• インデックスの使用: それ以上の検索を行わずに、インデックス ツリー内の情報のみを使用して実際の行を読み取ることにより、テーブルから列情報を取得します。
• 一時テーブルの使用: クエリを解決するには、MySQL は結果を保持する一時テーブルを作成する必要があります。
• where:WHERE 句を使用すると、次のテーブルに一致する行、またはクライアントに送信される行を制限できます。
• sort_union(...) の使用、union(...) の使用、intersect(...) の使用: これらの関数は、index_merge 結合タイプのインデックス スキャンをマージする方法を示しています。
• group-by にインデックスを使用: テーブルにアクセスするインデックスを使用する方法と同様に、group-by にインデックスを使用すると、MySQL が GROUP BY または DISTINCT クエリのすべてのカラムをクエリするために使用できるインデックスを見つけたことを意味します。実際のテーブルへの追加の検索。

以下にいくつかのデータベース SQL 最適化ソリューションを示します。
(01) テーブル名の最も効率的な順序を選択します (頻繁な筆記テスト)

リーリー

(02) WHERE句での接続順序（頻繁な筆記テスト）

リーリー

(03) SELECT 句での * 記号の使用を避ける

リーリー

(04) DELETE の代わりに TRUNCATE を使用します

(05) 可能な限りCOMMITを使用する

リーリー

(06) HAVING 句を WHERE 句に置き換えます

リーリー

(07) より多くの内部関数を使用して SQL 効率を向上させます

(08) テーブルエイリアスの使用

リーリー

(09) 列のエイリアスを使用する

リーリー

Answer 2

説明

Answer 3

SQL 実行時に Explain 分析操作を自動的に実行し、コマンド ラインよりも操作が便利なビジュアル ツール navicat を使用することをお勧めします。

Answer 4

インターンシップから間もなく、私は SQL サーバーを使用しましたが、これも他の人によって発見され、記録されました。
DBCC DROPCLEANBUFFERS はキャッシュをクリアします
DBCC FREEPROCCACHE はプラン キャッシュ内の要素を削除します
SET STATISTICS TIME ON を使用して CPU 時間を確認します
SET STATISTICS IO ON を使用して論理読み取り数を確認します
SET STATISTICSプロフィールはこれではありません

の使い方がわかりません