Performance (MySQL)
MySQL langzamer dan je verwacht had?
Hier is de hitlist
- Indexes
- Enkelvoudige joins
- Soorten joins
- Beperk aantal kolommen
Indexes
Dit is het eerste om aan te denken rondom performance: Indexes.
Ik heb dankzij indexes de tijdsduur van queries van minuten, kunnen reduceren tot secondes. Gewoon alle velden die in joins voorkomen, voorzien van een index → Indexes (MySQL)
Enkelvoudige joins
Vervang update-queries met meervoudige joins, door losse meerdere update-queries die elk met enkelvoudige joins werken. Zie casus verderop in dit artikel.
Soorten joins
Gewone joins zijn supersnel terwijl bv. left joins onthutsend langzaam zijn [1].
Beperk aantal kolommen
Voorbeeld (nov. 2019):
Op een tabel met ca. 40 kolommen, kostte deze query 15,7 seconde voor 1.000 records:
update content_tmp_02 set part_80_meta_description_01 = "Te koop op koolborstels.shop", part_80_meta_description_02 = "Prijs:", part_80_meta_description_03 = "Afmetingen:", part_80_sku = "SKU:", part_80_for = "voor";
Vervolgens de tabel gesplitst. De nieuwe tabel bevat alleen de PK + bovengenoemde velden. Performance: 15,7s → 0,049s - Dat is bijna 400x zo snel!
EXPLAIN
Met explain kun je zien hoe MySQL een query aanpakt. Ik ben niet erg onder de indruk (nov. 2019).
EXPLAIN SELECT * FROM foo WHERE foo.bar = 'infrastructure as a service' OR foo.bar = 'iaas';
Samengestelde PK's: Volgorde is van belang!
als je een index defineert, bv. (tool_id, sku)
, maar je zoekt in de praktijk op sku
, dan heb je niets aan deze index, omdat tool_id
eerst is. In dat geval:
- volgorde van de velden binnen de samengestelde sleutel aanpassen
- Additionele index defineren voor alleen dat veld.
Zie Grouping (MySQL) voor een gruwelijk voorbeeld.
Varchar ipv. txt
TXT-velden lijken een fractie sneller te zijn dan varchar-velden. Verschil is verwaarloosbaar.
Casus: Update-query met dubbele join (aug. 2018)
Deze query eindigde steeds met een time-out na 300 seconde. Zo ver ik kan nagaan, stonden de indexes goed:
update description_tmp join root_tmp on description_tmp.sku = root_tmp.sku_leading join tool_tmp on root_tmp.tool_id = tool_tmp.tool_id set en_part_02_application_special = concat ( " for ", root_tmp.tool_brand, " ", tool_tmp.kind_en, " ", # Hiervoor heb je de dubbbele join root_tmp.tool_type, " " );
Echter, opgeslitst in twee enkelvoudige update-queries, doet-ie er maar zes seconde over:
# Use to update-queries with a single join each, in stead of one # update-query with a double join: Huge difference in execution time # # First update-query: root_tmp ################################## # alter table root_tmp add column kind_en varchar(45) null; update root_tmp join tool_tmp on root_tmp.tool_id = tool_tmp.tool_id set root_tmp.kind_en = tool_tmp.kind_en; # Second update-query ################################## # call add_column_unless_exists("description_tmp","en_part_02_application_special","varchar(100)"); update description_tmp join root_tmp on description_tmp.sku = root_tmp.sku_leading set en_part_02_application_special = concat ( " for ", root_tmp.tool_brand, " ", root_tmp.kind_en, " ", root_tmp.tool_type );
Zie ook
Bronnen
- https://stackoverflow.com/questions/7908531/improve-performance-with-left-join
- https://dev.mysql.com/doc/refman/8.0/en/explain.html
- https://www.percona.com/blog/2015/06/15/speed-up-group-by-queries-with-subselects-in-mysql/
- https://dzone.com/articles/how-to-optimize-mysql-queries-for-speed-and-perfor