MySQLにおける索引とビューの使い方と違いの詳細

前言
この記事では主にMySQLのインデックスとビューの使い方と違いについて紹介します。参考学習のために、以下の話は多くなくなりました。詳しく紹介してみましょう。
索引
一、概要
すべてのMysql列タイプが索引されます。
mysqlはBTREEインデックス、HASHインデックス、プレフィックス索引、全文本索引（FULLTEXT）【MyISAMエンジンのみのサポートであり、char、varrhar、text列のみ】、空間列索引【MyISAMエンジンのみのサポートであり、インデックスのフィールドは空でない必要があります】が、関数インデックスはサポートされていません。
MyISAMとInnoDBエンジンのテーブルはデフォルトでBTREEインデックスを作成し、
MEMORYエンジンのテーブルはデフォルトでHASHインデックスを作成します。
二、索引の作成
create index文法は以下の通りです。

create [unique|fulltext|spatial] index index_name
 [using index_type]
on tbl_name(index_col_name, ...);

index_col_name:
 col_name [(length)] [asc/desc]

インデックスを追加するためにalter tableを使用することもできます。

ALTER [IGNORE] TABLE tbl_name
 alter_specification [, alter_specification] ...

alter_specification:
 ...
 ADD INDEX [index_name] [index_type] (index_col_name,...)
 ...

たとえば、シティテーブルのために10バイトのプレフィックスインデックスを作成します。

mysql> create index cityName on city(Name(10));
mysql> alter table city add index cityName(Name(10));

三、索引を表示するshow index from table;を使用して、テーブルの現在のすべてのインデックスを見ることができます。
四、索引の削除

drop index index_name on tbl_name;

五、BTREEインデックスとHASHインデックス
MEMORYエンジンのテーブルはBTREEインデックスとHASHインデックスを使用して選択できます。
BTREEインデックス:

使用すると、<、=>=、between、=、<>またはlike xxx（xxxはワイルドカードで開始しない）の操作を行う場合、関連する列のBTREEインデックスを使用することができます。

HASHインデックスの使用上の注意事項：（HASH表の制限に関連して）

は、=<=>オペレータ間の等式比較のみを使用することができます。

最適化器はHASHインデックスを使用してorder by動作を加速することができません。

mysqlは二つの値の前にどれぐらいの行があるかを確認できません。検索の効率に影響します。

はキーワードを使って1行だけ検索できます。

六、設計索引の原則
検索した索引の列は、必ずしも選択する列ではありません。インデックスに最適な列は、selectの後の列ではなく、where子文に現れる列です。
一意の索引を使用します。数値が分かりやすい列を選択して索引を作成します。例えば、誕生日に対する索引は性別に対する索引よりもいいです。誕生日の列は異なる値を持っていますので、比較的に区別しやすいです。性別の列はMとFだけあります。この時は索引はあまり役に立たないです。毎回索引は大体半分の行が出ます。
短い索引を使用します。文字列のプレフィックスインデックスには通常、プレフィックス長が指定されます。上位10文字から20文字までの間に多数値が一意であれば、列全体をインデックスする必要なく、最初の10文字から20文字までの索引を行うことができます。インデックススペースを節約し、I/O時間を減らし、クエリ効率を向上させることができます。
インデックスを過度にしないでください。各追加のインデックスは追加の空間を占有し、書き込みの性能を低下させ、表の修正にはインデックスを更新し、さらに再構成する可能性があるので、インデックスが多くなり、時間がかかります。また、MySQLは実行計画を生成する際に、各インデックスを考慮し、余分なインデックスはクエリ最適化の作業をより重くします。
表示
一、概要
mysqlは5.0.1バージョンから表示機能を提供します。
ビューは、データベースには実際には存在しない仮想テーブルであり、行と列のデータは、カスタムビューのクエリーで使用されるテーブルから来て、ビューを使用して動的に生成されます。
二、ビューの作成または変更
ビューの作成にはcreate view権限が必要であり、クエリーに関するテーブルと列にはselect権限が必要である。create or replaceまたはalter権限を使用してビューを変更する場合は、そのビューのdrop権限も必要である。
ビューの構文を作成するには、次のとおりです。

create [or replace][algorithm = {undefined|merge|temptable}]
 view view_name[(column_list)]
 as select_statement
 [with [cascade|local] check option]

ビューの構文を変更するには、次のとおりです。

alter [algorithm = {undefined|merge|temptable}]
 view view_name[(column_list)]
 as select_statement
 [with [cascade|local] check option]

mysqlのビューの定義にはいくつかの制限があります。例えば、fromキーの後にサブクエリが含まれてはいけません。これは他のデータベースと違います。
三、ビューの更新可能性
ビューの更新可能性は、ビュー内のクエリーの定義に関連しています。以下のタイプのビューは更新できません。

は、集計関数（sum、min、max、countなど）、distinct、group by、having、unionまたはunion allを含む。

定数表示。

selectにはサブクエリが含まれています。

joinです

fromは更新できないビューです。

where文のサブクエリーにfrom文の表が引用されています。

例えば、以下のビューは更新できません。

--       
mysql > create or replace view payment_sum as 
 -> select staff_id,sum(amount) 
 -> from payment
 -> group by staff_id;

--     
mysql > create or replace view pi as 
 -> select 3.1415926 as pi;

-- select      
mysql > create view city_view as
 -> select ( select city from city where city_id = 1);

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

local：このビューの条件を満たせば

を更新することができます。

cascaded：このビューのすべてのビューに対する条件を満たさなければ更新できない。

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

drop view [if exists] view_name [,view_name] ... [restrict|cascaded]

例えばビューpay_を削除します。ビュー

mysql> drop view pay_view1,pay_view2;
Query OK, 0 rows affected (0.00 sec)

五、ビューを表示する
MySQLは5.1バージョンからスタートし、show tablesコマンドは表の名前だけでなく、ビューを単独で表示するshow viewコマンドが存在しません。
同様に、以下のコマンドで確認しても良いです。

show table status [from db_name] [like 'pattern']

例

mysql> show table status like 'pay_view' \G
*************************** 1. row ***************************
  Name: pay_view
  Engine: NULL
 Version: NULL
 Row_format: NULL
  Rows: NULL
 Avg_row_length: NULL
 Data_length: NULL
Max_data_length: NULL
 Index_length: NULL
 Data_free: NULL
 Auto_increment: NULL
 Create_time: NULL
 Update_time: NULL
 Check_time: NULL
 Collation: NULL
 Checksum: NULL
 Create_options: NULL
 Comment: VIEW
1 row in set (0.00 sec)

ビューの定義を表示するには、show create viewを使用して見ることができます。
例

mysql> show create view pay_view \G
*************************** 1. row ***************************
  View: pay_view
  Create 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
character_set_client: gbk
collation_connection: gbk_chinese_ci
1 row in set (0.00 sec)

最後に、システムテーブルinformation_schema.viewsを見ることによって、ビューの関連情報を見ることもできる。
例

mysql> select * from information_schema.views where table_name = 'pay_view' \G
*************************** 1. row ***************************
 TABLE_CATALOG: def
 TABLE_SCHEMA: mysqldemo
  TABLE_NAME: pay_view
 VIEW_DEFINITION: select `mysqldemo`.`pay`.`pid` AS `pid`,`mysqldemo`.`pay`.`amount` AS `amount` from `mysqldemo`.`pay` where (`mysqldemo`.`pay`.`amount` < 10)
 CHECK_OPTION: CASCADED
 IS_UPDATABLE: YES
  DEFINER: root@localhost
 SECURITY_TYPE: DEFINER
CHARACTER_SET_CLIENT: gbk
COLLATION_CONNECTION: gbk_chinese_ci
1 row in set (0.03 sec)

Q&A:
MySQLビューはインデックスが使えますか？
答えは肯定的だと思います。インデックスはビューの後ろにある真実のテーブルの上に立てられています。
インデックスはモード（schema）に格納されたデータベースオブジェクトであり、インデックスの役割はテーブルの検索速度を向上させることであり、インデックスは素早くアクセスする方法で迅速にデータを位置決めし、ディスクに対する読み書き操作を減少させることである。索引はデータベースのオブジェクトです。独立して存在することはできません。テーブルオブジェクトに依存する必要があります。
表示とは、テーブルまたは複数のテーブルのクエリの結果であり、データを格納することができない仮想テーブルである。
参考資料
唐漢明等著、『深入浅出MySQL』、人民郵便出版社、2014
締め括りをつける
以上はこの文章の全部の内容です。本文の内容は皆さんの学習や仕事に対して一定の参考となる学習価値を持っています。質問があれば、メッセージを書いて交流してください。ありがとうございます。