MySQL调优三步曲(慢查询、explain profile),链接网站 : http://www.linuxidc.com/Linux/2012-09/70459.htm
profile , explain , slow_query_log 分析优化查询:
在做性能测试中经常会遇到一些sql的问题,其实做性能测试这几年遇到问题最多还是数据库这块,要么就是IO高要么就是cpu高,所以对数据的优化在性能测试过程中占据着很重要的地方,下面我就介绍一些msyql性能调优过程中经常用到的三件利器:
1、慢查询 (分析出现出问题的sql)
2、Explain (显示了mysql如何使用索引来处理select语句以及连接表。可以帮助选择更好的索引和写出更优化的查询语句)
3、Profile(查询到 SQL 会执行多少时间, 并看出 CPU/Memory 使用量, 执行过程中 Systemlock, Table lock 花多少时间等等.)
 
Profile 使用方法 :

#来查看是否已经启用profile,如果profilng值为0,可以通过
 > select @@profiling;

+-----------------+
| @@profiling |
+-----------------+
|                 0    |
+-----------------+
1 row in set (0.00 sec)

#启用profiling
> set profiling=1;

Query OK, 0 rows affected (0.00 sec)

> select @@profiling;

+-------------------+
|  @@profiling  |
+-------------------+
|                       1 |
+-------------------+
1 row in set (0.00 sec)

#执行一条查询语句,比如:
> select * from user where uid=1;

+-----+---------------+------+
 | uid  | username | pwd  |
+-----+---------------+------+
 |     1 |      小明     | 123  |
+-----+---------------+------+
1 row in set (0.00 sec)

#注意:Query_ID表示刚执行的查询语句
> show profiles;

+-------------+------------------+--------------------------------------------+
| Query_ID |      Duration   | Query                                              |
+-------------+------------------+--------------------------------------------+
|               1 | 0.00026750  | select @@profiling                      |
|               2 | 0.00094700  | select * from user where uid=1   |
+-------------+------------------+--------------------------------------------+
2 rows in set (0.00 sec)

#查看刚刚查询语句的执行过程,对症下药,优化SQL语句
> show profile for query 2;

+------------------------------+---------------+
| Status                            | Duration    |
+------------------------------+---------------+
| starting                          |  0.000066 |
| checking permissions |  0.000009 |
| Opening tables             |  0.000478 |
| System lock                  |  0.000015 |
| init                                  |  0.000024 |
| optimizing                     |  0.000012 |
| statistics                       |   0.000086 |
| preparing                     |   0.000022 |
| executing                     |   0.000003 |
| Sending data              |   0.000012 |
| end                               |   0.000007 |
| query end                    |   0.000004 |
| closing tables             |   0.000006 |
| freeing items              |   0.000197 |
| logging slow query    |   0.000005 |
| cleaning up                |   0.000003 |
+----------------------------+----------------+
16 rows in set (0.00 sec)

#此条查询语句的执行过程及执行时间,总的时间约为 0.00094700s 。这时候我们同一条SQL再执行一次
> select * from user where uid=1;
> show profiles;

+-------------+------------------+--------------------------------------------+
| Query_ID |      Duration   | Query                                              |
+-------------+------------------+--------------------------------------------+
|               1 | 0.00026750  | select @@profiling                      |
|               2 | 0.00094700  | select * from user where uid=1   |
|               3 | 0.00039200  | select * from user where uid=1   |
+-------------+------------------+--------------------------------------------+
3 rows in set (0.00 sec)
#重新执行的时候,时间明显降低很多,因为前一次的查询生成了cache

> show profile for query 3;

+------------------------------+---------------+
| Status                            | Duration    |
+------------------------------+---------------+
| starting                          |  0.000055 |
| checking permissions |  0.000007 |
| Opening tables             |  0.000018 |
| System lock                  |  0.000010 |
| init                                  |  0.000022 |
| optimizing                     |  0.000010 |
| statistics                       |   0.000060 |
| preparing                     |   0.000019 |
| executing                     |   0.000003 |
| Sending data              |   0.000011 |
| end                               |   0.000005 |
| query end                    |   0.000003 |
| closing tables             |   0.000156 |
| freeing items              |   0.000197 |
| logging slow query    |   0.000004 |
| cleaning up                |   0.000003 |
+----------------------------+----------------+
16 rows in set (0.00 sec)

 
 
 
explain 使用方法 : 相关链接 explain 用法详解

explain select * from user order by uid desc limit 1;

+----+---------------+-------+-------+--------------------+--------------+-----------+---------+------+---------+
| id | select_type | table | type  | possible_keys | key            | key_len | ref       | rows | Extra |
+----+---------------+-------+-------+--------------------+--------------+-----------+---------+------+---------+
|  1 | SIMPLE      | user  | index | NULL               | PRIMARY | 4            | NULL |    1   |             |
+----+---------------+-------+-------+--------------------+--------------+-----------+---------+------+---------+
1 row in set (0.00 sec)

#字段说明:

id

SELECT识别符。这是SELECT的查询序列号

select_type

SELECT类型,可以为以下任何一种:

SIMPLE:简单SELECT(不使用UNION或子查询)

PRIMARY:最外面的SELECT

UNION:UNION中的第二个或后面的SELECT语句

DEPENDENT UNION:UNION中的第二个或后面的SELECT语句,取决于外面的查询

UNION RESULT:UNION 的结果

SUBQUERY:子查询中的第一个SELECT

DEPENDENT SUBQUERY:子查询中的第一个SELECT,取决于外面的查询

DERIVED:导出表的SELECT(FROM子句的子查询)

table

输出的行所引用的表

type

联接类型。下面给出各种联接类型,按照从最佳类型到最坏类型进行排序:

system:表仅有一行(=系统表)。这是const联接类型的一个特例。

const:表最多有一个匹配行,它将在查询开始时被读取。因为仅有一行,在这行的列值可被优化器剩余部分认为是常数。const表很快,因为它们只读取一次!

eq_ref:对于每个来自于前面的表的行组合,从该表中读取一行。这可能是最好的联接类型,除了const类型。

ref:对于每个来自于前面的表的行组合,所有有匹配索引值的行将从这张表中读取。

ref_or_null:该联接类型如同ref,但是添加了MySQL可以专门搜索包含NULL值的行。

index_merge:该联接类型表示使用了索引合并优化方法。

unique_subquery:该类型替换了下面形式的IN子查询的ref: value IN (SELECT primary_key FROM single_table WHERE some_expr) unique_subquery是一个索引查找函数,可以完全替换子查询,效率更高。

index_subquery:该联接类型类似于unique_subquery。可以替换IN子查询,但只适合下列形式的子查询中的非唯一索引: value IN (SELECT key_column FROM single_table WHERE some_expr)

range:只检索给定范围的行,使用一个索引来选择行。

index:该联接类型与ALL相同,除了只有索引树被扫描。这通常比ALL,因为索引文件通常比数据文件小。

ALL:对于每个来自于先前的表的行组合,进行完整的表扫描。

possible_keys

指出MySQL能使用哪个索引在该表中找到行

key

显示MySQL实际决定使用的键(索引)。如果没有选择索引,键是NULL。

key_len

显示MySQL决定使用的键长度。如果键是NULL,则长度为NULL。

ref

显示使用哪个列或常数与key一起从表中选择行。

rows

显示MySQL认为它执行查询时必须检查的行数。多行之间的数据相乘可以估算要处理的行数。

filtered

显示了通过条件过滤出的行数的百分比估计值。

Extra

该列包含MySQL解决查询的详细信息

Distinct:MySQL发现第1个匹配行后,停止为当前的行组合搜索更多的行。

Not exists:MySQL能够对查询进行LEFT JOIN优化,发现1个匹配LEFT JOIN标准的行后,不再为前面的的行组合在该表内检查更多的行。

range checked for each record (index map: #):MySQL没有发现好的可以使用的索引,但发现如果来自前面的表的列值已知,可能部分索引可以使用。

Using filesort:MySQL需要额外的一次传递,以找出如何按排序顺序检索行。

Using index:从只使用索引树中的信息而不需要进一步搜索读取实际的行来检索表中的列信息。

Using temporary:为了解决查询,MySQL需要创建一个临时表来容纳结果。

Using where:WHERE 子句用于限制哪一个行匹配下一个表或发送到客户。

Using sort_union(...), Using union(...), Using intersect(...):这些函数说明如何为index_merge联接类型合并索引扫描。

Using index for group-by:类似于访问表的Using index方式,Using index for group-by表示MySQL发现了一个索引,可以用来查 询GROUP BYDISTINCT查询的所有列,而不要额外搜索硬盘访问实际的表。

 
 
 
 
mysql的慢查询日志 :
1,Linux配置开启

在mysql配置文件my.cnf中增加

log-slow-queries=/var/lib/mysql/slowquery.log (指定日志文件存放位置,可以为空,系统会给一个缺省的文件host_name-slow.log)

long_query_time=2 (记录超过的时间,默认为10s)

log-queries-not-using-indexes (log下来没有使用索引的query,可以根据情况决定是否开启)

log-long-format (如果设置了,所有没有使用索引的查询也将被记录)

Windows:

在my.ini的[mysqld]添加如下语句:

log-slow-queries =E:\web\mysql\log\mysqlslowquery.log

long_query_time = 2(其他参数如上)

2,查看方式

使用mysql自带命令mysqldumpslow查看
常用命令

-s ORDER what to sort by (t, at, l, al, r, aretc), 'at’ is default

-t NUM just show the top n queries

-g PATTERN grep: only consider stmts that includethis string

eg:

s,是order的顺序,说明写的不够详细,俺用下来,包括看了代码,主要有 c,t,l,r和ac,at,al,ar,分别是按照query次数,时间,lock的时间和返回的记录数来排序,前面加了a的时倒序 -t,是top n的意思,即为返回前面多少条的数据 -g,后边可以写一个正则匹配模式,大小写不敏感的

mysqldumpslow -s c -t 20 host-slow.log

mysqldumpslow -s r -t 20 host-slow.log

上述命令可以看出访问次数最多的20个sql语句和返回记录集最多的20个sql。

mysqldumpslow -t 10 -s t -g “left join” host-slow.log这个是按照时间返回前10条里面含有左连接的sql语句。

---------------------------------------------------------------------------------------------
不忘初心 方得始终!

唯有志存高远,方能风行天下。

道之所存,虽千万人吾往矣! 情之所钟,虽千万里吾念矣~

原创作品,允许转载,转载时请务必以超链接形式标明文章 原始出处 、作者信息和本声明。否则将追究法律责任。