相对简单的查询非常慢。我做错了什么？

Question

只涉及4个表，所以我不知道为什么要用90+秒在我的DB上执行。

records表中有大约300万条记录，但是软件中的其他查询都没有这个查询慢。有许多更复杂的查询比这个查询要快得多，所以我知道我做错了什么。

这确实产生了正确的结果，但速度太慢了。我在这里做错了什么？

SELECT 
            r.id as `ID`,  
            concat(r.fname, " ", r.lname) as `Applicant`,
            r.added `App Time`,
            concat(trim(r.city), ", ", r.state) as `City`, 
            coalesce(q.count, 0) as `Attempts`,
            coalesce(q.last_called, 0) as `Last Called`,
            null as `Removed`
        FROM myfreshp_crm.records r 
        left join (
            SELECT rid, count(rid) as count, max(called) as last_called
            from myfreshp_crm.cc_queue
            where status = 'called'
            group by rid
        ) q on q.rid = r.id 
        left join (
            select rid, max(time) as appt
            from myfreshp_crm.calendar 
            where event = 'Appointment' 
            group by rid
        ) a on a.rid = r.id
        left join (
            select rid, max(sent) as sent
            from myfreshp_crm.cc_queue
            group by rid
        ) c on c.rid = r.id
        where 
        r.id not in (select lead_id from asap_blacklist) 
        and coalesce(q.count, 0) < 4
        AND ( 
            c.sent > (UNIX_TIMESTAMP() - (60 * 60 * 24 * 28)) OR
            r.added > (UNIX_TIMESTAMP() - (60 * 60 * 24 * 28))
        )
        AND (
            (
                a.appt is not null
                and a.appt < UNIX_TIMESTAMP()
                and c.sent is not null
                and c.sent > a.appt
            ) OR (
                r.source = 'Online' 
                and a.appt is null
            )
        )

explain extended...的输出如下：

+----+-------------+----------------+------+---------------+-------------+---------+-------------------+---------+----------+----------------------------------------------+
| id | select_type | table          | type | possible_keys | key         | key_len | ref               | rows    | filtered | Extra                                        |
+----+-------------+----------------+------+---------------+-------------+---------+-------------------+---------+----------+----------------------------------------------+
|  1 | PRIMARY     | r              | ALL  | added,source  | NULL        | NULL    | NULL              | 3436521 |   100.00 | Using where                                  |
|  1 | PRIMARY     | <derived2>     | ref  | <auto_key0>   | <auto_key0> | 4       | myfreshp_crm.r.id |      10 |   100.00 | Using where                                  |
|  1 | PRIMARY     | <derived3>     | ref  | <auto_key1>   | <auto_key1> | 4       | myfreshp_crm.r.id |      15 |   100.00 | Using where                                  |
|  1 | PRIMARY     | <derived4>     | ref  | <auto_key1>   | <auto_key1> | 4       | myfreshp_crm.r.id |      15 |   100.00 | Using where                                  |
|  5 | SUBQUERY    | asap_blacklist | ALL  | NULL          | NULL        | NULL    | NULL              |     287 |   100.00 | NULL                                         |
|  4 | DERIVED     | cc_queue       | ALL  | rid           | NULL        | NULL    | NULL              |   77090 |   100.00 | Using temporary; Using filesort              |
|  3 | DERIVED     | calendar       | ALL  | rid,event     | NULL        | NULL    | NULL              |  102750 |    97.15 | Using where; Using temporary; Using filesort |
|  2 | DERIVED     | cc_queue       | ALL  | rid,status    | NULL        | NULL    | NULL              |   77090 |    99.39 | Using where; Using temporary; Using filesort |
+----+-------------+----------------+------+---------------+-------------+---------+-------------------+---------+----------+----------------------------------------------+
8 rows in set, 1 warning (0.08 sec)

Show warnings;提供了以下内容：

+-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          |
+-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1003 | /* select#1 */ select `myfreshp_crm`.`r`.`id` AS `ID`,concat(`myfreshp_crm`.`r`.`fname`,' ',`myfreshp_crm`.`r`.`lname`) AS `Applicant`,`myfreshp_crm`.`r`.`added` AS `App Time`,concat(trim(`myfreshp_crm`.`r`.`city`),', ',`myfreshp_crm`.`r`.`state`) AS `City`,coalesce(`q`.`count`,0) AS `Attempts`,coalesce(`q`.`last_called`,0) AS `Last Called`,NULL AS `Removed` from `myfreshp_crm`.`records` `r` left join (/* select#2 */ select `myfreshp_crm`.`cc_queue`.`rid` AS `rid`,count(`myfreshp_crm`.`cc_queue`.`rid`) AS `count`,max(`myfreshp_crm`.`cc_queue`.`called`) AS `last_called` from `myfreshp_crm`.`cc_queue` where (`myfreshp_crm`.`cc_queue`.`status` = 'called') group by `myfreshp_crm`.`cc_queue`.`rid`) `q` on((`q`.`rid` = `myfreshp_crm`.`r`.`id`)) left join (/* select#3 */ select `myfreshp_crm`.`calendar`.`rid` AS `rid`,max(`myfreshp_crm`.`calendar`.`time`) AS `appt` from `myfreshp_crm`.`calendar` where (`myfreshp_crm`.`calendar`.`event` = 'Appointment') group by `myfreshp_crm`.`calendar`.`rid`) `a` on((`a`.`rid` = `myfreshp_crm`.`r`.`id`)) left join (/* select#4 */ select `myfreshp_crm`.`cc_queue`.`rid` AS `rid`,max(`myfreshp_crm`.`cc_queue`.`sent`) AS `sent` from `myfreshp_crm`.`cc_queue` group by `myfreshp_crm`.`cc_queue`.`rid`) `c` on((`c`.`rid` = `myfreshp_crm`.`r`.`id`)) where ((not(<in_optimizer>(`myfreshp_crm`.`r`.`id`,`myfreshp_crm`.`r`.`id` in ( <materialize> (/* select#5 */ select `myfreshp_crm`.`asap_blacklist`.`lead_id` from `myfreshp_crm`.`asap_blacklist` where 1 ), <primary_index_lookup>(`myfreshp_crm`.`r`.`id` in <temporary table> on <auto_key> where ((`myfreshp_crm`.`r`.`id` = `materialized-subquery`.`lead_id`))))))) and (coalesce(`q`.`count`,0) < 4) and ((`c`.`sent` > <cache>((unix_timestamp() - (((60 * 60) * 24) * 28)))) or (`myfreshp_crm`.`r`.`added` > <cache>((unix_timestamp() - (((60 * 60) * 24) * 28))))) and (((`a`.`appt` is not null) and (`a`.`appt` < <cache>(unix_timestamp())) and (`c`.`sent` is not null) and (`c`.`sent` > `a`.`appt`)) or ((`myfreshp_crm`.`r`.`source` = 'Online') and isnull(`a`.`appt`)))) |
+-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

records表：

CREATE TABLE `records` (
  `id` int(20) NOT NULL AUTO_INCREMENT,
  `uid` int(20) NOT NULL,
  `cid` int(20) NOT NULL,
  `vid` int(8) NOT NULL,
  `added` int(25) NOT NULL,
  `fname` varchar(50) NOT NULL,
  `mname` varchar(50) NOT NULL,
  `lname` varchar(50) NOT NULL,
  `address` varchar(200) NOT NULL,
  `city` varchar(50) NOT NULL,
  `state` varchar(50) NOT NULL,
  `zip` int(5) NOT NULL,
  `phone1` varchar(16) NOT NULL,
  `phone2` varchar(16) NOT NULL,
  `mobilephone` varchar(16) NOT NULL,
  `email` varchar(100) NOT NULL,
  `status` enum('active','inactive','followup','responded','sold','dead') NOT NULL,
  `ssn` varchar(11) NOT NULL,
  `perm` enum('yes','no') NOT NULL DEFAULT 'no',
  `printed_label` int(30) NOT NULL,
  `printed_letter` int(30) NOT NULL,
  `dob` varchar(20) NOT NULL,
  `source` varchar(15) NOT NULL DEFAULT 'imported',
  `opt_out` enum('no','yes') NOT NULL DEFAULT 'no',
  `other_data` longtext NOT NULL,
  `sms_opt_in` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `cid` (`cid`),
  KEY `uid` (`uid`),
  KEY `vid` (`vid`),
  KEY `status` (`status`),
  KEY `uid_2` (`uid`),
  KEY `printed_label` (`printed_label`),
  KEY `fname` (`fname`),
  KEY `mname` (`mname`),
  KEY `lname` (`lname`),
  KEY `phone1` (`phone1`),
  KEY `phone2` (`phone2`),
  KEY `printed_letter` (`printed_letter`),
  KEY `address` (`address`),
  KEY `city` (`city`),
  KEY `state` (`state`),
  KEY `added` (`added`),
  KEY `source` (`source`),
  KEY `email` (`email`),
  KEY `zip` (`zip`),
  KEY `ssn` (`ssn`),
  KEY `dob` (`dob`)
) ENGINE=InnoDB AUTO_INCREMENT=8938455 DEFAULT CHARSET=latin1

cc_queue表：

CREATE TABLE `cc_queue` (
  `id` int(20) NOT NULL AUTO_INCREMENT,
  `rid` int(20) NOT NULL,
  `sent` int(30) NOT NULL,
  `called` int(30) NOT NULL,
  `reason` varchar(150) COLLATE utf8_unicode_ci NOT NULL,
  `status` enum('waiting','called') COLLATE utf8_unicode_ci NOT NULL,
  `disposition` longtext COLLATE utf8_unicode_ci NOT NULL,
  `comments` varchar(250) COLLATE utf8_unicode_ci NOT NULL,
  `sentToCC` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `rid` (`rid`),
  KEY `status` (`status`),
  KEY `sent` (`sent`),
  KEY `called` (`called`),
  KEY `sentToCC` (`sentToCC`)
) ENGINE=MyISAM AUTO_INCREMENT=77097 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

calendar表：

CREATE TABLE `calendar` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `uid` int(11) NOT NULL,
  `rid` int(20) NOT NULL,
  `added` int(25) NOT NULL,
  `time` int(11) NOT NULL,
  `event` varchar(500) COLLATE utf8_unicode_ci NOT NULL,
  `details` varchar(1000) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`),
  KEY `uid` (`uid`),
  KEY `rid` (`rid`),
  KEY `added` (`added`),
  KEY `time` (`time`),
  KEY `event` (`event`(333))
) ENGINE=MyISAM AUTO_INCREMENT=151930 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

asap_blacklist表：

CREATE TABLE `asap_blacklist` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `time` int(11) NOT NULL,
  `lead_id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1483 DEFAULT CHARSET=utf8 COLLATE=utf8_bin

information_schema是这么说的..。

select * from information_schema.tables where table_name in ('records', 'cc_queue', 'calendar', 'asap_blacklist');
+---------------+--------------+----------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------------+
| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME     | TABLE_TYPE | ENGINE | VERSION | ROW_FORMAT | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH | MAX_DATA_LENGTH | INDEX_LENGTH | DATA_FREE | AUTO_INCREMENT | CREATE_TIME         | UPDATE_TIME         | CHECK_TIME          | TABLE_COLLATION   | CHECKSUM | CREATE_OPTIONS | TABLE_COMMENT |
+---------------+--------------+----------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------------+
| def           | myfreshp_crm | asap_blacklist | BASE TABLE | InnoDB |      10 | Compact    |        287 |             57 |       16384 |               0 |            0 |         0 |           1483 | 2021-03-13 22:20:35 | NULL                | NULL                | utf8_bin          |     NULL |                |               |
| def           | myfreshp_crm | calendar       | BASE TABLE | MyISAM |      10 | Dynamic    |     102750 |            178 |    18325956 | 281474976710655 |      7480320 |         0 |         151930 | 2015-10-06 13:07:55 | 2021-05-04 21:38:09 | 2016-06-04 21:10:52 | utf8_unicode_ci   |     NULL |                |               |
| def           | myfreshp_crm | cc_queue       | BASE TABLE | MyISAM |      10 | Dynamic    |      77092 |            112 |    14584528 | 281474976710655 |      5064704 |   5935072 |          77097 | 2015-12-09 09:43:24 | 2021-05-05 09:30:02 | 2016-06-04 21:10:52 | utf8_unicode_ci   |     NULL |                |               |
| def           | myfreshp_crm | records        | BASE TABLE | InnoDB |      10 | Compact    |    3436523 |            204 |   702349312 |               0 |   1715929088 |   6291456 |        8938456 | 2021-02-18 04:16:51 | NULL                | NULL                | latin1_swedish_ci |     NULL |                |               |
+---------------+--------------+----------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------------+
4 rows in set (0.00 sec)

Answer 1

你可以简化你的代码。

首先，在myfreshp_crm.cc_queue中聚合两次，然后将两个结果集分别加入到myfreshp_crm.records，但是可以使用条件聚合一次。

q和c别名子查询可以合并到以下内容中：

SELECT rid, 
       COUNT(CASE WHENE status = 'called' THEN rid END) AS count, 
       MAX(CASE WHENE status = 'called' THEN called END) AS last_called,
       MAX(sent) AS sent
FROM myfreshp_crm.cc_queue
GROUP BY rid

还简化了WHERE子句中的条件。

条件：

a.appt is not null and a.appt < UNIX_TIMESTAMP()

可简化为：

a.appt < UNIX_TIMESTAMP()

因为如果a.appt < UNIX_TIMESTAMP()是true或false，这意味着a.appt不是null。

同样，条件如下：

c.sent is not null and c.sent > a.appt

可以简化为：

c.sent > a.appt

所以，试试下面的代码：

SELECT r.id AS `ID`,  
       CONCAT(r.fname, ' ', r.lname) AS `Applicant`,
       r.added `App Time`,
       CONCAT(trim(r.city), ', ', r.state) AS `City`, 
       COALESCE(q.count, 0) AS `Attempts`,
       COALESCE(q.last_called, 0) AS `Last Called`,
       NULL AS `Removed`
FROM myfreshp_crm.records r 
LEFT join (
  SELECT rid, 
         COUNT(CASE WHENE status = 'called' THEN rid END) AS count, 
         MAX(CASE WHENE status = 'called' THEN called END) AS last_called,
         MAX(sent) AS sent
  FROM myfreshp_crm.cc_queue
  GROUP BY rid
) q ON q.rid = r.id 
LEFT join (
  SELECT rid, MAX(time) AS appt
  FROM myfreshp_crm.calendar 
  WHERE event = 'Appointment' 
  GROUP BY rid
) a ON a.rid = r.id
WHERE r.id NOT IN (SELECT lead_id FROM asap_blacklist) 
  AND COALESCE(q.count, 0) < 4
  AND (q.sent > (UNIX_TIMESTAMP() - (60 * 60 * 24 * 28)) OR r.added > (UNIX_TIMESTAMP() - (60 * 60 * 24 * 28)))
  AND ((a.appt < UNIX_TIMESTAMP()  AND q.sent > a.appt) OR (r.source = 'Online'  AND a.appt is NULL)) 

Answer 2

连接到子查询确实会对性能产生不利影响。在可能的情况下，我用“加入意见”来代替这些意见。

我发现即使是你在WHERE条款中的行文.r.id not in (从ASAP_BlackList中选择lead_id )替换为: LEFT ASAP_BlackList BL ON BL.Lead_ID = R.ID ...and和BL.Lead_ID为NULL时速度快得多

Answer 3

cc_queue:  INDEX(status, rid, called)
cc_queue:  INDEX(rid, sent)
calendar:  INDEX(event, rid, time)

并删除相应的状态、rid和事件的单列。

变出

AND r.id not in (select lead_id from asap_blacklist)

至

LEFT JOIN asap_blacklist AS ab ON (ab.lead_id = r.id)
...
AND ab.id IS NULL