Mysql 5.6でGroup BY(複数カラム)を実行しても重複が残るのですが、理由がわかりません。

Question

正規化されていないテーブル(table_1)の共通項目を別テーブル(table_2)にくくりだし、それを参照するようにしようとしています。

そこで、まずtable_1のデータをgroup byし重複を取り除いて、table_2に登録しようとしたのですが、group byしたはずなのにtable_2に重複行が現れます。（実行したのは下記の手順）

1 空のtable_2に対して、group byしたtable_1のデータを登録する。

INSERT table_2(col_1, col_2, col_3, col_4, col_5, col_6, col_7, col_8, col_9, col_10)
SELECT col_1, col_2, col_3, col_4, col_5, col_6, col_7, col_8, col_9, col_10
FROM table_1
GROUP BY col_1, col_2, col_3, col_4, col_5, col_6, col_7, col_8, col_9, col_10

2 table_2をgroup byする 重複行が見つかる。

SELECT col_1, col_2, col_3, col_4, col_5, col_6, col_7, col_8, col_9, col_10
FROM table_2
GROUP BY col_1, col_2, col_3, col_4, col_5, col_6, col_7, col_8, col_9, col_10

なお、条件は

• グループ化するカラムは、integerまたはdecimalまたはVARCHAR
• col_1, col_2以外にはNULLが入っている可能性がある。
• table_1のデータ数は, 22000件程度

このような結果になるのは何故なのでしょうか。

よろしくお願いします。

以下追記

カラムの数及び型に記載の誤りがありました。

• カラムは全部で10個
• 型に文字型がある(太字で修正してます。)

なお、詳細なtable等の条件は

• 各テーブルにはidとcreated_at, updated_atがある。(idはオートインクリメント)
• 各テーブルはPrimary keyはidのみ
• 各テーブルにPrimary以外のユニーク制約はない。
• col_1: int(11)
• col_2 ~ col_9: VARCHAR(255)
• col_10: DECIMAL(4,1)
• 文字コードは全カラムがCAHRSET: utf-8, COLLATE: utf8_unicode_ci
• 今回のケースでtable_2にinsertされた件数は7000件弱

のようになっています。

また、2の重複の見つけ方について、補足・修正します。

2.1 table_1にtable_2への参照を持たせるために、下記のようなsqlを実行すると重複エラーが起きる。

UPDATE table_1 t1
SET id_of_table_2 = (
  SELECT id FROM table_2 t2
  WHERE t1.col_1 = t2.col_1
    AND t1.col_2 =t2.col_2
            :
            :
   AND t1.col_10 =t2.col_10

2.2 重複がないことをチェックするため、下記のsqlを投げると1件もHITしない。

SELECT *
FROM table_2
GROUP BY col_1, col_2, col_3, col_4, col_5, col_6, col_7, col_8, col_9, col_10
HAVING COUNT(id) > 1

2.3 2.1のupdate文をwhere句で範囲指定し、重複エラーをはいている行を特定し、table_2の該当行を見つける。

すると、idの1と２が同じであるように見受けられる。

2.4 ２行あると見受けられるものに絞ってGROUP BYしてみる。1件HITする。

SELECT *
FROM table_2
WHERE id IN(1,2)
GROUP BY col_1, col_2, col_3, col_4, col_5, col_6, col_7, col_8, col_9, col_10
HAVING COUNT(id) > 1

2.5 何度か2.2と2.4のSQLを交互に実行してみても結果変わらず。(2.4の行をコピペしてwhere句を削って実行しているだけなので、typoはしていないと思う。)

というような手順で重複行が見つかってると判断しています。

以下追記2(質問に対する回答で使用した方法)

調べること

• 重複してる行(ID1とID2)の値が本当に一致しているか。

調べた際の条件

• table_2のID1とID2は目で見た感じはcol_1~col_10の値が同じ
• ID1とID2に関してはすべてのカラム(col_1~col_10)に値がある。

流したSQL

SELECT b.*
FROM table_2 a, table_2 b
WHERE a.id = ID1
        AND a.id <> b.id
	AND a.col_1 = b.col_1
                  :
                  :
	AND a.col_10 = b.col_10

上記のsqlを流すと、ID2のデータが返ってくる。
そのため、ID1とID2のcol_1~col_10の値は同値と判断した。

Comments

[KiyoshiMotoki]

手元の環境で簡単に検証してみましたが、再現しません。

もっと詳しい状況が知りたいので、差し支えない範囲で以下の情報を追記いただけますか？
1. table_1、table_2のテーブル定義（CREATE TABLE文）
2. 重複が発生しているデータの、実際の値

あと、
> 2 table_2をgroup byする。重複行が見つかる。
で提示されているSQL文では重複の有無は分からないはずですが、
「重複が発生している」と判断した理由を教えていただけますか？

[Mikan]

申し訳ございません。記載にミスがあり文字型もデータに含まれていました。
間違った情報でお時間を取らせてしまいすいませんでした。
記載内容を修正し、重複を発見したと考える手順を追記いたしました。

Answer 1

情報の追記、ありがとうございます。

追記いただいた情報をもとに調査してみましたが、私も原因が分かりませんorz
そのため、以下に記載する内容は具体的な回答ではない事を前置きさせていただきます。

---

まず、念のため以下を確認させていただきますが、お気を悪くなさらないで下さい(^^;

1 空のtable_2に対して、group byしたtable_1のデータを登録する。

の前は、

• "table_2"テーブルは空でしたね？

実行したINSERT文は

• ご質問に記載した通りのものですね？実はGROUP BY句に指定するカラムを一つ、忘れたりしていませんね？

INSERT文を実行した後、

• "table_1", "table_2"双方ともデータの更新・追加は一切行なっていませんね？
• 実はMikan様以外のメンバーがこっそり触っていたり、
• 稼働中のサービスがアクセスしていたりしませんね？

---

で、ここから本題です。

疑わしいレコードを特定できているようなので、次は
「疑わしいレコードのどのカラムに差分が発生しているか」
を特定しましょう。
原因の箇所を絞り込めば、それだけ調査は前進できると思いますので。

カラムを特定する方法ですが、

以下追記2(質問に対する回答で使用した方法)

で実施されたSQL文の

AND a.col_[n] = b.col_[n]

部分を1行ずつ、以下のように変更しながら繰り返し実施してみて下さい。

AND a.col_[n] = BINAARY b.col_[n]

以下のような要領です。

1回目 : col_1のみ

SELECT b.*
FROM table_2 a, table_2 b
WHERE a.id = ID1
        AND a.id <> b.id
    AND a.col_1 = BINARY b.col_1
    AND a.col_2 = b.col_2
                  :
    AND a.col_10 = b.col_10

2回目 : col_1とcol_2

SELECT b.*
FROM table_2 a, table_2 b
WHERE a.id = ID1
        AND a.id <> b.id
    AND a.col_1 = BINARY b.col_1
    AND a.col_2 = BINARY b.col_2
                  :
    AND a.col_10 = b.col_10

3回目 : col_1、col_2、col_3

（以下略）

もし、バイナリレベルの差分があれば、
原因のカラムを比較する式にBINARY句を追加した時点で
ID2 のデータが返却されなくなるはずです。

Comments

[Mikan]

回答が遅くなってしまいすいません。

まず、前提条件の部分なのですが、再度冷静に確認/実行しましたが、カラム数やtable_2の状態は質問内容通りとなっていました。

ここからが本題の方ですが、ご指摘いただいた方法でbinary比較をしていったところ、col_6にbinaryをつけた段階で表示結果が変わりましたので、col_6の値がbinaryレベルで違うことがわかりました。ありがとうございます。

ところで、今回の結果からgroup byの際の比較ではbinaryレベルで行い、=を用いた比較ではbinaryレベルではない比較（何で比較しているのかはよく分からない）しているように思うのですが、これらの認識であっているでしょうか。

よろしくお願いします。

[KiyoshiMotoki]

Mikan様、ご報告ありがとうございます。

> col_6の値がbinaryレベルで違うことがわかりました。
では、
・どの文字がどのように異なっているのか
・table_1とtable_2との間で、値が異なっていないか
　（INSERT時にデータが壊れていないか）
など、さらに踏み込んだ調査が可能になりますね。

> group byの際の比較ではbinaryレベルで行い、=を用いた比較ではbinaryレベルではない比較（何で比較しているのかはよく分からない）しているように思うのですが、これらの認識であっているでしょうか。

私の認識では、どちらも同じ方法 （対象のカラムに指定されている"COLLATE"属性）で比較しているはずです。

以下のように、GROUP BY句にBINARY句をつけた場合とつけない場合とで結果が変わる事からも、
この認識で間違いないように思います。

mysql> SELECT * FROM table_3;
+------+
| name |
+------+
| a |
| a | # 見た目では分からないが、実は'a 'という文字（末尾に半角スペースがある）
| A |
| a |
+------+
4 rows in set (0.00 sec)

mysql> SELECT * FROM table_3 GROUP BY name;
+------+
| name |
+------+
| a |
+------+
1 row in set (0.00 sec)

mysql> SELECT * FROM table_3 GROUP BY BINARY name;
+------+
| name |
+------+
| A |
| a |
| a |
+------+
3 rows in set (0.00 sec)

[Orlofsky]

Mikanさんは最初から
http://qiita.com/sawarame/items/aa99408ce24930ad7def
テーブルのCREATE文を提示すると解決が早かったかも？
できればダブっているデータを含んでいるINSERT文も。

Answer 2

実際にデータが重複するのを再現出来るだけの情報を提示された方が解決が早いですが、

SELECT col_1, col_2, col_3, col_4, col_5, col_6, col_7, col_8, col_9

FROM table_2
GROUP BY col_1, col_2, col_3, col_4, col_5, col_6, col_7, col_8, col_9

SELECT DISTINCT col_1, col_2, col_3, col_4, col_5, col_6, col_7, col_8, col_9
FROM table_2

としたらどうなりますか？
SELECTされた結果を一意にするには DISTINCT を使います。
GROUP BY はグループ関数とセットで使うのがマナーです。SQLはきちんと覚えた方が良いです。

Comments

[Mikan]

なるほど、そんなマナーがあるとは知りませんでした。ご指摘ありがとうございます。

ただ、ご指摘いただいたSQLを流してみてもやはり結果は変わりませんでした。

なお、DISTINCT と GROUP BY　を行った場合で、内部の処理ロジック/パフォーマンスが変わったりするのでしょうか。

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

[KiyoshiMotoki]

Mikan様、Orlofsky様、横から失礼します。

> DISTINCT と GROUP BY　を行った場合で、内部の処理ロジック/パフォーマンスが変わったりするのでしょうか。
変わる場合も、変わらない場合もあります。

テーブルや取得するカラム、抽出条件を様々に変えながら
"SELECT DISTINCT"と"GROUP BY"それぞれの実行計画(*)を取得してみると分かりますが、
両者で実行計画が同じ場合も、異なる場合もあるからです。

SQLは、異なる書き方で同じ結果が得られる文を複数、書く事ができますが、
操作対象のテーブルのレコード数や格納されているデータ、インデックスの有無など、様々な状況によって
劇的にパフォーマンスに差が出る場合があります。

そのため、画一的に
「こちらを使うべき」
と考える必要はなく、ケースバイケースで最適な方法を選択するのが良いです。
（これはSQLに限った話ではありませんが）

* 実行計画については以下を参照
https://dev.mysql.com/doc/refman/5.6/ja/explain.html
http://nippondanji.blogspot.jp/2009/03/mysqlexplain.html

[Mikan]

KiyoshiMotoki様

>テーブルや取得するカラム、抽出条件を様々に変えながら"SELECT DISTINCT"と"GROUP BY"それぞれの実行計画(*)を取得してみると分かりますが、両者で実行計画が同じ場合も、異なる場合もあるからです。
なるほど、内部では各々の計算手法で最適と思われる実行計画を選んでいるので一緒になる場合もあれば、違う場合もあるという感じなのですね。

>そのため、画一的に「こちらを使うべき」と考える必要はなく、ケースバイケースで最適な方法を選択するのが良いです。

やはり銀の弾丸みたいなものはどこにもないんですね〜。教えていただいた実行計画の取り方やパフォーマンスチューニングなどの方法を調べながら、そのあたりの知見を増やしていこうと思います。丁寧な解説ありがとうございました。

[Orlofsky]

KiyoshiMotoki様、フォローありがとうございます。
SQLはきちんと統計情報を取得した上で実行計画を確認するのがいちおうの目安。処理時間を実測してベストなSQLをチョイスって手間はかかります。

Mikan様はSQLをきちんと学んでください。

Answer 3

2番目のSQLだけでも重複行が存在するというのは変に思えますね。

因みに重複行があるとはco1_1～col_9の何れを比較しても一致する行が2行以上あるということですよね？ソートするとまったく同じ行があったみたいな。
という事は次のどれかでもcol_xに重複が現れるのでしょうか？

select co1_1,count(*) as n from table_2 group by co1_1
select co1_2,count(*) as n from table_2 group by co1_2
select co1_3,count(*) as n from table_2 group by co1_3
select co1_4,count(*) as n from table_2 group by co1_4
select co1_5,count(*) as n from table_2 group by co1_5
select co1_6,count(*) as n from table_2 group by co1_6
select co1_7,count(*) as n from table_2 group by co1_7
select co1_8,count(*) as n from table_2 group by co1_8
select co1_9,count(*) as n from table_2 group by co1_9

もし重複行が出たとしたらそのカラムに問題があるのかも知れません。

しかし、データ型が文字なら見た目同じでもデータは異なったというケースが想定できますが、数値ではそれも無いでしょうし。

Comments

[Mikan]

申し訳ございません。記載にミスがあり文字型もデータに含まれていました。
間違った情報でお時間を取らせてしまいすいませんでした。

[hirohiro]

むむ？2.2でHITゼロなのに、2.4に抽出行があるのですか？
2.4は2.2に絞込み条件を追加しただけに見えますが

[Mikan]

その通りです。絞り込み条件(見た目が重複っぽいもの)を加えると見つかるとい現象が起きています。

最初書き間違いを疑ったので、2.4の行を実行→2,4のsqlをコピーwhere句を消し、それを実行というやり方をしてみたのですが、結果が変わらず。

絞り込み条件を除いてまったく同じはずなので、見えない文字やutf8_unicode_ciによる影響もないような気がするんですが・・・

[hirohiro]

すみません。コメント内容に誤りがあり一部修正しました。

なるほど何故2.2でHITが無く、2.4にHITがあるのかは私にはわかりません。

table_2のidはユニークなのでしょうか？
SELECT * FROM table_2 WHERE id IN(1,2)
これで3行以上HITしたりしないでしょうか？

後はtable_2のIDはユニークだとして、ID1とID2のco1_1～col_10は実際には一致していますか？

[Mikan]

table_1, table_2ともにidのみがPRIMARY KEYとなってます。（記載漏れ度々すいません。）

念のため調べてみたのですが、ID1, ID2に該当するものはそれぞれ一つずつしか現れませんでした。

以下、投稿内容を修正しました。
一致しているかの判断をどうすればいいかわからなかったので、とりあえず投稿に加筆したSQLを流してみて一致しているという判断を下しました。

何かベストな確認方法があればお教えください。

[hirohiro]

原因や理由はさっぱりわからないのですが、データが重複と判定されるか異なると判定されるかの違いは、データがサブクエリを経由したかどうかに掛かっているようですね。(MySQLではIN句は実際にはサブクエリが動作しているようですので。)
select * from table_2 where id < 3 group by col_1,col_2,....,col_10 HAVING COUNT(id) > 1　を試みても抽出はゼロになるのではないかと予測します。
実際のデータが一致しているか異なっているのかはバイナリ値を比較してみればわかると思います。
ただデータが違った（もしくは一致していた）が判明したとして、なんでSQL上で本件のような現象が起きているのかは私にはわかりません。
中途半端でスミマセン。

[Mikan]

丁寧な回答をしていただきありがとうございます。

ご指摘いただいたようにbinary値の比較をしてみると、col_6で違いが出てきました。
ありがとうございます。

where句の有無に関する部分については、もうちょっと調べてみて何かわかったら追記しておきます。

[hirohiro]

>>binary値の比較をしてみると、col_6で違いが出てきました。
興味深いですね。特定(?)のバイナリ値で差異をもつデータは特定の条件下で同じと判定されているということですね。内部的に型変換が動いているのかも知れませんね。（もしかするとDBに一定の条件で起動する関数を登録してたりはしませんか？それが勝手に型変換やTAB/空白排除をしてる可能性もあるかも知れません。）

>>where句の有無に関する部分については
あ、前のコメントに書いたのはwhere句ではなくて、幾つかの結果を見たらサブクエリを経由した場合にbinary値の異なる値を重複と判定しているように見えましたので、inをwhereに置換してサブクエリを排除したらどうなるのかなと思ったものです。