MySQL のインデックスとビューの使用方法と違いの詳細な説明

序文

この記事では主に、MySQL のインデックスとビューの使用方法と違いを紹介し、参考と学習のために共有します。早速、詳しい紹介を見てみましょう。

索引

1. 概要

すべての MySQL 列タイプにインデックスを作成できます。

MySQL は、BTREE インデックス、HASH インデックス、プレフィックス インデックス、フルテキスト インデックス (FULLTEXT) [MyISAM エンジンでのみサポートされ、char、varchar、text 列に限定されます]、空間列インデックス [MyISAM エンジンでのみサポートされ、インデックス フィールドは空であってはなりません] をサポートしていますが、関数インデックスはサポートしていません。

MyISAM および InnoDB ストレージ エンジン テーブルは、デフォルトで BTREE インデックスを作成します。
デフォルトでは、MEMORY ストレージ エンジンを使用するテーブルに対して HASH インデックスが作成されます。

2. インデックスを作成する

インデックス作成の構文は次のとおりです。

[ユニーク|フルテキスト|空間]インデックスindex_nameを作成する
 [index_type を使用]
tbl_name(index_col_name, ...); に対して

インデックス列名:
 col_name [(長さ)] [昇順/降順]

alter tableを使用してインデックスを追加することもできます。構文は次のとおりです。

ALTER [IGNORE] テーブル tbl_name
 alter_specification [、alter_specification] ...

仕様の変更:
 ...
 ADD INDEX [インデックス名] [インデックスタイプ] (インデックス列名、...)
 ...

例: cityテーブルに10バイトのプレフィックスインデックスを作成します。

mysql> city(Name(10)) にインデックス cityName を作成します。
mysql> テーブル city を変更し、インデックス cityName(Name(10)) を追加します。

3. インデックスを表示する

テーブルの現在のインデックスをすべて表示するにはshow index from table;を使用します。

4. インデックスを削除する

tbl_name のインデックス index_name を削除します。

5. BTREEインデックスとHASHインデックス

MEMORYストレージエンジンを持つテーブルでは、BTREEインデックスとHASHインデックスの使用を選択できます。

BTREE インデックス:

• >、<、=、>=、<=、between、!=、<>、または like xxx (xxx はワイルドカードで始まらない) 演算子を使用する場合、関連する列の BTREE インデックスを使用できます。

HASH インデックスの使用に関する注意事項: (HASH テーブルの制限に関連)

• = または <=> 演算子を使用した等価比較にのみ使用できます。
• オプティマイザーは、HASH インデックスを使用して order by 操作を高速化することはできません。
• MySQL では、2 つの値の前に何行あるかおおよそ判断できないため、クエリの効率に一定の影響が出ます。
• キーワード全体を使用して検索できるのは 1 行のみです。

6. インデックス設計の原則

検索するインデックス列は、必ずしも選択される列とは限りません。インデックス作成に最も適した列は、SELECT ステートメントに表示される列ではなく、WHERE 句に表示される列です。

一意のインデックスを使用します。インデックス作成時に値を簡単に区別できる列を選択します。たとえば、誕生日の列にはさまざまな値があり区別しやすいのに対し、性別の列には M と F しかないため、誕生日のインデックスは性別のインデックスよりも適しています。この場合、インデックスはあまり役に立たず、各インデックスは行の約半分を返します。

短いインデックスを使用します。プレフィックスの長さは通常、文字列のプレフィックス インデックスに指定されます。ほとんどの値が最初の 10 ～ 20 文字の範囲内で一意である場合は、列全体をインデックスする必要はなく、最初の 10 ～ 20 文字をインデックスします。これにより、インデックス スペースが節約され、I/O 時間が短縮され、クエリの効率が向上します。

インデックスを過剰に作成しないでください。インデックスを追加するごとにスペースが余分に占有され、書き込み操作のパフォーマンスが低下します。テーブルが変更されると、インデックスを更新し、再構築する必要があり、インデックスの数が増えるほど、時間がかかります。さらに、MySQL は実行プランを生成する際にさまざまなインデックスを考慮します。冗長なインデックスがあると、クエリの最適化タスクがさらに困難になります。

ビュー

1. 概要

MySQL はバージョン 5.0.1 以降でビュー機能を提供しています。

ビューは、実際にはデータベースに存在しない仮想テーブルです。行と列のデータは、カスタム ビューのクエリで使用されるテーブルから取得され、ビューが使用されるときに動的に生成されます。

2. ビューを作成または変更する<br /> ビューを作成するには、 create view権限と、クエリに含まれるテーブルと列の選択権限が必要です。

ビューを変更するためにcreate or replace 、または変更権限を使用する場合は、ビューの削除権限も必要です。

ビューを作成するための構文は次のとおりです。

作成 [または置換][アルゴリズム = {未定義|マージ|誘惑的}]
 ビュー view_name[(列リスト)]
 選択ステートメントとして
 [[カスケード|ローカル]チェックオプション付き]

ビュー構文を次のように変更します。

変更 [アルゴリズム = {未定義|マージ|誘惑可能}]
 ビュー view_name[(列リスト)]
 選択ステートメントとして
 [[カスケード|ローカル]チェックオプション付き]

MySQL では、ビュー定義にいくつかの制限があります。たとえば、from キーワードにはサブクエリを含めることができません。これは他のデータベースとは異なります。

3. ビューの更新可能性

ビューの更新可能性は、ビュー内のクエリの定義に関係します。次の種類のビューは更新できません。

• 集計関数 (sum、min、max、count など)、distinct、group by、having、union、union all が含まれます。
• 常時表示。
• 選択にはサブクエリが含まれています。
• 参加する。
• 更新できないビューから。
• where 句のサブクエリは、from 句のテーブルを参照します。

たとえば、次のビューは更新できません

-- 集計関数を含むmysql > ビューpayment_sumを作成または置換 
 -> staff_id、sum(金額) を選択 
 -> 支払いから
 -> staff_id でグループ化します。

-- 定数ビュー mysql> ビュー pi を作成または置換します 
 -> 円周率として 3.1415926 を選択します。

-- サブクエリmysqlを含むを選択 > city_viewビューを作成
 -> select ( city_id = 1 の city から city を選択);

with[cascaded|local] check optionレコードがビュー条件を満たさなくなるデータの更新を許可するかどうかを決定します。デフォルトは cascaded です。このオプションは、Oracle データベースのオプションに似ています。

• ローカル: このビューの条件が満たされている限り更新できます
• カスケード: 更新を実行する前に、このビューの下にあるすべてのビューのすべての条件を満たす必要があります。

4. ビューを削除する

一度に 1 つ以上のビューを削除できますが、ビューに対する削除権限が必要です。

ビューを削除 [存在する場合] view_name [,view_name] ... [制限|カスケード]

たとえば、pay_viewビューを削除します。

mysql> ビュー pay_view1、pay_view2 を削除します。
クエリは正常、影響を受けた行は 0 行 (0.00 秒)

5. 表示 表示

MySQL バージョン 5.1 以降では、show tables コマンドはテーブル名だけでなくビュー名も表示します。ビューのみを表示する show views コマンドはありません。

同様に、次のコマンドでも表示できます。

テーブルの状態を表示 [db_name から] ['pattern' のように]

例

mysql> 'pay_view' のようなテーブルステータスを表示 \G
************************** 1. 行 ****************************
  名前: pay_view
  エンジン: NULL
 バージョン: NULL
 行形式: NULL
  行: NULL
 平均行長: NULL
 データ長: NULL
最大データ長: NULL
 インデックスの長さ: NULL
 データ空き: NULL
 自動増分: NULL
 作成時間: NULL
 更新時間: NULL
 チェック時間: NULL
 照合: NULL
 チェックサム: NULL
 作成オプション: NULL
 コメント: 表示
セット内の 1 行 (0.00 秒)

ビューの定義を表示する場合は、 show create view使用できます。

例

mysql> 表示作成ビュー pay_view \G
************************** 1. 行 ****************************
  表示: pay_view
  ビューの作成: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `pay_view` AS select `pay`.`pid` AS `pid`,`pay`.`amount` AS `amount` from `pay` where (`pay`.`amount` < 10) WITH CASCADED CHECK OPTION
文字セットクライアント: gbk
照合接続: gbk_chinese_ci
セット内の 1 行 (0.00 秒)

最後に、システム テーブルinformation_schema.viewsを表示することで、ビューに関する関連情報を表示することもできます。

例

mysql> information_schema.views から * を選択します。ここで、table_name は 'pay_view' です \G
************************** 1. 行 ****************************
 TABLE_CATALOG: 定義
 テーブル_スキーマ: mysqldemo
  テーブル名: ペイビュー
 VIEW_DEFINITION: `mysqldemo`.`pay`.`pid` AS `pid`,`mysqldemo`.`pay`.`amount` AS `amount` from `mysqldemo`.`pay` where (`mysqldemo`.`pay`.`amount` < 10) を選択します
 CHECK_OPTION: カスケード
 更新可能: はい
  定義者: root@localhost
 セキュリティタイプ: 定義者
文字セットクライアント: gbk
照合接続: gbk_chinese_ci
セット内の1行（0.03秒）

質疑応答:

MySQL ビューはインデックスを使用できますか?

答えは「はい」だと思います。インデックスはビュー上ではなく、ビューの背後にある実際のテーブル上に構築されます。

インデックスは、スキーマに格納されるデータベース オブジェクトです。インデックスの機能は、テーブル検索クエリの速度を上げることです。インデックスは、データをすばやく見つけるための高速アクセス方法であり、ディスクの読み取りおよび書き込み操作を削減します。インデックスはデータベース内のオブジェクトです。独立して存在することはできず、テーブル オブジェクトに依存する必要があります。

ビューは、1 つ以上のテーブルのクエリ結果です。データを保存できないため、仮想テーブルです。

参考文献

Tang Hanming 他、「MySQL in Simple Terms」、Posts and Telecommunications Press、2014 年

要約する

上記はこの記事の全内容です。この記事の内容が皆さんの勉強や仕事に一定の参考学習価値を持つことを願っています。ご質問があれば、メッセージを残してコミュニケーションしてください。123WORDPRESS.COM を応援していただきありがとうございます。