Najlepsze rozwiązania dotyczące rozwiązywania problemów z usługą Azure Database for MySQL — serwer elastyczny
Skorzystaj z poniższych sekcji, aby zapewnić bezproblemowe działanie elastycznych baz danych serwera usługi Azure Database for MySQL i użyć tych informacji jako wytycznych dotyczących zapewnienia optymalnego projektowania schematów i zapewnienia najlepszej wydajności dla aplikacji.
Sprawdzanie liczby indeksów
W zajętym środowisku bazy danych można zaobserwować wysokie użycie operacji we/wy, co może być wskaźnikiem słabych wzorców dostępu do danych. Nieużywane indeksy mogą mieć negatywny wpływ na wydajność, ponieważ zużywają miejsce na dysku i pamięć podręczną oraz spowalniają operacje zapisu (INSERT/ DELETE / UPDATE). Nieużywane indeksy niepotrzebnie zużywają więcej miejsca do magazynowania i zwiększają rozmiar kopii zapasowej.
Przed usunięciem jakiegokolwiek indeksu należy zebrać wystarczającą ilość informacji, aby sprawdzić, czy nie jest już używana. Ta weryfikacja może pomóc uniknąć przypadkowo usunięcia indeksu, który ma krytyczne znaczenie dla zapytania uruchamianego tylko co kwartał lub co rok. Należy również wziąć pod uwagę, czy indeks jest używany do wymuszania unikatowości, czy porządkowania.
Uwaga
Pamiętaj, aby okresowo przeglądać indeksy i wykonywać wszelkie niezbędne aktualizacje na podstawie wszelkich modyfikacji danych tabeli.
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;
(lub)
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));
Wyświetlanie listy najbardziej ruchliwych indeksów na serwerze
Dane wyjściowe z poniższego zapytania zawierają informacje o najczęściej używanych indeksach we wszystkich tabelach i schematach na serwerze bazy danych. Te informacje są pomocne podczas identyfikowania współczynnika zapisów do odczytów względem każdego indeksu oraz liczby opóźnień dla operacji odczytu, a także poszczególnych operacji zapisu, co może wskazywać, że dalsze dostrajanie jest wymagane w odniesieniu do bazowej tabeli i zapytań zależnych.
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
Przegląd projektu klucza podstawowego
Elastyczny serwer usługi Azure Database for MySQL używa aparatu magazynu InnoDB dla wszystkich tabel nietemporacyjnych. W przypadku bazy danych InnoDB dane są przechowywane w indeksie klastrowanym przy użyciu struktury drzewa B. Tabela jest fizycznie zorganizowana na podstawie wartości klucza podstawowego, co oznacza, że wiersze są przechowywane w kolejności klucza podstawowego.
Każdy wpis klucza pomocniczego w tabeli InnoDB zawiera wskaźnik do wartości klucza podstawowego, w której są przechowywane dane. Innymi słowy, pomocniczy wpis indeksu zawiera kopię wartości klucza podstawowego, do której wskazuje wpis. W związku z tym wybór klucza podstawowego ma bezpośredni wpływ na ilość nakładu pracy związanego z magazynem w tabelach.
Jeśli klucz pochodzi z rzeczywistych danych (np. nazwy użytkownika, wiadomości e-mail, sieci SSN itp.), jest nazywany kluczem naturalnym. Jeśli klucz jest sztuczny i nie pochodzi z danych (np. automatycznej liczby całkowitej), jest określany jako klucz syntetyczny lub klucz zastępczy.
Zwykle zaleca się unikanie używania naturalnych kluczy podstawowych. Te klucze są często bardzo szerokie i zawierają długie wartości z jednej lub wielu kolumn. Z kolei może to powodować poważne obciążenie magazynu z wartością klucza podstawowego skopiowaną do każdego wpisu klucza pomocniczego. Ponadto klucze naturalne zwykle nie są zgodne ze wstępnie ustaloną kolejnością, co znacznie zmniejsza wydajność i wywołuje fragmentację strony podczas wstawiania lub aktualizowania wierszy. Aby uniknąć tych problemów, użyj monotonicznie zwiększających klucze zastępcze zamiast kluczy naturalnych. Kolumna autoinkrementacji (big)integer jest dobrym przykładem monotonicznie rosnącego klucza zastępczego. Jeśli potrzebujesz określonej kombinacji kolumn, być unikatowa, zadeklaruj te kolumny jako unikatowy klucz pomocniczy.
Podczas początkowych etapów tworzenia aplikacji możesz nie myśleć, aby wyobrazić sobie czas, w którym tabela zaczyna zbliżać się do dwóch miliardów wierszy. W związku z tym możesz zdecydować się na użycie podpisanej liczby całkowitej 4-bajtowej dla typu danych kolumny IDENTYFIKATOR (klucz podstawowy). Pamiętaj, aby sprawdzić wszystkie klucze podstawowe tabeli i przełączyć się do użycia 8-bajtowych kolumn całkowitych (BIGINT), aby pomieścić potencjał dużego woluminu lub wzrostu.
Uwaga
Aby uzyskać więcej informacji o typach danych i ich maksymalnych wartościach, zobacz Podręcznik dokumentacji bazy danych MySQL, zobacz Typy danych.
Korzystanie z indeksów obejmujących
W poprzedniej sekcji wyjaśniono, jak indeksy w bazie danych MySQL są zorganizowane jako drzewa B i w indeksie klastrowanym węzły liścia zawierają strony danych tabeli bazowej. Indeksy pomocnicze mają tę samą strukturę drzewa B co indeksy klastrowane i można je zdefiniować w tabeli lub widoku z indeksem klastrowanym lub stertą. Każdy wiersz indeksu w indeksie pomocniczym zawiera nieklastrowaną wartość klucza i lokalizator wierszy. Ten lokalizator wskazuje wiersz danych w indeksie klastrowanym lub stercie o wartości klucza. W związku z tym każde wyszukiwanie obejmujące indeks pomocniczy musi przechodzić od węzła głównego przez węzły gałęzi do poprawnego węzła liścia, aby pobrać wartość klucza podstawowego. Następnie system wykonuje losowe operacje we/wy odczytane w indeksie klucza podstawowego (po raz kolejny przechodząc z węzła głównego przez węzły gałęzi do poprawnego węzła liścia), aby pobrać wiersz danych.
Aby uniknąć tego dodatkowego losowego odczytu operacji we/wy w indeksie klucza podstawowego w celu pobrania wiersza danych, użyj indeksu obejmującego wszystkie pola wymagane przez zapytanie. Ogólnie rzecz biorąc, użycie tego podejścia jest korzystne dla obciążeń związanych z we/wy i buforowanych obciążeń. Najlepszym rozwiązaniem jest użycie indeksów obejmujących, ponieważ mieszczą się w pamięci i są mniejsze i bardziej wydajne do odczytu niż skanowanie wszystkich wierszy.
Rozważmy na przykład tabelę używaną do próby znalezienia wszystkich pracowników, którzy dołączyli do firmy po 1 stycznia 2000 r.
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';
Jeśli uruchomisz plan WYJAŚNIj dla tego zapytania, zauważysz, że obecnie nie są używane żadne indeksy, a sama klauzula where jest używana do filtrowania rekordów pracowników.
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)
Jeśli jednak dodano indeks, który obejmuje kolumnę w klauzuli where, wraz z przewidywanymi kolumnami, zobaczysz, że indeks jest używany do znajdowania kolumn znacznie szybciej i wydajniej.
mysql> CREATE INDEX cvg_idx_ex ON employee (joindate, empid, fname, lname);
Teraz, jeśli uruchomisz plan WYJAŚNIj dla tego samego zapytania, wartość "Korzystanie z indeksu" pojawi się w polu "Extra" (Dodatkowe), co oznacza, że usługa InnoDB wykonuje zapytanie przy użyciu utworzonego wcześniej indeksu, co potwierdza to jako indeks obejmujący.
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)
Uwaga
Ważne jest, aby wybrać prawidłową kolejność kolumn w indeksie pokrycia, aby prawidłowo obsługiwać zapytanie. Ogólną regułą jest wybranie kolumn do filtrowania najpierw (klauzula WHERE), a następnie sortowanie/grupowanie (ORDER BY i GROUP BY), a na koniec projekcja danych (SELECT).
W poprzednim przykładzie widzieliśmy, że posiadanie indeksu obejmującego dla zapytania zapewnia bardziej wydajne ścieżki pobierania rekordów i optymalizuje wydajność w wysoce współbieżnym środowisku bazy danych.