线上有这样一个Sql耗时很长,统计 了下基本在80~200s之间。
SQL: SELECT `mysites_vultask`.`id`, `mysites_vultask`.`site_id`, `mysites_vultask`.`status`, `mysites_vultask`.`timestamp`, `mysites_vultask`.`conf`, `mysites_vultask`.`task_state`, `mysites_vultask`.`task_id`, `mysites_vultask`.`error_id`, `mysites_vultask`.`endtime`, `mysites_vultask`.`messages`, `mysites_vultask`.`operation`, `mysites_vultask`.`report_name`, `mysites_vultask`.`work_id`, `mysites_vultask`.`reload`, `mysites_vultask`.`urllist`, `mysites_vultask`.`task_type`, `mysites_vultask`.`task_serial`, `mysites_vultask`.`task_option`, `mysites_vultask`.`strategy`, `mysites_vultask`.`check_status`, `mysites_vultask`.`score` FROM `mysites_vultask` WHERE (`mysites_vultask`.`status` = 'Y' AND `mysites_vultask`.`site_id` = 2761 AND `mysites_vultask`.`report_name` IS NOT NULL AND NOT ((`mysites_vultask`.`report_name` = '' AND `mysites_vultask`.`report_name` IS NOT NULL))) ORDER BY `mysites_vultask`.`endtime` DESC;
一、查看其执行过程,在creating sort index 占用了绝大部分的时间,如图1,通过google和度娘:creating sort index 代表了mysql 要通过内部临时表的方案来处理这次Select,当然这个表是内存中的。当内存放不下时,全部copy到磁盘。导致性能极具下降。另外:
MySQL 临时表分为“内存临时表”和“磁盘临时表”,其中内存临时表使用MySQL的MEMORY存储引擎,磁盘临时表使用MySQL的 MyISAM存储引擎;
一般情况下,MySQL会先创建内存 临时表,但内存临时表超过配置指定的值后,MySQL会将内存临时表导出到磁盘临时表; 详细见参考资料1.
二、由于使用order by 一定场景下会带来creating sort index,故先去掉后,继续查看执行情况,如下图,发现查询时间还在100秒以上,这次时间主要在Sending data上边。
Sending data :线程在读取和处理SELECT语句,发送数据到客户端。由于语句需要大量的磁盘访问,这个状态会在语句的整个生命周期中占据最长的一个状态。
EXPLAIN 会发现,这条sql语句会利用表中已建立好的索引,理论上不应该慢啊。但是要注意,我们SELECT的是表中所有的字段。所以MySQL要根据索引去定位出具体的数据行返回给客户端。
SQL: SELECT `mysites_vultask`.`id`, `mysites_vultask`.`site_id`, `mysites_vultask`.`status`, `mysites_vultask`.`timestamp`, `mysites_vultask`.`conf`, `mysites_vultask`.`task_state`, `mysites_vultask`.`task_id`, `mysites_vultask`.`error_id`, `mysites_vultask`.`endtime`, `mysites_vultask`.`messages`, `mysites_vultask`.`operation`, `mysites_vultask`.`report_name`, `mysites_vultask`.`work_id`, `mysites_vultask`.`reload`, `mysites_vultask`.`urllist`, `mysites_vultask`.`task_type`, `mysites_vultask`.`task_serial`, `mysites_vultask`.`task_option`, `mysites_vultask`.`strategy`, `mysites_vultask`.`check_status`, `mysites_vultask`.`score` FROM `mysites_vultask` WHERE (`mysites_vultask`.`status` = 'Y' AND `mysites_vultask`.`site_id` = 2761 AND `mysites_vultask`.`report_name` IS NOT NULL AND NOT ((`mysites_vultask`.`report_name` = '' AND `mysites_vultask`.`report_name` IS NOT NULL)));
三、问题基本定位到了表中的列的情况,那就看看表的大概情况吧~
执行:select * from information_schema.tables where TABLE_NAME like '%mysites_vul%';
发现该表的平均行长度竟然有70K, 查看该表结构和数据。发现messages 和 urllist 字段存储了改任务的扫描结果,有的达到了几十M。
innodb引擎中。对于大字段如text blob等,只会存放768个字节在数据页中,而剩余的数据会存储在溢出段中。而在我们的表中messages 和 urllist 太大。存放在了很多页上。
导致每次查询该字段都会去访问很多页(ps:根据网上同行测试。 innodb中溢出页中的数据也不会缓存)。
四:根据分析,把messages 和 urllist 从SELECT都去掉。在看效果。仅仅用了0.24s就查询 出来了。
SQL:
SELECT `mysites_vultask`.`id`, `mysites_vultask`.`site_id`, `mysites_vultask`.`status`,
`mysites_vultask`.`timestamp`, `mysites_vultask`.`conf`, `mysites_vultask`.`task_state`,
`mysites_vultask`.`task_id`, `mysites_vultask`.`error_id`, `mysites_vultask`.`endtime`,
# `mysites_vultask`.`messages`,
`mysites_vultask`.`operation`, `mysites_vultask`.`report_name`,
`mysites_vultask`.`work_id`, `mysites_vultask`.`reload`,
#`mysites_vultask`.`urllist`,
`mysites_vultask`.`task_type`, `mysites_vultask`.`task_serial`, `mysites_vultask`.`task_option`,
`mysites_vultask`.`strategy`, `mysites_vultask`.`check_status`,
`mysites_vultask`.`score` FROM `mysites_vultask`
WHERE (`mysites_vultask`.`status` = 'Y' AND `mysites_vultask`.`site_id` = 2761
AND `mysites_vultask`.`report_name` IS NOT NULL AND NOT ((`mysites_vultask`.`report_name` = ''
AND `mysites_vultask`.`report_name` IS NOT NULL)))
ORDER BY `mysites_vultask`.`endtime` DESC;
五、 总结一下:
(1)表设计时需要考虑到这样的问题。像这种存放大数据的字段需要拆分到其他 表中。或者是考虑细化存储其中的数据
(2)select时,用哪个就查哪个。不要select没用的列,或者 Select *
(3)只要固定几行数据就使用LIMIT
相关推荐
第2章 风驰电掣——有效缩短SQL优化过程 24 2.1 SQL调优时间都去哪儿了 25 2.1.1 不善于批处理频频忙交互 25 2.1.2 无法抓住主要矛盾瞎折腾 25 2.1.3 未能明确需求目标白费劲 26 2.1.4 没有分析操作难度乱调优...
, 随后《收获,不止SQL优化——抓住SQL的本质》指引大家学会等价改写、过程包优化、高级SQL、分析函数、需求优化这些相关的五大神功。有点头晕,能否少一点套路?淡定,这还是“术”的范畴,依然是教你如何解决问题...
第2章 风驰电掣——有效缩短SQL优化过程 24 2.1 SQL调优时间都去哪儿了 25 2.1.1 不善于批处理频频忙交互 25 2.1.2 无法抓住主要矛盾瞎折腾 25 2.1.3 未能明确需求目标白费劲 26 2.1.4 没有分析操作难度乱调优...
存储过程中可以包含逻辑控制语句和数据操纵语句,它可以接受参数、输出参数、返回单个或多个结果集以及返回值。 由于存储过程在创建时即在数据库服务器上进行了编译并存储在数据库中,所以存储过程运行要比单个的...
随后《收获,不止SQL优化——抓住SQL的本质》指引大家学会等价改写、过程包优化、高级SQL、分析函数、需求优化这些相关的五大神功。有点头晕,能否少一点套路?淡定,这还是“术”的范畴,依然是教你如何解决问题,...
减少网络流量--将多种操作放在一个过程中,减少访问次数 速度快--执行是,数据库无需再次编译 适应性强--可以被多种应用程序调用 执行计划 什么是执行计划? 执行计划可以理解为SQL语句的执行路径,通过图表...
以上四个SQL在ORACLE分析整理之后产生的结果及执行的时间是一样的,但是从ORACLE共享内存SGA的原理,可以得出ORACLE对每个SQL 都会对其进行一次分析,并且占用共享内存,如果将SQL的字符串及格式写得完全相同则...
这样读取效率就比较高,因为一次读取就可能包含了两个表中的数据,因此提高了查询效率。要解决“磁盘数据组织不合理,导致磁盘的访问次数过多”这个问题,我们可以将经常读写的数据放置在不同的磁盘上,也就是将经常...
另外《Oracle优化日记:一个金牌DBA的故事》第一次详尽地披露了Oracle数据库内部存储结构,并公布了部分代码,对于有兴趣研究数据库内部存储结构或者编写dul工具的读者有一定的参考价值。《Oracle优化日记:一个金牌...
存储过程中的每一条语句 写入sql server错误日志的错误 打开游标 向数据对象添加或释放锁 Profiler事件 SQL Server Profiler里Standard模板的事件类 数据库引擎优化顾问介绍 引擎优化顾问提供数据库系统的...
在几次升级Hive的过程中,我们遇到了一些大大小小的问题。通过向社区的咨询和自己的努力,在解决这些问题的同时我们对Hive将SQL编译为MapReduce的过程有了比较深入的理解。对这一过程的理解不仅帮助我们解决了一些...
当存储过程执行一次后,可以将语句缓存中,这样下次执行的时候直接使用缓存中的语句。这样就可以提高存储过程的性能。 Ø 存储过程的概念 存储过程Procedure是一组为了完成特定功能的SQL语句集合,经编译后存储在...
4.6 基于集合的方法和迭代/过程方法的比较,以及一个优化练习2 4.7 总结 第5章 算法和复杂性 5.1 你有一个1夸特的硬币吗? 5.1.1 如何从零钱罐中取回1夸特钱 5.1.2 有时零钱罐中没有1夸特的硬币 5.2 如何度量...
*修正开启服务过程中碰到异常时,没有修改实例状态的错误; +支持强制使用默认实例,也就是使用1433端口和空实例名。 ps:升级了tools目录下的makesql和runsql,均支持unicode,与MSSQL配合使用效果非常 不错的...
盖国强 的一次案例分析 是一次SQL优化分析的全过程,曾经在itpub上发过相关的帖子,现在整理了一下,添加了详细的说明,希望对大家有些帮助。
《Microsoft SQL Server 2008技术内幕:T-SQL查询》全面深入地介绍了Microsoft SQL Server 2008中高级T-SQL查询、性能优化等方面的内容,以及SQL Server 2008新增加的一些特性。主要内容包括SQL的基础理论、查询优化...
查询仅需解析(或预处理)一次,但可以用相同或不同的参数执行多次。当查询准备好后,数据库将分析、编译和优化执行该查询的计划。对于复杂的查询,此过程要花费较长的时间,如果需要以不同参数多次重复相同的查询...
4.6 基于集合的方法和迭代/过程方法的比较,以及一个优化练习208 4.7 总结214 第5章 算法和复杂性215 5.1 你有一个1夸特的硬币吗?215 5.1.1 如何从零钱罐中取回1夸特钱216 5.1.2 有时零钱罐中没有1夸特的硬币...