データベースとは? 表・SQL・インデックスのしくみ

身近なところでは

ネットショップで注文のボタンを押すと、在庫が1つ減り、注文の記録が1件増えます。銀行のアプリを開くと、いまの残高がすぐに出ます。どちらも裏では、データベースにデータを預けたり、取り出したりしています。

スマホの中にもあります。SQLite(エスキューライト)という小さなデータベースのソフトです。開発者の公式サイトによれば、AndroidにもiPhoneにも入っています。

この記事では、なぜ大量のデータから一瞬で探せるのか、なぜお金の記録を任せられるのかを見ていきます。ITパスポート試験や基本情報技術者試験の出題範囲(シラバス)にある言葉も、ふつうの言葉で説明します。

表計算のファイルでは、なぜ足りないの?

データを表にするだけなら、表計算ソフトでもできます。困るのは、使う人とデータの量が増えたときです。理由は大きく3つあります。

1つ目は、大勢が同時に書き込むことです。大きなネットショップには、1秒に何件もの注文が届きます。2人が同じファイルを別々に開いて書き換えると、後から保存したほうが前の変更を消してしまうことがあります。

2つ目は、決まりを守らせることです。「個数」の欄に文字が入る、存在しない会員の注文が入る。人が手で入力すると、こうした誤りが紛れ込みます。データベースなら、前もって決めた決まりに合わないデータを、入れる時点で断れます。

3つ目は、量と速さです。Excelの1枚のシートに入るのは、最大で1,048,576行です(マイクロソフトの仕様)。大きなサービスでは、それより桁違いに多い行を扱います。その中から目的の1行を、利用者を待たせずに探す必要があります。

こうした仕事を引き受けるのが、データベースを管理する専用のソフトです。これをDBMS(データベース管理システム)と呼びます。製品には、PostgreSQL、MySQL、SQLite、Oracle Databaseなどがあります。アプリはデータを自分でファイルに書かず、DBMSに「これを保存して」「これを探して」と頼みます。

しくみ① 表・行・列と「キー」

広く使われているのは、データを表の形で持つ方式です。これを関係データベース(リレーショナルデータベース)と呼びます。IBMの研究者エドガー・コッドが、1970年の論文でこの考え方を示しました。

表の横の並びを「行」、縦の並びを「列」と呼びます。1行が1件のデータ(たとえば会員1人)で、列はその項目(名前や住所)です。表計算ソフトと違うのは、列ごとに「整数だけ」「空欄は禁止」のような決まりをつけられることです。

行を1つに特定するために、同じ値が2つとない列を決めておきます。会員番号のような列です。これを主キーと呼びます。同じ名前の会員が2人いても、会員番号を使えば取り違えません。

注文の表の「会員番号」の列は、会員の表の主キーを指しています。このように、ほかの表の主キーを指す列を外部キーと呼びます。会員の表にない番号の注文を入れようとすると、DBMSはそれを断ります。

なぜ、注文の表に会員の名前を直接書かないのでしょうか。名前を注文のたびに書くと、名前が変わったときに何十行も直すことになります。1か所でも直し忘れると、データが食い違います。同じ事実を1か所だけに書くように表を分ける設計の手順を、正規化と呼びます。

しくみ② SQLは「何がほしいか」を書く言葉

DBMSに頼みごとを伝える言葉がSQL(エスキューエル)です。国際規格のISO/IEC 9075で定められています。製品ごとに少しずつ方言はありますが、基本の書き方は共通です。

下の3つは、上の図の表に対するSQLの例です。英語の単語に近いので、声に出して読むと意味がつかめます。表と列の名前は読みやすさのため日本語にしました(実際のシステムでは英語の名前が多く使われます)。

SELECTは「選んで取り出す」、FROMは「どの表から」、WHEREは「どんな条件の行を」という意味です。SQLで書くのは「何がほしいか」だけで、「どの順に探すか」は書きません。探し方は、DBMSが表の大きさなどを見て自分で決めます。

2つの表を会員番号でつなげば、「大阪の会員が注文した商品」のように、両方にまたがる質問にも答えられます。このとき、外部キーがつなぐ目印になります。

しくみ③ インデックス: 探す範囲を一気に絞る

1億行の注文の表から、会員番号が457の注文を探すとします。何の準備もなければ、DBMSは1行目から順に、すべての行を調べるしかありません。1行が100バイトなら、読む量は10GBです。続けて読むと毎秒7GBほど出る速いSSDでも、1秒以上かかる計算です。

そこで、探したい列の値を小さい順に並べ、その値の行がどこにあるかを書き添えた一覧を、別に作っておきます。これをインデックス(索引)と呼びます。多くのDBMSでは「CREATE INDEX」という命令で作ります(主キーの列には自動で作られます)。作っておけば、DBMSは役に立つと判断したときに自分で使い、行が変わるたびに自分で直します。

DBMSは、SSDやメモリとデータをやり取りするとき、1行ずつではなく、決まった大きさのかたまりで読み書きします。このかたまりを「ページ」と呼びます。大きさはPostgreSQLでふつう8KB、MySQLの標準の方式(InnoDB)で16KBです。

索引の一覧も、行が1億あれば巨大になります。そこで、一覧をページごとに区切り、その上に「どのページに何番までがあるか」という案内の段を重ねます。こうしてできる木の形の索引を、B木(ビーき、B-tree)と呼びます。1972年に、バイヤーとマクライトが論文で発表しました。

図のように、上の段から1ページずつ読み、どの枝に進むかを選びます。1ページには数百の案内を書けるので、1段下がるごとに範囲が数百分の1に絞られます。仮に1ページに300の案内が入るとすると、3段で300×300×300の2,700万、4段で81億の行を見分けられます。1億行の表でも、4ページほど読めば行の場所がわかります。

同じ1億行を、範囲を半分ずつ絞る二分探索で探すと、比べる回数は最大27回です。ただ、比べる場所がはじめは大きく離れているので、そのたびに別のページを読みます。同じく1ページに300件と仮定すると、読むページは20近くになります。B木は1ページの中で数百の候補をまとめて比べ、遅い記憶装置から読むページの数を減らします。

PostgreSQLやMySQL(InnoDB)がふつうに作るインデックスも、B木の仲間です。実際には、いちばん下の段どうしを横につないで範囲の検索をしやすくした、「B+木」などの改良形が使われます。

ただし、インデックスがいつも使われるとは限りません。条件に当てはまる行が表の大半なら、索引を引いて1行ずつ取りに行くより、表を頭から読むほうが速いこともあります。どちらを選ぶかは、DBMSが見積もって決めます。

しくみ④ トランザクション: 「全部やるか、何もしないか」

AさんがBさんに1万円を振り込む場面を考えます。データベースの中では、2つの書き換えが必要です。Aさんの残高から1万円を引くことと、Bさんの残高に1万円を足すことです。

もし1つ目が終わった直後に、停電でサーバが止まったらどうなるでしょう。Aさんのお金は減ったのに、Bさんには届かず、1万円が消えてしまいます。これを防ぐには、2つの書き換えを、切り離せない1組として扱う必要があります。

この1組の処理をトランザクションと呼びます。PostgreSQLなどでは、BEGINで始め、最後にCOMMITと書くと結果が確定します。途中で問題が起きたら、ROLLBACKで始める前の状態に戻します。

トランザクションが守るべき4つの性質を、英語の頭文字をとってACID特性と呼びます。1983年に、テオ・ヘルダーとアンドレアス・ロイターが論文で名づけました。それぞれの意味を、振込の例で表にまとめます。

独立性を守るため、DBMSは同じ行を書き換えようとする処理の片方を、しばらく待たせます。行に「使用中」の札をかけるようなもので、これをロックと呼びます。そのため、2つの振込が同時に同じ口座から引いても、残高の計算が狂いません。

ただ、待たせすぎると全体が遅くなります。そこで、どこまで厳しく守るかを、設定で選べるようになっています。この設定を分離レベルと呼びます。

耐久性は、書く順番の工夫で守ります。DBMSはSSDの上の表を書き換える前に、「何をどう変えるか」を別の記録(ログ)に書きます。そのログがSSDなどに書けたのを確かめてから、「完了」と返します。停電しても、再起動のときにログを読み直せば、確定した変更を元どおりにできます。

関係データベースとNoSQL

表の形に合わないデータや、とても大きな規模のサービスのために、表を使わないデータベースも作られてきました。関係データベース以外のいろいろな種類を、まとめてNoSQL(ノーエスキューエル)と呼びます。

わかりやすい例は、名前を言えば中身を返すだけの方式です。名前を「キー」、中身を「バリュー」と呼ぶので、キー・バリュー型と呼ばれます。中身に何が入っているかを、データベースは気にしません。

1件分のデータを丸ごと1枚の書類のようにしまう、ドキュメント型という種類もあります。会員1人の名前と、その人の注文の一覧を、表に分けずに1つのまとまりとして持てます。

NoSQLの製品の多くは、データを多くのサーバに分けて置き、台数を増やして規模を広げやすく作られています。このやり方をスケールアウトと呼びます。その代わり、厳密さの一部をあきらめることがあります。たとえばAmazonが2007年に論文で示したキー・バリュー型のDynamoは、止まらないことを最優先にしました。故障の起き方によっては、サーバごとの中身が一時的に食い違うことを許します。

どちらが優れているというより、使い分けです。お金や在庫のように、食い違いが許されないデータは関係データベースが得意です。大量のアクセスを多くのサーバでさばきたいときは、NoSQLが向くことがあります。

データベースは、どこに置かれているの?

データベースの中身は、最後はサーバの中のファイルです。表もインデックスもページの集まりとして、電源を切っても消えないSSDやHDDに書かれています。

ただ、SSDから1回読むのを待つ時間は約0.1ミリ秒で、メモリ(約100ナノ秒)のおよそ千倍です(「メモリとストレージの違いは?」)。そこでDBMSは、よく使うページの写しをメモリに置きます。MySQLの説明書によれば、専用のサーバでは、メモリの最大80%ほどをこの置き場に割り当てることがよくあります。B木の上の段は何度も読まれるのでメモリに残りやすく、SSDまで読みに行くのは下の段だけで済むことが多くなります。

いまは、データベースをクラウドの事業者のサービスとして借りることも増えました。その場合も、データは事業者のデータセンターにあるサーバのSSDなどに置かれています(「クラウドとは?」)。

よくある誤解

数字で見る

B木と全部を読む場合の数字は、話を簡単にした目安です。実際の段数はキーの長さやページの埋まり方で、読む時間はメモリに写しがあるかどうかで変わります。

もっと詳しく学ぶ

• 試してみる: SQL練習ツール、二分探索木・ヒープの可視化ツール、RAIDの容量計算ツール
• 違いを表で見る: RDBとNoSQLの違い、内部結合と外部結合の違い、SSDとHDDの違い
• 次の一歩の用語: E-R図、第3正規形、ビュー、2相ロック、デッドロック、SQLインジェクション
• 土台から読む: 「メモリとストレージの違いは?」、「アルゴリズムとは?」、「データセンターとは?」