Aanbevolen procedures voor het oplossen van problemen met uw Azure Database for MySQL-server
VAN TOEPASSING OP:
Azure Database for MySQL - Enkele server Azure Database for MySQL - Flexibele server
Belangrijk
Azure Database for MySQL - Enkele server bevindt zich op het buitengebruikstellingspad. We raden u ten zeerste aan een upgrade uit te voeren naar Azure Database for MySQL - Flexible Server. Zie Wat gebeurt er met Azure Database for MySQL enkele server voor meer informatie over migreren naar Azure Database for MySQL - Flexibele server?
Gebruik de onderstaande secties om uw MySQL-databases soepel te laten werken en gebruik deze informatie als leidraad om ervoor te zorgen dat de schema's optimaal zijn ontworpen en de beste prestaties bieden voor uw toepassingen.
Het aantal indexen controleren
In een drukke databaseomgeving ziet u mogelijk een hoog I/O-gebruik, wat een indicator kan zijn van slechte gegevenstoegangspatronen. Ongebruikte indexen kunnen de prestaties negatief beïnvloeden wanneer ze schijfruimte en cache verbruiken en schrijfbewerkingen vertragen (INSERT/DELETE/UPDATE). Ongebruikte indexen verbruiken onnodig extra opslagruimte en vergroten de back-upgrootte.
Voordat u een index verwijdert, moet u voldoende informatie verzamelen om te controleren of deze niet meer in gebruik is. Zo voorkomt u onbedoeld dat u een index verwijdert die mogelijk essentieel is voor een query die slechts per kwartaal of jaarlijks wordt uitgevoerd. Zorg er ook voor dat u overweegt of een index wordt gebruikt om uniekheid of volgorde af te dwingen.
Notitie
Vergeet niet om indexen periodiek te controleren en eventuele benodigde updates uit te voeren op basis van eventuele wijzigingen in de tabelgegevens.
SELECT object_schema, object_name, index_name FROM performance_schema.table_io_waits_summary_by_index_usage WHERE index_name IS NOT NULL AND count_star = 0 ORDER BY object_schema, object_name;
(of)
use information_schema; select tables.table_name, statistics.index_name, statistics.cardinality, tables.table_rows from tables join statistics on (statistics.table_name = tables.table_name and statistics.table_schema = '<YOUR DATABASE NAME HERE>' and ((tables.table_rows / statistics.cardinality) > 1000));
Het ontwerp van de primaire sleutel controleren
Azure Database for MySQL gebruikt de InnoDB-opslagengine voor alle niet-tijdelijke tabellen. Met InnoDB worden gegevens opgeslagen in een geclusterde index met behulp van een B-Tree-structuur. De tabel is fysiek ingedeeld op basis van primaire-sleutelwaarden, wat betekent dat rijen worden opgeslagen in de volgorde van de primaire sleutel. Elke secundaire sleutelvermelding in een InnoDB-tabel bevat een aanwijzer naar de waarde van de primaire sleutel waarin de gegevens worden opgeslagen. Met andere woorden, een secundaire indexvermelding bevat een kopie van de primaire sleutelwaarde waarnaar de vermelding verwijst. Daarom hebben primaire-sleutelkeuzes een direct effect op de hoeveelheid opslagoverhead in uw tabellen.
Als een sleutel wordt afgeleid van werkelijke gegevens (bijvoorbeeld gebruikersnaam, e-mail, SSN, enzovoort), wordt deze een natuurlijke sleutel genoemd. Als een sleutel kunstmatig is en niet is afgeleid van gegevens (bijvoorbeeld een automatisch verhoogd geheel getal), wordt deze een synthetische sleutel of surrogaatsleutel genoemd.
Het wordt over het algemeen aanbevolen om te voorkomen dat natuurlijke primaire sleutels worden gebruikt. Deze sleutels zijn vaak zeer breed en bevatten lange waarden uit een of meerdere kolommen. Dit kan op zijn beurt leiden tot ernstige opslagoverhead, waarbij de primaire-sleutelwaarde wordt gekopieerd naar elke secundaire sleutelvermelding. Bovendien volgen natuurlijke sleutels meestal niet een vooraf bepaalde volgorde, waardoor de prestaties aanzienlijk worden verminderd en paginafragmentatie wordt veroorzaakt wanneer rijen worden ingevoegd of bijgewerkt. Gebruik monotonisch toenemende surrogaatsleutels in plaats van natuurlijke sleutels om deze problemen te voorkomen. Een kolom met automatische verhogingen (groot)gehele getallen is een goed voorbeeld van een monotonisch toenemende surrogaatsleutel. Als u een bepaalde combinatie van kolommen nodig hebt, moet u deze kolommen als een unieke secundaire sleutel declareren.
Tijdens de eerste fasen van het bouwen van een toepassing kunt u zich misschien niet voorstellen wanneer uw tabel begint te naderen met twee miljard rijen. Als gevolg hiervan kunt u ervoor kiezen om een ondertekend 4 byte-geheel getal te gebruiken voor het gegevenstype van een id-kolom (primaire sleutel). Zorg ervoor dat u alle primaire tabelsleutels controleert en overschakelt naar 8 byte integerkolommen (BIGINT) om het potentieel voor een groot volume of groei mogelijk te maken.
Notitie
Zie Gegevenstypen voor meer informatie over gegevenstypen en hun maximumwaarden in de MySQL-verwijzingshandleiding.
Gebruik voor indexen
In de vorige sectie wordt uitgelegd hoe indexen in MySQL zijn ingedeeld als B-Trees en in een geclusterde index, bevatten de bladknooppunten de gegevenspagina's van de onderliggende tabel. Secundaire indexen hebben dezelfde B-structuur als geclusterde indexen en u kunt deze definiëren in een tabel of weergave met een geclusterde index of een heap. Elke indexrij in de secundaire index bevat de niet-geclusterde sleutelwaarde en een rijzoeker. Deze locator verwijst naar de gegevensrij in de geclusterde index of heap met de sleutelwaarde. Als gevolg hiervan moet elke zoekactie waarbij een secundaire index betrokken is, navigeren vanaf het hoofdknooppunt door de vertakkingsknooppunten naar het juiste leaf-knooppunt om de waarde van de primaire sleutel te nemen. Het systeem voert vervolgens een willekeurige IO uit die wordt gelezen op de primaire-sleutelindex (opnieuw navigeren van het hoofdknooppunt via de vertakkingsknooppunten naar het juiste leaf-knooppunt) om de gegevensrij op te halen.
Als u wilt voorkomen dat deze extra willekeurige I/O wordt gelezen op de primaire-sleutelindex om de gegevensrij op te halen, gebruikt u een dekkingsindex, die alle velden bevat die door de query zijn vereist. Over het algemeen is het gebruik van deze benadering nuttig voor I/O-gebonden workloads en workloads in de cache. Als best practice kunt u dus indexen gebruiken omdat ze in het geheugen passen en kleiner en efficiënter zijn om te lezen dan alle rijen te scannen.
Denk bijvoorbeeld aan een tabel die u gebruikt om te zoeken naar alle werknemers die na 1 januari 2000 lid zijn van het bedrijf.
mysql> show create table employee\G
*************************** 1. row ***************************
Table: employee
Create Table: CREATE TABLE `employee` (
`empid` int(11) NOT NULL AUTO_INCREMENT,
`fname` varchar(10) DEFAULT NULL,
`lname` varchar(10) DEFAULT NULL,
`joindate` datetime DEFAULT NULL,
`department` varchar(10) DEFAULT NULL,
PRIMARY KEY (`empid`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)`
`mysql> select empid, fname, lname from employee where joindate > '2000-01-01';
Als u een EXPLAIN-plan uitvoert voor deze query, ziet u dat er momenteel geen indexen worden gebruikt en dat er alleen een where-component wordt gebruikt om de werknemersrecords te filteren.
mysql> EXPLAIN select empid, fname, lname from employee where joindate > '2000-01-01'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: employee
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 3
filtered: 33.33
Extra: Using where
1 row in set, 1 warning (0.01 sec)
Als u echter een index toevoegt die de kolom in de where-component dekt, samen met de geprojecteerde kolommen, zou u zien dat de index wordt gebruikt om de kolommen veel sneller en efficiënter te vinden.
mysql> CREATE INDEX cvg_idx_ex ON employee (joindate, empid, fname, lname);
Als u nu EXPLAIN-plan uitvoert voor dezelfde query, wordt de waarde 'Index gebruiken' weergegeven in het veld 'Extra', wat betekent dat InnoDB de query uitvoert met behulp van de index die we eerder hebben gemaakt, waardoor dit wordt bevestigd als een dekkingsindex.
mysql> EXPLAIN select empid, fname, lname from employee where joindate > '2000-01-01'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: employee
partitions: NULL
type: range
possible_keys: cvg_idx_ex
key: cvg_idx_ex
key_len: 6
ref: NULL
rows: 1
filtered: 100.00
Extra: Using where; Using index
1 row in set, 1 warning (0.01 sec)
Notitie
Het is belangrijk om de juiste volgorde van de kolommen in de betreffende index te kiezen om de query correct te verwerken. De algemene regel is het kiezen van de kolommen voor het filteren eerst (WHERE-component), vervolgens sorteren/groeperen (ORDER BY en GROUP BY) en ten slotte de gegevensprojectie (SELECT).
Uit het vorige voorbeeld hebben we gezien dat het hebben van een dekkingsindex voor een query efficiëntere paden voor het ophalen van records biedt en de prestaties in een zeer gelijktijdige databaseomgeving optimaliseert.
Volgende stappen
Ga naar Stack Overflow om peer-antwoorden te vinden op uw belangrijkste vragen of om vragen te posten of te beantwoorden.