質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

ただいまの
回答率

87.78%

MySQLで重複しないレコードのみを抽出するSQLが遅い

解決済

回答 4

投稿 編集

  • 評価
  • クリップ 3
  • VIEW 2,665

score 80

開発中のシステムで、各顧客からの受注をデータベースで管理しているのですが、受注に付随する情報として「伝票番号」があり、この伝票番号が他の受注と重複していない受注のみを一覧表示する必要があります。

伝票番号は他社システムより振り出されるものですが、一定範囲の連番がサイクリックに振り出されるようで、そのため重複の条件として、伝票日付が前後3か月以内の受注のみを対象とすることになっています。

伝票番号は受注によって付与されないケースも、また、1つの受注に2件以上付与されるケースもありますが、最大2件管理することになっています。

あれこれ検索のSQLやインデックスをこね回してみても、受注データが3000件弱で4秒ほどかかってしまっており苦戦しております。
検索のSQLやインデックスの貼り方、あるいはテーブル構造の見直しでも何らかヒントが頂けたらと思っています。
MySQLのバージョンは5.6.42です。 

テーブル構造

必要なところを抜き出すとこんな感じです。

DROP TABLE IF EXISTS orders;
CREATE TABLE orders(
    id INT NOT NULL AUTO_INCREMENT COMMENT '伝票ID',
    customer_id INT NOT NULL COMMENT '顧客ID',
    voucher_date DATE NOT NULL COMMENT '伝票日付',
    voucher_no1 VARCHAR(20) COMMENT '伝票番号1',
    voucher_no2 VARCHAR(20) COMMENT '伝票番号2',
    is_deleted TINYINT NOT NULL DEFAULT 0 COMMENT '削除フラグ(1:削除済み)',
    PRIMARY KEY (id),
    INDEX idx_orders_c_deleted(is_deleted, customer_id, voucher_no1, voucher_no2),
    INDEX idx_orders_c_voucher_no1(customer_id, is_deleted, voucher_no1, voucher_date),
    INDEX idx_orders_c_voucher_no2(customer_id, is_deleted, voucher_no2, voucher_date)
) DEFAULT CHARSET=utf8; 

試したこと

以下のようなSQLを書いてみましたが、EXPLAINの結果ordersのtypeがALLになってしまい、4秒近くかかります。また、ordersにFORCE INDEXを追加するとtypeはrefになるものの、時間はあまり変わりません。

SELECT orders.* FROM orders
WHERE is_deleted = 0
AND customer_id = 2
AND NOT EXISTS(
   SELECT 1 FROM orders duplicated
   WHERE 2 = duplicated.customer_id
   AND duplicated.is_deleted = 0
   AND orders.voucher_date - INTERVAL 3 MONTH <= duplicated.voucher_date 
   AND duplicated.voucher_date  <= orders.voucher_date + INTERVAL 3 MONTH 
   AND orders.id != duplicated.id
   AND (
    orders.voucher_no1 = duplicated.voucher_no1
    OR
    orders.voucher_no1 = duplicated.voucher_no2
    OR
    orders.voucher_no2 = duplicated.voucher_no1
    OR
    orders.voucher_no2 = duplicated.voucher_no2
   )
);


以下も試しましたがref_or_nullになってもっと遅いです。(3000件弱で5秒ちょっと)

SELECT orders.* FROM orders  FORCE INDEX(idx_orders_c_deleted)
WHERE customer_id = 2 
AND is_deleted = 0
AND (
    orders.voucher_no1 IS NULL OR (
        orders.voucher_no1 NOT IN (
            SELECT duplicate1.voucher_no1 
            FROM orders duplicate1 
            WHERE duplicate1.customer_id = 2 
            AND duplicate1.is_deleted = 0 
            AND duplicate1.id != orders.id 
            AND duplicate1.voucher_no1 IS NOT NULL 
            AND duplicate1.voucher_date BETWEEN orders.voucher_date - INTERVAL 3 MONTH AND orders.voucher_date + INTERVAL 3 MONTH
        )
        AND orders.voucher_no1 NOT IN (
            SELECT duplicate2.voucher_no2
            FROM orders duplicate2
            WHERE duplicate2.customer_id = 2
            AND duplicate2.is_deleted = 0
            AND duplicate2.id != orders.id
            AND duplicate2.voucher_no2 IS NOT NULL
            AND duplicate2.voucher_date BETWEEN orders.voucher_date - INTERVAL 3 MONTH AND orders.voucher_date + INTERVAL 3 MONTH
        )
    )
    OR
    orders.voucher_no2 IS NULL OR (
        orders.voucher_no2 NOT IN (
            SELECT duplicate3.voucher_no1
            FROM orders duplicate3
            WHERE duplicate3.customer_id = 2
            AND duplicate3.is_deleted = 0
            AND duplicate3.id != orders.id
            AND duplicate3.voucher_no1 IS NOT NULL
            AND duplicate3.voucher_date BETWEEN orders.voucher_date - INTERVAL 3 MONTH AND orders.voucher_date + INTERVAL 3 MONTH
        )
        OR
        orders.voucher_no2 NOT IN (
            SELECT duplicate4.voucher_no2
            FROM orders duplicate4
            WHERE duplicate4.customer_id = 2
            AND duplicate4.is_deleted = 0
            AND duplicate4.id != orders.id
            AND duplicate4.voucher_no2 IS NOT NULL
            AND duplicate4.voucher_date BETWEEN orders.voucher_date - INTERVAL 3 MONTH AND orders.voucher_date + INTERVAL 3 MONTH
        )
    )
);


よろしくお願い致します。

  • 気になる質問をクリップする

    クリップした質問は、後からいつでもマイページで確認できます。

    またクリップした質問に回答があった際、通知やメールを受け取ることができます。

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 過去に投稿した質問と同じ内容の質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

回答 4

checkベストアンサー

+3

voucher_no1, voucher_no2,IDを共に含むインデックスが無いですね。
効率から考えると、以下の様な並びのインデックスじゃないかな。
(customer_id, voucher_date, voucher_no1, voucher_no2, ID, is_deleted)

チューニングは適切にインデックスが使用される所から徐々に条件を追加するなどすると、分かりやすいですよ。

追記

SQLは解決したとして、インデックスについて纏めると
メイン用のインデックス(customer_id, voucher_date, voucher_no1, voucher_no2, is_deleted)
サブクエリー用のインデックス1(customer_id, voucher_date, voucher_no1)
サブクエリー用のインデックス2(customer_id, voucher_date, voucher_no2)
になるかと。
is_deletedidについてはフィルター程度でしょうから、インデックスが使われない様なら、インデックスに含めるという事で。

現状とあまり違いは無いかもしれませんが、順序を変えるのは効果が出るかもしれません。
狙いとしては、抽出条件がインデックスのみでの解決となる事。

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2018/12/14 14:31 編集

    サブクエリ側を
    WHERE orders.customer_id = duplicated.customer_id
    としても所要時間は定数値の場合と同じですね。

    > サブクエリーでは、サブクエリー側の項目は式にしないこと
    式にしない、とは計算したり関数を通したりしない、という意味でよかったでしょうか?
    orders.voucher_date - INTERVAL 3 MONTH <= duplicated.voucher_date
    だとメインクエリ側は3か月ひいてるけどサブクエリ側はそのままの値だからOKということでしょうか?

    キャンセル

  • 2018/12/14 14:36

    OKです。
    メイン側も式インデックスにすれば、効率は良くなるかもしれません。
    https://qiita.com/hmatsu47/items/128ece7276e4deac1477
    現状で十分であれば、敢えて行う必要はありませんが。

    キャンセル

  • 2018/12/14 14:46

    式インデックスは初めて知りました。勉強不足でした。
    MySQLのバージョンを変更しないと使えませんが、レコードを増やしてみてから検討します。
    いろいろありがとうございます。勉強になりました。

    キャンセル

+2

AND orders.voucher_date - INTERVAL 3 MONTH <= duplicated.voucher_date 
AND duplicated.voucher_date  <= orders.voucher_date + INTERVAL 3 MONTH 

これを where 句の末尾に移動したら時間が変わるなんてことはないですかね?
この部分はINDEXは効かないし、時間がかかる可能性があると思うので。
基本こういったものはオプティマイザーの方で最適化されているはずですが、経験上うまく働かないこともあったので、念のため最適と思われる順序を試してみるのも良いと思います。

あとは、以下のように条件を絞ってコストが高い部分を見極めてみるのも1つの手がかりになると思います。

・「前後3か月以内」という条件を抜かすと速くなったりしますか?
・「伝票番号1」「伝票番号2」という条件を「伝票番号1」のみとした場合、速くなったりしますか?

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2018/12/14 13:34

    いろいろありがとうございます。
    日付の条件をサブクエリの末尾にしてもレスポンスは変わりませんね。
    ちなみに、試したこととして明記はしなかったのですが、順番の変更はすべて試してみましたが、変化しなかったです。

    前後3か月を条件からはずすと、13秒とさらに遅くなりました。試しにvoucher_dateを含まないINDEXを追加して試してもみたのですが変わりません。
    伝票番号1=伝票番号1のみにした場合は0.01秒前後です。ネックになっているのはやはりその部分のようです。

    キャンセル

  • 2018/12/14 13:56 編集

    ありがとうございます。「伝票番号1のみにする」がヒントになりました。NOT EXISTSをANDで4つつなぐのでよさそうです。

    キャンセル

  • 2018/12/14 14:40

    なるほど。OR検索がネックだったようですね

    キャンセル

0

MYSQLは詳しくないので、間違っているかもしれませんが

AND NOT EXISTS(
   SELECT 1 FROM orders duplicated


上記のEXISTS 内の FROM に orders がありますが
これは先頭の orders と同じ扱いになるんですかね?

この、EXISTS 内の FROM には orders は要らないような気がします。
私の認識が間違ってたらすいません。

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2018/12/14 13:38

    EXISTS内では、ordersテーブル内に同じ伝票番号がないかを検索しているため、FROM ordersで正しいと思います。duplicatedはサブクエリ内のordersテーブルの別名です。相関サブクエリであり、メインのクエリと同じテーブル名なので、別名をつける必要があると思います。
    (私もSQLは得意ではないので誤っていたらすみません)

    キャンセル

  • 2018/12/14 13:40

    なるほど別名ですね、私が間違っていました。
    申し訳ありません。

    キャンセル

0

解決済みのようですが

    orders.voucher_no1 = duplicated.voucher_no1
    OR
    orders.voucher_no1 = duplicated.voucher_no2

    orders.voucher_no1 IN(duplicated.voucher_no1, duplicated.voucher_no2)

と表記できるはずです。もしうまくいって時間が変わらないのであれば、冗長な記述は少し減らせますね。

案1

SELECT orders.* FROM orders
  WHERE is_deleted = 0
  AND customer_id = 2
  AND NOT EXISTS(
    SELECT 1 FROM orders duplicated
      WHERE 2 = duplicated.customer_id
      AND duplicated.is_deleted = 0
      AND orders.id != duplicated.id
      AND orders.voucher_no1 IN(duplicated.voucher_no1, duplicated.voucher_no2)
      AND orders.voucher_date - INTERVAL 3 MONTH <= duplicated.voucher_date 
      AND duplicated.voucher_date <= orders.voucher_date + INTERVAL 3 MONTH 
  )
  AND NOT EXISTS(
    SELECT 1 FROM orders duplicated
      WHERE 2 = duplicated.customer_id
      AND duplicated.is_deleted = 0
      AND orders.id != duplicated.id
      AND orders.voucher_no2 IN(duplicated.voucher_no1, duplicated.voucher_no2)
      AND orders.voucher_date - INTERVAL 3 MONTH <= duplicated.voucher_date 
      AND duplicated.voucher_date <= orders.voucher_date + INTERVAL 3 MONTH 
  )
;

案2

SELECT orders.* FROM orders
  WHERE is_deleted = 0
  AND customer_id = 2
  AND NOT EXISTS(
    SELECT 1 FROM orders duplicated
      WHERE 2 = duplicated.customer_id
      AND duplicated.is_deleted = 0
      AND orders.id != duplicated.id
      AND  duplicated.voucher_no1 IN(orders.voucher_no1, orders.voucher_no2)
      AND orders.voucher_date - INTERVAL 3 MONTH <= duplicated.voucher_date 
      AND duplicated.voucher_date <= orders.voucher_date + INTERVAL 3 MONTH 
  )
  AND NOT EXISTS(
    SELECT 1 FROM orders duplicated
      WHERE 2 = duplicated.customer_id
      AND duplicated.is_deleted = 0
      AND orders.id != duplicated.id
      AND  duplicated.voucher_no2 IN(orders.voucher_no1, orders.voucher_no2)
      AND orders.voucher_date - INTERVAL 3 MONTH <= duplicated.voucher_date 
      AND duplicated.voucher_date <= orders.voucher_date + INTERVAL 3 MONTH 
  )
;

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2018/12/18 15:34 編集

    AND orders.voucher_no1 IN (duplicated.voucher_no1, duplicated.voucher_no2)
    AND orders.voucher_no2 IN (duplicated.voucher_no1, duplicated.voucher_no2)
    先ほど書きましたが、こちらも20秒近くかかります。オプティマイザが賢くない(選ばれるインデックスが適切でない)というよりはMySQLの仕様ではないのでしょうか。

    > SHOW PROFILE も参考にしてみてください。
    どの過程で時間がかかっているか見るという意味でしょうか?

    キャンセル

  • 2018/12/18 15:50

    > 先ほど書きました
    失礼しました、見落としをしていたようですね。

    そうですね。オプティマイザが賢くない場合は、INDEX 指定が効く可能性もありますが、これはもう議論がある程度されているようなので口を挟みません。クエリを分割する以外に、2つ以上のインデックスを使ってくれないということなのでしょう。

    > どの過程で時間がかかっているか見るという意味でしょうか?
    はい、そうです。今回は役に立たない気もします。

    キャンセル

  • 2018/12/18 16:00

    > クエリを分割する以外に、2つ以上のインデックスを使ってくれないということなのでしょう。
    ORでつながれた各条件が、同じカラムに対する条件なら問題ないのでしょうが、別のカラムの場合はそのうちの1つしかインデックスの効果を期待できないということなのだと思います。確認していないのですが、単純に伝票番号1または2が'12345'であるレコードを抽出する場合も、別々にSELECTした結果をUNIONする方が速かったりするのかもしれません。勉強になりました。

    >> どの過程で時間がかかっているか見るという意味でしょうか?
    > はい、そうです。今回は役に立たない気もします。
    覚えておきます。といってももう廃止予定の構文なんですね。

    キャンセル

15分調べてもわからないことは、teratailで質問しよう!

  • ただいまの回答率 87.78%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる

関連した質問

同じタグがついた質問を見る