Maszyna fizyczna zaczyna wykazywać ogromne zapotrzebowanie na CPU. W slowlogu pojawia się taki, bądź podobny (patrz wpisy dotyczące formatu slowlogów: standardowy i rozszerzony) wpis:
# User@Host: root[root] @ localhost []
# Thread_id: 19484 Schema: test
# Query_time: 0.615715 Lock_time: 0.000084 Rows_sent: 0 Rows_examined: 1497474 Rows_affected: 0 Rows_read: 1497474
# Bytes_sent: 411 Tmp_tables: 0 Tmp_disk_tables: 0 Tmp_table_sizes: 0
# QC_Hit: No Full_scan: Yes Full_join: No Tmp_table: No Tmp_table_on_disk: No
# Filesort: No Filesort_on_disk: No Merge_passes: 0
# No InnoDB statistics available for this query
SET timestamp=1274095507;
SELECT * FROM links WHERE a = '3457013470' AND f = '78uhimkq3no5ekk' AND ip = '165.72.200.11' AND data > '2009-05-17, 07:27';
Bezpośrednią przyczyną wystąpienia problemu jest to, że wykonywany jest pełny skan tabeli. W rozszerzonym logu jest to napisane wprost, można też wywnioskować to z faktu, że wartości Rows_examined i Rows_read są takie same. Explain potwierdza brak indeksu:
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
| 1 | SIMPLE | links | ALL | NULL | NULL | NULL | NULL | 1497474 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
1 row in set (0,00 sec)
Na którą kolumnę nałożyć indeks? Najprostszym sposobem na znalezienie dobrych kandydatów jest wykonanie następującego zapytania:
*************************** 1. row ***************************
SUM(a='3457013470'): 381732
COUNT(DISTINCT a): 8
wyb_a: 0.0000
SUM(f = '78uhimkq3no5ekk'): 1109822
COUNT(DISTINCT f): 16
wyb_f: 0.0000
SUM(ip = '165.72.200.11'): 59
COUNT(DISTINCT ip): 494693
wyb_ip: 0.3304
SUM(data > '2009-05-17, 07:27'): 1497474
COUNT(DISTINCT data): 11472
wyb_data: 0.0077
COUNT(*): 1497474
1 row in set (0,00 sec)
Teraz trzeba ustalić, co jest ważne – wybiórczość dla tego, konkretnego zapytania, czy też „ogólna”. Jeśli interesuje nas dane, konkretne zapytanie, do indeksowania wybieramy tą kolumnę, dla której wartość wyrażenia SUM(warunek) będzie najmniejsza – w tym wypadku będzie to kolumna `ip` – w wyniku otrzymamy 59 rekordów. Jeśli interesuje nas wybiórczość dla całego zestawu danych, ważnym będzie wynik wyrażenia (COUNT(DISTINCT kolumna)/COUNT(*)), opisany powyżej jako wyb_kolumna. Im wartość ta będzie bliżej 1, tym mniejsza jest oczekiwana ilość rekordów. W tym konkretnym przypadku najlepszym wyborem będzie także kolumna `ip` – zawiera ona 494693 unikalnych a 1497474 wszystkich wartości czyli, statystycznie patrząc, zapytanie z warunkiem uwzględniającym tą kolumnę zwraca ok. trzy rekordy. Oczywiście, jest tak zakładając równomierny rozkład wartości w kolumnie, a to zdarza się rzadko. Zawsze jest to jednak jakiś wskaźnik.
Jeśli konieczne jest nałożenie indeksu na dwie lub więcej kolumn, należy całość powtórzyć, dodając na końcu zapytania warunek na kolumnę, którą wybraliśmy jako pierwszą do indeksu:
Analizując tabelę w powyżej opisany sposób trzeba zwrócić uwagę na to, że jeśli w warunku WHERE mamy operatory ‚> ‚ czy ‚<‚ to wybiórczość takiej kolumny będzie inna niż w przypadku operatora ‚=’.
Procedura wyboru indeksu opisana powyżej jest ogromnie uproszczona i nie uwzględnia wielu elementów, które można by dodatkowo analizować – rozkład statystyczny wartości w kolumnie, częstotliwość występowania różnych wartości w warunku WHERE, wpływ indeksu na operacje INSERT/UPDATE/DELETE itp. Administrator MySQL zazwyczaj wychodzi jednak z założenia, że lepiej jest aby twórca aplikacji założył indeks na podstawie tego typu analizy, niż aby założył go bez chwili zastanowienia się, czy też wręcz w ogóle indeksu nie założył. A to zdarza się niestety często.
Komentarze