『MySQL技術内幕:InnoDBストレージエンジン』(一)-MySQLアーキテクチャとストレージエンジン
MySQL:シングルプロセスマルチスレッドデータベース.
MySQLアーキテクチャのストレージ・エンジンを明確に理解するには、まずデータベースとデータベース・インスタンスの2つの概念を説明します.データベース:ファイルの集合、あるデータモデルに従って組織され、二次メモリ(ハードディスク)に格納されたデータセット データベースインスタンス:アプリケーション、ユーザーとオペレーティングシステムの間にあるデータ管理ソフトウェア、ユーザーのデータベースデータに対する操作、データベース定義、データクエリーメンテナンス、データベース実行制御など、データベースインスタンスの下で行われ、アプリケーションはデータベースインスタンスを通じてしかデータベースと付き合うことができない.
簡単に言えば、データベースは1つのファイルから構成されています(一般的にはバイナリファイルです).これらのファイルに対してSELECT、INSERT、UPDATE、DELETEなどの操作を行う場合は、簡単な操作ファイルではデータベースの内容を変更するのではなく、データベースのインスタンスでデータベースの操作を完了する必要があります.
データベース・インスタンスとデータベースは、通常、1つの対応関係ですが、クラスタの場合、1つのデータベースは複数のインスタンスで使用できます.
インスタンスを起動すると、MySQLデータベースはプロファイルを読み込みます.プロファイルがない場合は、コンパイル時のデフォルトのパラメータ設定に従ってインスタンスを起動します(Oracleではプロファイルがない場合、起動時にパラメータファイルが見つからないというプロンプトが表示されます).コマンドでMySQLインスタンスの起動時を表示できます.プロファイルを検索する場所(MySQLインストールディレクトリのbinディレクトリの下で実行):
各プロファイルは前後から順番に読み込まれ、複数のプロファイルに同じパラメータがある場合は、読み込まれた最後のプロファイルのパラメータに準じます(つまり、前のプロファイルを上書きします)
Datadirパラメータ:データベースが存在するパスを指定します.次のコマンドでデータベースのパスを表示できます.
MySQLデータベースのアーキテクチャ
いくつかの概念を理解して、以下MySQLデータベースのアーキテクチャを紹介します:(画像は『MySQL技術の内幕:InnoDBストレージエンジン』)
MySQLは次のセクションで構成されています.接続プールコンポーネント 管理サービスおよびツールコンポーネント SQLインタフェースコンポーネント クエリーアナライザコンポーネント オプティマイザ・コンポーネント バッファアセンブリ プラグインストレージエンジン 物理ファイル 注意:プラグインアーキテクチャはMySQL独自であり、ストレージエンジンはMySQLが他のデータベースと区別される最も重要な機能です.ストレージ・エンジンは、データベースではなくテーブル・ベースです.
ストレージエンジンのメリット:各ストレージエンジンにはそれぞれの特徴があり、異なるアプリケーションに基づいて異なるストレージエンジンテーブルを構築することができます.
InnoDBストレージエンジン:Windowsバージョンのデフォルトストレージエンジンで、トランザクションをサポートし、主にオンライントランザクション(OLT P)方面の応用に向いている.
ロー・ロックの設計、外部キー、およびOracleと同様の非ロック・リードをサポートします.つまり、デフォルトでは読み込み操作にロックは発生しません.
InnoDBストレージエンジンは、データを論理的な表領域に配置し、InnoDB自身で管理します(ブラックボックスに似ています). InnoDBは、マルチバージョン同時制御(MVCC)を用いることにより、高同時性 を得る. SQL規格の4つの独立性レベル(デフォルトはREPEATABLEレベル) を実現 next-key lockingポリシーを使用して幻読現象 を回避する.は、挿入バッファ、二次書き込み、適応ハッシュインデックス、プリフェッチなどの を提供する.
テーブル内のデータの格納については、InnoDBストレージエンジンは、Oracleのインデックス集計テーブルのような集計方式を採用し、プライマリ・キーが指定されていない場合は、ローごとに6バイトのROWIDを生成し、プライマリ・キーとして使用します.
MySQLテーブルストレージエンジン
MyISAMストレージエンジン:Windowsバージョン以外のすべてのMySQLバージョンのデフォルトのストレージエンジン.トランザクション、テーブル・ロック、および全文インデックスはサポートされていません.OLAP(オンライン分析処理)は速度が速い.
MyISAMストレージエンジンは、MYD(データファイルの格納)とMYI(インデックスファイルの格納)で構成されています.
NDBストレージエンジン:クラスタストレージエンジン(OracleのRACクラスタと同様)、share nothingのクラスタ構造で、データはすべてメモリに格納されます(5.1バージョン後、インデックス以外のデータはディスクに格納されます)、プライマリ・キーの検索速度は非常に速く、NDBデータストレージノードを追加することで、データベースのパフォーマンスを線形に向上させることができます.
注意:NDBストレージエンジンの接続操作(JOIN)はMySQLデータベース層で行われ、ストレージエンジン層ではなく、複雑な接続操作には大きなネットワークオーバーヘッドが必要で、クエリー速度が遅い.
Memoryストレージエンジン:HEAPストレージエンジンで、テーブルのデータをメモリに格納し、一時データを格納する一時テーブルとデータウェアハウスの緯度テーブルに適用します.デフォルトでは、B+ツリーインデックスではなくハッシュインデックスが使用されます.
テーブルロックのみがサポートされ、パフォーマンスが悪く、TEXTやBLOBカラムタイプはサポートされていません.varcharを格納する場合はcharに従います.
注意:MySQLデータベースでは、Memoryストレージエンジンを一時テーブルとして使用して、クエリの中間結果セットを保存します.中間結果セットがMemoryストレージエンジンテーブルの容量設定より大きい場合、または中間結果にTEXTまたはBLOBが含まれている場合は、MyISAMストレージエンジンテーブルに変換してディスクに格納されます(5.1バージョン以降はインデックス以外のデータがディスクに格納されます).この場合、発生した一時テーブルのパフォーマンスはクエリに損失します.
Archieveストレージエンジン:INSERTとSELECT操作のみをサポートし、5.1バージョンからインデックスをサポートします.zlibアルゴリズムを使用してデータ行を圧縮して格納します(圧縮比率は一般的に1:10に達します).ログ情報などのアーカイブデータの格納に適しています.ロー・ロックを使用して、トランザクション・セキュリティ以外の高同時挿入操作を実現し、高速な挿入と圧縮機能を提供することを目的としています.
Federatedストレージエンジン:データを保存せず、リモートMySQLデータベースサーバ上のテーブル(SQLサーバのリンクサーバやOracleの透明ゲートウェイのようなもの)を指し、MySQLデータベーステーブルのみをサポートします.
Mariaストレージエンジン:既存のMyISAMストレージエンジンに取って代わるために設計され、特徴は:キャッシュデータとインデックスファイル、行ロック設計、MVCC機能を提供し、トランザクションと非トランザクションのセキュリティをサポートするオプションサポート、およびより良いBLOB文字タイプの処理性能である.
各種ストレージエンジン間の比較
SHOW ENGINESで現在使用されているMySQLデータベースでサポートされているストレージエンジンを表示したり、information_schemaアーキテクチャの下のENGINESテーブルの表示:
MySQLはインスタンス・データベースを提供しますが、データベースのインストール時にユーザーにインストールするかどうかを提示していません.そのため、自分でダウンロードする必要があります.MySQLのインスタンス・データベースを簡単な例として、各ストレージ・エンジンの違いを表示します.
MySQLへの接続
接続MySQL操作は接続プロセスとMySQLデータベースの実例で通信を行い、実質的にプロセス通信であり、プロセス通信の常用方式はパイプ、命名パイプ、命名パイプ、TCP/IPソケット、Unixドメイン名ソケットである.
TCP/IP:以下の図:
TCP/IPでMySQLインスタンスに接続する場合、MySQLはまず、クライアントIPがMySQLインスタンスに接続できるかどうかを判断するための権限ビューをチェックします.このビューはmysqlライブラリの下にあり、テーブル名はuserです.
名前付きパイプと共有メモリ:
通信が必要な2つのプロセスが同じサーバにある場合は、名前付きパイプを使用します.MySQL4.1以降、MySQLは共有メモリの接続方法を提供し、プロファイルに--shared-memoryを追加します.
Unixドメインソケット:
Unixドメインソケットはネットワークプロトコルではないため、クライアントとデータベースインスタンスが同じサーバ上でのみ使用でき、LinuxおよびUnix環境でのみ使用できます.-socket=/tmp/mysqlなど、コンフィギュレーションファイルにソケットファイルのパスを指定できます.sock.
Unixドメインソケットファイルを検索するには、次の手順に従います.
Unixドメインソケットファイルのパスを知ると、次のように接続できます.
小結
まず、データベースとデータベースのインスタンスの意味を説明し、MySQLのアーキテクチャを分析し、一般的なストレージエンジンの特性、適用状況、お互いの違いを詳しく説明しました.
内容は『MySQL技術内幕:InnoDBストレージエンジン』から
MySQLアーキテクチャのストレージ・エンジンを明確に理解するには、まずデータベースとデータベース・インスタンスの2つの概念を説明します.
簡単に言えば、データベースは1つのファイルから構成されています(一般的にはバイナリファイルです).これらのファイルに対してSELECT、INSERT、UPDATE、DELETEなどの操作を行う場合は、簡単な操作ファイルではデータベースの内容を変更するのではなく、データベースのインスタンスでデータベースの操作を完了する必要があります.
データベース・インスタンスとデータベースは、通常、1つの対応関係ですが、クラスタの場合、1つのデータベースは複数のインスタンスで使用できます.
インスタンスを起動すると、MySQLデータベースはプロファイルを読み込みます.プロファイルがない場合は、コンパイル時のデフォルトのパラメータ設定に従ってインスタンスを起動します(Oracleではプロファイルがない場合、起動時にパラメータファイルが見つからないというプロンプトが表示されます).コマンドでMySQLインスタンスの起動時を表示できます.プロファイルを検索する場所(MySQLインストールディレクトリのbinディレクトリの下で実行):
[root@mysql bin]# ./mysql --help | grep my.cnf
order of preference, my.cnf, $MYSQL_TCP_PORT,
/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf
各プロファイルは前後から順番に読み込まれ、複数のプロファイルに同じパラメータがある場合は、読み込まれた最後のプロファイルのパラメータに準じます(つまり、前のプロファイルを上書きします)
Datadirパラメータ:データベースが存在するパスを指定します.次のコマンドでデータベースのパスを表示できます.
mysql> show variables like 'datadir'\G;
*************************** 1. row ***************************
Variable_name: datadir
Value: /usr/local/mysql/mysql-5.6.40-linux-glibc2.12-x86_64/data/
1 row in set (0.00 sec)
MySQLデータベースのアーキテクチャ
いくつかの概念を理解して、以下MySQLデータベースのアーキテクチャを紹介します:(画像は『MySQL技術の内幕:InnoDBストレージエンジン』)
MySQLは次のセクションで構成されています.
ストレージエンジンのメリット:各ストレージエンジンにはそれぞれの特徴があり、異なるアプリケーションに基づいて異なるストレージエンジンテーブルを構築することができます.
InnoDBストレージエンジン:Windowsバージョンのデフォルトストレージエンジンで、トランザクションをサポートし、主にオンライントランザクション(OLT P)方面の応用に向いている.
ロー・ロックの設計、外部キー、およびOracleと同様の非ロック・リードをサポートします.つまり、デフォルトでは読み込み操作にロックは発生しません.
InnoDBストレージエンジンは、データを論理的な表領域に配置し、InnoDB自身で管理します(ブラックボックスに似ています).
テーブル内のデータの格納については、InnoDBストレージエンジンは、Oracleのインデックス集計テーブルのような集計方式を採用し、プライマリ・キーが指定されていない場合は、ローごとに6バイトのROWIDを生成し、プライマリ・キーとして使用します.
MySQLテーブルストレージエンジン
MyISAMストレージエンジン:Windowsバージョン以外のすべてのMySQLバージョンのデフォルトのストレージエンジン.トランザクション、テーブル・ロック、および全文インデックスはサポートされていません.OLAP(オンライン分析処理)は速度が速い.
MyISAMストレージエンジンは、MYD(データファイルの格納)とMYI(インデックスファイルの格納)で構成されています.
NDBストレージエンジン:クラスタストレージエンジン(OracleのRACクラスタと同様)、share nothingのクラスタ構造で、データはすべてメモリに格納されます(5.1バージョン後、インデックス以外のデータはディスクに格納されます)、プライマリ・キーの検索速度は非常に速く、NDBデータストレージノードを追加することで、データベースのパフォーマンスを線形に向上させることができます.
注意:NDBストレージエンジンの接続操作(JOIN)はMySQLデータベース層で行われ、ストレージエンジン層ではなく、複雑な接続操作には大きなネットワークオーバーヘッドが必要で、クエリー速度が遅い.
Memoryストレージエンジン:HEAPストレージエンジンで、テーブルのデータをメモリに格納し、一時データを格納する一時テーブルとデータウェアハウスの緯度テーブルに適用します.デフォルトでは、B+ツリーインデックスではなくハッシュインデックスが使用されます.
テーブルロックのみがサポートされ、パフォーマンスが悪く、TEXTやBLOBカラムタイプはサポートされていません.varcharを格納する場合はcharに従います.
注意:MySQLデータベースでは、Memoryストレージエンジンを一時テーブルとして使用して、クエリの中間結果セットを保存します.中間結果セットがMemoryストレージエンジンテーブルの容量設定より大きい場合、または中間結果にTEXTまたはBLOBが含まれている場合は、MyISAMストレージエンジンテーブルに変換してディスクに格納されます(5.1バージョン以降はインデックス以外のデータがディスクに格納されます).この場合、発生した一時テーブルのパフォーマンスはクエリに損失します.
Archieveストレージエンジン:INSERTとSELECT操作のみをサポートし、5.1バージョンからインデックスをサポートします.zlibアルゴリズムを使用してデータ行を圧縮して格納します(圧縮比率は一般的に1:10に達します).ログ情報などのアーカイブデータの格納に適しています.ロー・ロックを使用して、トランザクション・セキュリティ以外の高同時挿入操作を実現し、高速な挿入と圧縮機能を提供することを目的としています.
Federatedストレージエンジン:データを保存せず、リモートMySQLデータベースサーバ上のテーブル(SQLサーバのリンクサーバやOracleの透明ゲートウェイのようなもの)を指し、MySQLデータベーステーブルのみをサポートします.
Mariaストレージエンジン:既存のMyISAMストレージエンジンに取って代わるために設計され、特徴は:キャッシュデータとインデックスファイル、行ロック設計、MVCC機能を提供し、トランザクションと非トランザクションのセキュリティをサポートするオプションサポート、およびより良いBLOB文字タイプの処理性能である.
各種ストレージエンジン間の比較
SHOW ENGINESで現在使用されているMySQLデータベースでサポートされているストレージエンジンを表示したり、information_schemaアーキテクチャの下のENGINESテーブルの表示:
mysql> show engines\G;
*************************** 1. row ***************************
Engine: InnoDB
Support: DEFAULT
Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
XA: YES
Savepoints: YES
*************************** 2. row ***************************
Engine: CSV
Support: YES
Comment: CSV storage engine
Transactions: NO
XA: NO
Savepoints: NO
*************************** 3. row ***************************
Engine: MRG_MYISAM
Support: YES
Comment: Collection of identical MyISAM tables
Transactions: NO
XA: NO
Savepoints: NO
*************************** 4. row ***************************
Engine: BLACKHOLE
Support: YES
Comment: /dev/null storage engine (anything you write to it disappears)
Transactions: NO
XA: NO
Savepoints: NO
*************************** 5. row ***************************
Engine: MyISAM
Support: YES
Comment: MyISAM storage engine
Transactions: NO
XA: NO
Savepoints: NO
*************************** 6. row ***************************
Engine: MEMORY
Support: YES
Comment: Hash based, stored in memory, useful for temporary tables
Transactions: NO
XA: NO
Savepoints: NO
*************************** 7. row ***************************
Engine: ARCHIVE
Support: YES
Comment: Archive storage engine
Transactions: NO
XA: NO
Savepoints: NO
*************************** 8. row ***************************
Engine: FEDERATED
Support: NO
Comment: Federated MySQL storage engine
Transactions: NULL
XA: NULL
Savepoints: NULL
*************************** 9. row ***************************
Engine: PERFORMANCE_SCHEMA
Support: YES
Comment: Performance Schema
Transactions: NO
XA: NO
Savepoints: NO
9 rows in set (0.00 sec)
MySQLはインスタンス・データベースを提供しますが、データベースのインストール時にユーザーにインストールするかどうかを提示していません.そのため、自分でダウンロードする必要があります.MySQLのインスタンス・データベースを簡単な例として、各ストレージ・エンジンの違いを表示します.
MySQLへの接続
接続MySQL操作は接続プロセスとMySQLデータベースの実例で通信を行い、実質的にプロセス通信であり、プロセス通信の常用方式はパイプ、命名パイプ、命名パイプ、TCP/IPソケット、Unixドメイン名ソケットである.
TCP/IP:以下の図:
TCP/IPでMySQLインスタンスに接続する場合、MySQLはまず、クライアントIPがMySQLインスタンスに接続できるかどうかを判断するための権限ビューをチェックします.このビューはmysqlライブラリの下にあり、テーブル名はuserです.
mysql> use mysql;
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 host, user, password from user;
+-------------------+------+-------------------------------------------+
| host | user | password |
+-------------------+------+-------------------------------------------+
| localhost | root | *D5C139BE655F9C459762CC5D8C6819988C732B94 |
| host-mysql | root | |
| 127.0.0.1 | root | |
| ::1 | root | |
| localhost | | |
| host-mysql | | |
| % | root | *D5C139BE655F9C459762CC5D8C6819988C732B94 |
+-------------------+------+-------------------------------------------+
7 rows in set (0.00 sec)
名前付きパイプと共有メモリ:
通信が必要な2つのプロセスが同じサーバにある場合は、名前付きパイプを使用します.MySQL4.1以降、MySQLは共有メモリの接続方法を提供し、プロファイルに--shared-memoryを追加します.
Unixドメインソケット:
Unixドメインソケットはネットワークプロトコルではないため、クライアントとデータベースインスタンスが同じサーバ上でのみ使用でき、LinuxおよびUnix環境でのみ使用できます.-socket=/tmp/mysqlなど、コンフィギュレーションファイルにソケットファイルのパスを指定できます.sock.
Unixドメインソケットファイルを検索するには、次の手順に従います.
mysql> show variables like 'socket';
+---------------+-----------------+
| Variable_name | Value |
+---------------+-----------------+
| socket | /tmp/mysql.sock |
+---------------+-----------------+
1 row in set (0.00 sec)
Unixドメインソケットファイルのパスを知ると、次のように接続できます.
[root@mysql bin]# ./mysql -uroot -S /tmp/mysql.sock -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 22
Server version: 5.6.40 MySQL Community Server (GPL)
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
小結
まず、データベースとデータベースのインスタンスの意味を説明し、MySQLのアーキテクチャを分析し、一般的なストレージエンジンの特性、適用状況、お互いの違いを詳しく説明しました.
内容は『MySQL技術内幕:InnoDBストレージエンジン』から