线上有这样一个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
相关推荐
7. **优化SQL语句**:在存储过程中,确保使用最有效的SQL语句,例如,避免全表扫描,使用索引,减少JOIN操作,以及正确使用临时表和变量。 8. **减少不必要的数据检索**:使用SELECT *应谨慎,只选择需要的列可以...
第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语句,...
这四个原则贯穿于整个SQL优化过程,是提升查询效率的基础。 1. 减少查询次数:通过联合查询、子查询优化和存储过程等方式,将多次数据库交互合并为一次,降低网络传输和数据库处理的压力。 2. 减小数据量:通过...
存储过程在创建时进行一次性编译,之后的执行会使用已编译的计划,从而避免了每次执行时的语法解析和编译步骤,理论上应该更快。然而,当存储过程中的参数变化导致执行计划不适用时,SQL Server可能会为每次不同的...
综上所述,SQL语句优化是一个多方面的过程,涉及对SQL语句的定位、分析、调整等多个环节。通过合理地利用数据库管理系统提供的各种工具和技术,可以有效地提高SQL语句的执行效率,进而提升整个系统的性能。在实际...
随后《收获,不止SQL优化——抓住SQL的本质》指引大家学会等价改写、过程包优化、高级SQL、分析函数、需求优化这些相关的五大神功。有点头晕,能否少一点套路?淡定,这还是“术”的范畴,依然是教你如何解决问题,...
SQL改写优化是数据库性能提升的关键技术之一,它涉及到如何通过改进SQL查询语句的结构、语法或逻辑来提高查询效率...通过学习这些案例,我们可以更好地理解如何在实际工作中进行SQL优化,提升数据库系统的性能和效率。
文章首先介绍了如何利用`CTAS`(Create Table As Select)语句快速创建一个包含大量数据的表,通过多次插入`dba_objects`表中的数据,可以迅速生成几千万条记录,为后续的测试和优化提供了一个理想的实验环境。...
SQL语句优化是数据库性能提升的关键环节,尤其是在大数据量的环境下。优化SQL语句能够显著提高查询速度,减少...同时,监控和分析SQL执行计划也是优化过程中的重要步骤,它能帮助我们找出性能瓶颈并针对性地进行改进。
, 随后《收获,不止SQL优化——抓住SQL的本质》指引大家学会等价改写、过程包优化、高级SQL、分析函数、需求优化这些相关的五大神功。有点头晕,能否少一点套路?淡定,这还是“术”的范畴,依然是教你如何解决问题...
如果某一操作包含大量的 Transaction-SQL 代码,,或分别被多次执行,那么存储过程要比批处理的执行速度快很多,因为存储过程是预编译的,在首次运行一个存储过程时查询优化器对其进行分析优化,并给出最终被存在系统...
标题和描述均提到了“SQL语句优化技术分析”,这一主题是数据库管理和性能提升的关键领域。在数据库系统中,SQL(Structured ...在日常开发和维护过程中,应持续关注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性能问题的原因及解决方法 SQL性能问题是数据库管理中常见的挑战之一,它直接影响到系统的响应时间和整体性能。理解SQL性能问题的原因及其解决方法对于提升系统的运行效率至关...
SQL查询优化是数据库管理系统中至关重要的一环,它旨在提高数据检索速度,降低系统资源消耗,提升整体应用性能。SQL查询优化主要包括以下几个方面: 1. 合理使用常量优化查询: 在编写SQL语句时,应尽量避免将字段...