mysqlスロークエリ解決の道

タスク:本番ライブラリの遅いクエリーログで、1つのクエリーに3 s以上の時間がかかることを示します.解決が必要だ.
 

 1. mysqlスロークエリー

について
mysqlスロークエリー機能は、すべてのデータベースの操作記録を検出することができ、操作時間>設定時間のとき、操作sql文などの情報を記録する.ファイルに保存します.
 
1.1サービスのオープン
 

mysql> show variables like '%slow%';
+---------------------+---------------------------------+
| Variable_name       | Value                           |
+---------------------+---------------------------------+
| log_slow_queries    | OFF                             |
| slow_launch_time    | 2                               |
| slow_query_log      | OFF                             |
| slow_query_log_file | /var/run/mysqld/mysqld-slow.log |
+---------------------+---------------------------------+
4 rows in set (0.00 sec)

off、閉じる、開く(global必須)
 
 

mysql> set global log_slow_queries=1;
Query OK, 0 rows affected, 1 warning (0.15 sec)

オンにすると、slow_query_logは自動的にon.
 
1.2遅い問合せ間隔の設定
変数の表示
 

mysql> show variables like '%long%';
+--------------------+-----------+
| Variable_name      | Value     |
+--------------------+-----------+
| long_query_time    | 10.000000 |
| max_long_data_size | 1048576   |
+--------------------+-----------+

スロークエリのしきい値を2 sに設定.
 

mysql> set global long_query_time=2;
Query OK, 0 rows affected (0.00 sec)

ここで注意しなければならないのは、
1). globalを使用する必要があります.そうしないと、現在のセッション値だけです. 
2). 設定に成功したら、接続を再確立する必要があります.webアプリケーションでは、再起動する必要があります.--ここでは実際には、長い間悩んでいたが、設定後、webアプリケーションで呼び出しを記録することはできなかった.原因はここにある. 
 
1.3スロー・クエリー・ログ

# Time: 130812 18:43:52
# User@Host: epl[epl] @  [192.168.1.73]
# Query_time: 4.644414  Lock_time: 0.000250 Rows_sent: 0  Rows_examined: 1226476
SET timestamp=1376333032;
select                                                         
   	distinct a.id as id ,                                                 
.................

そのうち、Query_time: 4.644414. このクエリを説明する時間は4.6以上である. 
 

2. mysql分析ツール

を使用
2.1 explain使用
explain +sql. このsqlの実行順序、インデックスの使用、テーブルデータのスキャンなどを分析することができる.
具体的に関連資料を検索することができて、explainの結果、すべて最適化に対して重要な参考作用がある.例えばselect type,type,extra...
例えば:http://www.cnitblog.com/aliyiyi08/archive/2008/09/09/48878.html
 

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	b	index	idx_content_lang	idx_content_lang	5		3340	Using where; Using index; Using temporary; Using filesort
1	PRIMARY	a	eq_ref	PRIMARY	PRIMARY	4	slow_test.b.contentId	1	Using where
3	DEPENDENT SUBQUERY	p	ALL	PRIMARY				1681	Using where; Using temporary
3	DEPENDENT SUBQUERY	pc	eq_ref	idx_product_content	idx_product_content	8	slow_test.p.id,func	1	Using where
2	DEPENDENT SUBQUERY	pc	eq_ref	idx_product_content	idx_product_content	8	const,slow_test.a.id	1	Using index

その中から、いくつかの重要な情報があります.
typeは、id=3、table=p、のtype=ALLが見える、全テーブルスキャンを示す.sqlからpをクエリする文は次のとおりです.

 and a.id in(
		    	select distinct pc.contentId from  product_content pc,ssports.product p 
		    	where pc.productId=p.id and p.state='reviewed'
		    	and p.status = 0
		    	and pc.status = 0
		    )

以上、サブクエリを使用した.(mysqlサブクエリの効率に関する問題は単独でgoogleすることができる).
サブクエリはテンポラリテーブル(Extra情報のUsing temporary)を使用する.
一方、テーブル構造を見ると、contentIdはテーブルpにインデックスを付けていない. 
 

3. 解決策

contentIdはインデックスを追加します.
テストにより、クエリーは0.1 s以内である. 
問題が初歩的に解決する
またprofile.しかし、疑問の問題に直面した.また今度分かち合います.
 

4. 問題まとめ

これまでsql方面の最適化をしたことがなくて、一日の資料を調べて勉強しました.最終的に問題を解決した.プログラマーとしての達成感を味わう.しかしmysqlの最適化はまだ広い.
この文章はmyslqが最適化した氷山の一角にすぎない. 
Googleの下でmysqlの最適化、多くの良い文章があります.ゆっくり勉強しなければなりません.
 
http://www.oicto.com/mysql-explain-show/