小心!高效率的sql查询,它也会导致网站响应变慢

简介:

最近一个项目进行2.0版本升级。2.0版本部署到所有的线上机器后,发现网站访问速度变的很慢。为了不影响用户体验,紧急进行版本回滚,然后进行问题查找。

分析
首先查看php的日志,没有发现有用的线索。
然后看了下mysql db的监控情况。如下图:
cpu_io_wait

<img class="alignnone size-full wp-image-228" alt="cpu_io_wait" src="http://www.bo56.com/wp-content/uploads/2013/11/cpu_io_wait.jpg" width="910" height="207" /></a></p>

load

<img class="alignnone size-full wp-image-229" alt="load" src="http://www.bo56.com/wp-content/uploads/2013/11/load.jpg" width="928" height="206" /></a></p>

memory_usage

<img class="alignnone size-full wp-image-230" alt="memory_usage" src="http://www.bo56.com/wp-content/uploads/2013/11/memory_usage.jpg" width="900" height="206" /></a></p>

network_in

<img class="alignnone size-full wp-image-231" alt="network_in" src="http://www.bo56.com/wp-content/uploads/2013/11/network_in.jpg" width="900" height="206" /></a></p>

network_out

<img class="alignnone size-full wp-image-232" alt="network_out" src="http://www.bo56.com/wp-content/uploads/2013/11/network_out.jpg" width="900" height="206" /></a></p>

qps

<img class="alignnone size-full wp-image-233" alt="qps" src="http://www.bo56.com/wp-content/uploads/2013/11/qps.jpg" width="900" height="206" /></a></p>

reponse_time

<img class="alignnone size-full wp-image-234" alt="reponse_time" src="http://www.bo56.com/wp-content/uploads/2013/11/reponse_time.jpg" width="900" height="206" /></a></p>

sys_cpu

<img class="alignnone size-full wp-image-235" alt="sys_cpu" src="http://www.bo56.com/wp-content/uploads/2013/11/sys_cpu.jpg" width="900" height="206" /></a></p>

user_cpu

<img class="alignnone size-full wp-image-236" alt="user_cpu" src="http://www.bo56.com/wp-content/uploads/2013/11/user_cpu.jpg" width="900" height="206" /></a></p>

2.0版本是在20点左右上线,20点20分左右回滚。从上图,可以看到2.0版本上线后,数据库服务器的网络io明显增高。这说明,不仅查询的次数增多了,而且返回的数据量也增大了很多。看来网站变慢很可能和mysql数据库查询有关。和db负责人沟通,让其查看是否有sql的满查询。但是反馈很让人意外。他查看慢查询日志后,没有发现执行效率有问题的sql。

在web服务器上,使用strace对php进程的执行情况做了进一步的跟踪。发现有一条sql (show status)语句频繁执行。这条语句的具体执行情况如下:

1382678984.106491 write(19, "\r\0\0\0\3 SHOW STATUS;", 17) = 17 <0.000334>
1382678984.106896 read(19, "\1\0\0\1\2N\0\0\2\3def\22information_schema\6STATUS\6STATUS\rVariable_name\rVARIABLE_NAME\f\34\0\200\0\0\0\375\1\0\0\0\0G\0\0\3\3def\22information_schema\6STATUS\6STATUS\5Value\16VARIABLE_VALUE\f\34\0\0\10\0\0\375\0\0\0\0\0\5\0\0\4\376\0\0\"\0\26\0\0\5\17Aborted_clients\00597839\32\0\0"..., 16384) =  4096 <0.002601>
1382678984.109672 read(19, "_discover\0010\25\0\0\254\17Handler_prepare\0041290\30\0\0\255\22Handler_read_first\0042060\30\0\0\256\20Handler_read_key\006524197\26\0\0\257\21Handler_read_last\003604\31\0\0\260\21Handler_read_next\006499561\31\0\0\261\21Handler_read_prev\006404599\30\0\0\262\20Handler_read_rnd\00611"..., 16384) =  6648 <0.000036>
1382678984.109947 poll([{fd=19, events=POLLIN|POLLPRI}], 1, 0) = 0 (Timeout) <0.000029>

看这条show status语句的执行情况。

A.  从发起sql查询,到可以读取结果大概花费了0.405毫秒。从1382678984.106491开始向mysql服务器发送查询请求。从1382678984.106896就已经完成了sql查询,并且可以读取数据了。可见这条sql语句的查询速度还是很快的。
B.  从发起sql查询,到读取完所有数据大概消耗了3毫秒。这条sql语句返回的数据大概10k左右,查询结果分两次才读取完毕。
C.  这条sql语句每秒执行了240次计算,这样每秒大概要有3*240 = 720毫秒消耗在这条sql语句中。这样1秒中有72%的时间消耗在这条sql查询上。这样就导致要多花费3.5倍的时间进行数据库操作。大家都直到web站点的瓶颈多数在数据库查询。

这样看来,很有可能就是这条sql语句导致的网站响应速度变慢。那为什么会每秒有这么多次查询?在2.0代码中增加了重试机制,即发现数据库连接有问题的时候,进行数据库重连。在设计重试机制时逻辑有问题,是每次进行数据库操作前都进行一次show status的查询,如果查询失败就进行数据库重新建立连接。

总结
1.不要因为某条sql的执行效率高就忽视。甚至肆无忌惮的使用。
2.不仅要注意sql的执行效率,还要特别注意返回数据量比较大的sql。否则过大的数据量返回,会给数据库造成很大的网络io压力。进而会导致load过高等一系列的反应。
3.合理的机制和策略很重要。不要滥用sql查询。

补充
本文原发布在阿里内网“阿里云计算”圈中,引起一些评论。因此在原文的基础上结合评论整理后发在本圈。
在原文评论中提到了select查询时,*符号的使用。我感觉非特殊必要,建议不要在select查询中使用*符号。如:select * from feed. 原因有以下几点:
1.当你仅需要表中部分字段中的内容时,必然会导致资源浪费。如,多余的数据必然会导致更多的网络io(大家直到io是很耗资源的一个操作)。多于数据在网络中传输会导致网络带宽的浪费。
2.不利于后期维护。作为web程序对应数据表的更改是常事。如表中某个字段名修改了,如果使用*的情况下,必须把所有引用此字段的地方的代码都要做相应修改。如果是通过select field from feed这样指定字段名查询数据。当field字段更名为new_field时,只要在select中使用AS 关键字即可。select new_field AS field from feed. 这样改动比较小。

另外,有两点需要注意。不过这些和数据库的SERVER端实现有关。
1.如果使用*的时候,可能会导致从*到表中字段名columns的转换。会造成一些时间浪费。2.在所需要的列正好都有索引时,可能数据直接读取索引。这样可以更少的磁盘io,从而提高效率。

目录
相关文章
|
4天前
|
SQL 资源调度 数据库
深入探究SQL查询语句执行过程
深入探究SQL查询语句执行过程
15 2
|
4天前
|
SQL Java
使用java在未知表字段情况下通过sql查询信息
使用java在未知表字段情况下通过sql查询信息
11 1
|
28天前
|
SQL 存储 缓存
高基数 GroupBy 在 SLS SQL 中的查询加速
本文详细介绍了SLS中的高基数GroupBy查询加速技术。
|
27天前
|
SQL 运维 程序员
一个功能丰富的SQL审核查询平台
一个功能丰富的SQL审核查询平台
|
9天前
|
SQL
SQL: 巧妙使用CASE WHEN实现查询
文章演示了如何利用SQL中的CASE WHEN语句来有效地进行条件性聚合查询,通过具体示例展示了CASE WHEN在统计分析中的应用技巧。
21 0
|
2月前
|
SQL 数据库 Java
HQL vs SQL:谁将统治数据库查询的未来?揭秘Hibernate的神秘力量!
【8月更文挑战第31天】Hibernate查询语言(HQL)是一种面向对象的查询语言,它模仿了SQL的语法,但操作对象为持久化类及其属性,而非数据库表和列。HQL具有类型安全、易于维护等优点,支持面向对象的高级特性,内置大量函数,可灵活处理查询结果。下面通过示例对比HQL与SQL,展示HQL在实际应用中的优势。例如,HQL查询“从员工表中筛选年龄大于30岁的员工”只需简单地表示为 `FROM Employee e WHERE e.age &gt; 30`,而在SQL中则需明确指定表名和列名。此外,HQL在处理关联查询时也更为直观易懂。然而,对于某些复杂的数据库操作,SQL仍有其独特优势。
39 0
|
2月前
|
SQL 关系型数据库 MySQL
|
2月前
|
API Java 数据库连接
从平凡到卓越:Hibernate Criteria API 让你的数据库查询瞬间高大上,彻底告别复杂SQL!
【8月更文挑战第31天】构建复杂查询是数据库应用开发中的常见需求。Hibernate 的 Criteria API 以其强大和灵活的特点,允许开发者以面向对象的方式构建查询逻辑,同时具备 SQL 的表达力。本文将介绍 Criteria API 的基本用法并通过示例展示其实际应用。此 API 通过 API 构建查询条件而非直接编写查询语句,提高了代码的可读性和安全性。无论是简单的条件过滤还是复杂的分页和连接查询,Criteria API 均能胜任,有助于提升开发效率和应用的健壮性。
62 0
|
2月前
|
Java UED 开发者
当错误遇上Struts 2:一场优雅的异常处理盛宴,如何让错误信息成为用户体验的救星?
【8月更文挑战第31天】在Web应用开发中,异常处理对确保用户体验和系统稳定性至关重要。Struts 2 提供了完善的异常处理机制,包括 `exception` 拦截器、`ActionSupport` 类以及 OGNL 表达式,帮助开发者优雅地捕获和展示错误信息。本文详细介绍了 Struts 2 的异常处理策略,涵盖拦截器配置、错误信息展示及自定义全局异常处理器的实现方法,使应用程序更加健壮和用户友好。
36 0
|
2月前
|
Java XML Maven
跨越时代的飞跃:Struts 2 升级秘籍——从旧版本无缝迁移到最新版,焕发应用新生!
【8月更文挑战第31天】随着软件技术的发展,Struts 2 框架也在不断更新。本文通过具体案例指导开发者如何从旧版平滑升级到 Struts 2.6.x。首先更新 `pom.xml` 中的依赖版本,并执行 `mvn clean install`。接着检查 `struts.xml` 配置,确保符合新版本要求,调整包扫描器等设置。审查 Action 类及其注解,检查配置文件中的弃用项及插件。更新自定义拦截器实现,并验证日志配置。最后,通过一系列测试确保升级后的系统正常运行。通过这些步骤,可以顺利完成 Struts 2 的版本升级,提升应用的安全性和性能。
94 0
下一篇
无影云桌面