Czasami zdarza się, że zamiast oczekiwanego efektu wykonanie SELECT’a kończy się poniższym błędem:
Dzieje się tak wtedy, gdy optimizer MySQL uzna, że dane zapytanie musi sprawdzić większą ilość rekordów niż wartość zmiennej max_join_size. Zmienna ta ma na celu zabezpieczenie serwera przed wykonaniem zapytań, które trwałyby kosmicznie długo. Domyślnie przyjmuje wartość 4G (4*1024*1024*1024). Wydaje się to dużo, ale w praktyce okazuje się, że taki limit prosto przekroczyć.
Tworzymy następujące tabele:
CREATE TABLE `test`.`tab1` (id_1 INT AUTO_INCREMENT, kol11 VARCHAR(20), kol12 VARCHAR(20), PRIMARY KEY (id_1));
CREATE TABLE `test`.`tab2` (id_1 INT AUTO_INCREMENT, id_2 INT, kol21 VARCHAR(20), kol22 VARCHAR(20), PRIMARY KEY (id_1));
CREATE TABLE `test`.`tab3` (id_1 INT AUTO_INCREMENT, id_2 INT, kol31 VARCHAR(20), kol32 VARCHAR(20), PRIMARY KEY (id_1));
Wypełniamy je następującymi danymi:
+------+-----------------+-----------------+
| id_1 | kol11 | kol12 |
+------+-----------------+-----------------+
| 1 | dcydbdbzdydazcz | cyzycdbcybzdczy |
| 2 | cbydydyaydcbzby | ydyzbdcdbyczbyd |
+------+-----------------+-----------------+
2 rows in set (0.00 sec)
mysql> SELECT * FROM tab2;
+------+------+-----------------+-----------------+
| id_1 | id_2 | kol21 | kol22 |
+------+------+-----------------+-----------------+
| 1 | 1 | bcdybzdbczbcbzy | bdcybydybybyzcb |
| 2 | 2 | cbcybdczbcbzyzd | dbzdcbzbdybdydz |
+------+------+-----------------+-----------------+
2 rows in set (0.00 sec)
mysql> SELECT * FROM tab3;
+------+------+-----------------+-----------------+
| id_1 | id_2 | kol31 | kol32 |
+------+------+-----------------+-----------------+
| 1 | 1 | ybydydybzdzbzyb | zabcybcbdcdcdcd |
| 2 | 2 | yzbdzdbzbzbcycz | zbzycyzczcbdbyd |
+------+------+-----------------+-----------------+
2 rows in set (0.00 sec)
Załóżmy, że chcemy wykonać JOIN’a pomiędzy tabelami `tab1`, `tab2` i `tab3`, a następnie wybieramy te wyniki, w przypadku których zawartość kolumny kol11 nie zaczyna się na literę ‚d’, lub zawartość kolumny kol12 zaczyna się na literę ‚c’. Piszemy zapytanie:
+------+-----------------+-----------------+------+------+-----------------+-----------------+------+------+-----------------+-----------------+
| id_1 | kol11 | kol12 | id_1 | id_2 | kol21 | kol22 | id_1 | id_2 | kol31 | kol32 |
+------+-----------------+-----------------+------+------+-----------------+-----------------+------+------+-----------------+-----------------+
| 1 | dcydbdbzdydazcz | cyzycdbcybzdczy | 1 | 1 | bcdybzdbczbcbzy | bdcybydybybyzcb | 1 | 1 | ybydydybzdzbzyb | zabcybcbdcdcdcd |
| 2 | cbydydyaydcbzby | ydyzbdcdbyczbyd | 1 | 1 | bcdybzdbczbcbzy | bdcybydybybyzcb | 1 | 1 | ybydydybzdzbzyb | zabcybcbdcdcdcd |
| 1 | dcydbdbzdydazcz | cyzycdbcybzdczy | 2 | 2 | cbcybdczbcbzyzd | dbzdcbzbdybdydz | 1 | 1 | ybydydybzdzbzyb | zabcybcbdcdcdcd |
| 2 | cbydydyaydcbzby | ydyzbdcdbyczbyd | 2 | 2 | cbcybdczbcbzyzd | dbzdcbzbdybdydz | 1 | 1 | ybydydybzdzbzyb | zabcybcbdcdcdcd |
| 1 | dcydbdbzdydazcz | cyzycdbcybzdczy | 1 | 1 | bcdybzdbczbcbzy | bdcybydybybyzcb | 2 | 2 | yzbdzdbzbzbcycz | zbzycyzczcbdbyd |
| 2 | cbydydyaydcbzby | ydyzbdcdbyczbyd | 1 | 1 | bcdybzdbczbcbzy | bdcybydybybyzcb | 2 | 2 | yzbdzdbzbzbcycz | zbzycyzczcbdbyd |
| 1 | dcydbdbzdydazcz | cyzycdbcybzdczy | 2 | 2 | cbcybdczbcbzyzd | dbzdcbzbdybdydz | 2 | 2 | yzbdzdbzbzbcycz | zbzycyzczcbdbyd |
| 2 | cbydydyaydcbzby | ydyzbdcdbyczbyd | 2 | 2 | cbcybdczbcbzyzd | dbzdcbzbdybdydz | 2 | 2 | yzbdzdbzbzbcycz | zbzycyzczcbdbyd |
+------+-----------------+-----------------+------+------+-----------------+-----------------+------+------+-----------------+-----------------+
8 rows in set (0.00 sec)
Jeśli sprawdzimy to zapytanie przy pomocy EXPLAIN zobaczymy:
+----+-------------+-------+------+---------------+------+---------+------+------+-------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------------+
| 1 | SIMPLE | tab1 | ALL | NULL | NULL | NULL | NULL | 2 | Using where |
| 1 | SIMPLE | tab2 | ALL | NULL | NULL | NULL | NULL | 2 | Using join buffer |
| 1 | SIMPLE | tab3 | ALL | NULL | NULL | NULL | NULL | 2 | Using join buffer |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------------+
3 rows in set (0.00 sec)
Okazuje się, że tabele łączone są przy pomocy join buffer, generowane też są wszystkie kombinacje rekordów – nie wykorzystywany jest żaden indeks, gdyż tabele nie są połączone więzami integralności. Jeśli w takiej sytuacji każda z tabel zawierać będzie po 2000 rekordów, okaże się że łącznie do sprawdzenia jest 8 miliardów kombinacji. W przypadku domyślnego limitu max_join_size zapytanie zostanie przerwane, a w przypadku gdy zwiększymy ten limit zapytanie będzie się długo wykonywać. Można co prawda przyspieszyć tego typu zapytanie zwiększając wielkość zmiennej join_buffer_size, ale przełoży się to na zwiększone zapotrzebowanie na pamięć. W pewnym momencie, wraz ze wzrostem ilości danych w tabelach, dojdzie się do momentu w którym pamięci nie wystarczy – zapytanie ponownie zacznie działać tragicznie wolno. Jeśli chcemy stosować zwykły JOIN, bez więzów integralności (USING, ON), trzeba pamiętać o tym, że ilość rekordów do sprawdzenia rośnie gwałtownie wraz z przyrostem danych w tabelach – wynikiem jest mnożenie ilość rekordów w każdej z nich.
Komentarze