MySQLのアーキテクチャ概要

クライアントからのMySQLサーバへの接続、MySQLサーバでのSQLの解析・発行、MySQLサーバでのデータの保存は、次のようになっている。

◎クライアント
クライアント側でMySQLサーバと通信を行うインタフェースはlibmysqlclient(windowsの場合は、libmysql.dll)である。
このインタフェースを介して、MySQLサーバとクライアントは、TCP/IPもしくはソケットファイルで通信をする。

◎MySQLサーバ
MySQLサーバ側での処理は次のようになる。

①クライアントからの接続を受け付けて専用のスレッドを割り当てる。
スレッドはキャッシュ領域（接続プール）に保管され、2回目以降に新規にスレッドを生成するコストを軽減する。
②SELECT文が以前に実行したものと一字一句違わない場合は、検索処理を実際に行う代わりに、事前にキャッシュしておいた検索結果をそのまま返す（クエリキャッシュ）
③クエリキャッシュにヒットしない場合は、SQL文の構文をチェックする（構文解析処理、パース処理）
④構文解析が正常終了した場合、SQL文の実行計画を決定し、SQLを発行する。

SQLの発行は、ストレージエンジンに対して行われる。
ストレージエンジンはMySQLの大きな特徴の1つである。
ストレージエンジンには、幾つかの種類があり、テーブルごとに使用するストレージエンジンを変えることもできる。代表的な2つのストレージエンジンとしては、InnoDBとMyISAMがあげられる。
InnoDBはトランザクションをサポートしているが、MyISAMはサポートしていない。

InnoDBストレージエンジンの構成は次のイメージである。

それでは実際にコマンドを使用して、使用されるストレージエンジンの確認や、ストレージエンジンの変更などをしてみる。

1.MySQLサーバに接続する
2.テスト用にデータベースを作成する
3.テスト用のテーブルを作成する
4.使用されているストレージエンジンを確認する
5.ストレージエンジンを変更してみる

1.MySQLサーバに接続する

root@ubuntu:~# mysql -uroot -p
mysql>

2.テスト用にデータベースを作成する
今回は、sampleDbというテスト用のデータベースを作成する。

mysql> create database sampleDb;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sampleDb           |
| testdb             |
+--------------------+
5 rows in set (0.00 sec)

データディレクトリ(/var/lib/mysql)を確認すると、sampleDbに対応したフォルダが作成されている。

root@ubuntu:~# ls -l /var/lib/mysql/
total 116760
-rw-rw---- 1 mysql mysql       56 Sep 10 06:42 auto.cnf
-rw-r--r-- 1 root  root         0 Sep  2 17:40 debian-5.5.flag
-rw-rw---- 1 mysql mysql 18874368 Sep 13 05:00 ibdata1
-rw-rw---- 1 mysql mysql 50331648 Sep 13 05:00 ib_logfile0
-rw-rw---- 1 mysql mysql 50331648 Sep 10 06:42 ib_logfile1
drwx------ 2 mysql root      4096 Sep  2 17:40 mysql
-rw-rw---- 1 root  root         6 Sep  2 17:40 mysql_upgrade_info
drwx------ 2 mysql mysql     4096 Sep  2 17:40 performance_schema
drwx------ 2 mysql mysql     4096 Sep 13 05:00 sampleDb
drwx------ 2 mysql mysql     4096 Sep 13 04:08 testdb

3.テスト用のテーブルを作成する
今回は、sampleTblというテスト用のテーブルを作成する。

mysql> use sampleDb;
Database changed
mysql> create table sampleTbl(id int, name varchar(40));
Query OK, 0 rows affected (0.01 sec)

データディレクトリを確認してみる。

root@ubuntu:~# ls -l /var/lib/mysql/sampleDb/
total 112
-rw-rw---- 1 mysql mysql    65 Sep 13 04:26 db.opt
-rw-rw---- 1 mysql mysql  8586 Sep 13 05:14 sampleTbl.frm
-rw-rw---- 1 mysql mysql 98304 Sep 13 05:14 sampleTbl.ibd

各ファイルの役割は次の通りである。ただし、これはストレージエンジンがInnoDBの場合である。

.frmファイル：フィールド定義などテーブルの情報を記録するファイル
.ibdファイル：データとインデックスの両方を記録するファイル

4.使用されているストレージエンジンを確認する
InnoDBがデフォルトで使用されていることが分かる。

mysql> use information_schema;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select table_name, engine from tables where table_schema = 'sampleDb';
+------------+--------+
| table_name | engine |
+------------+--------+
| sampleTbl  | InnoDB |
+------------+--------+
1 row in set (0.00 sec)

5.ストレージエンジンを変更してみる
ストレージエンジンを変更したいテーブルが存在するデータベースへ接続してから、alter table文でストレージエンジンを変更する。
今回は試しに、MyISAMに変更してみる。

mysql> use sampleDb;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> alter table sampleTbl engine = MyISAM;
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

ストレージエンジンが変更されたことを確認する。

mysql> show create table sampleTbl\G;
*************************** 1. row ***************************
       Table: sampleTbl
Create Table: CREATE TABLE `sampleTbl` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(40) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

ERROR:
No query specified

データディレクトリを確認してみる。

root@ubuntu:~# ls -l /var/lib/mysql/sampleDb/
total 20
-rw-rw---- 1 mysql mysql   65 Sep 13 04:26 db.opt
-rw-rw---- 1 mysql mysql 8586 Sep 13 05:00 sampleTbl.frm
-rw-rw---- 1 mysql mysql    0 Sep 13 05:00 sampleTbl.MYD
-rw-rw---- 1 mysql mysql 1024 Sep 13 05:00 sampleTbl.MYI

次の3つのファイルを確認できる。これらは、ストレージエンジンがMyISAMの場合である。
InnoDBでは1つの.ibdファイルに格納されていたデータとインデックスが、MyISAM ではデータファイル(.MYDファイル）とインデックスファイル(.MYIファイル）に分けられる。

.frmファイル：フィールド定義などテーブルの情報を記録するファイル
.MYDファイル：実施のデータを記録するファイル
.MYIファイル：テーブルのインデックスを記録するファイル