MySQL のテーブルとビュー

ベーステーブルは行を永続化します。ビューはクエリ定義を永続化し、その結果を テーブルのようなオブジェクトとして提示します。SHOW FULL TABLES で両者を区別でき、 INFORMATION_SCHEMA.TABLES.TABLE_TYPE は BASE TABLE または VIEW を返します。

InnoDB の行フォーマット #

InnoDB は REDUNDANT、COMPACT、DYNAMIC、COMPRESSED の各行フォーマットを サポートします。現在のデフォルトは DYNAMIC です。可変長カラムでは、InnoDB は 長い値をオーバーフローページに格納し、クラスタ化レコード内に参照を保持できます。 インラインとオーバーフローの厳密な動作は、行フォーマット、ページサイズ、カラム型、 値の長さによって異なります。そのため、スキーマ設計では TEXT または BLOB の値が 行内で固定サイズを占めると仮定すべきではありません。

テーブルに適用されている設定を確認します。

SHOW TABLE STATUS LIKE 'orders';
SHOW CREATE TABLE orders;

ビュー #

安定した明示的なカラムリストを指定してビューを作成します。

CREATE VIEW active_customer_orders AS
SELECT o.id, o.customer_id, o.created_at, o.total
FROM orders AS o
WHERE o.status = 'active';

デフォルトでは、ビューは別個のテーブルを格納しません。オプティマイザーは、 マージ可能なビューを外側のクエリにマージできます。一方、集約、DISTINCT、 一部の派生テーブル操作など、定義上必要な場合は一時テーブルを実体化できます。 ALGORITHM = MERGE と ALGORITHM = TEMPTABLE は優先する方式を表しますが、 ビュー定義は選択したアルゴリズムの制約を満たす必要があります。

影響を受ける各行が単一のベーステーブルに明確に対応する場合、一部のビューは 更新可能です。集約、GROUP BY、DISTINCT、ウィンドウ関数、集合演算、 または更新不可能な結合を含むビューは更新できません。ビューの更新動作が 必須インターフェイスの一部でない場合は、書き込みにベーステーブルを使用します。

DEFINER アカウントと SQL SECURITY 設定によって、ビュー実行時に誰の権限を 適用するかが決まります。ビューで制限された射影をアプリケーションアカウントに 公開する場合は、両方を確認してください。

WITH CHECK OPTION は、更新可能なビューを介した書き込みによって、そのビューから 参照できない行が作成されることを防ぎます。CASCADED はビューと基になるビューの 条件を検査し、LOCAL は現在のビューの条件のみを検査します。

CREATE SQL SECURITY INVOKER VIEW active_customer_orders AS
SELECT id, customer_id, created_at, total, status
FROM orders
WHERE status = 'active'
WITH CASCADED CHECK OPTION;

MySQL はビュー定義をデータディクショナリに格納し、ビューの実行時に参照先 オブジェクトを解決します。ただし、スキーマ変更によってビューが無効になる場合が あります。依存テーブルを変更した後は SHOW CREATE VIEW と CHECK TABLE を使用し、 呼び出し元から互換性のない定義が見えないようにマイグレーションの順序を決めます。

MySQL には、自動的に維持されるネイティブなマテリアライズドビューオブジェクトは ありません。事前計算が必要な場合は、スケジュールされたトランザクション、 変更データパイプライン、またはアプリケーションジョブによってデータを投入する ベーステーブルを使用します。更新の原子性と鮮度を明示的に定義してください。 そのテーブルの入れ替えや更新は、通常のビューに対するクエリとは、整合性と メタデータロックの動作が異なります。

参考資料 #

• InnoDB の行フォーマット
• ビュー処理アルゴリズム
• 更新可能なビューとチェックオプション