MySQL における 8 つの一般的な SQL 使用例

序文

MySQL は、2016 年もデータベースの人気において力強い成長傾向を維持し続けました。 MySQL データベース上でアプリケーションを構築したり、Oracle から MySQL に移行したりする顧客が増えています。ただし、MySQL データベースの使用時に、応答時間が遅くなったり、CPU がいっぱいになったりするなどの問題が発生するお客様もいます。

Alibaba Cloud RDS 専門サービスチームは、クラウド顧客が多くの緊急の問題を解決するのを支援してきました。「ApsaraDB エキスパート診断レポート」に表示される一般的な SQL の問題の一部を、参考のために以下にまとめます。

1. LIMITステートメント

ページネーションクエリは最も一般的に使用されるシナリオの 1 つですが、通常、問題が発生する可能性が最も高い場所でもあります。

たとえば、次の単純なステートメントの場合、一般的な DBA の解決策は、type、name、および create_time フィールドに複合インデックスを追加することです。このように、条件付きソートはインデックスを効果的に活用し、パフォーマンスを迅速に向上させることができます。

選択* 
FROM操作 
ここで、タイプ = 'SQLStats' 
  AND 名前 = 'SlowLog' 
ORDER BY 作成時間 
制限 1000、10;

おそらく、DBA の 90% 以上がここでこの問題を解決するのをやめるでしょう。

しかし、LIMIT 句が「LIMIT 1000000,10」になると、プログラマーは依然として不満を言うでしょう。「レコードを 10 個しか取得しないのに、なぜまだ遅いのか?」

データベースは 1,000,000 番目のレコードがどこから始まるかを認識しておらず、インデックスがある場合でも最初から計算する必要があることを知っておく必要があります。このようなパフォーマンスの問題が発生する場合、ほとんどの場合、プログラマーが怠惰であることが原因です。

フロントエンドのデータ参照やページング、大規模なデータのバッチエクスポートなどのシナリオでは、前のページの最大値をクエリ条件のパラメーターとして使用できます。 SQL は次のように再設計されます。

選択* 
FROM操作 
ここで、タイプ = 'SQLStats' 
AND 名前 = 'SlowLog' 
かつ、create_time > '2017-03-16 14:00:00' 
ORDER BY create_time 制限 10;

新しい設計では、クエリ時間は基本的に固定されており、データ量が増えても変化しません。

2. 暗黙的な変換

SQL ステートメント内のクエリ変数とフィールド定義タイプ間の不一致も、よくあるエラーの 1 つです。たとえば、次の文:

mysql> 拡張SELECTの説明 * 
  > my_balance bから 
  > b.bpn = 14000000123 の場合 
  > AND b.isverified は NULL です。
mysql> 警告を表示します。
| 警告 | 1739 | フィールド 'bpn' の型または照合順序の変換のため、インデックス 'bpn' で参照アクセスを使用できません

フィールド bpn は varchar(20) として定義されており、MySQL の戦略では比較前に文字列を数値に変換します。関数はテーブルフィールドに作用し、インデックスは無効になります。

上記の状況は、プログラマの本来の意図ではなく、アプリケーションフレームワークによって自動的に入力されたパラメータである可能性があります。最近は複雑なアプリケーションフレームワークが数多く存在します。これらは便利に使用できますが、落とし穴に陥る可能性もあるので注意が必要です。

3. 関連付けの更新と削除

MySQL 5.6 ではマテリアライゼーション機能が導入されましたが、現時点ではクエリステートメントのみが最適化されることに注意することが重要です。更新または削除は手動で JOIN に書き換える必要があります。

例えば、次の UPDATE 文では、MySQL は実際にループ/ネストされたサブクエリ (DEPENDENT SUBQUERY) を実行し、その実行時間を想像することができます。

UPDATE操作o 
ステータスを「申請中」に設定 
WHERE o.id IN (SELECT id 
    FROM (SELECT o.id, 
        o.ステータス 
      オペレーションoから 
      ここで o.group = 123 
        AND o.status が ( 'done' ) に含まれない 
      ORDER BY o.parent、 
         o.id 
      制限1) t);

実行計画:

+----+--------------------+-------+-------+---------------+---------+-------+------+-----------------------------------------------------+
| id | select_type | テーブル | タイプ | possible_keys | key | key_len | ref | 行 | 追加 |
+----+--------------------+-------+-------+---------------+---------+-------+------+-----------------------------------------------------+
| 1 | PRIMARY | o | index | | PRIMARY | 8 | | 24 | where の使用; temporary の使用 |
| 2 | 依存サブクエリ | | | | | | | | | const テーブルを読み取った後に不可能な WHERE が検出されました |
| 3 | DERIVED | o | ref | idx_2,idx_5 | idx_5 | 8 | const | 1 | where の使用; filesort の使用 |
+----+--------------------+-------+-------+---------------+---------+-------+------+-----------------------------------------------------+

JOIN に書き換えられた後、サブクエリの選択モードが DEPENDENT SUBQUERY から DERIVED に変わり、実行速度が 7 秒から 2 ミリ秒に大幅に高速化されます。

UPDATE操作o 
  JOIN (SELECT o.id, 
       o.ステータス 
      オペレーションoから 
      ここで o.group = 123 
       AND o.status が ( 'done' ) に含まれない 
      ORDER BY o.parent、 
        o.id 
      制限 1) t
   オン o.id = t.id 
ステータスを「申請中」に設定

実行計画は次のように簡略化されます。

+----+-------------+-------+-------+---------------+--------+-------+------+-----------------------------------------------------+
| id | select_type | テーブル | タイプ | possible_keys | key | key_len | ref | 行 | 追加 |
+----+-------------+-------+-------+---------------+--------+-------+------+-----------------------------------------------------+
| 1 | PRIMARY | | | | | | | | | const テーブルを読み取った後に不可能な WHERE が見つかりました |
| 2 | DERIVED | o | ref | idx_2,idx_5 | idx_5 | 8 | const | 1 | where の使用; filesort の使用 |
+----+-------------+-------+-------+---------------+--------+-------+------+-----------------------------------------------------+

4. 混合ソート

MySQL では混合ソートにインデックスを使用できません。しかし、シナリオによっては、特別な方法を使用してパフォーマンスを向上させる機会がまだあります。

選択* 
my_order から o 
  my_appraise a を a.orderid = o.id に内部結合します 
ORDER BY a.is_reply ASC、 
   a.appraise_time DESC 
制限 0, 20

実行プランには完全なテーブルスキャンが表示されます。

+----+-------------+--------+----------+-----------+----------+-------------------------+-+
| id | select_type | テーブル | タイプ | 可能なキー | キー | キー長 | ref | 行 | 追加 
+----+-------------+--------+----------+-----------+----------+-------------------------+-+
| 1 | SIMPLE | a | ALL | idx_orderid | NULL | NULL | NULL | 1967647 | filesort を使用 |
| 1 | シンプル | o | eq_ref | プライマリ | プライマリ | 122 | a.orderid | 1 | NULL |
+----+-------------+--------+---------+----------+----------+----------------+----------+-+

is_reply には 0 と 1 の 2 つの状態しかないため、次のように書き直すと、実行時間は 1.58 秒から 2 ミリ秒に短縮されます。

選択* 
から((選択*
   my_order から o 
    内部結合 my_appraise a 
      ON a.orderid = o.id 
       かつ、is_reply = 0 
   ORDER BY appraise_time DESC 
   制限0、20） 
  ユニオンオール 
  (選択*
   my_order から o 
    内部結合 my_appraise a 
      ON a.orderid = o.id 
       かつ、is_reply = 1 
   ORDER BY appraise_time DESC 
   制限0、20））t 
ORDER BY is_reply ASC、 
   評価時間 DESC 
制限 20;

5. EXISTSステートメント

MySQL は EXISTS 句を処理するときに、ネストされたサブクエリ実行方法を使用します。次の SQL ステートメントのようになります。

選択*
my_neighbor から n 
  左結合 my_neighbor_apply sra 
    オン n.id = sra.neighbor_id 
     かつ sra.user_id = 'xxx' 
n.topic_status < 4 の場合 
  存在する(1を選択) 
     message_info m から 
     n.id = m.neighbor_idの場合 
       かつ、m.inuser = 'xxx') 
  かつ n.topic_type <> 5

実行計画は次のとおりです。

+----+--------------------+-------+------+-----+------------------------------------------+---------+--------+---------+ -----+
| id | select_type | テーブル | タイプ | possible_keys | key | key_len | ref | 行 | 追加 |
+----+--------------------+-------+-------+ -----+------------------------------------------+---------+---------+ -----+
| 1 | PRIMARY | n | ALL | | NULL | NULL | NULL | 1086041 | where の使用 |
| 1 | PRIMARY | sra | ref | | idx_user_id | 123 | const | 1 | where の使用 |
| 2 | 従属サブクエリ | m | ref | | idx_message_info | 122 | const | 1 | インデックス条件の使用; where の使用 |
+----+--------------------+-------+-------+ -----+------------------------------------------+---------+---------+ -----+

exists を削除して join に変更すると、ネストされたサブクエリを回避でき、実行時間を 1.93 秒から 1 ミリ秒に短縮できます。

選択*
my_neighbor から n 
  内部結合メッセージ情報 m 
    ON n.id = m.neighbor_id 
     かつ、m.inuser = 'xxx' 
  左結合 my_neighbor_apply sra 
    オン n.id = sra.neighbor_id 
     かつ sra.user_id = 'xxx' 
n.topic_status < 4 の場合 
  かつ n.topic_type <> 5

新しい実行プラン:

+----+-------------+-------+--------+ -----+------------------------------------------+--------+ -----+------+ -----+
| id | select_type | テーブル | タイプ | possible_keys | key | key_len | ref | 行 | 追加 |
+----+-------------+-------+--------+ -----+------------------------------------------+--------+ -----+------+ -----+
| 1 | SIMPLE | m | ref | | idx_message_info | 122 | const | 1 | インデックス条件の使用 |
| 1 | SIMPLE | n | eq_ref | | PRIMARY | 122 | ighbor_id | 1 | where の使用 |
| 1 | SIMPLE | sra | ref | | idx_user_id | 123 | const | 1 | where の使用 |
+----+-------------+-------+--------+ -----+------------------------------------------+--------+ -----+------+ -----+

6. 条件付きプッシュダウン

外部クエリ条件を複雑なビューまたはサブクエリにプッシュダウンできない場合は次のとおりです。

集計サブクエリ。
LIMIT を含むサブクエリ;
UNION または UNION ALL サブクエリ。
出力フィールド内のサブクエリ。

次のステートメントに示すように、実行プランでは、条件が集計サブクエリの後に実行されることが示されています。

選択* 
FROM (SELECT ターゲット、 
    カウント（*） 
  FROM操作 
  GROUP BY ターゲット) t 
ここで、ターゲット = 'rm-xxxx'

+----+--------------+------------+--------+--------------+-------------+-------+-------+-------------+
| id | select_type | テーブル | タイプ | possible_keys | key | key_len | ref | 行 | 追加 |
+----+--------------+------------+--------+--------------+-------------+-------+-------+-------------+
|
1
| プライマリ |
 <派生2> 
| 参照 |
 <自動キー
0
> 
| <自動キー0> |
514
| 定数 |
2
| where の使用 |
| 2 | DERIVED | 操作 | インデックス | idx_4 | idx_4 | 519 | NULL | 20 | インデックスを使用 |
+----+--------------+------------+--------+--------------+-------------+-------+-------+-------------+

クエリ条件をセマンティックな観点から直接プッシュダウンできると判断したら、次のように書き直します。

ターゲットを選択、 
  カウント（*） 
FROM操作 
ここで、ターゲット = 'rm-xxxx' 
GROUP BYターゲット

実行計画は次のようになります。

+----+--------------+-----------+-------+---------------+--------+-------+------+--------------------+
| id | select_type | テーブル | タイプ | possible_keys | key | key_len | ref | 行 | 追加 |
+----+--------------+-----------+-------+---------------+--------+-------+------+--------------------+
| 1 | SIMPLE | 操作 | ref | idx_4 | idx_4 | 514 | const | 1 | where の使用; index の使用 |
+----+--------------+-----------+-------+---------------+--------+-------+------+--------------------+

MySQL の外部条件をプッシュダウンできない理由の詳細については、次の記事を参照してください: http://mysql.taobao.org/monthly/2016/07/08

7. 事前に範囲を絞り込む

まず、最初の SQL ステートメント:

選択* 
my_order から o 
    左結合 my_userinfo u 
       ON o.uid = u.uid
    左結合 my_productinfo p 
       オン o.pid = p.pid 
ここで ( o.display = 0 ) 
    かつ ( o.ostaus = 1 ) 
ORDER BY o.selltime DESC 
制限 0, 15

この SQL ステートメントの本来の目的は、最初に一連の左結合を実行し、次にソートして最初の 15 件のレコードを取得することです。実行プランからは、最後のステップでソートされたレコードの推定数は 900,000 で、消費時間は 12 秒であることもわかります。

+----+-------------+-------+--------+---------------+----------+---------+----------------+--------+----------------------------------------------------+
| id | select_type | テーブル | タイプ | possible_keys | key | key_len | ref | 行 | 追加 |
+----+-------------+-------+--------+---------------+----------+---------+----------------+--------+----------------------------------------------------+
| 1 | SIMPLE | o | ALL | NULL | NULL | NULL | NULL | 909119 | where の使用; temporary の使用; filesort の使用 |
| 1 | シンプル | u | eq_ref | プライマリ | プライマリ | 4 | o.uid | 1 | NULL |
| 1 | SIMPLE | p | ALL | PRIMARY | NULL | NULL | NULL | 6 | where の使用; 結合バッファーの使用 (ブロック ネスト ループ) |
+----+-------------+-------+--------+---------------+----------+---------+----------------+--------+----------------------------------------------------+

最終的な WHERE 条件とソートはどちらも左端のプライマリテーブルに対するものであるため、左結合を実行する前に、my_order を事前にソートしてデータ量を減らすことができます。 SQL を書き換えると、実行時間は約 1 ミリ秒に短縮されます。

選択* 
から （
選択* 
my_order から o 
ここで ( o.display = 0 ) 
    かつ ( o.ostaus = 1 ) 
ORDER BY o.selltime DESC 
制限 0, 15
） 
   左結合 my_userinfo u 
       ON o.uid = u.uid 
   左結合 my_productinfo p 
       オン o.pid = p.pid 
ORDER BY o.selltime DESC
制限 0, 15

実行プランを再度確認してください。サブクエリはマテリアライズされた後 (select_type=DERIVED) に JOIN に参加します。スキャンされる行数の推定値は依然として 900,000 ですが、インデックスと LIMIT 句を使用すると、実際の実行時間は非常に短くなります。

+----+--------------+------------+---------+---------------+----------+---+---------+----------------------------------------------------+
| id | select_type | テーブル | タイプ | possible_keys | key | key_len | ref | 行 | 追加 |
+----+--------------+------------+---------+---------------+----------+---+---------+----------------------------------------------------+
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 15 | temporary を使用; filesort を使用 |
| 1 | プライマリ | u | eq_ref | プライマリ | プライマリ | 4 | o.uid | 1 | NULL |
| 1 | PRIMARY | p | ALL | PRIMARY | NULL | NULL | NULL | 6 | where の使用; 結合バッファーの使用 (ブロック ネスト ループ) |
| 2 | DERIVED | o | index | NULL | idx_1 | 5 | NULL | 909112 | where の使用 |
+----+--------------+------------+---------+---------------+----------+---+---------+----------------------------------------------------+

8. 中間結果セットのプッシュダウン

最初に最適化された次の例を見てみましょう (左結合のメインテーブルがクエリ条件よりも優先されます)。

SELECT a.*、 
     c.割り当てられた 
から （ 
       リソースIDを選択 
       my_distributed から 
          ここで、isdelete = 0 
          かつ cusmanagercode = '1234567' 
          ORDER BY セールコード制限 20) a 
左結合 
     （ 
       SELECT resourcesid、sum(ifnull(allocation, 0) * 12345) 割り当て済み 
       my_resourcesから 
          GROUP BYリソースid) c 
ON a.resourceid = c.resourcesid

この声明には他に何か問題がありますか?サブクエリ c が全テーブル集計クエリであることは容易にわかります。これにより、テーブルの数が特に多い場合にステートメント全体のパフォーマンスが低下します。

実際、サブクエリ c の場合、左結合の最終結果セットは、メインテーブルのリソース ID と一致するデータのみを考慮します。したがって、ステートメントを次のように書き直すと、実行時間は元の 2 秒から 2 ミリ秒に短縮されます。

SELECT a.*、 
     c.割り当てられた 
から （ 
          リソースIDを選択 
          my_distributed から 
          ここで、isdelete = 0 
          かつ cusmanagercode = '1234567' 
          ORDER BY セールコード制限 20) a 
左結合 
     （ 
          SELECT resourcesid、sum(ifnull(allocation, 0) * 12345) 割り当て済み 
          my_resources rから、 
              （ 
                   リソースIDを選択 
                   my_distributed から 
                   ここで、isdelete = 0 
                   かつ cusmanagercode = '1234567' 
                   ORDER BY セールコード 制限 20) a 
          ここで、r.resourcesid = a.resourcesid 
          GROUP BYリソースid) c 
ON a.resourceid = c.resourcesid

しかし、サブクエリ a は SQL ステートメント内に複数回出現します。この記述方法では、オーバーヘッドが追加されるだけでなく、ステートメント全体が複雑に見えてしまいます。 WITH ステートメントを使用して再度書き直します。

と 
（ 
     リソースIDを選択 
     my_distributed から 
     ここで、isdelete = 0 
     かつ cusmanagercode = '1234567' 
     ORDER BY セールコード 制限 20)
SELECT a.*、 
     c.割り当てられた 
から 
左結合 
     （ 
          SELECT resourcesid、sum(ifnull(allocation, 0) * 12345) 割り当て済み 
          my_resources rから、 
              1つの 
          ここで、r.resourcesid = a.resourcesid 
          GROUP BYリソースid) c 
ON a.resourceid = c.resourcesid

要約する

データベースコンパイラは実行プランを生成し、SQL が実際にどのように実行されるかを決定します。しかし、コンパイラは最善を尽くすだけであり、すべてのデータベースコンパイラが完璧であるわけではありません。

上記のシナリオのほとんどでは、他のデータベースでもパフォーマンスの問題が発生します。データベースコンパイラの特性を理解することによってのみ、その欠点を回避し、高性能な SQL ステートメントを記述することができます。

プログラマーがデータモデルを設計し、SQL ステートメントを記述するときは、アルゴリズムのアイデアや認識を取り入れる必要があります。

複雑な SQL ステートメントを記述する場合は、WITH ステートメントを使用する習慣を身に付ける必要があります。簡潔で明確な SQL ステートメントを使用すると、データベースへの負担も軽減されます。

さて、今回の記事は以上です。この記事の内容が皆さんの勉強や仕事に少しでも参考になれば幸いです。123WORDPRESS.COM をよろしくお願いいたします。

以下もご興味があるかもしれません: