高性能MySQL-クエリパフォーマンスの最適化
14891 ワード
本記事の出典は『高性能MySQL』
一、クエリーの良し悪しをどうチェックするか
クエリーのパフォーマンスが低下する最も基本的な原因は、アクセスするデータが多すぎることです.非効率的なクエリの原因は次の2つです.
1.不要なデータを大量に取得します.
2.MySQLサービス層は、必要以上のデータ行を大量に分析しています.
1.不要なデータを大量に取得
1.1不要なレコードの問合せ
よくあるエラーは、MySQLが必要なデータだけを返すと勘違いしていることです.実際には、MySQLはすべての結果セットを返してから演算します.e.g.ページに10個のデータを表示するだけであれば、結果セット全体を検出して捨てるのではなく、LIMITを追加するのが最も簡単で効果的です.
1.2余分な列を返す
高性能MySQL本では、これを複数のテーブルに分割して関連付けると、すべての列を返し、常にすべての列を取り出します.個人的な感覚は一列にまとめることができる.複数のテーブルが関連付けられている場合、テーブルの1つだけのデータが必要な場合は、「SELECTテーブル名.*「直接*を使用しないでください.select*を使用するときは、本当にすべてのデータが必要かどうかに注意してください.すべての列を取り出すと、オプティマイザはインデックスオーバーライドスキャンのような最適化を完了できず、サーバに追加のI/O、メモリ、CPUの消費をもたらします.
1.3同じデータを繰り返す
これは主に開発者が注意しなければならないことで、あるモジュールを開発して、例えばコメントを開発する時、ユーザーの顔のURLを検索する必要があり、ユーザーが何度もコメントした時、クエリーを繰り返す可能性があります.変数ストレージを使用することが好ましく、使用頻度が特に高く、session、cookieで保存することもできる.
2.MySQLは追加のレコードをスキャンする
MySQLは、クエリのオーバーヘッドを簡単に測定する3つの指標です.
2.1応答時間
応答時間にはサービス時間と待機時間が含まれますが、この2つの時間は細分化されていないため、応答時間の影響が大きいです.クエリの応答時間を推定することで、最も初歩的な判断を行うことができます.
2.2スキャンの行数
2.3返されるローの数
二、MySQLクエリ実行基礎
MySQLクライアントとサービス側の通信プロトコルは「半二重」であり、これは、クライアントがサーバとサーバに送信してクライアントに送信することは同時に発生できないことを意味し、このプロトコルはMySQL通信を簡単かつ迅速にするが、トラフィック制御を行うことができず、一端が開始されると、他端が終了するのを待つことができる.したがって、クエリ文が長い場合、パラメータmax_allowed_packetは特に重要です.
MySQLがリクエストを送信したいとき、MySLQはいったい何をしましたか.
1.クライアントがサーバにクエリーを送信します.
2.サーバはクエリー・キャッシュをチェックし、ヒットした場合、すぐにキャッシュに格納された結果を返します.そうしないと、次のフェーズに進みます.
3.MySQLは、オプティマイザが生成した実行計画に基づいて、ストレージエンジンのAPIを呼び出してクエリーを実行します.
4.クエリの結果をクライアントに返す.
MySQL(オプティマイザ)が処理できる最適化タイプ:
1.関連テーブルの順序を再定義します.
2.外部ジョインを内部ジョインに変換します.
3.等価変換ルールを使用します.
いくつかの恒成立といくつかの恒不成立の判読を除去することができる.例えば(5=5 AND a>5)=>a>5
4.COUNT()、MIN()、MAX()の最適化
たとえば、あるカラムの最小値を見つけるには、B-Treeインデックスの左端に対応するレコードをクエリーするだけです.
5.インデックススキャンの上書き
インデックス内のカラムにすべてのクエリに必要なカラムが含まれている場合は、インデックスを使用してデータを返すだけで、データ行を検索する必要はありません.
6.サブクエリの最適化
MySQLは、サブクエリをより効率的な形式に変換し、複数のクエリが複数回データにアクセスできるようにします.
7.クエリーの早期終了
クエリーの要件が満たされていることに気づいたとき、MySQLは常にすぐにクエリーを終了することができます.
8.等値伝播
2つのカラムの値が等式で関連付けられている場合、MySQLはその1つのカラムのWHERE条件を別のカラムに渡すことができます.
9.リストIN()の比較
MySQLではIN()リストのデータ線を並べ替え、二分検索でリストの値が条件を満たすかどうかを決定し、O(lgn)レベルの操作であり、ORクエリに等価に変換する複雑さはO(n)である
MySQLはどのように関連付けを実行しますか?
MySQLでの関連付けは、1つのクエリーが2つのテーブルに一致する必要があるだけでなく、各クエリー、各セグメント(サブクエリー、さらには単一テーブルベースのSELECTを含む)が関連付けられている可能性があります.
MySQLはネストされたループ関連付け操作で関連付けられます.
具体的なやり方:1.MySQLは、まず1つのテーブルから1つのデータをループして取り出し、次にネストされたループを次のテーブルに一致するローを探して、すべてのテーブルで一致するローまで順番に下ります.
2.各テーブルが一致するローに基づいて、クエリーに必要なカラムを返します.
MySQLは、最後の関連テーブルで一致するすべてのローを見つけようとします.最後の関連テーブルでより多くのローが見つからない場合、MySQLは前の階層の関連テーブルに戻ります.見つかるかどうか見て、順番に類推します.
三、MySQLクエリーオプティマイザの限界
1.関連サブクエリ
MySQLのサブクエリは非常に悪いです.最悪のクラスのクエリは、WHERE条件にIN()を含むサブクエリ文です.
e.g.
一、クエリーの良し悪しをどうチェックするか
クエリーのパフォーマンスが低下する最も基本的な原因は、アクセスするデータが多すぎることです.非効率的なクエリの原因は次の2つです.
1.不要なデータを大量に取得します.
2.MySQLサービス層は、必要以上のデータ行を大量に分析しています.
1.不要なデータを大量に取得
1.1不要なレコードの問合せ
よくあるエラーは、MySQLが必要なデータだけを返すと勘違いしていることです.実際には、MySQLはすべての結果セットを返してから演算します.e.g.ページに10個のデータを表示するだけであれば、結果セット全体を検出して捨てるのではなく、LIMITを追加するのが最も簡単で効果的です.
1.2余分な列を返す
高性能MySQL本では、これを複数のテーブルに分割して関連付けると、すべての列を返し、常にすべての列を取り出します.個人的な感覚は一列にまとめることができる.複数のテーブルが関連付けられている場合、テーブルの1つだけのデータが必要な場合は、「SELECTテーブル名.*「直接*を使用しないでください.select*を使用するときは、本当にすべてのデータが必要かどうかに注意してください.すべての列を取り出すと、オプティマイザはインデックスオーバーライドスキャンのような最適化を完了できず、サーバに追加のI/O、メモリ、CPUの消費をもたらします.
1.3同じデータを繰り返す
これは主に開発者が注意しなければならないことで、あるモジュールを開発して、例えばコメントを開発する時、ユーザーの顔のURLを検索する必要があり、ユーザーが何度もコメントした時、クエリーを繰り返す可能性があります.変数ストレージを使用することが好ましく、使用頻度が特に高く、session、cookieで保存することもできる.
2.MySQLは追加のレコードをスキャンする
MySQLは、クエリのオーバーヘッドを簡単に測定する3つの指標です.
2.1応答時間
応答時間にはサービス時間と待機時間が含まれますが、この2つの時間は細分化されていないため、応答時間の影響が大きいです.クエリの応答時間を推定することで、最も初歩的な判断を行うことができます.
2.2スキャンの行数
2.3返されるローの数
二、MySQLクエリ実行基礎
MySQLクライアントとサービス側の通信プロトコルは「半二重」であり、これは、クライアントがサーバとサーバに送信してクライアントに送信することは同時に発生できないことを意味し、このプロトコルはMySQL通信を簡単かつ迅速にするが、トラフィック制御を行うことができず、一端が開始されると、他端が終了するのを待つことができる.したがって、クエリ文が長い場合、パラメータmax_allowed_packetは特に重要です.
MySQLがリクエストを送信したいとき、MySLQはいったい何をしましたか.
1.クライアントがサーバにクエリーを送信します.
2.サーバはクエリー・キャッシュをチェックし、ヒットした場合、すぐにキャッシュに格納された結果を返します.そうしないと、次のフェーズに進みます.
3.MySQLは、オプティマイザが生成した実行計画に基づいて、ストレージエンジンのAPIを呼び出してクエリーを実行します.
4.クエリの結果をクライアントに返す.
MySQL(オプティマイザ)が処理できる最適化タイプ:
1.関連テーブルの順序を再定義します.
2.外部ジョインを内部ジョインに変換します.
3.等価変換ルールを使用します.
いくつかの恒成立といくつかの恒不成立の判読を除去することができる.例えば(5=5 AND a>5)=>a>5
4.COUNT()、MIN()、MAX()の最適化
たとえば、あるカラムの最小値を見つけるには、B-Treeインデックスの左端に対応するレコードをクエリーするだけです.
5.インデックススキャンの上書き
インデックス内のカラムにすべてのクエリに必要なカラムが含まれている場合は、インデックスを使用してデータを返すだけで、データ行を検索する必要はありません.
6.サブクエリの最適化
MySQLは、サブクエリをより効率的な形式に変換し、複数のクエリが複数回データにアクセスできるようにします.
7.クエリーの早期終了
クエリーの要件が満たされていることに気づいたとき、MySQLは常にすぐにクエリーを終了することができます.
8.等値伝播
2つのカラムの値が等式で関連付けられている場合、MySQLはその1つのカラムのWHERE条件を別のカラムに渡すことができます.
9.リストIN()の比較
MySQLではIN()リストのデータ線を並べ替え、二分検索でリストの値が条件を満たすかどうかを決定し、O(lgn)レベルの操作であり、ORクエリに等価に変換する複雑さはO(n)である
MySQLはどのように関連付けを実行しますか?
MySQLでの関連付けは、1つのクエリーが2つのテーブルに一致する必要があるだけでなく、各クエリー、各セグメント(サブクエリー、さらには単一テーブルベースのSELECTを含む)が関連付けられている可能性があります.
MySQLはネストされたループ関連付け操作で関連付けられます.
具体的なやり方:1.MySQLは、まず1つのテーブルから1つのデータをループして取り出し、次にネストされたループを次のテーブルに一致するローを探して、すべてのテーブルで一致するローまで順番に下ります.
2.各テーブルが一致するローに基づいて、クエリーに必要なカラムを返します.
MySQLは、最後の関連テーブルで一致するすべてのローを見つけようとします.最後の関連テーブルでより多くのローが見つからない場合、MySQLは前の階層の関連テーブルに戻ります.見つかるかどうか見て、順番に類推します.
三、MySQLクエリーオプティマイザの限界
1.関連サブクエリ
MySQLのサブクエリは非常に悪いです.最悪のクラスのクエリは、WHERE条件にIN()を含むサブクエリ文です.
e.g.
SELECT * FROM sakia.film WHERE film_id IN( SELECT film_id FROM sakia.film_actor WHERE actor_d = 1)
MySQL ， ， ， ，MySQL ， ：
SELECT * FROM sakia.film WHERE EXIST (SELECT * FROM sakia.film_actor WHERE actor_id = 1 AND film_actor.film_id = film.film_id);
MySQL ， ， 。
：
1.
SELECT film.* FROM sakila.film INNER JOIN sakila.film_actor USING(film_id) WHERE actor_id = 1;
2.
SELECT *FROM sakia.film WHERE EXISTS( SELECT * FROM sakia.film_actor WHERE actor_id = 1 AND film_actor.film_id = film.film_id);
， ， ， ， 。
EXPLAIN SELECT film_id, language_id FROM sakila.film WHERE NOT EXISTS(
SELECT * FROM sakia.film_actor
WHERE film_actor.film.id = film.film_id
);
， 。
、
1. COUNT()
COUNT ：
1. COUNT （ NULL）。
2. COUNT(*) 。 ， COUNT(*)， ， 。
：MyISAM COUNT() ， WHERE COUNT(*) ， 。MySQL 。 WHERE ， ，MyISAM COUNT() 。
PS： COUNT MyISAM 。 ， SELECT COUNT(*) FROM world.City WHERE ID > 5; SELECT (SELECT COUNT(*) FROM world.City - COUNT(*) FROM world.City WHERE ID <= 5;
， EXPLAIN ， 。 30 ， 。
count() ， 。 MySQL ， ， 。
2.
1. ON USING 。 。 A B c ， B、A， B 。 。
2. GROUP BY ORDER BY ， MySQL 。
3.
， MySQL 。 ， 。
4. GROUP BY DISTINCT
MySQL 。 ，GROUP BY ： 。
SELECT actor.first_name, actor.last_name,COUNT(*)
FROM sakila.film_actor
INNER JOIN sakia.actor USING(actor_id)
GROUP BY actor.first_name, actor.last_name;
SELECT actor.first_name, actor.last_name, COUNT(*)
FROM sakia.film_actor
INNER JOIN sakila.actor USING(actor_id)
GROUP BY film_actor.actor_id;
SQL 。
5. LIMIT
LIMIT ， ORDER BY 。 ， 。
， ，LIMIT 1000,20 ， 10020 20 ， 10000 ， 。 ， 。 ：
SELECT film_id, description FROM sakila.film ORDER BY title LIMIT 50，5；
， ：
SELECT film.film_id,film.description
FROM sakia.film
INNER JOIN (
SELECT film_id FROM sakia.film
ORDER BY LIMIT 50,5
) AS lim USING(film_id);
， MySQL 。
LIMIT ， MySQL 。 ， ， ， 。
6. UNION
， UNION ALL， 。 ALL ，MySQL DISTINCT ， 。
《 MySQL》 ， ， , http://database.51cto.com/art/201407/445934.htm。
1. ， ， where order by 。
2. where null ， 。 ， NULL 。
3. where != <> ， 。
4.
where or ， union all
5. in not in ， ， between in， exists ， ：
SELECT num FROM a WHERE num in(SELECT num FROM b)
exists ：
SELECT num FROM a WHERE EXISTS (SELECT 1 FROM b WHERE num = a.num)
6. ， 。
7. where ， ，
SELECT id FROM t wiith( index( )) WHERE num = @num
8. where 。
9. where ， 。 ：
SELECT id from t where datediff(day,createdate,'2015-11-30') = 0
：
SELECT id FROM t where createdate >= '2015-11-30' and createdate < '2015-12-1'
10. Update ， 1、2 ，
Update
， ， 。
11.
（ ） JOIN， JOIN， ， 。
12 . ， ， 1 ， 。