属性？について

Question

こんにちは。MySQLを触りはじめ、まだ日が浅いのですが、書籍を手にしながらローカル環境で学んでおります。

そこで今回お教えいただきたいのは、最初に手にした書籍には、
データを保存する際、重複しないように連番をつける！という説明で、
auto_increment を指定するよう書かれていました。

なるほど・・と思いつつ、以降、テーブルを新規作成する際は、必ず先の設定を行っているのですが、後々購入した書籍には、インデックスとする、とか、プライマリーキー設定を、などと、始めに購入した書籍では触れていなかった設定がでている状況となっております。

これまで、先にお伝えしたように、auto_increment属性としたidを元に、各データを結びつけながらデータを呼び出しているのですが、少なくとも今のところ、ローカル環境においては何ら問題が出ていない状況です。

しかしながら、インデックスやプライマリーキーという言葉が頭から払拭できず、諸先輩方々からお教えいただけたらと思い、恥を忍んで投函させていただきます。宜しくお願い致します。

chukenkenkou · Accepted Answer

#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 · Answer

#2回答者です。

少し追記します。

実際に表を設計する場合、プライマリキーが通番だけで表せるものは、そう多くはありません。
例えば、#2の例の商品コードなら、「商品分類コード＋通番」、社員コードなら、「入社年度＋通番」など、意味のあるコードが付加されて構成されます。

書籍などの例では、説明を簡単にするために、auto_incrementを多用しているだけであって、実世界での表設計とはかなり違います。

chukenkenkou · Answer

表を設計する場合、表の中の行を一意に識別するためのキーを持たせるのが一般的です。
例えば、商品を管理するなら商品コード、社員を管理するなら社員コードといった感じです。
社員の家族を管理する表を考えた場合、一人の社員に複数の家族が存在し得えます。こういった場合、「社員コード＋家族内通番」といったキーで、家族一人一人を一意に識別できるようにします。

行を一意に識別できるキーを、プライマリキーといい、通常、一つの表には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では、ロールバックが発生した場合等に欠番ができてしまうようになっています。そのため、欠番が許されないようなシステムでは、別の仕組みを作る必要があります。

moon_night · Answer

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

属性？について

#2回答者です。

この回答への補足

#2回答者です。

表を設計する場合、表の中の行を一意に識別するためのキーを持たせるのが一般的です。

auto_incrementは自動的に重複しないIDを割り振る機能ですので、インデックスやプライマリキーとは関係がありません。

似たような質問が見つかりました

関連するカテゴリからQ&Aを探す

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング