データベースは、データの高速な参照・更新を目的として作られているソフトウェアです。
ですので、如何に高速に参照・更新処理を実行するかは、
データベース製品にとっての至上命題です。
そこで今回は、MySQLがどうやって高速な参照・更新処理を実現しているかについて見ていきましょう。
MySQLのメモリ・実ファイル構成を図にしてみました。
図 MySQLのコンポーネント
MySQLが使用するバッファは、大きくグローバルバッファとスレッドバッファに分かれます。
グローバルバッファは、全てのコネクション共用のもので、
スレッドバッファは、各セッションごとに割り当てられるものです。
グローバルバッファには、さらに各ストレージエンジン専用のものがあり、
innodb buffer pool、innodb log buffer、innodb additional mem poolはInnoDB専用、
key bufferはMyISAM専用です。
その他、クエリキャッシュなどもあります。
スレッドバッファはセッションの数だけ割り当てられますので、
うかつに大きすぎる値にしないよう注意してください。
それぞれどんなものなのか、見ていきましょう。
innodb buffer pool
種別：グローバルバッファ
現在のMySQLで一番多くの容量をとり、かつ一番重要なのはこのinnodb buffer poolです。
InnoDBテーブルのデータ、インデックスなどをキャッシュしておくためのスペースで、
クエリが来た時、このバッファにデータが乗っていれば、ディスクアクセスすることなく
結果を返すことができます。
チューニングする際は、他のメモリが使う分を除いた全てのメモリをここにつぎ込む、
くらいのつもりでよいと思います。
目安としては、（DB専用サーバであれば）サーバ搭載のメモリの80%程度、
と言われています。
ただし、実際にはinnodb_buffer_pool_sizeで指定したよりも10%ほど多くメモリを消費するので、
割り当て過ぎるとOSが使うメモリを圧迫してしまうので注意が必要です。
関連コンポーネント：ibdataファイル、.ibdファイル
関連サーバ変数：innodb_buffer_pool_size
innodb log buffer
種別：グローバルバッファ
innodb log bufferは、ib_logfile（※後述）書き込みのためのバッファです。
トランザクションが未コミットの状態では、MySQLはib_logfileに書くべき内容を
このバッファ上に持っています。
コミットされた時点で内容は実ファイルであるib_logfileにフラッシュされますので、
それほど多くの容量は必要としません。
5.7のデフォルトは16MBとなっており、特に変更する必要はないでしょう。
（5.6以前ではデフォルトが8MBですので、もう少し大きくしても良いかもしれません）
関連コンポーネント：ib_logfile
関連サーバ変数：innodb_log_buffer_size
innodb additional mem pool
種別：グローバルバッファ
InnoDBテーブルの内部データ構造を格納する際の領域です。
5.6のGA（正式リリース版）では非推奨となっており、5.7では削除されているパラメータですので、
今後このパラメータを意識しなければならない可能性は低いでしょう。
関連コンポーネント：なし
関連サーバ変数：innodb_additional_mem_pool_size
key buffer
種別：グローバルバッファ
MyISAMテーブルのインデックスをキャッシュしておく領域のサイズです。
イメージはinnodb buffer poolのMyISAM版ですが、
MyISAMはインデックスのキャッシュは自前でしますが、データのキャッシュはOSのキャッシュを利用するため、
MyISAMを使っているシステム（今では少ないかもしれませんが）であっても、
あまりkey bufferに領域を割り当てすぎるとOSのキャッシュが少なくなり、
ページングが多発する結果となります。
マシンの使うメモリの25%ほどが目安とのことです（マニュアルより）。
関連コンポーネント：.MYDファイル、.MYIファイル
関連サーバ変数：key_buffer_size
tmp table
種別：グローバルバッファ
MySQLに限らず、多くのデータベース製品はクエリ実行の際、一時テーブルを作成します。
一時テーブルはメモリ上に作成されるため、メモリ上に一定の領域を確保しておく必要があります。
このサイズを超えると、一時テーブルはディスク上に作られ始めますので、
大幅なスループット低下につながります。
一時テーブルを大量に使うような複雑なクエリを使うことが想定されるシステムでは
デフォルトよりも大きくすることを検討しても良いでしょう。
なお、tmp tableの最大サイズはtmp_table_sizeで指定できますが、
実際に作成されるtmp tableの最大サイズは、max_heap_table_sizeとの最小値になるので注意が必要です。
関連コンポーネント：tmp disk table
関連サーバ変数：tmp_table_size、max_heap_table_size
query cache
種別：グローバルバッファ
こちらの記事で書きましたが、MySQLは全く同じクエリを投げられた場合を想定して、
SQLと結果のセットをキャッシュしておき、同じクエリが来たときにこのキャッシュから結果を返す仕組みがあります。
このキャッシュをしておくのがクエリキャッシュです。
クエリキャッシュは更新系のクエリには効果がなく、
むしろクエリキャッシュへのデータの出入りがある分、オーバーヘッドとなります。
更新が多いシステムでは、そもそもクエリキャッシュ機能を使用しない方が良いかもしれません（デフォルトではOFFになっています）。
使用する場合、have_query_cacheがYESであることを確認し、
query_cache_typeを1（SQL_NO_CACHEで始まるクエリ以外をキャッシュ）か2（SQL_CACHEで始まるクエリのみをキャッシュ）にセットし、
query_cache_sizeを指定します。
関連コンポーネント：なし
関連サーバ変数：have_query_cache、query_cache_type、query_cache_size
sort buffer
種別：スレッドバッファ
クエリ実行中にソート処理を行うためのバッファです。
通常はデフォルトのままで問題ないでしょう。
DWH系の処理など、大量のデータをソートする場合などにセッションごとにチューニングすると良いかもしれません。
関連コンポーネント：なし
関連サーバ変数：sort_buffer_size
join buffer
種別：スレッドバッファ
インデックスを使用しない、テーブルフルスキャンを伴う結合を行う際に使用されるバッファです。
通常はデフォルトのままで問題ないでしょう。
join bufferを大きくする必要があるシステムがあるのであれば、
まずはそのキーに対してインデックスを付けることを検討するべきでしょう。
関連コンポーネント：なし
関連サーバ変数：join_buffer_size
read buffer
種別：スレッドバッファ
MyISAMテーブルのテーブルフルスキャンを行う際に読んだデータをキャッシュしておくためのバッファです。
通常はデフォルトのままで問題ないでしょう。
あまり大量のread bufferを必要とするのであれば、
クエリを改修してテーブルフルスキャンを無くすことを検討すべきでしょう。
関連コンポーネント：なし
関連サーバ変数：read_buffer_size
read rnd buffer
種別：スレッドバッファ
MyISAMテーブルをソートした後のデータを読み取る際に使用されるバッファです。
ソートしたデータを順に読み取っていくケースでのディスクシークを避けるためにあります。
通常はデフォルトのままで問題ないでしょう。
MyISAMテーブルを使用しているのであれば、
ソートを伴う処理速度の向上のため大きくする選択もありますが、
そもそもMyISAMテーブルを利用しているシステムはあまりないと思われます。
関連コンポーネント：なし
関連サーバ変数：read_rnd_buffer_size
binlog cache
種別：スレッドバッファ
バイナリログの書き込みを高速化するためのバッファです。
トランザクションがコミットされた時またはバッファがあふれた時にバイナリログにフラッシュします。
多くの場合、特段チューニングする必要はないでしょう。
関連コンポーネント：バイナリログ
関連サーバ変数：binlog_cache_size
ibdataファイル
種別：実ファイル
InnoDBテーブルのメタ情報が格納され、
さらにinnodb_file_per_tableがOFFの際には、
InnoDBテーブルの全データ・インデックスが格納されている重要なファイルです。
（ONの時にはデータ・インデックスはテーブル名.ibdファイルに格納されます）
MySQLがインメモリデータベースであるといっても、
実データを持つファイルはACID特性のD（Durability）担保のために必要ですから。
複数のファイルに分割することや、自動拡張することが可能で、
innodb_data_file_pathで指定することができます。
デフォルトではibdata1という名前になっており、自動拡張設定がされています。
注意点として、innodb_file_per_tableがONの時であっても（5.6以降、デフォルトでONです）、
InnoDBテーブルのメタ情報を持っているため削除は厳禁です。
関連コンポーネント：innodb_buffer_pool、.ibdファイル
関連サーバ変数：innodb_data_file_path、innodb_file_per_table
.ibdファイル
種別：実ファイル
innodb_file_per_tableがONの時（5.6以降デフォルトでON）、
ibdataファイルに格納されるデータのうち、データとインデックスなどはこの.ibdファイルに格納されています。
変数名をみて分かる通り、テーブルごとに.ibdファイルが作成され、
「テーブル名.ibd」という名前でOS上にファイルが作成されます。
関連コンポーネント：innodb buffer pool、ibdataファイル
関連サーバ変数：innodb_file_per_table
.MYDファイル＆.MYIファイル
種別：実ファイル
MyISAMテーブルのデータ・インデックスが格納されるファイルです（それぞれ、.MYDファイル、.MYIファイルです）。
データとインデックスでファイルが分割されている点以外は、
.ibdファイルのMyISAM版、と考えて良いでしょう。
.ibdファイル同様、
「テーブル名.MYD」、「テーブル名.MYI」
という名前でOS上にファイルが作成されます。
関連コンポーネント：key buffer
関連サーバ変数：なし
ib logfile
種別：実ファイル
ib logfileは、一言でいうならWAL(Write Ahead Log)です。
MySQLでは、コミットされたデータを実データファイル（テーブルスペース）に反映するのではなく、
一旦ログにどんなデータをコミットしたかを書いておきます。
このログがWAL、というわけです。
「それがどうしてパフォーマンス向上につながるのか？」
「直接、実データファイルを更新すれば良いではないか」
と考える方もいるかと思います。
しかし、実データファイルの更新というのはディスクへのランダムアクセスであり、
インデックスの更新なども必要となる、非常に高コストな作業です。
当然、高コストだからといってやらないわけにはいかないので、いずれは実行するのですが、
できれば今すぐではなく負荷が低い時にやりたい、、、ということで、
実行すべき作業はログに書いておき、innodb buffer pool上のデータのみ更新する、
という仕様になっています。
ログへの書き込みはシーケンシャルアクセスですので、
ランダムアクセスである実データファイルの更新よりも負荷が少なくて済む、というわけです。
ib logfileは、innodb_log_file_sizeでサイズが指定でき、
innodb_log_files_in_groupでその数が指定できます（ローテーションで使用されます）。
適正なサイズというのはシステムによって異なります。
小さすぎると頻繁に実データへの反映が行われてしまいパフォーマンスが犠牲になりますし、
大きすぎるとクラッシュリカバリ時に時間がかかってしまいます。
（クラッシュリカバリ時にib logfileからibdataファイルへのデータ反映が行われるため）
クラッシュ時のリカバリには多少時間がかかってもよい！
という環境であれば、思い切って1GB程度のサイズにしてしまって良いでしょう。
クラッシュ時のリカバリスピードも気にする！という場合は下記ブログが参考になりますので、
是非一読してみて下さい。
漢のコンピュータ道：InnoDBのログとテーブルスペースの関係
ちなみに、このログのフラッシュはMySQLシャットダウン時にもデフォルトでは行われないので、
シャットダウン時であってもうかつにib logfileを削除してはいけません。
「ログだから消してもいいだろう」といって削除してしまうと、
取り返しのつかないことになりますので、絶対に削除しないで下さい。
関連コンポーネント：innodb log buffer
関連サーバ変数：innodb_log_file_size
tmp disk table
種別：実ファイル
一時テーブルがメモリからあふれた時、実ファイルとしてディスクに書くことになってしまったファイルです。
こんなファイルが作成されないよう、tmp_table_sizeとmax_heap_table_sizeを調整しましょう。
実ファイルではありますが、クエリ実行中に作成され、使用し終えたらDROPされるので、
あまり実際にOS上からファイルを見ることはないかと思います。
関連コンポーネント：tmp table
関連サーバ変数：なし
バイナリログ
種別：実ファイル
MySQLが実行したクエリを書いておくためのファイルです。
少しMySQLのことを勉強したことがある方、特にレプリケーション構成を作ったことがある方であれば、
よくご存知のことと思います。
このバイナリログはパフォーマンスに寄与するものではなく、
この記事の趣旨から逸脱するため、詳細は省きます。
バイナリログについてはググればすぐに資料が出てきますので、
そちらをご参照下さい。
関連コンポーネント：binlog cache
関連サーバ変数：log_bin
まとめ
随分と長くなってしまいましたが、
MySQLを高速化するための仕組みに関係するコンポーネントを書いてみました。
これだけ書いておいてなんですが、
パフォーマンスチューニングする観点で言えば、
innodb_buffer_pool_size、ib_log_file_sizeを調整すれば
概ね問題ない性能は出るでしょう（雑なチューニングなのは否めないですが、、、）。
この記事を読んだことで、少しでもMySQLの理解が進んだ、と言う方がいれば何よりです。
では。