---

I. ストレージエンジンのコンセプトの紹介

  MySQL のデータは、さまざまな異なるテクノロジーを使用してファイルに保存され、それぞれが異なるストレージメカニズム、インデックス作成技術、ロックレベルを使用し、最終的に異なる特徴と機能を提供します。これらの異なるテクノロジーと付随する機能は MySQL ではストレージ エンジンと呼ばれ、ストレージ エンジンは MySQL がファイル システムにデータを格納する方法または格納フォーマットです。

MySQL データベースがサポートするストレージエンジンを表示します。

MySQL root@localhost:(none)> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+--------+--------- ---+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+--------+--------- ---+
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES | YES |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO | NO |

| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO | NO
| NO | NO | NO | YES | MyISAM storage engine | NO | NO | NO | NO | CSV
| CSV | YES | CSV storage engine | NO | NO | NO | NO |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO | NO |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO | NO | FEDERATED
| FEDERATED | NO | Federated MySQL storage engine | <null> | <null> | <null> | <null> |
+--------------------+---------+----------------------------------------------------------------+--------------+--------+--------- ---+

9 rows in set
Time: 0.017s

その中でもよく使うのが

• マイサム
• InnoDB

簡単に説明すると

• ストレージエンジンは、実際のデータI/O操作を行うデータベース内のコンポーネントである
• ストレージエンジンはファイルシステムの上にあり、データをデータファイルに保存する前にストレージエンジンに転送し、各ストレージエンジンの保存形式に保存される

II. MyISAM ストレージエンジン

1. MyISAMの紹介

• MyISAMはトランザクションをサポートせず、外部キー制約もサポートせず、フルテキストインデックスのみをサポートし、データファイルとインデックスファイルは別々に保存される
• 高速なアクセス、トランザクションの整合性を必要としない
• MyISAMは、クエリ、インサートベースのアプリケーションシナリオに適しています。

<ブロッククオート

MyISAM は、ファイル名とテーブル名が同じで、以下の拡張子を持つ 3 つのファイルとしてディスクに保存されます。

• .frmファイルには、テーブル構造の定義が格納されている
• データファイルの拡張子は.MYD(MYData)
• インデックスファイルの拡張子は.MYI(MYIndex)

<ブロッククオート

• データ更新時にテーブル全体をロックするテーブルレベルロック形式
• 読み書きの際にデータベースが相互にブロックし合う：シリアルオペレーション、順番に操作し、読み書きのたびにテーブル全体をロックする。
• は、データ書き込み中にユーザーデータの読み込みをブロックします（読み込みまたは書き込みを同時に行うことはできません）。
• また、データの読み取り中にユーザーがデータを書き込むこともブロックします。
• 特徴 データの書き込み、読み出しを別々に行うため、処理が速く、使用するリソースも比較的少ない

2. MyISAMテーブルは3種類の保存形式をサポート

(1) 静的（固定長）テーブル

  静的テーブルは、デフォルトの保存形式である。静的テーブルのフィールドはすべて非可変であり、各レコードは固定長です。このタイプのストレージの利点は、保存が非常に速く、キャッシュが容易で、障害から回復しやすいことです。欠点は、通常、動的テーブルよりも多くのスペースを占有することです。

(2) ダイナミックテーブル

  動的テーブルは可変フィールド（varchar）を含み、レコードは固定長ではなく、このストレージの利点は、より少ないスペースで済むことですが、レコードの頻繁な更新と削除は、断片化を作成し、定期的に実行する必要があります OPTIMIZE TABLE ステートメントまたは myisamchk -r コマンドを使用すると、パフォーマンスが向上し、障害が発生した場合の復旧が比較的困難になります。

(3)テーブルの圧縮

  圧縮テーブルを構成するのは myisamchk ツールは、各レコードが個別に圧縮されるため、ごくわずかなアクセス費用しか発生せず、非常に小さなスペースを占有しています。

3. MyISAMの制作シナリオ

• トランザクションのサポートが不要な会社業務
• 一方的に大量のデータを読み書きする業務
• 同時アクセス数が比較的少ない読み書きを使用するビジネス
データの変更が比較的少ない業務 データのビジネス上の一貫性をあまり必要としないビジネス
• サーバーのハードウェアリソースが比較的貧弱

まとめると
単方向（読み書き）のタスクシナリオ、同時並行性の低いシナリオ、トランザクションの要求が低いシナリオに適しています。

III. InnoDBストレージエンジン

1. InnoDBの特徴

• トランザクションをサポートし、4つのトランザクション分離レベルを持つ
  

  MySQL's default storage engine is InnoDB from version 5.5.5 onwards, before 5.5 it was myisam (isam)
  

• トランザクション分離レベルに関する読み取りと書き込みのブロッキング

• インデックスとデータを非常に効率的にキャッシュ

• テーブルと主キーはクラスタに格納される

• パーティション、テーブルスペースのサポート（オラクルデータベースに似ている

• 外部キー制約のサポート、5.5以前はフルテキストインデックスのサポートなし、5.5以降はフルテキストインデックスのサポートあり

• 高いハードウェアリソースを必要とするアプリケーションに適しています。

• 行レベルロック、ただしテーブルのフルスキャンはテーブルレベルロック(select )となります。
  

  update table set a=1 where user like '%lic%';
  

• の実行のように、テーブルの行数がInnoDBに保存されない。 select count(*) from table; InnoDB はテーブル全体をスキャンして行数を把握する必要がありますが、MyISAM は単に保存されている行数を読み出すだけです。ここで重要なのは count(*) ステートメントに where の条件では、MyISAM もテーブル全体をスキャンする必要があります。

• 自己増殖するフィールドの場合、InnoDB はそのフィールドだけのインデックスを含む必要がありますが、MyISAM テーブルの他のフィールドと組み合わせたインデックスを作成することができます。

• MyISAMがテーブルを再構築（切り捨て）する

2. InnoDBのプロダクションシナリオ

• ビジネスニーズのあるトランザクションのサポート
• 行レベルロックは高い同時実行性には適しているが、クエリはインデックスを介して行われることを保証する必要がある
• フォーラムやマイクロブログなど、ビジネスデータの更新頻度が高いシナリオ。
• 銀行業務など、ビジネスデータの一貫性要求が高い場合
• ハードウェア・デバイスのメモリが大きい場合、InnoDBの優れたキャッシュ機能を使用してメモリ使用率を向上させ、ディスクIOの圧力を低減させる

3. デッドロック

• MyISAM: テーブルレベルロック
• InnoDB: 行レベルロック

2つのリクエストがそれぞれ2行をアクセス/読み取り、同時に互いの行データを読み取る必要がある場合、（行ロック制限）のためにブロッキングが発生します。

MySQLのデッドロックと解決策
mysqlのデッドロック 解決方法

IV. に基づくストレージエンジンの企業選択

• MyISAM (テーブルレベルロック): 低オーバーヘッド、高速ロック、デッドロックなし、ロック粒度が大きい、ロック競合の確率が高い、並行処理能力が最も低い
• InnoDB（行レベルロック）：オーバーヘッドが大きく、ロックが遅い、デッドロックが発生する、ロック粒度が最も小さく、ロック競合の確率が最も低く、並行性が最も高い。

<ブロッククオート

ストレージエンジンを選択するためには、次のようなことを考慮する必要があります。

• <フォント 対応するフィールドとデータ型

  • すべてのエンジンでサポートされている共通のデータ型
  • しかし、すべてのエンジンがバイナリオブジェクトのような他のフィールドタイプをサポートしているわけではない

• ロックタイプ

  • テーブルロック。MyISAMのサポート
  • 行ロック。InnoDBサポート

• <フォント インデックス対応

  • インデックスを構築することで、データベース内のデータを検索・復元する際のパフォーマンスを大幅に向上させることができる
  • ストレージエンジンによって、インデックスを作成するための技術は異なる
  • インデックス作成に全く対応していないストレージエンジンもある

• トランザクションのサポート

  • テーブルの更新や挿入の際の信頼性向上
  • トランザクションをサポートしたいかどうかでストレージエンジンを選択する

  まとめると、同時並行性の高いビジネスシナリオではInnoDBを使用し、個別の書き込み、挿入、読み取り操作が多い場合は、MyISAMを使用することが推奨されます。

V. ストレージエンジンの表示と変更

1. システムがサポートするストレージエンジンを表示する

show engines;

例

MySQL root@localhost:(none)> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+--------+--------- ---+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+--------+--------- ---+
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES | YES |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO | NO | MEMORY | YES | Hash

| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO | NO
| NO | NO | NO | YES | MyISAM storage engine | NO | NO | NO | NO | CSV
| CSV | YES | CSV storage engine | NO | NO | NO | NO
| ARCHIVE | YES | Archive storage engine | NO | NO | NO | NO |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO | NO | FEDERATED
| FEDERATED | NO | Federated MySQL storage engine | <null> | <null> | <null> | <null> |
+--------------------+---------+----------------------------------------------------------------+--------------+--------+--------- ---+

9 rows in set
Time: 0.013s

2. テーブルが使用するストレージエンジンを表示する

方法1

show table status from library name where name='table name'\G;

例

MySQL root@localhost:(none)> show table status from mysql where name='user'\G;
***************************[ 1. row ]***************************
Name | user
Engine | MyISAM
Version | 10
Row_format | Dynamic
Rows | 2
Avg_row_length | 132
Data_length | 408
Max_data_length | 281474976710655
Index_length | 4096
Data_free | 144
Auto_increment | <null>
Create_time | 2021-10-21 14:07:58
Update_time | 2021-10-23 09:38:46
Check_time | <null>
Collation | utf8_bin
Checksum | <null>
Create_options | 
Comment | Users and global privileges

1 row in set
Time: 0.003s

方法2

use library name;
show create table Table name;

例

MySQL root@localhost:(none)> use info;
You are now connected to database "info" as user "root"
Time: 0.001s
MySQL root@localhost:info> show tables;
+----------------+
| Tables_in_info |
+----------------+
| member |
| test | test3
| test3 |
+----------------+
3 rows in set
Time: 0.007s
MySQL root@localhost:info> show create table test;
+-------+------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------+
| Table | Create Table |
+-------+------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------+
| test | CREATE TABLE "test" (\n "id" int(4) NOT NULL,\n "name" varchar(10) NOT NULL,\n "cardid" varchar( 18) NOT NULL,\n KEY "id_index" ("id")\n) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------+

1 row in set
Time: 0.008s
MySQL root@localhost:info> show create table test\G;
***************************[ 1. row ]***************************
Table | test
Create Table | CREATE TABLE "test" (
  "id" int(4) NOT NULL,
  "name" varchar(10) NOT NULL,
  "cardid" varchar(18) NOT NULL,
  KEY "id_index" ("id")
) ENGINE=InnoDB DEFAULT CHARSET=utf8

1 row in set
Time: 0.001s

use library name;
alter table table name engine=MyISAM;

3. ストレージエンジンの変更

(1) テーブルの変更による修正

MySQL root@localhost:info> use info;
You are now connected to database "info" as user "root"
Time: 0.001s
MySQL root@localhost:info> alter table test engine=myisam;
You're about to run a destructive command.
Do you want to proceed? (y/n): y
Your call!
Query OK, 0 rows affected
Time: 0.013s
MySQL root@localhost:info> show create table test\G;
***************************[ 1. row ]***************************
Table | test
Create Table | CREATE TABLE "test" (
  "id" int(4) NOT NULL,
  "name" varchar(10) NOT NULL,
  "cardid" varchar(18) NOT NULL,
  KEY "id_index" ("id")
) ENGINE=MyISAM DEFAULT CHARSET=utf8

1 row in set
Time: 0.001s

vim /etc/my.cnf
# Add the following module
[mysqld]
default-storage-engine=INNODB

# Restart the mysql service
systemctl restart mysqld.service

Note: This method only works for new tables created after modifying the configuration file and restarting the mysql service; existing tables are not changed.
use library name;
create table table name(field1 data type, ...) engine=MyISAM;

例

MySQL root@localhost:(none)> use info;
You are now connected to database "info" as user "root"
Time: 0.001s
MySQL root@localhost:info> create table hello(name varchar(10),age char(4)) engine=myisam;
Query OK, 0 rows affected
Time: 0.004s
MySQL root@localhost:info> show create table hello\G;
***************************[ 1. row ]***************************
Table | hello
Create Table | CREATE TABLE "hello" (
  "name" varchar(10) DEFAULT NULL,
  "age" char(4) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8

1 row in set
Time: 0.001s

(2) 設定ファイル /etc/my.cnf を修正する。

vim /etc/my.cnf
# Add the following module
[mysqld]
default-storage-engine=INNODB

# Restart the mysql service
systemctl restart mysqld.service

Note: This method only works for new tables created after modifying the configuration file and restarting the mysql service; existing tables are not changed.

(3) テーブル作成時のストレージエンジンの指定

use library name;
create table table name(field1 data type, ...) engine=MyISAM;

例

MySQL root@localhost:(none)> use info;
You are now connected to database "info" as user "root"
Time: 0.001s
MySQL root@localhost:info> create table hello(name varchar(10),age char(4)) engine=myisam;
Query OK, 0 rows affected
Time: 0.004s
MySQL root@localhost:info> show create table hello\G;
***************************[ 1. row ]***************************
Table | hello
Create Table | CREATE TABLE "hello" (
  "name" varchar(10) DEFAULT NULL,
  "age" char(4) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8

1 row in set
Time: 0.001s