開発中のシステムで、各顧客からの受注をデータベースで管理しているのですが、受注に付随する情報として「伝票番号」があり、この伝票番号が他の受注と重複していない受注のみを一覧表示する必要があります。
伝票番号は他社システムより振り出されるものですが、一定範囲の連番がサイクリックに振り出されるようで、そのため重複の条件として、伝票日付が前後3か月以内の受注のみを対象とすることになっています。
伝票番号は受注によって付与されないケースも、また、1つの受注に2件以上付与されるケースもありますが、最大2件管理することになっています。
あれこれ検索のSQLやインデックスをこね回してみても、受注データが3000件弱で4秒ほどかかってしまっており苦戦しております。
検索のSQLやインデックスの貼り方、あるいはテーブル構造の見直しでも何らかヒントが頂けたらと思っています。
MySQLのバージョンは5.6.42です。
テーブル構造
必要なところを抜き出すとこんな感じです。
SQL
1DROP TABLE IF EXISTS orders; 2CREATE TABLE orders( 3 id INT NOT NULL AUTO_INCREMENT COMMENT '伝票ID', 4 customer_id INT NOT NULL COMMENT '顧客ID', 5 voucher_date DATE NOT NULL COMMENT '伝票日付', 6 voucher_no1 VARCHAR(20) COMMENT '伝票番号1', 7 voucher_no2 VARCHAR(20) COMMENT '伝票番号2', 8 is_deleted TINYINT NOT NULL DEFAULT 0 COMMENT '削除フラグ(1:削除済み)', 9 PRIMARY KEY (id), 10 INDEX idx_orders_c_deleted(is_deleted, customer_id, voucher_no1, voucher_no2), 11 INDEX idx_orders_c_voucher_no1(customer_id, is_deleted, voucher_no1, voucher_date), 12 INDEX idx_orders_c_voucher_no2(customer_id, is_deleted, voucher_no2, voucher_date) 13) DEFAULT CHARSET=utf8;
試したこと
以下のようなSQLを書いてみましたが、EXPLAINの結果ordersのtypeがALLになってしまい、4秒近くかかります。また、ordersにFORCE INDEXを追加するとtypeはrefになるものの、時間はあまり変わりません。
SQL
1SELECT orders.* FROM orders 2WHERE is_deleted = 0 3AND customer_id = 2 4AND NOT EXISTS( 5 SELECT 1 FROM orders duplicated 6 WHERE 2 = duplicated.customer_id 7 AND duplicated.is_deleted = 0 8 AND orders.voucher_date - INTERVAL 3 MONTH <= duplicated.voucher_date 9 AND duplicated.voucher_date <= orders.voucher_date + INTERVAL 3 MONTH 10 AND orders.id != duplicated.id 11 AND ( 12 orders.voucher_no1 = duplicated.voucher_no1 13 OR 14 orders.voucher_no1 = duplicated.voucher_no2 15 OR 16 orders.voucher_no2 = duplicated.voucher_no1 17 OR 18 orders.voucher_no2 = duplicated.voucher_no2 19 ) 20);
以下も試しましたがref_or_nullになってもっと遅いです。(3000件弱で5秒ちょっと)
SQL
1SELECT orders.* FROM orders FORCE INDEX(idx_orders_c_deleted) 2WHERE customer_id = 2 3AND is_deleted = 0 4AND ( 5 orders.voucher_no1 IS NULL OR ( 6 orders.voucher_no1 NOT IN ( 7 SELECT duplicate1.voucher_no1 8 FROM orders duplicate1 9 WHERE duplicate1.customer_id = 2 10 AND duplicate1.is_deleted = 0 11 AND duplicate1.id != orders.id 12 AND duplicate1.voucher_no1 IS NOT NULL 13 AND duplicate1.voucher_date BETWEEN orders.voucher_date - INTERVAL 3 MONTH AND orders.voucher_date + INTERVAL 3 MONTH 14 ) 15 AND orders.voucher_no1 NOT IN ( 16 SELECT duplicate2.voucher_no2 17 FROM orders duplicate2 18 WHERE duplicate2.customer_id = 2 19 AND duplicate2.is_deleted = 0 20 AND duplicate2.id != orders.id 21 AND duplicate2.voucher_no2 IS NOT NULL 22 AND duplicate2.voucher_date BETWEEN orders.voucher_date - INTERVAL 3 MONTH AND orders.voucher_date + INTERVAL 3 MONTH 23 ) 24 ) 25 OR 26 orders.voucher_no2 IS NULL OR ( 27 orders.voucher_no2 NOT IN ( 28 SELECT duplicate3.voucher_no1 29 FROM orders duplicate3 30 WHERE duplicate3.customer_id = 2 31 AND duplicate3.is_deleted = 0 32 AND duplicate3.id != orders.id 33 AND duplicate3.voucher_no1 IS NOT NULL 34 AND duplicate3.voucher_date BETWEEN orders.voucher_date - INTERVAL 3 MONTH AND orders.voucher_date + INTERVAL 3 MONTH 35 ) 36 OR 37 orders.voucher_no2 NOT IN ( 38 SELECT duplicate4.voucher_no2 39 FROM orders duplicate4 40 WHERE duplicate4.customer_id = 2 41 AND duplicate4.is_deleted = 0 42 AND duplicate4.id != orders.id 43 AND duplicate4.voucher_no2 IS NOT NULL 44 AND duplicate4.voucher_date BETWEEN orders.voucher_date - INTERVAL 3 MONTH AND orders.voucher_date + INTERVAL 3 MONTH 45 ) 46 ) 47);
よろしくお願い致します。
回答4件
あなたの回答
tips
プレビュー
バッドをするには、ログインかつ
こちらの条件を満たす必要があります。
2018/12/14 03:41 編集
2018/12/14 04:14
2018/12/14 04:16
2018/12/14 04:21
2018/12/14 04:36 編集
2018/12/14 04:51
2018/12/14 04:53
2018/12/14 04:58
2018/12/14 05:06
2018/12/14 05:23 編集
2018/12/14 05:20
2018/12/14 05:27 編集
2018/12/14 05:25
2018/12/14 05:33 編集
2018/12/14 05:36
2018/12/14 05:46