MYSQL学習ノート——sql文最適化ツール

前にmysqlの基礎知識をたくさん説明しましたが、この章ではmysqlの文の最適化について説明します.
一、位置付けが遅いクエリー
私たちはsql文を最適化します.最初のステップは実行速度の遅い文を見つけることです.では、どのようにして1つのプロジェクトの中でこれらの実行速度の遅いsql文を位置決めしますか?次に、遅いクエリーを位置決めする方法について説明します. 
1.1、データベース準備
まず、データベース・テーブルを作成します.

CREATE TABLE emp
(empno  MEDIUMINT UNSIGNED  NOT NULL  DEFAULT 0 COMMENT '  ',
ename VARCHAR(20) NOT NULL DEFAULT "" COMMENT '  ',
job VARCHAR(9) NOT NULL DEFAULT "" COMMENT '  ',
mgr MEDIUMINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '    ',
hiredate DATE NOT NULL COMMENT '    ',
sal DECIMAL(7,2)  NOT NULL COMMENT '  ',
comm DECIMAL(7,2) NOT NULL COMMENT '  ',
deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '    '
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

次に、パラメータnの長さのランダム文字列を返すストレージ関数を構築します.

delimiter $$

create function rand_string(n INT) 
returns varchar(255) #           
begin 
	declare chars_str varchar(100) default 'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
	declare return_str varchar(255) default '';
	declare i int default 0;
 	while i < n do 
   	    set return_str =concat(return_str,substring(chars_str,floor(1+rand()*52),1));
        set i = i + 1;
 	end while;
 	return return_str;
end $$

delimiter ;

次に、ランダムint値を返すストレージ関数を作成します.

delimiter $$

create function rand_num( )
returns int(5)
begin 
 declare i int default 0;
 set i = floor(10+rand()*500);
return i;
  end $$

delimiter ;

次に、作成したばかりの2つのストレージ関数を使用して、データテーブルempを挿入するデータバーの数を表すパラメータを含むストレージ・プロシージャを作成します.

delimiter $$

create procedure insert_emp(in max_num int(10))
begin
declare i int default 0; 
 set autocommit = 0;  
 repeat
 set i = i + 1;
 insert into emp values (i ,rand_string(6),'SALESMAN',0001,curdate(),2000,400,rand_num());
  until i = max_num
 end repeat;
   commit;
 end $$

delimiter ;

最後に、変更によって作成されたストレージ・プロシージャを呼び出し、empテーブルに1000 wのデータを挿入します.

call insert_emp(10000000);

1.2、遅い問合せの表示
次のコマンドを使用して、遅いクエリーの回数を表示できます.

show status like 'slow_queries';

mysqlでこのコマンドを入力すると、valueが1であることがわかります.この遅いクエリーは1000 wのデータを一括挿入したばかりで生成されます.
このコマンドを使用すると、遅いクエリーの回数しか表示できませんが、どのクエリーが遅いクエリーを生成したのか、どのクエリーが遅いクエリーを生成したのかを知るにはmysqlのプロファイルを変更する必要があります.mysqlのプロファイル(windowsシステムはmy.ini、linuxシステムはmy.cnf)を開き、[mysqld]の下に次のコードを追加します.

log-slow-queries=mysql_slow.log
long_query_time=1

mysqlで次のコマンドを実行します.slow_が表示されます.query_logはON状態、log_fileも私たちが指定したファイルです.

mysql> show variables like 'slow_query%';  
+---------------------+------------------------------+
| Variable_name       | Value                        |
+---------------------+------------------------------+
| slow_query_log      | ON                           |
| slow_query_log_file | mysql_slow.log |
+---------------------+------------------------------+
2 rows in set (0.00 sec)

次のコマンドを実行すると、設定した遅いクエリー時間も有効になります.クエリー時間が1 sより大きい場合、クエリー文はログファイルに格納されます.

mysql> show variables like 'long_query_time';  
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 1.000000 |
+-----------------+----------+
1 row in set (0.00 sec)

クエリー時間が1 sを超えるクエリー文を実行します.

mysql> select * from emp where empno=413345;
+--------+--------+----------+-----+------------+---------+--------+--------+
| empno  | ename  | job      | mgr | hiredate   | sal     | comm   | deptno |
+--------+--------+----------+-----+------------+---------+--------+--------+
| 413345 | vvOHUB | SALESMAN |   1 | 2014-10-26 | 2000.00 | 400.00 |     11 |
+--------+--------+----------+-----+------------+---------+--------+--------+
1 row in set (6.55 sec)

次にmysqlインストールディレクトリの下のdataディレクトリを表示します.このディレクトリは遅いクエリーログファイルを生成します.mysql_slow.log、このファイルの内容は以下の通りです.

/usr/local/mysql/bin/mysqld, Version: 5.1.73-log (MySQL Community Server (GPL)). started with:
Tcp port: 3306  Unix socket: /tmp/mysql.sock
Time                 Id Command    Argument
# Time: 141026 23:24:08
# User@Host: root[root] @ localhost []
# Query_time: 6.547536  Lock_time: 0.002936 Rows_sent: 1  Rows_examined: 10000000
use temp;
SET timestamp=1414337048;
select * from emp where empno=413345;

このログ・ファイルでは、遅いクエリーが生成された時間、最終的に何行の結果が生成されたか、何行の結果がテストされたか、文が実行されたかを知ることができます.ここでは,この文は結果を生成するが,1000 w行のレコードが検出され,全テーブルスキャンであることが分かる.
二、Explain実行計画
遅いクエリー・ログは、クエリー時間が長すぎるすべてのsql文を記録するのに役立ちます.これらの文を最適化する前に、explainコマンドを使用してmysqlの実行計画を表示し、最適化可能な点を探す必要があります.
explainコマンドの使用は非常に簡単で、「explain+sql文」だけでいいです.次のコマンドは、さっきの遅いクエリー文に対してexplainを使用した後の結果です.

mysql> explain select * from emp where empno=413345\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: emp
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 10000351
        Extra: Using where
1 row in set (0.00 sec)

ERROR: 
No query specified

explainコマンドの結果は、id,select_の列で表示されます.type, table, type, possible_keys, key, key_len,ref,rows,Extra,これらの列はそれぞれ以下の意味を表す.
1、id:SELECT識別子.これはSELECTの検索シーケンス番号です.
      2、select_type:クエリーのタイプは、主にPRIMARY(サブクエリーの最外層クエリー)、SUBQUERY(サブクエリーの内層の最初のSELECT)、UNION(UNION文の2番目のSELECT開始後のすべてのSELECT)、SIMPLEE(サブクエリーまたはunion以外のクエリー)があります.
3、table:アクセスしたデータベースの表示;
4、type:テーブルへのアクセス方法、以下のタイプall(全テーブルスキャン)、index(全インデックススキャン)、rang(インデックス範囲スキャン)、ref(join文で駆動テーブルインデックス参照クエリー)、eq_ref(プライマリ・キーまたは一意のインデックスでアクセスすると、最大1つの結果しか得られません)、const(定数を読み、1回だけ読む)、system(システム・テーブル.テーブルに1つのデータしかありません)、null(速度が最も速い).
      5、possible_keys:使用可能なインデックスをクエリーします.
6、key:最後に選択したインデックス;
      7、key_len:インデックスの最大長を使用します.
8、ref:あるテーブルのフィールドフィルタをリストする.
9、rows:推定した結果の行数;
10、extra:詳細情報をクエリーします.distinct、using filesort(order by操作)、using index(indexでのみ取得できるデータ)、using temporary(テンポラリ・テーブルを使用する)、using where(whereが含まれ、インデックスだけではコンテンツを取得できない場合、この情報が含まれます).
これにより、「explain select*from emp where empno=413345G」コマンドの出力により、このクエリー文は全テーブルスキャン文であり、クエリー時にインデックスが使用されないため、クエリー時間が遅いことが明らかになります.
三、Profilingの使用
mysqlでは、explainコマンドを使用してコマンド実行計画を表示するほか、文クエリー中のリソース消費量を表示するprofilingツールも用意されています.まず、次のコマンドを使用してProfiling機能をオンにします.

set profiling = 1;

次に、クエリーコマンドを実行します.

mysql> select * from emp where empno=413345;
+--------+--------+----------+-----+------------+---------+--------+--------+
| empno  | ename  | job      | mgr | hiredate   | sal     | comm   | deptno |
+--------+--------+----------+-----+------------+---------+--------+--------+
| 413345 | vvOHUB | SALESMAN |   1 | 2014-10-26 | 2000.00 | 400.00 |     11 |
+--------+--------+----------+-----+------------+---------+--------+--------+
1 row in set (6.44 sec)

Query Profiler機能がオンになると、MySQLは実行中のすべてのQueryのprofile情報を自動的に記録します.次に、システムに保存されているすべてのQueryのprofileサマリー情報を次のコマンドで取得します.

mysql> show profiles;
+----------+------------+--------------------------------------+
| Query_ID | Duration   | Query                                |
+----------+------------+--------------------------------------+
|        1 | 0.00053000 | show tables                          |
|        2 | 0.07412700 | select * from dept                   |
|        3 | 0.06743300 | select * from salgrade               |
|        4 | 6.44056000 | select * from emp where empno=413345 |
+----------+------------+--------------------------------------+
4 rows in set (0.00 sec)

次に、次のコマンドで、特定のクエリーのprofile情報を表示できます.

mysql> show profile cpu, block io for query 4;
+--------------------+----------+----------+------------+--------------+---------------+
| Status             | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+--------------------+----------+----------+------------+--------------+---------------+
| starting           | 0.000107 | 0.000072 |   0.000025 |            0 |             0 |
| Opening tables     | 0.000021 | 0.000018 |   0.000003 |            0 |             0 |
| System lock        | 0.000006 | 0.000004 |   0.000001 |            0 |             0 |
| Table lock         | 0.000009 | 0.000008 |   0.000001 |            0 |             0 |
| init               | 0.000034 | 0.000033 |   0.000002 |            0 |             0 |
| optimizing         | 0.000012 | 0.000011 |   0.000001 |            0 |             0 |
| statistics         | 0.000014 | 0.000012 |   0.000001 |            0 |             0 |
| preparing          | 0.000013 | 0.000012 |   0.000002 |            0 |             0 |
| executing          | 0.000005 | 0.000005 |   0.000016 |            0 |             0 |
| Sending data       | 6.440260 | 7.818553 |   0.178155 |            0 |             0 |
| end                | 0.000008 | 0.000006 |   0.000011 |            0 |             0 |
| query end          | 0.000002 | 0.000002 |   0.000003 |            0 |             0 |
| freeing items      | 0.000030 | 0.000013 |   0.000017 |            0 |             0 |
| logging slow query | 0.000001 | 0.000000 |   0.000001 |            0 |             0 |
| logging slow query | 0.000035 | 0.000020 |   0.000015 |            0 |             0 |
| cleaning up        | 0.000003 | 0.000003 |   0.000000 |            0 |             0 |
+--------------------+----------+----------+------------+--------------+---------------+
16 rows in set (0.00 sec)

このprofileは、各ステップの操作にかかる時間とcpuとBlock IOの消費量を示しており、クエリー文をより的確に最適化できます.これは全テーブルスキャンであるため、ここで最も時間がかかるのはsending dataであることがわかります.このような場合に加えて、converting HEAP to MyISAM(クエリー結果が大きすぎる場合、結果をディスクに置く)、create tmp table(groupのような一時テーブルの作成時に中間結果を格納する)、Copying to tmp table on disk(メモリ一時テーブルをディスクにコピーする)、locked(他のクエリーにロックされる)、logging slow query(記録が遅いクエリー)のような時間がかかる場合もあります.