Als je een VARCHAR veld in je database hebt van bijv. 50 tekens, maakt het dan voor het geheugen of bestandsgrootte van de database iets uit of er wel of niet een waarde in dat veld staat?
Waarom ik dit graag wil weten ... ik wil ergens een hash gebruiken om een rij in een database te kunnen identificeren, maar dat hoeft maar 1x te gebeuren. Stel ik heb 1.000 rijen, dan heb ik dus ook 1.000 hashes. Aangezien ik de hash maar eenmalig nodig heb, zou ik het veld daarna kunnen leegmaken. Wordt daarmee de database dan ook kleiner? Of reserveert die VARCHAR(50) altijd dezelfde ruimte, ongeacht of je er wel of niet iets invult?
De 50 bij een VARCHAR is een restrictie, geen directe reservering. Bij CHAR ligt dit anders. Verder is het maar de vraag of je database kleiner wordt na invullen/verwijderen van de waarde, omdat het nogal veel werk zou zijn om de data opnieuw te schikken wanneer dit niet strict noodzakelijk is.
Dus als ik je goed begrijp neemt een ingevuld VARCHAR veld altijd meer ruimte in dan een leeg VARCHAR veld?
>> Verder is het maar de vraag of je database kleiner wordt na invullen/verwijderen van de waarde, omdat het nogal veel werk zou zijn om de data opnieuw te schikken wanneer dit niet strict noodzakelijk is.
Ik snap niet echt wat je hiermee bedoelt.
Ik heb de hash eenmalig nodig en daarna niet meer. Wat gebeurt er dan als ik het veld leegmaak? Ik zou denken dat de database dan kleiner wordt, maar is dat niet zo?
>> Dus als ik je goed begrijp neemt een ingevuld VARCHAR veld altijd meer ruimte in dan een leeg VARCHAR veld?
Correct. De opslag bestaat uit een lengte gevolgd door de data, dus wanneer er niets ingevuld is staat er alleen een lengte van 0, meer niet.
>> Ik heb de hash eenmalig nodig en daarna niet meer. Wat gebeurt er dan als ik het veld leegmaak? Ik zou denken dat de database dan kleiner wordt, maar is dat niet zo?
Niet per definitie, je moet de inhoud van je database soortgelijk zien aan een bestandssysteem: er kan en zal fragmentatie optreden. Daarnaast krijg je nog te maken met ACID, de data binnen een transactie kan en mag niet van buiten de transactie veranderen. Dit wordt vaak (ook in innodb) opgelost met multiversion concurrency control, ofwel MVCC. Er worden altijd meerdere versies van dezelfde rij opgeslagen, een nieuwe versie per UPDATE. Het opschonen hiervan gebeurt wanneer gegarandeerd kan worden dat geen enkele transactie meer bezig is met een bepaalde versie, en ook hierbij kunnen gaten vallen in je datafile.
Je ziet vaak dat als een varchar veld leeg gemaakt wordt, de bestandsgrootte niet verandert. het lijkt er dan op, of de gebruikte ruimte blijft staan, net zolang tot er een actie is die genoeg actie veroorzaakt in de tabel om de ontstane gaten op te vullen.
Andere benadering zou voor jou mogelijk een losse tabel zijn met een id als FK naar je huidige tabel en een varchar voor je hash.
Na gebruik zou je in dat geval de rij kunnen delete-n.
Correct, maar de verwijdering van een rij op zichzelf maakt de ruimte ook niet direct vrij, tenzij het de laatste rij in je bestand is, en daar kun je geen voorspellingen over doen omdat de ruimte verdeeld wordt in pages. Je zou natuurlijk in dit specifieke geval een TRUNCATE kunnen doen, omdat de hashes maar eenmalig nodig zijn. Dit heeft geen last van genoemde problemen, omdat TRUNCATE een exclusief lock op de te legen table neemt, en gewoon alles wegmikt.
Hmmm ... oké ... een vrij ingewikkeld/technisch verhaal dus.
Het idee van de koppeltabel wat IVO voorstelt vind ik eigenlijk niet slecht. Dan kan ik de hashes ook mooi gescheiden houden van de echte data, en vaak zal het inderdaad gaan om de laatste rij.
>> Het idee van de koppeltabel wat IVO voorstelt vind ik eigenlijk niet slecht. Dan kan ik de hashes ook mooi gescheiden houden van de echte data, en vaak zal het inderdaad gaan om de laatste rij.
De laatste rij in je tabel is meestal niet gelijk aan de laatste rij in je datafile, doordat je te maken krijgt met onvoorspelbare herschikking. Vandaar dat je geen voorspellingen kan doen over wat je datafiles gaan doen.
Dit specifieke geval zou een reden zijn om "te ver" door te normaliseren, immers: je kunt efficienter je ruimte gebruiken wanneer je dit soort data integraal kan weggooien. Ik geloof dat OPTIMIZE je in andere gevallen zou kunnen helpen. Een database als PostgreSQL gebruikt hier VACUUM voor, of om de ruimte gegarandeerd vrij te geven VACUUM FULL. Dit rangschikt de data in de tabellen opnieuw, waardoor gaten verdwijnen. Het kan echter tot performance issues leiden op de korte termijn omdat je vlak na de opschoning te maken krijgt met het reserveren van nieuwe bestandsruimte. Voor MySQL geldt hetzelfde probleem, zie https://www.percona.com/blog/2010/12/09/mysql-optimize-tables-innodb-stop/
Het klinkt me allemaal nog te technisch (no offense, ligt aan mij) en ik weet niet of ik er op dit moment wel of niet iets mee 'moet' doen. Ik ga de hashes in ieder geval in een aparte koppeltabel zetten, en als ze niet meer nodig zijn dan verwijder ik de betreffende rij. Die koppeltabel zal dus altijd heel klein zijn en hooguit uit een paar rijen bestaan die zichzelf dan telkens weer 'opruimen'. Het voordeel is in ieder geval dat ik dan geen overbodige hashes hoef op te slaan, en dat er geen onnodig hash-veld in die andere tabel staat. Mocht het echt ooit traag worden, dan maak ik de database wel opnieuw aan ofzo :-)
Traagheid is een relatief begrip, en je zal over een beperkt aantal rijen (een aantal duizenden) geen significant verschil zien. Echter zal ook rij voor rij uit een koppeltabel verwijderen ook niet tot winst leiden. Hier geldt alles of niets. Of je verwijdert alles (en alleen TRUNCATE zal dit voor je doen) of je verandert niets aan je opstelling en je behaalt hetzelfde effect.
Ik moet hier wel bij zeggen dat ruimte niet meer is dan dat: ruimte. Als het om performance of efficientie gaat zou ik me eerder richten op de grootte van indexes en je caching instellingen. Hier haal je veel meer winst uit.
EDIT: het is inderdaad een technisch verhaal, maar databases zijn ook zeer technisch. Het is niet voor niets dat DBA's goed betaald worden, zij hebben de meeste kennis over wat je database doet (vooral intern) en wat tot performance kan leiden. Ik word onder andere hiervoor al jaren goed betaald.
