無視される可能性のあるMySQLの最適化テクニック
説明:本明細書の内容はMySQL 5に適用する.1-5.6バージョンは、新しいバージョンで依然として適用されることを保証しません.ほとんどの一般的なアプリケーションシーンでのみ、実際のビジネスデータに基づくテストに基づいて効果があるかどうかについて説明します.
1優先的にカラムをNOT NULLに設定
NULLを許可するカラムは、より多くのディスク領域を占有するだけでなく、クエリ・アナライザのSQL文の最適化にも影響します.ビジネス・シーンが許可されている場合は、NOT NULLとして優先的に設定し、空白文字列、-1などのデフォルト値を指定します.
2浮動小数点数タイプの代わりに整数を使用
DEMICALは高い精度を持っているが,計算効率も占有空間もFLOAT,DOUBLEより劣っているが,後者は非常に精度の高いデータには適用されない.効率と精度を両立させることで、浮動小数点数を整数に変換することができます.例えば、小数点以下7ビットのデータに1000000を乗算し、表示時にアプリケーションで変換します.
3優先的にインデックスを一意に設定
1つのカラムの内容が重複するローが表示されず、インデックスを作成する必要があることが知られている場合は、エラーを防止するだけでなく、クエリー・アナライザに明確に通知する必要があります.一部のクエリーでは、条件に合致するレコードを見つけてスキャンを終了することができます.
4ハッシュインデックスの使用
長い文字列で正確に検索する場合は、インデックスを直接作成するのは最善の方法ではありません.これにより、このクエリなど、より多くのディスク領域とインデックス効率が低下する可能性があります.
select url from myurls where url='http://blog.csdn.net/autfish/article/details/51660864';
応用面からの最適化を考慮すると、myurlsテーブルにint列hashurlを追加し、レコードを挿入する際に一定のハッシュアルゴリズムでurlのハッシュ値を計算し、hashurl列に記入し、その列にインデックスを確立することができる.
クエリ文を次のように変更します.
select url from myurls where hashurl=3346369 and url='http://blog.csdn.net/autfish/article/details/51660864';
hashurlのインデックスにより、条件に合致しないローの大部分がフィルタリングされ、後続の正確なマッチングによりハッシュ競合の問題が解決されます.
5接頭辞インデックスの使用
この問題(長い文字列にインデックスを作成する)では、最初の文字の一部のみにインデックスを作成することが最適化されています.たとえば、次のようになります.
alter table myurls add key(url(10));
もちろん、この方法はこの例には適していません.ウェブサイトの多くがhttpで始まるため、このインデックスの大部分の内容はフィルタリングの役割を果たしません.
高性能MySQL本では、インデックス文字数を決定する方法を紹介しています.まず、列の完全な内容に対してgroup byをして各値の分布状況を統計し、その後、前のn文字を取ってgroupby操作を行い、各値の分布の数字が完全な内容の分布の数字に近づくまで徐々に文字数を増やします.
6逆順格納文字列
一部のビジネスシーンでは、すべてのqqメールボックスユーザーをクエリーするなど、ワイルドカード%で始まる値を頻繁にクエリー条件として使用します.
select * ffrom emails where email like '%qq.com'
このようなクエリー・インデックスは機能しないことを知っています.この場合、文字列を逆シーケンスするなど、アプリケーション・レベルで解決できます[email protected]に変換する.qq@321を選択します.
select * from emails where email like 'moc.qq@%';
7 ENUMタイプの使用
たとえば
8文字列タイプを整数に変換
いくつかの特殊なタイプの文字列は、IPおよび16進数形式の文字列が一般的な整数記憶に変換することができ、典型的な応用はMD 5コードである.
変換IP:
select inet_aton('192.168.0.1');
select inet_ntoa(3232235521);
変換16進数:
select hex('a0b1');
select unhex(61306231); 9遅延関連付けの使用
遅延関連を理解する前提は、インデックスの上書きを理解することです.インデックスの上書きは、selectのすべてのカラムがインデックスで直接取得できることを意味します.たとえば、次のようにします.
select name,sex,birthday from users where birthday>'2000-01-01';
インデックスがindex(name,sex,birthday)である場合、クエリを実行するときにテーブルストレージに戻ってデータを読み出す必要はありません.通常、上書きインデックスと呼ばれ、非常に効率的です.
しかし、多くの場合、このような良いことはありません.ビジネスでは、すべてのフィールドの十中八九を取り出す必要があるか、方法の多重化のために直接select*を使用する必要がある場合があります.この場合、遅延関連付けを使用してみてください.
select * from mytable join(select id from mytable where ...) as t1 on(t1.id=mytable.id)
これはInnoDBを利用した非クラスタリングインデックスがプライマリ・キーを含む特性で実行計画の一部にインデックス・オーバーライドを使用しているためであり,もちろん実行効率を向上させることができるかどうかは具体的な業務にもよるが,一概には論じられない.
10 explainを使用した近似値
大量のデータでページングするシーンでは、完全に正確な総数の代わりに近似値を選択する場合があります.近似値をどのように取得するか、explainコマンドで推定したrowsは良い案です.explainを実行するには、実際にクエリーを実行する必要はありません.コストは低いです.
合計が近似値の場合、「次のページ」ボタンが表示されるかどうかを決定するにはどうすればいいですか?次のページの内容を読み取るたびに、必要な行数よりも1行多く読むことができます.例えば、ページごとに20行、新しいページの内容を読むたびに21行を読み、21条目が存在する場合は次のページを表示し、存在しない(または20条未満)場合は表示しません.
1優先的にカラムをNOT NULLに設定
NULLを許可するカラムは、より多くのディスク領域を占有するだけでなく、クエリ・アナライザのSQL文の最適化にも影響します.ビジネス・シーンが許可されている場合は、NOT NULLとして優先的に設定し、空白文字列、-1などのデフォルト値を指定します.
2浮動小数点数タイプの代わりに整数を使用
DEMICALは高い精度を持っているが,計算効率も占有空間もFLOAT,DOUBLEより劣っているが,後者は非常に精度の高いデータには適用されない.効率と精度を両立させることで、浮動小数点数を整数に変換することができます.例えば、小数点以下7ビットのデータに1000000を乗算し、表示時にアプリケーションで変換します.
3優先的にインデックスを一意に設定
1つのカラムの内容が重複するローが表示されず、インデックスを作成する必要があることが知られている場合は、エラーを防止するだけでなく、クエリー・アナライザに明確に通知する必要があります.一部のクエリーでは、条件に合致するレコードを見つけてスキャンを終了することができます.
4ハッシュインデックスの使用
長い文字列で正確に検索する場合は、インデックスを直接作成するのは最善の方法ではありません.これにより、このクエリなど、より多くのディスク領域とインデックス効率が低下する可能性があります.
select url from myurls where url='http://blog.csdn.net/autfish/article/details/51660864';
応用面からの最適化を考慮すると、myurlsテーブルにint列hashurlを追加し、レコードを挿入する際に一定のハッシュアルゴリズムでurlのハッシュ値を計算し、hashurl列に記入し、その列にインデックスを確立することができる.
クエリ文を次のように変更します.
select url from myurls where hashurl=3346369 and url='http://blog.csdn.net/autfish/article/details/51660864';
hashurlのインデックスにより、条件に合致しないローの大部分がフィルタリングされ、後続の正確なマッチングによりハッシュ競合の問題が解決されます.
5接頭辞インデックスの使用
この問題(長い文字列にインデックスを作成する)では、最初の文字の一部のみにインデックスを作成することが最適化されています.たとえば、次のようになります.
alter table myurls add key(url(10));
もちろん、この方法はこの例には適していません.ウェブサイトの多くがhttpで始まるため、このインデックスの大部分の内容はフィルタリングの役割を果たしません.
高性能MySQL本では、インデックス文字数を決定する方法を紹介しています.まず、列の完全な内容に対してgroup byをして各値の分布状況を統計し、その後、前のn文字を取ってgroupby操作を行い、各値の分布の数字が完全な内容の分布の数字に近づくまで徐々に文字数を増やします.
6逆順格納文字列
一部のビジネスシーンでは、すべてのqqメールボックスユーザーをクエリーするなど、ワイルドカード%で始まる値を頻繁にクエリー条件として使用します.
select * ffrom emails where email like '%qq.com'
このようなクエリー・インデックスは機能しないことを知っています.この場合、文字列を逆シーケンスするなど、アプリケーション・レベルで解決できます[email protected]に変換する.qq@321を選択します.
select * from emails where email like 'moc.qq@%';
7 ENUMタイプの使用
たとえば
create table mytable
(id int not null auto_increment,
sex enum('m','f') not null default 'm',
primary key(id)
);8文字列タイプを整数に変換
いくつかの特殊なタイプの文字列は、IPおよび16進数形式の文字列が一般的な整数記憶に変換することができ、典型的な応用はMD 5コードである.
変換IP:
select inet_aton('192.168.0.1');
select inet_ntoa(3232235521);
変換16進数:
select hex('a0b1');
select unhex(61306231); 9遅延関連付けの使用
遅延関連を理解する前提は、インデックスの上書きを理解することです.インデックスの上書きは、selectのすべてのカラムがインデックスで直接取得できることを意味します.たとえば、次のようにします.
select name,sex,birthday from users where birthday>'2000-01-01';
インデックスがindex(name,sex,birthday)である場合、クエリを実行するときにテーブルストレージに戻ってデータを読み出す必要はありません.通常、上書きインデックスと呼ばれ、非常に効率的です.
しかし、多くの場合、このような良いことはありません.ビジネスでは、すべてのフィールドの十中八九を取り出す必要があるか、方法の多重化のために直接select*を使用する必要がある場合があります.この場合、遅延関連付けを使用してみてください.
select * from mytable join(select id from mytable where ...) as t1 on(t1.id=mytable.id)
これはInnoDBを利用した非クラスタリングインデックスがプライマリ・キーを含む特性で実行計画の一部にインデックス・オーバーライドを使用しているためであり,もちろん実行効率を向上させることができるかどうかは具体的な業務にもよるが,一概には論じられない.
10 explainを使用した近似値
大量のデータでページングするシーンでは、完全に正確な総数の代わりに近似値を選択する場合があります.近似値をどのように取得するか、explainコマンドで推定したrowsは良い案です.explainを実行するには、実際にクエリーを実行する必要はありません.コストは低いです.
合計が近似値の場合、「次のページ」ボタンが表示されるかどうかを決定するにはどうすればいいですか?次のページの内容を読み取るたびに、必要な行数よりも1行多く読むことができます.例えば、ページごとに20行、新しいページの内容を読むたびに21行を読み、21条目が存在する場合は次のページを表示し、存在しない(または20条未満)場合は表示しません.