任务: 生产库慢查询日志, 显示有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/