我正在嘗試優化Rails應用程序中的一些數據庫查詢，但有幾個讓我感到困惑。它們都使用INin WHERE子句，并且都進行了全表掃描，即使適當的索引似乎已經到位。例如：SELECT `user_metrics`.* FROM `user_metrics` WHERE (`user_metrics`.user_id IN (N,N,N,N,N,N,N,N,N,N,N,N))執行全表掃描并EXPLAIN說：select_type: simpletype: allextra: using wherepossible_keys: index_user_metrics_on_user_id (which is an index on the user_id column)key: (none)key_length: (none)ref: (none)rows: 208使用IN語句時是否不使用索引，或者我需要做不同的事情嗎？這里的查詢是由Rails生成的，因此我可以重新定義我的關系的定義，但是我認為我首先要從數據庫級別的潛在修復開始。
3 回答
森林海
TA貢獻2011條經驗 獲得超2個贊
在向表中添加額外的2000左右行之后，還要驗證MySQL是否仍執行全表掃描user_metrics。在小型表中，按索引訪問實際上比表掃描更昂貴（在I / O方式上），MySQL的優化程序可能會考慮到這一點。
與我之前的文章相反，事實證明MySQL也在使用基于成本的優化器，這是一個好消息-也就是說，ANALYZE如果您認為數據庫中的數據量足以代表數據庫運行了至少一次，將來的日常使用。
在處理基于成本的優化器（Oracle，Postgres等）時，您需要確保ANALYZE在其大小增加10-15％以上時，定期在各種表上運行。（默認情況下，Postgres將自動為您執行此操作，而其他RDBMS將把此職責留給DBA（即您）。）通過統計分析，ANALYZE將有助于優化程序更好地了解I / O量（以及其他相關資源）在各種候選執行計劃之間進行選擇時，將涉及到諸如CPU之類的，例如用于排序的信息。運行失敗ANALYZE可能會導致非常糟糕的規劃決策，有時甚至是災難性的決策（例如，由于s 上的嵌套循環不好，毫秒查詢有時會花費數小時JOIN）。
如果運行后性能仍然不能令人滿意ANALYZE，則通?？梢允褂锰崾緛斫鉀Q該問題，例如FORCE INDEX，而在其他情況下，您可能會偶然發現MySQL錯誤（例如，這個較舊的錯誤可能會咬住您的本人）。使用Rails的nested_set）。
現在，由于您使用的是Rails應用程序，因此ActiveRecord使用提示來發出自定義查詢而不是繼續使用ActiveRecord-生成的查詢將很麻煩（并且破壞的目的）。
我曾經提到過，在我們的Rails應用程序中，所有 SELECT查詢切換到Postgres后都降至100ms以下，而ActiveRecord由于內部表掃描的嵌套循環，即使使用索引，由MySQL 生成的某些復雜聯接有時也可能需要15s甚至更長的時間?？捎?。沒有哪個優化器是完美的，您應該注意這些選擇。除了查詢計劃優化之外，還需要注意其他潛在的性能問題。但是，這超出了您的問題范圍。
犯罪嫌疑人X
TA貢獻2080條經驗 獲得超4個贊
嘗試強制使用此索引：
SELECT `user_metrics`.*
FROM `user_metrics` FORCE INDEX (index_user_metrics_on_user_id)
WHERE (`user_metrics`.user_id IN (N,N,N,N,N,N,N,N,N,N,N,N))
我剛剛檢查過，它確實在完全相同的查詢上使用了索引：
EXPLAIN EXTENDED
SELECT * FROM tests WHERE (test IN ('test 1', 'test 2', 'test 3', 'test 4', 'test 5', 'test 6', 'test 7', 'test 8', 'test 9'))
1, 'SIMPLE', 'tests', 'range', 'ix_test', 'ix_test', '602', '', 9, 100.00, 'Using where'
絕地無雙
TA貢獻1946條經驗 獲得超4個贊
我知道我遲到了。但是希望我可以幫助其他有類似問題的人。
最近，我遇到了同樣的問題。然后，我決定使用自我結合來解決我的問題。問題不是MySQL。問題是我們。子查詢的返回類型與我們的表不同。因此，我們必須將子查詢的類型轉換為選擇列的類型。下面是示例代碼：
select `user_metrics`.*
from `user_metrics` um
join (select `user_metrics`.`user_id` in (N, N, N, N) ) as temp
on um.`user_id` = temp.`user_id`
或我自己的代碼：
舊：（不使用索引：?4s）
SELECT
`jxm_character`.*
FROM
jxm_character
WHERE
information_date IN (SELECT DISTINCT
(information_date)
FROM
jxm_character
WHERE
information_date >= DATE_SUB('2016-12-2', INTERVAL 7 DAY))
AND `jxm_character`.`ranking_type` = 1
AND `jxm_character`.`character_id` = 3146089;
新：（使用指數：?0.02s）
SELECT
*
FROM
jxm_character jc
JOIN
(SELECT DISTINCT
(information_date)
FROM
jxm_character
WHERE
information_date >= DATE_SUB('2016-12-2', INTERVAL 7 DAY)) AS temp
ON jc.information_date = STR_TO_DATE(temp.information_date, '%Y-%m-%d')
AND jc.ranking_type = 1
AND jc.character_id = 3146089;
jxm_character：
記錄：?350萬
PK：jxm_character（信息日期，ranking_type，character_id）
SHOW VARIABLES LIKE '%version%';
'protocol_version', '10'
'version', '5.1.69-log'
'version_comment', 'Source distribution'
最后說明：確保您了解MySQL索引最左邊的規則。
P / s：對不起，我的英語不好。我發布了代碼（當然是生產版）以清除我的解決方案：D。
添加回答
舉報
0/150
提交
取消