MySQL verwendet keine Indizes mit der WHERE IN-Klausel?

Siehe Wie MySQL Indizes verwendet .

Überprüfen Sie auch, ob MySQL noch einen vollständiger Tabellenscan ausführt, nachdem Sie Ihrer user_metrics -Tabelle weitere Zeilen von etwa 2000 hinzugefügt haben. In kleinen Tabellen ist der Zugriff über den Index (E/A) tatsächlich teurer als ein Tabellenscan, und der Optimierer von MySQL berücksichtigt dies möglicherweise.

Im Gegensatz zu meinem vorherigen Beitrag hat sich herausgestellt, dass MySQL auch nter Verwendung eines kostenbasierten Optimierers ist, was eine sehr gute Nachricht ist - vorausgesetzt Sie Führen Sie ANALYZE mindestens einmal aus, wenn Sie glauben, dass das Datenvolumen in Ihrer Datenbank repräsentativ für die zukünftige tägliche Verwendung ist.

Wenn Sie mit kostenbasierten Optimierern (Oracle, Postgres usw.) arbeiten, müssen Sie sicherstellen, dass ANALYZE in Ihren verschiedenen Tabellen regelmäßig ausgeführt wird, wenn deren Größe um mehr als 10-15% zunimmt. (Postgres erledigt dies standardmäßig automatisch für Sie, während andere RDBMS diese Verantwortung einem DBA überlassen, dh Ihnen.) Durch statistische Analyse kann ANALYZE dem Optimierer helfen, eine bessere Vorstellung davon zu bekommen, wie viel E/A (und andere damit verbundene) Ressourcen, wie z. B. CPU, die zum Sortieren benötigt werden, werden bei der Auswahl zwischen verschiedenen Ausführungsplänen für Kandidaten berücksichtigt. Wenn ANALYZE nicht ausgeführt wird, kann dies zu sehr schlechten, manchmal katastrophalen Planungsentscheidungen führen (z. B. Millisekunden-Abfragen, die manchmal Stunden dauern, weil fehlerhafte verschachtelte Schleifen für JOINs vorhanden sind).

Wenn die Leistung nach dem Ausführen von ANALYZE immer noch nicht zufriedenstellend ist, können Sie das Problem in der Regel mithilfe von Hinweisen umgehen, z. FORCE INDEX, während Sie in anderen Fällen möglicherweise über einen MySQL-Fehler gestolpert sind (z. B. diesen älteren , der Sie möglicherweise gebissen hätte, wenn Sie den nested_set von Rails verwendet hätten).

Nun, da Sie sich in einer Rails App befinden, wird es umständlich sein (und den Zweck von ActiveRecord zunichte machen), Ihre benutzerdefinierten Abfragen mit Hinweisen zu versehen, anstatt weiterhin die ActiveRecord- zu verwenden. erzeugte.

Ich hatte erwähnt, dass in unserer Rails -Anwendung alle SELECT-Abfragen nach dem Wechsel zu Postgres unter 100 ms fielen, während einige der komplexen Verknüpfungen, die von ActiveRecord generiert wurden, gelegentlich wie folgt ausfielen 15s oder mehr mit MySQL 5.1 wegen verschachtelter Schleifen mit inneren Tabellenscans, selbst wenn Indizes verfügbar waren. Kein Optimierer ist perfekt, und Sie sollten sich der Optionen bewusst sein. Neben der Optimierung des Abfrageplans sind weitere potenzielle Leistungsprobleme zu beachten, die das Sperren betreffen. Dies liegt jedoch außerhalb des Rahmens Ihres Problems.

Versuchen Sie, diesen Index zu erzwingen:

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))

Ich habe gerade überprüft, dass ein Index für genau dieselbe Abfrage verwendet wird:

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'

Manchmal verwendet MySQL keinen Index, auch wenn einer verfügbar ist. Dies ist beispielsweise der Fall, wenn das Optimierungsprogramm schätzt, dass MySQL für die Verwendung des Indexes auf einen sehr großen Prozentsatz der Zeilen in der Tabelle zugreifen muss. (In diesem Fall ist ein Tabellenscan wahrscheinlich viel schneller, da weniger Suchvorgänge erforderlich sind.)

Wie viel Prozent der Zeilen stimmen mit Ihrer IN-Klausel überein?

Ich weiß, ich komme zu spät zur Party. Aber ich hoffe, ich kann jemandem mit ähnlichen Problemen helfen.

In letzter Zeit habe ich das gleiche Problem. Dann entscheide ich mich für die Verwendung von Self-Join-Dingen, um mein Problem zu lösen ... Das Problem ist nicht MySQL. Problem sind wir. Der Rückgabetyp aus der Unterabfrage unterscheidet sich von unserer Tabelle. Daher müssen wir den Typ der Unterabfrage in den Typ der Auswahlspalte umwandeln . Nachfolgend finden Sie Beispielcode:

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`

Oder mein eigener Code:

Alt: (Index nicht verwenden: ~ 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;

Neu: (Verwendungsindex: ~ 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:

• Aufzeichnungen: ~ 3.5M
• PK: jxm_character (information_date, ranking_type, character_id)

SHOW VARIABLES LIKE '%version%';

'protocol_version', '10'
'version', '5.1.69-log'
'version_comment', 'Source distribution'

Letzter Hinweis: Vergewissern Sie sich, dass Sie die Regel des linken MySQL-Index verstehen.

P/s: Sorry für mein schlechtes Englisch. Ich poste meinen Code (Produktion natürlich), um meine Lösung zu löschen: D.

Wird es besser, wenn Sie die redundanten Klammern um die where-Klausel entfernen?

Obwohl es nur so sein könnte, weil Sie nur etwa 200 Zeilen haben, wurde entschieden, dass ein Tabellenscan schneller wäre. Versuchen Sie es mit einer Tabelle mit mehr Datensätzen.