Mysql性能优化(二) - 慢查询配置与分析
## 慢日志配置
### 查看慢日志配置
查看慢查询日志是否启用
```sql
mysql> show variables like 'log_slow_queries';
+------------------+-------+
&##124; Variable_name &##124; Value &##124;
+------------------+-------+
&##124; log_slow_queries &##124; ON &##124;
+------------------+-------+
1 row in set (0.00 sec)
```
查看慢查询时间设置
```sql
mysql> show variables like 'long_query_time';
+-----------------+-------+
&##124; Variable_name &##124; Value &##124;
+-----------------+-------+
&##124; long_query_time &##124; 1 &##124;
+-----------------+-------+
1 row in set (0.00 sec)
```
### 开启慢查询日志
#### 方法一
打开my.ini文件（Linux下文件名为my.cnf），查找到\[mysqld\]区段，增加如下配置：
```
[mysqld]
#开启慢查询日志，slow_query_log=0关闭，slow_query_log=1开启。
slow_query_log=1
#日志文件存放目录。
log="C:/temp/mysql.log"
#慢查询日志存放位置（5.5之前版本）。
#log_slow_queries="C:/temp/mysql_slow.log"
#慢查询日志存放位置（5.5及以后版本）。
slow_query_log_file="C:/temp/mysql_slow.log"
#执行时间超过1s的慢查询将被记录。
long_query_time=1
#5.21及以后版本支持毫秒级记录，此处设置为100ms。
#long_query_time=0.1
```
重启mysql生效。
#### 方法二
开启慢查询日志
```
mysql > SET GLOBAL slow_query_log = ON
```
设置慢查询时间
```
mysql > SET long_query_time = 2
```
**此配置为临时配置，重启mysql后失效。**
### 测试慢查询配置
进入mysql控制台，执行如下sql语句：
```
select sleep(2);
```
然后打开慢查询日志查看即可。
## 慢查询分析工具 \- mysqldumpslow
我们可以通过打开log文件查看得知哪些SQL执行效率低下
```
[root@localhost mysql]# more slow.log
Time: 141028 17:00:29
User@Host: root[root] @ localhost []
Query_time: 2.000330 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0
SET timestamp=1414486829;
select sleep(2);
```
从日志中，可以发现查询时间超过2秒的SQL，而小于2秒的没有出现在此日志中。
如果慢查询日志中记录内容很多，可以使用mysqldumpslow工具（MySQL客户端安装自带）来对慢查询日志进行分类汇总。mysqldumpslow对日志文件进行了分类汇总，显示汇总后摘要结果。
进入log的存放目录，运行：
```
[root@localhost mysql]# ./bin/mysqldumpslow slow.log
Reading mysql slow query log from slow.log
Count: 1 Time=2.00s (2s) Lock=0.00s (0s) Rows=1.0 (1), root[root]@mysql
select sleep(N)
```
### mysqldumpslow命令
```
/path/mysqldumpslow -s c -t 10 /database/mysql/slow-query.log
```
这会输出记录次数最多的10条SQL语句，其中：
-s, 是表示按照何种方式排序，c、t、l、r分别是按照记录次数、时间、查询时间、返回的记录数来排序，ac、at、al、ar，表示相应的倒叙；
-t, 是top n的意思，即为返回前面多少条的数据；
-g, 后边可以写一个正则匹配模式，大小写不敏感的；
例如：
```
/path/mysqldumpslow -s r -t 10 /database/mysql/slow-log
```
得到返回记录集最多的10个查询。
```
/path/mysqldumpslow -s t -t 10 -g “left join” /database/mysql/slow-log
```
得到按照时间排序的前10条里面含有左连接的查询语句。
使用mysqldumpslow命令可以非常明确的得到各种我们需要的查询语句，对MySQL查询语句的监控、分析、优化是MySQL优化非常重要的一步。开启慢查询日志后，由于日志记录操作，在一定程度上会占用CPU资源影响mysql的性能，但是可以阶段性开启来定位性能瓶颈。
## explain分析查询
使用 EXPLAIN 关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的。这可以帮你分析你的查询语句或是表结构的性能瓶颈。通过explain命令可以得到:
* 表的读取顺序
* 数据读取操作的操作类型
* 哪些索引可以使用
* 哪些索引被实际使用
* 表之间的引用
* 每张表有多少行被优化器查询
```
mysql> describe test;
+-------+------------+------+-----+---------+----------------+
&##124; Field &##124; Type &##124; Null &##124; Key &##124; Default &##124; Extra &##124;
+-------+------------+------+-----+---------+----------------+
&##124; id &##124; bigint(20) &##124; NO &##124; PRI &##124; NULL &##124; auto_increment &##124;
&##124; name &##124; char(10) &##124; YES &##124; MUL &##124; NULL &##124; &##124;
+-------+------------+------+-----+---------+----------------+
2 rows in set (0.01 sec)
mysql> explain select name from test where id = 5;
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
&##124; id &##124; select_type &##124; table &##124; type &##124; possible_keys &##124; key &##124; key_len &##124; ref &##124; rows &##124; Extra &##124;
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
&##124; 1 &##124; SIMPLE &##124; test &##124; const &##124; PRIMARY &##124; PRIMARY &##124; 8 &##124; const &##124; 1 &##124; &##124;
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
1 row in set (0.00 sec)
```
### EXPLAIN字段
**Table：**显示这一行的数据是关于哪张表的
**possible_keys：**显示可能应用在这张表中的索引。如果为空，没有可能的索引。可以为相关的域从WHERE语句中选择一个合适的语句
**key：**实际使用的索引。如果为NULL，则没有使用索引。MYSQL很少会选择优化不足的索引，此时可以在SELECT语句中使用USE INDEX（index）来强制使用一个索引或者用IGNORE INDEX（index）来强制忽略索引
**key_len：**使用的索引的长度。在不损失精确性的情况下，长度越短越好
**ref：**显示索引的哪一列被使用了，如果可能的话，是一个常数
**rows：**MySQL认为必须检索的用来返回请求数据的行数
**type：**这是最重要的字段之一，显示查询使用了何种类型。从最好到最差的连接类型为system、const、eq_reg、ref、range、index和ALL
* system、const：可以将查询的变量转为常量. 如id=1; id为 主键或唯一键.
* eq_ref：访问索引,返回某单一行的数据.(通常在联接时出现，查询使用的索引为主键或惟一键)
* ref：访问索引,返回某个值的数据.(可以返回多行) 通常使用=时发生
* range：这个连接类型使用索引返回一个范围中的行，比如使用>或<查找东西，并且该字段上建有索引时发生的情况(注:不一定好于index)
* index：以索引的顺序进行全表扫描，优点是不用排序,缺点是还要全表扫描
* ALL：全表扫描，应该尽量避免
**Extra：**关于MYSQL如何解析查询的额外信息，主要有以下几种
* using index：只用到索引,可以避免访问表.
* using where：使用到where来过虑数据. 不是所有的where clause都要显示using where. 如以=方式访问索引.
* using tmporary：用到临时表
* using filesort：用到额外的排序. (当使用order by v1,而没用到索引时,就会使用额外的排序)
* range checked for eache record(index map:N)：没有好的索引.
```sql
mysql> select * from test;
+----+------+
&##124; id &##124; name &##124;
+----+------+
&##124; 1 &##124; aa &##124;
&##124; 2 &##124; abc &##124;
&##124; 3 &##124; aab &##124;
&##124; 4 &##124; acf &##124;
&##124; 5 &##124; aac &##124;
&##124; 6 &##124; acd &##124;
&##124; 7 &##124; cvf &##124;
+----+------+
7 rows in set (0.00 sec)
mysql> explain select * from test where name > 'a';
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
&##124; id &##124; select_type &##124; table &##124; type &##124; possible_keys &##124; key &##124; key_len &##124; ref &##124; rows &##124; Extra &##124;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
&##124; 1 &##124; SIMPLE &##124; test &##124; ALL &##124; NULL &##124; NULL &##124; NULL &##124; NULL &##124; 7 &##124; Using where &##124;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
mysql> explain select * from test where name > 'a' limit 3;
+----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
&##124; id &##124; select_type &##124; table &##124; type &##124; possible_keys &##124; key &##124; key_len &##124; ref &##124; rows &##124; Extra &##124;
+----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
&##124; 1 &##124; SIMPLE &##124; test &##124; range &##124; test &##124; test &##124; 31 &##124; NULL &##124; 3 &##124; Using where; Using index &##124;
+----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
1 row in set (0.00 sec)
```
## profiling分析查询
通过慢日志查询可以知道哪些SQL语句执行效率低下，通过explain我们可以得知SQL语句的具体执行情况，索引使用等，还可以结合show命令查看执行状态。
如果觉得explain的信息不够详细，可以同通过profiling命令得到更准确的SQL执行消耗系统资源的信息。
### Show Profile命令格式
```
SHOW PROFILE [type [, type] … ] [FOR QUERY n] [LIMIT row_count [OFFSET offset]]
```
**type:**
```
&##124; ALL
&##124; BLOCK IO
&##124; CONTEXT SWITCHES
&##124; CPU
&##124; IPC
&##124; MEMORY
&##124; PAGE FAULTS
&##124; SOURCE
&##124; SWAPS
```
profiling默认是关闭的。可以通过以下语句查看
```sql
mysql> select @@profiling;
+-------------+
&##124; @@profiling &##124;
+-------------+
&##124; 0 &##124;
+-------------+
1 row in set (0.00 sec)
```
打开功能并执行需要测试的sql 语句：
```sql
mysql> set profiling=1;
Query OK, 0 rows affected (0.02 sec)
mysql> select * from test;
+----+------+
&##124; id &##124; name &##124;
+----+------+
&##124; 1 &##124; aa &##124;
&##124; 3 &##124; aab &##124;
&##124; 5 &##124; aac &##124;
&##124; 2 &##124; abc &##124;
&##124; 6 &##124; acd &##124;
&##124; 4 &##124; acf &##124;
&##124; 7 &##124; cvf &##124;
+----+------+
7 rows in set (0.39 sec)
mysql> show profilesG;
*************************** 1. row ***************************
Query_ID: 1
Duration: 0.39353075
Query: select * from test
1 row in set (0.00 sec)
ERROR:
No query specified
mysql> show profile for query 1;
+--------------------+----------+
&##124; Status &##124; Duration &##124;
+--------------------+----------+
&##124; starting &##124; 0.024071 &##124;
&##124; Opening tables &##124; 0.305523 &##124;
&##124; System lock &##124; 0.000320 &##124;
&##124; Table lock &##124; 0.014546 &##124;
&##124; init &##124; 0.000050 &##124;
&##124; optimizing &##124; 0.000446 &##124;
&##124; statistics &##124; 0.000359 &##124;
&##124; preparing &##124; 0.000452 &##124;
&##124; executing &##124; 0.000006 &##124;
&##124; Sending data &##124; 0.047042 &##124;
&##124; end &##124; 0.000021 &##124;
&##124; query end &##124; 0.000005 &##124;
&##124; freeing items &##124; 0.000663 &##124;
&##124; logging slow query &##124; 0.000025 &##124;
&##124; cleaning up &##124; 0.000004 &##124;
+--------------------+----------+
15 rows in set (0.05 sec)
```
mysql> show profilesG; 可以得到被执行的SQL语句的时间和ID
mysql>show profile for query 1; 得到对应SQL语句执行的详细信息
以上的15rows是针对非常简单的select语句的资源信息，对于较复杂的SQL语句，会有更多的行和字段，比如converting HEAP to MyISAM 、Copying to tmp table等等，由于以上的SQL语句不存在复杂的表操作，所以未显示这些字段。通过profiling资源耗费信息，我们可以采取针对性的优化措施。
测试完毕以后 ，关闭参数：
```sql
mysql> set profiling = 0;
Query OK, 0 rows affected (0.00 sec)
```