MySQLインデックスについての深い解析
前言
インデックスの選択は最適化器段階の仕事であることは知っていますが、最適化器は万能ではなく、使用するインデックスを誤って選択する可能性があります。一般的な最適化器はインデックスを選択して考慮する要素があります。行の数をスキャンして並べ替えますか？仮テーブルを使用しますか？
explinを使ってsqlを分析します。
explinはとても良い自己測定命令です。explaninを使うことによって、もっと合理的なsql文を書くこととより合理的な索引を作ることに役立ちます。
テーブルフィールド：どのテーブルについて表しますか？
typeフィールド：system、const、eq_reg，ref，range，index，all。普通はランク以上になります。
system、const：クエリーの変数を定数に変換できます。id=1のように。idはメインキーまたは一意キーです。
eq_ref：インデックスにアクセスして、単一の行のデータを返します。通常は接続時に現れます。クエリに使用されるインデックスはプライマリキーまたは一意キーです。
ref：インデックスにアクセスして、ある価値のあるデータを返します。
ランク：インデックスを使用して範囲内の行情報を返します。
index：インデックスの順序で全表スキャンを行います。インデックスがありますが、並べ替えはありません。全表スキャンを行います。
all：全表スキャン
keyフィールド：実際に使用される索引;
key_lenフィールド：使用されるインデックス長（精度を損なわない場合は、長さが短いほど良い）。
refフィールド：インデックスを表示するどの列が使用されましたか？
rowsフィールド：MySQLは検索に必要なデータの行数を考えています。
Extraフィールド：クエリーの追加情報は、主に以下の種類があります。
using index：インデックスを使用しました。
using where：where条件を使用しました。
using tmporary：臨時表を使って現在のクエリーを処理します。
using filesort：orderフィールドのような追加の並べ替えを使用してインデックスがありません。
range checed for eache record(index map:N)：インデックスなしで利用できます。
using index for group-by：テーブル名は、インデックス内でパケットに必要なすべてのデータを見つけることができます。実際のテーブルを調べる必要はありません。
一般的にUsing temporaryとUsing filesortに出会うと、何とか最適化しなければなりません。インデックスが使えないからです。
MySQLはどうやって検索する行数を計算しますか？
実際には、MySQLによって統計されたスキャン行数は正確な値ではなく、場合によってはかなり違っていますが、スキャン行数はインデックスの基数に基づいて計算されます。
MySQLでは、サンプル統計によってインデックス基数を取得します。システムはデフォルトでN個のデータページを選択し、統計データのページに異なる平均値を付け、インデックスのページ数を乗じて基数を取得します。また、MySQLは変更されたデータの行数が1/Mを超えるときに、リセット・ソーティング統計の動作をトリガします。
MySQLにはインデックス統計を格納する2つの方法があり、innodb_を設定することによりstatspersistentパラメータを選択します。
onに設定すると、統計情報が恒久的に保存されます。このとき、デフォルトのNは20、Mは10です。
offに設定した場合、統計情報はメモリにのみ保存されます。このとき、デフォルトのNは8、Mは16です。
一般的には基数統計のデータと実際の行数には大きな差はないが、データの削除に関しては頻繁なデータテーブルがあり、データテーブルが10万件あるかもしれないが、基数統計が20万件ある場合、これはMVCCのせいかもしれません。MySQLのInnoDBの事務サポートは、複数のデータバージョンを維持する必要があります。いくつかの問題がまだ終わっていないかもしれません。まだ削除されたデータを使っています。削除されたデータ空間が解放されなくなりました。新たに追加されたデータは新しい空間が開けられました。この時、基数統計においてデータのページ数にミスが発生する可能性があります。大きなエラーが発生しました。
よく修正された方法は、インデックス情報を再統計するためにanalzeテーブル名を実行します。
インデックスの選択を間違えました。どうすればいいですか？
必要なインデックスを正しく作成した後、ほとんどの場合、最適化器は間違ったインデックスを選択しません。インデックスの選択が間違っている場合、どうやって処理しますか？
1、force indexを使って、あるインデックスを強制的に使用します。
2、考え方を変えて、sql文を最適化すると、そのインデックスに使えるかもしれません。
3、より適切な索引を新規作成したり、誤用された不合理な索引を削除したりする(ある時、本当にこのインデックスは余分で、まだ最適ではないかもしれません。最適化器はちょうどそれを使いました。
締め括りをつける
以上はこの文章の全部の内容です。本文の内容は皆さんの学習や仕事に対して一定の参考学習価値を持ってほしいです。ありがとうございます。
インデックスの選択は最適化器段階の仕事であることは知っていますが、最適化器は万能ではなく、使用するインデックスを誤って選択する可能性があります。一般的な最適化器はインデックスを選択して考慮する要素があります。行の数をスキャンして並べ替えますか？仮テーブルを使用しますか？
explinを使ってsqlを分析します。
explinはとても良い自己測定命令です。explaninを使うことによって、もっと合理的なsql文を書くこととより合理的な索引を作ることに役立ちます。
mysql> explain select * from t where (a between 1 and 1000) and (b between 50000 and 100000) order by b limit 1;
+----+-------------+-------+------------+-------+---------------+------+---------+------+-------+----------+------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+------+---------+------+-------+----------+------------------------------------+
| 1 | SIMPLE | t | NULL | range | a,b | b | 5 | NULL | 50223 | 1.00 | Using index condition; Using where |
+----+-------------+-------+------------+-------+---------------+------+---------+------+-------+----------+------------------------------------+
1 row in set, 1 warning (0.01 sec)テーブルフィールド：どのテーブルについて表しますか？
typeフィールド：system、const、eq_reg，ref，range，index，all。普通はランク以上になります。
system、const：クエリーの変数を定数に変換できます。id=1のように。idはメインキーまたは一意キーです。
eq_ref：インデックスにアクセスして、単一の行のデータを返します。通常は接続時に現れます。クエリに使用されるインデックスはプライマリキーまたは一意キーです。
ref：インデックスにアクセスして、ある価値のあるデータを返します。
ランク：インデックスを使用して範囲内の行情報を返します。
index：インデックスの順序で全表スキャンを行います。インデックスがありますが、並べ替えはありません。全表スキャンを行います。
all：全表スキャン
keyフィールド：実際に使用される索引;
key_lenフィールド：使用されるインデックス長（精度を損なわない場合は、長さが短いほど良い）。
refフィールド：インデックスを表示するどの列が使用されましたか？
rowsフィールド：MySQLは検索に必要なデータの行数を考えています。
Extraフィールド：クエリーの追加情報は、主に以下の種類があります。
using index：インデックスを使用しました。
using where：where条件を使用しました。
using tmporary：臨時表を使って現在のクエリーを処理します。
using filesort：orderフィールドのような追加の並べ替えを使用してインデックスがありません。
range checed for eache record(index map:N)：インデックスなしで利用できます。
using index for group-by：テーブル名は、インデックス内でパケットに必要なすべてのデータを見つけることができます。実際のテーブルを調べる必要はありません。
一般的にUsing temporaryとUsing filesortに出会うと、何とか最適化しなければなりません。インデックスが使えないからです。
MySQLはどうやって検索する行数を計算しますか？
実際には、MySQLによって統計されたスキャン行数は正確な値ではなく、場合によってはかなり違っていますが、スキャン行数はインデックスの基数に基づいて計算されます。
MySQLでは、サンプル統計によってインデックス基数を取得します。システムはデフォルトでN個のデータページを選択し、統計データのページに異なる平均値を付け、インデックスのページ数を乗じて基数を取得します。また、MySQLは変更されたデータの行数が1/Mを超えるときに、リセット・ソーティング統計の動作をトリガします。
MySQLにはインデックス統計を格納する2つの方法があり、innodb_を設定することによりstatspersistentパラメータを選択します。
onに設定すると、統計情報が恒久的に保存されます。このとき、デフォルトのNは20、Mは10です。
offに設定した場合、統計情報はメモリにのみ保存されます。このとき、デフォルトのNは8、Mは16です。
一般的には基数統計のデータと実際の行数には大きな差はないが、データの削除に関しては頻繁なデータテーブルがあり、データテーブルが10万件あるかもしれないが、基数統計が20万件ある場合、これはMVCCのせいかもしれません。MySQLのInnoDBの事務サポートは、複数のデータバージョンを維持する必要があります。いくつかの問題がまだ終わっていないかもしれません。まだ削除されたデータを使っています。削除されたデータ空間が解放されなくなりました。新たに追加されたデータは新しい空間が開けられました。この時、基数統計においてデータのページ数にミスが発生する可能性があります。大きなエラーが発生しました。
よく修正された方法は、インデックス情報を再統計するためにanalzeテーブル名を実行します。
インデックスの選択を間違えました。どうすればいいですか？
必要なインデックスを正しく作成した後、ほとんどの場合、最適化器は間違ったインデックスを選択しません。インデックスの選択が間違っている場合、どうやって処理しますか？
1、force indexを使って、あるインデックスを強制的に使用します。
2、考え方を変えて、sql文を最適化すると、そのインデックスに使えるかもしれません。
3、より適切な索引を新規作成したり、誤用された不合理な索引を削除したりする(ある時、本当にこのインデックスは余分で、まだ最適ではないかもしれません。最適化器はちょうどそれを使いました。
締め括りをつける
以上はこの文章の全部の内容です。本文の内容は皆さんの学習や仕事に対して一定の参考学習価値を持ってほしいです。ありがとうございます。