dowm41315 2013-02-07 10:58
浏览 34
已采纳

优化MySQL计数查询,提供完整信息

I have this query that runs for over 3 minutes. I am looking to optimize to run in a few seconds:

SELECT Count(DISTINCT l.licitatii_id) c 
FROM   licitatii_ue l 
       INNER JOIN domenii_licitatii dl 
               ON l.licitatii_id = dl.licitatii_id 
                  AND dl.tip_licitatie = '2' 
       INNER JOIN domenii d 
               ON dl.domenii_id = d.domenii_id 
                  AND d.tip_domeniu = '1' 
                  AND d.status = 1 
WHERE  l.status = 1 
       AND l.data_limita >= '1357768800' 

explain shows:

*** row 1 ***
          table:  d
           type:  ref
  possible_keys:  PRIMARY,key_status_tip_domeniu,ind_v1
            key:  key_status_tip_domeniu
        key_len:  9
            ref:  const,const
           rows:  39
          Extra:  Using where; Using index
*** row 2 ***
          table:  dl
           type:  ref
  possible_keys:  PRIMARY,licitatii_id,licitatii_id_2
            key:  PRIMARY
        key_len:  5
            ref:  web1db1.d.domenii_id,const
           rows:  1882
          Extra:  Using index
*** row 3 ***
          table:  l
           type:  eq_ref
  possible_keys:  PRIMARY,data_limita
            key:  PRIMARY
        key_len:  4
            ref:  web1db1.dl.licitatii_id
           rows:  1
          Extra:  Using where

Schema:

licitatii_ue

  PRIMARY KEY (`licitatii_id`),
  UNIQUE KEY `nr_ue` (`nr_ue`),
  KEY `nume` (`nume`),
  KEY `tip_licitatie` (`tip_licitatie`),
  KEY `surse_ue_id` (`surse_ue_id`),
  KEY `data_publicarii` (`data_publicarii`),
  KEY `tara_id` (`tara_id`),
  KEY `creat` (`creat`),
  KEY `data_limita` (`data_limita`),
  FULLTEXT KEY `nume_fulltext` (`nume`,`descriere`)
) ENGINE=MyISAM

/*Index Information*/
---------------------

Table         Non_unique  Key_name         Seq_in_index  Column_name      Collation  Cardinality  Sub_part  Packed  Null    Index_type  Comment  
------------  ----------  ---------------  ------------  ---------------  ---------  -----------  --------  ------  ------  ----------  ---------
licitatii_ue           0  PRIMARY                     1  licitatii_id     A               136456    (NULL)  (NULL)          BTREE                
licitatii_ue           0  nr_ue                       1  nr_ue            A               (NULL)    (NULL)  (NULL)  YES     BTREE                
licitatii_ue           1  nume                        1  nume             A                68228    (NULL)  (NULL)          BTREE                
licitatii_ue           1  tip_licitatie               1  tip_licitatie    A                   13    (NULL)  (NULL)  YES     BTREE                
licitatii_ue           1  surse_ue_id                 1  surse_ue_id      A                    1    (NULL)  (NULL)          BTREE                
licitatii_ue           1  data_publicarii             1  data_publicarii  A               136456    (NULL)  (NULL)  YES     BTREE                
licitatii_ue           1  tara_id                     1  tara_id          A                   41    (NULL)  (NULL)  YES     BTREE                
licitatii_ue           1  creat                       1  creat            A               136456    (NULL)  (NULL)  YES     BTREE                
licitatii_ue           1  data_limita                 1  data_limita      A                  770    (NULL)  (NULL)  YES     BTREE                
licitatii_ue           1  nume_fulltext               1  nume             (NULL)               1    (NULL)  (NULL)          FULLTEXT             
licitatii_ue           1  nume_fulltext               2  descriere        (NULL)               1    (NULL)  (NULL)  YES     FULLTEXT   

domenii_licitatii

  PRIMARY KEY (`domenii_id`,`tip_licitatie`,`licitatii_id`),
  KEY `licitatii_id` (`licitatii_id`,`tip_licitatie`),
  KEY `licitatii_id_2` (`licitatii_id`)
) ENGINE=InnoDB 

/*Index Information*/
---------------------

Table              Non_unique  Key_name        Seq_in_index  Column_name    Collation  Cardinality  Sub_part  Packed  Null    Index_type  Comment  
-----------------  ----------  --------------  ------------  -------------  ---------  -----------  --------  ------  ------  ----------  ---------
domenii_licitatii           0  PRIMARY                    1  domenii_id     A                   20    (NULL)  (NULL)          BTREE                
domenii_licitatii           0  PRIMARY                    2  tip_licitatie  A                  228    (NULL)  (NULL)          BTREE                
domenii_licitatii           0  PRIMARY                    3  licitatii_id   A               430634    (NULL)  (NULL)          BTREE                
domenii_licitatii           1  licitatii_id               1  licitatii_id   A               430634    (NULL)  (NULL)          BTREE                
domenii_licitatii           1  licitatii_id               2  tip_licitatie  A               430634    (NULL)  (NULL)          BTREE                
domenii_licitatii           1  licitatii_id_2             1  licitatii_id   A               430634    (NULL)  (NULL)          BTREE                

domenii

  PRIMARY KEY (`domenii_id`),
  KEY `key_status_tip_domeniu` (`status`,`tip_domeniu`),
  KEY `ind_v1` (`domenii_id`,`tip_domeniu`,`status`),
  KEY `parent_id` (`parent_id`)
) ENGINE=InnoDB 

/*Index Information*/
---------------------

Table    Non_unique  Key_name                Seq_in_index  Column_name  Collation  Cardinality  Sub_part  Packed  Null    Index_type  Comment  
-------  ----------  ----------------------  ------------  -----------  ---------  -----------  --------  ------  ------  ----------  ---------
domenii           0  PRIMARY                            1  domenii_id   A                   79    (NULL)  (NULL)          BTREE                
domenii           1  key_status_tip_domeniu             1  status       A                    4    (NULL)  (NULL)  YES     BTREE                
domenii           1  key_status_tip_domeniu             2  tip_domeniu  A                   11    (NULL)  (NULL)          BTREE                
domenii           1  ind_v1                             1  domenii_id   A                   79    (NULL)  (NULL)          BTREE                
domenii           1  ind_v1                             2  tip_domeniu  A                   79    (NULL)  (NULL)          BTREE                
domenii           1  ind_v1                             3  status       A                   79    (NULL)  (NULL)  YES     BTREE                
domenii           1  parent_id                          1  parent_id    A                   79    (NULL)  (NULL)          BTREE  
  • 写回答

2条回答 默认 最新

  • doulei8861 2013-02-08 01:37
    关注

    You really need compound indexes on your tables.

    table             index
    licitatii         ( status, data_limita, licitatii_id )
    domenii_licitatii ( licitatii_id, tip_licitatie )
    domenii           ( domenii_id, tip_domeniu, status )
    

    You should have compound keys that help to optimize your joins AND where criteria. Additionally, as I have them included, the query engine can find the values for the joins directly from the indexes instead of going back to the pages of actual data. If you only have a single column in the index, the query would then go to the raw data to join. See what it does for you.

    Ok, yes, the other two indexes and you added the third -- didn't help. Then I would try reversing the query some... Not exactly knowing the data, but it looks like they are some type of lookup category elements. Try

    select STRAIGHT_JOIN
          Count(DISTINCT dl.licitatii_id) c
       from
          domenii d
             join domenii_licitatii dl
                on d.domenii_id = dl.domenii_id
               AND dl.tip_licitatie = '2'
                JOIN licitatii_ue L
                   on dl.licitatii_id = L.licitatii_id
                  AND L.status = 1
                  AND L.Data_Limita >= '1357768800'
       where
              d.tip_domeniu = '1'
          and d.status = 1
    

    added by Pentium10

    Explain show the following:

    +---+--------+----+--------+---------------------------------------+------------------------+---+----------------------------+------+--------------------------+
    | 1 | SIMPLE | d  | ref    | PRIMARY,key_status_tip_domeniu,ind_v1 | key_status_tip_domeniu | 9 | const,const                |   39 | Using where; Using index |
    | 1 | SIMPLE | dl | ref    | PRIMARY,licitatii_id,licitatii_id_2   | PRIMARY                | 5 | web1db1.d.domenii_id,const | 1882 | Using index              |
    | 1 | SIMPLE | L  | eq_ref | PRIMARY,data_limita,i1                | PRIMARY                | 4 | web1db1.dl.licitatii_id    |    1 | Using where              |
    +---+--------+----+--------+---------------------------------------+------------------------+---+----------------------------+------+--------------------------+
    
    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
查看更多回答(1条)

报告相同问题?

悬赏问题

  • ¥20 iqoo11 如何下载安装工程模式
  • ¥15 本题的答案是不是有问题
  • ¥15 关于#r语言#的问题:(svydesign)为什么在一个大的数据集中抽取了一个小数据集
  • ¥15 C++使用Gunplot
  • ¥15 这个电路是如何实现路灯控制器的,原理是什么,怎么求解灯亮起后熄灭的时间如图?
  • ¥15 matlab数字图像处理频率域滤波
  • ¥15 在abaqus做了二维正交切削模型,给刀具添加了超声振动条件后输出切削力为什么比普通切削增大这么多
  • ¥15 ELGamal和paillier计算效率谁快?
  • ¥15 蓝桥杯单片机第十三届第一场,整点继电器吸合,5s后断开出现了问题
  • ¥15 file converter 转换格式失败 报错 Error marking filters as finished,如何解决?