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

インデックスの機能は、MySQL での主キーの検索を高速化することです。ユニークおよびジョイントユニークもインデックスです。検索を高速化するだけでなく、これらのインデックスには制約の機能もあります。

#上記のインデックスを作成するときに、インデックスの種類を指定できます。ハッシュ タイプのインデックスには、高速な単一クエリと低速な範囲クエリの 2 種類があります。Btree タイプのインデックス: B+ ツリー、レイヤーが増えるほど、データ量が指数関数的に増加します (InnoDB がデフォルトでサポートしているため、これを使用します)

#異なるストレージ エンジンは異なるインデックス タイプをサポートします。InnoDB はトランザクション、行レベルのロック、B ツリー、フルテキストなどのインデックスをサポートしますが、ハッシュ インデックスはサポートしません。
MyISAM はトランザクションをサポートしていませんが、テーブル レベルのロック、B ツリー、フルテキスト、およびその他のインデックスをサポートしていますが、ハッシュ インデックスはサポートしていません。
メモリはトランザクションをサポートしていませんが、テーブル レベルのロック、B ツリー、ハッシュ、およびその他のインデックスをサポートしていますが、フルテキスト インデックスはサポートしていません。
NDB はトランザクション、行レベルのロック、ハッシュ インデックスをサポートしますが、B ツリー、フルテキスト、その他のインデックスはサポートしません。
アーカイブはトランザクションをサポートしていませんが、テーブル レベルのロックはサポートしています。B ツリー、ハッシュ、フルテキスト、およびその他のインデックスはサポートしていません。

ヘルプドキュメントを活用して作成する
インデックスの作成を支援
==================
1. インデックスを作成する - テーブルを作成するときに作成します（注意すべき点がいくつかあります）
  テーブルs1を作成します(
  id int、#ここに主キーを追加できます
  #id int index #index は単なるインデックスであり、制約ではないため、このようにインデックスを追加することはできません。
  #主キーや一意制約などのフィールドを定義するときに、インデックス名 char(20) を追加することはできません。
  年齢 int、
  メールアドレスvarchar(30)
  #主キー(id) #インデックス(id) を追加することもできます #次のように追加できます);
  - テーブルを作成した後、create index name on s1(name); #共通インデックスを追加しますcreate unique age on s1(age);一意のインデックスを追加しますalter table s1 add primary key(id); #住宅および建設インデックスを追加します。つまり、id フィールドに主キー制約を追加しますcreate index name on s1(id,name); #共通の結合インデックスを追加します2. インデックスを削除しますdrop index id on s1;
  drop index name on s1; #共通インデックスを削除しますdrop index age on s1; #ユニークインデックスを削除します。共通インデックスと同様に、削除するためにインデックスの前に unique を追加する必要はなく、直接削除できますalter table s1 drop primary key; #プライマリキーを削除します (alter に従って追加されたため、削除にも alter を使用します)

#1. テーブルを準備する create table s1(
id int、
名前varchar(20),
性別文字(6)
メールアドレスvarchar(50)
);

#2. バッチでレコードを挿入するストアドプロシージャを作成します。区切り文字は $$ です。#ストアドプロシージャの終了記号を $$ と宣言します。
プロシージャ auto_insert1() を作成する
始める
  i int をデフォルトで 1 と宣言します。
  i<3000000の場合
    s1 に値 (i、concat ('egon'、i)、'male'、concat ('egon'、i、'@oldboy')) を挿入します。
    i=i+1 と設定します。
  終了しながら;
END$$ #$$区切り記号の終了; #セミコロンを終了記号として再宣言します#3. ストアド プロシージャを表示します show create procedure auto_insert1\G 

#4. ストアド プロシージャ call auto_insert1(); を呼び出します。

#インデックスなし: 最初から最後までスキャンするため、クエリ速度が非常に遅くなります。mysql> select * from s1 where id=333;
+------+---------+--------+----------------+
| ID | 名前 | 性別 | メール |
+------+---------+--------+----------------+
| 333 | egon333 | 男性 | [email protected] |
| 333 | egon333 | f | alex333@oldboy |
| 333 | egon333 | f | alex333@oldboy |
+------+---------+--------+----------------+
セット内の行数 (0.32 秒)

mysql> s1 から * を選択します。ここで、email は 'egon333@oldboy' です。
....
... 行セット (0.36 秒)

#1. 検索条件フィールドのインデックスを必ず作成してください。たとえば、select * from t1 where age > 5; の場合は、age のインデックスを追加する必要があります。#2. テーブルにすでに大量のデータがある場合、インデックスの作成には非常に時間がかかり、ハードディスクの容量を消費します。挿入、削除、更新はすべて非常に遅くなります。クエリだけが高速です。たとえば、create index idx on s1(id); はテーブル内のすべてのデータをスキャンし、id をデータ項目として使用してインデックス構造を作成し、ハードディスク上のテーブルに格納します。
作成後、クエリは非常に高速になります #3。innodbテーブルのインデックスはs1.ibdファイルに保存されますが、myisamテーブルのインデックスには別のインデックスファイルtable1.MYIがあることに注意してください。 

#分析 select * from s1 where id=123;
SQL はインデックスにヒットしますが、それをカバーしません。
id=123 を使用して、ハードディスク内、またはインデックス データ構造内のデータ テーブル内の ID の場所を特定します。
ただし、選択したフィールドは * であり、id 以外のフィールドも必要なので、インデックス構造を通じて id を取得するだけでは不十分です。
また、IDを使用して、IDが配置されている行の他のフィールド値を見つける必要があり、時間がかかります。明らかに、IDのみを選択した場合、
この問題は次のようにすると解消されます: select id from s1 where id=123;
これはカバーリングインデックスです。インデックスをヒットし、インデックスデータ構造からハードディスク上のIDのアドレスを直接取得します。速度が非常に速いです。 

#インデックスのマージ: 複数の単一列インデックスをマージし、#分析を使用します。
インデックスのマージを使用すると、結合インデックスで実行できるすべての問題を解決できます。たとえば、create index ne on s1(name,email);#Combined index のように、name と email のインデックスを個別に作成できます。結合インデックスでは、次の操作を実行できます。
name='egon' の場合、s1 から * を選択します。
name='egon' かつ email='adf' の場合、s1 から * を選択します。

インデックスのマージにより次のことが起こります:
name='egon' の場合、s1 から * を選択します。
email='adf' の場合、s1 から * を選択します。
name='egon' かつ email='adf' の場合、s1 から * を選択します。

一見すると、インデックスのマージのほうが優れているように見えます。より多くのケースにヒットしますが、実際にはケースによって異なります。name='egon'、email='adf'の場合、
その場合、結合インデックスの効率はインデックスマージの効率よりも高くなります。単一条件クエリの場合は、インデックスマージを使用する方が合理的です。 

1. 左端のプレフィックス一致の原則は非常に重要な原則です。
s1(name,email,) にインデックス ix_name_email を作成します。
- 左端のプレフィックス一致: 左から右に一致する必要があります。 select * from s1 where name='egon'; #ok select * from s1 where name='egon' and email='asdf'; #ok select * from s1 where email='[email protected]'; #No MySQL は、範囲クエリ (>、<、between、like) に遭遇して一致を停止するまで、右側への一致を続けます。
例えば、a = 1、b = 2、c > 3、d = 4の場合、(a、b、c、d)の順序でインデックスを作成すると、
d のインデックスは必要ありません。(a,b,d,c) のインデックスを作成すれば、すべて使用できます。a,b,d の順序は任意に調整できます。

#2.= および in は任意の順序で指定できます。たとえば、a = 1、b = 2、c = 3 です。(a、b、c) インデックスは任意の順序で作成できます。MySQL クエリ オプティマイザーは、インデックスが認識できる形式に最適化するのに役立ちます。#3. インデックスとして識別性の高い列を選択するようにしてください。識別の式は count(distinct col)/count(*) です。
重複しないフィールドの割合を示します。割合が大きいほど、スキャンするレコードが少なくなります。一意のキーの識別は1ですが、一部の州では、
ビッグデータでは性別フィールドの識別値は 0 になる可能性があるため、この比率に経験的な価値があるのか​​と疑問に思う人もいるかもしれません。さまざまな使用シナリオ、
この値も決定するのが困難です。一般的に、結合する必要があるフィールドは 0.1 以上である必要があります。つまり、1 つのレコードに対して平均 10 レコードがスキャンされます。#4. インデックス列は計算に使用できません。列は「クリーン」な状態にしておいてください (from_unixtime(create_time) = '2014-05-29' など)。
インデックスは使用できません。理由は非常に簡単です。B+ツリーはフィールド値をデータテーブルに格納します。
ただし、検索時には、比較のためにすべての要素に関数を適用する必要があり、コストがかかりすぎるのは明らかです。
したがって、ステートメントは create_time = unix_timestamp('2014-05-29'); と記述する必要があります。

mysql> s1 から * を選択します。ここで、id>3 かつ name='egon' かつ email='[email protected]' かつ gender='male' です。
空集合（0.39秒）

mysql> create index idx on s1(id,name,email,gender); # 左端のプレフィックスがフォローされていません クエリは正常、0 行が影響を受けました (15.27 秒)
レコード: 0 重複: 0 警告: 0

mysql> s1 から * を選択します。ここで、id>3 かつ name='egon' かつ email='[email protected]' かつ gender='male' です。
空セット（0.43秒）


mysql> s1 のインデックス idx を削除します。
クエリは正常、影響を受けた行は 0 行 (0.16 秒)
レコード: 0 重複: 0 警告: 0

mysql> create index idx on s1(name,email,gender,id); # 左端のプレフィックスに従います クエリは正常です。0 行が影響を受けました (15.97 秒)
レコード: 0 重複: 0 警告: 0

mysql> s1 から * を選択します。ここで、id>3 かつ name='egon' かつ email='[email protected]' かつ gender='male' です。
空セット (0.03 秒)

- '%xx' のように
  tb1 から * を選択します。メールアドレスは '%cn' のようなものになります。
  
  
- 関数 select * from tb1 where river(email) = 'wupeiqi'; を使用します。
  
  
- または
  nid = 1 または name = '[email protected]' の場合、tb1 から * を選択します。
  
  
  特殊: or 条件にインデックスが付けられていない列がある場合にのみ無効になります。次の例では、インデックス select * from tb1 where nid = 1 or name = 'seven' を使用します。
      nid = 1 または name = '[email protected]' かつ email = 'alex' の場合、tb1 から * を選択します。
      
      
- 型の不一致 列が文字列型の場合、入力条件は引用符で囲む必要があります。それ以外の場合は...
  tb1 から * を選択します (email = 999)。
  
通常のインデックスは、インデックスが使用されないことを意味するものではありません - !=
  tb1 から * を選択します。メールアドレスが != 'alex' の場合
  
  特別: 主キーの場合、インデックスは引き続き使用されます。select * from tb1 where nid != 123
->
  tb1 から * を選択し、メール > 'alex' を選択します。
  
  
  特別: 主キーまたはインデックスが整数型の場合、インデックスは引き続き使用されます。select * from tb1 where nid > 123
    tb1 から * を選択 (num > 123)
    
    
#ソート条件がインデックスの場合、選択フィールドもインデックスフィールドである必要があります。そうでない場合はヒットしません - order by
  s1 から名前を選択、電子メールで順序を指定、desc;
  インデックスで並べ替える場合、選択したクエリ フィールドがインデックスでない場合、インデックスは使用されません。select email from s1 order by email desc;
  特別: 主キーがソートされている場合でも、インデックスは使用されます。
    tb1 から * を選択し、nid desc で並べ替えます。
 
- 結合インデックスの左端のプレフィックス 結合インデックスが次の場合: (名前、メール)
  名前とメール - インデックスを使用 名前 - インデックスを使用 メール - インデックスを使用しないでください - count(1) または count(column) の代わりに count(*) を使用しても、mysql では違いはありません - tb(title(19)) にインデックス xxxx を作成します #テキスト タイプ、長さを指定する必要があります

- select *の使用を避ける
- count(*) の代わりに count(1) または count(column)
- テーブルを作成するときは、varcharではなくcharを使用するようにしてください。
- テーブル内のフィールドの順序は固定長フィールドが最初です - 複数の単一列インデックスの代わりに複合インデックスを使用します（クエリに複数の条件が頻繁に使用される場合）
- 可能な限り短いインデックスを使用する - サブクエリの代わりにJOINを使用する
- テーブルを結合するときは、条件タイプが一貫していることを確認してください - インデックスハッシュ値（重複が少ない）はインデックス作成に適していません。例：性別は適していません

0. 最初に実行して本当に遅いかどうかを確認し、SQL_NO_CACHEを設定します
1. Where条件の単一テーブルクエリで、最小の戻りレコードテーブルをロックします。この文は、返されるレコード数が最も少ないテーブルにクエリ ステートメントの where 句を適用し、クエリを開始することを意味します。テーブルの各フィールドを個別にクエリして、どのフィールドの識別度が最も高いかを確認します。2. 実行プランが 1 の期待と一致しているかどうかを確認することを説明します (ロックされたレコードが少ないテーブルからクエリを開始します)。
3. order by limit 形式の SQL 文を使用して、ソートされたテーブルに優先順位を付けます。4. ビジネス側の使用シナリオを理解します。5. インデックスを追加するときは、インデックス作成の主要な原則を参照してください。6. 結果を観察します。期待どおりでない場合は、0 から分析を続けます。