mysql时间类型字段的优化技巧

太过爱你忘了你带给我的痛 2022-03-20 18:07 471阅读 0赞

### 文章目录 ###

*  mysql时间类型字段的优化技巧
 *   *  第一种实现
     *  第二种实现
     *  两种实现对比

# mysql时间类型字段的优化技巧 #

最近开发一个功能时需要查询今天开播的主播用户。在业务上我们有一个数据表存储了主播每次开播的记录。表中有一个时间类型的字段表示主播开播时间。(为了业务安全，这里用time字段表明实际字段. record表示数据表)

## 第一种实现 ##

在开始实现的时候想到的就是 **time = 今天**这种实现逻辑上比较直接。

select distinct userId
      from record
     where date_format(time, '%Y%m%d')= 
     date_format(now(), '%Y%m%d')

可以看到关键就是`where`后的`date_format`函数了。对日期格式进行转换。这个`sql`在测试环境跑没有什么问题，在线上跑之后一直报`timeout`。最后查看了慢语句汇总。发现上面的`sql`也俨然在列。

## 第二种实现 ##

实现思路

select distinct userId
         from record
         where  
         time >= 'YYYY-mm-dd 00:00:00'
         and  time <= 'YYYY-mm-dd 23:59:59'

具体实现（也可以从服务层穿参这样sql显的就不复杂了）：

select distinct userId from record
     where time >=
     (SELECT  str_to_date(DATE_FORMAT( NOW(), '%Y-%m-%d'), '%Y-%m-%d %H:%i:%s'))
    and time <=(select DATE_ADD( DATE_ADD(str_to_date(DATE_FORMAT(NOW(),'%Y-%m-%d'),'%Y-%m-%d %H:%i:%s'), INTERVAL 1 DAY),INTERVAL -1 SECOND));

## 两种实现对比 ##

虽然两种实现都可以完成这个功能，但是第二种在线上跑没有性能问题。此时查看`sql`的执行计划`explain`。  
发现两个的执行计划的`row`相差*1000*倍，并且单次测量第一种执行时间需要*3000多毫秒*，而第二种只需要*4ms*。

<table> 
 <thead> 
  <tr> 
   <th>first row</th> 
   <th>second row</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>3000</td> 
   <td>3</td> 
  </tr> 
 </tbody> 
</table>

<table> 
 <thead> 
  <tr> 
   <th>first time</th> 
   <th>second time</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>3300ms</td> 
   <td>4ms</td> 
  </tr> 
 </tbody> 
</table>

原因是date\_format这种需要对日期进行转换，需要查询出所有行进行过滤。而第二种可以利用在time上建立索引，查询极快。