Jak przyśpieszyć zapytania do bazy MySQL?

Oto garść porad, których zastosowanie powinno przyśpieszyć działanie twoich skryptów i odciążyć serwer baz danych. Większość z nich znajdzie zastosowanie ogólnie przy relacyjnych bazach danych, nie tylko przy MySQL-u.

Korzystaj z typu CHAR gdy tylko to jest możliwe (zamiast VARCHAR, BLOB czy TEXT). Wiele kolumn na pewno będzie zawierało pola o stałej długości – np. polski numer IBAN to zawsze 26 znaków. Dane z kolumn typu CHAR mogą być znacznie szybciej przeszukane, niż z kolumn innych typów.

Zaoszczędź na pamięci wykorzystywanej na każdą kolumnę, deklarując jej rozmiar na tyle tylko, aby wystarczyło na zachowanie przechowywanych wartości.

Jeśli kolumna nie jest pusta, zadeklaruj to za pomocą NOT NULL. To przyśpieszy przeszukiwanie tabeli.

Nie dziel tabeli, jeśli wydaje ci się, że masz zbyt wiele kolumn. Podczas uzyskiwania dostępu do wiersza, najwięcej czasu zajmuje wyszukanie pierwszego bajtu w wierszu.

Jeśli przeszukujesz wiersze w tej samej kolejności (np. wyr1, wyr2, wyr3, …), zoptymalizuj tabelę za pomocą polecenia ALTER TABLE … ORDER BY wyr1, wyr2, wyr3.

Nie używaj pętli PHP aby pobrać z bazy dany wiersze jeden po drugim. Zamiast tego lepiej skorzystać z dyrektywy IN, np. tak: SELECT * FROM 'tabela' WHERE 'id' IN (1,3,5,9,11);

Nie trzymaj w bazie plików binarnych (np. obrazków) – najlepiej by były one zachowane na dysku jako pliki, a w bazie znajdowały się tylko odnośniki do nich.

Jeśli musisz w bazie trzymać pliki tekstowe o dużych rozmiarach, sprawdź, czy nie lepiej zadeklarować typu kolumny jako BLOB, wprowadzać dane po ich skompresowaniu gzipem, a następnie dekompresować już w aplikacji.

Jeśli ładujesz tabelę z pliku tekstowego, wykorzystaj polecenie LOAD DATA INFILE. Jest bardzo szybkie.

Dane o sesji użytkownika, jak i wszelkie inne niekrytyczne dane, warto trzymać w tabeli MEMORY. Jest o wiele szybsza niż inne. Tabelę taką zakłada się poleceniem CREATE TABLE test ENGINE=MEMORY;.

Porzuć REPLACE. REPLACE to złożenie DELETE i INSERT. Lepiej jest użyć INSERT … ON DUPLICATE KEY UPDATE (czyli INSERT oraz UPDATE, jeśli doszło do konfliktu).

Unikaj stosowania klauzuli HAVING. Jest dość powolna.

Grupuj podobne INSERT-y w jeden długi INSERT z wieloma wartościami, tak aby wstawić wiele wierszy jednocześnie. Każde połączenie, przesłanie i parsowanie kwerendy zajmuje kilkukrotnie dłużej niż samo wstawienie danych. Jeśli takie zgrupowanie jest niemożliwe, to jeśli korzystasz z engine'u InnoDB, wypróbuj START TRANSACTION oraz COMMIT. Jeśli jest to MyISAM, zastosuj LOCK TABLES – wówczas bufor indeksu będzie napełniany tylko raz, po wszystkich INSERT-ach. (Pamiętaj jednak, żeby co kilkaset wierszy otwierać (unlock) tabele, aby inne wątki miały do nich dostęp).

Korzystaj z funkcji BENCHMARK, aby przetestować poszególne wyrażenia.