Dela via

Metodtips för felsökning av Azure Database for MySQL – flexibel server

  • Artikel

GÄLLER FÖR: Azure Database for MySQL – Azure Database for MySQL – enskild server – flexibel server

Viktigt!

Azure Database for MySQL – enskild server är på väg att dras tillbaka. Vi rekommenderar starkt att du uppgraderar till en flexibel Azure Database for MySQL-server. Mer information om hur du migrerar till en flexibel Azure Database for MySQL-server finns i Vad händer med Azure Database for MySQL – enskild server?

Använd följande avsnitt för att hålla azure database for MySQL-databaserna flexibla och använda den här informationen som vägledande principer för att säkerställa att schemana är utformade optimalt och ger bästa möjliga prestanda för dina program.

Kontrollera antalet index

I en upptagen databasmiljö kan du observera hög I/O-användning, vilket kan vara en indikator på dåliga dataåtkomstmönster. Oanvända index kan ha en negativ inverkan på prestanda när de förbrukar diskutrymme och cacheminne, och långsammare skrivåtgärder (INSERT/DELETE/UPDATE). Oanvända index förbrukar i onödan mer lagringsutrymme och ökar säkerhetskopieringsstorleken.

Innan du tar bort ett index måste du samla in tillräckligt med information för att kontrollera att det inte längre används. Den här verifieringen kan hjälpa dig att undvika att oavsiktligt ta bort ett index som är kritiskt för en fråga som bara körs kvartalsvis eller årligen. Tänk också på om ett index används för att framtvinga unikhet eller ordning.

Kommentar

Kom ihåg att granska index regelbundet och utföra nödvändiga uppdateringar baserat på eventuella ändringar i tabelldata.

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;

(eller)

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));

Lista de mest trafikerade indexen på servern

Utdata från följande fråga innehåller information om de mest använda indexen i alla tabeller och scheman på databasservern. Den här informationen är användbar för att identifiera förhållandet mellan skrivningar och läsningar mot varje index och svarstidsnumren för läsningar samt enskilda skrivåtgärder, vilket kan tyda på att ytterligare justering krävs mot den underliggande tabellen och beroende frågor.

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

Granska designen för primärnyckeln

Azure Database for MySQL – flexibel server använder InnoDB-lagringsmotorn för alla icke-temporära tabeller. Med InnoDB lagras data i ett grupperat index med hjälp av en B-Trädstruktur. Tabellen är fysiskt ordnad baserat på primärnyckelvärden, vilket innebär att rader lagras i primärnyckelordning.

Varje sekundär nyckelpost i en InnoDB-tabell innehåller en pekare till det primära nyckelvärde där data lagras. Med andra ord innehåller en sekundär indexpost en kopia av det primära nyckelvärde som posten pekar på. Därför har primära nyckelval en direkt effekt på mängden lagringsutrymme i dina tabeller.

Om en nyckel härleds från faktiska data (t.ex. användarnamn, e-post, SSN osv.) kallas den för en naturlig nyckel. Om en nyckel är artificiell och inte härleds från data (t.ex. ett heltal med automatisk avföring) kallas den för en syntetisk nyckel eller surrogatnyckel.

Vi rekommenderar vanligtvis att du undviker att använda naturliga primära nycklar. Dessa nycklar är ofta mycket breda och innehåller långa värden från en eller flera kolumner. Detta kan i sin tur medföra allvarliga lagringskostnader, där det primära nyckelvärdet kopieras till varje sekundär nyckelpost. Dessutom följer naturliga nycklar vanligtvis inte en fördefinierad ordning, vilket avsevärt minskar prestanda och framkallar sidfragmentering när rader infogas eller uppdateras. Undvik dessa problem genom att använda monotont ökande surrogatnycklar i stället för naturliga nycklar. En kolumn med autoinkrement (stort)heltal är ett bra exempel på en monotont ökande surrogatnyckel. Om du behöver en viss kombination av kolumner ska du vara unik och deklarera dessa kolumner som en unik sekundärnyckel.

Under de inledande stegen för att skapa ett program kanske du inte tänker framåt för att föreställa dig en tid när tabellen börjar närma sig att ha två miljarder rader. Därför kan du välja att använda ett signerat heltal på 4 byte för datatypen för en ID-kolumn (primärnyckel). Kontrollera alla primärnycklar i tabellen och växla till att använda bigint-kolumner (8 byte heltal) för att hantera potentialen för en hög volym eller tillväxt.

Kommentar

Mer information om datatyper och deras högsta värden finns i Referenshandbok för MySQL i Datatyper.

Använda täckande index

I föregående avsnitt beskrivs hur index i MySQL organiseras som B-Träd och i ett grupperat index innehåller lövnoderna datasidorna i den underliggande tabellen. Sekundära index har samma B-trädstruktur som klustrade index, och du kan definiera dem i en tabell eller vy med ett grupperat index eller en heap. Varje indexrad i det sekundära indexet innehåller det icke-illustrerade nyckelvärdet och en radlokaliserare. Den här lokaliseraren pekar på dataraden i det klustrade indexet eller heapen med nyckelvärdet. Därför måste alla sökningar som involverar ett sekundärt index navigera från rotnoden via grennoderna till rätt lövnod för att ta det primära nyckelvärdet. Systemet kör sedan en slumpmässig I/O-läsning på primärnyckelindexet (navigerar återigen från rotnoden via grennoderna till rätt lövnod) för att hämta dataraden.

Om du vill undvika den här extra slumpmässiga I/O-läsningen på primärnyckelindexet för att hämta dataraden använder du ett täckande index som innehåller alla fält som krävs av frågan. I allmänhet är det fördelaktigt att använda den här metoden för I/O-bundna arbetsbelastningar och cachelagrade arbetsbelastningar. Vi rekommenderar därför att du använder täckande index eftersom de passar i minnet och är mindre och effektivare att läsa än att skanna alla rader.

Överväg till exempel en tabell som du använder för att försöka hitta alla anställda som anslöt sig till företaget efter den 1 januari 2000.

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';

Om du kör en EXPLAIN-plan för den här frågan skulle du observera att för närvarande används inga index och att enbart en where-sats används för att filtrera de anställdas poster.

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)

Men om du har lagt till ett index som täcker kolumnen i where-satsen, skulle du tillsammans med de beräknade kolumnerna se att indexet används för att hitta kolumnerna mycket snabbare och effektivare.

mysql> CREATE INDEX cvg_idx_ex ON employee (joindate, empid, fname, lname);

Om du kör EXPLAIN-planen på samma fråga visas värdet "Använda index" i fältet Extra, vilket innebär att InnoDB kör frågan med hjälp av indexet som vi skapade tidigare, vilket bekräftar detta som ett täckande index.

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)

Kommentar

Det är viktigt att välja rätt ordning på kolumnerna i det täckande indexet för att hantera frågan korrekt. Den allmänna regeln är att välja kolumnerna för filtrering först (WHERE-sats), sedan sortering/gruppering (ORDER BY och GROUP BY) och slutligen dataprojektionen (SELECT).

I det tidigare exemplet har vi sett att ett täckande index för en fråga ger effektivare sökvägar för posthämtning och optimerar prestanda i en mycket samtidig databasmiljö.

Nästa steg

Om du vill hitta peer-svar på dina viktigaste frågor, eller om du vill publicera eller svara på frågor, går du till Stack Overflow.