向我的外鍵添加索引會提高這個 MySQL 查詢的性能嗎？

考慮以下查詢：

SELECT
 `locations`.`id` AS `location_id`,
 `locations`.`address`,
 `locations`.`lat`,
 `locations`.`lng`,
 `tickets`.`status_id`,
 `customers`.`name`,
 `tickets`.`id` AS `id`,
 `tickets`.`updated_at` AS `updated_at`,
 ( 3959 * acos( cos( radians('39.78222851322262') ) * cos( radians( `lat` ) ) * cos( radians( `lng` ) - radians('-86.16299560000004') ) + sin( radians('39.78222851322262') ) * sin( radians( `lat` ) ) ) ) AS `distance`
FROM `locations`
RIGHT JOIN `tickets`
 ON (`tickets`.`location_id` = `locations`.`id`)
LEFT JOIN `customers`
 ON (`tickets`.`customer_id` = `customers`.`id`)
WHERE `tickets`.`client_id` = '20'
AND
 (
   `customers`.`name` LIKE '%Mahoney%'
   OR `customers`.`email` LIKE '%Mahoney%'
   OR `locations`.`address` LIKE '%Mahoney%'
 )
HAVING `distance` < '5'
ORDER BY `distance`
LIMIT 200;

使用分析工具，我得到了這份報告：

Speed: 45.569 ms
Query analysis:
· Query: SIMPLE on tickets · Type: ALL · Rows: 160 (Using where; Using temporary; Using filesort)
· Query: SIMPLE on locations · Possible keys: PRIMARY · Key Used: PRIMARY · Type: eq_ref · Rows: 1
· Query: SIMPLE on customers · Possible keys: PRIMARY · Key Used: PRIMARY · Type: eq_ref · Rows: 1 (Using where)

這是一個 MySQL 數據庫。所有的表都是帶有 utf8_unicode_ci 的 InnoDB。每個表上的主鍵都被呼叫id並int(11)帶有索引。

• 是否應該在此查詢上添加索引tickets.location_id和/或tickets.customer_id和/或tickets.client_id提高該查詢的性能？
• 為什麼或者為什麼不？
• 是否還有其他欄位我應該考慮索引以提高此查詢的效率？
• 我應該在我的數據庫模式中明確定義我的外鍵嗎？

我的想法是，由於我首先從位置中進行選擇，所以我想要一個關於被引用的外鍵的索引。我在這裡讀到：索引、外鍵和MySQL 需要所有外鍵上的索引的優化。在 InnoDB 模式中顯式定義外鍵是否會提高性能？很抱歉成為這樣一個總 n00b。謝謝您的幫助！

1. 是的，如果添加這些索引，性能可能會更好。但是，由於行數如此之少，很可能全表掃描更有效，而優化器選擇不使用任何索引。
2. 添加索引後，您的執行計劃將有所不同，要粗略估計索引的有效性，您可以將“Rows”列乘以每行輸出explain
3. 一般來說，參與過濾/加入條件/順序/組的欄位的索引可以提高性能。您還需要考慮列的選擇性（您有多少不同的值）；如果它太低，引擎將不會使用它，除非它覆蓋查詢的索引。
4. 外鍵是一個約束；任何約束的主要目的是強制執行某些限制（在 FK 的情況下是參照完整性）。因此，如果您關心數據的完整性，則應添加外部約束。

Mysql 在 FK 列上隱式創建索引這一事實意味著更好的讀取性能，以及更差的插入/更新/刪除性能（因為必須更新索引本身）。

最後，

我的想法是，由於我首先從位置進行選擇，…

不是絕對正確的。物理處理不等於邏輯處理；優化器決定它處理涉及的表的順序（正如您在輸出中看到的，引擎首先訪問tickets表）以及使用什麼訪問方法。您可以通過提示在某種程度上控制它……

*邊注。你的WHERE條款寫的方式：

WHERE `tickets`.`client_id` = '20'
 AND
 (
   `customers`.`name` LIKE '%Mahoney%'
   OR `customers`.`email` LIKE '%Mahoney%'
   OR `locations`.`address` LIKE '%Mahoney%'
 )

讓你的LEFT JOIN customers行為像INNER JOIN.*

更新 沒關係我的旁注，我沒有註意到你有OR多個表。

我希望它是有幫助的。

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