mysql - 查询执行时间较长，大约 120 秒

Question

1. 我的查询执行时间很长，大约 120 秒。
2. 任何人都可以帮我重写这个查询。
3. 请参阅下面的解释计划和表格结构。
4. 我们经常在慢日志中收到此查询。

查询:

   select count(*) as col_0_0_  
   from tab4 tab3, 
        tab5 tab1, 
        tab6 tt2aghierar_ 
   where tab3.own_domain_id = 263 
     and tab3.id=tab1.resource_id  
     and tab1.hierarchy_id = 18 
     and tab2.id=tab1.pattern_id;

解释计划:

   +----+-------------+--------------+--------+----------------------------------------------------------+---------------------+---------+----------------------------------+--------+-------------+
   | id | select_type | table        | type   | possible_keys                                            | key                 | key_len | ref                              | rows   | Extra       |
   +----+-------------+--------------+--------+----------------------------------------------------------+---------------------+---------+----------------------------------+--------+-------------+
   |  1 | SIMPLE      | tab1 | ref    | hierarcyHierarchyId,domain_id_hierarchy_id_resource_type | hierarcyHierarchyId | 4       | const                            | 111456 |             |
   |  1 | SIMPLE      | tab2 | eq_ref | PRIMARY                                                  | PRIMARY             | 4       | comp1.tab1.pattern_id  |      1 | Using index |
   |  1 | SIMPLE      | tab3   | ref    | id,own_domain_id                                         | id                  | 4       | comp1.tab1.resource_id |      1 | Using where |
   +----+-------------+--------------+--------+----------------------------------------------------------+---------------------+---------+----------------------------------+--------+-------------+
   3 rows in set (1.62 sec)

表结构:

   mysql> show create table tab4\G
   *************************** 1. row ***************************
          Table: tab4
   Create Table: CREATE TABLE `tab4` (
     `id` int(11) NOT NULL AUTO_INCREMENT,
     `keyword_name` varchar(255) DEFAULT NULL,
     `keyword_value` varchar(255) DEFAULT NULL,
     `type` int(11) DEFAULT NULL,
     `description` varchar(2000) DEFAULT NULL,
     `own_domain_id` int(11) DEFAULT NULL,
     `rank_check` int(11) DEFAULT NULL,
     `rank1` int(11) DEFAULT NULL,
     `rank2` int(11) DEFAULT NULL,
     `rank3` int(11) DEFAULT NULL,
     `yesterday_entrances` int(11) DEFAULT NULL,
     `week_entrances` int(11) DEFAULT NULL,
     `current_ctr` float(16,4) DEFAULT NULL,
     `monthly_search_volume` int(11) DEFAULT NULL,
     `avg_monthly_search_volume` int(11) DEFAULT NULL,
     `traffic_increase` int(11) DEFAULT NULL,
     `rank_improvement` int(11) DEFAULT NULL,
     `rank_update_date` date DEFAULT NULL,
     `top_rank_targeturl_id` int(11) DEFAULT NULL,
     `frequency` int(10) DEFAULT '1',
     `score` float DEFAULT NULL,
     `create_date` datetime DEFAULT NULL,
     `bing_rank1` int(10) DEFAULT NULL,
     `bing_rank2` int(10) DEFAULT NULL,
     `yesterday_bing_entrances` int(11) DEFAULT NULL,
     `bing_rank_improvement` int(11) DEFAULT NULL,
     KEY `id` (`id`),
     KEY `keyword_name` (`keyword_name`),
     KEY `own_domain_id` (`own_domain_id`,`rank_check`),
     KEY `rank_check` (`rank_check`)
   ) ENGINE=InnoDB AUTO_INCREMENT=720988063 DEFAULT CHARSET=utf8
   /*!50100 PARTITION BY RANGE (`rank_check`)
   (PARTITION p0 VALUES LESS THAN (0) ENGINE = InnoDB,
    PARTITION p1 VALUES LESS THAN (1) ENGINE = InnoDB,
    PARTITION p2 VALUES LESS THAN (2) ENGINE = InnoDB,
    PARTITION pEOW VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
   1 row in set (0.03 sec)
   
   mysql> show create table tab5\G
   *************************** 1. row ***************************
          Table: tab5
   Create Table: CREATE TABLE `tab5` (
     `id` int(10) NOT NULL AUTO_INCREMENT,
     `domain_id` int(10) NOT NULL DEFAULT '0',
     `hierarchy_id` int(10) NOT NULL DEFAULT '0',
     `resource_id` int(10) NOT NULL DEFAULT '0',
     `resource_type` int(10) NOT NULL DEFAULT '0',
     `pattern_id` int(10) NOT NULL,
     `top_hierarchy_id` int(11) NOT NULL,
     PRIMARY KEY (`id`),
     UNIQUE KEY `domain_id_resource_id_resource_type_top_hierarchy_id` (`domain_id`,`resource_id`,`resource_type`,`top_hierarchy_id`),
     KEY `hierarcyHierarchyId` (`hierarchy_id`),
     KEY `domain_id_hierarchy_id_resource_type` (`resource_id`,`hierarchy_id`)
   ) ENGINE=MyISAM AUTO_INCREMENT=126564587 DEFAULT CHARSET=utf8
   1 row in set (0.00 sec)
   
   mysql> show create table tab6\G
   *************************** 1. row ***************************
          Table: tab6
   Create Table: CREATE TABLE `tab6` (
     `id` int(10) NOT NULL AUTO_INCREMENT,
     `pattern` varchar(500) NOT NULL DEFAULT '0',
     `hierarchy_id` int(10) NOT NULL DEFAULT '0',
     `match_level` int(10) NOT NULL DEFAULT '0',
     `create_date` datetime DEFAULT NULL,
     `flg` int(10) DEFAULT NULL,
     `is_regular_expression` int(2) DEFAULT NULL,
     `is_case_sensitive` int(2) DEFAULT NULL,
     PRIMARY KEY (`id`),
     KEY `hierarchy_id` (`hierarchy_id`)
   ) ENGINE=MyISAM AUTO_INCREMENT=2293 DEFAULT CHARSET=utf8

Answer 1

您可能想尝试此查询而不是执行完整联接:

select count(*) as col_0_0_  
from tab4 tab3
inner join tab5 tab1
    on tab3.id = tab1.resource_id
inner join tab6 tab2
    on tab2.id = tab1.pattern_id
where 
    tab3.own_domain_id = 263 
    and tab1.hierarchy_id = 18 

此外，您可能需要以下列上的索引:

tab4.own_domain_id
tab5.hierarchy_id
tab5.resource_id
tab5.pattern_id