varchar vraagje
Een kort vraagje.
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?
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?
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.
Gewijzigd op 25/02/2017 22:46:26 door Ben van Velzen
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.
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.
Dat lijkt me dus een betere oplossing. Thanks!
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/
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.
Gewijzigd op 26/02/2017 02:52:58 door Ben van Velzen
De hash heeft in dat geval namelijk buiten die tabel niet zoveel bestaansrecht en zoals hierboven staat kun je (mogelijk meer) winst pakken op andere plaatsen.
Je zou bijvoorbeeld via een cron alle tabellen bij tijd en wijlen OPTIMIZEn, dat kan, in ieder geval voor snelheid, al wat uitmaken.
Oh, en als je uitsluitend van InnoDB gebruik maakt maakten bepaalde instellingen nogal veel uit. In mijn inmiddels wat gedateerde WAMP-opstelling zag ik dat mijn MySQL-(hulp)bestanden alleen maar groter werden terwijl ik geen data aan het toevoegen was maar zelfs aan het verwijderen / OPTIMIZEn was. Het heeft weinig zin om deze instellingen te wijzigen als de database al actief is maar je zou kunnen proberen om aparte hulpbestanden aan te houden per tabel middels de flag innodb_file_per_table. En daarnaast zou je andere settings kunnen tweaken zoals expire_logs_days (voor een rotatie van de bin-files) en innodb_log_file_size en innodb_buffer_pool_size. De simpelste manier om je database met deze nieuwe instellingen te laten werken lijkt mij het opnieuw opzetten hiervan (dump, drop, (edit: en rond dit punt even de instellingen wijzigen uiteraard :p) import). Het is wat moeilijker om dit on-the-fly te doen.
Gewijzigd op 26/02/2017 16:19:27 door Thomas van den Heuvel
Kan ik in plaats van InnoDB beter voor iets anders kiezen dan?
Geen idee eigenlijk.
> Kan ik in plaats van InnoDB beter voor iets anders kiezen dan?
Nou nee voor zo gauw ik weet niet, want InnoDB gebruik je voor echte relationele databases (foreign keys, transacties etc.).