我正在嘗試優(yōu)化Rails應(yīng)用程序中的一些數(shù)據(jù)庫(kù)查詢，但有幾個(gè)讓我感到困惑。它們都使用INin WHERE子句，并且都進(jìn)行了全表掃描，即使適當(dāng)?shù)乃饕坪跻呀?jīng)到位。例如：SELECT `user_metrics`.* FROM `user_metrics` WHERE (`user_metrics`.user_id IN (N,N,N,N,N,N,N,N,N,N,N,N))執(zhí)行全表掃描并EXPLAIN說(shuō)：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語(yǔ)句時(shí)是否不使用索引，或者我需要做不同的事情嗎？這里的查詢是由Rails生成的，因此我可以重新定義我的關(guān)系的定義，但是我認(rèn)為我首先要從數(shù)據(jù)庫(kù)級(jí)別的潛在修復(fù)開始。
3 回答
森林海
TA貢獻(xiàn)2011條經(jīng)驗(yàn) 獲得超2個(gè)贊
在向表中添加額外的2000左右行之后，還要驗(yàn)證MySQL是否仍執(zhí)行全表掃描user_metrics。在小型表中，按索引訪問實(shí)際上比表掃描更昂貴（在I / O方式上），MySQL的優(yōu)化程序可能會(huì)考慮到這一點(diǎn)。
與我之前的文章相反，事實(shí)證明MySQL也在使用基于成本的優(yōu)化器，這是一個(gè)好消息-也就是說(shuō)，ANALYZE如果您認(rèn)為數(shù)據(jù)庫(kù)中的數(shù)據(jù)量足以代表數(shù)據(jù)庫(kù)運(yùn)行了至少一次，將來(lái)的日常使用。
在處理基于成本的優(yōu)化器（Oracle，Postgres等）時(shí)，您需要確保ANALYZE在其大小增加10-15％以上時(shí)，定期在各種表上運(yùn)行。（默認(rèn)情況下，Postgres將自動(dòng)為您執(zhí)行此操作，而其他RDBMS將把此職責(zé)留給DBA（即您）。）通過(guò)統(tǒng)計(jì)分析，ANALYZE將有助于優(yōu)化程序更好地了解I / O量（以及其他相關(guān)資源）在各種候選執(zhí)行計(jì)劃之間進(jìn)行選擇時(shí)，將涉及到諸如CPU之類的，例如用于排序的信息。運(yùn)行失敗ANALYZE可能會(huì)導(dǎo)致非常糟糕的規(guī)劃決策，有時(shí)甚至是災(zāi)難性的決策（例如，由于s 上的嵌套循環(huán)不好，毫秒查詢有時(shí)會(huì)花費(fèi)數(shù)小時(shí)JOIN）。
如果運(yùn)行后性能仍然不能令人滿意ANALYZE，則通?？梢允褂锰崾緛?lái)解決該問題，例如FORCE INDEX，而在其他情況下，您可能會(huì)偶然發(fā)現(xiàn)MySQL錯(cuò)誤（例如，這個(gè)較舊的錯(cuò)誤可能會(huì)咬住您的本人）。使用Rails的nested_set）。
現(xiàn)在，由于您使用的是Rails應(yīng)用程序，因此ActiveRecord使用提示來(lái)發(fā)出自定義查詢而不是繼續(xù)使用ActiveRecord-生成的查詢將很麻煩（并且破壞的目的）。
我曾經(jīng)提到過(guò)，在我們的Rails應(yīng)用程序中，所有 SELECT查詢切換到Postgres后都降至100ms以下，而ActiveRecord由于內(nèi)部表掃描的嵌套循環(huán)，即使使用索引，由MySQL 生成的某些復(fù)雜聯(lián)接有時(shí)也可能需要15s甚至更長(zhǎng)的時(shí)間。可用。沒有哪個(gè)優(yōu)化器是完美的，您應(yīng)該注意這些選擇。除了查詢計(jì)劃優(yōu)化之外，還需要注意其他潛在的性能問題。但是，這超出了您的問題范圍。
犯罪嫌疑人X
TA貢獻(xiàn)2080條經(jīng)驗(yàn) 獲得超4個(gè)贊
嘗試強(qiáng)制使用此索引：
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))
我剛剛檢查過(guò)，它確實(shí)在完全相同的查詢上使用了索引：
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'
絕地?zé)o雙
TA貢獻(xiàn)1946條經(jīng)驗(yàn) 獲得超4個(gè)贊
我知道我遲到了。但是希望我可以幫助其他有類似問題的人。
最近，我遇到了同樣的問題。然后，我決定使用自我結(jié)合來(lái)解決我的問題。問題不是MySQL。問題是我們。子查詢的返回類型與我們的表不同。因此，我們必須將子查詢的類型轉(zhuǎn)換為選擇列的類型。下面是示例代碼：
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;
新：（使用指數(shù)：?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萬(wàn)
PK：jxm_character（信息日期，ranking_type，character_id）
SHOW VARIABLES LIKE '%version%';
'protocol_version', '10'
'version', '5.1.69-log'
'version_comment', 'Source distribution'
最后說(shuō)明：確保您了解MySQL索引最左邊的規(guī)則。
P / s：對(duì)不起，我的英語(yǔ)不好。我發(fā)布了代碼（當(dāng)然是生產(chǎn)版）以清除我的解決方案：D。
添加回答
舉報(bào)
0/150
提交
取消