Neulich f\u00fchrte ich eine Query aus, die mehr als 9 Sekunden rechnete. Die Query bestand aus einem Join zweier Tabellen: Eine Liste von Items und einem Zeitverlauf je Item. Vom Schema sah das ungef\u00e4hr so aus:<\/p>\n
Die Query sah ungef\u00e4hr so aus: Auf gutdeutsch also: Liste alle Items mit ihrem neuesten Preis auf.<\/p>\n Hat man etwa 9.000 Items und ca. 11.000 Preis\u00e4nderungen, w\u00fcrde die Query sofort lahm werden. Doch woran liegt das?<\/p>\n Zum einen gibt es hier eine Nested Query. Es gibt zwar eine allgemeine Methode, beliebige Sub-Queries in Joins umzuwandeln<\/a> (Paper von Thomas Neumann auf der BTW15, Hamburg), allerdings kann diese Methode nicht mit der ORDER BY und dem LIMIT-Klausel umgehen. Der Ausf\u00fchrungsplan der Query ist also klar: Zuerst wird die Item-Tabelle gescannt und f\u00fcr jede ihrer Zeilen wird die Sub-Query ausgef\u00fchrt. Grundessenz einer Query-Optimierung hier ist also, dass die Subquery m\u00f6glichst ohne Zeitaufwand stattfindet (am besten Lookup-Index).<\/p>\n Das n\u00e4chste Problem ist die unklare Schl\u00fcssel-Situation, denn hier bilden zwei Schl\u00fcssel den Prim\u00e4r-Index. Ein Index m\u00fcsste also beide Schl\u00fcssel enthalten.<\/p>\n Es gibt grunds\u00e4tzlich zwei Arten von Indizes: Den Hash-Index und den BTree. Der Hash-Index ist eine Hash-Table, aus der man idealerweise mit einem Aufwand O(1) Items herauspicken kann, deren Zugriffs-Schl\u00fcssel man kennt. Der BTree hingegen ist eine Art sortierter Listen-Baum. Der BTree kann weit mehr als der Hash-Index: Items mit einem exakten Key finden, Items, deren Key zwischen zwei Werten liegt oder einfach die Items in sortierter Reihenfolge auslesen.<\/p>\n Weitet man einen Index auf mehrere Dimensionen aus, spielt sich etwas interessantes ab: Beim Hash-Index kann man nur noch Werte abfragen, bei denen man die Werte aller Zugriffs-Schl\u00fcssel kennt. Der BTree hingegen hat folgende Features:<\/p>\n Technisch gesehen ist der multidimensionale BTree also eine Liste, die nach den Schl\u00fcsseln sortiert ist. Der Aufwand f\u00fcr eine BTree-Anfrage ist O(log(n)).<\/p>\n Jeder Index verbraucht Speicher in der Datenbank. Deshalb sollte man sparsam mit Indizes umgehen. Doch folgende Richtlinien helfen bei einem guten Index-Aufbau:<\/p>\n Um die Query im oberen Beispiel also effizient abarbeiten zu k\u00f6nnen, ist folgender Index empfehlenswert: Die beiden Equi-Joins ShopID und SubID sind normale O(log(n))-Lookups auf dem Index. Hat man diese Range eingeschr\u00e4nkt, besteht die Range nur noch aus einer nach Timestamp sortierten Liste des \u00fcber ShopID und SubID festgelegten Items. Aufgrund der ORDER-BY-DESC-Klausel wei\u00df der Index nun, dass er aus der sortierten Liste das letzte Item zuerst auslesen muss. Die LIMIT-1-Klausel bricht beim Auslesen des ersten Items ab. Effektiv wurde also O(log(n)) Zeit in der inneren Schleife verbraucht.<\/p>\n Zu jeder noch so komplizierten Query l\u00e4sst sich ein Multi-Index finden, der die Joins optimiert. Software von Launix<\/a> wird also auch bei gro\u00dfen Datenmengen effizient laufen.<\/p>\n","protected":false},"excerpt":{"rendered":" Neulich f\u00fchrte ich eine Query aus, die mehr als 9 Sekunden rechnete. Die Query bestand aus einem Join zweier Tabellen: Eine Liste von Items und einem Zeitverlauf je Item. Vom Schema sah das ungef\u00e4hr so aus: Item(ShopID, SubID, Title) ItemPrice(ShopID, SubID, Price, Timestamp) Die Query sah ungef\u00e4hr so aus: SELECT Title, (SELECT Price FROM ItemPrice…<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_editorskit_title_hidden":false,"_editorskit_reading_time":0,"_editorskit_is_block_options_detached":false,"_editorskit_block_options_position":"{}","_uag_custom_page_level_css":"","footnotes":""},"categories":[128],"tags":[30,72,29],"class_list":["post-659","post","type-post","status-publish","format-standard","hentry","category-programming","tag-datenbank","tag-programmieren","tag-sql","single-item"],"featured_image_urls_v2":{"full":"","thumbnail":"","medium":"","medium_large":"","large":"","1536x1536":"","2048x2048":"","trp-custom-language-flag":"","xs-thumb":"","appku-shop-single":""},"post_excerpt_stackable_v2":" Neulich f\u00fchrte ich eine Query aus, die mehr als 9 Sekunden rechnete. Die Query bestand aus einem Join zweier Tabellen: Eine Liste von Items und einem Zeitverlauf je Item. Vom Schema sah das ungef\u00e4hr so aus: Item(ShopID, SubID, Title) ItemPrice(ShopID, SubID, Price, Timestamp) Die Query sah ungef\u00e4hr so aus: SELECT Title, (SELECT Price FROM ItemPrice p WHERE i.ShopID = p.ShopID AND i.SubID = p.SubID ORDER BY Timestamp DESC LIMIT 1) AS Price FROM Item i Auf gutdeutsch also: Liste alle Items mit ihrem neuesten Preis auf. Die Krux mit den Joins? Hat man etwa 9.000 Items und ca. 11.000 Preis\u00e4nderungen,…<\/p>\n","category_list_v2":"Programming<\/a>","author_info_v2":{"name":"Carl-Philip H\u00e4nsch","url":"https:\/\/launix.de\/launix\/author\/carli\/"},"comments_num_v2":"0 comments","uagb_featured_image_src":{"full":false,"thumbnail":false,"medium":false,"medium_large":false,"large":false,"1536x1536":false,"2048x2048":false,"trp-custom-language-flag":false,"xs-thumb":false,"appku-shop-single":false},"uagb_author_info":{"display_name":"Carl-Philip H\u00e4nsch","author_link":"https:\/\/launix.de\/launix\/author\/carli\/"},"uagb_comment_info":0,"uagb_excerpt":"Neulich f\u00fchrte ich eine Query aus, die mehr als 9 Sekunden rechnete. Die Query bestand aus einem Join zweier Tabellen: Eine Liste von Items und einem Zeitverlauf je Item. Vom Schema sah das ungef\u00e4hr so aus: Item(ShopID, SubID, Title) ItemPrice(ShopID, SubID, Price, Timestamp) Die Query sah ungef\u00e4hr so aus: SELECT Title, (SELECT Price FROM ItemPrice...","_links":{"self":[{"href":"https:\/\/launix.de\/launix\/wp-json\/wp\/v2\/posts\/659","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/launix.de\/launix\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/launix.de\/launix\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/launix.de\/launix\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/launix.de\/launix\/wp-json\/wp\/v2\/comments?post=659"}],"version-history":[{"count":7,"href":"https:\/\/launix.de\/launix\/wp-json\/wp\/v2\/posts\/659\/revisions"}],"predecessor-version":[{"id":5375,"href":"https:\/\/launix.de\/launix\/wp-json\/wp\/v2\/posts\/659\/revisions\/5375"}],"wp:attachment":[{"href":"https:\/\/launix.de\/launix\/wp-json\/wp\/v2\/media?parent=659"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/launix.de\/launix\/wp-json\/wp\/v2\/categories?post=659"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/launix.de\/launix\/wp-json\/wp\/v2\/tags?post=659"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}Item(ShopID, SubID, Title)
\nItemPrice(ShopID, SubID, Price, Timestamp)<\/code><\/p>\n
\nSELECT Title, (SELECT Price FROM ItemPrice p WHERE i.ShopID = p.ShopID AND i.SubID = p.SubID ORDER BY Timestamp DESC LIMIT 1) AS Price FROM Item i<\/code><\/p>\nDie Krux mit den Joins?<\/h2>\n
Der MultiIndex<\/h2>\n
\n
Effiziente Verwendung des MultiIndex<\/h2>\n
\n
Zur\u00fcck zum Beispiel<\/h2>\n
\nIndex(ShopID, SubID, Timestamp)<\/code><\/p>\nFazit<\/h2>\n