【テーブル設計/SQL】

1案件に20個のキーワードを設定し、それらを検索したいという要件があるとき、どういうテーブル設計が自然(ベター)ですか?

1. 1テーブルに20個のキーワード項目を用意する。
kanriNumber, keyword1, keyword2, keyword3..., contents

2. 案件テーブルとキーワードテーブルの2つを用意する。
kanriNumber, contents
kanriNumber, keyNumber, keyword

3. その他
他にいい方法があれば教えてください。

テーブルを2つに分けたほうがSQL文はまとまりがよくなると思いますが、
パフォーマンスが悪くなるのでは、と心配しています。

2の際に想定しているSQL文をコメント欄に書きます。

回答の条件
  • 1人2回まで
  • 登録:2008/07/08 12:02:04
  • 終了:2008/07/12 12:49:50

回答(2件)

id:chuken_kenkou No.1

chuken_kenkou回答回数722ベストアンサー獲得回数542008/07/08 14:52:52

ポイント60pt

母体データ件数は、どのくらいあるのでしょうか?

まず、「列名 LIKE '%a%'」といった検索条件を使う発想をやめましょう。こういった条件指定をすると、インデクスを定義しても、有効利用されず、母体データ件数に比例して性能劣化します。

せっかくキーワード列を設けるなら、「列名='キーワード'」か、「列名 LIKE 'c%'」といったように

インデクスを有効利用できる検索条件を使えるように設計できませんか?

そうでないなら、キーワードを持った列を作ること自体、意味がありません。

「=」条件やLIKEの前方一致の条件にすれば、キーワード列のインデクスを有効利用できるので、案2の方式を採用しても性能を出せます。

どうしても、「列名 LIKE '%a%'」のような検索条件しか設計できなければ、キーワードを持つ列など作るのはやめて、全文検索などの機能を使うことを検討する必要があるでしょう。

id:Kenju

ありがとうございます。

> まず、「列名 LIKE '%a%'」といった検索条件を使う発想をやめましょう。

なるほど、性能劣化は問題ですね。ちょっと考え直します。

2008/07/08 19:10:48
id:inokuni No.2

いのくに回答回数1343ベストアンサー獲得回数212008/07/08 15:11:47

ポイント10pt

AND条件

SELECT DISTINCT Anken.* 
 FROM Anken
 INNER JOIN Keyword AS Key1
  ON  Anken.kanriNumber = Key1.kanriNumber
 INNER JOIN Keyword AS Key2
  ON  Anken.kanriNumber = Key2.kanriNumber
 INNER JOIN Keyword AS Key3
  ON  Anken.kanriNumber = Key3.kanriNumber
 WHERE (Key1.keyword LIKE '%a%')
  AND  (Key2.keyword LIKE '%b%')
  AND  (Key3.keyword LIKE '%c%')

OR条件

SELECT DISTINCT Anken.* 
 FROM Anken
 INNER JOIN Keyword
  ON  Anken.kanriNumber = Keyword.kanriNumber
 WHERE (Keyword.keyword LIKE '%a%')
  OR (Keyword.keyword LIKE '%b%')
  OR (Keyword.keyword LIKE '%d%')
id:Kenju

ありがとうございます。

そうですね、このSQL文のほうがすっきりしていていいですね。

設計レベルでは何かないでしょうか?

2008/07/08 19:11:55
  • id:Kenju
    And条件
    Select Anken.*
    From Anken
    Inner join ( Select Distinct kanriNumber
    From Keyword
    Where keyword Like '%a%') As key1
    On Anken.kanriNumber = key1.kanriNumber
    Inner join ( Select Distinct kanriNumber
    From Keyword
    Where keyword Like '%b%') As key2
    On key1.kanriNumber = key2.kanriNumber
    Inner join ( Select Distinct kanriNumber
    From Keyword
    Where keyword Like '%c%') As key3
    On key2.kanriNumber = key3.kanriNumber

    Or条件
    Select Anken.*
    From Anken
    Inner join ( Select Distinct kanriNumber
    From Keyword
    Where keyword Like '%a%'
    Or keyword Like '%b%'
    Or keyword Like '%c%' ) As key1
    On Anken.kanriNumber = key1.kanriNumber
  • id:kn1967
    1つのSQLで様々な条件に合わせられるようにLIKEを使っているのだとすれば
    その考え方自体が問題かもしれません。
    処理を高速化したいのであればLIKEを使わずに
    プログラム側で動的にSQLを生成するほうがよろしいかと思います。
    そうすればシンプルな構造1のほうが高速動作が期待できると思います。

    RDBMSも判らなければデータ量も判らないので何とも言えないのですが
    コメント欄のSQLについては
    INNER JOINのネストは重かろうと思います。
      AND条件はWHERE と COUNT
      OR条件はWHERE と EXISTS
    の構文のほうが高速処理してくれそうな気がします。
  • id:Kenju
    kn1967 様
    コメントありがとうございます。

    > RDBMSも判らなければデータ量も判らない
    SQLSerever2005です。
    データ量は案件が400行程度、キーワードが1案件に10程度で、1案件が1Mバイトくらいです。(ファイルを格納するので

    > 処理を高速化したいのであればLIKEを使わずに
    > プログラム側で動的にSQLを生成するほうがよろしいかと思います。
    この部分想定できないのですが、例えば"bab"というキーワードをaという単語で検索するとき、どうしたらいいのでしょうか。

    > AND条件はWHERE と COUNT
    これは、条件設定した個数がcountの値と一致するか、で見ればいいですか。
    SQL文を編集するしかないものの、innner joinがひとつになっていいですね。
    検討します。
  • id:Kenju
    回答へのコメントができないので後にします。

    キーワードに設定した文字列は本文には含まれないようなのです。
    そのため全文検索も要件として存在します。
    こちらはlikeで検索するしかないですよね。

    キーワードはlikeを使わない方向で考えます。ありがとうございます。


    ところで、テーブル設計についてもご教示願いたいと思います。
    2案よりは1案のほうが速度が速いという感じなのですかね。
    こちらはすっきりしたSQLを思いつかなくて困ってます。

  • id:kn1967
    1案のほうはオーバーヘッドがかかりませんし
    仮にLIKEを使わないのであればインデックスも多少効くかもしれません。

    ただし、仮に
      keyword1,keyword2
      みかん,果実
      果実,りんご
    というようにキーワードが無秩序に入っているとした場合などには
    2案でないと対応が難しい。

    そうではなくて
      keyword1,keyword2
      みかん,果実
      りんご,果実
    というようにフィールド毎に意味が分けられているならば1案が望ましい。

    話の流れ的にデータベース概論的な方向になってしまいますが
    データベース構築の際にはフィールド毎に意味を持たせるのが基本となりますので
    それに則って案1にすべきか案2にすべきかを決めるべきかと思います。
    キーワードが無秩序にあるのであれば必然的に案2になるのではないでしょうか?

    いずれにしても必要に応じて動的にSQLを生成するほうが楽かと個人的には思います。
  • id:kn1967
    >データ量は案件が400行程度、キーワードが1案件に10程度で

    SQLが少々複雑になっても、インデックスが効かなくても
    それほど気にするほどの応答時間はかからないと思いますよ。
    もちろん、SQLが簡単になるほうがメンテナンスの面などで後々が楽ですけど。

    案1のSQLはWHERE句にツラツラと条件を並べるだけですから・・・
    動的生成で必要な部分だけ書くようにすればシンプルですよね。
    ただ、話の流れから考えると今回の件は案2のほうがヨサゲですが・・・。
  • id:Kenju
    kn1967 様
    ありがとうございます。

    >  keyword1,keyword2
    >  みかん,果実
    >  果実,りんご
    とくに決め事がないので恐らくこれになります。
    なので、1案だと、keyword1からもkeyword2からも検索しなければならず大変だな、と思っていました。
    たぶん最大で20x20の400の検索条件ですよね。。。

    まだ詳細は決めていませんが案2になりそうですね。
    とても参考になりました。

    回答されていらっしゃらないので後ほどポイント送信いたします。
  • id:kn1967
    それほど具体的な事を書いてもいないのでコメント欄を使わせていただいただけですから、あまりお気遣い無く。
    それよりも実は、回答1と回答2の投稿者からのフォローが見ものだったりしてます(笑)
  • id:Kenju
    もう使わない予定ですが、あとから参考にされる方もいらっしゃるかもしれませんので書き直しておきます。

    And条件
    Select Anken.*
    From Anken
    Inner join ( Select kanriNumber, Count(*) As counter
    From Keyword
    Where keyword Like '%a%'
    Or keyword Like '%b%'
    Or keyword Like '%c%'
    Group by kanriNumber ) As Keyword
    On Anken.kanriNumber = Keyword.kanriNumber
    Where Keyword.counter >= 3

    Or条件
    Select Anken.*
    From Anken
    Where Exists ( Select *
    From Keyword
    Where Anken.kanriNumber = Keyword.kanriNumber
    And ( keyword Like '%a%'
    Or keyword Like '%b%'
    Or keyword Like '%c%' ) )
  • id:Kenju
    でもこの記述だと、キーワードの内容が、以下だったときもヒットしちゃいますね。

    1a1
    2b2
    3a3


    解消する方法があるのかな。
  • id:kuro-yo
    書き直し失敬。

    20個のキーワードがあらかじめ決まっている場合なら、単純に2^20の整数値(当然ながら32ビット値に収まる)で集合を表せますので、それでキーワードテーブルを作成すれば(つまり、どのキーワードのキーナンバーも、2^nの形の整数にする)、検索のオーバーヘッドはほとんど無視できます。

    また、キーワードを文字列にしてしまえば、テーブルは案件テーブル一つにする事もできます。データベースは大きくなってしまいそうですが。

    または、キーワードが今後増える可能性があるなら、テーブルを三つにしては?
    -案件テーブル
    -キーワードテーブル
    -キーワード集合テーブル
    まず、検索キーワードの組み合わせをキーワード集合テーブルで検索して、検索用のキーナンバーを得ます。そのキーナンバーで案件テーブルを検索すれば、結果的には最初に書いたのと同じ事になります。
    テーブルの管理とクエリの形はかえって面倒くさくなりますが、検索のオーバーヘッドがどうしても気になるほど大きくなる事が予想されるのでしたら、管理が多少面倒でも速い方を選ぶしかないですよね。

    どこまで正規化するかは、結局、何を選ぶかのトレードオフでしかありませんよね。
  • id:kuro-yo
    補足:
    > キーワードを文字列に

    キーワードを全部つなげた文字列に、です。カラム一つで済みます。
    ただし、カラムを可変長にしなければならないかもしれません。

    または、キーワードテーブルのキーナンバーをつないだ文字列にする方法もありますね。
  • id:Kenju
    kuro-yo 様

    コメントありがとうございます。

    > キーワードを全部つなげた文字列に、です。カラム一つで済みます。

    そうですね。この方法も考えたのですが、1a1と2b2のキーワードを12で検索したときヒットすべきでないのにヒットしてしまうのですね。
    それを防ぐためにスペース区切りなどにしても、今度はキーワードとしてスペースが入ってきたときに置換しなければならない、など問題が多く今回は見送りました。

    > 単純に2^20の整数値で集合を表せる
    > キーワードテーブルのキーナンバーをつないだ文字列にする

    これらはわかりません。
    どういうことなんでしょう。
  • id:kuro-yo
    > 単純に2^20の整数値で集合を表せる

    2進数で表すと、各桁の値で、その桁に対応するキーワードの存在の有無を表せます。プログラミングの常套手段です。

    すなわち、n番目のキーワードを整数値2^(n-1)に対応付けます。
    最初のキーワードを1、次のキーワードを2、3番目のキーワードを4、4番目のキーワードを8、…、
    という具合に倍々の整数値に対応付けていくわけです。

    で、案件テーブルの方には、指定されたキーワードに対応付けた整数値の合計を格納しておくわけです。
    例えば、1番目のキーワードと5番目のキーワードと7番目のキーワードが指定されていたら、81(=1+16+64)を格納しておけばいいです。
    検索するときも同様で、検索したいキーワードに対応付けた整数値の合計で検索すればいいわけです。

    ただしこの方法では、OR条件で検索する時や、案件に指定されているキーワードを取得する際に、ビット演算が必要になります。

    > キーワードテーブルのキーナンバーをつないだ文字列にする

    キーワードの文字列をそのまま検索キーに使う必然性はないわけですので、キーワードに対応付けた別の文字列(数字がいいでしょうか)をつないだ文字列を1カラムに格納しても構わないわけです。

    例えば、1番目のキーワードと5番目のキーワードと7番目のキーワードが指定されていたら"1.5.7."を格納し、また検索する時も同様にします(※"1.5.7."は一例です。他のやりかたもあると思います)。

    > それを防ぐためにスペース区切りなどにしても、今度はキーワードとしてスペースが入ってきたときに置換しなければならない、など問題が多く今回は見送りました。

    トリッキーですが、スペースではなく、改行で区切るという方法もあるかと思います(推奨するわけではありません)。
  • id:Kenju
    kuro-yo 様

    ありがとうございます。

    すこし認識に相違があるかもしれません。
    今回、キーワードに指定する文字列は何番目という意味を持ちません。
    abcというキーワードは1-20のどこにいれてもよく、bで検索したときはどこに入っていてもヒットさせる、という要件です。
    -> この辺はabcでなければヒットしない、という方向にもっていきますが。

    で、順番に意味がないので前につめて格納することになります。
    8が空きだったら9-20は空きなのです。
    そして、キーワードはフリーの文字列です。固定の文字列ならもう少し楽なのですが・・・。
    キーワードテーブルにkeyNumberがあったので誤認があったのだと思いますが、登録順序の保持と主キーの設定のために設けている項目です。


    > トリッキーですが、スペースではなく、改行で区切るという方法もあるかと思います

    これは面白いですね。
    確かに改行が入ってくることはありません。
  • id:kuro-yo
    まず僕の認識の上での補足ですが、

    > 今回、キーワードに指定する文字列は何番目という意味を持ちません。

    テーブルに格納してIDがつけば、意味がありますよ。
    何番目というのは、説明のために持ち出した序数にすぎません。
    実際には、IDを使っても、論理的な何の違いはありません。

    (ただ、2進数の桁で表すほうは、最初に断っているように、20個のキーワードがあらかじめ決まっている場合、に限られますが、これも物理的限界があるからであって、例えば多倍長整数が使えるなら、やはり論理的な違いはありません)

    > abcというキーワードは1-20のどこにいれてもよく

    どこに入れてもいいのなら、あらかじめソートして入れてもいいのです。


    いずれも、単に考え方を示しているだけで、実装方法を示しているわけではないと、お考えください。

    ----

    > すこし認識に相違があるかもしれません。

    といわれて気付いたのですが、たぶん、認識のずれというのは、

    > 1案件に20個のキーワードを設定し、それらを検索したいという要件があるとき

    というのを、私は「20個のキーワードがあり、そのなかの自由な組み合わせで案件毎に設定する」という意味と捉えたのですが、
    Kenjuさんのおっしゃりたかったのは、「不特定のキーワードを、一つの案件につき最大20個まで設定する」という意味だったのですね…。

    数カラムならともかく、20カラムも検索対象があるのはあまり好ましくないと思いますので(データベースによっては、カラムを通して検索できるものもあるかもしれませんので、一概には言えませんが)、やはり、ある程度正規化する事を考えた方がいいと思います。

    各案件で、キーワードをある程度共通化して使う事が想定されるのであれば、
    案件だけのテーブル、キーワードだけのテーブル、案件とキーワードをつなぐテーブル、の三つに分けるのが一番良さそうです。

    案件とキーワードをつなぐテーブルは、単なるペアにする方法もありますが、キーワードがある程度固まるのであれば、キーワードの組み合わせのテーブルにする方法もあります:前者は、案件がひとつ増えると、設定されたキーワード分だけロウ数が増えるのに対し、後者はキーワードの実際に使用されている組み合わせ以上にロウ数が増えません。その代わり、管理は前者の方が後者より圧倒的に楽です。

    あとは、分かりやすいという点で、文字列を使う方法がいいかもしれません(ただ、これもデータベースによっては不定長の文字列をカラムに設定できないものもありますので、必ずできるとは言えませんが)。
    今なら、データベース内を全文検索することもできるようですから、文字列を使うのも案外いいのかもしれません。

    ----

    ところで、上の方のコメントを実はまだ読んでいなかったのですが、

    案件というのはバイナリデータとかテキストデータとか、そういう大きなまとまった量のデータなのですか?
    ならば、さらにもうひとつ、案件を格納するデータはIDをつけるだけにして、それとは別に案件検索用のテーブルを作った方がいいような気がします。そうすれば、検索用テーブルは多少カラム数を多くしても、クライアント側でSQLを動的に生成すれば、カラム数もあまり問題にはならないですね。
  • id:kuro-yo
    > 検索用テーブルは多少カラム数を多くしても、クライアント側でSQLを動的に生成すれば、カラム数もあまり問題にはならない

    と書きましたが、やっぱり問題ありますね。
  • id:Kenju
    kuro-yo 様

    ありがとうございます。

    認識の相違についてはその通りで、
    ・不特定のキーワードを1案件に最大20個設定し、さらに検索時にも20個設定できる。
    ・キーワードは不特定なので文字列で格納するしかない。
    ・順番に指定もないので検索時は設定された全てのキーワードを検索する。

    ということでした。最初にまとめておくべきでしたね。

    使ってみればある程度のまとまりはできるかもしれませんが、これは想定できません。
    3つにする方法はスキル不足で想定ができないのですが管理も複雑になりそうですしうまくいかなそうです。

    というあたりです。上のほうはちょっとわかりませんでした。
    たぶん、前認識の状態のレスではないかと推測します。
    特定のキーワードが20個あるという意味なら仰るとおり順序に意味がでてきます。
    その場合なら案件テーブルにはキーワードのIDだけ格納しておけばいいのでもう少し楽でいいですよね。
  • id:kuro-yo
    話をしていてちょっと気になったのですが、何もかもデータベースにやらせようとしていませんか?
    まさか、クライアントがコンピューターではない、という事はないですよね?
  • id:Kenju
    クライアントはコンピュータですよ。
  • id:kn1967
    ポイント頂戴しました。
    たいしたお役にも立てませんでしたが、ありがとうございます。

    1カラムに収める件は私も考えたのですが、同様に駄目だと判断してました。
    改行コードを入れるというのは盲点というか、昔、自分もやったことありました。
    年々記憶力の衰えを感じる今日この頃です・・・。

    長くなりそうなので、ひとまず、お礼まで。
  • id:kuro-yo
    > クライアントはコンピュータですよ。

    いえ、そういう事ではなく、
    クエリのたびに人間がSQLをじか打ちしたり、
    あるいはそれとほとんど変わらないフロントエンドしか用意してない
    (例えば、検索フォームに入った文字列をそのままストアドプロシージャのパラメータに渡すだけ、というような)、
    というわけではないですよね?という意味です。

    …ただ、昨日書いてしまってから思ったのですが、
    データベースの設計だけ受注していて(あるいはプロジェクト内で依頼されてて)、
    しかも、クエリのインターフェースも発注者側(依頼者側)で指定されていて、
    そのうえ、クエリの仕方について仕様が固定されていて、変更の提案ができない、
    という事情がもしもあるのだとすると、
    処理の多くをデータベースに依存せざるを得ないのも仕方ないかもしれません…。
    私はてっきり、クライアントも含めての設計だと思っていましたので、そのつもりで答えてました。
  • id:Kenju
    > あるいはそれとほとんど変わらないフロントエンドしか用意してない
    >(例えば、検索フォームに入った文字列をそのままストアドプロシージャのパラメータに渡すだけ、というような)

    想定はこれです。
    ただ、webシステムの設計なので変えられないわけではありません。
    あまりいい方法を思いつかないだけです。

    クライアント -> webアプリケーション -> データベースサーバ
    という流れなので、webアプリ側でSQL文は適当に書き直せます。
    ただ、保守性から複雑なSQLを構築したくなかったり、うまい方法を思いつかなかったりで固定的なSQL文を想定する結果になっています。
  • id:kuro-yo
    まだちゃんとテーブルの構成について書いてませんでした。
    とりあえず、テーブルは3つとして説明します。テーブル名、カラム名は適当です。
    案件そのものを格納するカラムは、説明に全く不要なので書きません。
    SQLはすっかり忘れてうろ覚えなので書きません。

    ・案件テーブル…案件と検索IDを関連づける。案件IDはユニーク※。
    案件ID, 検索ID

    ・検索テーブル…キーワードIDの集合を検索IDに関連づける。異なる集合には異なる検索IDをつける事。
    検索ID, キーワードID

    ・キーワードテーブル…キーワードとキーワードIDを対応づける。キーワードIDはユニーク※。
    キーワードID, キーワード

    (※テーブル側で自動でつけさせます。なお行を削除してもIDは再利用しません。)

    検索の際は、まずキーワードテーブルから、検索キーワードに対応づけられたキーワードIDを取得します。
    文字列比較を行うのはこの時だけです。
    次に検索テーブルで、そのキーワードIDを含む検索IDを全て取得します(OR条件)。
    最後に、この検索IDを持った案件IDを全てひろえばいいわけです。

    AND条件にする場合には、検索テーブルのクエリで、
    同一検索IDの行数と取得したキーワードIDの個数とを一致させます。

    管理は少し面倒です:案件へのキーワードの登録は、
    まず一致するキーワードをキーワードテーブルで検索し、ないものは新たに追加します。
    次に、検索テーブルでその全てのキーワードIDが登録されていて、
    なおかつ同一検索IDの行数が登録するキーワードIDの個数に一致するものを検索します。
    なければ新しい検索IDを決めて、その検索IDで、案件に登録する全てのキーワードIDを検索テーブルに追加します。
    (つまり、キーワードIDの集合と検索IDが一対一に対応します)
    最後に検索IDを該当案件に設定します。

    検索IDの設定がやっかいですが、それは解決方法がいくらでもあると思います。

    一見オーバーヘッドが大きいように思えるかもしれませんが、クエリの組み立て方次第です。
    あいにく具体例を示す時間はありませんが、私が以前行った仕事(MSSQLServer2000だったと思います)では、
    サブクエリの順番を変えただけで、クエリの時間を10倍以上速くした事もあります。
    要は、joinするテーブルがサイズが小さくければ速くなります。
    上に書いた手順はまさにそうなっていると思いますので、この手順になるようにクエリを組み立てます。

    いくらかでもお役にたてば何よりです。
  • id:Kenju
    kuro-yo 様

    ありがとうございます。
    ややこしそうですが検討してみます。
  • id:Kenju
    残念なことにキーワードの部分検索を行うように要求されました。

    likeでやらざるを得ないのか。どうしようかな。
  • id:kuro-yo
    likeを使うのは構わないのではないですか?
    例えば私の例では、キーワードテーブルでlikeで文字列比較してキーワードIDを取得すれば良いのです。

この質問への反応(ブックマークコメント)

「あの人に答えてほしい」「この質問はあの人が答えられそう」というときに、回答リクエストを送ってみてましょう。

これ以上回答リクエストを送信することはできません。制限について

絞り込み :
はてなココの「ともだち」を表示します。
回答リクエストを送信したユーザーはいません