MySQLで、複数列の一意な値を取得したい

2015/09/26 21:21 追記しました。

いつもお世話になっております。
以下の様なテーブルがあり、no1〜no5の列全てから一意な値を取得したいのですが、UNIONで列を統合してしまうと非常に時間がかかるため、他に良い方法があればご教示下さい。

正規化してテーブルを分けることができたら一番良いんですが、もともとこのような問い合わせをする予定がなかったらしく、今更変えるわけにも行かないのが現状です・・・。

sql
1--CREATE文
2CREATE TABLE tbl(
3 id int(5) Primary key, 
4 name varchar(255) Not Null,
5 no1 int(3), -- id列に存在する数値が入ります
6 no2 int(3), -- id列に存在する数値が入ります
7 no3 int(3), -- id列に存在する数値が入ります
8 no4 int(3) -- id列に存在する数値が入ります
9);
10
11--現在考えてる案（パフォーマンスに難あり）
12SELECT id, name 
13FROM tbl
14WHERE id IN (
15 SELECT no1 FROM tbl
16 UNION SELECT no2 FROM tbl
17 UNION SELECT no3 FROM tbl
18 UNION SELECT no4 FROM tbl 
19);
20

ーーー以下追記ーーー

「どういうデータの時にどういう結果を取得したいのか」

no1〜no4に登録されている数値のnameを一意に取得したいと考えています。
要件そのものは上のSELECT文で満たしているので、主にパフォーマンスの問題です。

こちらで回答になっているでしょうか。

ーーー追記その２ーーー
取得するデータのイメ０時についてですが、
以下の様なデータが入っているとします。

この場合は、no1〜no4の列に4を除く数字が挿入されているため、
取得したいデータは0,1,2,3,5の５種類と、関連するname列のデータとなります。

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

Tak1wa

2015/09/25 16:43

文章と現在考えている案からやりたいことは解釈できそうなのですが、認識違いがあると面倒ですので良ければ、「どういうデータの時にどういう結果を取得したいのか」のサンプルを記載してもらえないでしょうか。

pi-chan

2015/09/26 01:11

サンプルデータをご提示頂けると助かります。そうすれば、ご提示頂いたSQL（UNIONを使ったもの）で取得したい結果も把握できますし、代替案で期待通りの結果が得られることも示せると思いますので。

行動規範の内容に同意します

回答3件

SQLをチューニングする際は、まず実行計画を見ることをお勧めします。

質問者様の"現在考えてる案"では、全表走査が5回実行されていることが分かります。

SQL
1mysql> EXPLAIN SELECT id, name 
2    -> FROM tbl
3    -> WHERE id IN (
4    ->  SELECT no1 FROM tbl
5    ->  UNION SELECT no2 FROM tbl
6    ->  UNION SELECT no3 FROM tbl
7    ->  UNION SELECT no4 FROM tbl 
8    -> );
9+----+--------------------+----------------+------+---------------+------+---------+------+------+-------------+
10| id | select_type        | table          | type | possible_keys | key  | key_len | ref  | rows | Extra       |
11+----+--------------------+----------------+------+---------------+------+---------+------+------+-------------+
12|  1 | PRIMARY            | tbl            | ALL  | NULL          | NULL | NULL    | NULL |    6 | Using where |
13|  2 | DEPENDENT SUBQUERY | tbl            | ALL  | NULL          | NULL | NULL    | NULL |    6 | Using where |
14|  3 | DEPENDENT UNION    | tbl            | ALL  | NULL          | NULL | NULL    | NULL |    6 | Using where |
15|  4 | DEPENDENT UNION    | tbl            | ALL  | NULL          | NULL | NULL    | NULL |    6 | Using where |
16|  5 | DEPENDENT UNION    | tbl            | ALL  | NULL          | NULL | NULL    | NULL |    6 | Using where |
17| NULL | UNION RESULT       | <union2,3,4,5> | ALL  | NULL          | NULL | NULL    | NULL | NULL |             |
18+----+--------------------+----------------+------+---------------+------+---------+------+------+-------------+
196 rows in set (0.00 sec)

恐らく、この全表走査の繰り返しがパフォーマンスに悪影響を与えていると考えられますので、
以下のクエリで改善できるかと思います。

SQL
1mysql> SELECT id, name FROM tbl AS t1 WHERE EXISTS (
2    ->     SELECT * FROM tbl AS t2 
3    ->     WHERE t1.id = t2.no1 
4    ->        OR t1.id = t2.no2 
5    ->        OR t1.id = t2.no3 
6    ->        OR t1.id = t2.no4
7    -> );
8+----+------+
9| id | name |
10+----+------+
11|  0 | A    |
12|  1 | B    |
13|  2 | C    |
14|  3 | D    |
15|  5 | F    |
16+----+------+
175 rows in set (0.00 sec)

このクエリだと、以下の通り2回の全表走査で済みます。

SQL
1mysql> EXPLAIN SELECT id, name FROM tbl AS t1 WHERE EXISTS (
2    ->     SELECT * FROM tbl AS t2 
3    ->     WHERE t1.id = t2.no1 
4    ->        OR t1.id = t2.no2 
5    ->        OR t1.id = t2.no3 
6    ->        OR t1.id = t2.no4
7    -> );
8+----+--------------------+-------+------+---------------+------+---------+------+------+-------------+
9| id | select_type        | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
10+----+--------------------+-------+------+---------------+------+---------+------+------+-------------+
11|  1 | PRIMARY            | t1    | ALL  | NULL          | NULL | NULL    | NULL |    6 | Using where |
12|  2 | DEPENDENT SUBQUERY | t2    | ALL  | NULL          | NULL | NULL    | NULL |    6 | Using where |
13+----+--------------------+-------+------+---------------+------+---------+------+------+-------------+
142 rows in set (0.00 sec)

もっとも、全表走査自体が重い処理なので、
レコードの件数によってはこれでも期待するパフォーマンスは出ないかも知れません。

その場合は、'no1'から'no4'の各列にインデックスを張ることを検討してみてはいかがでしょうか?

SQL
1ALTER TABLE tbl ADD INDEX (no1);
2ALTER TABLE tbl ADD INDEX (no2);
3ALTER TABLE tbl ADD INDEX (no3);
4ALTER TABLE tbl ADD INDEX (no4);

サブクエリでインデックスを使用できるようになれば、さらにパフォーマンスが上がるかも知れません。
（以下の実行計画では、レコードが少なすぎて結局インデックスを使っていませんが。）

SQL
1mysql> EXPLAIN SELECT id, name FROM tbl AS t1 WHERE EXISTS (
2    ->     SELECT * FROM tbl AS t2 
3    ->     WHERE t1.id = t2.no1 
4    ->        OR t1.id = t2.no2 
5    ->        OR t1.id = t2.no3 
6    ->        OR t1.id = t2.no4
7    -> );
8+----+--------------------+-------+------+-----------------+------+---------+------+------+-------------+
9| id | select_type        | table | type | possible_keys   | key  | key_len | ref  | rows | Extra       |
10+----+--------------------+-------+------+-----------------+------+---------+------+------+-------------+
11|  1 | PRIMARY            | t1    | ALL  | NULL            | NULL | NULL    | NULL |    6 | Using where |
12|  2 | DEPENDENT SUBQUERY | t2    | ALL  | no1,no2,no3,no4 | NULL | NULL    | NULL |    6 | Using where |
13+----+--------------------+-------+------+-----------------+------+---------+------+------+-------------+
142 rows in set (0.00 sec)

投稿2015/09/27 10:52

編集2015/09/27 11:00

KiyoshiMotoki

総合スコア4791

・「非常に時間がかかる」というのは、具体的にはどの程度の時間ですか？
・レコード数は現在何件ですか？将来の増加予想はどんなものですか？
・ストレージの全容量と使用量はどの程度ですか？

正規化してテーブルを分けることができたら一番良いんですが、
もともとこのような問い合わせをする予定がなかったらしく、
今更変えるわけにも行かないのが現状です・・・。

「今更」でいいので、テーブル構成を変更するのが最もいいとは思います。

そうでない場合、検索用のテーブルを作成して対応してもいいでしょう。nameとnoだけのテーブルで2つのカラムで一意になるように。そしてデータ追加の際には検索用テーブルにも追加すると。削除・更新の場合には、検索用のレコードを削除すべきかどうか確認して対応するというようにすればいいでしょう。(既存テーブル中のどのカラム・どのレコードにも存在しないか確認の上で削除するというように)

そもそもこんなクソ設計する方が悪いのです。遅くなって当然。どうしても改善したいのならストレージを犠牲にするのがベストではないかと思います。

投稿2015/09/26 19:39

miu_ras

総合スコア902

no1〜no5の列全てから一意な値を取得

この文章を見て、「no1列内で一意な値」「no2列内で一意な値」「no3列内で一意な値」「no4列内で一意な値」を全て抽出するのかと思ったのですが、例示されたコードを見た感じ最終目的は全行のno1～no4列の何処かに1つ以上存在するIDの抽出でしょうか？
(「一意な値」は「一つしかない値」といった意味で使われる事が多いようですが、コードは2つ以上存在する値も対象にしているようですので)

mySqlではサブクエリでの抽出を元にしたIN句は相関サブクエリになって負荷を上げる事があるようですので、UNIONよりもそっちが遅い原因かも知れませんね。その場合はこれで解決するかも知れません。

SQL
1SELECT id, name 
2FROM tbl
3WHERE id EXISTS (
4 SELECT no1 FROM tbl
5 UNION SELECT no2 FROM tbl
6 UNION SELECT no3 FROM tbl
7 UNION SELECT no4 FROM tbl 
8);