非常慢的 MySQL 查詢，即使有索引

我有一個相對較大的 4 深度關係數據設置，如下所示：

client_applications: (可能有 1,000 條記錄)

   - ...

   - account_id

   -deleted_at

client_application_versions: (可能有 10,000 條記錄)

   - ...

   - client_application_id

   -deleted_at

cloud_logs: (可能有 1,000,000 條記錄)

   - …

   - client_application_version_id

   -deleted_at

logs: (可能有 1,000,000,000 條記錄)

   - ...

   - cloud_log_id

   - time_stamp

   -deleted_at

---

我仍在開發中，所以結構和設置不是一成不變的，但我認為設置沒問題。使用 Rails 3.2.11 和 InnoDB MySQL。數據庫充滿了一小部分（與最終的數據庫大小相比）數據集（logs只有 700,000 行）我有 4 個查詢，其中 3 個有問題，以檢索日誌。

1. 抓取第一頁日誌，按時間戳排序，限制為account_id, client_application_id, client_application_version_id(超過 100 秒)
2. 抓取日誌的第一頁，按時間戳排序，限制為account_id, client_application_id（超過 100 秒）
3. 抓取日誌的第一頁，按時間戳排序，限制為account_id（超過 100 秒）
4. 獲取日誌的第一頁，按時間戳排序（約 2 秒）

---

這裡是解釋語句。我已經在所有適用領域都有索引。複製表...id上的各個欄位logs以防止連接會更好嗎？還是在進行這些查詢時我缺少一些魔法醬？我以前從未處理過這麼多的數據，所以也許我處理設置和查詢的標準方式無法擴展？如何更改我的設置或語句以使這些查詢在合理的時間內返回？

---

更新

我在 Logs 表上添加了一些組合索引，並節省了一點時間。這是對此的解釋。我得出的結論是 ORDER 方法是延遲的根源。刪除這些order by timestamp desc會導致查詢在一秒鐘左右返回。所以新的問題是，為什麼在時間戳索引時，執行這個查詢仍然需要一分鐘多的時間？

---

更新 2

使用子查詢將 100 id 的性能提高到 14 秒，但這仍然太長了。到目前為止我嘗試過的優化都縮短了一點時間，但我覺得它們並沒有找到問題的根源。這是子查詢方法的解釋。

這裡不是 DBA 或 MySQL 專家，但讓我們試試 :)。因此，讓我們進行第二個查詢 - 比第一個小一點 - 並簡化表名。

我們有類似的東西：（LO = 日誌，CL = cloud_logs，CAV = client_application_versions，CA = client_applications）

SELECT LO.* FROM LO 
INNER JOIN CL      ON CL.id    = LO.cloud_log_id 
INNER JOIN CAV     ON CAV.id   = CL.client_application_version_id 
INNER JOIN CA      ON CA.id    = CAV.client_application_id 
WHERE (LO.deleted_at IS NULL) 
AND (CA.account_id = '3') 
AND (CA.id = '5') 
ORDER BY timestamp DESC LIMIT 100 OFFSET 0

所以你說這需要大約 100 秒，對嗎？

當你說 ：

我已經在所有適用領域都有索引。

然而，我相信這就是缺陷所在。你沒有那麼多的連接，你可能有 70 億個數據或只有 700 個，如果正確地考慮索引應該表現良好，我認為這可能是由於糟糕而影響你的性能的 order by/limit索引。

1/ 你有沒有試過：

SELECT LO.* FROM LO WHERE (LO.deleted_at IS NULL)

要麼

SELECT * FROM CA WHERE (CA.account_id = '3') AND (CA.id = '5')

如果這兩張表一切正常，看看這些請求是如何及時執行的？

2/你也索引了時間戳嗎？索引您正在“排序依據”的列也很重要。事實上，您甚至應該考慮您的數據以及您要查詢的每個數據將擁有多少個值。那裡很好地解釋了這一點：http ://www.mysqlperformanceblog.com/2006/09/01/order-by-limit-performance-optimization/ 肯定會幫助你。

3/從幾分鐘前我在 MySQL 上讀到的內容，你也可以試試MySQLCheck，看看你的表是否一切正常，如果你認為你的索引是好的 http://dev.mysql.com/doc/refman/5.0/ zh/mysqlcheck.html。我知道在舊版本的 oracle 中，我們必須在創建索引後計算統計資訊，也許這裡有類似的東西？

希望這可以幫助。

$$ EDIT : 12/01/13 After comments $$

好的，很高興看到您已經將時間除以 4，但 25 秒確實太長了。

**1/**您是否嘗試過通過創建一個有意義的索引來使用索引，就像彼得在這裡解釋的那樣 (http://www.mysqlperformanceblog.com/2006/09/01/order-by-limit-performance-optimization/) ? 像 (CA.account_id, CA.id, timestamp) 等上的索引？

---

**2/**像下面這樣通過/限制取消訂單需要多長時間？

SELECT LO.* FROM LO 
INNER JOIN CL      ON CL.id    = LO.cloud_log_id 
INNER JOIN CAV     ON CAV.id   = CL.client_application_version_id 
INNER JOIN CA      ON CA.id    = CAV.client_application_id 
WHERE (LO.deleted_at IS NULL) 
AND (CA.account_id = '3') 
AND (CA.id = '5') 

檢查這是否會影響您的表現？

---

3/如果2得到驗證，您可以嘗試以下操作：

SELECT LO.* FROM LO 
INNER JOIN CL      ON CL.id    = LO.cloud_log_id 
INNER JOIN CAV     ON CAV.id   = CL.client_application_version_id 
INNER JOIN CA      ON CA.id    = CAV.client_application_id 
INNER JOIN 
(
   SELECT LO.id FROM LO 
   INNER JOIN CL      ON CL.id    = LO.cloud_log_id 
   INNER JOIN CAV     ON CAV.id   = CL.client_application_version_id 
   INNER JOIN CA      ON CA.id    = CAV.client_application_id 
   WHERE (LO.deleted_at IS NULL) 
   AND (CA.account_id = '3') 
   AND (CA.id = '5') 
   ORDER BY timestamp DESC LIMIT 0,100
 ) AS PERF  ON PERF.id = LO.id

將 LO.id 替換為對 Logs 有意義的列（我想你有某種 Logs id 。這是基於：http ://explainextended.com/2009/10/23/mysql-order-by- limit-performance-late-row-lookups/ 請注意，您可以更改 LIMIT 0,100 並保留 OFFSET 關鍵字以備不時之需（如果需要 PostgreSQL 兼容性）。

不確定這是否有幫助，但在我的情況下，有 3 百萬行的表。非常簡單的連接或聯合或循環查詢都非常慢..測試和測試後只有 20 行結果我只是放了一個簡單的命令來查詢

強制索引（one_colum_indexed）

如果您的表有多個索引欄位，則必須一一測試 which one_colum_indexed，以尋找最佳的。

所有的信用都到這裡使用力指數來加速查詢

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