MySQLのアーキテクチャ
初めまして、ALTURA X エンジニアインターン生の目時と申します。
今回は、ALTURA Xのプロダクトでも使用されているデータベースについて学び、MySQLのアーキテクチャについてまとめさせていただきました。
MySQLのアーキテクチャには、大きく分けてサーバー層と、ストレージエンジン層の２つに分かれます。
サーバー層：connector、parser、optimizer、execute
ストレージエンジン層：MyISAM、InnoDB、Memory、NDB Cluster
ストレージエンジン層に関しては、それぞれ持つ特徴が違うため、用途に合わせて変えて使用をします。
*MySQLのプラガブルストレージエンジンアーキテクチャは、データベース管理システムにおける柔軟性と拡張性を高めるための設計
サーバー層
サーバー層には、connector、parser、optimizer、executeが存在します。それぞれどのような役割を持っているのが説明していきます。
connecter：クライアント接続を行う。権限認証、セキュリティチェックなども
ここで、例えば、GoやPythonなどの言語を使って、クラウドサーバー上にあるデータベースにアクセスするなどを行います。
parser：SQLクエリの構文解析
ここで、渡されるのは、SELECT,INSERT,UPDATEなどの文。そして、このクエリ文が正しいかどうかを解析します。
optimizer：SQLクエリの実行計画の作成
クエリの実行計画を最適化して、最も効率の良い方法で、クエリを実行します。
EXPLAINを使うことで、MySQLの実行計画(どのインデックスを使用するのかなど)がわかるようになります。しかし、その過程での、最適化などはブラックボックスのままです。
ここで、オプティマイザトレースを使用することで、EXPLAINでブラックボックスとなっていたことを解決できるようになります。
execute：結果を生成
optimizerで最適化されたクエリを実行し、結果を生成します。
ストレージエンジン層
MySQLには、テーブルごとにデータのストレージエンジンを選ぶことができ、
MyISAM、InnoDB、Memory,NDB Clusterなどが例として挙げられます。
ここで、ストレージエンジン層の前に、データベースを考える上で以下のような重要な概念が存在するため先に説明します。
トランザクションとは？
複数のデータベースの操作を一度に扱えるようにする。完全に実行or全て実行されないかのどちらかの状態に全体を保つこと。例として銀行の入出金について考えてみます。
例：
A,Bの手持ちの金額にそれぞれに＋10000をするとき、完全に実行される必要がある。Aだけ、Bだけでは×
ここで、トランザクションには以下の４つの概念(ACID特性)を持つことが要求されます。以下にて説明していきます。
Atomicity(原子性)：データの読み取り、書き込み、更新、削除は、一つの単位で扱われる為、何らかの障害などにより途中で、実行中止されたら、全く実行がされていない状態になる。つまり、完全に実行された(1)か、全く実行されたか(0)のような性質。
Consistency（一貫性）：テーブルに対して、予め定義されている方法でしかデータの変更を認めないこと。仮にエラーが起きてもその時は何も変更をしない。
Isolation（独立性）：複数ユーザーが同時に同じテーブルで、実行しても、各々のトランザクションは、干渉することなく独立していること。
→ロック
Durability（永続性）：システム障害が起きても、障害以前に正常に実行された変更を保持すること。
トランザクションは、BEGIN(開始)から、COMMIT(変更を反映し終了)or ROLLBACK(変更を反映せずに、終了)まで、処理をまとめて行うことができます。
BEGIN ; (START TRANSACTION ;)
SELECT
INSERT
UPDATE
COMMIT ; or ROLLBACK ;例えば、トランザクションがあることで、ネットと店舗での在庫処理を行うことができます。
ロック
同時に多数のトランザクションが発生する際に、データベースの整合性を保つためのもので、概念として２種類存在します。
共有・占有ロック
機能的なこと、行ロックなど
楽観的・悲観的ロック(排他制御)
どういう風にロックをするのかという考え方的なこと
楽観的：自分が使用している時には、他の人は使用していないということが前提とするもの。
データを更新時に、データを取得した時のバージョンと一致しているかを確認する。
悲観的：自分が使用するときに、予め使用しますと宣言しておく。
更新したい対象の読み込みをしてから、COMMITあるいは、ROLLBACKをするまで他の人が情報を更新できないようにする。
#トランザクションの開始
START TRANSACTION;
-- 対象レコードの悲観的ロック取得
SELECT * FROM table_name WHERE condition FOR UPDATE;
-- データの更新処理
UPDATE table_name SET column = value WHERE condition;
-- トランザクションのコミット
COMMIT;悲観的ロックを使用してトランザクションを行う例
例として、銀行の入出金を取り上げてみます。
まず、データベースbank_dbを作成後、テーブルで、accountsを作成し、情報を追加(INSERT)しました。
+------------+----------------+----------+
| account_id | account_holder | balance |
+------------+----------------+----------+
| 1 | John Doe | 10000.00 |
| 2 | Jane Smith | 2000.00 |
+------------+----------------+----------+#トランザクションを開始する
START TRANSACTION;
#悲観的ロックを使用する(account_id = 1をロックする)
SELECT balance FROM accounts WHERE account_id = 1;
#出金する
UPDATE SET balance = balance - 100 WHERE account_id = 1;
#悲観的ロックを使用する(account_id = 2をロックする)
SELECT balance FROM accounts WHERE account_id = 2 FOR UPDATE;
#入金処理する
UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;
#反映を確認
mysql> SELECT * FROM accounts;
+------------+----------------+---------+
| account_id | account_holder | balance |
+------------+----------------+---------+
| 1 | John Doe | 9900.00 |
| 2 | Jane Smith | 2100.00 |
+------------+----------------+---------+
2 rows in set (0.00 sec)
#ただしまだ、COMMITしていないことに注意ターミナルを新たに開き、同じデータベースを開いてアクセスしてみます。
mysql> SELECT * FROM accounts;
+------------+----------------+----------+
| account_id | account_holder | balance |
+------------+----------------+----------+
| 1 | John Doe | 10000.00 |
| 2 | Jane Smith | 2000.00 |
+------------+----------------+----------+
2 rows in set (0.00 sec)実際にまだ上のトランザクションにおいて、COMMITしていないので、結果が反映されていないことがわかります。
COMITTをした後に、新しい方から確認すると、更新されていることが確認できました。
mysql> select * from accounts;
+------------+----------------+---------+
| account_id | account_holder | balance |
+------------+----------------+---------+
| 1 | John Doe | 9900.00 |
| 2 | Jane Smith | 2100.00 |
+------------+----------------+---------+
2 rows in set (0.01 sec)
悲観的ロックでは、SELECT ... FOR UPDATE を使ってデータにロックをかける一方で、楽観的ロックは、データベースにバージョン管理用の列を追加して、トランザクション処理を管理します。
疑問：このロックの流れを自動でできないのか？
→PythonやPHPなどでデータベースと接続してトランザクションを行う際には、エラー発生時に自動でROLLBACKする設定などができるみたいです。
次に、大事な概念を説明したところで、ストレージエンジンの説明をしていきます。
ストレージエンジン
以下のようにして、利用可能なストレージエンジンを確認することができます。
mysql> SHOW engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| ndbcluster | NO | Clustered, fault-tolerant tables | NULL | NULL | NULL |
| CSV | YES | CSV storage engine | NO | NO | NO |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| ndbinfo | NO | MySQL Cluster system information storage engine | NULL | NULL | NULL |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ここから、上でも挙げた+一般に使用されることが多いストレージエンジンについて説明していきます。
MyISAM
以前のデフォルトエンジン
ロックは、テーブル単位。
テーブル単位のロックなので、作業負荷が抑えられる。
CREATE table data(
accountid INT,
account_holder VARCHAR(255),
balance INT) engine=MyISAM; ストレージエンジンは、engine=のようにして、指定をすることができます。
InnoDB
バージョン5.5以降では、デフォルトのストレージエンジン
トランザクションをサポート
ロックは、行単位。
ユーザーデータの保護機能、クラッシュリカバリ機能に優れている。
CREATE table data(
accountid INT,
account_holder VARCHAR(255),
balance INT) engine=InnoDB;行ロックの方が、ロックの解除の待ち時間が少なくて済むため、頻繁に更新などがされる商用に向いていると言えます。
エンジンを変えたい時には、
mysql > ALTER table data engine=MyISAM;とすることで変更が可能です。
その他のストレージエンジン(一部)
Memory
全てのデータは、RAMに格納。
RAM：揮発性のメモリ、指定がない限りはストレージなどに保存されないもの。
RAMに保存をするので、軽いが、MySQLサーバーが停止あるいは、再起動した時には、データが失われるという特徴があります。
NDB Cluster
Memoryエンジンと同じ機能を提供、また、Memoryでは使用できない追加機能を提供します。
トランザクションは、READ COMMITTEDを提供します。
MySQL Clusterを構築する際に使用されます。
CSV
カンマ区切り値を持つ実際のテキストファイル。
Excelなどで出力することができる。
まとめ
今回、MySQLの中でどのように動いているのかを学習しました。実際銀行などの例を挙げてみるとロックなんかの例は理解しやすいと感じました。
参考：
https://dev.mysql.com/doc/refman/8.0/ja/upgrading-from-previous-series.html
https://www.seshop.com/product/detail/19425
https://dev.mysql.com/doc/refman/8.0/ja/storage-engines.html
https://dev.mysql.com/doc/refman/8.0/ja/memory-storage-engine.html#memory-storage-engine-compared-cluster
https://dev.mysql.com/doc/refman/8.0/ja/mysql-cluster.html
https://dev.mysql.com/doc/refman/8.0/ja/mysql-cluster-ndb-innodb-engines.html