mysqlの中身について調べてみたお話

#背景
以前、アプリのパフォーマンスチューニングを行った際、ふと「あれ、mysqlの内部構造ってどんなんやっけ？」「なんでインデックス貼ると検索が早くなるんやっけ？」と思ったのでさくっと調べてみました。
（大学で習ったようが気がしなくもないですが、まあいいでしょう。

#ストレージエンジンについて
ストレージエンジンとは、データストアに対し実際にデータの読み書きを行う部品です。mysqlでは様々なストレージエンジンを利用することができます。これらはテーブルごとに違うストレージエンジンを割り当てることが可能なため、テーブルの特徴似合わせて適切なストレージエンジンを選択することで効率よく運用していくことができます。

以前のデフォルトストレージエンジンはMyISAMでしたが、MySQL5.5以降ではデフォルトでinnoDBが採用されているようです。

なお、ストレージエンジンはmy.cnfから設定可能です

default_storage_engine = InnoDB

##MyISAMとinnoDBの違い

###ロックの対象
データの更新の際、MyISAMでは更新対象のテーブル全体をロックします。つまり、次に控えているクエリが同テーブルに対する更新リクエストである時、ロックが解除されるまでクエリは実行されません。一方innoDBでは、更新対象のレコードに対してロックがかかります。故に、次に控えているクエリが同テーブルに対する更新リクエストであっても、違うレコードに対する更新であれば、並列に実行することができます。

###トランザクション
トランザクションはデータ不整合が起きないように、ある処理をひとまとまりとして実行します。よくある例では銀行のATMの話ですね。10万入ってる口座に対し、同時に1万円の引き出しと預け入れを行った時、引き出しの処理中に預け入れの処理が上書きされ、1万引き出したにもかかわらず口座の中は11万になってる！！！みたいなのを防ぐやつです。

MyISAMはこのトランザクションをサポートしていません。なのでロールバックとかコミットとかそういうのは使えません。クエリが実行された時が実際のデータの読み書きのタイミングとなります。一方でinnoDBではトランザクションをサポートしています。安心感ありますね。

###どちらを使えばいいのか
ここまで見た感じだとMyISAMはSELECT文の実行などに向いている感じがしますね。実際MyISAMはシンプル故、実行は高速であるそうです（現在はinnoDBも負けずに高速になってきているようですが）。

しかし、公式でもデフォルトストレージエンジンをMyISAMからinnoDBに移行したこともあり、デファクトスタンダードになってきているのはinnoDBなのでしょうか。
公式でもそのような記述が見られますね。

ストレージエンジンの使用傾向は、より拡張可能な InnoDB の方へシフトしています。したがって、MySQL 5.5 は、InnoDBをデフォルトのストレージエンジンにするための論理遷移リリースでした。

#ファイル構成
##データ格納場所
my.cnfの中にデータディレクトリのパスが記述されています。

[mysqld]
datadir=/var/lib/mysql

このディレクトリを見に行くと、データベース毎にディレクトリが作成されています。

##innoDBのファイル構成
###ibdata1
実際のテーブルに保存されるデータやインデックスなどの情報が格納されています。なおこのファイルは容量が足りなくなれば自動で容量を増やしていきます。気をつけるべきこととしては、テーブルやDBを削除しても、このファイルの容量は減らないということ。故にディスク容量を圧迫する恐れがあるので、適宜最適化していく必要があります。

mysql> show variables like 'innodb_data_file_path';
+-----------------------+------------------------+
| Variable_name         | Value                  |
+-----------------------+------------------------+
| innodb_data_file_path | ibdata1:1M:autoextend |
+-----------------------+------------------------+

###*.frm
そのテーブルのテーブル定義が格納されています。

###ib_logfile
テーブルへのデータの書き込み、トランザクションの情報を保存します。

#インデックスについて
インデックスを貼ると検索が速くなる！とよく聞きます。なぜインデックスを貼ると高速化するのでしょうか。

• インデックスを張らない場合

たとえばuser_idが5のユーザの所持しているアイテムを取得するとします。

SELECT * FROM items WHERE user_id = 5;

このクエリが実行されると、itemsテーブルの全レコードを読み込み、その中からuser_idが5と一致するレコードを取得します。一度テーブルのすべてのデータを読み込むため、検索の速度はそのテーブルの中のレコード数によって増加していきます。計算量としてはオーダーnですね。

• インデックスを貼る場合

itemsテーブルのuser_idに対しインデックスを貼ります。

CREATE TABLE items (
    id INT NOT NULL,
    user_id INT NOT NULL, 
    item ...
    UNIQUE INDEX (user_id)
);

インデックスはいわゆる索引的な働きをします。インデックスを貼った要素を元に多分木を作成し、その木構造を用いてデータ検索を行うため、全レコードをわざわざ読む必要がなくなります。インデックスの実装アルゴリズムはB-treeが用いられており、計算量はオーダーlognとなります。

B-treeは節が最大m個（m>=2）の子を持つことができる木構造で、いわゆる二分木を一般化したデータ構造です。根からすべての葉までの経路の長さが等しい、という性質を持っているため、その計算量をオーダーlognに収めることが可能です。

このあたりの詳しい話はまた次回に持ち越したいと思います。

#最後に
というわけで簡単にではありますが、mysqlについて、その使い方だけじゃなくてもう少し踏み込んだところまで調べてみました。次回はインデックスのアルゴリズム的なところをまとめてみたいと思います。

#参考
http://shindolog.hatenablog.com/entry/2015/04/01/185703
http://tech.lexues.co.jp/archives/1405
http://www.dbonline.jp/mysql/storage/index3.html
http://blog.flatlabs.net/20100430_072738/