複雜的where顯著影響查詢性能

在這些表上執行了更複雜的查詢，這些查詢要快得多，但這個查詢需要 90 多秒才能執行。

我在這裡做錯了什麼？如何改進？

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)

information_schema有兩件事情會隨著細節立即跳出來：

• MyISAM
• 不同的排序規則

關於 MyISAM

當 MyISAM 和 InnoDB 之間存在 a 時JOIN，由於 MyISAM 需要如何操作，InnoDB 表將遭受表級鎖定行為而不是行級鎖定。

MySQL 中的查詢引擎在很大程度上依賴於索引基數來確定優化計劃。只要數據是靜態的，MyISAM 表中的索引基數通常是穩定的……這種情況很少發生。InnoDB 索引基數從一分鐘到下一分鐘不同。

由於上述原因，我強烈建議您在下一個數據庫維護視窗期間將任何 MyISAM 表轉換為 InnoDB。

關於不同的排序規則

查詢引擎需要將utf8_bin、utf8_unicode_ci和轉換latin1_swedish_ci為與每個查詢一致的內容，並且您的表上的某些索引非常大。這需要時間。理想情況下，數據庫中的每個表都具有相同的排序規則，以避免這些昂貴的轉換。

附加閱讀

這是一個關於 MySQL 在加入 MyISAM 和 InnoDB 表進行查詢時的性能十多年的錯誤報告。當查詢的結果用於MyISAM 表時INSERT，情況會更糟。UPDATE到處鎖！🤐

引用自：https://dba.stackexchange.com/questions/291042