mysql - 执行时间过多也会影响数据库性能

Question

1.query 执行大约需要 10 分钟到 1 小时。2.由于这个原因，我们经常遇到负载峰值。3.请帮我重写查询。4.还帮助我提高了查询的性能。

查询解释计划

   explain select count(*) 
      from t_event eve 
 left join t_event_element_rel rel on rel.event_id = eve.id 
 left join t_object object on eve.create_object_id = object.id 
 left join t_keyword keyword on  rel.element_id = keyword.id and rel.element_type_id = 1 
 left join t_target_url targeturl on  rel.element_id = targeturl.id and rel.element_type_id = 2 
 where eve.domain_id = 522



+----+-------------+-----------+--------+-------------------------+-------------------------+---------+----------------------------+--------+-------------+
| id | select_type | table     | type   | possible_keys           | key                     | key_len | ref                        | rows   | Extra       |
+----+-------------+-----------+--------+-------------------------+-------------------------+---------+----------------------------+--------+-------------+
|  1 | SIMPLE      | eve       | ref    | domain_id_event_type_cd | domain_id_event_type_cd | 4       | const                      | 243430 |             |
|  1 | SIMPLE      | rel       | ref    | FK_event                | FK_event                | 4       | company.eve.id             |      2 |             |
|  1 | SIMPLE      | user      | eq_ref | PRIMARY                 | PRIMARY                 | 4       | company.eve.create_user_id |      1 | Using index |
|  1 | SIMPLE      | keyword   | ref    | id                      | id                      | 4       | company.rel.element_id     |      1 | Using index |
|  1 | SIMPLE      | targeturl | ref    | id                      | id                      | 4       | company.rel.element_id     |      1 | Using index |
+----+-------------+-----------+--------+-------------------------+-------------------------+---------+----------------------------+--------+-------------+
5 rows in set (0.45 sec)

表结构:

mysql> show create table t_event_element_rel\G
*************************** 1. row ***************************
       Table: t_event_element_rel
Create Table: CREATE TABLE `t_event_element_rel` (
  `id` int(255) NOT NULL AUTO_INCREMENT,
  `element_type_id` int(11) NOT NULL,
  `event_id` int(11) NOT NULL,
  `element_id` int(11) NOT NULL,
  `element_desc` varchar(500) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `FK_event` (`event_id`),
  KEY `element_type_id_element_id` (`element_type_id`,`element_id`)
) ENGINE=InnoDB AUTO_INCREMENT=45901159 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> show create table t_object\G
*************************** 1. row ***************************
       Table: t_object
Create Table: CREATE TABLE `t_object` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `password` varchar(255) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  `is_admin` int(11) DEFAULT NULL,
  `phone` varchar(50) DEFAULT NULL,
  `company` varchar(255) DEFAULT NULL,
  `last_login` datetime DEFAULT NULL,
  `state` int(11) DEFAULT NULL,
  `validate_code` varchar(50) DEFAULT NULL,
  `has_login` smallint(6) DEFAULT NULL COMMENT 'if null or 0 then hasn''t login, first time login',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1304 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)


mysql> show create table t_event\G
*************************** 1. row ***************************
       Table: t_event
Create Table: CREATE TABLE `t_event` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `create_user_id` int(11) DEFAULT NULL,
  `event_create_date` date DEFAULT NULL,
  `event_type_cd` int(11) NOT NULL,
  `event_desc` varchar(512) NOT NULL,
  `IsGlobalEvent` int(2) DEFAULT NULL,
  `event_start_date` datetime NOT NULL,
  `event_end_date` datetime NOT NULL,
  `job_id` int(11) DEFAULT NULL,
  `domain_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `event_user_FK` (`create_user_id`),
  KEY `domain_id_event_type_cd` (`domain_id`,`event_type_cd`)
) ENGINE=InnoDB AUTO_INCREMENT=8586007 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> show create table t_keyword\G
*************************** 1. row ***************************
       Table: t_keyword
Create Table: CREATE TABLE `t_keyword` (
  `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 COMMENT 'yesterday rank value',
  `rank2` int(11) DEFAULT NULL COMMENT 'the day before yesterday rank value',
  `rank3` int(11) DEFAULT NULL COMMENT 'special date rank for overstock.com',
  `yesterday_entrances` int(11) DEFAULT NULL COMMENT 'yesterday entrances',
  `week_entrances` int(11) DEFAULT NULL COMMENT '7 days entrances',
  `current_ctr` float(16,4) DEFAULT NULL COMMENT 'Current CTR',
  `monthly_search_volume` int(11) DEFAULT NULL COMMENT 'Most Recent Month search volume',
  `avg_monthly_search_volume` int(11) DEFAULT NULL COMMENT 'avg_monthly_search_volume',
  `traffic_increase` int(11) DEFAULT NULL COMMENT 'Traffic Increase',
  `rank_improvement` int(11) DEFAULT NULL COMMENT 'Rank Improvement',
  `rank_update_date` date DEFAULT NULL COMMENT 'rank be updated for Special Date',
  `top_rank_targeturl_id` int(11) DEFAULT NULL,
  `frequency` int(10) DEFAULT '1' COMMENT '1: daily, 2: weekly, 3: monthly',
  `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=670267018 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.00 sec)

mysql> show create table t_target_url\G
*************************** 1. row ***************************
       Table: t_target_url
Create Table: CREATE TABLE `t_target_url` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `own_domain_id` int(11) DEFAULT NULL,
  `url` varchar(2000) NOT NULL,
  `create_date` datetime DEFAULT NULL,
  `friendly_name` varchar(255) DEFAULT NULL,
  `section_name_id` int(11) DEFAULT NULL,
  `type` int(11) DEFAULT NULL,
  `status` int(11) DEFAULT NULL,
  `week_entrances` int(11) DEFAULT NULL COMMENT 'last 7 days entrances',
  `week_bounces` int(11) DEFAULT NULL COMMENT 'last 7 days bounce',
  `canonical_url_id` int(11) DEFAULT NULL COMMENT 'the primary URL ID, NOT allow canonical of canonical',
  KEY `id` (`id`),
  KEY `urlindex` (`url`(255)),
  KEY `own_domain_id_type_status` (`own_domain_id`,`type`,`status`),
  KEY `canonical_url_id` (`canonical_url_id`),
  KEY `type` (`type`,`status`)
) ENGINE=InnoDB AUTO_INCREMENT=237034388 DEFAULT CHARSET=utf8
/*!50100 PARTITION BY RANGE (`type`)
(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.01 sec)

Answer 1

您的 EXPLAIN 的第一行显示了很多 domain_id_event_type_cd 的行，这是一个复合索引，但在您的查询中您只搜索索引的一部分 (eve.domain_id = xxx) .因此，除非域 522 真的有 243K 个条目，否则您可以通过仅为 domain_id 添加索引来减少这一点。

此外，您似乎正在尝试通过使用 rel.element_type_id 作为引用不同类型数据的“开关”来进行 OR 搜索。由于您只对计数感兴趣，我建议您将查询分成两部分，然后添加结果。这将减少 LEFT JOINS。您甚至可以在 MySQL 中添加结果:

SELECT (SELECT COUNT (*) FROM ....) + (SELECT COUNT (*) FROM ...)

(你知道你可以滥用 SELECT 来做数学，不是吗？)