mysql – proste zapytanie SQL na wp_postmeta bardzo wolne
wp_postmeta
ma nieefektywne indeksy. Opublikowana tabela (patrz Wikipedia) jest
CREATE TABLE wp_postmeta (
meta_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
post_id bigint(20) unsigned NOT NULL DEFAULT '0',
meta_key varchar(255) DEFAULT NULL,
meta_value longtext,
PRIMARY KEY (meta_id),
KEY post_id (post_id),
KEY meta_key (meta_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Problemy:
- The
AUTO_INCREMENT
nie zapewnia żadnych korzyści; w rzeczywistości spowalnia większość zapytań (ponieważ trzeba szukać w indeksie dodatkowym, aby znaleźć identyfikator auto_inc, a następnie szukać w danych rzeczywistego identyfikatora, którego potrzebujesz) - The
AUTO_INCREMENT
powoduje dodatkowy bałagan – zarówno na dysku, jak i w pamięci podręcznej. - Dużo lepiej
PRIMARY KEY(post_id, meta_key)
— klastrowany, obsługuje obie części zwykłegoJOIN
. BIGINT
to przesada, ale nie da się tego naprawić bez zmiany innych tabel.VARCHAR(255)
może być problemem w MySQL 5.6 zutf8mb4
; zobacz obejścia poniżej.- Kiedy kiedykolwiek będzie meta_key lub meta_value
NULL
?
Rozwiązania:
CREATE TABLE wp_postmeta (
post_id BIGINT UNSIGNED NOT NULL,
meta_key VARCHAR(255) NOT NULL,
meta_value LONGTEXT NOT NULL,
PRIMARY KEY(post_id, meta_key),
INDEX(meta_key)
) ENGINE=InnoDB;
Typowe użycie:
JOIN wp_postmeta AS m ON p.id = m.post_id
WHERE m.meta_key = '...'
Uwagi:
- Kompozyt
PRIMARY KEY
przechodzi bezpośrednio do żądanego wiersza, bez dygresji poprzez indeks pomocniczy ani przeszukiwania wielu wierszy. INDEX(meta_key)
mogą być przydatne lub nie, w zależności od innych zapytań.- Do „klastrowania” wymagany jest InnoDB.
- Idąc dalej, użyj utf8mb4, a nie utf8. Należy jednak zachować spójność we wszystkich tabelach WP i parametrach połączenia.
Błąd „maksymalna długość klucza to 767”, który może wystąpić w MySQL 5.6 podczas próby użycia ZESTAWU ZNAKÓW utf8mb4. Wykonaj jedną z poniższych czynności (każda ma wady), aby uniknąć błędu:
- Uaktualnij do wersji 5.7.7, aby uzyskać limit 3072 bajtów — Twoja chmura może tego nie zapewniać;
- Zmień 255 na 191 w VARCHAR – tracisz klucze dłuższe niż 191 znaków (mało prawdopodobne?);
- ALTER .. CONVERT TO utf8 – tracisz emoji i część chińskiego;
- Użyj indeksu „przedrostkowego” – tracisz część korzyści związanych z wydajnością;
- Skonfiguruj ponownie (jeśli pozostaniesz przy wersji 5.6.3 – 5.7.6) – 4 rzeczy do zmiany: Barracuda + innodb_file_per_table + innodb_large_prefix + dynamiczny lub skompresowany.
Potencjalne niezgodności
meta_id
prawdopodobnie nie jest nigdzie używany. (Ale usunięcie go jest ryzykowne).- Mógłbyś zachować
meta_id
i uzyskaj większość korzyści, przechodząc na te indeksy:PRIMARY KEY(post_id, meta_key, meta_id), INDEX(meta_id), INDEX(meta_key, post_id)
. (Uwaga: mającmeta_id
na końcu PK możliwe jest, że post_id+meta_key nie będzie unikalny.) - Zmiana z
BIGINT
do mniejszego typu danych wymagałoby zmiany również innych tabel.
utf8mb4
- Przejście do wersji 5.7 nie powinno być niezgodne.
- Kurczy się do
VARCHAR(191)
wymagałoby od użytkownika zrozumienia, że limitem jest teraz arbitralny „191” zamiast poprzedniego arbitralnego limitu „255”. - Poprawka „rekonfiguracji” dotyczy problemów z DBA, a nie problemów z niezgodnością.
Komentarz
Mam nadzieję, że część tego, co zalecam, znajdzie się w planie działania WordPressa. Tymczasem stackoverflow i dba.stackexchange są zaśmiecone pytaniami „dlaczego WP działa tak wolno”. Uważam, że podane tutaj poprawki znacznie ograniczyłyby liczbę takich pytań typu skarga.
Należy pamiętać, że niektórzy użytkownicy przechodzą na utf8mb4 pomimo problemów ze zgodnością. Potem wpadają w kłopoty. Próbowałem zająć się wszystkimi MySQL’a problemy, które mają.
Zaczerpnięte z bloga mysql Ricka Jamesa: źródło