MYSQL 查询优化请教.

digitalchina

-- Table "isa" DDL

CREATE TABLE `isa` (
  `KEYIN` varchar(255) default NULL,
  `agt` varchar(255) default NULL,
  `TBD` char(10) default NULL,
  `ISA` varchar(255) default NULL,
  `peird` varchar(255) default NULL,
  `val` varchar(255) default NULL,
  `G` varchar(255) default NULL,
  `H` varchar(255) default NULL,
  `I` varchar(255) default NULL,
  KEY `idx1` (`TBD`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;


查询:

explain  extended
select @t_count := count(*),
       @t_total :=sum(bbb.eee),
       @t_num1 := sum( if(bbb.eee =1 ,1 ,0)),
       @t_num2 := sum( if(bbb.eee =2 ,1 ,0)),
       @t_num3 := sum( if(bbb.eee =3 ,1 ,0))

from (
   select ( if(substring(isa.TBD,1,1)=substring('0300116938',1,1),1,0)
          + if(substring(tbd,2,1)=substring('0300116938',2,1),1,0)
          + if(substring(tbd,3,1)=substring('0300116938',3,1),1,0) ) as  eee
         from isa    ) as bbb;


我打算变成 :

explain  extended
select @t_count := count(@eee),  
    @eee :=( if(substring(isa.TBD,1,1)=substring('0300116938',1,1),1,0)
          + if(substring(tbd,2,1)=substring('0300116938',2,1),1,0)
          + if(substring(tbd,3,1)=substring('0300116938',3,1),1,0) )  ,
       @t_total :=sum(@eee),
       @t_num1 := sum( if(@eee =1 ,1 ,0)),
       @t_num2 := sum( if(@eee =2 ,1 ,0)),
       @t_num3 := sum( if(@eee =3 ,1 ,0))
         from isa   group by isa.TBD ;

但是结果不对.

实际应用中 '0300116938' 是用变量传到存储过程里的,  这里只用一个常数来优化SQL语句.
该怎样优化才正确?

[ 本帖最后由 digitalchina 于 2008-6-11 13:47 编辑 ]

digitalchina

自己又试了一下, 增加了一个总为空的索引字段ISA

KEY `IXD2` (`TBD`,`ISA`)

使用这个查询结果正确.  但是EXPLAIN 提示:
"Using index; Using temporary; Using filesort "  是不是还可以优化???   

因为实际运行中通常会有100W以上的数据,所以想尽可能少些遍历..

SQL 如下:



explain  extended
select @t_count := count(  if(substring(isa.TBD,1,1)=substring('0300116938',1,1),1,0)
          + if(substring(isa.TBD,2,1)=substring('0300116938',2,1),1,0)
          + if(substring(isa.TBD,3,1)=substring('0300116938',3,1),1,0)),
      

    @eee :=( if(substring(isa.TBD,1,1)=substring('0300116938',1,1),1,0)
          + if(substring(tbd,2,1)=substring('0300116938',2,1),1,0)
          + if(substring(tbd,3,1)=substring('0300116938',3,1),1,0) )  ,

       @t_total :=sum(  if(substring(isa.TBD,1,1)=substring('0300116938',1,1),1,0)
          + if(substring(isa.TBD,2,1)=substring('0300116938',2,1),1,0)
          + if(substring(isa.TBD,3,1)=substring('0300116938',3,1),1,0)),
       @t_num1 := sum(if(
            if(substring(isa.TBD,1,1)=substring('0300116938',1,1),1,0)
          + if(substring(isa.TBD,2,1)=substring('0300116938',2,1),1,0)
          + if(substring(isa.TBD,3,1)=substring('0300116938',3,1),1,0) =1
         ,1 ,0)),
       @t_num2 := sum( if(  if(substring(isa.TBD,1,1)=substring('0300116938',1,1),1,0)
          + if(substring(isa.TBD,2,1)=substring('0300116938',2,1),1,0)
          + if(substring(isa.TBD,3,1)=substring('0300116938',3,1),1,0) =2 ,1 ,0)),
       @t_num3 := sum( if(  if(substring(isa.TBD,1,1)=substring('0300116938',1,1),1,0)
          + if(substring(isa.TBD,2,1)=substring('0300116938',2,1),1,0)
          + if(substring(isa.TBD,3,1)=substring('0300116938',3,1),1,0) =3 ,1 ,0))



         from isa   group by ISA.isa ;

yueliangdao0608

Use order by null following your sql statement.

digitalchina

1 实测:  30W数据行
  查询1   5.218 秒
查询2    未验证,结果错误.
查询3    172.5秒.


看来不用优化了.  


不过还想问问,这样的表需要用INNODB吗?   是不是MYISAM更快?

数据通常会有100W行. 平时主要是查询,每月进行一次写档,然后转入历史档.

yueliangdao0608

原帖由 digitalchina 于 2008-6-12 10:06 发表
1 实测:  30W数据行
  查询1   5.218 秒
查询2    未验证,结果错误.
查询3    172.5秒.


看来不用优化了.  


不过还想问问,这样的表需要用INNODB吗?   是不是MYISAM更快?

数据通常会有100W行.  ...

Yeah.

chuhongze

原帖由 digitalchina 于 2008-6-12 10:06 发表
1 实测:  30W数据行
  查询1   5.218 秒
查询2    未验证,结果错误.
查询3    172.5秒.


看来不用优化了.  


不过还想问问,这样的表需要用INNODB吗?   是不是MYISAM更快?

数据通常会有100W行.  ...

digitalchina

以上查询中 substring('0300116938',1,1) 可以直接用变量先算出来, 速度进一步提高,

特别对于查询3提高很明显,但是还是比不过查询1.
以下是我最终采用的版本:    (如果大家还有更好的办法,请提意见)

set key1=substring('0300116938',1,1)
set key2=substring('0300116938',2,1)
set key3=substring('0300116938',3,1)


explain  extended
select @t_count := count(*),
       @t_total :=sum(bbb.eee),
       @t_num1 := sum( if(bbb.eee =1 ,1 ,0)),
       @t_num2 := sum( if(bbb.eee =2 ,1 ,0)),
       @t_num3 := sum( if(bbb.eee =3 ,1 ,0))

from (
   select ( if(substring(isa.TBD,1,1)=KEY1,1,0)
          + if(substring(tbd,2,1)=KEY2,1,0)
          + if(substring(tbd,3,1)=KEY3,1,0) ) as  eee
         from isa    ) as bbb;