Aanbevolen procedures voor het oplossen van problemen met Azure Database for MySQL - Flexible 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 een flexibele Azure Database for MySQL-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 volgende secties om ervoor te zorgen dat uw flexibele Azure Database for MySQL-serverdatabases soepel worden uitgevoerd 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 een negatieve invloed hebben op de prestaties wanneer ze schijfruimte en cache verbruiken en schrijfbewerkingen vertragen (INSERT / DELETE / UPDATE). Ongebruikte indexen verbruiken onnodig meer opslagruimte en vergroten de back-upgrootte.

Voordat u een index verwijdert, moet u voldoende informatie verzamelen om te controleren of deze niet meer wordt gebruikt. Met deze verificatie kunt u voorkomen dat u per ongeluk een index verwijdert die essentieel is voor een query die slechts elk kwartaal of jaarlijks wordt uitgevoerd. Zorg er ook voor dat u bedenkt 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 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));

De drukste indexen op de server weergeven

De uitvoer van de volgende query bevat informatie over de meest gebruikte indexen in alle tabellen en schema's op de databaseserver. Deze informatie is handig bij het identificeren van de verhouding tussen schrijfbewerkingen en leesbewerkingen voor elke index en de latentienummers voor leesbewerkingen en afzonderlijke schrijfbewerkingen, die kunnen aangeven dat verdere afstemming vereist is voor de onderliggende tabel en afhankelijke query's.

SELECT 
object_schema AS table_schema, 
object_name AS table_name, 
index_name, count_star AS all_accesses, 
count_read, 
count_write, 
Concat(Truncate(count_read / count_star * 100, 0), ':', 
Truncate(count_write / count_star * 100, 0)) AS read_write_ratio, 
 count_fetch AS rows_selected , 
 count_insert AS rows_inserted, 
 count_update AS rows_updated, 
 count_delete AS rows_deleted, 
 Concat(Round(sum_timer_wait / 1000000000000, 2), ' s') AS total_latency , 
 Concat(Round(sum_timer_fetch / 1000000000000, 2), ' s') AS select_latency, 
 Concat(Round(sum_timer_insert / 1000000000000, 2), ' s') AS insert_latency, 
Concat(Round(sum_timer_update / 1000000000000, 2), ' s') AS update_latency, 
 Concat(Round(sum_timer_delete / 1000000000000, 2), ' s') AS  delete_latency 
FROM performance_schema.table_io_waits_summary_by_index_usage 
WHERE index_name IS NOT NULL AND count_star > 0 
ORDER BY sum_timer_wait DESC 

Het ontwerp van de primaire sleutel controleren

Azure Database for MySQL Flexibele server maakt gebruik van 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 sleutelkeuzen 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 gegenereerd 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 waarde van de primaire sleutel wordt gekopieerd naar elke secundaire sleutelvermelding. Bovendien volgen natuurlijke sleutels meestal geen vooraf vastgestelde volgorde, wat de prestaties aanzienlijk vermindert en paginafragmentatie 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 increment (big)integer is een goed voorbeeld van een monotonisch toenemende surrogaatsleutel. Als u een bepaalde combinatie van kolommen nodig hebt, moet u deze kolommen uniek declareren als een unieke secundaire sleutel.

Tijdens de eerste fasen van het bouwen van een toepassing denkt u misschien niet vooruit om zich een tijd voor te stellen waarop 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 kolommen met 8 bytes gehele getallen (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.

Dekkingsindexen gebruiken

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 zoekopdracht waarbij een secundaire index is betrokken, navigeren vanaf het hoofdknooppunt via 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 in de primaire-sleutelindex om de gegevensrij op te halen, gebruikt u een dekkingsindex, die alle velden bevat die vereist zijn voor de query. Over het algemeen is het gebruik van deze methode nuttig voor I/O-gebonden workloads en workloads in de cache. Als best practice kunt u indexen gebruiken omdat ze in het geheugen passen en kleiner en efficiënter zijn om te lezen dan het scannen van alle rijen.

Denk bijvoorbeeld aan een tabel die u gebruikt om alle werknemers te vinden 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 een where-component alleen 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 hebt toegevoegd die betrekking heeft op de kolom in de where-component, samen met de geprojecteerde kolommen, ziet u 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 een 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

Als u peerantwoorden wilt vinden op uw belangrijkste vragen of vragen wilt posten of beantwoorden, gaat u naar Stack Overflow.