MYSQLインデックスの詳細
6043 ワード
本文は大物の文章を最適化し、補充した.https://www.cnblogs.com/chenshishuo/p/5030029.html
インデックスの定義(インデックス別名index,key,キー)
リレーショナル・データベースでは、インデックスは、テーブル内の1つまたは複数のカラムの値をソートするストレージ構造であり、テーブル内の1つまたは複数のカラムの値の集合であり、対応するテーブルに記録された参照ポインタが含まれています.インデックスの役割は図書のディレクトリに相当し、ディレクトリのページ番号に基づいて必要な内容をすばやく見つけることができます.
インデックスもテーブルのコンポーネントであり、インデックスの作成が多すぎると、各インデックスファイルを同じように更新する必要があるため、更新と挿入の速度に影響します.常に更新と挿入が必要なテーブルでは、あまり使用されていないwhere文にインデックスを個別に作成する必要はありません.比較的小さなテーブルでは、ソートのオーバーヘッドは大きくありません.インデックスを作成する必要もありません.
例を挙げると、まず、仮に1枚の表が設けられ、10 W個の記録があるが、その中にa='1'が知られている記録があり、対応する記録を手に入れたい場合、必要なsql文はSELECT*FROM xxx WHERE a='1'である.一般的に、クエリー文では、インデックスが確立されていない場合、mysqlは全テーブルスキャンを行い、10 W個のレコードをスキャンしないと停止しません.nicknameにインデックスを作成すると、mysqlはnicknameという列だけをスキャンすることに相当します.また、この列が順序付けられているため、対応する結果または結果セットを見つけて直接返すことができます.
mysqlのインデックスは、単一カラムインデックス(全文インデックス、プライマリ・キー・インデックス、一意インデックス、通常インデックス)と組合せインデックスに分けられます.
≪単一列索引｜Single Column Index｜oem_src≫:1つの索引には1つの列のみが含まれ、1つの表には複数の単一列索引があります.
≪コンポジット・インデックス｜Composite Index｜oem_src≫:コンポジット・インデックスには2つ以上の列があります.
(一)インデックスの作成1.単列索引
1-1)通常インデックス(これが基本インデックス)
表作成時:INDEX IndexName(`フィールド名`(length))
表作成後:CREATE INDEX IndexName ON`Table Name`(`フィールド名`(length))
またはALTER TABLE Table Name ADD INDEX IndexName(`フィールド名`(length)
注意:フィールドデータがCHARの場合、VARCHARタイプであれば、lengthを指定できます.この値はフィールドの実際の長さよりも小さく、BLOBタイプとTEXTタイプであればlengthを指定する必要があります.
このlengthの使い道は何ですか.
長いテキストフィールドにインデックスを作成する必要がある場合がありますが、このインデックスはインデックスの記憶領域を増加させ、インデックスの効率を低下させます.この場合、lengthを使用することができます.インデックスを作成するときにlengthのインデックスを使用します.接頭辞インデックスと呼ばれます.接頭辞インデックスは、フィールドデータの最初のn文字をインデックスとして選択します.これにより、インデックス空間を大幅に節約することができます.これにより、インデックスの効率が向上します.
ここに示す文は、フィールドデータの最初の10文字を使用したインデックスを作成するために使用されます.CREATE INDEX part_of_name ON customer (name(10));
フィールドデータの一部を使用してインデックスを作成すると、インデックスファイルが大幅に減少し、ディスク領域が大幅に節約され、INSERT操作の速度が向上する可能性があります.
接頭辞インデックスは、インデックスをより小さく、より速くする有効な方法ですが、MySqlでは、接頭辞インデックスをORDER BYおよびGROUP BYに使用したり、接頭辞インデックスをオーバーライドスキャンに使用したりすることはできません.
ここでまた新しい概念を引き出して、スキャンをカバーします!
結合インデックスなどのインデックスにクエリーするすべてのフィールドの値が含まれている場合は、次のように上書きインデックスと呼ばれます.
SELECT user_name, city, age FROM user_test WHERE user_name = 'feinik' AND age > 25;クエリーするフィールド(user_name,city,age)は、インデックスを組み合わせたインデックス列に含まれているため、インデックスを上書きしてクエリーを上書きし、実行計画のExtraの値がUsing indexであることで、インデックスを上書きすることでアクセス性を大幅に向上させることができます.
1-2)一意のインデックスであり、フィールドのすべての値が一意であることが要求されます.これはプライマリ・キー・インデックスと同じですが、空の値を許可します.
表作成時:UNIQUE INDEX IndexName(`フィールド名`(length))
表作成後:CREATE UNIQE INDEX IndexName ON`Table Name`(`フィールド名`(length))
またはALTER TABLE Table Name ADD UNIQUE INDEX IndexName(`フィールド名`(length))
1～3)プライマリ・キー・インデックス、NULL値は許可されていません
普通は表を建てる時に自動的に作成して、主キーは普通intに設定してしかもAUTO_ですINCREMENT自増タイプの
1-4)全文索引仮定フィールドのデータ型は長いテキストであり,テキストフィールド(textなど)には通常の索引が確立されているが,キーワードを検索する必要がある場合はwhere column like'%xxxx%'しか条件にならないが,これにより索引が失効し,全文索引が必要となる.
表作成時:FULLTEXT INDEX IndexName(`フィールド名`(length))
表作成後:CREATE FULLTEXT INDEX IndexName ON`Table Name`(`フィールド名`(length))
またはALTER TABLE Table Name ADD FULLTEXT INDEX IndexName(`フィールド名`(length))
使用:SELECT*FROM TableNameWHERE MATCH(column 1,column 2)AGAINST(‘xxx’,‘sss’,‘ddd’)このコマンドはcolumn 1とcolumn 2フィールドにxxx,sss,dddがあるデータレコードをすべて検索します.
次に例を挙げます.
文書コンテンツフィールドのデータ型がtextである書籍テーブルがあると仮定する
記事id
記事タイトル
文章の内容
1
スーパーセア人
私はスーパーセア人です.私はりんごが好きです.私は天朝の人でも地球人でもありません.
2
天朝大国
私の大天朝の威武、私の大天朝の13億人、私の大天朝
3
私は水泳が好きです.
水泳にはいい方法がたくさんある.
4
アニメーション
私の息子はアニメーションを见ることが好きで、特にドラゴンボール、中にサイヤ人がいるため、その上サイヤ人はリンゴを食べることが好きで、彼らは地球人ではありません
5
うんどう
私はスポーツが好きで、ランニングが好きで、水泳が好きで、フィットネスが好きで、xxooが好きです
6
砲撃する
私は第二次世界大戦の老兵で、これは私の回顧録で、私の最も幸せな时間は天朝でリンゴを食べて砲を撃つことです
7
..........
..........
8
..........
..........
9
..........
..........
広大な本の内容の中でキーワードを検索したいのですが、%xxx%で検索すると、効率が低すぎます.
記事の内容フィールドに全文インデックスを作成します.次はインデックスファイルです.
キーワード
文章id(参照ポインタ)
セア人
1,4
りんご
1,4,6
天朝
1,2,6
地球
1,4
水泳
3,5
ドラゴンボール
4
好き
1,4,5,6
では、「セア人」を検索したいとき、このインデックスファイルは私に文章idが1と4の文章にこの言葉があることを直接教えてくれました.
しかし、これらのキーワードはどのように抽出されたのでしょうか.これは新しい概念、「分詞」に言及します!分詞はキーワードを抽出することですが、MYSQLのFULLTEXTは分詞に対して知能が足りず、中国語に対してもあまりサポートされていないので、全文インデックスは一般的に使いません.代わりに、
coreseek=sphinx+mmesgというプログラムでこの問題を解決できます.
sphinxはインデックスプログラムです.
mmsegは分詞プログラムです.
国内ではsphinxソースコードを修正した人がいて、内建とmmsegが協力して、統合するとcoreseekです(中国語版sphinx)!
2.結合インデックス仮定フィールドa,bはいずれもインデックスがあり、我々のクエリー条件はa=1であり、b=2クエリープロセスはmysqlがa=1に合致する結果セットを先に選択し、これらの結果セットでb=2の結果セットを選択するが、mysqlはa,bをクエリーする際にインデックスを使用することはなく、そのうちの1つしか使用できないので、我々の予想とは異なるので、組合せインデックスを使用する
表作成時:INDEX IndexName(`フィールド名`(length),`フィールド名`(length),….)
表作成後:CREATE INDEX IndexName ON`Table Name`(`フィールド名`(length),`フィールド名`(length),...)
またはALTER TABLE Table Name ADD INDEX IndexName(`フィールド名`(length),`フィールド名`(length),….)
(二)インデックスの削除
DORP INDEX IndexName ON `TableName`
(三)インデックスが失効した場合
1.条件にorがある場合、条件付きインデックスがあっても使用されません(これもorをできるだけ少なく使う理由です)
orを使用し、インデックスを有効にするには、or条件の各カラムにインデックスを付けるしかありません.
2.クエリーを使用する場合はmysqlコンビネーションインデックスの「最左接頭辞」ルールに従います.コンビネーションインデックス(a,b,c)が現在存在すると仮定すると、クエリー文はa=1またはa=1になります.
and b=1またはa=1 and b=1 and c=1.ここで注意しなければならない2つの点は、1 a=1 and b=1とb=1 and a=1と同様に、区別がなく、インデックス2を組み合わせたインデックス(a,b,c)の最左接頭辞がaであることに注意しなければならない.結合インデックス(c,b,a)の最左接頭辞はcであり、最左接頭辞はテーブルフィールドの順序に関係なく
コンポジットインデックスでwhereクエリー条件のカラムが範囲クエリー(%がどこにあるかにかかわらず)を使用している場合、右側のすべてのカラムはインデックスを使用してクエリーを最適化できません.
3.likeクエリは%で始まる
4.カラムタイプが文字列の場合は、条件でデータを引用符で参照する必要があります.そうしないと、インデックスは使用されません.
5.mysqlがインデックスを使用するよりも全テーブルスキャンを使用するほうが速いと推定した場合、インデックスは使用されません.
6.インデックス列は式の一部ではなく、関数のパラメータとしても使用できません.そうしないと、インデックスクエリは使用できません.次に例を示します.
1
インデックスの定義(インデックス別名index,key,キー)
リレーショナル・データベースでは、インデックスは、テーブル内の1つまたは複数のカラムの値をソートするストレージ構造であり、テーブル内の1つまたは複数のカラムの値の集合であり、対応するテーブルに記録された参照ポインタが含まれています.インデックスの役割は図書のディレクトリに相当し、ディレクトリのページ番号に基づいて必要な内容をすばやく見つけることができます.
インデックスもテーブルのコンポーネントであり、インデックスの作成が多すぎると、各インデックスファイルを同じように更新する必要があるため、更新と挿入の速度に影響します.常に更新と挿入が必要なテーブルでは、あまり使用されていないwhere文にインデックスを個別に作成する必要はありません.比較的小さなテーブルでは、ソートのオーバーヘッドは大きくありません.インデックスを作成する必要もありません.
例を挙げると、まず、仮に1枚の表が設けられ、10 W個の記録があるが、その中にa='1'が知られている記録があり、対応する記録を手に入れたい場合、必要なsql文はSELECT*FROM xxx WHERE a='1'である.一般的に、クエリー文では、インデックスが確立されていない場合、mysqlは全テーブルスキャンを行い、10 W個のレコードをスキャンしないと停止しません.nicknameにインデックスを作成すると、mysqlはnicknameという列だけをスキャンすることに相当します.また、この列が順序付けられているため、対応する結果または結果セットを見つけて直接返すことができます.
mysqlのインデックスは、単一カラムインデックス(全文インデックス、プライマリ・キー・インデックス、一意インデックス、通常インデックス)と組合せインデックスに分けられます.
≪単一列索引｜Single Column Index｜oem_src≫:1つの索引には1つの列のみが含まれ、1つの表には複数の単一列索引があります.
≪コンポジット・インデックス｜Composite Index｜oem_src≫:コンポジット・インデックスには2つ以上の列があります.
(一)インデックスの作成1.単列索引
1-1)通常インデックス(これが基本インデックス)
表作成時:INDEX IndexName(`フィールド名`(length))
表作成後:CREATE INDEX IndexName ON`Table Name`(`フィールド名`(length))
またはALTER TABLE Table Name ADD INDEX IndexName(`フィールド名`(length)
注意:フィールドデータがCHARの場合、VARCHARタイプであれば、lengthを指定できます.この値はフィールドの実際の長さよりも小さく、BLOBタイプとTEXTタイプであればlengthを指定する必要があります.
このlengthの使い道は何ですか.
長いテキストフィールドにインデックスを作成する必要がある場合がありますが、このインデックスはインデックスの記憶領域を増加させ、インデックスの効率を低下させます.この場合、lengthを使用することができます.インデックスを作成するときにlengthのインデックスを使用します.接頭辞インデックスと呼ばれます.接頭辞インデックスは、フィールドデータの最初のn文字をインデックスとして選択します.これにより、インデックス空間を大幅に節約することができます.これにより、インデックスの効率が向上します.
ここに示す文は、フィールドデータの最初の10文字を使用したインデックスを作成するために使用されます.CREATE INDEX part_of_name ON customer (name(10));
フィールドデータの一部を使用してインデックスを作成すると、インデックスファイルが大幅に減少し、ディスク領域が大幅に節約され、INSERT操作の速度が向上する可能性があります.
接頭辞インデックスは、インデックスをより小さく、より速くする有効な方法ですが、MySqlでは、接頭辞インデックスをORDER BYおよびGROUP BYに使用したり、接頭辞インデックスをオーバーライドスキャンに使用したりすることはできません.
ここでまた新しい概念を引き出して、スキャンをカバーします!
結合インデックスなどのインデックスにクエリーするすべてのフィールドの値が含まれている場合は、次のように上書きインデックスと呼ばれます.
SELECT user_name, city, age FROM user_test WHERE user_name = 'feinik' AND age > 25;クエリーするフィールド(user_name,city,age)は、インデックスを組み合わせたインデックス列に含まれているため、インデックスを上書きしてクエリーを上書きし、実行計画のExtraの値がUsing indexであることで、インデックスを上書きすることでアクセス性を大幅に向上させることができます.
1-2)一意のインデックスであり、フィールドのすべての値が一意であることが要求されます.これはプライマリ・キー・インデックスと同じですが、空の値を許可します.
表作成時:UNIQUE INDEX IndexName(`フィールド名`(length))
表作成後:CREATE UNIQE INDEX IndexName ON`Table Name`(`フィールド名`(length))
またはALTER TABLE Table Name ADD UNIQUE INDEX IndexName(`フィールド名`(length))
1～3)プライマリ・キー・インデックス、NULL値は許可されていません
普通は表を建てる時に自動的に作成して、主キーは普通intに設定してしかもAUTO_ですINCREMENT自増タイプの
1-4)全文索引仮定フィールドのデータ型は長いテキストであり,テキストフィールド(textなど)には通常の索引が確立されているが,キーワードを検索する必要がある場合はwhere column like'%xxxx%'しか条件にならないが,これにより索引が失効し,全文索引が必要となる.
表作成時:FULLTEXT INDEX IndexName(`フィールド名`(length))
表作成後:CREATE FULLTEXT INDEX IndexName ON`Table Name`(`フィールド名`(length))
またはALTER TABLE Table Name ADD FULLTEXT INDEX IndexName(`フィールド名`(length))
使用:SELECT*FROM TableNameWHERE MATCH(column 1,column 2)AGAINST(‘xxx’,‘sss’,‘ddd’)このコマンドはcolumn 1とcolumn 2フィールドにxxx,sss,dddがあるデータレコードをすべて検索します.
次に例を挙げます.
文書コンテンツフィールドのデータ型がtextである書籍テーブルがあると仮定する
記事id
記事タイトル
文章の内容
1
スーパーセア人
私はスーパーセア人です.私はりんごが好きです.私は天朝の人でも地球人でもありません.
2
天朝大国
私の大天朝の威武、私の大天朝の13億人、私の大天朝
3
私は水泳が好きです.
水泳にはいい方法がたくさんある.
4
アニメーション
私の息子はアニメーションを见ることが好きで、特にドラゴンボール、中にサイヤ人がいるため、その上サイヤ人はリンゴを食べることが好きで、彼らは地球人ではありません
5
うんどう
私はスポーツが好きで、ランニングが好きで、水泳が好きで、フィットネスが好きで、xxooが好きです
6
砲撃する
私は第二次世界大戦の老兵で、これは私の回顧録で、私の最も幸せな时間は天朝でリンゴを食べて砲を撃つことです
7
..........
..........
8
..........
..........
9
..........
..........
広大な本の内容の中でキーワードを検索したいのですが、%xxx%で検索すると、効率が低すぎます.
記事の内容フィールドに全文インデックスを作成します.次はインデックスファイルです.
キーワード
文章id(参照ポインタ)
セア人
1,4
りんご
1,4,6
天朝
1,2,6
地球
1,4
水泳
3,5
ドラゴンボール
4
好き
1,4,5,6
では、「セア人」を検索したいとき、このインデックスファイルは私に文章idが1と4の文章にこの言葉があることを直接教えてくれました.
しかし、これらのキーワードはどのように抽出されたのでしょうか.これは新しい概念、「分詞」に言及します!分詞はキーワードを抽出することですが、MYSQLのFULLTEXTは分詞に対して知能が足りず、中国語に対してもあまりサポートされていないので、全文インデックスは一般的に使いません.代わりに、
coreseek=sphinx+mmesgというプログラムでこの問題を解決できます.
sphinxはインデックスプログラムです.
mmsegは分詞プログラムです.
国内ではsphinxソースコードを修正した人がいて、内建とmmsegが協力して、統合するとcoreseekです(中国語版sphinx)!
2.結合インデックス仮定フィールドa,bはいずれもインデックスがあり、我々のクエリー条件はa=1であり、b=2クエリープロセスはmysqlがa=1に合致する結果セットを先に選択し、これらの結果セットでb=2の結果セットを選択するが、mysqlはa,bをクエリーする際にインデックスを使用することはなく、そのうちの1つしか使用できないので、我々の予想とは異なるので、組合せインデックスを使用する
表作成時:INDEX IndexName(`フィールド名`(length),`フィールド名`(length),….)
表作成後:CREATE INDEX IndexName ON`Table Name`(`フィールド名`(length),`フィールド名`(length),...)
またはALTER TABLE Table Name ADD INDEX IndexName(`フィールド名`(length),`フィールド名`(length),….)
(二)インデックスの削除
DORP INDEX IndexName ON `TableName`
(三)インデックスが失効した場合
1.条件にorがある場合、条件付きインデックスがあっても使用されません(これもorをできるだけ少なく使う理由です)
orを使用し、インデックスを有効にするには、or条件の各カラムにインデックスを付けるしかありません.
2.クエリーを使用する場合はmysqlコンビネーションインデックスの「最左接頭辞」ルールに従います.コンビネーションインデックス(a,b,c)が現在存在すると仮定すると、クエリー文はa=1またはa=1になります.
and b=1またはa=1 and b=1 and c=1.ここで注意しなければならない2つの点は、1 a=1 and b=1とb=1 and a=1と同様に、区別がなく、インデックス2を組み合わせたインデックス(a,b,c)の最左接頭辞がaであることに注意しなければならない.結合インデックス(c,b,a)の最左接頭辞はcであり、最左接頭辞はテーブルフィールドの順序に関係なく
コンポジットインデックスでwhereクエリー条件のカラムが範囲クエリー(%がどこにあるかにかかわらず)を使用している場合、右側のすべてのカラムはインデックスを使用してクエリーを最適化できません.
3.likeクエリは%で始まる
4.カラムタイプが文字列の場合は、条件でデータを引用符で参照する必要があります.そうしないと、インデックスは使用されません.
5.mysqlがインデックスを使用するよりも全テーブルスキャンを使用するほうが速いと推定した場合、インデックスは使用されません.
6.インデックス列は式の一部ではなく、関数のパラメータとしても使用できません.そうしないと、インデックスクエリは使用できません.次に例を示します.
1
SELECT * FROM user_test WHERE user_name = concat(user_name, 'fei' );