sql分析

 UPDATE t_order_thirdchild AS t
 INNER JOIN t_order_attachment AS s ON s.forderId = t.fthirdchildId
 SET t.fuserstate = 5,
    t.fuser2state = 24,
    t.fupdated = now(),
    t.ffinished = now()
 WHERE
    s.fattachmentName = 'receipt'
    AND datediff(curdate(), s.fcreated) = 7
    AND t.fuser2state = 23
    AND t.fuserstate = 4;
 对比
 UPDATE t_order_thirdchild AS t
 INNER JOIN t_order_attachment AS s ON s.forderId = t.fthirdchildId
 SET t.fuserstate = 5,
    t.fuser2state = 24,
    t.fupdated = now()
 WHERE
   s.fattachmentName = 'receipt'
   AND s.fcreated < DATE_ADD(CURDATE(), INTERVAL - 7 DAY)
   AND t.fuser2state = 23
   AND t.fuserstate = 4;
   datediff(curdate(), s.fcreated)=7和s.fcreated < DATE_ADD(CURDATE(), INTERVAL - 7 DAY)的对比分析
  1.datediff(curdate(), s.fcreated)=7每一列都要进行计算,效率低
  2.s.fcreated < DATE_ADD(CURDATE(), INTERVAL -7 DAY),只计算一次DATE_ADD(CURDATE(), INTERVAL -7 DAY)效率高
  3.datediff(curdate(), s.fcreated)=7中s.fcreated无法添加索引
  4.s.fcreated < DATE_ADD(CURDATE(), INTERVAL -7 DAY)中s.fcreated可以添加索引
  5.s.fcreated < DATE_ADD(CURDATE(), INTERVAL - 7 DAY),运算符的左边尽量不要使用函数.