這個分頁SQL查詢中的MySQLLEFTJOIN可以優化嗎？需要超過7分鐘-有解無憂

我有兩張桌子。search 有 89000 條記錄， email_leads 有 26000 條。

我想通過左連接搜索表中的 base_id 列來關聯哪個電子郵件和名稱執行了搜索。

但是，當我在查詢中包含 LEFT JOIN 時，它需要超過 7 分鐘以上。當我洗掉它時，查詢會立即執行。有什么方法可以重組查詢，使其不需要那么長時間。解釋輸出讓我認為 mysql 實際上正在將它從搜索表掃描的所有行與 email_leads 表連接起來，而不僅僅是我希望從搜索表回傳的 10 條記錄。

這是我正在運行的查詢：

SELECT 
sh.base_id,
sh.client_id,
sh.app_id, 
sh.result_cnt, 
sh.search_type, 
sh.min_price, 
sh.max_price,
sh.bedrooms, 
sh.bathrooms, 
sh.neighborhoods, 
sh.office_connector, 
sh.created,
any_value(el.from_email) as email, 
any_value(el.from_name) as name
FROM search AS sh
LEFT JOIN email_leads AS el ON(sh.base_id = el.base_id)
WHERE date(sh.created) >= '2022-04-27'
AND date(sh.created) <= '2022-05-27'
GROUP BY sh.app_id, sh.base_id, sh.client_id, sh.result_cnt, sh.search_type, sh.min_price, sh.max_price,
sh.bedrooms, sh.bathrooms, sh.neighborhoods, sh.office_connector, sh.created ORDER BY sh.created DESC LIMIT 0, 10

以下是兩個表的索引：

mysql> show index from search;
 -------- ------------ ------------------------ -------------- ------------------ ----------- ------------- ---------- -------- ------ ------------ --------- --------------- --------- ------------------------- 
| Table  | Non_unique | Key_name               | Seq_in_index | Column_name      | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression              |
 -------- ------------ ------------------------ -------------- ------------------ ----------- ------------- ---------- -------- ------ ------------ --------- --------------- --------- ------------------------- 
| search |          0 | PRIMARY                |            1 | id               | A         |       87659 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL                    |
| search |          1 | app_id_index           |            1 | app_id           | A         |           5 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL                    |
| search |          1 | search_type_index      |            1 | search_type      | A         |           3 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL                    |
| search |          1 | office_connector_index |            1 | office_connector | A         |           6 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL                    |
| search |          1 | bedrooms_index         |            1 | bedrooms         | A         |          53 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL                    |
| search |          1 | bathrooms_index        |            1 | bathrooms        | A         |           5 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL                    |
| search |          1 | min_price_index        |            1 | min_price        | A         |          33 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL                    |
| search |          1 | max_price_index        |            1 | max_price        | A         |          51 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL                    |
| search |          1 | base_id_index          |            1 | base_id          | A         |        5474 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL                    |
| search |          1 | client_id_index        |            1 | client_id        | A         |          18 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL                    |
| search |          1 | created_to_date        |            1 | NULL             | A         |          68 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | cast(`created` as date) |
 -------- ------------ ------------------------ -------------- ------------------ ----------- ------------- ---------- -------- ------ ------------ --------- --------------- --------- ------------------------- 
11 rows in set (0.02 sec)

mysql> show index from email_leads;
 ------------- ------------ ------------------ -------------- ------------- ----------- ------------- ---------- -------- ------ ------------ --------- --------------- --------- ------------ 
| Table       | Non_unique | Key_name         | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
 ------------- ------------ ------------------ -------------- ------------- ----------- ------------- ---------- -------- ------ ------------ --------- --------------- --------- ------------ 
| email_leads |          0 | PRIMARY          |            1 | id          | A         |       21340 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| email_leads |          1 | from_email_index |            1 | from_email  | A         |        5211 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| email_leads |          1 | base_id_index    |            1 | base_id     | A         |        1482 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| email_leads |          1 | client_id_index  |            1 | client_id   | A         |           3 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
 ------------- ------------ ------------------ -------------- ------------- ----------- ------------- ---------- -------- ------ ------------ --------- --------------- --------- ------------ 
4 rows in set (0.00 sec)

查詢的解釋輸出：

 ---- ------------- ------- ------------ ------ ----------------- --------------- --------- ---------------------- ------- ---------- ---------------------------------------------- 
| id | select_type | table | partitions | type | possible_keys   | key           | key_len | ref                  | rows  | filtered | Extra                                        |
 ---- ------------- ------- ------------ ------ ----------------- --------------- --------- ---------------------- ------- ---------- ---------------------------------------------- 
|  1 | SIMPLE      | sh    | NULL       | ALL  | created_to_date | NULL          | NULL    | NULL                 | 87659 |    50.00 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | el    | NULL       | ref  | base_id_index   | base_id_index | 98      | srmanager.sh.base_id |    14 |   100.00 | NULL                                         |
 ---- ------------- ------- ------------ ------ ----------------- --------------- --------- ---------------------- ------- ---------- ----------------------------------------------

顯示創建表輸出：

| search | CREATE TABLE `search` (
  `id` varchar(32) NOT NULL,
  `app_id` smallint NOT NULL,
  `client_id` varchar(32) NOT NULL,
  `base_id` varchar(32) NOT NULL,
  `result_cnt` int NOT NULL,
  `created` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  `office_connector` varchar(255) DEFAULT 'all',
  `search_type` varchar(15) DEFAULT NULL,
  `bedrooms` varchar(300) DEFAULT 'all',
  `bathrooms` varchar(300) DEFAULT 'all',
  `neighborhoods` text,
  `min_price` int DEFAULT '0',
  `max_price` int DEFAULT '99999',
  PRIMARY KEY (`id`),
  KEY `app_id_index` (`app_id`),
  KEY `search_type_index` (`search_type`),
  KEY `office_connector_index` (`office_connector`),
  KEY `bedrooms_index` (`bedrooms`),
  KEY `bathrooms_index` (`bathrooms`),
  KEY `min_price_index` (`min_price`),
  KEY `max_price_index` (`max_price`),
  KEY `base_id_index` (`base_id`),
  KEY `client_id_index` (`client_id`),
  KEY `created_to_date` ((cast(`created` as date)))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 |

| email_leads | CREATE TABLE `email_leads` (
  `id` varchar(32) NOT NULL,
  `from_name` varchar(255) CHARACTER SET utf8mb3 COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `from_email` varchar(255) CHARACTER SET utf8mb3 COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `base_id` varchar(32) NOT NULL DEFAULT '',
  `client_id` varchar(32) NOT NULL DEFAULT '',
  `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `from_email_index` (`from_email`),
  KEY `base_id_index` (`base_id`),
  KEY `client_id_index` (`client_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 |

uj5u.com熱心網友回復：

您可以嘗試使用子查詢而不是左連接。

SELECT 
sh.base_id,
sh.client_id,
sh.app_id, 
sh.result_cnt, 
sh.search_type, 
sh.min_price, 
sh.max_price,
sh.bedrooms, 
sh.bathrooms, 
sh.neighborhoods, 
sh.office_connector, 
sh.created,
(select el.from_email from email_leads AS el where sh.base_id = el.base_id limit 1) as email, 
(select el.from_name from email_leads AS el where sh.base_id = el.base_id limit 1)  as name
FROM search AS sh
WHERE date(sh.created) >= '2022-04-27'
AND date(sh.created) <= '2022-05-27'
GROUP BY sh.app_id, sh.base_id, sh.client_id, sh.result_cnt, sh.search_type, sh.min_price, sh.max_price,
sh.bedrooms, sh.bathrooms, sh.neighborhoods, sh.office_connector, sh.created ORDER BY sh.created DESC LIMIT 0, 10

uj5u.com熱心網友回復：

DATE(sh.created)當您將列放入函式時，您使用防止使用該列上的索引。所以它必須對 89k 行進行表掃描。這在 EXPLAIN 中type: ALL和rows: 87659EXPLAIN 中顯示。

相反，試試這個：

...
WHERE sh.created >= '2022-04-27'
 sh.created < '2022-05-28'
...

請注意，我使后一個條件嚴格小于 ( <) 并將日期提前一天。這是為了讓日期匹配，即使日期時間是 23:59:59。

uj5u.com熱心網友回復：

聞起來像“爆炸-內爆”癥狀。

問題 1：您從兩個表中收集了所有相關列，構建了一個大型臨時表。然后是基于GROUP BY.

問題 2：您只需要 10 行，但它會收集所有行（來自兩個表），然后對它們進行排序，最后交付 10 行。

問題 3：不可分割的使用created

讓我們試著把查詢從里到外。“派生表”經過優化，可以輕松獲得 10 個 id；然后其余的收集其他列：

SELECT ...
    FROM (   -- "derived table"
        SELECT id
            FROM search
            WHERE created >= '2022-04-27'
              AND created  < '2022-04-27'   INTERVAL 1 MONTH
            GROUP BY id       -- probably this is all that is needed
            ORDER BY created DESC
            LIMIT 0, 10
         ) AS sh1
    JOIN sh ON sh.id = sh1.id   -- to get the rest of the columns
    LEFT JOIN email_leads AS el ON(sh.base_id = el.base_id)
ORDER BY sh.created DESC    -- yes, again

這將使派生表快速：

INDEX(created, id)

請注意，JOINandLEFT JOIN僅適用于 10 行。唯一需要的其他索引是 e on el：

INDEX(base_id)

轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/482540.html

標籤：mysql sql 加入左连接查询优化

上一篇：使用Set運算子查詢以顯示特定列

下一篇：Pandas將串列列轉換為文本列資料預處理