10 miljoen database records
Ik was bezig met het kijken naar een tabel met 10 miljoen records. In dit geval kan een redelijk simpele query die records ophaalt aan het eind van de tabel erg lang duren. Is hier een goede oplossing voor? De tabel waarmee ik heb getest had 8 kolommen (varchar en int), met een primary key en AI.
Heb je het echter op enkele rijen uit de tabel zoeken (dus met WHERE), dan kan je hier optimalisatie halen door enkel goed doordachte indexen te plaatsen. Je kan ook onderzoeken waarom je query traag werkt door EXPLAIN ervoor te plaatsen.
Heb je ook de juiste indexes gemaakt? En die tabel, is die goed genormaliseerd?
Ik heb (online) een tabel met een dikke 1,2 mil records, waarvan de grootste tabel een dikke 570,000 records heeft (niet zo groot als die van jou, maar is een voorbeeld) en met meerdere users tegelijkertijd merk je helemaal niets van het selecteren.
Hoe ziet je tabel er (qua structuur) uit en welke selectiecriteria heb je toegepast in je query?
id (int) 7 (ai) primary key
userid (int) 7
varchar1 (varchar) 100
varchar2 (varchar) 100
varchar3 (varchar) 50
varchar4 (varchar) 50
varchar5 (varchar) 10
varchar6 (varchar) 10
Ongeveer zo ziet die tabel er uit, ik weet niet of dit in de praktijk voor gaat komen, maar het was een testje. Een query kan soms meer dan 10 seconden duren, doe ik dan iets verkeerd? Gewoon met een where criterium.
En hoe ziet je query er uit?
Code (php)
1
SELECT id FROM tabel LIMIT 9000000 , 30
Duurt al een paar seconden.
Als ik steen goed begrijp zitten er nog geen indexes in de table. Dus is het voor ons vooral lonend om de query te zien, zodat we van daaruit kunnen bepalen welke indexes er gemaakt moeten worden.
@steen
Leuk testje, dat wel.. Maar nu alsnog er een index op zetten gaat je ook goed tijd kosten.
Je query duurt 10 seconden? Of het weergeven in de browser duurt 10 seconden? Dat is namelijk een wereld van verschil! (neem de tijd op voor en na mysql_query of iets gelijkaardigs)
Je zegt dat je een where doet, maar je vermeldt niet op welke velden met welke gegevens. Ook vermeld je niet welke indexen je hebt gezet.
Zoals ik al zei, doe eens EXPLAIN SELECT ... FROM tabel WHERE ...
Quote:
zodat we van daaruit kunnen bepalen welke indexes er gemaakt moeten worden.
Yep, maar ik verwacht dat de TS niet slechts één type query op zijn database gaat draaien. Dus ik vermoed dat je beter op basis van de relatietypen van de data e.d. kunt werken. Bijv. FK/PK-relatie op userid is heel waarschijnlijk. Daarop is dus een index wel handig. En ik mag gewoon hopen dat ie geen relaties heeft op zijn varchar, want dat is gewoon een type die relatief een slechte performance heeft.
Ik ben overigens net als Hipska ook wel heel benieuwd naar die explain. Voornamelijk naar de extended version daarvan :)
Gewijzigd op 01/01/1970 01:00:00 door Jan Willem van der Veer
Als je de TS en daarna ook nog de post leest zie je dat het een oefenprojectje is met maar 1 tabel. Externe relaties hebben daar niets mee te maken. Daarnaast móet je voor externe relaties altijd een relatie tussen 2 indexen maken. (waarbij normaliter 1 van de 2 de primary key in een tabel is)
Dus die relatie zelf zal het probleem niet geven normaal gesproken, maar juist het toevoegen van de juiste indexen zal een enorme performance boost geven.
Het is in elk geval niet een bestaande opbouw, maar meer eens testen hoe en of de database daarmee om kan gaan. Dus nu eerst maar eens kijken voor steen hoe die de indexering moet regelen en van daaruit dit gaan omzetten naar een bestaand systeem.
@steen
Als je snapt wat je wanneer moet indexeren (en op zich heb je daar niet persé een explain voor nodig) kan je dit vrij snel toepassen. Zelf pas ik het meestal wel toe, zelfs wanneer het een systeem betreft welke vrij klein zal blijven.
De TS geeft aan dat hij de test uit wil voeren voor één bepaalde tabel. Hij geeft voor zover ik kan zien niet aan dat dit de enige tabel is in zijn project. Als het gaat om de kolommen binnen een tabel, is mijn ervaring dat de Foreign Keys één van de meest gebruikte zijn in de WHERE- of ON-clausules. Daarnaast zie ik een PK niet echt als een index, gezien het daar gaat om een rij met allen unieke waarden. Indexeren van een rij met strict unieke waarden levert over het algemeen geen beter performance op.
Quote:
Als je snapt wat je wanneer moet indexeren (en op zich heb je daar niet persé een explain voor nodig) kan je dit vrij snel toepassen.
Als er één tool handig is om indices aan te maken, dan is het wel een EXPLAIN. Een explain geeft je de informatie over hoe de SQL-server een bepaalde query interpreteert en welke kolommen het handigst zijn om een index op te zetten. Daarnaast geeft het na afloop van je index-actie ook nog eens een feedback door het aangeven van welke indices hij bij welke queries kan gebruiken. M.a.w.: als je niet met een EXPLAIN gecontroleerd hebt, kun je weinig zinnige uitspraken doen over de zinnigheid van je indices bij een bepaalde query.
Quote:
Die varchars staan voor bijvoorbeeld postcodes en namen of iets dergelijks
Kun je wat specifieker zijn? Bijvoorbeeld door gewoon de CREATE-statement van de tabel te laten zien?
Je zou er ook over kunnen denken om bijv. postcode-velden van het type CHAR(6) te maken. CHAR(6) heeft namelijk relatief een betere performance dan VARCHAR(n).
Jan Willem van der Veer schreef op 21.09.2009 23:31:
@Robert:
De TS geeft aan dat hij de test uit wil voeren voor één bepaalde tabel. Hij geeft voor zover ik kan zien niet aan dat dit de enige tabel is in zijn project. Als het gaat om de kolommen binnen een tabel, is mijn ervaring dat de Foreign Keys één van de meest gebruikte zijn in de WHERE- of ON-clausules.
De TS geeft aan dat hij de test uit wil voeren voor één bepaalde tabel. Hij geeft voor zover ik kan zien niet aan dat dit de enige tabel is in zijn project. Als het gaat om de kolommen binnen een tabel, is mijn ervaring dat de Foreign Keys één van de meest gebruikte zijn in de WHERE- of ON-clausules.
Lees onderstaande quote, ik vraag me werkelijk af hoe jij erbij komt met ON clausules. Hij zegt hier toch heel duidelijk dat het gaat om 1 tabel en dat hij alleen een WHERE heeft bij de voorwaarden.
Lees wel het hele topic voordat je een reactie plaatst, dat voorkomt dit soort onduidelijkheden.
steen schreef op 21.09.2009 20:23:
tabel:
id (int) 7 (ai) primary key
userid (int) 7
varchar1 (varchar) 100
varchar2 (varchar) 100
varchar3 (varchar) 50
varchar4 (varchar) 50
varchar5 (varchar) 10
varchar6 (varchar) 10
Ongeveer zo ziet die tabel er uit, ik weet niet of dit in de praktijk voor gaat komen, maar het was een testje. Een query kan soms meer dan 10 seconden duren, doe ik dan iets verkeerd? Gewoon met een where criterium.
id (int) 7 (ai) primary key
userid (int) 7
varchar1 (varchar) 100
varchar2 (varchar) 100
varchar3 (varchar) 50
varchar4 (varchar) 50
varchar5 (varchar) 10
varchar6 (varchar) 10
Ongeveer zo ziet die tabel er uit, ik weet niet of dit in de praktijk voor gaat komen, maar het was een testje. Een query kan soms meer dan 10 seconden duren, doe ik dan iets verkeerd? Gewoon met een where criterium.
Jan Willem van der Veer schreef op 21.09.2009 23:31:
Als er één tool handig is om indices aan te maken, dan is het wel een EXPLAIN. Een explain geeft je de informatie over hoe de SQL-server een bepaalde query interpreteert en welke kolommen het handigst zijn om een index op te zetten. Daarnaast geeft het na afloop van je index-actie ook nog eens een feedback door het aangeven van welke indices hij bij welke queries kan gebruiken. M.a.w.: als je niet met een EXPLAIN gecontroleerd hebt, kun je weinig zinnige uitspraken doen over de zinnigheid van je indices bij een bepaalde query.
Ik zeg ook niet dat je EXPLAIN niet moet gebruiken, maar als je er vaker mee werkt en ook weet hoe query's werken kan je heel snel ook zelf bepalen welke indexes een performance winst gaan opleveren.
EXPLAIN is een goed hulpmiddel, echter je eigen inzicht en kennis van een systeem kunnen heel veel doen en één en anderen bij voorhand al problemen voorkomen.
Wat je namelijk vaak ziet (en daarom begin ik ook over eigen inzicht/ kennis) is dat een tabel wordt gemaakt, data wordt geplaatst (hier in deze test ook) en dat dan de indexes e.d. aan worden gemaakt. Als je dit soort dingen van tevoren bepaald, voorkom je problemen en lange wachttijden bij query's of het toevoegen van indexes. Door bijvoorbeeld al bij het bouwen te bepalen welke gegevens je nodig gaat hebben uit tabellen en welke koppelingen/ voorwaarden daarbij nodig kúnnen zijn kan je in elk geval al een aantal belangrijke indexes aanmaken, zodat je die wachttijden voor bent. Sterker nog, ik heb het laatst zelf nog gehad, dat ik een index was vergeten, query voor opzoeken duurde in 10-tallen miljoenen records bijna een halve minuut, de index toevoegen had het systeem (de server) behoorlijk moeite mee. Daarom kan je dit beter voor zijn.
Dit is overigens vooral handig wanneer er koppelingen tussen verschillende tabellen zijn (en al helemaal wanneer je geen InnoDB tot je beschikking hebt met foreign keys en dergelijke).
Jan Willem van der Veer schreef op 21.09.2009 23:31:
Kun je wat specifieker zijn? Bijvoorbeeld door gewoon de CREATE-statement van de tabel te laten zien?
Je zou er ook over kunnen denken om bijv. postcode-velden van het type CHAR(6) te maken. CHAR(6) heeft namelijk relatief een betere performance dan VARCHAR(n).
Quote:
Die varchars staan voor bijvoorbeeld postcodes en namen of iets dergelijks
Kun je wat specifieker zijn? Bijvoorbeeld door gewoon de CREATE-statement van de tabel te laten zien?
Je zou er ook over kunnen denken om bijv. postcode-velden van het type CHAR(6) te maken. CHAR(6) heeft namelijk relatief een betere performance dan VARCHAR(n).
Ondanks dat CHAR misschien wel sneller is (ik heb dat niet kunnen terugvinden), heeft het eigenlijk geen voordelen ten opzichte van Varchar. Zeker bij invoegen van DATA zal CHAR bij een te grote input geen melding geven, bij VARCHAR kan je een warning, dan wel error laten genereren wanneer de lengte van de input groter is dan die eigenlijk in het veld mag.
Daarnaast vult CHAR (in de database, bij uitlezen wordt dat ongedaan gemaakt) de "lege ruimte" (het aantal tekens dat overblijft als zijnde vrijde ruimte bij invoegen) op met spaties, zodat altijd de volledige ruimte wordt gebruikt voor de data die in die kolom staat.
Ik zou gewoon Varchar blijven gebruiken, je hebt daarmee zelf meer controle dan met CHAR.
En lees ook dit even Jan-Willem:
user comments op mysql site:
Posted by Julian Morrison on July 14 2006 2:30pm [Delete]
Edit:
Note that using CHAR will only speed up your access if the whole record is fixed size. That is, if you use any variable size object, you might as well make all of them variable size. You gain no speed by using a CHAR in a table that also contains a VARCHAR.
Posted by Kirby Wirby on April 9 2007 8:33pm [Delete]
Note that using CHAR will only speed up your access if the whole record is fixed size. That is, if you use any variable size object, you might as well make all of them variable size. You gain no speed by using a CHAR in a table that also contains a VARCHAR.
Posted by Kirby Wirby on April 9 2007 8:33pm [Delete]
Edit:
Keep in mind that defining a column as VARCHAR will only save space if the data in a particular column is variable in length. I've worked on plenty of data sets where data in a given column is fixed in size -- e.g., code values or indicator/flag fields. In these cases, it's more space-efficient to use CHAR. Consider the case of an indicator field where the value is either 'Y' or 'N'. If defined as a CHAR, the field requires only one byte. However, if defined as a VARCHAR, the field requires two bytes. I worked on a multi-terabyte project at Bank of America where the DBAs actually went to the trouble to rebuild some tables that contained numerous flag or indicator fields because the fields were originally defined as VARCHAR(1) instead of CHAR(1).
[/quote]
Keep in mind that defining a column as VARCHAR will only save space if the data in a particular column is variable in length. I've worked on plenty of data sets where data in a given column is fixed in size -- e.g., code values or indicator/flag fields. In these cases, it's more space-efficient to use CHAR. Consider the case of an indicator field where the value is either 'Y' or 'N'. If defined as a CHAR, the field requires only one byte. However, if defined as a VARCHAR, the field requires two bytes. I worked on a multi-terabyte project at Bank of America where the DBAs actually went to the trouble to rebuild some tables that contained numerous flag or indicator fields because the fields were originally defined as VARCHAR(1) instead of CHAR(1).
[/quote]
Gewijzigd op 01/01/1970 01:00:00 door Robert Deiman
Quote:
Hij zegt hier toch heel duidelijk dat het gaat om 1 tabel en dat hij alleen een WHERE heeft bij de voorwaarden.
Lees wel het hele topic voordat je een reactie plaatst, dat voorkomt dit soort onduidelijkheden.
Lees wel het hele topic voordat je een reactie plaatst, dat voorkomt dit soort onduidelijkheden.
Yep, dat heb ik gezien: de TS geeft aan dat hij de test alleen uitvoert met één tabel. Maar dat wil niet zeggen dat er meerdere tabellen zijn. En om nou zo kortzichtig te zijn dat je alleen uitgaat van deze ene situatie waarbij hij nu per ongeluk alleen maar een WHERE-clausule heeft, vind ik persoonlijk een beetje korte termijn politiek. M.a.w.: het geheel gedraagt zich anders dan de som der delen. Dus je kunt niet stellen dat je handig bezig bent wanneer je enkel op één tabel focused en de rest laat voor wat het is.
En mede gezien hij de column `userid` heeft staan, vermoed ik het bestaan van de tabel met de naam `users`...
Quote:
maar als je er vaker mee werkt en ook weet hoe query's werken kan je heel snel ook zelf bepalen welke indexes een performance winst gaan opleveren.
Sorry, maar ik werk al zo'n kleine zeven jaar met MySQL en af en toe zit ik mij nog steeds achter mijn oren te krabben als ik zie wat een EXTENDED EXPLAIN oplevert (bijv. welke indices die opeens gaat gebruiken, voor mij niet altijd even logisch).
Quote:
Als je dit soort dingen van tevoren bepaald, voorkom je problemen en lange wachttijden bij query's of het toevoegen van indexes.
Uiteraard helemaal mee eens, maar achteraf moet je wel je effectiviteit van je indices kunnen testen. Het toevoegen van teveel indices heeft namelijk juist een slechtere performance. En het testen van het gebruik van specifieke indices kun je echt alleen maar doen met EXPLAIN [EXTENDED]
Quote:
Zeker bij invoegen van DATA zal CHAR bij een te grote input geen melding geven, bij VARCHAR kan je een warning, dan wel error laten genereren wanneer de lengte van de input groter is dan die eigenlijk in het veld mag.
Dat zal ik maar even ontkrachten voor je.
Ik heb deze tabel:
DROP TABLE IF EXISTS test;
CREATE TABLE test (
dag char(4) NOT NULL,
test varchar(4) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Deze query:
INSERT INTO test VALUES ('Te Lang uiteraard', 'Te Lang uiteraard');
En krijg deze foutmeldingen:
Data truncated for column 'dag' at row 1
Data truncated for column 'test' at row 1
Beiden errorNr 1265.
Vertel mij, wat is het verschil?
Quote:
Daarnaast vult CHAR (in de database, bij uitlezen wordt dat ongedaan gemaakt) de "lege ruimte" (het aantal tekens dat overblijft als zijnde vrijde ruimte bij invoegen) op met spaties, zodat altijd de volledige ruimte wordt gebruikt voor de data die in die kolom staat.
Klopt, en dat is ook de volledige snelheidswinst. MySQL hoeft niet eerst te gaan achterhalen wat de werkelijke lengte van het veld is. Daarnaast neem ik aan dat je toch altijd een postcode zult moeten invoeren. Het heeft over het algemeen ook alleen maar zin wanneer het veld verplicht is en deze eigenlijk altijd gevuld is. Als deze niet altijd helemaal gevuld is of redelijk vaak de waarde NULL bevat, is de snelheid met VARCHAR inderdaard groter. Daarom gebruikte ik ook het voorbeeld van postcode.
Zie wat performance betreft ook nog even (tips daar gelden alleen voor InnoDB):
http://dev.mysql.com/doc/refman/5.0/en/innodb-tuning.html
Jan Willem van der Veer schreef op 22.09.2009 10:41:
Yep, dat heb ik gezien: de TS geeft aan dat hij de test alleen uitvoert met één tabel. Maar dat wil niet zeggen dat er meerdere tabellen zijn. En om nou zo kortzichtig te zijn dat je alleen uitgaat van deze ene situatie waarbij hij nu per ongeluk alleen maar een WHERE-clausule heeft, vind ik persoonlijk een beetje korte termijn politiek. M.a.w.: het geheel gedraagt zich anders dan de som der delen. Dus je kunt niet stellen dat je handig bezig bent wanneer je enkel op één tabel focused en de rest laat voor wat het is.
En mede gezien hij de column `userid` heeft staan, vermoed ik het bestaan van de tabel met de naam `users`...
En mede gezien hij de column `userid` heeft staan, vermoed ik het bestaan van de tabel met de naam `users`...
Hij vraagt nu waarom zijn 1e tabel zo traag is en wat hij er aan kan doen. Voor de Explain query zal die ook die betreffende query gaan gebruiken. Het heeft niets met kortzichtigheid te maken, het gaat nu om deze tabel omdat die daarmee wil oefenen. Extra tabellen bijmaken heeft nu dus helemaal geen nut.
Het is geen korte termijn politiek en het gaat ook niet om een bestaand systeem, maar om een voorbeeld. Dus gaan we dit voorbeeld aanpakken en niet een "heel systeem eromheen bedenken, want stel nou toch eens dat.... " omdat je graag lange termijn wil denken.
Het gaat hier om die 1e tabel, hij geeft ook aan dat die zelf die tabel zo heeft gemaakt, uit zijn fantasie en dat hij niet weet of die ooit zo zou kunnen / gaan bestaan. Dan lijkt het mij voldoende om die casus te behandelen en niet om een nieuwe casus aan de ts voor te leggen.
Quote:
Sorry, maar ik werk al zo'n kleine zeven jaar met MySQL en af en toe zit ik mij nog steeds achter mijn oren te krabben als ik zie wat een EXTENDED EXPLAIN oplevert (bijv. welke indices die opeens gaat gebruiken, voor mij niet altijd even logisch).
Nogmaals, als je ook een beetje leest en niet alleen maar de dingen eruit pikt die jou wel mooi uitkomen: Ik geef ook aan dat je vantevoren al wel e.e.a. kan bedenken, aan indexes. Vooral als je een beetje duidelijk hebt wat voor systeem het wordt en welke gegevens je (al dan niet uit meerdere tabellen) nodig hebt, kan je al een groot deel van de indexes aanmaken. Ik zeg helemaal niet dat je géén explain moet gebruiken, maar het loont om daar wel meteen over na te denken.
Quote:
Uiteraard helemaal mee eens, maar achteraf moet je wel je effectiviteit van je indices kunnen testen. Het toevoegen van teveel indices heeft namelijk juist een slechtere performance. En het testen van het gebruik van specifieke indices kun je echt alleen maar doen met EXPLAIN [EXTENDED]
Repeat: Ik geef ook niet aan dat hij geen explain moet gebruiken, maar dat er van tevoren nagedacht moet worden over de structuur, dat voorkomt frustratie. Niemand zegt hier en zal hier zeggen dat je géén explain moet gebruiken, het is een hulpmiddel, niet een doel.
Quote:
Dat zal ik maar even ontkrachten voor je.
Ik heb deze tabel:
DROP TABLE IF EXISTS test;
CREATE TABLE test (
dag char(4) NOT NULL,
test varchar(4) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Deze query:
INSERT INTO test VALUES ('Te Lang uiteraard', 'Te Lang uiteraard');
En krijg deze foutmeldingen:
Data truncated for column 'dag' at row 1
Data truncated for column 'test' at row 1
Beiden errorNr 1265.
Vertel mij, wat is het verschil?
Ik heb deze tabel:
DROP TABLE IF EXISTS test;
CREATE TABLE test (
dag char(4) NOT NULL,
test varchar(4) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Deze query:
INSERT INTO test VALUES ('Te Lang uiteraard', 'Te Lang uiteraard');
En krijg deze foutmeldingen:
Data truncated for column 'dag' at row 1
Data truncated for column 'test' at row 1
Beiden errorNr 1265.
Vertel mij, wat is het verschil?
Dan klopt de uitleg op de MySQL site niet, kan ik ook niet helpen. De tekst die daar staat is een quote van die site.
Quote:
Klopt, en dat is ook de volledige snelheidswinst. MySQL hoeft niet eerst te gaan achterhalen wat de werkelijke lengte van het veld is. Daarnaast neem ik aan dat je toch altijd een postcode zult moeten invoeren. Het heeft over het algemeen ook alleen maar zin wanneer het veld verplicht is en deze eigenlijk altijd gevuld is. Als deze niet altijd helemaal gevuld is of redelijk vaak de waarde NULL bevat, is de snelheid met VARCHAR inderdaard groter. Daarom gebruikte ik ook het voorbeeld van postcode.
Zie wat performance betreft ook nog even (tips daar gelden alleen voor InnoDB):
http://dev.mysql.com/doc/refman/5.0/en/innodb-tuning.html
Zie wat performance betreft ook nog even (tips daar gelden alleen voor InnoDB):
http://dev.mysql.com/doc/refman/5.0/en/innodb-tuning.html
Voor wat betreft je snelheidswinst geld die alleen (wederom uit die quote van de mysql site) wanneer je de hele tijd char gebruikt, door elkaar gebruiken levert uiteindelijk verlies op.
Overigens is deze discussie nogal onzinnig in zijn geheel genomen. Ik heb staan wat ik wilde zeggen en denk dat het voor de TS ook wel duidelijk is hoe het zit zo.
Het gaat er tenslotte om de TS te helpen, niet om gelijk te krijgen. En als je ergens op wil reageren doe dat dan op het geheel, niet op losse zinnetjes die in verband staan met elkaar.
Gewijzigd op 01/01/1970 01:00:00 door Robert Deiman