Optimiere SELECT ... WHERE IN (...)

Question

Ich erhalte eine Sequenz von Produkt-IDs aus einem externen System. Ich muss die Produktinformationen unter Beibehaltung der Reihenfolge anzeigen.

Ich verwende die folgende Abfrage, um dies zu tun:

SELECT * FROM  products
WHERE  prodid in (10331,11639,12127..) ORDER BY Field(prodid, 10331,11639,12127...);

Die Sequenz kann aus 20 IDs bestehen. Prodid hat einen b-tree Index.

Es handelt sich um eine sehr häufige Abfrage, und ich versuche, Möglichkeiten zu finden, um die Leistung dieses Teils des Systems zu verbessern. Derzeit beträgt die durchschnittliche Zeit für diese Abfrage 0,14-0,2 Sekunden. Ich würde gerne die Zeit auf 0,01-0,05 Sekunden verkürzen.

Wie kann ich das am besten erreichen? MySQL HASH-Index, Speichern der Produkt-ID in Memcached oder etwas anderes?

Answer 1

SELECT * FROM  Produkte                         <<-- select * ist nicht optimal
WHERE  prodid in (10331,11639,12127..) 
ORDER BY Field(prodid, 10331,11639,12127...);   <<-- dein Problem ist hier

Zuerst setzen Sie einen Index auf prodid siehe Antwort von @Anthony.

Dann ändern Sie die Abfrage wie folgt:

SELECT only,the,fields,you,need FROM  Produkte
WHERE  prodid in (10331,11639,12127..) 
ORDER BY prodid

Wenn Sie sicherstellen, dass Ihre IN-Liste vor dem Angebot an die IN-Klausel aufsteigend sortiert ist, wird das order by prodid das gleiche Ergebnis liefern wie order by field(...

• Die Verwendung einer Funktion anstelle eines Feldes beseitigt die Möglichkeit der Verwendung eines Index und verursacht Langsamkeit.
• select * holt Daten, die Sie möglicherweise nicht benötigen, verursacht zusätzlichen Zugriff auf die Festplatte und zusätzlichen Speicherbedarf sowie zusätzlichen Netzwerkverkehr.
• Bei InnoDB, wenn Sie nur indexierte Felder selecten, liest MySQL niemals die Tabelle, sondern nur den Index und spart Zeit (in Ihrem Fall ist dies wahrscheinlich jedoch kein Problem)

Was ist der beste Weg, dies zu tun? MySQL HASH-Index, Speichern von Produkt IDs in Memcached oder etwas anderes?

Es gibt ein paar Tricks, die Sie verwenden können.

• Wenn die Produkte-Tabelle nicht zu groß ist, können Sie sie zu einer memory-Tabelle machen, die im RAM gespeichert ist. Machen Sie dies nicht für große Tabellen, es wird andere Dinge verlangsamen.
  Sie können nur hash-Indizes auf Memory-Tabellen verwenden.
• Wenn die prodid's kontinuierlich sind, können Sie statt IN (1000, 1001 ..., 1019) BETWEEN 1000 AND 1019 verwenden.

Answer 2

Sie können versuchen, eine Vereinigung von Ergebnissen zu erstellen:

SELECT * FROM  products WHERE prodid = 10331
UNION ALL
SELECT * FROM  products WHERE prodid = 11639
UNION ALL
.
.
UNION ALL
SELECT * FROM  products WHERE prodid = 12127

Answer 3

Sie könnten versuchen, einen Index auf der prodid Spalte mit der folgenden Abfrage zu setzen:

CREATE INDEX index_name
ON products (prodid); 

Weitere Informationen finden Sie unter diesem Beitrag.