我有以下查詢需要 7 秒才能執行

SELECT COUNT(*) AS `count` 
FROM `yuldi`.`businesses` AS `Business` 
 LEFT JOIN `yuldi`.`businesses_categories` AS `BusinessesCategory` 
   ON (`Business`.`id` = `BusinessesCategory`.`business_id`) 
 LEFT JOIN `yuldi`.`categories` AS `Category` 
   ON (`BusinessesCategory`.`category_id` = `Category`.`id`) 
WHERE `Category`.`slug` = 'building-construction'

當我第二次執行它需要 160 毫秒。為什麼每次第一次執行都需要這麼長時間？

解釋：

| id | select_type | table              | type   | possible_keys                                                                                   | key                                          | key_len | ref      | rows  | Extra                    |
|  1 | SIMPLE      | Category           | const  | PRIMARY,UNIQUE_SLUG,index_lug                                                                   | UNIQUE_SLUG                                  | 302     | const    |     1 | Using index              |
|  1 | SIMPLE      | BusinessesCategory | ref    | PRIMARY,fk_businesses_has_categories_categories1_idx,fk_businesses_has_categorie_businesses_idx | fk_businesses_has_categories_categories1_idx | 4       | const    | 49630 | Using where; Using index |
|  1 | SIMPLE      | Business           | eq_ref | PRIMARY                                                                                         | PRIMARY                                      | 4       | yuldi.BusinessesCategory.business_id |     1 | Using index              |

需要返工的三個方面

方面#1：查詢

請看查詢

SELECT COUNT(*) AS `count`
FROM `yuldi`.`businesses` AS `Business`
LEFT JOIN `yuldi`.`businesses_categories` AS `BusinessesCategory`
   ON (`Business`.`id` = `BusinessesCategory`.`business_id`)
LEFT JOIN `yuldi`.`categories` AS `Category`
   ON (`BusinessesCategory`.`category_id` = `Category`.`id`)
WHERE `Category`.`slug` = 'building-construction';

您正在執行 LEFT JOIN。這不是做 COUNT 所必需的。為什麼 ？

如果是統計商家數量，只需要從一張表開始計算

SELECT COUNT(*) AS `count`
FROM `yuldi`.`businesses` AS `Business`;

由於您有一個 WHERE 子句Category.slug，因此需要 JOIN。您應該從 LEFT JOIN 切換到 INNER JOIN。這將產生一個較小的內部臨時表。將在該臨時表中搜尋 slug ‘building-construction’。

方面#2：索引

從 EXPLAIN 計劃中，我看到兩個索引名稱相似

• fk_businesses_has_categories_categories1_idx
• fk_businesses_has_categories_businesses_idx

您需要查看它們的列並確保列列表不相同。

您可能想要創建兩個索引

ALTER TABLE BusinessesCategory
   ADD INDEX bus_cat_ndx (business_id,category_id),
   ADD INDEX cat_bus_ndx (category_id,business_id)
;

擁有復合索引可能意味著更少地從表中剔除 JOIN 資訊。

請記住，這個建議是盲目的，因為我不知道您數據的密鑰分佈。

方面#3：記憶體

收集索引統計資訊是為了查詢評估不會立即轉化為慢查詢。這種緩慢體現在真正的罪魁禍首：記憶體。您的查詢第二次執行得更快的原因與您第一次讀取的數據有關。當您發出查詢時，您需要的數據很可能在 MySQL 的記憶體中不可用。

MySQL 的兩個主要記憶體是 InnoDB 緩衝池（用於訪問 InnoDB 表的查詢）和 MyISAM 鍵記憶體（用於訪問 MyISAM 表的查詢）。

InnoDB 記憶體數據和索引頁，而 MyISAM 只記憶體索引頁。

在檢索數據進行比較時，mysqld 會首先嘗試確定它需要的數據是否在 RAM 中。有一些伺服器狀態變數需要監控：

InnoDB 記憶體

• Innodb_buffer_pool_read_requests：邏輯讀取請求的數量。
• Innodb_buffer_pool_reads：InnoDB 無法從緩衝池中滿足的邏輯讀取次數，必須直接從磁碟讀取。

MyISAM 記憶體

• Key_read_requests：從 MyISAM 密鑰記憶體中讀取密鑰塊的請求數。
• Key_reads：從磁碟物理讀取密鑰塊到 MyISAM 密鑰記憶體的次數。如果 Key_reads 很大，那麼您的 key_buffer_size 值可能太小了。記憶體未命中率可以計算為 Key_reads/Key_read_requests。

如果您的查詢第一次很慢，這僅僅意味著您查詢所需的數據不在記憶體中。這將通過您的Innodb_buffer_pool_reads或Key_reads遞增來指示。

第二次，您的同一查詢的數據和/或索引頁面將位於 RAM 中，並且更多可用於您的查詢和其他人的查詢。

我建議您查看 InnoDB 緩衝池和/或 MyISAM 密鑰記憶體的大小。

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