sql语句执行时间差别很大的问题。

duzq1985

线上的数据库出现查询变慢的情况，然后查看慢查询日志，基本都是

1. 
   
2. # Query_time: 53.424102  Lock_time: 0.000098 Rows_sent: 0  Rows_examined: 450
   
3. SET timestamp=1334569593;
   
4. select  id, toid, datetime, state, cinmessage
   
5.             from cin_offlinemessages_355
   
6.             where toid = 428562355  and state=0 and datetime >= date_add(now(),interval -7 day) order by id;

复制代码

还有

1. 
   
2. # Query_time: 25.639119  Lock_time: 0.000088 Rows_sent: 0  Rows_examined: 0
   
3. SET timestamp=1334570111;
   
4. update cin_offlinemessages_355
   
5.                 set state = 2 where toid = 630152030 and state = 0 and datetime >= date_add(now(),interval -7 day);

复制代码

其中update语句很少，大概几十条慢查询select才会出现一条update。但是拿这些刚生成的慢查询语句自己登陆线上数据库去查询的时间却都是 Empty set (0.01 sec)，结果集为空是正常的（几乎所有的慢查询结果都为空），但是时间也几乎没
什么消耗，这为什么慢查询里面会消耗这么长的时间呢？请高手推测下原因。
补充：explain查询语句，可以用到toid的索引。
query_cache没有开启。

龙雪刚

一般出现这样的情况（线上运行很慢，自己单独运行又很快）是由于锁竞争导致的。这条SQL要扫描450条数据，可以通过建立联合索引减少扫描数据
建议建立联合索引(toid ,state ,datetime ) 再看慢查询。另外SQL语句中的now()函数，如果有应用层，可以在应用层生成时间值直接传进SQL中，这样可以转移压力(能在应用层得到的就不要用mysql的).另外一次改进无法得到最优，建议多尝试。

ning_lianjie

楼主最好贴一下执行计划.
根据现在的情况:
1.查询用到toid索引
2.时间消耗在where条件的比较中.
3.另外最后的排序,如果结果集比较大,可能会把结果集写在硬盘上.

龙雪刚

查到的结果集是空的，排序应该没有影响吧？

bikong0411

回复 4# 龙雪刚


    都空了还有啥影响！！！

horizonhyg

复 1# duzq1985


    1、并不是空了就没有影响，因为没有cache，即便是空也会去查询。
    2、不知道楼主用的什么引擎，如果是Mysiam，表锁出现这个时间是就比较容易解释了。
    3、给出explain语句。
    4、更新空数据的控制一下，查出来用主键去更新，速度更快。

龙雪刚

回复 6# horizonhyg

你估计理解错了，“有没有影响”，是说如果where后过滤条件得到的结果集是空的，那么这个时候再去order by 是不是没有影响.

horizonhyg

回复 7# 龙雪刚


    哦，看错了，没看到你下一条。。我说咋没看到“影不影响”呢，结果是空的，排序没啥意思了。不过联合索引要看选择性，而且后边有range查询，那么建和单独建一个toid没啥区别。

龙雪刚

回复 8# horizonhyg


    后面的确有range,但是处于联合索引中最后一个字段。根据最左匹配原则，能够使用到联合索引。如果仅在toid 列建议索引，肯定还需要后过滤。

horizonhyg

回复 9# 龙雪刚


    恩，你说的对，我卡了，刚才没有考虑清楚。