BAUSTELLE: Zusätzliche Indizes für die Datenbank

[vr]

Hallo,

Die ganze Index-Vorschlagsliste auf Brauchbarkeit zu checken wäre ein größeres Projekt. Einen einzigen Index neu ins Spiel zu bringen, kann schon mal (muss nicht) mit ein paar Stunden Testen und Abfragenanalysieren und Aufrufstellen/Nutzungsmuster im Code untersuchen einhergehen.

Eigentlich läuft das eher so, dass man gezielt einen setzt, wenn man entweder von vornherein weiss, dass Abfragen den brauchen können - oftmals sieht man es ihnen und den Daten an - oder wenn die Performance an einer bestimmten Stelle schlecht ist, also Abfragen bspw mehr als 2 Sek. dauern. Der Normalfall ist eher letzteres - jemand berichtet, dass irgendwas lange dauert. Dann schaut man sich die Abfragen an, die der Shop an der Stelle absetzt und versucht die Abfrage zu isolieren, die die Zeit verdaddelt. Hat man die eine am Wickel, findet man raus, warum. Evtl fehlt ihr wirklich ein Index. Evtl wird sie unnötigerweise 200 mal in einer Schleife ausgeführt. Evtl kann sie besser formuliert werden.

Denkt dran, dass sich mit jedem neuen Index speziell auf großen Tabellen auch die Restore-Zeit der DB erhöht, man verkleinert sich also das Wartungsfenster. U.a. deshalb bin ich für nur soviele Indizes wie nötig, und die gezielt gesetzt.

Grüße, Volker

[Tomcraft]

Alles klar, danke für die tolle Erklärung!

Grüße

Torsten

[DokuMan]

Ich hab mal mit Blick auf andere Forks eine Liste möglicher Kandidaten erstellt, die noch nicht mit der aktuellen trunk-Version erschlagen wurden.
Über die Sinnhaftigkeit darf sicherlich an der ein oder anderen Stelle diskutiert werden

Code: SQL  [Auswählen]

#KEY idx_content_group (content_group),
ALTER TABLE content_manager ADD INDEX (content_group);

#KEY idx_orders_id (orders_id)
ALTER TABLE orders_status_history ADD INDEX ( orders_id);
ALTER TABLE orders_products_attributes ADD INDEX ( orders_id );
ALTER TABLE orders_products_download ADD INDEX ( orders_id );

#KEY idx_orders_products_id (orders_products_id)
ALTER TABLE orders_products_attributes ADD INDEX ( orders_products_id );

#KEY idx_products_id (products_id)
ALTER TABLE products_images ADD INDEX ( products_id );
ALTER TABLE products_xsell ADD INDEX ( products_id );
ALTER TABLE reviews ADD INDEX ( products_id );
ALTER TABLE products_content ADD INDEX ( products_id );

#KEY idx_manufacturers_id (manufacturers_id),
ALTER TABLE products ADD INDEX ( manufacturers_id );

#KEY idx_products_model (products_model)
ALTER TABLE products ADD INDEX ( products_model );

#KEY idx_categories_id (categories_id)
ALTER TABLE products_to_categories ADD INDEX ( categories_id );

#KEY idx_session_id (session_id)
ALTER TABLE whos_online ADD INDEX ( session_id );

#KEY idx_customer_id (customer_id)
ALTER TABLE whos_online ADD INDEX ( customer_id );
ALTER TABLE customers_basket ADD INDEX ( customers_id );
ALTER TABLE customers_basket_attributes ADD INDEX ( customers_id );
ALTER TABLE orders ADD INDEX ( customers_id);
 

[atmiral]

Ich habe einige Zeit aus verschiedenen Projekten mit unterschiedlichen osc oder xtc Forks und Versionen einen SQL-Script zusammengestellt.

Code: SQL  [Auswählen]

ALTER TABLE banners ADD INDEX idx_banners_group (banners_group);
ALTER TABLE banners_history ADD INDEX idx_banners_id (banners_id);

ALTER TABLE campaigns_ip ADD INDEX idx_campaign (campaign);
ALTER TABLE campaigns_ip ADD INDEX idx_time (TIME);

ALTER TABLE categories ADD INDEX idx_categories_status (categories_status);

ALTER TABLE configuration ADD INDEX idx_key (configuration_key);

ALTER TABLE content_manager ADD INDEX idx_file_flag (file_flag);
ALTER TABLE content_manager ADD INDEX idx_content_group (content_group);

ALTER TABLE coupons ADD INDEX idx_coupon_code (coupon_code);

ALTER TABLE coupon_redeem_track ADD INDEX idx_coupon_customer_id (coupon_id, customer_id);
ALTER TABLE coupon_redeem_track ADD INDEX idx_coupon_id (coupon_id);
ALTER TABLE coupon_redeem_track ADD INDEX idx_customer_id (customer_id);

ALTER TABLE customers ADD INDEX idx_email_address (customers_email_address);
ALTER TABLE customers ADD INDEX idx_refferers_id (refferers_id);

ALTER TABLE customers_basket ADD INDEX idx_customers_id (customers_id);

ALTER TABLE customers_basket_attributes ADD INDEX idx_customers_id (customers_id);

ALTER TABLE customers_memo ADD INDEX idx_customers_id (customers_id);

ALTER TABLE customers_status_history ADD INDEX idx_customers_id (customers_id);

ALTER TABLE newsletter_recipients ADD INDEX idx_customers_id (customers_id);

ALTER TABLE orders ADD INDEX idx_customers_id (customers_id);
ALTER TABLE orders ADD INDEX idx_status_id (orders_status);
ALTER TABLE orders ADD INDEX idx_date_purchased (date_purchased);
ALTER TABLE orders ADD INDEX idx_orders_date_finished (orders_date_finished);
ALTER TABLE orders ADD INDEX idx_refferers_id (refferers_id);

--ALTER TABLE orders_products ADD INDEX idx_orders_id (orders_id);
--ALTER TABLE orders_products ADD INDEX idx_products_id (products_id);

-- ALTER TABLE orders_products_attributes ADD INDEX idx_orders_id (orders_id);
ALTER TABLE orders_products_attributes ADD INDEX idx_products_id (orders_products_id);
ALTER TABLE orders_products_attributes ADD INDEX idx_orders_products (orders_id, orders_products_id);

ALTER TABLE orders_products_download ADD INDEX idx_products_id (orders_products_id);
ALTER TABLE orders_products_download ADD INDEX idx_orders_products (orders_id, orders_products_id);

ALTER TABLE orders_status_history ADD INDEX idx_orders_id (orders_id);
ALTER TABLE orders_status_history ADD INDEX idx_orders_status_id (orders_status_id);

ALTER TABLE orders_total ADD INDEX idx_class (class);

ALTER TABLE personal_offers_by_customers_status_0 ADD INDEX idx_products_id (products_id);
ALTER TABLE personal_offers_by_customers_status_1 ADD INDEX idx_products_id (products_id);
ALTER TABLE personal_offers_by_customers_status_2 ADD INDEX idx_products_id (products_id);
ALTER TABLE personal_offers_by_customers_status_3 ADD INDEX idx_products_id (products_id);

ALTER TABLE products ADD INDEX idx_manufacturers_id (manufacturers_id);
--ALTER TABLE products ADD INDEX idx_products_date_added (products_date_added);
ALTER TABLE products ADD INDEX idx_products_status (products_status);

--ALTER TABLE products_attributes ADD INDEX idx_products_id (products_id);
--ALTER TABLE products_attributes ADD INDEX idx_options_id (options_id);
--ALTER TABLE products_attributes ADD INDEX idx_options_values_id (options_values_id);

ALTER TABLE products_content ADD INDEX idx_products_languages (products_id, languages_id);

ALTER TABLE products_images ADD INDEX idx_products_id (products_id);

ALTER TABLE products_options_values_to_products_options ADD INDEX idx_options_id (products_options_id);

ALTER TABLE products_options_values_to_products_options ADD INDEX idx_options_values_id (products_options_values_id);

ALTER TABLE products_to_categories ADD INDEX idx_categories_id (categories_id);

ALTER TABLE products_vpe ADD PRIMARY KEY (products_vpe_id, language_id);

ALTER TABLE products_xsell ADD INDEX idx_products_id (products_id);
ALTER TABLE products_xsell ADD INDEX idx_xsell_id (xsell_id);
ALTER TABLE products_xsell ADD INDEX idx_grp_name_id (products_xsell_grp_name_id);

ALTER TABLE reviews ADD INDEX idx_products_id (products_id);

ALTER TABLE sessions ADD INDEX idx_expiry (expiry);

ALTER TABLE specials ADD INDEX idx_products_id (products_id);
ALTER TABLE specials ADD INDEX idx_expires_date (expires_date);
ALTER TABLE specials ADD INDEX idx_status (STATUS);

ALTER TABLE tax_rates ADD INDEX idx_tax_zone_id (tax_zone_id);
ALTER TABLE tax_rates ADD INDEX idx_tax_class_id (tax_class_id);

ALTER TABLE whos_online ADD INDEX idx_session_id (session_id);
ALTER TABLE whos_online ADD INDEX idx_customers_id (customer_id);

ALTER TABLE zones_to_geo_zones ADD INDEX idx_country_id (zone_country_id);
ALTER TABLE zones_to_geo_zones ADD INDEX idx_zone_id (zone_id);
ALTER TABLE zones_to_geo_zones ADD INDEX idx_geo_zone_id (geo_zone_id);
 

Die Liste ist anders sortiert als die von DokuMan, weil es praktischer ist im phpMyAdmin alphabetisch die Tabellen durchzugehen und abzugleichen, z.B. wenn ein oder andere index aus dem Script bereits existiert...

Gruß
Alexander

[h-h-h]

Genau das habe ich gerade gesucht.
Habe gerade mit meinem Hoster genau dieses Thema besprochen.

Danke

h-h-h

[Tomcraft]

Wer ist denn dein Hoster?

Danke für's Bereitstellen. Vielleicht kann Volker (unser SQL-Spezialist) da nochmal rüber schauen?

Grüße

Torsten

[vr]

Danke! Ich habs mir als Favorit markiert, damit ichs nicht vergesse.

Grüße, Volker

[Tomcraft]

Oh... leg dir mal vorsichtshalber den Link noch irgendwo ab, denn ich weiss nicht genau, ob Favoriten bei der Foren-Migration mit übernommen werden, aber ich frage nochmal nach.

Grüße

Torsten

[vr]

Hallo,

erstmal danke für die Aufstellungen. Um noch annähernd den Effekt beurteilen zu können, würde ich mit Dokumans Liste anfangen, weil sie kleiner ist. Dokumans Liste läuft überwiegend darauf hinaus, Fremdschlüssel (auch wenn sie nicht so deklariert sind) zu den großen Tabellen wie products, orders, customers anzulegen. Der Ansatz hat seine Berechtigung - in anderen Datenbanksystemen, die Fremdschlüssel vernünftig unterstützen, werden Fremdschlüssel automatisch indiziert. Die übrigen: products_model in products kommt zwar eher in like-Bedingungen vor, da kann der Index eh nicht genutzt werden, aber immerhin auch bei der Sortierung. idx_content_group in content_manager lohnt sich eigentlich nicht, hängt von der Größe der Tabelle ab, hier müsste man vergleichen.

Wenn wir aber mehrere Indizes auf einmal ändern, ist es schwer herauszufinden, welche was gebracht haben, ausser man analysiert das slow log vorher und nachher. Hängt ausserdem von der konkreten Datensituation ab. Hier nochmal Dokumans Liste:

Code: SQL  [Auswählen]

#KEY idx_content_group (content_group),
ALTER TABLE content_manager ADD INDEX (content_group);

#KEY idx_orders_id (orders_id)
ALTER TABLE orders_status_history ADD INDEX ( orders_id);
ALTER TABLE orders_products_attributes ADD INDEX ( orders_id );
ALTER TABLE orders_products_download ADD INDEX ( orders_id );

#KEY idx_orders_products_id (orders_products_id)
ALTER TABLE orders_products_attributes ADD INDEX ( orders_products_id );

#KEY idx_products_id (products_id)
ALTER TABLE products_images ADD INDEX ( products_id );
ALTER TABLE products_xsell ADD INDEX ( products_id );
ALTER TABLE reviews ADD INDEX ( products_id );
ALTER TABLE products_content ADD INDEX ( products_id );

#KEY idx_manufacturers_id (manufacturers_id),
ALTER TABLE products ADD INDEX ( manufacturers_id );

#KEY idx_products_model (products_model)
ALTER TABLE products ADD INDEX ( products_model );

#KEY idx_categories_id (categories_id)
ALTER TABLE products_to_categories ADD INDEX ( categories_id );

#KEY idx_session_id (session_id)
ALTER TABLE whos_online ADD INDEX ( session_id );

#KEY idx_customer_id (customer_id)
ALTER TABLE whos_online ADD INDEX ( customer_id );
ALTER TABLE customers_basket ADD INDEX ( customers_id );
ALTER TABLE customers_basket_attributes ADD INDEX ( customers_id );
ALTER TABLE orders ADD INDEX ( customers_id);

Bevor wir die übernehmen, sollten bitte ein paar Leute mit großen Shops die einspielen und Ergebnisse durchgeben. Passieren kann da nix.

Grüße, Volker

[ShopNix]

Die Diskussion finde ich recht lustig, denn den Ausgangspunkt habe ich offensichtlich selbst geliefert.

Meine Liste entstand aus der Notwendigkeit, einem Shop mit ca. 8.000 Artikeln mehr Speed zu geben. Dazu habe ich zunächst von MySQL ein Log-File schreiben lassen, das mir alle Querys protokollierte, die mehr als 2 Sekunden in Anspruch nahmen. Diese Querys habe ich mir dann wieder von MySQL per "explain" erklären lassen. Explain zeigt, welchen Weg der Optimizer nimmt, welche Indizes er benutzt und wo Full-Table-Scans notwendig sind.

Wo Full-Table-Scans nötig waren, habe ich dann versucht, mit Indizes Abhilfe zu schaffen und die Zahl der gelesenen Datensätze verglichen.

Die Liste ist sicher nicht vollständig, weil sie aus genau einem Anwendungsfall resultiert. Dort brachte sie allerdings (in Zusammenhang mit einer Optimierung der MySQL-Parameter) einen Performancegewinn von rund 60%.

Natürlich schlucken Indizes auch Ressourcen, aber ein wenig Platz auf der Platte habe ich immer, und der Aufbau von Indizes beim Laden der Tabellen bzw. beim Anlegen von Artikeln etc. fällt gegen die Mehrleistung bei den Abfragen nicht ins Gewicht.

Damit ist der grundsätzliche Nutzen der o.a. Liste hoffentlich abschließend geklärt.

Sinnvoll ist es natürlich, derartige Experimente auch auf dem eigenen System durchzuführen. Immerhin ändert sich der Datenbankaufbau mit der Programmpflege und eventuellen Zusatzmodulen. Zudem können andere Anwendungsfälle natürlich auch zum vermehrten Auftreten langsamer Querys führen, die in meinem Fall gar nicht auftraten.

Noch eine Frage zum Schluß: Irgendwer hat die kombinierten Indizes verworfen und statt dessen zwei einzelne Indizes vorgeschlagen. Darf ich den Grund erfahren?

[Haina]

Volker hat mit all Seinen Einwänden Recht,
wenn man an der Stelle bohrt sollte man wohl überlegt handeln.

Die SQL-DB ist mächtig und performant - aber nicht resistent gegen Dummheit und Overhead.
Weniger ist oft mehr.

[Tomcraft]

Die Diskussion finde ich recht lustig, denn den Ausgangspunkt habe ich offensichtlich selbst geliefert.
[...]

Wie kommst du denn darauf?

Grüße

Torsten

[ShopNix]

Siehe Link auf dem ersten Beitrag dieses Treads.  

[EDIT Tomcraft 29.05.2011: Full-Quote entfernt, siehe: FAQ: Full-Quotes und Groß-/Kleinschreibung - mit anderen Worten Netiquette!]

[Tomcraft]

Ah okay... Dein Blog also.

Grüße

Torsten

[khmweb]

[...]
Ich denke es macht keinen Sinn, die Indices schon während der Shopinstallation anzulegen, denn da sind sie ja noch leer.
Ich weiß nicht, ob MySQL die Indices automatisch neu aufbaut.
[...]

Hi, vielleicht hilft das etwas weiter?

MySQL Index - Overclock Your Tables

Gruß
khmweb