mysql - 两个连接查询需要 540 秒才能运行 - 我怎样才能加快速度？

Question

一个特定的查询需要非常的时间来运行，大约 540 秒(是的，9 分钟!)。这会导致负载尖峰。

有没有一种方法可以重写查询 - 或者添加索引 - 来加快速度？ 发生了。

查询

select DISTINCT tab3.id 
from `tab1`  
left join `tab2` on tab2.element_id = tab1.resource_id 
    and tab1.resource_type = 2 
    and tab2.element_type_id = 1 
left join `tab3` on tab2.tab3nt_id = tab3.id  
where tab1.group_tab1_id = 1 
    and tab1.domain_id = 2 
    and tab3.domain_id = 2 
    and tab3.tab3nt_start_date >= '2012-12-01'  
    and tab3.tab3nt_start_date <= '2013-03-01'

解释计划

+----+-------------+-------+--------+-------------------------------------------------------------------------------------+----------------------------+---------+-------------------------------+------+-------------------------------------------+
| id | select_type | table | type   | possible_keys                                                                       | key                        | key_len | ref                           | rows | Extra                                     |
+----+-------------+-------+--------+-------------------------------------------------------------------------------------+----------------------------+---------+-------------------------------+------+-------------------------------------------+
|  1 | SIMPLE      | tab1   | ref    | group_tab1_id_resource_id,domain_id,domain_id_resource_type,tgtr_resource_domain_key | domain_id                  | 13      | const,const,const           |  962 | Using where; Using index; Using temporary |
|  1 | SIMPLE      | tab2   | ref    | FK_tab3nt,element_type_id_element_id                                                 | element_type_id_element_id | 8       | const,svr.tab1.resource_id  |   14 | Using where                               |
|  1 | SIMPLE      | tab3   | eq_ref | PRIMARY,domain_id_tab3nt_type_cd                                                     | PRIMARY                    | 4       | svr.tab2.tab3nt_id          |    1 | Using where                               |
+----+-------------+-------+--------+-------------------------------------------------------------------------------------+----------------------------+---------+-------------------------------+------+--------

表结构

mysql> show create table tab1\G
*************************** 1. row ***************************
       Table: tab1
Create Table: CREATE TABLE `tab1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `domain_id` int(11) NOT NULL,
  `group_tab1_id` int(11) NOT NULL,
  `resource_id` int(11) NOT NULL,
  `resource_type` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `group_tab1_id_resource_id` (`group_tab1_id`,`resource_id`),
  KEY `domain_id` (`domain_id`,`group_tab1_id`,`resource_type`,`resource_id`),
  KEY `domain_id_resource_type` (`domain_id`,`resource_type`),
  KEY `tgtr_resource_domain_key` (`resource_id`,`domain_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2039461 DEFAULT CHARSET=latin1
1 row in set (0.01 sec)

mysql> show create table tab2\G
*************************** 1. row ***************************
       Table: tab2
Create Table: CREATE TABLE `tab2` (
  `id` int(255) NOT NULL AUTO_INCREMENT,
  `element_type_id` int(11) NOT NULL,
  `tab3nt_id` int(11) NOT NULL,
  `element_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `FK_tab3nt` (`tab3nt_id`),
  KEY `element_type_id_element_id` (`element_type_id`,`element_id`)
) ENGINE=InnoDB AUTO_INCREMENT=53195543 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> show create table tab3\G
*************************** 1. row ***************************
       Table: tab3
Create Table: CREATE TABLE `tab3` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `create_user_id` int(11) DEFAULT NULL,
  `tab3nt_type_cd` int(11) NOT NULL,
  `tab3nt_start_date` datetime NOT NULL,
  `tab3nt_end_date` datetime NOT NULL,
  `domain_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `tab3nt_user_FK` (`create_user_id`),
  KEY `domain_id_tab3nt_type_cd` (`domain_id`,`tab3nt_type_cd`)
) ENGINE=InnoDB AUTO_INCREMENT=9393276 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

Answer 1

尝试在 tab2.element_id 上放置一个简单(非复合)索引。