Mysql性能优化(二) - 慢查询配置与分析

概要:

在优化MySQL时,通常需要对数据库进行分析,常见的分析手段有慢查询日志、explain分析查询以及profiling分析等。

| |目录

慢日志配置

查看慢日志配置

查看慢查询日志是否启用

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

查看慢查询时间设置

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

开启慢查询日志

方法一

打开my.ini文件(Linux下文件名为my.cnf),查找到[mysqld]区段,增加如下配置:

[mysqld]
slow_query_log=1
#开启慢查询日志,slow_query_log=0关闭,slow_query_log=1开启。
log="C:/temp/mysql.log"
#日志文件存放目录。
log_slow_queries="C:/temp/mysql_slow.log"
#慢查询日志存放位置(5.5之前版本)。
slow_query_log_file="C:/temp/mysql_slow.log"
#慢查询日志存放位置(5.5及以后版本)。
long_query_time=1
#执行时间超过1s的慢查询将被记录。
long_query_time=0.1
#5.21及以后版本支持毫秒级记录,此处设置为100ms。

重启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;
+-------+------------+------+-----+---------+----------------+
| Field | Type       | Null | Key | Default | Extra          |
+-------+------------+------+-----+---------+----------------+
| id    | bigint(20) | NO   | PRI | NULL    | auto_increment |
| name  | char(10)   | YES  | MUL | NULL    |                |
+-------+------------+------+-----+---------+----------------+
2 rows in set (0.01 sec)
mysql> explain select name from test where id = 5;
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | test  | const | PRIMARY       | PRIMARY | 8       | const |    1 |       |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
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):没有好的索引.

mysql> select * from test;
+----+------+
| id | name |
+----+------+
|  1 | aa   |
|  2 | abc  |
|  3 | aab  |
|  4 | acf  |
|  5 | aac  |
|  6 | acd  |
|  7 | cvf  |
+----+------+
7 rows in set (0.00 sec)
mysql> explain select * from test where name > 'a';
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | test  | ALL  | NULL          | NULL | NULL    | NULL |    7 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
mysql> explain select * from test where name > 'a' limit 3;
+----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra                    |
+----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
|  1 | SIMPLE      | test  | range | test          | test | 31      | NULL |    3 | Using where; Using index |
+----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
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:                                                                                  

  | ALL
  | BLOCK IO
  | CONTEXT SWITCHES
  | CPU                                                                      
  | IPC
  | MEMORY
  | PAGE FAULTS
  | SOURCE
  | SWAPS

profiling默认是关闭的。可以通过以下语句查看

mysql> select @@profiling;
+-------------+
| @@profiling |
+-------------+
|           0 |
+-------------+
1 row in set (0.00 sec)

打开功能: mysql>set profiling=1; 执行需要测试的sql 语句:

mysql> set profiling=1;
Query OK, 0 rows affected (0.02 sec)
mysql> select * from test;
+----+------+
| id | name |
+----+------+
|  1 | aa   |
|  3 | aab  |
|  5 | aac  |
|  2 | abc  |
|  6 | acd  |
|  4 | acf  |
|  7 | cvf  |
+----+------+
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;
+--------------------+----------+
| Status             | Duration |
+--------------------+----------+
| starting           | 0.024071 |
| Opening tables     | 0.305523 |
| System lock        | 0.000320 |
| Table lock         | 0.014546 |
| init               | 0.000050 |
| optimizing         | 0.000446 |
| statistics         | 0.000359 |
| preparing          | 0.000452 |
| executing          | 0.000006 |
| Sending data       | 0.047042 |
| end                | 0.000021 |
| query end          | 0.000005 |
| freeing items      | 0.000663 |
| logging slow query | 0.000025 |
| cleaning up        | 0.000004 |
+--------------------+----------+
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资源耗费信息,我们可以采取针对性的优化措施。

测试完毕以后 ,关闭参数:mysql> set profiling=0

mysql> set profiling = 0;
Query OK, 0 rows affected (0.00 sec)
评论关闭
评论 还能输入200
评论关闭
评论 还能输入200
  • 全部评论(0)
资料加载中...
已关注 , 取消