Wat zijn de voor- en nadelen van MySQL, en wanneer is het een goede keuze?
MySQL is een relationeel databasebeheersysteem dat kan worden gebruikt voor gangbare webservices en online transaction processing (OLTP), vooral met de InnoDB-opslagengine en functies zoals transacties, locking op rijniveau en consistente reads. Replicatie voor leesverkeer is daarentegen standaard asynchroon, en configuraties voor hoge beschikbaarheid, complexe query's en partitionering kennen ontwerp- en operationele beperkingen. De voordelen zijn alleen echte voordelen wanneer ze passen bij de workload en de operationele capaciteiten van het team. dev.mysql.com dev.mysql.com
Bij het beoordelen van MySQL is het nauwkeuriger om te kijken naar hoe gegevens worden gelezen en geschreven, wat bij storingen moet worden gegarandeerd en hoe complex het schema wordt, dan om alleen te vragen of het een "snelle database" is. De onderstaande bespreking volgt het bereik van de officiële MySQL 8.4-documentatie. Het feitelijke gedrag kan verschillen per versie, opslagengine, configuratie en replicatietopologie. dev.mysql.com
Wat voor soort database is MySQL?
Een relationele database slaat gegevens op in tabellen die uit rijen en kolommen bestaan en gebruikt de querytaal SQL om relaties tussen tabellen te beheren. Een webshop kan bijvoorbeeld tabellen zoals customers, orders en order_items gebruiken om relaties tussen klanten, bestellingen en bestelde producten te beheren. In veel situaties moeten verschillende wijzigingen in één bewerking worden gegroepeerd, zoals het aanmaken van een bestelling, het verlagen van de voorraad en het vastleggen van de betaalstatus.
Opslagengines zijn belangrijk in MySQL. Een opslagengine is een component die verantwoordelijk is voor de manier waarop tabellen worden opgeslagen, vergrendeld en hersteld. InnoDB is met name de standaardopslagengine van MySQL en biedt ACID-transacties, commits en rollbacks, herstel na crashes, locking op rijniveau, multiversion concurrency control (MVCC) en referentiële sleutels. De transactiebaarheid die gewoonlijk met MySQL wordt geassocieerd, verwijst daarom vaak naar MySQL met correct geconfigureerde InnoDB-tabellen. dev.mysql.com
ACID is een verzamelnaam voor de verwachte eigenschappen van transacties. Atomiciteit betekent dat een volledige bewerking óf slaagt óf wordt geannuleerd. Consistentie betekent dat gedefinieerde gegevensregels behouden blijven. Isolatie beheerst de effecten die gelijktijdig uitgevoerde bewerkingen op elkaar hebben, terwijl duurzaamheid betekent dat gecommitte resultaten storingen moeten overleven. De ACID-eigenschappen van MySQL worden ook beïnvloed door de engine, configuratie, hardware en operationele procedures; uit de naam alleen mag dus niet worden afgeleid dat elk storingsscenario automatisch is opgelost. dev.mysql.com
Waarom zijn InnoDB-transacties en concurrency een voordeel?
OLTP verwijst naar workloads met frequente, relatief korte verzoeken, zoals het ontvangen van bestellingen, het bijwerken van lidmaatschapsgegevens of het wijzigen van een betaalstatus. In deze omgeving kunnen veel gebruikers gelijktijdig hetzelfde soort gegevens wijzigen. Daarom is het belangrijk om gegevenswijzigingen veilig te groeperen en de omvang van conflicten zo klein mogelijk te houden.
Omdat InnoDB transaction commits, rollbacks en herstel na crashes biedt, kan een applicatie bijvoorbeeld zo worden geconfigureerd dat een transactie wordt teruggedraaid wanneer een stap tijdens het aanmaken van een bestelling en het verlagen van de voorraad mislukt. Locking op rijniveau vergrendelt specifieke rijen wanneer dat nodig is en kan gelijktijdig werk beter ondersteunen dan het breed vergrendelen van een hele tabel. Wachttijden en conflicten verdwijnen echter niet wanneer meerdere bewerkingen vaak om dezelfde rijen of aangrenzende gegevensbereiken concurreren. dev.mysql.com
MVCC biedt consistente reads door meerdere versies van gegevens te gebruiken. Dit betekent niet simpelweg dat reads en writes elkaar nooit hinderen. De waargenomen resultaten en het lockinggedrag kunnen verschillen afhankelijk van het isolatieniveau van een transactie, de SQL-statements die worden uitgevoerd en het gebruik van locking reads. Controleer daarom bij het oplossen van concurrencyproblemen niet alleen de naam van de engine. Leg eerst in bedrijfsregels vast welke reads de meest actuele waarde moeten zien en welke updates elkaar moeten uitsluiten.
Een referentiële sleutel is een beperking die helpt waarborgen dat een waarde in de ene tabel verwijst naar een bestaande rij in een andere tabel. Zo kan deze afdwingen dat een klant-ID in een bestelling naar een werkelijke klant verwijst. Dit kan ongeldige verwijzingen helpen verminderen, maar betekent ook dat verwijder- en updateregels en de tabelstructuur vooraf zorgvuldig moeten worden ontworpen. Als u later partitionering wilt invoeren, moet u ook de compatibiliteitsbeperkingen rond referentiële sleutels controleren. dev.mysql.com dev.mysql.com
Wat zijn de voordelen voor ontwikkelomgevingen en toegangsbeheer?
MySQL biedt meerdere clientprotocollen en API's voor C/C++, Java, PHP, Python, Ruby en andere talen. Applicaties die deze talen en tools al gebruiken, hebben daardoor opties om een verbindingslaag te bouwen, en het kan relatief eenvoudig zijn om een basisverbinding tussen een webapplicatie en de database op te zetten. De beschikbaarheid van een API voor een bepaalde taal garandeert echter niet op zichzelf dat connection pooling, herhalingen na fouten, tekensets en tijdzoneafhandeling correct zijn geconfigureerd. De data-accessbenadering van de applicatie moet afzonderlijk worden gevalideerd. dev.mysql.com
Ook het privilegesysteem maakt deel uit van het operationele ontwerp. MySQL biedt privileges op globaal, database- en objectniveau, evenals dynamische privileges. Hiermee kunnen rollen worden gescheiden: geef een applicatieaccount bijvoorbeeld alleen de lees- en schrijfrechten die het voor specifieke tabellen nodig heeft, en gebruik aparte accounts voor back-up- en beheertaken. Het principe van minimale rechten is een nuttig ontwerpprincipe om de impact te beperken als een account wordt gecompromitteerd of een programma een fout bevat. dev.mysql.com
Fijnmazigere privileges maken beveiliging echter niet vanzelf compleet. In de praktijk moet u beheren welke accounts welke privileges hebben, of beheer- en applicatieaccounts gescheiden zijn en welk proces wijzigingen in privileges regelt. Met andere woorden: de privilegefuncties van MySQL bieden beheersmechanismen, maar de verantwoordelijkheid om deze volgens bedrijfsrollen toe te kennen blijft bij operations.
Welke problemen lossen indexen en partitionering op?
Een index is een gegevensstructuur die is ontworpen om de noodzaak te verkleinen om een hele tabel te scannen om gewenste rijen te vinden. Als verzoeken om één bestelling op bestelnummer te vinden bijvoorbeeld veel voorkomen, kan een index op die kolom helpen. Indexen met meerdere kolommen kunnen nuttig zijn voor query's die verschillende kolommen gezamenlijk als voorwaarde gebruiken, maar de kolomvolgorde en de feitelijke querypredicaten zijn van belang. InnoDB ondersteunt maximaal 64 secundaire indexen per tabel en maximaal 16 kolommen per index met meerdere kolommen. dev.mysql.com
Indexen zijn echter niet automatisch beter naarmate er meer worden aangemaakt. Indexen gebruiken opslagruimte en moeten ook worden onderhouden wanneer rijen worden ingevoegd, bijgewerkt of verwijderd. De limiet op het aantal ondersteunde indexen is een technische limiet, geen ontwerpdoel. Of een index die een zoekpad verkort werkelijk nodig is en hoeveel belasting deze toevoegt aan schrijfpaden, moet worden beoordeeld aan de hand van representatieve query's en gegevensdistributie.
Stringindexen hebben ook fysieke beperkingen. De limiet voor het InnoDB-indexsleutelvoorvoegsel is doorgaans 3.072 bytes, maar kan afhankelijk van het rijformaat worden teruggebracht tot 767 bytes. Als u lange strings probeert te indexeren met een tekenset met een grote opslaggrootte per teken, zoals utf8mb4, kan deze limiet van invloed zijn op het schemaontwerp. Het is vooral belangrijk te onderscheiden dat dit een limiet in bytes is en niet in het aantal tekens. dev.mysql.com
Partitionering is een functie die één tabel volgens gedefinieerde regels over meerdere partities opslaat. Als een voorwaarde overeenkomt met de partitioneringsregels, kan partition pruning partities uitsluiten die MySQL niet hoeft te doorzoeken. Voor een grote geschiedenistabel die op datumbereik wordt bevraagd, kunt u bijvoorbeeld, als de tabel op datum is gepartitioneerd, een ontwerp overwegen dat het doelbereik bij zoekopdrachten over een specifieke periode verkleint. dev.mysql.com
Dit betekent niet dat elke grote tabel moet worden gepartitioneerd. Als veelgebruikte voorwaarden niet overeenkomen met de partitiesleutel, vindt de verwachte vermindering van doelgegevens mogelijk niet plaats. Partitionering introduceert ook aanvullende regels voor operations, sleutelontwerp en beperkingen. Daarom is het beter eerst te vergelijken of het probleem kan worden opgelost met eenvoudigere indexen en queryverbeteringen.
Welke beperkingen gelden voor partitionering en full-text search?
In MySQL 8.4 wordt partitionering ondersteund door de opslagengines InnoDB en NDB. Een gepartitioneerde InnoDB-tabel kan geen referentiële sleutels hebben en kan ook niet het doel zijn van verwijzingen met referentiële sleutels vanuit een andere tabel. Daarnaast moet elke kolom die in de partitiesleutel wordt gebruikt, deel uitmaken van elke unieke sleutel, inclusief de primaire sleutel. Deze voorwaarde kan het model sterk veranderen wanneer u een kerntabel met veel verwijzingen, zoals een bestellingentabel, probeert te partitioneren. dev.mysql.com
Full-text search is een functie voor het zoeken naar woorden in tekst. MySQL ondersteunt full-text search met InnoDB en MyISAM, maar dit wordt niet ondersteund voor gepartitioneerde tabellen. Als u dus zowel zoekfunctionaliteit voor lange documenten als partitionering voor grootschalige historische gegevens in dezelfde tabel verwacht, moet u vroegtijdig controleren of die combinatie mogelijk is. Het later toevoegen van een functie kan vereisen dat u tabellen splitst of de zoekarchitectuur wijzigt. dev.mysql.com
Deze beperkingen tonen niet simpelweg aan dat MySQL functies mist, maar dat functies mogelijk niet onafhankelijk combineerbaar zijn. U kunt de behoefte aan referentiële sleutels, unieke sleutels, partitiesleutels en full-text search afzonderlijk controleren. Het is veiliger een schema niet te bepalen op basis van het voordeel van slechts één functie.
Hoe kan replicatie worden gebruikt voor leesschaling en back-ups?
Replicatie is een architectuur die wijzigingen van de ene server naar de andere stuurt. Doorgaans legt een bronserver wijzigingen vast en passen replicaservers deze toe. Door een deel van de leesverzoeken over meerdere replica's te verdelen, kunt u de leesbelasting van de bron verlagen. U kunt ook overwegen om back-up- of analysetaken naar replica's te verplaatsen. dev.mysql.com
GTID is een methode om replicatieposities te verwerken door aan elke transactie een identifier toe te kennen. Replicatie op basis van GTID kan helpen de last te verminderen van het handmatig uitlijnen van namen en posities van binary log-bestanden. Het opzetten van een replicatietopologie staat echter los van het bewaken van replicatievertraging en het uitvoeren van herstelprocedures. U moet bepalen welke server writes verwerkt, welke servers reads kunnen bedienen en wat er bij vertraging gebeurt. dev.mysql.com
Standaardreplicatie is asynchroon. Dit betekent dat er op het moment dat een commit op de bron is voltooid, geen garantie bestaat dat elke replica dezelfde wijziging heeft toegepast. Een query die naar een replica wordt gerouteerd onmiddellijk nadat een gebruiker een adres heeft gewijzigd, kan bijvoorbeeld nog het vorige adres tonen. Dit kan worden gezien als een read-after-write-consistentieprobleem. Verzoeken die de meest actuele gegevens moeten hebben, vereisen een beleid dat ze naar de bron routeert of rekening houdt met de toepassingsstatus van replica's. dev.mysql.com
Semi-synchrone replicatie gebruikt een benadering waarbij de bron bevestiging ontvangt dat een replica een transactiegebeurtenis heeft ontvangen en gelogd. Dit is een alternatief voor standaard asynchrone replicatie, maar betekent niet dat elke vereiste volledig synchroon wordt. Bij sterke synchrone vereisten moet u het vereiste consistentieniveau, het acceptabele latentiegebied en het gedrag bij storingen helder definiëren en vervolgens ook afzonderlijke opties zoals NDB Cluster overwegen. dev.mysql.com
Lost Group Replication hoge beschikbaarheid automatisch op?
Hoge beschikbaarheid is het doel om een systeem zo te configureren dat een service kan doorgaan wanneer een deel van een server of netwerk uitvalt. Group Replication beheert groepslidmaatschap, kiest automatisch een primaire server in single-primary-modus of ondersteunt multi-primary-configuraties. De mogelijkheid om in combinatie met InnoDB Cluster en MySQL Router een topologie voor hoge beschikbaarheid te vormen, is een belangrijke MySQL-optie. dev.mysql.com
Consensus binnen databaseservers en failover van applicatieverbindingen zijn echter niet hetzelfde probleem. Group Replication bevat geen functionaliteit om clients die zijn uitgevallen naar gezonde leden over te schakelen. Applicaties hebben MySQL Router, een load balancer, een connector of aangepaste middleware nodig om te bepalen waar verbinding mee wordt gemaakt, en ook die laag moet worden beheerd met oog voor storingen, retries en statusupdates. dev.mysql.com
Wanneer u dus "automatische failover" hoort, stel dan minstens drie afzonderlijke vragen. Ten eerste: kan een primaire server worden gekozen? Ten tweede: gaan nieuwe applicatieverbindingen naar een gezonde server? Ten derde: welke resultaten zien lopende verzoeken en door gebruikers opnieuw uitgevoerde verzoeken? Het bestaan van een functie voor de eerste vraag garandeert niet automatisch de andere twee.
Ook een multi-primary-configuratie is moeilijk te begrijpen als slechts een schakelaar voor betere schrijfprestaties. Wanneer writes vanaf meerdere locaties zijn toegestaan, moet u ook ontwerpen hoe gelijktijdige wijzigingen aan dezelfde gegevens op bedrijfsniveau worden voorkomen of afgehandeld en aan welke regels het schrijfpad van de applicatie moet voldoen. Hoge beschikbaarheid is een operationeel vraagstuk dat naast functiekeuze ook storingsoefeningen, observability en herstelprocedures omvat.
Waarom kunnen complexe query's de tuninglast verhogen?
De optimizer is een component die uit verschillende manieren om een SQL-statement uit te voeren, het uitvoeringsplan kiest met de geschat laagste kosten. Zo bepaalt deze welke index eerst wordt gebruikt en in welke volgorde tabellen worden gejoint. De op kosten gebaseerde optimizer van MySQL kan op schattingen vertrouwen wanneer statistieken onvoldoende zijn en daardoor een plan kiezen dat afwijkt van wat iemand verwacht. dev.mysql.com
Naarmate het aantal gejointe tabellen toeneemt, kan het aantal kandidaat-uitvoeringsplannen exponentieel groeien. In dat geval kan niet alleen het ophalen van gegevens zelf, maar ook de optimalisatietijd die nodig is om geschikte plannen te onderzoeken, een bottleneck worden. In systemen die vaak complexe analytische query's of veel joins uitvoeren, is het daarom moeilijk de geschiktheid alleen vast te stellen op basis van de vraag of de SQL syntactisch kan worden uitgevoerd. Bij tests moeten werkelijke gegevensdistributies en representatieve voorwaarden worden gebruikt. dev.mysql.com
EXPLAIN is een tool om het voor een query gekozen uitvoeringsplan te controleren. Wanneer resultaten traag zijn, onderzoekt u eerst predicaten, joinvoorwaarden, de gebruikte indexen en geschatte aantallen rijen. Indien nodig kunt u statistieken vernieuwen of indexen en de querystructuur aanpassen. Ook index hints en functies voor optimizerbesturing zijn beschikbaar, maar benaderingen die een specifiek plan afdwingen moeten voortdurend worden geverifieerd om zeker te stellen dat zij na gegevenswijzigingen geldig blijven. dev.mysql.com dev.mysql.com
Dit betekent niet dat complexe analyses niet in MySQL kunnen worden uitgevoerd. Als grootschalige joins over meerdere tabellen en analytische query's echter de kernworkload vormen, is het realistisch vooraf te vergelijken hoeveel tijd u aan tuning kunt besteden, of analyses naar replica's moeten worden verplaatst en of een gespecialiseerd analysesysteem moet worden toegevoegd. Omgekeerd kan deze last relatief kleiner zijn voor een service die hoofdzakelijk uit korte, voorspelbare transacties bestaat.
Wanneer moet u voorzichtig zijn met opgeslagen routines?
Opgeslagen routines zijn procedures of functies die op de databaseserver worden opgeslagen en uitgevoerd. Zij kunnen enkele regels voor gegevensverwerking dicht bij de database houden, maar opgeslagen functies die in SQL-statements kunnen worden gebruikt kennen beperkingen. Een opgeslagen functie kan bijvoorbeeld geen statement gebruiken dat een resultaatset retourneert. Een functie die één retourwaarde berekent en een querybewerking die meerdere rijen retourneert, hebben verschillende doelen en gebruikspatronen. dev.mysql.com dev.mysql.com
Ook de deterministischheid van opgeslagen routines is van belang in replicatieomgevingen. Deterministisch betekent dat bij dezelfde invoer hetzelfde resultaat wordt geproduceerd. Niet-deterministische of tijdafhankelijke routines die variëren op basis van tijd of omgevingsstatus kunnen, afhankelijk van de replicatiemethode, problemen met reproduceerbaarheid veroorzaken. Dat vereist bijzondere zorg bij statement-based replication. Wanneer u bedrijfslogica in de database onderbrengt, moet u ook beoordelen of die logica tijdens replicatie en herstel na crashes hetzelfde resultaat kan opleveren. dev.mysql.com
Of u opgeslagen routines gebruikt, gaat minder over de vraag of een functie bestaat dan over waar de verantwoordelijkheid voor wijzigingen, tests en deployment moet liggen. Wanneer regels worden verdeeld tussen applicatiecode en databaseroutines, kunnen tracering en tests complexer worden. Omgekeerd kunnen ze nuttig zijn voor eenvoudige regels dicht bij gegevensintegriteit. De kernvraag is of het team kan begrijpen en beheren waar die regels worden uitgevoerd en welke gevolgen ze voor replicatie hebben.
Wanneer is MySQL een goede keuze en wanneer moet u voorzichtig zijn?
De volgende tabel is geen productrangschikking. Zij biedt een perspectief om de aansluiting tussen vereisten en functies te controleren.
| Situatie | Wat u met MySQL kunt overwegen | Voorwaarden die u daarnaast moet controleren |
|---|---|---|
| Algemene webservices en verwerking van bestellingen of lidmaatschappen | U kunt InnoDB-transacties, locking op rijniveau, MVCC en referentiële sleutels gebruiken. | Transactiegrenzen en regels voor gelijktijdige updates moeten worden ontworpen. |
| Services met veel leesverkeer | Bron-replicareplicatie kan lees-, back-up- en analysebelasting scheiden. | U hebt beleid nodig voor replicatievertraging en actuele reads. |
| Services die een fouttolerante configuratie nodig hebben | U kunt een topologie overwegen die Group Replication, Router en gerelateerde componenten combineert. | Verbindingsfailover, retries en storingsprocedures moeten afzonderlijk worden beheerd. |
| Grote historiequery's op datumbereik | Partition pruning kan, afhankelijk van de voorwaarde, het aantal doelpartities verminderen. | Controleer eerst beperkingen voor referentiële sleutels, unieke sleutels en full-text search. |
| Analysegerichte workloads die veel tabellen joinen | U kunt SQL-uitvoering en functies voor index- en optimizerbesturing gebruiken. | Beoordeel validatie van uitvoeringsplannen en de kosten van doorlopende tuning. |
De eerste drie rijen van de tabel zijn gebaseerd op de officiële functies van InnoDB, replicatie en Group Replication. De laatste twee rijen weerspiegelen ook het gedrag en de beperkingen van partitionering en de optimizer. dev.mysql.com dev.mysql.com dev.mysql.com dev.mysql.com dev.mysql.com
Vooral wanneer sterke consistentie tussen meerdere regio's of ononderbroken failover een kernvereiste is, moet u geen beslissing nemen op basis van alleen standaard asynchrone replicatie. Specificeer vereisten voor actualiteit, acceptabele latentie, de vraag of writes tijdens storingen mogelijk blijven en het failoverpad van de applicatie, en vergelijk vervolgens Group Replication, NDB Cluster of andere gedistribueerde opties. Als u daarentegen typische lees-schrijftransacties betrouwbaar binnen één servicegebied wilt verwerken en reads naar behoefte over replica's wilt verdelen, kan de combinatie van functies van MySQL een praktisch uitgangspunt zijn. dev.mysql.com dev.mysql.com
Wat moet u vóór adoptie controleren?
Controleer eerst of kerntabellen InnoDB gebruiken en of transactiegrenzen overeenkomen met bedrijfseenheden. Wijzigingen die gezamenlijk moeten slagen of mislukken, zoals het aanmaken van een bestelling, moeten als één transactie worden gedefinieerd; vermijd daarbij onnodig lange transacties die de lockduur verhogen. Maak ten tweede een lijst van de meest voorkomende lees- en schrijfquery's en controleer of vereiste indexen overeenkomen met de werkelijke predicaten en sorteermethode. dev.mysql.com dev.mysql.com
Beslis ten derde, als u replicatie gebruikt, "welke reads vanaf replica's zijn toegestaan". Een mogelijke aanpak is onderscheid maken tussen verzoeken die actualiteit vereisen, zoals het controleren van een status onmiddellijk na betaling, en lijst- en statistiekquery's die enige vertraging kunnen verdragen. Test ten vierde, als een configuratie voor hoge beschikbaarheid vereist is, storingsscenario's niet alleen voor de verkiezing van databaseleden, maar ook voor de vraag waar applicatieverbindingen daadwerkelijk naartoe gaan. dev.mysql.com dev.mysql.com
Ga er ten vijfde van uit dat gegevens zullen groeien en controleer of partitionering werkelijk nodig is en of u beperkingen voor referentiële sleutels en unieke sleutels kunt accepteren. Als u zoeken in lange strings, full-text search en partitionering samen nodig hebt, beoordeel dan eerst de beperkingen tussen deze functies. Onderzoek tot slot, als complexe joins centraal staan, EXPLAIN onder omstandigheden die dicht bij productiegegevens liggen en beoordeel of u de capaciteit hebt om statistieken en indexwijzigingen voortdurend te beheren. dev.mysql.com dev.mysql.com dev.mysql.com
Conclusie: hoe moet u de voor- en nadelen van MySQL beoordelen?
De sterke punten van MySQL omvatten transacties en concurrencybeheer op basis van InnoDB, integratie met een breed scala aan ontwikkelomgevingen, leesdistributie via replicatie en officiële functies voor het bouwen van configuraties voor hoge beschikbaarheid. Deze kunnen een betekenisvolle basis bieden voor algemene webservices en typische OLTP-workloads. dev.mysql.com dev.mysql.com dev.mysql.com
Tegelijk moeten de mogelijke vertraging van standaardreplicatie, aanvullend ontwerp voor failover van verbindingen bij hoge beschikbaarheid, validatie van uitvoeringsplannen voor complexe query's en beperkingen rond indexen, partitionering en full-text search als reële kosten worden beschouwd. Uiteindelijk is MySQL geen keuze met universeel positieve eigenschappen. Het is een database waarvan de geschiktheid kan worden beoordeeld wanneer vereisten voor gegevensconsistentie, de lees-schrijfverhouding, schemabeperkingen, het niveau van storingsrespons en de capaciteit voor tuning en beheer concreet zijn gemaakt. dev.mysql.com dev.mysql.com dev.mysql.com