mysql 慢查询解决之路

任务: 生产库慢查询日志, 显示有1个查询 耗时3s 以上. 需要解决.

 

•  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   |
+--------------------+-----------+

 设置为慢查询阈值为2s.

 

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.1s之内. 

问题初步解决.

 另 也尝试了profile. 但遇到了疑惑的问题. 下次再分享.

 

• 4. 问题总结

之前没有做过sql 方面的优化,  经过一天的查资料学习. 最终解决了问题. 体验到作为程序员的成就感. 但mysql 的优化涉及的面还是很广的.

这篇文章只是myslq 优化的冰山一角. 

google下 mysql优化,有太多的好文章. 需慢慢学习ing.

 

如下: http://www.oicto.com/mysql-explain-show/

 

评论

1 楼 lvwenwen 2013-08-22  

google下 mysql优化,有太多的好文章