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].
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!
Ergens las is dat 100 kolommen een kritische grens is. Ik ervaar dat 40 al kritisch is!
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