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

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

ただいまの
回答率

90.45%

  • MySQL

    7155questions

    MySQL(マイエスキューエル)は、TCX DataKonsultAB社などが開発するRDBMS(リレーショナルデータベースの管理システム)です。世界で最も人気の高いシステムで、オープンソースで開発されています。MySQLデータベースサーバは、高速性と信頼性があり、Linux、UNIX、Windowsなどの複数のプラットフォームで動作することができます。

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

受付中

回答 3

投稿 編集

  • 評価
  • クリップ 2
  • VIEW 2,412

tamogi

score 72

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

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

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

--CREATE文
CREATE TABLE tbl(
 id int(5) Primary key, 
 name varchar(255) Not Null,
 no1 int(3), -- id列に存在する数値が入ります
 no2 int(3), -- id列に存在する数値が入ります
 no3 int(3), -- id列に存在する数値が入ります
 no4 int(3) -- id列に存在する数値が入ります
);

--現在考えてる案(パフォーマンスに難あり)
SELECT id, name 
FROM tbl
WHERE id IN (
 SELECT no1 FROM tbl
 UNION SELECT no2 FROM tbl
 UNION SELECT no3 FROM tbl
 UNION SELECT no4 FROM tbl 
);

ーーー以下追記ーーー
>「どういうデータの時にどういう結果を取得したいのか」
no1〜no4に登録されている数値のnameを一意に取得したいと考えています。
要件そのものは上のSELECT文で満たしているので、主にパフォーマンスの問題です。

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


ーーー追記その2ーーー
取得するデータのイメ0時についてですが、
以下の様なデータが入っているとします。
イメージ説明
この場合は、no1〜no4の列に4を除く数字が挿入されているため、
取得したいデータは0,1,2,3,5の5種類と、関連するname列のデータとなります。

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



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

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

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

    クリップを取り消します

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

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

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

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

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

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

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

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

    質問の評価を下げる

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

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

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

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

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

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

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

    詳細な説明はこちら

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

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

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

質問への追記・修正、ベストアンサー選択の依頼

  • Tak1wa

    2015/09/26 01:43

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

    キャンセル

  • pi-chan

    2015/09/26 10:11

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

    キャンセル

回答 3

+1

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

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

mysql> EXPLAIN SELECT id, name 
    -> FROM tbl
    -> WHERE id IN (
    ->  SELECT no1 FROM tbl
    ->  UNION SELECT no2 FROM tbl
    ->  UNION SELECT no3 FROM tbl
    ->  UNION SELECT no4 FROM tbl 
    -> );
+----+--------------------+----------------+------+---------------+------+---------+------+------+-------------+
| id | select_type        | table          | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+--------------------+----------------+------+---------------+------+---------+------+------+-------------+
|  1 | PRIMARY            | tbl            | ALL  | NULL          | NULL | NULL    | NULL |    6 | Using where |
|  2 | DEPENDENT SUBQUERY | tbl            | ALL  | NULL          | NULL | NULL    | NULL |    6 | Using where |
|  3 | DEPENDENT UNION    | tbl            | ALL  | NULL          | NULL | NULL    | NULL |    6 | Using where |
|  4 | DEPENDENT UNION    | tbl            | ALL  | NULL          | NULL | NULL    | NULL |    6 | Using where |
|  5 | DEPENDENT UNION    | tbl            | ALL  | NULL          | NULL | NULL    | NULL |    6 | Using where |
| NULL | UNION RESULT       | <union2,3,4,5> | ALL  | NULL          | NULL | NULL    | NULL | NULL |             |
+----+--------------------+----------------+------+---------------+------+---------+------+------+-------------+
6 rows in set (0.00 sec)

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

mysql> SELECT id, name FROM tbl AS t1 WHERE EXISTS (
    ->     SELECT * FROM tbl AS t2 
    ->     WHERE t1.id = t2.no1 
    ->        OR t1.id = t2.no2 
    ->        OR t1.id = t2.no3 
    ->        OR t1.id = t2.no4
    -> );
+----+------+
| id | name |
+----+------+
|  0 | A    |
|  1 | B    |
|  2 | C    |
|  3 | D    |
|  5 | F    |
+----+------+
5 rows in set (0.00 sec)

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

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

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

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

ALTER TABLE tbl ADD INDEX (no1);
ALTER TABLE tbl ADD INDEX (no2);
ALTER TABLE tbl ADD INDEX (no3);
ALTER TABLE tbl ADD INDEX (no4);

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

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

投稿

編集

  • 回答の評価を上げる

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

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

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

  • 回答の評価を下げる

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

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

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

0

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

mySqlではサブクエリでの抽出を元にしたIN句は相関サブクエリになって負荷を上げる事があるようですので、UNIONよりもそっちが遅い原因かも知れませんね。その場合はこれで解決するかも知れません。
SELECT id, name 
FROM tbl
WHERE id EXISTS (
 SELECT no1 FROM tbl
 UNION SELECT no2 FROM tbl
 UNION SELECT no3 FROM tbl
 UNION SELECT no4 FROM tbl 
);
ただサブクエリの抽出結果が膨大で何ギガバイトにもなるなら、それが原因でメモリを圧迫して遅くなる可能性はあります。(その場合はもうテーブル構成の見直しが必要でしょうが…)

逆にサブクエリの抽出結果が少量と予測できるなら、UNIONは重複削除の為に都度ソートを行うのでUNION ALLにしてしまったほうが早いかも知れません。(微差だと思います)

投稿

編集

  • 回答の評価を上げる

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

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

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

  • 回答の評価を下げる

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

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

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

-1

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


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

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

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


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

投稿

  • 回答の評価を上げる

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

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

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

  • 回答の評価を下げる

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

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

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

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

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

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

  • MySQL

    7155questions

    MySQL(マイエスキューエル)は、TCX DataKonsultAB社などが開発するRDBMS(リレーショナルデータベースの管理システム)です。世界で最も人気の高いシステムで、オープンソースで開発されています。MySQLデータベースサーバは、高速性と信頼性があり、Linux、UNIX、Windowsなどの複数のプラットフォームで動作することができます。