MySQL-Abfrage IN()-Klausel langsam bei indizierter Spalte

Question

Ich habe eine MySQL-Abfrage, die von einem PHP-Skript generiert wird, wird die Abfrage etwas wie folgt aussehen:

SELECT * FROM Recipe_Data WHERE 404_Without_200 = 0 AND Failures_Without_Success = 0 AND RHD_No IN (10, 24, 34, 41, 43, 51, 57, 59, 61, 67, 84, 90, 272, 324, 402, 405, 414, 498, 500, 501, 510, 559, 562, 595, 632, 634, 640, 643, 647, 651, 703, 714, 719, 762, 765, 776, 796, 812, 814, 815, 822, 848, 853, 855, 858, 866, 891, 920, 947, 956, 962, 968, 1049, 1054, 1064, 1065, 1070, 1100, 1113, 1119, 1130, 1262, 1287, 1292, 1313, 1320, 1327, 1332, 1333, 1335, 1340, 1343, 1344, 1346, 1349, 1352, 1358, 1362, 1365, 1482, 1495, 1532, 1533, 1537, 1549, 1550, 1569, 1571, 1573, 1574, 1596, 1628, 1691, 1714, 1720, 1735, 1755, 1759, 1829, 1837, 1844, 1881, 1919, 2005, 2022, 2034, 2035, 2039, 2054, 2076, 2079, 2087, 2088, 2089, 2090, 2091, 2092, 2154, 2155, 2156, 2157, 2160, 2162, 2164, 2166, 2169, 2171, 2174, 2176, 2178, 2179, 2183, 2185, 2186, 2187, 2201, 2234, 2236, 2244, 2245, 2250, 2255, 2260, 2272, 2280, 2281, 2282, 2291, 2329, 2357, 2375, 2444, 2451, 2452, 2453, 2454, 2456, 2457, 2460, 2462, 2464, 2465, 2467, 2468, 2469, 2470, 2473, 2474, 2481, 2485, 2487, 2510, 2516, 2519, 2525, 2540, 2545, 2547, 2553, 2571, 2579, 2580, 2587, 2589, 2597, 2602, 2611, 2629, 2660, 2662, 2700, 2756, 2825, 2833, 2835, 2858, 2958, 2963, 2964, 3009, 3090, 3117, 3118, 3120, 3121, 3122, 3123, 3126, 3127, 3129, 3130, 3133, 3135, 3137, 3138, 3139, 3141, 3142, 3145, 3146, 3147, 3151, 3152, 3155, 3193, 3201, 3204, 3219, 3221, 3222, 3223, 3224, 3225, 3226, 3227, 3228, 3229, 3231, 3232, 3233, 3234, 3235, 3237, 3239, 3246, 3250, 3253, 3259, 3261, 3291, 3315, 3328, 3377, 3381, 3383, 3384, 3385, 3387, 3388, 3389, 3390, 3396, 3436, 3463, 3465, 3467, 3470, 3471, 3484, 3507, 3515, 3554, 3572, 3641, 3672, 3683, 3689, 3690, 3692, 3693, 3694, 3697, 3698, 3705, 3711, 3713, 3715, 3716, 3717, 3719, 3720, 3722, 3726, 3727, 3732, 3737, 3763, 3767, 3770, 3771, 3772, 3773, 3803, 3810, 3812, 3816, 3846, 3847, 3848, 3851, 3874, 3882, 3902, 3903, 3906, 3908, 3916, 3924, 3967, 3987, 4006, 4030, 4043, 4045, 4047, 4058, 4067, 4107, 4108, 4114, 4115, 4131, 4132, 4133, 4137, 4138, 4139, 4140, 4141, 4142, 4146, 4150, 4151, 4152, 4153, 4157, 4158, 4160, 4163, 4166, 4167, 4171, 4179, 4183, 4221, 4225, 4242, 4257, 4435, 4437, 4438, 4443, 4446, 4449, 4450, 4451, 4452, 4454, 4460, 4550, 4557, 4618, 4731, 4775, 4804, 4972, 5025, 5026, 5039, 5042, 5294, 5578, 5580, 5599, 5602, 5649, 5726, 5779, 5783, 5931, 5934, 5936, 5939, 5940, 5941, 5978, 6044, 6056, 6113, 6116, 6118, 6122, 6123, 6125, 6127, 6128, 6129, 6130, 6131, 6135, 6141, 6145, 6147, 6150, 6152, 6153, 6154, 6160, 6166, 6169);

Die Spalte RHD_No ist der Primärschlüssel für diese Datenbank, und es gibt insgesamt etwa 400.000 Zeilen. Das Problem ist, dass die Abfrage extrem langsam ist. Sie dauert oft um die 2 Sekunden, aber ich habe schon erlebt, dass sie bis zu 10 Sekunden dauert.

Wenn ich versuche, die Abfrage zu erklären, scheint alles in Ordnung zu sein:

+----+-------------+-------------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table       | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+-------------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | Recipe_Data | range | PRIMARY       | PRIMARY | 4       | NULL |  420 | Using where |
+----+-------------+-------------+-------+---------------+---------+---------+------+------+-------------+

Wenn ich ein Profil der Abfrage erstelle, erhalte ich:

mysql> show profile;
+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| starting                       | 0.000015 |
| checking query cache for query | 0.000266 |
| Opening tables                 | 0.000009 |
| System lock                    | 0.000004 |
| Table lock                     | 0.000006 |
| init                           | 0.000115 |
| optimizing                     | 0.000038 |
| statistics                     | 0.000797 |
| preparing                      | 0.000047 |
| executing                      | 0.000002 |
| Sending data                   | 2.675270 |
| end                            | 0.000007 |
| query end                      | 0.000003 |
| freeing items                  | 0.000071 |
| logging slow query             | 0.000002 |
| logging slow query             | 0.000058 |
| cleaning up                    | 0.000005 |
+--------------------------------+----------+

Ich beschäftige mich schon seit langem mit diesem Problem und habe noch keine Lösung gefunden. Ist an dieser Abfrage irgendetwas offensichtlich falsch? Ich verstehe nicht, wie das Betrachten von 420 Zeilen 2+ Sekunden dauern sollte.

Answer 1

Sie greifen auf 420 Zeilen über den Primärschlüssel zu, was wahrscheinlich zu einem Index-Zugriffspfad führt. Dies könnte auf 2 Indexseiten und eine Datenseite pro Schlüssel zugreifen. Wenn sich diese im Cache befinden, sollte die Abfrage schnell laufen. Ist dies nicht der Fall, wird jeder Seitenzugriff, der auf die Festplatte geht, die übliche Festplattenlatenz verursachen. Wenn wir von 5 ms Festplattenlatenz und 80 % Cache-Treffern ausgehen, kommen wir auf 420*3*0,2*5 ms=1,2 Sekunden, was in etwa dem entspricht, was Sie sehen.

Answer 2

Das Problem ist, dass IN wird im Grunde wie ein Haufen von OR s (z.B..

col IN (1,2,3)

est

col = 1 OR col = 2 OR col = 3

Das ist VIEL langsamer als eine Verbindung.

Sie sollten den SQL-Code generieren, der die temporäre Tabelle erstellt, sie mit den Werten in der "IN"-Klausel auffüllt und dann mit dieser temporären Tabelle verknüpft

CREATE TEMPORARY TABLE numbers (n INT)

Fügen Sie dann in einer Schleife

INSERT numbers  VALUES ($next_number)

Dann am Ende

SELECT * FROM numbers, Recipe_Data 
WHERE numbers.n = RHD_No

Answer 3

Sie sollten die IN-Klauseln in INNER JOIN-Klauseln umwandeln.

Sie können eine Abfrage wie diese umwandeln:

SELECT  foo   
FROM    bar   
WHERE bar.stuff IN  
       (SELECT  stuff FROM asdf)

In eine Abfrage wie diese andere:

SELECT  b.foo 
FROM    ( 
        SELECT  DISTINCT stuff 
        FROM    asdf ) a 
JOIN    bar b 
ON      b.stuff = a.stuff

Sie werden eine Menge an Leistung gewinnen.

Da die php die Abfrage generiert, versuchen Sie eine Art Trick wie eine temporäre Tabelle für die Elemente innerhalb der IN-Klausel. Versuchen Sie immer, die IN-Klauseln zu vermeiden, wenn Sie können, weil sie sehr zeitaufwendig sind.

Answer 4

Ich gehe hier ein Risiko ein und schlage vor, dass die Ausführung der folgenden Abfrage nur einmal, um einen für Ihre Abfrage geeigneten Index zu erstellen, die Abfragezeit um mindestens eine Sekunde reduzieren sollte...

CREATE INDEX returnstatus ON Recipe_Data(404_Without_200,Failures_Without_Success)

Siehe: http://dev.mysql.com/doc/refman/5.0/en/create-index.html für die Erstellung von Indizes, und http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html wie Indizes in Abfragen verwendet werden.

Andernfalls sehen Sie sich alle laufenden Prozesse auf mysql an, um zu sehen, ob eine laufende Abfrage aus irgendeiner Quelle sich einfach weigert, zu sterben, während sie die gesamte Zeit des Servers verbraucht, und beenden Sie sie. Siehe: http://dev.mysql.com/doc/refman/5.0/en/kill.html

Andernfalls ermitteln Sie, welche weiteren Gemeinsamkeiten die einzelnen Datensätze haben, um zu vermeiden, dass Sie jeden Datensatz einzeln über die ID-Nummer in Ihrer Datenbank referenzieren müssen. IN Anweisung. Fügen Sie ggf. eine weitere Tabellenspalte hinzu, um diese Gemeinsamkeiten zu erfassen. Fügen Sie dann die Spalte(n) mit dieser Gemeinsamkeit zum obigen Index hinzu und filtern Sie danach in Ihrer WHERE Klausel anstelle der Verwendung der IN Erklärung. Wenn Sie zum Beispiel nur die ID-Nummern auf einer Seite ausdrucken wollen, müssen Sie eine visible Spalte als Typ: tinyint mit Wert 0 auszuschließen, und Wert 1 die Sie in Ihre Suchergebnisse aufnehmen möchten, und fügen Sie dann visible Spalte zu Ihren Indizes und WHERE Klausel, um die Abfrage zu beschleunigen. Das bräuchten Sie nicht IN Aussage überhaupt nicht.

Vielleicht ist Ihr in Anweisung wird dynamisch anhand einer früheren Abfrage erstellt. Wenn das der Fall ist, versuchen Sie, alle Zeilen mit Recipe_Data WHERE 404_Without_200 = 0 AND Failures_Without_Success = 0 . Dann verwerfen Sie in Ihrem PHP-Skript einfach einen Datensatz in Ihrer Abrufschleife, wenn die RHD_No nicht mit einem erwarteten Wert übereinstimmt.

Answer 5

Für jemanden wie mich, der SQlAlchemy verwendet, ist die Verwendung einer for-Schleife ebenfalls eine gute Option:

rows=[]

for id in ids:
  row = cls.query.filter(cls.id==id).first()
  if row:
     rows.append(row)

#return rows