database sortering
Code (php)
1
2
3
2
3
id type
2 webshop
1 website
2 webshop
1 website
Is dat niet vreemd? Ik had verwacht dat de sortering op basis van de primary key (ID) zou plaatsvinden, en dat dus "website" bovenaan zou staan.
Wat ik ook vreemd vind, is dat wanneer ik de tabel exporteer (naar een bestandje) eerst "webshop" wordt ingevoegd en dan pas "website", terwijl ik ze zelf precies andersom heb ingevoerd.
Code (php)
1
2
3
2
3
INSERT INTO `foo` (`id`, `type`) VALUES
(2, 'webshop'),
(1, 'website');
(2, 'webshop'),
(1, 'website');
Kan iemand mij uitleggen hoe dit komt?
Mijn vermoeden is dat dit te maken heeft met het feit dat InnoDB gebruik maakt van een clustered index voor je primary key. De records worden dan opgeslagen in de volgorde waarin ze in de index staan.
Maar ook bij databases geldt: ga er nooit vanuit dat je records in een bepaalde volgorde staan, tenzij je ze expliciet sorteert. ;-)
Gewijzigd op 19/05/2013 19:30:05 door Willem vp
Code (php)
1
2
3
4
2
3
4
id type
3 aardappel
2 webshop
1 website
3 aardappel
2 webshop
1 website
Waarom zou ie dat doen? Het lijkt me toch veel logischer om op de primary key te sorteren. Ik vind het vreemd.
Toevoeging op 19/05/2013 20:46:05:
Ik merk nu dat het pas gebeurt als ik "type" UNIQUE maak in PHPMyAdmin. Als ik die index eraf haal dan klopt het weer wel. Blijkbaar sorteert ie dus op en UNIQUE veld en niet op de primary key :-s
Gewijzigd op 19/05/2013 20:49:15 door Ozzie PHP
Maar dat maakt voor jouw website toch niet uit? Je geeft altijd een ORDER BY aan (als goed is).
Om je probleem op te lossen: net boven de kolommen kan je kiezen hoe en waarop PHPmyAdmin moet sorteren.
Als je wil dat resultaten gesorteerd weergegeven worden dan moet je een ORDER BY clausule meegeven. Punt.
Als ik in een tabel als 1e kolom een primary key auto increment id instel, dan zou je toch verwachten dat daarop wordt gesorteerd? Maar dat is niet wat er gebeurt. Blijkbaar omdat ik kolom 2 uniek maak, wordt ineens daarop gesorteerd. Da's toch raar??? Ik krijg dus ik plaats van dit:
Code (php)
1
2
3
4
2
3
4
id foo
1 zebra
2 aap
3 koalabeer
1 zebra
2 aap
3 koalabeer
Dit...
Code (php)
1
2
3
4
2
3
4
id foo
2 aap
3 koalabeer
1 zebra
2 aap
3 koalabeer
1 zebra
Kan aan mij liggen, maar ik vind dit vreemd.
Als MySQL steeds zelf bezig zou zijn met het sorteren zou je database alleen maar trager worden.
TJVB tvb op 19/05/2013 23:35:04:
Hij sorteert standaard niet
Blijkbaar sorteert ie standaard wel, want hij zet de kolom met het unique veld op alfabetische volgorde. Alleen hij sorteert niet zoals je zou verwachten.
Overigens heb ik het niet over sortering in de programmacode, maar heb ik het nog steeds erover hoe je de tabel in PHPMyAdmin te zien krijgt. Ik had verwacht dat je dan standaard de rijen op ID (PK AI) gesorteerd zou zien.
Laat ik proberen te vertellen hoe het ongeveer werkt. Dit is een versimpeling van de werkelijkheid, maar de materie is al complex genoeg.
Waarom worden de records gesorteerd op de kolom waarop je de unique index hebt staan? Nou, eigenlijk worden ze dat niet. Ze worden getoond in de volgorde waarin ze in de primary index staan.
InnoDB maakt voor de primary key gebruik van een clustered index. Daarbij wordt de data van een record opgeslagen vlak bij de index-entry. Als je dan een record selecteert op basis van de primary key, heeft MySQL vrijwel meteen de data, want die staat in dezelfde data page.
Volgende punt: indices in een InnoDB-tabel zijn altijd B-tree indices. Een eigenschap daarvan is dat die gesorteerd zijn, zodat de database weet welke tak van de boom hij af moet wandelen om bij de gezochte entry te komen. Op het moment dat je een primary key definieert, wordt je data dus eigenlijk al gesorteerd opgeslagen.
Maar vervolgens maak je een unique index aan op een andere kolom. Als je dan een record insert, moet eerst in de unique index gekeken worden of de key nog niet bestaat. Zodra dat bekend is, wordt op die plaats het record toegevoegd aan de database. Dat is dus niet noodzakelijkerwijs op de volgorde van de primary key.
Maar nu wordt het nog iets ingewikkelder. Een B-tree is opgebouwd uit pages van 16 kB. Die drie records van jou passen zonder problemen in zo'n page. Als een page vol begint te raken (ergens tussen 8 en 15 kB, afhankelijk van hoe sequentieel de records worden toegevoegd) maakt de engine een extra page aan. Vanaf dat moment worden je records op verschillende pages opgeslagen en zit er minder samenhang in de fysieke volgorde. En om het nog leuker te maken: als je meerdere indices hebt, zal de db engine op basis van de keysize of de (al dan niet) volgordelijkheid van de data in de voor die indices gebruikte kolommen voor elke index een ander moment kiezen om te pagineren. Gegevens die bij de ene index op dezelfde page staan, kunnen bij een andere index over meerdere pages zijn verdeeld. Daar komt nog eens bij dat wanneer de fill factor van een page onder de 8 kB komt (bijvoorbeeld doordat je records hebt gewist) de engine gaat proberen de page te verwijderen door de gegevens in andere pages op te nemen.
En dan komen we nu op het punt waar je steeds op door blijft hameren: de gepercipieerde sortering van de records in een select-statement.
Op het moment dat je een select doet zonder where-condities, zal MySQL geen gebruik maken van de index om je data op te halen. Immers, alle records moeten worden opgehaald en het gebruik van een index daarbij werkt alleen maar vertragend. De engine zal dus aan het begin beginnen en een voor een alle records oplepelen. Bij een zo goed als lege database lijkt het alsof die data gesorteerd is, omdat die toevallig zo op de disk is opgeslagen. Wanneer er echter meer activiteit heeft plaatsgevonden, zul je zien dat er weinig samenhang meer is in de records. Waarschijnlijk zal de database chunks van records geven die onderling min of meer gesorteerd zijn, omdat die records toevallig op dezelfde page staan.
Gewijzigd op 20/05/2013 01:27:28 door Willem vp
Willem vp op 20/05/2013 01:24:56:
InnoDB maakt voor de primary key gebruik van een clustered index. Daarbij wordt de data van een record opgeslagen vlak bij de index-entry.
Goed, hier was ik je dus al kwijt :-/
Hahaha... maar ik zal me er maar gewoon bij neerleggen. Maar begrijp ik goed dat dit fenomeen zich voordoet omdat het een InnoDB tabel is met een unieke key?
Stel het was een MyISAM tabel, was ie dan wel op primary key gesorteerd?
Ozzie PHP op 20/05/2013 01:31:18:
Goed, hier was ik je dus al kwijt :-/
Euw... Samenvatting in jip-en-janneketaal: als je weinig records in je database hebt, worden je records als je geluk hebt gesorteerd opgeslagen, maar misschien ook niet. Als je veel records hebt (meer dan enkele tientallen) is elke vorm van sortering geheel per ongeluk.
Quote:
Stel het was een MyISAM tabel, was ie dan wel op primary key gesorteerd?
MyISAM maakt, net als InnoDB, gebruik van B-tree indices, dus daar zit het verschil niet in. Wat wel zal uitmaken, is dat de primary key index non-clustered is. Dat is inherent aan het feit dat de data en de indices op disk-niveau in aparte bestanden worden opgeslagen (.myd en .myi, waar InnoDB alleen .ibd gebruikt).
Omdat de fysieke locatie van de record-data dus niet per se overeenkomt met de locatie van de index voor dat record, kun je het hele pagineringsverhaal overslaan. Dat komt mogelijk wel weer terug op het moment dat je where-condities in je queries gaat gebruiken.
In het .myd-bestand staat dus alleen de record-data. Lekker op volgorde van toevoegen. Een select zonder where zal dus netjes alles op volgorde teruggeven.
Nou ja, dat valt ook wel tegen eigenlijk. ;-) Als je namelijk records verwijdert, wordt de vrijgekomen data gebruikt voor nieuwe inserts. En zelfs als je nooit iets weggooit is dat geen garantie dat nieuwe records alleen maar achteraan worden geplakt. Als je namelijk row-format 'dynamic' of 'compact' gebruikt en je wijzigt een bestaand record op zo'n manier dat het record kleiner wordt, kan ook die vrijgekomen ruimte weer gebruikt worden voor nieuwe records (ja, een MyISAM-tabel heeft dus last van fragmentatie en moet dus eigenlijk ook regelmatig gedefragmenteerd worden).
Concluderend: (ik heb het al gezegd, maar ik zeg het gewoon nog eens en zal het altijd blijven zeggen) vertrouw nooit op de volgorde van de records die je uit een database haalt, tenzij je ze expliciet sorteert.
Gewijzigd op 20/05/2013 02:38:08 door Willem vp
Ozzie PHP op 20/05/2013 02:38:59:
Hehe, oké thanks. Ik zal het onthouden. Vind het altijd weer bijzonder hoe iemand ineens zoveel over een bepaald onderwerp weet, terwijl je zou denken dat dat voor de 'normale' werking niet echt nodig is. Wel grappig :)
Ach, wat is "normaal"? Ik beheer in het dagelijks leven een serverpark van 18 MySQL/MariaDB-servers waar op jaarbasis zo'n 4 miljard records worden ge-insert (soms in bursts van 1500 records per seconde), en dan is het wel handig als je er net iets meer vanaf weet dan de gemiddelde Nederlander. ;-)
Daar komt bij dat ik tijdens mijn studie behoorlijk wat database-theorie tot me heb moeten nemen, en alhoewel dat alweer zo'n 20 jaar geleden is, is de basis eigenlijk nog steeds hetzelfde.
Gewijzigd op 20/05/2013 03:00:00 door Willem vp
Nu begrijp ik hoe het komt dat je er zoveel vanaf weet :)
MariaDB... de 1e keer dat ik daar weer van hoor :)
Maar leuk hoor, zo leer je nog eens wat van elkaar!
In principe is MariaDB (nog) volledig uitwisselbaar met MySQL en wijzigingen in MySQL worden ook verwerkt in MariaDB. Zelfs de executables hebben dezelfde naam; je moet echt de versiegegevens opvragen om erachter te komen waarmee je werkt. Onder water zitten echter wat verbeteringen qua performance en stabiliteit. Zo heeft MariaDB bijvoorbeeld een andere InnoDB-engine die een stuk beter is dan die van MySQL.
Met ingang van MySQL 5.6 zullen er wat meer verschillen komen, omdat enkele features van MySQL 5.6 niet voldoen aan de kwaliteitseisen van MariaDB, zodat ze die die code niet zullen mergen maar zelf implementeren.