记录一个sql优化后大大提高查询效率的情形

最新推荐文章于 2024-12-11 09:00:38 发布

最新推荐文章于 2024-12-11 09:00:38 发布 · 454 阅读

文章标签：

#sql优化 #索引 #concat和substr #拆分

数据库专栏收录该内容

14 篇文章

订阅专栏

本文介绍了一种通过优化SQL查询来提高数据库查询效率的方法。通过对原始SQL语句的重构，将原本复杂的函数运算分解为简单的条件判断，实现了从4.5秒到0.05秒的查询性能飞跃。

众所周知，sql写得怎么样，对于查询效率的影响是颇大的。下面看一个比较普通的查询：

假设一张表有event_date和event_time2个字段分别表示日期和时间，现在直接给你一个时间字符串，这个时间字符串是“日期+时间”的组合，现在需要按时间范围过滤出一部分数据。如果你不转弯的话，很有可能你会按照惯性思维使用concat(event_date, event_time)连接函数，如：

SELECT A, MAX(B) AS B,SUBSTR(CONCAT(event_date, event_time),1,12)AS tran_time 
       FROM tablename 
            WHERE SUBSTR(CONCAT(event_date, event_time),1,12) >= :start_time  
            AND SUBSTR(CONCAT(event_date, event_time),1,12) < :end_time  
     AND A = :A  	
        GROUP BY A,tran_time  
        ORDER BY A,tran_time ASC

start_time和end_time是传递过来的由“日期+时间”组合成的字符串。

这是比较容易想到的传统思维，但这种sql语句的查询效率老低了。差不多查一次需要4.5s+，因为不仅仅需要用到max函数，还需按SUBSTR(CONCAT(event_date, event_time),1,12) 这个计算出来的字段group by，最主要的是在where语句里，对2个时间的字段需要进行多重函数运算后看看那些记录符合你所规定的时间范围~~ 这样，在不知不觉中，造成了全表扫描，效率极低，特别是在表中数据量比较大的时候。

我们已经知道，如果一张表是数据量比较大的表，并且一般都用与于查询时，索引的使用会给我们带来极大的性能优化。考虑到使用索引，我们应该尽量避免where语句里的字段不进行函数作用（因为即使你对where下的字段建立了索引，但如果你对他们进行了函数运算，索引是失效的），所以我们考虑把它们尽量独立开来。

基于上述思想，改正如下：

先对A, evnet_date和event_time三个字段建立索引。

SELECT A, MAX(B) AS B,SUBSTR(CONCAT(event_date, event_time),1,12)AS tran_time 
       FROM tablename  
            WHERE event_date >= :start_date AND event_time >= :start_time  
       AND event_date <= :end_date AND event_time < :end_time   
     AND A = :A  	
        GROUP BY A,tran_time  
        ORDER BY A,tran_time ASC

可以看出我们把event_date和event_time的起止时间都“拆”开来了，所以只需在传过来的起止时间字符串上截取响应的日期和时间字符串给sql语句即可。

测试检查发现，2句sql查询出的结果是一致的，说明他们是等效的。

改正之后，发现效率提升至0.05s左右，性能得到极大提升！！！

这句优化后的sql语句是突破了sql语句字段去适应外部传过来的时间字符串，而是从sql优化的角度看，让外部的条件去适应sql语句的查询，这种思想我之前很少有，也算是一种思维的突破了！