Jak mogę zoptymalizować to zapytanie MySQL?

Mam zapytanie, którego uruchomienie zajmuje szczególnie dużo czasu (ponad 15 sekund), a wraz z upływem czasu staje się coraz gorzej. Zoptymalizowałem to w przeszłości i dodałem indeksy, sortowanie na poziomie kodu i inne optymalizacje, ale wymaga to dalszego dopracowania.

SELECT sounds.*, avg(ratings.rating) AS avg_rating, count(ratings.rating) AS votes FROM `sounds` 
INNER JOIN ratings ON sounds.id = ratings.rateable_id 
WHERE (ratings.rateable_type = 'Sound' 
   AND sounds.blacklisted = false 
   AND sounds.ready_for_deployment = true 
   AND sounds.deployed = true 
   AND sounds.type = "Sound" 
   AND sounds.created_at > "2011-03-26 21:25:49") 
GROUP BY ratings.rateable_id

Zapytanie ma na celu uzyskanie mi sound idśredniej oceny najnowszych wydanych dźwięków. Istnieje około 1500 dźwięków i 2 miliony ocen.

Mam kilka indeksów sounds

mysql> show index from sounds;
+--------+------------+------------------------------------------+--------------+----------------------+-----------+-------------+----------+--------+------+------------+————+
| Table  | Non_unique | Key_name                                 | Seq_in_index | Column_name          | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+--------+------------+------------------------------------------+--------------+----------------------+-----------+-------------+----------+--------+------+------------+————+
| sounds |          0 | PRIMARY                                  |            1 | id                   | A         |        1388 |     NULL | NULL   |      | BTREE      |         | 
| sounds |          1 | sounds_ready_for_deployment_and_deployed |            1 | deployed             | A         |           5 |     NULL | NULL   | YES  | BTREE      |         | 
| sounds |          1 | sounds_ready_for_deployment_and_deployed |            2 | ready_for_deployment | A         |          12 |     NULL | NULL   | YES  | BTREE      |         | 
| sounds |          1 | sounds_name                              |            1 | name                 | A         |        1388 |     NULL | NULL   |      | BTREE      |         | 
| sounds |          1 | sounds_description                       |            1 | description          | A         |        1388 |      128 | NULL   | YES  | BTREE      |         | 
+--------+------------+------------------------------------------+--------------+----------------------+-----------+-------------+----------+--------+------+------------+---------+

i kilka dalej ratings

mysql> show index from ratings;
+---------+------------+-----------------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+————+
| Table   | Non_unique | Key_name                                | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+---------+------------+-----------------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+————+
| ratings |          0 | PRIMARY                                 |            1 | id          | A         |     2008251 |     NULL | NULL   |      | BTREE      |         | 
| ratings |          1 | index_ratings_on_rateable_id_and_rating |            1 | rateable_id | A         |          18 |     NULL | NULL   |      | BTREE      |         | 
| ratings |          1 | index_ratings_on_rateable_id_and_rating |            2 | rating      | A         |        9297 |     NULL | NULL   | YES  | BTREE      |         | 
+---------+------------+-----------------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

Tutaj jest EXPLAIN

mysql> EXPLAIN SELECT sounds.*, avg(ratings.rating) AS avg_rating, count(ratings.rating) AS votes FROM sounds INNER JOIN ratings ON sounds.id = ratings.rateable_id WHERE (ratings.rateable_type = 'Sound' AND sounds.blacklisted = false AND sounds.ready_for_deployment = true AND sounds.deployed = true AND sounds.type = "Sound" AND sounds.created_at > "2011-03-26 21:25:49") GROUP BY ratings.rateable_id;
+----+-------------+---------+--------+--------------------------------------------------+-----------------------------------------+---------+-----------------------------------------+---------+——————+
| id | select_type | table   | type   | possible_keys                                    | key                                     | key_len | ref                                     | rows    | Extra       |
+----+-------------+---------+--------+--------------------------------------------------+-----------------------------------------+---------+-----------------------------------------+---------+——————+
|  1 | SIMPLE      | ratings | index  | index_ratings_on_rateable_id_and_rating          | index_ratings_on_rateable_id_and_rating | 9       | NULL                                    | 2008306 | Using where | 
|  1 | SIMPLE      | sounds  | eq_ref | PRIMARY,sounds_ready_for_deployment_and_deployed | PRIMARY                                 | 4       | redacted_production.ratings.rateable_id |       1 | Using where | 
+----+-------------+---------+--------+--------------------------------------------------+-----------------------------------------+---------+-----------------------------------------+---------+-------------+

Raz uzyskane wyniki zapisuję w pamięci podręcznej, więc wydajność witryny nie stanowi większego problemu, ale moje podgrzewacze pamięci podręcznej działają coraz dłużej z powodu tak długiego połączenia i zaczyna to stanowić problem. To nie wydaje się być dużą liczbą liczb do zgryzienia w jednym zapytaniu…

Co jeszcze mogę zrobić, aby to działało lepiej ?

Po przejrzeniu zapytania, tabel i klauzul WHERE AND GROUP BY, zalecam następujące czynności:

Zalecenie nr 1) Przeanalizuj zapytanie

Zreorganizowałem zapytanie, aby zrobić trzy (3) rzeczy:

1. tworzyć mniejsze tabele tymczasowe
2. Przetwórz klauzulę WHERE w tych tabelach tymczasowych
3. Opóźnij dołączanie do ostatniego

Oto moje proponowane zapytanie:

SELECT
  sounds.*,srkeys.avg_rating,srkeys.votes
FROM
(
  SELECT AA.id,avg(BB.rating) AS avg_rating, count(BB.rating) AS votes
  (
    SELECT id FROM sounds
    WHERE blacklisted = false 
    AND   ready_for_deployment = true 
    AND   deployed = true 
    AND   type = "Sound" 
    AND   created_at > '2011-03-26 21:25:49'
  ) AA INNER JOIN
  (
    SELECT AAA.ratings,AAA.rateable_id
    FROM ratings AAA
    WHERE rateable_type = 'Sound'
  ) BB
  ON AA.id = BB.rateable_id
  GROUP BY BB.rateable_id
) srkeys INNER JOIN sounds USING (id);

Zalecenie nr 2) Indeksuj tabelę dźwięków za pomocą indeksu, który pomieści klauzulę WHERE

Kolumny tego indeksu obejmują wszystkie kolumny z klauzuli WHERE, z wartościami statycznymi na początku, a na końcu ruchomym celem

ALTER TABLE sounds ADD INDEX support_index
(blacklisted,ready_for_deployment,deployed,type,created_at);

Szczerze wierzę, że będziesz mile zaskoczony. Spróbuj !!!

AKTUALIZACJA 21.05.2011 19:04

Właśnie widziałem liczność. AUĆ !!! Kardynalność 1 dla rateable_id. Chłopcze, czuję się głupio !!!

AKTUALIZACJA 21.05.2011, 19:20

Może zrobienie indeksu wystarczy, by coś poprawić.

AKTUALIZACJA 21.05.2011 22:56

Uruchom to:

EXPLAIN SELECT
  sounds.*,srkeys.avg_rating,srkeys.votes
FROM
(
  SELECT AA.id,avg(BB.rating) AS avg_rating, count(BB.rating) AS votes FROM
  (
    SELECT id FROM sounds
    WHERE blacklisted = false 
    AND   ready_for_deployment = true 
    AND   deployed = true 
    AND   type = "Sound" 
    AND   created_at > '2011-03-26 21:25:49'
  ) AA INNER JOIN
  (
    SELECT AAA.ratings,AAA.rateable_id
    FROM ratings AAA
    WHERE rateable_type = 'Sound'
  ) BB
  ON AA.id = BB.rateable_id
  GROUP BY BB.rateable_id
) srkeys INNER JOIN sounds USING (id);

AKTUALIZACJA 21.05.2011 23:34

Znowu przebudowałem to. Spróbuj tego:

EXPLAIN
  SELECT AA.id,avg(BB.rating) AS avg_rating, count(BB.rating) AS votes FROM
  (
    SELECT id FROM sounds
    WHERE blacklisted = false 
    AND   ready_for_deployment = true 
    AND   deployed = true 
    AND   type = "Sound" 
    AND   created_at > '2011-03-26 21:25:49'
  ) AA INNER JOIN
  (
    SELECT AAA.ratings,AAA.rateable_id
    FROM ratings AAA
    WHERE rateable_type = 'Sound'
  ) BB
  ON AA.id = BB.rateable_id
  GROUP BY BB.rateable_id
;

AKTUALIZACJA 21.05.2011 23:55

Znowu przebudowałem to. Spróbuj tego, proszę (ostatni raz):

EXPLAIN
  SELECT A.id,avg(B.rating) AS avg_rating, count(B.rating) AS votes FROM
  (
    SELECT BB.* FROM
    (
      SELECT id FROM sounds
      WHERE blacklisted = false 
      AND   ready_for_deployment = true 
      AND   deployed = true 
      AND   type = "Sound" 
      AND   created_at > '2011-03-26 21:25:49'
    ) AA INNER JOIN sounds BB USING (id)
  ) A INNER JOIN
  (
    SELECT AAA.ratings,AAA.rateable_id
    FROM ratings AAA
    WHERE rateable_type = 'Sound'
  ) B
  ON A.id = B.rateable_id
  GROUP BY B.rateable_id;

AKTUALIZACJA 22.05.2011 00:12

Nienawidzę poddawać się !!!!

EXPLAIN
  SELECT A.*,avg(B.rating) AS avg_rating, count(B.rating) AS votes FROM
  (
    SELECT BB.* FROM
    (
      SELECT id FROM sounds
      WHERE blacklisted = false 
      AND   ready_for_deployment = true 
      AND   deployed = true 
      AND   type = "Sound" 
      AND   created_at > '2011-03-26 21:25:49'
    ) AA INNER JOIN sounds BB USING (id)
  ) A,
  (
    SELECT AAA.ratings,AAA.rateable_id
    FROM ratings AAA
    WHERE rateable_type = 'Sound'
    AND AAA.rateable_id = A.id
  ) B
  GROUP BY B.rateable_id;

AKTUALIZACJA 22.05.2011 07:51

Niepokoi mnie to, że oceny wracają z 2 milionami wierszy w WYJAŚNIENIU. Potem mnie uderzyło. Możesz potrzebować innego indeksu w tabeli ocen, który zaczyna się od rateable_type:

ALTER TABLE ratings ADD INDEX
rateable_type_rateable_id_ndx (rateable_type,rateable_id);

Celem tego indeksu jest zmniejszenie tabeli tymczasowej, która manipuluje ocenami, tak aby była mniejsza niż 2 miliony. Jeśli uda nam się znacznie zmniejszyć tę tabelę temp. (Co najmniej o połowę), możemy mieć większą nadzieję na twoje zapytanie i szybciej pracować.

Po utworzeniu tego indeksu spróbuj ponownie zaproponować moje oryginalne zapytanie i wypróbuj swoje:

SELECT
  sounds.*,srkeys.avg_rating,srkeys.votes
FROM
(
  SELECT AA.id,avg(BB.rating) AS avg_rating, count(BB.rating) AS votes
  (
    SELECT id FROM sounds
    WHERE blacklisted = false 
    AND   ready_for_deployment = true 
    AND   deployed = true 
    AND   type = "Sound" 
    AND   created_at > '2011-03-26 21:25:49'
  ) AA INNER JOIN
  (
    SELECT AAA.ratings,AAA.rateable_id
    FROM ratings AAA
    WHERE rateable_type = 'Sound'
  ) BB
  ON AA.id = BB.rateable_id
  GROUP BY BB.rateable_id
) srkeys INNER JOIN sounds USING (id);

AKTUALIZACJA 22.05.2011, 18:39: SŁOWA KOŃCOWE

Przeprojektowałem zapytanie w procedurze przechowywanej i dodałem indeks, aby pomóc odpowiedzieć na pytanie dotyczące przyspieszenia. Dostałem 6 głosów pozytywnych, zaakceptowałem odpowiedź i odebrałem 200 nagród.

Dokonałem również refaktoryzacji innego zapytania (wyniki marginalne) i dodałem indeks (wyniki dramatyczne). Mam 2 głosy poparcia i odpowiedź została zaakceptowana.

Dodałem indeks dla kolejnego wyzwania zapytania i raz zostałem oceniony

a teraz twoje pytanie .

Chcąc odpowiedzieć na wszystkie takie pytania (w tym twoje), zainspirowałem się filmem na YouTube, który oglądałem podczas refaktoryzacji zapytań.

Jeszcze raz dziękuję, @coneybeare !!! Chciałem odpowiedzieć na to pytanie w możliwie najszerszym zakresie, nie tylko przyjmując punkty lub pochwały. Teraz czuję, że zdobyłem punkty !!!

Dzięki za wynik EXPLAIN. Jak można stwierdzić z tego stwierdzenia, powodem, dla którego tak długo to trwa, jest pełne skanowanie tabel w tabeli ocen. Nic w instrukcji WHERE nie filtruje 2 milionów wierszy.

Możesz dodać indeks do ratings.type, ale zgaduję, że KARDYNALNOŚĆ będzie naprawdę niska i nadal będziesz skanował sporo wierszy ratings.

Alternatywnie możesz spróbować użyć wskazówek indeksu, aby zmusić mysql do korzystania z indeksów dźwięków.

Zaktualizowano:

Gdybym to był ja, dodałbym indeks, sounds.createdponieważ ma on największą szansę na filtrowanie wierszy i prawdopodobnie zmusi optymalizator zapytań mysql do korzystania z indeksów tabeli dźwięków. Uważaj tylko na zapytania, które wykorzystują utworzone ramy czasowe (1 rok, 3 miesiące, zależy tylko od wielkości tabeli dźwięków).

Jeśli musi to być zapytanie „w locie” , to nieco ogranicza twoje opcje.

Mam zamiar zaproponować podział i pokonanie tego problemu.

--
-- Create an in-memory table
CREATE TEMPORARY TABLE rating_aggregates (
rateable_id INT,
avg_rating NUMERIC,
votes NUMERIC
);
--
-- For now, just aggregate. 
INSERT INTO rating_aggregates
SELECT ratings.rateable_id, 
avg(ratings.rating) AS avg_rating, 
count(ratings.rating) AS votes FROM `sounds`  
WHERE ratings.rateable_type = 'Sound' 
GROUP BY ratings.rateable_id;
--
-- Now get your final product --
SELECT 
sounds.*, 
rating_aggregates.avg_rating, 
rating_aggregates.votes AS votes,
rating_aggregates.rateable_id 
FROM rating_aggregates 
INNER JOIN sounds ON (sounds.id = rating_aggregates.rateable_id) 
WHERE 
ratings.rateable_type = 'Sound' 
   AND sounds.blacklisted = false 
   AND sounds.ready_for_deployment = true 
   AND sounds.deployed = true 
   AND sounds.type = "Sound" 
   AND sounds.created_at > "2011-03-26 21:25:49";

Używaj JOIN, a nie podkwerend. Czy pomogła Ci jakakolwiek próba podzapytania?

POKAŻ UTWÓRZ dźwięki w tabeli \ G

POKAŻ UTWÓRZ oceny w tabeli \ G

Często korzystne jest posiadanie indeksów „złożonych”, a nie indeksów jednokolumnowych. Być może INDEKS (typ, utworzony_at)

Filtrujesz obie tabele w JOIN; może to być problem z wydajnością.

Istnieje około 1500 dźwięków i 2 miliony ocen.

Polecam, że masz identyfikator auto_increment ratings, zbuduj tabelę podsumowań i użyj identyfikatora AI do śledzenia miejsca, w którym „przerwałeś”. Nie przechowuj jednak średnich w tabeli podsumowań:

avg (ratings.rating) AS avg_rating,

Zamiast tego zachowaj SUM (ratings.rating). Średnia średnich jest matematycznie niepoprawna przy obliczaniu średniej; (suma sum) / (suma zliczeń) jest poprawna.