属性？について

質問

こんにちは。MySQLを触りはじめ、まだ日が浅いのですが、書籍を手にしながらローカル環境で学んでおります。

そこで今回お教えいただきたいのは、最初に手にした書籍には、
データを保存する際、重複しないように連番をつける！という説明で、
auto_increment を指定するよう書かれていました。

なるほど・・と思いつつ、以降、テーブルを新規作成する際は、必ず先の設定を行っているのですが、後々購入した書籍には、インデックスとする、とか、プライマリーキー設定を、などと、始めに購入した書籍では触れていなかった設定がでている状況となっております。

これまで、先にお伝えしたように、auto_increment属性としたidを元に、各データを結びつけながらデータを呼び出しているのですが、少なくとも今のところ、ローカル環境においては何ら問題が出ていない状況です。

しかしながら、インデックスやプライマリーキーという言葉が頭から払拭できず、諸先輩方々からお教えいただけたらと思い、恥を忍んで投函させていただきます。宜しくお願い致します。

回答No.4

chukenkenkou · ベストアンサー

#2回答者です。

MySQLのDDL（定義系SQL）の話なのか、phpMyAdminの設定の話なのかが紛らわしいのですが。。。
phpMyAdminでどういう指定をすれば、どういうDDLが生成されるか把握していますか？

phpMyAdminはバージョンにより違いが多いと聞いていますが、バージョン2.7では以下のようになっています。

「主要」・・・「create table」で「primary key」を生成
「インデックス」・・・「create table」で「index」を生成
「一意性」・・・「create table」で「unique」を生成

「create table」での「unique」指定は、「create unique index」と同等です。
「create table」での「index」指定は、「create index」と同等であり、重複を許します。

一意性をRDBMS側で保証してもらうには、「primary key」か「unique」にする必要があります。
「primary key」と「unique」の違いには、以下のような事項があります。
(1)前者はナル値の格納不可、後者は格納可（ナル値の場合は、重複チェックされない）。
(2)前者は表に1個しか定義できない、後者は複数定義可。

>memberテーブルのユニークはidを設定し、accessは、noを設定する

「primary key」または「unique」にすることで、一意性が保証されます。

>phpMyAdminにて、作成済みのテーブルを触ってみたのですが、
>Indexに指定したカラムはユニークにするべきではないのですか？

一意性を保ちたいなら、「primary key」または「unique」にする必要があります。
重複を許し、検索性能を向上させたいなら、「index」指定になります。
例えばaccess表のid列に重複可のインデクスが無ければ、member表と結合時にaccess表は全行検索になってしまいます。
また、一方で無闇にインデクスをたくさん定義すると、insert、update、delete時にオーバヘッドになります。この辺は母体件数や検索、更新の頻度などで、調整する必要があります。

お礼

chukenkenkou様

こんばんは。細かな説明を有難う御座います。
投函時にも書きましたが、手持ちの書籍にはauto_incrementしか説明がなく、後に購入した書籍には多少の説明があったものの、これまで、最初の書籍を参考に、コマンドからテーブルを作成していたもので、phpMyAdminでの操作に戸惑いを覚えての質問でした。

いずれにせよ、インデックス、プライマリ、ユニークの関係が理解に大分前進出来ました。今後、この関係を考えながら作成に取り掛かりたいと思います。

補足

chukenkenkou様

おはよう御座います。解決済みのつもりが そのままになっていたようです。失礼いたしました。
今後、また何かありました際には宜しくお願い致します。この度は有難う御座いました。

回答No.3

chukenkenkou

#2回答者です。

少し追記します。

実際に表を設計する場合、プライマリキーが通番だけで表せるものは、そう多くはありません。
例えば、#2の例の商品コードなら、「商品分類コード＋通番」、社員コードなら、「入社年度＋通番」など、意味のあるコードが付加されて構成されます。

書籍などの例では、説明を簡単にするために、auto_incrementを多用しているだけであって、実世界での表設計とはかなり違います。

回答No.2

chukenkenkou

表を設計する場合、表の中の行を一意に識別するためのキーを持たせるのが一般的です。
例えば、商品を管理するなら商品コード、社員を管理するなら社員コードといった感じです。
社員の家族を管理する表を考えた場合、一人の社員に複数の家族が存在し得えます。こういった場合、「社員コード＋家族内通番」といったキーで、家族一人一人を一意に識別できるようにします。

行を一意に識別できるキーを、プライマリキーといい、通常、一つの表には1個だけ存在するように設計します。
殆どのRDBMSでは、プライマリキーを実装する上で、内部的にインデクスを定義しています。これは、データ格納時に重複有無のチェックをする上で、インデクスがなければ、全行検索しなければならないからです。

プライマリキーは、参照制約を定義する場合に、外部キーと対応するキーでもあります。しかし、参照制約を定義しない場合やプライマリキーではないが、一意性を保ちたい別の列が存在する場合もあります。
このため、多くのRDBMSでは、「create unique index」といった一意性を保証するインデクスを実装可能にしています。

表を条件を指定して検索する場合、インデクスがなければ全行検索になってしまいます。全行検索になると、母体件数に比例して性能が劣化してしまいます。そのため、多くのRDBMSでは、「create index」でインデクスを実装可能にしています。

表を設計する上で、一意な通番を持ちたい場合があります。注文表の注文番号などがその例です。これをSQLでやろうとすると、
(1)select max(注文番号) from 注文表」で最新の番号を得る
(2)(1)の番号に+1したものを最新の注文番号として格納する
といった操作になります。
複数ユーザが同時にアクセスする場合は、(1)と(2)の間に、注文番号の最大値が変わってしまう場合があります。そのため、こういったケースに対応する仕組みを、システム上で持たせる必要があります。

このような場合に、システム構築を容易にするのが、シーケンスという機能で、多くのRDBMSで実装されています。
MySQLでは、シーケンスに相当するものとして、auto_incrementという形で実装されています。
MySQLのauto_incrementでは、通番の一意性の保証及び最大値を得るという内部的な操作を実装する上での前提として、primary keyにする必要があります。一意性の保証や最大値を得る処理を、インデクスなしで行なおうとすれば、前述のように全行検索になってしまうからです。

なお、シーケンスを使う場合、多くのRDBMSでは、ロールバックが発生した場合等に欠番ができてしまうようになっています。そのため、欠番が許されないようなシステムでは、別の仕組みを作る必要があります。

お礼

chukenkenkou様

はじめまして、こんにちは。ご丁寧なご説明に感謝いたします。
何度も読み直してみましたが、例を出して改めてご指摘いただけますでしょうか？
例えば、

table:member
Field　|　Type　|　Null　|　Key　|　Default　|　Extra
------------------------------------------------------
id　　｜int(11)|　　　　　|　Nul ｜NULL　　　｜auto_increment
name　｜text　｜YES｜　　　｜NULL　　　｜
pass　｜varchar(32)|　｜　　　｜NULL　　　｜

というものと、
table:access
Field　|　Type　|　Null　|　Key　|　Default　|　Extra
------------------------------------------------------
no　　｜int(11)|　　　　　|　Nul ｜NULL　　　｜auto_increment
id　　｜int(11)|　　　　　|　　　　｜NULL　　　｜
date　｜datetime|　　　｜　　　｜NULL　　　｜

というもの２つのテーブルがあり、下のaccessというテーブルのidには、memberテーブルのidが入るような場合、
memberテーブルのユニークはidを設定し、accessは、noを設定する。という認識で宜しいでしょうか？（accessのidは状況によりその都度入る事あり）

また、今、phpMyAdminにて、作成済みのテーブルを触ってみたのですが、Indexに指定したカラムはユニークにするべきではないのですか？
質問させていただいた当初、物は試しにと、id（member）やno(access）をインデックスとして修正していたのですが、
『殆どのRDBMSでは、プライマリキーを実装する上で、内部的にインデクスを定義しています』というご説明からすると、一意性を持たせるデータはインデックス設定するより、ユニークとして設定すべき。という認識で宜しいのでしょうか？

詳しくご説明していただいたにも関わらず、例を持ち出しての質問に恐縮しておりますが、再度、ご指摘・アドバイスいただければ幸いです。

回答No.1

moon_night

auto_incrementは自動的に重複しないIDを割り振る機能ですので、インデックスやプライマリキーとは関係がありません。
プライマリキーと言うのは、その値が唯一の値とされるのでそのキーだけみれば他のレコードと区別がつくの物です、似ているのがユニーク（unique）キーです。
これは他のレコードに重複がないことを保証します。
プライマリキーと似ていますが別物です。
インデックスは、検索がしやすいように検索用の仕分けを作っておきます。
ただし、常にインデックスが使用されるとは限りません。（likeのあいまい検索などを使用すると使われないことがあります。）

auto_incrementはprimary keyとあわせて使うのが普通です。
つけなくても特に問題が起こらない場合がありますが、使い方によっては問題が起こる場合があります。

場合によって使い分けるとよいでしょう。

お礼

はじめまして、こんばんは。
ご丁寧なアドバイスをありがとう御座います。

インデックスとプライマリキー、およびユニークキー・・・
なるほど・・とは思うものの、全て同じような気がしてなりません。汗

恐れ入りますが、少し例を用いてご説明願えないでしょうか？
恐縮いたします・・・。